day30-mysql数据库DML以及DQL练习

总结

• DML (数据操作语言)

-- insert
use school;

insert into tb_college values 
(default, '计算机学院', '学习计算机科学与技术，助力国家信息化建设', '');

insert into tb_college(col_name, col_introduce, col_location) values
('外国语学院', '学习英语、德语、法语、西班牙语言的学院', '');

insert into tb_college(col_name, col_introduce, col_location) values
('金融管理学院', '学习与金融有关的学院', ''), 
('石油化工学院', '学习石油开采以及探测等的学院', ''),
('艺术学院', '搞艺术的学院', '');



use school;

insert into tb_student values
(20163104, '小玖', '女', '1997-09-09', default, 16),
(20163102, '重云', '男', '1987-09-09', default, 11),
(20163111, '刻晴', '女', '1526-09-09', default, 18),
(20163192, '霄', '男', '1145-09-09', default, 15),
(20163177, '莫娜', '女', '1985-09-09', default, 18);

-- Error Code: 1062. Duplicate entry '20163104' for key 'tb_student.PRIMARY'
insert into tb_student values
(20163104, '小小玖', '女', '1917-09-09', default, 16);

-- Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 
-- (`school`.`tb_student`, CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`))
insert into tb_student values
(20163124, '小小玖', '女', '1917-09-09', default, 13);

-- delete
use school;

delete from tb_student where stu_id = 20163104;

select * from tb_student;

-- 删全表，并且无法回复
-- Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint 
-- (`school`.`tb_record`, CONSTRAINT `tb_record_ibfk_1`)
truncate table tb_student;

-- update
use school;

select * from tb_student;

update tb_student set stu_addr = '四川成都' where stu_id in (20163102, 20163104, 20163192);

-- Error Code: 1175. You are using safe update mode and you tried to 
-- update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
update tb_student set stu_addr = '四川成都';

update tb_student set stu_birth = '1990-01-01', stu_addr = '四川成都'
where stu_id between 20163333 and 20161111;

select * from tb_student;

• DQL (数据查询语言)

-- select 

use school;

-- 查询所有学生信息
-- select * from tb_student;  -- 效率低
select stu_id, stu_name, stu_sex, stu_birth, stu_addr, col_id from tb_student;

-- 查询所有课程名称及学分(投影和别名)
select cou_name, cou_credit from tb_course;
select cou_name as 课程名称, cou_credit as 学分 from tb_course;

-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student where stu_sex = '女';
select stu_name as 姓名, stu_birth as 出生日期 from tb_student where stu_sex = '女';

-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name, stu_sex, stu_birth from tb_student 
where stu_birth between '1980-01-01' and '1989-12-31';

select stu_name, stu_sex, stu_birth from tb_student 
where stu_birth >= '1980-01-01' and stu_birth <= '1989-12-31';

-- case xxx when 1 then 'y1' else 'z1' end  通用
select stu_name as 性别, 
case stu_sex when 1 then '男' else '女' end as 性别, 
stu_birth as 生日 from tb_student 
where stu_birth >= '1980-01-01' and stu_birth <= '1989-12-31';

-- SQL方言(因为其他的数据库可能没有if函数)
-- 例如：Oracle中做同样的事的函数叫做decode
select stu_name as 性别, 
if(stu_sex, '男', '女') as 性别, 
stu_birth as 生日 from tb_student 
where stu_birth >= '1980-01-01' and stu_birth <= '1989-12-31';

-- 查询所有80后所有女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student 
where stu_birth >= '1980-01-01' and stu_birth <= '1989-12-31' and stu_sex = '女';

-- 查询姓”杨“的学生姓名和性别(模糊)
-- % ————》用于匹配零个或者任意多个字符
select stu_name, case stu_sex  when 1 then '男' else '女' end from tb_student where stu_name like '杨%';

-- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
-- _ ————》用于匹配一个字符
select stu_name, stu_sex from tb_student where stu_name like '杨_';

-- 查询姓”杨“名字三个字的学生姓名和性别(模糊)
select stu_name, stu_sex from tb_student where stu_name like '杨__';

-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
-- 提示：前面带%的查询性能基本上都是非常糟糕的
select stu_name from tb_student where stu_name like '%不%' or stu_name like '%嫣%';
-- union 并集运算
select stu_name from tb_student where stu_name like '%不%'
union
select stu_name from tb_student where stu_name like '%嫣%';
-- union all 并集运算所有的，不去重
select stu_name from tb_student where stu_name like '%不%'
union all
select stu_name from tb_student where stu_name like '%嫣%';

-- 正则表达式模糊查询
select stu_name from tb_student where stu_name regexp '^.*[不|嫣].*$';

-- 查询没有录入家庭住址的学生姓名(空值)
-- null做任何运算结果也是null，null相当于是条件不成立
select stu_name from tb_student where stu_addr<=>null;
select stu_name from tb_student where stu_addr is null;

-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_addr is not null;

-- 查询学生选课的所有日期(去重)
select distinct sel_date from tb_record;

-- 查询学生的家庭住址(去重)
select distinct stu_addr from tb_student where stu_addr is not null;

-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- asc - (默认)升序(从小到大), desc - 降序（从大到小)
select stu_name, stu_birth from tb_student 
where stu_sex = 1 order by stu_birth asc;
-- excercise --
-- select * from tb_student;
-- insert into tb_student values (76231, '林平之', 1, '1989-12-03', '四川成都', 2);
select stu_name, stu_birth from tb_student 
order by stu_birth asc, stu_sex;

-- 查询年龄最大的学生的出生日期(聚合函数)
select min(stu_birth) from tb_student;

-- 查询年龄最大的学生的年龄
select now() from dual;
select curdate() from dual;
select min(stu_birth) as 生日, floor(datediff(curdate(), min(stu_birth))/365) from tb_student;
select min(stu_birth) as 生日, year(curdate()) - year(min(stu_birth)) from tb_student;

-- 查询所有考试的平均成绩
-- 聚合函数在遇到null值的时候会做忽略的处理
select avg(score) as 平均分 from tb_record;
select sum(score) / count(score) from tb_record;

-- 查询年龄最小的学生的出生日期(聚合函数)
select max(stu_birth) from tb_record;

-- 如果做计数操作，建议使用count(*),这样才不会漏掉空值
select count(*) from tb_record;		-- 18
select count(score) from tb_record;		-- 14

-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(score) from tb_record where cid = 1111;

-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) from tb_record where sid = 1001;

-- 查询男女学生的人数(分组和聚合函数)
-- SAC (Split - Aggregate - Combine)
select if(stu_sex, '男', '女') as 性别, count(*) as 人数 from tb_student group by stu_sex order by stu_sex desc;

-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select sid, round(avg(score), 1) as 平均分 from tb_record group by sid;

-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 分组以前的数据筛选使用where子句,分组以后的条件语句用having
select sid, round(avg(score), 1) as 平均分 from tb_record group by sid having 平均分 >= 90;

-- Error Code: 1248. Every derived table must have its own alias
select * from (select sid, round(avg(score), 1) from tb_record group by sid);

select * from (select sid, round(avg(score), 1) as 平均分 from tb_record group by sid) as temp where 平均分 >= 90;

-- 查询年龄最大的学生的姓名(子查询)
select stu_name from tb_student where stu_birth = 
(select min(stu_birth) from tb_student);


-- 查询年龄最大的学生姓名和年龄(子查询+运算)
select stu_name, year(curdate()) - year(stu_birth) as age from tb_student where stu_birth = (select min(stu_birth) from tb_student);
-- 该，如何有多个同年龄且最大的时候 = 会出错
select stu_name, year(curdate()) - year(stu_birth) as age from tb_student where stu_birth in (select min(stu_birth) from tb_student);


-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select sid from tb_record group by sid having count(*) > 2;
select stu_name from tb_student where stu_id in (
select sid from tb_record group by sid having count(*) > 2
);
-- 连接
select stu_name from tb_student as a right join (select sid, count(*) as 个数 from tb_record group by sid having 个数 > 2) as b on a.stu_id = b.sid;
select stu_name from tb_student as a inner join (select sid, count(*) as 个数 from tb_record group by sid having 个数 > 2) as b on a.stu_id = b.sid;


-- 单独练习
select cou_name, cou_credit, tea_name from tb_course t1 inner join tb_teacher t2 on t1.tea_id=t2.tea_id;


-- 查询学生姓名、课程名称以及成绩(连接查询)
select stu_name, cou_name, score from tb_student, tb_record, 
tb_course where stu_id=sid and cid = cou_id and score is not null;

select stu_name, cou_name, score from tb_student t1 inner join tb_record t2 on t1.stu_id = t2.sid 
inner join tb_course t3 on t2.cid = t3.cou_id where score is not null;


-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu_name, avg_score from tb_student, (
select sid, round(sum(score) / count(*), 2) avg_score from tb_record group by sid) tb_temp 
where sid = stu_id;


-- 查询每个学生的姓名和选课数量(左外连接和子查询)
-- 内连接: 只能查到满足连接条件的记录
-- 外连接: 左连接、右外连接、全连接（MySql不支持)
-- 写在join前面的是左表, 写在join右边的是右表
-- 左外链接：把左表不满足连接条件的记录也能够完整的查出来，不满足条件的地方默认填null
select stu_name, 
ifnull(total, 0) total
from tb_student left join
(select sid, count(*) as total from tb_record group by sid) tb_temp
on stu_id = sid;

select stu_name, 
case total when total then total else 0 end total
from tb_student left join
(select sid, count(*) as total from tb_record group by sid) tb_temp
on stu_id = sid;