1.單表查詢

單表查詢語法：

          
            select distinct 字段1,字段2... from 表名
                              where 條件
                              group by field
                              having 篩選
                              order by field
                              limit 限制條數
          
        

關鍵字執行的優先級:

          
            from
where
group by
select
distinct
having
order by
limit
          
        

1.1 簡單查詢

1.select語句：

• select * from 表；
• select 字段,字段.. from 表；
• select distinct 字段,字段.. from 表； # 按照查出來的字段去重
• select 字段*5 from 表； # 給查出來的字段做四則運算 (字段時int類型)
• select 字段 as 新名字,字段 as 新名字 from 表； # 給查出來的字段重命名
• select 字段 新名字 from 表；給查出來的字段重命名

2.定義顯示的格式 concat

• 1.concat() 函數 用于字符串拼接

  例：select concat('姓名: ',emp_name,' 年薪: ', salary*12) as annual_salary
  from employee;

• 2.concat_ws() 第一個參數為分隔符

  例：select concat_ws(':',emp_name,salary*12) as annual_salary
  from employee;

1.2 where語句

where語句不能與聚合函數合用

1.比較運算

> 、< 、= 、>= 、<= 、!= 或<> (不等于）

用于數值比較（不會用于做字符串的比較）

2.范圍篩選

• 1.多選一 ：字段名 in (值1,值2，值3)

  in (值1,值2，值3) 相當于 or

  例：select * from employee where salary in (20000,30000,3000,19000,18000,17000)

• 2.在一個模糊的范圍里

  • 1.在一個數值區間 between 值1 and 值2

                      
                        # 查薪資在1w-2w之間的所有人的名字
    select emp_name from employee where salary between 10000 and 20000;
                      
                    

  • 2.字符串的模糊查詢 like

    通配符 % 匹配任意長度的任意內容

                      
                        select * from employee where emp_name like '程%'；# 查詢以程開頭的
    select * from employee where emp_name like '%n'；# 查詢以n結尾的
    select * from employee where emp_name like '%n%'；# 查詢包含n的
                      
                    

    通配符 _ 匹配一個字符長度的任意內容

  • 3.正則匹配 regexp 用于更加細粒度的匹配的時候

    select * from 表 where 字段 regexp 正則表達式

    select * from employee where emp_name regexp '^j[a-z]{5}'

                      
                        查看崗位是teacher且名字是jin開頭的員工姓名、年薪
    select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%'
    select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'
                      
                    

3.邏輯運算 - 條件的拼接

與 and
或 or
非 not

select * from employee where salary not in (20000,30000,3000,19000,18000,17000

4.身份運算符 - 關于null —— is null /is not null

          
            查看崗位描述不為NULL的員工信息
select * from employee where post_comment is not null;
          
        

1.3 分組 group by

select *(或 字段名) from 表名 group by 字段名

select * from employee group by post

會把在group by后面的這個字段，也就是post字段中的每一個不同的項都保留下來,并且把值是這一項的的所有行歸為一組

distinct 是基于分組完成的，所以一般去重用分組。

1.4 聚合

1.聚合：把很多行的同一個字段進行一些統計，最終的到一個結果

count(字段) — 統計這個字段有多少項

sum(字段) — 統計這個字段對應的數值的和

avg(字段) — 統計這個字段對應的數值的平均值

min(字段)

max(字段)

2.分組聚合

先分組在聚合

1.總是根據會重復的項來進行分組

2.分組總是和聚合函數一起用 最大 最小 平均 求和 有多少項

          
            #求各個部門的人數
select post,count(*) from employee group by post;
#求公司里 男生 和女生的人數
select sex,count(id) from employee group by sex;
#求各部門的平均薪資
select post,avg(salary) from employee group by post;
#求各部門年齡最小的
select post,min(age) from employee group by post
          
        

求部門的最高薪資或者求公司的最高薪資都可以通過聚合函數取到,但是要得到對應的人，就必須通過多表查詢

1.5 having 過濾

having 條件 # 過濾組 ,永遠與group合用,是在group by 之后執行的。

執行順序：總是先執行where ，再執行group by分組，所以相關先分組之后再根據分組做某些條件篩選的時候，where都用不上，只能用having來完成。

          
            部門人數大于3的部門
select post from employee group by post having count(*) > 3
平均薪資大于10000的部門
select post from employee group by post having avg(salary) > 10000

select * from employee having age>18
          
        

注：普通的條件篩選不要用having，要使用where。

1.6 order by 查詢排序

order by 某一個字段; 根據字段默認升序排列

order by 某一個字段 asc; 默認是升序 ，asc 從小到大

order by 某一個字段 desc; 指定降序排列 ，desc 從大到小

order by 第一個字段 asc,第二個字段 desc; 指定先根據第一個字段升序排列，在第一個字段相同的情況下，再根據第二個字段排列

1.7 LIMIT 限制查詢的記錄數

1.取前n個 ： limit n == limit 0,n

• 考試成績的前三名
• 入職時間最晚的前三個

2.分頁 ： limit m,n 從m+1開始取n個

• 員工展示的網頁
  • 18個員工
  • 每一頁展示5個員工

3.limit n offset m == limit m,n 從m+1開始取n個

1.8 總結

          
            select distinct 需要顯示的列 from 表
                            where 條件
                            group by 分組
                            having 過濾組條件
                            order by 排序
                            limit (offset) 前n 條
          
        

單表查詢順序：

2. 多表查詢

兩張表是怎么連在一起的？

select * from emp,department;

2.1 連表查詢

把兩張表連在一起查

• 1.內鏈接 inner join

  兩張表條件不匹配的項不會出現在結果中

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

  

• 2.外連接

  • 左外連接 left join

    永遠顯示全量的左表中的數據

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

    

  • 右外連接 right join

    永遠顯示全量的右表中的數據

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

  • 全外連接

    全外連接：在內連接的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果

    注意：mysql不支持全外連接 full join

    強調：mysql可以使用此種方式間接實現全外連接:

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

連接的語法：

• select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;

常用的連表查詢：內鏈接 、左外鏈接

          
            # 找技術部門的所有人的姓名
select * from emp inner join department on emp.dep_id = department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技術         |
|  2 | alex      | female |   48 |    201 |  201 | 人力資源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力資源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 銷售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技術         |
+----+-----------+--------+------+--------+------+--------------+
select * from emp inner join department on emp.dep_id = department.id where department.name = '技術'
select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = '技術'

# 找出年齡大于25歲的員工以及員工所在的部門名稱
select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;

# 根據age的升序順序來連表查詢emp和department
select * from emp inner join department as d on emp.dep_id = d.id order by age;

          
        

既可以用連表查詢也可以用子查詢時，優先使用連表查詢，因為連表查詢的效率高。

2.2 子查詢

在查詢一個結果的時候，依賴一個條件，這個條件也是一個sql語句

          
            # 找技術部門的所有人的姓名
先找到部門表技術部門的部門id
select id from department where name = '技術';
再找emp表中部門id = 200
select name from emp where dep_id = (select id from department where name = '技術');

# 找到技術部門和銷售部門所有人的姓名
先找到技術部門和銷售部門的的部門id
select id from department where name = '技術' or name='銷售'
找到emp表中部門id = 200或者202的人名
select name from emp where dep_id in (select id from department where name = '技術' or name='銷售');
# 連表查詢方法：
select emp.name from emp inner join department on emp.dep_id = department.id where department.name in ('技術','銷售');
          
        

小練習

          
            # 查詢平均年齡在25歲以上的部門名
部門名 department表
select name from department where id in (
  select dep_id from emp group by dep_id having avg(age)>25
);
員工表
select dep_id,avg(age) from emp group by dep_id;
select dep_id from emp group by dep_id having avg(age)>25;

# 查看不足1人的部門名(子查詢得到的是有人的部門id)
查emp表中有哪些部門id
select dep_id from emp group by dep_id;
再看department表中
select * from department where id not in (???)
select * from department where id not in (select dep_id from emp group by dep_id);

# 查詢大于所有人平均年齡的員工名與年齡
select * from emp where age>(select avg(age) from emp);

# 查詢大于部門內平均年齡的員工名、年齡
select dep_id,avg(age) from emp group by dep_id;
select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d
on emp.dep_id = d.dep_id where emp.age > d.avg_age;