數(shù)據(jù)庫相關(guān)概念

1、DB：數(shù)據(jù)庫贸辈，保存一組有組織的數(shù)據(jù)的容器释树。
?2、DBMS：數(shù)據(jù)庫管理系統(tǒng)擎淤，又稱為數(shù)據(jù)庫軟件（產(chǎn)品）奢啥，用于管理DB中的數(shù)據(jù)，常見的DBMS為MySql,Oracle,SqlServer,DB2嘴拢。桩盲。。
?3席吴、SQL:結(jié)構(gòu)化查詢語言赌结，用于和DBMS通信的語言。

MySQL產(chǎn)品的介紹和安裝

MySQL服務(wù)的啟動和停止

? 方式一：計算機(jī)——右擊管理——服務(wù)
? 方式二：通過管理員身份運(yùn)行
? net start 服務(wù)名（啟動服務(wù)）
? net stop 服務(wù)名（停止服務(wù)）

MySQL服務(wù)的登錄和退出

方式一：通過mysql自帶的客戶端
只限于root用戶

方式二：通過windows自帶的客戶端
登錄：
mysql 【-h主機(jī)名 -P端口號 】-u用戶名 -p密碼
例如： mysql -u root -p123456
退出：
exit或ctrl+C

MySQL的常見命令

1.查看當(dāng)前所有的數(shù)據(jù)庫
show databases;

2.打開指定的庫
use 庫名;

3.查看當(dāng)前庫的所有表
show tables;

4.查看其它庫的所有表
show tables from 庫名;

5.創(chuàng)建表
create table 表名(
    列名 列類型孝冒，
    ...
);

6.查看表結(jié)構(gòu)
desc 表名;

7.查看服務(wù)器的版本
方式一：登錄到mysql服務(wù)端
select version();

方式二：沒有登錄到mysql服務(wù)端
mysql --version
或
mysql --V

MySQL的語法規(guī)范

? 1.不區(qū)分大小寫,但建議關(guān)鍵字大寫姑曙，表名、列名小寫迈倍。
? 2.每條命令最好用分號結(jié)尾伤靠。
? 3.每條命令根據(jù)需要，可以進(jìn)行縮進(jìn)或換行。

單行注釋：#注釋文字    -- 注釋文字 (注意中間要帶有一個空格才能生效)
多行注釋：/* 注釋文字 */

SQL的語言分類

1.DQL（Data Query Language)：數(shù)據(jù)查詢語言
select
2.DML (Data Manipulate Language): 數(shù)據(jù)操作語言
insert 宴合、update焕梅、delete
3.DDL（Data Define Languge)：數(shù)據(jù)定義語言
create、drop卦洽、alter
4.TCL （Transaction Control Language)：事務(wù)控制語言
commit贞言、rollback、savepoint
5.DCL（Data Control Language)：數(shù)據(jù)控制語言
grant阀蒂、revoke

DQL語言的學(xué)習(xí)

基礎(chǔ)查詢

#語法：
查詢表的所有數(shù)據(jù):  SELECT * FROM 表名;  
查詢表中指定字段的值: SELECT 字段1,字段2...字段n FROM 表名;

特點(diǎn)：
①通過select查詢完的結(jié)果 ,是一個虛擬的表格该窗，不是真實(shí)存在。
②要查詢的東西可以是常量值蚤霞、可以是表達(dá)式酗失、可以是字段、可以是函數(shù)昧绣。

使用別名

#給查詢結(jié)果中的字段使用別名
字段名 as 別名
或者
字段名 "別名"
SELECT name as "花名" FROM  flower;

注意: as可以省略不寫规肴，別名中沒有特殊字符，雙引號也可以省略不寫夜畴。

去除重復(fù)

SELECT DISTINCT 字段1,字段2...字段n  FROM 表名;

注意：去除重復(fù)的規(guī)則是按照行進(jìn)行去除的,多行數(shù)據(jù)完全相同取其一,DISTINCT必須放在要查詢字段的開頭拖刃。

+號

#僅僅只有一個功能:運(yùn)算符

#兩個操作數(shù)為數(shù)值型，則做加法運(yùn)算
SELECT 100+90;  -- 190

#只要其中一方為字符型贪绘，試圖將字符型數(shù)值轉(zhuǎn)換成數(shù)值型 
#1.如果轉(zhuǎn)換成功,則繼續(xù)做加法運(yùn)算
#2.如果轉(zhuǎn)換失敗,則將字符型數(shù)值轉(zhuǎn)換為0
SELECT "100"+90; -- 190
SELECT "Tom"+90; -- 90

#只要其中一方為null,則結(jié)果肯定為null
SELECT null+90; -- null

concat實(shí)現(xiàn)拼接操作

#使用方法 
CONCAT(str1,str2...)  

