查詢語句

mysql中要學習的知識：多表關系，查詢語句，索引

添加數據補充

將一個查詢結果插入到另一張表中

create table student(name char(10), gender int);

insert into student values('nalituo', 1);

insert into student values('sasigi', 0);

create table student_man(name char(10), gender int);

# 快速將student表中性別為1的記錄插入student_man表中

insert into student_man select * from student where gender = 1;

所有select關鍵字

select [distinct] * from table_name

where

group by

having

order by

limit a,b

必須存在的 select

* 可以換成任意一個或者多個字段，可以重復，但是*必須寫最前面

# 注意：關鍵字的順序是固定的不能隨意變化

# 注意：執行順序distinct函數在having后面，其余從select到limit都和定義順序相同

簡單查詢

*表示通配符，顯示所有字段

可以指定任意個字段名

可以對字段的數據進行四則運算

聚合函數，如下

取別名

當字段名太長獲取時不容易理解時，可以使用as來取別名，也可以不寫as，直接空格加別名

舉例(下面查詢的都是在這張表的基礎上)：where 條件

# 數據創建

create table stu(

id int primary key auto_increment,

name char(10),

math float,

english float

);

insert into stu values(null,"趙云",90,30);

insert into stu values(null,"小喬",90,60);

insert into stu values(null,"小喬",90,60);

insert into stu values(null,"大喬",10,70);

insert into stu values(null,"李清照",100,100);

insert into stu values(null,"鐵拐李",20,55);

insert into stu values(null,"小李子",20,55);

# 查看所有數據

select * from stu;

# 查看英語成績

select english from stu;

# 查看每個人的總分

select english+math as 總分 from stu;

# 為每個人的數學都減10分

select math-10 數學 from stu;

# 不需要加引號，包括中文

1.where

select * from table_name

where 后接

1.比較運算符 > < >= <= = !=

2.成員運算符 in not in 后面接一個set

3.邏輯運算符 and or not

4.模糊查詢like

% 表示0-任意個數的任意字符

_ 表示1個任意字符

%李%表示查詢所有帶有李字的記錄

5.between and 兩者之間 兩者都能取到值

6.is null 是否為空

7.exists 子查詢語句

# 注意：int類型，查詢的時候加上''也可以查到

舉例：

# 查詢姓大的，數學小于80分，并且英語大于20分的人的數學成績

select name,math from stu where name like '大%' and math < 80 and english > 20;

# 查詢英語及格的人的平均分

select name, (english+math)/2 as '平均分' from stu where english >= 60;

2.distinct 去除重復記錄

# 僅在查詢結果中所有字段全部相同時，才算重復的記錄

select distinct * from stu;

# 名字相同就重復

select distinct name from stu;

3.group by

分組，即將一個整體按照某個特征或依據來分為不同的部分

為什么要分組，分組是為了統計，例如統計男性有幾個，女性有幾個

數據準備：

create table emp(

id int primary key auto_increment,

name char(10),

sex char(10),

dept char(10),

job char(10),

salary float

);

insert into emp values

(1,"劉備","男","市場","總監",5800),

(2,"張飛","男","市場","員工",3000),

(3,"關羽","男","市場","員工",4000),

(4,"孫權","男","行政","總監",6000),

(5,"周瑜","男","行政","員工",5000),

(6,"小喬","女","行政","員工",4000),

(7,"曹操","男","財務","總監",10000),

(8,"司馬懿","男","財務","員工",6000);

# 語法：

select xxx from table_name group by 字段名;

# 需求：按照性別進行分組

select * from emp group by sex;

# 這樣做只會顯示該分組的第一行數據，這是因為emp有很多行，而分組聚合后的sex只有兩行，兩者的行數不匹配，邏輯是錯誤的。

# mysql 5.6下，查詢的結果是name僅顯示為該分組的第一個

# mysql 5.7以上則會直接報錯，5.6也可以手動開啟這個功能

# 把 ONLY_FULL_GROUP_BY 添加到服務器的配置文件中

統計函數

也稱之為聚合函數

將一堆數據經過計算得出一個結果

sum avg count max min

可以用在，字段的位置 ，或是分組的后面

求和：sum(字段名)

平均數 avg(字段名)

最大值 max(字段名)

最小值 min(字段名)

個數 count(字段名) # 字段名稱可以使用*代替,因為如果只查一個字段，那么當該字段為空時，是不計數的

# 例如：查找所有人的工資總和

select sum(salary) from emp;

# 例如：查詢所有人的平均工資

select avg(salary) from emp;

# 例如：查詢工資最高的人的姓名

select name,max(salary) from emp;

