目錄 一、###MySQL登錄和退出 二、###MySQL常用命令 三、###MySQL語法規範 四、###基礎查詢 五、###條件查詢 六、###排序查詢 七、###常見函數的學習 八、###分組查詢 九、###連接查詢 十、###子查詢 十一、###分頁查詢 十二、###聯合查詢 十三、###D ...
目錄
一、###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)$
