MySQ（三）：语句规则和难点

常见查询语句：

• 创建数据库：create database 数据库名;
• 进入数据库：use 数据库名;
• 显示数据库/表：show databases/tables;
• 查看表结构：desc 表名;
• 完全删除数据库/表：drop databasse/table 数据库名/表名;
• 创建表：create table 表名(字段名 数据类型 [约束条件] [主键],...)
• 修改：alter，添加、修改、删除

-- 添加主键字段
alter table 表名 add 字段名 数据类型 primary key;
-- 添加定义外键
alter table 表名 add foreign key(字段名) references 表名(字段名)
-- 添加字段
alter table 表名 add 字段名 数据类型 [约束条件];
-- 添加约束条件
alter table 表名 add unique(字段名);
alter table 表名 alter column 字段名 set default 值;

-- 修改表名
alter table 表名 rename 新表名;
-- 修改字段名
alter table 表名 change 字段名 新字段名 数据类型;
-- 修改字段数据类型
alter table 表名 modify 字段名 数据类型 [约束条件];
-- 修改字段的排列位置
alter table 表名 modify 字段名 数据类型 first/after 字段名;

-- 删除字段
alter table 表名 drop 字段名;
-- 删除主键约束
alter table  表名 drop primary key;

• 插入：insert into

-- 插入指定字段名的值
insert into 表名(字段名,...) values (值...),...;
-- 默认插入所有字段的值
insert into 表名 values(值...),...;
-- 向已有表插入其他表的列
insert into 表名(字段名,...) select 字段名 from 表名;

• csv文件数据导入mysql：先建表后导数，且用/或\\

-先查看安全路径
show variables like '%secure%';
load data infile '安全路径/文件名.csv'
into table 表名
fields terminated by ','
ignore 1 lines;

• mysql查询结果导入csv文件：不用提前建表

select 语句
into outfile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/文件名"
fields terminated by ',';

• 更新数据：要先设置数据库安全权限：set sql_safe_updates=0，后更新数据：update 表名 set 字段名=新值
• 删除表格内容保留结构：delete from 表名 [where 条件]，或者：truncate 表名，前者可以进行where筛选，但后者效率更高
• 模糊查询：select * from emp where ename like '_a%';
• 限制查询结果数量limit：

select * from emp limit 5;
select * from emp limit 0,5;
select * from emp limit 5 offset 0;

• 条件查询：

√ 普通筛选where，单值筛选用=，多值筛选用in/any/all，若in后存在null则结果返回null    
√ 引用子查询要用别名
√ exists(子查询)与in不同，前者在于子查询返回True或False
select * from emp where deptno in(10,20);
select * from emp where sal>any(...);
select * from emp where sal>all(...);
select * from emp where (deptno,job)=(20,'clerk');

√ 分组后筛选having，可用聚合函数、开窗函数、group_concat函数
√ having要搭配group by，不能单独适用，如果要where筛选聚合函数时考虑子查询
select avg(sal) from emp group by deptno having avg(sal)>=5000;

• 子查询：标量子查询、行/列子查询、表子查询、select子查询

说明：
√ 子查询的位置很灵活，可以是select子句中主查询的字段或计算值、where/having子句中主查询的条件、from子句中主查询的表  
√ 子查询效率不高，需要为建立和销毁临时表；因此数据量多的情况用连接查询join替代子查询优化速度  
√ 连接查询几乎都可以替换为子查询，但不是子查询都可以替换为连接查询，如where子句中用聚合函数做条件筛选

• 表的连接与合并：join和union

-- left/right/inner join：表的横向连接，关键字段key
select * from t1 
left join t2 on t1.key1=t2.key2;
√ 注：join on后面还可以连接between...and...，如查找工资等级

-- 无join的条件连接
select * from t1,t2
where t1.key1=t2.key2;

-- union：表的纵向合并，表字段名和数据类型一致，union去重，union all不去重
select * from t1
union 
select * from t2；

√ 全连接
select * from t1 left join t2 on t1.key1=t2.key2
union
select * from t1 right join t2 on t1.key1=t2.key2;

√ 左反连接
select * from t1 left join t2 on t1.key1=t2.key2
where t2.key2 is null;

√ 自连接：适用于一张表存在可自我匹配的key的场景，还可以进行数值大小比较
select s1.name 员工姓名 s2.name 领导姓名
from emp s1
left join emp s2 on s1.mgr=s2.empid;

书写顺序和执行顺序：

关键字	执行顺序
select	⑦
distinct	⑧
from	①
join	③
on	②
where	④
group by	⑤
having	⑥
order by	⑨
limit	⑩

难点：

• 分数表格一维转二维
  说明：对每条记录判断后得到的score/0做求和，每条记录会返回score/0；一定要分组group by，否则会对所有记录求和。

-- 查询所有学生的课程及分数情况（一维转二维）
select s_id,
sum(if(c_id='01',score,0)) '01',
sum(if(c_id='02',score,0)) '02',
sum(if(c_id='03',score,0)) '03'
from sc group by s_id;

select s_id,
sum((c_id=01)*score) '01',
sum((c_id=02)*score) '02',
sum((c_id=03)*score) '03'
from sc group by s_id;

• 率的计算
  说明：可以利用avg/sum/count的定义和判断值1/0，也可以用计算公式。但是要注意count和sum的区别，前者是对字段记录做计数，判断结果不影响计数的结果，而后者是对判断结果的求和，判断结果会影响求和的结果。

-- 计算各科成绩最高分、最低分和平均分：课程ID，课程name，最高分，最低分，平均分，及格率(>=60)，中等率(70-90)，优秀率(>=90)
select sc.c_id,c_name,max(score),min(score),avg(score),
		avg(score>=60) 及格率,
        avg(score>=70 and score<80) 中等率,
        avg(score>=80 and score<90) 优良率,
        avg(score>=90) 优秀率
from sc
left join co on sc.c_id=co.c_id
group by sc.c_id;

select co.c_id,co.c_name,max(score),min(score),avg(score),
sum(case when score>=60 then 1 else 0 end)/count(sc.c_id) 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(sc.c_id) 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.c_id) 优良率,
sum(case when score>=90 then 1 else 0 end)/count(sc.c_id) 优秀率
from sc left join co on sc.c_id=co.c_id
group by sc.c_id;

• 聚合函数+窗口函数用于排名
  说明：order by后面可以用聚合函数，不能在group by后面使用order by，因为order by会在select后面执行，先出名次再排序得出错误的结果

-- 按学生的总成绩从高到低进行排名
select s_id,sum(score) 总成绩,dense_rank() over(order by sum(score) desc) 排名 
from sc
group by s_id;

• 查询生日周或生日月

-- 查询本周生日的学生
select * from stu
where date_format(s_birth,'2020-%m-%d') 
between date_sub(curdate(),interval date_format(curdate(),'%w') day)
and date_add(curdate(),interval 6-date_format(curdate(),'%w') day);

-- 查询下月生日的同学：学生满足条件——出生月份=(本月=12,1,本月+1)
select * from stu
where month(s_birth)=if(month(curdate())=12,1,month(curdate())+1);

• 构造触发器

-- 构造一个触发器audit_log，在向employees_test表中插入数据时，触发插入相关的数据到audit中
create trigger audit_log after insert on employees_test
begin
	insert into audit values(new.id,new.name)
end;