MySQL多表查詢，mysql數據庫：mysql增刪改、單表、多表及子查詢

一、數據增刪改

二、單表查詢

三、正表達式匹配

四、多表查詢

五、子查詢

一、.數據增刪改

增加
? insert [into] 表名[(可選字段名)] values(一堆值1),(一堆值2),.....
?? into 可以省略
?? 表名后的字段可以選
?? 如果寫了 后面的values中的值必須與表名后的字段一一對應
?? 如果沒寫 后面的values中的值必須與表的所有字段一一對應
?? values后面可以給多組值 用逗號隔開

刪除
? delete from 表名[where 條件]
? 條件不寫 是刪除所有記錄 是一行一行刪除?? 注意自增id 不會歸零
? truncate 重建表? 先記錄表結構 刪除整個表再重新建出來表 自增id 會歸零

更新數據
??? update 表名 set 字段名 = 值[,字段2 = 值2],[where 條件]
??? 可以一次性修改多個字段的值用逗號隔開
??? 條件如果不寫 修改所有記錄

二.單表查詢
??? 不帶關鍵字的查詢
??? select? {1.*|2.字段名|3.四則運行|4.聚合函數} from 表名 [where 條件]
??? 1.* 表示查詢所有字段
??? 2.可以手動要查詢的字段
??? 3.字段的值可以進行加減乘除運算
??? 4.聚合函數,用于統計
??? where 是可選的

select 的完整語法
數據準備
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);

關鍵字的作用
distinct? 去除重復數據 所有數據全都重復才算重復
where? 在逐行讀取數據時的一個判斷條件
group by? 對數據分組
having?? 對分組后的數據進行過濾
order by 對結果排序
limit? 指定獲取數據條數

完整的select 語句 語法 *****
select [distinct] * from 表名
[where
group by
having
order by
limit
]
注意 在書寫時 必須按照這個順序來寫? 但是順寫順寫不代表執行順序
數據庫偽代碼
def from():
??? 打開文件
def where():
??? 對讀取的數據進行過濾

def group_by():
??? 對數據分組
def having():
??? 對分組后的數據進行過濾
def distinct():
??? 去除重復數據
def order():
??? 排序
def limit():
??? 指定獲取條數

select 語句的執行順序 *****
def select(sql):
??? data = from()
??? data = where(data)
??? data = group by(data)
??? data = having(data)
??? data = distinct(data)
??? data = orderby(data)
??? data = limit(data)
??? return data;

簡單查詢
??? 指定顯示格式:
??? concat()函數用于拼接字符串
??? select
??????? (
??????? case
??????? when english + math > 120 then
??????? concat(name," nice")
??????? when english + math <= 130 then
??????? concat(name," shit")
??????? end
??? ) ,english,math from stu;??? *完全不重要

where 條件
??? create table emp (id int,name char(10),sex char,dept char(10),job
char(10),salary double);
insert into emp values
(1,"劉備","男","市場","總監",5800),
(2,"張飛","男","市場","員工",3000),
(3,"關羽","男","市場","員工",4000),
(4,"孫權","男","行政","總監",6000),
(5,"周瑜","男","行政","員工",5000),
(6,"小喬","女","行政","員工",4000),
(7,"曹操","男","財務","總監",10000),
(8,"司馬懿","男","財務","員工",6000);

group by 分組查詢 *****
什么是分組
??? 把一個整體 分割為多個部分
為什么分組
??? 在數據庫中分組為了統計 *****
分組后 組里的詳細記錄就被隱藏起來了? 不能直接查看
??? dept 一分組 變成三條記錄? 每個組中卻包含多條記錄 沒辦法顯示
??? 一定要顯示的話
??? 可以使用group_concat(字段名)
??? 可以將多個值拼接成一個字符串

注意*****: 1.只有出現在group by 后面的字段 才可以被顯示 其他都被影藏了
????????? 2.聚合函數不能寫在where的后面? where最先執行 它的作用硬盤讀取數據并
??????????? 過濾 以為數據還沒有讀取完 此時不能進行統計

什么樣的字段適合用于分組
??????? 重復性高的字段

請查詢每種性別的平均工資
??????? 性別分組
??? 請查詢每種崗位的平均工資
??????? 崗位分組
??? 每后面就是分組的依據

了解：
??? 在mysql 5.6中 分組后會默認顯示 每組的第一條記錄 這是沒有意義的
??? 5.7不顯示 因為5.7中 sql_mode中自帶? ONLY_FULL_GROUP_BY
??? group by 后面可以有多個分組與依據 會按照順序執行

??? order by 排序用的
??? asc 表示升序 是默認的
??? desc 表示降序
??? by 后面可以有多個排序依據

