資料庫重點知識-表操作(CRUD)

来源:https://www.cnblogs.com/yangyuanhu/archive/2019/07/12/11178857.html
-Advertisement-
Play Games

1.數據增刪改 2.單表查詢 3.正則表達式 4.多表查詢 ​ 笛卡爾積 ​ 內連接 ​ 外鏈接 ​ 子查詢 一.數據的增刪改 為什麼不說查 因為查詢語句 有很多細節 所以先從簡單的說起 添加數據: 1.insert into table_name[column,[column2.....]] va ...


1.數據增刪改
2.單表查詢
3.正則表達式
4.多表查詢
​ 笛卡爾積
​ 內連接
​ 外鏈接
​ 子查詢

一.數據的增刪改

為什麼不說查 因為查詢語句 有很多細節 所以先從簡單的說起

添加數據:

1.insert into table_name[column,[column2.....]] values(value1...)[,(value1...)];

如果表名後沒制定欄位 則插入的數據必須和表格結構順序匹配 每一個欄位都必須給出值 當然 數據類型也必須一致

如果在表名後制定了欄位名 可以選擇性的插入指定欄位 插入的數據必須和前面聲明的欄位匹配

2.insert into table_name select *from another_table;

該方式用於插入另一個表的查詢結果

要求查詢出的結構必須與當前表結構相同

上述方法 into 都可以省略

刪除數據:

delete from table_name [where condition];

沒有條件則刪除所有數據 逐條刪除

truncate table table_name;

要刪除全部數據最好的方式是重建表 他是直接刪除文件

修改數據:

update table_name set column = value[,column2 = value2] [where condition]

可以一次性修改多個欄位

沒有條件則全部修改

二.單表查詢

1.完整的查詢語句語法

select  distinct (* or 欄位名 or 四則運算) from 表名
where 條件
group by   欄位
having  條件
order by  排序
limit   控制從哪裡開始顯示以及 顯示幾條

以上是書寫順序,必須按照這個順序來書寫sql語句

書寫順序代表執行順序嗎?

並不是!

2.關鍵字的執行順序

註意：書寫順序錯誤將報錯

偽代碼:
  
第一步找到對應的文件
def from(name):
    open (file)
    pass

第二步 讀取並篩選數據
def where(條件):
讀取每一行數據 判斷是否滿足條件
  for line in file:
        if XXXxxx

def group():
  將數據 按照某個欄位進行分組
  pass

def having():
  對分組後的數據進行篩選
  pass

def distinct():
  對數據進行去重處理
    pass

def order():
    對數據進行排序
    pass

def limit()
    選取一部分數據
    pass

def select()
  from()
  wher()
  group()
  having()
  distinct()
  order()
  limit()
    return data;

3.簡單查詢

上述的關鍵字大多數是可選的 必選的是哪些?

select distinct from 表名;

distinct是可選的 用於取出重覆記錄

只有當顯示的所有列的數據都重覆時才去除

當欄位名太長獲取不容易理解時 可使用as來取別名

1.*表示通配符 顯示所有欄位

2.可以指定任意個欄位名

3.可以對欄位的數據進行四則運算

4.聚合函數 接下來講

案例:

準備數據
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 name,english from stu;
查看所有人的數學成績 並去除項姓名相同的數據
select distinct name,mathenglish from stu;
統計每個人的總分
select name,english+math 總分 from stu;
為每個為的英語加10分顯示
select name,english+10 總分 from stu;


調整顯示的格式:
需要 在欄位的數據前加上欄位名: 
name:趙雲   english:90  math:30

如果是python怎麼實現?   字元串拼接
mysql中的字元串拼接函數  concat()

select concat("姓名:",name),concat("英語:",english),concat("數學:",math) from stu;

取別名時  可以省略 as
select concat("姓名:",name) name,concat("英語:",english) english,concat("數學:",math) math from stu;

concat_ws()  類似python的join



需求 如果 總分小於150  在名字後面加上shit 大於等於 加上nice
如果 那就意味有邏輯判斷 (瞭解)
select 
(case 
when english + math > 120 then
concat("name"," nice")
when english + math <= 130 then
concat("name"," shit")
end),
english,math from stu;

