我們使用SQL查詢不能只使用很簡單譬猫、最基礎(chǔ)的SELECT語句查詢移怯。如果想從多個(gè)表查詢比較復(fù)雜的信息，就會(huì)使用高級查詢實(shí)現(xiàn)和橙。常見的高級查詢包括多表連接查詢、內(nèi)連接查詢造垛、外連接查詢與組合查詢等
以一個(gè)簡單的學(xué)生信息表（學(xué)生ID魔招、學(xué)生姓名、學(xué)生性別）與一個(gè)科目表（科目ID五辽、科目）還有學(xué)生成績表（學(xué)生ID办斑、科目ID、分?jǐn)?shù)）為例子
創(chuàng)建表
create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2),
);
create table students(
id int auto_increment primary key,
sname varchar(20),
sex char(1)
);
create table subjects(
id int auto_increment primary key,
stitle varchar(20)
);
學(xué)生列的數(shù)據(jù)不是在這里新建的杆逗，而應(yīng)該從學(xué)生表引用過來乡翅，關(guān)系也是一條數(shù)據(jù)；根據(jù)范式要求應(yīng)該存儲(chǔ)學(xué)生的編號罪郊，而不是學(xué)生的姓名等其它信息
同理蠕蚜，科目表也是關(guān)系列，引用科目表中的數(shù)據(jù)
表關(guān)系
外鍵
思考：怎么保證關(guān)系列數(shù)據(jù)的有效性呢悔橄？任何整數(shù)都可以嗎靶累？
答：必須是學(xué)生表中id列存在的數(shù)據(jù)，可以通過外鍵約束進(jìn)行數(shù)據(jù)的有效性驗(yàn)證為stuid添加外鍵約束
alter table scores
add constraint stu_sco foreign key(stuid) references students(id);
此時(shí)插入或者修改數(shù)據(jù)時(shí)癣疟，如果stuid的值在students表中不存在則會(huì)報(bào)錯(cuò)
在創(chuàng)建表時(shí)可以直接創(chuàng)建約束
外鍵的級聯(lián)操作
- 在刪除students表的數(shù)據(jù)時(shí)挣柬，如果這個(gè)id值在scores中已經(jīng)存在，則會(huì)拋異常
- 推薦使用邏輯刪除睛挚，還可以解決這個(gè)問題
- 可以創(chuàng)建表時(shí)指定級聯(lián)操作邪蛔，也可以在創(chuàng)建表后再修改外鍵的級聯(lián)操作
alter table scores
add constraint stu_sco foreign key(stuid) references students(id)
on delete cascade;
在創(chuàng)建表時(shí)可以直接創(chuàng)建約束，添加級聯(lián)操作
create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2),
#這里我在創(chuàng)建表時(shí)直接創(chuàng)建約束扎狱，添加級聯(lián)操作侧到。后面就不用在添加了
foreign key(stuid) references students(id) on delete cascade,
foreign key(subid) references subjects(id) on delete cascade
);
級聯(lián)操作的類型包括：
restrict（限制）：默認(rèn)值，拋異常
cascade（級聯(lián)）：如果主表的記錄刪掉淤击，則從表中相關(guān)聯(lián)的記錄都將被刪除
set null：將外鍵設(shè)置為空
no action：什么都不做
連接查詢
-- 建表和數(shù)據(jù)S
create table students(
id int auto_increment primary key,
sname varchar(20)
);
create table subjects(
id int auto_increment primary key,
stitle varchar(20)
);
create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id) on delete cascade,
foreign key(subid) references subjects(id) on delete cascade
);
insert into subjects(stitle) values('語文');
insert into subjects(stitle) values('數(shù)學(xué)');
insert into subjects(stitle) values('英語');
insert into students(sname,sex) values('小明','男');
insert into students(sname,sex) values('小美','女');
insert into students(sname,sex) values('小壯','男');
insert into students(sname,sex) values('小敏','女');
insert into scores(stuid,subid,score) values(1,1,88);
insert into scores(stuid,subid,score) values(1,2,95);
insert into scores(stuid,subid,score) values(2,1,89);
insert into scores(stuid,subid,score) values(2,3,95);
insert into scores(stuid,subid,score) values(3,1,92);
insert into scores(stuid,subid,score) values(3,2,85);
insert into scores(stuid,subid,score) values(4,2,82);
insert into scores(stuid,subid,score) values(4,3,99);
/*查詢學(xué)生的編號床牧，姓名，科目的名稱遭贸，成績*/
select t1.id,t1.sname,t2.stitle,t3.score
from students t1,subjects t2,scores t3
where t3.stuid=t1.id and t3.subid=t2.id;
select t1.id,t1.sname,t2.stitle,t3.score
from scores t3
inner join students t1
on t3.stuid = t1.id
inner join subjects t2
on t3.subid = t2.id;
內(nèi)連接（INNER JOIN）
INNER JOIN ...表 ON ...條件
-- 查詢學(xué)生的姓名戈咳、平均分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
/*查詢學(xué)生的編號，姓名，科目的名稱著蛙，成績*/
-- 方法一
select t1.id,t1.sname,t2.stitle,t3.score
from students t1,subjects t2,scores t3
where t3.stuid=t1.id and t3.subid=t2.id;
-- 方法二
select t1.id,t1.sname,t2.stitle,t3.score
from scores t3
inner join students t1
on t3.stuid = t1.id
inner join subjects t2
on t3.subid = t2.id;
-- 方法三
select t1.id,t1.sname,t2.stitle,t3.score
from students t1
inner join scores t3
on t3.stuid = t1.id
inner join subjects t2
on t3.subid = t2.id;
-- 查詢學(xué)生的姓名删铃、平均分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
-- 查詢男生的姓名、總分
select students.sname,sum(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.sex='男'
group by students.sname;
-- 查詢科目的名稱踏堡、平均分
select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;
外聯(lián)結(jié)
1猎唁、左外連接（LEFT OUTER JOIN）
概述：指將左表的所有記錄與右表符合條件的記錄，返回的結(jié)果除內(nèi)連接的結(jié)果顷蟆，還有左表不符合條件的記錄渺氧，并在右表相應(yīng)列中填NULL。
2翩腐、右外連接（RIGHT OUTER JOIN）
概述：與左外連接相反御雕，指將右表的所有記錄與左表符合條件的記錄，返回的結(jié)果除內(nèi)連接的結(jié)果削樊，還有右表不符合條件的記錄豁生，并在左表相應(yīng)列中填NULL。
學(xué)生姓名來源于students表漫贞，科目名稱來源于subjects甸箱，分?jǐn)?shù)來源于scores表
當(dāng)查詢結(jié)果來源于多張表時(shí)，需要使用連接查詢
當(dāng)查詢結(jié)果來源于多張表時(shí)迅脐，需要使用連接查詢
關(guān)鍵：找到表間的關(guān)系芍殖，當(dāng)前的關(guān)系是
students表的id---scores表的stuid
subjects表的id---scores表的subid
select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
- 連接查詢分類如下：
- 表A inner join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中
- 表A left join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中，外加表A中獨(dú)有的數(shù)據(jù)谴蔑，未對應(yīng)的數(shù)據(jù)使用null填充
- 表A right join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中围小，外加表B中獨(dú)有的數(shù)據(jù)，未對應(yīng)的數(shù)據(jù)使用null填充
- 在查詢或條件中推薦使用“表名.列名”的語法
- 如果多個(gè)表中列名不重復(fù)可以省略“表名.”部分
- 如果表的名稱太長树碱，可以在表名后面使用' as 簡寫名'或' 簡寫名'肯适，為表起個(gè)臨時(shí)的簡寫名稱
用一個(gè)部門表，員工表演示一下內(nèi)外連接查詢
-- 部門表成榜，員工表
drop table if exists emp;
drop table if exists dept;
create table dept(
id int auto_increment primary key,
dname varchar(20)
);
create table emp(
id int auto_increment primary key,
ename varchar(20),
did int not null,
mgr int,
foreign key(did) references dept(id),
foreign key(mgr) references emp(id)
);
insert into dept(dname) values('研發(fā)部');
insert into dept(dname) values('人事部');
insert into dept(dname) values('財(cái)務(wù)部');
insert into emp(ename,did,mgr) values('老王',1,null);
insert into emp(ename,did,mgr) values('老張',1,1);
insert into emp(ename,did,mgr) values('老趙',1,1);
insert into emp(ename,did,mgr) values('小紅',2,3);
insert into emp(ename,did,mgr) values('小麗',2,3);
/*查詢員工額編號框舔，姓名，上級的姓名*/
-- 1
select t1.id,t1.ename,t2.ename
from emp t1,emp t2
where t1.mgr = t2.id;
-- 2inner join
select t1.id,t1.ename,t2.ename
from emp t1
inner join emp t2
on t1.mgr = t2.id;
-- 3左外連接
select t1.id,t1.ename,t2.ename
from emp t1
left join emp t2
on t1.mgr = t2.id;
-- 4右外連接
select t1.id,t1.ename,t2.ename
from emp t1
right join emp t2
on t1.mgr = t2.id;
Paste_Image.png
Paste_Image.png
自關(guān)聯(lián)
概述：指用表的別名實(shí)現(xiàn)表自身的連接赎婚。
在員工表中mgr列中代表了他們的上級
/*查詢員工額編號刘绣，姓名，上級的姓名*/
select 員工表.id,員工表.ename 員工,上級表.ename 上級 from emp 員工表,emp 上級表
where 員工表.mgr = 上級表.id;
Paste_Image.png
子查詢
查詢支持嵌套使用
查詢各學(xué)生的語文挣输、數(shù)學(xué)纬凤、英語的成績
-- 查詢各學(xué)生的語文、數(shù)學(xué)撩嚼、英語的成績
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id
where sub.stitle='語文' and stuid=stu.id) as 語文,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id
where sub.stitle='數(shù)學(xué)' and stuid=stu.id) as 數(shù)學(xué),
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id
where sub.stitle='英語' and stuid=stu.id) as 英語
from students stu;
查詢結(jié)果
內(nèi)置函數(shù)
字符串函數(shù)
-- 查看字符的ascii碼值ascii(str)停士，str是空串時(shí)返回0
select ascii('a');
-- 查看ascii碼值對應(yīng)的字符char(數(shù)字)
select char(97);
-- 拼接字符串concat(str1,str2...)
select concat(12,34,'ab');
-- 包含字符個(gè)數(shù)length(str)
select length('abc');
-- 截取字符串
-- left(str,len)返回字符串str的左端len個(gè)字符
-- right(str,len)返回字符串str的右端len個(gè)字符
-- substring(str,pos,len)返回字符串str的位置pos起len個(gè)字符
select substring('abc123',2,3);
-- 去除空格
-- ltrim(str)返回刪除了左空格的字符串str
-- rtrim(str)返回刪除了右空格的字符串str
-- trim([方向 remstr from str)返回從某側(cè)刪除remstr后的字符串str挖帘，
-- 方向詞包括both、leading恋技、trailing拇舀，表示兩側(cè)、左蜻底、右
select trim(' bar ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
-- 返回由n個(gè)空格字符組成的一個(gè)字符串space(n)
select space(10);
-- 替換字符串replace(str,from_str,to_str)
select replace('abc123','123','def');
-- 大小寫轉(zhuǎn)換骄崩，函數(shù)如下
-- lower(str)
-- upper(str)
select lower('aBcD');
數(shù)學(xué)函數(shù)
-- 求絕對值abs(n)
select abs(-32);
-- 求m除以n的余數(shù)mod(m,n)，同運(yùn)算符%
select mod(10,3);
select 10%3;
-- 地板floor(n)薄辅，表示不大于n的最大整數(shù)
select floor(2.3);
-- 天花板ceiling(n)要拂，表示不小于n的最大整數(shù)
select ceiling(2.3);
-- 求四舍五入值round(n,d)，n表示原數(shù)站楚，d表示小數(shù)位置脱惰，默認(rèn)為0
select round(1.6);
-- 求x的y次冪pow(x,y)
select pow(2,3);
-- 獲取圓周率PI()
select PI();
-- 隨機(jī)數(shù)rand()，值為0-1.0的浮點(diǎn)數(shù)
select rand();
-- 還有其它很多三角函數(shù)源请，使用時(shí)可以查詢文檔
日期時(shí)間函數(shù)
獲取子值枪芒，語法如下
year(date)返回date的年份(范圍在1000到9999)
month(date)返回date中的月份數(shù)值
day(date)返回date中的日期數(shù)值
hour(time)返回time的小時(shí)數(shù)(范圍是0到23)
minute(time)返回time的分鐘數(shù)(范圍是0到59)
second(time)返回time的秒數(shù)(范圍是0到59)
select year('2016-12-21');
日期計(jì)算彻况，使用+-運(yùn)算符谁尸，數(shù)字后面的關(guān)鍵字為year、month纽甘、day良蛮、hour、minute悍赢、second
select '2016-12-21'+interval 1 day;
獲取年%Y决瞳，返回4位的整數(shù)
獲取年%y，返回2位的整數(shù)
獲取月%m左权，值為1-12的整數(shù)
獲取日%d皮胡，返回整數(shù)
獲取時(shí)%H，值為0-23的整數(shù)
獲取時(shí)%h赏迟，值為1-12的整數(shù)
獲取分%i屡贺，值為0-59的整數(shù)
獲取秒%s，值為0-59的整數(shù)
/*日期-->字符串*/
select date_format('2017-10-20','%Y年%m月%d日')
/*字符串-->日期*/
select str_to_date('2017年10月20日','%Y年%m月%d日')
當(dāng)前日期current_date()
select current_date();
當(dāng)前時(shí)間current_time()
select current_time();
當(dāng)前日期時(shí)間now()
select now();
