資料庫查詢操作
關鍵字:select
select 結果集 from 資料源 [where 條件] [group by(分組)] [having 條件] [order by(排序) asc|desc] [limit(限制) s,n]
結果集(欄位,*代表所有欄位)；s start 從第幾條開始,查詢n條資料,s不寫默認從第一條開始，
(1)欄位運算式
select 除了可以做查詢,還可以呼叫函式,也可以用來做計算，
select rand();
(2)from子句
from 子句后面跟的是資料源，
(3)dual表
dual不是一個真實的表,只是一個語法；?
dual的主要作用是為了保證select陳述句的完整性而設計的，
(4)where子句
where子句的作用,篩選過濾資料；
一般跟在資料源的后面,位置不能亂放，
(5)is null | is not null
篩選資料是否為慷訓不為空；
放在where的后面,是where的判斷條件之一，
(6)between | not between
篩選資料的范圍,用在數字集上
select * from user where uid between 2 and 4; select * from user where uid>1 and uid<5;
(7)MySQL中的運算子
a.算數運算子
+ - * / % ++ --
b.比較運算子
> < >= <= != (<>)
c.邏輯運算子
and 與
or 或
not 非
(8).聚合函式
max() 最大值
min() 最小值
sum() 求和
avg() 平均值
count() 計數
聚合函式只能寫在結果集中，
(9).通配符
_ ：一次通配一個字符
?
% ：一次通配多個字符
(10).模糊查詢(like)
作用:查找長字串中的某一個關鍵字
select * from user where passwd like '_介石'; ? select * from user where passwd like '%介石%';
(11).分組查詢
關鍵字:group by
將相同的值分為N個類別,使得統計變的更加的便捷，
create table stuinfo( sid int auto_increment primary key comment'學號', sname char(64) comment'姓名', sex enum('女','男'), age tinyint unsigned, city char(64) ); insert into stuinfo values(null,'小馬',2,20,'杭州'),(null,'小王',2,21,'潮州'),(null,'小林',2,18,'大連'),
(null,'小強',2,30,'宿遷'),(null,'小軍',2,80,'仙桃'); insert into stuinfo values(null,'小梅',1,18,'杭州'),(null,'小芳',1,34,'潮州'),(null,'小寶',2,18,'大連'),
(null,'小天',1,21,'宿遷'),(null,'小楊',1,11,'仙桃'); create table stumarks( stuno int comment'學號', ch float comment'語文', ma float comment'數學' ); insert into stumarks values(1,88,99),(2,100,0),(3,0,0),(4,80,59),(5,100,100); insert into stumarks values(6,88,99),(7,100,0),(8,10,0),(9,80,59),(10,100,100);
分組查詢
-- 根據性別分組查詢 （得不到想要的結果） select * from stuinfo group by sex; -- 根據性別+城市 select * from stuinfo group by sex,city; ? -- group_concat(),concat是級聯(合并)；得到根據性別分好的兩組姓名 select group_concat(sname) from stuinfo group by sex;
(12).回溯統計(了解)
關鍵字:with rollup，不常用，放在group by 的后面，在統計的基礎上在統計一次，
select *,group_concat(sname) from stuinfo group by sex with rollup;
(13).having條件
where:設定篩選條件,只能在group by前面；
having:設定篩選條件,要求條件匹配的欄位必須在結果集當中,在group by后面，
select * from stuinfo where age > 18; # 正確 ? select * from stuinfo having age > 18; # 正確 ? select sname from stuinfo where age > 18; # 正確 ? select sname,age from stuinfo having age > 18; # 錯誤 ? select * from stuinfo where age>18 group by sex; # 正確 ? select * from stuinfo having age>18 group by sex; # 錯誤 ? select sname,age from stuinfo where city='杭州' group by sex having age>18; # 正確
(14).order by排序[asc|desc]
order by 添加在group by 的后面,只是負責排序?
1.asc,升序排列,默認值
2.desc,降序排列?
select * `count` from stuinfo where age>18 order by sid desc;
(15).limit限制
limit 在分頁的時候是經常使用的；limit 在order by 的后面,是最后一個子句，
1. limit 10 #默認從第一條開始,向后取10條
2. limit 10,10 #從第10條開始向后取10條
?select * from stuinfo limit 3; ? select * from stuinfo limit 0,3; ? select * from stuinfo limit 5,5; ? select * from stuinfo where age>18 order by sid desc limit 3;
(16).查詢資料中的選項
distinct:去重，
select distinct city from stuinfo;
(17).on duplicate key update
on duplicate key update 有重復則更新，配合insert使用的一組工具型sql子句?，
假如資料庫當中有了已經有了一個id為1的物體,我還箱在插入id為1的物體,一定不能成功，?
如果表中沒有id為11的物體則插入,如果有,則執行指定的更新：
insert into stuinfo values(11,'四毛',2,20,'杭州') on duplicate key update sname='四毛',sex=1;
資料庫聯合查詢
關鍵字：union
1.all:可寫可不寫；2.distinct:去重，
-- 查找大連的男生和杭州的女生 select * from stuinfo where city='大連' and sex=2 union select * from stuinfo where city='杭州' and sex=1; select * from stuinfo where (city='大連' and sex=2) or (city='杭州' and sex=1);
使用union的要求:
1.兩邊的欄位數要相同；
2.欄位名和資料型別可以不一致,按照左邊的表欄位回傳，
select sid,sname,age from stuinfo union select * from stumarks; -- 會報錯，兩邊欄位數不相等 select sid,sname,age from stuinfo union select * from stumarks;
男生的年齡降序,女生的年齡升序，
使用union分段排序一定不會成功,必須添加limit,limit的值要足夠大(100w就可以了)，為什么給100就可以了:每張極限效率在80W左右，
(select * from stuinfo where sex=2 order by age desc limit 999999999) union (select * from stuinfo where sex=1 order by age limit 999999999);
多表查詢
分類
1.內連接 inner join
2.外連接 outer join
左外連接 left join
右外連接 right join
3.交叉連接 cross join
4.自然連接 natural join
(1).內連接(inner join)
連接的關鍵字應當寫在表和表之間；
給表起別名 stuinfo as a == stuinfo a,as默認可以省略；
給兩張變建立關聯,on a.sid = b.stuno；
select * from stuinfo a inner join stumarks b on a.sid = b.stuno;
inner join 可以直接寫成 join
select * from stuinfo a join stumarks b on a.sid = b.stuno;
內連接的特點:內連接在關聯表兩邊的資料完全對等的情況下才回傳,任何一個表的資料不完整,則不顯示，
(3).外連接(outer join)
a.左外連接?
特點:以左邊表的資料為基準,只要左邊表有該條資料,右表資料為空自動填補Null
select * from stuinfo a left outer join stumarks b on a.sid = b.stuno;
b.右外連接
特點:以右邊表的資料為基準,只要右邊表有該條資料,左表資料為空自動填補Null
select * from stuinfo a right outer join stumarks b on a.sid = b.stuno;?
(4).交叉連接(cross join)
-- 交叉連接的回傳結果和內連接一致 select * from stuinfo a cross join stumarks b on a.sid=b.stuno; ? -- 笛卡爾積寫法 select * from `stuinfo`,`stumarks`;
(5).自然連接(natural join)
自動查找關聯欄位，
1.自動判斷條件,依據同名欄位；
2.如果沒有同名欄位,回傳的是笛卡爾積；
3.自動回傳并整理結果：a.連接的欄位回傳一個；b.連接的欄位放前面，
1). natural join 自然內連接
2). natural left join 自然左外連接
3). natural right join 自然右外連接
select * from stuinfo a natural join stumarks b;
(6).using()
using()是一個MySQL內置函式，作用:自動連接關聯欄位，
select * from stuinfo left join stumarks using(`sid`);
練習
-- 1.顯示地區和每個地區參加幼ò訃試的人數,按照地區人數降序排列 /* count(ch) as `count`:這里的count為什么起別名,函式只可以寫在結果集當中,排序需要使用count的結果,別名可以避免這個規則. count不統計Null */ select city,count(ch) as `count` from stuinfo left join stumarks using(`sid`) group by `city` order by `count` desc; -- 2.顯示男生和女生人數 select sum(sex=1) 女,sum(sex=2) 男 from stuinfo; select sex,count(sex) from stuinfo group by sex; select sex,count(sex) from stuinfo where sex=1 union select sex,count(sex) from stuinfo where sex=2; -- 3.顯示每個地區的男生人數和女生人數,總人數 select sum(sex=1) 女,sum(sex=2) 男,count(sex) 總人數 from stuinfo group by city;
子查詢
查詢陳述句中包涵一個查詢，外面的查詢是父查詢,里面的就是子查詢，子查詢是為父查詢提供查詢條件，
查詢數學成績大于80分的學生資訊(不包含成績)；
子查詢回傳的值應該是單一的一個欄位；
在子查詢能用in的,絕對不用=，
select * from stuinfo where sid in (select sid from stumarks where ma > 80); ? select a.* from stuinfo a left join stumarks using(sid) where ma>80;?
查找數學成績最高分的學生資訊
select max(b.ma),a.* from stuinfo a left join stumarks b using(sid);
寫子查詢,先理清楚關系:回傳數學最高分的學生sid,查找學生資訊
select * from stuinfo where sid in (select sid from stumarks where ma in (select max(ma) from stumarks));
(1)in | not in
在查詢中,回傳的結果是一個集合,等于號會發生錯誤,這時是用in 和 not in來解決集合問題
-- 查詢數學成績不及格的學生 select * from stuinfo where sid not in (select sid from stumarks where ma>=60); ? select * from stuinfo where sid in (select sid from stumarks where ma<60); ? -- 查詢沒有參加數學考試的學生 select * from stuinfo where sid in (select sid from stumarks where ma is null); ? -- 查詢沒有參加考試的學生 select * from stuinfo where sid in (select sid from stumarks where ma is null or ch is null);
(2)some,all,any
some和any是一樣的,表示的是一些,類似于in，
!=some 和 !=any != not in； !=all = in
-- 查詢數學成績不及格的學生 select * from stuinfo where sid =some (select sid from stumarks where ma<60); ? select * from stuinfo where sid =any (select sid from stumarks where ma<60); select * from stuinfo where sid =all (select sid from stumarks where ma<60); ?-- 查不出 ? -- 查詢數學成績90分以上的學生 select * from stuinfo where sid !=some (select sid from stumarks where ma<90); -- 查不出 select * from stuinfo where sid !=any (select sid from stumarks where ma<90); ?-- 查不出 ? select * from stuinfo where sid !=all (select sid from stumarks where ma<90); ? select * from stuinfo where sid not in (select sid from stumarks where ma<90);
主要記住in和 not in即可，
(3)exists | not exists
回傳bool值
-- 如果有人數學成績達到100分,則顯示所有人的資訊 select * from stuinfo where exists(select sid from stumarks where ma=100); ? -- 上面的條件取反（找到有不成立的條件） select * from stuinfo where not exists(select sid from stumarks where ma < 0);
https://blog.csdn.net/nihui123/article/details/90452750
https://www.runoob.com/mysql/mysql-database-export.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/64459.html
標籤：MySQL
上一篇：資料庫連接池的原理？連接池使用什么資料結構實作？實作連接池？
下一篇：InnoDB基礎