MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，在 WEB 應(yīng)用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關(guān)系數(shù)據(jù)庫管理系統(tǒng))應(yīng)用軟件之一

介紹

什么是數(shù)據(jù)庫

數(shù)據(jù)庫（Database）是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲(chǔ)和管理數(shù)據(jù)的倉庫。每個(gè)數(shù)據(jù)庫都有一個(gè)或多個(gè)不同的 API 用于創(chuàng)建，訪問，管理，搜索和復(fù)制所保存的數(shù)據(jù)。我們也可以將數(shù)據(jù)存儲(chǔ)在文件中，但是在文件中讀寫數(shù)據(jù)速度相對(duì)較慢。所以，現(xiàn)在我們使用關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS）來存儲(chǔ)和管理大數(shù)據(jù)量。所謂的關(guān)系型數(shù)據(jù)庫，是建立在關(guān)系模型基礎(chǔ)上的數(shù)據(jù)庫，借助于集合代數(shù)等數(shù)學(xué)概念和方法來處理數(shù)據(jù)庫中的數(shù)據(jù)。

MySQL數(shù)據(jù)庫

MySQL 是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典 MySQL AB 公司開發(fā)，目前屬于 Oracle 公司。MySQL 是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)，關(guān)聯(lián)數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉庫內(nèi)，這樣就增加了速度并提高了靈活性。

• MySQL 是開源的，目前隸屬于 Oracle 旗下產(chǎn)品。
• MySQL 支持大型的數(shù)據(jù)庫?？梢蕴幚頁碛猩锨f條記錄的大型數(shù)據(jù)庫。
• MySQL 使用標(biāo)準(zhǔn)的 SQL 數(shù)據(jù)語言形式。
• MySQL 可以運(yùn)行于多個(gè)系統(tǒng)上，并且支持多種語言。這些編程語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 對(duì)PHP有很好的支持，PHP 是目前最流行的 Web 開發(fā)語言。
• MySQL 支持大型數(shù)據(jù)庫，支持 5000 萬條記錄的數(shù)據(jù)倉庫，32 位系統(tǒng)表文件最大可支持 4GB，64 位系統(tǒng)支持最大的表文件為8TB。
• MySQL 是可以定制的，采用了 GPL 協(xié)議，你可以修改源碼來開發(fā)自己的 MySQL 系統(tǒng)。

RDBMS 術(shù)語

在我們開始學(xué)習(xí)MySQL 數(shù)據(jù)庫前，讓我們先了解下RDBMS的一些術(shù)語

• 數(shù)據(jù)庫: 數(shù)據(jù)庫是一些關(guān)聯(lián)表的集合。
• 數(shù)據(jù)表: 表是數(shù)據(jù)的矩陣。在一個(gè)數(shù)據(jù)庫中的表看起來像一個(gè)簡(jiǎn)單的電子表格。
• 列: 一列(數(shù)據(jù)元素) 包含了相同類型的數(shù)據(jù), 例如郵政編碼的數(shù)據(jù)。
• 行：一行（=元組，或記錄）是一組相關(guān)的數(shù)據(jù)，例如一條用戶訂閱的數(shù)據(jù)。
• 冗余：存儲(chǔ)兩倍數(shù)據(jù)，冗余降低了性能，但提高了數(shù)據(jù)的安全性。
• 主鍵：主鍵是唯一的。一個(gè)數(shù)據(jù)表中只能包含一個(gè)主鍵。你可以使用主鍵來查詢數(shù)據(jù)。
• 外鍵：外鍵用于關(guān)聯(lián)兩個(gè)表。
• 復(fù)合鍵：復(fù)合鍵（組合鍵）將多個(gè)列作為一個(gè)索引鍵，一般用于復(fù)合索引。
• 索引：使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。索引是對(duì)數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。類似于書籍的目錄。
• 參照完整性: 參照的完整性要求關(guān)系中不允許引用不存在的實(shí)體。與實(shí)體完整性是關(guān)系模型必須滿足的完整性約束條件，目的是保證數(shù)據(jù)的一致性。

MySQL 為關(guān)系型數(shù)據(jù)庫(Relational Database Management System), 這種所謂的關(guān)系型可以理解為表格的概念, 一個(gè)關(guān)系型數(shù)據(jù)庫由一個(gè)或數(shù)個(gè)表格組成, 如圖所示的一個(gè)表格

數(shù)據(jù)庫表的存儲(chǔ)位置

MySQL數(shù)據(jù)表以文件方式存放在磁盤中：

• 包括表文件、數(shù)據(jù)文件以及數(shù)據(jù)庫的選項(xiàng)文件
• 位置：MySQL安裝目錄data下存放數(shù)據(jù)表。目錄名對(duì)應(yīng)數(shù)據(jù)庫名，該目錄下文件名對(duì)應(yīng)數(shù)據(jù)表

注：

InnoDB類型數(shù)據(jù)表只有一個(gè)*. frm文件，以及上一級(jí)目錄的ibdata1文件
MylSAM類型數(shù)據(jù)表對(duì)應(yīng)三個(gè)文件：

• *. frm —— 表結(jié)構(gòu)定義文件
• *. MYD —— 數(shù)據(jù)文件
• *. MYI —— 索引文件

存儲(chǔ)位置：因操作系統(tǒng)而異，可查my.ini

數(shù)據(jù)類型

MySQL提供的數(shù)據(jù)類型包括數(shù)值類型（整數(shù)類型和小數(shù)類型）、字符串類型、日期類型、復(fù)合類型（復(fù)合類型包括enum類型和set類型）以及二進(jìn)制類型 。

一. 整數(shù)類型

• 整數(shù)類型的數(shù)，默認(rèn)情況下既可以表示正整數(shù)又可以表示負(fù)整數(shù)（此時(shí)稱為有符號(hào)數(shù)）。如果只希望表示零和正整數(shù)，可以使用無符號(hào)關(guān)鍵字“unsigned”對(duì)整數(shù)類型進(jìn)行修飾。
• 各個(gè)類別存儲(chǔ)空間及取值范圍。

二. 小數(shù)類型

• decimal(length, precision)用于表示精度確定（小數(shù)點(diǎn)后數(shù)字的位數(shù)確定）的小數(shù)類型，length決定了該小數(shù)的最大位數(shù)，precision用于設(shè)置精度（小數(shù)點(diǎn)后數(shù)字的位數(shù)）。

• 例如： decimal (5,2)表示小數(shù)取值范圍：999.99～999.99 decimal (5,0)表示： -99999～99999的整數(shù)。

• 各個(gè)類別存儲(chǔ)空間及取值范圍。

三. 字符串

• char()與varchar(): 例如對(duì)于簡(jiǎn)體中文字符集gbk的字符串而言，varchar(255)表示可以存儲(chǔ)255個(gè)漢字，而每個(gè)漢字占用兩個(gè)字節(jié)的存儲(chǔ)空間。假如這個(gè)字符串沒有那么多漢字，例如僅僅包含一個(gè)‘中’字，那么varchar(255)僅僅占用1個(gè)字符（兩個(gè)字節(jié)）的儲(chǔ)存空間；而char(255)則必須占用255個(gè)字符長(zhǎng)度的存儲(chǔ)空間，哪怕里面只存儲(chǔ)一個(gè)漢字。
• 各個(gè)類別存儲(chǔ)空間及取值范圍。

四. 日期類型

1、date表示日期，默認(rèn)格式為‘YYYY-MM-DD’； time表示時(shí)間，格式為‘HH:ii:ss’； year表示年份； datetime與timestamp是日期和時(shí)間的混合類型，格式為’YYYY-MM-DD HH:ii:ss’。

2、datetime與timestamp都是日期和時(shí)間的混合類型，區(qū)別在于： 表示的取值范圍不同，datetime的取值范圍遠(yuǎn)遠(yuǎn)大于timestamp的取值范圍。 將NULL插入timestamp字段后，該字段的值實(shí)際上是MySQL服務(wù)器當(dāng)前的日期和時(shí)間。 同一個(gè)timestamp類型的日期或時(shí)間，不同的時(shí)區(qū)，顯示結(jié)果不同。

3、各個(gè)類別存儲(chǔ)空間及取值范圍。

五. 復(fù)合類型

MySQL 支持兩種復(fù)合數(shù)據(jù)類型：enum枚舉類型和set集合類型。 enum類型的字段類似于單選按鈕的功能，一個(gè)enum類型的數(shù)據(jù)最多可以包含65535個(gè)元素。 set 類型的字段類似于復(fù)選框的功能，一個(gè)set類型的數(shù)據(jù)最多可以包含64個(gè)元素。

六. 二進(jìn)制類型

二進(jìn)制類型的字段主要用于存儲(chǔ)由‘0’和‘1’組成的字符串，因此從某種意義上將，二進(jìn)制類型的數(shù)據(jù)是一種特殊格式的字符串。二進(jìn)制類型與字符串類型的區(qū)別在于：字符串類型的數(shù)據(jù)按字符為單位進(jìn)行存儲(chǔ)，因此存在多種字符集、多種字符序；而二進(jìn)制類型的數(shù)據(jù)按字節(jié)為單位進(jìn)行存儲(chǔ)，僅存在二進(jìn)制字符集binary。

約束

約束是一種限制，它通過對(duì)表的行或列的數(shù)據(jù)做出限制，來確保表的數(shù)據(jù)的完整性、唯一性。

下面文章就來給大家介紹一下6種mysql常見的約束，希望對(duì)大家有所幫助。

一. 非空約束(not null)

• 非空約束用于確保當(dāng)前列的值不為空值，非空約束只能出現(xiàn)在表對(duì)象的列上。
• Null類型特征：所有的類型的值都可以是null，包括int、float 等數(shù)據(jù)類型

二. 唯一性約束(unique)

• 唯一約束是指定table的列或列組合不能重復(fù)，保證數(shù)據(jù)的唯一性。
• 唯一約束不允許出現(xiàn)重復(fù)的值，但是可以為多個(gè)null。
• 同一個(gè)表可以有多個(gè)唯一約束，多個(gè)列組合的約束。
• 在創(chuàng)建唯一約束時(shí)，如果不給唯一約束名稱，就默認(rèn)和列名相同。
• 唯一約束不僅可以在一個(gè)表內(nèi)創(chuàng)建，而且可以同時(shí)多表創(chuàng)建組合唯一約束。

三. 主鍵約束(primary key) PK