select if(english+math > 120,concat(name,"nice"),concat(name,"shit")) from stu;

4.where關鍵字

支持的表達式

image-20190318144219550

練習:

1.查詢英語分數在 80－90之間的同學。

2.查詢數學分數為89,90,91的同學。

3.查詢所有姓李的學生成績。

4.查詢所有姓名帶有李的學生成績。

5.查詢數學分>80並且英語分>80的同學。

6.查詢數學分<60並且英語分<60的同學。

where 是如何找到你要的數據的?

where語句條件在沒有索引的情況下 是挨個遍歷判斷 效率非常低

如果有索引 則可以忽略掉大多數無用數據

5.group by

分組查詢

準備數據

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);

分組,需要一個欄位作為分組依據,把一個整體分割為不同部分

作用,用於將數據以組為單位進行統計,例如上述表中 統計每個部門的員工數量

1.將數據按照部門分組

select *from emp group by dept;

註意:mysql5.6 預設分組後可以查看每個分組的第一條記錄的所有欄位 但這個數據有意義嗎? 沒有

我們可以添加 ONLY_FULL_GROUP_BY 到sql_mode中避免這個問題 5.7後預設

分組後:就只能查看分組的欄位 以及對組內記錄的統計結果 (通過聚合函數來統計)

為何?

2.何為聚合函數?

將多個數據進行計算 並得到一個結果 稱為聚合

有哪些集合函數?

sum
count
avg
max/min

簡單練習:

1.查詢每個部門有幾個人

2.計算每個部門的平均工資

3.計算每個崗位的平均工資

4.計算每個部門每個崗位的平均工資

5.查詢平均工資大於5000的部門

#理所當然的想到使用where來進行篩選  
select dept from emp where avg(salary) > 5000;
error: Invalid use of group function

#報錯原理解析
where 條件中不允許使用分組函數

#解決方案:
group_concat函數
用於拼接組內記錄的某個欄位  拼接為一個字元串

#用法:
select dept,group_concat(name) from emp group by dept;

#註意事項：
分組後，分組下的內容被隱藏 只能查詢分組欄位

6.having

用於對分組後的數據進行篩選

作用與where相同 用於過濾

不同點在於:where 是從文件讀取數據時的過濾條件

這導致了where中不能使用聚合函數

因為數據讀取工作都沒有完成 不可能統計出數據

having是在分組後進行的過濾條件

分組的執行順序是在where之後

此時數據已經全部讀取了

所以可以使用聚合函數來進行統計

為什麼 不分組的時候在select 後面可以使用聚合函數呢?

select sum(salary) from where;

因為你where比select 後面的欄位篩選更早執行 此時數據全都已經讀取了 所以可以進行統計

練習:

#查詢 崗位平均薪資高於6000的 崗位名稱和平均薪資
select dept,avg(salary) from emp group by dept having avg(salary) > 6000 ;
#查詢 部門人數少於3的 部門名稱 人員名稱 人員個數
select dept,group_concat(name),count(*) from emp group by dept having count(name) < 3;

7.order by

用於對記錄排序

select * from emp order  by salary;
預設為升序

select * from emp order  by salary asc;
指定為升序

select * from emp order  by salary desc;
指定為降序

select * from emp order  by salary,id desc;
工資相同時按照id排序

8.limit

用於限制顯示的記錄數

limit [start,] count;

start 開始位置

count 顯示條數

不指定start 時 則從第一條開始顯示

查看前三人
select *from emp limit 3;

查看工資最高的那個人信息
select *from emp order by salary desc limit 1;

指定起始位置
查看id為3-6的人
select *from emp  limit 2,4; 


limit 可用於分頁
分頁原理:先查詢總數據條數 設為a
確定每頁數量b
總頁數為c = a / b 如果除不盡則需要加1  例如 10 / 3 正確頁數為4
查詢語句的起始位置為s = 當前頁數d 減去1 乘以每頁數量
即  s =  (d - 1) * b
語句為:select*from table_name limit s,b

三 正則表達式匹配

正則表達式用於模糊查詢,模糊查詢已經講過了