SELECT CONCAT(1,3,4);  測試  -- 一個數(shù)字參數(shù)被轉(zhuǎn)化為與之相等的二進(jìn)制字符串格式
SELECT CONCAT(id,',',name) "編號,花名" from flower;

注意:返回結(jié)果為連接參數(shù)產(chǎn)生的字符串兑牡。如有任何一個參數(shù)為NULL ，則返回值為 NULL税灌。

條件查詢

條件查詢：根據(jù)條件過濾原始表的數(shù)據(jù)发绢，查詢到想要的數(shù)據(jù)。

語法：
select 要查詢的字段|表達(dá)式|常量值|函數(shù) from 表名 where 篩選條件;

一垄琐、條件表達(dá)式

條件運(yùn)算符：> < >= <= = <> !=

二边酒、邏輯表達(dá)式

邏輯運(yùn)算符：
and（&&）:兩個條件如果同時成立，結(jié)果為true狸窘，否則為false墩朦。
or (||)：兩個條件只要有一個成立，結(jié)果為true翻擒，否則為false氓涣。
not (!)：如果條件成立，則not后為false陋气，否則為true劳吠。

?

三、模糊查詢

%： 代替0個或多個任意字符巩趁。
_ ：代替1個任意字符痒玩。

SELECT * From flower where name like '_a%';
#轉(zhuǎn)義關(guān)鍵詞  ESCAPE
SELECT * From flower where name like '_\_%';
SELECT * From flower where name like '_$_%' ESCAPE '$';

四、關(guān)鍵字 between and

#包含兩頭的數(shù)據(jù) [ , ]
SELECT * From emp where sal >=200 and sal<=300;
#等價與
SELECT * From emp where sal between 200 and 300;

五、關(guān)鍵字 in

含義:判斷某字段的值是否屬于in列表中的某一項蠢古。

#格式
in (目標(biāo)值1奴曙，目標(biāo)值2，...) -- in 中的內(nèi)容只能為一個字段的值,目標(biāo)值之間必須使用逗號分隔,并且括在括號中草讶。
SELECT * From emp where job in ('Salesman','manager','analyst'); 

六洽糟、關(guān)鍵字 is null , is not null

#格式 
SELECT * FROM 表名 WHERE 字段名 is null; -- 字段值為Null
SELECT * FROM 表名 WHERE 字段名 is not null; -- 字段值不為Null
SELECT * FROM emp WHERE comm is not null; -- 字段值不為Null

注意: 不能使用=,<>來判斷NULL值。

七堕战、安全等于 <=>

SELECT * FROM flower WHERE production <=> null;

is null : 僅僅可以判斷NULL值坤溃，可讀性高,建議使用。

<=> : 既可以判斷NULL值,又可以判斷普通數(shù)值嘱丢，可讀性較低薪介。

排序查詢

#語法
#單字段排序
select 要查詢的東西 from 表名 where 篩選條件 order by 排序的字段|表達(dá)式|函數(shù)|別名 [asc|desc]

#多字段排序
select 要查詢的東西 from 表名 where 篩選條件 order by 字段名1 [asc|desc],字段名2 [asc|desc]...  

注意:

1.asc代表的是升序,desc代表的是降序,如果不寫,默認(rèn)為升序。

2.order by 子句中可以支持單個字段,多個字段,表達(dá)式,函數(shù),別名屿讽。

3.order by 子句一般是放在查詢語句的最后面,limit子句除外。

常見函數(shù)

一吠裆、單行函數(shù)

#用法
SELECT 函數(shù)名() FROM 表名 WHERE 篩選條件
1伐谈、字符函數(shù)
    concat　拼接
    substr　截取子串  -- 注意：索引從1開始
    upper　轉(zhuǎn)換成大寫
    lower　轉(zhuǎn)換成小寫
    trim　去前后指定的空格和字符
    ltrim　去左邊空格
    rtrim　去右邊空格
    replace　替換
    lpad　用指定的字符實(shí)現(xiàn)左填充指定長度
    rpad　用指定的字符實(shí)現(xiàn)右填充指定長度
    instr　返回子串第一次出現(xiàn)的索引 -- 如果找不到返回0
    length 獲取字節(jié)個數(shù)

2、數(shù)學(xué)函數(shù)
    round 四舍五入
    rand 隨機(jī)數(shù)
    floor 向下取整 -- <=該參數(shù)的最大整數(shù)
    ceil 向上取整  -- >=該參數(shù)的最小整數(shù)
    mod 取余
    truncate 截斷
    
3试疙、日期函數(shù)
    now 當(dāng)前系統(tǒng)日期+時間
    curdate 當(dāng)前系統(tǒng)日期
    curtime 當(dāng)前系統(tǒng)時間
    str_to_date 將字符轉(zhuǎn)換成日期
    date_format  將日期轉(zhuǎn)換成字符
    DATEDIFF 求兩個日期相差的天數(shù) 
    SELECT DATEDIFF(now(),"1998-05-27") 
    
