• MySQL約束

注釋

-- 單行注釋
/*多行注釋*/
# 井號注釋

MySQL命令執行順序

FROM > ON > JOIN WHERE > GROUP BY > HAVING + 聚合函數 > SELECT > ORDER BY > LIMIT

數據庫設計——DDL

show databases;	-- 查看當前所有的數據庫
use [數據庫名];	-- 打開指定的數據庫
show tables;	-- 查看所有的表
describe/desc [表名];	-- 顯示表的信息
create database [數據庫名];	-- 創建一個數據庫，不容許重名
select database();	-- 查看當前操作的數據庫
exit	-- 退出連接

數據庫

/*創建數據庫*/
CREATE DATABASE [if not exists] db_name
CHARACTER SET gbk
COLLATE gbk_chinese_ci;

/*修改數據庫*/
ALTER DATABASE db_name
[CHARACTER SET gbk]	-- 可選
[COLLATE gbk_chinese_ci];	-- 可選

/*刪除數據庫*/
DROP DATABASE [if exists] db_name;

表

• CREATE 創建表

/*基本格式*/
CREATE TABLE [if not exists] tb_name(
	字段名 字段類型 [約束] [auto_increment] [comment "備注"],
 	stu_code char(10) not null primary key,
 	name 	  char(10) not null,
 	gender   tinyint(1) not null
)CHARACTER SET 字符集 COLLATE 校對規則 ENGINE 引擎;

/*復制其他表*/
CREATE TABLE stu1 LIKE stu2;  
或  CREATE TABLE stu1 AS (SELECT * FROM stu2);
-- 后者僅復制表的數據

• ALTER 修改表

/*基本格式*/
ALTER TABLE t_name ['選項'];	-- 若要同時進行多個操作，只需將每個選項用','隔開

/*選項：*/
-- 1.插入新列，FIRST|AFTER用于指明新列插入的位置
ADD 列定義1 [FIRST|AFTER 列名],
ADD 列定義2 [FIRST|AFTER 列名],
...	;
-- 2.更改表中某列的默認值或刪除默認值，花括號不用寫
ALTER 列名 SET DEFAULT 默認值； 或 DROP DEFAULT;
-- 3.更改列名，必須指定類型
CHANGE oldname newname 類型 [FIRST|AFTER 列名],
CHANGE ...;
-- 4.修改列屬性
MODIFY 列定義1 [FIRST|AFTER 列名],
MODIFY ...;
-- 5.刪除列
DROP 列名;
-- 6.表重命名
RENAME 新表名; {不用ALTER：RENAME TABLE 舊表名 TO 新表名;}
-- 7.按某列排序
ORDER BY 列名;

數據庫操作語句——DML

• INSERT 插入一個新記錄到表里面

/*往表中插入單條或多條記錄（不指定字段默認包含全部字段）*/
INSERT INTO 表名[(字段列表)] VALUES
(屬性1，屬性2...屬性n),
(...);

-- 將'表1'數據插入'表2'中
INSERT INTO 表2 SELECT * FROM 表1；

• UPDATE 修改基本表中的記錄

/*基本格式*/
UPDATE [LOW_PRIORITY] [IGNORE] 表名
SET 字段1=expr1 [,字段2=expr2 ...]
[WHERE 條件]	-- 不加條件默認修改所有記錄
[ORDER BY ...]
[LIMIT row_count];

• DELETE 刪除基本表中的記錄

/*基本格式*/
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM 表名
[WHERE 條件]
[ORDER BY ...]
[LIMIT row_count];

/*清除所有記錄*/
DELETE FROM 表名; | TRUNCATE TABLE 表名;

數據庫查詢語句——DQL

/*基本格式*/
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2][,...]]}	-- 要查詢的字段
FROM tb_name [as table_alias]
[left | right | inner join table_name2]  -- 聯合查詢
[WHERE ...]  -- 指定結果需滿足的條件
[GROUP BY ...]  -- 指定結果按照哪幾個字段來分組
[HAVING]  -- 過濾分組后的記錄必須滿足的次要條件
[ORDER BY 字段 排序方式, ...]  -- 排序查詢
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 分頁查詢

• 1、單表查詢

SELECT 字段1,字段2,..,字段n FROM 表1;
/*查詢整張表的記錄*/
SELECT * FROM 表名;
/*別名--字段和表均可起別名,給表起別名可以方便SELECT語句的編寫*/
SELECT studentkey AS 學號,name AS 姓名 FROM stu AS 學生表;
/*聚合函數*/
SELECT COUNT(*) AS 學生人數 FROM stu;	-- C OUNT()不計入條件值為NULL的記錄