like 僅支持 % 和 _ 遠沒有正則表達式靈活

當然絕大多數情況下 like足夠使用

#準備數據:
insert into emp values(1,"laowangba","男",26,1,"總監",5800);
insert into emp values(1,"laoliba","男",26,1,"總監",5800);
insert into emp values(1,"laocheng","男",26,1,"總監",5800);


#語法:
select *from table where name regexp "正則表達式";

四 多表查詢

數據準備:

#不存在外鍵關聯的兩張表
#一張表示員工表 
#存在一些不正確的部門id

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.笛卡爾積查詢:

是兩張表相乘的結果,若左邊有m條 右邊有n條 查詢結果為m*n條; 往往包含大量錯誤數據

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

2.鏈接查詢

1.內連接查詢 查詢出兩張表都有匹配關係的記錄

select *from dept,emp where dept.id=emp.dept_id;  
 #where用於篩選數據,而在多多表查詢中要篩選的是兩邊的關係 on用於過濾關聯關係
 #而where單獨做條件過濾，這樣sql看起來可以更清晰明確，當然where依然可以代替on  

select *from dept join emp on dept.id=emp.dept_id;
inner可以省略

2.左外鏈接查詢

左表中記錄的無論是否有匹配關係都全部顯示，右表中僅顯示匹配成功的記錄

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

3.右外鏈接查詢

右表中記錄的無論是否有匹配關係都全部顯示，左表中僅顯示匹配成功的記錄

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

4.全外鏈接查詢

無論是否匹配成功，兩邊表中的記錄都要全部顯示

##mysql 不支持
select *from dept full join emp on dept.id = emp.dept_id;  

#mysql中可以使用合併查詢結果 在所有語句最後寫分號
select *from dept left join emp on dept.id=emp.dept_id
union
select *from dept right join emp on dept.id=emp.dept_id;

union 只能用於欄位數量相同的兩個表 會自動去除重覆的記錄

union all 則保留所有記錄

練習：查詢1號部門的名稱和其所有員工名稱;

select dept.name 部門,emp.name 姓名 from dept join emp on dept.id=emp.dept_id where dept.id = 1;

多表查詢的思路是 先將多張表的數據連在一起 在使用條件來過濾

五 子查詢

什麼是子查詢?

當一個查詢是另一個查詢的條件時,這個查詢稱之為子查詢(內層查詢)

什麼時候用?

當查詢需求比較複雜,一次性查詢無法得到結果,需要多次查詢時,

例如:給出一個部門名稱,需要獲得該部門所有的員工信息

需要先確定部門的id,

然後才能通過id確定員工

解決問題的方式是把一個複雜的問題拆分為若幹個簡單的問題

如何使用?

首先明確子查詢就是一個普通的查詢,當一個查詢需要作為子查詢使用時,用括弧包裹即可

案列:

準備數據:

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);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市場"),(2,"行政"),(3,"財務");

#需求:財務部有哪些:
#數據在兩張表中 可以使用鏈接查詢
select emp.name from emp inner join dept on dept.id = emp.dept_id where dept .name = "財務"; 




#子查詢方式:
#數據在兩張表中,先查詢那張?
#emp? 不行  不知道部門名 查dept
#第一步 需要知道財務部的id 
select id from dept where  name = "財務";
#第二步 用查詢的到的id作為判斷條件查詢emp
select name from emp where dept_id = 3;
# id3不能寫死 是上一個查詢的結果 所以直接寫在後面  加上括弧就變成了子查詢
select name from emp where dept_id = (select id from dept where  name = "財務");

in 關鍵字子查詢

"查詢平均年齡大於25的部門名稱
子查詢方式:
平均年齡大於25的部門id有哪些?
先要求出每個部門的平年齡! 篩選出平均年齡大於25的部門id
拿著部門id 去查詢部門表查詢"

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


"多表查詢方式:
先把數據拼接到一起 在加以篩選"

select dept.name from emp inner join dept
on emp.dept_id = dept.id 
group by dept.name
having avg(age) >25;

exists關鍵字子查詢

exists 後跟子查詢 子查詢有結果是為True 沒有結果時為False

