初識MySQL之綜合復習篇（干貨）-有解無憂

在日常開發和資料查詢當中，我們需要等值連接或者左右連接多個表進行相關的欄位查詢

當然我們在MySQL語法知識點里面我們需要對它的整個框架要有一個比較熟悉的掌握程度，這樣才能在比較的熟悉的操作我們的資料

建表

# 根據E-R圖創建表
DROP TABLE IF EXISTS wxw_data;
CREATE TABLE wxw_data (
	SN CHAR(20) PRIMARY KEY,
	NAME VARCHAR(50) NOT NULL,
	PRICE DOUBLE,
	EDITOR varchar(50)
);

DROP TABLE IF EXISTS wxw_CANKAO;
CREATE TABLE wxw_CANKAO (
  JIAOCAI_SN CHAR(20),
	COURSE_CODE char(8),
	type varchar(50),
	PRIMARY KEY (JIAOCAI_SN,COURSE_CODE),
	CONSTRAINT fk_jiaocai_sn FOREIGN KEY (JIAOCAI_SN) REFERENCES wxw_data(SN),
	CONSTRAINT fk_course_code FOREIGN KEY (COURSE_CODE) REFERENCES tb_course(CODE)
);

增

USE XSXK;
INSERT INTO wxw_data VALUES ('978-7-115-48910-4','MySQL資料庫技術與應用',42.00,'張素青');
INSERT INTO wxw_data SELECT '978-7-115-48910-5','MySQL資料庫技術與應用',42.00,'張素青';

# 插入自己的資訊
INSERT INTO tb_student SELECT '2019888888','tanguangyu','男','2010-10-10','15902333748',
 ID from tb_major_class where COLLEGE='數理與大資料學院' and grade=2019 and class=1 and
 major='大資料';
SELECT * FROM tb_student where ID='2019888888';

INSERT INTO tb_student SELECT '2018888888','tanguangyu','男','2010-10-10','15902333748',
 ID from tb_major_class where COLLEGE='數理與大資料學院' and grade=2018 and class=1 and 
 major='大資料';
 
SELECT * FROM tb_student where ID='2018888888';

刪

USE XSXK;
DELETE FROM tb_student where ID='2018888888';

洗掉該條件下的一行資料

改

UPDATE tb_student SET GENDER='女' WHERE ID='2019888888';
--定位到ID為這個的行，然后把性別改為女即可，基本的操作及時

update 表名 set 需要改變的欄位='新欄位屬性' where 條件定位

SELECT * FROM tb_student where ID='2019888888';

查

# 查詢每個老師帶了哪些課程班
select CODE 班號, TEACHER_ID 教師編號 from tb_course_class;
# 1， 回傳的欄位有哪些
SELECT t.NAME 教師姓名, cc.CODE 班號
# 2， 要回傳的欄位從哪些表(臨時的表、視圖、表連接、子查詢結果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判斷要回傳的欄位從哪些行取得
WHERE t.ID = cc.TEACHER_ID;


# 查詢每個老師帶了多少課程班
# 1， 回傳的欄位有哪些
SELECT t.NAME 教師姓名, COUNT(cc.CODE) 班級數量 
# 2， 要回傳的欄位從哪些表(臨時的表、視圖、表連接、子查詢結果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判斷要回傳的欄位從哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么條件分組聚合
GROUP BY t.NAME;

# 查詢每個老師帶了多少課程班,按數量多少排序
# 1， 回傳的欄位有哪些
SELECT t.NAME 教師姓名, COUNT(cc.CODE) 班級數量 
# 2， 要回傳的欄位從哪些表(臨時的表、視圖、表連接、子查詢結果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判斷要回傳的欄位從哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么條件分組聚合
GROUP BY t.NAME
# 5， 排序默認正序從小到大
ORDER BY 班級數量 DESC;

# 查詢每個老師帶了多少課程班,按數量多少排序, 篩選大于5個班的老師
# 1， 回傳的欄位有哪些
SELECT t.NAME 教師姓名, COUNT(cc.CODE) 班級數量 
# 2， 要回傳的欄位從哪些表(臨時的表、視圖、表連接、子查詢結果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判斷要回傳的欄位從哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么條件分組聚合
GROUP BY t.NAME
# 5， 對查詢結果集再次篩選
HAVING 班級數量>5;

# 查詢每個老師帶了多少課程班,按數量多少排序, 篩選大于5個班的老師
# 1， 回傳的欄位有哪些
SELECT t.NAME 教師姓名, COUNT(cc.CODE) 班級數量 
# 2， 要回傳的欄位從哪些表(臨時的表、視圖、表連接、子查詢結果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3， 判斷要回傳的欄位從哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4， 按什么條件分組聚合
GROUP BY t.NAME
# 5， 對查詢結果集再次篩選
HAVING 班級數量>5
ORDER BY 班級數量
LIMIT 1;


