目錄
一、###MySQL登錄和退出
二、###MySQL常用命令
三、###MySQL語法規范
四、###基礎查詢
五、###條件查詢
六、###排序查詢
七、###常見函式的學習
八、###分組查詢
九、###連接查詢
十、###子查詢
十一、###分頁查詢
十二、###聯合查詢
十三、###DML語言
十四、###DDL語言
十五、###常見的資料型別
十六、###常見的約束
十七、###標識列
十八、###TCL
十九、###視圖
二十、###變數
二十一、###存盤程序和函式
###1.MySQL登錄和退出
登錄：
mysql (-h主機名 -p埠號)-u用戶名 -p密碼
退出：
exit或者ctrl+c
###2.MySQL基礎命令
1.查看所有資料庫
show databases;
2.打開庫
use 庫名;
3.查看其他庫中的表
show tables from 庫名;
4.創建表
creat table 表名(
列名 列型別，
列名 列型別，
，，，
);
5.查看表結構
desc 表名;
###3.MySQL語法規范
1.不區分大小寫 建議關鍵字大寫，表名、列名小寫
2.每條命令用分號結尾
3.每條命令根據需要可以縮進 換行
4.注釋
單行注釋：#注釋文字
單行注釋: - -注釋文字
多行注釋：/* 注釋文字 */
###4.基礎查詢
#語法：
select 查詢的東西
from 表名;
#特點：
1.查詢的東西可以是：欄位、常量值、運算式、函式
2.查詢的結果是一個虛擬的表格，
#1.查詢表中的單個欄位
SELECT NAME FROM city;
#2.查詢表中的多個欄位
SELECT NAME,id FROM city;
#3.查詢表中所有的欄位
SELECT * FROM city;
#4.查詢常量值
SELECT 100;
SELECT 'beijing';
#5.查詢運算式
SELECT 100*98;
#6.查詢函式
SELECT version(); //得到的是查詢函式的回傳值
#7.為欄位起別名
①便于理解
②如果查詢的欄位有重名情況，使用別名可以區分開
#方式一：使用As
SELECT 100*98 As 結果;
SELECT 'beijing' AS 首都;
#方式二：使用空格
SELECT 'beijing' 首都;
#案例：如果別名中存在關鍵字或特殊符號，加雙引號
（單引號也可，但建議雙引號）
#8.去重
SELECT DISTINCT 'beijing' from city;
#9.+的作用
運算子：
例如：select 100+10;//兩個運算元都為數值型，則作加法運算
select '123'+90;//一個運算元是字符，另個一是數值，會
嘗試將字符型轉換為數值型 結果：213;
SELECT DISTINCT 'beijing' + 10;//若不能轉換，則將字符型
視作0 結果：10;
SELECT DISTINCT 'beijing' + null;//若有一方是null，則
結果為null
###5.條件查詢
#語法：
select
查詢串列
from
表名
where
篩選條件;
#分類
一、按條件運算式篩選
條件運算子> < = != <= >= 建議不等于用<>
案例：篩選id>50的城市
SELECT
*
FROM
city
WHERE
id>50
二、 按邏輯運算子篩選
邏輯運算子：
與：$$ 或：|| 非：!
建議使用： and or not
案例：查詢id 50到100的城市：
SELECT
*
FROM
city
WHERE
id>50 AND id<100
三、模糊查詢
like
between and
in
is null | is not null
#1.like
特點：
①一般和通配符搭配使用
通配符：
% 任意多個字符
_ 任意單個字符
如果查詢的中含有通配符，應轉譯 例如 \_
案例：查找ch開頭的國家
SELECT
*
FROM
country
WHERE
NAME LIKE 'ch%'
#2.between and
特點：
①包含臨界值
②調換順序不報錯，但是錯誤
案例：查找id在100到120的城市
SELECT
*
FROM
city
WHERE
id BETWEEN 100 AND 120
#3.in
特點：
①in串列的值必須一致或兼容
②不能使用通配符
案例：查詢國家代碼是CHN和NLD的城市名和ID
SELECT
id,
NAME
FROM
city
WHERE
countrycode IN ('CHN','NLD')
#4.is null
id = null 不成立
id isnull
###6.排序查詢
語法：
select 查詢串列
from 表
【where 篩選條件】
order by 排序串列 【asc|desc】
特點：
①asc是升序 desc是降序，如果不寫，默認升序
②支持單個欄位，也支持多個欄位、運算式、函式、別名
③order by子句一般放在查詢陳述句的最后，limit子句除外
案例1：查詢城市名字和id，按id降序
SELECT
id AS 序號,NAME AS 名字
FROM
city
ORDER BY
id DESC
案例2：按照城市名的位元組長度排序【按函式排序】
SELECT
*,LENGTH(NAME)
FROM
city
ORDER BY LENGTH(NAME) DESC
案例3：按別名排序
案例4：先按照名字長度排序，再按id排序【按多個欄位排序】
SELECT
*,LENGTH(NAME)
FROM
city
ORDER BY LENGTH(NAME) DESC,id ASC
###7.常見函式的學習
概念：類似java的方法
呼叫：select 函式名(引數串列) 【from 表】
分類：
1.單行函式
如 concat、length、ifnull等
2.分組函式
功能：做統計使用，又稱作統計函式、聚合函式、組函式
# 單行函式
#一.字符函式
#1.length 獲取引數值的位元組個數
#2.concat 拼接字串
#3.upper、lower 變大寫，變小寫
#4.substr 、substring 截取字串
注意：索引從1開始
#一個引數：截取從指定索引到結尾
SELECT
SUBSTR('李莫愁愛上了陸湛遠',7)
輸出陸湛遠
#兩個引數：截取從指定索引指定長度
SELECT
SUBSTR('李莫愁愛上了陸湛遠',1,3)
輸出李莫愁
#5.instr 回傳子串第一次出現的索引，如果找不到回傳0
SELECT INSTR('楊不悔愛上了殷六俠','殷六俠')
輸出7
#6.trim
SELECT TRIM(' 周福利 ')
輸出：周福利
SELECT TRIM('a' FROM 'aaaaa周aaaa福aaaaa利aaaaa')
輸出：周aaaa福aaaaa利
#7.lpad 用指定的字符實作左填充指定長度
SELECT LPAD('周福里',10,'*')
輸出：*******周福里
#8.rpad 用指定的字符實作右填充指定長度
#9.replace 替換
SELECT REPLACE('周芷若周芷若周芷若張無忌愛上了周芷若周芷若','周芷若','趙敏')
輸出：趙敏趙敏趙敏張無忌愛上了趙敏趙敏
#二、數學函式
#1.round 四舍五入
先轉化為絕對值，再四舍五入
SELECT ROUND(-1.51)
SELECT ROUND(-1.515,2)小數點后保存兩位
#2.ceil 向上取整
回傳>=該引數的最小整數
#3.floor 向下取整
回傳<=該引數的最大整數
#4.truncate 截斷
SELECT TRUNCATE(1.6999,1)
輸出：1.6
#5.mod 取余
mod(a,b) : a-a/b*b;
#三、日期函式
#1.now 回傳當前系統日期+時間
#2.curdate 回傳當前系統日期，不包含時間
#3.curtime 回傳當前時間，不含日期
#4.可以獲取指定部分，年、月、日、小時、分鐘、秒
select year(now()) 年;
select year('1998-1-1') 年;
輸出1998
select month(now());
9
select monthname(now());
December
#5.str_to_date:將字符轉換為指定型別的日期
select str_to_date('4-3 1992','%c-%d %Y')
輸出：1998-03-02
%Y 四位年份的年
%y 2位年份的年
%m 月份（01，02，...,11，12）
%c 月份（1，2，3.，，，，11，12）
%d 日（01，02.，，，）
%H 小時（24小時制）
%h 小時（12小時制）
%i 分鐘（00，01，02，....，59）
%s 秒（00，01，02，....，59）
#6.date_format將時間型別轉化為字串
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
輸出：2019年12月06日
#7.datediff（ ， ）
查找兩個日期相差的時間
#四、其他函式
version（）查看版本
database（）查看當前資料庫
user（）查看當前用戶
#五、流程控制函式
#1.if函式： if else的效果
SELECT IF(10>5,'大','小')
輸出：大
#2.case函式的使用一： switch case的效果
語法：
case 要判斷的欄位或運算式
when 常量1 then 要顯示的值1或陳述句1;
when 常量2 then 要顯示的值2或陳述句2;
....
else 要顯示的值n或陳述句n;
end
案例：
SELECT ID 序號, NAME 名字,
CASE id
WHEN 1 THEN 'test'
ELSE NAME
END AS 新姓名
FROM city
#3.case 函式的使用二： 類似于多重if
語法：
case
when 條件1 then 要顯示的值1或陳述句1;
when 條件2 then 要顯示的值2或陳述句2;
.，，
else 要顯示的值n或陳述句n
end
案例：
SELECT ID 序號, NAME 名字,
CASE
WHEN id<=10 THEN 'A'
WHEN id>10 AND id<=20 THEN 'B'
ELSE 'C'
END AS 新姓名
FROM city
#二、分組函式
/*
功能：用作統計使用，又稱聚合函式或統計函式或組函式
分類：sum求和、avg平均值、max最大值、min最小值、count計算個數
*/
#1、簡單的使用
SELECT SUM(id) FROM city
SELECT COUNT(id) FROM city
.....
#2.引數支持哪些型別
sum和avg只支持數值型別 不支持字符型 不支持日期型別
max和min值支持字符型、數字型、日期型
count任何型別都支持
#3.是否忽略null值
以上分組函式都忽略null值
#4.可以和distinct搭配
#5.count函式的詳細介紹
SELECT COUNT(1) FROM city//顯示所有欄位
SELECT COUNT(*) FROM city
#6. 和分組函式一同查詢的欄位有限制
和分組函式一同查詢的欄位要求是group by后的欄位
###8.分組查詢
語法：
select 分組函式，列（要求出現在group by的后面）
from 表
【where 篩選條件】
group by 分組串列
【order by 子句】
特點：
①分組查詢中的篩選條件可以分為分組前查詢和分組后查詢
②分組查詢支持單個欄位分組，也支持多個欄位分組（中間用逗號隔開，順序可變）
③也可以添加排序（放在最后）
案例1：查詢每個國家的最大城市id
SELECT MAX(id) 最大id,`CountryCode`
FROM city
GROUP BY countrycode
ORDER BY 最大id
案例2：查詢每個國家的城市數
SELECT COUNT(*),`CountryCode`
FROM city
GROUP BY countrycode
案例3：添加篩選條件
SELECT COUNT(*),`CountryCode`
FROM city
WHERE `CountryCode` ='chn'
GROUP BY countrycode
#添加復雜的篩選條件
案例：查詢每個城市數大于50的國家
SELECT COUNT(*) 城市數,`CountryCode`
FROM city
GROUP BY countrycode
HAVING 城市數>50
由于where只能跟在from后面，所以用了新的having
###9.連接查詢：
含義：又稱多表查詢，查詢的欄位來自不同表時使用
分類：
按年代分類：
sql192標準 僅僅支持內連接：
sql199標準【推薦】支持內+外（左外+右外）+交叉連接
按功能分類：
內連接：
等值連接
非等值連接
自連接
外連接
左外連接
右外連接
自連接
交叉連接
#一、sql92標準
#1.等值連接
#1.案例：查詢城市名對應的國家名
SELECT city.`Name`,country.`Name`
FROM city,country
WHERE city.`CountryCode`=country.`Code`
#2.為表起別名
#與給欄位起別名一至
#縮短表名長度，提高效率
#起完別名后則查詢欄位不能使用原來的表名限定
#3.兩個表的順序可以調換
#4.可以加篩選
#案例：
SELECT a.`Name`,b.`Name`
FROM city a,country b
WHERE a.`CountryCode`=b.`Code`
AND a.`ID`>10
#5.可以分組
#6.可以排序
#7.可以實作三表連接
#2.非等值連接
#3.自連接
使用別名進行區分
#二、sql99語法
/*
語法：
select 查詢串列
from 表1 別名 【連接型別】
join 表1 別名
on 連接條件
【where 篩選條件】
【group by】
【order】
*/
#連接型別關鍵字：
內連接：inner
外連接：
左外 left 【outer】
右外 right 【outer】
全外 full 【outer】
交叉連接：cross
#(一)內連接
select 查詢串列
from 表1 別名
innet join 表2 別名
on 連接條件
#案例：
SELECT DISTINCT `CountryCode`,b.`Name`
FROM `city` a
INNER JOIN `country` b
ON a.`CountryCode`=b.`Code`
#(二)外連接
/*
應用場景：用于查詢一個表中有，另一個表中沒有的記錄
特點：
1.外連接查詢結果為主表中的所有記錄
如果從表中沒有和他匹配的值，則顯示null
如果又和他匹配的值，則顯示匹配的值
外連接查詢的結果等于=內連接結果加主表中沒有的記錄
2.左外連接，left join 左邊是主表
右外連接，right join 右邊是主表
3.左外和右外互換表的位置也可以實作相同的效果
*/
#案例：
select b.name,bo.*
frome beauty b
left outer join boys bo
on b.id = bo.id
select b.name ,bo.*
frome boys bo
right outer join beauty b
on b.id = bo.id
#全外（msql不支持！）
select b.*,bo.*
from beauty b
full outer join boys b
on b.id = bo.id
#交叉連接
案例：
select b.*,bo.*
from beauty b
cross join boys bo
on b.id = bo.id
產生笛卡爾積
#sql92和sql99
功能：sql99功能較多
可讀性：sql99可讀性更高
###10.子查詢
/*
含義：
出現在其他陳述句中的select陳述句，稱為子查詢或內查詢
外部的查詢陳述句，成為主查詢或外查詢
分類：
按子查詢出現的位置：
select后面
僅僅標量子查詢
from后面
支持表子查詢
where或having后面?
標量子查詢（單行）
列子查詢（多行）
行子查詢
exists后面（相關子查詢）
表子查詢
按結果集的行列數不同：
標量子查詢（結果集只有一行一列）
列子查詢（結果集只有一列多行）
行子查詢（結果集有一行多列）
表子查詢（結果集一般為多行多列）
*/
#(一)where或having后面的子查詢
1.標量子查詢（單行子查詢）
2.列子查詢（多行子查詢）
3.行子查詢（多行多列）
#一、標量子查詢
#特點：
①子查詢都要放在小括號內
②子查詢一般放在條件的右側
③標量子查詢一般搭配著單行運算子使用
< > >= <=
列子查詢一般搭配多行運算子使用
in、any/some、all
④子查詢的執行要優先于主查詢
#非法使用標量子查詢
#案例：查詢誰的工資比Abel高？
#①查詢Abel的工資
select salary
from employee
where last_name = 'Able'
#②查詢員工的資訊，滿足salary>①的結果
select *
from employee
where salary > (
select salary
from employee
where last_name = 'Able'
)
#(二)列子查詢（多行子查詢）
關鍵字： IN any/some all
#案例：
select *
from employee
where asalry<all(
)
#(三)行子查詢（結果集一行多列或多行多列）
不常用
#二、select后面
/*
#里面僅支持標量子查詢
*/
#三、將子查詢放在from后面
#四、exists后面（相關子查詢）
/*
語法：
exists(完整的查詢陳述句)
結果：
0或1
*/
###11.分頁查詢
/*
應用場景：
要顯示的資料一頁顯示不全，需要分頁提交sql請求
語法：
limit offset,size;
offset:要顯示條目的起始索引（索引從0開始）
size：要顯示的條目個數
*/
#案例：
查詢前五條員工資訊
SELECT * FROM employees LIMIT 0,5;
#特點：
①limit陳述句放在查詢陳述句的最后
②公式
顯示的頁數是page，每頁條目數是size
select 查詢串列
from 表
limit (page -1)*size,size;
#應用場景：
要查詢的結果來自于多個表，多個表沒有直接關系，但查詢的資訊相同
#注意事項：
①要求多條查詢陳述句的查詢列數是一致的
②查詢的每列型別和順序是一致的
③union關鍵字默認是去重的，如果使用union all會保留所有項
###12.聯合查詢
/*
union 將多條查詢陳述句的結果合并成一個結果
語法：
查詢陳述句1
union
查詢陳述句2
union
...
*/
#案例1：查詢部門編號>90或郵箱中包含a的員工資訊
做法1：
select *
from employees
where email like '%a%' or department_id>90
做法2：
select *
from employees
where email like '%a%'
union
select *
from employees
where department_id >90
#案例2：查詢中國用戶男士的資訊和外國用戶男士的資訊
###13.DML語言
/*
資料操作語言：
插入：insert
修改：update
洗掉：delete
*/
#一、插入語言：
#方式一：
/*
#語法：
insert into 表名(列名,....) values(值1,...)
#注意事項：
①插入值的型別要與列的型別一致
②不可以為null的列必須插入值，可以為null的列，寫null或直接不寫列
③列的順序可以顛倒
④列和值的個數必須一致
⑤可以省略列名，默認是所有列，而且列的順序和表中的順序一致
*/
#方式二：
/*
語法：
insert into 表名
set 列名=值,列名=值,....
*/
#兩種方式pk
①方式1支持多行輸入，方式二不支持
②方式1支持子查詢，方式二不支持
#二、修改陳述句
/*
1.修改單表中的記錄：
語法：
update 表名
set 列= 新值,...
where 篩選條件;
案例：
update beauty
set sex = 'a'
where name = '蘇德間'
2.修改多表中的記錄
語法：
update 表1 別名,
inner join 表2 別名
on 連接條件
set 列= 值...
where 篩選條件
#案例1：修改張無忌的女朋友的手機號為112
UPDATE beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
SET b.`phone` = '112'
WHERE bo.`boyName` = '張無忌'
#案例2：修改沒有男朋友的boyfriendid為5
UPDATE beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
SET b.`boyfriend_id` = 5
WHERE bo.`id` IS NULL;
*/
#三、洗掉陳述句
/*
#方式一：delete
語法：
delete from 表名 where 篩選條件
多表洗掉
#案例一：洗掉手機號0結尾的
delete from beauty where phone like '%0'
#方式二：truncate
語法：truncate table 表名
#兩種方式pk
①delete可以添加where條件，另一個不能
②truncate洗掉效率高一丟丟
③如果使用delete洗掉所有后，再插入資料，自增長列從斷點開始
truncate洗掉后自增長列從1開始
④truncate洗掉沒有回傳值，delete洗掉有回傳值
⑤truncate洗掉不能回滾，delete可以回滾
*/
###14.DDL語言
/*
資料定義語言
庫和表的管理
#一、庫的管理
創建、修改、洗掉
#二、表的管理
創建、修改、洗掉
創建：create
修改：alter
洗掉：drop
*/
#一、庫的管理
#1.庫的創建
#語法：
create datebase 庫名;
#案例：
CREATE DATABASE books
#2.庫的修改（基本不修改，會導致資料丟失，不安全）
#更改庫的字符集
alter database books character set gbk
#3.庫的洗掉
drop database if exists books
#二、表的管理
/*
alter table 表名 add|drop|modify|change column
*/
#1.表的創建
create table 表名(
列名 列的型別【（長度） 約束】,
....
)
#2.表的修改
#①修改列名
alter table book change column publishdate pubDate datetime
#②修改列的型別或約束
alter table book modify column pubdate timestamp
#③添加新列
alter table author add column annual double
#④洗掉列
alter table author drop column annual
#⑤修改表名
alter table author rename to book_author
#3.表的洗掉
drop table book_author;
#通用的寫法
drop database if exists 舊庫名;
create database 新庫名;
drop table if exists 舊表名;
create table 新表名();
#4.表的復制
#1.僅僅賦值表的結構
CREATE TABLE copy LIKE book_author`copy`
#2.復制表的結構+資料（或部分資料）
CREATE TABLE copy2
SELECT * FROM book_author
【where】
只復制某些欄位
CREATE TABLE copy3
SELECT id,au_name
FROM book_author
###15.常見的資料型別
/*
數值型：
整形：
小數：
定點數
浮點數
字符型：
較短的文本：char、varchar
較長的文本：text、blob（較長的二進制資料）
日期型：
*/
#一、整形
/*
分類：
tinyint、smallint mediumint int/integer、bigint
特點：
①如果不設定無符號，則默認有符號
②超出范圍默認插入臨界值，且會報例外
③如果不設定長度，會有默認長度
長度代表了顯示的最大寬度，如果不夠會用0填充，需搭配zerofill使用
*/
#1.如何設定有符號和無符號
unsigned 約束關鍵字
#二、小數
1.浮點型
float(M,D)
double(M,D)
2.定點型
dec(M,D)
decimal(M,D)
特點：
①M:整數部位+小數部位
D:小數部位
如果超過范圍，則插入臨界值
②M和D可以省略
如果是decimal，則M默認是10，D默認是0
如果是float和double則會根據插入數值的精度確認精度
③定點型的精確度較高，如果對插入數值的精度要求較高可以使用，如貨幣
#原則：
所選擇的型別越簡單越好，能保存數值的型別越小越好
#三、字符型
/*
較短的文本：
char
varchar
較長的文本：
text
blob
*/
特點：
#char(M)
#varchar(M)
char和varchar區別：carchar可變，char不可變
varchar省空間 但是char效率較高
例如性別，可以用char
char的m可以省略，默認1 ，varchar不能省略
#enum型別： 列舉型別
案例：
create table season(
s1 ENUM('春','夏','秋','冬')
)
insert into season values ('春')
#set型別：
create table tab_set(
s1 set('a','b','c','d')
)
insert into tab_set values ('a')
insert into tab_set values ('a,b')
#binary和varbinary用于保存較短的二進制
#四、日期型
分類：
date 只保存日期
time 只保存時間
year 只保存年
datetime 日期+時間 受時區英雄
timestamp 日期+時間 不受時區影響
###16.常見的約束
/*
含義：一種限制，用于限制表中的資料為了保證表中資料的準確性和可靠性
分類： 六大約束
NOT NULL：非空，用于保證該欄位的值不能為空
比如姓名、學號等
DEFAULT:默認，用于保證該欄位有默認的值
比如性別
PRIMATY KEY:主鍵，用于保證該欄位的值具備唯一性并且非空
比如學號、員工編號等
UNIQUE:唯一，用于保證該欄位的值具有唯一性，可以為空
比如：座位號
CHECK:檢查約束【mysql不支持】
比如：年齡、性別
FOREIGN KEY:外鍵，用于限制兩個表的關系，用于保證該欄位的值來自于主表的關聯列的值
在從表添加外鍵約束，用于參考主表中某列的值
比如：學生表的專業編號，員工表的部門編號
添加約束的時機：
1.創建表時
2.修改表時
約束的添加分類：
列級約束
六大約束語法上都支持，但外鍵約束沒有效果
表級約束
除了非空、默認，其他的都支持
*/
#一、創建表時添加約束
#1.添加列級約束
語法：
直接在欄位名和型別名后面追加約束
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主鍵
stuname VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender = '女'),#檢查（無效）
seat INT UNIQUE , #唯一
age INT DEFAULT 18 #默認
)
#2.添加表級約束
語法：
在各個欄位的最下面
【CONSTRAINT 約束名】 約束型別（欄位名）
取名可以省略（中括號內的）
CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20),
gender CHAR(1) ,
seat INT ,
age INT ,
magorid INT,
CONSTRAINT pk PRIMARY KEY(ID),
CONSTRAINT ck UNIQUE(seat),
CONSTRAINT fk_s_m FOREIGN KEY(magorid) REFERENCES magor(`m_id`)
)
#通用寫法：
外鍵約束用表級，其他都用列級約束
#主鍵和唯一的對比：
保證唯一性 是否允許為空 一個表中可以有多少個 是否允許組合
主鍵 √ X 至多有一個 √(組合主鍵，不推薦)
唯一 √ √ 可以有多個 √(不推薦)
#外鍵：
1.要求在從表設定外鍵的關系
2.從表的外鍵列的型別和主表的關聯列的型別一致
3.主表的關聯列必須是一個key（一般是主鍵或唯一鍵）
4.插入資料時，先插入主表，再插入從表
洗掉資料時，先洗掉從表，再洗掉主表
#二、修改表時添加約束
/*
1.添加列級約束：
alter table 表名 modify column 欄位名 欄位型別 新約束
2.添加表級約束：
alter table 表名 add 【constraint 約束名】 約束型別(欄位名)
*/
#1.添加非空、默認約束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOTNULL
#2.添加主鍵、唯一
#①列級約束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
#②表級約束
ALTER TABLE stuinfo ADD PRIMARY KEY(id)
#3.添加外鍵
ALTER TABLE stuinfo ADD CONSTRAINT fk_s_m FOREIGN KEY(majorid) REFRENCES major(id)
#三、修改表時洗掉約束
基本與添加一致
#洗掉唯一
alter table s drop index 約束名
#洗掉外鍵
alter table s drop foreign key 約束名
###17.標識列
/*
又稱為自增長列
含義：可以不用手動的插入值，系統提供默認的序列值
特點：
①標識列不一定和主鍵搭配，但一定要和key搭配
②一個表中最多有一個標識列
③標識列的型別只能是數值型，一般是int
④標識列可以通過
set auto_increment_increment= 3
設定步長
可以通過設定第一個值的來決定開始數值
*/
#一、創建表時設定標識列
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
#二、修改表時設定標識列
alter table s modify colunm id primary key auto_increment
###18.TCL
/*
Transaction Control Language 事務控制語言
sql支持insert,update,delete
事務：
一個或一組sql陳述句組成一個執行單元，這個執行單元要么全部執行，要么全部不執行
案例： 實時轉賬
張三非：1000
郭襄： 1000
update 表 set 張三非的余額= 500 whrer name = ‘張三非’
update 表 set 郭襄的余額 = 1500 where name = ‘郭襄’
#事務所具備的屬性/特點：（ACID）
1.原子性（atomiciy）
事務是一個不可分割的作業單位，要么都發生，要么都不發生
2.一致性（consistency）
事務必須使資料庫從一個一致性狀態變換到另一個一致性的狀態
3.隔離性（isolation）
事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務的操作及
使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能相互干擾
4.持久性（durability）
指一個事務一旦被提交，他對資料庫中資料的改變是永久性的
#事務的創建
#隱式事務：事務沒有明顯的開啟和結束的標記
比如：insert update delete
#顯式事務：事務具有明顯的開啟和結束的標記
前提：必須先設定自動提交功能禁用
set autocommit = 0;
步驟1：開啟事務
set autocommit = 0;
start transaction;
步驟2：撰寫事務中的sql陳述句(select insert update delete)
陳述句1;
陳述句2;
...
步驟3：結束事務
commit;提交事務
rollback;回滾事務
#并發事務
1.事務的并發問題是如何發生的？
多個事務同時操作同一個資料庫的相同資料時
2.并發的問題有那些？
臟讀：一個事務讀取了其他事務還沒提交的資料，讀到的是其他事務‘更新的事務’
不可重復讀：一個事務多次讀取，結果不一樣
幻讀：一個事務讀取了其他事務沒有提交的事務，讀到了其他事務‘插入的事務’
3.如何解決并發問題？
設定隔離級別
#事務的隔離級別：
臟讀 幻讀 不可重復度
read uncommitted(讀未提交)： √ √ √
read committed(讀已提交): x √ √
repeatable read(可重復讀): x x √
serializable(串行化)： x x x
mysql中默認第三個隔離級別 repeatable read
orical中默認第二個隔離級別 read commited
查看隔離級別：
select @@tx_isolation;
設定隔離級別：
set session|global transaction isolation level 隔離級別;
savepoint 節點名; 設定保存點
配合rollback to 節點名; 使用，
*/
###19.視圖
/*
含義：虛擬表，他的資料來自表，通過執行時動態生成
意義：①重用sql陳述句
②簡化復雜的sql陳述句
③保護資料，提高安全性
#視圖與表
創建語法及關鍵字 是否實際占用物理空間 使用
視圖 create table 占用較小只是保存了sql邏輯 一般只是用查詢
表 create view 是，保存了實際資料 增刪改查
*/
#案例：查詢J開頭的員工的姓名和部門名
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`
WHERE a.`first_name` LIKE 'J%'
建立視圖：
CREATE VIEW v1
AS
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`
從視圖中查詢：
SELECT * FROM v1
WHERE first_name LIKE 'J%'
#一、創建視圖
#語法：
create view 視圖名
as
查詢陳述句;
#
#二、視圖的修改
#方式一：
create or replace view 視圖名
as
查詢陳述句;
#方式二：
alter view 視圖名
as
查詢陳述句;
#三、洗掉視圖
#語法：
drop view 視圖名,視圖名,....;
DROP VIEW v1,v2
#四、查看視圖
desc 視圖
show create view 視圖
#五、視圖的更新
#1.插入
insert into 視圖 values()
與表的插入類似
#2.修改
與表的更新類似
#3.修改
DELETE FROM v1
WHERE last_name = '展飛'
#具備以下特點的視圖是不允許更新的
1.包含 group by,distinct,分組函式,having,union ,union all
2.常量視圖
3.select中包含子查詢
4.join
5.from一個不能更新的視圖
6.where子句的子查詢參考了from子句中的表
###20.變數
/*
系統變數：
全域變數
會話變數
自定義變數
用戶變數
區域變數
*/
#一、系統變數
#說明：變數由系統提供，不是用戶定義，屬于服務器層面
#使用的語法：
1.查看所有的系統變數
show global|【session】 variables;
2.查看滿足條件的部分系統變數
show global|【session】 variables like '%char%'
3.查看指定的某個系統變數的值
select @@ gloable|【session】.系統變數名; //跨連接有效
4.為某個系統變數復制
set gloabal|【session】 系統變數名 = 值;
注意：如果是全域級別，需要加global
如果是會話級別，則需要加session
如果不寫，默認session
#1、全域變數
#作用域：服務器每次啟動將為所有的全域變數賦初始值，修改針對于所有的連接都有效，
但是不能跨重啟(重啟會初始化)
#2、會話變數
#作用域：僅僅針對當前會話(連接)有效
#二、自定義變數
/*
說明：變數由用戶定義的，不是系統的
#使用步驟：
宣告
賦值
使用（查看、比較、運算）
*/
#1.用戶變數
#作用域：針對有當前會話（連接）有效的，同于會話變數的作用域
#應用：可以放在begin end任何地方
#①宣告并初始化
賦值的運算子：= 或 :=
set @用戶變數名 = 值;
set @用戶變數名:=值;
select @用戶變數名 :=值;
#②賦值(更新用戶變數的值)
方式一：
set @用戶變數名 = 值;
set @用戶變數名:=值;
select @用戶變數名 :=值;
方式二：通過select 欄位 into @變數名
select 欄位 into @變數名
from 表;
#③使用(查看用戶變數的值)
select @用戶變數
#2.區域變數
#作用域：僅僅再定義在他的begin end 中有效
#應用：應用在begin end中的第一句話
#①宣告
declear 變數名 型別;
declear 變數名 型別 default 值;
#②賦值
方式一：
set 區域變數名 = 值;
set 區域變數名:=值;
select @區域變數名 :=值;
方式二：通過select 欄位 into 變數名
select 欄位 into 變數名
from 表;
#③使用
select 區域變數名;
#3.對比用戶變數和區域變數
作用域 定義和使用的位置 語法
用戶變數 當前會話 會話中的任何位置 必須加@符號，不用限定型別
區域變數 begin end中 只能在begin end中，且為第一句 一般不用加@符號，需要限定型別
###21.存盤程序和函式
/*
#類似于java中的方法
#好處：
1.提高代碼的重用性
2.簡化操作
*/
#存盤程序
#含義：一組預先編譯好的sql陳述句的集合，
#好處：
1.提高代碼的重用性
2.簡化操作
3.減少了編譯次數和資料庫服務器的連接次數，提高效率
#一、創建語法
#語法：
create procedure 儲存程序名(引數串列)
begin
存盤程序體(一組合法的sql陳述句)
end
注意：
1.引數串列包含三部分
引數模式 引數名 引數型別
舉例 IN stuname VARCHAR(20)
引數模式：
IN：該引數可以作為輸入，也就是說該引數需掉用方法傳入值
OUT：該引數可以作為輸出，也就是該引數可以作為回傳值
INOUT：該引數既可以作為輸入，又可以作為輸出，即該引數既可以傳入值，也可以回傳值
2.如果存盤程序體僅僅只有一句話，begin end 可以省略
存盤程序體中的每條sql陳述句的結尾必須加分號
存盤程序的結尾可以使用delimiter重新設定
語法： delimiter 結束標記
案例: delimiter $
#二、呼叫語法
#語法：
call 存盤程序名(實參串列);
#1.空參串列
#案例：插入到admin表中五條記錄
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin (username,PASSWORD)
VALUES('j2','0000'),('j3','0000'),('j4','0000'),('j5','0000'),('j1','0000');
END $
呼叫：
CALL myp1()$
#2.創建帶in模式引數的存盤程序
#案例1：創建存盤程序實作 根據女神名，查詢對應的男神資訊
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END $
call myp2('關曉彤')$
#案例2：創建存盤程序實作，用戶是否登錄成功
DELIMITER $
CREATE PROCEDURE myp3 (IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result
FROM admin a
WHERE a.username = username
AND a.password = PASSWORD;
SELECT IF(result>0,'成功','失敗');
END $
#3、創建帶out模式的存盤程序
#案例1：根據女神名，回傳對應的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT bName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO bName
FROM boys bo
INNER JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END$
呼叫：
DELIMITER $
CALL myp5('周芷若',@bName)$
SELECT @bName$
#4.創建帶inout模式引數的存盤程序
#案例1：傳入a和b兩個值，最終a和b都翻倍并回傳
#二、洗掉存盤程序
語法：drop procedure 存盤程序
#三、查看存盤程序的資訊
show create procedure myp2;
#函式
#好處：
1.提高代碼的重用性
2.簡化操作
3.減少了編譯次數和資料庫服務器的連接次數，提高效率
#區別：
存盤程序：可以有0個回傳，也可以有多個回傳，適合做批量插入、批量更新
函式：有且僅有一個回傳，適合做處理資料后回傳一個結果
#一、創建語法
create function 函式名(引數串列) returns 回傳型別
begin
函式體
end
/*
注意：
1.引數串列 包含兩部分：
引數名 引數型別
2.函式體：肯定會有return陳述句，如果沒有會報錯
如果return陳述句沒有放在函式體的最后也不會報錯，但是不建議
3.函式體只有一句話，可以省略begin end
4.使用delimiter陳述句作為設定結束標記
*/
#二、呼叫語法
select 函式名(引數串列)
#1.無參有回傳
#案例1：回傳公司的員工個數
CREATE FUNCTION myf1()RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM employees; RETURN c;
END$
SELECT myf1()
#2.有參有回傳
#案例：根據員工名，回傳他的工資
#三、查看函式
show create function 函式名
#四、洗掉函式
drop function 函式名
###流程控制結構
/*
順序結構：程式從上往下依次執行
分支結構：程式可以從兩潭訓多條路徑中選擇一條去執行
回圈結構：在滿足一定條件的基礎上，重復執行一段代碼
*/
#一、分支結構
#1.if函式
功能：實作簡單的雙分支
語法：select if(運算式1,運算式2,運算式3)
執行順序：
如果運算式一成立，則if函式回傳運算式2的值，否則回傳運算式3的值
應用：任何地方
#2.case結構
情況1：類似于java中的switch陳述句，一般用于實作等值判斷
語法：
case 變數|運算式|欄位
when 要判斷的值1 then 回傳的值1或陳述句1;
when 要判斷的值2 then 回傳的值2或陳述句2;
...
else 要回傳的值n或陳述句n;
end case;
情況2：類似于java中的多重if陳述句，一般用于實作區間判斷
語法：
case
when 要判斷的條件1 then 回傳的值1或陳述句1;
when 要判斷的條件2 then 回傳的值2或陳述句2 ;
...
else 要回傳的值n或陳述句n;
end case;
特點：
①既可以作為運算式，嵌套在其他陳述句中使用，begin end中或begin end后面
可以作為獨立的陳述句去使用，只能放在begin end中
②如果when中的值滿足或條件成立，則執行對應的then后面的陳述句，并結束case
如果都不滿足，則執行else中的陳述句或值
③else可以省略，如果else省略并且所有when都不滿足時，則回傳null
#案例：創建存盤程序，根據傳入成績，來顯示等級，如果傳入90-100 A 80-90B 60-80 C 其他D
CREATE PROCEDURE test_case(IN score INT)
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$
#3.if結構
功能：實作多重分支
語法：
if 條件一 then 陳述句1;
elseif 條件2 then 陳述句2;
...
【else 陳述句n;】
end if;
應用：
在begin end中
#案例：創建存盤程序，根據傳入成績，來顯示等級，如果傳入90-100 A 80-90B 60-80 C 其他D
#二、回圈結構
分類：
while、loop、repeat
回圈控制：
iterate類似于 continue
leave 類似于break
#1.while
語法：
【標簽:】while 回圈條件 do
回圈體;
end while【標簽】;
#2.loop
語法：
【標簽：】loop
回圈體;
end loop 【標簽】;
可以用來模擬簡單的死回圈
#3.repeat
語法：
【標簽:】repeat
回圈體;
until 回圈結束的條件
end repeat 【標簽】;
#沒有添加回圈空陳述句
#案例：批量插入，根據次數插入到admin表中多條記錄
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE ('rose','6666');
SET i = i +1;
END WHILE;
END$
CALL pro_while1(100)$
#添加一個leave陳述句
#案例：和上一個相同，如果次數》20則停止
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE (CONCAT('rose',i),'6666');
IF i>=20 THEN LEAVE a;
END IF;
SET i = i +1;
END WHILE a;
END$
CALL pro_while1(100)$
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/87427.html
標籤：MySQL
上一篇：FIND_IN_SET()函式