4诵棵、其他函數(shù)
    version 版本
    database 當(dāng)前庫
    user 當(dāng)前連接用戶
    md5('字符') 返回該字符的md5加密形式 
    datediff(大的日期，小的日期) 返回兩個日期的時間差

?

二祝旷、分組函數(shù)

    sum 求和
    max 最大值
    min 最小值
    avg 平均值
    count 計算個數(shù)

特點(diǎn)：
1履澳、sum,max,min,avg,count(字段名)都忽略null值，count(* )不忽略null值怀跛。
2距贷、sum和 avg一般用于處理數(shù)值型；max吻谋、min忠蝗、count可以處理任何數(shù)據(jù)類型。
3漓拾、都可以搭配distinct使用阁最，用于統(tǒng)計去重后的結(jié)果。
4骇两、count的參數(shù)可以支持：字段速种、常量值、* 低千，一般放1,建議使用 count(*)配阵。

效率：

MyISAM存儲引擎下,count(*)的效率高。

InnoDB存儲引擎下,count(*)和count(1)的效率差不多，比count(字段名)要高一些闸餐。

分組查詢

#語法：
#分組前的篩選
select 查詢的字段饱亮，分組函數(shù)
from 表名
[where 篩選條件]
group by 分組的字段
[order by 子句]

#分組后的篩選 having 子句
-- 查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資
select MAX(salary),job_id from employees where commission_pct is not null
group by job_id having MAX(salart)>12000;

-- 按員工姓名的長度分組,查詢每一組的員工個數(shù)，篩選員工個數(shù)>5
select count(*) c,LENGTH(last_name) len_name from employees 
group by len_name having c>5;
#group by,having后可以支持別名舍沙。

特點(diǎn)：
1近上、支持按單個字段分組;也可以按多個字段分組,多個字段之間用逗號隔開,沒有順序要求;表達(dá)式或函數(shù)(用得較少)。
2拂铡、和分組函數(shù)一同查詢的字段最好是分組后的字段壹无。
3、分組篩選
針對的表 位置 關(guān)鍵字
分組前篩選： 原始表 group by的前面 where
分組后篩選： 分組后的結(jié)果集 group by的后面 having

分組函數(shù)做條件肯定是放在having子句中感帅。

4.查詢列表必須特殊,要求是分組函數(shù)和group by后出現(xiàn)的字段斗锭。

多表連接查詢

一、分類

按年代分類：

sql92標(biāo)準(zhǔn)失球，sql99標(biāo)準(zhǔn)

按功能分類:

內(nèi)連接：等值連接,非等值連接,自連接

外連接：左外連接,右外連接,全外連接

交叉連接

二岖是、笛卡爾乘積

表1有m行，表2有n行,結(jié)果為m*n行实苞。
解決辦法：添加上有效的連接條件豺撑。

select * from emp,dept;

三、sql92標(biāo)準(zhǔn)

含義：1992年推出的sql語法黔牵。

僅僅支持內(nèi)連接聪轿。

#1.等值連接 先做笛卡爾積，然后篩選,篩選條件為等值篩選猾浦。
select e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno;
/*
1.多表等值連接的結(jié)果為多表的交集部分陆错。
2.n表連接，至少需要n-1個連接條件金赦。
3.多表的順序沒有要求音瓷，一般需要為表起別名。
*/

#2.非等值連接
select * from emp e,sqlgrade s where e.sal>=s.losal and e.sal<=s.hisal;

#3.自連接
-- 查詢員工姓名,工作,薪資,及上級領(lǐng)導(dǎo)的姓名
select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

四夹抗、sql99標(biāo)準(zhǔn)[推薦使用]

含義：1999年推出的sql語法外莲。
支持：內(nèi)連接,外連接(左外連接，右外連接),交叉連接

#語法：
select 查詢列表
from 表1 別名
【inner|left [outer]|right [outer]|cross】join 表2 別名
on 連接條件
【where 篩選條件】
【group by 分組字段】
【having 分組后的篩選條件】
【order by 排序的字段或表達(dá)式】

-- 內(nèi)連接
#1.等值連接
select 查詢列表
from 表1 別名
[inner] join 表2 別名
on 連接條件
[where 篩選條件]

select e.ename,e.job,d.dname 
from emp e
inner join dept d 
on e.deptno = d.deptno
where e.deptno=1001;
/*
1.inner可以省略兔朦。
2.連接條件放在on后面,篩選條件放在where后面,提高分離性,便于閱讀偷线。
*/

#2.非等值連接
select * 
from emp e
inner join sqlgrade s 
on e.sal>=s.losal and e.sal<=s.hisal;

#3.自連接
select e1.ename,e1.job,e1.sal,e2.ename 
from emp e1
inner join emp e2 
on e1.mgr=e2.empno;

