數(shù)據(jù)庫(kù)操作(DDL)

數(shù)據(jù)庫(kù)的創(chuàng)建

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] charset_name]

例如：

CREATE DATABASE IF NOT EXISTS  test_db DEFAULT CHARACTER SET utf8

查看當(dāng)前服務(wù)器下的數(shù)據(jù)庫(kù)列表

SHOW { DATABASES | SCHEMAS }

查看數(shù)據(jù)庫(kù)的定義

SHOW CREATE  { DATABASE | SCHEMA } db_name

查看上一步操作的警告信息

SHOW WARNINGS

刪除數(shù)據(jù)庫(kù)

DROP  { DATABASE | SCHEMA } [IF EXISTS] db_name

打開(kāi)數(shù)據(jù)庫(kù)

USE db_name

修改數(shù)據(jù)庫(kù)的編碼方式

ALTER { DATABASE | SCHEMA }  db_name [DEFAULT] CHARACTER SET [=] charset_name

數(shù)據(jù)表操作

MYSQL中的數(shù)據(jù)類型

整數(shù)類型

浮點(diǎn)類型

字符串類型

日期時(shí)間類型

二進(jìn)制類型

JSON類型(ver5.7新增)

存儲(chǔ)引擎

查看MYSQL支持的存儲(chǔ)引擎

SHOW ENGINES

查看支持的存儲(chǔ)引擎信息

SHOW VARIABLES LIKE 'have%'

查看默認(rèn)的存儲(chǔ)引擎

SHOW VARIABLES LIKE '%storage_engine%'

常用存儲(chǔ)引擎(注意各引擎優(yōu)缺點(diǎn))

• InnoDB
• MyISAM
• Memory

約束條件

一個(gè)表只能有一個(gè)主鍵(PRIMATY KEY),但是能夠有多個(gè)唯一(UNIQUE KEY)，唯一的字段的值不允許出現(xiàn)重復(fù)，但是NULL值不算做重復(fù)的值

• PRIMARY KEY 主鍵，PRIMARY可省略 (注意：PRIMARY KEY(a,b,c,...) 來(lái)定義一個(gè)主鍵時(shí),由a,b,c,...所有的值才能確定一個(gè)主鍵，即一個(gè)表包括a,b,c...等字段，其中單獨(dú)的a,b,c字段相等，是可以的，但不能全部相等，全部相等表明主鍵相等，不可插入。）
• AUTO_INCREMENT 自增,需要和主鍵(PRIMARY KEY)搭配使用?？稍趧?chuàng)建表時(shí)自定義從某個(gè)數(shù)開(kāi)始,如CREATE TABLE tbl_name(id INT key AUTO_INCREMENT,......)AUTO_INCREMENT=數(shù)值,表示內(nèi)部自增字段從此數(shù)值開(kāi)始。(也可通過(guò)ALTER TABLE tbl_name AUTO_INCREMENT=數(shù)值修改)
• FOREIGN KEY 外鍵
• NOT NULL 非空
• UNIQUE KEY 唯一，KEY 可省略
• DEFAULT 默認(rèn)值 插入數(shù)據(jù)時(shí)可使用DEFAULT

數(shù)據(jù)表的創(chuàng)建

CREATE TABLE [IF NOT EXISTS] tbl_name (
     字段名稱 字段類型 [完整性約束條件] ##完整性約束條件順序?yàn)? [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默認(rèn)值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
     ......
)ENGINE = 引擎名稱 CHARSET = 編碼方式；

查看當(dāng)前數(shù)據(jù)庫(kù)下的數(shù)據(jù)表

SHOW TABLES;

查看表結(jié)構(gòu)及創(chuàng)建信息

DESC tbl_name
DESCRIBE tbl_name
SHOW COLUMNS FROM tbl_name
SHOW CREATE TABLE tbl_name

修改表結(jié)構(gòu)

修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_name

或者

RENAME TABLE tbl_name TO new_name

添加字段

ALTER TABLE tbl_name ADD 字段名稱 字段類型 [完整性約束條件]  [ FIRST | (AFTER 字段名稱)] 

若需要添加多個(gè)字段，需要多個(gè)ADD操作，以“，”號(hào)分隔

刪除字段

ALTER TABLE tbl_name DROP 字段名稱

若需要?jiǎng)h除多個(gè)字段，需要多個(gè)DROP操作，以“，”號(hào)分隔。

