喊了八十变要复习SQL,终于有时间了~( ̄. ̄)~
❤
前言:了解数据库与SQL语言
概念区分:
- 数据:描述事物的符号和记录称为数据。
- 数据库(容器):长期储存在计算机内的,有组织的,可共享的数据集合。
- 数据库管理系统DBMS(数据库软件):是位于用户和操作系统之间的数据管理软件。
- 数据库系统:由数据库,数据库管理系统(及其应用开发工具),应用软件,数据库管理员组成的存储,管理,处理,维护数据的系统。
- 注意:我们常使用的就是DBMS即数据库管理系统,用户通过DBMS来操作数据库。常见的DBMS有:MySQL、SQL Server、Oracle、Db2
MySQL: 关系型数据库。一个关系型数据库由一个或数个表格组成。
SQL语言: 结构化查询语言。不区分大小写,可以层删改查数据库、表、视图、存储过程及表中的数据。所有的DBMS都支持SQL语言,差别就是个别可能会在SQL中有一定的扩展,比如SQL Server数据库在SQL语言基础上扩展了T-SQL语言。
SQL基础用法
通过Navicat for MySQL可以很方便的新建表格或数据库。
1. 创建数据库
创建一个名为python_databases的数据库
create DATABASE python_databases charset=utf8;
2. 数据库的查看与使用
//查看数据库的所有内容
show databases;
//使用创建好的python_databases数据库
use python_databases;
3. 表格的查看与新建
查看表:
//查看所有表格
show tables;
新建表:
这个是Navicat里边的DDL
语言,直接在Navicat for MySQL中使用即可。同样,如果通过Navicat创建好了一个表,则可以通过右击表-对象信息-DDL
查看相应的SQL语言。若要在cmd通过mysql>
命令窗口创建数据表,则逐行输入即可。(ENGINE=InnoDB DEFAULT CHARSET=utf8
也可以去掉)
//创建新表table
CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `grade` (
`no` int(11) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
`kemu` varchar(255) DEFAULT NULL,
`score` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
//删除table:
mysql> DROP TABLE table_name;
4. 插入数据
用 INSERT INTO
语句
例如,使用 SQL INSERT INTO 语句向 MySQL 数据表 runoob_tbl 插入数据:
mysql> INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("name", "age", NOW());
5. 查询数据
语法:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
- 以使用一个或者多个表,表之间使用逗号(
,
)分割,并使用WHERE
语句来设定查询条件。 - 你可以使用
WHERE
语句来包含任何条件。 - 可以使用
LIMIT
属性来设定返回的记录数。 - 可以使用星号(*)来代替其他字段,
SELECT
语句会返回表的所有字段数据 - 可以通过
OFFSET
指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
//选择数据作为表格table_name
SELECT [data] AS [table_name]
一、模块一:简单的查询与统计Day1
题目以及对应的知识点:
题号 | 知识点 |
---|---|
1 | 排序:ORDER BY ;默认升序,DESC 实现降序 |
2 | 统计总和用SUM ,分组用GROUP BY |
3 | 先得出新列sum_score,然后联合得出结果 |
4 | MAX() 求最大值 |
5 | COUNT(*) 函数返回匹配指定条件的行数 COUNT函数的具体解释 |
GROUP BY的相关用法:
函数 | 作用 | 支持性 |
---|---|---|
sum(列名) | 求和 | |
max(列名) | 最大值 | |
min(列名) | 最小值 | |
avg(列名) | 平均值 | |
first(列名) | 第一条记录 | 仅Access支持 |
last(列名) | 最后一条记录 | 仅Access支持 |
count(列名) | 统计这一列的记录数,字段为null不统计。 | 注意和count(*)的区别 |
具体题目:
已知:
有两张表,其中学生表(student)基本信息有:
科目和分数表(grade):
1. 查询所有学生的数学成绩,显示学生姓名name,分数score,由高到低
- 分析 :用
ORDER BY
实现排序,DESC
实现降序。
笔记:a和b是为了区分开两个表,不用也可以。升序为ASC
,降序为DESC
,
升序:select * from 表名 order by 表中的字段 asc(mysql中默认是升序排列,可不写)
降序:select * from 表名 order by 表中的字段 desc
若要进行同时一个升序,一个降序,则如下:
order by 升序字段 asc,降序字段 desc。
- 代码:
SELECT a.name, b.score
FROM student a, grade b
WHERE a.id = b.id
AND kemu = '数学'
ORDER BY score
DESC
2. 统计每个学生的总成绩,显示字段:姓名,总成绩
-
分析:统计总成绩用
sum
,分组用GROUP BY
总和sum的计算是以GROUP BY为组进行统计的。 -
代码:
SELECT a.name, sum(b.score) AS sum_score
FROM student a, grade b
WHERE a.id = b.id
GROUP BY `name`
DESC
3. 统计每个学生的总成绩(由于学生可能有重复名字),显示字段:学生id,姓名,总成绩
- 分析:统计总成绩时要用表grade,里边没有name信息,只有id和student是通用的。因此,第一步先根据id将总成绩聚合找出id所对应的总成绩,然后依次输出id,name,sum_score。
- 代码:
SELECT a.id, a.name, c.sum_score
FROM student a,
(SELECT b.id, sum(b.score) as sum_score
FROM grade b
GROUP BY id
) c
WHERE a.id = c.id
ORDER BY sum_score
DESC
4. 列出各门课程成绩最好的学生, 要求显示字段: 学号,姓名,科目,成绩
- 分析:第一步先group by找出单科最好成绩,得到新列
max_score
。再结合学生表和分数表,得到单科最好成绩。
- 代码:
SELECT b.id, a.name, b.kemu, b.score
FROM student a, grade b ,
(SELECT kemu, MAX(score) as max_score
FROM grade
GROUP BY kemu
) c
WHERE b.kemu = c.kemu
AND b.score = c.max_score
AND a.id = b.id
5. 列出各门课程成绩最好的2位学生, 要求显示字段: 学号,姓名,科目,成绩
- 分析:
可能基础部分还是太差,需要补补:查书,查资料。
以下代码实现的功能:从成绩表中找出科目相同但成绩大于t1的那几个行数,count(*)返回行数。为啥小于2,难道是mysql数据库的索引是从0开始的?
怎么理解??
(SELECT count(*) FROM grade t2
WHERE t1.kemu=t2.kemu AND t2.score>t1.score
)<2
- 代码:
SELECT t1.id, a.name, t1.kemu, t1.score
FROM grade t1, student a
WHERE
(SELECT count(*) FROM grade t2
WHERE t1.kemu=t2.kemu AND t2.score>t1.score
)<2
AND a.id = t1.id
ORDER BY t1.kemu, t1.score
DESC
❤
二、模块二:简单的查询与统计Day2
题目以及对应的知识点:
题号 | 知识点 |
---|---|
1 | 求平均值用avg() |
2 | CASE WHEN [条件] THEN [满足条件的值] ELSE [不满足条件的值] END 相当于if([条件]) [满足条件的值] else [不满足条件的值];SUM 求和;AVG 求平均 |
3 | 求平均值用avg() |
4 | @ 表局部变量;’:= '符号为对变量赋值; '= '符号为if等条件判断中使用; |
5 | COUNT() 函数返回匹配指定条件的行数 |
6 |
1. 计算每个人的平均成绩,要求显示字段 :学号,姓名,平均成绩
- 分析:
求平均成绩用avg(),先从表二得到以id为准每个人的平均成绩,记为表avg_score,然后再联合表a进行统计。 - 代码:
SELECT a.id,a.name,c.avg_score
FROM student a,
( SELECT b.id, avg(b.score) as avg_score
FROM grade b
GROUP BY b.id
) c
WHERE a.id = c.id
2. 计算每个人的成绩,总分数,平均分,要求显示:学号,姓名,语文,数学,英语,总分,平均分
- 分析:
使用case when
语法把科目字段分解成具体的科目:语文,数学, 英语;
(CASE WHEN b.kemu='语文' THEN score ELSE 0 END) as 语文
等等条件也是SELECT
的一个选择条件。
然后对这张已经分组好的表进行操作即可。
语数英的成绩直接对该科目下的成绩求和,GROUP BY以id为组进行求和sum或者求最大值max
,比如张三的三门成绩求法如图:
总分直接group by
以id为分组对grade表中的score求和sum即可;
平均分直接group by
以id为分组对grade表中的score求平均值avg即可; - 代码:
方法一:
SELECT a.id as 学号, a.name as 姓名,
sum(case when b.kemu='语文' then score else 0 end) as 语文,
sum(case when b.kemu='数学' then score else 0 end) as 数学,
sum(case when b.kemu='英语' then score else 0 end) as 英语,
sum(b.score) as 总分 ,
avg(b.score) as 平均分
FROM student a, grade b
where a.id = b.id
GROUP BY b.id
方法二:使用了MAX()、LEFT JOIN 、ON方法
SELECT s.id, s.name,
max(case when g.kemu='语文' then score else 0 end) as 语文,
max(case when g.kemu='数学' then score else 0 end) as 数学,
max(case when g.kemu='英语' then score else 0 end) as 英语,
sum(score) as 总分,
avg(score) as 平均分
from student s
LEFT JOIN grade g
ON s.id = g.id
GROUP BY s.id
- 笔记:
JOIN
用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
JOIN: 如果表中有至少一个匹配,则返回行
LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配,就返回行ON
条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
JOIN…ON同时使用:on后面是2个表的join条件
3. 列出各门课程的平均成绩,要求显示字段:课程,平均成绩
- 分析:
直接对grade表格操作即可 - 代码:
SELECT b.kemu, AVG(b.score)
FROM grade b
GROUP BY b.kemu
4. 列出数学成绩的排名, 要求显示字段:学号,姓名,成绩,排名
-
分析:先显示出学号,姓名,成绩,然后那个排名该怎么搞?
在查询结果表里面添加一个变量@paiming,让它自动加1:
OK,事实远比我们所想象的要复杂得多,当两个人的数学成绩一样时,排名应该是相同的,所以以上代码是有BUG
的。即如果两个分数之间存在平局,则两者应具有相同的排名。 请注意,在平局之后,下一个排名数应该是下一个连续的整数值。 换句话说,等级之间不应该存在“漏洞”。 -
代码:
SELECT
c.id, c.name, c.score as 数学分数,
(CASE
WHEN @temp = c.score THEN #当前的值与分数相同,则排名不加1
@paiming
WHEN @temp := c.score THEN #否则排名+1
@paiming :=@paiming + 1
WHEN @temp = 0 THEN
@paiming :=@paiming + 1 #初始值是一定要+1的
END) AS 排名
FROM
(SELECT a.id, a.name, b.score
FROM student a, grade b
WHERE b.kemu = '数学'
AND a.id = b.id
ORDER BY b.score
DESC) AS c,
(SELECT @paiming := 0, @temp := 0) suiyi
- 笔记:
SELECT DISTINCT
语句:关键词 DISTINCT 用于返回唯一不同的值。
语法:SELECT DISTINCT 列名称 FROM 表名称
COUNT
是用于计数- 对表中数据进行排名的例子,可是只能对现有表进行排序:图1所示的成绩进行排名,得到图2,一般的排名代码如下:
select Score,
(select count(distinct Score) from scores where Score >= a.Score) 排名
from scores as a
order by Score
desc
图1:原表
图2:排名之后
三、模块三:查询Day3
题目以及对应的知识点:
题号 | 知识点 |
---|---|
1 | LIMIT n 用于从表中选出n条数据列出 |
2 | LIMIT m,n 查询从m开始的n条记录 |
3 | LIMIT m,n |
5 | COUNT(*) |
6 | select count(*) 表查询出表中符合条件的行数 |
1. 列出数学成绩前3名的学生(要求显示字段:学号,姓名, 科目,成绩)
- 分析:
前三名使用LIMIT 3
- 代码:
SELECT a.id, a.name, b.kemu, b.score
FROM student a, grade b
WHERE b.kemu = '数学'
AND a.id = b.id
ORDER BY b.score
DESC
LIMIT 3
2. 查询数学成绩第2和第3名的学生(要求显示字段:学号,姓名, 科目,成绩)
- 分析:
LIMIT m,n
查询从m开始的n条记录 - 代码:
SELECT a.id, a.name, b.kemu, b.score
FROM student a, grade b
WHERE b.kemu = '数学'
AND a.id = b.id
ORDER BY b.score
DESC
LIMIT 1,2
3. 查询第3名到后面所有的学生数学成绩(要求显示字段:学号,姓名, 科目,成绩)
- 分析:
LIMIT位置写成3, 条数写一个大一点的数,比如100。 - 代码:
SELECT a.id, a.name, b.kemu, b.score
FROM student a, grade b
WHERE b.kemu = '数学'
AND a.id = b.id
ORDER BY b.score
DESC
LIMIT 3,100
4. 统计英语课程少于80分的,显示 学号id, 姓名,科目,分数
- 分析:
原先建的表里边英语成绩都大于80,所以第一次运行时,显示的数据为空,现随意改了两个值66和78,得到以下结果。 - 代码:
SELECT a.id, a.name, b.kemu, b.score
FROM student a, grade b
WHERE b.kemu = '英语'
AND a.id = b.id
AND b.score < 80
5. 统计每门课程不及格(<60)、一般、优秀(>80)的个数
-
分析:
SELECT
时找出符合三个区域的条件。SELECT后边的内容可以理解为要显示的数据的列名,然后行本来是所有的科目名,但被GROUP BY聚合到了一块显示出没门课程的数据值。
COUNT()统计出了每门课程(用GROUP BY
对科目进行聚合)的三种情况个数。 -
代码:
SELECT b.kemu,
(SELECT COUNT(*) FROM grade WHERE score<60 AND kemu = b.kemu) AS 不及格,
(SELECT COUNT(*) FROM grade WHERE score BETWEEN 69 AND 80 AND kemu=b.kemu) AS 一般,
(SELECT COUNT(*) FROM grade WHERE score>80 AND kemu = b.kemu) AS 优秀
FROM grade b
GROUP BY kemu
6. 查找每科成绩前2名,显示id, 姓名,科目,分数
- 分析:
-
先按科目名以及成绩进行总的排名查询,即顺序为同一个科目的排到一起,然后成绩是按各科成绩大小排序
-
再查找每科成绩前两名
(SELECT count(*) FROM grade t2
WHERE t1.kemu=t2.kemu AND t2.score>=t1.score
)<=2
select count(*)
表查询出表中符合条件的行数;
t1.kemu=t2.kemu AND t2.score>=t1.score
表示查询的条件;
总的来说以上代码的意思就是从表grade t2
中查询出满足查询条件(科目相同且t2的成绩要高于t1的成绩)的行数;
这个查询出的行数要<=2,所以"行数<=2"是作为前一个查询语句的查询条件的。
- 代码:
SELECT a.id, a.name, t1.kemu,t1.score
FROM student a, grade t1
WHERE
(SELECT count(*) FROM grade t2
WHERE t1.kemu=t2.kemu AND t2.score>=t1.score
)<=2
and a.id = t1.id
ORDER BY t1.kemu,t1.score
DESC
四、模块五:SQL经典面试题
题目以及对应的知识点:
题号 | 知识点 |
---|---|
1 | HAVING 子句可以让我们筛选分组后的各组数据(在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用); NOT IN 不包含 |
2 | COUNT(*) 可用来统计* 的数据条数 |
3 | DELETE 删除操作 |
4 | NOT IN |
5 | 模糊查询% ; IN 在; LIKE 像 |
6 | 考察通配符操作:通配符可用于替代字符串中的任何其他字符,与LIKE 操作符一起使用; 使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE ) 来操作正则表达式 |
已知表如下:
1. 用一条SQL 语句查询xuesheng表每门课都大于80 分的学生姓名
- 分析:
- 方法一:用
GROUP BY
以名字为分组,将每个人的成绩分组到名字下,如果每个人各门成绩的最小值都>80,则每门课都大于80; - 方法二:逆向思维,若这个学生的各门成绩中<=80的课程不存在,则他每门课成绩都大于80.
- 代码:
SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)>80
或:
SELECT DISTINCT name
FROM xuesheng
WHERE name
NOT IN
(SELECT DISTINCT name
FROM xuesheng
WHERE score <= 80)
2. 用一条SQL 语句查询xuesheng表每门课都大于80 分且课程数大于等于3的情况的学生姓名
SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)>80
AND COUNT(kecheng)>=3
3. 删除除了自动编号不同, 其他都相同的学生冗余信息
已知表xueshengbiao如下:
- 代码:
DELETE t1
FROM xueshengbiao t1, xueshengbiao t2
WHERE t1.id = t2.id
and t1.name = t2.name
and t1.kcid = t2.kcid
and t1.kcname = t2.kcname
and t1.score = t2.score
and t1.autoid < t2.autoid
4. 查询出自动编号不同, 其他都相同的学生冗余信息
- 分许:先找出除了自动编号不同其他都相同的学生的最小的自动编号,然后用
NOT IN
将其去除 - 代码:
SELECT *
FROM xueshengbiao t1
WHERE t1.autoid
NOT IN
(SELECT
MIN(autoid) as autoid
FROM xueshengbiao
GROUP BY id, name, kcid, kcname, score
)
5. 模糊查询%:查询出“张”姓学生中平均成绩大于75分的学生信息
- 代码:
SELECT *
FROM student
WHERE name IN
(SELECT name FROM student
WHERE name LIKE '张%'
GROUP BY name
HAVING avg(score)>75
)
6. SQL 通配符:找出姓张和姓李的学生中平均成绩大于75分的学生信息
- 代码:
select * from xuesheng
where name in
(select name from xuesheng
where name rlike '[张李]' group by name having avg(score) > 75);
或
select * from xuesheng
where name in
(select name from xuesheng
where name REGEXP '^[张李]' group by name having avg(score) > 75);
- 笔记:
- 在 SQL 中,通配符与 SQL LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。在 SQL 中,可使用以下通配符:
通配符 | 描述 |
---|---|
% | 替代 0 个或多个字符 |
_ | 替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist] 或[!charlist] | 不在字符列中的任何单一字符 |
- MySQL 中使用
REGEXP
或NOT REGEXP
运算符 (或RLIKE
和NOT RLIKE
) 来操作正则表达式