• 條件查詢——WHERE子句

關于WHERE的條件語句：

/*邏輯判斷*/
AND--&& OR--||,NOT和!不能直接替換

SELECT 屬性列表 FROM 表名 
WHERE [篩選條件];
例1：使用聚合函數，查詢表score中101課程的最高分和最低分(成績字段--result,編號字段--courseid)
SELECT MAX(result),MIN(result) FROM score
WHERE courseid='101';
/*模式匹配符`%`和`_`，通常與LIKE搭配使用*/
% 可以指代多個字符
_ 則只能指代一個字符
例子：查詢名字倒數第二個字為d的同學
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';

/*NULL值判斷,如果符合則返回true，否則返回false*/
WHERE 字段 IS NULL
WHERE 字段 IS NOT NULL

/*枚舉查詢 IN,NOT IN*/
- IN (屬性1，屬性2...屬性n)：查找字段值在屬性表中匹配的記錄
- NOT IN (...)：查找與屬性表中不匹配的記錄
【注】：枚舉查詢優化較差，通常使用其他語句代替

• 3、分組查詢——GROUP BY子句

/*PS: 分組之后SELECT返回的字段主要包含兩類：
	a. 分組的字段
	b. 聚合函數
*/
例1：使用HAVING子句，查找平均成績在85分以上的學生的學號和平均成績
SELECT studentkey,AVG(result) FROM score
GROUP BY studentkey
HAVING AVG(result)>=85;
-- 關于WHERE和HAVING子句：HAVING用于分組后再篩選，而WHERE用于分組前查詢

• 4、排序查詢

/*
asc 升序
desc 降序
指定查詢的結果按一個或多個條件排序,默認升序，并且當前面字段相同時，才會根據后面的字段排序
*/
SELECT *|字段 FROM 表名
ORDER BY 字段1 排序方式, ...;

• 5、分頁查詢

/*
：指定查詢的記錄從哪條至哪條
- 不包含起始索引，起始索引默認從0開始
- 每頁的起始索引 = (查詢頁號 - 1) * 每頁記錄數
*/
SELECT 字段列表 FROM 表名
LIMIT 起始索引，每頁記錄數;

• 6、MySQL中的流程控制

/*if語句*/
select if(gender=1,'男性員工','女性員工') '性別',count(*) from tb_emp
group by gender;

/*case-when-else-end語句*/
select (case job
           when 1 then '班主任'
           when 2 then '講師'
           when 3 then '學工主管'
           when 4 then '教導主任'
           else 'null' end) as 職位,count(*)
from tb_emp
group by job;

連接查詢（多表關聯查詢）

t1	m	n	t2	m	n
i1	1	a	j1	2	b
i2	2	b	j2	3	c
i3	3	c	j3	4	d

• 常用格式

SELECT 字段列表|* FROM 表1 {INNER | LEFT | RIGHT} JOIN 表2
ON 表1.字段x=表2.字段y
WHERE 篩選條件;	# where用于連接查詢后再篩選

• 1、內連接--INNER JOIN

-- 最后返回的結果是兩張表同時存在的數據行數，只存在于單張表的數據行并不會返回
例1： SELECT * FROM t1 INNER JOIN t2 ON t1.m=t2.m;
->返回結果：2 | b、3 | c

• 2、外連接

/*外連接之左外連接--LEFT JOIN*/
# 以JOIN左邊的表為'主表'，右邊的為'從表'，返回的數據行數以left表為行數，拼接右表中匹配的字段
# 對于左表中的數據行，當右表無匹配時，通常以NULL填充
例2： SELECT * FROM t1 LEFT JOIN t2 ON t1.m=t2.m;
->返回結果：i2|j1 ; i3|j2 ; i1|NULL

/*外連接之右外連接--RIGHT JOIN*/
# 以JOIN右邊的表為主表，左邊的為從表
例3： SELECT * FROM t1 RIGHT JOIN t2 ON t1.m=t2.m;
->返回結果：i2|j1 ; i3|j2 ; NULL|j3

• 3、自連接

自連接即自己和自己連接，相當于和另一張副本表連接
SELECT a.`courseid` AS '父課程',b.`courseid` AS '子課程'
FROM course AS a,course AS b
WHERE a.`courseid`=b.`pid`;

子查詢

• 單行子查詢

=等于，>大于，>=大于等于，<小于，<=小于等于，!=|<>不等于
-- 查找薪水大于所有員工平均薪水的員工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees);

