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

介紹

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

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

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

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

• MySQL 是開源的，目前隸屬于 Oracle 旗下產(chǎn)品。
• MySQL 支持大型的數(shù)據(jù)庫。可以處理擁有上千萬條記錄的大型數(shù)據(jù)庫。
• MySQL 使用標準的 SQL 數(shù)據(jù)語言形式。
• MySQL 可以運行于多個系統(tǒng)上，并且支持多種語言。這些編程語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 對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 術語

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

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

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

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

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

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

注：

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

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

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

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

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

一. 整數(shù)類型

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

二. 小數(shù)類型

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

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

• 各個類別存儲空間及取值范圍。

三. 字符串

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

四. 日期類型

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

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

3、各個類別存儲空間及取值范圍。

五. 復合類型

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

六. 二進制類型

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

約束

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

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

一. 非空約束(not null)

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

二. 唯一性約束(unique)

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

三. 主鍵約束(primary key) PK

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

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

• 當創(chuàng)建主鍵的約束時，系統(tǒng)默認會在所在的列和列組合上建立對應的唯一索引。

四. 外鍵約束(foreign key) FK

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

五. 默認值約束 (Default)

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

六. 自增約束(AUTO_INCREMENT)

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

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

常用命令

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

一. 啟動服務

語法：

mysql> net stop mysql

二. 關閉服務

語法：

mysql> net start mysql

三. 鏈接MySQL

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

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

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

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

語法：quit

mysql> quit

結(jié)果：

DDL（Data Definition Languages）

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

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

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

一. 創(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：主要存儲了系統(tǒng)中的一些數(shù)據(jù)庫對象信息。比如用戶表信息、列信息、權(quán)限信息、字符集信息、分區(qū)信息等。
• cluster：存儲了系統(tǒng)的集群信息。
• mysql：存儲了系統(tǒng)的用戶權(quán)限信息。
• test：系統(tǒng)自動創(chuàng)建的測試數(shù)據(jù)庫，任何用戶都可以使用。

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

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

mysql> use mzc-test;

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

有些人可能會問到，連接以后怎么退出。其實，不用退出來，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é)果：

注意：刪除時，最好用 `` 符號把表明括起來

六. 設置表的類型

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

語法：

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

適用場景：

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

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

語法：

SHOW ENGINES

結(jié)果：

2. 查看默認引擎

語法：

SHOW VARIABLES LIKE "storage_engine";

結(jié)果：

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

一. 創(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é)果

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

二. 查看表定義

語法：desc 表名

mysql> desc Student;

結(jié)果：

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

語法：show create table 表名 G;

mysql> show create table Student G;

結(jié)果：

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

三. 刪除表

語法：drop table 表名

mysql> drop table Student;

結(jié)果：

四. 修改表 (重要)