# 子查詢
# where子句中的子查詢
# 查詢某門課程是那個學院開設的
SELECT DISTINCT COLLEGE
FROM tb_major_class
WHERE ID IN 
	(SELECT MAJOR_CLASS FROM tb_student WHERE ID IN
		(SELECT STUDENT_ID FROM tb_electives WHERE COURSE_CLASS_CODE IN 
			(SELECT CODE FROM tb_course_class WHERE COURSE_CODE IN
				(
				SELECT CODE FROM tb_course WHERE NAME='Web程式設計基礎'
				)
			)
		)
	);

# 表連接（內連接）
# 查詢某門課程是那個學院開設的
SELECT DISTINCT mc.COLLEGE
FROM tb_major_class mc, tb_student s, tb_electives e, tb_course_class cc, tb_course c
WHERE 
	mc.ID = s.MAJOR_CLASS and
	s.ID = e.STUDENT_ID and
	e.COURSE_CLASS_CODE = cc.CODE and
	cc.COURSE_CODE = c.CODE and
	c.NAME like 'Web%';
	
# 查詢某門課程是那個學院開設的
SELECT DISTINCT c.NAME 課程,mc.COLLEGE 學院
FROM tb_major_class mc, tb_student s, tb_electives e, tb_course_class cc, tb_course c
WHERE 
	mc.ID = s.MAJOR_CLASS and
	s.ID = e.STUDENT_ID and
	e.COURSE_CLASS_CODE = cc.CODE and
	cc.COURSE_CODE = c.CODE;

# SELECT 生成一個結果集（二維表）
SELECT 1,2;
SELECT 'abc';
SELECT CURRENT_TIME();

# FROM 掃描一個二維表結構（物體表、臨時表、查詢結果集）
SELECT tb_course.NAME, CODE
FROM tb_course;

# FROM 表連接（新的臨時表）
-- 交叉連接（笛卡爾積） A CROSS JOIN B => 列數=A+B 行數=A*B
SELECT * from tb_course,tb_teacher;
SELECT * FROM tb_course CROSS JOIN tb_teacher;
# 內連接
SELECT * from tb_course as c,tb_course_class as cc WHERE c.CODE=cc.COURSE_CODE;

# 查詢課程班級的課程名稱
# 查課程班級對應的課程編號
SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09';
# 查課程名稱
SELECT * FROM tb_course WHERE CODE = (SELECT COURSE_CODE FROM tb_course_class WHERE CODE='3SL1113A.09');