-- 外連接
#應(yīng)用場景:用于查詢一個表中有，另一個表沒有的記錄沽甥。
/*
特點(diǎn)：
1.外連接的查詢結(jié)果為主表中的所有記錄
  如果從表中有和它匹配的,則顯示匹配的值声邦。
  如果從表中沒有和它匹配的,則顯示null。
  外連接查詢的結(jié)果=內(nèi)連接+主表中有而從表沒有的記錄摆舟。
2.左外連接：left join左邊的是主表
  右外連接：left join右邊的是主表
3.左外和右外交換兩個表的順序,可以實(shí)現(xiàn)同樣的效果亥曹。
*/

#1.左外連接與右外連接
-- 左外連接
select e.*,d.* 
from emp e
left outer join dept d
on e.deptno = d.deptno;

-- 右外連接
select e.*,d.* 
from emp e
right outer join dept d
on e.deptno = d.deptno;

#2.交叉連接 本質(zhì)就是笛卡爾積
select e.*,d.* from emp e
cross join dept d
on e.deptno=d.deptno;

五邓了、sql92 與 sql99的比較

功能：sql99支持的較多。

可讀性：sql99實(shí)現(xiàn)連接條件和篩選條件的分離,可讀性較高媳瞪。

推薦性: 建議使用sql99骗炉。

子查詢

含義：

? 一條查詢語句中又嵌套了另一條完整的select語句，其中被嵌套的select語句蛇受，稱為子查詢或內(nèi)查詢;在外面的查詢語句句葵，稱為主查詢或外查詢。

分類：

按子查詢出現(xiàn)的位置：

    SELECT 后面：
        僅僅支持標(biāo)量子查詢
    from 后面:
        支持表子查詢
    where或having后面:
        標(biāo)量子查詢 (單行子查詢)
        列子查詢   (多行子查詢)
        行子查詢   (多列多行)
    exists 后面 (相關(guān)子查詢)
        表子查詢

按結(jié)構(gòu)集的行列數(shù)不同:
標(biāo)量子查詢（結(jié)果集只有一行一列）

? 列子查詢 （結(jié)果集只有一列多行）

? 行子查詢 （結(jié)果集有一行多列）