為true時外層執行 為false外層不執行

select *from emp where exists (select *from emp where salary > 1000);
#查看exists 的返回結果: 只有 0 和 1


select (exists (select *from emp where salary > 10000));

#一個查詢結果也是一個表 既然是表就能鏈接起來
#綜合練習:
"查詢每個部門工資最高的員工信息
先查詢每個部門的最高工資
將查詢結果與員工表聯合起來
在加條件判斷部門id相同並且 最高工資相同 則顯示"

select *from emp  inner join  
(select dept_id,max(salary) m from emp group by dept_id)  t2
on emp.dept_id = t2.dept_id 
where
emp.salary = t2.m;

三表聯查

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(t_id) references tea(id));

insert into stu values(null,"張三"),(null,"李四");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);



#egon老師教過哪些人?
select *from stu join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";


'tea表中查得egon的 id 為1
關係表中查的 123和egon有關係
學生表中查得 123的名字為abc'

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux 下的7種文件類型
    普通文件類型 （-）Linux中最多的一種文件類型, 包括 純文本文件(ASCII)；二進位文件(binary)；數據格式的文件(data);各種壓縮文件.第一個屬性為 [-] ，這些文件一般是用一些相關的應用程式創建，比如圖像工具、文檔工具、歸檔工具... .... 或 cp工具等。這類文件的刪除 ...
  • linux軟體管理-RPM
    [toc] linux軟體管理 RPM RPM的基礎概述 rpm：RPM全稱 縮寫，由紅帽開發用於軟體包的安裝，升級卸載與查詢 rpm包的命名規則 name: 軟體包名稱 version: 版本號, 主版本, 重大更新. 次版本, 子功能更新. 修訂號, 修複bug release: 發佈版本 el ...
  • 源碼包安裝轉換rpm包
    [toc] 純凈版虛擬機 1. 先安裝個虛擬機，登陸nginx官網 http://nginx.org/選擇一個穩定的版本 2. 右鍵複製地址，到新克隆的純凈虛擬機wget 下載 先yum install y wget 把源換成阿裡雲的 curl o /etc/yum.repos.d/CentOS B ...
  • Linux目錄和文件——查詢目錄和文件的命令
    Linux目錄和文件——查詢目錄和文件的命令 摘要：本文主要學習了在Linux系統中是如何查詢目錄和文件的。 which命令 which命令是根據PATH環境變數設置的路徑，去搜索執行文件。 基本語法 選項說明 使用舉例 type命令 type命令用來顯示指定命令的類型： 1）alias：別名。 2 ...
  • crontab
    crontab 簡介 ​ crontab常見於Unix和類Unix的操作系統之中，用於設置周期性被執行的指令。編輯好的crontab會被存放於“crontab”文件中，以供之後讀取和執行。每個用戶可以擁有自己的crontab文件。通常，crontab存儲的指令被守護進程crond激活，crond常常 ...
  • 運維面試題
    前言 近期時間在努力投簡歷加找工作中，將自己或者朋友遇到的面試題在此一一整理，後期將持續更新。。。 （由於博主起步階段水平有限，接觸的都是基礎題目，大佬不要見笑） 1、測試網路連通性的命令 ping：最基礎的就不解釋了 tracent（trace router的縮寫）：判斷數據包到目的地經過的哪些節 ...
  • 安裝Win10,ERROR_0x8007025D問題解決
    Windows10安裝的時候，出現 大概提示為:windows 無法安裝所需的文件。請確保全裝所需的所有文件可用，並重新啟動安裝。 本人在出現這個問題的原因，是因為U盤的'文件系統格式'不正確，這裡給出我的步驟，希望能幫助到大家解決問題. 1. 合理的使用工具製作'啟動盤'(如:Rufus、Ultr ...
  • 學習Elasticsearch原理筆記
    Elasticsearch是一個分散式可拓展的實時搜索和分析引擎 文件存儲：Elasticsearch，面向文檔型資料庫，一條數據就是一個文檔，用JSON作為文檔序列化的格式 MySQL和Elasticsearch數據關係術語對比： 關係資料庫-資料庫-表-行-列 Elasticsearch-索引- ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類