數據庫的相關概念

一、數據庫的好處

可以持久化數據到本地
結構化查詢

二、數據庫的常見概念

1、DB：數據庫，儲存數據的容器
2、DBMS：數據庫管理系統，又稱爲數據庫軟體或數據庫產品，用於建立或管理DB
3、SQL：結構化查詢語言，用於和數據庫通訊的語言，不是某個數據庫軟體特有的，而是幾乎所有的主流數據庫軟體通用的語言

三、數據庫儲存數據的特點

1、數據存放到表中，然後表再放到庫中
2、一個庫中可以有多張表，每張表具有唯一的表名用來標識自己
3、表中有一個或多個列，列又稱爲「欄位」，相當於java中「屬性」
4、表中的每一行數據，相當於java中「物件」

四、常見的數據庫管理系統

mysql、oracle、db2、sqlserver

五、MySQL的優點

1、開源、免費、成本低
2、效能高、移植性也好
3、體積小，便於安裝

DQL語言

基礎查詢

一、語法

select 查詢列表
from 表名;

二、特點

1、查詢列表可以是欄位、常數、表達式、函數，也可以是多個
2、查詢結果是一個虛擬表

三、範例

1、查詢單個欄位

select 欄位名 from 表名;

SELECT `last_name` FROM `employees`;

2、查詢多個欄位

select 欄位名，欄位名 from 表名;

SELECT `last_name`,`email`,`employee_id`
FROM employees;

3、查詢所有欄位

select * from 表名

SELECT * FROM `employees`;

4、查詢常數

select 常數值;
注意：字元型和日期型的常數值必須用單引號引起來，數值型不需要

SELECT 100 ;

5、查詢函數

select 函數名(實參列表);

SELECT DATABASE();
SELECT VERSION();
SELECT USER();

6、查詢表達式

select 100/1234;

select 100/1234;

7、起別名

	①as
	②空格

#方式一：使用as關鍵字

SELECT USER() AS 使用者名稱;
SELECT USER() AS "使用者名稱";
SELECT USER() AS '使用者名稱';

SELECT last_name AS "姓 名" FROM employees;


#方式二：使用空格


SELECT USER()   使用者名稱;
SELECT USER()   "使用者名稱";
SELECT USER()   '使用者名稱';

SELECT last_name   "姓 名" FROM employees;

8、去重

select distinct 欄位名 from 表名;

#需求：查詢員工涉及到的部門編號有哪些


SELECT DISTINCT department_id FROM employees;

9、+

作用：做加法運算
select 數值+數值; 直接運算
select 字元+數值;先試圖將字元轉換成數值，如果轉換成功，則繼續運算；否則轉換成0，再做運算
select null+值;結果都爲null

-- 需求：查詢 first_name 和last_name 拼接成的全名，最終起別名爲：姓 名

#方案1：使用+    pass×
SELECT first_name+last_name AS "姓 名"
FROM employees;



#方案2：使用concat拼接函數

SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;

10、【補充】concat函數

功能：拼接字元
select concat(字元1，字元2，字元3,...);

SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;

11、【補充】ifnull函數

功能：判斷某欄位或表達式是否爲null，如果爲null 返回指定的值，否則返回原本的值

	select ifnull(commission_pct,0) from employees;

12、【補充】isnull函數

功能：判斷某欄位或表達式是否爲null，如果是，則返回1，否則返回0

條件查詢

一、語法

select 查詢列表
from 表名
where 篩選條件

#案例：查詢工資<15000的姓名、工資
SELECT last_name,salary
FROM employees
WHERE salary<15000;

二、篩選條件的分類

1、簡單條件運算子

> < = <> != >= <=  <=>安全等於

#案例：查詢部門編號不是100的員工資訊
SELECT *
FROM employees
WHERE department_id <> 100;

2、邏輯運算子

	&& and
	|| or
	!  not

#案例1：查詢部門編號不是 50-100之間員工姓名、部門編號、郵箱
#方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;
#方式2：


SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

