ToB企服应用市场:ToB评测及商务社交产业平台

标题: 2022-8-17 mysql 第三天 [打印本页]

作者: 曹旭辉 时间: 2022-9-16 17:20
标题: 2022-8-17 mysql 第三天
DQL查询语言

子查询

按照结果集的行列数不同，子查询可以分为以下几类：

标量子查询：结果集只有一行一列（单行子查询）
列子查询：结果集有一列多行
行子查询：结果集有一行多列
表子查询：结果集多行多列

-- 查询比小虎年龄大的所有学生
-- 标量子查询
SELECT
*
FROM
student
WHERE
age > ( SELECT age FROM student WHERE NAME = '小虎' );

复制代码

-- 查询有一门学科分数大于90分的学生信息
-- 列子查询
SELECT
*
FROM
student
WHERE
id IN (
SELECT
s_id
FROM
scores
WHERE
score > 90);

复制代码

-- 查询男生且年龄最大的学生
-- 行子查询
SELECT
*
FROM
student
WHERE
age = (
SELECT
max( age )
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
-- 优化
SELECT
*
FROM
student
WHERE
( age, gender ) = (
SELECT
max( age ),
gender
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)

复制代码

总结：

where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。

where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。

-- 取排名数学成绩前五的学生，正序排列
SELECT
*
FROM
(
SELECT
s.*,
sc.score score,
c.NAME 科目
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
c.NAME = '数学'
ORDER BY
score DESC
LIMIT 5
) t
WHERE
t.gender = '男';

复制代码

经验分享：

分析需求

拆步骤

分步写sql

整合拼装sql

-- 查询每个老师的代课数
SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量
FROM
teacher t;
----------------------------------------------------------------------------
SELECT
t.id,
t.NAME,
count(*) '代课的数量'
FROM
teacher t
LEFT JOIN course c ON c.t_id = t.id
GROUP BY
t.id,
t.NAME;

复制代码

-- exists
SELECT
*
FROM
teacher t
WHERE
EXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );
----------------------------------------------------------------------------SELECT
t.*,
c.`name`
FROM
teacher t
INNER JOIN course c ON t.id = c.t_id;

复制代码

总结：如果一个需求可以不用子查询，尽量不使用。
sql可读性太低。

需求

