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	[[email protected] , 13314569878](mailto:[email protected] , 13314569878)
1002	bbb	[[email protected] , 13245678945](mailto:[email protected] , 13245678945)
1003	ccc	[[email protected] , 15000456987](mailto:[email protected] , 15000456987)

符合第一范式的表結構：

id	name	郵箱	手機號
1001	aaa	[email protected]	12321321321
1002	bbb	[email protected]	32132654654
1003	ccc	[email protected]	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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/502267.html

標籤：MySQL

上一篇：在windows上用docker desktop安裝StoneDB

下一篇：在windows上用docker desktop安裝StoneDB

2022-8-17 mysql 第三天