5.模糊查詢 like % 通配符 _ 通配符
like “%b” 匹配以b結(jié)尾的任意長(zhǎng)度的字符串
like “b%” 匹配以b開頭的任意長(zhǎng)度的字符串
like “%b%” 匹配字符串中含有b的任意長(zhǎng)度的內(nèi)容
like “_b" 匹配總長(zhǎng)度為3個(gè)字符,任意內(nèi)容的字符串,并且以b結(jié)尾
like "b” 匹配總長(zhǎng)度為2個(gè)字符,任意內(nèi)容的字符串,并且以b開頭

單表查詢案例解析

創(chuàng)建表

create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一個(gè)部門一個(gè)屋子
depart_id int
);

#三個(gè)部門：教學(xué)，銷售，運(yùn)營(yíng)

insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學(xué)部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龍','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('張野','male',28,'20160311','operation',10000.13,403,3), #以下是運(yùn)營(yíng)部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;

查看表結(jié)構(gòu)

查看表數(shù)據(jù)

1. 查詢部門是sale的所有員工姓名:

select emp_name from employee where post="sale";

2. 部門是teacher , 收入大于10000的所有數(shù)據(jù)

select * from employee where post = "teacher" and salary > 10000;

3. 收入在1萬到2萬之間的所有員工姓名和收入

select emp_name,salary from employee where salary between 10000 and 20000;

4. 收入不在1萬到2萬之間的所有員工姓名和收入

范圍查詢:
between and 表示在一個(gè)連續(xù)的范圍內(nèi)查詢

in 表示在一個(gè)非連續(xù)的范圍內(nèi)查詢

select emp_name,salary from employee where salary not between 10000 and 20000;

5. 查看崗位描述為NULL的員工信息

select emp_name from employee where post_comment = null;
select emp_name from employee where post_comment = '';
select emp_name from employee where post_comment is null;

為空只能是is null ，= null = ‘’ 都不對(duì)，null是mysql的關(guān)鍵字，使用is來作比對(duì)

6. 查看崗位描述不為NULL的員工信息

select emp_name from employee where post_comment is not null;

7. 查詢收入是3000 ,4000 ,5000,8300 所有員工的姓名和收入

select emp_name,salary from employee where salary in(3000,4000,5000,8300);
select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300;

8. 查詢收入不是3000 ,4000 ,5000,8300 所有員工的姓名和收入

select emp_name,salary from employee where salary not in(3000,4000,5000,8300);

9. 以on結(jié)尾的員工名搜一下

select emp_name from employee where emp_name like "%on";
select emp_name from employee where emp_name like "ji%";
select emp_name from employee where emp_name like "_le_";

10. 統(tǒng)計(jì)員工一年的年薪

select concat(" 姓名: ",emp_name,"  收入:  ",salary) from employee;

計(jì)算年薪,可以在mysql中使用四則運(yùn)算符 + - * /

select concat(" 姓名: ",emp_name,"  收入:  ",salary * 12) from employee;
select concat_ws("  :  ",emp_name,salary*12 ) from employee;

以 “:” 將字段拼接一起

select concat_ws("  :  ",emp_name,salary*12 ) from employee;

以 “:” 將字段拼接一起，自己設(shè)個(gè)單獨(dú)的拼接符

11. 查詢部門的種類，distinct去重

distinct 返回唯一不同的值，去重，可以去除重復(fù)數(shù)據(jù)行

select distinct(post)  from employee;

group by 子句分組分類

group by 字段,對(duì)數(shù)據(jù)進(jìn)行分類, by后面接什么字段,select后面就搜什么字段
分組查詢:

分組查詢就是將查詢結(jié)果按照指定字段進(jìn)行分組，字段中數(shù)據(jù)相等的分為一組

語法格式:

group by 列名[having 條件表達(dá)式][with rollup]

說明:
having條件表達(dá)式:用來過濾分組后的數(shù)據(jù)
在所有記錄的最后加上一條數(shù)據(jù)，顯示select查詢時(shí)聚合函數(shù)的統(tǒng)計(jì)和計(jì)算結(jié)果

group by的使用: 只能查詢指定分組的字段

select sex from  employee group by sex;

group_concat 按照分組把對(duì)應(yīng)字段拼在一起;
group_concat(字段名)：列出每個(gè)分組指定字段的總數(shù)集合，每個(gè)信息之間用逗號(hào)隔開

select group_concat(emp_name),post from  employee group by post;