3、模糊查詢

like:一般搭配萬用字元使用，可以判斷字元型或數值型
萬用字元：%任意多個字元，_任意單個字元

#案例1：查詢姓名中包含字元a的員工資訊
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

4、其他

between and

 功能：判斷某個欄位的值是否介於xx之間
 between and/not between and

#案例1：查詢部門編號是30-90之間的部門編號、員工姓名

#方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

in

功能：查詢某欄位的值是否屬於指定的列表之內
a  in(常數值1,常數值2,常數值3,...)
a not in(常數值1,常數值2,常數值3,...)
in/not in
is null /is not null：用於判斷null值


#案例1：查詢部門編號是30/50/90的員工名、部門編號


#方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

is null/is not null

is null PK <=>
			  普通型別的數值	     null值		       可讀性
is null		      ×			        √	             √
<=>			      √					√			     ×

#案例1：查詢沒有獎金的員工資訊
SELECT *
FROM employees
WHERE commission_pct IS NULL;

#案例2：查詢有獎金的員工資訊
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

=		只能判斷普通的內容

IS      只能判斷NULL值

<=>     安全等於，既能判斷普通內容，又能判斷NULL值

SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

排序查詢

一、語法

select 查詢列表
from 表
where 篩選條件
order by 排序列表 【asc}desc】

二、特點

1、asc ：升序，如果不寫預設升序
   desc：降序

2、排序列表 支援 單個欄位、多個欄位、函數、表達式、別名

3、order by的位置一般放在查詢語句的最後（除limit語句之外）

三、範例

1、按單個欄位排序

#案例1：將員工編號>120的員工資訊進行工資的升序
SELECT * 
FROM employees 

ORDER BY salary ;

#案例1：將員工編號>120的員工資訊進行工資的降序
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary DESC;

2、按表達式排序

#案例1：對有獎金的員工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

3、按別名排序

#案例1：對有獎金的員工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees

ORDER BY 年薪 DESC;

4、按函數的結果排序

#案例1：按姓名的字數長度進行升序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

5、按多個欄位排序

#案例1：查詢員工的姓名、工資、部門編號，先按工資升序，再按部門編號降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

6、補充選學：按列數排序

SELECT * FROM employees 
ORDER BY 2 DESC;

SELECT * FROM employees 
ORDER BY first_name;

常見函數

一、字元函數

方法	描述
concat(str1,str2)	拼接
substr(str,pos)	擷取從pos開始的所有字元，起索引從1開始
substr(str,pos,len)	擷取len個從pos開始的字元，起始索引從1開始
Length(str)	獲取位元組個數
upper(str)	變大寫
lower(str)	變小寫
trim(【substr from】str)	去前後之後指定的字元，預設是去空格
left(str,len)	從左邊擷取指定len個數的字元
right(str,len)	從右邊截指定Len個數的字元
lpad(str,substr,len)	左填充
rpad(str,substr,len)	右填充
strcmp(str1,str2)	比較兩個字元的大小
instr(str,substr)	獲取substr在str中的第一次出現的索引

範例：

CONCAT 拼接字元

SELECT CONCAT('hello', first_name,last_name) 備註
FROM employees;

LENGTH 獲取位元組長度

SELECT LENGTH('hello');

CHAR_LENGTH 獲取字元個數

SELECT CHAR_LENGTH('hell,xiaoming');

SUBSTRING 擷取子串

SELECT SUBSTR('hellowoorld',1,3);
SELECT SUBSTR('hellowoorld',2);

INSTR獲取字元第一次出現的索引

SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

TRIM去前後指定的字元，預設是去空格

SELECT TRIM('  xiaoming  ') AS a;

LPAD/RPAD 左填充/右填充

SELECT LPAD('小明',10,'a');

UPPER/LOWER 變大寫/變小寫

案例：查詢員工表的姓名，要求格式：姓首字元大寫，其他字元小寫，名所有字元大寫，且姓和名之間用_分割，最後起別名「OUTPUT」

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) AS 'OUTPUT'
FROM employees;

