柚子快報邀請碼778899分享：SQL學習-基礎(chǔ)篇

http://yzkb.51969.com/

提示：文章寫完后，目錄可以自動生成，如何生成可參考右邊的幫助文檔

文章目錄

一、數(shù)據(jù)庫

二、SQL1，SQL通用語法2. SQL分類2.1-DDL-建庫建表2.1.1-數(shù)據(jù)類型2.1.2-DDL-表操作2.2-DML-操作數(shù)據(jù)2.2.1-DML-添加數(shù)據(jù)2.2.2-DML-修改數(shù)據(jù)2.2.3-DML-刪除數(shù)據(jù)2.3-DQL-數(shù)據(jù)查詢-業(yè)務(wù)里用的最多2.3.1-DQL-基本查詢2.3.2-DQL-條件查詢2.3.3-DQL-聚合函數(shù)2.3.4-DQL-分組查詢（group by）2.3.5-DQL-排序查詢(order by)2.3.5-DQL-分頁查詢(LIMIT)2.4-DCL2.4.1-DCL-管理用戶2.4.1-DCL-權(quán)限控制

三、函數(shù)3.1-函數(shù)-字符串函數(shù)3.2-函數(shù)-數(shù)值函數(shù)3.3-函數(shù)-日期函數(shù)3.4-函數(shù)-流程函數(shù)

四、約束五、多表查詢（表連接）5.1 內(nèi)連接（查詢的是兩張表交集的部分）5.2 外連接5.3 自連接5.4 聯(lián)合查詢5.5 嵌套查詢（子查詢）5.5.1-標量子查詢5.5.2-列子查詢5.5.3-行子查詢5.5.3-表子查詢

六、 窗口函數(shù)

一、數(shù)據(jù)庫

1. 數(shù)據(jù)庫相關(guān)概念 (1)數(shù)據(jù)庫-DB (2)數(shù)據(jù)庫管理系統(tǒng)-DBMS-操控數(shù)據(jù)庫的軟件 (3)sql-編程語言 2. mysql數(shù)據(jù)庫（RDBMS） 是關(guān)系型數(shù)據(jù)庫，建立在關(guān)系模型上，由二維表組成的數(shù)據(jù)庫。 3. 數(shù)據(jù)模型 創(chuàng)建表

二、SQL

1，SQL通用語法

1，單行或多行，分號結(jié)尾 2，可用空格 3，不區(qū)分大小寫，但關(guān)鍵字建議大寫 4，注釋

（1）單行注釋：#或– （2）多行注釋：/* ,*/

5，*代表所有數(shù)據(jù)

2. SQL分類

1，DDL-定義語言-表、字段、數(shù)據(jù)庫 2，DML-操作語言-對數(shù)據(jù)增刪改 3，DQL-查詢語言-查詢表的記錄 4，DCL-控制語言-創(chuàng)建、控制數(shù)據(jù)庫的訪問權(quán)限

2.1-DDL-建庫建表

SHOW DATABASES#查詢所有數(shù)據(jù)庫

SELECT DATABASE()#查詢當前數(shù)據(jù)庫

CREATE DATABASE[IF NOT EXISTS]#數(shù)據(jù)庫名

[DEFAULT CHARSET]#字符集

[COLLATE]#排序規(guī)則,[]代表可有可無

CREATE DATABASE DUAN DEFAULT CHARSET utf8mb4#例子

DROP DATABASE[IF EXISTS]#刪除

USE 數(shù)據(jù)庫名#使用

（1）DDL-表操作?，進入數(shù)據(jù)庫后即USE

SHOW TABLES#查詢數(shù)據(jù)庫所有表

DESC#查表

SHOW CREATE TABLE#查詢建表語句

CREATE TABLE 表名(

字段1 字段1類型[COMMENT字段1注釋],

字段2 字段2類型[COMMENT字段2注釋],

.....

字段n 字段n類型[COMMENT字段n注釋]

)[COMMENT 表注釋]#建表，最后一個字段沒有逗號，且為英文逗號

建表例子，第一步先使用數(shù)據(jù)庫

create table tb_user(

id int comment '編號',

name varchar(50) comment '性名',

age int comment '年齡',

gender varchar(1) comment '姓別'

) comment '用戶表';

2.1.1-數(shù)據(jù)類型

數(shù)值類型 int-整 float-單精度浮點數(shù) double-雙精度浮點數(shù)

字符串類型 char-定長字符串-用不滿會自動填比如char(50) varchar-變長字符串-varchar(50)-用多少是多少 text-文本數(shù)據(jù)

日期類型 date-年月日-日期 time-時分秒-時間值或持續(xù)時間 year-年份 datetime-年月日+時分秒

