資料庫重點知識-表操作(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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類