• 主鍵約束相當(dāng)于 唯一約束 + 非空約束 的組合，主鍵約束列不允許重復(fù)，也不允許出現(xiàn)空值。

• 每個(gè)表最多只允許一個(gè)主鍵，建立主鍵約束可以在列級(jí)別創(chuàng)建，也可以在表級(jí)別創(chuàng)建。

• 當(dāng)創(chuàng)建主鍵的約束時(shí)，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引。

四. 外鍵約束(foreign key) FK

• 外鍵約束是用來加強(qiáng)兩個(gè)表（主表和從表）的一列或多列數(shù)據(jù)之間的連接的，可以保證一個(gè)或兩個(gè)表之間的參照完整性，外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或是兩個(gè)表的兩個(gè)字段之間的參照關(guān)系。
• 創(chuàng)建外鍵約束的順序是先定義主表的主鍵，然后定義從表的外鍵。也就是說只有主表的主鍵才能被從表用來作為外鍵使用，被約束的從表中的列可以不是主鍵，主表限制了從表更新和插入的操作。

五. 默認(rèn)值約束 (Default)

若在表中定義了默認(rèn)值約束，用戶在插入新的數(shù)據(jù)行時(shí)，如果該行沒有指定數(shù)據(jù)，那么系統(tǒng)將默認(rèn)值賦給該列，如果我們不設(shè)置默認(rèn)值，系統(tǒng)默認(rèn)為NULL。

六. 自增約束(AUTO_INCREMENT)

• 自增約束(AUTO_INCREMENT)可以約束任何一個(gè)字段，該字段不一定是PRIMARY KEY字段，也就是說自增的字段并不等于主鍵字段。
• 但是PRIMARY_KEY約束的主鍵字段，一定是自增字段，即PRIMARY_KEY 要與AUTO_INCREMENT一起作用于同一個(gè)字段。

當(dāng)插入第一條記錄時(shí)，自增字段沒有給定一個(gè)具體值，可以寫成DEFAULT/NULL，那么以后插入字段的時(shí)候，該自增字段就是從1開始，沒插入一條記錄，該自增字段的值增加1。當(dāng)插入第一條記錄時(shí)，給自增字段一個(gè)具體值，那么以后插入的記錄在此自增字段上的值，就在第一條記錄該自增字段的值的基礎(chǔ)上每次增加1。也可以在插入記錄的時(shí)候，不指定自增字段，而是指定其余字段進(jìn)行插入記錄的操作。

常用命令

登錄數(shù)據(jù)庫相關(guān)命令

一. 啟動(dòng)服務(wù)

語法：

mysql> net stop mysql

二. 關(guān)閉服務(wù)

語法：

mysql> net start mysql

三. 鏈接MySQL

語法：mysql -u用戶名 -p密碼;

root@243ecf24bd0a:/ mysql -uroot -p123456;

在以上命令行中，mysql 代表客戶端命令，-u 后面跟連接的數(shù)據(jù)庫用戶，-p 表示需要輸入密碼。如果數(shù)據(jù)庫設(shè)置正常，并輸入正確的密碼，將看到上面一段歡迎界面和一個(gè) mysql>提示符。

四. 退出數(shù)據(jù)庫

語法：quit

mysql> quit

結(jié)果：

DDL（Data Definition Languages）

語句：即數(shù)據(jù)庫定義語句

對(duì)于數(shù)據(jù)庫而言實(shí)際上每一張表都表示是一個(gè)數(shù)據(jù)庫的對(duì)象，而數(shù)據(jù)庫對(duì)象指的就是DDL定義的所有操作，例如：表，視圖，索引，序列，約束等等，都屬于對(duì)象的操作，所以表的建立就是對(duì)象的建立，而對(duì)象的操作主要分為以下三類語法

• 創(chuàng)建對(duì)象：CREATE 對(duì)象名稱;
• 刪除對(duì)象：DROP 對(duì)象名稱;
• 修改對(duì)象：ALTER 對(duì)象名稱;

一. 創(chuàng)建數(shù)據(jù)庫

語法：create database 數(shù)據(jù)庫名字;

mysql> create database sqltest;

結(jié)果：

二. 查看已經(jīng)存在的數(shù)據(jù)庫

語法：show databases;

mysql> show databases;

結(jié)果：

• information_schema：主要存儲(chǔ)了系統(tǒng)中的一些數(shù)據(jù)庫對(duì)象信息。比如用戶表信息、列信息、權(quán)限信息、字符集信息、分區(qū)信息等。
• cluster：存儲(chǔ)了系統(tǒng)的集群信息。
• mysql：存儲(chǔ)了系統(tǒng)的用戶權(quán)限信息。
• test：系統(tǒng)自動(dòng)創(chuàng)建的測(cè)試數(shù)據(jù)庫，任何用戶都可以使用。

三. 選擇數(shù)據(jù)庫

語法：use 數(shù)據(jù)庫名;

mysql> use mzc-test;

返回Database changed代表我們已經(jīng)選擇 sqltest 數(shù)據(jù)庫，后續(xù)所有操作將在 sqltest 數(shù)據(jù)庫上執(zhí)行。

有些人可能會(huì)問到，連接以后怎么退出。其實(shí)，不用退出來，use 數(shù)據(jù)庫后，使用show databases就能查詢所有數(shù)據(jù)庫，如果想跳到其他數(shù)據(jù)庫，用use 其他數(shù)據(jù)庫名字。

四. 查看數(shù)據(jù)庫中的表

語法：show tables;

mysql> show tables;

結(jié)果：

五. 刪除數(shù)據(jù)庫

語法：drop database 數(shù)據(jù)庫名稱;

mysql> drop database mzc-test;

結(jié)果：

注意：刪除時(shí)，最好用 `` 符號(hào)把表明括起來

六. 設(shè)置表的類型

MySQL的數(shù)據(jù)表類型：MyISAM、InnoDB、HEAP、 BOB、CSV等

語法：

CREATE TABLE 表名(
	#省略代碼
）ENGINE= InnoDB;

適用場(chǎng)景：

1. 使用MyISAM：節(jié)約空間及響應(yīng)速度快；不需事務(wù)，空間小，以查詢?cè)L問為主
2. 使用InnoDB：安全性，事務(wù)處理及多用戶操作數(shù)據(jù)表；多刪除、更新操作，安全性高，事務(wù)處理及并發(fā)控制

1. 查看mysql所支持的引擎類型

語法：

SHOW ENGINES

結(jié)果：

2. 查看默認(rèn)引擎

語法：

SHOW VARIABLES LIKE 'storage_engine';

結(jié)果：

數(shù)據(jù)庫表相關(guān)操作

一. 創(chuàng)建表

語法：create table 表名 {列名,數(shù)據(jù)類型,約束條件};

CREATE TABLE `Student`(
	`s_id` VARCHAR(20),
	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(`s_id`)
);

結(jié)果

注意：表名還請(qǐng)遵守?cái)?shù)據(jù)庫的命名規(guī)則，這條數(shù)據(jù)后面要進(jìn)行刪除，所以首字母為大寫。

二. 查看表定義

語法：desc 表名

mysql> desc Student;

結(jié)果：

雖然 desc 命令可以查看表定義，但是其輸出的信息還是不夠全面，為了查看更全面的表定義信息，有時(shí)就需要通過查看創(chuàng)建表的 SQL 語句來得到，可以使用如下命令實(shí)現(xiàn)

語法：show create table 表名 G;

mysql> show create table Student G;

結(jié)果：

從上面表的創(chuàng)建 SQL 語句中，除了可以看到表定義以外，還可以看到表的engine（存儲(chǔ)引擎）和charset（字符集）等信息。G選項(xiàng)的含義是使得記錄能夠按照字段豎著排列，對(duì)于內(nèi)容比較長(zhǎng)的記錄更易于顯示。

三. 刪除表

語法：drop table 表名

mysql> drop table Student;

結(jié)果：

四. 修改表 (重要)

對(duì)于已經(jīng)創(chuàng)建好的表，尤其是已經(jīng)有大量數(shù)據(jù)的表，如果需要對(duì)表做一些結(jié)構(gòu)上的改變，我們可以先將表刪除（drop），然后再按照新的表定義重建表。這樣做沒有問題，但是必然要做一些額外的工作，比如數(shù)據(jù)的重新加載。而且，如果有服務(wù)在訪問表，也會(huì)對(duì)服務(wù)產(chǎn)生影響。因此，在大多數(shù)情況下，表結(jié)構(gòu)的更改一般都使用 alter table語句，以下是一些常用的命令。

1. 修改表類型

語法：ALTER TABLE 表名 MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

例如，修改表 student 的 s_name 字段定義，將 varchar(20)改為 varchar(30)

mysql> alter table Student modify s_name varchar(30);

結(jié)果：

2. 增加表字段

語法：ALTER TABLE 表名 ADD [COLUMN] [FIRST | AFTER col_name];

例如，表 student 上新增加字段 s_test，類型為 int(3)

mysql> alter table student add column s_test int(3);

結(jié)果：

3. 刪除表字段

語法：ALTER TABLE 表名 DROP [COLUMN] col_name

例如，將字段 s_test 刪除掉

mysql> alter table Student drop column s_test;

結(jié)果：

4. 字段改名

語法：ALTER TABLE 表名 CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]

例如，將 s_sex 改名為 s_sex1，同時(shí)修改字段類型為 int(4)

mysql> alter table Student change s_sex s_sex1 int(4);

結(jié)果：

注意：change 和 modify 都可以修改表的定義，不同的是 change 后面需要寫兩次列名，不方便。但是 change 的優(yōu)點(diǎn)是可以修改列名稱，modify 則不能。

5. 修改字段排列順序

前面介紹的的字段增加和修改語法（ADD/CNAHGE/MODIFY）中，都有一個(gè)可選項(xiàng)first|after column_name，這個(gè)選項(xiàng)可以用來修改字段在表中的位置，默認(rèn) ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默認(rèn)都不會(huì)改變字段的位置。

例如，將新增的字段 s_test 加在 s_id 之后

語法：alter table 表名 add 列名 數(shù)據(jù)類型 after 列名;

mysql> alter table Student add s_test date after s_id;

結(jié)果：

修改已有字段 s_name，將它放在最前面

mysql> alter table Student modify s_name varchar(30) default ‘’ first;

結(jié)果：

注意：CHANGE/FIRST|AFTER COLUMN 這些關(guān)鍵字都屬于 MySQL 在標(biāo)準(zhǔn) SQL 上的擴(kuò)展，在其他數(shù)據(jù)庫上不一定適用。