SELECT a.*,b.NAME FROM 
		(SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
	 , 
		tb_course b
WHERE a.COURSE_CODE=b.CODE;

SELECT a.*,b.NAME FROM 
		(SELECT Teacher_id,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
	 INNER JOIN
		tb_teacher b
   ON a.teacher_id=b.ID;  -- join 條件
	 
	 
SELECT cc.CODE,cc.COURSE_CODE,t.NAME FROM 
    tb_teacher t INNER JOIN tb_course_class cc ON t.ID=cc.teacher_id
WHERE cc.CODE='3SL1113A.09';

SELECT cc.CODE,c.NAME,t.NAME from 
	tb_course c, tb_teacher t, tb_course_Class cc
WHERE c.CODE=cc.COURSE_CODE and t.ID=cc.Teacher_id;

SELECT cc.CODE 班級編號,c.NAME 課程名稱,t.NAME 授課老師 from 
   tb_course_class cc 
	 JOIN tb_course c ON cc.COURSE_CODE=c.CODE 
	 JOIN tb_teacher t ON cc.teacher_id=t.ID
where cc.CODE='3SL1113A.09';
	 
# 外連接 （左連接，右連接）



# WHERE 條件判斷 => 是否SELECT
SELECT 'a' FROM tb_course WHERE 1=1;
# 查詢2學分的課程資訊
SELECT * FROM tb_course WHERE CREDIT=2;
# 查詢不及格的學生成績
SELECT * FROM tb_electives WHERE SCORE < 60;
# LIKE (不是喜歡，是像）  % 通配符，匹配任意0個或多個字符
SELECT * FROM tb_course WHERE NAME like '%程式設計';
# 查詢和‘我’同姓的學生資訊
SELECT * FROM tb_student WHERE NAME like '譚%飛';
# IN  ==> OR OR OR 
SELECT * FROM tb_student WHERE NAME='譚飛' OR NAME='譚華' OR NAME='譚貝';
SELECT * FROM tb_student WHERE NAME IN ('譚飛','譚華','譚貝');

# 子查詢
# 查詢3SL1037A.01班分數最高的學生的ID
# 1.先要知道最高分
SELECT MAX(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.01';
# 2.再知道誰是最高分
SELECT * FROM tb_electives WHERE SCORE=100 AND COURSE_CLASS_CODE='3SL1037A.01';
SELECT * FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.02' ORDER BY SCORE ASC LIMIT 1;

UPDATE tb_electives SET SCORE=100 WHERE COURSE_CLASS_CODE='3SL1037A.01' and SCORE=99;



SELECT * FROM tb_electives 
WHERE 
		SCORE IN (SELECT MIN(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE like '3SL1037A.0%') 
		AND 
		COURSE_CLASS_CODE like '3SL1037A.0%';


# 查詢最低分
SELECT MIN(SCORE) FROM tb_electives ;
# 分班查詢最低分
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;

# 分班查詢最低分的學生
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;

select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE 
from 
	tb_electives t 
		INNER JOIN 
	(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
		ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore ;

		
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 
from 
	tb_electives t 
	INNER JOIN 
	(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
		ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
	LEFT JOIN tb_student s 
		ON t.STUDENT_ID=s.ID;

select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 學生姓名,c.NAME 課程名
from 
	tb_electives t 
	INNER JOIN 
	(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
		ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
	LEFT JOIN tb_student s 
		ON t.STUDENT_ID=s.ID
	LEFT JOIN tb_course c
	 ON instr(t.COURSE_CLASS_CODE,c.CODE);
	
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 學生姓名,c.NAME 課程名,cc.TEACHER_ID
from 
	tb_electives t 
	INNER JOIN 
	(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
		ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
	LEFT JOIN tb_student s 
		ON t.STUDENT_ID=s.ID
	LEFT JOIN tb_course c
	 ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
	 ON t.COURSE_CLASS_CODE=cc.CODE;


select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 學生姓名,c.NAME 課程名,te.NAME 教師姓名,te.TITLE 教師職稱
from 
	tb_electives t 
	INNER JOIN 
	(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
		ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
	LEFT JOIN tb_student s 
		ON t.STUDENT_ID=s.ID
	LEFT JOIN tb_course c
	 ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
	 ON t.COURSE_CLASS_CODE=cc.CODE
  LEFT JOIN tb_teacher te 
	 ON cc.TEACHER_ID=te.ID;
	 
select 
  mc.COLLEGE 學院,
  concat(mc.MAJOR,mc.GRADE,'-',mc.CLASS) as 專業班級,
  t.STUDENT_ID 學生學號,
	s.NAME 學生姓名,
	t.COURSE_CLASS_CODE 課程班編號,
	c.NAME 課程名稱,
	t.SCORE 分數,
	te.NAME 教師姓名,
	te.TITLE 教師職稱
from 
	tb_electives t 
	INNER JOIN 
	(SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
		ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
	LEFT JOIN tb_student s 
		ON t.STUDENT_ID=s.ID
	LEFT JOIN tb_course c
	 ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
	 ON t.COURSE_CLASS_CODE=cc.CODE
  LEFT JOIN tb_teacher te 
	 ON cc.TEACHER_ID=te.ID
  LEFT JOIN tb_major_class mc 
	 ON mc.ID=s.MAJOR_CLASS;
	 
 
# 查詢‘我’所在專業班級的資訊
# 專業班在哪兒找： tb_major_class
# 已知條件：我=》 數理學院，大資料專業，2019級， 3班
SELECT * 
FROM tb_major_class 
WHERE COLLEGE='數理與大資料學院' 
				AND 
			MAJOR='大資料' 
				AND 
			GRADE=2019 
				AND 
			CLASS=3;

# 我的專業班級編號為43
# 統計我這個班有多少人
# 統計數量COUNT()
# 人-學生人數，學生在哪里找： tb_student
SELECT COUNT(*) AS 學生人數, MAJOR_CLASS AS 班級編號
FROM tb_student
WHERE MAJOR_CLASS=43;

# 分組統計每個班的學生人數
SELECT COUNT(*) AS 學生人數, MAJOR_CLASS AS 班級編號
FROM tb_student
GROUP BY MAJOR_CLASS;

# 分組統計每個班的學生人數, 按學生人數從多到少排序
# major class 專業班人數
SELECT COUNT(*) AS 學生人數, MAJOR_CLASS AS 班級編號
FROM tb_student
GROUP BY MAJOR_CLASS
ORDER BY 學生人數 DESC;

# course class 課程班人數
# 課程班學生在哪里找： tb_electives
SELECT COUNT(*) as 學生人數, COURSE_CLASS_CODE as 課程班編號
FROM tb_electives
GROUP BY COURSE_CLASS_CODE;

每文一語

學習不僅是借鑒，還要學會轉移和利用，最后內化為自己的知識點

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

標籤：其他

上一篇：mysql陳述句整理(增刪改查分組起別名...)

下一篇：菜鳥求助如何更效率的reload 資料庫