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

數(shù)據(jù)庫的創(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

查看當前服務器下的數(shù)據(jù)庫列表

SHOW { DATABASES | SCHEMAS }

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

SHOW CREATE  { DATABASE | SCHEMA } db_name

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

SHOW WARNINGS

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

DROP  { DATABASE | SCHEMA } [IF EXISTS] db_name

打開數(shù)據(jù)庫

USE db_name

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

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

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

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

整數(shù)類型

浮點類型

字符串類型

日期時間類型

二進制類型

JSON類型(ver5.7新增)

存儲引擎

查看MYSQL支持的存儲引擎

SHOW ENGINES

查看支持的存儲引擎信息

SHOW VARIABLES LIKE 'have%'

查看默認的存儲引擎

SHOW VARIABLES LIKE '%storage_engine%'

常用存儲引擎(注意各引擎優(yōu)缺點)

• InnoDB
• MyISAM
• Memory

約束條件

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

• PRIMARY KEY 主鍵岸蜗，PRIMARY可省略 (注意：PRIMARY KEY(a,b,c,...) 來定義一個主鍵時,由a,b,c,...所有的值才能確定一個主鍵尉咕，即一個表包括a,b,c...等字段，其中單獨的a,b,c字段相等璃岳，是可以的年缎，但不能全部相等，全部相等表明主鍵相等铃慷，不可插入单芜。）
• AUTO_INCREMENT 自增,需要和主鍵(PRIMARY KEY)搭配使用±绻瘢可在創(chuàng)建表時自定義從某個數(shù)開始,如CREATE TABLE tbl_name(id INT key AUTO_INCREMENT,......)AUTO_INCREMENT=數(shù)值,表示內(nèi)部自增字段從此數(shù)值開始洲鸠。(也可通過ALTER TABLE tbl_name AUTO_INCREMENT=數(shù)值修改)
• FOREIGN KEY 外鍵
• NOT NULL 非空
• UNIQUE KEY 唯一，KEY 可省略
• DEFAULT 默認值 插入數(shù)據(jù)時可使用DEFAULT

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

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

查看當前數(shù)據(jù)庫下的數(shù)據(jù)表

SHOW TABLES;

查看表結構及創(chuàng)建信息

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

修改表結構

修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_name

或者

RENAME TABLE tbl_name TO new_name

添加字段

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

若需要添加多個字段扒腕，需要多個ADD操作，以“萤悴，”號分隔

刪除字段

ALTER TABLE tbl_name DROP 字段名稱

若需要刪除多個字段瘾腰，需要多個DROP操作，以“稚疹，”號分隔居灯。

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

修改字段

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

修改字段名稱

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

添加默認值

ALTER TABLE tbl_name ALTER 字段名稱 SET DEFAULT 默認值

刪除默認值

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

修改表的存儲引擎

ALTER TABLE tbl_name ENGINE=存儲引擎名稱

修改表的自增長值

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,......)

同時插入多條記錄

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

通過SET形式插入記錄

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

將查詢結果插入到表中

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 條件 對分組結果進行二次篩選]
    [ORDER BY {col_name | position } [ ASC | DESC], ... 排序]
    [LIMIT 限制顯示條數(shù)]
]

查詢表達式

每個表達式表示想要的一列祭务，必須至少有一列内狗，多個列以","號分隔怪嫌； "*" 表示所有列，tbl_name.*表示命名表的所有列柳沙。查詢表達式可以使用 [AS] alias_name 為其賦予別名岩灭。

WHERE條件

模糊查詢

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

GROUP BY 查詢結果分組

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

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

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

5.通過HAVING子句對分組結果進行二次篩選

配合GROUP BY 實現(xiàn)噪径，比如：

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

通過ORDER BY 進行排序

默認升序排列(ASC),可省略。降序排列(DESC),可排序多個字段通過","號連接

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

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

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

(SELECT * FROM country LIMIT 2,1)

連接查詢

什么是鏈接查詢

連接查詢是將兩個或者兩個以上的表按照某個條件連接起來找爱，從中選取需要的數(shù)據(jù)。連接查詢是同時查詢兩個或者兩個以上的表時使用的泡孩。當不同的表中存在相同意義的字段時车摄，可通過該字段連接這幾個表。

內(nèi)連接查詢

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

注意：三張表或者三張表以上查詢可以直接加入更多的JOIN ...ON ...來連接多張表仑鸥，注意找準表之間的連接條件

例如:

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)

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

外鍵

概覽

外鍵是表的一個特殊字段吮播，被參照的表是主表，外鍵所在字段的表為子表眼俊。設置外鍵的原則需要記住意狠，就是依賴于數(shù)據(jù)庫中已存在的表的主鍵。外鍵的作用是建立該表與其父表的關聯(lián)關系疮胖。父表中對記錄做操作時环戈，子表中與之對應的信息也應有相應的改變。
外鍵的作用是保持數(shù)據(jù)庫的一致性和完整性澎灸。
可以實現(xiàn)一對一或者一對多的關系

注意

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

外鍵約束的參照操作：

• CASCADE:從父表刪除或者更新且自動刪除或者更新子表中匹配的行。
• SET NULL:從父表刪除或者更新行括蝠，并設置子表的中的外鍵列為NULL鞠抑。如果使用該選項必須保證子表列中沒有指定NOT NULL。
• RESTRICT:拒絕對父表的更新或者刪除操作忌警。
• NOT ACTION: 標準SQL的關鍵字搁拙，在MYSQL中與RESTRICT相同

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

CREATE TABLE [IF NOT EXISTS] tbl_name (
 字段名稱 字段類型 [完整性約束條件] ##完整性約束條件順序為: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默認值] [[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的員工存在，這顯然是不合適的箕速，這時酪碘，可以使用外鍵來解決

創(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 語句 UNION SELECT 語句)

UNION ALL

(SELECT 語句 UNION SELECT 語句)

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

子查詢

什么是子查詢

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

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

• 使用 [NOT] IN 的子查詢
• 使用比較運算符的子查詢 = > < >= <= <> != <=>
• 使用 [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)

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

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

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

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

正則表達式查詢

REGEXP '匹配方式'

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

常用匹配方式

運算符

算數(shù)運算符

比較運算符

邏輯運算符

運算符的優(yōu)先級

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

MYSQL中的函數(shù)

數(shù)學函數(shù)

字符串函數(shù)

日期時間函數(shù)

條件判斷函數(shù)

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

加密函數(shù)

其他常用函數(shù)

索引

什么是索引

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

索引分類

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

創(chuàng)建索引

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

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

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

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

或者

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

刪除索引

(DROP INDEX 索引名稱 ON tbl_name)

其他

注釋

• 以‘-- ’ 為前綴
• 以'#' 為前綴
• 字段注釋通過COMMENT '.....'進行注釋