-- 3.查询每个同学的最高成绩和科目名称****
SELECT
t.id,
t.NAME,
c.id,
c.NAME,
r.score
FROM
(
SELECT
s.id,
s.NAME,(
SELECT
max( score )
FROM
scores r
WHERE
r.s_id = s.id
) score
FROM
student s
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;
-- 5.查询每个课程的最高分的学生信息*****
SELECT
*
FROM
student s
WHERE
id IN (
SELECT DISTINCT
r.s_id
FROM
(
SELECT
c.id,
c.NAME,
max( score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
c.id,
c.NAME
) t
LEFT JOIN scores r ON r.c_id = t.id
AND t.score = r.score
)

复制代码

-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
s.id,
s.NAME sname,
sc.score,
c.NAME
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON sc.c_id = c.id
WHERE
s.NAME LIKE '%张%'
OR s.NAME LIKE '%李%';
-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
*
FROM
student
WHERE
id IN (
SELECT
sc.s_id
FROM
scores sc
GROUP BY
sc.s_id
HAVING
avg( sc.score ) >= 70
)
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
s.id,
s.NAME,
sum( sc.score ) score
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
GROUP BY
s.id,
s.NAME
ORDER BY
score DESC,
s.id ASC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
c.NAME,
max( sc.score ),
min( sc.score ),
avg( sc.score )
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
WHERE
c.NAME = '数学';
-- 10.将各科目按照平均分排序。
SELECT
c.id,
c.NAME,
avg( sc.score ) score
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
c.id,
c.NAME
ORDER BY
score DESC;

复制代码

-- 11.查询老师的信息和他所带的科目的平均分
SELECT
t.id,
t.NAME,
c.id cid,
c.NAME cname,
avg( r.score )
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
t.id,
t.NAME,
c.id,
c.NAME;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT
t.id,
t.NAME,
c.id cid,
c.NAME cname,
max( r.score ),
min( r.score )
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
LEFT JOIN scores r ON r.c_id = c.id
GROUP BY
t.id,
t.NAME,
c.id,
c.NAME
HAVING
t.NAME IN ( 'Tom', 'Jerry' );
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT
t.id,
t.sname,
r.c_id,
c.NAME,
t.score
FROM
(
SELECT
s.id,
s.NAME sname,
max( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
GROUP BY
s.id,
s.NAME
) t
LEFT JOIN scores r ON r.s_id = t.id
AND r.score = t.score
LEFT JOIN course c ON r.c_id = c.id;
-- 14.查询所有学生的课程及分数
SELECT
s.id,
s.NAME,
c.id,
c.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON c.id = r.c_id;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT
*
FROM
student s
WHERE
s.id IN (
SELECT
r.s_id
FROM
scores r
WHERE
r.c_id = 1
AND r.score > 60)
--------------------------------------------------------
SELECT
s.*,
r.*
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
WHERE
r.c_id = 1
AND r.score > 60

复制代码

-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
SELECT
s.id,
s.NAME,
t.score
FROM
student s
LEFT JOIN ( SELECT r.s_id, avg( r.score ) score FROM scores r GROUP BY r.s_id ) t ON s.id = t.s_id
WHERE
t.score >= 70;
-- 17.查询有不及格课程的学生信息
SELECT
*
FROM
student s
WHERE
id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING min( r.score ) < 60 );
-- 18.查询每门课程有成绩的学生人数
SELECT
c.id,
c.NAME,
t.number
FROM
course c
LEFT JOIN ( SELECT r.c_id, count(*) number FROM scores r GROUP BY r.c_id ) t ON c.id = t.c_id;
----------------------------------------------------
SELECT
c.id,
c.NAME,
count(*)
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT
c.id,
c.NAME,
avg( score ) score
FROM
course c
LEFT JOIN scores r ON c.id = r.c_id
GROUP BY
c.id,
c.NAME
ORDER BY
score DESC,
c.id ASC;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s.id,
s.NAME sname,
avg( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
GROUP BY
s.id,
s.NAME
HAVING
score > 65;

复制代码

-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT
*
FROM
student
WHERE
id IN ( SELECT r.s_id FROM scores r WHERE r.score > 80 GROUP BY r.s_id HAVING COUNT(*) = 1 );
----------------------------------------------------------------------------
SELECT
s.id,
s.NAME,
s.gender
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
WHERE
r.score > 80
GROUP BY
s.id,
s.NAME,
s.gender
HAVING
count(*) = 1
-- 22.查询出只有三门课程的学生的学号和姓名
SELECT
*
FROM
student s
WHERE
id IN ( SELECT r.s_id FROM scores r GROUP BY r.s_id HAVING count(*) = 3 );
----------------------------------------------------------------------------
SELECT
s.id,
s.NAME,
s.gender
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME,
s.gender
HAVING
count(*) = 3
-- 23.查询有不及格课程的课程信息
SELECT
*
FROM
course c
WHERE
id IN (
SELECT
r.c_id
FROM
scores r
GROUP BY
r.c_id
HAVING
min( r.score ) < 60
)
----------------------------------------------------------------------------
SELECT
c.id,
c.NAME
FROM
course c
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
sc.c_id,
c.NAME
HAVING
min( sc.score ) < 60;
-- 24.查询至少选择4门课程的学生信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) >= 4
)
----------------------------------------------------------------------------
SELECT
s.id,
s.NAME
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
count(*) >= 4;
-- 25.查询没有选全所有课程的同学的信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) != 5
)

复制代码

