Mysql查詢集合

• 2019 年 10 月 6 日
• 筆記

無論是風裡，還是在雨里，我都在這裡守候著你～

數據準備及回憶：

進入資料庫：

mysql -uroot -p123456

創建一個資料庫：

create database student charset = utf8;

進入資料庫：

use student;

查看當前所在資料庫：

select database();

創建classinfo表：

create table classinfo(

classid int unsigned primary key auto_increment not null,

classname varchar(20) default ''

);

創建studentinfo表：

添加一個is_delete列，表示是否刪除。

create table studentinfo(

studentid int unsigned primary key auto_increment not null,

studentname nvarchar(20),

classid int unsigned,

studentsex enum('男','女','保密') default '保密',

studentage tinyint unsigned,

sadress varchar(255),

is_delete bit default 0

);

插入數據：

insert into classinfo values

(0,'Mysql一班'),

(0,'Mysql二班'),

(0,'Mysql三班');

insert into studentinfo values

(0,'張三',1,'男',18,'武漢',0),

(0,'小花',2,'女',20,'長沙',0),

(0,'李四',3,'男',21,'上海',0),

(0,'小貓',1,'女',18,'北京',0),

(0,'王五',2,'男',19,'南京',0),

(0,'小狗',3,'女',25,'成都',0);

查詢

–查詢所有欄位

select * from studentinfo;

select * from classinfo;

–查詢指定欄位

select studentname,studentsex from studentinfo;

select classname from classinfo;

–使用as給欄位添加別名

select studentname as 姓名,studentsex as 性別 from studentinfo;

select classname as 班級名稱 from classinfo;

–使用as給表添加別名

select stu.studentname,stu.studentsex from studentinfo as stu;

select cla.classname from classinfo as cla;

–查詢性別並去除重複的

select studentsex from studentinfo;

–查出所有的，如果我只想得到男和女和保密可以這樣寫來消除重複行

select distinct studentsex from studentinfo;

條件查詢

— 比較運算符

— 查詢年齡等於18的人

select * from studentinfo where studentage = 18;

— 查詢年齡不等於18的人

select * from studentinfo where studentage != 18;

或者

select * from studentinfo where studentage <> 18;

— 查詢年齡小於20

select * from studentinfo where studentage < 18;

— 查詢年齡大於等於20的

select * from studentinfo where studentage >= 18;

–邏輯運算符and，or，not

— 查詢18到20之間的

select * from studentinfo where studentage >= 18 and studentage < 20;

— 查詢20歲以下，或者性別為女的

select * from studentinfo where studentage < 20 or studentsex = '女';

— 查詢不在年齡小於20中的

select * from studentinfo where not studentage < 20;

— 優先順序

直接加括弧，每個語言都有優先順序，記住？不可能的，加括弧就好了。

模糊查詢

— like

— % 替換一個或者多個

— _ 替換一個

— 查新名字中含有小的人

select * from studentinfo where studentname like '%小%';

— 查詢名字中以小開頭的人

select * from studentinfo where studentname like '小%';

— 查詢名字是兩個字的人

select * from studentinfo where studentname like '__';

— 查詢所有

select * from studentinfo where studentname like '%';

— 查詢名字至少有兩個字的

select * from studentinfo where studentname like '__%';

— rlike 正則匹配

— 還是查詢以小開頭的

select * from studentinfo where studentname rlike '^小.*';

— 查詢以小開頭以花結尾的

select * from studentinfo where studentname rlike '^小.*花$';

範圍查詢

–in

— 查詢id為1,2,3的

select * from studentinfo where studentid in (1,2,3);

— 查詢id不為1,2,3的

select * from studentinfo where studentid not in (1,2,3);

— between and

— 查詢年齡18到20之間的(包含18和20)

select * from studentinfo where studentage between 18 and 20;

— not between and

— 查詢年齡18到20之間的(包含18和20)

select * from studentinfo where studentage not between 18 and 20;

— 判斷是否為空

— 查詢名字為空的（這裡沒有數據，可以添加一個）

select * from studentinfo where studentname is null;

— 查詢名字不為空的

select * from studentinfo where studentname is not null;

排序

— order by

— asc 從小到大排序

— desc 從大到小排序

— 查詢所有按照從小到大排序

— asc可以省略，默認就是從小到大

select * from studentinfo order by studentage;

select * from studentinfo order by studentage asc;

— 從大到小排序

select * from studentinfo order by studentage desc;

— order by 多個欄位

— 如果有兩個年齡相同的，他會默認用主鍵排序，我想讓他用班級id排序

select * from studentinfo order by studentage asc,classid desc;

聚合函數

–總數 count

–查詢男性有多少人

select count(*) as 男的 from studentinfo where studentsex = 1;

–最大值 max

–查詢年齡的最大值

select max(studentage) as 最大年齡 from studentinfo;

–最小值 mix

–查詢年齡的最小值

select mix(studentage) as 最小年齡 from studentinfo;

