用mysql创建一个school数据库，建四张学生表tb_student, 记录表tb_record,课程表tb_course表。

下面是简单的mysql基础语句查询

use school;
– 查询所有学生的所有信息
 select * from tb_student;– 查询学生的学号、姓名和家庭住址(投影)
 select stu_id, stu_name, stu_addr from tb_student;– 查询所有课程的名称及学分(投影和别名) —> alias
 select cou_name as 课程名称, cou_credit as 学分 from tb_course;– 查询所有女学生的姓名和出生日期(筛选)
 select stu_name, stu_birth from tb_student where stu_sex=0;– 查询所有80后学生的姓名、性别和出生日期(筛选)
 select stu_name, stu_sex, stu_birth
 from tb_student
 where stu_birth>=‘1980-1-1’ and stu_birth<=‘1989-12-31’;select stu_name, stu_sex, stu_birth
 from tb_student
 where stu_birth between ‘1980-1-1’ and ‘1989-12-31’;– 标准SQL的做法
 select
 stu_name as 姓名,
 case stu_sex when 1 then ‘男’ else ‘女’ end as 性别,
 stu_birth as 生日
 from tb_student
 where stu_birth between ‘1980-1-1’ and ‘1989-12-31’;select stu_name,
 case stu_sex when 1 then '男’else ‘女’ end,
 stu_birth from tb_student where stu_birth between ‘1980-1-1’ and ‘1989-12-31’;– MySQL方言 if() 函数 —> Oracle方言 decode() 函数
 select
 stu_name as 姓名,
 if(stu_sex, ‘男’, ‘女’) as 性别,
 stu_birth as 生日
 from tb_student
 where stu_birth between ‘1980-1-1’ and ‘1989-12-31’;– 查询姓“杨”的学生姓名和性别(模糊)
 – 通配符（wildcard）—> % —> 匹配零个或任意多个字符
 select stu_name, stu_sex from tb_student where stu_name like ‘杨%’;– 查询姓“杨”名字两个字的学生姓名和性别(模糊)
 – 通配符（wildcard）—> _ —> 精确匹配一个字符
 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 ‘%嫣%’;select stu_name from tb_student where stu_name like ‘%嫣%’
 union
 select stu_name from tb_student where stu_name like ‘%不%’;– 基于正则表达式的模糊查询
 select stu_name from tb_student where stu_name regexp ‘[杨林].{2}’;– 查询没有录入家庭住址的学生姓名(空值)
 select stu_name from tb_student
 where stu_addr is null or stu_addr=’’;select stu_name from tb_student
 where stu_addr<=>null or stu_addr=’’;– 查询录入了家庭住址的学生姓名(空值)
 select stu_name from tb_student
 where stu_addr is not null and stu_addr<>’’;select stu_name from tb_student where stu_addr is not null and stu_addr<>’’;
– 查询学生选课的所有日期(去重)
 select distinct sel_date from tb_record;– 查询学生的家庭住址(去重)
 select distinct stu_addr from tb_student
 where stu_addr is not null;– 查询男学生的姓名和生日按年龄从大到小排列(排序)
 – 升序（从小到大）—> ascending
 select stu_name, stu_birth from tb_student
 where stu_sex=1
 order by stu_birth asc;– curdate —> 获取当前日期
 – datediff —> 计算时间差（以天为单位）
 – floor / ceil —> 向下/上取整
 select
 stu_name,
 stu_birth,
 floor(datediff(curdate(), stu_birth)/365) as stu_age
 from tb_student
 where stu_sex=1
 order by stu_age desc;select stu_name,stu_birth,
 floor(datediff(curdate(),stu_birth)/365) as stu_age
 from tb_student where stu_birth order by stu_age desc;
 – 降序（从大到小）—> descending
 select stu_name, stu_birth from tb_student
 where stu_sex=1
 order by stu_birth desc;– 查询年龄最大的学生的出生日期(聚合函数)
 select min(stu_birth) from tb_student;
 select min(stu_birth) from tb_student;– 查询年龄最小的学生的出生日期(聚合函数)
 select max(stu_birth) from tb_student;– 查询编号为1111的课程考试成绩的最高分
 select max(score) from tb_record where cou_id=1111;– 查询学号为1001的学生考试成绩的最低分
 select min(score) from tb_record where stu_id=1001;– 查询学号为1001的学生考试成绩的平均分
 select avg(score) from tb_record where stu_id=1001;
 select sum(score) / count(score) from tb_record where stu_id=1001;– 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分
 select sum(score) / count(*) from tb_record where stu_id=1001;
 select avg(ifnull(score, 0)) from tb_record where stu_id=1001;– ifnull —> 如果遇到null（空值），将其替换为指定的值
