概述：

1.通过表结构介绍、来操作mysql常用的语法&语句；

2.现在学习下几个基本的sql语句，万变不离其宗，把基础弄扎实了其它的就很容易了

一、表结构&建表：

1.学生基础信息表student：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL COMMENT '学生id',
`sname` varchar(30) DEFAULT NULL COMMENT '学生姓名',
`sage` int(11) DEFAULT NULL COMMENT '学生年龄',
`ssex` varchar(8) DEFAULT NULL COMMENT '学生性别',
`creation_time` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.学生成绩表sc：

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
    `sid` INT (11) NOT NULL COMMENT '学生id',
    `cid` INT (11) NOT NULL COMMENT '成绩id',
    `score` INT (11) DEFAULT NULL COMMENT '成绩分数',
    `creation_time` datetime NOT NULL COMMENT '创建时间'
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

二、常用语法：

查询：

1.简单查询

#查询sage等于25
SELECT * from student where sage=25;

#查询sage不等于25
SELECT * from student where sage<>25;
SELECT * from student where sage!=25;


########逻辑查询 and、or
SELECT * from student where sage=25 and sage='女';
SELECT * from student where sage=25 or sage='女';

#in，not in 的用法
SELECT * FROM student WHERE sname in ('刘亦菲','王康','陈三');
SELECT * FROM student WHERE sname not in('刘亦菲','王康','陈三');


####is 和not is、null
#查询不为null
SELECT * from student where ssex  IS not NULL;
#查询为null
SELECT * from student where ssex  IS NULL;


#############限制返回记录：
#最后三条
select * from student order by  sage desc limit 3; 
#分页查询(范围)
select * from student limit 0,5
#显示前2行
select * from student limit 2

2.范围查询

#查询sage 25到30的数据
SELECT * from student where sage BETWEEN 25 and 30;

#查询 creation_time日期范围
SELECT * from student where creation_time BETWEEN '2021-10-03 00:00:00' and '2021-10-25 18:00:00'

#查询creation_time数据
SELECT * from student where creation_time like '2021-10-%'

3.分组查询

#查询后分组
SELECT * from student where ssex='女' GROUP BY sage;
#分组后倒序
SELECT * from student where ssex='女' GROUP BY sage ORDER BY creation_time desc;

#倒序-desc
SELECT * from student where ssex='女' ORDER BY creation_time desc;
#顺序-asc
SELECT * from student where ssex='女' ORDER BY creation_time asc;

4.like匹配查询

#####查询匹配关键字（%表示所有，_表示任意一个字符）
#匹配包含'刘'记录
select * from student where sname like '%刘%';
#匹配以'伊朗'结尾的记录
select * from student where sname like '%伊朗';
#匹配任意一个字符
select * from student where sname like '刘_菲';

5.子查询

#子查询-包含查询
select  * from student  where  sage in(select  sage from  student  where ssex='女' and sage<30 and creation_time like '2021-10%' GROUP BY sage); 


#子查询-别名查询
SELECT * from(
(SELECT  date_format(creation_time,'%Y-%m-%d') as _time  FROM districtdervice_task_synchronization 
where DATE_SUB(CURDATE(),INTERVAL 7 DAY) <=date(creation_time))
) 别名 GROUP BY _time;

6.单表统计

#统计'2021年10月份'每天的sname字段的个数
SELECT date_format(creation_time,'%Y-%m-%d') as _time ,count(sname) FROM student where  creation_time  like '2021-10%' GROUP BY DAY(creation_time);
#DAY()按天统计 , WEEK()按周统计，MONTH()按月统计，QUARTER()按季度统计，YEAR()按年统计
# date_format日期格式化


#统计最近30天,每天的sname字段的个数
SELECT date_format(creation_time,'%Y-%m-%d') as _time ,count(sname) FROM student where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=date(creation_time)   GROUP BY DAY(creation_time);


#统计某个月的记录数
SELECT count(*) from student where  creation_time like '%2021-10%';

7.聚合函数

#去重
SELECT distinct* from student;

#统计条数
SELECT count(*) from student;

#求和
SELECT sum(sage) from student where ssex='女';

#平均数
SELECT avg(sage) from student where ssex='女';

#ROUND(字段,n)保留小数n位
SELECT ROUND(avg(sage),1) as _avg from student where ssex='女';

#最大值
SELECT max(sage) from student where ssex='女';

#最小值
SELECT min(sage) from student where ssex='女';

8.多表连接查询

#等值查询
select  *  from student st INNER JOIN sc scc on st.sid = scc.sid
where st.sage=25

select  st.sid,st.sname,st.sage,st.ssex,scc.sid,scc.score from student st INNER JOIN sc scc on st.sid = scc.sid
where st.ssex='女';