2.1.2-DDL-表操作

ALTER TABLE 表名 ADD 字段名 類型(長度) comment '注釋'#添加字段

ALTER TABLE 表名 MODIFY 字段名 新數(shù)據(jù)類型(長度)#修改數(shù)據(jù)類型

ALTER TABLE 表名 CHANGE 舊字段名 新字段名 類型(長度)#修改字段名和字段類型

ALTER TABLE 表名 DROP 字段名#刪除字段

ALTER TABLE 表名 RENAME TO 新表名#修改表名

DROP TABLE [IF EXISTS] 表名#刪除表

TRUNCATE TABLE 表名#刪除并重建（只有表結(jié)構(gòu)沒有數(shù)據(jù)）

2.2-DML-操作數(shù)據(jù)

添加數(shù)據(jù)-INSERT 修改數(shù)據(jù)-UPDATE 刪除數(shù)據(jù)-DELETE

2.2.1-DML-添加數(shù)據(jù)

INSERT INTO 表名 (字段1,字段2,...) VALUES(值1,值2,...)#給指定字段添加數(shù)據(jù)

INSERT INTO 表名 VALUES (值1,值2,...)#給全部字段添加數(shù)據(jù)

INSERT INTO 表名 (字段1,字段2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)#批量添加數(shù)據(jù)

INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...)#給全部字段添加數(shù)據(jù)

2.2.2-DML-修改數(shù)據(jù)

UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[where 條件]

2.2.3-DML-刪除數(shù)據(jù)

DELETE FROM 表名 [where 條件]

2.3-DQL-數(shù)據(jù)查詢-業(yè)務(wù)里用的最多

編寫順序

select 字段列表

from 表名

where 條件列表

group by 分組字段列表

having 分組后條件列表

order by 排序字段列表

limit 分頁參數(shù)

執(zhí)行順序

from 表名

where 條件列表

group by 分組字段列表

having 分組后條件列表

select 字段列表（設(shè)置別名等）

order by 排序字段列表

limit 分頁參數(shù)

2.3.1-DQL-基本查詢

1，查詢多個字段

SELECT 字段1,字段2,字段3...FROM 表名

SELECT*FROM 表名#返回所有字段

2，設(shè)置別名

SELECT 字段1 [AS] 別名1,字段2 [AS] 別名2...FROM表名#不加AS就隔個空格就行

3，去除重復(fù)記錄

SELECT DISTINCT 字段列表 FROM 表名#在這個字段下重復(fù)的歸為一類，字段列表的意思同上，即多個字段。

2.3.2-DQL-條件查詢

可以這么寫

where(A,B)=(1111,12222)代表A=1111，B等于12222

SELECT 字段列表 FROM 表名 WHERE 條件列表

邏輯： <=,小于等于 ！=或<>是不等于 is null沒有，空值；is not null 有 and或者&&——且（多個條件成立） or或者||——或（任一條件成立） not或者！——非，不是

between 18 and 30;#取值18-30，閉區(qū)間

in(…)多選一，例如

where age in(1,11,111);#年齡為1歲或11歲或111歲

like，'_‘單個字符，’%'多個字符，例 可以not like

where name like '__';#兩個下劃線，即名字兩個字

where idcard like '%X';#查詢身份證號最后一位是X的

where idcard like '_________________X';#另一種寫法17個下劃線

where university like '%北京%';#大學的名稱里有北京

select prod_name,

prod_desc

from Products

where prod_desc like '%toy%carrots%'

#先后出現(xiàn)toy和carrots

2.3.3-DQL-聚合函數(shù)

1，指的是將一列數(shù)據(jù)作為一個整體，進行縱向計算。null不參與計算。 2，常見聚合函數(shù)：

count 統(tǒng)計數(shù)量maxminavg 平均值sum sum是相加和值，count是統(tǒng)計這一列的個數(shù)

sum(if(qpd.result='right', 1, 0))

#正確返回1 ，不成立返回0

round(value,n) #value是數(shù)值，n代表保留幾位小數(shù)

3，語法

SELECT 聚合函數(shù)(字段名) FROM 表名

2.3.4-DQL-分組查詢（group by）

1，語法

SELECT 字段列表 FROM 表名 [WHERE 條件] GROUP BY 分組字段名 [HAVING 分組后過濾條件]

#where分組前過濾條件，不滿足where條件，不參與分組；having分組后過濾條件。

where不能對聚合函數(shù)判斷，having可以。 示例1：根據(jù)性別分組，統(tǒng)計男性員工和女性員工數(shù)量（不涉及條件）

select gender,count(*) from 表名 group by gender;

#因為想看到兩列，所以寫了gender,count(*)，返回兩列