? 表子查詢 (結(jié)果集一般為多行多列）

特點(diǎn)：

1兢仰、子查詢都放在小括號內(nèi)
2乍丈、子查詢可以放在from后面、select后面把将、where后面轻专、having后面亭枷，但一般放在條件的右側(cè)
3力试、子查詢優(yōu)先于主查詢執(zhí)行占拍，主查詢使用了子查詢的執(zhí)行結(jié)果
4锄奢、子查詢根據(jù)查詢結(jié)果的行數(shù)不同分為以下兩類：
① 單行子查詢
結(jié)果集只有一行
一般搭配單行操作符使用：> < = <> >= <=
非法使用子查詢的情況：
a、子查詢的結(jié)果為一組值
b指黎、子查詢的結(jié)果為空

# 標(biāo)量子查詢
-- 查詢工資比Tom高的員工信息
select *
form emp
where sal>(
            select sal
            from emp 
            where ename = 'Tom'
);


-- 查詢最低工資大于50號的最低工資的部門編號和其最低工資干茉。
select min(sal),deptno
from emp 
group by deptno
having min(sal)>(
                select min(sal) 
                from emp 
                where deptno=50
);

② 多行子查詢
結(jié)果只有一個字段但是字段有n個值论颅。

? 一般搭配多行操作符使用：any绞铃、all镜雨、in嫂侍、not in儿捧。

? any: 任意

? all: 所有

? in：表示任意存在，相當(dāng)于=any挑宠。

? not in :相當(dāng)于<> all菲盾。

#關(guān)鍵字 any
-- 查詢工資高于任意一個CLERK的所有員工信息。
select * 
from emp
where sal > any(
                select sal
                from emp 
                where job='CLERK'
);

#關(guān)鍵詞 all
-- 查詢工資高于所有SALESMAN的員工信息各淀。
select * 
from emp 
where sal > all(
                select sal
                from emp 
                where job='SALESMAN'
);

#關(guān)鍵詞 in
-- 查詢部門號20中同部門10的雇員工作一樣的雇員信息懒鉴。
select * 
from emp
where job in (
             select job
             from emp 
             where deptno=10
) and deptno =20;
-- 等價于
select * 
from emp
where job = any(
             select job
             from emp 
             where deptno=10
) and deptno =20;

行子查詢 (使用頻率少)

? 結(jié)果集一行多列或者多行多列

#查詢員工編號最小并且工資最高的員工信息。
select *
from emp
where empno = (
                select min(empno)
                from emp
)  and sal = (
                select max(sal)
                from emp
);
-- 等價于
select *
from emp
where (empno,sal) = (
                    select min(empno),max(sal)
                    from emp
);

from后面的子查詢

#查詢每個部門的平均工資的工資等級
-- 將子查詢結(jié)果充當(dāng)一張表,要求必須起別名
select avg_dep.*,s.grade
from (
     select avg(sal) avg,deptno
     from emp 
     group by deptno
) avg_dep
inner join salgrade s
on avg_dep.avg between losal and hisal;

exists后面的子查詢 （相關(guān)子查詢）

EXISTS用于檢查子查詢是否至少會返回一行數(shù)據(jù)碎浇，該子查詢實(shí)際上并不返回任何數(shù)據(jù)临谱，而是返回值true或false,結(jié)果為1或0。

語法: exists (完整的查詢語句)

#查詢有員工的部門名
select d.dname 
from dept d
where exists (
            select *
            from emp e
            where d.deptno =e.deptno        
);
-- 等價于
select d.dname 
from dept d
where d.deptno in (
            select deptno
            from emp                    
);

分頁查詢

應(yīng)用場景：

實(shí)際的web項目中需要根據(jù)用戶的需求提交對應(yīng)的分頁查詢的sql語句奴璃。

語法：

select 字段|表達(dá)式,...
from 表
【where 條件】
【group by 分組字段】
【having 條件】
【order by 排序的字段】
limit 【起始的條目索引悉默，】條目數(shù);

特點(diǎn)：

1.起始條目索引從0開始。

2.limit子句放在查詢語句的最后

3.公式：select * from 表 limit （page-1）*size,size
每頁顯示條目數(shù)size
要顯示的頁數(shù) page

#查詢前五條員工的信息
select * from emp limit 0,5;
select * from emp limit 5;

#查詢第11條-第25條
select * from emp 10,15;

#有獎金的員工信息,并且工資較高的前10名顯示出來
select * 
from emp
where comm is not null
order by sal desc
limit 0,10;

聯(lián)合查詢

引入：
union 聯(lián)合苟穆、合并：將多條查詢語句的結(jié)果合并成一個結(jié)果抄课。

語法：

select 字段|常量|表達(dá)式|函數(shù) 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達(dá)式|函數(shù) 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達(dá)式|函數(shù) 【from 表】 【where 條件】 union  【all】
.....
select 字段|常量|表達(dá)式|函數(shù) 【from 表】 【where 條件】

特點(diǎn)：

1唱星、多條查詢語句的查詢的列數(shù)必須是一致的。
2跟磨、多條查詢語句的查詢的列的類型幾乎相同间聊。
3、union代表去重抵拘，union all代表不去重哎榴。

應(yīng)用場景：

要查詢的結(jié)果來自于多個表，且多個表沒有直接的連接關(guān)系,但查詢的信息一致時仑濒。

#查詢中國用戶中男性的信息以及外國用戶中年男性的用戶信息
select id,cname,csex from t_ca where csex ='男'
union
select t_id,tname from t_ua where tGender='male';

DML語言的學(xué)習(xí)

插入

#語法：
-- 方式一 支持插入多行叹话，支持子查詢
    insert into 表名(字段名，...) values(值1墩瞳，...);
-- 方式二 
    insert into 表名 set 列名1=值1,列名2=值2...;

特點(diǎn)：

1驼壶、字段類型和值類型一致或兼容，而且一一對應(yīng)喉酌。
2热凹、可以為空的字段，可以不用插入值泪电，或用null填充般妙。
3、不可以為空的字段相速，必須插入值碟渺。
4、字段個數(shù)和值的個數(shù)必須一致突诬。
5苫拍、字段可以省略，但默認(rèn)所有字段旺隙，并且順序和表中的存儲順序一致绒极。

#方式一支持插入多行
insert into beauty
values(1603,'尹遠(yuǎn)洋','男',23),(1604,'張三','男',21),(1605,'李麗','女',18);

#支持子查詢
insert into beauty(id,name,age) select 1603,'尹遠(yuǎn)洋',23; 

修改

修改單表語法：

update 表名 set 字段=新值,字段=新值 where 篩選條件;

修改多表語法：

#sql92語法
update 表1 別名1,表2 別名2
set 字段=新值，字段=新值
where 連接條件
and 篩選條件;

#sql99語法
update 表1 別名1
inner|left|right join 表2 別名2
on 連接條件
set 字段=新值蔬捷，字段=新值
where 篩選條件;

刪除

方式1：delete語句

單表的刪除： ★

delete from 表名 [where 篩選條件];

多表的刪除：

#sql92語法
    delete 別名1垄提，別名2
    from 表1 別名1，表2 別名2
    where 連接條件
    and 篩選條件;
    
#sql99語法
    delete 別名1周拐，別名2
    from 表1 別名1
    inner|left|right join 表2 別名2
    on 連接條件
    where 篩選條件;

方式2：truncate語句

truncate table 表名;

兩種方式的區(qū)別【面試題】

1.truncate不能加where條件铡俐，而delete可以加where條件。

2.truncate的效率高一丟丟妥粟。

3.truncate 刪除帶自增長的列的表后审丘，如果再插入數(shù)據(jù)，數(shù)據(jù)從1開始罕容；
delete 刪除帶自增長列的表后备恤，如果再插入數(shù)據(jù)稿饰，數(shù)據(jù)從上一次的斷點(diǎn)處開始。

4.truncate刪除沒有返回值露泊，delete刪除有返回值喉镰。

4.truncate刪除不能回滾，delete刪除可以回滾惭笑。

DDL語言的學(xué)習(xí)

庫和表的管理

庫的管理

#創(chuàng)建庫
create database [if not exists] 庫名;
#修改庫 
-- 目前不能用了,不安全
#更改庫的字符集
alter database 庫名 character set 字符集;
#刪除庫
drop database [if exists] 庫名;

表的管理

#創(chuàng)建表
CREATE TABLE [IF NOT EXISTS] 表名(
    列名 列的類型[(長度) 約束],
    列名 列的類型[(長度) 約束],
    ...
);

CREATE TABLE IF NOT EXISTS stuinfo(
    stuId INT,
    stuName VARCHAR(20),
    gender CHAR,
    bornDate DATETIME
);


#修改表 alter
語法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段類型】;

#①修改字段名
ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;

#②修改表名
ALTER TABLE stuinfo RENAME [TO] studentinfo;

#③修改字段類型和列級約束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

#④添加字段
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20);