#左连接
select  st.sid,st.sname,st.sage,st.ssex,scc.sid,scc.score from student st left JOIN sc scc on st.sid = scc.sid
where st.ssex='女';

#右连接
select  st.sid,st.sname,st.sage,st.ssex,scc.sid,scc.score from student st right join sc scc on st.sid = scc.sid
where st.ssex='女';

#左连接：左表不管能不能匹配上条件,最终都会保留:能匹配正确的保留; 若不能匹配,右表的字段都置NULL

#右连接：右表不管能不能匹配上条件,最终都会保留:能匹配正确的保留; 若不能匹配,左表的字段都置NULL。

9.REGEXP正则

#匹配包含某个字符的记录
SELECT * FROM student WHERE sname REGEXP  '刘';
#匹配字段包含数字的记录
SELECT * FROM student WHERE sname REGEXP  '\\d';
SELECT * FROM student WHERE sage REGEXP  '[0-9]';
#匹配包含数字5位以上的记录
SELECT * FROM student WHERE sname REGEXP  '\\d{5}';


#匹配'王'开头
SELECT * FROM student WHERE sname REGEXP  '^王';
#匹配'菲'结尾
SELECT * FROM student WHERE sname REGEXP  '菲$';


#匹配整型至少3位开始
SELECT * FROM student WHERE sage REGEXP  '^\\d{3,}$';
#匹配整型3到5位(字段为整形,筛选3到5位数)
SELECT * FROM student WHERE sage REGEXP  '^\\d{3,5}$';
#匹配字符串,显示2到6位的记录
SELECT * FROM dalan_test WHERE apk_link REGEXP  '^\\w{2,6}$';


#匹配包含"陈"或"菲"的记录
SELECT * FROM student WHERE sname REGEXP  '陈|菲';


#匹配任意一位字符串
SELECT * FROM student WHERE sname REGEXP  '刘.菲';
#匹配一次或多次
SELECT * FROM student WHERE sname REGEXP  '刘.+菲';
#匹配任意次数
SELECT * FROM student WHERE sname REGEXP  '刘*';

10.联合查询

联合查询结果是将多个select语句的查询结果合并到一块，因为在某种情况下需要将几个select语句查询的结果合并起来显示。需要两个表结构一样

其中union选项有两个选项可选

all：表示无论重复都输出
distinct: 去重(整个重复)（默认的）

select * from student
union all
select * from student111;

备注：不加all，就会过滤相同的记录

Union:
作用：把2次或多次查询结果合并起来
要求：两次查询的列数一致
推荐：查询的每一列，相对应的列类型也一样

#合并两个查询结果
SELECT * from districtdervice_task_synchronization where job_flow_id=20007
UNION
SELECT * from districtdervice_task_synchronization where job_flow_id=22420

11.EXISTS的用法

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测行的存在。

select  * from student  
where  sid<10 and EXISTS(
select  sage from  student  
where ssex='女' and sage<30 and creation_time like '2021-10%' GROUP BY sage
);

备注：如果子查询结果为空，那么整体查询也为空

12高级查询case...when....then

注意：使用case...when....then将表中的列数据转为行（表头）构建一个虚表，再使用case...when....then根据条件替换数据

when表示条件，then表示替换的值(可以是字段)

使用场景：等值转换、范围转换、行转列

##___________________________________student表查询_______________________________
#一行显示多个条件的查询
select * from(
(select '小于20',count(sage)as 'sage' from student where sage<20) chen20,
(select '20到40',count(sage)as 'sage' from student where sage between 20 and 40) wei,
(select '大于40',count(sage)as 'sage' from student where sage>40) cheb40
)


#使用case...when....then 把条件放到一列
SELECT sage,
(CASE  WHEN sage >=40 THEN '优秀' WHEN sage >=30 THEN '及格' ELSE '不及格' END) AS '评分',
#(CASE  WHEN sage >=40 THEN '优秀' WHEN sage >=30 THEN '及格' ELSE '不及格' END) AS '评分'
FROM student

#使用case...when....then一行查询
SELECT sage,(case  when sage >=40 then '优秀' when sage >=30 then '及格' else '不及格' end) FROM student;


#多条件查询后，分组并统计
select count(sage),评分 from(
(SELECT sage,
(CASE  WHEN sage >=40 THEN '优秀' WHEN sage >=30 THEN '及格' ELSE '不及格' END) AS '评分'
FROM student) chen) GROUP BY 评分;

13.查询记录以'逗号'作为条件：

#以student.sname字段作为条件，使用逗号查询
select * from sc as sccc where find_in_set( sccc.sid,(select sname FROM  student  WHERE  sid=3))

