數(shù)據(jù)庫的特點
- 1.將數(shù)據(jù)放到表中,表再放到庫中
- 2.一個數(shù)據(jù)庫中可以有多個表，每個表都有一個的名字，用來標(biāo)識自己。表名具有唯一性。
- 3、表具有一些特性，這些特性定義了數(shù)據(jù)在表中如何存儲，類似java中 “類”的設(shè)計。
- 4、表由列組成，我們也稱為字段。所有表都是由一個或多個列組成的，每一列類似java 中的”屬性”
- 5、表中的數(shù)據(jù)是按行存儲的，每一行類似于java中的“對象”。
MySQL服務(wù)的登錄和退出
方式一：
- 通過mysql自帶的客戶端
只限于root用戶
方式二：
- 通過windows自帶的客戶端
登錄： mysql 【-h主機名 -P端口號 】-u用戶名 -p密碼
退出：exit或ctrl+C
MySQL的常見命令
1.查看當(dāng)前所有的數(shù)據(jù)庫
show databases;
2.打開指定的庫
use 庫名
3.查看當(dāng)前庫的所有表
show tables;
4.查看其它庫的所有表
show tables from 庫名;
5.創(chuàng)建表
create table 表名(
列名 列類型,
列名 列類型，
。。。
);
6.查看表結(jié)構(gòu)
desc 表名;
7.查看服務(wù)器的版本
方式一：登錄到mysql服務(wù)端
select version();
方式二：沒有登錄到mysql服務(wù)端
mysql --version
或
mysql --V
MySQL的語法規(guī)范
1.不區(qū)分大小寫,但建議關(guān)鍵字大寫，表名、列名小寫
2.每條命令最好用分號結(jié)尾,不加分號,執(zhí)行多條語句可能報錯
3.每條命令根據(jù)需要，可以進行縮進 或換行
4.注釋
單行注釋：#注釋文字
單行注釋：-- 注釋文字
多行注釋：/* 注釋文字 */
SQL的語言分類
DQL（Data Query Language）：數(shù)據(jù)查詢語言
select
DML(Data Manipulate Language):數(shù)據(jù)操作語言
insert 、update、delete
DDL（Data Define Languge）：數(shù)據(jù)定義語言
create、drop、alter
TCL（Transaction Control Language）：事務(wù)控制語言
commit、rollback
SQL的常見命令
show databases； 查看所有的數(shù)據(jù)庫
use 庫名； 打開指定 的庫
show tables ; 顯示庫中的所有表
show tables from 庫名;顯示指定庫中的所有表
create table 表名(
字段名 字段類型,
字段名 字段類型
); 創(chuàng)建表
desc 表名; 查看指定表的結(jié)構(gòu)
select * from 表名;顯示表中的所有數(shù)據(jù)
執(zhí)行本地sql腳本
image.png
DQL語言的學(xué)習(xí)
著重號: ` 用于區(qū)別關(guān)鍵字和字段名
where 的篩選條件不支持別名
#進階1: 基礎(chǔ)查詢
/*
語法:
select 查詢列表 from 表名;
類似于: System.out.println(打印的東西);
特點:
1.查詢列表可以是: 表中的字段,常量值,表達式,函數(shù)
2.查詢的結(jié)果是一個虛擬的表格
*/
#在做操作之前,在最上面打開或啟動指定的數(shù)據(jù)庫
USE myemployees;
#查詢表中的單個字段
SELECT last_name FROM employees;
#同時查詢表中的多個字段
SELECT last_name,salary,email FROM employees;
#查詢表中的所有字段
SELECT * FROM employees;
#查詢常量值
SELECT 100%98;
#給字段起別名
/*
1.便于理解
2.若要查詢的字段有重名的情況,用別名可以區(qū)別開來
*/
#方式一: 用as
SELECT 100%98 AS 結(jié)果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二: 用空格
SELECT last_name 姓,first_name 名 FROM employees;
#案例: 查詢salary,顯示結(jié)果為 out put,要用引號引起來,否則會出現(xiàn)歧義
SELECT salary AS 'out put' FROM employees;
#去重
#案例: 查詢員工表中涉及到的所有的部門的編號
SELECT DISTINCT department_id FROM employees;
# +號的作用
/*
java中的+號:
1.運算符,兩個操作數(shù)都為數(shù)值型
2.連接符,只要有一個操作數(shù)為字符串
mysql中的+號:
僅僅只有一個功能:運算符
select 100+90; 兩個操作數(shù)都為數(shù)值型,則做加法運算
select '123'+90;只要其中一方為字符型,試圖將字符型數(shù)值轉(zhuǎn)換成數(shù)值型
若轉(zhuǎn)換成功,則繼續(xù)做加法運算
select 'John'+90;若轉(zhuǎn)換失敗,則將字符型數(shù)值轉(zhuǎn)換成0
select null+90; 只要其中一方為null,則結(jié)果肯定為null
*/
# 字段拼接: 用concat函數(shù)
#案例: 查詢員工名和姓連接成一個字段,并顯示為姓名
SELECT CONCAT('a','b','c') AS 結(jié)果;
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employees;
#顯示表結(jié)構(gòu):
DESC employees;
# 顯示出表employees的全部列,各個列之間用逗號連接,列頭顯示成OUT_PUT
#ifnull函數(shù): 根據(jù)字段進行判斷是否為空,參數(shù)1: 可能為null的字段,參數(shù)2: 若為null想返回的值,若不用ifnull函數(shù),則字段為null時,則會影響字段拼接的顯示
SELECT IFNULL(`commission_pct`,0) AS 獎金率,`commission_pct`
FROM employees;
SELECT
CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0)) AS OUT_PUT
FROM
employees;
#進階2: 條件查詢
/*
語法:
select
查詢列表
from
表名
where
篩選條件
分類:
1.按條件表達式篩選
簡單條件運算符: > < = <>(!=) >= <=
2.按邏輯表達式篩選
邏輯運算符:
&& || !
and or not
&&和and: 兩個條件都為true,結(jié)果為true,反之為false
||或or: 只要有一個條件為true,結(jié)果為true,范志偉false
!或not: 如果連接的條件本身為false,結(jié)果為true,反之為false
3.模糊查詢
like
between and
in
is null
*/
#按條件表達式篩選
#案例1: 查詢工資>12000的員工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
#案例2: 查詢部門編號不等于90的員工名和部門編號
SELECT
last_name,department_id
FROM employees
WHERE
department_id <> 90;
#2.按邏輯表達式篩選
#案例1:查詢工資z在10000到20000之間的員工名、工資以及獎金
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE salary >= 10000 AND salary <= 20000;
#案例2:查詢部門編號不是在90到110之間，或者工資高于15000的員工信息
SELECT
*
FROM
employees
WHERE
NOT(deparment_id >= 90 AND department_id <= 110) OR salary > 15000;
#department_id < 90 or department_id > 110 or salary > 15000;
#模糊查詢:
/*
like
特點:
1.一般和通配符一起搭配使用
通配符:
% 表示0個或任意多個字符
_ 表示任意單個字符
between and
in
is null/is not null
*/
#1.like: 因為完全匹配,字符型值必須用單引號引起來,%代表通配符:不確定的字符
#案例1:查詢員工名中包含字符a的員工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';#abc
#案例2:查詢員工名中第三個字符為e，第五個字符為a的員工名和工資
SELECT
last_name,salary
FROM
employees
WHERE
last_name LIKE '__e_a%';
#特殊情況: 以通配符為查詢結(jié)果
#案例3:查詢員工名中第二個字符為_的員工名
SELECT
*
FROM
employees
WHERE
last_name LIKE '_\_%';
# last_name like '_$_%' escape '$';#指定符號為轉(zhuǎn)義字符
#2.between and
/*
1.使用between and可以提高語句的簡潔度
2.包含兩邊臨界值
3.兩個臨界值不要調(diào)換順序
*/
#案例1:查詢員工編號在100到120之間的員工信息
SELECT
*
FROM
employees
WHERE
department_id BETWEEN 100 AND 120;
#等價于department_id >= 100 and department <= 120;
#3.in
/*
含義: 判斷字段的值是否屬于in列表中的某一項
特點:
1.用in提高語句間接度
2.in列表的值必須一致或兼容
3.不支持通配符的使用,因為 in('')等價于 = ''
#案例:查詢員工的工種編號是IT_PROG、AD_VP、AD_PRES中的一個員工名和工種編號
*/
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_VP','AD_PRES');
#等價于job_id = 'IT_PROT’OR job_id = 'AD_VP'OR JOB_ID ='AD_PRES';
#is null/is not null
/*
運算符的=或<>不能判斷null值
is null/is not null可以判斷null值
*/
#案例1:查詢沒有獎金的員工名和獎金率
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
commission_pct IS NOT NULL;
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#安全等于: 可讀性差
#案例1: 查詢工資為12000的員工信息
SELECT last_name,commission_pct,salary
FROM employees
WHERE salary <=> 12000;
#is null pk <=>
#is null: 僅僅可以判斷null值,可讀性高,建議使用
#<=>: 既可以判斷null值,又可以判斷普通的數(shù)值,可讀性較低
#2.查詢員工號為176的員工的姓名和部門號和年薪
SELECT
last_name,
department_id,
salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
WHERE
employee_id = 176;
SELECT *
FROM employees
WHERE commission_pct LIKE '%%' #
AND last_name LIKE '%%';
SELECT * FROM employees WHERE 1=0;#查詢不到結(jié)果
經(jīng)典面試題:
試問:
SELECT * FROM employees;
和
SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';
是否一樣? 原因
不一樣,因為第一條可以查找出字段中含有NULL值的,因為,在 SQL 中NULL 表示“未知”。也就是說，NULL 值表示的是“未知”的值。,like不能查詢得到不知道的值的字段.
SELECT * FROM employees WHERE commission_pct LIKE '%%' or last_name LIKE '%%' or employee_id like '%%';
而第一條和第三條一樣,以為第三條語句中,查詢條件之間是or,也就是取并集,始終會有一個字段是不能為NULL值的,例如當(dāng)主鍵的時候.
進階3: 排序查詢
/*
引入:
select * from employees;
語法:
select 查詢列表
from 表
where 篩選條件
order by 排序列表 (asc(升序)|desc(降序))
特點:
1、 asc代表的是升序,desc代表的是降序如果不寫，默認是升序
2、order by子句中可以支持單個字段、多個字段、表達式、函數(shù)、別名
3. order by子句一般是放在查詢語句的最后面，limit子句除外
*/
#案例: 查詢員工信息,要求工資從高到低排序
SELECT * FROM employees ORDER BY salary ASC;#ASC可省略,默認為升序
SELECT * FROM employees ORDER BY salary DESC;
#案例2:查詢部門編號>=90的員工信息，按入職時間的先后進行排序【添加篩選條件】
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;
#案例3:按年薪的高低顯示員工的信息和年薪【按表達式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) ASC;
#支持按別名排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 ASC;
#案例5:按姓名的長度顯示員工的姓名和工資【按函數(shù)排序】
SELECT LENGTH('john');#查詢字節(jié)長度函數(shù)
SELECT LENGTH(last_name) AS 字節(jié)長度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例6:查詢員工信息，要求先按工資升序，再按員工編號降序【按多個字段排序】第一個字段對排序影響最大
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
sql語句執(zhí)行順序
image.png
# 練習(xí):
#1.查詢員工的姓名和部門號和年薪，按年薪降序按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;
#2.選擇工資不在8000到17000的員工的姓名和工資，按工資降序
SELECT last_name,salary
FROM employees
WHERE #not(salary between 8000 and 17000)
salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#3.查詢郵箱中包含e的員工信息，并先按郵箱的字節(jié)數(shù)降序，再按部門號升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
#進階4: 常見函數(shù)
/*
概念:類似于java的方法，將一組邏輯語句封裝在方法體中，對外暴露方法名好處:
1、隱藏了實現(xiàn)細節(jié)
2、提高代碼的重用性
調(diào)用:select函數(shù)名(實參列表: 數(shù)據(jù)表)【from表】;
函數(shù)調(diào)用時最需關(guān)注的特點:
1.叫什么(函數(shù)名)
2.干什么(函數(shù)功能)
分類:
1.單行函數(shù): 如 concat,length,ifnull等
2.分組函數(shù): 功能: 做統(tǒng)計使用,又稱統(tǒng)計函數(shù),聚合函數(shù),組函數(shù)
*/
# 1.字符函數(shù)
#length: 獲取參數(shù)值的字節(jié)個數(shù)
SELECT LENGTH('john');
SELECT LENGTH('張三豐hhh');#一個中文占3個字節(jié),一個英文占一個字節(jié)
#可以查詢客戶端用的字符類型
SHOW VARIABLES LIKE '%char%';
#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名
FROM employees;
#3.upper,lower大小寫轉(zhuǎn)換函數(shù)
SELECT UPPER('john');
SELECT LOWER('JOHN');
#將姓變成大寫,將名變成小寫,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名
FROM employees;
#substr,substring: 截取字符串
#注意: 數(shù)據(jù)庫中的索引都是從1開始
#截取從指定索引處后面所有字符
SELECT SUBSTR('李莫愁愛上陸展元',7) AS out_put;
#截取從指定索引處指定字符長度的字符
# 參數(shù)1: 指定索引值 參數(shù)2: 指定字符長度
SELECT SUBSTR('李莫愁愛上陸展元',1,3) AS out_put;
#案例:姓名中首字符大寫，其他字符小寫然后用_拼接，顯示出來
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS 姓名
FROM employees;
#5.instr返回子串第一次出現(xiàn)的索引，如果找不到返回0
SELECT INSTR('李莫愁愛上陸展元','陸展元') AS out_put;
#6.trim函數(shù): 取出前后兩端內(nèi)容(空格)
SELECT LENGTH(TRIM(' 張翠山 ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaa張翠aaaaa山aaaaaa') AS out_put;
#7.lpad用指定的字符實現(xiàn)左填充達到指定長度
#當(dāng)指定字符長度小原字符長度時,從右開始往左截斷
SELECT LPAD('哈哈哈哈哈',1,'*') AS out_put;
#8.rpad用指定的字符實現(xiàn)右填充指定長度
SELECT RPAD('啊啊啊啊阿',10,'a') AS out_put;
#replace 替換字符
SELECT REPLACE('啊啊啊哦哦哦鵝鵝鵝','啊啊啊','呀呀呀');
image.png
#數(shù)學(xué)函數(shù)
#round 四舍五入,正負情況一樣
SELECT ROUND(1.65);
SELECT ROUND(1.357,2);#參數(shù)2: 保留小數(shù)點后指定位數(shù)
#(屋頂)ceil:向上取整,返回>=該參數(shù)的最小整數(shù)
SELECT CEIL(1.001);
#(地板)floor: 向下取整,返回<=該參數(shù)的最小整數(shù)
#truncate: 截斷: 參數(shù)1: 要截斷的指定小數(shù),參數(shù)2: 指定小數(shù)后幾位開始截斷
SELECT TRUNCATE(1.69999,1);
#取余: 正負跟被除數(shù)有關(guān)
/*
mod(a,b) = a-a/b*b
mod(-10,-3): -10-(-10)/(-3)*(-3)=-1
*/
SELECT MOD(10,-3);
#日期函數(shù)
#now 返回當(dāng)前系統(tǒng)日期+時間
SELECT NOW();
#curdate: 返回當(dāng)前系統(tǒng)日期,不包含時間
SELECT CURDATE();
#curtime: 返回當(dāng)前時間,不包含系統(tǒng)日期
SELECT CURTIME();
#year: 可以獲取指定的部分,年,月,日,小時,分鐘,秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1999-1-1') AS 年;
#獲取員工入職的年份
SELECT YEAR(hiredate) AS 年 FROM employees;
#獲取月份
SELECT MONTH(NOW() 月份;
#獲取月分名
SELECT MONTHNAME(NOW()) 月份名;
#str_to_date 將字符通過指定的格式轉(zhuǎn)換成日期
#數(shù)據(jù)庫接收前端頁面日期的返回值格式有多種,若數(shù)據(jù)庫中直接等號賦值可能會顯示不出來,因此就需要用到str_to_date進行格式轉(zhuǎn)換,
SELECT STR_TO_DATE('1999-4-3','%Y-%c-%d') AS out_put;
#查詢?nèi)肼毴掌跒?992-4-3的員工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3-1992','%c-%d-%Y');#有具體的業(yè)務(wù)場景
#date_format將日期轉(zhuǎn)換成字符
SELECT DATE_FORMAT(NOW(),'%y年%c月%d日') AS 結(jié)果;
#查詢有獎金的員工名和入職日期(xx月/xx日xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%c月/%d日%y年')
FROM employees
WHERE commission_pct IS NOT NULL;
#其他函數(shù)
SELECT VERSION();#當(dāng)前數(shù)據(jù)庫版本號
SELECT DATABASE();#當(dāng)前數(shù)據(jù)庫
SELECT USER();#當(dāng)前用戶
#路程控制函數(shù)
# if函數(shù): if-else的效果,參數(shù)1: 判斷條件,參數(shù)2:條件成立,返回表達式,參數(shù)3:條件不成立,返回表達式
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'沒獎金,嗚嗚嗚','有獎金,哈哈哈') AS 備注
FROM employees;
# case函數(shù): 使用一: switch case 效果
/*
java中
適用于等值判斷
switch(變量或表達式){
case 變量1: 語句1;break;
...
default: 語句n;break;
}
mysql中
case 判斷的字段或表達式
when 常量1 then 要顯示的值1或語句1(若為值可不加分號,若為語句則要加分號);
when 常量2 then 要顯示的值2或語句2(若為值可不加分號,若為語句則要加分號);
...
else(代表default) 要顯示的值n或語句n;
end
當(dāng)case和select搭配使用時,相當(dāng)于作為表達式來用,then后面就不能接語句了,只能接值
當(dāng)在存儲過程或函數(shù)中case判斷可以當(dāng)做單個語句來用,也就是不搭配select也可以直接用,then后面就接語句
當(dāng)then后面接值的時候就不用加分號,若為語句則要加分號
*/
/*案例:查詢員工的工資,要求
部門號=30，顯示的工資為1.1倍
部門號=40，顯示的工資為1.2倍
部門號=50，顯示的工資為1.3倍
其他部門，顯示的工資為原工資
*/
SELECT salary AS 原始工資,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工資
FROM employees;
#3.case 函數(shù)的使用二:類似于多重if
/*
java中:
if(條件1){
語句1;
}else if(條件2){
語句2;
}else if(條件3){
語句3;
}
...
else{
語句n;
}
mysql中
case
when 條件1 then 要顯示的值1或語句1;
when 條件2 then 要顯示的值2或語句2;
...
else 要顯示的值n或語句n;
end
*/
#案例:查詢員工的工資的情況
如果工資>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;
練習(xí):
#顯示系統(tǒng)時間(注:白期+時間)
SELECT NOW();
#2.查詢員工號，姓名，工資，以及工資提高百分之20%后的結(jié)果(new salary)
SELECT employee_id 工號, last_name 姓名,salary 工資, salary*1.2 AS 'new salary'
FROM employees;
#3.將員工的姓名按首字母排序，并寫出姓名的長度( length）
# 按首字母排序和按姓名排序效果不同,姓名的每個字母都會排序
SELECT LENGTH(last_name) 姓名長度, SUBSTR(last_name,1,1) AS 首字母, last_name
FROM employees
ORDER BY 首字母;
#4.做一個查詢，產(chǎn)生下面的結(jié)果
<last_name> earns <salary> monthly but wants <salary* 3>
Dream salary
King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salary*3) AS 'Dream salary'
FROM employees
WHERE salary = 24000;
# 5.用 case-when,按下面條件,
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLFRK E
產(chǎn)生下面的結(jié)果
Last_name job_id grade
king AD_PRES A
SELECT last_name,job_id AS job,CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLFRK' THEN 'E'
END AS grade
FROM employees
WHERE job_id = 'AD_PRES';
#二.分組函數(shù)
/*
功能: 用作統(tǒng)計使用,又稱為聚合函數(shù)或統(tǒng)計函數(shù)或組函數(shù)
分類:
sum 求和,avg 平均值,max 最大值,min 最小值,count 計算個數(shù)
特點:
1.sum,avg一般只用于處理數(shù)值型
max,min,count可以處理任何類型
2.以上分組函數(shù)都忽略null值
3.可以和distinct關(guān)鍵字搭配使用實現(xiàn)去重運算
4.count函數(shù)的單獨介紹
一般用count(*)用作統(tǒng)計行數(shù)
5.和分組函數(shù)一同查詢的字段要求是group by后的字段
*/
#1.簡單地使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;#salary字段有值的一共有幾個
#查詢多個分組函數(shù)
SELECT SUM(salary) 總和,AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最低,COUNT(salary) 個數(shù)
FROM employees;
SELECT SUM(salary) 總和,ROUND(AVG(salary),3) 平均,MAX(salary) 最大,MIN(salary) 最低,COUNT(salary) 個數(shù)
FROM employees;
#2.參數(shù)支持哪些類型
SELECT SUM(last_name),AVG(last_name) FROM employees;#sum和avg一般只處理數(shù)值型
SELECT SUM(hiredate),AVG(hiredate) FROM employees;#日期型也不行
SELECT MAX(last_name),AVG(last_name) FROM employees;
SELECT MAX(hiredate), AVG(hiredate) FROM employees;
#count計算非空的值的個數(shù)
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3.是否忽略null
都忽略null運算
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/72 FROM employees;
SELECT COUNT(commission_pct) FROM employees;
#4.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#5.count函數(shù)的詳細介紹
#統(tǒng)計非空的行數(shù)
SELECT COUNT(*) FROM employees;
#count里面可以加個常量值(通常加1較為簡單),相當(dāng)于在表中加了一列這個常量值,來統(tǒng)計非null數(shù)據(jù)的個數(shù)
SELECT COUNT(1) FROM employees;
效率: 有多種方法能干一件事時
myisam存儲引擎下,COUNT(*)的效率高,因為這個引擎內(nèi)部有個計數(shù)器,他直接就返回了個數(shù),所以*效率最高
innodb存儲引擎下,COUNT(*)和count(1)效率差不多,但比count(字段)要高一些,因為括號里加字段,他實際上要有個判斷,要判斷該字段的值是否為null,若為null則不加1,不為null,則加1,他有個篩選在里面
綜上所述,不管用哪個引擎下,用count(*)較多
#1.查詢公司員工工資的最大值,最小值，平均值,總和
SELECT MAX(salary) 最大值,MIN(salary) 最小值, AVG(salary) 平均值, SUM(salary) 總和 FROM employees;
#日期函數(shù): 求兩個日期的差值
SELECT DATEDIFF(NOW(),'2000-6-2');
#2.查詢員工表中的最大入職時間和最小入職時間的相差天數(shù)(DIFFRENCE)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 相差 FROM employees;
#3.查詢部門編號為90的員工個數(shù)
SELECT COUNT(*) FROM employees WHERE department_id = 90;
進階5: 分組查詢
/*
語法:
select 分組函數(shù),列(要求出現(xiàn)在group by的后面的字段)
from 表
[where 篩選條件]
group by 分組的列表
[order by 子句]
注意:
查詢列表必須特殊,要求是分組函數(shù)和group by后出現(xiàn)的字段
特點:
1.分組查詢中的篩選條件分為兩類
數(shù)據(jù)源 位置 關(guān)鍵字
分組前篩選 原始表 group by子句前 where
分組后篩選 分組后結(jié)果集 group by子句后 having
①分組函數(shù)做條件肯定是放在having子句中,也就是分組后篩選
②能用分組前篩選的,就優(yōu)先考慮用分組前篩選,考慮到性能問題
比如分組后的字段,用where或having都可以,優(yōu)先用where
*/
#案例1 :查詢每個工種的最高工資
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2:查詢每個位置上的部門個數(shù)
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#添加篩分組前的篩選條件
#案例1:查詢郵箱中包含a字符的,每個部門的平均工資
#group by 放在 where 后面,放在order by 前面
SELECT AVG(salary) 平均工資,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2:查詢每個領(lǐng)導(dǎo)手下有獎金的員工的最高工資
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加分組后的篩選條件
只要涉及的求個數(shù),就用count(*)
#①查詢每個部門的員工個數(shù)
where對分組前的結(jié)果進行篩選,必須凡在group BY 前,where過濾行
having對分組后的結(jié)果進行篩選,必須放在group BY 后,having過濾分組
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#②根據(jù)1的結(jié)果進行篩選，查詢哪個部門的員工個數(shù)>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例2:查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資
SELECT job_id 工種編號,MAX(salary) 最高工資
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例3:查詢領(lǐng)導(dǎo)編號>102的每個領(lǐng)導(dǎo)手下的最低工資>5000的領(lǐng)導(dǎo)編號是哪個，以及其最低工資
SELECT manager_id 領(lǐng)導(dǎo)編號,MIN(salary) 其最低工資
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;
#按表達式或函數(shù)分組
#案例:按員工姓名的長度分組，查詢每一組的員工個數(shù)，篩選員工個數(shù)>5的有哪些
SELECT LENGTH(last_name) 姓名長度,COUNT(*) 員工個數(shù)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;
#按多個字段分組
#案例:查詢每個部門每個工種的員工的平均工資
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
#添加排序
#案例:查詢每個部門每個工種的員工的平均工資，獎金不為空,平均工資大于10000,并且按平均工資的高低顯示
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC;
#1.查詢各job_id的員工工資的最大值，最小值，平均值，總和，并按job_id升序
SELECT job_id, MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;
#2.查詢員工最高工資和最低工資的差距(DIFFERENCE)
SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;
#3.查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在內(nèi)
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
#4.查詢所有部門的編號，員工數(shù)量和工資平均值,并按平均工資降序
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
#5.選擇具有各個job_id的員工人數(shù)
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
進階6：連接查詢
/*
含義: 又稱多表查詢,當(dāng)查詢的字段來自于多個表時,就會用到連接查詢
笛卡爾乘積現(xiàn)象: 表1 有m行,表2有n行,結(jié)果=m*n行
發(fā)生原因: 沒有有效的連接條件
如何避免: 添加有效的連接條件
分類:
按年代分類:
sql92標(biāo)準(zhǔn): 僅僅支持內(nèi)連接
sql99標(biāo)準(zhǔn)[推薦]: 支持內(nèi)連接+外連接(左外和右外)+交叉連接
按功能分類:
內(nèi)連接:
等值連接
非等值連接
外連接:
左外連接
右外連接
全外連接
交叉連接
*/
SELECT * FROM beauty;
SELECT `name`,boyName
FROM beauty,boys
WHERE beauty.`boyfriend_id` = boys.`id`;
一.sql92標(biāo)準(zhǔn)
#1.等值連接
/*
①多表等值連接的結(jié)果為多表的交集部分,因為只有連接條件值一樣才能查詢出來
②n表連接,至少需要n-1個連接條件
③多表的順序沒有要求
④一般要為表起別名,起法和當(dāng)時給字段起別名一樣
⑤可以搭配前面介紹的所有子句使用,比如排序,分組,篩選
*/
#原理: 一個一個匹配,但是會通過連接條件篩選
#案例1:查詢女神名和對應(yīng)的男神名
SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.`boyfriend_id` = boys.`id`;
#案例2:查詢員工名和對應(yīng)的部門名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;
#2.可以給表起別名
/*
①提高語句的簡潔度
②區(qū)分多個重名的字段
注意:
加入在執(zhí)行from時起了別名了,select查詢時還用原始表名限定就會報錯
因為根據(jù)執(zhí)行順序來講,第一先走from,走了from就有了別名了,相當(dāng)于生成了虛擬試圖,到select的時候就不認原始表名了
如果為表起了別名,則查詢的字段就不能使用原來的表名去限定,要么就不限定
*/
#3.兩個表順序可以調(diào)換,效果一樣,因為都是看連接條件的值一樣的才篩選出來
#查詢員工名、工種號、工種名
一般情況都會為表起別名在連接查詢的時候因為連接查詢經(jīng)常會有歧義的列
SELECT last_name,e.`job_id`,job_title
FROM employees e,jobs j #表間互換效果不變
WHERE e.`job_id` = j.`job_id`;
#4.可以加篩選
#案例:查詢有獎金的員工名、部門名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE employees.`department_id` = departments.`department_id`
AND commission_pct IS NOT NULL;
#案例2:查詢城市名中第二個字符為o的部門名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
#5、可以加分組
#案例1:查詢每個城市的部門個數(shù)
SELECT COUNT(*),city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;
#案例2:查詢有獎金的每個部門的部門名和部門的領(lǐng)導(dǎo)編號和該部門的最低工資
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE commission_pct IS NOT NULL AND d.`department_id` = e.`department_id`
GROUP BY department_name,d.`manager_id`;#當(dāng)分組時不確定部門名和領(lǐng)導(dǎo)id是否一一對應(yīng)時,把兩個都加上比較好
#6.可以加排序
#案例:查詢每個工種的工種名和員工的個數(shù)，并且按員工個數(shù)降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#7、可以實現(xiàn)三表連接
#案例:查詢員工名、部門名和所在的城市
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`;
2.非等值連接
#案例1:查詢員工的工資和工資級別
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
3.自連接(自己連接自己)
#案例:查詢員工名和上級的名稱
SELECT e.last_name,e.employee_id,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
練習(xí):
一.顯示員工表的最大工資,平均工資值
SELECT MAX(salary),AVG(salary)
FROM employees
二、查詢員工表的employee_id，job_id, last_name ，按department_id降序，salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary ASC;
三、查詢員工表的job_id中包含a和e的,并且a在e的前面
SELECT job_id
FROM employees
WHERE job_id LIKE'%a%e%';
四、已知表student,_里面有id(學(xué)號)，name，gradeId(年級編號)
已知表grade，里面有id(年級編號),NAME(年級名）
己知表result，里面有id,score,studentNo（學(xué)號)
要求查詢姓名,年級名,成績
SELECT s.name,g.name,r.score
FROM student s,grade g,result r
WHERE s.id = r.studentNo
AND g.id = g.gradeId;
五、顯示當(dāng)前日期，以及去前后空格，截取子字符串的函數(shù)
SELECT NOW();
SELECT TRIM(指定前后字符,FROM '')
SELECT SUBSTR(要截取的總字符串,起始索引,截取長度)
練習(xí)2:
#1.顯示所有員工的姓名,部門號和部門名稱。
USE myemployees;
SELECT last_name, e.department_id,department_name
FROM employees e,departments d
WHERE d.`department_id` = e.`department_id`;
#2.查詢90號部門員工的job_id和90號部門的location_id
SELECT e.job_id,d.location_id
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND e.department_id = 90;
#3.選擇所有有獎金的員工的last_name,department_name,location_id,city
SELECT e.last_name,d.department_name,l.location_id,city
FROM employees e,departments d,locations l
WHERE commission_pct IS NOT NULL
AND e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND e.`commission_pct` IS NOT NULL;
#4.選擇city在Toronto工作的員工的
SELECT last_name,job_id,e.department_id,d.department_name
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';
#5.查詢每個工種、每個部門的部門名、工種名和最低工資
SELECT d.department_name,job_title,MIN(salary)
FROM employees e,departments d,jobs j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY department_name,job_title;
#6.查詢每個國家下的部門個數(shù)大于2的國家編號
SELECT COUNT(*) 部門個數(shù),country_id
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY country_id
HAVING COUNT(*) > 2;
#7、選擇指定員工的姓名，員工號，以及他的管理者的姓名和員工號，結(jié)果類似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
SELECT e.last_name employees,e.employee_id "Emp#",m.last_name magager, m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar';
二.sql99語法
/*
語法:
select 查詢列表
from 表1 別名 [連接類型]
join 表2 別名
on 連接條件
[where 分組前的篩選條件]
[group by 分組]
[having 分組后的篩選條件]
[order by 排序列表]
內(nèi)連接(*): 連接類型: inner
外連接
左外(*): left [outer]
右外(*): right [outer]
全外: full [outer]
交叉連接: cross
*/
(一)內(nèi)連接
/*
語法:
select 查詢列表
from 表1 別名
inner join 表2 別名
on 連接條件;
分類:
等值
非等值
自連接
特點:
①添加排序,分組,篩選
②inner關(guān)鍵字可以省略
③篩選條件放在where后面,連接條件放在on后面,提高分離性,便于閱讀
④inner join 連接和sql92語法中的內(nèi)連接的等值連接效果一樣,都是查詢多表的交集
*/
1.等值連接
#案例1.查詢員工名、部門名
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;
#案例2.查詢名字中包含e的員工名和工種名（添加篩選)
SELECT last_name,job_title
FROM employees e INNER JOIN jobs j
ON e.`job_id` = j.`job_id` #連接條件的類型最好一樣
WHERE last_name LIKE '%e%';
#3．查詢部門個數(shù)>3的城市名和部門個數(shù)，（添加分組+篩選)
SELECT city,COUNT(*) 部門個數(shù)
FROM departments d
INNER JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;
#案例4.查詢哪個部門的員工個數(shù)>3的部門名和員工個數(shù)，并按個數(shù)降序（添加排序)
SELECT department_name,COUNT(*)
FROM departments d
INNER JOIN employees e
WHERE d.`department_id` = e.`department_id` #只要兩表連接就一定要加連接條件
GROUP BY d.`department_name`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;
#5.查詢員工名、部門名、工種名，并按部門名降序(添加三表連接,連接表前兩表之間一定要有連接條件,所以有一點連接順序)
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
ORDER BY d.`department_name` DESC;
二.非等值連接
#查詢員工的工資級別
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
#查詢工資級別的個數(shù)>20的個數(shù),并且按工資級別降序
SELECT grade_level,COUNT(*)
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;
三.自連接
#查詢員工名中包含字符k的員工的名字、上級的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';
四.外連接
/*
應(yīng)用場景: 用于查詢一個表中有,另一個表中沒有的記錄
特點:
1.外連接的查詢結(jié)果為主表中的所有記錄
若從表中有和它匹配的(連接條件一致),則顯示匹配的值
若從表中沒有和它匹配的,則顯示null
外連接查詢結(jié)果=內(nèi)連接結(jié)果+主表中有而從表中沒有的記錄
2.左外連接,left join 左邊的是主表
右外連接,right join 右邊的是主表
3.左外和右外交換兩個表的順序,可以實現(xiàn)同樣的效果
4.全外連接=內(nèi)連接的結(jié)果+表1中有但表2沒有的+表2中有但表1中沒有的記錄
要查詢的信息主要來自于哪個表,那么誰就是主表
*/
#引入:查詢男朋友不在男神表的的女神名
#左外連接
SELECT b.name,bo.boyName
FROM beauty b LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;
#右外連接
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
#案例1:查詢哪個部門沒有員工
#左外連接
SELECT d.*
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
#右外連接
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` IS NULL;
#全外連接(相當(dāng)于兩個集合的并集),mysql中不支持
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;
#交叉連接(就是笛卡爾乘積,只是把逗號改成cross join)
USE girls;
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
- 內(nèi)連接
image.png
- 左外連接
image.png
- 添加篩選條件
image.png
- 右外連接
image.png
- 添加篩選條件
image.png
- 全外連接
image.png
- 添加篩選條件
image.png
#—、查詢編號>3的女神的男朋友信息，如果有則列出詳細，如果沒有，用null填充
SELECT b.`id`,bo.*
FROM beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` > 3;
#二、查詢哪個城市沒有部門
departments是主表,locations是從表
SELECT city,d.*
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id` IS NULL;#從表的主鍵設(shè)空
#三、查詢部門名為SA工或IT的員工信息
SELECT e.*,d.`department_name`,d.`department_id`
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SA','IT');
進階7: 子查詢
/*
含義:
出現(xiàn)在其他語句中的select語句,稱為子查詢或內(nèi)查詢
外部的查詢的語句,稱為主查詢或外查詢
分類:
按子查詢出現(xiàn)的位置:
select后面:
僅僅支持標(biāo)量子查詢
from后面:
支持表子查詢
where或having后面:(重點)
標(biāo)量子查詢(重點): 單行子查詢
列子查詢(重點): 多行子查詢
行子查詢
exists后面(相關(guān)子查詢)
支持表子查詢
按結(jié)果集的行列數(shù)不同:
標(biāo)量子查詢(結(jié)果集只有一行一列)
列子查詢(結(jié)果集只有一列多行)
行子查詢(結(jié)果集有多行多列,一般為一行多列)
表子查詢(結(jié)果集一般為多行多列)
*/
一.where或having后面
1.標(biāo)量子查詢(單行子查詢)
2.列子查詢(多行子查詢)
3.行子查詢(多列多行)
子查詢的特點:
①子查詢放在小括號內(nèi)
②子查詢一般放在條件的右側(cè)
③標(biāo)量子查詢,一般搭配著單行操作符使用
> < >= <= = <>
列子查詢,一般搭配著多行操作符使用
IN,ANY/SOME,ALL
④子查詢的執(zhí)行優(yōu)先于主查詢執(zhí)行,主查詢的條件用到了子查詢的結(jié)果
1.標(biāo)量子查詢
#案例1:誰的工資比 Abel 高?
#①查詢Abel的工資
SELECT salary
FROM employees
WHERE last_name = 'Abel';
#②查詢員工的信息,滿足salary>①結(jié)果
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#案例2:返回job_id與141號員工相同，salary比143號員工多的員工姓名，job_id和工資
#①查詢141號員工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
#②查詢143號員工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
#③查詢員工的姓名,job_id和工資,要求job_id=①并且salary>②
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
);
#案例3:返回公司工資最少的員工的last_name,job_id和salary
#①查詢工資的最低工資
SELECT MIN(salary)
FROM employees
#②查詢last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
#案例4:查詢最低工資大于50號部門最低工資的部門id和其最低工資
#①查詢50號部門的最低工資
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;
#②查詢每個部門的最低工資
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#③篩選②,滿足min (salary) >①
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#非法使用標(biāo)量子查詢
篩選條件本應(yīng)該用標(biāo)量子查詢,但卻用了列子查詢
篩選條件中用單行操作符時,括號里只能搭配標(biāo)量子查詢
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT salary
FROM employees
WHERE department_id = 50
);
#2.列子查詢(一列多行)
#案例1:返回location_id是1400或1700的部門中的所有員工姓名
#①查詢location_id是1400或1700的部門編號
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
#②查詢員工姓名,要求部門號是①列表中的某一個
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#或
SELECT last_name
FROM employees
WHERE department_id =ANY(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例1:返回location_id不是1400或1700的部門中的所有員工姓名
SELECT last_name
FROM employees
WHERE department_id NOT IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#或
SELECT last_name
FROM employees
WHERE department_id <>ALL(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2:返回其它工種中比job_id為'IT_PROG'部門任一工資低的員工的員工號、姓名、job_id 以及salary
比任意一個小,小于最大值,比全部都小,小于最小值
#①查詢job_id為'IT_PROG'部門任一工資
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
#②查詢員工號、姓名、job_id 以及salary,,salary<(1)的任意一個
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#案例3:返回其它工種中比job_id為'IT_PROG'部門所有工資低的員工的員工號、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
SELECT MIN(salary)
FROM employees
WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
#3、行子查詢(結(jié)果集一行多列或多行多列)
#案例:查詢員工編號最小并且工資最高的員工信息
將多個字段當(dāng)成一個虛擬字段來用
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#①查詢最小的員工編號
SELECT MIN(employee_id)
FROM employees
#②查詢最高工資
SELECT MAX(salary)
FROM employees
#③查詢員工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
二.select后面
/*
僅僅支持標(biāo)量子查詢
*/
#案例:查詢每個部門的員工個數(shù)
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) 個數(shù)
FROM departments d;
#案例2:查詢員工號=102的部門名
SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id = 102
)部門名;
#三,from后面
/*
將子查詢結(jié)果充當(dāng)一張表,要求必須起別名
*/
#案例:查詢每個部門的平均工資的工資等級
#①查詢每個部門平均工資
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②連接①的結(jié)果集和job_grades表，篩選條件平均工資between lowest_sal and highest_sal
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 highestsal
四.exists后面(相關(guān)子查詢)
/*
語法:
exists(完整的查詢語句)
結(jié)果: 1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 30000);
#案例1:查詢有員工的部門名
#查詢某個字段的值,根據(jù)exists函數(shù)查詢的過濾,所以叫相關(guān)子查詢,意思是exists子查詢涉及到了主查詢的字段
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
);
#或
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
);
#案例2:查詢沒有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT `boyfriend_id`
FROM beauty
);
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT `boyfriend_id`
FROM beauty b
WHERE bo.`id` = b.`boyfriend_id`
)
8.分頁查詢(重點)
/*
應(yīng)用場景: 當(dāng)要顯示的數(shù)據(jù),一頁顯示不全,需要分頁提交SQL請求
語法:
select 查詢列表
from 表1
[join type join 表2
on 連接條件
where 分組前篩選條件
group by 要分組的字段
having 分組后的篩選條件
order by 排序的字段]
limit [offset] size; 若從第一條開始顯示,其實索引可以省略
執(zhí)行順序: from > join > on > where > group by > having > select > order by > limit
offset: 要顯示的條目的起始索引(起始索引從0開始)
size: 要顯示的條目個數(shù)
特點:
①limit語句從執(zhí)行上或是語法上都是放在查詢的最后
②公式
要顯示的頁數(shù)page,每頁的條目數(shù)size
select 查詢列表
from 表
limit (page -1)*size,size;
假如:size=10
page 起始索引
1 0
2 10
3 20
*/
#案例1:查詢前五條員工信息
SELECT *
FROM employees
LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
#案例2: 查詢第11條~25條
SELECT * FROM employees LIMIT 10,15;
#案例3:有獎金的員工信息，并且工資較高的前10名顯示出來
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;
進階9.聯(lián)合查詢
聯(lián)合和表連接的區(qū)別: 表連接是增加不同字段,聯(lián)合是增加同字段多行數(shù)據(jù)
/*
union: 聯(lián)合 合并: 將多條查詢語句的結(jié)果合并成一個結(jié)果
語法:
查詢語句1
union
查詢語句2
union
...
就是單純的聯(lián)合結(jié)果而已,沒有內(nèi)部邏輯聯(lián)系
應(yīng)用場景:
要查詢的結(jié)果來自于多個表,且多個表沒有直接的連接關(guān)系,但查詢的信息要一致
特點:
1.要去多條查詢語句的查詢列數(shù)是一致的
2.要求多條查詢語句的查詢的每一列的類型和順序最好是一致的,順序默認顯示是第一個表的
3.union關(guān)鍵字默認是去重的,若使用union all 可以包含重復(fù)項
*/
#引入的案例:查詢部門編號>90或郵箱包含a的員工信息
#舊方法
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90;
#union
#當(dāng)兩個表的字段沒有關(guān)聯(lián)時,union可以將兩個表關(guān)聯(lián)起來，就起作用了
#聯(lián)合的兩個表的字段要一樣多,否則會報錯
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;
#案例:查詢中國用戶中男性的信息以及外國用戶中年男性的用戶信息
SELECT id,cname FROM t_ca WHERE csex = '男'
UNION
SELECT t_id,tName FROM t_ua WHERE tGender = 'male';
#union all 包含重復(fù)項
SELECT id,cname FROM t_ca WHERE csex = '男'
UNION ALL
SELECT t_id,tName FROM t_ua WHERE tGender = 'male';
DML語言
/*
數(shù)據(jù)操作語言:
插入: insert
修改: update
刪除: delete
*/
一.插入語句
方式一: 經(jīng)典的插入
/*
語法:
insert into 表名((列名)字段名,...) values(值1,...);
*/
#字段類型Nullable意思是選填,可以不用管
#1.插久的值的類型要與列的類型一致或兼容
SELECT * FROM beauty;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐藝昕','女','1990-4-23','1986156261',NULL,2);
#2.不可以為null的列必須插入值?？梢詾閚ull的列如何插入值?
#方式一: 將可為空的列名寫上,值用null填充
SELECT * FROM beauty;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐藝昕','女','1990-4-23','1986156261',NULL,2);
#方式二: 可為空的字段和其對應(yīng)的值都省略不寫
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','18664844848');
#3.列的順序可以調(diào)換
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蔣欣','女',17,'110');
#4.列數(shù)和值的個數(shù)必須一致
#5.可以省略不寫列名，但是默認是所 有列，而且列的順序和表中列的順序一致
INSERT INTO beauty
VALUES(18,'張飛','男',NULL,'119',NULL,NULL);
方式二:
/*
語法:
insert into 表
set 列名=值,列名=值,...
*/
INSERT INTO beauty
SET id = 19,NAME = '劉濤',phone = '999';
兩種方式大pk
1.方式一支持插入多行,方式二不支持
一條語句批量插入三行
INSERT INTO beauty
VALUES(23,'唐藝昕1','女','1990-4-23','1986156261',NULL,2),
(24,'唐藝昕2','女','1990-4-23','1986156261',NULL,2),
(25,'唐藝昕3','女','1990-4-23','1986156261',NULL,2);
2.方式一支持子查詢,方式二不支持
INSERT INTO + SELECT 相當(dāng)于從其他表復(fù)制數(shù)據(jù)
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','116686';#相當(dāng)于把子查詢查詢出的結(jié)果集插入到對應(yīng)的列里去
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyName,'125616'
FROM boys WHERE id < 3;
二.修改語句
/*
1.修改單表的記錄(重點)
語法:
update 表名
set 列=新值,列=新值,...
where 篩選條件;(若果不加where,則修改表中所有行)
執(zhí)行順序: update(鎖定數(shù)據(jù)源) > where(進行過濾篩選) > set(修改值)
2.修改多表的記錄[補充]
語法:
sql92語法: 只支持內(nèi)連接
update 表1 別名,表2 別名
set 列=值
where 連接條件
and 篩選條件;
sql99語法:
update 表1 別名
inner|left|right join 表2 別名
on 連接條件
set 列=值,...
where 篩選條件;
*/
1.修改單表的記錄
#案例1:修改beauty表中姓唐的女神的電話為13899888899
UPDATE beauty
SET phone = '13899888899'
WHERE NAME LIKE '唐%';
#案例2:修改boys表中id好為2的名稱為張飛，魅力值10
UPDATE boys
SET boyName = '張飛',userCP = 10
WHERE id = 2;
2.修改多表的記錄
#案例1:修改張無忌的女朋友的手機號為114
UPDATE boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
SET phone = '114'
WHERE boyName = '張無忌';
#案例2:修改沒有男朋友的女神的男朋友編號都為2號
要改的字段是哪張表,那張表就是主表
#修改beauty表,所以beauty表是主表
UPDATE boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.boyfriend_id = 2
WHERE bo.id IS NULL;
三.刪除語句
/*
方式一: delete
語法:
1.單表的刪除[重點]
delete from 表名 where 篩選條件
2.多表的刪除[補充]
sql92語法:
delete 表的別名 #要刪除哪張表的記錄就寫哪張表,若要刪除兩張表級聯(lián)的,兩張都要寫
from 表1 別名,表2 別名 #表1和表2連接,笛卡爾乘積
where 連接條件
and 篩選條件;
sql99語法:
delete 表1的別名,表2的別名
from 表1 別名
inner|left|right join 表2 on 連接條件 #兩個表連接出來一個新的結(jié)果集
where 篩選條件;
方式二: truncate
語法: truncate table 表名; 刪除整個表的數(shù)據(jù),不能加where條件
*/
#方式一: delete
#1.單表的刪除
#案例1: 刪除手機號以9結(jié)尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;
2.多表的刪除
#案例:刪除張無忌的女朋友的信息(存在的信息用內(nèi)連接)
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName = '張無忌';
#案例:刪除黃曉明的信息以及他女朋友的信息(級聯(lián)刪除)
DELETE bo,b
FROM boys bo
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE bo.boyName = '黃曉明';
方式二: truncate語句(也叫清空數(shù)據(jù),不能加where)
#案例:將魅力值>100的男神信息刪除
TRUNCATE TABLE boys;
DELETE vs TRUNCATE (面試題☆)
/*
1.delete可以加where條件,truncate不能加
2.truncate刪除,效率高一點點
3.假如要刪除的表中有自增長列,
若用delete刪除后,再插入數(shù)據(jù),自增長列的值從斷點處(刪除的位置)開始,
而truncate刪除后,再插入數(shù)據(jù),自增長列的值從1開始
4.truncate刪除后沒有返回值(受影響的行數(shù)為0),delete刪除有返回值
5.truncate刪除不能回滾,delete刪除可以回滾
*/
SELECT * FROM boys;
DELETE FROM boys;
TRUNCATE FROM boys;
INSERT INTO boys (boyName,userCP)
VALUES('張飛',100),('劉備',100),('關(guān)云長',100);
#DDL語言:
/*
數(shù)據(jù)定義語言
庫和表的管理
一.庫的管理
創(chuàng)建,修改,刪除
二.表的管理
創(chuàng)建,修改,刪除
創(chuàng)建: create
修改: alter
刪除: drop
*/
一.庫的管理
1.庫的創(chuàng)建
/*
語法:
create database [if not exists]庫名; xxx exists: 容錯性處理
*/
#案例:創(chuàng)建庫Books
CREATE DATABASE IF NOT EXISTS books;
2.庫的修改(目前不能修改)
RENAME DATABASE bookes TO 新庫名; #已廢除
更改庫的字符集
ALTER DATABASE books CHARACTER SET gbk;
庫的刪除
DROP DATABASE IF EXISTS books;
二.表的管理
1.表的創(chuàng)建(重點)
/*
語法:
create table 表名(
列名 列的類型[(長度) 約束],
列名 列的類型[(長度) 約束],
列名 列的類型[(長度) 約束],
...
列名 列的類型[(長度) 約束]
)
*/
#案例: 創(chuàng)建表Book
CREATE TABLE Book(
id INT,
bName VARCHAR(20),
price DOUBLE,
authorId INT,
publishDate DATETIME
);
DESC book;
2.表的修改
只有庫和表的創(chuàng)建和刪除時候可以用exists處理容錯性,列沒有這功能
/*
alter table 表名 add|drop|modify|change column 列名 [列類型 約束];
*/
①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;#change的column可以省略
②修改列的類型或約束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
③添加新的列
ALTER TABLE 表名 ADD COLUMN 列名 列類型;
④刪除列
ALTER TABLE 表名 DROP COLUMN 列名;
⑤修改表名
ALTER TABLE 表名 RENAME TO 新列名;
3.表的刪除
DROP IF EXISTS 表名;
SHOW TABLES;#查看當(dāng)前庫的所有表
#通用寫法: 想自己設(shè)計數(shù)據(jù)庫和表,可以這樣寫,數(shù)據(jù)庫或表里有數(shù)據(jù)不能這么寫
DROP DATABASE IF EXISTS 舊庫名;
CREATE DATABASE 新庫名;
DROP TABLE IF EXISTS 舊表名;
CREATE TABLE 表名();
4.表的復(fù)制
INSERT INTO 表名 VALUES
(1,'村上春樹','日本'),
(2,'莫言','中國'),
(3,'馮唐','中國'),
(4,'金庸','中國');
#1.僅僅復(fù)制表的結(jié)構(gòu)(表里沒數(shù)據(jù))
CREATE TABLE 新表名 LIKE 要復(fù)制的表名;
#2.復(fù)制表的結(jié)構(gòu)+數(shù)據(jù)
CREATE TABLE 新表名
SELECT * FROM 要復(fù)制的表名;
#3.只復(fù)制部分?jǐn)?shù)據(jù)
CREATE TABLE 新表名
SELECT id,au_name
FROM author
WHERE nation = '中國';
#4.僅僅復(fù)制某些字段,但沒有數(shù)據(jù)
CREATE TABLE 新表名
SELECT id,au_name
FROM author
WHERE 0;#或 1=2; 代表任何字段都不滿足
#標(biāo)識列
標(biāo)識列的類型只能數(shù)值類型
AUTO_INCREMENT
#TCL
/*
Transaction Control Language 事務(wù)控制語言
事務(wù):
一個或一組sql語句組成一個執(zhí)行單元,這個執(zhí)行單元要么沒全部執(zhí)行,要么全部不執(zhí)行
在這個單元中,每個sql語句都是相互依賴的(捆綁式執(zhí)行)
案例: 張三豐向郭襄轉(zhuǎn)賬500
余額:
張三豐: 1000
郭襄: 1000
轉(zhuǎn)賬后:
update表set張三豐的余額=500 where name='張三豐'
假如在執(zhí)行完第一條sql語句,出現(xiàn)了以外,數(shù)據(jù)庫掛掉了,這樣就會導(dǎo)致上面執(zhí)行成功,下面失敗了
這樣就會變成張三豐500,郭襄1000,導(dǎo)致數(shù)據(jù)不可靠,所以事務(wù)就是用來解決這種現(xiàn)象的,主要用在這種場合
update表set郭襄的余額=1500 where name='郭襄'
所以我們希望,這個多條SQL語句組成一個獨立的執(zhí)行單元,這個執(zhí)行單元要么全部執(zhí)行成功,要么全部失敗,這就是事務(wù)的意思
回滾: 把之前所有做的操作撤銷,從新回到最初的狀態(tài); 舉例: 加入平時安裝軟件的時候,安裝到某個步驟是出現(xiàn)錯誤,軟件提示繼續(xù)或者撤銷,假如選了撤銷,安裝進度就回到最初的起點
事務(wù)的ACID特性:(面試重點)
原子性(Atomicity)(最重要): 一個事務(wù)不可再分割,要么都執(zhí)行,要么都不執(zhí)行.(比如找工作時,兩個人是捆綁式就業(yè),要么兩人都要,要么都不要)
一致性(Consistency): 一個事務(wù)執(zhí)行會使數(shù)據(jù)從一個一致狀態(tài)切換到另外一個一致狀態(tài).(比如轉(zhuǎn)賬的時候,轉(zhuǎn)賬前和轉(zhuǎn)賬后兩人的余額之和一致,數(shù)據(jù)還是準(zhǔn)確可靠的)
隔離性(Isolation): 一個事務(wù)的執(zhí)行不受其他事務(wù)的干擾.(要看隔離級別,有的隔離級別會受干擾)(在開發(fā)中,有可能多個事務(wù)并發(fā)地操作同一個數(shù)據(jù)庫的數(shù)據(jù))
持久性(Durability): 一個事務(wù)一旦提交,則會永久改變數(shù)據(jù)庫的數(shù)據(jù).(比如: 刪除就是一個事務(wù))
事務(wù)的創(chuàng)建
隱式事務(wù):事務(wù)沒有明顯的開啟和結(jié)束的標(biāo)記,也就是看出來是事務(wù),自動開啟自動結(jié)束
比如insert, update,delete語句,也就是平時寫一條insert語句就相當(dāng)于開啟了一個事務(wù)并且提交了
*/
支持事務(wù)的原理是行鎖,只有innodb支持行鎖,所以只有innodb支持事務(wù)
存儲引擎:不同的數(shù)據(jù)庫服務(wù)器默認的存儲引擎不同,在mysql中,數(shù)據(jù)不同的存儲技術(shù),稱作存儲引擎,也叫表類型
查看存儲引擎
SHOW ENGINES;
用的最多的引擎:
INNODB 現(xiàn)在默認的存儲引擎 支持事務(wù)
MYISAM 5.5版本前默認的 不支持事務(wù)
MEMORY 不支持事務(wù)
查看變量
#自動提交的功能它的值默認是開啟的
SHOW VARIABLES LIKE 'autocommit';#Value=ON
好比下面一句就代表一個事務(wù),一執(zhí)行就滿足事務(wù)自動提交的特點
DELETE FROM 表 WHERE id = 1;
弊端: 當(dāng)工作單元有多條sql語句就
假如要做一件事需要用到兩條sql語句,希望把兩條sql語句合并成一條事務(wù),但是默認一條語句就是一條事務(wù)
這時就要用到顯示事務(wù): 事務(wù)具有明顯的開啟和結(jié)束的標(biāo)記
前提:必須先設(shè)置自動提交功能為禁用,因為默認是開啟的
#關(guān)閉自動提交功能只對當(dāng)前事務(wù)(會話)有效,并不是關(guān)一次就永遠生效
#所以每次開機顯示事務(wù)都要手動禁用
SET autocommit = 0;
書寫步驟1:開啟事務(wù)
SET autocommit = 0;#必須的
START TRANSACTION;#可選的
步驟2: 編寫s事務(wù)中的sql語句(SELECT,INSERT,UPDATE,DELETE)(DDL語言沒有事務(wù)之說,例如:CREATE,ALTER,DROP)
語句1;
語句2;
...
步驟3: 結(jié)束事務(wù)
COMMIT;#提交事務(wù)
ROLLBACK;#回滾事務(wù),發(fā)生異常時
#演示事務(wù)的使用步驟
DROP TABLE IF EXISTS account;
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('張無忌',1000),('趙敏',1000);
#開啟事務(wù)
SET autocommit = 0;
START TRANSACTION;
#編寫一組事務(wù)的語句
UPDATE account SET balance = 1000 WHERE username = '張無忌';
UPDATE account SET balance = 1000 WHERE username = '趙敏';
#結(jié)束事務(wù): 要么提交要么回滾,不能同時一起執(zhí)行,只有有了結(jié)束的標(biāo)記,才決定是撤銷還是提交到磁盤文件,在沒結(jié)束前,數(shù)據(jù)只是駐留在內(nèi)存
ROLLBACK;#數(shù)據(jù)沒有變,可以理解為,結(jié)束事務(wù)之前,數(shù)據(jù)只是保存到了內(nèi)存,并沒有提交到磁盤文件
#commit;
SELECT * FROM account;
2.delete和truncate在事務(wù)使用時的區(qū)別
DDL語言不能回滾,truncate是DDL語言,delete是DML
#演示delete
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#演示truncate
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;#實際刪除,不支持回滾
ROLLBACK;
事務(wù)并發(fā)問題的介紹
臟讀: 事務(wù)1讀取已經(jīng)被事務(wù)2更新的但還沒被提交的字段.之后若事務(wù)2回滾,事務(wù)1讀取的內(nèi)容是臨時且無效的
不可重復(fù)讀: 事務(wù)1讀取了一個字段,然后事務(wù)2更新了該字段,之后,事務(wù)1再次讀取同一個字段,值就不同了;同一個事務(wù)多次查詢的結(jié)果不一樣
幻讀: 事務(wù)1從表中讀取一個字段,然后事務(wù)2在該表中插入了一些新的行,之后,如果事務(wù)1再次讀取同一個表,就會多出幾行
修改表的字符集
SET NAMES gbk;
savepoint的使用,只搭配rollback使用
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id = 1;#已刪除
SAVEPOINT a;#設(shè)置保存點名為a
DELETE FROM account WHERE id = 2;#未刪除
ROLLBACK TO a;#回到保存點
SELECT * FROM account;