limit 限制顯示條數

limit a,b
??? limit 1,5
??? 從1開始 到5結束 錯誤
??? 從1開始 不包含1 取5條

分頁查詢
??? 每頁顯示3條? 共有10條數據
??? if 10 % 3 == 0:
??????? 10 / 3
??? else:
??????? 10/3 +1
??? 總頁數4

第一頁
??? select *from emp limit(0,3)
??? 第二頁
??? select *from emp limit(3,3)
??? 第二頁
??? select *from emp limit(6,3)

起始位置的算法
??? 頁數 - 1 * 條數
??? 1 - 1 = 0 * 3 = 0
??? 2 - 1 = 1 * 3 = 3

三、正表達式匹配
?? 由于like只能使用% 和 _ 不太靈活
?? 可以將like換為 regexp 來使用正則表達式

四、多表查詢 ******
?? 數據準備
create table emp (
id int,
name char(10),
sex char,
dept_id int
);

insert emp values(1,"大黃","m",1);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);

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

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

? 1.笛卡爾積查詢
??? select *from 表1,表n
??? 查詢結果是
??? 將坐標中的每條記錄 與右表中的每條記錄都關聯一遍
??? 因為 他不知道什么樣的對應關系是正確 只能幫你都對一遍
??? a表有m條記錄? b表有n條記錄
??? 笛卡爾積結果為m * n 記錄

需要自己篩選出正確的關聯關系
??? select *from emp,dept where emp.dept_id = dept.id;

2.內連接查詢 就是笛卡爾積查詢
??? select *from emp [inner] join dept;
??? select *from emp inner join dept where emp.dept_id = dept.id;

3.左外鏈接查詢
??? select *from emp left join dept on emp.dept_id = dept.id;
??? 左表數據全部顯示?? 右表只顯示匹配上的

4.右外鏈接查詢
??? select *from emp right join dept on emp.dept_id = dept.id;
??? 右表數據全部顯示?? 左表只顯示匹配上的

內和外的理解?? 內指的是匹配上的數據? 外指的是沒匹配上的數據

5.全外連接
???? select *from emp full join dept on emp.dept_id = dept.id;? ##mysql不支持

union 合并查詢結果
???? select *from emp left join dept on emp.dept_id = dept.id
???? union
???? select *from emp right join dept on emp.dept_id = dept.id;

union 去除重復數據? 只能合并字段數量相同的表
???? union all 不會去除重復數據

??? on 關鍵字 where 都是用于條件過濾? 沒有本質區別
??? 在單表中where的作用是篩選過濾條件
??? 在多表中where 連接多表 滿足條件就連接 不滿足就不連接
??? 為了區分是單表還是多表? 搞個新的名字 就是 on
??? 只要是連接多表的條件 就使用on

??? 三表查詢

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(s_id) references stu(id),
foreign key(s_id) references stu(id));
insert into stu values(null,"張三"),(null,"李四");
insert into tea values(null,"egon"),(null,"yyh");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);

select *from stu1 join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";

多表查詢套路
1.把所有表都連起來
2.加上連接條件
3.如果有別的過濾條件 加上where

五、.子查詢
??? 條件當一個查詢的結果是另一個查詢的時 這個查詢稱之為子查詢(內層查詢)

什么時候使用子查詢
??? 當一次查詢無法得到想要結果時? 需要多次查詢
??? 解決問題的思路
??? 是把一個復雜的問題 拆分為多個簡單的問題
??? 是把一個復雜的查詢 拆分為多個簡單的查詢
?in (1,2)

給你部門的的名稱
??? 查部門有哪些人?
??? 第一步查到部門的id
??? 第二部 拿著id去員工表查詢
??? select *from dept join emp on dept.id = emp.dept_id;

select *from emp join
# 使用子查詢 得到 每個部門的id 以及部門的 最高工資? 形成一個虛擬表 把原始表和 虛擬表連接在一起
(select dept_id,max(salary)as m from emp group by dept_id) as t1
# 如果這個人的部門編號 等于 虛擬表中的部門編號
?on emp.dept_id = t1.dept_id
?and
?# 并且 如果這個人的工資 等于 虛擬表中的最高工資? 就是你要找的人
?emp.salary = t1.m;

create table emp (id int,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);

day_id?? 成功?? 失敗
2018-10-01 2???? 2
2018-10-02 1???? 2

insert into emp2 values(1,"laowangba","男",26,"總監",5800);
insert into emp2 values(2,"laoliba","男",26,"總監",5800);
insert into emp2 values(3,"laocheng","男",26,"總監",5800);