注意:添加字段(ADD)與刪除字段(DROP)可以在同一個(gè)語(yǔ)句里面(ALTER TABLE tbl_name ADD .....,DROP .....)

修改字段

ALTER TABLE tbl_name MODIFY 字段名稱 字段類型 [完整性約束條件] [FIRST | (AFTER  字段名稱)]

修改字段名稱

ALTER TABLE tbl_name CHANGE 舊字段名稱 新字段名稱 字段類型 [完整性約束條件] [FIRST | (AFTER  字段名稱)]

添加默認(rèn)值

ALTER TABLE tbl_name ALTER 字段名稱 SET DEFAULT 默認(rèn)值

刪除默認(rèn)值

ALTER TABLE tbl_name ALTER 字段名稱 DROP DEFAULT

添加主鍵

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (字段名稱,...) 

刪除主鍵

ALTER TABLE tbl_name DROP PRIMARY KEY

添加唯一

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [索引名稱] (字段名稱,...)

刪除唯一

ALTER TABLE tbl_name DROP  {INDEX| KEY} index_name

修改表的存儲(chǔ)引擎

ALTER TABLE tbl_name ENGINE=存儲(chǔ)引擎名稱

修改表的自增長(zhǎng)值

ALTER TABLE tbl_name AUTO_INCREMENT= 值

刪除數(shù)據(jù)表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name,...]

數(shù)據(jù)操作(DML)

插入數(shù)據(jù)

不指定具體的字段

INSERT [INTO] tbl_name VALUE | VALUES (值,......)

列出指定字段

INSERT [INTO] tbl_name (字段名稱1,......) VALUE | VALUES (值1,......)

同時(shí)插入多條記錄

INSERT [INTO] tbl_name   [(字段名稱1,......)]  VALUE | VALUES (值,......), (值,......),......

通過(guò)SET形式插入記錄

INSERT [INTO] tbl_name SET 字段名稱=值,......

將查詢結(jié)果插入到表中

INSERT [INTO] tbl_name   [(字段名稱1,......)]  SELECT 字段名稱 FROM tbl_name  [WHERE 條件]

更新數(shù)據(jù)

UPDATE tbl_name  SET 字段名稱=值,...... [WHERE 條件] [ORDER BY 字段名稱] [LIMIT 限制條件]

刪除數(shù)據(jù)

(DELETE FROM tbl_name  [WHERE 條件] [ORDER BY 字段名稱] [LIMIT 限制條件])

或者徹底清空數(shù)據(jù)表

(TRUNCATE [TABLE ] tbl_name)

查詢數(shù)據(jù)操作(DQL)

查詢記錄

SELECT select_expr[,select_expr,...]
[ 
    FROM table_reference
    [WHERE 條件]
    [GROUP BY {col_name | position } [ ASC | DESC], ... 分組]
    [HAVING 條件 對(duì)分組結(jié)果進(jìn)行二次篩選]
    [ORDER BY {col_name | position } [ ASC | DESC], ... 排序]
    [LIMIT 限制顯示條數(shù)]
]

查詢表達(dá)式

每個(gè)表達(dá)式表示想要的一列，必須至少有一列，多個(gè)列以","號(hào)分隔； "*" 表示所有列，tbl_name.*表示命名表的所有列。查詢表達(dá)式可以使用 [AS] alias_name 為其賦予別名。

WHERE條件

模糊查詢

• %：代表0個(gè)，1個(gè)或者多個(gè)任意字符
• _ ：代表1個(gè)字符

GROUP BY 查詢結(jié)果分組

默認(rèn)情況下，GROUP BY 查詢結(jié)果分組得到每組的第一個(gè)值
配合GROUP_CONCAT()得到分組詳情
配合聚合函數(shù)

• COUNT() NULL值不算一條記錄
• MAX()
• MIN()
• AVG()
• SUM()

配合WITH ROLLUP記錄上面所有記錄的總和

5.通過(guò)HAVING子句對(duì)分組結(jié)果進(jìn)行二次篩選

配合GROUP BY 實(shí)現(xiàn)，比如：

SELECT Region,COUNT(*),MAX(Population),MIN(Population),SUM(Population),AVG(Population) FROM country GROUP BY Region HAVING MAX(Population) > 1000000

通過(guò)ORDER BY 進(jìn)行排序

默認(rèn)升序排列(ASC),可省略。降序排列(DESC),可排序多個(gè)字段通過(guò)","號(hào)連接