對于已經(jīng)創(chuàng)建好的表，尤其是已經(jīng)有大量數(shù)據(jù)的表，如果需要對表做一些結(jié)構(gòu)上的改變，我們可以先將表刪除（drop），然后再按照新的表定義重建表。這樣做沒有問題，但是必然要做一些額外的工作，比如數(shù)據(jù)的重新加載。而且，如果有服務在訪問表，也會對服務產(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，同時修改字段類型為 int(4)

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

結(jié)果：

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

5. 修改字段排列順序

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

例如，將新增的字段 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 這些關鍵字都屬于 MySQL 在標準 SQL 上的擴展，在其他數(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ù)庫對象中所包含的數(shù)據(jù)

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

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

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

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

特點：

插入值的類型要與列的類型一致或兼容。插入NULL可實現(xiàn)為列插入NULL值。列的順序可以調(diào)換。列數(shù)和值的個數(shù)必須一致。可省略列名，默認所有列，并且列的順序和表中列的順序一致。

案例：

-- 插入學生表測試數(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，其中每個括號內(nèi)部的數(shù)據(jù)表示一行數(shù)據(jù)，這個例子中插入了三行數(shù)據(jù)。Insert 語句也可以只給部分列插入數(shù)據(jù)，這種情況下，需要在 Value List 之前加上 ColumnName List，

例如：

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

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

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

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

案例：

INSERT INTO student SELECT * from student1;

注意：

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

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

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

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

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

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

案例：

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

2. 修改多個字段為同一的值

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

案例：

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

3. 使用case when實現(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í)行，但是會提高sql執(zhí)行的效率。確保sql語句僅執(zhí)行需要修改的行數(shù)，這里只有3條數(shù)據(jù)進行更新，而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ù)，它就會永遠消失。 因此，在執(zhí)行DELETE語句之前，應該先備份數(shù)據(jù)庫，以防萬一要找回刪除過的數(shù)據(jù)。 1. 刪除指定數(shù)據(jù)

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

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

案例：

DELETE FROM student WHERE s_id="09"

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

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

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

語法：TRUNCATE 表名;

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

與 DELETE 不同的是，TRUNCATE 只能刪除表中的全部數(shù)據(jù)，而不能通過 WHERE 子句指定條件來刪除部分數(shù)據(jù)。也正是因為它不能具體地控制刪除對象， 所以其處理速度比 DELETE 要快得多。實際上，DELETE 語句在 DML 語句中也 屬于處理時間比較長的，因此需要刪除全部數(shù)據(jù)行時，使用 TRUNCATE 可以縮短 執(zhí)行時間。

案例：

TRUNCATE student1;

DQL（Data Query Language）

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

一. wherer 條件語句

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

where的作用：

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

搜索條件的組成：

算數(shù)運算符

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

比較運算符

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

表數(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也可以為表取別名 （作用：單表查詢意義不大，但是當多個表的時候取別名就好操作，當不同的表里有相同名字的列的時候區(qū)分就會好區(qū)分）

三. distinct 去除重復記錄

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

案例

SELECT DISTINCT * FROM student

指定字段

星號表示所有字段

手動指定需要查詢的字段

SELECT DISTINCT s_name,s_birth FROM student

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

語法：

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

1. 單個字段分組

SELECT COUNT(*)FROM student GROUP BY s_sex;

2. 多個字段分組

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

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

我們?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ù)某個字段排序，默認升序(從小到大)

語法：

select * from 表名 order by 字段名;

1. 一個字段，降序（從大到小）

SELECT * FROM student ORDER BY s_id DESC;

2. 多個字段

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

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

語法1:

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

語法2:

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

案例：

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

SELECT * FROM student LIMIT 3;

從第三條開始 查詢3條

SELECT * FROM student LIMIT 2,3;

注意：起始位置 從0開始

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

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

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

SELECT*FROMscoreWHEREs_id =(SELECTs_idFROMstudentWHEREs_name = ‘趙信")

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

子查詢的思路:

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

根據(jù)步驟寫出對應的sql語句

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

SELECT*FROMscoreWHEREs_id IN (SELECTs_idFROMstudentWHEREs_sex = ‘男")

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

語法:

select * from  表1 inner join 表2;

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

注意: 在外連接查詢中不能使用where 關鍵字 必須使用on專門來做表的對應關系

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

DCL（Data Control Language）

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

關鍵字

• GRANT
• REVOKE

查看用戶權(quán)限

當成功創(chuàng)建用戶賬戶后，還不能執(zhí)行任何操作，需要為該用戶分配適當?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語句 對于新建的MySQL用戶，必須給它授權(quán)，可以用GRANT語句來實現(xiàn)對新建用戶的授權(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)限時，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)建存儲過程和存儲函數(shù)的權(quán)限。
• ALTER ROUTINE：表示授予用戶可以更新和刪除數(shù)據(jù)庫中已有的存儲過程和存儲函數(shù)的權(quán)限。
• INDEX：表示授予用戶可以在特定數(shù)據(jù)庫中的所有數(shù)據(jù)表上定義和刪除索引的權(quán)限。
• DROP：表示授予用戶可以刪除特定數(shù)據(jù)庫中所有表和視圖的權(quán)限。
• CREATE TEMPORARY TABLES：表示授予用戶可以在特定數(shù)據(jù)庫中創(chuàng)建臨時表的權(quán)限。
• CREATE VIEW：表示授予用戶可以在特定數(shù)據(jù)庫中創(chuàng)建新的視圖的權(quán)限。
• EXECUTE ROUTINE：表示授予用戶可以調(diào)用特定數(shù)據(jù)庫的存儲過程和存儲函數(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)限時，priv_type可以指定為以下值：

• SELECT：授予用戶可以使用 SELECT 語句進行訪問特定表的權(quán)限。
• INSERT：授予用戶可以使用 INSERT 語句向一個特定表中添加數(shù)據(jù)行的權(quán)限。
• DELETE：授予用戶可以使用 DELETE 語句從一個特定表中刪除數(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)建一個外鍵來參照特定數(shù)據(jù)表的權(quán)限。
• CREATE：授予用戶可以使用特定的名字創(chuàng)建一個數(shù)據(jù)表的權(quán)限。
• INDEX：授予用戶可以在表上定義索引的權(quán)限。
• ALL或ALL PRIVILEGES：所有的權(quán)限名

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

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

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

授予列(字段)權(quán)限時，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’;

對象類型(object_type)

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

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

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

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

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

"user_name"@"host_name"

Tips：'host_name’用于適應從任意主機訪問數(shù)據(jù)庫而設置的，可以指定某個地址或地址段訪問。可以同時授權(quán)多個用戶。

user表中host列的默認值

host

說明

%

匹配所有主機

localhost

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

127.0.0.1

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

::1

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

host_name格式有以下幾種：

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

身份驗證方式(auth_option)

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

加密連接(tls_option)

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

用戶資源限制(resource_option)

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

參數(shù)

說明

MAX_QUERIES_PER_HOUR count

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

MAX_UPDATES_PER_HOUR count

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

MAX_CONNECTIONS_PER_HOUR count

每小時連接次數(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）

語句：事務控制語句

什么是事物？

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

事務的ACID屬性

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

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

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

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

分類

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

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

事務的使用步驟

開啟事務

默認開啟事務

SET autocommit = 0 ;

提交事務

COMMIT;

回滾事務

ROLLBACK ;

查看當前的事務隔離級別

select @@tx_isolation;

設置當前連接事務的隔離級別

set session transaction isolation level read uncommitted;

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

set global transaction isolation level read committed ;

常用函數(shù)

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

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

因為內(nèi)容太多了這里只演示一些常用的

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

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

1. ABS(number)

作用：返回 number 的絕對值

SELECT
ABS(s_score)
FROM
score;

ABS(-86) 返回：86

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

2. PI()

例1：pi() 返回：3.141592653589793

例2：pi(2) 返回：6.283185307179586

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

3. SQRT(x) 作用：返回非負數(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ù)，即對x進行四舍五入；后者返回最接近x的數(shù)，其值保留到小數(shù)點后面y位，若y為負值，則將保留到x到小數(shù)點左邊y位

SELECT ROUND(345222.9)

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

nExp個位數(shù) =0：表示當前小數(shù)點的位置

9. POW(x,y)和、POWER(x,y) 作用：返回x的y次乘方的值 10. EXP(x) 作用：返回e的x乘方后的值 11. LOG(x) 作用：返回x的自然對數(shù)，x相對于基數(shù)e的對數(shù) 12. LOG10(x) 作用：返回x的基數(shù)為10的對數(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)

作用：計算字符串字符個數(shù)

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

2. CONCAT(s1,s2，…)

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

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

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

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

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

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

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

SELECT INSERT(‘測試字符串替換’,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個字符，后者返回字符串s從最右邊開始的n個字符

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

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

作用：前者返回s1，其左邊由字符串s2填補到len字符長度，假如s1的長度大于len，則返回值被縮短至len字符；前者返回s1，其右邊由字符串s2填補到len字符長度，假如s1的長度大于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的情況下則默認刪除空格 11. REPEAT(s,n)

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

SELECT REPEAT(‘測試’,5);

12. SPACE(n)

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

SELECT SPACE(20);

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

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

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

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

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

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

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

SELECT MID("測試測試",2,2);

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

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

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

17. REVERSE(s)

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

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

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

作用：返回第N個字符串

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

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

當前時間

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

作用：這四個函數(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)

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

SELECT UNIX_TIMESTAMP()

4. FROM_UNIXTIME(date)

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

前者返回當前UTC（世界標準時間）日期值，其格式為"YYYY-MM-DD"或"YYYYMMDD"，后者返回當前UTC時間值，其格式為"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對應的工作日的英文名稱，如Sunday、Monday等；DAYOFWEEK(d)返回的對應一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d對應的工作日索引，0表示周一，1表示周二 8. WEEK(d)

計算日期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)返回指定日期對應的年份，范圍是1970~2069；QUARTER(date)返回date對應一年中的季度，范圍是1~4；MINUTE(time)返回time對應的分鐘數(shù)，范圍是0~59；SECOND(time)返回制定時間的秒值

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_小時 + 60_分鐘 + 秒"

SELECT TIME_TO_SEC(NOW())

13. SEC_TO_TIME()

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

SELECT SEC_TO_TIME(530)

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

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

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

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

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

作用：前者進行date的時間加操作，后者進行date的時間減操作

四. 條件判斷函數(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等于某個vn，則返回對應位置THEN后面的結(jié)果，如果與所有值都不想等，則返回ELSE后面的rn

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

1. VERSION()

作用：查看MySQL版本號

SELECT VERSION()

2. CONNECTION_ID()

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

SELECT CONNECTION_ID()

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

作用：查看當前被MySQL服務器驗證的用戶名和主機的組合，一般這幾個函數(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計算并返回加密后的字符串密碼，注意這個函數(shù)的加密是單向的（不可逆），因此不應將它應用在個人的應用程序中而應該只在MySQL服務器的鑒定系統(tǒng)中使用

SELECT PASSWORD(‘mima’)

2. MD5(str)

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

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ù)點后n位，結(jié)果以字符串形式返回

SELECT FORMAT(446.454,2)

2. CONV(N,from_base,to_base)

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

3. INET_ATON(expr)

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

4. INET_NTOA(expr)

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

5. BENCHMARK(count,expr)

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

6. CONVERT(str USING charset)

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

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

SQL實戰(zhàn)練習

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

創(chuàng)建表（并初始化數(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`));-- 課程表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`));-- 成績表CREATE TABLE `score`(`s_id` VARCHAR(20),`c_id` VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`));-- 插入學生表測試數(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" , "女");-- 課程表測試數(shù)據(jù)insert into course values("01" , "語文" , "02");insert into course values("02" , "數(shù)學" , "01");insert into course values("03" , "英語" , "03");-- 教師表測試數(shù)據(jù)insert into teacher values("01" , "死亡歌頌者");insert into teacher values("02" , "流浪法師");insert into teacher values("03" , "邪惡小法師");-- 成績表測試數(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語句的練習，所以并沒有遵守一些規(guī)范。下面讓我們來看看相關的表結(jié)構(gòu)吧

學生表（student）

s_id = 學生編號，s_name = 學生姓名，s_birth = 出生年月，s_sex = 學生性別

課程表（course）

c_id =