示例2：根據(jù)性別分組，統(tǒng)計男性員工和女性員工平均年齡（不涉及條件）

select gender,avg(age) from 表名 group by gender;

示例3：查詢年齡小于45的員工，并根據(jù)工作地址分組，獲取員工數(shù)量大于等于3的工作地址。

select '地址' ,count(*) from 表名 where age<45 group by '地址' having count(*)=>3

一般返回分組之后的字段和聚合函數(shù)。

2.3.5-DQL-排序查詢(order by)

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

select * from emp order by age adc,entrydata desc;

排序方式： ASC：升序（默認） DESC：降序

2.3.5-DQL-分頁查詢(LIMIT)

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查詢記錄數(shù);#起始索引別忘了乘記錄數(shù)

select * from 表名 order by age asc limit 5;#查詢前五個員工并年齡升序

起始索引從0開始，起始索引=（查詢頁碼-1）*每頁顯示記錄數(shù)不同的數(shù)據(jù)庫有不同的實現(xiàn)，Mysql是limit第一頁可以簡寫為limit 10

2.4-DCL

2.4.1-DCL-管理用戶

1，查詢用戶

USE mysql;

select * from user;

2，創(chuàng)建用戶

create user '用戶名'@'主機名' identified by '密碼';

3，修改密碼

alter user '用戶名'@'主機名' identified with mysql_native_password by '新密碼';

4，刪除函數(shù)

drop user '用戶名'@'主機名';

2.4.1-DCL-權(quán)限控制

show grants for '用戶名'@'主機名';#查詢權(quán)限

grant 權(quán)限列表 on 數(shù)據(jù)庫名.表名 to '用戶名'@'主機名';#授予權(quán)限

revoke 權(quán)限列表 on 數(shù)據(jù)庫名.表名 from '用戶名'@'主機名';#撤銷權(quán)限

三、函數(shù)

函數(shù)指一段可以直接被另一段程序調(diào)用的程序或代碼。

3.1-函數(shù)-字符串函數(shù)

select concat(last_name, " ", first_name) from employees 拼接并且中間以空格隔開

concat()-字符串拼接lower()-全部轉(zhuǎn)小寫upper()-全部轉(zhuǎn)大寫lpad(a,n,c)-左填充rpad(a,n,c)-右填充 例lpad(a,3,c)結(jié)果就是cca，rpad('o','3','6')結(jié)果就是066trim()-去除頭尾空格substring(A,n,m)-從字符串A中，從n開始截取到m，第一個是從1開始substring_index()更好用left(column_name,size):取字符串左邊 size個字符

使用：select 函數(shù)（）;

3.2-函數(shù)-數(shù)值函數(shù)

ceil(x)——向上取整 floor(x)——向下取整 mod(x,y)——返回x/y的模（余數(shù)） rand()——返回0-1內(nèi)的隨機數(shù) round(x,y)——求參數(shù)x的四舍五入的值，保留y位小數(shù) 例select lpad(round(rand()*1000000,0),6,'0');#六位隨機驗證碼

3.3-函數(shù)-日期函數(shù)

curdate()-返回當前日期 curtime()-返回當前時間 now()-返回當前時間和日期 year(date)-獲取指定date的年份 month(date)-獲取指定date的月份 day(date)-獲取指定date的日期 date_add(date,interval expr type)-返回加上expr的時間/日期值 type是類型如年月日 datediff(date1,date2)-返回起始時間date1和date2之間的天數(shù) timestampdiff(時間格式，開始時間，結(jié)束時間)返回時間差，時間格式可以是年、月、天 date_format(date,format) DATE_FORMAT將傳來的Date類型數(shù)據(jù)轉(zhuǎn)為自己需要的格式，如%Y-%m-%d %H:%i:%s會將傳來的Time數(shù)據(jù)轉(zhuǎn)為"yyyy-MM-dd HH:mm:ss"格式

SELECT DATE_FORMAT(需要轉(zhuǎn)的哪個時間字段,...)

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 結(jié)果：2020-12-07 22:18:58

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'); -- 結(jié)果：2020-12-07 22:18

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H'); -- 結(jié)果：2020-12-07 22

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); -- 結(jié)果：2020-12-07

SELECT DATE_FORMAT(NOW(),'%H:%i:%s'); -- 結(jié)果：22:18:58

SELECT DATE_FORMAT(NOW(),'%H'); -- 結(jié)果：22

SELECT DATE_FORMAT(NOW(),'%Y%m%d'); -- 結(jié)果：20201207

3.4-函數(shù)-流程函數(shù)

if(value,t,f)-若value為真(true)，則返回t，否則返回f ifnull(value1,value2)-若value1不為空返回value1，否則返回value2 case [expr] when [val1] then [res1] … else [default] end ——若expr的值等于val1，返回res1，否則返回default

