Python 100 Days Day37 SQL Advanced
19 Sep 2022 | beginner pythonauthor: jackfrued
SQL 和 MySQL 詳解
SQL 詳解
我們通常可以將 SQL 分為四類，分別是 DDL（數據定義語言）、DML（數據操作語言）、DQL（數據查詢語言）和 DCL（數據控制語言）。DDL 主要用於創建、刪除、修改數據庫中的對象，比如創建、刪除和修改二維表，核心的關鍵字包括create、drop和alter；DML 主要負責數據的插入、刪除和更新，關鍵詞包括insert、delete和update；DQL 負責數據查詢，最重要的一個關鍵詞是select；DCL 通常用於授予和召回權限，核心關鍵詞是grant和revoke。
說明：SQL 是不區分大小寫的語言，為了書寫和識別方便，下面的 SQL 都使用了小寫字母來書寫。
DDL（數據定義語言）
下面我們來實現一個選課系統的數據庫，如下所示的 SQL 創建了名為school的數據庫和五張表，分別是學院表（tb_college）、學生表（tb_student）、教師表（tb_teacher）、課程表（tb_course）和選課記錄表（tb_record），其中學生和教師跟學院之間是多對一關系，課程跟老師之間也是多對一關系，學生和課程是多對多關系，選課記錄表就是維持學生跟課程多對多關系的中間表。
-- 如果存在名為school的數據庫就刪除它
drop database if exists `school`;
-- 創建名為school的數據庫並設置默認的字符集和排序方式
create database `school` default character set utf8mb4 collate utf8mb4_general_ci;
-- 切換到school數據庫上下文環境
use `school`;
-- 創建學院表
create table `tb_college`
(
`col_id` int unsigned auto_increment comment '編號',
`col_name` varchar(50) not null comment '名稱',
`col_intro` varchar(500) default '' comment '介紹',
primary key (`col_id`)
) engine=innodb auto_increment=1 comment '學院表';
-- 創建學生表
create table `tb_student`
(
`stu_id` int unsigned not null comment '學號',
`stu_name` varchar(20) not null comment '姓名',
`stu_sex` boolean default 1 not null comment '性別',
`stu_birth` date not null comment '出生日期',
`stu_addr` varchar(255) default '' comment '籍貫',
`col_id` int unsigned not null comment '所屬學院',
primary key (`stu_id`),
constraint `fk_student_col_id` foreign key (`col_id`) references `tb_college` (`col_id`)
) engine=innodb comment '學生表';
-- 創建教師表
create table `tb_teacher`
(
`tea_id` int unsigned not null comment '工號',
`tea_name` varchar(20) not null comment '姓名',
`tea_title` varchar(10) default '助教' comment '職稱',
`col_id` int unsigned not null comment '所屬學院',
primary key (`tea_id`),
constraint `fk_teacher_col_id` foreign key (`col_id`) references `tb_college` (`col_id`)
) engine=innodb comment '老師表';
-- 創建課程表
create table `tb_course`
(
`cou_id` int unsigned not null comment '編號',
`cou_name` varchar(50) not null comment '名稱',
`cou_credit` int not null comment '學分',
`tea_id` int unsigned not null comment '授課老師',
primary key (`cou_id`),
constraint `fk_course_tea_id` foreign key (`tea_id`) references `tb_teacher` (`tea_id`)
) engine=innodb comment '課程表';
-- 創建選課記錄表
create table `tb_record`
(
`rec_id` bigint unsigned auto_increment comment '選課記錄號',
`stu_id` int unsigned not null comment '學號',
`cou_id` int unsigned not null comment '課程編號',
`sel_date` date not null comment '選課日期',
`score` decimal(4,1) comment '考試成績',
primary key (`rec_id`),
constraint `fk_record_stu_id` foreign key (`stu_id`) references `tb_student` (`stu_id`),
constraint `fk_record_cou_id` foreign key (`cou_id`) references `tb_course` (`cou_id`),
constraint `uk_record_stu_cou` unique (`stu_id`, `cou_id`)
) engine=innodb comment '選課記錄表';
上面的DDL有幾個地方需要強調一下：
-
創建數據庫時，我們通過
default character set utf8mb4指定了數據庫默認使用的字符集為utf8mb4（最大4字節的utf-8編碼），我們推薦使用該字符集，它也是 MySQL 8.x 默認使用的字符集，因為它能夠支持國際化編碼，還可以存儲 Emoji 字符。可以通過下面的命令查看 MySQL 支持的字符集以及默認的排序規則。show character set;+----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)如果要設置 MySQL 服務啟動時默認使用的字符集，可以修改MySQL的配置並添加以下內容。
[mysqld] character-set-server=utf8 -
在創建表的時候，可以自行選擇底層的存儲引擎。MySQL 支持多種存儲引擎，可以通過
show engines命令進行查看。MySQL 5.5 以後的版本默認使用的存儲引擎是 InnoDB，它是我們推薦大家使用的存儲引擎（因為更適合當下互聯網應用對高並發、性能以及事務支持等方面的需求），為了 SQL 語句的向下兼容性，我們可以在建表語句結束處右圓括號的後面通過engine=innodb來指定使用 InnoDB 存儲引擎。show engines\G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 9 rows in set (0.00 sec)下面的表格對MySQL幾種常用的數據引擎進行了簡單的對比。
特性 InnoDB MRG_MYISAM MEMORY MyISAM 存儲限制 有 沒有 有 有 事務 支持 鎖機制 行鎖 表鎖 表鎖 表鎖 B樹索引 支持 支持 支持 支持 哈希索引 支持 全文檢索 支持（5.6+） 支持 集群索引 支持 數據緩存 支持 支持 索引緩存 支持 支持 支持 支持 數據可壓縮 支持 內存使用 高 低 中 低 存儲空間使用 高 低 低 批量插入性能 低 高 高 高 是否支持外鍵 支持 通過上面的比較我們可以了解到，InnoDB 是唯一能夠支持外鍵、事務以及行鎖的存儲引擎，所以我們之前說它更適合互聯網應用，而且在較新版本的 MySQL 中，它也是默認使用的存儲引擎。
-
在定義表結構為每個字段選擇數據類型時，如果不清楚哪個數據類型更合適，可以通過 MySQL 的幫助系統來了解每種數據類型的特性、數據的長度和精度等相關信息。
? data typesYou asked for help about help category: "Data Types" For more information, type 'help <item>', where <item> is one of the following topics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOLEAN CHAR CHAR BYTE DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECISION ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT SET DATA TYPE SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARBINARY VARCHAR YEAR DATA TYPE? varcharName: 'VARCHAR' Description: [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. See http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html. MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. *Note*: MySQL follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values. VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard SQL way to define that a VARCHAR column should use some predefined character set. MySQL uses utf8 as this predefined character set. http://dev.mysql.com/doc/refman/5.7/en/charset-national.html. NVARCHAR is shorthand for NATIONAL VARCHAR. URL: http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html在數據類型的選擇上，保存字符串數據通常都使用
VARCHAR和CHAR兩種類型，前者通常稱為變長字符串，而後者通常稱為定長字符串；對於 InnoDB 存儲引擎，行存儲格式沒有區分固定長度和可變長度列，因此VARCHAR類型和CHAR類型沒有本質區別，後者不一定比前者性能更好。如果要保存的很大字符串，可以使用TEXT類型；如果要保存很大的字節串，可以使用BLOB（二進制大對象）類型。在 MySQL 中，TEXT和BLOB又分別包括TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB三種不同的類型，它們主要的區別在於存儲數據的最大大小不同。保存浮點數可以用FLOAT或DOUBLE類型，FLOAT已經不推薦使用了，而且在 MySQL 後續的版本中可能會被移除掉。而保存定點數應該使用DECIMAL類型。如果要保存時間日期，DATETIME類型優於TIMESTAMP類型，因為前者能表示的時間日期範圍更大。
DML（數據操作語言）
我們通過如下所示的 SQL 給上面創建的表添加數據。
use school;
-- 插入學院數據
insert into `tb_college`
(`col_name`, `col_intro`)
values
('計算機學院', '計算機學院1958年設立計算機專業，1981年建立計算機科學系，1998年設立計算機學院，2005年5月，為了進一步整合教學和科研資源，學校決定，計算機學院和軟件學院行政班子合並統一運作、實行教學和學生管理獨立運行的模式。 學院下設三個系：計算機科學與技術系、物聯網工程系、計算金融系；兩個研究所：圖象圖形研究所、網絡空間安全研究院（2015年成立）；三個教學實驗中心：計算機基礎教學實驗中心、IBM技術中心和計算機專業實驗中心。'),
('外國語學院', '外國語學院設有7個教學單位，6個文理兼收的本科專業；擁有1個一級學科博士授予點，3個二級學科博士授予點，5個一級學科碩士學位授權點，5個二級學科碩士學位授權點，5個碩士專業授權領域，同時還有2個碩士專業學位（MTI）專業；有教職員工210余人，其中教授、副教授80余人，教師中獲得中國國內外名校博士學位和正在職攻讀博士學位的教師比例占專任教師的60%以上。'),
('經濟管理學院', '經濟學院前身是創辦於1905年的經濟科；已故經濟學家彭迪先、張與九、蔣學模、胡寄窗、陶大鏞、胡代光，以及當代學者劉詩白等曾先後在此任教或學習。');
-- 插入學生數據
insert into `tb_student`
(`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`)
values
(1001, '楊過', 1, '1990-3-4', '湖南長沙', 1),
(1002, '任我行', 1, '1992-2-2', '湖南長沙', 1),
(1033, '王語嫣', 0, '1989-12-3', '四川成都', 1),
(1572, '岳不群', 1, '1993-7-19', '陜西鹹陽', 1),
(1378, '紀嫣然', 0, '1995-8-12', '四川綿陽', 1),
(1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
(2035, '東方不敗', 1, '1988-6-30', null, 2),
(3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
(3755, '項少龍', 1, '1993-1-25', '四川成都', 3),
(3923, '楊不悔', 0, '1985-4-17', '四川成都', 3);
-- 插入老師數據
insert into `tb_teacher`
(`tea_id`, `tea_name`, `tea_title`, `col_id`)
values
(1122, '張三豐', '教授', 1),
(1133, '宋遠橋', '副教授', 1),
(1144, '楊逍', '副教授', 1),
(2255, '範遙', '副教授', 2),
(3366, '韋一笑', default, 3);
-- 插入課程數據
insert into `tb_course`
(`cou_id`, `cou_name`, `cou_credit`, `tea_id`)
values
(1111, 'Python程序設計', 3, 1122),
(2222, 'Web前端開發', 2, 1122),
(3333, '操作系統', 4, 1122),
(4444, '計算機網絡', 2, 1133),
(5555, '編譯原理', 4, 1144),
(6666, '算法和數據結構', 3, 1144),
(7777, '經貿法語', 3, 2255),
(8888, '成本會計', 2, 3366),
(9999, '審計學', 3, 3366);
-- 插入選課數據
insert into `tb_record`
(`stu_id`, `cou_id`, `sel_date`, `score`)
values
(1001, 1111, '2017-09-01', 95),
(1001, 2222, '2017-09-01', 87.5),
(1001, 3333, '2017-09-01', 100),
(1001, 4444, '2018-09-03', null),
(1001, 6666, '2017-09-02', 100),
(1002, 1111, '2017-09-03', 65),
(1002, 5555, '2017-09-01', 42),
(1033, 1111, '2017-09-03', 92.5),
(1033, 4444, '2017-09-01', 78),
(1033, 5555, '2017-09-01', 82.5),
(1572, 1111, '2017-09-02', 78),
(1378, 1111, '2017-09-05', 82),
(1378, 7777, '2017-09-02', 65.5),
(2035, 7777, '2018-09-03', 88),
(2035, 9999, '2019-09-02', null),
(3755, 1111, '2019-09-02', null),
(3755, 8888, '2019-09-02', null),
(3755, 9999, '2017-09-01', 92);
注意：上面的
insert語句使用了批處理的方式來插入數據，這種做法插入數據的效率比較高。
DQL（數據查詢語言）
接下來，我們完成如下所示的查詢。
-- 查詢所有學生的所有信息
select * from `tb_student`;
-- 查詢學生的學號、姓名和籍貫(投影)
select `stu_id`, `stu_name`, `stu_addr` from `tb_student`;
-- 查詢所有課程的名稱及學分(投影和別名)
select `cou_name` as 課程名稱, `cou_credit` as 學分 from `tb_course`;
-- 查詢所有女學生的姓名和出生日期(篩選)
select `stu_name`, `stu_birth` from `tb_student` where `stu_sex`=0;
-- 查詢籍貫為“四川成都”的女學生的姓名和出生日期(篩選)
select `stu_name`, `stu_birth` from `tb_student` where `stu_sex`=0 and `stu_addr`='四川成都';
-- 查詢籍貫為“四川成都”或者性別為“女生”的學生
select `stu_name`, `stu_birth` from `tb_student` where `stu_sex`=0 or `stu_addr`='四川成都';
-- 查詢所有80後學生的姓名、性別和出生日期(篩選)
select `stu_name`, `stu_sex`, `stu_birth` from `tb_student`
where `stu_birth`>='1980-1-1' and `stu_birth`<='1989-12-31';
select `stu_name`, `stu_sex`, `stu_birth` from `tb_student`
where `stu_birth` between '1980-1-1' and '1989-12-31';
-- 補充：將表示性別的 1 和 0 處理成 “男” 和 “女”
select
`stu_name` as 姓名,
if(`stu_sex`, '男', '女') as 性別,
`stu_birth` as 出生日期
from `tb_student`
where `stu_birth` between '1980-1-1' and '1989-12-31';
select
`stu_name` as 姓名,
case `stu_sex` when 1 then '男' else '女' end as 性別,
`stu_birth` as 出生日期
from `tb_student`
where `stu_birth` between '1980-1-1' and '1989-12-31';
-- 查詢學分大於2的課程的名稱和學分(篩選)
select `cou_name`, `cou_credit` from `tb_course` where `cou_credit`>2;
-- 查詢學分是奇數的課程的名稱和學分(篩選)
select `cou_name`, `cou_credit` from `tb_course` where `cou_credit`%2<>0;
select `cou_name`, `cou_credit` from `tb_course` where `cou_credit` mod 2<>0;
-- 查詢選擇選了1111的課程考試成績在90分以上的學生學號(篩選)
select `stu_id` from `tb_record` where `cou_id`=1111 and `score`>90;
-- 查詢名字叫“楊過”的學生的姓名和性別
select `stu_name`, `stu_sex` from `tb_student` where `stu_name`='楊過';
-- 查詢姓“楊”的學生姓名和性別(模糊)
-- % - 通配符（wildcard），它可以匹配0個或任意多個字符
select `stu_name`, `stu_sex` from `tb_student` where `stu_name` like '楊%';
-- 查詢姓“楊”名字兩個字的學生姓名和性別(模糊)
-- _ - 通配符（wildcard），它可以精確匹配一個字符
select `stu_name`, `stu_sex` from `tb_student` where `stu_name` like '楊_';
-- 查詢姓“楊”名字三個字的學生姓名和性別(模糊)
select `stu_name`, `stu_sex` from `tb_student` where `stu_name` like '楊__';
-- 查詢名字中有“不”字或“嫣”字的學生的姓名(模糊)
select `stu_name` from `tb_student` where `stu_name` like '%不%' or `stu_name` like '%嫣%';
-- 將“岳不群”改名為“岳不嫣”，比較下面兩個查詢的區別
update `tb_student` set `stu_name`='岳不嫣' where `stu_id`=1572;
select `stu_name` from `tb_student` where `stu_name` like '%不%'
union
select `stu_name` from `tb_student` where `stu_name` like '%嫣%';
select `stu_name` from `tb_student` where `stu_name` like '%不%'
union all
select `stu_name` from `tb_student` where `stu_name` like '%嫣%';
-- 查詢姓“楊”或姓“林”名字三個字的學生的姓名(正則表達式模糊查詢)
select `stu_name` from `tb_student` where `stu_name` regexp '[楊林].{2}';
-- 查詢沒有錄入籍貫的學生姓名(空值處理)
select `stu_name` from `tb_student` where `stu_addr` is null;
select `stu_name` from `tb_student` where `stu_addr` <=> null;
-- 查詢錄入了籍貫的學生姓名(空值處理)
select `stu_name` from `tb_student` where `stu_addr` is not null;
-- 下面的查詢什麽也查不到，三值邏輯 --> true / false / unknown
select `stu_name` from `tb_student` where `stu_addr`=null or `stu_addr`<>null;
-- 查詢學生選課的所有日期(去重)
select distinct `sel_date` from `tb_record`;
-- 查詢學生的籍貫(去重)
select distinct `stu_addr` from `tb_student` where `stu_addr` is not null;
-- 查詢男學生的姓名和生日按年齡從大到小排列(排序)
-- 升序：從小到大 - asc，降序：從大到小 - desc
select `stu_id`, `stu_name`, `stu_birth` from `tb_student`
where `stu_sex`=1 order by `stu_birth` asc, `stu_id` desc;
-- 補充：將上面的生日換算成年齡(日期函數、數值函數)
select
`stu_id` as 學號,
`stu_name` as 姓名,
floor(datediff(curdate(), `stu_birth`)/365) as 年齡
from `tb_student`
where `stu_sex`=1 order by 年齡 desc, `stu_id` desc;
-- 查詢年齡最大的學生的出生日期(聚合函數)
select min(`stu_birth`) from `tb_student`;
-- 查詢年齡最小的學生的出生日期(聚合函數)
select max(`stu_birth`) from `tb_student`;
-- 查詢編號為1111的課程考試成績的最高分(聚合函數)
select max(`score`) from `tb_record` where `cou_id`=1111;
-- 查詢學號為1001的學生考試成績的最低分(聚合函數)
select min(`score`) from `tb_record` where `stu_id`=1001;
-- 查詢學號為1001的學生考試成績的平均分(聚合函數)
select avg(`score`) from `tb_record` where `stu_id`=1001;
select sum(`score`) / count(`score`) from `tb_record` where `stu_id`=1001;
-- 查詢學號為1001的學生考試成績的平均分，如果有null值，null值算0分(聚合函數)
select sum(`score`) / count(*) from `tb_record` where `stu_id`=1001;
select avg(ifnull(`score`, 0)) from `tb_record` where `stu_id`=1001;
-- 查詢學號為1001的學生考試成績的標準差(聚合函數)
select std(`score`), variance(`score`) from `tb_record` where `stu_id`=1001;
-- 查詢男女學生的人數(分組和聚合函數)
select
case `stu_sex` when 1 then '男' else '女' end as 性別,
count(*) as 人數
from `tb_student` group by `stu_sex`;
-- 查詢每個學院學生人數(分組和聚合函數)
select
`col_id` as 學院,
count(*) as 人數
from `tb_student` group by `col_id` with rollup;
-- 查詢每個學院男女學生人數(分組和聚合函數)
select
`col_id` as 學院,
if(`stu_sex`, '男', '女') as 性別,
count(*) as 人數
from `tb_student` group by `col_id`, `stu_sex`;
-- 查詢每個學生的學號和平均成績(分組和聚合函數)
select
`stu_id`,
round(avg(`score`), 1) as avg_score
from `tb_record` group by `stu_id`;
-- 查詢平均成績大於等於90分的學生的學號和平均成績
-- 分組以前的篩選使用where子句，分組以後的篩選使用having子句
select
`stu_id`,
round(avg(`score`), 1) as avg_score
from `tb_record`
group by `stu_id` having avg_score>=90;
-- 查詢1111、2222、3333三門課程平均成績大於等於90分的學生的學號和平均成績
select
`stu_id`,
round(avg(`score`), 1) as avg_score
from `tb_record` where `cou_id` in (1111, 2222, 3333)
group by `stu_id` having avg_score>=90;
-- 查詢年齡最大的學生的姓名(子查詢/嵌套查詢)
-- 嵌套查詢：把一個select的結果作為另一個select的一部分來使用
select `stu_name` from `tb_student`
where `stu_birth`=(
select min(`stu_birth`) from `tb_student`
);
-- 查詢選了兩門以上的課程的學生姓名(子查詢/分組條件/集合運算)
select `stu_name` from `tb_student`
where `stu_id` in (
select `stu_id` from `tb_record`
group by `stu_id` having count(*)>2
);
-- 查詢學生的姓名、生日和所在學院名稱
select `stu_name`, `stu_birth`, `col_name`
from `tb_student`, `tb_college`
where `tb_student`.`col_id`=`tb_college`.`col_id`;
select `stu_name`, `stu_birth`, `col_name`
from `tb_student` inner join `tb_college`
on `tb_student`.`col_id`=`tb_college`.`col_id`;
select `stu_name`, `stu_birth`, `col_name`
from `tb_student` natural join `tb_college`;
-- 查詢學生姓名、課程名稱以及成績(連接查詢/聯結查詢)
select `stu_name`, `cou_name`, `score`
from `tb_student`, `tb_course`, `tb_record`
where `tb_student`.`stu_id`=`tb_record`.`stu_id`
and `tb_course`.`cou_id`=`tb_record`.`cou_id`
and `score` is not null;
select `stu_name`, `cou_name`, `score` from `tb_student`
inner join `tb_record` on `tb_student`.`stu_id`=`tb_record`.`stu_id`
inner join `tb_course` on `tb_course`.`cou_id`=`tb_record`.`cou_id`
where `score` is not null;
select `stu_name`, `cou_name`, `score` from `tb_student`
natural join `tb_record`
natural join `tb_course`
where `score` is not null;
-- 補充：上面的查詢結果取前5條數據(分頁查詢)
select `stu_name`, `cou_name`, `score`
from `tb_student`, `tb_course`, `tb_record`
where `tb_student`.`stu_id`=`tb_record`.`stu_id`
and `tb_course`.`cou_id`=`tb_record`.`cou_id`
and `score` is not null
order by `score` desc
limit 0,5;
-- 補充：上面的查詢結果取第6-10條數據(分頁查詢)
select `stu_name`, `cou_name`, `score`
from `tb_student`, `tb_course`, `tb_record`
where `tb_student`.`stu_id`=`tb_record`.`stu_id`
and `tb_course`.`cou_id`=`tb_record`.`cou_id`
and `score` is not null
order by `score` desc
limit 5 offset 5;
-- 補充：上面的查詢結果取第11-15條數據(分頁查詢)
select `stu_name`, `cou_name`, `score`
from `tb_student`, `tb_course`, `tb_record`
where `tb_student`.`stu_id`=`tb_record`.`stu_id`
and `tb_course`.`cou_id`=`tb_record`.`cou_id`
and `score` is not null
order by `score` desc
limit 5 offset 10;
-- 查詢選課學生的姓名和平均成績(子查詢和連接查詢)
select `stu_name`, `avg_score`
from `tb_student` inner join (
select `stu_id` as `sid`, round(avg(`score`), 1) as avg_score
from `tb_record` group by `stu_id`
) as `t2` on `stu_id`=`sid`;
-- 查詢學生的姓名和選課的數量
select `stu_name`, `total` from `tb_student` as `t1`
inner join (
select `stu_id`, count(*) as `total`
from `tb_record` group by `stu_id`
) as `t2` on `t1`.`stu_id`=`t2`.`stu_id`;
-- 查詢每個學生的姓名和選課數量(左外連接和子查詢)
-- 左外連接：左表（寫在join左邊的表）的每條記錄都可以查出來，不滿足連表條件的地方填充null。
select `stu_name`, coalesce(`total`, 0) as `total`
from `tb_student` as `t1`
left outer join (
select `stu_id`, count(*) as `total`
from `tb_record` group by `stu_id`
) as `t2` on `t1`.`stu_id`=`t2`.`stu_id`;
-- 修改選課記錄表，去掉 stu_id 列的外鍵約束
alter table `tb_record` drop foreign key `fk_record_stu_id`;
-- 插入兩條新紀錄（注意：沒有學號為 5566 的學生）
insert into `tb_record`
values
(default, 5566, 1111, '2019-09-02', 80),
(default, 5566, 2222, '2019-09-02', 70);
-- 右外連接：右表（寫在join右邊的表）的每條記錄都可以查出來，不滿足連表條件的地方填充null。
select `stu_name`, `total` from `tb_student` as `t1`
right outer join (
select `stu_id`, count(*) as `total`
from `tb_record` group by `stu_id`
) as `t2` on `t1`.`stu_id`=`t2`.`stu_id`;
-- 全外連接：左表和右表的每條記錄都可以查出來，不滿足連表條件的地方填充null。
-- 說明：MySQL不支持全外連接，所以用左外連接和右外連接的並集來表示。
select `stu_name`, `total`
from `tb_student` as `t1`
left outer join (
select `stu_id`, count(*) as `total`
from `tb_record` group by `stu_id`
) as `t2` on `t1`.`stu_id`=`t2`.`stu_id`
union
select `stu_name`, `total` from `tb_student` as `t1`
right outer join (
select `stu_id`, count(*) as `total`
from `tb_record` group by `stu_id`
) as `t2` on `t1`.`stu_id`=`t2`.`stu_id`;
上面的DML有幾個地方需要加以說明：
-
MySQL目前的版本不支持全外連接，上面我們通過
union操作，將左外連接和右外連接的結果求並集實現全外連接的效果。大家可以通過下面的圖來加深對連表操作的認識。
-
MySQL 中支持多種類型的運算符，包括：算術運算符（
+、-、*、/、%）、比較運算符（=、<>、<=>、<、<=、>、>=、BETWEEN...AND...、IN、IS NULL、IS NOT NULL、LIKE、RLIKE、REGEXP）、邏輯運算符（NOT、AND、OR、XOR）和位運算符（&、|、^、~、>>、<<），我們可以在 DML 中使用這些運算符處理數據。 -
在查詢數據時，可以在
SELECT語句及其子句（如WHERE子句、ORDER BY子句、HAVING子句等）中使用函數，這些函數包括字符串函數、數值函數、時間日期函數、流程函數等，如下面的表格所示。常用字符串函數。
函數 功能 CONCAT將多個字符串連接成一個字符串 FORMAT將數值格式化成字符串並指定保留幾位小數 FROM_BASE64/TO_BASE64BASE64解碼/編碼 BIN/OCT/HEX將數值轉換成二進制/八進制/十六進制字符串 LOCATE在字符串中查找一個子串的位置 LEFT/RIGHT返回一個字符串左邊/右邊指定長度的字符 LENGTH/CHAR_LENGTH返回字符串的長度以字節/字符為單位 LOWER/UPPER返回字符串的小寫/大寫形式 LPAD/RPAD如果字符串的長度不足，在字符串左邊/右邊填充指定的字符 LTRIM/RTRIM去掉字符串前面/後面的空格 ORD/CHAR返回字符對應的編碼/返回編碼對應的字符 STRCMP比較字符串，返回-1、0、1分別表示小於、等於、大於 SUBSTRING返回字符串指定範圍的子串 常用數值函數。
函數 功能 ABS返回一個數的絕度值 CEILING/FLOOR返回一個數上取整/下取整的結果 CONV將一個數從一種進制轉換成另一種進制 CRC32計算循環冗余校驗碼 EXP/LOG/LOG2/LOG10計算指數/對數 POW求冪 RAND返回[0,1)範圍的隨機數 ROUND返回一個數四舍五入後的結果 SQRT返回一個數的平方根 TRUNCATE截斷一個數到指定的精度 SIN/COS/TAN/COT/ASIN/ACOS/ATAN三角函數 常用時間日期函數。
函數 功能 CURDATE/CURTIME/NOW獲取當前日期/時間/日期和時間 ADDDATE/SUBDATE將兩個日期表達式相加/相減並返回結果 DATE/TIME從字符串中獲取日期/時間 YEAR/MONTH/DAY從日期中獲取年/月/日 HOUR/MINUTE/SECOND從時間中獲取時/分/秒 DATEDIFF/TIMEDIFF返回兩個時間日期表達式相差多少天/小時 MAKEDATE/MAKETIME制造一個日期/時間 常用流程函數。
函數 功能 IF根據條件是否成立返回不同的值 IFNULL如果為NULL則返回指定的值否則就返回本身 NULLIF兩個表達式相等就返回NULL否則返回第一個表達式的值 其他常用函數。
函數 功能 MD5/SHA1/SHA2返回字符串對應的哈希摘要 CHARSET/COLLATION返回字符集/校對規則 USER/CURRENT_USER返回當前用戶 DATABASE返回當前數據庫名 VERSION返回當前數據庫版本 FOUND_ROWS/ROW_COUNT返回查詢到的行數/受影響的行數 LAST_INSERT_ID返回最後一個自增主鍵的值 UUID/UUID_SHORT返回全局唯一標識符
DCL（數據控制語言）
數據控制語言用於給指定的用戶授權或者從召回指定用戶的指定權限，這組操作對數據庫管理員來說比較重要，將一個用戶的權限最小化（剛好夠用）是非常重要的，對數據庫的安全至關重要。
-- 創建名為 wangdachui 的賬號並為其指定口令，允許該賬號從任意主機訪問
create user 'wangdachui'@'%' identified by '123456';
-- 授權 wangdachui 可以對名為school的數據庫執行 select 和 insert 操作
grant select, insert on `school`.* to 'wangdachui'@'%';
-- 召回 wangdachui 對school數據庫的 insert 權限
revoke insert on `school`.* from 'wangdachui'@'%';
說明：創建一個可以允許任意主機登錄並且具有超級管理員權限的用戶在現實中並不是一個明智的決定，因為一旦該賬號的口令泄露或者被破解，數據庫將會面臨災難級的風險。
MySQL 詳解
索引
索引是關系型數據庫中用來提升查詢性能最為重要的手段。關系型數據庫中的索引就像一本書的目錄，我們可以想象一下，如果要從一本書中找出某個知識點，但是這本書沒有目錄，這將是意見多麽可怕的事情！我們估計得一篇一篇的翻下去，才能確定這個知識點到底在什麽位置。創建索引雖然會帶來存儲空間上的開銷，就像一本書的目錄會占用一部分篇幅一樣，但是在犧牲空間後換來的查詢時間的減少也是非常顯著的。
MySQL 數據庫中所有數據類型的列都可以被索引。對於MySQL 8.0 版本的 InnoDB 存儲引擎來說，它支持三種類型的索引，分別是 B+ 樹索引、全文索引和 R 樹索引。這里，我們只介紹使用得最為廣泛的 B+ 樹索引。使用 B+ 樹的原因非常簡單，因為它是目前在基於磁盤進行海量數據存儲和排序上最有效率的數據結構。B+ 樹是一棵平衡樹，樹的高度通常為3或4，但是卻可以保存從百萬級到十億級的數據，而從這些數據里面查詢一條數據，只需要3次或4次 I/O 操作。
B+ 樹由根節點、中間節點和葉子節點構成，其中葉子節點用來保存排序後的數據。由於記錄在索引上是排序過的，因此在一個葉子節點內查找數據時可以使用二分查找，這種查找方式效率非常的高。當數據很少的時候，B+ 樹只有一個根節點，數據也就保存在根節點上。隨著記錄越來越多，B+ 樹會發生分裂，根節點不再保存數據，而是提供了訪問下一層節點的指針，幫助快速確定數據在哪個葉子節點上。
在創建二維表時，我們通常都會為表指定主鍵列，主鍵列上默認會創建索引，而對於 MySQL InnoDB 存儲引擎來說，因為它使用的是索引組織表這種數據存儲結構，所以主鍵上的索引就是整張表的數據，而這種索引我們也將其稱之為聚集索引（clustered index）。很顯然，一張表只能有一個聚集索引，否則表的數據豈不是要保存多次。我們自己創建的索引都是二級索引（secondary index），更常見的叫法是非聚集索引（non-clustered index）。通過我們自定義的非聚集索引只能定位記錄的主鍵，在獲取數據時可能需要再通過主鍵上的聚集索引進行查詢，這種現象稱為“回表”，因此通過非聚集索引檢索數據通常比使用聚集索引檢索數據要慢。
接下來我們通過一個簡單的例子來說明索引的意義，比如我們要根據學生的姓名來查找學生，這個場景在實際開發中應該經常遇到，就跟通過商品名稱查找商品是一個道理。我們可以使用 MySQL 的explain關鍵字來查看 SQL 的執行計劃（數據庫執行 SQL 語句的具體步驟）。
explain select * from tb_student where stuname='林震南'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_student
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
在上面的 SQL 執行計劃中，有幾項值得我們關注：
select_type：查詢的類型。SIMPLE：簡單 SELECT，不需要使用 UNION 操作或子查詢。PRIMARY：如果查詢包含子查詢，最外層的 SELECT 被標記為 PRIMARY。UNION：UNION 操作中第二個或後面的 SELECT 語句。SUBQUERY：子查詢中的第一個 SELECT。DERIVED：派生表的 SELECT 子查詢。
table：查詢對應的表。type：MySQL 在表中找到滿足條件的行的方式，也稱為訪問類型，包括：ALL（全表掃描）、index（索引全掃描，只遍歷索引樹）、range（索引範圍掃描）、ref（非唯一索引掃描）、eq_ref（唯一索引掃描）、const/system（常量級查詢）、NULL（不需要訪問表或索引）。在所有的訪問類型中，很顯然 ALL 是性能最差的，它代表的全表掃描是指要掃描表中的每一行才能找到匹配的行。possible_keys：MySQL 可以選擇的索引，但是有可能不會使用。key：MySQL 真正使用的索引，如果為NULL就表示沒有使用索引。key_len：使用的索引的長度，在不影響查詢的情況下肯定是長度越短越好。rows：執行查詢需要掃描的行數，這是一個預估值。extra：關於查詢額外的信息。Using filesort：MySQL 無法利用索引完成排序操作。Using index：只使用索引的信息而不需要進一步查表來獲取更多的信息。Using temporary：MySQL 需要使用臨時表來存儲結果集，常用於分組和排序。Impossible where：where子句會導致沒有符合條件的行。Distinct：MySQL 發現第一個匹配行後，停止為當前的行組合搜索更多的行。Using where：查詢的列未被索引覆蓋，篩選條件並不是索引的前導列。
從上面的執行計劃可以看出，當我們通過學生名字查詢學生時實際上是進行了全表掃描，不言而喻這個查詢性能肯定是非常糟糕的，尤其是在表中的行很多的時候。如果我們需要經常通過學生姓名來查詢學生，那麽就應該在學生姓名對應的列上創建索引，通過索引來加速查詢。
create index idx_student_name on tb_student(stuname);
再次查看剛才的 SQL 對應的執行計劃。
explain select * from tb_student where stuname='林震南'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_student
partitions: NULL
type: ref
possible_keys: idx_student_name
key: idx_student_name
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
可以注意到，在對學生姓名創建索引後，剛才的查詢已經不是全表掃描而是基於索引的查詢，而且掃描的行只有唯一的一行，這顯然大大的提升了查詢的性能。MySQL 中還允許創建前綴索引，即對索引字段的前N個字符創建索引，這樣的話可以減少索引占用的空間（但節省了空間很有可能會浪費時間，時間和空間是不可調和的矛盾），如下所示。
create index idx_student_name_1 on tb_student(stuname(1));
上面的索引相當於是根據學生姓名的第一個字來創建的索引，我們再看看 SQL 執行計劃。
explain select * from tb_student where stuname='林震南'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb_student
partitions: NULL
type: ref
possible_keys: idx_student_name
key: idx_student_name
key_len: 5
ref: const
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
不知道大家是否注意到，這一次掃描的行變成了2行，因為學生表中有兩個姓“林”的學生，我們只用姓名的第一個字作為索引的話，在查詢時通過索引就會找到這兩行。
如果要刪除索引，可以使用下面的SQL。
alter table tb_student drop index idx_student_name;
或者
drop index idx_student_name on tb_student;
在創建索引時，我們還可以使用覆合索引、函數索引（MySQL 5.7 開始支持），用好覆合索引實現索引覆蓋可以減少不必要的排序和回表操作，這樣就會讓查詢的性能成倍的提升，有興趣的讀者可以自行研究。
我們簡單的為大家總結一下索引的設計原則：
- 最適合索引的列是出現在WHERE子句和連接子句中的列。
- 索引列的基數越大（取值多、重覆值少），索引的效果就越好。
- 使用前綴索引可以減少索引占用的空間，內存中可以緩存更多的索引。
- 索引不是越多越好，雖然索引加速了讀操作（查詢），但是寫操作（增、刪、改）都會變得更慢，因為數據的變化會導致索引的更新，就如同書籍章節的增刪需要更新目錄一樣。
- 使用 InnoDB 存儲引擎時，表的普通索引都會保存主鍵的值，所以主鍵要盡可能選擇較短的數據類型，這樣可以有效的減少索引占用的空間，提升索引的緩存效果。
最後，還有一點需要說明，InnoDB 使用的 B-tree 索引，數值類型的列除了等值判斷時索引會生效之外，使用>、<、>=、<=、BETWEEN...AND... 、<>時，索引仍然生效；對於字符串類型的列，如果使用不以通配符開頭的模糊查詢，索引也是起作用的，但是其他的情況會導致索引失效，這就意味著很有可能會做全表查詢。
視圖
視圖是關系型數據庫中將一組查詢指令構成的結果集組合成可查詢的數據表的對象。簡單的說，視圖就是虛擬的表，但與數據表不同的是，數據表是一種實體結構，而視圖是一種虛擬結構，你也可以將視圖理解為保存在數據庫中被賦予名字的 SQL 語句。
使用視圖可以獲得以下好處：
- 可以將實體數據表隱藏起來，讓外部程序無法得知實際的數據結構，讓訪問者可以使用表的組成部分而不是整個表，降低數據庫被攻擊的風險。
- 在大多數的情況下視圖是只讀的（更新視圖的操作通常都有諸多的限制），外部程序無法直接透過視圖修改數據。
- 重用 SQL 語句，將高度覆雜的查詢包裝在視圖表中，直接訪問該視圖即可取出需要的數據；也可以將視圖視為數據表進行連接查詢。
- 視圖可以返回與實體數據表不同格式的數據，在創建視圖的時候可以對數據進行格式化處理。
創建視圖。
-- 創建視圖
create view `vw_avg_score`
as
select `stu_id`, round(avg(`score`), 1) as `avg_score`
from `tb_record` group by `stu_id`;
-- 基於已有的視圖創建視圖
create view `vw_student_score`
as
select `stu_name`, `avg_score`
from `tb_student` natural join `vw_avg_score`;
提示：因為視圖不包含數據，所以每次使用視圖時，都必須執行查詢以獲得數據，如果你使用了連接查詢、嵌套查詢創建了較為覆雜的視圖，你可能會發現查詢性能下降得很厲害。因此，在使用覆雜的視圖前，應該進行測試以確保其性能能夠滿足應用的需求。
使用視圖。
select * from `vw_student_score` order by `avg_score` desc;
+--------------+----------+
| stuname | avgscore |
+--------------+----------+
| 楊過 | 95.6 |
| 任我行 | 53.5 |
| 王語嫣 | 84.3 |
| 紀嫣然 | 73.8 |
| 岳不群 | 78.0 |
| 東方不敗 | 88.0 |
| 項少龍 | 92.0 |
+--------------+----------+
既然視圖是一張虛擬的表，那麽視圖的中的數據可以更新嗎？視圖的可更新性要視具體情況而定，以下類型的視圖是不能更新的：
- 使用了聚合函數（
SUM、MIN、MAX、AVG、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL的視圖。 SELECT中包含了子查詢的視圖。FROM子句中包含了一個不能更新的視圖的視圖。WHERE子句的子查詢引用了FROM子句中的表的視圖。
刪除視圖。
drop view vw_student_score;
說明：如果希望更新視圖，可以先用上面的命令刪除視圖，也可以通過
create or replace view來更新視圖。
視圖的規則和限制。
- 視圖可以嵌套，可以利用從其他視圖中檢索的數據來構造一個新的視圖。視圖也可以和表一起使用。
- 創建視圖時可以使用
order by子句，但如果從視圖中檢索數據時也使用了order by，那麽該視圖中原先的order by會被覆蓋。 - 視圖無法使用索引，也不會激發觸發器（實際開發中因為性能等各方面的考慮，通常不建議使用觸發器，所以我們也不對這個概念進行介紹）的執行。
函數
MySQL 中的函數跟 Python 中的函數太多的差異，因為函數都是用來封裝功能上相對獨立且會被重覆使用的代碼的。如果非要找出一些差別來，那麽 MySQL 中的函數是可以執行 SQL 語句的。下面的例子，我們通過自定義函數實現了截斷超長字符串的功能。
delimiter $$
create function truncate_string(
content varchar(10000),
max_length int unsigned
) returns varchar(10000) no sql
begin
declare result varchar(10000) default content;
if char_length(content) > max_length then
set result = left(content, max_length);
set result = concat(result, '……');
end if;
return result;
end $$
delimiter ;
說明1：函數聲明後面的
no sql是聲明函數體並沒有使用 SQL 語句；如果函數體中需要通過 SQL 讀取數據，需要聲明為reads sql data。說明2：定義函數前後的
delimiter命令是為了修改定界符，因為函數體中的語句都是用;表示結束，如果不重新定義定界符，那麽遇到的;的時候代碼就會被截斷執行，顯然這不是我們想要的效果。
在查詢中調用自定義函數。
select truncate_string('和我在成都的街頭走一走，直到所有的燈都熄滅了也不停留', 10) as short_string;
+--------------------------------------+
| short_string |
+--------------------------------------+
| 和我在成都的街頭走一…… |
+--------------------------------------+
過程
過程（又稱存儲過程）是事先編譯好存儲在數據庫中的一組 SQL 的集合，調用過程可以簡化應用程序開發人員的工作，減少與數據庫服務器之間的通信，對於提升數據操作的性能也是有幫助的。其實迄今為止，我們使用的 SQL 語句都是針對一個或多個表的單條語句，但在實際開發中經常會遇到某個操作需要多條 SQL 語句才能完成的情況。例如，電商網站在受理用戶訂單時，需要做以下一系列的處理。
- 通過查詢來核對庫存中是否有對應的物品以及庫存是否充足。
- 如果庫存有物品，需要鎖定庫存以確保這些物品不再賣給別人， 並且要減少可用的物品數量以反映正確的庫存量。
- 如果庫存不足，可能需要進一步與供應商進行交互或者至少產生一條系統提示消息。
- 不管受理訂單是否成功，都需要產生流水記錄，而且需要給對應的用戶產生一條通知信息。
我們可以通過過程將覆雜的操作封裝起來，這樣不僅有助於保證數據的一致性，而且將來如果業務發生了變動，只需要調整和修改過程即可。對於調用過程的用戶來說，過程並沒有暴露數據表的細節，而且執行過程比一條條的執行一組 SQL 要快得多。
下面的過程實現了查詢某門課程的最高分、最低分和平均分。
drop procedure if exists sp_score_stat;
delimiter $$
create procedure sp_score_stat(
courseId int,
out maxScore decimal(4,1),
out minScore decimal(4,1),
out avgScore decimal(4,1)
)
begin
select max(score) into maxScore from tb_record where cou_id=courseId;
select min(score) into minScore from tb_record where cou_id=courseId;
select avg(score) into avgScore from tb_record where cou_id=courseId;
end $$
delimiter ;
說明：在定義過程時，因為可能需要書寫多條 SQL，而分隔這些 SQL 需要使用分號作為分隔符，如果這個時候，仍然用分號表示整段代碼結束，那麽定義過程的 SQL 就會出現錯誤，所以上面我們用
delimiter $$將整段代碼結束的標記定義為$$，那麽代碼中的分號將不再表示整段代碼的結束，整段代碼只會在遇到end $$時才會執行。在定義完過程後，通過delimiter ;將結束符重新改回成分號（恢覆現場）。
上面定義的過程有四個參數，其中第一個參數是輸入參數，代表課程的編號，後面的參數都是輸出參數，因為過程不能定義返回值，只能通過輸出參數將執行結果帶出，定義輸出參數的關鍵字是out，默認情況下參數都是輸入參數。
調用過程。
call sp_score_stat(1111, @a, @b, @c);
獲取輸出參數的值。
select @a as 最高分, @b as 最低分, @c as 平均分;
刪除過程。
drop procedure sp_score_stat;
在過程中，我們可以定義變量、條件，可以使用分支和循環語句，可以通過遊標操作查詢結果，還可以使用事件調度器，這些內容我們暫時不在此處進行介紹。雖然我們說了很多過程的好處，但是在實際開發中，如果頻繁的使用過程並將大量覆雜的運算放到過程中，會給據庫服務器造成巨大的壓力，而數據庫往往都是性能瓶頸所在，使用過程無疑是雪上加霜的操作。所以，對於互聯網產品開發，我們一般建議讓數據庫只做好存儲，覆雜的運算和處理交給應用服務器上的程序去完成，如果應用服務器變得不堪重負了，我們可以比較容易的部署多台應用服務器來分攤這些壓力。
如果大家對上面講到的視圖、函數、過程包括我們沒有講到的觸發器這些知識有興趣，建議大家閱讀 MySQL 的入門讀物《MySQL必知必會》進行一般性了解即可，因為這些知識點在大家將來的工作中未必用得上，學了也可能僅僅是為了應付面試而已。
MySQL 新特性
JSON類型
很多開發者在使用關系型數據庫做數據持久化的時候，常常感到結構化的存儲缺乏靈活性，因為必須事先設計好所有的列以及對應的數據類型。在業務發展和變化的過程中，如果需要修改表結構，這絕對是比較麻煩和難受的事情。從 MySQL 5.7 版本開始，MySQL引入了對 JSON 數據類型的支持（MySQL 8.0 解決了 JSON 的日志性能瓶頸問題），用好 JSON 類型，其實就是打破了關系型數據庫和非關系型數據庫之間的界限，為數據持久化操作帶來了更多的便捷。
JSON 類型主要分為 JSON 對象和 JSON數組兩種，如下所示。
- JSON 對象
{"name": "駱昊", "tel": "13122335566", "QQ": "957658"}
- JSON 數組
[1, 2, 3]
[{"name": "駱昊", "tel": "13122335566"}, {"name": "王大錘", "QQ": "123456"}]
哪些地方需要用到JSON類型呢？舉一個簡單的例子，現在很多產品的用戶登錄都支持多種方式，例如手機號、微信、QQ、新浪微博等，但是一般情況下我們又不會要求用戶提供所有的這些信息，那麽用傳統的設計方式，就需要設計多個列來對應多種登錄方式，可能還需要允許這些列存在空值，這顯然不是很好的選擇；另一方面，如果產品又增加了一種登錄方式，那麽就必然要修改之前的表結構，這就更讓人痛苦了。但是，有了 JSON 類型，剛才的問題就迎刃而解了，我們可以做出如下所示的設計。
create table `tb_test`
(
`user_id` bigint unsigned,
`login_info` json,
primary key (`user_id`)
) engine=innodb;
insert into `tb_test` values
(1, '{"tel": "13122335566", "QQ": "654321", "wechat": "jackfrued"}'),
(2, '{"tel": "13599876543", "weibo": "wangdachui123"}');
如果要查詢用戶的手機和微信號，可以用如下所示的 SQL 語句。
select
`user_id`,
json_unquote(json_extract(`login_info`, '$.tel')) as 手機號,
json_unquote(json_extract(`login_info`, '$.wechat')) as 微信
from `tb_test`;
+---------+-------------+-----------+
| user_id | 手機號 | 微信 |
+---------+-------------+-----------+
| 1 | 13122335566 | jackfrued |
| 2 | 13599876543 | NULL |
+---------+-------------+-----------+
因為支持 JSON 類型，MySQL 也提供了配套的處理 JSON 數據的函數，就像上面用到的json_extract和json_unquote。當然，上面的 SQL 還有更為便捷的寫法，如下所示。
select
`user_id`,
`login_info` ->> '$.tel' as 手機號,
`login_info` ->> '$.wechat' as 微信
from `tb_test`;
再舉個例子，如果我們的產品要實現用戶畫像功能（給用戶打標簽），然後基於用戶畫像給用戶推薦平台的服務或消費品之類的東西，我們也可以使用 JSON 類型來保存用戶畫像數據，示意代碼如下所示。
創建畫像標簽表。
create table `tb_tags`
(
`tag_id` int unsigned not null comment '標簽ID',
`tag_name` varchar(20) not null comment '標簽名',
primary key (`tag_id`)
) engine=innodb;
insert into `tb_tags` (`tag_id`, `tag_name`)
values
(1, '70後'),
(2, '80後'),
(3, '90後'),
(4, '00後'),
(5, '愛運動'),
(6, '高學歷'),
(7, '小資'),
(8, '有房'),
(9, '有車'),
(10, '愛看電影'),
(11, '愛網購'),
(12, '常點外賣');
為用戶打標簽。
create table `tb_users_tags`
(
`user_id` bigint unsigned not null comment '用戶ID',
`user_tags` json not null comment '用戶標簽'
) engine=innodb;
insert into `tb_users_tags` values
(1, '[2, 6, 8, 10]'),
(2, '[3, 10, 12]'),
(3, '[3, 8, 9, 11]');
接下來，我們通過一組查詢來了解 JSON 類型的巧妙之處。
-
查詢愛看電影（有
10這個標簽）的用戶ID。select * from `tb_users` where 10 member of (user_tags->'$'); -
查詢愛看電影（有
10這個標簽）的80後（有2這個標簽）用戶ID。``` select * from
tb_userswhere json_contains(user_tags->’$’, ‘[2, 10]’); -
查詢愛看電影或80後或90後的用戶ID。
select `user_id` from `tb_users_tags` where json_overlaps(user_tags->'$', '[2, 3, 10]');
說明：上面的查詢用到了
member of謂詞和兩個 JSON 函數，json_contains可以檢查 JSON 數組是否包含了指定的元素，而json_overlaps可以檢查 JSON 數組是否與指定的數組有重疊部分。
窗口函數
MySQL 從8.0開始支持窗口函數，大多數商業數據庫和一些開源數據庫早已提供了對窗口函數的支持，有的也將其稱之為 OLAP（聯機分析和處理）函數，聽名字就知道跟統計和分析相關。為了幫助大家理解窗口函數，我們先說說窗口的概念。
窗口可以理解為記錄的集合，窗口函數也就是在滿足某種條件的記錄集合上執行的特殊函數，對於每條記錄都要在此窗口內執行函數。窗口函數和我們上面講到的聚合函數比較容易混淆，二者的區別主要在於聚合函數是將多條記錄聚合為一條記錄，窗口函數是每條記錄都會執行，執行後記錄條數不會變。窗口函數不僅僅是幾個函數，它是一套完整的語法，函數只是該語法的一部分，基本語法如下所示：
<窗口函數> over (partition by <用於分組的列名> order by <用戶排序的列名>)
上面語法中，窗口函數的位置可以放以下兩種函數：
- 專用窗口函數，包括：
lead、lag、first_value、last_value、rank、dense_rank和row_number等。 - 聚合函數，包括：
sum、avg、max、min和count等。
下面為大家舉幾個使用窗口函數的簡單例子，我們先用如下所示的 SQL 建庫建表。
-- 創建名為hrs的數據庫並指定默認的字符集
create database `hrs` default charset utf8mb4;
-- 切換到hrs數據庫
use `hrs`;
-- 創建部門表
create table `tb_dept`
(
`dno` int not null comment '編號',
`dname` varchar(10) not null comment '名稱',
`dloc` varchar(20) not null comment '所在地',
primary key (`dno`)
);
-- 插入4個部門
insert into `tb_dept` values
(10, '會計部', '北京'),
(20, '研發部', '成都'),
(30, '銷售部', '重慶'),
(40, '運維部', '深圳');
-- 創建員工表
create table `tb_emp`
(
`eno` int not null comment '員工編號',
`ename` varchar(20) not null comment '員工姓名',
`job` varchar(20) not null comment '員工職位',
`mgr` int comment '主管編號',
`sal` int not null comment '員工月薪',
`comm` int comment '每月補貼',
`dno` int not null comment '所在部門編號',
primary key (`eno`),
constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`),
constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`)
);
-- 插入14個員工
insert into `tb_emp` values
(7800, '張三豐', '總裁', null, 9000, 1200, 20),
(2056, '喬峰', '分析師', 7800, 5000, 1500, 20),
(3088, '李莫愁', '設計師', 2056, 3500, 800, 20),
(3211, '張無忌', '程序員', 2056, 3200, null, 20),
(3233, '丘處機', '程序員', 2056, 3400, null, 20),
(3251, '張翠山', '程序員', 2056, 4000, null, 20),
(5566, '宋遠橋', '會計師', 7800, 4000, 1000, 10),
(5234, '郭靖', '出納', 5566, 2000, null, 10),
(3344, '黃蓉', '銷售主管', 7800, 3000, 800, 30),
(1359, '胡一刀', '銷售員', 3344, 1800, 200, 30),
(4466, '苗人鳳', '銷售員', 3344, 2500, null, 30),
(3244, '歐陽鋒', '程序員', 3088, 3200, null, 20),
(3577, '楊過', '會計', 5566, 2200, null, 10),
(3588, '朱九真', '會計', 5566, 2500, null, 10);
例子1：查詢按月薪從高到低排在第4到第6名的員工的姓名和月薪。
select * from (
select
`ename`, `sal`,
row_number() over (order by `sal` desc) as `rank`
from `tb_emp`
) `temp` where `rank` between 4 and 6;
說明：上面使用的函數
row_number()可以為每條記錄生成一個行號，在實際工作中可以根據需要將其替換為rank()或dense_rank()函數，三者的區別可以參考官方文檔或閱讀《通俗易懂的學會：SQL窗口函數》進行了解。在MySQL 8以前的版本，我們可以通過下面的方式來完成類似的操作。select `rank`, `ename`, `sal` from ( select @a:=@a+1 as `rank`, `ename`, `sal` from `tb_emp`, (select @a:=0) as t1 order by `sal` desc ) t2 where `rank` between 4 and 6;
例子2：查詢每個部門月薪最高的兩名的員工的姓名和部門名稱。
select `ename`, `sal`, `dname`
from (
select
`ename`, `sal`, `dno`,
rank() over (partition by `dno` order by `sal` desc) as `rank`
from `tb_emp`
) as `temp` natural join `tb_dept` where `rank`<=2;
說明：在MySQL 8以前的版本，我們可以通過下面的方式來完成類似的操作。
select `ename`, `sal`, `dname` from `tb_emp` as `t1` natural join `tb_dept` where ( select count(*) from `tb_emp` as `t2` where `t1`.`dno`=`t2`.`dno` and `t2`.`sal`>`t1`.`sal` )<2 order by `dno` asc, `sal` desc;
其他內容
範式理論
範式理論是設計關系型數據庫中二維表的指導思想。
- 第一範式：數據表的每個列的值域都是由原子值組成的，不能夠再分割。
- 第二範式：數據表里的所有數據都要和該數據表的鍵（主鍵與候選鍵）有完全依賴關系。
- 第三範式：所有非鍵屬性都只和候選鍵有相關性，也就是說非鍵屬性之間應該是獨立無關的。
說明：實際工作中，出於效率的考慮，我們在設計表時很有可能做出反範式設計，即故意降低方式級別，增加冗余數據來獲得更好的操作性能。
數據完整性
-
實體完整性 - 每個實體都是獨一無二的
- 主鍵（
primary key） / 唯一約束（unique）
- 主鍵（
-
引用完整性（參照完整性）- 關系中不允許引用不存在的實體
- 外鍵（
foreign key）
- 外鍵（
-
域（domain）完整性 - 數據是有效的
-
數據類型及長度
-
非空約束（
not null） -
默認值約束（
default） -
檢查約束（
check）說明：在 MySQL 8.x 以前，檢查約束並不起作用。
-
數據一致性
-
事務：一系列對數據庫進行讀/寫的操作，這些操作要麽全都成功，要麽全都失敗。
- 事務的 ACID 特性
- 原子性：事務作為一個整體被執行，包含在其中的對數據庫的操作要麽全部被執行，要麽都不執行
- 一致性：事務應確保數據庫的狀態從一個一致狀態轉變為另一個一致狀態
- 隔離性：多個事務並發執行時，一個事務的執行不應影響其他事務的執行
- 持久性：已被提交的事務對數據庫的修改應該永久保存在數據庫中
-
MySQL 中的事務操作
-
開啟事務環境
start transaction -
提交事務
commit -
回滾事務
rollback
-
-
查看事務隔離級別
show variables like 'transaction_isolation';+-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+可以看出，MySQL 默認的事務隔離級別是
REPEATABLE-READ。 -
修改（當前會話）事務隔離級別
set session transaction isolation level read committed;重新查看事務隔離級別，結果如下所示。
+-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+
關系型數據庫的事務是一個很大的話題，因為當存在多個並發事務訪問數據時，就有可能出現三類讀數據的問題（臟讀、不可重覆讀、幻讀）和兩類更新數據的問題（第一類丟失更新、第二類丟失更新）。想了解這五類問題的，可以閱讀我發布在 CSDN 網站上的《Java面試題全集（上）》一文的第80題。為了避免這些問題，關系型數據庫底層是有對應的鎖機制的，按鎖定對象不同可以分為表級鎖和行級鎖，按並發事務鎖定關系可以分為共享鎖和獨占鎖。然而直接使用鎖是非常麻煩的，為此數據庫為用戶提供了自動鎖機制，只要用戶指定適當的事務隔離級別，數據庫就會通過分析 SQL 語句，然後為事務訪問的資源加上合適的鎖。此外，數據庫還會維護這些鎖通過各種手段提高系統的性能，這些對用戶來說都是透明的。想了解 MySQL 事務和鎖的細節知識，推薦大家閱讀進階讀物《高性能MySQL》，這也是數據庫方面的經典書籍。
ANSI/ISO SQL 92標準定義了4個等級的事務隔離級別，如下表所示。需要說明的是，事務隔離級別和數據訪問的並發性是對立的，事務隔離級別越高並發性就越差。所以要根據具體的應用來確定到底使用哪種事務隔離級別，這個地方沒有萬能的原則。
總結
關於 SQL 和 MySQL 的知識肯定遠遠不止上面列出的這些，比如 SQL 本身的優化、MySQL 性能調優、MySQL 運維相關工具、MySQL 數據的備份和恢覆、監控 MySQL 服務、部署高可用架構等，這一系列的問題在這里都沒有辦法逐一展開來討論，那就留到有需要的時候再進行講解吧，各位讀者也可以自行探索。
Comments