STRCMP 比較兩個字元大小

SELECT STRCMP('aec','aec');
SELECT STRCMP('adc','cfg');

LEFT/RIGHT 擷取子串

SELECT LEFT('鳩摩智',1);
SELECT RIGHT('鳩摩智',1);

二、數位函數

方法	描述
ceil(x)	向上取整
floor(X)	向下取整
round(x,d)	四捨五入
mod(x,y)	取模/取餘
truncate(x,d)	擷取，保留小數點後d位
abs(x)	求絕對值

範例：

ABS 絕對值

SELECT ABS(-2.4);

CEIL 向上取整返回>=該參數的最小整數

SELECT CEIL(-1.77999);
SELECT CEIL(-1.09);
SELECT CEIL(0.09);

FLOOR 向下取整，返回<=該參數的最大整數

SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);

ROUND 四捨五入

SELECT ROUND(4,555);
SELECT ROUND(1.8712345,2);

TRUNCATE 截斷

SELECT TRUNCATE(1.8776787,2);

MOD 取餘

SELECT MOD(10,3); // 1
SELECT MOD(-10,3); // -1
SELECT MOD(10,-3); // 1

三、日期函數

方法	描述
now()	獲取當前日趨–時間
curtime()	只有時間
curdate()	只有日期
date_format(date,格式)	格式日期爲字元
str_to_date(str，格式)	將字元轉換爲日期
datediff(date1,date2)	獲取兩個日期之間的天差數
year(date)	獲取年
month(date)	獲取月

SELECT NOW();

CURDATE

SELECT CURDATE();

DATEDIFF

SELECT DATEDIFF('1998-7-16','2019-7-13');

DATE_FORMAT

SELECT DATE_FORMAT('1998-7-19','%Y年%m月%d日 %H小時%i分鐘%s秒') 出生日期
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小時%i分鐘%s秒')入職日期

STR_TO_DATE 按指定格式解析字串爲日期型別

SELECT * FROM employees 
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');

四、流程控制語句

① if(條件，表達式1, 表達式2)：如果條件成立，返回表達式1，否則返回表達式2

② case 表達式

when 值1 then 結果1

when 值2 then 結果2

…

else 結果n

end

③ case

when 條件1 then 結果1

when 條件2 then 結果2

…

else 結果n

end

範例：

IF函數

SELECT IF(100>9,'好','壞');

需求：如果有獎金，則顯示最終獎金，如果沒有，則顯示0

SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 獎金
FROM employees;

CASE函數

案例：
部門編號是30，工資顯示爲2倍
部門編號是50，工資顯示爲3倍
部門編號是60，工資顯示爲4倍
否則不變
顯示部門編號，新工資，舊工資

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END newsalary
FROM employees;

案例：如果工資>20000,顯示級別A
工資>15000,顯示級別B
工資>10000,顯示級別C
否則，顯示D

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS '等級'
FROM employees

分組函數

方法	描述
sum	求和
avg	求平均數
max	求最大值
min	求最小值
count	計算非空欄位值的個數

範例：

#案例1 ：查詢員工資訊表中，所有員工的工資和、工資平均值、最低工資、最高工資、有工資的個數。

SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary)
FROM employees

分組查詢

一、語法

select 分組函數，分組的欄位

from 表名

where 分組前的篩選條件

grounp by 分組列表

having 分組後的篩選

order by 排序列表

二、特點

① 分組列表可以是單個欄位、多個欄位

② 篩選條件分爲兩類

	篩選的基表	使用的關鍵字	位置
分組前篩選	原始表	where	group by 前面
分組後篩選	分組後的結果集	having	group by 後面

三、執行順序

① from 子句

② where 子句

③ group by 子句

④ having 子句

⑤ select 子句

⑥order by 子句

範例：

1. 查詢每個工種的員工平均工資

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

查詢哪個部門的員工個數>5

SELECT COUNT(*) 員工個數,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

每個工種有獎金的員工的最高工資>12000的工種編號和最高工資

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