select

name,

case workaddress when '北京' then '一線城市' when '上海' then '一線城市' else '二線城市' end as '工作地址' from 表名;

#這里select后不能重復(fù)加workaddress

四、約束

作用于表中字段上的規(guī)則，用于限制存儲在表中的數(shù)據(jù)

非空約束-not null唯一約束-unique-數(shù)據(jù)唯一不重復(fù)主鍵約束-primary key-一行數(shù)據(jù)的唯一標識，要求非空且唯一默認約束-default-未指定字段的值，采取默認值檢查約束-check-保證字段值滿足某個條件外鍵約束-foreign key-兩個表的連接

用法：在建表時直接在字段數(shù)據(jù)類型后面加就行

有外鍵時在父表中不允許刪除

alter table 表名 add constraint 外鍵名稱 foreign key(外鍵字段名)

references 主表(主表列名)# 添加外鍵

alter table 表名 drop foreign key 外鍵名稱#刪除外鍵

外鍵約束刪除或更新行為 cascade-有外鍵時，父表做刪除或更新，子表也刪除或更新 set null-有外鍵時，父表做刪除，子表出null

alter table 表名 add constraint 外鍵名稱 foreign key(外鍵字段名)

references 主表(主表列名) on update 行為 on delete 行為

五、多表查詢（表連接）

一對多：在多的一方建立外鍵，指向一的一方的主鍵 多對一：建立第三張中間表，中間表至少包含兩個外鍵，分別關(guān)聯(lián)兩方主鍵 一對一：隨意一方加主鍵關(guān)聯(lián)另一方主鍵，并設(shè)置為唯一的（UNIQUE） from a left join b 查詢a的所有行，并且將所有b里匹配的信息拿過來，若行數(shù)不一致或者出現(xiàn)缺失會出現(xiàn)null值 右鏈接反過來了，查詢right join 后的所有行…

5.1 內(nèi)連接（查詢的是兩張表交集的部分）

select 字段列表 from 表1,表2 where 條件...;#隱式內(nèi)連接

select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;#例

select 字段列表 from 表1 [inner] join 表2 on 連接條件...;#顯式內(nèi)連接，比隱式更快

5.2 外連接

左連接會把相匹配的字段拿過來，不是光on相等的那個字段

select 字段列表 from 表1 left [outer] join 表2 on 條件...;

#左外連接，查詢左表，包含交集

select 字段列表 from 表1 right [outer] join 表2 on 條件...;

#右外連接，同理

select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;#例

5.3 自連接

select 字段列表 from 表A 別名A join 表A 別名B on 條件...；#自連接可以左可以右

select a.name,b.name from emp a,emp b where a.mangerid = b.id;#例

5.4 聯(lián)合查詢

union[all]:把多次查詢結(jié)果合并，形成新的查詢結(jié)果集。

示例：

select 字段列表 from 表A ...

union[all]

select 字段列表 from 表B ...;#省略號代表后續(xù)加條件等...all代表去重

多張表的列數(shù)要一致，字段類型要一致 union 會去重，union all 全部

5.5 嵌套查詢（子查詢）

select * from t1 where column1 = (select column1 from t2)

select可以換為insert/update/delete

標量子查詢（結(jié)果是單個值）列子查詢行子查詢表子查詢（結(jié)果多行多列）

根據(jù)子查詢位置，分為where之后，from之后，select之后。

5.5.1-標量子查詢

返回單個值（數(shù)字，字符串，日期等） 例

select id from dept where name = '銷售部';

select * from emp where dept_id = 4;

select * from emp where dept_id = (select id from dept where name = '銷售部');

#將第一個第二個嵌套

5.5.2-列子查詢

返回的是一列 in-指定集合多選一 not in-不在指定集合 any-子查詢返回列表任意一個滿足即可 some=any all-必須全部滿足

用法all() 例

select id from dept where name = '財務(wù)部';

select salary from emp where dept_id = (select id from dept where name = '財務(wù)部')；

select * from emp where salaly > all(select salary from emp where dept_id = (select id from dept where name = '財務(wù)部'));

5.5.3-行子查詢

返回一行

5.5.3-表子查詢

返回多行多列

select job,salary from emp where name = 'A' or name = 'B';

select * from emp where (job,salary) in (select job,salary from emp where name = 'A' or name = 'B');

六、 窗口函數(shù)

搬運 https://zhuanlan.zhihu.com/p/92654574

avg加分組可以直接分組求平均值 sum()over(partition by a) 根據(jù)a求和排序

柚子快報邀請碼778899分享：SQL學習-基礎(chǔ)篇

http://yzkb.51969.com/

相關(guān)閱讀