1.SQL 詳解

我們通常可以將 SQL 分為四類，分別是 DDL（數據定義語言）、DML（數據操作語言）、DQL（數據查詢語言）和 DCL（數據控制語言）。DDL 主要用於創建、刪除、修改資料庫中的對象，比如創建、刪除和修改二維表，核心的關鍵字包括create、drop和ALTER；DML 主要負責數據的插入、刪除和更新，關鍵詞包括insert、delete和update；DQL 負責數據查詢，最重要的一個關鍵詞是select；DCL 通常用於授予和召回權限，核心關鍵詞是grant和revoke。

2.DDL（數據定義語言）

2.1創建資料庫

CREATE DATABASE 資料庫名;

示例

2.2刪除資料庫

DROP DATABASE <資料庫名>;

2.3創建數據表

通用語法：

CREATE TABLE table_name (column_name column_type);

示例：在 RUNOOB 資料庫中創建數據表runoob_tbl

CREATE TABLE runoob_tbl(
runoob_id INT NOT NULL AUTO_INCREMENT,
runoob_title VARCHAR(100) NOT NULL,
runoob_authorVARCAHR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY (runoob_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.4刪除資料庫表

通用語法：

DROP TABLE table_name

示例：刪除數據表runoob_tbl

DROP TABLE runoob_tbl

2.5修改資料庫表

示例表數類型如下：

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | CHAR(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

2.5.1刪除表欄位

使用 ALTER 命令及 DROP 子句來刪除以上創建表的 i 欄位：

alter table testalter_tbl drop i;

2.5.2添加表欄位

使用 ADD 子句來向數據表中添加列，如下實例在表 testalter_tbl 中添加 i 欄位，並定義數據類型:

alter table testalter_tbl add i int;

使用 SHOW COLUMNS 查看表結構的變化：

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

2.5.3修改欄位類型及名稱

把欄位 c 的類型從 CHAR(1) 改為 CHAR(10)，可以執行以下命令:

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

指定欄位 j 為 NOT NULL 且默認值為100

alter table testalter_tbl modify j begin not null default 100;

使用 ALTER 來修改欄位的默認值

alter table testalter_tbl alter i set default 1000;

使用 ALTER 命令及 DROP子句來刪除欄位的默認值

alter table testalter_tbl alter i drop default;

SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

2.5.4修改數據表類型

alter table testalter_tbl engine=myisam;

2.5.5修改表名

alter table testalter_tbl rename to alter_tbl;

2.6DDL資料庫定義語句示例

下面我們來實現一個選課系統的資料庫，如下所示的 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 '選課記錄表';

創建資料庫時，我們通過default character set utf8mb4指定了資料庫默認使用的字符集為utf8mb4（最大4字節的utf-8編碼），我們推薦使用該字符集，它也是 MySQL 8.x 默認使用的字符集，因為它能夠支持國際化編碼，還可以存儲 Emoji 字符。可以通過下面的命令查看 MySQL 支持的字符集以及默認的排序規則。

3.DML（數據操作語言）

3.1 insert into 插入數據

語法：

插入單行數據

insert into table_name(field1, field2, field3, ...) values (value1, value2, value3, ...);

插入多行數據

insert into table_name(field1, field2, field3, ...) values (value11, value12, value13, ...), (value21, value12, value23, ...), (value31, value32, value33, ...);

實例：向 runoob_tbl 表插入三條數據

INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("學習 PHP", "菜鳥教程", NOW()), ("學習 MySQL", "菜鳥教程", NOW()), ("JAVA 教程", "RUNOOB.COM", '2016-05-06');

3.2update 更新數據

以下是 UPDATE 命令修改 MySQL 數據表數據的通用 SQL 語法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

實例：更新數據表中 runoob_id 為 3 的 runoob_title 欄位值

update table_tbl set runoob_title='學習 C++' where runoob_id=3;

3.3delete 刪除數據

以下是 SQL DELETE 語句從 MySQL 數據表中刪除數據的通用語法：

DELETE FROM table_name [WHERE Clause];

實例：刪除 runoob_tbl 表中 runoob_id 為3 的記錄：

delete from runoob_tbl where runoob_id=3;

3.4 DML資料庫操作語句示例

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);

4.DQL（數據查詢語言）

4.1DQL資料庫查詢語言示例

-- 查詢所有學生的所有信息
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`;

5.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'@'%';

6.索引

6.1什麼是索引

索引是一種數據結構，會對添加索引的欄位的值進行排序存放，提高查詢效率；一張表中可以添加多個索引；innodb存儲引擎默認使用的是b+tree索引結構，也支持哈希、全文索引。

6.2索引的優缺點　

6.2.1索引的優點

提高資料庫查詢效率
減少鎖等待和死鎖的產生（行鎖是基於索引創建的）
減少主從複製從庫的延遲時間（sql thread回放sql時會應用索引）

6.2.2索引的缺點

索引維護成本高（可通過insert buffer,change buffer提升DML語句效率）
占用更多的存儲空間（磁碟和內存）
索引過多會造成優化器負擔

創建索引

CREATE INDEX indexName ON table_name (column_name);

添加索引

alter table tablename add index indexname(columnName);

創建表的時候直接指定索引

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

刪除索引的語法

drop index [indexname] on mytable;

示例：在學生姓名對應的列上創建索引。

create index idx_student_name on tb_student(stuname);

7.視圖

視圖是關係型資料庫中將一組查詢指令構成的結果集組合成可查詢的數據表的對象。簡單的說，視圖就是虛擬的表，但與數據表不同的是，數據表是一種實體結構，而視圖是一種虛擬結構，你也可以將視圖理解為保存在資料庫中被賦予名字的 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會被覆蓋。
視圖無法使用索引，也不會激發觸發器（實際開發中因為性能等各方面的考慮，通常不建議使用觸發器，所以我們也不對這個概念進行介紹）的執行。

8.存儲過程

存儲過程是事先編譯好存儲在資料庫中的一組 SQL 的集合。存儲過程和函數是事先經過編譯並存儲在資料庫中的一段 SQL 語句的集合，調用存儲過程和函數可以簡化應用開發人員的很多工作，減少數據在資料庫和應用伺服器之間的傳輸，對於提高數據處理的效率是有好處的。

drop procedure if exists sp_score_stat;

delimiter $$

create procedure sp_score_stat(
    in 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 ;將結束符重新改回成分號（恢復現場）。

調用過程。

call sp_score_stat(1111, @a, @b, @c);

獲取輸出參數的值。

select @a as 最高分, @b as 最低分, @c as 平均分;

刪除過程。

drop procedure sp_score_stat;

9.存儲過程和函數好像差不多，你說說他們有什麼區別?

相同點

存儲過程和函數都是為了可重複的執行操作資料庫的 SQL 語句的集合。
存儲過程和函數都是一次編譯後緩存起來，下次使用就直接命中已經編譯好的 sql 語句，減少網絡交互提高了效率。

不同點

標識符不同，函數的標識符是 function，存儲過程是 procedure。
函數返回單個值或者表對象，而存儲過程沒有返回值，但是可以通過 OUT 參數返回多個值。
函數限制比較多，比如不能用臨時表，只能用表變量，一些函數都不可用等，而存儲過程的限制相對就比較少。
一般來說，存儲過程實現的功能要複雜一點，而函數的實現的功能針對性比較強。
函數的參數只能是 IN 類型，存儲過程的參數可以是 IN OUT INOUT 三種類型。
存儲函數使用 select 調用，存儲過程需要使用 call 調用。

10.範式理論

範式理論是設計關係型資料庫中二維表的指導思想。

第一範式：數據表的每個列的值域都是由原子值組成的，不能夠再分割。
第二範式：數據表里的所有數據都要和該數據表的鍵（主鍵與候選鍵）有完全依賴關係。
第三範式：所有非鍵屬性都只和候選鍵有相關性，也就是說非鍵屬性之間應該是獨立無關的。

11.約束

實體完整性 - 每個實體都是獨一無二的
主鍵（primary key） / 唯一約束（unique）
PRIMARY KEY (Id_P);
UNIQUE (Id_P);
引用完整性（參照完整性）- 關係中不允許引用不存在的實體
外鍵（foreign key）
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);
域（domain）完整性 - 數據是有效的
數據類型及長度
非空約束（not null）
默認值約束（default）
檢查約束（check）
算術運算：CHECK (grade<=100)
邏輯運算：CHECK (name in not null)
指定值：CHECK (sex in ('M','F'))
範圍約束：CHECK (age between 15 and 50)

12.事務

12.1事務是什麼

一系列對資料庫進行讀/寫的操作，這些操作要麼全都成功，要麼全都失敗。

12.2事務的 ACID 特性

原子性：事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部被執行，要麼都不執行
一致性：事務應確保資料庫的狀態從一個一致狀態轉變為另一個一致狀態
隔離性：多個事務並發執行時，一個事務的執行不應影響其他事務的執行
持久性：已被提交的事務對資料庫的修改應該永久保存在資料庫中

12.3MySQL 中的事務操作

開啟事務環境
start transaction
提交事務
commit
回滾事務
rollback

12.4事務的隔離級別

當資料庫出現有多個事務同時執行時，就可能出現髒讀，幻讀，不可重複讀等問題，隔離級別就是為了解決這些問題的。隔離的越嚴實，效率就越低，並發越低，安全性越高。

髒讀：指一個事務中訪問到了另外一個事務未提交的數據
幻讀：一個事務讀取2次，得到的記錄條數不一致：
不可重複讀：一個事務讀取同一條記錄2次，得到的結果不一致

隔離級別分為以下 4 種：

讀未提交（read uncommitted，RU）一個事務還未提交時，它做的變更就可以被別的事務看到。
讀提交（read committed，RC）事務提交以後，它做的變更才能被其它事務看到。但是在這個事務未提交之前，資料庫中發生的變更，這個事務也能看見。
可重複度（repeatable read，RR）事務總是只能看見在啟動的那個時刻，資料庫的狀態。事務未提交之前做的變更，其它事務看不見。事務執行期間，資料庫中已經發生的變更，這個事務也看不見。只能看見事務剛啟動時刻，資料庫的狀態。
串行化（serializable）事務對某一行的操作會加鎖，「寫」會加「寫鎖」，「讀」會加「讀鎖」，在鎖釋放掉之前，其它的事務都無法都這一行的記錄進行操作。必須等之前的事務執行完畢，釋放鎖。後面的事務又會重新加鎖。

13.請說明InnoDB和MyISAM的區別

InnoDB支持事務，MyISAM不支持；
InnoDB數據存儲在共享表空間，MyISAM數據存儲在文件中；
InnoDB支持行級鎖，MyISAM只支持表鎖；
InnoDB支持崩潰後的恢復，MyISAM不支持；
InnoDB支持外鍵，MyISAM不支持；
InnoDB不支持全文索引，MyISAM支持全文索引；

Mysql資料庫

1.SQL 詳解

2.DDL（數據定義語言）

2.1創建資料庫

2.2刪除資料庫

2.3創建數據表

2.4刪除資料庫表

2.5修改資料庫表

2.5.1刪除表欄位

2.5.2添加表欄位

2.5.3修改欄位類型及名稱

2.5.4修改數據表類型

2.5.5修改表名

2.6DDL資料庫定義語句示例

3.DML（數據操作語言）

3.1 insert into 插入數據

3.2update 更新數據

3.3delete 刪除數據

3.4 DML資料庫操作語句示例

4.DQL（數據查詢語言）

4.1DQL資料庫查詢語言示例

5.DCL（數據控制語言）

6.索引

6.1什麼是索引

6.2索引的優缺點

7.視圖

8.存儲過程

9.存儲過程和函數好像差不多，你說說他們有什麼區別?

10.範式理論

11.約束

12.事務

12.1事務是什麼

12.2事務的 ACID 特性

12.3MySQL 中的事務操作

12.4事務的隔離級別

13.請說明InnoDB和MyISAM的區別

6.2索引的優缺點　