聚合函數(shù)

聚合函數(shù): 聚合函數(shù)不統(tǒng)計(jì)空值
聚合函數(shù)又叫組函數(shù)，通常對(duì)表中數(shù)據(jù)進(jìn)行統(tǒng)計(jì)和計(jì)算，一般結(jié)合分組group by來使用
用于統(tǒng)計(jì)和計(jì)算分組數(shù)據(jù)

常用的聚合函數(shù):
1、count(col)指定列的總行數(shù) 當(dāng)某一列有空值，不做統(tǒng)計(jì) 一般統(tǒng)計(jì)總行數(shù)用count(*)
2、max(col)指定列的最大值
3、min(col)指定列的最小值
4、sum(col)指定列求和
5、avg(col)指定列求平均值

使用group by 時(shí)，聚合函數(shù)可以直接搜，其他字段不能直接搜，要搜只能按by的字段分組列出來該分組中指定字段的總和
如下，emp_name沒有by，不能直接搜索

如要搜索，需要結(jié)合group_concat()

聚合函數(shù)可以直接搜

group by 后面如果是 unique唯一索引或主鍵其他字段都可以搜

一、count

1、count(1)：可以統(tǒng)計(jì)表中所有數(shù)據(jù)，不統(tǒng)計(jì)所有的列，用1代表代碼行，在統(tǒng)計(jì)結(jié)果中包含列字段為null的數(shù)據(jù)；

2、count(字段)：只包含列名的列，統(tǒng)計(jì)表中出現(xiàn)該字段的次數(shù)，并且不統(tǒng)計(jì)字段為null的情況；

3、count(*)：統(tǒng)計(jì)所有的列，相當(dāng)于行數(shù)，統(tǒng)計(jì)結(jié)果中會(huì)包含字段值為null的列；

二、count執(zhí)行效率

列名為主鍵，count(列名)比count(1)快；列名不為主鍵，count(1)會(huì)比count(列名)快；
如果表中多個(gè)列并且沒有主鍵，則count(1)的執(zhí)行效率優(yōu)于count(*)；
如果有主鍵，則select count(主鍵)的執(zhí)行效率是最優(yōu)的；如果表中只有一個(gè)字段，則select count(*)最優(yōu)。

如果某列字段中個(gè)別值是空值，直接計(jì)算平均值時(shí)，會(huì)不準(zhǔn)確，它只統(tǒng)計(jì)非空值的全部數(shù)據(jù)的平均數(shù) 非空值數(shù)據(jù)和除以非空值個(gè)數(shù)

針對(duì)空值設(shè)為默認(rèn)值，可以解決這個(gè)問題

如果遇到某列有空值，avg()是不做統(tǒng)計(jì)的，它只統(tǒng)計(jì)非空值的全部數(shù)據(jù)的平均數(shù) 非空值數(shù)據(jù)和除以非空值個(gè)數(shù)

如果想把空值的個(gè)數(shù)也算進(jìn)去，需要用ifnull函數(shù)，設(shè)個(gè)默認(rèn)值

ave(ifnull(height, 0))，默認(rèn)空值的數(shù)據(jù)為0.這樣統(tǒng)計(jì)出來的就是非空值全部數(shù)據(jù)的和除以非空值個(gè)數(shù)加上空值個(gè)數(shù)

對(duì)sum也適用
select sum(ifnull(height, 1.6)) from student;

    # count 統(tǒng)計(jì)總數(shù) *所有
    select count(*) from employee;
    # max  統(tǒng)計(jì)最大值
    select max(salary) from employee;
    # min  統(tǒng)計(jì)最小值
    select min(salary) from employee;
    # avg  統(tǒng)計(jì)平均值
    select avg(salary) from employee;
    # sum  統(tǒng)計(jì)總和
    select sum(salary) from employee;

查詢部門名以及各部門的平均薪資

select avg(salary),post from employee group by post;

查詢部門名以及各部門的最高薪資

select max(salary),post from employee group by post;

查詢部門名以及各部門的最低薪資

select min(salary),post from employee group by post;

查詢公司內(nèi)男員工和女員工的個(gè)數(shù)

select count(*),sex from employee group by sex;

查詢部門名以及部門包含的所有員工名字

select group_concat(emp_name),post from employee group by post;

可以group by 兩個(gè)字段,就可以同時(shí)搜索兩個(gè)字段。可以group by 多個(gè)字段。by誰搜誰