SELECT * FROM country ORDER BY Population ASC,SurfaceArea ASC
SELECT * FROM country ORDER BY 3 ASC
SELECT * FROM country ORDER BY RAND() #隨機(jī)記錄

LIMIT限制查詢結(jié)果顯示條數(shù)

• LIMIT 顯示條數(shù)
• LIMIT 偏移量，顯示條數(shù)

(SELECT * FROM country LIMIT 2,1)

連接查詢

什么是鏈接查詢

連接查詢是將兩個(gè)或者兩個(gè)以上的表按照某個(gè)條件連接起來(lái)，從中選取需要的數(shù)據(jù)。連接查詢是同時(shí)查詢兩個(gè)或者兩個(gè)以上的表時(shí)使用的。當(dāng)不同的表中存在相同意義的字段時(shí)，可通過(guò)該字段連接這幾個(gè)表。

內(nèi)連接查詢

1. JOIN | CROSS JOIN | INNER JOIN
2. 通過(guò)ON 連接條件
3. 顯示兩個(gè)表中符合連接條件的記錄

注意：三張表或者三張表以上查詢可以直接加入更多的JOIN ...ON ...來(lái)連接多張表，注意找準(zhǔn)表之間的連接條件

例如:

SELECT 
    user_info.id, user_info.name, city_info.pro_name
FROM
    user_info
        JOIN
    city_info ON user_info.pro_id = city_info.pro_id;

外連接查詢

左外連接

(LEFT [OUTER] JOIN)

顯示左表的全部記錄及右表符合連接條件的記錄

右外連接

(RIGHT [OUTER] JOIN)

顯示右表的全部記錄及左表符合連接條件的記錄

外鍵

概覽

外鍵是表的一個(gè)特殊字段，被參照的表是主表，外鍵所在字段的表為子表。設(shè)置外鍵的原則需要記住，就是依賴于數(shù)據(jù)庫(kù)中已存在的表的主鍵。外鍵的作用是建立該表與其父表的關(guān)聯(lián)關(guān)系。父表中對(duì)記錄做操作時(shí)，子表中與之對(duì)應(yīng)的信息也應(yīng)有相應(yīng)的改變。
外鍵的作用是保持?jǐn)?shù)據(jù)庫(kù)的一致性和完整性。
可以實(shí)現(xiàn)一對(duì)一或者一對(duì)多的關(guān)系

注意

1. 父表和子表必須使用相同的存儲(chǔ)引擎，而且禁止使用臨時(shí)表。
2. 數(shù)據(jù)庫(kù)的存儲(chǔ)引擎只能為InnoDB。
3. 外鍵列和參照列必須具有相似的數(shù)據(jù)類型其中數(shù)字的長(zhǎng)度或是否有符號(hào)位必須相同：而字符的長(zhǎng)度則可以不同。
4. 外鍵列和參照列必須創(chuàng)建索引。如果外鍵不存在索引的話，MYSQL將自動(dòng)創(chuàng)建索引。

外鍵約束的參照操作：

• CASCADE:從父表刪除或者更新且自動(dòng)刪除或者更新子表中匹配的行。
• SET NULL:從父表刪除或者更新行，并設(shè)置子表的中的外鍵列為NULL。如果使用該選項(xiàng)必須保證子表列中沒(méi)有指定NOT NULL。
• RESTRICT:拒絕對(duì)父表的更新或者刪除操作。
• NOT ACTION: 標(biāo)準(zhǔn)SQL的關(guān)鍵字，在MYSQL中與RESTRICT相同

創(chuàng)建外鍵(先有主表)

CREATE TABLE [IF NOT EXISTS] tbl_name (
 字段名稱 字段類型 [完整性約束條件] ##完整性約束條件順序?yàn)? [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默認(rèn)值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
...
[CONSTRAINT 外鍵名稱] FOREIGN KEY(字段名) REFERENCES 父表(字段名) [ON {DELETE | UPDATE } {CASCADE | SET NULL }] [ON {DELETE | UPDATE } {CASCADE | SET NULL}]
)

刪除外鍵

(ALTER TABLE tbl_name DROP FOREIGN KEY)

添加外鍵

(ALTER TABLE tbl_name ADD [CONSTRAINT 外鍵名稱]  FOREIGN KEY(字段名) REFERENCES 父表(字段名))

示例：

部門表(父表)