-- 26.查询选全所有课程的同学的信息
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
HAVING
number = ( SELECT count(*) FROM course );
-- 27.查询各学生都选了多少门课
SELECT
s.id,
s.NAME,
count(*) number
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
s.NAME
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT
s.id,
s.NAME,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON r.c_id = c.id
WHERE
c.NAME = 'java'
AND r.score < 60;
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT
s.id,
s.NAME
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT
*
FROM
student
WHERE
id NOT IN (
SELECT DISTINCT
s.id
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON c.id = r.c_id
LEFT JOIN teacher t ON t.id = c.t_id
WHERE
t.NAME = 'Tom'
)

复制代码

日期格式

格式描述%a缩写的星期名%b缩写月名%c月，数值%D带有英文前缀的月中的天%d月的天，数值（00-31）%e月的天，数值（0-31）%f微秒%H小时（00-23）%h小时（01-12）%I小时（01-12）%i分钟，数值（00-59）%j年的天（001-366）%k小时（0-23）%l小时（1-12）%M月名%m月，数值（00-12）%pAM或PM%r时间，12-小时（hh:mm:ss AM或PM）%S秒（00-59）%s秒（0-59）%T时间，24-小时（hh:mm:ss）%U周（00-53）星期日是一周的第一天%u周（00-53）星期一是一周的第一天%W星期名%Y年，2022%y年，22MySQL常用函数

聚合函数

count：计数。count(*)≈count(1)>count(主键)
- count(*)：MySQL对count(*)底层优化，count(0)。
- count(1)
- count(主键)
- count(字段)
min：最小值
max：最大值
sum：求和
avg：平均值

数值型函数

主要是对数值型进行处理。

ceiling(x)：向上取整
floor(x)：向下取整
round(x)：四舍五入
truncate(x,y)：返回数字x截断为y位小数的结果
PI：圆周率，π
rand：返回0到1的随机数
abs：绝对值

-- 绝对值
select ABS(-4) 4的绝对值,ABS(-1.1);
-- 向下取整，向上取整，四舍五入
select CEILING(4.1),FLOOR(1.1),ROUND(-4.4)
-- 取余
select MOD(60,11);
-- 随机数
select RAND(),RAND(),RAND()
-- 截断
select TRUNCATE(2.33999999,2);

复制代码

字符串型函数

对字符串进行处理。

length(s)：字符串的长度
concat(s1,s2,.....sn)：合并字符串
lower(str)：将字母转成小写
upper(str)：将字母转成大写
left(str,x)：返回字符串str的左边的x个字符
right(str,x)：返回字符串str右边的x个字符
trim：去掉左右两边的空格
replace：替换
substring：截取
reverse：反转

select LEFT('abcdefg',2);
select RIGHT('abcdefg',2);
select REVERSE('hijklmn');
select REPLACE('abcdefg','abc','x');

复制代码

日期和时间函数

date,time,datetime,timestamp,year。
获取时间和日期

【curdate】和【current_date】，返回当前的系统日期。
【curtime】和【current_time】，返回当前的系统时间。
【now】和【sysdate】，返回当前的系统时间和日期。

select CURRENT_DATE();
select CURTIME();
select now();

复制代码

时间戳和日期转换函数

【UNIX_TIMESTAMP】获取unix时间戳函数
【FROM_UNIXTIME】将时间戳转换为时间格式

select UNIX_TIMESTAMP();
select FROM_UNIXTIME(1660785720);

复制代码

根据日期获取年月日的数值

select MONTH(SYSDATE());
select MONTHNAME(SYSDATE());
select DAYNAME(SYSDATE());
select DAYOFWEEK(SYSDATE());
select WEEK(SYSDATE());
select DAYOFMONTH(SYSDATE());
select YEAR(SYSDATE());

复制代码

时间日期的计算