select emp_name,post from employee group by post ,emp_name;

三.having

在數(shù)據(jù)分類分組之后,對(duì)數(shù)據(jù)進(jìn)行二次過濾,一般配合group by來使用的;
找出各部門平均薪資,并且大于10000

select post , avg(salary) from  employee group by post having avg(salary) > 10000

1.查詢各崗位內(nèi)包含的員工個(gè)數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個(gè)數(shù)

select post , group_concat(emp_name), count(*) from employee group by post having count(*) < 2;

2.查詢各崗位平均薪資小于10000的崗位名、平均工資

select post , avg(salary) from employee group by post having avg(salary) < 10000

3.查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資

select post, avg(salary) from employee group by post having avg(salary) between 10000 and 20000

select post, avg(salary) from employee group by post having avg(salary) > 10000 and  avg(salary) < 20000;

四.order by 排序

按照某字段排序?？梢越佣鄠€(gè)字段，前面的排序相同，再按后面字段來排序
order by age asc (升序) order by age desc (降序)
按照年齡從小到大排序，默認(rèn)升序

select * from employee order by age;

按照年齡從大到小排序

select * from employee order by age desc;

查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序

select * from employee order by age asc ,  hire_date desc;

查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資升序排列

select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc

查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資降序排列

select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc

五.limit 限制查詢條數(shù) (應(yīng)用在分頁)

limit m,n m代表從第幾條數(shù)據(jù)開始查, n 代表查幾條 m=0 代表的是第一條數(shù)據(jù)，默認(rèn)m從0開始

select * from employee limit 0,10   # 0代表的是第一條數(shù)據(jù)開始
select * from employee limit 10,10  # 10代表的是第十一條數(shù)據(jù)
select * from employee limit 20,10  # 20代表的是第二十一條數(shù)據(jù)

搜索前10條

limit + num num => 搜索的條數(shù)據(jù)

select * from employee limit 1

搜索一條數(shù)據(jù)

搜索這個(gè)表里面最后一條數(shù)據(jù)

select * from employee order by id desc limit 1

搜索這個(gè)表里面最后五條數(shù)據(jù)

select * from employee order by id desc limit 5

六.mysql 當(dāng)中可以使用正則表達(dá)式 (不推薦,效率低)

select * from employee where  emp_name regexp ".*on$";  # mysql中無法識(shí)別?

新版的mysql8.0支持.*?

select * from employee where  emp_name regexp "^程.*";
select * from employee where  emp_name regexp "^程.*金";

查詢姓名以程開頭的記錄

新版的mysql支持.*?

mysql多表查詢操作

連接查詢:
連接查詢可以實(shí)現(xiàn)多個(gè)表的查詢，當(dāng)查詢的字段數(shù)據(jù)來自于不同的表，就可以使用連接查詢

連接查詢分為:
內(nèi)連接查詢 inner join 取的就是兩張表的交集
左連接查詢 left join 左外連接：左邊的是主表，左表數(shù)據(jù)全部顯示，右表顯示符合ON后的條件的數(shù)據(jù)，不符合的用NULL代替。
右連接查詢 right join 右外連接：右邊邊的是主表，右邊表數(shù)據(jù)全部顯示，左邊表顯示符合ON后的條件的數(shù)據(jù)，不符合的用NULL代替。
全連接查詢 union 全連接查詢的是左表所有的數(shù)據(jù) 加上右表所有的數(shù)據(jù) 并去重。

多表查詢案例解析

建表