# 不是想要的效果，因為默認顯示的是第一個name，因為name有很多行，而max(salary)只有一行

select name from emp where salary = max(salary);

# 報錯，原因：偽代碼

def where(條件):

for line in file:

if salary = max(salary)

# 分析 where 讀取滿足條件的一行 ,max()先要拿到所有數據 才能求最大值。

# 但這里由于讀取沒有完成所有無法求出最大值

# 結論 where 后面不能使用聚合函數

分組+統計

# 需求 查詢每個性別有幾個人

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

# 需求：查詢每個性別有幾個人，并且顯示名字

select name,sex,count(*) from emp group by sex;

# 報錯

# 我們可以用group_concat 將分組之外的字段 做一個拼接 ,但是這是沒有意義

# 如果要查詢某個性別下的所有信息，直接使用where即可

# 結論，只有出現在了group by 后面得字段才能出現在select的后面

4.having

用于過濾，但是與where不同的是，having使用在分組之后

案例：

# 求出平均工資大于5000的部門信息

select dept,avg(salary) from emp group by dept having avg(salary) > 5000;

# 查詢部門人數少于3的部門名稱 人員名稱 人員個數

select dept, group_concat(name), count(*) from emp group by dept having count(*) < 3;

5.order by

根據某個字段排序

# 語法

select * from table_name order by 字段名稱;

# 默認升序，使用desc降序，asc升序

# 改為降序

select * from table_name order by 字段名稱 desc;

# 多字段，先按照第一個排序，第一個相同，再按照第二個

select * from table_name order by 字段名稱1 desc, 字段名稱2 asc;

# 案例：工資升序，id降序

select * from emp order by salary asc, id desc；

6.limit

用于限制要顯示的記錄數量

# 語法1

select * from table_name limit 個數;

# 語法2

select * from table_name limit 起始位置,個數;

# 語法3

select * from table_name limit 個數 offset 過濾個數;

# 案例：查詢前三條

select * from emp limit 3;

# 從第3條開始，查詢3條

select * from emp limit 2, 3;

# 從第4條開始，查詢2條

select * from emp limit 2 offset 3;

# 注意：起始位置，從0開始

# 經典的使用場景：分頁顯示

1.每一頁顯示的條數 a = 3

2.明確當前頁數 b = 2

3.計算起始位置 c = (b-1) * a

select * from emp limit 0,3;

select * from emp limit 3,3;

select * from emp limit 6,3;

# django中會提供一個分頁功能，但是它是先查詢所有數據，丟到列表中，再取出數據，這樣如果數據量太大可能會有問題

子查詢

將一條語句的結果作為另一條語句的條件或者是數據來源

當我們一次性查不到想要的數據時就需要使用子查詢

in 關鍵字子查詢

當內層查詢(括號內的)結果會有多個結果時，不能使用=必須使用in，另外in查詢只能包含一個字段，也就是一列數據

需求：找出平均年齡大于25的部門名稱

準備數據：

drop table if exists emp;

create table emp (id int primary key ,name char(10),sex char,age int,dept_id int,job char(10),salary double);

insert into emp values

(1,"劉備","男",26,1,"總監",5800),

(2,"張飛","男",24,1,"員工",3000),

(3,"關羽","男",30,1,"員工",4000),

(4,"孫權","男",25,2,"總監",6000),

(5,"周瑜","男",22,2,"員工",5000),

(6,"小喬","女",31,2,"員工",4000),

(7,"曹操","男",19,3,"總監",10000),

(8,"司馬懿","男",24,3,"員工",6000);

create table dept(id int primary key,name char(10));

insert into dept values(1,"市場"),(2,"行政"),(3,"財務");

# 1. 查詢出平均年齡大于25的部門編號

select dept_id from emp group by dept_id having avg(age) > 25;

#2. 根據編號查詢部門的名稱

select * from dept where id in (1, 2);

# 子查詢合并

select * from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);

子查詢的思路:

1.要分析 查到最終的數據 到底有哪些步驟

2.根據步驟寫出對應的sql語句

3.把上一個步驟的sql語句丟到下一個sql語句中作為條件

# 注意，如果用子查詢，那么內部的語句查詢的字段必須指定，而不能為*，否則會報錯，因為外部得到的結果會亂掉

exist關鍵字子查詢

當內層查詢有結果時，外層才會執行

# 案例

select * from dept where exists (select * from dept where id = 1);

# 由于內層查詢產生了結果，所以執行了外層查詢dept的所有數據

多表查詢

笛卡爾積查詢

# 案例：

select * from table1,table2,...

# 笛卡爾積查詢在數據關聯關系錯誤時，結果會出現大量的錯誤數據，

# 這時我們需要添加過濾條件，從表外鍵值等于主表的主鍵值