/*子查詢返回結果為單行多列*/
-- 查詢tb_emp表中'入職日期'、'崗位'都與'張三'相同的員工信息
SELECT * FROM tb_emp WHERE (entrydate,job)=
(select entrydate,job FROM tb_emp WHERE name="張三")

• 多行子查詢

/*
[NOT] IN: [不]等于子查詢返回值中的任意一個
ANY: 需與單行比較操作符一起使用，表示至少或任一
ALL: 需與單行比較操作符一起使用，表示與子查詢的所有值進行比較
*/
-- 例1： 返回其它job id中比job id為'IT_PROG’部門任一工資低的員工的員工號姓名、job id以及salary
select last_name,job_id,salary
from employees
where salary < any (
    select salary from employees where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';

-- 例2：將子查詢作為一張'臨時表'使用
查詢入職日期是"2006-01-01"之后的員工信息，及其部門名稱
select e.*,d.name from 
(select * from tb_emp where entrydate > '2006-01-01') e, tb_dept d where e.dept_id=d.id;

事務

/*一個事務是一組操作，要么一起成功，要么一起失敗，當一個事務中的某次執行失敗時，可以通過回滾來恢復數據，當事務執行失敗時，不會影響原表中的數據，操作的結果只會作用于當前界面內的副本，保持了事務的一致性*/
begin ;
delete from tb_dept where name='學工部';
delete from tb_emp where id=1;
select * from tb_dept;
commit ;

rollback ;

• 四大特性

數據庫的優化——索引

索引(index)是幫助數據庫高效獲取數據的數據結構，使用空間來換時間，同時也降低了增刪改的效率

• 索引結構

默認是B+樹(多路平衡搜索樹)結構組織的索引

/*主鍵、唯一約束都會默認創建一個索引，主鍵索引是性能最高的*/
-- 創建索引
create [unique] index 索引名 on 表名(字段1,...)
-- 查看索引
show index from 表名
-- 刪除索引
drop index 索引名 on 表名

# 限定查詢 LIMIT
- LIMIT n;	//只返回查詢結果的前n行記錄
- LIMIT offset,length;		//返回從下標為offset處開始查詢，往后的length行記錄

- 分頁查詢公式: LIMIT (pageCount-1)*rowsCount,rowsCount;
pageCount:頁碼	rowsCount:每頁數據行數

## 聯合查詢 UNION和UNION ALL
UNION-- 自動去除重復行
UNION ALL-- 不去除重復行
【注】：使用UNION時上下 SELECT子句中字段數必須相同

## 唯一 UNIQUE
約束字段值不可重復，但 NULL 值不受約束

## 主鍵 PRIMARY KEY
 唯一+非空:主鍵修飾的字段不可為空
 CREATE TABLE t1
 (id int NOT NULL PRIMARY KEY,
  ...
 );		或者
 CREATE TABLE t1
 (id int NOT NULL,
  ...,
  PRIMARY KEY(id)	/*寫在字段聲明語句的后面*/
 );
 
 ## 自增長 AUTO_INCREMENT
 /*
 1.使用auto_increment的字段必須是一個鍵：UNIQUE | PRIMARY KEY
 2.自增長值默認從1開始
 3.auto_increment修飾字段可以顯式指定字段值，只要滿足唯一非空約束即可，且如果字段值>自增長值，則直接將字段值賦值給當前自增長值
 4.使用delete刪除記錄并不會影響自增長值
 5.使用'TRUNCATE 表名;'可以刪除整表記錄，并重置自增長計數器
 */
 CREATE TABLE t1(
 id int(11) NOT NULL UNIQUE|PRIMARY KEY AUTO_INCREMENT,
 ...
 );
 
 ## 外鍵
 /*外鍵可連接兩張表，從而使兩張表中的同一個字段同步，進行約束*/
 常用格式：
 CREATE TABLE t1(
     ...,
     CONSTRAINT 外鍵名 FOREIGN KEY(cuorseid) cuorseid REFERENCES t2(id),	
     -- 字段1為當前表中作為外鍵約束的數據
     -- 選擇作為外鍵約束的主表t2的字段id
     ...
 );
 【注】
# 外鍵約束有利有弊
# 優勢:
#       1. 添加數據有條件約束，防止添加錯誤
#       2. 刪除數據有條件約束，防止誤刪
# 劣勢:
#       1. 整個數據庫成為一坨，操作不便
#       2. 外鍵約束會降低【數據庫】的性能
#       3. 羈絆過多，耦合度太高了

# 心中有外鍵思想，數據庫無外鍵約束