查詢沒有獎金的員工的最高工資>6000的工種編號和最高工資,按最高工資升序

SELECT MAX(salary) 最高工資, job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;

查詢每個工種每個部門的最低工資,並按最低工資降序 (工種和部門都一樣，纔是一組)

SELECT MIN(salary) 最低工資,job_id,department_id
FROM employees
GROUP BY job_id,department_id

連線查詢

說明：當查詢中涉及到了多個欄位，則需要通過多表連線

笛卡爾乘積：

出現原因：沒有有效的連線條件

解決辦法：新增有效的連線條件

一、SQL92語法

語法：

select 查詢列表 ①

from 表1 別名，表2 別名 ②

where 連線條件 ③

and 篩選條件 ④

group by 分組列表 ⑤

having 分組後的篩選 ⑥

order by 排序列表 ⑦

**執行順序：**② ③ ④ ⑤ ⑥ ① ⑦

1、等值連線

語法：

select 查詢列表

from 表1 別名，表2 別名

where 表1.key= 表2.key

【and 篩選條件】

【group by 分組欄位】

【having 分組後的篩選】

【order by 排序欄位】

程式碼範例：

等值連線：查詢女神名和對應的男神名

SELECT NAME ,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

查詢有獎金的員工名、部門名

SELECT last_name , department_name,commission_pct
FROM employees e , departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

3. 查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資

SELECT department_name, d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`department_id`;

查詢每個工種的工種名和員工的個數，並且按員工個數降序

SELECT job_title , COUNT(*)
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC

三表連線: 查詢員工名、部門名和所在的城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'

ORDER BY department_name DESC;

2、非等值連線

語法：

select 查詢列表

from 表1 別名,表2 別名

where 非等值的連線條件

【and 篩選條件】

【group by 分組欄位】

【having 分組後的篩選】

【order by 排序欄位】

3、自連線

語法：

select 查詢列表

from 表別名1,表別名2

where 等值的連線條件

【and 篩選條件】

【group by 分組欄位】

【having 分組後的篩選】

【order by 排序欄位】

程式碼範例：

自連線: 查詢員工名和上級的名稱

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

二、SQL99語法

1、內連線

語法：

select 查詢列表 ①

from 表1 別名 ②

【inner】 join 表2 on 連線條件 ③

【inner】 join 表3 on 連線條件 ③

where 篩選條件 ④

group by 分組列表 ⑤

having 分組後的篩選 ⑥

order by 排序列表 ⑦

執行順序： ② ③ ④ ⑤ ⑥ ① ⑦

特點：

① 表的順序可以調換

② 內連線的結果=多表的交集

③ n表連線至少需要n-1個連線條件

分類：

等值連線
1. 非等值連線
2. 自連線

程式碼範例:

查詢員工名和部門名

SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.`department_id`=d.department_id;

新增篩選條件: 查詢部門編號>100的部門名和所在的城市名

SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

新增分組+篩選+排序: 查詢部門中員工個數>10的部門名，並按員工個數降序

SELECT department_name,COUNT(*) 員工個數
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_id`
HAVING 員工個數>10
ORDER BY 員工個數 DESC;

非等值連線: 查詢部門編號在10-90之間的員工的工資級別，並按級別進行分組

SELECT COUNT(*) 個數,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary`AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.`grade`

自連線: 查詢員工名和對應的領導名

SELECT e.`last_name`, m.last_name
FROM employees e
JOIN employees  m
ON e.`manager_id`=m.employee_id;

2、外連線

語法：

select 查詢列表

from 表1 別名

left | right| full 【outer】 join 表2 別名 on 連線條件

where 篩選條件

group by 分組後的篩選

order by 排序列表

特點：

① 查詢的結果 = 主表的所有的行，如果從表和它比配的將顯示匹配行，如果從表沒有匹配的則顯示null

② left join 左邊的就是主表，right join 右邊的就是主表， full jion 兩邊都是主表

③ 一般用於查詢除了交集部分的剩餘的不匹配的行

