1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
| DCL :
-- 创建用户并指定登录口令
create user hellokitty identified by '123123';
-- 授予权限和召回权限
grant all on shcool.tb_student to hellokitty;
revoke all on shcool.tb_student from hellokitty;
grant select all on shcool.tb_student to hellokitty;
grant all on school.* to hellokitty;
grant all on *.* to 'hellokitty'@'%';
revoke all on *.* from hellokitty;
-- 删除用户
drop user hellokitty;
---------------------------------------------------
查询(重点)
查学生表所有行所有列
select * from tb_student
查课程所有表所有列
select * from tb_course
-- 笛卡尔积
select * from tb_student, tb_course;
-- 投影和别名: 查询所有课程名称及学分
select sname as 姓名, ssex as 性别 from tb_student;
select sname as 姓名, case ssex when 1 then '男' else '女' end as 性别 from tb_student;
select sname as 姓名, if(ssex, '男', '女') as 性别 from tb_student;
-- 筛选: 查询所有女学生的姓名和出生日期
-- = / <> / > / < / >= / <= / is null / is not null
select sname, sbirth from tb_student where ssex=0;
select courseid, cname from tb_course where ccredit>2;
-- 范围筛选: 查询所有80后学生的姓名、性别和出生日期
select sanme, ssex, sbirth from tb_student
where '1980-1-1'<=sbirth and sbirth<='1989-12-31';
select sanme, ssex, sbirth from tb_student
where sbirth between '1980-1-1' and '1989-12-31';
-- 模糊查询: 查询姓王的学生姓名和性别
select * from tb_student where sanme='杨过';
select * from tb_student where sanme like'杨%';
通配符(widcard)
% (通配符)代表0个或多个任意字符
-- 模糊查询: 查询姓杨名字总共两个字的学生的姓名
select * from tb_student where sanme like'杨_';
-- 模糊查询: 查询姓杨名字总共三个字的学生的姓名
select * from tb_student where sanme like '杨__';
-- 模糊查询: 查询名字中有杨字的学生的姓名(模糊)
select * from tb_student where sanme like '%杨%';
-- 多条件和空值处理: 查询没有录入生日和家庭住址的学生姓名
-- 在判断一个列是否为null的时候不能用=或<>而要使用is或者is not
select * from tb_student where sbirth is null or saddr is null;
-- 去重(distint): 查询学生的籍贯
select distinct saddr from tb_student where saddr is not null;
-- 排序: 查询学生的姓名和生日按年龄从大到小排列
select * from tb_student order by ssex asc(升序), sbirth desc(降序);
有删选条件排序时先删选再排序
select * from tb_student order by ssex asc, sbirth desc;
-- 筛选和排序: 查询所有录入了家庭住址的男学生的姓名、出生日期和家庭住址按年龄从小到大排列
select sname, sbirth, saddr from tb_student
where saddr is not null and ssex=1
order by sbirth desc;
-- 聚合函数: 查询年龄最大的学生的出生日期
-- MySQL特有函数: now() / if()
-- 最常用五个聚合函数:
-- max() / min() / sum() / avg()-平均 / count() - 计数
select min(sbirth) from tb_student;
select max(sbirth) from tb_student;
-- 分组查询: 查询男女学生的人数
select count(stuid) from tb_student;
select count(stuid) from tb_student where ssex=1;
-- 经验 : 在使用group by 分组时如果不希望执行默认的排序操作
-- 可以在分组后使用order by null 来避免默认的排序操作提升查询性能
select if(ssex, '男', '女') as 性别, count(*) as 人数
from tb_student group by ssex;
order by - 排序 / group by - 分组查询
asc (升序 - 从小到大) / desc(降序 - 从大到小)
先筛选 - 再分组 - 再排序 (重点) - 否则会导致语法错误
select if(ssex, '男', '女') as 性别, count(*) as 人数
from tb_student
where saddr is not null
group by ssex
order by ssex desc;
-- 聚合函数: 查询课程编号为1111的课程的平均成绩
-- 聚合函数会自动排除空值,不会纳入计算
select cid, avg(score) from tb_sc
where cid=1111;
-- where 子句构造的筛选是分组以前的筛选
-- 如果希望对分组以后的数据进行筛选那么要写having子句而不是where子句
select cid, avg(score) from tb_sc
group by cid having avg(score) < 80;
-- 聚合函数: 查询学号为1001的学生所有课程的平均成绩
select sid, avg(score) from tb_sc
where sid=1001;
-- 子查询: 查询年龄最大的学生的姓名(在查询里嵌套别的查询) -- 重点
select sname, sbirth from tb_student
where sbirth=(select min(sbirth) from tb_student);
去掉更多记录的条件放到右边 - SQL语句 (优化)
-----------------------------------------------------
--- 连接查询 :
注意 : 给表别名不写as / 给列别名写as
要点 : 从里往外写 - 先写子查询 -
-- 自然连接
select sname, cname, score
from tb_sc, tb_student, tb_course
where sid=stuid and cid=courseid;
-- 连接查询: 查询选课学生的姓名和平均成绩
select sname, avgScore from tb_student t1,
(select sid, avg(score) as avgScore from tb_sc
group by sid) t2
where stuid=sid;
-- 连接查询: 查询学生姓名、所选课程名称和成绩
select sname, total from tb_student t1,
(select sid, count(sid) as total from tb_sc
group by sid) t2
where stuid=sid;
如果表里的列有同名.可以加一个前缀来别名加以区分
-- 左外连接 : 左表(写在前面的表)不满足连表条件的记录也要查询出来
select sname, if(total, total, 0) from tb_student t1
left outer join
(select sid, count(sid) as total from tb_sc
group by sid) t2
on stuid=sid
或者 - 两种方法结果是一样的
select sname, ifnull(total, 0) from tb_student t1
left outer join
(select sid, count(sid) as total from tb_sc
group by sid) t2
on stuid=sid
inner join --- 内连接
--- 数据库特定写法 ----
-- 分页查询
select sname, cname, score from tb_sc
inner join tb_student on sid=stuid
inner join tb_course on cid=courseid
以下三种写法:
-- limit 5;
-- limit 0, 5;
limit 5 offset 10;
先筛选 - 分页 - 排序 - 分组
领域特定语言 - 不完备 - 只能做某个领域特定的事情
图灵语言 - 完备 - Python
low_p - 低优先级
重点:
-- 经验: 尽可能不使用distinct去重和in集合运算
-- SQL优化
-- 想去掉distinct和in运算可以使用exists(查询结果是否存在)和not exists操作
相应场景下 去重 / 集合 用下面方法操作:
select ename, job from tbemp t1
where exists(select 'x' from tbemp t2 where t1.empno=t2.mgr);
x 为 任意值
python程序中操作数据库 --- 预习
|
