# ### part1 單表查詢
# sql 查詢陳述句的完整語法
''' select .. from .. where .. group by .. having .. order by .. limit .. '''
# 一.where 條件的使用
"""功能:對表中的資料進行篩選過濾"""
"""
語法:
1.判斷的符號:
= > < >= <= != <> 不等于
2.拼接條件的關鍵字
and or not
3.查詢的區間范圍值 between
between 小值 and 大值 [小值,大值] 查詢兩者之間這個范圍的所有資料
4.查詢具體某個值的范圍 in
in(1,-9,-10,"a") 指定范圍
5.模糊查詢 like "%" 通配符
like "%a" 匹配以a結尾的任意長度的字串
like "a%" 匹配以a開頭的任意長度的字串
like "%a%" 匹配含有a字母的任意長度字串
like "_a" 個數一共2個字符,必須以a結尾,前面這個字符隨意
like "a__" 個數一共3個字符,必須以a開頭,后面這個兩字符隨意
"""
# (1) 單條件的查詢
# 查詢部門是sale的所有員工姓名:
select emp_name from employee where post = "sale";
# (2) 多條件的查詢
# 部門是teacher,收入大于10000的所有資料
select * from employee where post = "teacher" and salary > 10000;
# (3) 關鍵字between .. and
# 收入在1萬到2萬之間的所有員工姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不在1萬到2萬之間的所有員工姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) null關鍵字 在搜索的時候,要用is進行判定,不能用=
# 查詢 post_comment 是空的NULL 所有資料
select * from employee where post_comment = NULL 資料是空,搜索不到
select * from employee where post_comment is NULL
select * from employee where post_comment is not NULL
update employee set post_comment = "" where id = 1
select * from employee where post_comment = '';
# (5) 關鍵字 in 的查詢
# 查詢收入是 3000 或 5000 或者 4000 或者 8000 所有員工姓名和收入
select emp_name,salary from employee where salary=3500 or salary=5000 or salary=8300 or salary=4000;
# 用in優化,在小括號里面寫上可能的值
select emp_name,salary from employee where salary in (3500,5000,8300,4000);
# 不在括號中的值,搜索出來
select emp_name,salary from employee where salary not in (3500,5000,8300,4000);
# (6) 關鍵字 like 模糊查詢
# (1) % 通配符
select emp_name,age,post from employee where emp_name like "%on";
# (2) _ 通配符
select emp_name,age,post from employee where emp_name like "a_e_";
# (7) concat
select concat("姓名:",emp_name,"薪資:",salary) as aaa from employee;
# concat_ws(拼接的符號,引數1,引數2,引數3 ... )
select concat_ws(" : ",emp_name,salary) as bbb from employee;
# 可以在sql中使用四則運算(+ - * /)
select concat_ws(" : ",emp_name, salary * 12 ) as bbb from employee;
# 二.group by 子句 分組,分類
"""group by 對資料進行分類, by 后面接的欄位,就是select要搜索的欄位"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分組形式進行欄位的拼接
select group_concat(emp_name),post from employee where id>1 group by post;
# 聚合函式
# 統計總數 count *所有
select count(*) from employee
# 統計最大值 max
select max(salary) from employee
# 統計最小值 min
select min(salary) from employee
# 統計平均值 avg
select avg(salary) from employee
# 統計總和 sum
select sum(salary) from employee
# 一般來說 使用時 分組 + 聚合函式 配合使用
# 1. 查詢部門名以及各部門的平均薪資
select post , avg(salary) from employee group by post;
# 2. 查詢部門名以及各部門的最高薪資
select post , max(salary) from employee group by post;
# 3. 查詢部門名以及各部門的最低薪資
select post , min(salary) from employee group by post;
# 4. 查詢公司內男員工和女員工的個數
select sex,count(*) from employee group by sex
# 5. 查詢部門名以及部門包含的所有員工名字
select group_concat(emp_name) , post from employee group by post
select emp_name,post from employee group by post,emp_name
# 三.having 查詢資料之后在進行過濾,一般是配合group by使用, 主要用分組后過濾
# 找出各部門的平均薪資,并且大于10000以上的所有部門
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查詢各崗位內包含的員工個數小于2的崗位名,員工名,個數
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 排序 , 按照什么欄位進行排序
# 默認值asc 升序排序
# 按照desc 降序排序
select * from employee order by age (默認升序)
select * from employee order by age desc (降序)
# 1. 查詢所有員工資訊，先按照age升序排序，如果age相同則按照hire_date降序排序
select emp_name,sex,age,hire_date,post from employee order by age,hire_date desc
# 2. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結果按平均薪資降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 3. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結果按平均薪資升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc
# 五.limit 限制查詢的條數 (資料分頁)
limit m,n m代表從第幾條開始查詢,n代表查詢幾條 m=0 代表的是第一條
select * from employee limit 0,5 從第一條開始查,查5條
select * from employee limit 5,5 從第六條開始查,查5條
# 只查詢一條資料
select * from employee limit 1
# 想要瞬間得到資料表中,最后一條資料
select * from employee order by id desc limit 1
# 拿到最后三條資料
select * from employee order by id desc limit 3
# 六.(了解) 可以使用正則運算式查詢資料 (不推薦使用,不好用效率不高)
select * from employee where emp_name regexp ".*on$" # .*? 的?號不識別
select * from employee where emp_name regexp "^程";
select * from employee where emp_name regexp "^程.*金";
# ### part2 多表查詢
# 內連接:(內聯查詢 inner join ) : 兩表或者多表滿足條件的所有資料查詢出來[兩個表之間共同具有的資料]
"""
# 兩表查詢
select 欄位 from 表1 inner join 表2 on 條件
# 多表查詢
select 欄位 from 表1 inner join 表2 on 條件 inner join 表3 on 條件
"""
# 基本語法 inner join on 接的表與表之間的必要連接條件
select * from employee inner join department on employee.dep_id = department.id
# 用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 實作的就是行內查詢
select * from employee,department where employee.dep_id = department.id
select * from employee as e,department as d where e.dep_id = d.id
# 外連接
# (1) 左連接 (左聯查詢 left join ) : 以左表為主,右表為輔,完整查詢左表所有資料,右表沒有的資料補NULL
""" select 欄位 from 表1 left join 表2 on 條件 """
select * from employee left join department on employee.dep_id = department.id
# (2) 右連接 (右聯查詢 right join) : 以右表為主,左表為輔,完整查詢右表所有資料,左表沒有的資料補NULL
""" select 欄位 from 表1 right join 表2 on 條件 """
select * from employee right join department on employee.dep_id = department.id
# (3) 全連接 (union) 所有資料全都合并起來
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
# ### part3 子查詢
"""
子查詢: 嵌套查詢
(1) 子查詢是查詢的陳述句當中又嵌套的另外一條sql陳述句,用括號()抱起來,表達一個整體
(2) 一般應用在from 子句后面表達一張表,或者 where 子句后面表達一個條件
(3) 速度從快到慢 單表查詢速度最快 -> 聯表查詢 -> 子查詢
"""
# (1)找出平均年齡大于25歲以上的部門
# 普通的where 相當于行內查詢
select
d.id,d.name
from
employee e,department 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 e inner join department 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 dep_id having avg(age) > 25;
# 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 dep_id having avg(age) > 25)
# (2)查看技術部門員工姓名
# 1.普通where查詢
select
e.name
from
employee e ,department d
where
e.dep_id = d.id and d.name = "技術"
# 2.inner join 實作
select
e.name
from
employee e inner join department d on e.dep_id = d.id
where
d.name = "技術"
# 3.子查詢
# 1.找技術部門對應id
select id from department where name = "技術"
# 2.通過id找員工姓名
select name from employee where employee.dep_id = ?
# 3.綜合拼接
select name from employee where employee.dep_id = (select id from department where name = "技術")
# (3)查看哪個部門沒員工
# 聯表寫法
select
d.id,d.name
from
employee e right join department d on e.dep_id = d.id
where
e.dep_id is NULL
# 子查詢
# 1.先查詢,員工都在哪些部門
select dep_id from employee group by dep_id => (200,201,202,204)
# 2.把不在部門串列中的資料找出來
select from department where id not in (1)
# 3.綜合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)
# (4)查詢大于平均年齡的員工名與年齡
# 假設平均年齡是18歲
select name,age from employee where age > ?
# 找平均年齡
select avg(age) from employee
# 綜合拼裝
select name,age from employee where age > (select avg(age) from employee)
# (5)把大于其本部門平均年齡的員工名和姓名查出來
# employee
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id || dep_id | avg(age) |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
# department
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 運營 |
+------+--------------+
# 1.先計算平均年齡
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作拼接,聯合成一張更大的表,做一次單表查詢;
select
*
from
employee as t1 inner join (1) 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.把額外的比較的條件加進去
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
t1.age > t2.avg_age
# (6)查詢每個部門最新入職的那位員工 # 利用上一套資料表進行查詢;
# 1.找每個部門最大的入職時間
select post,max(hire_date) as max_date from employee group by post
# 2.把子查詢查出來的資料和employee聯合成一張更大的表,做一次單表查詢
select
from
employee as t1 inner join (1) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date
# 3.綜合拼接
select
t1.emp_name,t1.hire_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
# (7)帶EXISTS關鍵字的子查詢
"""
exists 關鍵字表達存在
如果內層sql 能夠查到資料, 回傳True , 外層sql執行查詢陳述句
如果內層sql 不能查到資料, 回傳False, 外層sql不執行查詢陳述句
"""
select * from employee where exists (select * from employee where id = 1)
"""
子查詢總結:
子查詢可以單獨作為一個子句,也可以作為一個表或者某個欄位
一般用在from where select 子句后面
通過查詢出來的臨時表,可以跟任意的表重新拼接,組成更大的表,在通過篩選達成自己的目的
"""
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101385.html
標籤：MySQL