程式碼範例：

查詢所有女神記錄，以及對應的男神名,如果沒有對應的男神，則顯示爲null。

左連線

SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

右連線：

SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.`boyfriend_id` = bo.`id`;

子查詢

一、含義

巢狀在其他語句內部的select語句稱爲子查詢或內查詢
外面的語句可以是insert、update、select等，一般select作爲外面的語句較多
外面如果爲select語句，則此語句稱爲外查詢或主查詢

二、分類

按出現位置

select 後面

僅僅支援標量子查詢

from 後面

表子查詢

where或having後面

標量子查詢

列子查詢

行子查詢

表子查詢

按結果集的行列

標量子查詢(單行子查詢)：結果集爲一行一列

列子查詢(多行子查詢):結果集爲多行一列

行子查詢：結果集爲多行多列

表子查詢：結果集爲多行多列

程式碼範例：

查詢最低工資大於50號部門最低工資的部門id和其最低工資

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
    );

返回job_id與141號員工相同，salary比143號員工多的員工姓名，job_id 和工資

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(
      SELECT job_id
      FROM employees
      WHERE employee_id = 141		
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

多行子查詢:返回location_id是1400或1700的部門中的所有員工姓名

SELECT last_name
FROM employees
WHERE department_id IN(
    # distinct 去重
    SELECT distinct department_id
    FROM departments
    WHERE location_id IN(1400,1700)
    );

放在select後面: 查詢部門編號是50的員工個數

SELECT (
	SELECT COUNT(*)
	FROM employees
	WHERE department_id = 50
) 個數;

放在from後面: 查詢每個部門的平均工資的工資級別

SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (

	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id

) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

放在exists後面

SELECT EXISTS(
	SELECT *
	FROM employees
	WHERE last_name='Abel'
) 有無Able;

分頁查詢

一、應用場景

當要查詢的條目數太多，一頁顯示不全

二、語法

select 查詢列表

from 表

limit【offset】size

注意：

offset代表的是起始條目的索引，預設開始從0開始
size代表的是條目數

公式：

假如要顯示的頁數爲page,每一頁的條目數爲size

select 查詢列表

from 表

limit(page - 1)* size, size

程式碼範例：

查詢員工資訊表的前5條

SELECT * FROM employees LIMIT 0,5;

查詢年薪最高的前10名

SELECT last_name, salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC
LIMIT 0,10

查詢有獎金的，且工資較高的第11名到第20名

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10,10;

聯合查詢

一、含義

union: 合併、聯合，將多次查詢的結果合併成爲一個點結果

二、語法

查詢語句1

union【all】

查詢語句2

union【all】

…

三、意義

將一條比較複雜的查詢語句拆分成爲多條語句
適用於查詢多個表的時候，查詢的列表是一致

四、特點

要求多查詢列數必須一致
要求多條查詢語句的查詢的各列型別、順序最好一致
union去重，union all 包含重複項

程式碼範例：

查詢所有國家的年齡>20歲的使用者資訊

SELECT * FROM usa WHERE uage > 20 UNION
SELECT * FROM chinese WHERE age >20

查詢所有國家的使用者姓名和年齡

SELECT uname,uage FROM usa
UNION
SELECT age,'name' FROM chinese

union自動去重/union all 可以支援重複項

SELECT 1,'小明' 
UNION ALL
SELECT 1,'小明' 
UNION  ALL
SELECT 1,'小明' 
UNION  ALL
SELECT 1,'小明' ;

DDL語言

庫的管理

① 建立庫

create database 【if not exists】庫名【characster set 字元集名】

② 修改表

alter database 庫名 character set 字元集名；

③ 刪除表

drop database 【if exists】庫名；

程式碼範例：

建立數據庫

CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

刪除數據庫

DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

表的管理

① 建立表

create table【if not exists】表名（

欄位名欄位型別【約束】，

欄位名欄位型別【約束】，

欄位名欄位型別【約束】，

…

）

② 修改表

1.新增列

alter table 表名 add conlumu 列名型別【firat|after】

2.修改列的型別或約束

alter table 表名 modify column 列名新型別【新約束】

3.修改列名

alter table 表名 change column 舊列名新列名型別

4.刪除列

alter table 表名 drop column 列名

5.修改表名

alter table 表名 raname【to】新表名

③ 刪除表

drop table【if exists】表名

④ 複製表

① 複製表的結構

creato table 表名 like 舊錶

② 複製表的結構+數據

create table 表名 select 查詢列表 from 舊錶【where 篩選】

程式碼範例：

沒有新增約束

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT ,
	stuname VARCHAR(20),
	stugender CHAR(1),
	email VARCHAR(20),
	borndate DATETIME

);

