# :-: MYSQL基礎
[toc]
### 1\. mysql常見命令
* 查看當前所有的數據庫`show databases`
* 使用指定的數據庫 `use 數據庫名`
* 查看數據庫下的所有表 `show tables`、`show tables from 數據庫名`
* 查看表的詳細信息`desc 表名`
* 查看mysql標本:`cmd中：mysql --version`、`mysql服務端中：select version()`
* mysql服務啟動與停止：`net start mysql`、`net stop mysql`
### 2. 函數
1.分組函數
* max
* min
* count
* sum
* avg
2. 單行函數
* concat
* instr
```
select instr('123666','666'); -- 輸出:4
找不到返回0
```
* substr:
```
select suvstr('1234567',6) -- 輸出：67
select substr('1234567',2,3) -- 輸出：234
```
* upper/lower
* trim
```
select trim(' abc abc '); -- 輸出:abc abc
select trim('aa' from 'aaa嘻嘻aaa嘻嘻aaaa') -- 輸出:a嘻嘻aaa嘻嘻
```
* lpad/rpad
```
select lpad('張三',3,'#') -- 輸出:#張三
select lpad('張三',1,'#') -- 輸出:張
select rpad('張三',1,'#') -- 輸出:張
select rpad('張三',3,'#') -- 輸出:張三#
```
* replace
* ifnull:`ifnull(commission_percentage,0)`
* length 輸出字節數
```
select lenght('張三')； -- GBK編碼中輸出：4，UTF-8中輸出：6
```
3.日期函數
* date_format
* str_to_date
```
%Y 年
%c或%m 月
%d 日
%H 24H
%h 12H
%i 分
%S或% 秒
%p 上下午
```
4.數學函數
* round
* ceil
* floor
* truncate`select truncate('3.14159',4); 輸出:3.1415`
* mod
```
select mod(10,3); -- 輸出:1
select mod(10,-3); -- 輸出:1
select mod(-10,3); -- 輸出:-1
```
5.流程控制函數
* if
* case
### 3.查詢
1. 分組查詢 group by
可按多個字段進行分組`group by 字段1，字段2...`
```
where和having的區別：
where：對原表中存在的字段進行篩選，放在group by之前。分組前篩選。
having: 對原表中不存在的字段進行篩選，放在group by之后。分組后篩選。
```
2.連接查詢
* sql 92
僅支持內連接(等值連接、非等值連接、自連接)
```
案例：
select * from employees e,departments d
where e.department_id = d.department_id and e.employee_id = 1001
```
* sql 99
支持內連接([inner] join)、外連接[左外、右外、全外(mysql不支持)]、交叉連接(cross join 笛卡爾積)
```
案例1：
select * from employees e inner join departments d
on e.department_id = d.department_id
where e.employee_id = 1001;
案例2:顯示所有女生的男朋友信息，沒有的返回null。left outer join outer可省略，左表為主表，右表為從表。
select * from girls g left outer join boys b
on g.boyfriend_id = b.id;
```
```
on:連接條件，where篩選條件。可提高分離性，便于閱讀。
```
3. 子查詢
```
標量子查詢：一行一列
行子查詢：一行多列
列子查詢：一列多行
表子查詢：多行多列
```
```
多行操作符：in/not in、some、any、all
```
select后僅支持標量子查詢。
```
案例1：
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 個數
FROM departments d;
案例2：
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部門名;
```
from后支持表子查詢，要給子查詢起別名。
```
案例：查詢每個部門的平均工資的工資等級。
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
```
where/having后標量子查詢、列子查詢、行子查詢。
exists相關子查詢。
```
案例：查詢沒有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE b.`boyfriend_id`=bo.`id`
);
```
4. 聯合查詢 union(去重)、union all
```
注意：
1>.要求多條查詢語句查詢列數是一致的。
2>.要求多條查詢語句的查詢的每一列的類型和順序最好一致(或兼容)。
```
### 4. DML和DDL
* DML：insert、update、delete
```
inset：
insert into student(stu_id,stu_name,stu_score) values(1001,'xss',92);
inset into student set id=1001,stu_name='xss',stu_score=92;
前一種支持多行插入，后一種不支持。
delete：
delete from 表名 ... where ...;
案例：多表刪除。刪除黃曉明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黃曉明';
update：
update 表名 set ... where ....;
案例：修改多表記錄，修改沒有男朋友的女神的男朋友編號都為2號
UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
```
```
面試題：truncate和delete的區別：
1.delete 可以加where 條件，truncate不能加
2.truncate刪除，效率高一丟丟
3.假如要刪除的表中有自增長列，如果用delete刪除后，再插入數據，自增長列的值從斷點開始，
而truncate刪除后，再插入數據，自增長列的值從1開始。
4.truncate刪除沒有返回值，delete刪除有返回值
5.truncate刪除不能回滾，delete刪除可以回滾.
```
* DDL：create、drop、alter
### 5. 數據類型
* 數值類型
整數：tinyint(1個字節) smallint(2) mediumint(3) int/integer(4) bigint(8)
一個字節：有符號：-128~127 無符號：0-255
```
① 如果不設置無符號還是有符號，默認是有符號，如果想設置無符號，需要添加unsigned關鍵字
② 如果插入的數值超出了整型的范圍,會報out of range異常，并且插入臨界值
③ 如果不設置長度，會有默認的長度,度代表了顯示的最大寬度，如果不夠會用0在左邊填充，但必須搭配zerofill使用.
```
小數：浮點數(double、float)、定點數[dec(M,D)、decimal(M,D),M為有效位數，D為小數位數] 定點數精確度高。
* 字符：主要有：varchar（長度不可省略，可變長）、char(長度可省略，定長)
* 日期：date(日期) 、 time(時間) 、datetime(時期+時間。不受時區影響) 、timestamp(時期+時間。受時區影響)
### 6. 事務
1.事務定義： 一個或一組SQL語句組成一個執行單元，這個執行單元要么全部執行，要么全部不執行。
2.事務特性：ACID
```
1.原子性(Atomicity):一個事務不可再分割，要么都執行要么都不執行
2.一致性(Consistency):一個事務執行會使數據從一個一致性狀態切換到另外一個一致性狀態
3.隔離性(Isolation):一個事物的執行不受其他事務的干擾
4.持久性(Durability):一個事務一旦提交，則會永久的改變數據庫的數據
```
3.事務創建
```
1.隱式事務：事務沒有明顯的開啟和結束標記
例如：insert、update、delete語句
2.顯示事務：事務具有明顯的開啟和結束的標記
開啟事務：set autocommit=0;
結束事務：commit;(提交事務) rollback;(回滾事務)
在開始事務和結束事務中間填寫要執行的增、刪、改、查的操作（DML）。
```
4. 事務并發問題
```
臟讀:事務A讀取了另一個事務未提交的數據.
不可重復讀:一個事務范圍內的2個相同的查詢前后返回不同的數據。A事務讀取了B事務已提交的數據。
幻讀: A事務讀取了B事務新增的數據。
```
**臟讀必須避免**
5.事務的隔離級別
```
read uncommited 可提交讀 事務并發問題：臟讀、不可重復讀、幻讀
read committed 不可提交讀(oracle默認) 事務并發問題：不可重復讀、幻讀
repeatable read 可重復讀(mysql默認) 事務并發問題：幻讀
serializable 序列化讀 無上述事務并發問題。事務串行化順序執行，效率低，比較消耗數據庫性能。
```
### 7. 視圖
視圖只保存了sql邏輯，不占實際物理空間。
優點:可實現對表的權限管理，只暴露特定的字段。可將常用的復雜的查詢寫入視圖。
缺點：效率低。
* 創建視圖
```
create view 視圖名
as
查詢語句;
//案例：查詢姓名中包含a字符的員工名、部門名和工種信息
//①創建(先創建出一個視圖，方便以后的查詢)
create view myv1
as
select last_name,department_id,job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on j.job_id = e.job_id;
//②使用
select * from myv1 where last_name like '%a%';
```
* 視圖修改
```
create or replace view 視圖名
as
查詢語句;
alter view myv3
as
查詢語句;
```
* 視圖刪除
```
drop view 視圖名,視圖名,...;
```
* 查看視圖
```
desc myv3;
show create view myv3;
```
### 8. 變量
* 系統變量
```
系統變量分為全局變量（global）和會話變量（session）。
說明：變量由系統定義，不是用戶定義，屬于服務器層面
注意：全局變量要加global關鍵字，若不寫默認是會話級別
//1.全局變量
show global variables; //查看所有全局變量
show global variables like "%char"; //查看滿足條件的部分全局變量
select @@global.autocommit; //查看指定的系統變量的值
set @@global.autocommit=0; //為變量賦值
//2.會話變量
show session variables;
```
* 自定義變量
```
自定義變量分為用戶變量和局部變量 。
說明：變量由用戶自定義，要求用戶聲明、賦值，然后才能使用。
用戶變量：
賦值操作符：=或:=
①聲明并初始化
SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;
②賦值（更新變量的值）
方式一：
SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;
方式二：
SELECT 字段 INTO @變量名
FROM 表;
③使用（查看變量的值）
SELECT @變量名;
局部變量：
/*
作用域：僅僅在定義它的begin end塊中有效
應用在 begin end中的第一句話
*/
①聲明
DECLARE 變量名 類型;
DECLARE 變量名 類型 【DEFAULT 值】;
②賦值（更新變量的值）
方式一：
SET 局部變量名=值;
SET 局部變量名:=值;
SELECT 局部變量名:=值;
方式二：
SELECT 字段 INTO 具備變量名
FROM 表;
③使用（查看變量的值）
SELECT 局部變量名;
```
### 9.存儲過程、函數
```
含義：一組預先編譯好的SQL語句的集合，理解成批處理語句
1、提高代碼的重用性
2、簡化操作
3、減少了編譯次數并且減少了和數據庫服務器的連接次數，提高了效率
```
* 存儲過程
```
定義：
CREATE PROCEDURE 存儲過程名(參數列表)
BEGIN
存儲過程體（一組合法的SQL語句）
END
```
參數模式：in 、out 、inout
參數列表：參數模式 參數名 參數類型
```
調用：call 存儲過程名(實參列表)
刪除：drop procedure 存儲過程名；
```
* 函數
```
定義：
CREATE FUNCTION 函數名(參數列表) RETURNS 返回類型
BEGIN
函數體
END
```
參數列表 ：參數名 參數類型
函數體：必須得有return語句，否則會報錯。
```
調用：SELECT 函數名(參數列表)；
刪除：drop function 函數名；
```
函數和存儲過程的區別：
```
存儲過程：可以有0個返回，也可以有多個返回，適合做批量插入、批量更新。
函數：有且僅有1 個返回，適合做處理數據后返回一個結果。
```
### 10. 約束
not null 、default、primary key 、foreign key 、 unique、check(mysql不支持)
```
外鍵約束：
set null 刪除后設置為null
restrict 不能刪除
no action 不能刪除
cascade 級聯刪除
```
自增長列：
```
一個表至多一個。列的類型只能為數值類型。
```
