[toc]
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
一荧关、基礎(chǔ)查詢方法
SHOW DATABASES;
SELECT VERSION();
SHOW TABLES;
USE mysql;
SHOW TABLES;
STATUS;
SELECT * FROM db;
USE myemployees;
- 查看有多少個(gè)表
SHOW TABLES;
- 查看表的結(jié)構(gòu)
SELECT * FROM departments;
SELECT * FROM jobs;
SELECT * FROM employees;
- 查詢表中的單個(gè)字段
SELECT last_name FROM employees;
- 查詢表中的多個(gè)字段
SELECT last_name,first_name, FROM employees;
SELECT * FROM employees;
SELECT last_name,phone_number FROM employees;
- 查詢表中所有字段
SELECT
employee_id,
`first_name`,
`last_name`,
`phone_number`,
`last_name`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;
SELECT * FROM employees;
- 常量查詢
SELECT 100;
SELECT "john";
- 查詢表達(dá)式
SELECT 100*32.46545;
- 查詢系統(tǒng)函數(shù)
查看MySQL系統(tǒng)版本
SELECT VERSION();
- 起別名
#1)使用as
SELECT 100.154*34.14 AS 結(jié)果;
SELECT first_name AS 姓,last_name AS 名 FROM employees;
#2)使用空格
SELECT email 郵箱 FROM employees;
#案例查詢：salary挡闰，顯示結(jié)果為out put
USE employees;
SELECT salary AS "output" FROM employees;
- 去重
#查詢所有員工表中所有涉及到的部門編號
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;
- +號的作用
#僅僅是一個(gè)運(yùn)算符
SELECT 100+80;
SELECT '123'+2;
SELECT 'yang'+299;
SELECT NULL+2134;
SELECT TRUE+234;
案例
查詢員工和姓名連接成一個(gè)字段，并顯示為姓名
SELECT CONCAT('a','b','c') AS 結(jié)果;
SELECT
CONCAT(first_name,'-',last_name) AS 姓名
FROM
employees;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
二宛裕、條件查詢
/*
語法：
select
查詢列表
from
表名
where
篩選條件;
分類：
一槽惫、按條件表達(dá)式篩選
簡單條件運(yùn)算符：> < = != <> >= <=
二、按邏輯表達(dá)式篩選
邏輯運(yùn)算符：
作用：用于連接條件表達(dá)式
&& || !
and or not
&&和and：兩個(gè)條件都為true骇钦，結(jié)果為true宛渐，反之為false
||或or： 只要有一個(gè)條件為true，結(jié)果為true司忱，反之為false
!或not： 如果連接的條件本身為false皇忿，結(jié)果為true，反之為false
三坦仍、模糊查詢
like
between and
in
is null
*/
- 按條件查詢
#案例1：查詢工資>12000的員工信息 大于>
SELECT * FROM employees WHERE salary>10000;
#案例2：查詢部門編號不等于90號的員工名和部門名 不等于 <> !=
SELECT last_name,department_id FROM employees WHERE department_id<>90;
SELECT last_name,department_id FROM employees WHERE department_id!=90;
- 按邏輯表達(dá)式查詢
#查詢工資在10000到20000之間的員工名鳍烁、工資以及獎(jiǎng)金 && and 條件都為真時(shí)才執(zhí)行
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 AND salary <= 20000;
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 && salary <= 20000;
#案例3：查詢部門編號不是在90到110之間，或者工資高于15000的員工信息
# 不在什么區(qū)間可以用 not( * and * ) 或 !( * and * )
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
SELECT
*
FROM
employees
WHERE
!(department_id>=90 AND department_id<=110) OR salary>15000;
- 模糊查詢
/*
like
between and
in
is null|is not null
*/
- like模糊查詢
/*
特點(diǎn)：
一般和通配符使用：
% 任意多個(gè)字符,包含0個(gè)
_ 任意單個(gè)字符
*/
#案例1：查詢員工名包含字符a的員工信息
SELECT last_name FROM employees WHERE last_name LIKE "%a%";
#案例2：查詢員工名中包含第二個(gè)字符為u繁扎，第五個(gè)字符為o的員工名和工資
SELECT last_name,salary FROM employees WHERE last_name LIKE '_u_o%';
#案例3：查詢員工名中第二個(gè)字符為_的員工名 可以用轉(zhuǎn)義 \ ,也可以用ESCAPE $把 $當(dāng)成轉(zhuǎn)義符號.
SELECT last_name FROM employees WHERE last_name LIKE "_$_%" ESCAPE "$";
SELECT last_name FROM employees WHERE last_name LIKE "_\_%";
- between and可以提高語句的簡潔度
#案例1：查詢員工編號在100到120之間的員工信息
#第一種方法
SELECT
*
FROM
employees
WHERE
employee_id>=100 AND employee_id<=120;
#第二種方法
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
- in
/*
含義：判斷某字段的值是否屬于in列表中的某一項(xiàng)
特點(diǎn)：
①使用in提高語句簡潔度
②in列表的值類型必須一致或兼容
③in列表中不支持通配符
*/
#案例：查詢員工的工種編號是IT——PROG幔荒、AD_VP、AD_PRES中的一個(gè)員工和工種編號
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id = "IT_PROG" OR job_id="AD_VP" OR job_id="AD_PRES";
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN("IT_PROG","AD_VP","AD_PRES");
- null
/*
=或<>不能用于判斷null值
is null或is not null 可以判斷null值
*/
#案例1：查詢沒有獎(jiǎng)金的員工名和獎(jiǎng)金率
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
#案例2：查詢有獎(jiǎng)金的員工名和獎(jiǎng)金率,獎(jiǎng)金
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
#安全等于 <=>可以結(jié)合null使用
# 可以結(jié)合<=>使用梳玫，還可以結(jié)合變量使用
#案例3：查詢沒有獎(jiǎng)金的員工名和獎(jiǎng)金率
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例4：查詢工資為12000的員工信息
SELECT * FROM employees WHERE salary <=>12000;
#IS NULL:僅僅可以判斷NULL值爹梁，可讀性較高，建議使用
#<=> :既可以判斷NULL值提澎，又可以判斷普通的數(shù)值姚垃，可讀性較低
#案例5：查詢員工為176的員工的姓名和部門名和年薪
SELECT
last_name,employee_id,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees
WHERE
employee_id = 176;
#employee_id <=>176
SELECT job_id FROM employees;
SELECT DISTINCT job_id FROM employees;
SELECT * FROM employees;
SELECT * FROM employees WHERE commission_pct LIKE "%%" AND last_name LIKE "%%";
SELECT * FROM employees WHERE commission_pct LIKE "%%" OR last_name LIKE "%%";
#獲取表的結(jié)構(gòu)
DESC employees;
#查看詳細(xì)表的結(jié)構(gòu)
show create table employees;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
三、排序查詢
/*
語法：
select 查詢列表
from 表名
【where 篩選條件】
order by 排序的字段或表達(dá)式;
特點(diǎn)：
1盼忌、asc代表的是升序积糯，可以省略
desc代表的是降序
2掂墓、order by子句可以支持 單個(gè)字段、別名看成、表達(dá)式君编、函數(shù)、多個(gè)字段
3川慌、order by子句在查詢語句的最后面吃嘿，除了limit子句
*/
- 按單個(gè)字段排序
#這里不寫默認(rèn)是asc代表升序,后面添加desc代表降序
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary DESC;
- 添加篩選條件再排序
#案例：查詢部門編號>=90的員工信息，并按員工編號降序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;
- 按表達(dá)式排序
#案例：查詢員工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
- 按別名排序
#案例：查詢員工信息 按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
- 按函數(shù)排序
#案例：查詢員工名梦重，并且按名字的長度排序
SELECT LENGTH(last_name) AS 名字長度,last_name
FROM employees
ORDER BY 名字長度 DESC;
SELECT LENGTH(last_name) AS 名字長度,last_name
FROM employees
ORDER BY LENGTH(last_name) ASC;
- 按多個(gè)字段排序
#案例： 查詢員工信息兑燥，要求先按工資降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
- 練習(xí)案例：
#案例1：查詢員工的姓名和部門號和年薪忍饰，按年薪降序 按姓名升序
USE myemployees;
SHOW TABLES;
SELECT last_name AS 姓名,department_id AS 部門號,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC,姓名 ASC;
#案例2：選擇工資不在8000到17000的員工的姓名和工資贪嫂，按工資降序
SELECT last_name 姓名,salary 工資
FROM employees
WHERE 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;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
四、常見函數(shù)
/*
概念：類似于java的方法斗塘，將一組邏輯語句封裝在方法體中赢织，對外暴露方法名
好處：1、隱藏了實(shí)現(xiàn)細(xì)節(jié) 2馍盟、提高代碼的重用性
調(diào)用：select 函數(shù)名(實(shí)參列表) 【from 表】;
特點(diǎn)：
①叫什么（函數(shù)名）
②干什么（函數(shù)功能）
分類：
1于置、單行函數(shù)
如 concat、length贞岭、ifnull等
2八毯、分組函數(shù)
功能：做統(tǒng)計(jì)使用，又稱為統(tǒng)計(jì)函數(shù)瞄桨、聚合函數(shù)话速、組函數(shù)
常見函數(shù)：
一、單行函數(shù)
字符函數(shù)：
length:獲取字節(jié)個(gè)數(shù)(utf-8一個(gè)漢字代表3個(gè)字節(jié),gbk為2個(gè)字節(jié))
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
數(shù)學(xué)函數(shù)：
round
ceil
floor
truncate
mod
日期函數(shù)：
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
load_file
其他函數(shù)：
version
database
user
控制函數(shù)
if
case
*/
單行函數(shù)
- 字符函數(shù)
#1. length 獲取參數(shù)值的字節(jié)個(gè)數(shù)
SELECT LENGTH("TOM");
SELECT LENGTH("豬文文");
SHOW VARIABLES LIKE "%char%"; #查看系統(tǒng)的字符集
#2.concat 拼接字符串
SELECT CONCAT(last_name,"_",first_name) 姓名 FROM employees;
#3.upper(將字母都變大) lower(將字母都變小)
SELECT UPPER("joung");
SELECT LOWER("HOjg");
#示例：將姓變大芯侥，名變小泊交，然后拼接
SELECT CONCAT(UPPER(last_name),"-",LOWER(first_name)) FROM employees;
#4. substr substring
#注意：索引從1開始
#截取從指定索引處后面所有字符 從字符7到結(jié)束
SELECT SUBSTR("豬文文愛上一頭大蠢豬楊",7) 結(jié)果;
#截取從指定索引處指定字符長度的字符
SELECT SUBSTR("豬文文愛上一頭大蠢豬楊",1,3) 結(jié)果;
#案例：姓名中首字符大寫，其他字符小寫然后用_拼接柱查，顯示出來
SELECT CONCAT(last_name,"_",first_name) 結(jié)果 FROM employees;
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",LOWER(SUBSTR(last_name,2))) 結(jié)果 FROM employees;
#5. instr 返回字符串第一次出現(xiàn)的索引廓俭，如果找不到返回0,默認(rèn)索引是從1開始的
SELECT INSTR("豬文文愛上一頭大蠢豬文文","你") 結(jié)果;
#6. trim 默認(rèn)只能去掉空格，不能去掉制表符/t,也可以指定去掉的字符(去掉前后的空格或指定其他的字符)
SELECT LENGTH(TRIM(' 張翠 山 ')) 結(jié)果;
SELECT LENGTH(' 張翠 山 ') 結(jié)果;
SELECT TRIM('a' FROM 'aaaaa張aaaaa翠山aaaaaaaaaaaaaaaaaaa') 結(jié)果;
#7. lpad用于指定字符串實(shí)現(xiàn)左填充指定長度
SELECT
LPAD("殿速速", 6, "$") 結(jié)果;
#8. rpad用于指定字符實(shí)現(xiàn)右填充長度
SELECT RPAD("你是誰",6,"$") jieuog;
#9. replace全部替換
SELECT REPLACE("豬寶寶大豬寶寶文文文文","寶","$") jieguo;
#10讀取文件且返回文件內(nèi)容為字符串
update t set blob_col=load_file('/tmp/picture') where id=1;
- 數(shù)學(xué)函數(shù)
#round 四舍五入
SELECT ROUND(-132.633511) result;
SELECT ROUND(-132.233511) result;
SELECT ROUND(431.5345) result;
SELECT ROUND(431.2345) result;
#ceil 向上取整,返回>=該參數(shù)的最大整數(shù)(進(jìn)1法),對正數(shù)有用
SELECT CEIL(-12.4564);
SELECT CEIL(12.1545);
SELECT CEIL(12.6654);
#floor 向下取整唉工，返回<=該參數(shù)的最大整數(shù)(進(jìn)1法),只能對負(fù)數(shù)有用,對正數(shù)無用
SELECT FLOOR(-9.4564) result;
SELECT FLOOR(-121.1) result;
SELECT FLOOR(16546.5111) result;
SELECT FLOOR(2.4) resultl;
#truncate 截?cái)嘌衅梗?shù)位截?cái)?不會(huì)四舍五入,也不會(huì)向下取整
SELECT TRUNCATE(1.45646,1);
SELECT TRUNCATE(-4564.4654,2);
SELECT TRUNCATE(4654,2);
#mod取余
/*
mod(a,b): 等價(jià)于a-a/b*b
*/
SELECT MOD(10,-3); #10-(10)/(-3)*(-3)
SELECT MOD(-10,-3);
SELECT MOD(-10,-3);
SELECT MOD(10,3);
#產(chǎn)生0-1的隨機(jī)數(shù)
SELECT RAND();
#需求產(chǎn)生255-4000的隨機(jī)數(shù)
select ROUND(RAND()*3745+255);
#利用md5產(chǎn)生32位隨機(jī)數(shù)
SELECT MD5(RAND()*1000);
SELECT LENGTH(MD5(RAND()*1000));
#利用rand()和concat()函數(shù)生成11位隨機(jī)數(shù)
#生成11位隨機(jī)手機(jī)號函數(shù)：CONCAT('1',CEILING(RAND()*9000000000+1000000000));
select concat('1',ceiling(rand()*90000000000+1000000000));
- 日期函數(shù)
#now 返回當(dāng)前系統(tǒng)日期+時(shí)間
SELECT NOW();
#curdate 返回當(dāng)前系統(tǒng)的日期(不包含時(shí)間)
SELECT CURDATE();
#也可以獲取指定的部分,年,月,日,小時(shí),分鐘,秒 (YEAR,MONTH,DAY,minute,second)
SELECT YEAR(NOW());
SELECT YEAR("1893-10-12");
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
#str_to_date 將指定字符通過指定格式轉(zhuǎn)換成日期: "%Y-%c-%d"
SELECT STR_TO_DATE("1952_3_5","%Y_%m_%d"); #按照它指定字符格式去取日期
SELECT STR_TO_DATE("1952_3_5","%Y_%c_%d"); #按照它指定字符格式去取日期
#查詢?nèi)罩救掌跒?992-4-3的員工信息
SELECT hiredate,last_name FROM employees; #1992-04-03 00:00:00 K_ing
SELECT hiredate,last_name FROM employees WHERE hiredate="1992-4-3";
SELECT hiredate,last_name FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format 將日期轉(zhuǎn)成字符
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日%W%H時(shí)%i分%s秒");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日-星期%w-%H時(shí)%i分%s秒");
#查詢有獎(jiǎng)金的員工名和入職日期
SELECT last_name,DATE_FORMAT(hiredate,"%Y年%m月%d日") 日期,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
- 其他系統(tǒng)函數(shù)
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
- 流程控制函數(shù)
#1.if函數(shù)：if-else的效果
SELECT IF(10<3,'小','大');
#當(dāng)沒有獎(jiǎng)金的用戶表現(xiàn)出(沒獎(jiǎng)金呵呵),有獎(jiǎng)金時(shí)變現(xiàn)為(有獎(jiǎng)金呵呵)
SELECT last_name,commission_pct 獎(jiǎng)金,IF(commission_pct IS NULL,"沒獎(jiǎng)金 呵呵","有獎(jiǎng)金,嘻嘻") 備注
FROM employees
ORDER BY commission_pct DESC;
#2.case函數(shù)的使用一：switch case的效果
/*
mysql中
#case第一種寫法
case 要判斷的字段或表達(dá)式
when 常量1 then 要顯示的值1或語句1
when 常量2 then 要顯示的值2或語句2
...
else 要顯示的值n或語句n
end
*/
/*案例：查詢員工的工資，要求
部門號=30淋硝，顯示的工資為1.1倍
部門號=40雹熬，顯示的工資為1.2倍
部門號=50错维，顯示的工資為1.3倍
其他部門，顯示的工資為原工資
*/
SELECT last_name,salary 原工資,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 新工資
FROM employees;
#case：第二種寫法
/*
mysql中：
case
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
橄唬。赋焕。。
else 要顯示的值n或語句n
end
*/
SELECT last_name 姓名,salary 工資,
CASE
WHEN salary>20000 THEN "A"
WHEN salary>15000 THEN "B"
WHEN salary>10000 THEN "C"
ELSE "D"
END 工資登記
FROM employees
ORDER BY salary DESC;
練習(xí)題
#案例1：顯示系統(tǒng)時(shí)間(注：日期+時(shí)間)
SELECT NOW();
#案例2：查詢員工號仰楚，姓名隆判，工資，以及工資提高百分之20后的結(jié)果(new salary)
SELECT last_name 姓名,employee_id 員工號,salary 原工資,salary*1.2 AS 新工資
FROM employees;
#案例3：將員工的姓名按首字母排序僧界，并寫出姓名的長度(length)
SELECT last_name 姓名,LOWER(SUBSTR(last_name,1,1)) 姓名首字母,LENGTH(last_name) 姓名長度
FROM employees
ORDER BY 姓名首字母 DESC;
SELECT last_name FROM employees;
#案例4： 做一個(gè)查詢侨嘀，產(chǎn)生下面的結(jié)果
/*
Dream Salary
K_ing earns 24000.00 monthly but wants 72000.00
*/
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
other D
*/
SELECT job_id job,
CASE job_id
WHEN "AD_PRES" THEN "A"
WHEN "ST_MAN" THEN "B"
WHEN "IT_PROG" THEN "C"
ELSE "D"
END grade
FROM employees
ORDER BY grade ASC;
SELECT job_id FROM employees;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
五捂襟、分組函數(shù)
/*
功能：用作統(tǒng)計(jì)使用咬腕，又稱為聚合函數(shù)或統(tǒng)計(jì)函數(shù)或組函數(shù)
分類：
sum 求和、avg 平均值葬荷、max 最大值 涨共、min 最小值 、count 計(jì)算個(gè)數(shù)
特點(diǎn)：
1宠漩、sum举反、avg一般用于處理數(shù)值型
max、min扒吁、count可以處理任何類型
2火鼻、以上分組函數(shù)都忽略null值
3、可以和distinct搭配實(shí)現(xiàn)去重的運(yùn)算
4雕崩、count函數(shù)的單獨(dú)介紹
一般使用count(*)用作統(tǒng)計(jì)行數(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;
SELECT SUM(salary) 總和,TRUNCATE(AVG(salary),2) 平均值取兩位,MAX(salary) 最大值,MIN(salary) 最小值,COUNT(salary) 個(gè)數(shù)
FROM employees;
#2.參數(shù)支持哪些類型
SELECT SUM(last_name) FROM employees;
SELECT AVG(last_name) FROM employees;
SELECT MIN(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3. 是否會(huì)忽略null不計(jì)
SELECT MAX(last_name) FROM employees; #null會(huì)忽略不計(jì)
SELECT MIN(commission_pct) FROM employees; #null會(huì)忽略不計(jì)
SELECT COUNT(commission_pct) FROM employees; #null會(huì)忽略不計(jì)
#4.和distinct去重搭配
SELECT SUM(DISTINCT(salary)),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT(salary)),COUNT(salary) FROM employees;
#5. count合并函數(shù)的詳細(xì)介紹
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; #所需時(shí)間慢些在inndob引擎慢，在myisam引擎下快
SELECT COUNT(1) FROM employees; #所需時(shí)間快些在inndob引擎快盼铁，在myisam引擎下慢
#擴(kuò)展：查看當(dāng)前什么存儲引擎(利用系統(tǒng)變量和函數(shù)進(jìn)行查詢或模糊匹配)
SHOW TABLE STATUS FROM myemployees WHERE NAME="employees";
SHOW TABLE STATUS FROM mysql WHERE NAME="db";
SHOW VARIABLES LIKE "%storage_engine%";
#查看創(chuàng)建的表示什么引擎,
USE myemployees;
SHOW CREATE TABLE jobs; #這條命令不僅可以查看表示用什么引擎粗蔚，還可以看到怎么創(chuàng)建表的
USE mysql;
SHOW CREATE TABLE db;
#desc table只能查看表的一部分結(jié)構(gòu)
DESC jobs;
SELECT VERSION(); #查看版本號
SHOW ENGINES; #查看系統(tǒng)默認(rèn)支持的存儲引擎
USE mysql;
SHOW TABLES;
#6.和分組函數(shù)一同查詢的字段有限制
USE myemployees;
SELECT AVG(salary),employee_id FROM employees; #執(zhí)行不了
練習(xí)題
#案例1：查詢員工表中的最大入職時(shí)間和最小入職時(shí)間的相差天數(shù)(datediff)
#用到datediff函數(shù)來計(jì)算兩個(gè)日期相差的天數(shù)
#MAX(hiredate),MIN(hiredate)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))FROM employees;
SELECT DATE_FORMAT(NOW(),"%Y-%c-%d");
SELECT DATEDIFF(DATE_FORMAT(NOW(),"%Y-%c-%d"),"2019-5-26");
SELECT DATEDIFF(NOW(),"2019-5-26");
#案例2：查詢部門編號為90的員工個(gè)數(shù)
SELECT COUNT(*) FROM employees WHERE department_id=90;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
六、分組查詢
/*
語法：
select 查詢列表
from 表
【where 篩選條件】
group by 分組的字段
【order by 排序的字段】;
特點(diǎn)：
1捉貌、和分組函數(shù)一同查詢的字段必須是group by后出現(xiàn)的字段
2支鸡、篩選分為兩類：分組前篩選和分組后篩選
針對的表 位置 連接的關(guān)鍵字
分組前篩選 原始表 group by前 where
分組后篩選 group by后的結(jié)果集 group by后 having
A.分組函數(shù)做條件肯定是放在having字句后
B.能用分組前篩選的，就優(yōu)先考慮使用分組前篩選
問題1：分組函數(shù)做篩選能不能放在where后面
答：不能
問題2：where——group by——having
一般來講趁窃，能用分組前篩選的牧挣，盡量使用分組前篩選，提高效率
3醒陆、group by分組可以按單個(gè)字段也可以按多個(gè)字段(多個(gè)字段之間使用逗號隔開沒有順序要求),也支持表達(dá)式或函數(shù)(用得比較少)
4瀑构、可以搭配著排序order by使用,放在整個(gè)分組查詢之后的
*/
- 引入：查詢每個(gè)部門員工個(gè)數(shù)
SELECT COUNT(*) FROM employees WHERE department_id=90;
- 簡單的分組
#案例1：查詢每個(gè)工種的員工平均工資
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
SELECT DISTINCT job_id FROM employees;
#案例2：查詢每個(gè)位置的部門個(gè)數(shù)
SELECT COUNT(*),department_name
FROM departments
GROUP BY department_name;
SELECT COUNT(*) FROM departments;
SELECT * FROM departments;
SHOW CREATE TABLE departments;
- 可以實(shí)現(xiàn)分組前的篩選
#案例1：查詢郵箱中包含a字符的每個(gè)部門的最高工資
SELECT department_id,MAX(salary)
FROM employees
WHERE email LIKE "%a%"
GROUP BY department_id;
SELECT DISTINCT(department_id)
FROM employees;
#案例2： 查詢有獎(jiǎng)金的每個(gè)領(lǐng)導(dǎo)手下員工的平均工資
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
SELECT DISTINCT(manager_id)
FROM employees
WHERE commission_pct IS NOT NULL;
- 分組后的篩選
#案例1：查詢部門的員工個(gè)數(shù)大于5個(gè)的部門
SELECT COUNT(*) 員工個(gè)數(shù),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
SELECT COUNT(*) 員工個(gè)數(shù),department_id
FROM employees
GROUP BY department_id;
SELECT COUNT(*) 員工個(gè)數(shù),department_id
FROM employees
GROUP BY department_id
HAVING 員工個(gè)數(shù) > 5;
#案例2：每個(gè)工種有獎(jiǎng)金的員工的最高工資>12000的工種編號和最高工資
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id
HAVING MAX(salary)>10000;
SELECT DISTINCT job_id FROM employees;
#案例3：領(lǐng)導(dǎo)編號>102的每個(gè)領(lǐng)導(dǎo)手下的最低工資大于5000的領(lǐng)導(dǎo)編號和最低工資
#manager_id > 102
SELECT manager_id FROM employees WHERE manager_id>102;
SELECT DISTINCT manager_id FROM employees WHERE manager_id>102;
SELECT manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id;
SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>102 GROUP BY manager_id;
SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;
- 添加排序
#案例4：每個(gè)工種有獎(jiǎng)金的員工的最高工資>12000的工種編號最高工資，按最高工資升序
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000
ORDER BY MAX(salary) DESC;
#每個(gè)工種有獎(jiǎng)金的員工的最高工資>=8000的工種編號最高工資，按最高工資升序
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) >= 8000
ORDER BY MAX(salary) DESC;
#獲取所以的job_id工種編號最高的工資
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id;
- 按多個(gè)字段分組
#案例：查詢每個(gè)工種每個(gè)部門的最低工資寺晌，并按最低工資降序
SELECT MIN(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY MIN(salary) DESC;
SELECT MIN(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY MIN(salary) DESC;
- 按表達(dá)式或函數(shù)分組
#案例：按員工姓名的長度分組世吨，查詢每一組的員工個(gè)數(shù)，篩選員工個(gè)數(shù)>5的有哪些
SELECT COUNT(*),LENGTH(last_name) 長度
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5
ORDER BY 長度 DESC;
- 格式轉(zhuǎn)換函數(shù)
#cast()和convert()兩個(gè)函數(shù)都可以用來轉(zhuǎn)換數(shù)據(jù)類型或者轉(zhuǎn)換字符集
select convert('test',char character set utf8);
select cast('1996-10-6'as date);
練習(xí)解析：
#案例1：查詢各個(gè)job_id的員工工資的最大值呻征，最小值耘婚，平均值，總和陆赋，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id DESC;
SELECT DISTINCT job_id FROM employees;
#案例2：查詢員工最高工資和最低工資的差距(difference)
SELECT (MAX(salary)-MIN(salary)) 差距
FROM employees;
#案例3：查詢各個(gè)管理者手下員工的最低工資沐祷，其中最低的工資不能低于6000，沒有管理者的員工不計(jì)算在內(nèi)
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
#案例4：查詢所有部門的編號攒岛，員工數(shù)量和工資的平均值赖临，并按平均值工資降序
SELECT department_id FROM employees;
SELECT COUNT(*),AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
#案例5：選擇具有各個(gè)job_id的員工個(gè)數(shù)
SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
七.連接查詢
/*
含義：又稱多表查詢，當(dāng)查詢的字段來自于多個(gè)表時(shí)灾锯，就會(huì)用到連接查詢
笛卡爾乘積現(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)連接：
等值連接
非等值連接
自連接
外連接：
左外連接
右外連接
全外連接
交叉連接
*/
USE girls;
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty; #出現(xiàn)笛卡爾乘積現(xiàn)象出現(xiàn)混亂
- 等值連接
/*
多表等值連接的結(jié)果為多表的交集部分
n表連接顺饮，至少需要n-1個(gè)連接條件
多表的順序沒有要求
一般需要為表起別名
可以搭配前面介紹的所有子句使用吵聪，比如排序、分組领突、篩選
*/
#案例1：查詢女神名和對應(yīng)的男神名
#添加有效連接條件(boys.id=beauty.`boyfriend_id`)
SELECT NAME,boyName
FROM boys,beauty
WHERE boys.`id`=beauty.`boyfriend_id`;
#案例2：查詢員工名和對應(yīng)的部門名
SHOW DATABASES;
SELECT DATABASE();
USE myemployees;
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
- 為表起別名
/*
提高語句的簡潔讀
區(qū)分多個(gè)重名的字段
注意：如果為表起了別名暖璧，則查詢的字段就不能使用原來的表名去限定了
*/
#查詢員工名、工種號君旦、工種名
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
#查詢各個(gè)工種名包含的員工數(shù)
SELECT COUNT(*),j.`job_title`
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY e.`job_id`;
SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;
SELECT *
FROM jobs;
SELECT e.`job_id`,j.`job_title`
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
- 兩個(gè)表的順序是否可以調(diào)換
#查詢員工名、工種名嘲碱、工種號
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
- 可以加篩選條件
#案例：查詢有獎(jiǎng)金的員工名金砍、部門名
SELECT e.`last_name`,d.`department_name`,e.`commission_pct`
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#案例：查詢城市市名中第二個(gè)字符為o的部門名和城市名
SELECT d.`department_name`,l.`city`
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id` AND l.`city` LIKE "_o%";
- 可以加分組
#案例1：查詢每個(gè)城市的部門個(gè)數(shù)
SELECT COUNT(*) 部門個(gè)數(shù),l.`city`
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`;
SELECT COUNT(*) 部門個(gè)數(shù),l.`city`
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY d.`location_id`;
#案例2：查詢有獎(jiǎng)金的每個(gè)部門的部門名和部門的領(lǐng)導(dǎo)編號和該部門的最低工資
SELECT d.`department_name`,d.`manager_id`,MIN(e.`salary`)
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL
GROUP BY e.`department_id`,d.`manager_id`;
SELECT MIN(salary)
FROM employees
WHERE commission_pct IS NOT NULL AND department_id IS NOT NULL
GROUP BY department_id;
- 可以加排序
#案例：查詢每個(gè)工種的工種名和員工個(gè)數(shù)，并且按員工個(gè)數(shù)降序
SELECT j.`job_title`,COUNT(e.`last_name`) 員工個(gè)數(shù)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_title`
ORDER BY 員工個(gè)數(shù) DESC;
- 可以實(shí)現(xiàn)三表或多表連接?
#案例：查詢員工名麦锯、部門名和所在城市
SELECT e.`last_name`,d.`department_name`,l.`city`
FROM departments d,employees e,locations l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;
USE myemployees;
#在myemployees庫中新建表job_grades
/*
SELECT salary,employee_id FROM employees;
SELECT * FROM job_grades;
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);
*/
SHOW CREATE TABLE job_grades;
- 非等值連接
#案例1：查詢員工的工資和工資級別
SELECT e.`salary`,jb.`grade_level`
FROM employees e,job_grades jb
WHERE e.`salary` BETWEEN jb.`lowest_sal` AND jb.`highest_sal`;
#查詢員工的工資和工資級別恕稠，并列出把工資級別為A的所有員工
SELECT e.`salary`,jb.`grade_level`
FROM employees e,job_grades jb
WHERE e.`salary` BETWEEN jb.`lowest_sal` AND jb.`highest_sal` AND jb.`grade_level`='A';
- 自連接
#案例：查詢 員工名和上級的名稱
SELECT e.`last_name`,e.`employee_id`,m.`last_name`,m.`employee_id`
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
練習(xí)解析：
#1.顯示員工表的最大工資，工資的平均值
SELECT MAX(salary),AVG(salary) FROM employees;
#2.查詢員工表的employee_id,job_id,last_name,按department_id降序扶欣，salary升序
SELECT employee_id,job_id,last_name,department_id,salary
FROM employees
ORDER BY department_id DESC,salary ASC;
#3.查詢員工表的job_id中包含a和e的,并且a在e前面
SELECT job_id FROM employees WHERE job_id LIKE "%a%e%";
#5.顯示當(dāng)前日期鹅巍，以及去前后空格，截取字符串的函數(shù)
SELECT LENGTH(TRIM(NOW()));
#6. 查詢每個(gè)國家下的部門個(gè)數(shù)大于2的國家編號
SELECT country_id,COUNT(*) 部門個(gè)數(shù)
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
八料祠、sql99語法
/*
語法：
select 查詢列表
from 表1 別名 【連接類型】inner,left [outer],right [outer],full [outer]
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 連接條件
分類：
等值
非等值
自連接
特點(diǎn)：
1.添加排序、分組髓绽、篩選
2.inner可以省略
3.篩選條件放在where后面敛苇，連接條件放在on后面，提高分離性顺呕，便于閱讀
4.inner join連接和sq192語法中的等值連接效果一樣的枫攀，都是查詢多表的交集
*/
- 等值連接
#案例1括饶；查詢員工名、部門名
SELECT department_name,last_name
FROM departments d
INNER JOIN employees e
ON e.`department_id`=d.`department_id`;
SELECT department_name,last_name
FROM departments d
JOIN employees e
ON e.`department_id`=d.`department_id`;
#案例2：查詢名字中包含e的員工名和工種名(添加篩選)
SELECT last_name,job_title
FROM employees e
JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE "%e%";
#案例3.查詢部門個(gè)數(shù)>3的城市名和部門個(gè)數(shù) (添加分組+篩選)
SELECT city,COUNT(*) 部門個(gè)數(shù)
FROM locations l
JOIN departments d
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例4: 查詢哪個(gè)部門的員工個(gè)數(shù)>3的部門名和員工個(gè)數(shù),并按個(gè)數(shù)降序(添加排序)
SELECT department_name,COUNT(*) 員工個(gè)數(shù)
FROM departments d
JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#案例5：查詢員工名来涨、部門名图焰、工種名，并按部門名降序（添加三表連接）
/*錯(cuò)誤演示
select department_name,last_name,job_title
from departments d
join employees e,jobs j
on j.`job_id`=e.`job_id` and e.`department_id`=d.`department_id`
order by department_name desc;
*/
SELECT department_name,last_name,job_title
FROM employees e #在中間的值放在from 后面蹦掐，join分別連接其他多個(gè)表名
JOIN departments d ON e.`department_id`=d.`department_id`
JOIN jobs j ON j.`job_id`=e.`job_id`
ORDER BY department_name DESC;
- 非等值連接
#查詢員工的工資級別 employees job_grades
USE myemployees;
SELECT salary,grade_level
FROM employees e
JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#查詢工資級別的個(gè)數(shù)大于20的個(gè)數(shù)技羔，并且按工資級別降序
SELECT COUNT(*) 工資個(gè)數(shù),grade_level 工資級別
FROM job_grades g
JOIN employees e
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY COUNT(*) DESC;
- 自連接
#查詢員工的名字、上級的名字
SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;
#查詢姓名中包含字符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)用場景：用于查詢一個(gè)表中有，另一個(gè)表中沒有的記錄
特點(diǎn)：
1.外連接的查詢結(jié)果為主表中的所有記錄
如果從表中有和它匹配的颗味，則顯示匹配的值
如果從表中沒有和它匹配的超陆，則顯示null
外連接查詢結(jié)果=內(nèi)連接結(jié)果+主表中有從表沒有的記錄
2、左外連接浦马，left join左邊的是主表(也就是上面的from表示主表)
右外連接时呀，right join右邊的是主表 (也就是后面的join表示主表)
3、左外和右外交換兩個(gè)表的順序晶默，可以實(shí)現(xiàn)同樣的效果
4谨娜、全外連接=內(nèi)連接的結(jié)果+表1中有但表2沒有的+表2中有但表1沒有的
*/
- 引入：查詢男朋友 不在男神表中的女神名
#右外連接，rigth join右邊的是主表(也就是join后面的表是：主表)
USE girls;
SELECT b.*
FROM boys bo #這個(gè)是附表
RIGHT OUTER JOIN beauty b #這個(gè)是主表
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
#左外連接：left join左邊的是主表(也就是上面的from的表是: 主表)
USE girls;
SELECT b.*
FROM beauty b #這個(gè)是主表
LEFT OUTER JOIN boys bo #這個(gè)是附表
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
#全外（不支持）
USE girls;
SELECT b.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;
- 引入：查詢哪個(gè)部門沒有員工
#左外
USE myemployees;
SELECT d.*,e.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;
#右外
SELECT d.*
FROM employees e
RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;
基礎(chǔ)案例
#案例1：查詢編號>3的女神的男朋友信息磺陡，如果有則列出詳細(xì)趴梢，如果沒有，用null表示
USE girls;
SELECT b.name,b.id,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3;
#案例2：查詢哪個(gè)城市沒有部門
USE myemployees;
SELECT l.`city`,d.`department_name`
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;
#案例3：查詢部門名為SAL或IT的員工信息
SELECT e.*,d.`department_name`
FROM employees e
JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` = "SAL" OR d.`department_name`="IT";
SELECT e.*,d.`department_name`
FROM departments d
LEFT JOIN employees e
ON d.`department_id`= e.`department_id`
WHERE d.`department_name` IN ("SAL", "IT");
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
九币他、子查詢
/*
含義：
出現(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拍冠、行子查詢(多行多列)
特點(diǎn)：
子查詢放在小括號內(nèi)
子查詢一般放在條件的右側(cè)
標(biāo)量子查詢尿这，一般搭配著單行操作符使用
> < >= <= <>
列子查詢，一般搭配著多行操作符使用
in any/some all
子查詢的執(zhí)行優(yōu)先于主查詢的執(zhí)行庆杜，主查詢的條件用到了子查詢的結(jié)果
*/
- 標(biāo)量子查詢 ※
#案例1：誰的工資比Abel高射众？
SELECT salary
FROM employees
WHERE last_name="Abel";
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name="Abel"
);
#案例2：返回job_id與141號員工相同，salary比143號員工 姓名,job_id和工資
SELECT job_id
FROM employees
WHERE employee_id="141";
SELECT salary
FROM employees
WHERE employee_id="143";
SELECT last_name,job_id,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE employee_id="143"
) AND job_id=(
SELECT job_id
FROM employees
WHERE employee_id="141"
);
#案例3：返回公司工資最少的員工的last_name,job_id和salary
#返回工資最少的員工的工資
SELECT MIN(salary)
FROM employees;
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4：查詢最低工資大于50號部門最低工資的部門id和其最低工資 (在進(jìn)行分組后在進(jìn)行篩選having)
#I
SELECT MIN(salary)
FROM employees
WHERE department_id="50";
#II
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id;
#III 在II基礎(chǔ)上再篩選欣福，滿足min(salary)>0
SELECT MIN(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id="50"
);
#這個(gè)是錯(cuò)誤的非法使用標(biāo)量子查詢
SELECT MIN(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id="70"
);
- 列子查詢（多行子查詢）
#案例1：返回location_id是1400或1700的部門中所有員工姓名
#departments employees
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)
);
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';
#案例2：返回其他部門中比job_id為'IT_PROG'工種任一工資低的員工的員工號责球、姓名、job_id 以及salary
#方法一；
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ANY (
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <>"IT_PROG";
#方法二：
SELECT employee_id,last_name,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 employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ALL (
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <>"IT_PROG";
#法2
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
SELECT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <>"IT_PROG";
- 行子查詢（結(jié)果集一行多列或多行多列）
#案例：查詢員工編號最小并且工資最高的員工信息
#方法二：
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)量子查詢
*/
#案例1：查詢每個(gè)部門的員工個(gè)數(shù)栖博，null也會(huì)返回
#方法一：
SELECT d.`department_name`,COUNT(*)
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_name`;
#方法二：可以將null值也取出來
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
) 個(gè)數(shù)
FROM departments d;
SELECT (
SELECT d.`department_name`
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`employee_id`=102
) 部門名;
三屑宠、from后面（將查詢到的結(jié)果當(dāng)做一張表來）
#案例：查詢每個(gè)部門的平均工資的工資登記
SELECT AVG(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
#將平均工資當(dāng)做一張表來做工資登記
SELECT avg_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) avg_s,department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) avg_dep
JOIN job_grades g
ON avg_dep.avg_s BETWEEN g.`lowest_sal` AND g.`highest_sal`;
四、exists后面（相關(guān)子查詢）
#子查詢練習(xí)解析
#案例1：查詢和Zlotkey相同部門的員工姓名和工資
SELECT department_id
FROM departments
WHERE department_name="Zlotkey";
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name="Zlotkey"
);
#案例2：查詢工資比公司平均工資高的員工的員工號仇让，姓名典奉，工資
SELECT AVG(salary)
FROM employees;
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#案例3：查詢各部門中平均工資比本部門平均工資高的員工的員工號，姓名丧叽，和工資
#先查詢各部門的平均工資
SELECT AVG(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
#這里可以把查詢到的結(jié)果作為一個(gè)表卫玖，然后再將表和大表進(jìn)行比較求值，利用join on 內(nèi)連接sql99語法
SELECT last_name,employee_id,salary,department_id
FROM employees e
JOIN (
SELECT AVG(salary) avg_s,department_id dep_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
) avg_dep
ON e.`department_id`=avg_dep.dep_id
WHERE e.`salary` > avg_dep.avg_s;
#案例4：查詢和姓名中包含字母u的員工都在相同部門的員工的員工工號和姓名
#先查包含字母u的員工所在的部門號
SELECT department_id,last_name
FROM employees
WHERE last_name LIKE "%u%";
#這里就相當(dāng)于求最少兩個(gè)人同屬于在同一部門踊淳，且包含字母u的員工
SELECT m.last_name
FROM (
SELECT department_id,last_name
FROM employees
WHERE last_name LIKE "%u%"
) m
JOIN (
SELECT COUNT(*),department_id
FROM (
SELECT department_id,last_name
FROM employees
WHERE last_name LIKE "%u%"
) dep_ne
GROUP BY dep_ne.department_id
HAVING COUNT(*)>=2
) k
ON k.department_id=m.department_id;
SELECT last_name,employee_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE "%u%"
);
#案例5：查詢在部門的location_id為1700的部門工作員工的工號
SELECT department_id
FROM departments
WHERE location_id=1700;
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id=1700
);
#案例6：查詢管理者是K_ing的員工號假瞬，所管理的員工姓名和工資
SELECT employee_id
FROM employees
WHERE last_name='K_ing';
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name='K_ing'
);
#案例7：查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列迂尝，列名為 姓.名
SELECT CONCAT(first_name,".",last_name) 姓名
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
);
分頁查詢
/*
應(yīng)用場景：當(dāng)要顯示的數(shù)據(jù)脱茉，一頁顯示不全，需要分頁提交sql請求
語法：
這里的執(zhí)行順序是：(1 2 3 4 5 6 7 8 9
select 查詢列表 (7
from 表 (1
【join type join 表2 (2
on 連接條件 (3
where 篩選條件 (4
group by 分組字段 (5
having 分組后的篩選 (6
order by 排序的字段】 (8
limit 【offset,】size; (9
offset要顯示條目的起始索引（起始索引從0開始）
size 要顯示的條目個(gè)數(shù)
特點(diǎn)：
①limit語句放在查詢語句的最后
②公式
要顯示的頁數(shù) page垄开，每頁的條目數(shù)size
select 查詢列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20
*/
SELECT * FROM employees;
SELECT * FROM employees
LIMIT 10;
#案例：有獎(jiǎng)金的員工信息琴许，并且工資較高的前10名顯示出來
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
測試題
#已知表 stuinfo
/*
id 學(xué)號
name 姓名
email 郵箱 john@124.com
gradeId 年級編號
sex 性別 男 女
age 年齡
已知表 grade
id 年級編號
gradeName 年級稱號
*/
#一：查詢所有學(xué)生的郵箱的用戶名 （注：郵箱中@前面的字符）
SELECT SUBSTR(email,1,INSTR(email,"@")-1)
FROM stuinfo;
#二：查詢男生和女生的個(gè)數(shù)
SELECT COUNT(*) 個(gè)數(shù)
FROM stuinfo
GROUP BY sex;
#三：查詢年齡>18歲的所有學(xué)生的姓名和年級稱號
SELECT NAME,gradeName
FROM stuinfo s
JOIN grade g
ON s.gradeId=g.id
WHERE age>18;
#四：查詢年齡哪個(gè)年級學(xué)生最小的年齡>20歲
SELECT MIN(age),gradeid
FROM stuinfo
GROUP BY gradeid;
HAVING MIN(age)>20;
SELECT * FROM girls.`beauty`;
#五：試說出查詢語句中涉及到的所有的關(guān)鍵字，以及執(zhí)行先后順序
/*
select 查詢列表 (7
from 表 (1
連接類型 join 表2 (2
on 連接條件 (3
where 篩選條件 (4
group by 分組列表 (5
having 分組后的篩選 (6
order by 排序列表 (8
limit 偏移溉躲，條目數(shù) (9
*/
子查詢經(jīng)典案例題
#案例1：查詢工資最低的員工信息：last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例2：查詢平均工資最低的部門信息
#方法一：
#1)查詢平均工資的各個(gè)部門
SELECT AVG(salary) s
FROM employees
GROUP BY department_id;
#2)查詢1)結(jié)果上的最低工資
SELECT MIN(sd.s)
FROM (
SELECT AVG(salary) s
FROM employees
GROUP BY department_id
) sd;
#3)查詢哪個(gè)部門的平均工資=2)
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(sd.s)
FROM (
SELECT AVG(salary) s
FROM employees
GROUP BY department_id
) sd
);
#4)查詢部門信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(sd.s)
FROM (
SELECT AVG(salary) s
FROM employees
GROUP BY department_id
) sd
)
);
#方法二：可以用分頁查詢的方法來進(jìn)行查詢
#1）先查詢出平均工資最低的部門榜田，然后用正序排序的方法排列，然后再用limit分頁查詢出來
SELECT AVG(salary),department_id d
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;
#2)結(jié)合1）查詢出部門信息
SELECT dep.*
FROM departments dep
WHERE department_id=(
SELECT department_id d
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
);
#有問題
SELECT dep.*
FROM departments dep
JOIN (
SELECT AVG(salary),department_id d
FROM employees e
GROUP BY e.department_id
ORDER BY AVG(salary) ASC
LIMIT 1
) aep
ON dep.`department_id`=aep.d;
#案例3：查詢平均工資最低的部門信息和該部門的平均工資
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;
#最終結(jié)果
SELECT d.*,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
) aep
ON d.`department_id`=aep.department_id;
#案例4：查詢平均工資最高的job信息
SELECT j.*,sj.s
FROM jobs j
JOIN (
SELECT AVG(salary) s,job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
) sj
ON j.`job_id`=sj.job_id;
SELECT DISTINCT job_id FROM employees;
#案例5：查詢平均工資高于公司平均部門的工資有哪些部門
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);
#案例6：查詢公司中所有manager的詳細(xì)信息
#1）查詢所有manager的員工編號
SELECT DISTINCT manager_id
FROM employees;
#2)查詢詳細(xì)信息锻梳，滿足manager_id=1)
SELECT *
FROM employees
WHERE employee_id =ANY(
SELECT DISTINCT manager_id
FROM employees
);
#案例7：各個(gè)部門中 最高工資中最低的那個(gè)部門 的最低工資是多少
#最高工資中最低的那個(gè)部門
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1;
#最終結(jié)果
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1
);
#案例8：查詢平均工資最高的部門的manager的詳細(xì)信息：last_name,department_id,email,salary
#平均工資最高的部門
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#獲得manager的員工編號
SELECT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
#查詢詳細(xì)信息
SELECT *
FROM employees
WHERE employee_id =ANY(
SELECT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
);
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十：聯(lián)合查詢
/*
union 聯(lián)合 合并：將多條查詢語句的結(jié)果合并成一個(gè)結(jié)果
語法：
查詢語句1
union
查詢語句2
union
...
應(yīng)用場景：
要查詢的結(jié)果來自于多個(gè)表串慰，且多個(gè)表沒有直接的連接關(guān)系，但查詢的信息一致時(shí)
**特點(diǎn)：★
1唱蒸、要求多條查詢語句的查詢列數(shù)是一致的！
2灸叼、要求多條查詢語句的查詢的每一列的類型和順序最好一致
3神汹、union關(guān)鍵字默認(rèn)去重，如果使用union all 可以包含重復(fù)項(xiàng)
**
*/
#引入案例：查詢部門編號>90或郵箱包含a的員工信息
USE myemployees;
SELECT * FROM employees WHERE email LIKE "%a%" OR department_id>90;
SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE "%a%";
#案例：查詢中國用戶中男性的信息以及外國用戶中男性的信息
SELECT id,cname FROM t_ca WHERE csex="男"
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender="male";
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十：數(shù)據(jù)的增刪改
#DML語言
/*
數(shù)據(jù)操作語言：
insert 插入
update 更新古今，修改
delete 刪除
*/
一屁魏、插入語句
方式一：經(jīng)典插入語句
/*
語法：
insert into 表名(列名,......) values(值1,值2,值3,....值n)
*/
#查看girls的beauty表
SELECT * FROM girls.beauty;
- 插入的值的類型要與列的類型一致或兼容(values這里不用空格隔開的)
USE girls;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,"唐藝昕","女",'1990-4-23','18865471245',NULL,8);
- 不可以為Null的列必須要插入值，可以為null的列如何插入值?
#方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(14,"Alice","女",'1989-10-5','120',NULL,7);
#方式二：
SELECT * FROM girls.beauty;
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'Toms','女','192');
- 列的順序可以調(diào)換
INSERT INTO beauty(NAME,id,phone,sex)
VALUES('劉亦菲',20,'1478','女');
和數(shù)值個(gè)數(shù)必須一致
省略列名捉腥，默認(rèn)所有列氓拼，而且列的順序和表中的列的順序一致
SELECT * FROM girls.beauty;
INSERT INTO beauty
VALUES(21,'朱茵','女','1990-12-1','180',NULL,7);
方式二：用set 列名=值
/*
語法：
insert into 表名
set 列名=值,列名=值,...
*/
INSERT INTO beauty
SET id=22,NAME="鞏俐",phone='196464';
- values和set兩種方式的不同
#1.values支持多行的插入，set不支持
INSERT INTO beauty(id,NAME,phone)
VALUES(30,'A1','123'),
(31,'A2','124'),
(32,'A3','125'),
(33,'A4','126');
#2.values支持子查詢插入，set不支持
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1464'
FROM boys WHERE id<3;
二桃漾、修改語句
/*
1.修改單表的記錄
語法：
update 表名
set 列=值,列=新值,...
2.修改多表的記錄【補(bǔ)充】
語法：
sql92語法：
update 表1 別名,表2 別名
set 列=值,...
where 連接條件
and 篩選條件;
語法：
sql99語法：
update 表1 別名
inner | left | right join 表2 別名
on 連接條件
set 列=值,...
where 篩選條件;
*/
- 修改單表的記錄
#案例1：修改beauty表中姓A的女神的性別為男
UPDATE beauty SET sex="男"
WHERE NAME LIKE"A%";
- 修改多變的記錄
#案例1：修改張無忌的女朋友的手機(jī)號為1314
UPDATE beauty b
JOIN boys bo
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='1314',bo.`userCP`=1000
WHERE bo.`boyName`="張無忌";
SELECT b.*
FROM beauty b
JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`="張無忌";
SELECT * FROM boys;
#案例2：修改沒有男朋友的女神的男朋友編號都為2號
UPDATE beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
SELECT b.*
FROM beauty b
JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`="鹿晗";
三坏匪、刪除語句
/*
方式一：delete
語法：
1、單表的刪除
delete from 表名 where 篩選條件
2撬统、多表的刪除【補(bǔ)充】
sql92語法：
delete 表1的別名适滓，表2的別名
from 表1的別名，表2的別名
where 連接條件
and 篩選條件;
sql99語法：
delete 表1的別名,表2的別名
from 表1 別名
inner | left | right join 表2 別名 on 連接條件
where 篩選條件;
*/
- 單表的刪除（整行的刪除）
#案例：刪除手機(jī)號以9結(jié)尾的女神信息
SELECT * FROM beauty WHERE phone LIKE "%9";
DELETE FROM beauty WHERE phone LIKE "%9";
SELECT * FROM beauty;
- 多表的刪除
#案例：刪除張無忌的女朋友的信息
DELETE b
FROM beauty b
JOIN boys bo
ON bo.id=b.boyfriend_id
WHERE bo.boyName="張無忌";
SELECT b.*
FROM beauty b
JOIN boys bo
ON bo.`id`=b.`boyfriend_id`
WHERE bo.`boyName`="張無忌";
#案例：刪除黃曉明的信息以及他的女朋友的信息
#這里是查看黃曉明的女朋友信息
SELECT b.*,bo.*
FROM boys bo
JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE bo.`boyName`="黃曉明";
#下面是刪除的
DELETE bo,b
FROM boys bo
JOIN beauty b
ON bo.id=b.boyfriend_id
WHERE bo.boyName="黃曉明";
#truncate語句進(jìn)行刪除的語句的操作
#truncate 不能刪除行數(shù)據(jù),要?jiǎng)h就要把表清空
#案例：將表中的數(shù)據(jù)都清空
TRUNCATE TABLE boys;
#drop 刪除一個(gè)表
#drop (刪除表)：刪除內(nèi)容和定義恋追，釋放空間凭迹。簡單來說就是把整個(gè)表去掉.以后要新增數(shù)據(jù)是不可能的,除非新增一個(gè)表。
delete和truncate的區(qū)別 (面試題)
/*
1.delete可以加where條件進(jìn)行刪除苦囱，truncate不能加
2.truncate刪除嗅绸，效率高一丟丟
3.假如要?jiǎng)h除的表中有自增長序列，
如果用delete刪除后撕彤，再插入數(shù)據(jù)鱼鸠，自增長列的值從斷點(diǎn)開始。
而truncate刪除后喉刘，再插入數(shù)據(jù)瞧柔，自增長列的值從1開始+
4.truncate刪除后沒有返回值，delete刪除后有返回值
5.truncate刪除后信息不能回滾睦裳，delete刪除后信息有回滾
*/
#下面這些是測試語句
SELECT * FROM boys;
DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys (boyName,userCP)
VALUES("張飛",100),("劉備",100),("關(guān)云長",101);
數(shù)據(jù)的增刪改的操作練習(xí)
#1.運(yùn)行以下腳本創(chuàng)建my_employees表和users表
USE girls;
CREATE TABLE my_employees(
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);
#2.顯示表的my_empoyees表的結(jié)構(gòu)
DESC my_employees;
SHOW CREATE TABLE my_employees;
#3. 向my_employees表中插入下列數(shù)據(jù)
/*
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
*/
#方法一：
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbriri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1150);
SELECT * FROM my_employees;
DELETE FROM my_employees;
#方法二：
TRUNCATE TABLE my_employees;
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbriri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1150;
SELECT * FROM my_employees;
#4.向表users中插入以下數(shù)據(jù)
/*
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/
INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20);
SELECT * FROM users;
DESC users;
#5.將所有工資少于900的員工的工資修改為1000
UPDATE my_employees
SET salary=1000
WHERE salary<900;
#6.將3號員工的last_name修改為"drelxer"
UPDATE my_employees
SET last_name='drelxer'
WHERE id=3;
#7.將userid 為Bbiri的user表和my_employees表的記錄全部刪除
DELETE u,m
FROM users u
JOIN my_employees m
ON u.userid=m.Userid
WHERE u.userid="Bbiri";
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十一：數(shù)據(jù)的定義語言
/*
庫和表的管理
一造锅、庫的管理
創(chuàng)建、修改廉邑、刪除
二哥蔚、表的管理
創(chuàng)建胰挑、修改镇饮、刪除
創(chuàng)建：create
修改：alter
刪除：drop
*/
I添诉、庫的管理
- 庫的創(chuàng)建
/*
語法：create database [if not exists]庫名;
*/
#案例：創(chuàng)建庫Books
CREATE DATABASE IF NOT EXISTS Books;
#也可以在創(chuàng)建庫的時(shí)候加入字符集
CREATE DATABASE IF NOT EXISTS Message CHARACTER SET utf8mb4;
CREATE DATABASE IF NOT EXISTS test CHARSET utf8mb4 COLLATE utf8mb4_bin;
- 庫的修改
#不適合用移迫，現(xiàn)在被刪除了
RENAME DATABASE books TO book;
#更改字符集
ALTER DATABASE books CHARACTER SET utf8mb4;
#查看系統(tǒng)字符集
SHOW VARIABLES LIKE 'collation_%';
SHOW VARIABLES LIKE 'character_set_%';
- 庫的刪除
DROP DATABASE IF EXISTS books;
II震嫉、表的管理
- 表的創(chuàng)建
/*
語法：
create table 表名(
列名 列的類型 【(長度) 約束】,
列名 列的類型 【(長度) 約束】,
..
列名 列的類型 【(長度) 約束】
);
*/
#案例：創(chuàng)建Book表
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;
USE books;
CREATE TABLE book(
id INT,
bName VARCHAR(20),
pice DOUBLE,
authorId INT,
publishDate DATETIME
);
DESC book;
SHOW CREATE TABLE book;
#案例：創(chuàng)建author表
USE girls;
CREATE TABLE IF NOT EXISTS books.author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
);
DESC author;
SHOW CREATE TABLE author;
SHOW CREATE DATABASE books;
- 表的修改
/*
語法：
alter table 表名 add|drop|modify|change column 列名 【列類型 約束】;
修改表的存儲引擎
alter table 表名 engine=MyISAM;
*/
#1)修改列名 這里也要添加修改列的類型 change column
#修改book表中的publishDate列名改為pubDate
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
ALTER TABLE book CHANGE COLUMN bName bn VARCHAR(20);
#2)修改列的類型或約束 modify column
DESC book;
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
SHOW CREATE TABLE book;
#3)添加新列 add column
ALTER TABLE author ADD COLUMN salary DOUBLE;
#4)刪除列 drop column
ALTER TABLE author DROP COLUMN salary;
#alter table 表名 rename to 新表名
#5)修改表名
ALTER TABLE author RENAME TO book_author;
#修改表的存儲引擎
USE books;
SHOW CREATE TABLE author;
ALTER TABLE author ENGINE=MYISAM;
- 表的刪除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
#表的清空
DELETE FROM book_author;
TRUNCATE FROM book_author;
#通用寫法
DROP DATABASE IF EXISTS 舊庫名;
CREATE DATABASE 新庫名;
DROP TABLE IF EXISTS 舊表名;
CREATE TABLE 新表名;
DROP TABLE IF EXISTS author;
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(20)
);
- 表的復(fù)制
INSERT INTO author VALUES
(1,'莫言','中國'),
(2,'馮唐','中國'),
(3,'金庸','中國'),
(4,'村上春樹','日本');
#1.僅僅復(fù)制表的結(jié)構(gòu)
CREATE TABLE copy LIKE author;
#2.復(fù)制表的結(jié)構(gòu)+數(shù)據(jù),或復(fù)制部分?jǐn)?shù)據(jù)+結(jié)構(gòu)
CREATE TABLE copy2
SELECT * FROM author;
CREATE TABLE cp1
SELECT id,au_name
FROM author
WHERE nation="中國";
#只復(fù)制部分結(jié)構(gòu)不包含數(shù)據(jù)
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE 1=2;
CREATE TABLE cp2
SELECT au_name
FROM author
WHERE 0;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十二：常見的數(shù)據(jù)類型
/*
數(shù)值型：
整型
小數(shù)：
定點(diǎn)數(shù)
浮點(diǎn)數(shù)
字符型：
較短的文本：char varchar
較長的文本：text blod(較長的二進(jìn)制數(shù)據(jù))
日期型:datetime,timestemp
*/
I陨享、整型
/*
分類：
tinyint, smallint, mediumint, int/integer, bigint
1 2 3 4 5
特定：
1)如果不設(shè)置無符號還是有符號鄙陡，默認(rèn)就是有符號的乃正，如果想設(shè)置無符號的诺苹，需要添加unsigned關(guān)鍵字咕晋，無符號就是沒有負(fù)數(shù)，從0開始
2)如果插入的數(shù)值超出了整型的范圍收奔，會(huì)報(bào)out of range 異常掌呜，并且插入的值為臨界值
3)如果不設(shè)置長度，會(huì)有默認(rèn)的長度
長度代表了顯示的寬度坪哄，如果不夠用會(huì)用0在左邊填充质蕉，但必須搭配zerofill使用!
使用zerofill 默認(rèn)就是無符號的unsigned
*/
CREATE DATABASE IF NOT EXISTS DATA CHARSET utf8mb4 COLLATE utf8mb4_bin;
USE DATA;
SHOW DATABASES;
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT(7) UNSIGNED,
t2 INT(7),
t3 INT(7) ZEROFILL
);
DESC tab_int;
SELECT * FROM tab_int;
INSERT INTO tab_int VALUES(-1234,-1234,-1234);
INSERT INTO tab_int VALUES(04,-1234,1234);
INSERT INTO tab_int VALUES(0,-1234,0);
II势篡、小數(shù)
/*
分類：
1.浮點(diǎn)型
float(M,D)
double(M,D)
2.定點(diǎn)型
dec(M,D)
decimal(M,D)
M:整數(shù)部位+小數(shù)部位
D：小數(shù)部位
M和D都可以省略
如果是decimal,則M默認(rèn)為10，D默認(rèn)為0
如果是float和double模暗，則會(huì)根據(jù)插入的數(shù)值的精度來決定精度
*/
DROP TABLE IF EXISTS tab_float;
CREATE TABLE tab_float(
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL(10,4)
);
SELECT * FROM tab_float;
DESC tab_float;
INSERT INTO tab_float VALUES(123.3453,123.3453,123.3453);
INSERT INTO tab_float VALUES(123.345,123.3453,123.3453);
INSERT INTO tab_float VALUES(123.3,123.3,123.3);
INSERT INTO tab_float VALUES(123.45,123.456745,123456.465);
III禁悠、字符型
/*
char
varchar
binary和varbinary用于保存較短的二進(jìn)制
enum用于保存枚舉
set用于保存集合
較長的文本：
text
blob(較大的二進(jìn)制)
*/
DROP TABLE IF EXISTS tab_char;
CREATE TABLE tab_char(
c1 CHAR(4),
c2 VARCHAR(4)
);
INSERT INTO tab_char VALUES('char','char');
SELECT * FROM tab_char;
INSERT INTO tab_char VALUES('char','char');
DROP TABLE IF EXISTS tab_char;
CREATE TABLE tab_char(
c1 ENUM('a','b','c','6')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('d');
INSERT INTO tab_char VALUES("6");
SELECT * FROM tab_char;
DROP TABLE IF EXISTS tab_char2;
CREATE TABLE tab_char2(
c2 SET('a','b','c','d')
);
INSERT INTO tab_char2 VALUES('a');
INSERT INTO tab_char2 VALUES('A,B,C');
INSERT INTO tab_char2 VALUES('a,b,c');
SELECT * FROM tab_char2;
IV、日期型
/*
分類：
date只保存日期
time只保存時(shí)間
year只保存年
datetime保存日期+時(shí)間
timestamp保存日期+時(shí)間
*/
DROP TABLE IF EXISTS tab_date;
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT t1 時(shí)間1,t2 時(shí)間2 FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';
SET time_zone='system';
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十三：常見約束
/*
含義：一種限制汰蓉，用于限制表中的數(shù)據(jù)绷蹲，為了保證表中的數(shù)據(jù)的準(zhǔn)確和可靠性
分類：六大約束
not null: 非空，用于保證該字段的值不能為空
比如：姓名顾孽、學(xué)號等
default:默認(rèn)祝钢，用于保證該字段有默認(rèn)值
比如：性別
primary key:主鍵，用于保證該字段的值具有唯一性若厚，并且非空
比如：學(xué)號拦英、員工編號等
unique:唯一鍵，用于保證該字段的值具有唯一性测秸，可以為空
比如：座位號
check:檢查約束【mysql不支持】
比如：年齡疤估、性別
foreign key：外鍵，用于限制兩個(gè)表的關(guān)系霎冯，用于保證該字段的值必須來自于主表中的關(guān)聯(lián)列的值
在外表中添加外鍵約束铃拇，用于引用主表中某列的值
比如學(xué)生表的專業(yè)編號，員工表的部門編號沈撞，員工表的工種編號
*/
- 簡單的創(chuàng)建表添加列級約束
USE student;
DROP TABLE IF EXISTS stuinfo1;
CREATE TABLE stuinfo1(
id INT PRIMARY KEY,#主鍵
stuName VARCHAR(20) NOT NULL UNIQUE,#非空唯一鍵
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#檢查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默認(rèn)約束
majorId INT REFERENCES major1(id) #外鍵（寫在列級約束里不起作用）
);
CREATE TABLE major1(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo1;
#查看stuinfo1中所有的索引慷荔，包括主鍵、外鍵缠俺、唯一鍵
SHOW INDEX FROM stuinfo1;
- 添加表級約束
/*
語法：在各個(gè)字段的最下面
[constraint 外鍵名] 約束類型(字段名)
創(chuàng)建表級的外鍵的語法：
[constraint <外鍵名>] foreign key <從表的>字段名1 [,字段名2,...] references <主表名> 主鍵列1, [,主鍵列2,..]
*/
DROP TABLE IF EXISTS stuinfo2;
CREATE TABLE stuinfo2(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主鍵
CONSTRAINT uq UNIQUE(seat), #唯一鍵
CONSTRAINT ck CHECK(gender='男' OR gender='女'), #check檢查鍵
CONSTRAINT fk_stuinfo2_major2 FOREIGN KEY(majorid) REFERENCES major2(id) #外鍵
);
DROP TABLE IF EXISTS major2;
CREATE TABLE major2(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo2;
SHOW INDEX FROM stuinfo2;
DROP TABLE IF EXISTS major;
SHOW TABLES;
/*
unique和primary key的區(qū)別：
一個(gè)表中可以有多個(gè)字段聲明為UNIQUE,但只能最多有一個(gè)primary key聲明,
聲明為primary key的列不允許有空值,但只是聲明為unique的字段允許空值null的存在显晶。
*/
#通用寫法
CREATE TABLE IF NOT EXISTS stuinfo3(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
marjorid INT,
CONSTRAINT fk_stuinfo_marjorid FOREIGN KEY(marjorid) REFERENCES major(id)
);
SHOW CREATE TABLE stuinfo3;
DROP TABLE IF EXISTS major3;
DESC stuinfo3;
CREATE TABLE major3(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
修改表時(shí)添加約束
/*
1.添加列級約束
alter table 表名 modify change column 字段名 字段類型 新約束;
2.添加表級約束
alter table 表名 add [constraint 約束名] 約束類型(字段名) [外鍵的引用]
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
sex CHAR(1),
age INT,
seat INT,
marjorid INT
);
DESC stuinfo;
#添加非空，主鍵壹士，默認(rèn)磷雇，唯一（方法一）
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo CHANGE age age INT DEFAULT 18 NOT NULL;
#添加主鍵，非空躏救，默認(rèn)唯笙，唯一（方法二）設(shè)置默認(rèn)參數(shù)不能用ADD
ALTER TABLE stuinfo ADD UNIQUE(seat);
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#刪除外鍵
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(marjorid) REFERENCES major(id);
DESC stuinfo;
SHOW CREATE TABLE stuinfo;
SHOW INDEX FROM stuinfo;
SHOW CREATE TABLE stuinfo2;
#三、刪除表時(shí)的約束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
DESC stuinfo;
#刪除主鍵
ALTER TABLE stuinfo DROP PRIMARY KEY;
#刪除唯一鍵
ALTER TABLE stuinfo DROP INDEX seat;
#刪除外鍵
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
ALTER TABLE stuinfo MODIFY COLUMN id INT;
SHOW INDEX FROM stuinfo;
#系統(tǒng)信息函數(shù)
#1)查看系統(tǒng)用戶的總連接數(shù)
SELECT CONNECTION_ID();
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SELECT DATABASE();
SELECT SCHEMA();
SELECT USER();
SELECT CURRENT_USER();
SELECT SYSTEM_USER();
SELECT CHARSET(VERSION());
SELECT PASSWORD('yag');
SELECT MD5('yang');
SHOW PROCEDURE STATUS LIKE 's%';
標(biāo)識列
/*
又稱為自增長列
含義：可以不用手動(dòng)的插入值盒使，系統(tǒng)提供默認(rèn)的序列值
特點(diǎn)：
1睁本、標(biāo)識列必須和主鍵搭配嗎？不一定忠怖，但要求是一個(gè)key
2、一個(gè)表可以有幾個(gè)標(biāo)識列抄瑟？至多一個(gè)凡泣！
3枉疼、標(biāo)識列的類型只能是數(shù)值型
4、標(biāo)識列可以通過 SET auto_increment_increment=3;設(shè)置步長
可以通過 手動(dòng)插入值鞋拟，設(shè)置起始值
*/
USE student;
DROP TABLE IF EXISTS tab_identify;
#Incorrect table definition; there can be only one auto column and it must be defined as a key(你必須插入一個(gè)Key的值)
CREATE TABLE tab_identify(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
#seat int
);
TRUNCATE TABLE tab_identify;
INSERT INTO tab_identify(id,NAME) VALUES(10,'yang');
INSERT INTO tab_identify(NAME) VALUES('zhuwenwne');
SELECT * FROM tab_identify;
#這里顯示自增長的默認(rèn)值骂维，auto_increment_increment=1 這個(gè)是步長，auto_increment_offset=1這里是初始化值不能修改
#只對當(dāng)前的環(huán)境有影響
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment=3;
SET auto_increment_increment=1;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十四贺纲、事務(wù)TCL事務(wù)控制語言
/*
事務(wù)：一個(gè)或一組sql語句組成一個(gè)執(zhí)行單元航闺，這個(gè)執(zhí)行單元要么全部執(zhí)行，要么全部不執(zhí)行猴誊。
例如：轉(zhuǎn)賬案例
事務(wù)的特性：
ACID
原子性：一個(gè)事務(wù)不可再分割潦刃，要么都執(zhí)行，要么都不執(zhí)行
一致性：一個(gè)事務(wù)執(zhí)行會(huì)使數(shù)據(jù)從一個(gè)一致狀態(tài)到另外一個(gè)一致狀態(tài)
隔離性：一個(gè)事務(wù)的執(zhí)行不受其他事務(wù)的干擾
持久性：一個(gè)事務(wù)一旦提交懈叹，則會(huì)永久的改變數(shù)據(jù)庫的數(shù)據(jù)
事務(wù)的創(chuàng)建：
隱式事務(wù)：事務(wù)沒有明顯的開啟和結(jié)束的標(biāo)記
比如：insert update delete語句
顯示事務(wù)：事務(wù)具有明顯開啟和結(jié)束的標(biāo)記
前提：必須設(shè)置自動(dòng)提交功能為禁用
set autocommit=0;
步驟1：開啟事務(wù)
set autocommit=0
start transaction;可選的
步驟2：編寫事務(wù)中的sql語句(select insert update delete)
語句1乖杠；
語句2；
...
步驟3：結(jié)束事務(wù)
commit;提交事務(wù)
rollback;回滾事務(wù)
savapoint 節(jié)點(diǎn)名;設(shè)置保存點(diǎn)
事務(wù)的隔離級別： 臟讀 不可重復(fù)讀 幻讀
read uncommitted √ √ √
(讀取未提交)
read committed × √ √
(讀取已提交數(shù)據(jù))
repeateable read × × √
(可重復(fù)讀)
serializable × × ×
(串行化)
mysql中默認(rèn)第三個(gè)隔離級別 repeateable read
oracle中默認(rèn)第二個(gè)隔離級別 read committed
- 查看隔離級別
show @@tx_isolation;
- 設(shè)置隔離級別
set session|global transaction isolation level 隔離級別;
- 開啟事務(wù)的語句：
update 表 set 張三豐的余額=500 where name='張三豐'
update 表 set 郭襄的余額=1500 where name='郭襄'
臟讀: 對于兩個(gè)事務(wù) T1, T2, T1 讀取了已經(jīng)被 T2 更新但還沒有被提交的字段.
之后, 若 T2 回滾, T1讀取的內(nèi)容就是臨時(shí)且無效的.
不可重復(fù)讀: 對于兩個(gè)事務(wù)T1, T2, T1 讀取了一個(gè)字段, 然后 T2 更新了該字段.
之后, T1再次讀取同一個(gè)字段, 值就不同了.
幻讀: 對于兩個(gè)事務(wù)T1, T2, T1 從一個(gè)表中讀取了一個(gè)字段, 然后 T2 在該表中插
入了一些新的行. 之后, 如果 T1 再次讀取同一個(gè)表, 就會(huì)多出幾行
*/
SHOW VARIABLES LIKE 'autocommit';
#查看當(dāng)前的mysql提供什么存儲引擎
SHOW ENGINES;
#查看當(dāng)前的系統(tǒng)默認(rèn)的存儲引擎
SHOW VARIABLES LIKE '%storage_engine%';
#SHOW STATUS 查看MySQL服務(wù)器狀態(tài)
SHOW STATUS;
#如果我們需要查詢本次服務(wù)器啟動(dòng)之后執(zhí)行select語句的次數(shù)澄成，可以執(zhí)行如下命令：
SHOW STATUS LIKE 'com_select';
#查看試圖連接到MySQL(不管是否連接成功)的連接數(shù)
SHOW STATUS LIKE 'connections';
SHOW PROCESSLIST;
SHOW CREATE DATABASE DATA;
SHOW STATUS;
SHOW VARIABLES LIKE 'autocommit';
USE DATA;
DROP TABLE IF EXISTS account;
CREATE TABLE account(
id INT,
NAME VARCHAR(10),
balance INT
);
INSERT INTO account VALUES(1,'張無忌',1000);
INSERT INTO account VALUES(3,'趙敏',1000);
SELECT * FROM account;
- 演示事務(wù)的使用步驟
SET autocommit=0;
START TRANSACTION;
#編寫一組事務(wù)的語句
UPDATE account SET balance=500 WHERE NAME='張無忌';
UPDATE account SET balance=1500 WHERE NAME='趙敏';
#結(jié)束事務(wù)
COMMIT;
SELECT * FROM account;
SET autocommit=0;
START TRANSACTION;
#編寫一組事務(wù)的語句
UPDATE account SET balance=1000 WHERE NAME='張無忌';
UPDATE account SET balance=1000 WHERE NAME='趙敏';
#結(jié)束事務(wù)
ROLLBACK;
#COMMIT;
SELECT * FROM account;
SHOW SESSION VARIABLES;
- 演示savepoint的使用
SET autocommit=0;
SHOW VARIABLES LIKE 'autocommit';
DELETE FROM account WHERE id=1;
SAVEPOINT a;#設(shè)置保存點(diǎn)
DELETE FROM account WHERE id=3;
ROLLBACK TO a;#回滾到保存點(diǎn)
INSERT INTO account VALUES(1,'張三豐',10000);
SELECT * FROM account;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十五胧洒、視圖
/*
視圖 create view 只是保存了sql邏輯 增刪改查，只是一般不能增刪改
表 create table 保存了數(shù)據(jù) 增刪改查
*/
#案例：查詢姓張的學(xué)生名和專業(yè)名
SELECT stuname,majorname
FROM stuinfo s
JOIN major m ON s.`majorid`=m.`id`
WHERE s.`stuname` LIKE '張%';
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
JOIN major m ON s.`majorid`=m.`id`;
SELECT * FROM v1 WHERE stuname LIKE '張';
I墨状、創(chuàng)建視圖
/*
語法：
create view 視圖名
as
查詢語句
*/
USE myemployees;
#1.查詢姓名中包含a字母的員工名卫漫、部門名和工種信息
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON e.job_id=j.job_id;
#使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#查看視圖的結(jié)構(gòu)
DESC myv1;
SHOW CREATE VIEW myv1;
SHOW TABLES;
#2.查詢各部門的平均工資級別
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#使用視圖
SELECT myv2.`ag`,g.`grade_level`
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3.查詢平均工資最低的部門信息
SELECT * FROM myv2 ORDER BY myv2.`ag` LIMIT 1;
#4.查詢平均工資最低的部門名和工資
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY myv2.`ag` LIMIT 1;
SELECT d*,m.`ag`
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
II.視圖的修改
- 方式一：
/*
create or replace view 視圖名
as
查詢語句
*/
DESC myv3;
SHOW CREATE VIEW myv3;
#查看視圖的表的結(jié)果
SELECT * FROM myv3;
#修改視圖myv3如果不存在則會(huì)重新建視圖myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
- 方式二、
/*
語法：
alter view 視圖名
as
查詢語句
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
SELECT * FROM myv3;
III. 刪除視圖(可以刪除多個(gè)視圖名)
/*
語法：drop view 視圖名,視圖名,...;
*/
SHOW TABLE STATUS WHERE COMMENT='view';
DROP VIEW myv1,myv2,myv3;
VI肾砂、查看視圖
#查看所有的視圖(不包括表)
SHOW TABLE STATUS WHERE COMMENT='view';
SHOW TABLES;#查看所有的表和視圖
DESC myv3;#查看所有視圖的結(jié)構(gòu)
IV列赎、視圖的更新(一般不推薦對視圖的更改，刪除等操作)
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
#1.插入(對原視圖employees也會(huì)做修改)
SELECT * FROM myv1;
SELECT * FROM employees;
INSERT INTO myv1 VALUES('張飛','zf@email.qq.com');
#2.修改
UPDATE myv1 SET last_name='張無忌' WHERE last_name='張飛';
#3.刪除
DELETE FROM myv1 WHERE last_name='張無忌';
- 具備以下特點(diǎn)的視圖不能更新
#1)包含于關(guān)鍵字的sql語句：分組函數(shù):distinct通今、grop by粥谬、 having、 union或者union all
#2)常量視圖
CREATE OR REPLACE VIEW myv1
AS
SELECT 'yang' NAME;
SELECT * FROM myv1;
#嘗試更新
UPDATE myv1 SET NAME='zhuwenwen';
#3)select中包含子查詢
CREATE OR REPLACE VIEW myv2
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工資
FROM departments;
#嘗試更新
SELECT * FROM myv2;
UPDATE myv2 SET 最高工資=1000;
#4)from一個(gè)不能更新的視圖
CREATE OR REPLACE VIEW myv3
AS
SELECT * FROM myv2;
#嘗試更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工資=10000 WHERE department_id=60;
#5)where字句的子查詢引用了from 字句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
#嘗試更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name='K_ing';
案例講解
#1.創(chuàng)建視圖emp_v1辫塌，要求查詢電話號碼以'011'開頭的員工姓名和工資漏策、郵箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email,phone_number
FROM employees
WHERE phone_number LIKE '011%';
SELECT * FROM emp_v1;
#2.創(chuàng)建視圖emp_v2，要求查詢部門的最高工資高于12000的部門信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) max_dep,department_id
FROM employees
GROUP BY department_id
HAVING max_dep>12000;
SELECT * FROM emp_V2;
SELECT d.*,m.`max_dep`
FROM departments d
JOIN emp_v2 m ON m.`department_id`=d.`department_id`;
#案例
#聲明并初始化
SET @name:='john';
USE myemployees;
#賦值
SELECT COUNT(*) INTO @count
FROM employees;
#查看自定義變量
SELECT @count;
SELECT NOW();
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十六臼氨、變量
/*
系統(tǒng)變量：
全局變量
會(huì)話變量
自定義變量：
用戶變量
局部變量
*/
一掺喻、系統(tǒng)變量
/*
說明：變量由系統(tǒng)定義，不是用戶自定義储矩，屬于服務(wù)器層面
注意：全局變量需要添加global關(guān)鍵字感耙，會(huì)話變量需要添加session關(guān)鍵字，如果不寫持隧，默認(rèn)會(huì)話級別
使用步驟：
1.查看所有系統(tǒng)變量
show global[session] variables;
2.滿足條件的部分系統(tǒng)變量
show global|[session] variables like '%char%';
3.查看指定的系統(tǒng)變量的值
select @@global|[session] 系統(tǒng)變量名
4.為某個(gè)系統(tǒng)變量賦值
方式一：
set global|[session] 系統(tǒng)變量名=值;
方式二：
set @@global|[session] 系統(tǒng)變量名=值;
*/
#1)全局變量
/*
作用域：針對于所有會(huì)話(連接)有效即硼，但不能跨重啟
*/
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES LIKE '%char%';
#查看指定的系統(tǒng)變量值
SELECT @@global.autocommit;
#為某個(gè)系統(tǒng)變量賦值
SET @@global.autocommit=0;
SET GLOBAL autocommit=1;
#2) 會(huì)話變量
/*
作用域：針對于當(dāng)前會(huì)話（連接）有效
*/
#①查看所有會(huì)話變量
SHOW SESSION VARIABLES;
#②查看滿足條件的部分會(huì)話變量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的會(huì)話變量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④為某個(gè)會(huì)話變量賦值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
二、自定義變量
/*
使用步驟：
1屡拨、聲明
2只酥、賦值
3褥实、使用(查看、比較裂允、運(yùn)算等)
*/
#1）用戶變量
#方式一：
SET @wen='zhubaobao';
SELECT @wen;
#方式二：
SELECT * FROM employees WHERE last_name='K_ing';
SELECT * FROM employees;
SELECT last_name INTO @yang
FROM employees
WHERE last_name='Kochhar';
SELECT '我是豬' INTO @y;
SELECT @yang;
SELECT @y;
#2.局部變量
/*
作用域：僅僅在定義它的begin end中有效
*/
#聲明
DECLARE 變量名 類型;
DECLARE 變量名 類型 DEFAULT 值;
#案例：聲明兩個(gè)變量损离，求和并打印
SET @m=1;
SET @n=2;
SET @sum=@n+@m;
SELECT @sum;
#局部變量
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE j INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=i+j;
SELECT SUM;
END
BEGIN
#Routine body goes here...
#變量的定義
DECLARE var2 INT(4);
#變量的賦值方式一：直接賦值
SET var2 = 2;
#變量的賦值方式二：將查詢結(jié)果賦值給變量
SELECT u_id INTO var2 FROM users WHERE u_name = 'zhangsan';
END
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十七、存儲過程和函數(shù)
#------------------------------------------
/*
存儲過程和函數(shù)：類似于java中的方法
好處：
1绝编、提高代碼的重用性
2僻澎、簡化操作
*/
#存儲過程
/*
含義：一組預(yù)先編譯好的SQL語句的集合，理解成批處理語句
1十饥、提高代碼的重用性
2窟勃、簡化操作
3、減少了編譯次數(shù)并且減少了和數(shù)據(jù)庫服務(wù)器的連接次數(shù)绷跑，提高了效率
*/
一. 創(chuàng)建語法:procedure
CREATE PROCEDURE 存儲過程名(參數(shù)列表)
BEGIN
存儲過程體（一組合法的SQL語句）
END
#注意：
/*
1拳恋、參數(shù)列表包含三部分
參數(shù)模式 參數(shù)名 參數(shù)類型
舉例：
in stuname varchar(20)
參數(shù)模式：
in：該參數(shù)可以作為輸入，也就是該參數(shù)需要調(diào)用方傳入值
out：該參數(shù)可以作為輸出砸捏，也就是該參數(shù)可以作為返回值
inout：該參數(shù)既可以作為輸入又可以作為輸出谬运，也就是該參數(shù)既需要傳入值，又可以返回值
2垦藏、如果存儲過程體僅僅只有一句話梆暖，begin end可以省略
存儲過程體中的每條sql語句的結(jié)尾要求必須加分號。
存儲過程的結(jié)尾可以使用 delimiter 重新設(shè)置
語法：
delimiter 結(jié)束標(biāo)記
案例：
delimiter $
[delimiter $]修改默認(rèn)的命令結(jié)束符為[$]掂骏，函數(shù)體以[$]結(jié)束轰驳，[delimiter ;]恢復(fù)默認(rèn)的結(jié)束符為[;]。
*/
二弟灼、調(diào)用語法
CALL 存儲過程名(實(shí)參列表);
#-----------------------案例演示---------------------------
#1.空參列表
#案例1：插入到admin表中的5條記錄
SELECT * FROM admin;
DESC admin;
USE girls;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('tom','0000'),('alice','1000'),('tial','1000'),('apple','1000'),('juse','11000'),;
END $
SHOW TABLES;
#2.創(chuàng)建帶in模式參數(shù)的存儲過程
#案例1：創(chuàng)建存儲過程 根據(jù)女神名级解，查詢對應(yīng)的男神信息
USE myemployees;
USE girls;
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
JOIN beauty b ON b.boyfriend_id=bo.id
WHERE b.name=beautyName;
END $
#調(diào)用myp2存儲函數(shù)
CALL myp2('柳巖')$
#案例2：創(chuàng)建存儲過程實(shí)現(xiàn)，用戶是否登錄成功
CREATE PROCEDURE myp3(IN username VARCHAR(20) IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#聲明并初始化
SELECT COUNT(*) INTO result #賦值
FROM admin
WHERE admin.`username`=username AND admin.`password`=`password`;
SELECT IF(result>0,'成功','失敗');
END $
#調(diào)用
CALL myp3('張飛','88888')$
#3.創(chuàng)建out模式參數(shù)的存儲過程
#案例1:根據(jù)輸入的女神名田绑，返回對應(yīng)的男神名
CREATE PROCEDURE myp6(IN byN VARCHAR(20),OUT boN VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boN
FROM beauty b
JOIN boys bo ON b.boyfriend_id=bo.id
WHERE b.name=byN;
END $
#調(diào)用
CALL myp6('小昭',$boN)$ #這里的$boN相當(dāng)于自定義變量boN的值等于myp6的boN的值
#案例2：根據(jù)輸入的女神名勤哗，返回對應(yīng)的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP VARCHAR(20))
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCp
FROM boys bo
JOIN beauty b ON b.boyfriend_id=bo.id
WHERE b.name=beautyName;
END $
#調(diào)用并定義變量的值
CALL myp7('小昭',@boN,@userCP)$
SELECT @boN,@usercp;
#4.創(chuàng)建帶inout模式參數(shù)的存儲過程
#案例1：傳入a和b連個(gè)值，最終a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b)
BEGIN
SET a=a*2;
SET b=b*2
END $
#定義變量
SET @m=1000$
SET @n=2000$
#調(diào)用定義的變量值
CALL myp8(@m,@n)$
SELECT @m,@n$
SHOW PROCEDURE;
四掩驱、刪除存儲過程
#語法：drop procedure 存儲過程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p5,p3;#不能進(jìn)行多個(gè)刪除存儲過程名
五芒划、查看存儲過程信息，和創(chuàng)建所有的存儲名
#查看所在庫的所有創(chuàng)建的存儲名
SHOW PROCEDURE STATUS WHERE db='girls';
#查看存儲過程信息
DESC myp3; #不能查看
SHOW CREATE PROCEDURE myp3;
SHOW CREATE PROCEDURE myp4;
存儲的案例解析
#1）創(chuàng)建存儲過程實(shí)現(xiàn)傳入用戶名和密碼欧穴，插入到admin表中
DELIMITER $
CREATE PROCEDURE p1(IN uname VARCHAR(20),IN pword VARCHAR(20))
BEGIN
INSERT INTO admin (username,PASSWORD) VALUES(uname,pword);
END $
CALL p1('張飛','110')$
#2）創(chuàng)建存儲過程或函數(shù)實(shí)現(xiàn)傳入女神編號民逼，返回女神名稱和女神電話
CREATE PROCEDURE p2(IN bid INT,OUT bname VARCHAR(20),OUT bphone VARCHAR(20))
BEGIN
SELECT b.name,b.phone INTO bname,bphone
FROM beauty b
WHERE b.id=bid;
END $
CALL p2(2,@name,@phone)$
SELECT @name 姓名,@phone 電話號碼$
#3）創(chuàng)建存儲過程或函數(shù)實(shí)現(xiàn)傳入兩個(gè)女神生日，返回大小
CREATE PROCEDURE p3(IN bdate1 DATETIME,IN bdate2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(bdate1,bdate2) INTO result;
END $
CALL p3('1998-1-1',NOW(),@result)$
SELECT @result$
#4）創(chuàng)建存儲過程或函數(shù)實(shí)現(xiàn)傳入一個(gè)日期涮帘，格式化成xx年xx月xx日并返回
CREATE PROCEDURE p4(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
#date_format將日期轉(zhuǎn)字符
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strdate;
END$
CALL p4(NOW(),@str)$
SELECT @str$
#5）創(chuàng)建存儲過程或函數(shù)實(shí)現(xiàn)傳入女神名稱拼苍，返回：女神 and 男神 格式的字符串
#如 傳入： 小昭
# 返回： 張無忌
DROP PROCEDURE p5$
CREATE PROCEDURE p5(IN bname VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(bname,' and ', IFNULL(bo.boyName,'null')) INTO str
FROM boys bo
JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=bname;
SET str=
END$
CALL p5('小昭',@name)$
SELECT @name$
#6）創(chuàng)建存儲過程或函數(shù)，根據(jù)傳入的條目數(shù)和起始索引调缨，查詢beauty表的記錄
CREATE PROCEDURE p6(IN size INT,IN startindex INT)
BEGIN
SELECT * FROM beauty LIMIT startindex,size;
END $
CALL p6(3,5)$
SELECT * FROM beauty LIMIT 2,4;
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十八映屋、函數(shù)
/*
含義：一組預(yù)先編譯好的SQL語句的集合苟鸯，理解成批處理語句
1、提高代碼的重用性
2棚点、簡化操作
3、減少了編譯次數(shù)并且減少了和數(shù)據(jù)庫服務(wù)器的連接次數(shù)湾蔓，提高了效率
區(qū)別：
存儲過程：可以有0個(gè)返回瘫析，也可以有多個(gè)返回，適合做批量插入默责、批量更新
函數(shù)：有且僅有1 個(gè)返回贬循，適合做處理數(shù)據(jù)后返回一個(gè)結(jié)果
*/
一、創(chuàng)建語法
CREATE FUNCTION 函數(shù)名(參數(shù)列表) RETURNS 返回類型
BEGIN
函數(shù)體
END $
#注意：
#1.參數(shù)列表 包含兩部分
#參數(shù)名 參數(shù)類型
#2.函數(shù)體：肯定會(huì)有return語句桃序，如果沒有會(huì)報(bào)錯(cuò)
#如果return語句沒有放在函數(shù)體的最后也不會(huì)報(bào)錯(cuò)杖虾，但不建議
#retur 值
#3.函數(shù)體重僅有一句話，則可以省略begin end
#4.使用delimiter語句設(shè)置結(jié)束標(biāo)記
二媒熊、調(diào)用語法 SELECT 函數(shù)名(參數(shù)列表)
- 無參有返回
#案例：返回公司的員工個(gè)數(shù)
USE myemployees;
DROP FUNCTION f1;
DELIMITER $
CREATE FUNCTION f1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定義局部變量
SELECT COUNT(*) INTO c#賦值
FROM employees;
RETURN c;
END $
#查看函數(shù)體
SELECT f1()$
<font color=#FF0000 size=4>
當(dāng)出現(xiàn)了這個(gè)錯(cuò)誤的時(shí)候奇适，就是MySQL默認(rèn)是不允許創(chuàng)建函數(shù)
/* ERROR 1418 (HY000): This FUNCTION has NONE of DETERMINISTIC, NO SQL, OR READS SQL DATA IN its declaration AND BINARY l
ging IS enabled (you might want TO USE the LESS safe log_bin_trust_function_creators variable) */
</font>
#1.更改全局配置
SET GLOBAL log_bin_trust_function_creators=1;
SHOW VARIABLES LIKE 'log_bin%';
SET GLOBAL log_bin_trust_function_creators=1;
#2、更改配置文件my.cnf
LOG-BIN-trust-FUNCTION-creators=1; #重啟服務(wù)生效
- 有參有返回
#案例1：根據(jù)員工名芦鳍，返回它的工資
CREATE FUNCTION f2(ename VARCHAR(20)) RETURNS INT
BEGIN
SET @sal=0;
SELECT salary INTO @sal
FROM employees e
WHERE e.`last_name`=ename;
RETURN @sal;
END$
SELECT f2('Kochhar')$
SELECT * FROM employees WHERE last_name='Kochhar';
#案例2：根據(jù)部門名嚷往，返回該部門的平均工資
CREATE FUNCTION f3(dname VARCHAR(20)) RETURNS INT
BEGIN
DECLARE asal INT DEFAULT 0;
SELECT AVG(salary) INTO asal
FROM employees e
JOIN departments d ON e.department_id=d.department_id
WHERE d.department_name=dname;
RETURN asal;
END $
SELECT f3('部門名');
#查看各部門的平均工資
SELECT AVG(salary),department_name
FROM employees e
JOIN departments d ON e.department_id=d.department_id
GROUP BY d.department_name;
三、查看函數(shù)
#查看所在庫的所有創(chuàng)建的函數(shù)名
SHOW FUNCTION STATUS WHERE db='myemployees';
#查看詳細(xì)的函數(shù)創(chuàng)建過程
SHOW CREATE FUNCTION f1;
四柠衅、刪除函數(shù)
DROP FUNCTION f1;
案例：一皮仁、創(chuàng)建函數(shù)，實(shí)現(xiàn)傳入兩個(gè)float,返回兩者之和
CREATE FUNCTION sums(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE s FLOAT DEFAULT 0;
SET s=num1+num2;
RETURN s;
END $
SELECT sums(14234.4325,2352.231)$
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
#-----------------------------------------------------------------------------------------
十九菲宴、流程控制結(jié)構(gòu)
/*
順序結(jié)構(gòu)：程序從上往下依次執(zhí)行
分支結(jié)構(gòu)：程序從兩條或多條路徑中選擇一條去執(zhí)行
循環(huán)結(jié)構(gòu)：程序滿足一定條件的基礎(chǔ)贷祈，重復(fù)執(zhí)行一段代碼
*/
#一、分支結(jié)構(gòu)
#1.if函數(shù)
/*
語法：if(條件,值1喝峦，值2)
功能：實(shí)現(xiàn)雙分支
應(yīng)用在begin end中或外面
*/
#2.case結(jié)構(gòu)
/*
語法：
情況1：類似于switch
case 變量或表達(dá)式
when 值1 then 語句1;
when 值2 then 語句2;
...
else 語句n;
end
情況2：
case
when 條件1 then 語句1;
when 條件2 then 語句2;
...
else 語句n;
end
應(yīng)用在begin end 中或外面
*/
#3.if結(jié)構(gòu)
/*
語法：
if 條件1 then 語句1;
elseif 條件2 then 語句2;
....
else 語句n;
end if;
功能：類似于多重if
只能應(yīng)用在begin end 中
*/
- 案例：創(chuàng)建函數(shù)势誊，實(shí)現(xiàn)傳入成績，如果成績>90,返回A愈犹，如果成績>80键科，返回B，如果成績>60漩怎，返回C勋颖，否則返回D
#利用存儲過程實(shí)現(xiàn)case語句
DELIMITER $
CREATE PROCEDURE pro_case(IN score FLOAT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT "A";
WHEN score>=80 THEN SELECT "B";
WHEN score>=60 THEN SELECT "C";
ELSE SELECT "D";
END CASE;
END $
#利用函數(shù)實(shí)現(xiàn)if語句
CREATE FUNCTION pro_if(score FLOAT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
#1.while
/*
語法：
【標(biāo)簽:】while 循環(huán)條件 do
循環(huán)體;
end while【 標(biāo)簽】;
聯(lián)想：
while(循環(huán)條件){
循環(huán)體;
}
循環(huán)控制：
#2.loop
/*
語法：
【標(biāo)簽:】loop
循環(huán)體;
end loop 【標(biāo)簽】;
可以用來模擬簡單的死循環(huán)
*/
#3.repeat
/*
語法：
【標(biāo)簽：】repeat
循環(huán)體;
until 結(jié)束循環(huán)的條件
end repeat 【標(biāo)簽】;
*/
iterate類似于 continue，繼續(xù)勋锤，結(jié)束本次循環(huán)饭玲，繼續(xù)下一次
leave 類似于 break，跳出叁执，結(jié)束當(dāng)前所在的循環(huán)
*/
練習(xí)案例
#1.沒有添加循環(huán)控制語句
#案例：批量插入茄厘，根據(jù)次數(shù)插入到admin表中多條記錄
TRUNCATE admin;
CREATE PROCEDURE pro_while1(IN inserCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i < inserCount DO
INSERT INTO girls.`admin`(username,PASSWORD) VALUES(CONCAT('yang',i),CONCAT('1000',i));
SET i=i+1;
END WHILE a;
END$
CALL pro_while(100)$
#案例：生成10萬個(gè)用戶和隨機(jī)密碼
create table admin1(
id int primary key,auto_increment,
username varchar(8),
password varchar(11)
);
TRUNCATE admin1;
CREATE PROCEDURE pro_while2(IN inserCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE randStr VARCHAR(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare s varchar(11) default '11111111111';
a:WHILE i < inserCount DO
#SELECT CONCAT('1',CEIL(RAND()*9000000000+100000000)) into s;
set s=CONCAT('1',CEIL(RAND()*9000000000+100000000));
SET name=CONCAT(SUBSTR(randStr,ceil(RAND()*LENGTH(randStr)-4)),4);
INSERT INTO girls.`admin1`(username,PASSWORD) VALUES(name,s);
SET i=i+1;
END WHILE a;
END$
CALL pro_while2(10000000)$
#2.添加leave循環(huán)控制語句(跳出矮冬，結(jié)束當(dāng)前所在的所有循環(huán))（leave一般結(jié)合if語句去使用）
#案例：批量插入，根據(jù)次數(shù)插入到admin表中多條記錄次哈，如果次數(shù)>20則停止
TRUNCATE girls.`admin`;
CREATE PROCEDURE pro_leave1(IN inCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<inCount DO
INSERT INTO girls.`admin`(username,PASSWORD) VALUES(CONCAT('豬文文',i),CONCAT('1000',i));
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL pro_leave1(100)$
#3.添加iterate語句(跳出當(dāng)前循環(huán)繼續(xù)下一次循環(huán))胎署，一般會(huì)結(jié)合If語句使用
TRUNCATE girls.`admin`;
CREATE PROCEDURE pro_iterate1(IN inCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<inCount DO
SET i=i+1;
IF MOD(i,2) THEN ITERATE a;
END IF;
INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('豬文文',i),CONCAT('1000',i));
END WHILE a;
END $
CALL pro_iterate1(101)$
#已知表million表
DROP TABLE IF EXISTS million;
CREATE TABLE million(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(32)
);
DELIMITER $
CREATE PROCEDURE test_million(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR(32) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
DECLARE startIndex INT;#代表初始索引
DECLARE len INT;#代表截取的字符長度
a:WHILE i<=insertcount DO
SET str=MD5(i);
#SET startIndex=FLOOR(RAND()*32+1);#代表初始索引，隨機(jī)范圍1-26
#SET len=FLOOR(RAND()*(36-startIndex)+1);#代表截取長度窑滞，隨機(jī)范圍1-（20-startIndex+1）
INSERT INTO million(content) VALUES(SUBSTR(str,1,8));
SET i=i+1;
END WHILE a;
END $
call test_million(10000)$
#插入的結(jié)果時(shí)間大約為2分鐘
mysql> call test_million(100000)$
Query OK, 1 row affected (2 min 49.34 sec)