create table department(
id int,
name varchar(20) 
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

插入數(shù)據(jù)

insert into department values
(200,'技術(shù)'),
(201,'人力資源'),
(202,'銷售'),
(203,'運(yùn)營(yíng)');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

# 查詢:
# 一.找出平均年齡大于25歲以上的部門
# 二.查看技術(shù)部門員工姓名
# 三.查看哪個(gè)部門沒員工
# 四.查詢大于平均年齡的員工名與年齡
# 五.把大于其本部門平均年齡的員工名和姓名查出來
# 六.查詢每個(gè)部門最新入職的那位員工 # 利用上一套數(shù)據(jù)表進(jìn)行查詢;
# 七.帶EXISTS關(guān)鍵字的子查詢

1.內(nèi)聯(lián)接 : inner join

兩表或者多表之間,把滿足條件的所有數(shù)據(jù)查詢出來 (多表之間共同擁有的數(shù)據(jù)會(huì)被查詢出來)
兩表聯(lián)查
select 字段 from 表1 inner join 表2 on 必要的關(guān)聯(lián)條件
多表聯(lián)查
select 字段 from 表1 inner join 表2 on 必要的關(guān)聯(lián)條件1 inner join 表3 on 必要的關(guān)聯(lián)條件2

select * from employee inner join department on employee.dep_id = department.id;

內(nèi)連接查詢：查詢的是的就是兩張表的交集

as 起別名

select * from employee as e inner join department as d on e.dep_id = d.id;

也可以省略as (不推薦)

select * from employee e inner join department d on e.dep_id = d.id;

where 寫法默寫是內(nèi)聯(lián)接( 等同于inner join )

select * from employee,department where employee.dep_id = department.id;
select * from employee as e ,department as d where e.dep_id = d.id;

2.外聯(lián)接 : left join左聯(lián)接 / right join 右聯(lián)接

(1)left join左聯(lián)接

以左表為主,右表為輔,完整查詢左表所有數(shù)據(jù),右表沒有的數(shù)據(jù)補(bǔ)null

select * from employee left join department on employee.dep_id = department.id;

右表department沒有id為204的字段，所以以null補(bǔ)充

(2)right join右聯(lián)接

以右表為主,左表為輔,完整查詢右表所有數(shù)據(jù),左表沒有的數(shù)據(jù)補(bǔ)null

select * from employee right join department on employee.dep_id = department.id;

左表沒有dep_id為203的字段，右連接查詢，所以左邊表沒有的數(shù)據(jù)以null填充

(3)全聯(lián)接 : union

全連接查詢的是左表所有的數(shù)據(jù) 加上右表所有的數(shù)據(jù)

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;

左連接與右連接的并集

3.子查詢

子查詢: 嵌套查詢
(1) sql語句當(dāng)中又嵌套了另外一條sql,用括號(hào)()進(jìn)行包裹,表達(dá)一個(gè)整體
(2) 一般用在from子句,where子句... 身后,表達(dá)一個(gè)條件或者一個(gè)表
(3) 速度快慢: 單表查詢 > 聯(lián)表查詢 > 子查詢;

一.找出平均年齡大于25歲以上的部門

(1) where

select 
    d.id,d.name
from 
    employee as e ,department as d
where
    e.dep_id = d.id
group by 
    d.id,d.name
having
    avg(e.age) > 25

(2) inner join

select 
    d.id,d.name
from 
    employee as e inner join department as d on e.dep_id = d.id
group by 
    d.id,d.name
having
    avg(e.age) > 25

(3) 子查詢
1.先找出平均年齡大于25歲的部門id
select dep_id from employee group by employee.dep_id having avg(age)>25; # 201 202
2.通過部門的id找部門的名字
select name from department where id in (201,202);
3.綜合拼接:
select id , name from department where id in (select dep_id from employee group by employee.dep_id having avg(age)>25);

二.查看技術(shù)部門員工姓名

(1) 普通的where 查詢

select 
    e.id,e.name
from
    employee as e,department as d
where
    e.dep_id = d.id
    and
    d.name = "技術(shù)"

(2) inner join

select 
    e.id,e.name
from
    employee as e inner join department as d on e.dep_id = d.id 
where
    d.name = "技術(shù)"

(3)子查詢
(1) 找技術(shù)部門對(duì)應(yīng)的id

select id from department where name = "技術(shù)";

(2) 通過id找員工姓名

select name from employee where dep_id = 200;

(3) 綜合拼接

select id,name from employee where dep_id = (select id from department where name = "技術(shù)");

三.查看哪個(gè)部門沒員工

聯(lián)表寫法

select
    d.id,d.name
from
    department as d left join employee as e on d.id = e.dep_id
where
    e.dep_id is null

1.找員工在哪些部門 (200 201 202 204)

select dep_id from employee  group by dep_id

2.把不在該部門的員工找出來

select  id  from department where id not in (200,201,202,204);

3.綜合拼接

select  id,name  from department where id not in (select dep_id from employee  group by dep_id);

四.查詢大于平均年齡的員工名與年齡
假設(shè)已經(jīng)知道了平均年齡;

select name,age from employee where age > 30;

計(jì)算平均年齡

select avg(age) from employee;

綜合拼接

select name,age from employee where age > (select avg(age) from employee);

五.把大于其本部門平均年齡的員工名和姓名查出來

1.先計(jì)算本部門的平均年齡是多少

select dep_id , avg(age) from employee  group by dep_id;    
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
|    200 |  18.0000 |
|    201 |  43.0000 |
|    202 |  28.0000 |
|    204 |  18.0000 |
+--------+----------+

2.把查詢的各部門平均年齡和employee進(jìn)行聯(lián)表,變成一張大表,最后做單表查詢

select 
    *
from
    employee as t1 inner join (1號(hào)查詢出來的數(shù)據(jù)) as t2 on t1.dep_id = t2.dep_id

3.綜合拼裝

select 
    *
from
    employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id

4.最后做一次單表查詢,讓age > 平均值

select 
    *
from
    employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id
where 
    age >avg_age

使用連接查詢，變成一張大表。單表用連接查詢，要?jiǎng)?chuàng)建別名，不然查詢失敗
子查詢作為一張表，必須創(chuàng)建別名