CREATE TABLE IF NOT EXISTS department (id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;

員工表(子表)

CREATE TABLE IF NOT EXISTS employee (id TINYINT UNSIGNED AUTO_INCREMENT KEY,userName VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED)ENGINE=INNODB;

如果只刪除父表中部門4，那么員工表中還有屬于部門4的員工存在，這顯然是不合適的，這時(shí)，可以使用外鍵來(lái)解決

創(chuàng)建外鍵(先有主表)：

CREATE TABLE IF NOT EXISTS employee (id TINYINT UNSIGNED AUTO_INCREMENT KEY,userName VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED ， FOREIGN KEY(depId) REFERENCES deparment(id) )ENGINE=INNODB;

聯(lián)合查詢

UNION

(SELECT 語(yǔ)句 UNION SELECT 語(yǔ)句)

UNION ALL

(SELECT 語(yǔ)句 UNION SELECT 語(yǔ)句)

注意：UNION 和 UNION ALL 的區(qū)別是UNION 去掉相同記錄，UNION是簡(jiǎn)單的合并到一起

子查詢

什么是子查詢

子查詢是將一個(gè)查詢語(yǔ)句嵌套在另一個(gè)查詢語(yǔ)句之中。內(nèi)層查詢語(yǔ)句的查詢結(jié)果，可以為外層查詢語(yǔ)句提供條件

引發(fā)子查詢的情況

• 使用 [NOT] IN 的子查詢
• 使用比較運(yùn)算符的子查詢 = > < >= <= <> != <=>
• 使用 [NOT] EXISTS 的子查詢
• 使用 ANY | SOME 或者 ALL的子查詢

示例：

SELECT name FROM user where depId IN(SELECT id FROM ids)
SELECT name FROM user where depId >=(SELECT id FROM ids)
SELECT name FROM user where EXISTS(SELECT id FROM ids)

將查詢結(jié)果寫(xiě)入到數(shù)據(jù)表

(INSERT [INTO] tbl_name  [(col_name),......] SELECT ...)

創(chuàng)建數(shù)據(jù)表的同時(shí)將查詢結(jié)果寫(xiě)入到數(shù)據(jù)表

CREATE TABLE [IF NOT EXISTS] tbl_name (
  [(creat_defination)]
  select_statement

正則表達(dá)式查詢

REGEXP '匹配方式'

(SELECT * FROM user WHERE REGEXP '^[1-9]$')

常用匹配方式

運(yùn)算符

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

比較運(yùn)算符

邏輯運(yùn)算符

運(yùn)算符的優(yōu)先級(jí)

注意: 可以使用()改變優(yōu)先級(jí)

MYSQL中的函數(shù)

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

字符串函數(shù)

日期時(shí)間函數(shù)

條件判斷函數(shù)

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

加密函數(shù)

其他常用函數(shù)

索引

什么是索引

• 索引由數(shù)據(jù)庫(kù)中一列或者多列組成，其作用是提高對(duì)表中數(shù)據(jù)的查詢速度
• 索引的優(yōu)點(diǎn)是可以提高檢索數(shù)據(jù)的速度
• 索引的缺點(diǎn)是創(chuàng)建和維護(hù)索引需要耗費(fèi)時(shí)間
• 索引可以提高查詢速度，減慢寫(xiě)入速度

索引分類

• 普通索引
• 唯一索引(UNIQUE)
• 全文索引(FULLTEXT)
• 單列索引
• 多列索引
• 空間索引(數(shù)據(jù)類型為GEOMETRY,存儲(chǔ)引擎為MyISAM)SPATIAL

創(chuàng)建索引

創(chuàng)建表的時(shí)候創(chuàng)建索引

CREATE TABLE [IF NOT EXISTS] tbl_name (
     字段名稱 字段類型 [完整性約束條件] ##完整性約束條件順序?yàn)? [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默認(rèn)值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
     ......
 [UNIQUE | FULLTEXT | SPATIAL ] INDEX|KEY [索引名稱](字段名稱[(長(zhǎng)度)] [ASC|DESC]) 
)ENGINE = 引擎名稱 CHARSET = 編碼方式；

在已存在的表上創(chuàng)建索引

(CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX  索引名稱 ON 表名 {字段名稱[(長(zhǎng)度)]  [ASC|DESC] ) })

或者

(ALTER TABLE tbl_name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名稱(字段名稱[(長(zhǎng)度)] [ASC|DESC]) )

刪除索引

(DROP INDEX 索引名稱 ON tbl_name)

其他

注釋

• 以‘-- ’ 為前綴
• 以'#' 為前綴
• 字段注釋通過(guò)COMMENT '.....'進(jìn)行注釋