04 mysql 基础三 (进阶)
阶段一 mysql 单表查询
1.查询所有记录
select * from department; select * from student; select * from student_detail;
2.查询选中列记录
select s_name from student;
3.查询指定条件下的记录
select s_name from student where s_id>2;
4.查询后为列取别名
select s_name as 姓名 from student;
5.模糊查询
select * from student where s_name like '赵%' # %代表多个字符 select * from student where s_name like '_春_' # _代表一个字符
6.排序 order by : asc升序(默认) desc降序
select * from student order by dept_id; # 升序 select * from student order by dept_id desc; # 降序
7.限制显示数据的数量LIMIT
# 按学号升序输出前2条数据 select * from student order by s_id limit 2; # 按学号升序输出 第3条数据后面的2条数据 select * from student order by s_id limit 3,2;
8.常用聚合函数
#求最大年龄 select max(age) from stu_detail; #求最小年龄 select min(age) from stu_detail; #求和 select sum(age) from stu_detail; #求平均数 select avg(age) from stu_detail; #四舍五入 select round(avg(age)) from stu_detail; #统计 select count(age) from stu_detail;
9.分组查询 group by
# 对学生表中学院栏进行分组，并统计每个学院各有多少学生 select dept_id 学院,count(dept_id) 学生个数 from student group by dept_id; # having 分组条件 # having 后的字段必须是select 后出现过的 # 查看哪些学院，只有一个学生 select dept_id 学院,count(dept_id) 学生个数 from student group by dept_id -> having count(dept_id)=1;
阶段二 musql 子查询
出现在其他SQL语句内的SELECT字句。（select 中 嵌套 select ）
# 查出潭州学院中 '软件学院'和'外语学院' 的 id select tz_id from tanzhou where tz_name='软件学院' or tz_name='外语学院'; +-------+ | tz_id | +-------+ | 1 | | 3 | +-------+
# 查出学生表中属于'软件学院' 和 '外语学院' 的学员 select * from student where dept_id in( select tz_id from tanzhou where tz_name='软件学院' or tz_name='外语学院'); +------+--------------+---------+ | s_id | s_name | dept_id | +------+--------------+---------+ | 1 | 张三 | 3 | | 3 | 王六 | 1 | | 6 | 隔壁老王 | 3 | +------+--------------+---------+
阶段三 mysql 关联查询
1. 内连接 [inner | cross] join
-
无条件内连接: 又名交叉连接/笛卡尔连接
第一张表种的每一项会和另一张表的每一项依次组合
select * from student inner join tanzhou;
-
有条件内连接：
在无条件的内连接基础上，加上一个ON子句
当连接的时候，筛选出那些有实际意义的记录行来进行拼接
select * from student inner join tanzhou on dept_id=tz_id;
2. 外连接 { lifet | right} join
-
左外连接： （以左表为基准）
两张表做连接的时候，在连接条件不匹配的时候
留下左表中的数据，而右表中的数据以NULL填充
select * from tanzhou left join student on tz_id=dept_id; +-------+--------------+------+--------------+---------+ | tz_id | tz_name | s_id | s_name | dept_id | +-------+--------------+------+--------------+---------+ | 3 | 外语学院 | 1 | 张三 | 3 | | 2 | 艺术学院 | 2 | 李四 | 2 | | 1 | 软件学院 | 3 | 王六 | 1 | | 4 | 语言学院 | 4 | 陈七 | 4 | | 2 | 艺术学院 | 5 | 郭伟涛 | 2 | | 3 | 外语学院 | 6 | 隔壁老王 | 3 | | 5 | 电竞学院 | NULL | NULL | NULL | +-------+--------------+------+--------------+---------+
-
右外连接 right join
右外连接： （以右表为基准）
对两张表做连接的时候，在连接条件不匹配的时候
留下右表中的数据，而左表中的数据以NULL填充
select * from student right join tanzhou on tz_id=dept_id; +------+--------------+---------+-------+--------------+ | s_id | s_name | dept_id | tz_id | tz_name | +------+--------------+---------+-------+--------------+ | 1 | 张三 | 3 | 3 | 外语学院 | | 2 | 李四 | 2 | 2 | 艺术学院 | | 3 | 王六 | 1 | 1 | 软件学院 | | 4 | 陈七 | 4 | 4 | 语言学院 | | 5 | 郭伟涛 | 2 | 2 | 艺术学院 | | 6 | 隔壁老王 | 3 | 3 | 外语学院 | | NULL | NULL | NULL | 5 | 电竞学院 | +------+--------------+---------+-------+--------------+
-
查询没有学员的学院(电竞学员)
select * from student right join tanzhou on tz_id=dept_id where s_id is null; +------+--------+---------+-------+--------------+ | s_id | s_name | dept_id | tz_id | tz_name | +------+--------+---------+-------+--------------+ | NULL | NULL | NULL | 5 | 电竞学院 | +------+--------+---------+-------+--------------+
-
外连接多张表
mysql> select s_id,s_name,dept_id,tz_name,deptc_id,c_name from student left join tanzhou on dept_id=tz_id left join course on tz_id=deptc_id; +------+--------------+---------+--------------+----------+--------+ | s_id | s_name | dept_id | tz_name | deptc_id | c_name | +------+--------------+---------+--------------+----------+--------+ | 3 | 王六 | 1 | 软件学院 | 1 | python | | 2 | 李四 | 2 | 艺术学院 | 2 | java | | 5 | 郭伟涛 | 2 | 艺术学院 | 2 | java | | 1 | 张三 | 3 | 外语学院 | 3 | c/c++ | | 6 | 隔壁老王 | 3 | 外语学院 | 3 | c/c++ | | 4 | 陈七 | 4 | 语言学院 | 4 | 外语 | +------+--------------+---------+--------------+----------+--------+