student表：sc表：结果：

14.日期查询：

#查看student表最近30天的记录
SELECT * FROM student where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=date(creation_time); 

#查看昨天记录
SELECT * FROM 表名 WHERE TO_DAYS(NOW( ) ) - TO_DAYS( 时间字段名) <= 1;
#查看过去7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <=date(时间字段名);
#查看本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) =DATE_FORMAT( CURDATE( ) , '%Y%m' );
#查看上月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1        
#查看本年
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());  
#查看上年
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

插入：

#指定全字段插入
INSERT INTO student(sid,sname,sage,ssex,creation_time) VALUES('19','招佳轩',32,'女','2021-10-07 16:48:31');
#指定部分字段插入
INSERT INTO student(sid,sname,sage,creation_time) VALUES('20','招佳轩',32,'2021-10-07 16:48:31');
#直接插入
INSERT INTO student
VALUES('21','招佳轩',32,'男','2021-10-07 16:48:31');
#通过set插入
INSERT INTO student
set sid=23,sname='招佳轩',sage=25,ssex='男',creation_time='2021-10-07 16:48:31';

#先查询将查询结果插入数据表：
INSERT INTO 表名1(字段1) SELECT 字段2 FROM 表名2 WHERE 条件表达式;

更新：

#普通更新
UPDATE student SET ssex = '男',sname = '100' WHERE sage=30；

#连表更新
update student,sc set student.sname='这是测试111',sc.score='59' where student.sid=sc.sid and student.sage=29；

#同表条件更新(条件和更新字段在同一个表)
update  student set sname='test123'
WHERE sid IN(
SELECT stu.sid from(
SELECT sid from student where sname='王丽') stu
)

#子更新(条件和更新字段不在同表)
UPDATE student SET sage=50 WHERE  sname IN(SELECT student_chen.sname from student_chen WHERE student_chen.sid=31)

删除：

delete 和 truncate 仅仅删除表数据(这两种都不删除表的结构)，drop 连表数据和表结构一起删除。

truncate和drop不能回滚(删除后不能找回数据)，delete可以回滚数据

执行的速度上：drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

truncate不用写日志，delete要写日志，前者的删除效率要高于后者,前者是整体删除后者是逐句删除

truncate是将这个表的所有数据都删除，而delete可以用where条件删除部分数据，也可以删除所有数据

#####delete####
#指定条件删除记录(不加where条件是清空表、where条件后可加and或or)
DELETE FROM student where sage=30;
#删除条件为空的数据
delete from 表名  where 字段 is null;

###truncate###
#删除表的所有记录,保留表
TRUNCATE table 表名;

###drop###
#删除结构及表
drop table 表名;
#删除表字段
alter table 表名 drop column 字段;

库、表的相关命令：

###################表结构修改############
#修改字段类型 
alter table 表名 modify 字段 字段类型;
#添加新的字段 
alter table 表名 add 字段 字段类型;
#添加字段并指定位置  
alter table 表名 add 字段 字段类型   after 字段;
#删除表字段  
alter table 表名 drop 字段名;
#修改指定的字段  
alter table 表名 change 原字段名字  新的字段名字 字段类型;


#################数据库、表常用查询##############
#查看所有的数据库
show databases; 
#创建一个叫test的数据库
create database test; 
#删除一个叫test的数据库
drop database test;
#选中库 ,在建表之前必须要选择数据库
use test;
#在选中的数据库之中查看所有的表
show tables; 
#创建表
create table 表名 (字段1 类型, 字段2 类型);
#查看所在的表的字段
desc 表名;
#删除表
drop table 表名; 
#查看创建库的详细信息
show create database 库名;
#查看创建表的详细信息
show create table 表名;

三、通用语法：

1.连接数据库

#远程连接
mysql -h 主机地址 -u 用户名 -p 用户密码
#本地连接
mysql -u 用户名 -p

2.查询连接数

#查看最大连接数
show variables like '%max_connections%';

#当前打开的连接的数量
show status like '%Threads_connected%';

#重新设置最大连接数
set global max_connections=1000

#同时使用的连接的最大数目
show status like '%Max_used_connections%';

#发往服务器的查询的数量
show status like '%Questions%'

#尝试已经失败的MySQL服务器的连接的次数
show status like '%Aborted_connects%';

#试图连接MySQL服务器的次数。
show status like  '% Connections%';

3.获取当前时间&日期

#获取当前时间戳
select unix_timestamp(now())；
#获取当前日期+时间
SELECT now();
#获取当前日期
SELECT curdate();
#获取当前时间
SELECT curtime();

4.查看数据库信息&表信息

#查询当前用户
select user();
#查询表创建信息
show create table 表名;
#查询数据库版本
select version();
#查询当前使用的数据库
select database();

