mysql 学习笔记
完整笔记包括案例所需脚本 以及几道练习题,感兴趣的朋友可以下载:完整笔记
排序查询
语法:
select
查询列表
from
表名
[ 筛选条件 ]
order by 排序列表 [asc | desc]
例:
SELECT sname,age FROM stu ORDER BY age DESC; # 按年龄进行降序排序
SELECT sname,age FROM stu ORDER BY age ASC; # 按年龄进行升序排序,默认为升序,所以ASC可以省略
控制函数
if语句
select if(控制语句,true时的返回,false时的返回);
SELECT sname,IF(age>=18,'已成年','未成年') FROM stu; #类似于Java中 if else语句
case函数的使用,方式一:
类似于 Java中的 switch case 语句
语法:
case 要判断的字段或表达式
when 常量1 then 要显示的值或语句1
ween 常量2 then 要显示的值或语句2
…
else 要显示的值或语句n
end
- 案例:查询学生的学费,要求
北京的学生学费减少500元
湖北的学生学费增加300元
山西的学生学费减少400元
其他地区不变
SELECT tuition 原始学费,province,
CASE province
WHEN '北京' THEN tuition - 500
WHEN '湖北' THEN tuition + 300
WHEN '山西' THEN tuition - 400
ELSE tuition
END AS 新学费
FROM stu;
case函数的使用,方式二:
类似于 Java中的 多重if 语句
语法:
case
when 条件1 then 要显示的值或语句1
when 条件2 then 要显示的值或语句2
…
else 要显示的值或语句n
end
- 案例:查询学生交的学费情况
如果学费大于等于4500 则为vip课程
如果学费大于等于3500 则为高级课程
如果学费学费大于等于2000则为扩展课程
否则为基础课程
SELECT tuition,
CASE
WHEN tuition>=4500 THEN 'vip课程'
WHEN tuition>=3500 THEN '高级课程'
WHEN tuition>=2000 THEN '扩展课程'
ELSE '基础课程'
END AS '课程级别'
FROM stu;
分组函数
分组函数又称为聚合函数或统计函数
分类:
sum求和 avg平均值 max最大值 min最小值 count计算个数
例:
SELECT
SUM(tuition) 总和,AVG(tuition) 平均,MAX(tuition) 最高,MIN(tuition) 最低,COUNT(*) 总数
FROM
stu;
分组查询
语法:
select 分组函数,列 (要求出现在group by的后面)
from 表名
筛选条件
group by 子句
- 案例:统计每个城市的女学生数量
SELECT COUNT(*) 女生总数,province
FROM stu
WHERE gander = '女'
GROUP BY province;
- 案例2:查询女学生数量大于5的城市
注:分组前原表内是没有学生数量这个字段的,条件无法在where中直接给出条件
若要使用分组后追加筛选条件则需要使用having 加上条件
SELECT COUNT(*) 女生总数,province
FROM stu
WHERE gander = '女'
GROUP BY province
HAVING COUNT(*)>5; # 我们可以使用别名,如:having 女生总数>5
连接查询
连接查询又称多表查询,当查询的字段来自多表时就需要使用连接查询
笛卡尔积:表1有n行,表2有m行,结果为n*m行
SELECT e.`deptno`,d.`dname` FROM emp e,dept d; # 没有给出连接条件则会造成笛卡尔积现象
内连接 inner join ( inner 可以省略)
等值连接
非等值连接
自连接
等值连接
- 案例: 查询员工对应的部门名称
SELECT e.`ename`,d.`dname` FROM emp e JOIN dept d ON e.`deptno`= d.`deptno`; # 连接条件为等值条件
非等值连接
- 案例: 查询员工的工资等级
SELECT e.`ename`,e.`sal`,s.`grade` FROM emp e JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`; # 连接条件非等值
自连接
- 案例: 查询每名员工对应的上司
SELECT e1.`empno`,e1.`ename` AS 员工,e2.`empno`, e2.`ename` AS 上司 FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno;
外连接
1.外连接查询结果为主表中的所有记录与从表进行匹配,从表中有匹配的则显示匹配的值,没有匹配的则显示为null
左外连接 left outer join 左边为主表 ( outer可以省略 )
右外连接 right outer join 右边为主表 ( outer可以省略 )
- 案例:查询员工对应的部门及地址
SELECT d.`dname`,d.`loc`,e.`ename` FROM dept d LEFT JOIN emp e ON e.`deptno`=d.`deptno`;
交叉连接
SELECT * FROM dept d CROSS JOIN emp e # 交叉连接与我们上面的笛卡尔积是一样的
子查询
出现在其他语句中的select语句被称为子查询或内查询
子查询可以出现在:
select 后面
from 后面
where 或 having 后面
exista 后面
- 案例:查询工资大于员工’殷天正’ 的同部门员工
SELECT e.`ename`,e.`sal`
FROM emp e JOIN (SELECT sal,deptno FROM emp WHERE ename = '殷天正') t1
ON e.deptno=t1.deptno AND e.sal>t1.sal;
exists (完整查询语句) 结果 0 或 1
SELECT EXISTS(SELECT * FROM emp WHERE deptno = 20);
分页查询
语法:
exista select 查询列表
exista from 表
exista 查询条件…
exista limit 起始索引(从0开始),显示条目数
- 案例:显示部门编号为30的前四条数据
SELECT * FROM emp WHERE deptno = 30 LIMIT 0,4;
联合查询
union: 将多条查询语句结果合并成一个结果
注:
1.多条查询语句查询的列数必须一致否则报错
2.多条查询语句所查询每列的类型和顺序最好一致,否则不易理解
3.联合查询默认会进行去重,如果不希望去重可以在union 后面加入ALL (UNION ALL)
- 案例:查询工资小于10000的员工姓名以及年龄小于21的学生姓名
SELECT empno id,ename 姓名 FROM emp WHERE sal < 10000
UNION
SELECT sid,sname FROM stu WHERE age < 21;