新增約束

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY,#新增了主鍵約束
	stuname VARCHAR(20) UNIQUE NOT NULL,#新增了唯一約束+非空
	stugender CHAR(1) DEFAULT '男',#新增了預設約束
	email VARCHAR(20) NOT NULL,
	age INT CHECK( age BETWEEN 0 AND 100),#新增了檢查約束，mysql不支援
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#新增了外來鍵約束

);

修改表

#1.修改表名

ALTER TABLE stuinfo RENAME TO students;

#2.新增欄位
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;

DESC students;

#3.修改欄位名

ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;

#4.修改欄位型別

ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;

#5.刪除欄位

ALTER TABLE students DROP COLUMN birthday;

DESC students;

刪除表

DROP TABLE IF EXISTS students;

複製表

#僅僅複製表的結構

CREATE TABLE newTable2 LIKE major;

#複製表的結構+數據

CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;


#案例：複製employees表中的last_name,department_id,salary欄位到新表 emp表，但不復制數據
CREATE TABLE emp
SELECT lat_name,department_id,salary
FROM myemployees.`employees`
WHERE 1=2;

數據型別

一、數值型

1、整型

tinyint 、 sallint、mediumint、int/integer、bigint

1 2 3 4 8

特點：

① 都可以設定無符號和有符號，預設通過unsigned設定無符號

② 如果超出範圍，會報 out or range 異常，插入臨界值

③ 長度可以不指定，預設會有一個長度，長度代表顯示的最大寬度，如果不夠則左邊用0 填充，但需要搭配zerofill，並且預設變爲無符號整型。

2、浮點型

定點數：decimal(M,D)

浮點數：

float(M,D) 4

double(M,D) 8

特點：

① M代表整數+ 小數部位的個數，D代表小數整位

② 如果超出範圍，則報out or range異常，並且插入臨界值

③ M 和 D都可以省略，但對於定點數，M預設爲10， D預設爲0

④ 如果精度要較高，則優先考慮使用定點數

二、字元型

char 、varchar、binary、enum、set、text、blob

char: 固定長度的字元，寫法爲char(M),最大長度不能超過M，其中M可以省略，預設爲1

varchar: 可變長度的字元，寫法爲varchar(M),最大長度不能M，不可省略M

三、日期型

year年

date日期

time時間

datetime 日期時間 8

timestamp 日期+時間 4 比較容易受時區、語法模式、版本的影響，更能反映當前時區的真實時間

常見的約束

一、常見的約束

約束	描述
NOT NUll	非空，該欄位的值必填
UnIQUE	唯一，該欄位的值不可重複
DEFAULT	預設，該欄位的值不用手動插入有預設值
CHECK	檢查，mysql不支援
PRIMARY KEY	主鍵，該欄位的值不可重複並且非空 unique+not null
FPREIGN KEY	外來鍵，該欄位的值參照了另外的標的欄位

主鍵和唯一

1.區別：

①一個表至多有一個主鍵，但可以有多個唯一

② 主鍵不允許爲空，唯一可以爲空

2.相同點：

都具有唯一性

都支援組合鍵，但不推薦

外來鍵：

① 用於限制兩個表的關係，從表的欄位值參照了主表的某個欄位值

