子查询
根据结果集中的行数,子查询可以分为以下几类:
[En]
According to the number of rows in the result set, subqueries can be divided into the following categories:
- 标量量子查询:结果集只有一行一列(单行子查询)
[En]
scalar quantum query: the result set has only one row and one column (single-row subquery)*
- 列子查询:结果集在一列中有多行
[En]
column subquery: the result set has multiple rows in one column*
- 行子查询:结果集为一行多列
[En]
Row subquery: the result set has one row and multiple columns*
- 表子查询:多行多列的结果集
[En]
Table subquery: result set with multiple rows and columns*
-- 查询比小虎年龄大的所有学生-- 标量子查询SELECT *FROM studentWHERE age > ( SELECT age FROM student WHERE NAME = '小虎' );
-- 查询有一门学科分数大于90分的学生信息-- 列子查询SELECT *FROM studentWHERE id IN ( SELECT s_id FROM scoresWHERE score > 90);
-- 查询男生且年龄最大的学生-- 行子查询SELECT *FROM studentWHERE age = ( SELECT max( age ) FROM student GROUP BY gender HAVING gender = '男' )-- 优化SELECT *FROM studentWHERE ( 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;
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
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
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
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
聚合函数
- count:计数。count(*)≈count(1)>count(主键)
- count():MySQL对count()底层优化,count(0)。
- count(1)
- count(主键)
- count(字段)
- min:最小值
- max:最大值
- sum:求和
- avg:平均值
数值型函数
它主要涉及数值型。
[En]
It mainly deals with the numerical type.
- 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
三范式
- 第一种范式:需要一个主键,并要求每个字段的原子性不能再划分。
[En]
the first paradigm: requires a primary key, and requires that the atomicity of each field can no longer be divided.*
- 第二种范式:要求所有非主键字段完全依赖主键,不能产生部分依赖。
[En]
the second paradigm: all non-primary key fields are required to rely entirely on the primary key and cannot produce partial dependencies.*
- 第三种范式:所有非主键字段和主键字段之间不存在传递依赖。
[En]
the third paradigm: there is no transitive dependency between all non-primary key fields and primary key fields.*
不符合第一范式表结构:
id name 联系方式 1001 aaa [aaa@163.com , 13314569878](
, 13314569878) 1002 bbb [bbb@163.com , 13245678945](
, 13245678945) 1003 ccc [ccc@163.com , 15000456987](
, 15000456987)
符合第一范式的表结构:
id name 邮箱 手机号 1001 aaa aaa@163.com 12321321321 1002 bbb
32132654654 1003 ccc
45654654654
必须有一个主键,这是数据库设计的基本要求。一般来说,我们使用数字或固定长度的字符串,并且列不能再被分割,例如联系信息。
[En]
There must be a primary key, which is the basic requirement of database design. In general, we use numeric or fixed-length strings, and columns can no longer be divided, such as contact information.
关于第一范式,保证每一行的数据是唯一,每个表必须有主键。
基于第一范式,所有非主键字段都被要求完全依赖于主键,并且不能产生部分依赖。
[En]
Based on the first normal form, all non-primary key fields are required to be completely dependent on the primary key and cannot produce partial dependencies.
学号 性别 姓名 课程编号 课程名称 教室 成绩 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
成绩单:学生号和课程号是联合主键
[En]
Transcript: the student number and course number are joint primary keys
学号 课程编号 成绩 1001 2001 89 1002 2002 90 1003 2003 91 1004 2004 52 1005 2005 67 1006 2006 84
基于第二种范例,非主键字段不能在依赖主键字段上传递。
[En]
Based on the second paradigm, non-primary key fields cannot be passed on dependent primary key fields.
不满足第三范式:
学号 姓名 课程编号 课程名称 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#
常见的表关系
学生信息表分为基本信息表和信息表。
[En]
The student information table is divided into basic information table and information table.
- 分为两张表,共享主键。
- 分两张表,用外键连接。
两个表,外键在旁边,外键更多。
[En]
Two tables, the foreign key is on the side with more foreign keys.
- 分两张表存储,多面加外键。
[En]
it is stored in two tables, and foreign keys are added on more than one side.*
此外键字段引用是一方的主键
[En]
this foreign key field reference is the primary key of one party*
-
存储在三个表中,学生信息存储在学员表中,课程信息存储在课程时间表中。
[En]
stored in three tables, student information in the student table and course information in the course schedule.*
- 将学生与课程之间的通信存储在成绩单中。
[En]
store the correspondence between students and courses in the transcript.*
索引
, 视图
,存储过程,触发器,函数
Original: https://www.cnblogs.com/ychptz/p/16600328.html
Author: 阿萨德菩提子
Title: 2022-8-17 mysql 第三天
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/504988/
转载文章受原作者版权保护。转载请注明原作者出处!