最近又重新復(fù)習(xí)了一遍SQL的基礎(chǔ)和進(jìn)階知識(shí)，SQL可以很基礎(chǔ)也可以很復(fù)雜，寫一篇總結(jié)來(lái)梳理自己的知識(shí)脈絡(luò)
SQL是結(jié)構(gòu)化查詢語(yǔ)言，是一種用來(lái)操作RDBMS的數(shù)據(jù)庫(kù)語(yǔ)言，當(dāng)前關(guān)系型數(shù)據(jù)庫(kù)都支持使用SQL語(yǔ)言進(jìn)行操作,也就是說(shuō)可以通過(guò) SQL 操作 oracle,sql server,mysql,sqlite 等等所有的關(guān)系型的數(shù)據(jù)庫(kù)。
SQL語(yǔ)句主要分為：
DQL：數(shù)據(jù)查詢語(yǔ)言，用于對(duì)數(shù)據(jù)進(jìn)行查詢，如select
DML：數(shù)據(jù)操作語(yǔ)言，對(duì)數(shù)據(jù)進(jìn)行增加、修改、刪除，如insert、udpate、delete
TPL：事務(wù)處理語(yǔ)言，對(duì)事務(wù)進(jìn)行處理，包括begin transaction、commit、rollback
DCL：數(shù)據(jù)控制語(yǔ)言，進(jìn)行授權(quán)與權(quán)限回收，如grant、revoke
DDL：數(shù)據(jù)定義語(yǔ)言，進(jìn)行數(shù)據(jù)庫(kù)、表的管理等，如create、drop
CCL：指針控制語(yǔ)言，通過(guò)控制指針完成表的操作，如declare cursor
數(shù)據(jù)分析必須熟練編寫DQL、DML，能夠編寫DDL完成數(shù)據(jù)庫(kù)、表的操作，其它語(yǔ)言如TPL、DCL、CCL了解即可。
在實(shí)際操作過(guò)程中，我使用的是MYSQL，MySQL使用標(biāo)準(zhǔn)的SQL數(shù)據(jù)語(yǔ)言形式，同時(shí)開源免費(fèi)，適用范圍廣。開啟MYSQL可以用電腦的終端，也可以搭載可視化軟件(navicat或mysqlworkbench)。
一個(gè)數(shù)據(jù)庫(kù)就是一個(gè)完整的業(yè)務(wù)單元，可以包含多張表，數(shù)據(jù)被存儲(chǔ)在表中。在表中為了更加準(zhǔn)確的存儲(chǔ)數(shù)據(jù)，保證數(shù)據(jù)的正確有效，可以在創(chuàng)建表的時(shí)候，為表添加一些強(qiáng)制性的驗(yàn)證，包括數(shù)據(jù)字段的類型、約束
MySQL支持所有標(biāo)準(zhǔn)的SQL數(shù)據(jù)類型，主要分3類:
? ? ·數(shù)值類型
? ? ·字符串類型
? ? ·時(shí)間日期類型
可以參考這篇文章-https://blog.csdn.net/anxpp/article/details/51284106
約束 需要了解：
·主鍵primary key：物理上存儲(chǔ)的順序
·非空not null：此字段不允許填寫空值
·惟一unique：此字段的值不允許重復(fù)
·默認(rèn)default：當(dāng)不填寫此值時(shí)會(huì)使用默認(rèn)值，如果填寫時(shí)以填寫為準(zhǔn)
·外鍵foreign key：對(duì)關(guān)系字段進(jìn)行約束，當(dāng)為關(guān)系字段填寫值時(shí)，會(huì)到關(guān)聯(lián)的表中查詢此值是否存在，如果存在則填寫成功，如果不存在則填寫失敗并拋出異常。
數(shù)據(jù)庫(kù)的操作
-- 連接數(shù)據(jù)庫(kù)
mysql -uroot -p (輸入密碼）
-- 退出數(shù)據(jù)庫(kù)
exit/quit?
-- 查看所有數(shù)據(jù)庫(kù)
show databases;
-- 查看當(dāng)前使用的數(shù)據(jù)庫(kù)；
select database();
-- 創(chuàng)建數(shù)據(jù)庫(kù)
create database 數(shù)據(jù)庫(kù)名 charset=utf8;
--使用數(shù)據(jù)庫(kù)
use? 數(shù)據(jù)庫(kù)名;
-- 刪除數(shù)據(jù)庫(kù)
drop database 數(shù)據(jù)庫(kù)名;
數(shù)據(jù)表的操作
-- 查看當(dāng)前數(shù)據(jù)庫(kù)所有表
show tables;
-- 查看表結(jié)構(gòu)
desc 表名;
-- 創(chuàng)建表
create table 表名（字段 類型 約束，字段 類型 約束，···）;
create table user_info (
id int unsigned primary key auto_increment not null,
name varchar(20) not null
);
-- 查看建表語(yǔ)句
show create table 表名；
?-- 刪除表
drop table 表名;
表中字段的操作
-- 修改字段名 重命名
alter table 表名 change 原列名 新列名 類型及約束；
alter table user_info change id user_id varchar(20) not null;
-- 修改字段 不重命名
alter table 表名 modify 列名 類型及約束;
-- 添加字段?
alter table 表名 add 列名 類型；
-- 刪除字段?
alter table 表名 drop 列名；
數(shù)據(jù)的操作 增刪改查（crud)
-- 增加記錄
全列插入?insert into 表名 values(...) 如?insert into classes values(1,'zhangsan');
部分插入?insert into 表名(列1,...) values(值1,...) 如??insert into classes(name) values('zhangsan');
多行插入 insert into students values(0,'老劉',40,160,'男',003),(0,'老王',20,180,default,003);
注意：主鍵字段 可以用 0、null、default 來(lái)占位
-- 刪除記錄
物理刪除 delete from 表名 where 條件?
邏輯刪除 即用一個(gè)字段來(lái)表示這條信息已被刪除?
alter table students add is_delete bit default 0;?
-- 修改記錄
update 表名 set 列1=值1，列2=值2 ··· where 條件；
全部修改?update students set gender='保密'
部分按條件修改?update students set gender='女' where name='huahua'
按條件修改多個(gè)值?update students set gender='男' where cls_id=3;
-- 查詢記錄
全部查詢 select * from 表名；
條件查詢?select * from 表名 where 條件；
查詢指定列?select 列名 from 表名 ；
用AS給字段起別名?select 字段[as 別名] , 字段[as 別名] from 表名
***? 高級(jí)查詢語(yǔ)句
條件查詢：使用where子句對(duì)表中的數(shù)據(jù)篩選，結(jié)果為true的行會(huì)出現(xiàn)在結(jié)果集中
where后面支持多種運(yùn)算符，進(jìn)行條件的處理
1、比較運(yùn)算符（大于小于等于不等于···）
2、邏輯運(yùn)算符（and\or\not)
3、模糊查詢（like:%表示任意多個(gè)任意字符, _表示一個(gè)任意字符)
4、范圍查詢（in、 between··· and···）
5、空判斷（is null、is? not null)
聚合函數(shù)：
1、總數(shù) count(列）不會(huì)統(tǒng)計(jì)null值記錄
count(*)=count(1) ，結(jié)果是相同的，count(列) 不會(huì)統(tǒng)計(jì)null值記錄
2、最大值 max(列)
3、最小值 min(列)
4、求和?sum(列)
5、求平均值?avg(列)
分組查詢：
1、group by
2、group by + group_concat()?
group_concat(字段名)可以作為一個(gè)輸出字段來(lái)使用
select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+|?
gender | group_concat(name) |
+--------+-----------------------------------------------------------+|?
男 | 彭于晏,劉德華,周杰倫,程坤,郭靖 ||?
女 | 小明,小月月,黃蓉,王祖賢,劉亦菲,靜香,周杰 ||?
中性 | 金星 || 保密 | 鳳姐 |
+--------+-----------------------------------------------------------+
3、group by + 集合函數(shù)
4、group by + having?用來(lái)分組查詢后指定一些條件來(lái)輸出查詢結(jié)果
5、?group by + with rollup?在最后新增一行，來(lái)記錄當(dāng)前列里所有記錄的總和
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男? ? |? ? ? ? 5 |
| 女? ? |? ? ? ? 7 |
| 中性? |? ? ? ? 1 |
| 保密? |? ? ? ? 1 |
| NULL? |? ? ? 14 |
+--------+----------+
分頁(yè)查詢:
limit: select * from 表名 limit start,count?從start開始，獲取count條數(shù)據(jù)
連接查詢：
子查詢：在一個(gè) select 語(yǔ)句中,嵌入了另外一個(gè) select 語(yǔ)句, 那么被嵌入的 select 語(yǔ)句稱之為子查詢語(yǔ)句
子查詢分為三類：
1、標(biāo)量子查詢: 子查詢返回的結(jié)果是一個(gè)數(shù)據(jù)(一行一列)
如 查詢大于平均年齡的學(xué)生
2、列子查詢: 返回的結(jié)果是一列(一列多行)
如?查詢所有學(xué)生在班的所有班級(jí)名字
3、行子查詢: 返回的結(jié)果是一行(一行多列)
如 查詢年齡最大，身高最高的學(xué)生具體信息
要感慨一句，子查詢可以很復(fù)雜，繞人，但是有時(shí)候也很好用。。。。
數(shù)據(jù)查詢的執(zhí)行順序
from-where-groupby-having-select-orderby-limit
1、from首先從哪張表取數(shù)
2、where篩選條件，不是所有的數(shù)據(jù)都要
3、groupby 做一個(gè)分組聚合
4、having 分組聚合后的結(jié)果也并不是都要,滿足條件的才要
5、select? 選擇需要的指定字段
6、orderby 排序
7、limit 限制顯示數(shù)量
舉個(gè)例子：從訂單表中查詢出過(guò)去一年內(nèi)，購(gòu)買10次以上的用戶名單，展示前10名的姓名、購(gòu)物金額
select 姓名、sum(price) as 購(gòu)物金額
from 訂單表
where 時(shí)間=過(guò)去一年內(nèi)
group by 用戶ID
having count(*)>10
order by 購(gòu)物金額 desc
limit 10
*having后面可以接聚合函數(shù)，但是where后不能接聚合函數(shù)，因?yàn)閳?zhí)行順序的原因，where執(zhí)行時(shí)還沒有完成分組聚合。
時(shí)間函數(shù)
--獲取當(dāng)前時(shí)間
select now()?執(zhí)行開始時(shí)的系統(tǒng)時(shí)間
select sysdate()?在函數(shù)執(zhí)行時(shí)動(dòng)態(tài)得到值
-- 獲取年月日?
select current_date()
-- 獲取時(shí)分秒
select current_time()
-- 轉(zhuǎn)換字符串為日期格式
select str_to_date(時(shí)間字符串，字符串日期格式）
select str_to_date（'08/09/08' , '%m/%d/%y');?-- 2008-08-09
--?轉(zhuǎn)換日期為字符串格式
select date_format(now(),'%Y-%M-%d %H')
-- 提取時(shí)間的函數(shù)
常用的year/month/time/day/week/minute/hour/quarter````
dayofyear,dayofmonth,dayofweek,dayofmonth,yearweek```
-- 日期的運(yùn)算
減法?date_sub(日期 ，要減少偏移的間隔)
select date_sub('1998-01-01 00:00:00', interval 1 day); ---?1997-12-31 00:00:00
加法 date_add(date,INTERVAL expr type)
select date_add('1998-01-01 00:00:00', interval 1 day); ---?1998-1-2 00:00:00
-- 兩個(gè)日期的天數(shù)差
date_diff（time1，time2）：返回兩個(gè)日期之間（time1-time2）的天數(shù)。
eg：
select datediff('2008-08-08','2008-08-01')
--?兩個(gè)日期的時(shí)間差
timediff(time1,time2)：兩個(gè)日期相減 time1 - time2，返回 time 差值。
注意：timediff(time1,time2) 函數(shù)的兩個(gè)參數(shù)類型必須相同。
--- 兩個(gè)日期得到指定差
timestampdiff(unit,begin,end)
返回end-begin的結(jié)果，其中begin和end是date或datetime格式
例如：timestampdiff(year,birth,now()) 計(jì)算生日 返回兩個(gè)日期之間的年份，未過(guò)生日減一
做題的過(guò)程中，發(fā)現(xiàn)窗口函數(shù)也是常考內(nèi)容，補(bǔ)充一下
窗口函數(shù)：Mysql8.0以上版本支持窗口函數(shù)，窗口函數(shù)與分組聚合函數(shù)類似，但是每一行數(shù)據(jù)都生成一個(gè)結(jié)果。
面對(duì)一些既要分組又要排序的TOP N問題，我們可以使用窗口函數(shù)快速得出結(jié)果.
窗口函數(shù)和普通聚合函數(shù)區(qū)別如下：
··聚合函數(shù)是將多條記錄聚合為一條；而窗口函數(shù)是每條記錄都會(huì)執(zhí)行，有幾條記錄執(zhí)行完還是幾條。
··聚合函數(shù)也可以用于窗口函數(shù)中
使用方法：配合聚合窗口函數(shù)一起使用，例如SUM/AVG/COUNT/MAX/MIN等
select *?
from(
????select*,
????????row_number() over (partition by 要分組的列名 order by 要排序的列名 desc) as 排名
????from 表名) as a
where排名<=N;
ROW_NUMBER() 依次排序且不會(huì)重復(fù),不考慮并列名次的情況
DENSE_RANK() 遇到重復(fù)值時(shí)連續(xù)排序,如果有并列名次的行，不占用下一名次的位置
RANK() 跳躍排序,如果有并列名次的行，會(huì)占用下一名次的位置
PERCENT_RANK() 百分位排序，遇到相同值的時(shí)候連續(xù)排序。
case when語(yǔ)句
"case
when 列名= 條件值1 then 選擇項(xiàng)1
when 列名=條件值2 then 選項(xiàng)2.......
else 默認(rèn)值 end"
·經(jīng)常會(huì)結(jié)合聚合函數(shù)（sum)一起來(lái)計(jì)算人數(shù)，金額····
例如： select group, sum( case when ``` then 1 else 0 end)? as? ' 消費(fèi)人數(shù)'?
·還有用于行轉(zhuǎn)列的操作：
例如將圖1轉(zhuǎn)換為圖2
select name as '姓名'
,max(case course when '語(yǔ)文' then score else 0 end) as '語(yǔ)文'
,max(case course when '數(shù)學(xué)' then score else 0 end) as '數(shù)學(xué)'
,max(case course when '英語(yǔ)' then score else 0 end) as '英語(yǔ)'
from test.course_score group by name;
刷題的過(guò)程中還會(huì)發(fā)現(xiàn)很多常見解題套路，夢(mèng)回高中做數(shù)學(xué)題 _(:з」∠)_?
到時(shí)候再來(lái)總結(jié)