-- 日期加法
select DATE_ADD(SYSDATE(),INTERVAL 70 DAY);
-- 日期减法
select DATE_SUB(SYSDATE(),INTERVAL 10 DAY);
-- 时间间隔
select DATEDIFF('2023-01-01',SYSDATE());
-- 日期格式化
select DATE_FORMAT(SYSDATE(),'%W %M %D %Y');

复制代码

加密函数

-- 把传入的参数的字符串按照md5算法进行加密，得到一个32位的16进制的字符串
select MD5('123456');

复制代码

md5算法是不可逆的。

流程控制函数

可以进行条件判断，用来实现SQL语句的逻辑。

if(test,t,f)：如果test是真，则返回t，否则返回f
ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2
nullif(arg1,arg2)：如果arg1=arg2返回null，否则返回arg1

select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);

复制代码

对一系列的值进行判断：

-- 输出学生的各科的成绩，以及评级，60以下D，60-70是C，71-80是B，80以上是A
SELECT
*,
CASE
WHEN score < 60 THEN 'D' WHEN score >= 60
AND score < 70 THEN 'C' WHEN score >= 70
AND score < 80 THEN 'B' WHEN score >= 80 THEN
'A'
END AS '评级'
FROM
mystudent;

复制代码

-- 行转列
SELECT
user_name,
max( CASE course WHEN '数学' THEN score ELSE 0 END ) '数学',
max( CASE course WHEN '语文' THEN score ELSE 0 END ) '语文',
max( CASE course WHEN '英语' THEN score ELSE 0 END ) '英语'
FROM
mystudent
GROUP BY
user_name

复制代码

数据库设计

三范式

第一范式：要求有主键，并且要求每一个字段的原子性不能再分。
第二范式：要求所有的非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖。

第一范式

不符合第一范式表结构：
idname联系方式1001aaa[aaa@163.com , 13314569878](mailto:aaa@163.com , 13314569878)1002bbb[bbb@163.com , 13245678945](mailto:bbb@163.com , 13245678945)1003ccc[ccc@163.com , 15000456987](mailto:ccc@163.com , 15000456987)符合第一范式的表结构：
idname邮箱手机号1001aaaaaa@163.com123213213211002bbbbbb@163.com321326546541003cccccc@163.com45654654654必须有主键，这是数据库设计的基本要求，一般情况下我们采用数值型或定长字符串，列不能再分，比如：联系方式。
关于第一范式，保证每一行的数据是唯一，每个表必须有主键。
第二范式

建立在第一范式的基础上，要求所有非主键字段完全依赖于主键，不能产生部分依赖。
学号性别姓名课程编号课程名称教室成绩1001男a2001java301891002女b2002mysql302901003男c2003html303911004男d2004python304521005女e2005c++305671006男f2006c#30684解决方案：
学生表：学号是主键
学号性别姓名1001男a1002女b1003男c1004男d1005女e1006男f课程表：课程编号是主键
课程编号课程名称教室2001java3012002mysql3022003html3032004python3042005c++3052006c#306成绩表：学号和课程编号为联合主键
学号课程编号成绩100120018910022002901003200391100420045210052005671006200684第三范式

建立在第二范式基础上，非主键字段不能传递依赖于主键字段。
不满足第三范式：
学号姓名课程编号课程名称1001a2001java1002b2002mysql1003c2003html1004d2004python1005e2005c++1006f2006c#解决方案：
学生表：学号是主键
学号姓名课程编号1001a20011002b20021003c20031004d20041005e20051006f2006课程表：课程编号是主键
课程编号课程名称2001java2002mysql2003html2004python2005c++2006c#常见的表关系

一对一

学生信息表分为基本信息表和信息信息表。

分为两张表，共享主键。
分两张表，用外键连接。

一对多

两张表，外键在多的一方。

分两张表存储，在多的一方加外键
这个外键字段引用是一的一方的主键

多对多

分三张表存储，在学生表存储学生信息，在课程表存储课程信息。
在成绩表中存储学生和课程的对应关系。

索引，视图，存储过程，触发器，函数

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)