MySQL基础（尚硅谷笔记）
生活随笔
收集整理的這篇文章主要介紹了
MySQL基础（尚硅谷笔记）
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
基于尚硅谷MySQL視頻的學(xué)習(xí)筆記
目錄
1. 初識MySQL
1.1 數(shù)據(jù)庫相關(guān)概念
1.2 MySQL介紹
2. DQL語言
2.1 基礎(chǔ)查詢
2.2 條件查詢
2.3 排序查詢
2.4 常見函數(shù)
2.5 分組查詢
2.6 連接查詢
2.7 子查詢
2.8 分頁查詢
2.9 聯(lián)合查詢
2.9 聯(lián)合查詢
3. DML語言
3.1 插入語句
3.2 修改語句
3.3 刪除語句
4. DDL語言
4.1 庫的管理
4.2 表的管理
4.3 數(shù)據(jù)類型
4.4 常見約束
4.5 表與表之間的關(guān)系
5. TCL語言
5.1 事務(wù)的含義
5.2 事務(wù)的特點(diǎn)
5.3 事務(wù)的使用
5.4 并發(fā)事務(wù)
6. 其他
6.1 視圖
6.2 變量
6.3 存儲過程和函數(shù)
6.4 流程控制結(jié)構(gòu)
1. 初識MySQL
1.1 數(shù)據(jù)庫相關(guān)概念
- 數(shù)據(jù)庫的常見概念 ★
1、DB：數(shù)據(jù)庫，存儲數(shù)據(jù)的容器
2、DBMS：數(shù)據(jù)庫管理系統(tǒng)，又稱為數(shù)據(jù)庫軟件或數(shù)據(jù)庫產(chǎn)品，用于創(chuàng)建或管理DB
3、SQL：結(jié)構(gòu)化查詢語言，用于和數(shù)據(jù)庫通信的語言，不是某個數(shù)據(jù)庫軟件特有的，而是幾乎所有的主流數(shù)據(jù)庫軟件通用的語言 - 數(shù)據(jù)庫存儲數(shù)據(jù)的特點(diǎn)
1、數(shù)據(jù)存放到表中，然后表再放到庫中
2、一個庫中可以有多張表，每張表具有唯一的表名用來標(biāo)識自己
3、表中有一個或多個列，列又稱為“字段”，相當(dāng)于java中“屬性”
4、表中的每一行數(shù)據(jù)，相當(dāng)于java中“對象” - 常見的數(shù)據(jù)庫管理系統(tǒng)
mysql、oracle、db2、sqlserver
1.2 MySQL介紹
-
MySQL的背景
前身屬于瑞典的一家公司，MySQL AB
08年被sun公司收購
09年sun被oracle收購 -
MySQL的優(yōu)點(diǎn)
- 開源、免費(fèi)、成本低
- 性能高、移植性也好
- 體積小，便于安裝
-
MySQL服務(wù)的啟動和停止
- 方式一：通過命令行 net start 服務(wù)名 net stop 服務(wù)名
- 方式二：計(jì)算機(jī)——右擊——管理——服務(wù)
- MySQL服務(wù)的登錄和退出
-
登錄：
mysql 【-h 主機(jī)名 -P 端口號】 -u 用戶名 -p密碼退出：
exit 或 ctrl+C
-
- MySQL的常見命
-
MySQL的語法規(guī)范
-
不區(qū)分大小寫,但建議關(guān)鍵字大寫，表名、列名小寫
-
每條命令最好用分號結(jié)尾
-
每條命令根據(jù)需要，可以進(jìn)行縮進(jìn) 或換行
-
注釋
單行注釋：#注釋文字-
單行注釋：– 注釋文字
-
多行注釋：/* 注釋文字 */
-
-
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
2. DQL語言
2.1 基礎(chǔ)查詢
# 1、查詢單個字段 select 字段名 from 表名;# 2、查詢多個字段 select 字段名，字段名 from 表名;# 3、查詢所有字段 select * from 表名;# 4、查詢常量 # 注意：字符型和日期型的常量值必須用單引號引起來，數(shù)值型不需要 select 常量值;# 5、查詢函數(shù) select 函數(shù)名(實(shí)參列表);# 6、查詢表達(dá)式 select 100/1234;# 7、起別名 # ① as # ② 空格# 8、去重 select distinct 字段名 from 表名;# 9、+ # 作用：做加法運(yùn)算 select 數(shù)值+數(shù)值; # 直接運(yùn)算 select 字符+數(shù)值; # 先試圖將字符轉(zhuǎn)換成數(shù)值，如果轉(zhuǎn)換成功，則繼續(xù)運(yùn)算；否則轉(zhuǎn)換成0，再做運(yùn)算 select null+值; # 結(jié)果都為null# 10、【補(bǔ)充】concat函數(shù) # 功能：拼接字符 select concat(字符1，字符2，字符3,...);# 11、【補(bǔ)充】ifnull函數(shù) # 功能：判斷某字段或表達(dá)式是否為null，如果為null 返回指定的值，否則返回原本的值 select ifnull(commission_pct,0) from employees;# 12、【補(bǔ)充】isnull函數(shù) # 功能：判斷某字段或表達(dá)式是否為null，如果是，則返回1，否則返回02.2 條件查詢
# 1.條件查詢的基本結(jié)構(gòu) select 查詢列表 from 表名 where 篩選條件# 2.簡單條件運(yùn)算符 > < = <> != >= <= <=>安全等于 # 可以判斷null值# 3. 邏輯運(yùn)算符 && and || or ! not# 4、模糊查詢 between and # 左右邊界不能顛倒順序 between 120 and 100 # 等價(jià)于 >= 120 && <= 100 in ('', '') # in列表值類型必須統(tǒng)一,且不支持通配符 is null /is not null # 用于判斷null值, = 不能判斷null like # 一般搭配通配符使用，可以判斷字符型或數(shù)值型 # 5.通配符： %：任意多個字符 _：任意單個字符 通配符轉(zhuǎn)義 \ 或者 '_$_' ESCAPE '$'普通類型的數(shù)值 null值 可讀性 is null × √ √ <=> √ √ ×經(jīng)典面試題?：
select * from employees; select * from employees where name like '%%' and id like '%%'; 兩個查詢語句的執(zhí)行結(jié)果是否相同？ 解：不一樣，若name或者id有null值則查不出來2.3 排序查詢
# 1. 排序查詢的基本結(jié)構(gòu) select 查詢列表 from 表 where 篩選條件 order by 排序列表 [asc}desc]# 2. 特點(diǎn) /* 1、asc ：升序，如果不寫默認(rèn)升序desc：降序 2、排序列表 支持 單個字段、多個字段、函數(shù)、表達(dá)式、別名 3、order by的位置一般放在查詢語句的最后（除limit語句之外） */2.4 常見函數(shù)
- 功能：類似于java中的方法
- 好處：提高重用性和隱藏實(shí)現(xiàn)細(xì)節(jié)
- 調(diào)用：select 函數(shù)名(實(shí)參列表);
單行函數(shù)?
# 1、字符函數(shù) # concat:連接 # substr:截取子串，索引從1開始 # upper:變大寫 # lower：變小寫 # replace：替換 # length：獲取字節(jié)長度 # trim:去前后空格 # lpad：左填充 # rpad：右填充 # instr:獲取子串第一次出現(xiàn)的索引SELECT LENGTH('john'); SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees; SELECT UPPER('john'); SELECT LOWER('joHn'); SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees; SELECT SUBSTR('李莫愁愛上了陸展元',7) out_put; SELECT SUBSTR('李莫愁愛上了陸展元',1,3) out_put; SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees; SELECT INSTR('楊不殷六俠悔愛上了殷六俠','殷八俠') AS out_put; SELECT LENGTH(TRIM(' 張翠山 ')) AS out_put; SELECT TRIM('aa' FROM 'aaaaaa張aaaa翠山aaaaa') AS out_put; SELECT LPAD('殷素素',2,'*') AS out_put; SELECT RPAD('殷素素',12,'ab') AS out_put; SELECT REPLACE('周芷若周芷若張無忌愛上了周芷若','周芷若','趙敏') AS out_put;# 2、數(shù)學(xué)函數(shù) # ceil:向上取整 # round：四舍五入 # mod:取模 # floor：向下取整 # truncate:截?cái)?# rand:獲取隨機(jī)數(shù)，返回0-1之間的小數(shù) SELECT ROUND(-1.55); SELECT ROUND(1.567,2); SELECT CEIL(-1.02); SELECT FLOOR(-9.99); SELECT TRUNCATE(1.69999,1); #mod取余 /* mod(a,b) ： a-a/b*b mod(-10,-3):-10- (-10)/(-3)*（-3）=-1 */ SELECT MOD(10,-3); SELECT 10%3;# 3、日期函數(shù) # now：返回當(dāng)前日期+時(shí)間 # year:返回年 # month：返回月 # day:返回日 # date_format:將日期轉(zhuǎn)換成字符 # curdate:返回當(dāng)前日期 # str_to_date:將字符轉(zhuǎn)換成日期 # curtime：返回當(dāng)前時(shí)間 # hour:小時(shí) # minute:分鐘 # second：秒 # datediff:返回兩個日期相差的天數(shù) # monthname:以英文形式返回月 SELECT NOW(); SELECT CURDATE(); SELECT CURTIME(); SELECT YEAR(NOW()) 年; SELECT YEAR('1998-1-1') 年; SELECT YEAR(hiredate) 年 FROM employees; SELECT MONTH(NOW()) 月; SELECT MONTHNAME(NOW()) 月; SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; #查詢?nèi)肼毴掌跒?992--4-3的員工信息 SELECT * FROM employees WHERE hiredate = '1992-4-3'; SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y'); SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put; #查詢有獎金的員工名和入職日期(xx月/xx日 xx年) SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入職日期 FROM employees WHERE commission_pct IS NOT NULL;# 4、其他函數(shù) # version 當(dāng)前數(shù)據(jù)庫服務(wù)器的版本 # database 當(dāng)前打開的數(shù)據(jù)庫 # user當(dāng)前用戶 # password('字符')：返回該字符的密碼形式 # md5('字符'):返回該字符的md5加密形式 SELECT VERSION(); SELECT DATABASE(); SELECT USER();#5、流程控制函數(shù) /* ①if(條件表達(dá)式，表達(dá)式1，表達(dá)式2)：如果條件表達(dá)式成立，返回表達(dá)式1，否則返回表達(dá)式2 ②case情況1 case 變量或表達(dá)式或字段 when 常量1 then 值1 when 常量2 then 值2 ... else 值n end③case情況2 case when 條件1 then 值1 when 條件2 then 值2 ... else 值n end */ SELECT IF(10<5,'大','小');SELECT 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 AS 新工資 FROM employees;SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS 工資級別 FROM employees;分組函數(shù)(統(tǒng)計(jì)函數(shù)\聚合函數(shù))?
# 1.分類 max 最大值 min 最小值 sum 和 avg 平均值 count 計(jì)算個數(shù)# 2.簡單 的使用 SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT MAX(salary) FROM employees; SELECT COUNT(salary) FROM employees;SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 個數(shù) FROM employees;SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 個數(shù) FROM employees;# 3、參數(shù)支持哪些類型 # sum和avg一般用于處理數(shù)值型 # max、min、count可以處理任何數(shù)據(jù)類型 SELECT SUM(last_name) ,AVG(last_name) FROM employees; SELECT SUM(hiredate) ,AVG(hiredate) FROM employees; SELECT MAX(last_name),MIN(last_name) FROM employees; SELECT MAX(hiredate),MIN(hiredate) FROM employees; SELECT COUNT(commission_pct) FROM employees; SELECT COUNT(last_name) FROM employees;# 4、是否忽略null(以上分組函數(shù)都忽略) SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees;# 5、和distinct搭配 SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;# 6、count函數(shù)的詳細(xì)介紹 count(字段)：統(tǒng)計(jì)該字段非空值的個數(shù) count(*):統(tǒng)計(jì)結(jié)果集的行數(shù) SELECT COUNT(salary) FROM employees; SELECT COUNT(*) FROM employees; SELECT COUNT(1) FROM employees;效率： MYISAM存儲引擎下，COUNT(*)的效率高 INNODB存儲引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些#6、和分組函數(shù)一同查詢的字段有限制,要求是group by后出現(xiàn)的字段 SELECT AVG(salary),employee_id FROM employees; # 這個查詢沒有意義2.5 分組查詢
# 1.分組查詢的基本結(jié)構(gòu) select 分組函數(shù)，分組后的字段 from 表 [where 篩選條件] group by 分組的字段 [having 分組后的篩選] [order by 排序列表]# 2.篩選對比使用關(guān)鍵字 篩選的表 位置 分組前篩選 where 原始表 group by的前面 分組后篩選 having 分組后的結(jié)果 group by 的后面# 3. 常見問題 1：分組函數(shù)做篩選能不能放在where后面 答：不能2：where——group by——having 答：一般來講，能用分組前篩選的，盡量使用分組前篩選，提高效率3、分組可以按單個字段也可以按多個字段 4、可以搭配著排序使用# 4. 一些示例 #案例1：查詢每個工種的員工平均工資 SELECT AVG(salary),job_id FROM employees GROUP BY job_id; #案例2：每個工種有獎金的員工的最高工資>12000的工種編號和最高工資 SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000; #案例3：每個工種有獎金的員工的最高工資>6000的工種編號和最高工資,按最高工資升序 SELECT job_id,MAX(salary) m FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING m>6000 ORDER BY m ; #案例4：查詢每個工種每個部門的最低工資,并按最低工資降序 SELECT MIN(salary),job_id,department_id FROM employees GROUP BY department_id,job_id ORDER BY MIN(salary) DESC;2.6 連接查
2.7 子查詢
# 1. 子查詢的含義 出現(xiàn)在其他語句中的select語句，稱為子查詢或內(nèi)查詢 外部的查詢語句，稱為主查詢或外查詢# 2. 分類：# 按子查詢出現(xiàn)的位置：select后面：僅僅支持標(biāo)量子查詢from后面：支持表子查詢where或having后面：★標(biāo)量子查詢（單行） √列子查詢 （多行） √行子查詢exists后面（相關(guān)子查詢）表子查詢# 按結(jié)果集的行列數(shù)不同：標(biāo)量子查詢（結(jié)果集只有一行一列）列子查詢（結(jié)果集只有一列多行）行子查詢（結(jié)果集有一行多列）表子查詢（結(jié)果集一般為多行多列）# 3. 示例 # 3.1 where或having后面 特點(diǎn)： ① 子查詢放在小括號內(nèi) ② 子查詢一般放在條件的右側(cè) ③ 標(biāo)量子查詢，一般搭配著單行操作符使用 > < >= <= = <> 列子查詢，一般搭配著多行操作符使用 in、any/some、all④ 子查詢的執(zhí)行優(yōu)先于主查詢執(zhí)行，主查詢的條件用到了子查詢的結(jié)果# 標(biāo)量子查詢案例：查詢最低工資的員工姓名和工資① 最低工資select min(salary) from employees② 查詢員工的姓名和工資，要求工資=①select last_name,salaryfrom employeeswhere salary=(select min(salary) from employees);# 列子查詢 ★案例：查詢所有是領(lǐng)導(dǎo)的員工姓名① 查詢所有員工的 manager_idselect manager_idfrom employees② 查詢姓名，employee_id屬于①列表的一個select last_namefrom employeeswhere employee_id in(select manager_idfrom employees);# 行子查詢（了解）案例：查詢員工編號最小并且工資最高的員工信息SELECT * FROM employeesWHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM employees);① 查詢最小的員工編號SELECT MIN(employee_id)FROM employees② 查詢最高工資SELECT MAX(salary)FROM employees③ 查詢員工信息SELECT *FROM employeesWHERE employee_id=(SELECT MIN(employee_id)FROM employees)AND salary=(SELECT MAX(salary)FROM employees);# 3.2 select后面 僅僅支持標(biāo)量子查詢#案例：查詢每個部門的員工個數(shù)SELECT d.*,(SELECT COUNT(*)FROM employees eWHERE e.department_id = d.`department_id`) 個數(shù)FROM departments d;# 3.3 from后面 將子查詢結(jié)果充當(dāng)一張表，要求必須起別名#案例：查詢每個部門的平均工資的工資等級① 查詢每個部門的平均工資SELECT AVG(salary),department_idFROM employeesGROUP BY department_idSELECT * FROM job_grades;② 連接①的結(jié)果集和job_grades表，篩選條件平均工資 between lowest_sal and highest_salSELECT ag_dep.*,g.`grade_level`FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;# 3.4 exists后面（相關(guān)子查詢） 語法： exists(完整的查詢語句) 結(jié)果： 1或0SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);#案例：查詢有員工的部門名#inSELECT department_nameFROM departments dWHERE d.`department_id` IN(SELECT department_idFROM employees)#existsSELECT department_nameFROM departments dWHERE EXISTS(SELECT *FROM employees eWHERE d.`department_id`=e.`department_id`);2.8 分頁查詢
# 1. 分頁查詢 select 查詢列表 from 表 [join type join 表2 on 連接條件 where 篩選條件 group by 分組字段 having 分組后的篩選 order by 排序的字段] limit [offset，] size;# 2. 注意 offset代表的是起始的條目索引，默認(rèn)從0開始 size代表的是顯示的條目數(shù)# 3. 公式 假如要顯示的頁數(shù)為page，每一頁條目數(shù)為size select 查詢列表 from 表 limit (page-1)*size,size;2.9 聯(lián)合查詢
# 1. 聯(lián)合查詢: 將多條查詢語句的結(jié)果合并成一個結(jié)果 查詢語句1 union 查詢語句2 union ...# 2. 應(yīng)用場景： 要查詢的結(jié)果來自于多個表，且多個表沒有直接的連接關(guān)系，但查詢的信息一致時(shí)# 3. 特點(diǎn)：★ 3.1 要求多條查詢語句的查詢列數(shù)是一致的！ 3.2 要求多條查詢語句的查詢的每一列的類型和順序最好一致 3.3 union關(guān)鍵字默認(rèn)去重，如果使用union all 可以包含重復(fù)項(xiàng)# 4.案例：查詢部門編號>90或郵箱包含a的員工信息 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;; SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90;2.10查詢語句總結(jié)
# 語法以及執(zhí)行順序 select 查詢列表 ⑦ from 表1 別名 ① 連接類型 join 表2 ② on 連接條件 ③ where 篩選 ④ group by 分組列表 ⑤ having 篩選 ⑥ order by排序列表 ⑧ limit 起始條目索引，條目數(shù); ⑨3. DML語言
數(shù)據(jù)操作語言：
插入：insert
修改：update
刪除：delete
3.1 插入語句
方式一：
# 語法 insert into 表名(列名) values(值1,...);# 1.插入的值的類型要與列的類型一致或兼容 INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'唐藝昕','女','1990-4-23','1898888888',NULL,2);# 2.不可以為null的列必須插入值?？梢詾閚ull的列如何插入值？ # 2.1： INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'唐藝昕','女','1990-4-23','1898888888',NULL,2); # 2.2： INSERT INTO beauty(id,NAME,sex,phone) VALUES(15,'娜扎','女','1388888888');# 3.列的順序是否可以調(diào)換 INSERT INTO beauty(NAME,sex,id,phone) VALUES('蔣欣','女',16,'110');# 4.列數(shù)和值的個數(shù)必須一致 INSERT INTO beauty(NAME,sex,id,phone) VALUES('關(guān)曉彤','女',17,'110');# 5.可以省略列名，默認(rèn)所有列，而且列的順序和表中列的順序一致 INSERT INTO beauty VALUES(18,'張飛','男',NULL,'119',NULL,NULL);?方式二：
# 語法： insert into 表名 set 列名=值,列名=值,...# 示例 INSERT INTO beauty SET id=19, NAME='劉濤', phone='999';?對比兩種方式:
# 1、方式一支持插入多行,方式二不支持 INSERT INTO beauty VALUES(23,'唐藝昕1','女','1990-4-23','1898888888',NULL,2) ,(24,'唐藝昕2','女','1990-4-23','1898888888',NULL,2) ,(25,'唐藝昕3','女','1990-4-23','1898888888',NULL,2);# 2、方式一支持子查詢，方式二不支持 INSERT INTO beauty(id,NAME,phone) SELECT 26,'宋茜','11809866';INSERT INTO beauty(id,NAME,phone) SELECT id,boyname,'1234567' FROM boys WHERE id<3;3.2 修改語句
語法：
# 1.修改單表的記錄★ update 表名 set 列=新值,列=新值,... where 篩選條件;# 2.修改多表的記錄【補(bǔ)充】 # sql92語法： 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：修改張無忌的女朋友的手機(jī)號為114 UPDATE boys bo INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id` SET b.`phone`='119',bo.`userCP`=1000 WHERE bo.`boyName`='張無忌'; #案例2：修改沒有男朋友的女神的男朋友編號都為2號 UPDATE boys bo RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id` SET b.`boyfriend_id`=2 WHERE bo.`id` IS NULL; SELECT * FROM boys;3.3 刪除語句
方式一：
# 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 篩選條件;?示例：
# 1.單表的刪除 # 案例：刪除手機(jī)號以9結(jié)尾的女神信息 DELETE FROM beauty WHERE phone LIKE '%9';# 2.多表的刪除 # 案例：刪除張無忌的女朋友的信息 DELETE b FROM beauty b INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id` WHERE bo.`boyName`='張無忌'; # 案例：刪除黃曉明的信息以及他女朋友的信息 DELETE b,bo FROM beauty b INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id` WHERE bo.`boyName`='黃曉明';?方式二：
# 語法： truncate table 表名;?示例：
#案例：將魅力值>100的男神信息刪除 TRUNCATE TABLE boys ;delete 對比 truncate?[面試題]
delete 可以加where 條件，truncate不能加
truncate刪除，效率高一丟丟
假如要刪除的表中有自增長列，如果用delete刪除后，再插入數(shù)據(jù)，自增長列的值從斷點(diǎn)開始，而truncate刪除后，再插入數(shù)據(jù)，自增長列的值從1開始。
truncate刪除沒有返回值，delete刪除有返回值
truncate刪除不能回滾，delete刪除可以回滾.
4. DDL語言
數(shù)據(jù)定義語言 –> 庫和表的管理
一、庫的管理
創(chuàng)建、修改、刪除
二、表的管理
創(chuàng)建、修改、刪除
創(chuàng)建： create
修改： alter
刪除： drop
4.1 庫的管理
#1、庫的創(chuàng)建 create database [if not exists]庫名; #案例：創(chuàng)建庫Books CREATE DATABASE IF NOT EXISTS books ;#2、庫的修改 RENAME DATABASE books TO 新庫名; #更改庫的字符集 ALTER DATABASE books CHARACTER SET gbk;#3、庫的刪除 DROP DATABASE IF EXISTS books;4.2 表的管理
# 1.表的創(chuàng)建 ★ # 語法： create table 表名(列名 列的類型【(長度) 約束】,列名 列的類型【(長度) 約束】,列名 列的類型【(長度) 約束】,...列名 列的類型【(長度) 約束】 ) # 案例：創(chuàng)建表Book CREATE TABLE book(id INT,#編號bName VARCHAR(20),#圖書名price DOUBLE,#價(jià)格authorId INT,#作者編號publishDate DATETIME#出版日期 ); DESC book; #案例：創(chuàng)建表author CREATE TABLE IF NOT EXISTS author(id INT,au_name VARCHAR(20),nation VARCHAR(10) ) DESC author;# 2.表的修改 # 語法 alter table 表名 add|drop|modify|change column 列名 【列類型 約束】; # ① 修改列名 ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME; # ② 修改列的類型或約束 ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP; # ③ 添加新列 ALTER TABLE author ADD COLUMN annual DOUBLE; # ④ 刪除列 ALTER TABLE book_author DROP COLUMN annual; # ⑤ 修改表名 ALTER TABLE author RENAME TO book_author;# 3.表的刪除 DROP TABLE IF EXISTS book_author; SHOW TABLES; # 通用的寫法： DROP DATABASE IF EXISTS 舊庫名; CREATE DATABASE 新庫名; DROP TABLE IF EXISTS 舊表名; CREATE TABLE 表名();# 4.表的復(fù)制 INSERT INTO author VALUES (1,'村上春樹','日本'), (2,'莫言','中國'), (3,'馮唐','中國'), (4,'金庸','中國');SELECT * FROM Author; SELECT * FROM copy2; # 1.僅僅復(fù)制表的結(jié)構(gòu) CREATE TABLE copy LIKE author; # 2.復(fù)制表的結(jié)構(gòu)+數(shù)據(jù) CREATE TABLE copy2 SELECT * FROM author;# 只復(fù)制部分?jǐn)?shù)據(jù) CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中國';# 僅僅復(fù)制某些字段 CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;4.3 數(shù)據(jù)類型
常見的數(shù)據(jù)類型
-
數(shù)值型：
-
整型
-
小數(shù)：
-
定點(diǎn)數(shù)
-
浮點(diǎn)數(shù)
-
-
字符型：
-
較短的文本：char、varchar
-
較長的文本：text、blob（較長的二進(jìn)制數(shù)據(jù)）
-
日期型
整型
-
分類：
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8 -
特點(diǎn)：
① 如果不設(shè)置無符號還是有符號，默認(rèn)是有符號，如果想設(shè)置無符號，需要添加unsigned關(guān)鍵字
② 如果插入的數(shù)值超出了整型的范圍,會報(bào)out of range異常，并且插入臨界值
③ 如果不設(shè)置長度，會有默認(rèn)的長度
長度代表了顯示的最大寬度，如果不夠會用0在左邊填充，但必須搭配zerofill使用！
小數(shù)
-
分類：
- 浮點(diǎn)型
float(M,D)
double(M,D) - 定點(diǎn)型
dec(M，D)
decimal(M,D) -
特點(diǎn)：
①
M：整數(shù)部位+小數(shù)部位
D：小數(shù)部位
如果超過范圍，則插入臨界值②
M和D都可以省略
如果是decimal，則M默認(rèn)為10，D默認(rèn)為0
如果是float和double，則會根據(jù)插入的數(shù)值的精度來決定精度③
定點(diǎn)型的精確度較高，如果要求插入數(shù)值的精度較高如貨幣運(yùn)算等則考慮使用
-
原則：
所選擇的類型越簡單越好，能保存數(shù)值的類型越小越好
#測試M和D CREATE TABLE tab_float(f1 FLOAT,f2 DOUBLE,f3 DECIMAL ); SELECT * FROM tab_float; DESC tab_float;INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523); INSERT INTO tab_float VALUES(123.456,123.456,123.456); INSERT INTO tab_float VALUES(123.4,123.4,123.4); INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);
字符型
-
較短的文本：
char varchar
-
其他：
binary和varbinary用于保存較短的二進(jìn)制
enum用于保存枚舉
set用于保存集合 -
較長的文本：
text
blob(較大的二進(jìn)制) -
char/varchar 對比
| char | char(M) | 最大的字符數(shù)，可以省略，默認(rèn)為1 | 固定長度的字符 | 比較耗費(fèi) | 高 |
| varchar | varchar(M) | 最大的字符數(shù)，不可以省略 | 可變長度的字符 | 比較節(jié)省 | 低 |
- enum和set不區(qū)分大小寫
日期型
- 分類：
date只保存日期
time 只保存時(shí)間
year只保存年
datetime保存日期+時(shí)間
timestamp保存日期+時(shí)間
| datetime | 8 | 1000-9999 | 不受 |
| timestamp | 4 | 1970-2038 | 受 |
4.4 常見約束
-
含義：一種限制，用于限制表中的數(shù)據(jù)，為了保證表中的數(shù)據(jù)的準(zhǔn)確和可靠性
-
分類：六大約束
-
NOT NULL：非空，用于保證該字段的值不能為空
比如姓名、學(xué)號等 Copy -
DEFAULT：默認(rèn)，用于保證該字段有默認(rèn)值
比如性別 Copy -
PRIMARY KEY：主鍵，用于保證該字段的值具有唯一性，并且非空
比如學(xué)號、員工編號等 Copy -
UNIQUE：唯一，用于保證該字段的值具有唯一性，可以為空
比如座位號 Copy -
CHECK：檢查約束【mysql中不支持】
比如年齡、性別 Copy -
FOREIGN KEY：外鍵，用于限制兩個表的關(guān)系，用于保證該字段的值必須來自于主表的關(guān)聯(lián)列的值
? 在從表添加外鍵約束，用于引用主表中某列的值
? 比如學(xué)生表的專業(yè)編號，員工表的部門編號，員工表的工種編號
?
-
-
添加約束的時(shí)機(jī)：
1.創(chuàng)建表時(shí) 2.修改表時(shí) Copy -
約束的添加分類：
- 列級約束：
六大約束語法上都支持，但外鍵約束沒有效果 - 表級約束：
除了非空、默認(rèn)，其他的都支持1 2 3 4 5 CREATE TABLE 表名(字段名 字段類型 列級約束,字段名 字段類型,表級約束 ) Copy
- 列級約束：
?
- 主鍵和唯一的對比【面試題】
| 主鍵 | √ | × | 至多有1個 | √，但不推薦 |
| 唯一 | √ | √ | 可以有多個 | √，但不推薦 |
-
外鍵
-
要求在從表設(shè)置外鍵關(guān)系
-
從表的外鍵列的類型和主表的關(guān)聯(lián)列的類型要求一致或兼容，名稱無要求
-
主表的關(guān)聯(lián)列必須是一個key（一般是主鍵或唯一）
-
插入數(shù)據(jù)時(shí)，先插入主表，再插入從表；刪除數(shù)據(jù)時(shí)，先刪除從表，再刪除主表
# 可以通過以下兩種方式來刪除主表的記錄 # 方式一：級聯(lián)刪除 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE; # 方式二：級聯(lián)置空 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
-
-
約束的增刪改
# 一、創(chuàng)建表時(shí)添加約束 # 1.添加列級約束 /* 語法： 直接在字段名和類型后面追加 約束類型即可。 只支持：默認(rèn)、非空、主鍵、唯一 */ USE students; DROP TABLE stuinfo; CREATE TABLE stuinfo(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 major(id)#外鍵(這里外鍵無效));CREATE TABLE major(id INT PRIMARY KEY,majorName VARCHAR(20) );#查看stuinfo中的所有索引，包括主鍵、外鍵、唯一 SHOW INDEX FROM stuinfo;# 2.添加表級約束 /* 語法：在各個字段的最下面【constraint 約束名】 約束類型(字段名) */ DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo(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 = '女'),#檢查CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外鍵);SHOW INDEX FROM stuinfo;# 通用的寫法：★ CREATE TABLE IF NOT EXISTS stuinfo(id INT PRIMARY KEY,stuname VARCHAR(20),sex CHAR(1),age INT DEFAULT 18,seat INT UNIQUE,majorid INT,CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id));# 二、修改表時(shí)添加約束 /* 1. 添加列級約束 alter table 表名 modify column 字段名 字段類型 新約束; 2. 添加表級約束 alter table 表名 add 【constraint 約束名】 約束類型(字段名) 【外鍵的引用】; */ DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo(id INT,stuname VARCHAR(20),gender CHAR(1),seat INT,age INT,majorid INT ) DESC stuinfo;# 1.添加非空約束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL; # 2.添加默認(rèn)約束 ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18; # 3.添加主鍵 # ①列級約束 ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY; # ②表級約束 ALTER TABLE stuinfo ADD PRIMARY KEY(id); # 4.添加唯一 # ①列級約束 ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE; # ②表級約束 ALTER TABLE stuinfo ADD UNIQUE(seat); # 5.添加外鍵 ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); # 三、修改表時(shí)刪除約束 # 1.刪除非空約束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL; # 2.刪除默認(rèn)約束 ALTER TABLE stuinfo MODIFY COLUMN age INT ; # 3.刪除主鍵 ALTER TABLE stuinfo DROP PRIMARY KEY; # 4.刪除唯一 ALTER TABLE stuinfo DROP INDEX seat; # 5.刪除外鍵 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;SHOW INDEX FROM stuinfo;
-
自增長列(標(biāo)識列）
-
含義：可以不用手動的插入值，系統(tǒng)提供默認(rèn)的序列值
-
特點(diǎn)：
1、標(biāo)識列必須和主鍵搭配嗎？不一定，但要求是一個key
2、一個表可以有幾個標(biāo)識列？至多一個！
3、標(biāo)識列的類型只能是數(shù)值型
4、標(biāo)識列可以通過?SET auto_increment_increment=3;設(shè)置步長
可以通過 手動插入值，設(shè)置起始值
-
4.5 表與表之間的關(guān)系
- 一對一
- 一對多
- 多對多
5. TCL語言
Transaction Control Language 事務(wù)控制語言?
5.1 事務(wù)的含義
事務(wù)：
一個或一組sql語句組成一個執(zhí)行單元，這個執(zhí)行單元要么全部執(zhí)行，要么全部不執(zhí)行。?
5.2 事務(wù)的特點(diǎn)
事務(wù)的特性：ACID [面試題]
- 原子性：一個事務(wù)不可再分割，要么都執(zhí)行要么都不執(zhí)行.
- 一致性：一個事務(wù)執(zhí)行會使數(shù)據(jù)從一個一致狀態(tài)切換到另外一個一致狀態(tài).
- 隔離性：一個事務(wù)的執(zhí)行不受其他事務(wù)的干擾.
- 持久性：一個事務(wù)一旦提交，則會永久的改變數(shù)據(jù)庫的數(shù)據(jù).
存儲引擎:
5.3 事務(wù)的使用
-
事務(wù)的創(chuàng)建
-
隱式事務(wù)：事務(wù)沒有明顯的開啟和結(jié)束的標(biāo)記
比如insert、update、delete語句? delete from 表 where id =1;
-
顯式事務(wù)：事務(wù)具有明顯的開啟和結(jié)束的標(biāo)記
前提：必須先設(shè)置自動提交功能為禁用set autocommit=0;
-
-
顯式事務(wù)的使用
① 開啟事務(wù)
set autocommit=0;
start transaction;#可以省略② 編寫一組邏輯sql語句
注意：sql語句支持的是insert、update、delete設(shè)置回滾點(diǎn)：
savepoint 回滾點(diǎn)名;③ 結(jié)束事務(wù)
提交：commit;
回滾：rollback;
回滾到指定的地方：rollback to 回滾點(diǎn)名; -
delete和truncate在事務(wù)使用時(shí)的區(qū)別：
- delete支持回滾
- truncate不支持
5.4 并發(fā)事務(wù)
-
事務(wù)的并發(fā)問題是如何發(fā)生的？
? 多個事務(wù) 同時(shí) 操作 同一個數(shù)據(jù)庫的相同數(shù)據(jù)時(shí)
-
并發(fā)問題都有哪些？
- 臟讀：一個事務(wù)讀取了其他事務(wù)還沒有提交的數(shù)據(jù)，讀到的是其他事務(wù)“更新”的數(shù)據(jù)
- 不可重復(fù)讀：一個事務(wù)多次讀取，結(jié)果不一樣
- 幻讀：一個事務(wù)讀取了其他事務(wù)還沒有提交的數(shù)據(jù)，只是讀到的是其他事務(wù)“插入”的數(shù)據(jù)
- 更新丟失：當(dāng)兩個或者多個事務(wù)選擇同一行，然后基于最初的選定的值更新該行時(shí)，由于每一個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生更新問題。最后的更新覆蓋了其他事務(wù)所做的更新。
-
如何解決并發(fā)問題
? 通過設(shè)置隔離級別來解決并發(fā)問題
-
事務(wù)的隔離級別
| read uncommitted: 讀未提交 | 最低級別，只能保證不讀取物理上損壞的數(shù)據(jù) | × | × | × |
| read committed: 讀已提交 | 語句級 | √ | × | × |
| repeatable read: 可重復(fù)讀 | 事務(wù)級 | √ | √ | × |
| serializable: 串行化 (每次讀都需獲得表級共享鎖，讀寫相互都會阻塞,性能低下) | 最高級別，事務(wù)級 | √ | √ | √ |
6. 其他
6.1 視圖
含義：虛擬表，和普通表一樣使用
mysql5.1版本出現(xiàn)的新特性，是通過表動態(tài)生成的數(shù)據(jù),?只保存了sql邏輯, 不保存查詢結(jié)果
-
應(yīng)用場景:
- 多個地方用到相同的查詢結(jié)果
- 該查詢結(jié)果使用的sql語句比較復(fù)雜
-
好處：
- 重用sql語句
- 簡化復(fù)雜的sql操作，不必知道它的查詢細(xì)節(jié)
- 保護(hù)數(shù)據(jù)，提高安全性（只提供對方需要的信息）
-
創(chuàng)建視圖
- 修改視圖 #方式一： /* create or replace view 視圖名 as 查詢語句; */ SELECT * FROM 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;
- 刪除視圖 # 語法：drop view 視圖名,視圖名,...; DROP VIEW emp_v1,emp_v2,myv3;
- 查看視圖 DESC myv3; SHOW CREATE VIEW myv3;
- 更新視圖
視圖可以增刪改，示例如下
CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email FROM employees; SELECT * FROM myv1; SELECT * FROM employees; #1.插入 INSERT INTO myv1 VALUES('張飛','zf@qq.com'); #2.修改 UPDATE myv1 SET last_name = '張無忌' WHERE last_name='張飛'; #3.刪除 DELETE FROM myv1 WHERE last_name = '張無忌';?具備以下特點(diǎn)的視圖不允許更新
# ① 包含以下關(guān)鍵字的sql語句：分組函數(shù)、distinct、group by、having、union或者union all CREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) m,department_id FROM employees GROUP BY department_id; SELECT * FROM myv1; #更新 UPDATE myv1 SET m=9000 WHERE department_id=10;# ② 常量視圖 CREATE OR REPLACE VIEW myv2 AS SELECT 'john' NAME; SELECT * FROM myv2; #更新 UPDATE myv2 SET NAME='lucy';# ③ Select中包含子查詢 CREATE OR REPLACE VIEW myv3 AS SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工資 FROM departments; #更新 SELECT * FROM myv3; UPDATE myv3 SET 最高工資=100000;# ④ join CREATE OR REPLACE VIEW myv4 AS SELECT last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; #更新 SELECT * FROM myv4; UPDATE myv4 SET last_name = '張飛' WHERE last_name='Whalen'; INSERT INTO myv4 VALUES('陳真','xxxx');# ⑤ from一個不能更新的視圖 CREATE OR REPLACE VIEW myv5 AS SELECT * FROM myv3; #更新 SELECT * FROM myv5; UPDATE myv5 SET 最高工資=10000 WHERE department_id=60;# ⑥ where子句的子查詢引用了from子句中的表 CREATE OR REPLACE VIEW myv6 AS SELECT last_name,email,salary FROM employees WHERE employee_id IN(SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL ); #更新 SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';?表和視圖的對比
| 視圖 | create view | 只是保存了sql邏輯 | 增刪改查，只是一般不能增刪改 |
| 表 | create table | 保存了數(shù)據(jù) | 增刪改查 |
6.2 變量
- 系統(tǒng)變量：
- 全局變量：
-
會話變量：
服務(wù)器為每一個連接的客戶端都提供了系統(tǒng)變量，作用域?yàn)楫?dāng)前的連接（會話）
- 自定義變量：
- 用戶變量
- 局部變量
說明：變量由系統(tǒng)定義，不是用戶定義，屬于服務(wù)器層面
注意：全局變量需要添加global關(guān)鍵字，會話變量需要添加session關(guān)鍵字，如果不寫，默認(rèn)會話級別
使用步驟：
示例：
# 1》全局變量 /* 作用域：針對于所有會話（連接）有效，但不能跨重啟 */ # ① 查看所有全局變量 SHOW GLOBAL VARIABLES; # ② 查看滿足條件的部分系統(tǒng)變量 SHOW GLOBAL VARIABLES LIKE '%char%'; # ③ 查看指定的系統(tǒng)變量的值 SELECT @@global.autocommit; # ④ 為某個系統(tǒng)變量賦值 SET @@global.autocommit=0; SET GLOBAL autocommit=0;#2》會話變量 /* 作用域：針對于當(dāng)前會話（連接）有效 */ # ① 查看所有會話變量 SHOW SESSION VARIABLES; # ② 查看滿足條件的部分會話變量 SHOW SESSION VARIABLES LIKE '%char%'; # ③ 查看指定的會話變量的值 SELECT @@autocommit; SELECT @@session.tx_isolation; # ④ 為某個會話變量賦值 SET @@session.tx_isolation='read-uncommitted'; SET SESSION tx_isolation='read-committed';說明：變量由用戶自定義，而不是系統(tǒng)提供的
使用步驟：
1、聲明
2、賦值
3、使用（查看、比較、運(yùn)算等）
示例：
# 1》用戶變量 /* 作用域：針對于當(dāng)前會話（連接）有效，作用域同于會話變量 */ # 賦值操作符：=或:= # ① 聲明并初始化 SET @變量名=值; SET @變量名:=值; SELECT @變量名:=值; # ② 賦值（更新變量的值） #方式一：SET @變量名=值;SET @變量名:=值;SELECT @變量名:=值; #方式二：SELECT 字段 INTO @變量名FROM 表; # ③ 使用（查看變量的值） SELECT @變量名;# 2》局部變量 /* 作用域：僅僅在定義它的begin end塊中有效 應(yīng)用在 begin end中的第一句話 */ # ① 聲明 DECLARE 變量名 類型; DECLARE 變量名 類型 【DEFAULT 值】 # ② 賦值（更新變量的值） #方式一：SET 局部變量名=值;SET 局部變量名:=值;SELECT 局部變量名:=值; #方式二：SELECT 字段 INTO 具備變量名FROM 表; # ③ 使用（查看變量的值） SELECT 局部變量名;# 案例：聲明兩個變量，求和并打印 # 用戶變量 SET @m=1; SET @n=1; SET @sum=@m+@n; SELECT @sum; # 局部變量 DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 1; DECLARE SUM INT; SET SUM=m+n; SELECT SUM;?用戶變量和局部變量的對比：
| 用戶變量 | 當(dāng)前會話 | 會話的任何地方 | 加@符號，不用指定類型 |
| 局部變量 | 定義它的BEGIN END中 | BEGIN END的第一句話 | 一般不用加@,需要指定類型 |
6.3 存儲過程和函數(shù)
- 存儲過程和函數(shù)：類似于java中的方法
- 好處：
1、提高代碼的重用性
2、簡化操作
含義：一組預(yù)先編譯好的SQL語句的集合，理解成批處理語句
1、提高代碼的重用性
2、簡化操作
3、減少了編譯次數(shù)并且減少了和數(shù)據(jù)庫服務(wù)器的連接次數(shù)，提高了效率
示例：
# 一、創(chuàng)建語法★ CREATE PROCEDURE 存儲過程名(參數(shù)列表) BEGIN存儲過程體（一組合法的SQL語句） END /* 1、參數(shù)列表包含三部分 參數(shù)模式 參數(shù)名 參數(shù)類型 舉例： in stuname varchar(20)參數(shù)模式： in：該參數(shù)可以作為輸入，也就是該參數(shù)需要調(diào)用方傳入值,in可以省略 out：該參數(shù)可以作為輸出，也就是該參數(shù)可以作為返回值 inout：該參數(shù)既可以作為輸入又可以作為輸出，也就是該參數(shù)既需要傳入值，又可以返回值2、如果存儲過程體僅僅只有一句話，begin end可以省略 存儲過程體中的每條sql語句的結(jié)尾要求必須加分號。 存儲過程的結(jié)尾可以使用 delimiter 重新設(shè)置 語法： delimiter 結(jié)束標(biāo)記 案例： delimiter $ */# 二、調(diào)用語法 CALL 存儲過程名(實(shí)參列表);#----------------------案例演示------------------------- # 1.空參列表 #案例：插入到admin表中五條記錄 SELECT * FROM admin; DELIMITER $ CREATE PROCEDURE myp1() BEGININSERT INTO admin(username,`password`) VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000'); END $ #調(diào)用 CALL myp1()$# 2.創(chuàng)建帶in模式參數(shù)的存儲過程 #案例1：創(chuàng)建存儲過程實(shí)現(xiàn) 根據(jù)女神名，查詢對應(yīng)的男神信息 CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name=beautyName; END $ #調(diào)用 CALL myp2('柳巖')$ #案例2 ：創(chuàng)建存儲過程實(shí)現(xiàn)，用戶是否登錄成功 CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20)) BEGINDECLARE result INT DEFAULT 0;#聲明并初始化SELECT COUNT(*) INTO result#賦值FROM adminWHERE admin.username = usernameAND admin.password = PASSWORD;SELECT IF(result>0,'成功','失敗');#使用 END $ #調(diào)用 CALL myp3('張飛','8888')$# 3.創(chuàng)建out 模式參數(shù)的存儲過程 #案例1：根據(jù)輸入的女神名，返回對應(yīng)的男神名 CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGINSELECT bo.boyname INTO boynameFROM boys boRIGHT JOINbeauty b ON b.boyfriend_id = bo.idWHERE b.name=beautyName ; END $ #調(diào)用 CALL myp7('小昭', @name)$ SELECT @name$ #案例2：根據(jù)輸入的女神名，返回對應(yīng)的男神名和魅力值 CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) BEGINSELECT boys.boyname ,boys.usercp INTO boyname,usercpFROM boys RIGHT JOINbeauty b ON b.boyfriend_id = boys.idWHERE b.name=beautyName ; END $ #調(diào)用 CALL myp7('小昭', @name, @cp)$ SELECT @name,@cp$# 4.創(chuàng)建帶inout模式參數(shù)的存儲過程 #案例1：傳入a和b兩個值，最終a和b都翻倍并返回 CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT) BEGINSET a=a*2;SET b=b*2; END $ #調(diào)用 SET @m=10$ SET @n=20$ CALL myp8(@m,@n)$ SELECT @m,@n$# 三、刪除存儲過程 #語法：drop procedure 存儲過程名 DROP PROCEDURE p1; DROP PROCEDURE p2,p3;#×# 四、查看存儲過程的信息 DESC myp2; # 不可以 SHOW CREATE PROCEDURE myp2;含義：一組預(yù)先編譯好的SQL語句的集合，理解成批處理語句
1、提高代碼的重用性
2、簡化操作
3、減少了編譯次數(shù)并且減少了和數(shù)據(jù)庫服務(wù)器的連接次數(shù)，提高了效率
區(qū)別：
存儲過程：可以有0個返回，也可以有多個返回，適合做批量插入、批量更新
函數(shù)：有且僅有1 個返回，適合做處理數(shù)據(jù)后返回一個結(jié)果
示例：
# 一、創(chuàng)建語法 CREATE FUNCTION 函數(shù)名(參數(shù)列表) RETURNS 返回類型 BEGIN函數(shù)體 END /* 注意： 1.參數(shù)列表 包含兩部分： 參數(shù)名 參數(shù)類型 2.函數(shù)體： 肯定會有return語句，如果沒有會報(bào)錯 如果return語句沒有放在函數(shù)體的最后也不報(bào)錯，但不建議return 值; 3.函數(shù)體中僅有一句話，則可以省略begin end 4.使用 delimiter語句設(shè)置結(jié)束標(biāo)記 */#二、調(diào)用語法 SELECT 函數(shù)名(參數(shù)列表)#-----------------案例演示------------------- # 1.無參有返回 #案例：返回公司的員工個數(shù) CREATE FUNCTION myf1() RETURNS INT BEGINDECLARE c INT DEFAULT 0;#定義局部變量SELECT COUNT(*) INTO c#賦值FROM employees;RETURN c; END $ SELECT myf1()$# 2.有參有返回 # 案例1：根據(jù)員工名，返回它的工資 CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE BEGINSET @sal=0;#定義用戶變量 SELECT salary INTO @sal #賦值FROM employeesWHERE last_name = empName;RETURN @sal; END $ SELECT myf2('k_ing') $ # 案例2：根據(jù)部門名，返回該部門的平均工資 CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE BEGINDECLARE sal DOUBLE ;SELECT AVG(salary) INTO salFROM employees eJOIN departments d ON e.department_id = d.department_idWHERE d.department_name=deptName;RETURN sal; END $ SELECT myf3('IT')$#三、查看函數(shù) SHOW CREATE FUNCTION myf3;#四、刪除函數(shù) DROP FUNCTION myf3;# 案例: 創(chuàng)建函數(shù)，實(shí)現(xiàn)傳入兩個float，返回二者之和 CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT BEGINDECLARE SUM FLOAT DEFAULT 0;SET SUM=num1+num2;RETURN SUM; END $ SELECT test_fun1(1,2)$6.4 流程控制結(jié)構(gòu)
順序、分支、循環(huán)
分類：
while、loop、repeat
循環(huán)控制：
iterate 【標(biāo)簽】 類似于 continue，繼續(xù)，結(jié)束本次循環(huán)，繼續(xù)下一次
leave 【標(biāo)簽】 類似于 break，跳出，結(jié)束當(dāng)前所在的循環(huán)
語法：
示例：
#1.沒有添加循環(huán)控制語句 #案例：批量插入，根據(jù)次數(shù)插入到admin表中多條記錄 DROP PROCEDURE pro_while1$ CREATE PROCEDURE pro_while1(IN insertCount INT) BEGINDECLARE i INT DEFAULT 1;WHILE i<=insertCount DOINSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');SET i=i+1;END WHILE; END $ CALL pro_while1(100)$#2.添加leave語句 #案例：批量插入，根據(jù)次數(shù)插入到admin表中多條記錄，如果次數(shù)>20則停止 TRUNCATE TABLE admin$ DROP PROCEDURE test_while1$ CREATE PROCEDURE test_while1(IN insertCount INT) BEGINDECLARE i INT DEFAULT 1;a:WHILE i<=insertCount DOINSERT INTO admin(username,`password`)VALUES(CONCAT('xiaohua',i),'0000');IF i>=20 THEN LEAVE a;END IF;SET i=i+1;END WHILE a; END $ CALL test_while1(100)$#3.添加iterate語句 #案例：批量插入，根據(jù)次數(shù)插入到admin表中多條記錄，只插入偶數(shù)次 TRUNCATE TABLE admin$ DROP PROCEDURE test_while1$ CREATE PROCEDURE test_while1(IN insertCount INT) BEGINDECLARE i INT DEFAULT 0;a:WHILE i<=insertCount DOSET i=i+1;IF MOD(i,2)!=0 THEN ITERATE a;END IF;INSERT INTO admin(username,`password`)VALUES(CONCAT('xiaohua',i),'0000'); END WHILE a; END $ CALL test_while1(100)$總結(jié)
以上是生活随笔為你收集整理的MySQL基础（尚硅谷笔记）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： RPA如何助力新加坡破解生产力难题
- 下一篇： 建筑行业VR安全体验，亲身感受事故危害，