② 外來鍵列和主表的被參照列要求型別一致，意義一樣，名稱無要求

③ 主表的被參照列要求是一個key(一般就是主鍵)

④ 插入數據，先插入主表

刪除數據，先刪除從表

可以通過以下兩種方式來刪除主表的記錄

程式碼範例：

方式一：級聯刪除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

方式二：級聯置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、建立表的新增約束

create table 表名（

欄位名欄位型別 not null ,#非空

欄位名欄位型別 primary key,#主鍵

欄位名欄位型別 unique,#唯一

欄位名欄位型別 default 值， #預設

constraint 約束名 foreign key(欄位名) references 主表(被參照列1)

）

注意：

	支援型別	可以起約束名
列級約束	除了外來鍵	不可以
表級約束	除了非空和預設	可以，但對主鍵無效

列級約束可以在一個欄位上追加多個，中間用空格隔開，沒有順序要求

三、修改表時新增或刪除約束

1.非空

新增非空

alter table 表名 modify column 欄位名欄位型別 not null;

刪除非空

alter table 表名 modify column 欄位名欄位型別;

2.預設

新增預設

alter table 表名 modify columu 欄位名欄位型別 default 值;

刪除預設

alter table 表名 modify column 欄位名欄位型別;

3.主鍵

新增主鍵

alter table 表名 add【 constraint 約束名】 primary key(欄位名)；

刪除主鍵

alter table 表名 add drop primary key;

4.唯一

新增唯一

alter table 表名 add【constraint 約束名】 unique(欄位名);

刪除唯一

alter table 表名 drop index 索引名;

5.外來鍵

新增外來鍵

alter table 表名 add【constraint 約束名】 foreign key(欄位名) references 主表(被參照列)

刪除外來鍵

alter table 表名 drop foreign key 約束名;

四、自增長列

特定：

1.不用手動插入值，可以自動提供序列值，預設從1開始，步長爲1

auto_increment_increment

如果要更改起始值：手動插入值

如果要更改步長：更改系統變數

set auto_increment_increment=值

2.一個表至多有一個自增長列

3.自增長列只能支援數值型

4.自增長列必須爲一個key

建立表時設定自增長列

create table 表(

欄位名欄位型別約束 auto_increment

)

修改表時設定自增長列

alter table 表 modify column colum 欄位名欄位型別約束 auto_increment

刪除自增長列

alter table 表 modify column 欄位名欄位型別約束

DML語言

插入

1、方式一

語法：

insert into 表名(欄位名,…) values(值,…);

特點：

1、要求值的型別和欄位的型別要一致或相容

2、欄位的個數和順序不一定與原始表中的欄位個數和順序一致但必須保證值和欄位一一對應

3、假如表中有可以爲null的欄位，注意可以通過以下兩種方式插入null值

①欄位和值都省略 ②欄位寫上，值使用null

4、欄位和值的個數必須一致

5、欄位名可以省略，預設所有列

2、方式二

語法：

insert into 表名 set 欄位=值,欄位=值,…;

兩種方式的區別：

1.方式一支援一次插入多行，語法如下： insert into 表名【(欄位名,…)】 values(值，…),(值，…),…;

2.方式一支援子查詢，語法如下： insert into 表名查詢語句;

修改

1、修改單表的記錄

語法：update 表名 set 欄位=值，欄位=值【where 篩選條件】

2、修改多表的記錄【補充】

語法：

update 表1 別名

left|right|inner join 表2 別名 on

連線條件 set 欄位=值，欄位=值

【where篩選條件】

刪除

1、使用delete

刪除單表的記錄

語法：delete 別名1，別名2 from 表1 別名

inner| left | right join 表2 別名 on 連線條件

【where 篩選條件】

2、使用truncate

語法：truncate table 表名

3、兩種方式的區別

#【面試題】delete和truncate的區別

1.delete可以新增WHERE條件

TRUNCATE不能新增WHERE條件，一次性清除所有數據

2.truncate的效率較高

3.如果刪除帶自增長列的表，