–求和 sum

–查詢所有年齡的和

select sum(studentage) as 年齡和 from studentinfo;

–平均值avg

–查詢所有年齡的平均值

select avg(studentage) as 年齡平均值 from studentinfo;

–或者

select sum(studentage)/count(*) as 年齡平均值 from studentinfo;

–如果我想四捨五入，保留一位小數

–round(111.444,1) 輸出 111.4

select round(avg(studentage),1) as 年齡平均值 from studentinfo;

分組

–group by

–按照性別分組,查詢男，女各有幾個人。

select count(*),studentsex from studentinfo group by studentsex;

–只查詢男的人數

select count(*),studentsex from studentinfo where studentsex = 1 group by studentsex;

–查詢男，女各有幾個人,並且顯示出姓名

select count(*),studentsex,group_concat(studentname) from studentinfo group by studentsex;

count(*)

studentsex

group_concat(studentname)

3

男

張三,李四,王五

3

女

小花,小貓,小狗

–查詢男，女各有幾個人,並且顯示出姓名和年齡

select count(*),studentsex,group_concat(studentname,studentage) from studentinfo group by studentsex;

count(*)

studentsex

group_concat(studentname)

3

男

張三18,李四21,王五19

3

女

小花20,小貓18,小狗25

–但是查詢多了會連在一起這樣寫

select count(*),studentsex,group_concat("name_",studentname,"id_",studentage) from studentinfo group by studentsex;

–自己寫看效果

–對分組進行條件查詢

–查詢男，女人數，並且平均年齡大於20

select count(*),studentsex,group_concat(studentname),avg(studentage) from studentinfo group by studentsex having avg(studentage)>20;

分頁

–limit

–查詢全部只提取前3行數據

select * from studentinfo limit 3;

–條件查詢顯示男的2行數據

select * from studentinfo where studentsex=1 limit 2;

–只查詢了前面的，剩餘的怎麼查詢？

select * from studentinfo limit 0,3; 第一頁數據

select * from studentinfo limit 3,3; 第二頁數據

0和1表示從第幾個開始。

所有查詢 limit是在最後的。

–查詢所有女性，按照年齡從大到小，並且顯示2條數據。

select * from studentinfo where studentsex=2 order by studentage desc limit 2;

連接查詢

–兩個表聯查

–內連接

select * from studentinfo inner join classinfo;

我們發現，數據亂了。但是實現了雙表聯查。這不是我們想要的。我們把數據對應起來。

select * from studentinfo inner join classinfo on studentinfo.classid=classinfo.classid;

–如果我不想顯示這麼多數據，我只想要姓名和班級

select studentinfo.studentname,classinfo.classname from studentinfo inner join classinfo on studentinfo.classid=classinfo.classid;

–名字太長了給表格別名

select s.studentname,c.classname from studentinfo as s inner join classinfo as c on s.classid=c.classid;

所謂內連接就是找到條件，這裡是classid中相同的顯示出來，如果在studentinfo表中含有classid為4，而classinfo表中沒有4就不會顯示出來。

–外鏈接 left join

–外鏈接分為左連接和右鏈接

–左連接

–左連接以左表為主，相當於先查詢左表的全部內容，右表根據相關欄位匹配，沒有的以空值匹配。

–這裡兩個表都是相關的數據，所以和內連接沒有差別。

select s.studentname,c.classname from studentinfo as s left join classinfo as c on s.classid=c.classid;

–右鏈接 right join

select s.studentname,c.classname from studentinfo as s right join classinfo as c on s.classid=c.classid;

自關聯

–什麼是自關聯？

–自己關聯自己。

–為什麼關聯自己？

–比如省市區，表結構都是一樣的，如果你創建三個表，比較麻煩，都創建一個表，用自連接就可以完成。

這裡補充一個知識，我添加數據不可能去一個一個添加吧，

可以直接在網上下載一個sql腳本，直接執行sql腳本來添加數據，怎麼執行sql腳本？

source sql文件名;

比如我寫一個創建表的sql：

創建一個txt文件，寫上

create table City(

cid int,

cname varchar(20),

pid int

);

修改為sql文件，

source C:/WINDOWS/Temp/city.sql;即可

可能會報錯，出現沒有許可權訪問，所以需要把sql文件放在C:WINDOWSTemp下即可

我們添加數據的時候就可以用這種方式來添加。

–自關聯還是用的內鏈接，數據可以去網上下載，很多的。

–查詢湖北省的所有區

select * from City c1 inner join City c2 on c1.cid = c2.pid having c1.cname="湖北省"

即可。

子查詢

–select 中套select 就是子查詢

–在查詢的時候先查詢子條件中的select，把子條件當做一個條件查詢。

–查詢年齡最大的

select * from studentinfo where studentage =(select max(studentage) from studentinfo)

–像剛剛的自關聯也可以用子查詢解決

select * from studentinfo where pid = (select cid from studentinfo where cname = "湖北省")