6.表名修改

語法：ALTER TABLE 表名 RENAME [TO] new_tablename

例如，將表 Student 改名為 student

mysql> alter table Student rename student;

結(jié)果：

DML（Data Manipulation Language）

語句：即數(shù)據(jù)操縱語句 用于操作數(shù)據(jù)庫對(duì)象中所包含的數(shù)據(jù)

一. 添加數(shù)據(jù)：INSERT

Insert 語句用于向數(shù)據(jù)庫中插入數(shù)據(jù)

1. 插入單條數(shù)據(jù)（常用）

語法：insert into 表名(列名1,列名2,...) values(值1,值2,...)

特點(diǎn)：

插入值的類型要與列的類型一致或兼容。插入NULL可實(shí)現(xiàn)為列插入NULL值。列的順序可以調(diào)換。列數(shù)和值的個(gè)數(shù)必須一致?？墒÷粤忻?，默認(rèn)所有列，并且列的順序和表中列的順序一致。

案例：

-- 插入學(xué)生表測(cè)試數(shù)據(jù)
insert into Student(s_id,s_name,s_birth,s_sex) values('01' , '趙信' , '1990-01-01' , '男');

2. 插入單條數(shù)據(jù)

語法：INSERT INTO 表名 SET 列名 = 值，列名 = 值

這種方式每次只能插入一行數(shù)據(jù)，每列的值通過賦值列表制定。

案例：

INSERT INTO student SET s_id='02',s_name='德萊厄斯',s_birth='1990-01-01',s_sex='男'

3. 插入多條數(shù)據(jù)

語法：insert into 表名 values(值1,值2,值3),(值4,值5,值6),(值7,值8,值9);

案例：

INSERT INTO student VALUES('03','艾希','1990-01-01','女'),('04','德萊文','1990-08-06','男'),('05','俄洛依','1991-12-01','女');

上面的例子中，值1,值2,值3),(值4,值5,值6),(值7,值8,值9) 即為 Value List，其中每個(gè)括號(hào)內(nèi)部的數(shù)據(jù)表示一行數(shù)據(jù)，這個(gè)例子中插入了三行數(shù)據(jù)。Insert 語句也可以只給部分列插入數(shù)據(jù)，這種情況下，需要在 Value List 之前加上 ColumnName List，

例如：

INSERT INTO student(s_name,s_sex) VALUES('艾希','女'),('德萊文','男');

每行數(shù)據(jù)只指定了 s_name 和 s_sex 這兩列的值，其他列的值會(huì)設(shè)為 Null。

4. 表數(shù)據(jù)復(fù)制

語法：INSERT INTO 表名 SELECT * from 表名;

案例：

INSERT INTO student SELECT * from student1;

注意：

兩個(gè)表的字段需要一直，并盡量保證要新增的表中沒有數(shù)據(jù)

二. 更新數(shù)據(jù)：UPDATE

Update 語句一共有兩種語法，分別用于更新單表數(shù)據(jù)和多表數(shù)據(jù)。

注意：沒有 WHERE 條件的 UPDATE 會(huì)更新所有值！

1. 修改一條數(shù)據(jù)的某個(gè)字段

語法：UPDATE 表名 SET 字段名 =值 where 字段名=值

案例：

UPDATE student SET s_name ='張三' WHERE s_id ='01'

2. 修改多個(gè)字段為同一的值

語法：UPDATE 表名 SET 字段名= 值 WHERE 字段名 in ('值1','值2','值3');

案例：

UPDATE student SET s_name = '李四' WHERE s_id in ('01','02','03');

3. 使用case when實(shí)現(xiàn)批量更新

語法：update 表名 set 字段名 = case 字段名 when 值1 then '值' when 值2 then '值' when 值3 then '值' end where s_id in (值1,值2,值3)

案例：

update student set s_name = case s_id when 01 then '小王' when 02 then '小周' when 03 then '老周' end where s_id in (01,02,03)

這句sql的意思是，更新 s_name 字段，如果 s_id 的值為 01 則 s_name 的值為 小王，s_id = 02 則 s_name = 小周，如果s_id =03 則 s_name 的值為 老周。這里的where部分不影響代碼的執(zhí)行，但是會(huì)提高sql執(zhí)行的效率。確保sql語句僅執(zhí)行需要修改的行數(shù)，這里只有3條數(shù)據(jù)進(jìn)行更新，而where子句確保只有3行數(shù)據(jù)執(zhí)行。

案例 2：

UPDATE student SET s_birth = CASE s_nameWHEN '小王' THEN'2019-01-20'WHEN '小周' THEN'2019-01-22'END WHERE s_name IN ('小王','小周');

三. 刪除數(shù)據(jù)：DELETE

數(shù)據(jù)庫一旦刪除數(shù)據(jù)，它就會(huì)永遠(yuǎn)消失。 因此，在執(zhí)行DELETE語句之前，應(yīng)該先備份數(shù)據(jù)庫，以防萬一要找回刪除過的數(shù)據(jù)。 1. 刪除指定數(shù)據(jù)

語法：DELETE FROM 表名 WHERE 列名=值

注意：刪除的時(shí)候如果不指定where條件，則保留數(shù)據(jù)表結(jié)構(gòu)，刪除全部數(shù)據(jù)行，有主外鍵關(guān)系的都刪不了

案例：

DELETE FROM student WHERE s_id='09'

與 SELECT 語句不同的是，DELETE 語句中不能使用 GROUP BY、 HAVING 和 ORDER BY 三類子句，而只能使用WHERE 子句。