使用DELETE刪除後，重新插入數據，記錄從斷點處開始

使用TRUNCATE刪除後，重新插入數據，記錄從1開始

SELECT * FROM gradeinfo;

TRUNCATE TABLE gradeinfo;

INSERT INTO gradeinfo(gradename)VALUES(‘一年級’),(‘2年級’),(‘3年級’);

4.delete 刪除數據，會返回受影響的行數

TRUNCATE刪除數據，不返回受影響的行數

5.delete刪除數據，可以支援事務回滾

TRUNCATE刪除數據，不支援事務回滾

程式碼範例：

新增

INSERT INTO stuinfo values(1,'吳倩','女','[email protected]','2001.1.2');

修改(修改年齡<20的專業編號爲3號，且郵箱更改爲 [email protected])

UPDATE stuinfo SET majorid = 3,email = '[email protected]'
WHERE age<20;

刪除

#案例1：刪除姓李所有資訊
DELETE FROM stuinfo 
WHERE stuname LIKE '李%'；
#案例2：刪除表中所有數據
TRUNCATE TABLE stuinfo;

事務

1、含義

事務:一條或多條sql語句組成一個執行單位，一組sql語句要麼都執行要麼不執行

2、特點

A 原子性：一個事務是不可再分割的整體，要麼都執行要麼都不執行

C 一致性：一個事務可以使數據從一個狀態切換到另一個一致的狀態

I 隔離性：一個事務不受其他事務的幹擾，多個事務相互隔離的

D 永續性：一個事務一旦提交了，則永久的持久化到本地

3、事務的使用步驟

隱式（自動）事務：沒有明顯的開始的結束，本身就是一條事務可以自動提交，比如insert、update、delete 顯式事務：具有明顯的開啓和結束。

使用顯式事務：

①開啓事務

set autocommit = 0;
start transaction：#可以省略

設定回滾點：

提交：commit:

回滾：rollback;

回滾到指定的地方；rollback to 回滾點名：

4、併發事務

髒讀：一個事務讀取其他事務還沒有提交的數據，讀到的是其他事務「更新」的數據

不可重複讀：一個事務多次讀取，結果不一樣

幻讀：一個事務讀取了其他事務還沒有提交的數據，只是讀到的是其他事務"插入"的數據

隔離級別:

	髒讀	不可重複讀	幻讀
read uncommitted:讀未提交	×	×	×
read committed:讀已提交	√	×	×
repeatable read：可重複讀	√	√	×
serializable：序列化	√	√	√

MySQL基礎篇

數據庫的相關概念

一、數據庫的好處

二、數據庫的常見概念

三、數據庫儲存數據的特點

四、常見的數據庫管理系統

五、MySQL的優點

DQL語言

基礎查詢

一、語法

二、特點

三、範例

1、查詢單個欄位

2、查詢多個欄位

3、查詢所有欄位

4、查詢常數

5、查詢函數

6、查詢表達式

7、起別名

8、去重

9、+

10、【補充】concat函數

11、【補充】ifnull函數

12、【補充】isnull函數

條件查詢

一、語法

二、篩選條件的分類

1、簡單條件運算子

2、邏輯運算子

3、模糊查詢

4、其他

between and

in

is null/is not null

排序查詢

一、語法

二、特點

三、範例

1、按單個欄位排序

2、按表達式排序

3、按別名排序

4、按函數的結果排序

5、按多個欄位排序

6、補充選學：按列數排序

常見函數

一、字元函數

二、數位函數

三、日期函數

四、流程控制語句

分組函數

分組查詢

一、語法

二、特點

三、執行順序

連線查詢

一、SQL92語法

1、等值連線

2、非等值連線

3、自連線

二、SQL99語法

1、內連線

2、外連線

子查詢

一、含義

二、分類

分頁查詢

一、應用場景

二、語法

聯合查詢

一、含義

二、語法

三、意義

四、特點

DDL語言

庫的管理

表的管理

數據型別

一、數值型

二、字元型

三、日期型

2、使用truncate