select * from emp, dept where dept_id=dept.id;

# 并且會產生重復的字段信息，例如員工里的部門編號和部門表里的id字段，所以在select 后指出需要查詢的字段名稱

# 案例：

select dept.name as 部門, dept.id 部門編號, emp.name 姓名, emp.id 員工編號, sex from emp, dept where dept.id=dept_id;

內連接查詢語法

本質上就是笛卡爾積查詢

# 語法

select * from table1 inner join table2;

# 案例

select * from emp inner join dept where dept_id = dept.id

# 其中inner可以省略，emp和dept的順序也可以調換

# 這里的where可以用on替代

外連接查詢

包括沒有匹配關系的數據，不常用，因為一般都會有外鍵約束對應關系

舉例：

# 不存在外鍵關聯的兩張表

# 存在一些不正確的部門id

drop table if exists emp;

create table emp(id int primary key, name char(10), sex char(10), dept_id int );

insert into emp values(1,"大黃","m",1);

insert into emp values(2,"老王","m",2);

insert into emp values(3,"老李","w",30);

drop table if exists dept;

create table dept(id int primary key, name char(10));

insert into dept values(1, '市場');

insert into dept values(2, '財務');

insert into dept values(3, '行政');

左外連接查詢

左邊的表無論是否能夠匹配都要完整顯示，右邊的僅展示匹配上的記錄

# 需求：要查詢所有員工以及其所屬的部門信息

select * from emp left join dept on dept_id = dept.id;

# 注意，在外連接查詢中不能夠使用where關鍵字，必須使用on關鍵字專門來做表的對應關系

右外連接查詢

右邊的表無論是否能夠匹配都要完整顯示，左邊的僅展示匹配上的記錄

# 需求：要查詢所有部門以及其所屬的員工信息

select * from emp right join dept on dept.id = dept_id;

# 注意，哪個表放前面就先展示哪個表

全外連接查詢

無論是否匹配成功，兩邊表的數據都要全部顯示

# 需求：查詢所有員工與所有部門的對應關系

select * from emp full join dept on dept.id = dept_id;

# 雖然不會報錯，但也不是我們想要的結果

# mysql不支持全外連接

# 我們可以將左外連接查詢的結果，和右外連接查詢的結果，做一個合并

select * from emp left join dept on dept.id = dept_id

union

select * from emp right join dept on dept.id = dept_id;

# union的用法

select * from emp

union all

select * from emp;

# union將自動去除重復的記錄

# union all 不去除重復

select sex,name from emp

union

select * from dept;

# 注意 union 必須保證兩個查詢結果列數相同，一般用在多個結果結構完全一致時

總結：外連接查詢，查到的是沒有對應關系的記錄，但是這樣的數據原本就是有問題的，所以最常用的還是內連接查詢

內連接表示 只顯示匹配成功的記錄

外連接表示 沒有匹配成功的也要顯示

多表查詢案例：

create table stu(id int primary key auto_increment, name char(10));

create table tea(id int primary key auto_increment, name char(10));

create table tsr(

id int primary key auto_increment,

t_id int,

s_id int,

foreign key(t_id) references tea(id),

foreign key(s_id) references stu(id)

);

insert into stu values(null, '張三'), (null, '李四');

insert into tea values(null, 'nick'), (null, 'jerry');

insert into tsr values(null,1,1),(null,1,2),(null,2,2);

# nick老師教過哪些人？

select tea.name, stu.name from tea join tsr join stu on tea.id = t_id and stu.id = s_id where tea.name = 'nick';

注意:

在查詢數據中,應該將上述知識合并使用,特別是子查詢和內連接查詢,如果面臨多表聯合查詢,應該先理清楚步驟,一步一步實現,可以將一部分的查詢結果搭建成一個臨時表,再聯合其他表進行查詢.

例如:

# 查詢平均成績大于80分的同學的姓名以及平均成績

# 1. 根據學生id分組,查出所有平均成績大于80的學生id

select student_id, avg(num) from score group by student_id having avg(num) > 80;

#2. 將查詢結果作為一個臨時表,如果要使用臨時表的數據,就可以寫上as a 然后后面就用 a.字段 去使用

(select student_id, avg(num) from score group by student_id having avg(num) > 80) as a ;

#3. 將臨時表數據與學生表連接

select * from student join (select student_id, avg(num) from score group by student_id having avg(num) > 80) as a ;

#4. 進行一些篩選

select * from student join (select student_id, avg(num) from score group by student_id having avg(num) > 80) as a on sid = a.student_id;

總結

以上是生活随笔為你收集整理的mysql居左查询abcd_数据库--查询语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。