原因很簡(jiǎn)單， GROUP BY 和 HAVING 是從表中選取數(shù)據(jù)時(shí)用來改變抽取數(shù)據(jù)形式的， 而 ORDER BY 是用來指定取得結(jié)果顯示順序的。因此，在刪除表中數(shù)據(jù) 時(shí)它們都起不到什么作用。`

2. 刪除表中全部數(shù)據(jù)

語法：TRUNCATE 表名;

注意：全部刪除，內(nèi)存無痕跡，如果有自增會(huì)重新開始編號(hào)。

與 DELETE 不同的是，TRUNCATE 只能刪除表中的全部數(shù)據(jù)，而不能通過 WHERE 子句指定條件來刪除部分?jǐn)?shù)據(jù)。也正是因?yàn)樗荒芫唧w地控制刪除對(duì)象， 所以其處理速度比 DELETE 要快得多。實(shí)際上，DELETE 語句在 DML 語句中也 屬于處理時(shí)間比較長(zhǎng)的，因此需要?jiǎng)h除全部數(shù)據(jù)行時(shí)，使用 TRUNCATE 可以縮短 執(zhí)行時(shí)間。

案例：

TRUNCATE student1;

DQL（Data Query Language）

語句：即數(shù)據(jù)查詢語句 查詢數(shù)據(jù)庫中的記錄，關(guān)鍵字 SELECT，這塊內(nèi)容非常重要！

一. wherer 條件語句

語法：select 列名 from 表名 where 列名 =值

where的作用：

用于檢索數(shù)據(jù)表中符合條件的記錄搜索條件可由一個(gè)或多個(gè)邏輯表達(dá)式組成，結(jié)果一般為真或假

搜索條件的組成：

算數(shù)運(yùn)算符

邏輯操作符（操作符有兩種寫法）

比較運(yùn)算符

注意：數(shù)值數(shù)據(jù)類型的記錄之間才能進(jìn)行算術(shù)運(yùn)算，相同數(shù)據(jù)類型的數(shù)據(jù)之間才能進(jìn)行比較。

表數(shù)據(jù)

案例 1（AND）：

SELECT  * FROM student WHERE s_name ='小王' AND s_sex='男'

案例 2（OR）：

SELECT  * FROM student WHERE s_name ='崔絲塔娜' OR s_sex='男'

案例 3（NOT）：

SELECT  * FROM student WHERE NOT s_name ='崔絲塔娜' 

案例 4（IS NULL）：

SELECT * FROM student WHERE s_name IS NULL;

案例 5（IS NOT NULL）：

SELECT * FROM student WHERE s_name IS NOT NULL;

案例 6（BETWEEN）：

SELECT * FROM student WHERE s_birth BETWEEN '2019-01-20' AND '2019-01-22'

案例 7（LINK）：

SELECT * FROM student WHERE s_name LIKE '小%'

案例 8（IN）：

SELECT * FROM student WHERE s_name IN ('小王','小周')

二. as 取別名

表里的名字沒有變，只影響了查詢出來的結(jié)果

案例：

SELECT s_name as `name` FROM student 

使用as也可以為表取別名 （作用：?jiǎn)伪聿樵円饬x不大，但是當(dāng)多個(gè)表的時(shí)候取別名就好操作，當(dāng)不同的表里有相同名字的列的時(shí)候區(qū)分就會(huì)好區(qū)分）

三. distinct 去除重復(fù)記錄

注意：當(dāng)查詢結(jié)果中所有字段全都相同時(shí) 才算重復(fù)的記錄

案例

SELECT DISTINCT * FROM student

指定字段

星號(hào)表示所有字段

手動(dòng)指定需要查詢的字段

SELECT DISTINCT s_name,s_birth FROM student

還可也是四則運(yùn)算聚合函數(shù) 四. group by 分組 group by的意思是根據(jù)by對(duì)數(shù)據(jù)按照哪個(gè)字段進(jìn)行分組，或者是哪幾個(gè)字段進(jìn)行分組。

語法：

select 字段名 from 表名 group by 字段名稱;

1. 單個(gè)字段分組

SELECT COUNT(*)FROM student GROUP BY s_sex;

2. 多個(gè)字段分組

SELECT s_name,s_sex,COUNT(*) FROM student GROUP BY s_name,s_sex;

注意：多個(gè)字段進(jìn)行分組時(shí)，需要將s_name和s_sex看成一個(gè)整體，只要是s_name和s_sex相同的可以分成一組；如果只是s_sex相同，s_sex不同就不是一組。 五. having 過濾 HAVING 子句對(duì) GROUP BY 子句設(shè)置條件的方式與 WHERE 和 SELECT 的交互方式類似。WHERE 搜索條件在進(jìn)行分組操作之前應(yīng)用；而 HAVING 搜索條件在進(jìn)行分組操作之后應(yīng)用。HAVING 語法與 WHERE 語法類似，但 HAVING 可以包含聚合函數(shù)。HAVING 子句可以引用選擇列表中顯示的任意項(xiàng)。

我們?nèi)绻樵兡猩蛘吲藬?shù)大于4的性別

SELECT s_sex as 性別,count(s_id) AS 人數(shù) FROM student GROUP BY s_sex HAVING COUNT(s_id)>4

六. order by 排序

根據(jù)某個(gè)字段排序，默認(rèn)升序(從小到大)

語法：

select * from 表名 order by 字段名;

1. 一個(gè)字段，降序（從大到?。?/p>

SELECT * FROM student ORDER BY s_id DESC;

2. 多個(gè)字段

SELECT * FROM student ORDER BY s_id DESC, s_birth ASC;

多個(gè)字段 第一個(gè)相同在按照第二個(gè) asc 表示升序 limit 分頁 用于限制要顯示的記錄數(shù)量

語法1:

select * from table_name limit 個(gè)數(shù);

語法2:

select * from table_name limit 起始位置,個(gè)數(shù);

案例：

查詢前三條數(shù)據(jù)

SELECT * FROM student LIMIT 3;

從第三條開始 查詢3條

SELECT * FROM student LIMIT 2,3;

注意：起始位置 從0開始

經(jīng)典的使用場(chǎng)景:分頁顯示

每一頁顯示的條數(shù) a = 3明確當(dāng)前頁數(shù) b = 2計(jì)算起始位置 c = (b-1) * a 子查詢

將一個(gè)查詢語句的結(jié)果作為另一個(gè)查詢語句的條件或是數(shù)據(jù)來源， 當(dāng)我們一次性查不到想要數(shù)據(jù)時(shí)就需要使用子查詢。

SELECT
*
FROM
score
WHERE
s_id =(
SELECT
s_id
FROM
student
WHERE
s_name = ‘趙信')

1. in 關(guān)鍵字子查詢 當(dāng)內(nèi)層查詢 (括號(hào)內(nèi)的) 結(jié)果會(huì)有多個(gè)結(jié)果時(shí), 不能使用 = 必須是in ,另外子查詢必須只能包含一列數(shù)據(jù)

子查詢的思路:

要分析 查到最終的數(shù)據(jù) 到底有哪些步驟

根據(jù)步驟寫出對(duì)應(yīng)的sql語句

把上一個(gè)步驟的sql語句丟到下一個(gè)sql語句中作為條件

SELECT
*
FROM
score
WHERE
s_id IN (
SELECT
s_id
FROM
student
WHERE
s_sex = ‘男')

exists 關(guān)鍵字子查詢 當(dāng)內(nèi)層查詢 有結(jié)果時(shí) 外層才會(huì)執(zhí)行 多表查詢 1. 笛卡爾積查詢 笛卡爾積查詢的結(jié)果會(huì)出現(xiàn)大量的錯(cuò)誤數(shù)據(jù)即,數(shù)據(jù)關(guān)聯(lián)關(guān)系錯(cuò)誤,并且會(huì)產(chǎn)生重復(fù)的字段信息 ! 2. 內(nèi)連接查詢 本質(zhì)上就是笛卡爾積查詢，inner可以省略。

語法:

select * from  表1 inner join 表2;

3. 左外連接查詢 左邊的表無論是否能夠匹配都要完整顯示，右邊的僅展示匹配上的記錄

注意: 在外連接查詢中不能使用where 關(guān)鍵字 必須使用on專門來做表的對(duì)應(yīng)關(guān)系

4. 右外連接查詢 右邊的表無論是否能夠匹配都要完整顯示，左邊的僅展示匹配上的記錄

DCL（Data Control Language）

語句：即數(shù)據(jù)控制語句 DCL(Data Control Language)語句：數(shù)據(jù)控制語句，用于控制不同數(shù)據(jù)段直接的許可和訪問級(jí)別的語句。這些語句定義了數(shù)據(jù)庫、表、字段、用戶的訪問權(quán)限和安全級(jí)別。

關(guān)鍵字

• GRANT
• REVOKE

查看用戶權(quán)限

當(dāng)成功創(chuàng)建用戶賬戶后，還不能執(zhí)行任何操作，需要為該用戶分配適當(dāng)?shù)脑L問權(quán)限。

可以使用SHOW GRANTS FOR語句來查詢用戶的權(quán)限。

例如：

mysql> SHOW GRANTS FOR test;
+-------------------------------------------+
| Grants for test@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

GRANT語句 對(duì)于新建的MySQL用戶，必須給它授權(quán)，可以用GRANT語句來實(shí)現(xiàn)對(duì)新建用戶的授權(quán)。 格式語法

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]
object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}
priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}
user:
    (see Section 6.2.4, “Specifying Account Names”)
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}
tls_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}
resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

權(quán)限類型(priv_type) 授權(quán)的權(quán)限類型一般可以分為數(shù)據(jù)庫、表、列、用戶。 授予數(shù)據(jù)庫權(quán)限類型

授予數(shù)據(jù)庫權(quán)限時(shí)，priv_type可以指定為以下值：

• SELECT：表示授予用戶可以使用 SELECT 語句訪問特定數(shù)據(jù)庫中所有表和視圖的權(quán)限。
• INSERT：表示授予用戶可以使用 INSERT 語句向特定數(shù)據(jù)庫中所有表添加數(shù)據(jù)行的權(quán)限。
• DELETE：表示授予用戶可以使用 DELETE 語句刪除特定數(shù)據(jù)庫中所有表的數(shù)據(jù)行的權(quán)限。
• UPDATE：表示授予用戶可以使用 UPDATE 語句更新特定數(shù)據(jù)庫中所有數(shù)據(jù)表的值的權(quán)限。
• REFERENCES：表示授予用戶可以創(chuàng)建指向特定的數(shù)據(jù)庫中的表外鍵的權(quán)限。
• CREATE：表示授權(quán)用戶可以使用 CREATE TABLE 語句在特定數(shù)據(jù)庫中創(chuàng)建新表的權(quán)限。
• ALTER：表示授予用戶可以使用 ALTER TABLE 語句修改特定數(shù)據(jù)庫中所有數(shù)據(jù)表的權(quán)限。
• SHOW VIEW：表示授予用戶可以查看特定數(shù)據(jù)庫中已有視圖的視圖定義的權(quán)限。
• CREATE ROUTINE：表示授予用戶可以為特定的數(shù)據(jù)庫創(chuàng)建存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• ALTER ROUTINE：表示授予用戶可以更新和刪除數(shù)據(jù)庫中已有的存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• INDEX：表示授予用戶可以在特定數(shù)據(jù)庫中的所有數(shù)據(jù)表上定義和刪除索引的權(quán)限。
• DROP：表示授予用戶可以刪除特定數(shù)據(jù)庫中所有表和視圖的權(quán)限。
• CREATE TEMPORARY TABLES：表示授予用戶可以在特定數(shù)據(jù)庫中創(chuàng)建臨時(shí)表的權(quán)限。
• CREATE VIEW：表示授予用戶可以在特定數(shù)據(jù)庫中創(chuàng)建新的視圖的權(quán)限。
• EXECUTE ROUTINE：表示授予用戶可以調(diào)用特定數(shù)據(jù)庫的存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• LOCK TABLES：表示授予用戶可以鎖定特定數(shù)據(jù)庫的已有數(shù)據(jù)表的權(quán)限。
• SHOW DATABASES：表示授權(quán)可以使用SHOW DATABASES語句查看所有已有的數(shù)據(jù)庫的定義的權(quán)限。
• ALL或ALL PRIVILEGES：表示以上所有權(quán)限。

授予表權(quán)限時(shí)，priv_type可以指定為以下值：

• SELECT：授予用戶可以使用 SELECT 語句進(jìn)行訪問特定表的權(quán)限。
• INSERT：授予用戶可以使用 INSERT 語句向一個(gè)特定表中添加數(shù)據(jù)行的權(quán)限。
• DELETE：授予用戶可以使用 DELETE 語句從一個(gè)特定表中刪除數(shù)據(jù)行的權(quán)限。
• DROP：授予用戶可以刪除數(shù)據(jù)表的權(quán)限。
• UPDATE：授予用戶可以使用 UPDATE 語句更新特定數(shù)據(jù)表的權(quán)限。
• ALTER：授予用戶可以使用 ALTER TABLE 語句修改數(shù)據(jù)表的權(quán)限。
• REFERENCES：授予用戶可以創(chuàng)建一個(gè)外鍵來參照特定數(shù)據(jù)表的權(quán)限。
• CREATE：授予用戶可以使用特定的名字創(chuàng)建一個(gè)數(shù)據(jù)表的權(quán)限。
• INDEX：授予用戶可以在表上定義索引的權(quán)限。
• ALL或ALL PRIVILEGES：所有的權(quán)限名

授予列(字段)權(quán)限類型

授予列(字段)權(quán)限時(shí)，priv_type的值只能指定為SELECT、INSERT和UPDATE，同時(shí)權(quán)限的后面需要加上列名列表(column-list)。

授予創(chuàng)建和刪除用戶的權(quán)限

授予列(字段)權(quán)限時(shí)，priv_type的值指定為CREATE USER權(quán)限，具備創(chuàng)建用戶、刪除用戶、重命名用戶和撤消所有特權(quán)，而且是全局的。

ON

有ON，是授予權(quán)限，無ON，是授予角色。如：

– 授予數(shù)據(jù)庫db1的所有權(quán)限給指定賬戶
GRANT ALL ON db1.* TO ‘user1’@‘localhost’;
– 授予角色給指定的賬戶
GRANT ‘role1’, ‘role2’ TO ‘user1’@‘localhost’, ‘user2’@‘localhost’;

對(duì)象類型(object_type)

在ON關(guān)鍵字后給出要授予權(quán)限的object_type，通常object_type可以是數(shù)據(jù)庫名、表名等。

權(quán)限級(jí)別(priv_level)

指定權(quán)限級(jí)別的值有以下幾類格式：

• *：表示當(dāng)前數(shù)據(jù)庫中的所有表。
• .：表示所有數(shù)據(jù)庫中的所有表。
• db_name.*：表示某個(gè)數(shù)據(jù)庫中的所有表，db_name指定數(shù)據(jù)庫名。
• db_name.tbl_name：表示某個(gè)數(shù)據(jù)庫中的某個(gè)表或視圖，db_name指定數(shù)據(jù)庫名，tbl_name指定表名或視圖名。
• tbl_name：表示某個(gè)表或視圖，tbl_name指定表名或視圖名。
• db_name.routine_name：表示某個(gè)數(shù)據(jù)庫中的某個(gè)存儲(chǔ)過程或函數(shù)，routine_name指定存儲(chǔ)過程名或函數(shù)名。

被授權(quán)的用戶(user)

'user_name'@'host_name'

Tips：'host_name’用于適應(yīng)從任意主機(jī)訪問數(shù)據(jù)庫而設(shè)置的，可以指定某個(gè)地址或地址段訪問?？梢酝瑫r(shí)授權(quán)多個(gè)用戶。

user表中host列的默認(rèn)值

host

說明

%

匹配所有主機(jī)

localhost

localhost不會(huì)被解析成IP地址，直接通過UNIXsocket連接

127.0.0.1

會(huì)通過TCP/IP協(xié)議連接，并且只能在本機(jī)訪問

::1

::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

host_name格式有以下幾種：

• 使用%模糊匹配，符合匹配條件的主機(jī)可以訪問該數(shù)據(jù)庫實(shí)例，例如192.168.2.%或%.test.com；
• 使用localhost、127.0.0.1、::1及服務(wù)器名等，只能在本機(jī)訪問；
• 使用ip地址或地址段形式，僅允許該ip或ip地址段的主機(jī)訪問該數(shù)據(jù)庫實(shí)例，例如192.168.2.1或192.168.2.0/24或192.168.2.0/255.255.255.0；
• 省略即默認(rèn)為%。

身份驗(yàn)證方式(auth_option)

auth_option為可選字段，可以指定密碼以及認(rèn)證插件(mysql_native_password、sha256_password、caching_sha2_password)。

加密連接(tls_option)

tls_option為可選的，一般是用來加密連接。

用戶資源限制(resource_option)

resource_option為可選的，一般是用來指定最大連接數(shù)等。

參數(shù)

說明

MAX_QUERIES_PER_HOUR count

每小時(shí)最大查詢數(shù)

MAX_UPDATES_PER_HOUR count

每小時(shí)最大更新數(shù)

MAX_CONNECTIONS_PER_HOUR count

每小時(shí)連接次數(shù)

MAX_USER_CONNECTIONS count

用戶最大連接數(shù)

權(quán)限生效

若要權(quán)限生效，需要執(zhí)行以下語句：

FLUSH PRIVILEGES;

REVOKE語句

REVOKE語句主要用于撤銷權(quán)限。

語法格式

REVOKE語法和GRANT語句的語法格式相似，但具有相反的效果

REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type] priv_level
FROM user [, user] …

REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM user [, user] …

REVOKE PROXY ON user
FROM user [, user] …

• 若要使用REVOKE語句，必須擁有MySQL數(shù)據(jù)庫的全局CREATE USER權(quán)限或UPDATE權(quán)限;
• 第一種語法格式用于回收指定用戶的某些特定的權(quán)限，第二種回收指定用戶的所有權(quán)限；

TCL（Transaction Control Language）

語句：事務(wù)控制語句

什么是事物？

一個(gè)或一組sql語句組成一個(gè)執(zhí)行單元，這個(gè)執(zhí)行單元要么全部執(zhí)行，要么全部不執(zhí)行

事務(wù)的ACID屬性

• 原子性：事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生

• 一致性：事務(wù)必須使數(shù)據(jù)庫從一個(gè)一致性狀態(tài)變換到另外一個(gè)一致性狀態(tài)

• 隔離性：一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾，即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾

• 持久性：一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，接下來的其他操作和數(shù)據(jù)庫故障不應(yīng)該對(duì)其有任何影響

分類

• 隱式事務(wù)：事務(wù)沒有明顯的開啟和結(jié)束的標(biāo)記(比如insert，update，delete語句)

• 顯式事務(wù)：事務(wù)具有明顯的開啟和結(jié)束的標(biāo)記(autocommit變量設(shè)置為0)

事務(wù)的使用步驟

開啟事務(wù)

默認(rèn)開啟事務(wù)

SET autocommit = 0 ;

提交事務(wù)

COMMIT;

回滾事務(wù)

ROLLBACK ;

查看當(dāng)前的事務(wù)隔離級(jí)別

select @@tx_isolation;

設(shè)置當(dāng)前連接事務(wù)的隔離級(jí)別

set session transaction isolation level read uncommitted;

設(shè)置數(shù)據(jù)庫系統(tǒng)的全局的隔離級(jí)別

set global transaction isolation level read committed ;

常用函數(shù)

MySQL提供了眾多功能強(qiáng)大、方便易用的函數(shù)，使用這些函數(shù)，可以極大地提高用戶對(duì)于數(shù)據(jù)庫的管理效率，從而更加靈活地滿足不同用戶的需求。本文將MySQL的函數(shù)分類并匯總，以便以后用到的時(shí)候可以隨時(shí)查看。

(這里使用 Navicat Premium 15 工具進(jìn)行演示)

因?yàn)閮?nèi)容太多了這里只演示一些常用的

一. 數(shù)學(xué)函數(shù)

對(duì)數(shù)值型的數(shù)據(jù)進(jìn)行指定的數(shù)學(xué)運(yùn)算，如abs()函數(shù)可以獲得給定數(shù)值的絕對(duì)值，round()函數(shù)可以對(duì)給定的數(shù)值進(jìn)行四舍五入。

1. ABS(number)

作用：返回 number 的絕對(duì)值

SELECT
ABS(s_score)
FROM
score;

ABS(-86) 返回：86

number 參數(shù)可以是任意有效的數(shù)值表達(dá)式。如果 number 包含 Null，則返回 Null；如果是未初始化變量，則返回 0。

2. PI()

例1：pi() 返回：3.141592653589793

例2：pi(2) 返回：6.283185307179586

作用：計(jì)算圓周率及其倍數(shù)

3. SQRT(x) 作用：返回非負(fù)數(shù)的x的二次方根 4. MOD(x,y) 作用：返回x被y除后的余數(shù) 5. CEIL(x)、CEILING(x) 作用：返回不小于x的最小整數(shù) 6. FLOOR(x) 作用：返回不大于x的最大整數(shù) 7. FLOOR(x) 作用：返回不大于x的最大整數(shù) 8. ROUND(x)、ROUND(x,y)

作用：前者返回最接近于x的整數(shù)，即對(duì)x進(jìn)行四舍五入；后者返回最接近x的數(shù)，其值保留到小數(shù)點(diǎn)后面y位，若y為負(fù)值，則將保留到x到小數(shù)點(diǎn)左邊y位

SELECT ROUND(345222.9)

參數(shù)說明： numberExp 需要進(jìn)行截取的數(shù)據(jù) nExp 整數(shù)，用于指定需要進(jìn)行截取的位置，>0：從小數(shù)點(diǎn)往右位移nExp個(gè)位數(shù)， <0：從小數(shù)點(diǎn)往左

nExp個(gè)位數(shù) =0：表示當(dāng)前小數(shù)點(diǎn)的位置

9. POW(x,y)和、POWER(x,y) 作用：返回x的y次乘方的值 10. EXP(x) 作用：返回e的x乘方后的值 11. LOG(x) 作用：返回x的自然對(duì)數(shù)，x相對(duì)于基數(shù)e的對(duì)數(shù) 12. LOG10(x) 作用：返回x的基數(shù)為10的對(duì)數(shù) 13. RADIANS(x) 作用：返回x由角度轉(zhuǎn)化為弧度的值 14. DEGREES(x) 作用：返回x由弧度轉(zhuǎn)化為角度的值 15. SIN(x)、ASIN(x) 作用：前者返回x的正弦，其中x為給定的弧度值；后者返回x的反正弦值，x為正弦 16. COS(x)、ACOS(x) 作用：前者返回x的余弦，其中x為給定的弧度值；后者返回x的反余弦值，x為余弦 17. TAN(x)、ATAN(x) 作用：前者返回x的正切，其中x為給定的弧度值；后者返回x的反正切值，x為正切 18. COT(x) 作用：返回給定弧度值x的余切

二. 字符串函數(shù)

1. CHAR_LENGTH(str)

作用：計(jì)算字符串字符個(gè)數(shù)

SELECT CHAR_LENGTH(‘這是一個(gè)十二個(gè)字的字符串’);

2. CONCAT(s1,s2，…)

作用：返回連接參數(shù)產(chǎn)生的字符串，一個(gè)或多個(gè)待拼接的內(nèi)容，任意一個(gè)為NULL則返回值為NULL

SELECT CONCAT(‘拼接’,‘測(cè)試’);

3. CONCAT_WS(x,s1,s2,…)

作用：返回多個(gè)字符串拼接之后的字符串，每個(gè)字符串之間有一個(gè)x

SELECT CONCAT_WS(‘-’,‘測(cè)試’,‘拼接’,‘WS’)

4. INSERT(s1,x,len,s2)

作用：返回字符串s1，其子字符串起始于位置x，被字符串s2取代len個(gè)字符

SELECT INSERT(‘測(cè)試字符串替換’,2,1,‘牛’);

5. LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

作用：前兩者將str中的字母全部轉(zhuǎn)換成小寫，后兩者將字符串中的字母全部轉(zhuǎn)換成大寫

SELECT LOWER(‘JHGYTUGHJGG’),LCASE(‘HKJHKJHKJHKJ’);

SELECT UPPER('aaaaaa'),UCASE('vvvvv');

6. LEFT(s,n)、RIGHT(s,n)

作用：前者返回字符串s從最左邊開始的n個(gè)字符，后者返回字符串s從最右邊開始的n個(gè)字符

SELECT LEFT(‘左邊開始’,2),RIGHT(‘右邊開始’,2);

7. LPAD(s1,len,s2)、RPAD(s1,len,s2)

作用：前者返回s1，其左邊由字符串s2填補(bǔ)到len字符長(zhǎng)度，假如s1的長(zhǎng)度大于len，則返回值被縮短至len字符；前者返回s1，其右邊由字符串s2填補(bǔ)到len字符長(zhǎng)度，假如s1的長(zhǎng)度大于len，則返回值被縮短至len字符

SELECT LEFT(‘左邊開始’,2),RIGHT(‘右邊開始’,2);

8. LTRIM(s)、RTRIM(s)

作用：前者返回字符串s，其左邊所有空格被刪除；后者返回字符串s，其右邊所有空格被刪除

SELECT LTRIM(’ 左邊開始’),RTRIM(’ 右邊開始 ');

9. TRIM(s)

作用：返回字符串s刪除了兩邊空格之后的字符串

SELECT TRIM(’ 是是 ');

10. TRIM(s1 FROM s) 作用：刪除字符串s兩端所有子字符串s1，未指定s1的情況下則默認(rèn)刪除空格 11. REPEAT(s,n)

作用：返回一個(gè)由重復(fù)字符串s組成的字符串，字符串s的數(shù)目等于n

SELECT REPEAT(‘測(cè)試’,5);

12. SPACE(n)

作用：返回一個(gè)由n個(gè)空格組成的字符串

SELECT SPACE(20);

13. REPLACE(s,s1,s2) 作用：返回一個(gè)字符串，用字符串s2替代字符串s中所有的字符串s1 14. STRCMP(s1,s2)

作用：若s1和s2中所有的字符串都相同，則返回0；根據(jù)當(dāng)前分類次序，第一個(gè)參數(shù)小于第二個(gè)則返回-1，其他情況返回1

SELECT STRCMP(‘我我我’,‘我我我’);

SELECT STRCMP('我我我','是是是');

15. SUBSTRING(s,n,len)、MID(s,n,len)

作用：兩個(gè)函數(shù)作用相同，從字符串s中返回一個(gè)第n個(gè)字符開始、長(zhǎng)度為len的字符串

SELECT SUBSTRING(‘測(cè)試測(cè)試’,2,2);

SELECT MID('測(cè)試測(cè)試',2,2);

16. LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

作用：三個(gè)函數(shù)作用相同，返回子字符串str1在字符串str中的開始位置（從第幾個(gè)字符開始）

SELECT LOCATE(‘字’,‘獲取字符串的位置’);

17. REVERSE(s)

作用：將字符串s反轉(zhuǎn)

SELECT REVERSE(‘字符串反轉(zhuǎn)’);

18. ELT(N,str1,str2,str3,str4,…)

作用：返回第N個(gè)字符串

SELECT ELT(2,‘字符串反轉(zhuǎn)’,‘sssss’);

三. 日期和時(shí)間函數(shù)

當(dāng)前時(shí)間

1. CURDATE()、CURRENT_DATE() 作用：將當(dāng)前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語境中而定 2. CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

作用：這四個(gè)函數(shù)作用相同，返回當(dāng)前日期和時(shí)間值，格式為"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具體格式根據(jù)函數(shù)用在字符串或數(shù)字語境中而定

SELECT CURRENT_TIMESTAMP()

SELECT LOCALTIME()

SELECT NOW()

SELECT SYSDATE()

3. UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

作用：前者返回一個(gè)格林尼治標(biāo)準(zhǔn)時(shí)間1970-01-01 00:00:00到現(xiàn)在的秒數(shù)，后者返回一個(gè)格林尼治標(biāo)準(zhǔn)時(shí)間1970-01-01 00:00:00到指定時(shí)間的秒數(shù)

SELECT UNIX_TIMESTAMP()

4. FROM_UNIXTIME(date)

作用：和UNIX_TIMESTAMP互為反函數(shù)，把UNIX時(shí)間戳轉(zhuǎn)換為普通格式的時(shí)間 5. UTC_DATE()和UTC_TIME()

前者返回當(dāng)前UTC（世界標(biāo)準(zhǔn)時(shí)間）日期值，其格式為"YYYY-MM-DD"或"YYYYMMDD"，后者返回當(dāng)前UTC時(shí)間值，其格式為"YYYY-MM-DD"或"YYYYMMDD"。具體使用哪種取決于函數(shù)用在字符串還是數(shù)字語境中

SELECT UTC_DATE()

SELECT UTC_TIME()

6. MONTH(date)和MONTHNAME(date)

作用：前者返回指定日期中的月份，后者返回指定日期中的月份的名稱

SELECT MONTH(NOW())

SELECT MONTHNAME(NOW())

7. DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

作用：DAYNAME(d)返回d對(duì)應(yīng)的工作日的英文名稱，如Sunday、Monday等；DAYOFWEEK(d)返回的對(duì)應(yīng)一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d對(duì)應(yīng)的工作日索引，0表示周一，1表示周二 8. WEEK(d)

計(jì)算日期d是一年中的第幾周

SELECT WEEK(NOW())

9. DAYOFYEAR(d)、DAYOFMONTH(d)

作用：前者返回d是一年中的第幾天，后者返回d是一月中的第幾天

SELECT DAYOFYEAR(NOW())

SELECT DAYOFMONTH(NOW())

10. YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

作用： YEAR(date)返回指定日期對(duì)應(yīng)的年份，范圍是1970~2069；QUARTER(date)返回date對(duì)應(yīng)一年中的季度，范圍是1~4；MINUTE(time)返回time對(duì)應(yīng)的分鐘數(shù)，范圍是0~59；SECOND(time)返回制定時(shí)間的秒值

SELECT YEAR(NOW())

SELECT QUARTER(NOW())

SELECT MINUTE(NOW())

SELECT SECOND(NOW())

11. EXTRACE(type FROM date)

作用：從日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND 12. TIME_TO_SEC(time)

作用：返回以轉(zhuǎn)換為秒的time參數(shù)，轉(zhuǎn)換公式為"3600_小時(shí) + 60_分鐘 + 秒"

SELECT TIME_TO_SEC(NOW())

13. SEC_TO_TIME()

作用：和TIME_TO_SEC(time)互為反函數(shù)，將秒值轉(zhuǎn)換為時(shí)間格式

SELECT SEC_TO_TIME(530)

14. DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

作用：返回將起始時(shí)間加上expr type之后的時(shí)間，比如DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)表示的就是把第一個(gè)時(shí)間加1秒

15. DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

作用：返回將起始時(shí)間減去expr type之后的時(shí)間

16. ADDTIME(date,expr)、SUBTIME(date,expr)

作用：前者進(jìn)行date的時(shí)間加操作，后者進(jìn)行date的時(shí)間減操作

四. 條件判斷函數(shù)

1. IF(expr,v1,v2)

作用：如果expr是TRUE則返回v1，否則返回v2

2. IFNULL(v1,v2)

作用：如果v1不為NULL，則返回v1，否則返回v2

3. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

作用：如果expr等于某個(gè)vn，則返回對(duì)應(yīng)位置THEN后面的結(jié)果，如果與所有值都不想等，則返回ELSE后面的rn

五. 系統(tǒng)信息函數(shù)

1. VERSION()

作用：查看MySQL版本號(hào)

SELECT VERSION()

2. CONNECTION_ID()

作用：查看當(dāng)前用戶的連接數(shù)

SELECT CONNECTION_ID()

3. USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

作用：查看當(dāng)前被MySQL服務(wù)器驗(yàn)證的用戶名和主機(jī)的組合，一般這幾個(gè)函數(shù)的返回值是相同的

SELECT USER()

SELECT CURRENT_USER()

SELECT SYSTEM_USER()

SELECT SESSION_USER()

4. CHARSET(str)

作用：查看字符串str使用的字符集

SELECT CHARSET(555)

5. COLLATION()

作用：查看字符串排列方式

SELECT COLLATION(‘sssfddsfds')

六. 加密函數(shù)

1. PASSWORD(str)

作用：從原明文密碼str計(jì)算并返回加密后的字符串密碼，注意這個(gè)函數(shù)的加密是單向的（不可逆），因此不應(yīng)將它應(yīng)用在個(gè)人的應(yīng)用程序中而應(yīng)該只在MySQL服務(wù)器的鑒定系統(tǒng)中使用

SELECT PASSWORD(‘mima’)

2. MD5(str)

作用：為字符串算出一個(gè)MD5 128比特校驗(yàn)和，改值以32位十六進(jìn)制數(shù)字的二進(jìn)制字符串形式返回

SELECT MD5(‘mima’)

3. ENCODE(str, pswd_str)

作用：使用pswd_str作為密碼，加密str

SELECT ENCODE(‘fdfdz’,‘mima’)

4. DECODE(crypt_str,pswd_str)

作用：使用pswd_str作為密碼，解密加密字符串crypt_str，crypt_str是由ENCODE函數(shù)返回的字符串

SELECT DECODE(‘fdfdz’,‘mima’)

七. 其他函數(shù)

1. FORMAT(x,n)

作用：將數(shù)字x格式化，并以四舍五入的方式保留小數(shù)點(diǎn)后n位，結(jié)果以字符串形式返回

SELECT FORMAT(446.454,2)

2. CONV(N,from_base,to_base)

作用：不同進(jìn)制數(shù)之間的轉(zhuǎn)換，返回值為數(shù)值N的字符串表示，由from_base進(jìn)制轉(zhuǎn)換為to_base進(jìn)制

3. INET_ATON(expr)

作用：給出一個(gè)作為字符串的網(wǎng)絡(luò)地址的點(diǎn)地址表示，返回一個(gè)代表該地址數(shù)值的整數(shù)，地址可以使4或8比特

4. INET_NTOA(expr)

作用：給定一個(gè)數(shù)字網(wǎng)絡(luò)地址（4或8比特），返回作為字符串的該地址的點(diǎn)地址表示

5. BENCHMARK(count,expr)

作用：重復(fù)執(zhí)行count次表達(dá)式expr，它可以用于計(jì)算MySQL處理表達(dá)式的速度，結(jié)果值通常是0（0只是表示很快，并不是沒有速度）。另一個(gè)作用是用它在MySQL客戶端內(nèi)部報(bào)告語句執(zhí)行的時(shí)間

6. CONVERT(str USING charset)

作用：使用字符集charset表示字符串str

更多用法還請(qǐng)參考：http://www.geezn.com/documents/gez/help/117555-1355219868404378.html

SQL實(shí)戰(zhàn)練習(xí)

題目來自互聯(lián)網(wǎng)，建議每道題都在本地敲一遍鞏固記憶 ！ 創(chuàng)建數(shù)據(jù)庫

創(chuàng)建表（并初始化數(shù)據(jù)）

-- 學(xué)生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 課程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教師表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成績(jī)表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入學(xué)生表測(cè)試數(shù)據(jù)
insert into student values('01' , '趙信' , '1990-01-01' , '男');
insert into student values('02' , '德萊厄斯' , '1990-12-21' , '男');
insert into student values('03' , '艾希' , '1990-05-20' , '男');
insert into student values('04' , '德萊文' , '1990-08-06' , '男');
insert into student values('05' , '俄洛依' , '1991-12-01' , '女');
insert into student values('06' , '光輝女郎' , '1992-03-01' , '女');
insert into student values('07' , '崔絲塔娜' , '1989-07-01' , '女');
insert into student values('08' , '安妮' , '1990-01-20' , '女');
-- 課程表測(cè)試數(shù)據(jù)
insert into course values('01' , '語文' , '02');
insert into course values('02' , '數(shù)學(xué)' , '01');
insert into course values('03' , '英語' , '03');
-- 教師表測(cè)試數(shù)據(jù)
insert into teacher values('01' , '死亡歌頌者');
insert into teacher values('02' , '流浪法師');
insert into teacher values('03' , '邪惡小法師');
-- 成績(jī)表測(cè)試數(shù)據(jù)
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);

表結(jié)構(gòu) 這里建的表主要用于sql語句的練習(xí)，所以并沒有遵守一些規(guī)范。下面讓我們來看看相關(guān)的表結(jié)構(gòu)吧

學(xué)生表（student）

s_id = 學(xué)生編號(hào)，s_name = 學(xué)生姓名，s_birth = 出生年月，s_sex = 學(xué)生性別

課程表（course）

c_id = 課程編號(hào)，c_name = 課程名稱，t_id = 教師編號(hào)

教師表（teacher）

t_id = 教師編號(hào)，t_name = 教師姓名

成績(jī)表（score）

s_id = 學(xué)生編號(hào)，c_id = 課程編號(hào)，s_score = 分?jǐn)?shù)

習(xí)題

開始之前我們先來看看四張表中的數(shù)據(jù)。

1. 查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

SELECT
	st.*,
	sc.s_score AS '語文',
	sc2.s_score '數(shù)學(xué)' 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
	AND sc.c_id = '01'
	LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
	AND sc2.c_id = '02'

2. 查詢"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)

SELECT
	st.*,
	s.s_score AS 數(shù)學(xué),
	s2.s_score AS 語文 
FROM
	student st
	LEFT JOIN score s ON s.s_id = st.s_id 
	AND s.c_id = '01'
	LEFT JOIN score s2 ON s2.s_id = st.s_id 
	AND s2.c_id = '02' 
WHERE
	s.s_score < s2.s_score

3. 查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

SELECT
	st.s_id AS '學(xué)生編號(hào)',
	st.s_name AS '學(xué)生姓名',
	AVG( s.s_score ) AS avgScore 
FROM
	student st
	LEFT JOIN score s ON st.s_id = s.s_id 
GROUP BY
	st.s_id 
HAVING
	avgScore >= 60

4. 查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

(包括有成績(jī)的和無成績(jī)的)

SELECT
st.s_id AS ‘學(xué)生編號(hào)’,
st.s_name AS ‘學(xué)生姓名’,(
CASE

WHEN ROUND( AVG( sc.s_score ), 2 ) IS NULL THEN
		0 ELSE ROUND( AVG( sc.s_score ), 2 ) 
	END 
	) 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
HAVING
AVG( sc.s_score )< 60 
OR AVG( sc.s_score ) IS NULL

5. 查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)

SELECT
	st.s_id AS '學(xué)生編號(hào)',
	st.s_name AS '學(xué)生姓名',
	COUNT( sc.c_id ) AS '選課總數(shù)',
	sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '總成績(jī)' 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id

6. 查詢"流"姓老師的數(shù)量

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '流%'

7. 查詢學(xué)過"流浪法師"老師授課的同學(xué)的信息

SELECT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id
	LEFT JOIN course cs ON cs.c_id = sc.c_id
	LEFT JOIN teacher tc ON tc.t_id = cs.t_id 
	WHERE tc.t_name = '流浪法師'

8. 查詢沒學(xué)過"張三"老師授課的同學(xué)的信息

-- 查詢流浪法師教的課
SELECT
	cs.* 
FROM
	course cs
	LEFT JOIN teacher tc ON tc.t_id = cs.t_id 
WHERE
	tc.t_name = '流浪法師'
-- 查詢有流浪法師課程成績(jī)的學(xué)生id
SELECT
	sc.s_id 
FROM
	score sc 
WHERE
	sc.c_id IN (
	SELECT
		cs.c_id 
	FROM
		course cs
		LEFT JOIN teacher tc ON tc.t_id = cs.t_id 
	WHERE
	tc.t_name = '流浪法師')
-- 取反，查詢沒有學(xué)過流浪法師課程的同學(xué)信息
SELECT
	st.* 
FROM
	student st 
WHERE
	st.s_id NOT IN (
	SELECT
		sc.s_id 
	FROM
		score sc 
	WHERE
	sc.c_id IN ( SELECT cs.c_id FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法師' ) 
	)

9. 查詢學(xué)過編號(hào)為"01"并且也學(xué)過編號(hào)為"02"的課程的同學(xué)的信息

方法 1

– 查詢學(xué)過編號(hào)為01課程的同學(xué)id
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = ‘01';
– 查詢學(xué)過編號(hào)為02課程的同學(xué)id
SELECT
st2.s_id
FROM
student st2
INNER JOIN score sc2 ON sc2.s_id = st2.s_id
INNER JOIN course cs2 ON cs2.c_id = sc2.c_id
AND cs2.c_id = ‘02';
– 查詢學(xué)過編號(hào)為"01"并且也學(xué)過編號(hào)為"02"的課程的同學(xué)的信息
SELECT
st.*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND sc.c_id = ‘01'
WHERE
st.s_id IN (
SELECT
st2.s_id
FROM
student st2
INNER JOIN score sc2 ON sc2.s_id = st2.s_id
INNER JOIN course cs2 ON cs2.c_id = sc2.c_id
AND cs2.c_id = ‘02'
);

方法 2

SELECT
a.*
FROM
student a,
score b,
score c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = ‘01'
AND c.c_id = ‘02';

10. 查詢學(xué)過編號(hào)為"01"但是沒有學(xué)過編號(hào)為"02"的課程的同學(xué)的信息

SELECT
	st.s_id 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id
	INNER JOIN course cs ON cs.c_id = sc.c_id 
	AND cs.c_id = '01' 
WHERE
	st.s_id NOT IN (
	SELECT
		st.s_id 
	FROM
		student st
		INNER JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course cs ON cs.c_id = sc.c_id 
		AND cs.c_id = '02' 
	);

11. 查詢沒有學(xué)全所有課程的同學(xué)的信息

方法 1

SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = ‘01'
WHERE
st.s_id IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = ‘02'
WHERE
st.s_id
)
AND st.s_id IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = ‘03'
WHERE
st.s_id
)
);

方法 2

SELECT
a.*
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
COUNT( b.c_id ) != ‘3';

12. 查詢至少有一門課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

SELECT DISTINCT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
	sc.c_id IN ( SELECT sc2.c_id FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id WHERE st2.s_id = '01' );

13. 查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

SELECT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	GROUP_CONCAT( sc.c_id )=(
	SELECT
		GROUP_CONCAT( sc2.c_id ) 
	FROM
		student st2
		LEFT JOIN score sc2 ON sc2.s_id = st2.s_id 
	WHERE
		st2.s_id = '01' 
	);

14. 查詢沒學(xué)過"邪惡小法師"老師講授的任一門課程的學(xué)生姓名

SELECT
	* 
FROM
	student 
WHERE
	s_id NOT IN (
	SELECT
		sc.s_id 
	FROM
		score sc
		INNER JOIN course cs ON cs.c_id = sc.c_id
	INNER JOIN teacher t ON t.t_id = cs.t_id 
	AND t.t_name = '邪惡小法師');

15. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

SELECT
	st.s_id AS '學(xué)號(hào)',
	st.s_name AS '姓名',
	AVG( sc.s_score ) AS '平均成績(jī)' 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
	sc.s_id IN (
	SELECT
		sc.s_id 
	FROM
		score sc 
	WHERE
		sc.s_score < 60 
		OR sc.s_score IS NULL 
	GROUP BY
		sc.s_id 
	HAVING
		COUNT( 1 )>= 2 
	) 
GROUP BY
	st.s_id

16. 檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息

SELECT
	st.* 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id 
	AND sc.c_id = '01' 
	AND sc.s_score < '60' 
ORDER BY
	sc.s_score DESC;
SELECT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
	sc.c_id = '01' 
	AND sc.s_score < '60' 
ORDER BY
	sc.s_score DESC;

17. 按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

方法 1

SELECT
st.*,
AVG( sc4.s_score ) AS ‘平均分',
sc.s_score AS ‘語文',
sc2.s_score AS ‘?dāng)?shù)學(xué)',
sc3.s_score AS ‘英語'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = ‘01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = ‘02'
LEFT JOIN score sc3 ON sc3.s_id = st.s_id
AND sc3.c_id = ‘03'
LEFT JOIN score sc4 ON sc4.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc4.s_score ) DESC;

方法 2

SELECT
st.*,
( CASE WHEN AVG( sc4.s_score ) IS NULL THEN 0 ELSE AVG( sc4.s_score ) END ) AS ‘平均分',
( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS ‘語文',
( CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END ) AS ‘?dāng)?shù)學(xué)',
( CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END ) AS ‘英語'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = ‘01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = ‘02'
LEFT JOIN score sc3 ON sc3.s_id = st.s_id
AND sc3.c_id = ‘03'
LEFT JOIN score sc4 ON sc4.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc4.s_score ) DESC;

18. 查詢各科成績(jī)最高分、最低分和平均分：

以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率

及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90

SELECT
cs.c_id,
cs.c_name,
MAX( sc1.s_score ) AS ‘最高分',
MIN( sc2.s_score ) AS ‘最低分',
AVG( sc3.s_score ) AS ‘平均分',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 60
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS ‘及格率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 70
AND s_score < 80
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS ‘中等率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 80
AND s_score < 90
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS ‘優(yōu)良率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 90
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS ‘優(yōu)秀率'
FROM
course cs
LEFT JOIN score sc1 ON sc1.c_id = cs.c_id
LEFT JOIN score sc2 ON sc2.c_id = cs.c_id
LEFT JOIN score sc3 ON sc3.c_id = cs.c_id
GROUP BY
cs.c_id;

19. 按各科成績(jī)進(jìn)行排序，并顯示排名(實(shí)現(xiàn)不完全)

mysql沒有rank函數(shù)

加@score是為了防止用union all 后打亂了順序

SELECT
c1.s_id,
c1.c_id,
c1.c_name,
@score := c1.s_score,
@i := @i + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = “01”
ORDER BY
sc.s_score DESC
) c1,
( SELECT @i := 0 ) a UNION ALL
SELECT
c2.s_id,
c2.c_id,
c2.c_name,
c2.s_score,
@ii := @ii + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = “02”
ORDER BY
sc.s_score DESC
) c2,
( SELECT @ii := 0 ) aa UNION ALL
SELECT
c3.s_id,
c3.c_id,
c3.c_name,
c3.s_score,
@iii := @iii + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = “03”
ORDER BY
sc.s_score DESC
) c3;
SET @iii = 0;

20. 查詢學(xué)生的總成績(jī)并進(jìn)行排名

SELECT
	st.s_id,
	st.s_name,
	( CASE WHEN sum( sc.s_score ) IS NULL THEN 0 ELSE SUM( sc.s_score ) END ) 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
ORDER BY
	SUM( sc.s_score ) DESC

21. 查詢不同老師所教不同課程平均分從高到低顯示

SELECT
	t.t_id,
	t.t_name,
	AVG( sc.s_score ) 
FROM
	teacher t
	LEFT JOIN course c ON c.t_id = t.t_id
	LEFT JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
	t.t_id 
ORDER BY
	AVG( sc.s_score ) DESC

22. 查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)

SELECT
	a.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_id,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON sc.c_id = c.c_id 
		AND c.c_id = '01' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 1,
		2 
	) a UNION ALL
SELECT
	b.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_id,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '02' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 1,
		2 
	) b UNION ALL
SELECT
	c.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_id,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '03' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 1,
		2 
	) c;

23. 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
	c.c_id,
	c.c_name,
	(
	SELECT
		COUNT( 1 ) 
	FROM
		score sc 
	WHERE
		sc.c_id = c.c_id 
		AND sc.s_score <= 100 AND sc.s_score > 80 
		)/(
	SELECT
		COUNT( 1 ) 
	FROM
		score sc 
	WHERE
		sc.c_id = c.c_id 
	) AS '100-85',
	((
		SELECT
			COUNT( 1 ) 
		FROM
			score sc 
		WHERE
			sc.c_id = c.c_id 
			AND sc.s_score <= 85 AND sc.s_score > 70 
			)/(
		SELECT
			COUNT( 1 ) 
		FROM
			score sc 
		WHERE
			sc.c_id = c.c_id 
		)) AS '85-70',
	((
		SELECT
			COUNT( 1 ) 
		FROM
			score sc 
		WHERE
			sc.c_id = c.c_id 
			AND sc.s_score <= 70 AND sc.s_score > 60 
			)/(
		SELECT
			COUNT( 1 ) 
		FROM
			score sc 
		WHERE
			sc.c_id = c.c_id 
		)) AS '70-60',
	((
		SELECT
			COUNT( 1 ) 
		FROM
			score sc 
		WHERE
			sc.c_id = c.c_id 
			AND sc.s_score <= 60 AND sc.s_score >= 0 
			)/(
		SELECT
			COUNT( 1 ) 
		FROM
			score sc 
		WHERE
			sc.c_id = c.c_id 
		)) AS '85-70' 
FROM
	course c 
ORDER BY
	c.c_id 

24. 查詢學(xué)生平均成績(jī)及其名次

SET @i = 0;
SELECT
	a.*,
	@i := @i + 1 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		round( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END, 2 ) AS agvScore 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id 
	GROUP BY
		st.s_id 
	ORDER BY
		agvScore DESC 
	) a

25. 查詢各科成績(jī)前三名的記錄

SELECT
	a.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_id,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '01' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 0,
		3 
	) a UNION ALL
SELECT
	b.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_id,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '02' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 0,
		3 
	) b UNION ALL
SELECT
	c.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_id,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '03' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 0,
		3 
	) c

26. 查詢每門課程被選修的學(xué)生數(shù)

SELECT
	c.c_id,
	c.c_name,
	COUNT( 1 ) 
FROM
	course c
	LEFT JOIN score sc ON sc.c_id = c.c_id
	INNER JOIN student st ON st.s_id = c.c_id 
GROUP BY
	c.c_id

27. 查詢出只有兩門課程的全部學(xué)生的學(xué)號(hào)和姓名

SELECT
	st.s_id,
	st.s_name 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id
	INNER JOIN course c ON c.c_id = sc.c_id 
GROUP BY
	st.s_id 
HAVING
	COUNT( 1 ) = 2

28. 查詢男生、女生人數(shù)

SELECT s_sex, COUNT(1) FROM student GROUP BY s_sex

29. 查詢名字中含有"德"字的學(xué)生信息

SELECT * FROM student WHERE s_name LIKE '%德%'

30. 查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

select st.s_name,st.s_sex,count(1) from student st group by st.s_name,st.s_sex having count(1)>1

31. 查詢1990年出生的學(xué)生名單

SELECT st.* FROM student st WHERE st.s_birth LIKE '1990%';

32. 查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

SELECT
	c.c_id,
	c_name,
	AVG( sc.s_score ) AS scoreAvg 
FROM
	course c
	INNER JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
	c.c_id 
ORDER BY
	scoreAvg DESC,
	c.c_id ASC;

33. 查詢平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

SELECT
	st.s_id,
	st.s_name,
	( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END ) scoreAvg 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	scoreAvg > '85';

34. 查詢課程名稱為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)

SELECT
	* 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id 
	AND sc.s_score < 60
	INNER JOIN course c ON c.c_id = sc.c_id 
	AND c.c_name = '數(shù)學(xué)';

35. 查詢所有學(xué)生的課程及分?jǐn)?shù)情況

SELECT
	* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id
	LEFT JOIN course c ON c.c_id = sc.c_id 
ORDER BY
	st.s_id,
	c.c_name;

36. 查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)

SELECT
	st.s_id,st.s_name,c.c_name,sc.s_score 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id
	LEFT JOIN course c ON c.c_id = sc.c_id 
WHERE
	st.s_id IN (
	SELECT
		st2.s_id 
	FROM
		student st2
		LEFT JOIN score sc2 ON sc2.s_id = st2.s_id 
	GROUP BY
		st2.s_id 
	HAVING
		MIN( sc2.s_score )>= 70 
	ORDER BY
	st2.s_id 
	)

37. 查詢不及格的課程

SELECT
	st.s_id,
	c.c_name,
	st.s_name,
	sc.s_score 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id 
	AND sc.s_score < 60
	INNER JOIN course c ON c.c_id = sc.c_id

38. 查詢課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名

SELECT
	st.s_id,
	st.s_name,
	sc.s_score 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id 
	AND sc.c_id = '01' 
	AND sc.s_score >= 80;

39. 求每門課程的學(xué)生人數(shù)

SELECT
	c.c_id,
	c.c_name,
	COUNT( 1 ) 
FROM
	course c
	INNER JOIN score sc ON sc.c_id = c.c_id 
GROUP BY
	c.c_id;

40. 查詢選修"死亡歌頌者"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)

SELECT
	st.*,
	sc.s_score 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id
	INNER JOIN course c ON c.c_id = sc.c_id
	INNER JOIN teacher t ON t.t_id = c.t_id 
	AND t.t_name = '死亡歌頌者' 
ORDER BY
	sc.s_score DESC 
	LIMIT 0,1;

41. 查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)

SELECT
	st.s_id,
	st.s_name,
	sc.c_id,
	sc.s_score 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id
	LEFT JOIN course c ON c.c_id = sc.c_id 
WHERE
	(
	SELECT
		COUNT( 1 ) 
	FROM
		student st2
		LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
		LEFT JOIN course c2 ON c2.c_id = sc2.c_id 
	WHERE
		sc.s_score = sc2.s_score 
	AND c.c_id != c2.c_id 
	)>1;

42. 查詢每門功成績(jī)最好的前兩名

SELECT
	a.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '01' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 0,
		2 
	) a UNION ALL
SELECT
	b.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '02' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 0,
		2 
	) b UNION ALL
SELECT
	c.* 
FROM
	(
	SELECT
		st.s_id,
		st.s_name,
		c.c_name,
		sc.s_score 
	FROM
		student st
		LEFT JOIN score sc ON sc.s_id = st.s_id
		INNER JOIN course c ON c.c_id = sc.c_id 
		AND c.c_id = '03' 
	ORDER BY
		sc.s_score DESC 
		LIMIT 0,
	2 
	) c;

寫法 2

SELECT
	a.s_id,
	a.c_id,
	a.s_score 
FROM
	score a 
WHERE
	( SELECT COUNT( 1 ) FROM score b WHERE b.c_id = a.c_id AND b.s_score > a.s_score ) <= 2 
ORDER BY
	a.c_id;

43. 統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計(jì)）

要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

SELECT
c.c_id,
COUNT( 1 )
FROM
score sc
LEFT JOIN course c ON c.c_id = sc.c_id
GROUP BY
c.c_id
HAVING
COUNT( 1 ) > 5
ORDER BY
COUNT( 1 ) DESC,
c.c_id ASC;

44. 檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

SELECT
	st.s_id 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	COUNT( 1 )>= 2;

45. 查詢選修了全部課程的學(xué)生信息

SELECT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	COUNT( 1 )=(
	SELECT
		COUNT( 1 ) 
FROM
	course)

46. 查詢各學(xué)生的年齡

SELECT
	st.*,
	TIMESTAMPDIFF(
		YEAR,
		st.s_birth,
	NOW()) 
FROM
	student st

47. 查詢本周過生日的學(xué)生

SELECT
	st.* 
FROM
	student st 
WHERE
	WEEK (
	NOW())+ 1 = WEEK (
	DATE_FORMAT( st.s_birth, '%Y%m%d' ))

48. 查詢下周過生日的學(xué)生

SELECT
	st.* 
FROM
	student st 
WHERE
	WEEK (
		NOW())+ 1 = WEEK (
	DATE_FORMAT( st.s_birth, '%Y%m%d' ));

49. 查詢本月過生日的學(xué)生

SELECT
	st.* 
FROM
	student st 
WHERE
	MONTH (
	NOW())= MONTH (
	DATE_FORMAT( st.s_birth, '%Y%m%d' ));

50. 查詢下月過生日的學(xué)生

SELECT
	st.* 
FROM
	student st 
WHERE
	MONTH (
		TIMESTAMPADD(
			MONTH,
			1,
		NOW()))= MONTH (
	DATE_FORMAT( st.s_birth, '%Y%m%d' ));

【阿里巴巴開發(fā)手冊(cè)】