#⑤刪除字段
ALTER TABLE studentinfo DROP COLUMN email;

#刪除表
DROP TABLE [IF EXISTS] studentinfo;

#表的復(fù)制
-- 1.僅僅復(fù)制表的結(jié)構(gòu)
create table 表名 like 表名;

-- 2.復(fù)制表的結(jié)構(gòu)+數(shù)據(jù)
create table 新表 select * from 舊表;

?

常見的數(shù)據(jù)類型

整型：
tinyint侣姆、smallint、mediumint沉噩、int/Integer捺宗、bigint
小數(shù)：
浮點(diǎn)型: float、double
定點(diǎn)型:dec(M,D)川蒙、decimal(M,D)

? 特點(diǎn)：

? 1.M：整數(shù)部位+小數(shù)部位

? D:小數(shù)部位

? 如果超過范圍蚜厉，則插入臨界值。

? 2.M和D都可以省略畜眨，如果是decimal昼牛，則M默認(rèn)為10,D默認(rèn)為0。

? 如果是float和double,則會根據(jù)插入的數(shù)值的精度來決定精度康聂。

? 3.定點(diǎn)型的精確度較高,如果要求插入數(shù)值的精度較高如貨幣運(yùn)算等則考慮使用贰健。

字符型：

較短的文本:char 、varchar

較長的文本:text恬汁、blob(較大的二進(jìn)制)

其他：

binary和varbinary用于保存較短的二進(jìn)制伶椿。

enum用于保存枚舉。

set用于保存集合氓侧。

日期型：

分類：

? data:只保存日期

? time:只保存時間

? year:只保存年

? datetime:保存日期+時間

? timestamp:保存日期+時間

特點(diǎn)：

Blob類型：

? ·tinyblob:僅255個字符
　　·blob:最大限制到65K字節(jié)
　　·mediumblob:限制到16M字節(jié)
　　·longblob:可達(dá)4GB

常見約束

NOT NULL:非空,用來保證該字段的值不能為空
DEFAULT:默認(rèn),用于保證該字段有默認(rèn)值
UNIQUE:唯一,用于保證該字段的值具有唯一性,可以為空
CHECK:檢查約束  [mysql中不支持]
PRIMARY KEY:主鍵,用于保證該字段的值具有唯一性,并且非空
FOREIGN KEY:外鍵,用于限制兩個表的關(guān)系,用于保證該字段的值必須來自于主表的關(guān)聯(lián)列的值
在從表添加外鍵約束,用于引用主表中某列的值

添加約束的時機(jī)：

? 1.創(chuàng)建表時

? 2.修改表時

約束的添加分類：

? 列級約束：

? 六大約束語法上都支持,但是外鍵約束沒有效果

? 表級約束：

? 除了非空,默認(rèn),其他的都支持

create table 表名(
    字段名 字段類型 列級約束,
    字段名 字段類型,
    表級約束
);

列級約束

語法：直接在字段名和類型后面追加約束類型即可脊另。

只支持:默認(rèn),非空,主鍵,唯一。

表級約束

支持：主鍵,外鍵,唯一

[constraint 約束名] 約束類型(字段名);
constraint pk PRIMARY KEY(id), #主鍵約束
UNIQUE(seat), #唯一鍵
constraint fk_stuinfo_major FOREIGN KEY(majorid) references major(id)  #外鍵約束

主鍵和唯一的區(qū)別:

外鍵：

1.要求在從表設(shè)置外鍵關(guān)系烘豌。

2.從表的外鍵列的類型和主表的關(guān)聯(lián)列的類型要求一致或兼容载庭，名稱沒要求。