5.系统级操作&查询

#查看当前使用的存储引擎
show variables like '%storage_engine%';

#查看数据库中已创建的触发器
SHOW TRIGGERS;

#指定字符编码（一般用在建表的时候）
DEFAULT CHARSET=utf8;

6.用户新增&权限&修改密码

#新增用户 (如果想所有用户可在所有主机访问可把 用户名@登录主机 改为%@%)
grant select on 数据库.* to 用户名@登录主机 identified by "密码";

#允许root用户远程访问所有的数据库
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

#授权用户
grant all on test.*to'xiaoming'@'localhost';
#刷新权限
flush privileges;
#取消授权
revoke all on test.* from 'xiaoming'@'localhost';

#改密码
mysqladmin -u 用户名 -p 旧密码 password 新密码;

#删除用户
drop user 'xiaoming'@'localhost';

7.mysql备份&恢复

#备份：
语法：mysqldump -h服务器地址 -u登录名 -p 数据库名> 文件名
例子：mysqldump -hlocalhost -uroot db>e:/db.sql

#恢复：
语法：mysql -h服务器地址 -u登录名 -p 数据库名<文件名
例子：mysql -hlocahost -uroot -p db2<e:/db.sql

8.其他

#创建A表从B表备份(表备份)
CREATE TABLE A表 AS SELECT * FROM 表B where id=1

#创建表先先判断是否存在,存在就先删除
DROP TABLE IF EXISTS `test_chen`;
CREATE TABLE `test_chen` (
`sid` int(11) NOT NULL COMMENT '学生id',
`sname` varchar(30) DEFAULT NULL COMMENT '学生姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#用文本方式将数据装入数据表中、
LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名;



#查询字段在哪些表存在
SELECT * FROM  information_schema.COLUMNS  WHERE  COLUMN_NAME='字段';

#查看表的创建时间
select CREATE_TIME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='表名';

#查看表结构引擎及修改结构时间
select * from information_schema.tables where table_schema='db_name' and table_name='table_name';

四、概念&理论：

1.where和having的区别

2.内联接,外联接区别

内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。

在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种。

3.什么是存储过程？用什么来调用

存储过程是一组为了完成特定功能的 SQL 语句集合。

优点是允许模块化的设计只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

存储过程的弊端：语法差别很大，移植困难，换了数据库，需要重新编写；

#———————————————————————————补充——————————————————————————————————————————————————————

一、索引：

1.索引类型：

1.普通索引、2.唯一索引、3.主键索引、4.组合索引、5.全文索引

2.索引作用：

它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据(提高查询速度)

3.使用场景：

经常需要搜索的列上，可以加快搜索的速度；
经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

4.优点：

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
可以大大加快数据的检索速度，这也是创建索引的最主要的原因
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

5.缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
索引非常占内存，所以索引也需要谨慎添加，那些字段需要索引

语法案例：

1______________________普通索引_______________________________________________________

是最基本的索引，它没有任何限制。它有以下几种创建方式：

直接创建索引
CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引
CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER NOT NULL ,

    `content` text CHARACTER NULL ,

    `time` int(10) NULL DEFAULT NULL ,    PRIMARY KEY (`id`),    INDEX index_name (title(length))

)

删除索引
DROP INDEX index_name ON table


#_______________________________唯一索引____________________________________________________
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯

创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

创建表的时候直接指定
CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER NOT NULL ,

    `content` text CHARACTER NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    UNIQUE indexName (title(length))

);


#_________________________________主键索引_________________________________________
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) NOT NULL ,    PRIMARY KEY (`id`)

);


#________________________________组合索引___________________________________________
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

相关连接：

https://blog.csdn.net/weixin_39411321/article/details/90602030 ............................mysql联合查询、连接查询、子查询

https://www.cnblogs.com/donleo123/p/11642510.html ............................................MySQL（学生表、教师表、课程表、成绩表）多表查询

https://codeleading.com/article/93662363253/........................................................Mysql高级查询，列转行，大于等于80表示优秀，大于等于60且小于80表示及格，小于60表示不及格(case when的使用场景)

https://www.cnblogs.com/meipu/p/13140835.html ..................................................mysql操作记录查看

https://m.php.cn/article/416691.html ......................................................................mysql索引篇

https://www.cnblogs.com/think-and-do/p/6591523.html .........................................python清洗mysql

https://blog.csdn.net/u010797364/article/details/123400980 ................................docker搭建数据库监控开源工具Lepus，，Lepus

https://blog.csdn.net/fish_study_csdn/article/details/123937237 ...........................慢查询日志分析工具mysqldumpslow，慢查询日志分析

mysql常用语句操作

概述：