字段也要用別名

作比較的時(shí)候不能用avg(age)，小括號(hào)是一種特殊的符號(hào)，不能作比較

別名都創(chuàng)建好，可以正常查詢

六.查詢每個(gè)部門最新入職的那位員工 # 利用上一套數(shù)據(jù)表進(jìn)行查詢;

employee

+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name   | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |    max_date
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使              |              |    7300.33 |    401 |         1 | 2017-03-01
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 | 2015-03-02
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 | 2015-03-02 
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 | 2015-03-02
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 | 2015-03-02
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 | 2015-03-02
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 | 2015-03-02
|  8 | 成龍       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 | 2015-03-02
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 | 2017-01-27
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 | 2017-01-27
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 | 2017-01-27
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 | 2017-01-27
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 | 2017-01-27
| 14 | 張野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 | 2016-03-11
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 | 2016-03-11
| 16 | 程咬銀     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 | 2016-03-11
| 17 | 程咬銅     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 | 2016-03-11
| 18 | 程咬鐵     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 | 2016-03-11
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+

1.找各部門的最新入職的時(shí)間

select post,max(hire_date) as max_date from employee group by post
+-----------------------------------------+------------+
| post                                    | max_date   |
+-----------------------------------------+------------+
| operation                               | 2016-03-11 |
| sale                                    | 2017-01-27 |
| teacher                                 | 2015-03-02 |
| 老男孩駐沙河辦事處外交大使             | 2017-03-01 |
+-----------------------------------------+------------+

2.把子查詢搜索出來的結(jié)果作為一張表和employee這個(gè)表做聯(lián)表,把max_date拼接在employee這個(gè)表中,變成一張大表,最后做一次單表查詢

select 
    *
from
    employee as t1 inner join (1號(hào)數(shù)據(jù)) as t2 on t1.post = t2.post
where
    t1.hire_date = t2.max_date

3.綜合拼裝

select 
    emp_name , max_date
from
    employee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post
where
    t1.hire_date = t2.max_date

七.帶EXISTS關(guān)鍵字的子查詢

exists 關(guān)鍵字 , 表達(dá)存在 , 應(yīng)用在子查詢中
如果內(nèi)層sql , 能夠查到數(shù)據(jù), 返回True , 外層sql執(zhí)行相應(yīng)的sql語句
如果內(nèi)層sql , 不能查到數(shù)據(jù), 返回False , 外層sql不執(zhí)行sql語句

select * from employee where exists (select * from employee where id = 1);
select * from employee where exists (select * from employee where id = 100000);

總結(jié):
子查詢可以單獨(dú)作為臨時(shí)數(shù)據(jù),作為一張表或者一個(gè)字段,通過()進(jìn)行包裹,表達(dá)一個(gè)整體;
一般用在from,where,select.子句的后面
可以通過查詢出來的數(shù)據(jù)和另外的表做聯(lián)表變成更大一張表,
最后做單表查詢,達(dá)到目的;

到此這篇關(guān)于mysql單表查詢、多表查詢、分組查詢、子查詢等案例詳細(xì)解析的文章就介紹到這了,更多相關(guān)mysql單表查詢、多表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

国产无遮挡裸体免费直播视频,久久精品国产蜜臀av,动漫在线视频一区二区,欧亚日韩一区二区三区,久艹在线 免费视频,国产精品美女网站免费,正在播放 97超级视频在线观看,斗破苍穹年番在线观看免费,51最新乱码中文字幕

mysql單表查詢、多表查詢、分組查詢、子查詢案例解析

目錄

mysql查詢操作

where 條件的使用