3.主表的關(guān)聯(lián)列必須是一個key（一般是主鍵或者唯一)廊佩。

4.插入數(shù)據(jù)時,先插入主表,再插入從表囚聚。
刪除數(shù)據(jù)時,先刪除從表,再刪除主表。

修改表時添加約束

1.添加列級約束

alter table 表名 modify column 字段名 字段類型 新約束;

2.添加表級約束

alter table 表名 add [constraint 約束名] 約束類型(字段名) [外鍵的引用];

#添加非空約束
alter table stuinfo modify column stuname varchar(20) not null;
#添加默認(rèn)約束
alter table stuinfo modify column age int  default 18;
#添加主鍵
-- 列級約束
alter table stuinfo modify column id  int primary key;
-- 表級約束
alter table stuinfo add primary key(id);
#添加唯一
-- 列級約束
alter table stuinfo modify column seat int unique;
-- 表級約束
alter table stuinfo add unique(seat);
#添加外鍵
-- 表級約束
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

修改表時刪除約束

#刪除非空約束
alter table stuinfo modify column stuname varchar(20)  null;
#刪除默認(rèn)約束
alter table stuinfo modify column age int ;
#刪除主鍵
alter table stuinfo drop primary key;
#刪除唯一
alter table stuinfo drop index seat;
#刪除外鍵
alter table stuinfo drop foreign key fk_stuinfo_major; 

標(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=數(shù)字鬼譬；設(shè)置步長

可以通過手動插入值 ,設(shè)置起始娜膘。

create table user(
    id int primary key auto_increment,
    ...
)

修改表時設(shè)置標(biāo)識列:

alter table user modify column id int primary key auto_increment;

刪除表時設(shè)置標(biāo)識列:

alter table user modify column id int;

數(shù)據(jù)庫事務(wù)

特點(diǎn)(ACID):

? 原子性（Atomicity）：一個事務(wù)不可再分割,要么都執(zhí)行要么都不執(zhí)行。
? 一致性(Consistency)：一個事務(wù)保證數(shù)據(jù)的狀態(tài)操作前和操作后保持一致优质。
? 隔離性(Isolation)：多個事務(wù)同時操作相同數(shù)據(jù)庫的同一個數(shù)據(jù)時竣贪，一個事務(wù)的執(zhí)行不受另外一個事務(wù)的干擾。
? 持久性(Durability)：一個事務(wù)一旦提交巩螃，則數(shù)據(jù)將持久化到本地演怎，除非其他事務(wù)對其進(jìn)行修改。

事務(wù)的分類

隱式事務(wù)避乏，沒有明顯的開啟和結(jié)束事務(wù)的標(biāo)志爷耀。

比如insert、update拍皮、delete語句本身就是一個事務(wù)畏纲。

顯式事務(wù)，具有明顯的開啟和結(jié)束事務(wù)的標(biāo)志春缕。

前提：必須先設(shè)置自動提交功能為禁用盗胀。

set autocommit=0;

#步驟1:開啟事務(wù)
set autocommit=0;
start transaction; #可選的
    
#步驟2.編寫事務(wù)的一組邏輯操作單元（多條sql語句）
    select
    insert
    update
    delete  
    
#步驟3.提交事務(wù)或回滾事務(wù)
    commit; 提交事務(wù)
    rollback; 回滾事務(wù)

使用到的關(guān)鍵字

set autocommit=0;
start transaction;
commit;
rollback;
savepoint  斷點(diǎn)
commit to 斷點(diǎn)
rollback to 斷點(diǎn)

#演示savepoint的使用
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a;-- 設(shè)置保持點(diǎn)a
delete from account where id=28;
rollback to a; -- 回滾到保持點(diǎn)a

事務(wù)的隔離級別

事務(wù)并發(fā)問題如何發(fā)生

當(dāng)多個事務(wù)同時操作同一個數(shù)據(jù)庫的相同數(shù)據(jù)時。

事務(wù)的并發(fā)問題有哪些

1锄贼、臟讀

臟讀是指并發(fā)過程中票灰，一個事務(wù)處理過程里讀取了另一個未提交的事務(wù)中的數(shù)據(jù)。

當(dāng)一個事務(wù)正在多次修改某個數(shù)據(jù)宅荤，而在這個事務(wù)中這多次的修改都還未提交屑迂，這時一個并發(fā)的事務(wù)來訪問該數(shù)據(jù)，就會造成兩個事務(wù)得到的數(shù)據(jù)不一致冯键。例如：用戶A向用戶B轉(zhuǎn)賬100元惹盼，對應(yīng)SQL命令如下：

update account set money=money+100 where name=’B’;  (此時A通知B)

update account set money=money - 100 where name=’A’;

當(dāng)只執(zhí)行第一條SQL時，A通知B查看賬戶惫确，B發(fā)現(xiàn)確實(shí)錢已到賬（此時即發(fā)生了臟讀）手报，而之后無論第二條SQL是否執(zhí)行，只要該事務(wù)不提交改化，則所有操作都將回滾掩蛤，那么當(dāng)B以后再次查看賬戶時就會發(fā)現(xiàn)錢其實(shí)并沒有轉(zhuǎn)。

2陈肛、不可重復(fù)讀

不可重復(fù)讀是指在對于數(shù)據(jù)庫中的某條數(shù)據(jù)揍鸟，一個事務(wù)范圍內(nèi)多次查詢卻返回了不同的數(shù)據(jù)值，這是由于在查詢間隔句旱，被另一個事務(wù)修改并提交了阳藻。

例如事務(wù)T1在讀取某一數(shù)據(jù)晰奖，而事務(wù)T2立馬修改了這個數(shù)據(jù)并且提交事務(wù)給數(shù)據(jù)庫，事務(wù)T1再次讀取該數(shù)據(jù)就得到了不同的結(jié)果腥泥，發(fā)生了不可重復(fù)讀畅涂。

3、幻讀

幻讀是事務(wù)非獨(dú)立執(zhí)行時發(fā)生的一種現(xiàn)象道川。例如事務(wù)T1查詢整張表中有多少條記錄午衰，這時事務(wù)T2又對這個表中插入了一行數(shù)據(jù)。而操作事務(wù)T1的用戶如果再查看整張表有多少行數(shù)據(jù)冒萄，會發(fā)現(xiàn)多出一行數(shù)據(jù)臊岸，其實(shí)這行是事務(wù)T2添加的，就好像產(chǎn)生幻覺一樣尊流，這就是發(fā)生了幻讀帅戒。

不可重復(fù)讀和臟讀的區(qū)別是:

臟讀是某一事務(wù)讀取了另一個事務(wù)未提交的臟數(shù)據(jù)。

不可重復(fù)讀則是兩次讀取之間存在另一個事務(wù)提交的數(shù)據(jù)崖技。

幻讀和不可重復(fù)讀區(qū)別是：

• 不可重復(fù)讀的重點(diǎn)是修改（update）逻住，操作的是某一行數(shù)據(jù)，需要鎖行迎献。同樣的條件, 你讀取過的數(shù)據(jù), 再次讀取出來發(fā)現(xiàn)值不一樣了瞎访。

• 幻讀的重點(diǎn)在于新增（insert）或者刪除（delete），操作的是整張表吁恍，需要鎖表扒秸。同樣的條件, 第1次和第2次讀出來的記錄數(shù)不一樣。

如何避免事務(wù)的并發(fā)問題

通過設(shè)置事務(wù)的隔離級別:
1冀瓦、READ UNCOMMITTED（讀未提交）
2伴奥、READ COMMITTED（讀已提交） 可以避免臟讀
3、REPEATABLE READ（可重復(fù)讀） 可以避免臟讀翼闽、不可重復(fù)讀和一部分幻讀 (默認(rèn))
4拾徙、SERIALIZABLE (串行化) 可以避免臟讀、不可重復(fù)讀和幻讀

設(shè)置隔離級別

set session|global  transaction isolation level 隔離級別名;

查看隔離級別

select @@tx_isolation;

視圖

含義：理解成一張?zhí)摂M的表感局。

