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) |
%p | AM或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 | 年,22 |
MySQL常用函数
聚合函数
- 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
数据库设计
三范式
- 第一范式:要求有主键,并且要求每一个字段的原子性不能再分。
- 第二范式:要求所有的非主键字段完全依赖主键,不能产生部分依赖
- 第三范式:所有非主键字段和主键字段之间不能产生传递依赖。
第一范式
不符合第一范式表结构:
id | name | 联系方式 |
---|---|---|
1001 | aaa | [aaa@163.com , 13314569878](mailto:aaa@163.com , 13314569878) |
1002 | bbb | [bbb@163.com , 13245678945](mailto:bbb@163.com , 13245678945) |
1003 | ccc | [ccc@163.com , 15000456987](mailto:ccc@163.com , 15000456987) |
符合第一范式的表结构:
id | name | 邮箱 | 手机号 |
---|---|---|---|
1001 | aaa | aaa@163.com | 12321321321 |
1002 | bbb | bbb@163.com | 32132654654 |
1003 | ccc | ccc@163.com | 45654654654 |
必须有主键,这是数据库设计的基本要求,一般情况下我们采用数值型或定长字符串,列不能再分,比如:联系方式。
关于第一范式,保证每一行的数据是唯一,每个表必须有主键。
第二范式
建立在第一范式的基础上,要求所有非主键字段完全依赖于主键,不能产生部分依赖。
学号 | 性别 | 姓名 | 课程编号 | 课程名称 | 教室 | 成绩 |
---|---|---|---|---|---|---|
1001 | 男 | a | 2001 | java | 301 | 89 |
1002 | 女 | b | 2002 | mysql | 302 | 90 |
1003 | 男 | c | 2003 | html | 303 | 91 |
1004 | 男 | d | 2004 | python | 304 | 52 |
1005 | 女 | e | 2005 | c++ | 305 | 67 |
1006 | 男 | f | 2006 | c# | 306 | 84 |
解决方案:
学生表:学号是主键
学号 | 性别 | 姓名 |
---|---|---|
1001 | 男 | a |
1002 | 女 | b |
1003 | 男 | c |
1004 | 男 | d |
1005 | 女 | e |
1006 | 男 | f |
课程表:课程编号是主键
课程编号 | 课程名称 | 教室 |
---|---|---|
2001 | java | 301 |
2002 | mysql | 302 |
2003 | html | 303 |
2004 | python | 304 |
2005 | c++ | 305 |
2006 | c# | 306 |
成绩表:学号和课程编号为联合主键
学号 | 课程编号 | 成绩 |
---|---|---|
1001 | 2001 | 89 |
1002 | 2002 | 90 |
1003 | 2003 | 91 |
1004 | 2004 | 52 |
1005 | 2005 | 67 |
1006 | 2006 | 84 |
第三范式
建立在第二范式基础上,非主键字段不能传递依赖于主键字段。
不满足第三范式:
学号 | 姓名 | 课程编号 | 课程名称 |
---|---|---|---|
1001 | a | 2001 | java |
1002 | b | 2002 | mysql |
1003 | c | 2003 | html |
1004 | d | 2004 | python |
1005 | e | 2005 | c++ |
1006 | f | 2006 | c# |
解决方案:
学生表:学号是主键
学号 | 姓名 | 课程编号 |
---|---|---|
1001 | a | 2001 |
1002 | b | 2002 |
1003 | c | 2003 |
1004 | d | 2004 |
1005 | e | 2005 |
1006 | f | 2006 |
课程表:课程编号是主键
课程编号 | 课程名称 |
---|---|
2001 | java |
2002 | mysql |
2003 | html |
2004 | python |
2005 | c++ |
2006 | c# |
常见的表关系
一对一
学生信息表分为基本信息表和信息信息表。
- 分为两张表,共享主键。
- 分两张表,用外键连接。
一对多
两张表,外键在多的一方。
- 分两张表存储,在多的一方加外键
- 这个外键字段引用是一的一方的主键
多对多
- 分三张表存储,在学生表存储学生信息,在课程表存储课程信息。
- 在成绩表中存储学生和课程的对应关系。
索引
,视图
,存储过程,触发器,函数
本文来自博客园,作者:阿萨德菩提子,转载请注明原文链接:https://www.cnblogs.com/ychptz/p/16600328.html