– 查询学号为1001的学生考试成绩的标准差
 select std(score) from tb_record where stu_id=1001;– 查询男女学生的人数(分组和聚合函数)
 select
 if(stu_sex, ‘男’, ‘女’) as 性别,
 count(*) as 人数
 from tb_student group by 性别;select
 if(stu_sex, ‘男’, ‘女’) as 性别,
 count(*) as 人数
 from tb_student group by stu_sex;– 查询每个学院男女学生人数
 select
 col_id as 学院编号,
 if(stu_sex, ‘男’, ‘女’) as 性别,
 count(*) as 人数
 from tb_student group by col_id, stu_sex;– 查询每个学生的学号和平均成绩(分组和聚合函数)
 select
 stu_id as 学号,
 round(avg(score), 2) as 平均分
 from tb_record group by stu_id;– 查询平均成绩大于等于90分的学生的学号和平均成绩
 select
 stu_id as 学号,
 round(avg(score), 2) as 平均分
 from tb_record
 group by stu_id
 having 平均分>=90;– 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
 select
 stu_id as 学号,
 round(avg(score), 2) as 平均分
 from tb_record
 where cou_id in (1111, 2222, 3333)
 group by stu_id
 having 平均分>=90;– 分组以前的数据筛选使用where子句，分组以后的数据筛选使用having子句
– 查询年龄最大的学生的姓名(子查询)
 set @a=(select min(stu_birth) from tb_student);select @a:=(select min(stu_birth) from tb_student);
select @a;
select stu_name from tb_student where stu_birth=@a;
– 嵌套查询：把一个select的结果作为另一个select的一部分来使用
 – 嵌套查询通常也称之为子查询，在查询语句中有两个或多个select
 select stu_name from tb_student
 where stu_birth=(
 select min(stu_birth) from tb_student
 );– 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
 select stu_name from tb_student
 where stu_id in (
 select stu_id from tb_record
 group by stu_id having count(*)>2
 );– 查询学生的姓名、生日和所在学院名称
 select stu_name, stu_birth, col_name
 from tb_student, tb_college
 where tb_student.col_id=tb_college.col_id;select stu_name, stu_birth, col_name
 from tb_student t1 inner join tb_college t2
 on t1.col_id=t2.col_id;select stu_name, stu_birth, col_name
 from tb_student natural join tb_college;– 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
 select t2.stu_id, stu_name, t3.cou_id, cou_name, score
 from tb_record t1, tb_student t2, tb_course t3
 where
 t1.stu_id=t2.stu_id and
 t1.cou_id=t3.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.stu_id inner join tb_course t3
 on t2.cou_id=t3.cou_id where score is not null;select stu_name, cou_name, score
 from tb_student natural join tb_record natural join tb_course
 where score is not null;– 分页查询
 select stu_name, cou_name, score
 from tb_student natural join tb_record natural join tb_course
 where score is not null
 order by score desc limit 5;select stu_name, cou_name, score
 from tb_student natural join tb_record natural join tb_course
 where score is not null
 order by score desc limit 5 offset 5;select stu_name, cou_name, score
 from tb_student natural join tb_record natural join tb_course
 where score is not null
 order by score desc limit 5 offset 10;select stu_name, cou_name, score
 from tb_student natural join tb_record natural join tb_course
 where score is not null
 order by score desc limit 10,5;– alter table tb_record change column stu_id sid int unsigned not null;
 – alter table tb_record change column cou_id cid int unsigned not null;– 查询选课学生的姓名和平均成绩(子查询和连接查询)
 select stu_name, avg_score
 from
 tb_student t1,
 (
 select stu_id, round(avg(score),1) as avg_score
 from tb_record group by stu_id
 ) t2
 where t1.stu_id=t2.stu_id;– 查询学生的姓名和选课的数量
 select stu_name, total
 from
 tb_student t1,
 (
 select stu_id, count(*) as total from tb_record
 group by stu_id
 ) t2
 where t1.stu_id=t2.stu_id;– 查询每个学生的姓名和选课数量(左外连接和子查询)
 – 内连接：查询左右两表满足连接条件的数据。
 – 外连接
 – 左外连接：确保左表（现在join前面的表）中的所有记录都能查出来，不满足连接条件的补充null。
 – 右外连接：确保右表（现在join后面的表）中的所有记录都能查出来，不满足连接条件的补充null。
 – 全外连接：确保左表和右表中的所有记录都能查出来，不满足连接条件的补充null。– 左外连接
 select stu_name, ifnull(total, 0) as total
 from tb_student t1 left outer join (
 select stu_id, count(*) as total from tb_record
 group by stu_id
 ) t2 on t1.stu_id=t2.stu_id;– 删除tb_record表的外键约束
 alter table tb_record drop foreign key fk_record_stu_id;
 alter table tb_record drop foreign key fk_record_cou_id;– 给tb_record表加两条记录，学号5566在学生表没有对应的记录
 insert into tb_record
 values
 (default, 5566, 1111, ‘2019-09-02’, 80),
 (default, 5566, 2222, ‘2019-09-02’, 70);– 右外连接
 select t1.stu_id, stu_name, t2.stu_id, total as total
 from tb_student t1 right outer join (
 select stu_id, count(*) as total from tb_record
 group by stu_id
 ) t2 on t1.stu_id=t2.stu_id;– MySQL不支持全外连接
 – 可以通过左外连接与右外连接求并集运算得到全外连接的结果
 select t1.stu_id, stu_name, t2.stu_id, total as total
 from tb_student t1 left outer join (
 select stu_id, count() as total from tb_record
 group by stu_id
 ) t2 on t1.stu_id=t2.stu_id
 union
 select t1.stu_id, stu_name, t2.stu_id, total as total
 from tb_student t1 right outer join (
 select stu_id, count() as total from tb_record
 group by stu_id
 ) t2 on t1.stu_id=t2.stu_id;