視圖和表的區(qū)別：

視圖的好處：

1蓝厌、sql語句提高重用性玄叠，簡化復(fù)雜的sql操作古徒，效率高拓提。
2、和表實(shí)現(xiàn)了分離隧膘，保護(hù)數(shù)據(jù),提高了安全性代态。

視圖的創(chuàng)建

語法：

CREATE VIEW  視圖名
    AS
查詢語句;

視圖中數(shù)據(jù)增刪改查

#1寺惫、查看視圖的數(shù)據(jù) ★
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';

#一般不更改視圖里面的數(shù)據(jù)
#2、插入視圖的數(shù)據(jù)
INSERT INTO my_v4(last_name,department_id) VALUES('虛竹',90);

#3蹦疑、修改視圖的數(shù)據(jù)
UPDATE my_v4 SET last_name ='夢姑' WHERE last_name='虛竹';

#4西雀、刪除視圖的數(shù)據(jù)
DELETE FROM my_v4;

某些視圖不能更新

? 包含以下關(guān)鍵字的sql語句：分組函數(shù)、distinct歉摧、group by艇肴、having、union或者union all
? 常量視圖
? Select中包含子查詢
? join
? from一個不能更新的視圖
? where子句的子查詢引用了from子句中的表

視圖的更新

#方式一：
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;

#方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;

視圖的刪除

DROP VIEW test_v1,test_v2,test_v3;

視圖的查看

#方式1
DESC test_v7;
#方式2
SHOW CREATE VIEW test_v7;

查詢順序

FROM --> ON --> JOIN --> Where --> group by --> having --> select --> distinct --> order by --> limit