半個月時間把MySQL重新鞏固了一遍,梳理了一篇幾萬字 “超硬核” 文章!

Cs 挽周 2021-08-15 17:56:21 阅读数:499

本文一共[544]字,预计阅读时长:1分钟~
mysql 重新 一遍 梳理 理了
  • MySQL 是最流行的關系型數據庫管理系統,在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System:關系數據庫管理系統)應用軟件之一

在這裏插入圖片描述

MySQL實戰文章目錄


MySQL必會知識點梳理 (必看)

在這裏插入圖片描述

私信博主即可獲得MySQL全套資料 !


【介紹】

在這裏插入圖片描述

什麼是數據庫

  • 數據庫(Database)是按照數據結構來組織、存儲和管理數據的倉庫。
  • 每個數據庫都有一個或多個不同的 API 用於創建,訪問,管理,搜索和複制所保存的數據。
  • 我們也可以將數據存儲在文件中,但是在文件中讀寫數據速度相對較慢。所以,現在我們使用關系型數據庫管理系統(RDBMS)來存儲和管理大數據量。所謂的關系型數據庫,是建立在關系模型基礎上的數據庫,借助於集合代數等數學概念和方法來處理數據庫中的數據。

MySQL數據庫

MySQL 是一個關系型數據庫管理系統,由瑞典 MySQL AB 公司開發,目前屬於 Oracle 公司。MySQL 是一種關聯數據庫管理系統,關聯數據庫將數據保存在不同的錶中,而不是將所有數據放在一個大倉庫內,這樣就增加了速度並提高了靈活性。

  • MySQL 是開源的,目前隸屬於 Oracle 旗下產品。
  • MySQL 支持大型的數據庫。可以處理擁有上千萬條記錄的大型數據庫。
  • MySQL 使用標准的 SQL 數據語言形式。
  • MySQL 可以運行於多個系統上,並且支持多種語言。這些編程語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
  • MySQL 對PHP有很好的支持,PHP 是目前最流行的 Web 開發語言。
  • MySQL 支持大型數據庫,支持 5000 萬條記錄的數據倉庫,32 比特系統錶文件最大可支持 4GB,64 比特系統支持最大的錶文件為8TB。
  • MySQL 是可以定制的,采用了 GPL 協議,你可以修改源碼來開發自己的 MySQL 系統。

RDBMS 術語

在我們開始學習MySQL 數據庫前,讓我們先了解下RDBMS的一些術語

  • 數據庫: 數據庫是一些關聯錶的集合。
  • 數據錶: 錶是數據的矩陣。在一個數據庫中的錶看起來像一個簡單的電子錶格。
  • 列: 一列(數據元素) 包含了相同類型的數據, 例如郵政編碼的數據。
  • 行:一行(=元組,或記錄)是一組相關的數據,例如一條用戶訂閱的數據。
  • 冗餘:存儲兩倍數據,冗餘降低了性能,但提高了數據的安全性。
  • 主鍵:主鍵是唯一的。一個數據錶中只能包含一個主鍵。你可以使用主鍵來查詢數據。
  • 外鍵:外鍵用於關聯兩個錶。
  • 複合鍵:複合鍵(組合鍵)將多個列作為一個索引鍵,一般用於複合索引。
  • 索引:使用索引可快速訪問數據庫錶中的特定信息。索引是對數據庫錶中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
  • 參照完整性: 參照的完整性要求關系中不允許引用不存在的實體。與實體完整性是關系模型必須滿足的完整性約束條件,目的是保證數據的一致性。

MySQL 為關系型數據庫(Relational Database Management System), 這種所謂的關系型可以理解為錶格的概念, 一個關系型數據庫由一個或數個錶格組成, 如圖所示的一個錶格

數據庫錶的存儲比特置

MySQL數據錶以文件方式存放在磁盤中:

  1. 包括錶文件、數據文件以及數據庫的選項文件
  2. 比特置:MySQL安裝目錄\data下存放數據錶。目錄名對應數據庫名,該目錄下文件名對應數據錶

注:

InnoDB類型數據錶只有一個*. frm文件,以及上一級目錄的ibdata1文件
MylSAM類型數據錶對應三個文件:

  1. *. frm —— 錶結構定義文件
  2. *. MYD —— 數據文件
  3. *. MYI —— 索引文件

存儲比特置:因操作系統而异,可查my.ini


【數據類型】

  • MySQL提供的數據類型包括數值類型(整數類型和小數類型)、字符串類型、日期類型、複合類型(複合類型包括enum類型和set類型)以及二進制類型 。

一. 整數類型

在這裏插入圖片描述

  • 整數類型的數,默認情况下既可以錶示正整數又可以錶示負整數(此時稱為有符號數)。如果只希望錶示零和正整數,可以使用無符號關鍵字“unsigned”對整數類型進行修飾。
  • 各個類別存儲空間及取值範圍。

在這裏插入圖片描述

二. 小數類型

在這裏插入圖片描述

  • decimal(length, precision)用於錶示精度確定(小數點後數字的比特數確定)的小數類型,length决定了該小數的最大比特數,precision用於設置精度(小數點後數字的比特數)。

  • 例如: decimal (5,2)錶示小數取值範圍:999.99~999.99 decimal (5,0)錶示: -99999~99999的整數。

  • 各個類別存儲空間及取值範圍。

在這裏插入圖片描述

三. 字符串

在這裏插入圖片描述

  • char()與varchar(): 例如對於簡體中文字符集gbk的字符串而言,varchar(255)錶示可以存儲255個漢字,而每個漢字占用兩個字節的存儲空間。假如這個字符串沒有那麼多漢字,例如僅僅包含一個‘中’字,那麼varchar(255)僅僅占用1個字符(兩個字節)的儲存空間;而char(255)則必須占用255個字符長度的存儲空間,哪怕裏面只存儲一個漢字。
  • 各個類別存儲空間及取值範圍。

在這裏插入圖片描述

四. 日期類型

  • date錶示日期,默認格式為‘YYYY-MM-DD’; time錶示時間,格式為‘HH:ii:ss’; year錶示年份; datetime與timestamp是日期和時間的混合類型,格式為’YYYY-MM-DD HH:ii:ss’。
    在這裏插入圖片描述
  • datetime與timestamp都是日期和時間的混合類型,區別在於: 錶示的取值範圍不同,datetime的取值範圍遠遠大於timestamp的取值範圍。 將NULL插入timestamp字段後,該字段的值實際上是MySQL服務器當前的日期和時間。 同一個timestamp類型的日期或時間,不同的時區,顯示結果不同。
  • 各個類別存儲空間及取值範圍。

在這裏插入圖片描述

五. 複合類型

  • MySQL 支持兩種複合數據類型:enum枚舉類型和set集合類型。 enum類型的字段類似於單選按鈕的功能,一個enum類型的數據最多可以包含65535個元素。 set 類型的字段類似於複選框的功能,一個set類型的數據最多可以包含64個元素。

六. 二進制類型

  • 二進制類型的字段主要用於存儲由‘0’和‘1’組成的字符串,因此從某種意義上將,二進制類型的數據是一種特殊格式的字符串。
  • 二進制類型與字符串類型的區別在於:字符串類型的數據按字符為單比特進行存儲,因此存在多種字符集、多種字符序;而二進制類型的數據按字節為單比特進行存儲,僅存在二進制字符集binary。

在這裏插入圖片描述


【約束】

  • 約束是一種限制,它通過對錶的行或列的數據做出限制,來確保錶的數據的完整性、唯一性。下面文章就來給大家介紹一下6種mysql常見的約束,希望對大家有所幫助。

一. 非空約束(not null)

  • 非空約束用於確保當前列的值不為空值,非空約束只能出現在錶對象的列上。

  • Null類型特征:所有的類型的值都可以是null,包括int、float 等數據類型

在這裏插入圖片描述

二. 唯一性約束(unique)

  • 唯一約束是指定table的列或列組合不能重複,保證數據的唯一性。
  • 唯一約束不允許出現重複的值,但是可以為多個null。
  • 同一個錶可以有多個唯一約束,多個列組合的約束。
  • 在創建唯一約束時,如果不給唯一約束名稱,就默認和列名相同。
  • 唯一約束不僅可以在一個錶內創建,而且可以同時多錶創建組合唯一約束。

在這裏插入圖片描述

三. 主鍵約束(primary key) PK

  • 主鍵約束相當於 唯一約束 + 非空約束 的組合,主鍵約束列不允許重複,也不允許出現空值。

  • 每個錶最多只允許一個主鍵,建立主鍵約束可以在列級別創建,也可以在錶級別創建。

  • 當創建主鍵的約束時,系統默認會在所在的列和列組合上建立對應的唯一索引。

在這裏插入圖片描述

四. 外鍵約束(foreign key) FK

  • 外鍵約束是用來加强兩個錶(主錶和從錶)的一列或多列數據之間的連接的,可以保證一個或兩個錶之間的參照完整性,外鍵是構建於一個錶的兩個字段或是兩個錶的兩個字段之間的參照關系。

  • 創建外鍵約束的順序是先定義主錶的主鍵,然後定義從錶的外鍵。也就是說只有主錶的主鍵才能被從錶用來作為外鍵使用,被約束的從錶中的列可以不是主鍵,主錶限制了從錶更新和插入的操作。
    在這裏插入圖片描述

五. 默認值約束 (Default)

  • 若在錶中定義了默認值約束,用戶在插入新的數據行時,如果該行沒有指定數據,那麼系統將默認值賦給該列,如果我們不設置默認值,系統默認為NULL。

在這裏插入圖片描述

六. 自增約束(AUTO_INCREMENT)

  • 自增約束(AUTO_INCREMENT)可以約束任何一個字段,該字段不一定是PRIMARY KEY字段,也就是說自增的字段並不等於主鍵字段。

  • 但是PRIMARY_KEY約束的主鍵字段,一定是自增字段,即PRIMARY_KEY 要與AUTO_INCREMENT一起作用於同一個字段。

在這裏插入圖片描述

當插入第一條記錄時,自增字段沒有給定一個具體值,可以寫成DEFAULT/NULL,那麼以後插入字段的時候,該自增字段就是從1開始,沒插入一條記錄,該自增字段的值增加1。當插入第一條記錄時,給自增字段一個具體值,那麼以後插入的記錄在此自增字段上的值,就在第一條記錄該自增字段的值的基礎上每次增加1。也可以在插入記錄的時候,不指定自增字段,而是指定其餘字段進行插入記錄的操作。


【常用命令】

登錄數據庫相關命令

一. 啟動服務

語法:

mysql> net stop mysql

二. 關閉服務

語法:

mysql> net start mysql

三. 鏈接MySQL

  • 語法:mysql -u用戶名 -p密碼;
[email protected]:/ mysql -uroot -p123456;
  • 在以上命令行中,mysql 代錶客戶端命令,-u 後面跟連接的數據庫用戶,-p 錶示需要輸入密碼。如果數據庫設置正常,並輸入正確的密碼,將看到上面一段歡迎界面和一個 mysql>提示符。
    在這裏插入圖片描述

四. 退出數據庫

  • 語法:quit
mysql> quit
  • 結果:
    在這裏插入圖片描述

DDL(Data Definition Languages)語句:即數據庫定義語句

對於數據庫而言實際上每一張錶都錶示是一個數據庫的對象,而數據庫對象指的就是DDL定義的所有操作,例如:錶,視圖,索引,序列,約束等等,都屬於對象的操作,所以錶的建立就是對象的建立,而對象的操作主要分為以下三類語法

  • 創建對象:CREATE 對象名稱;
  • 删除對象:DROP 對象名稱;
  • 修改對象:ALTER 對象名稱;

數據庫相關操作

在這裏插入圖片描述

一. 創建數據庫

  • 語法:create database 數據庫名字;
mysql> create database sqltest;
  • 結果:
    在這裏插入圖片描述

二. 查看已經存在的數據庫

  • 語法:show databases;
mysql> show databases;
  • 結果:
    在這裏插入圖片描述

可以發現,在上面的列錶中除了剛剛創建的 mzc-test,sqltest,外,還有另外 4 個數據庫,它們都是安裝MySQL 時系統自動創建的,其各自功能如下。

  1. information_schema:主要存儲了系統中的一些數據庫對象信息。比如用戶錶信息、列信息、權限信息、字符集信息、分區信息等。
  2. cluster:存儲了系統的集群信息。
  3. mysql:存儲了系統的用戶權限信息。
  4. test:系統自動創建的測試數據庫,任何用戶都可以使用。

三. 選擇數據庫

  • 語法:use 數據庫名;
mysql> use mzc-test;
  • 返回Database changed代錶我們已經選擇 sqltest 數據庫,後續所有操作將在 sqltest 數據庫上執行。
    在這裏插入圖片描述
  • 有些人可能會問到,連接以後怎麼退出。其實,不用退出來,use 數據庫後,使用show databases就能查詢所有數據庫,如果想跳到其他數據庫,用use 其他數據庫名字。

四. 查看數據庫中的錶

  • 語法:show tables;
mysql> show tables;
  • 結果:
    在這裏插入圖片描述

五. 删除數據庫

  • 語法:drop database 數據庫名稱;
mysql> drop database mzc-test;
  • 結果:
    在這裏插入圖片描述
  • 注意:删除時,最好用 `` 符號把錶明括起來

六. 設置錶的類型

  • MySQL的數據錶類型:MyISAMInnoDB、HEAP、 BOB、CSV等

在這裏插入圖片描述
語法:

CREATE TABLE 錶名(
#省略代碼ENGINE= InnoDB;

適用場景:

1. 使用MyISAM:節約空間及響應速度快;不需事務,空間小,以查詢訪問為主
2. 使用InnoDB:安全性,事務處理及多用戶操作數據錶;多删除、更新操作,安全性高,事務處理及並發控制
1. 查看mysql所支持的引擎類型

語法:

SHOW ENGINES

結果:

在這裏插入圖片描述

2. 查看默認引擎

語法:

SHOW VARIABLES LIKE 'storage_engine';

結果:
在這裏插入圖片描述


數據庫錶相關操作

在這裏插入圖片描述

一. 創建錶

語法:create table 錶名 {列名,數據類型,約束條件};

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
  • 結果
    在這裏插入圖片描述

注意:錶名還請遵守數據庫的命名規則,這條數據後面要進行删除,所以首字母為大寫。

二. 查看錶定義

  • 語法:desc 錶名
mysql> desc Student;
  • 結果:
    在這裏插入圖片描述
  • 雖然 desc 命令可以查看錶定義,但是其輸出的信息還是不够全面,為了查看更全面的錶定義信息,有時就需要通過查看創建錶的 SQL 語句來得到,可以使用如下命令實現
  • 語法:show create table 錶名 \G;
mysql> show create table Student \G;
  • 結果:
    在這裏插入圖片描述
  • 從上面錶的創建 SQL 語句中,除了可以看到錶定義以外,還可以看到錶的engine(存儲引擎)和charset(字符集)等信息。\G選項的含義是使得記錄能够按照字段豎著排列,對於內容比較長的記錄更易於顯示。

三. 删除錶

  • 語法:drop table 錶名
mysql> drop table Student;
  • 結果:

在這裏插入圖片描述

四. 修改錶 (重要)

  • 對於已經創建好的錶,尤其是已經有大量數據的錶,如果需要對錶做一些結構上的改變,我們可以先將錶删除(drop),然後再按照新的錶定義重建錶。這樣做沒有問題,但是必然要做一些額外的工作,比如數據的重新加載。而且,如果有服務在訪問錶,也會對服務產生影響。因此,在大多數情况下,錶結構的更改一般都使用 alter table語句,以下是一些常用的命令。
1. 修改錶類型
  • 語法:ALTER TABLE 錶名 MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  • 例如,修改錶 student 的 s_name 字段定義,將 varchar(20)改為 varchar(30)
mysql> alter table Student modify s_name varchar(30);
  • 結果:
    在這裏插入圖片描述
2. 增加錶字段
  • 語法:ALTER TABLE 錶名 ADD [COLUMN] [FIRST | AFTER col_name];
  • 例如,錶 student 上新增加字段 s_test,類型為 int(3)
mysql> alter table student add column s_test int(3);
  • 結果:
    在這裏插入圖片描述
3. 删除錶字段
  • 語法:ALTER TABLE 錶名 DROP [COLUMN] col_name
  • 例如,將字段 s_test 删除掉
mysql> alter table Student drop column s_test;
  • 結果:
    在這裏插入圖片描述
4. 字段改名
  • 語法:ALTER TABLE 錶名 CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
  • 例如,將 s_sex 改名為 s_sex1,同時修改字段類型為 int(4)
mysql> alter table Student change s_sex s_sex1 int(4);
  • 結果:
    在這裏插入圖片描述

注意:change 和 modify 都可以修改錶的定義,不同的是 change 後面需要寫兩次列名,不方便。但是 change 的優點是可以修改列名稱,modify 則不能。

5. 修改字段排列順序
  • 前面介紹的的字段增加和修改語法(ADD/CNAHGE/MODIFY)中,都有一個可選項first|after column_name,這個選項可以用來修改字段在錶中的比特置,默認 ADD 增加的新字段是加在錶的最後比特置,而 CHANGE/MODIFY 默認都不會改變字段的比特置。

  • 例如,將新增的字段 s_test 加在 s_id 之後

  • 語法:alter table 錶名 add 列名 數據類型 after 列名;

mysql> alter table Student add s_test date after s_id;
  • 結果:
    在這裏插入圖片描述
  • 修改已有字段 s_name,將它放在最前面
mysql> alter table Student modify s_name varchar(30) default '' first;
  • 結果:
    在這裏插入圖片描述

注意:CHANGE/FIRST|AFTER COLUMN 這些關鍵字都屬於 MySQL 在標准 SQL 上的擴展,在其他數據庫上不一定適用。

6.錶名修改
  • 語法:ALTER TABLE 錶名 RENAME [TO] new_tablename
  • 例如,將錶 Student 改名為 student
mysql> alter table Student rename student;
  • 結果:
    在這裏插入圖片描述

DML(Data Manipulation Language)語句:即數據操縱語句

  • 用於操作數據庫對象中所包含的數據

一. 添加數據:INSERT

Insert 語句用於向數據庫中插入數據

1. 插入單條數據(常用)

語法:insert into 錶名(列名1,列名2,...) values(值1,值2,...)

特點:

  • 插入值的類型要與列的類型一致或兼容。插入NULL可實現為列插入NULL值。列的順序可以調換。列數和值的個數必須一致。可省略列名,默認所有列,並且列的順序和錶中列的順序一致。

案例:

-- 插入學生錶測試數據
insert into Student(s_id,s_name,s_birth,s_sex) values('01' , '趙信' , '1990-01-01' , '男');

在這裏插入圖片描述

2. 插入單條數據

語法:INSERT INTO 錶名 SET 列名 = 值,列名 = 值

  • 這種方式每次只能插入一行數據,每列的值通過賦值列錶制定。

案例:

INSERT INTO student SET s_id='02',s_name='德萊厄斯',s_birth='1990-01-01',s_sex='男'

在這裏插入圖片描述

3. 插入多條數據

語法:insert into 錶名 values(值1,值2,值3),(值4,值5,值6),(值7,值8,值9);

案例:

INSERT INTO student VALUES('03','艾希','1990-01-01','女'),('04','德萊文','1990-08-06','男'),('05','俄洛依','1991-12-01','女');

在這裏插入圖片描述

上面的例子中,值1,值2,值3),(值4,值5,值6),(值7,值8,值9) 即為 Value List,其中每個括號內部的數據錶示一行數據,這個例子中插入了三行數據。Insert 語句也可以只給部分列插入數據,這種情况下,需要在 Value List 之前加上 ColumnName List,

例如:

INSERT INTO student(s_name,s_sex) VALUES('艾希','女'),('德萊文','男');
  • 每行數據只指定了 s_name 和 s_sex 這兩列的值,其他列的值會設為 Null。

4. 錶數據複制

語法:INSERT INTO 錶名 SELECT * from 錶名;

案例:

INSERT INTO student SELECT * from student1;

在這裏插入圖片描述注意:

  • 兩個錶的字段需要一直,並盡量保證要新增的錶中沒有數據

二. 更新數據:UPDATE

Update 語句一共有兩種語法,分別用於更新單錶數據和多錶數據。

在這裏插入圖片描述

  • 注意:沒有 WHERE 條件的 UPDATE 會更新所有值!

1. 修改一條數據的某個字段

語法:UPDATE 錶名 SET 字段名 =值 where 字段名=值

案例:

UPDATE student SET s_name ='張三' WHERE s_id ='01'

在這裏插入圖片描述

2. 修改多個字段為同一的值

語法:UPDATE 錶名 SET 字段名= 值 WHERE 字段名 in ('值1','值2','值3');

案例:

UPDATE student SET s_name = '李四' WHERE s_id in ('01','02','03');

在這裏插入圖片描述

3. 使用case when實現批量更新

語法:update 錶名 set 字段名 = case 字段名 when 值1 then '值' when 值2 then '值' when 值3 then '值' end where s_id in (值1,值2,值3)

案例:

update student set s_name = case s_id when 01 then '小王' when 02 then '小周' when 03 then '老周' end where s_id in (01,02,03)

在這裏插入圖片描述

  • 這句sql的意思是,更新 s_name 字段,如果 s_id 的值為 01 則 s_name 的值為 小王,s_id = 02 則 s_name = 小周,如果s_id =03 則 s_name 的值為 老周。
  • 這裏的where部分不影響代碼的執行,但是會提高sql執行的效率。確保sql語句僅執行需要修改的行數,這裏只有3條數據進行更新,而where子句確保只有3行數據執行。

案例 2:

UPDATE student SET s_birth = CASE s_name
WHEN '小王' THEN
'2019-01-20'
WHEN '小周' THEN
'2019-01-22'
END WHERE s_name IN ('小王','小周');

在這裏插入圖片描述

三. 删除數據:DELETE

  • 數據庫一旦删除數據,它就會永遠消失。 因此,在執行DELETE語句之前,應該先備份數據庫,以防萬一要找回删除過的數據。

1. 删除指定數據

語法:DELETE FROM 錶名 WHERE 列名=值

  • 注意:删除的時候如果不指定where條件,則保留數據錶結構,删除全部數據行,有主外鍵關系的都删不了

案例:

DELETE FROM student WHERE s_id='09'

在這裏插入圖片描述與 SELECT 語句不同的是,DELETE 語句中不能使用 GROUP BY、 HAVING 和 ORDER BY 三類子句,而只能使用WHERE 子句。原因很簡單, GROUP BY 和 HAVING 是從錶中選取數據時用來改變抽取數據形式的, 而 ORDER BY 是用來指定取得結果顯示順序的。因此,在删除錶中數據 時它們都起不到什麼作用。`

2. 删除錶中全部數據

語法:TRUNCATE 錶名;

  • 注意:全部删除,內存無痕迹,如果有自增會重新開始編號。

  • 與 DELETE 不同的是,TRUNCATE 只能删除錶中的全部數據,而不能通過 WHERE 子句指定條件來删除部分數據。也正是因為它不能具體地控制删除對象, 所以其處理速度比 DELETE 要快得多。實際上,DELETE 語句在 DML 語句中也 屬於處理時間比較長的,因此需要删除全部數據行時,使用 TRUNCATE 可以縮短 執行時間。

案例:

TRUNCATE student1;

在這裏插入圖片描述


DQL(Data Query Language)語句:即數據查詢語句

  • 查詢數據庫中的記錄,關鍵字 SELECT,這塊內容非常重要!

一. wherer 條件語句

語法:select 列名 from 錶名 where 列名 =值

where的作用:

  1. 用於檢索數據錶中符合條件的記錄
  2. 搜索條件可由一個或多個邏輯錶達式組成,結果一般為真或假

搜索條件的組成:

  • 算數運算符

在這裏插入圖片描述

  • 邏輯操作符(操作符有兩種寫法)
    在這裏插入圖片描述
  • 比較運算符

在這裏插入圖片描述

注意:數值數據類型的記錄之間才能進行算術運算,相同數據類型的數據之間才能進行比較。

錶數據
在這裏插入圖片描述

案例 1(AND):

SELECT * FROM student WHERE s_name ='小王' AND s_sex='男'

在這裏插入圖片描述
案例 2(OR):

SELECT * FROM student WHERE s_name ='崔絲塔娜' OR s_sex='男'

在這裏插入圖片描述

案例 3(NOT):

SELECT * FROM student WHERE NOT s_name ='崔絲塔娜'

在這裏插入圖片描述
案例 4(IS NULL):

SELECT * FROM student WHERE s_name IS NULL;

在這裏插入圖片描述
案例 5(IS NOT NULL):

SELECT * FROM student WHERE s_name IS NOT NULL;

在這裏插入圖片描述
案例 6(BETWEEN):

SELECT * FROM student WHERE s_birth BETWEEN '2019-01-20' AND '2019-01-22'

在這裏插入圖片描述

案例 7(LINK):

SELECT * FROM student WHERE s_name LIKE '小%'

在這裏插入圖片描述

案例 8(IN):

SELECT * FROM student WHERE s_name IN ('小王','小周')

在這裏插入圖片描述

二. as 取別名

  • 錶裏的名字沒有變,只影響了查詢出來的結果

案例:

SELECT s_name as `name` FROM student

在這裏插入圖片描述

  • 使用as也可以為錶取別名 (作用:單錶查詢意義不大,但是當多個錶的時候取別名就好操作,當不同的錶裏有相同名字的列的時候區分就會好區分)

三. distinct 去除重複記錄

  • 注意:當查詢結果中所有字段全都相同時 才算重複的記錄

案例

SELECT DISTINCT * FROM student

在這裏插入圖片描述

指定字段

  1. 星號錶示所有字段
  2. 手動指定需要查詢的字段
SELECT DISTINCT s_name,s_birth FROM student

在這裏插入圖片描述

  1. 還可也是四則運算
  2. 聚合函數

四. group by 分組

  • group by的意思是根據by對數據按照哪個字段進行分組,或者是哪幾個字段進行分組。

語法:

select 字段名 from 錶名 group by 字段名稱;

1. 單個字段分組

SELECT COUNT(*)FROM student GROUP BY s_sex;

在這裏插入圖片描述

2. 多個字段分組

SELECT s_name,s_sex,COUNT(*) FROM student GROUP BY s_name,s_sex;

在這裏插入圖片描述

  • 注意:多個字段進行分組時,需要將s_name和s_sex看成一個整體,只要是s_name和s_sex相同的可以分成一組;如果只是s_sex相同,s_sex不同就不是一組。

五. having 過濾

  • HAVING 子句對 GROUP BY 子句設置條件的方式與 WHERE 和 SELECT 的交互方式類似。WHERE 搜索條件在進行分組操作之前應用;而 HAVING 搜索條件在進行分組操作之後應用。HAVING 語法與 WHERE 語法類似,但 HAVING 可以包含聚合函數。HAVING 子句可以引用選擇列錶中顯示的任意項。

我們如果要查詢男生或者女生,人數大於4的性別


SELECT s_sex as 性別,count(s_id) AS 人數 FROM student GROUP BY s_sex HAVING COUNT(s_id)>4

在這裏插入圖片描述

六. order by 排序

  • 根據某個字段排序,默認昇序(從小到大)

語法:

select * from 錶名 order by 字段名;

1. 一個字段,降序(從大到小)

SELECT * FROM student ORDER BY s_id DESC;

在這裏插入圖片描述

2. 多個字段

SELECT * FROM student ORDER BY s_id DESC, s_birth ASC;

在這裏插入圖片描述

  • 多個字段 第一個相同在按照第二個 asc 錶示昇序

limit 分頁

  • 用於限制要顯示的記錄數量

語法1:

select * from table_name limit 個數;

語法2:

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

案例:

  • 查詢前三條數據
SELECT * FROM student LIMIT 3;

在這裏插入圖片描述

  • 從第三條開始 查詢3條
SELECT * FROM student LIMIT 2,3;

在這裏插入圖片描述

注意:起始比特置 從0開始

經典的使用場景:分頁顯示

  1. 每一頁顯示的條數 a = 3
  2. 明確當前頁數 b = 2
  3. 計算起始比特置 c = (b-1) * a

子查詢

  • 將一個查詢語句的結果作為另一個查詢語句的條件或是數據來源,​ 當我們一次性查不到想要數據時就需要使用子查詢。
SELECT
*
FROM
score
WHERE
s_id =(
SELECT
s_id
FROM
student
WHERE
s_name = '趙信')

在這裏插入圖片描述

1. in 關鍵字子查詢

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

子查詢的思路:

  1. 要分析 查到最終的數據 到底有哪些步驟
  2. 根據步驟寫出對應的sql語句
  3. 把上一個步驟的sql語句丟到下一個sql語句中作為條件
SELECT
*
FROM
score
WHERE
s_id IN (
SELECT
s_id
FROM
student
WHERE
s_sex = '男')

在這裏插入圖片描述

exists 關鍵字子查詢

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

多錶查詢

1. 笛卡爾積查詢

  • 笛卡爾積查詢的結果會出現大量的錯誤數據即,數據關聯關系錯誤,並且會產生重複的字段信息 !

2. 內連接查詢

  • 本質上就是笛卡爾積查詢,inner可以省略。

在這裏插入圖片描述

語法:

select * from1 inner join2;

3. 左外連接查詢

  • 左邊的錶無論是否能够匹配都要完整顯示,右邊的僅展示匹配上的記錄

在這裏插入圖片描述

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

4. 右外連接查詢

  • 右邊的錶無論是否能够匹配都要完整顯示,左邊的僅展示匹配上的記錄

在這裏插入圖片描述


DCL(Data Control Language)語句:即數據控制語句

  • DCL(Data Control Language)語句:數據控制語句,用於控制不同數據段直接的許可和訪問級別的語句。這些語句定義了數據庫、錶、字段、用戶的訪問權限和安全級別。

關鍵字

  • GRANT
  • REVOKE

查看用戶權限

當成功創建用戶賬戶後,還不能執行任何操作,需要為該用戶分配適當的訪問權限。可以使用SHOW GRANTS FOR語句來查詢用戶的權限。

例如:

mysql> SHOW GRANTS FOR test;
+-------------------------------------------+
| Grants for [email protected]% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

GRANT語句

  • 對於新建的MySQL用戶,必須給它授權,可以用GRANT語句來實現對新建用戶的授權。

格式語法

GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH {
GRANT OPTION | resource_option} ...]
GRANT PROXY ON user
TO user [, user] ...
[WITH GRANT OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'auth_string'
| IDENTIFIED BY PASSWORD 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}

權限類型(priv_type)

  • 授權的權限類型一般可以分為數據庫、錶、列、用戶。
授予數據庫權限類型

授予數據庫權限時,priv_type可以指定為以下值:

  • SELECT:錶示授予用戶可以使用 SELECT 語句訪問特定數據庫中所有錶和視圖的權限。
  • INSERT:錶示授予用戶可以使用 INSERT 語句向特定數據庫中所有錶添加數據行的權限。
  • DELETE:錶示授予用戶可以使用 DELETE 語句删除特定數據庫中所有錶的數據行的權限。
  • UPDATE:錶示授予用戶可以使用 UPDATE 語句更新特定數據庫中所有數據錶的值的權限。
  • REFERENCES:錶示授予用戶可以創建指向特定的數據庫中的錶外鍵的權限。
  • CREATE:錶示授權用戶可以使用 CREATE TABLE 語句在特定數據庫中創建新錶的權限。
  • ALTER:錶示授予用戶可以使用 ALTER TABLE 語句修改特定數據庫中所有數據錶的權限。
  • SHOW VIEW:錶示授予用戶可以查看特定數據庫中已有視圖的視圖定義的權限。
  • CREATE ROUTINE:錶示授予用戶可以為特定的數據庫創建存儲過程和存儲函數的權限。
  • ALTER ROUTINE:錶示授予用戶可以更新和删除數據庫中已有的存儲過程和存儲函數的權限。
  • INDEX:錶示授予用戶可以在特定數據庫中的所有數據錶上定義和删除索引的權限。
  • DROP:錶示授予用戶可以删除特定數據庫中所有錶和視圖的權限。
  • CREATE TEMPORARY TABLES:錶示授予用戶可以在特定數據庫中創建臨時錶的權限。
  • CREATE VIEW:錶示授予用戶可以在特定數據庫中創建新的視圖的權限。
  • EXECUTE ROUTINE:錶示授予用戶可以調用特定數據庫的存儲過程和存儲函數的權限。
  • LOCK TABLES:錶示授予用戶可以鎖定特定數據庫的已有數據錶的權限。
  • SHOW DATABASES:錶示授權可以使用SHOW DATABASES語句查看所有已有的數據庫的定義的權限。
  • ALL或ALL PRIVILEGES:錶示以上所有權限。

授予錶權限類型

授予錶權限時,priv_type可以指定為以下值:

  • SELECT:授予用戶可以使用 SELECT 語句進行訪問特定錶的權限。
  • INSERT:授予用戶可以使用 INSERT 語句向一個特定錶中添加數據行的權限。
  • DELETE:授予用戶可以使用 DELETE 語句從一個特定錶中删除數據行的權限。
  • DROP:授予用戶可以删除數據錶的權限。
  • UPDATE:授予用戶可以使用 UPDATE 語句更新特定數據錶的權限。
  • ALTER:授予用戶可以使用 ALTER TABLE 語句修改數據錶的權限。
  • REFERENCES:授予用戶可以創建一個外鍵來參照特定數據錶的權限。
  • CREATE:授予用戶可以使用特定的名字創建一個數據錶的權限。
  • INDEX:授予用戶可以在錶上定義索引的權限。
  • ALL或ALL PRIVILEGES:所有的權限名。
授予列(字段)權限類型
  • 授予列(字段)權限時,priv_type的值只能指定為SELECT、INSERT和UPDATE,同時權限的後面需要加上列名列錶(column-list)。
授予創建和删除用戶的權限
  • 授予列(字段)權限時,priv_type的值指定為CREATE USER權限,具備創建用戶、删除用戶、重命名用戶和撤消所有特權,而且是全局的。

ON

  • 有ON,是授予權限,無ON,是授予角色。如:
-- 授予數據庫db1的所有權限給指定賬戶
GRANT ALL ON db1.* TO 'user1'@'localhost';
-- 授予角色給指定的賬戶
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

對象類型(object_type)

  • 在ON關鍵字後給出要授予權限的object_type,通常object_type可以是數據庫名、錶名等。

權限級別(priv_level)

指定權限級別的值有以下幾類格式:

  • *:錶示當前數據庫中的所有錶。
  • .:錶示所有數據庫中的所有錶。
  • db_name.*:錶示某個數據庫中的所有錶,db_name指定數據庫名。
  • db_name.tbl_name:錶示某個數據庫中的某個錶或視圖,db_name指定數據庫名,tbl_name指定錶名或視圖名。
  • tbl_name:錶示某個錶或視圖,tbl_name指定錶名或視圖名。
  • db_name.routine_name:錶示某個數據庫中的某個存儲過程或函數,routine_name指定存儲過程名或函數名。

被授權的用戶(user)

'user_name'@'host_name'
  • Tips:'host_name’用於適應從任意主機訪問數據庫而設置的,可以指定某個地址或地址段訪問。
  • 可以同時授權多個用戶。

user錶中host列的默認值

host 說明
% 匹配所有主機
localhost localhost不會被解析成IP地址,直接通過UNIXsocket連接
127.0.0.1 會通過TCP/IP協議連接,並且只能在本機訪問
::1 ::1就是兼容支持ipv6的,錶示同ipv4的127.0.0.1

host_name格式有以下幾種:

  • 使用%模糊匹配,符合匹配條件的主機可以訪問該數據庫實例,例如192.168.2.%或%.test.com;
  • 使用localhost、127.0.0.1、::1及服務器名等,只能在本機訪問;
  • 使用ip地址或地址段形式,僅允許該ip或ip地址段的主機訪問該數據庫實例,例如192.168.2.1或192.168.2.0/24或192.168.2.0/255.255.255.0;
  • 省略即默認為%。

身份驗證方式(auth_option)

  • auth_option為可選字段,可以指定密碼以及認證插件(mysql_native_password、sha256_password、caching_sha2_password)。

加密連接(tls_option)

  • tls_option為可選的,一般是用來加密連接。

用戶資源限制(resource_option)

  • resource_option為可選的,一般是用來指定最大連接數等。
參數 說明
MAX_QUERIES_PER_HOUR count 每小時最大查詢數
MAX_UPDATES_PER_HOUR count 每小時最大更新數
MAX_CONNECTIONS_PER_HOUR count 每小時連接次數
MAX_USER_CONNECTIONS count 用戶最大連接數

權限生效

  • 若要權限生效,需要執行以下語句:
FLUSH PRIVILEGES;

REVOKE語句

  • REVOKE語句主要用於撤銷權限。

語法格式

  • REVOKE語法和GRANT語句的語法格式相似,但具有相反的效果
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
  • 若要使用REVOKE語句,必須擁有MySQL數據庫的全局CREATE USER權限或UPDATE權限;
  • 第一種語法格式用於回收指定用戶的某些特定的權限,第二種回收指定用戶的所有權限;

TCL(Transaction Control Language)語句:事務控制語句

什麼是事物?

  • 一個或一組sql語句組成一個執行單元,這個執行單元要麼全部執行,要麼全部不執行

事務的ACID屬性

  • 原子性:事務是一個不可分割的工作單比特,事務中的操作要麼都發生,要麼都不發生

  • 一致性:事務必須使數據庫從一個一致性狀態變換到另外一個一致性狀態

  • 隔離性:一個事務的執行不能被其他事務幹擾,即一個事務內部的操作及使用的數據對並發的其他事務是隔離的,並發執行的各個事務之間不能互相幹擾

  • 持久性:一個事務一旦被提交,它對數據庫中數據的改變就是永久性的,接下來的其他操作和數據庫故障不應該對其有任何影響

分類

  • 隱式事務:事務沒有明顯的開啟和結束的標記(比如insert,update,delete語句)

  • 顯式事務:事務具有明顯的開啟和結束的標記(autocommit變量設置為0)

事務的使用步驟

開啟事務

  • 默認開啟事務
SET autocommit = 0 ;

提交事務

COMMIT;

回滾事務

ROLLBACK ;

查看當前的事務隔離級別

select @@tx_isolation;

設置當前連接事務的隔離級別

set session transaction isolation level read uncommitted;

設置數據庫系統的全局的隔離級別

set global transaction isolation level read committed ;

【常用函數】

  • MySQL提供了眾多功能强大、方便易用的函數,使用這些函數,可以極大地提高用戶對於數據庫的管理效率,從而更加靈活地滿足不同用戶的需求。本文將MySQL的函數分類並匯總,以便以後用到的時候可以隨時查看。

(這裏使用 Navicat Premium 15 工具進行演示)

在這裏插入圖片描述

因為內容太多了這裏只演示一些常用的在這裏插入圖片描述

一. 數學函數

對數值型的數據進行指定的數學運算,如abs()函數可以獲得給定數值的絕對值,round()函數可以對給定的數值進行四舍五入。

1. ABS(number)

  • 作用:返回 number 的絕對值
SELECT
ABS(s_score)
FROM
score;

在這裏插入圖片描述

在這裏插入圖片描述

  • ABS(-86) 返回:86

  • number 參數可以是任意有效的數值錶達式。如果 number 包含 Null,則返回 Null;如果是未初始化變量,則返回 0。

2. PI()

  • 例1:pi() 返回:3.141592653589793

  • 例2:pi(2) 返回:6.283185307179586

  • 作用:計算圓周率及其倍數

3. SQRT(x)

  • 作用:返回非負數的x的二次方根

4. MOD(x,y)

  • 作用:返回x被y除後的餘數

5. CEIL(x)、CEILING(x)

  • 作用:返回不小於x的最小整數

6. FLOOR(x)

  • 作用:返回不大於x的最大整數

7. FLOOR(x)

  • 作用:返回不大於x的最大整數

8. ROUND(x)、ROUND(x,y)

  • 作用:前者返回最接近於x的整數,即對x進行四舍五入;後者返回最接近x的數,其值保留到小數點後面y比特,若y為負值,則將保留到x到小數點左邊y比特
SELECT ROUND(345222.9)

在這裏插入圖片描述

  • 參數說明: numberExp 需要進行截取的數據 nExp 整數,用於指定需要進行截取的比特置,>0:從小數點往右比特移nExp個比特數, <0:從小數點往左

nExp個比特數 =0:錶示當前小數點的比特置

9. POW(x,y)和、POWER(x,y)

  • 作用:返回x的y次乘方的值

10. EXP(x)

  • 作用:返回e的x乘方後的值

11. LOG(x)

  • 作用:返回x的自然對數,x相對於基數e的對數

12. LOG10(x)

  • 作用:返回x的基數為10的對數

13. RADIANS(x)

  • 作用:返回x由角度轉化為弧度的值

14. DEGREES(x)

  • 作用:返回x由弧度轉化為角度的值

15. SIN(x)、ASIN(x)

  • 作用:前者返回x的正弦,其中x為給定的弧度值;後者返回x的反正弦值,x為正弦

16. COS(x)、ACOS(x)

  • 作用:前者返回x的餘弦,其中x為給定的弧度值;後者返回x的反餘弦值,x為餘弦

17. TAN(x)、ATAN(x)

  • 作用:前者返回x的正切,其中x為給定的弧度值;後者返回x的反正切值,x為正切

18. COT(x)

  • 作用:返回給定弧度值x的餘切

二. 字符串函數

1. CHAR_LENGTH(str)

  • 作用:計算字符串字符個數
SELECT CHAR_LENGTH('這是一個十二個字的字符串');

在這裏插入圖片描述

2. CONCAT(s1,s2,…)

  • 作用:返回連接參數產生的字符串,一個或多個待拼接的內容,任意一個為NULL則返回值為NULL
SELECT CONCAT('拼接','測試');

在這裏插入圖片描述

3. CONCAT_WS(x,s1,s2,…)

  • 作用:返回多個字符串拼接之後的字符串,每個字符串之間有一個x
SELECT CONCAT_WS('-','測試','拼接','WS')

在這裏插入圖片描述

4. INSERT(s1,x,len,s2)

  • 作用:返回字符串s1,其子字符串起始於比特置x,被字符串s2取代len個字符
SELECT INSERT('測試字符串替換',2,1,'牛');

在這裏插入圖片描述

5. LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

  • 作用:前兩者將str中的字母全部轉換成小寫,後兩者將字符串中的字母全部轉換成大寫
SELECT LOWER('JHGYTUGHJGG'),LCASE('HKJHKJHKJHKJ');

在這裏插入圖片描述

SELECT UPPER('aaaaaa'),UCASE('vvvvv');

在這裏插入圖片描述

6. LEFT(s,n)、RIGHT(s,n)

  • 作用:前者返回字符串s從最左邊開始的n個字符,後者返回字符串s從最右邊開始的n個字符
SELECT LEFT('左邊開始',2),RIGHT('右邊開始',2);

在這裏插入圖片描述

7. LPAD(s1,len,s2)、RPAD(s1,len,s2)

  • 作用:前者返回s1,其左邊由字符串s2填補到len字符長度,假如s1的長度大於len,則返回值被縮短至len字符;前者返回s1,其右邊由字符串s2填補到len字符長度,假如s1的長度大於len,則返回值被縮短至len字符
SELECT LEFT('左邊開始',2),RIGHT('右邊開始',2);

在這裏插入圖片描述

8. LTRIM(s)、RTRIM(s)

  • 作用:前者返回字符串s,其左邊所有空格被删除;後者返回字符串s,其右邊所有空格被删除
SELECT LTRIM(' 左邊開始'),RTRIM(' 右邊開始 ');

在這裏插入圖片描述

9. TRIM(s)

  • 作用:返回字符串s删除了兩邊空格之後的字符串
SELECT TRIM(' 是是 ');

在這裏插入圖片描述

10. TRIM(s1 FROM s)

  • 作用:删除字符串s兩端所有子字符串s1,未指定s1的情况下則默認删除空格

11. REPEAT(s,n)

  • 作用:返回一個由重複字符串s組成的字符串,字符串s的數目等於n
SELECT REPEAT('測試',5);

在這裏插入圖片描述

12. SPACE(n)

  • 作用:返回一個由n個空格組成的字符串
SELECT SPACE(20);

在這裏插入圖片描述

13. REPLACE(s,s1,s2)

  • 作用:返回一個字符串,用字符串s2替代字符串s中所有的字符串s1

14. STRCMP(s1,s2)

  • 作用:若s1和s2中所有的字符串都相同,則返回0;根據當前分類次序,第一個參數小於第二個則返回-1,其他情况返回1
SELECT STRCMP('我我我','我我我');

在這裏插入圖片描述

SELECT STRCMP('我我我','是是是');

在這裏插入圖片描述

15. SUBSTRING(s,n,len)、MID(s,n,len)

  • 作用:兩個函數作用相同,從字符串s中返回一個第n個字符開始、長度為len的字符串
SELECT SUBSTRING('測試測試',2,2);

在這裏插入圖片描述

SELECT MID('測試測試',2,2);

在這裏插入圖片描述

16. LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

  • 作用:三個函數作用相同,返回子字符串str1在字符串str中的開始比特置(從第幾個字符開始)
SELECT LOCATE('字','獲取字符串的比特置');

在這裏插入圖片描述

17. REVERSE(s)

  • 作用:將字符串s反轉
SELECT REVERSE('字符串反轉');

在這裏插入圖片描述

18. ELT(N,str1,str2,str3,str4,…)

  • 作用:返回第N個字符串
SELECT ELT(2,'字符串反轉','sssss');

在這裏插入圖片描述

三. 日期和時間函數

當前時間
在這裏插入圖片描述

1. CURDATE()、CURRENT_DATE()

  • 作用:將當前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回,具體格式根據函數用在字符串或是數字語境中而定

2. CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

  • 作用:這四個函數作用相同,返回當前日期和時間值,格式為"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS",具體格式根據函數用在字符串或數字語境中而定
SELECT CURRENT_TIMESTAMP()

在這裏插入圖片描述

SELECT LOCALTIME()

在這裏插入圖片描述

SELECT NOW()

在這裏插入圖片描述

SELECT SYSDATE()

在這裏插入圖片描述

3. UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

  • 作用:前者返回一個格林尼治標准時間1970-01-01 00:00:00到現在的秒數,後者返回一個格林尼治標准時間1970-01-01 00:00:00到指定時間的秒數
SELECT UNIX_TIMESTAMP()

在這裏插入圖片描述

4. FROM_UNIXTIME(date)

  • 作用:和UNIX_TIMESTAMP互為反函數,把UNIX時間戳轉換為普通格式的時間

5. UTC_DATE()和UTC_TIME()

  • 前者返回當前UTC(世界標准時間)日期值,其格式為"YYYY-MM-DD"或"YYYYMMDD",後者返回當前UTC時間值,其格式為"YYYY-MM-DD"或"YYYYMMDD"。具體使用哪種取决於函數用在字符串還是數字語境中
SELECT UTC_DATE()

在這裏插入圖片描述

SELECT UTC_TIME()

在這裏插入圖片描述

6. MONTH(date)和MONTHNAME(date)

  • 作用:前者返回指定日期中的月份,後者返回指定日期中的月份的名稱
SELECT MONTH(NOW())

在這裏插入圖片描述

SELECT MONTHNAME(NOW())

在這裏插入圖片描述

7. DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

  • 作用:DAYNAME(d)返回d對應的工作日的英文名稱,如Sunday、Monday等;DAYOFWEEK(d)返回的對應一周中的索引,1錶示周日、2錶示周一;WEEKDAY(d)錶示d對應的工作日索引,0錶示周一,1錶示周二

8. WEEK(d)

  • 計算日期d是一年中的第幾周
SELECT WEEK(NOW())

在這裏插入圖片描述

9. DAYOFYEAR(d)、DAYOFMONTH(d)

  • 作用:前者返回d是一年中的第幾天,後者返回d是一月中的第幾天
SELECT DAYOFYEAR(NOW())

在這裏插入圖片描述

SELECT DAYOFMONTH(NOW())

在這裏插入圖片描述

10. YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

  • 作用: YEAR(date)返回指定日期對應的年份,範圍是1970~2069;QUARTER(date)返回date對應一年中的季度,範圍是1~4;MINUTE(time)返回time對應的分鐘數,範圍是0~59;SECOND(time)返回制定時間的秒值
SELECT YEAR(NOW())

在這裏插入圖片描述

SELECT QUARTER(NOW())

在這裏插入圖片描述

SELECT MINUTE(NOW())

在這裏插入圖片描述

SELECT SECOND(NOW())

在這裏插入圖片描述

11. EXTRACE(type FROM date)

  • 作用:從日期中提取一部分,type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

12. TIME_TO_SEC(time)

  • 作用:返回以轉換為秒的time參數,轉換公式為"3600小時 + 60分鐘 + 秒"
SELECT TIME_TO_SEC(NOW())

在這裏插入圖片描述

13. SEC_TO_TIME()

  • 作用:和TIME_TO_SEC(time)互為反函數,將秒值轉換為時間格式
SELECT SEC_TO_TIME(530)

在這裏插入圖片描述

14. DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

  • 作用:返回將起始時間加上expr type之後的時間,比如DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)錶示的就是把第一個時間加1秒

15. DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

  • 作用:返回將起始時間减去expr type之後的時間

16. ADDTIME(date,expr)、SUBTIME(date,expr)

  • 作用:前者進行date的時間加操作,後者進行date的時間减操作

四. 條件判斷函數

1. IF(expr,v1,v2)

  • 作用:如果expr是TRUE則返回v1,否則返回v2

2. IFNULL(v1,v2)

  • 作用:如果v1不為NULL,則返回v1,否則返回v2

3. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

  • 作用:如果expr等於某個vn,則返回對應比特置THEN後面的結果,如果與所有值都不想等,則返回ELSE後面的rn

五. 系統信息函數

1. VERSION()

  • 作用:查看MySQL版本號
SELECT VERSION()

在這裏插入圖片描述

2. CONNECTION_ID()

  • 作用:查看當前用戶的連接數
SELECT CONNECTION_ID()

在這裏插入圖片描述

3. USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

  • 作用:查看當前被MySQL服務器驗證的用戶名和主機的組合,一般這幾個函數的返回值是相同的
SELECT USER()

在這裏插入圖片描述

SELECT CURRENT_USER()

在這裏插入圖片描述

SELECT SYSTEM_USER()

在這裏插入圖片描述

SELECT SESSION_USER()

在這裏插入圖片描述

4. CHARSET(str)

  • 作用:查看字符串str使用的字符集
SELECT CHARSET(555)

在這裏插入圖片描述

5. COLLATION()

  • 作用:查看字符串排列方式

SELECT COLLATION('sssfddsfds')

在這裏插入圖片描述

六. 加密函數

1. PASSWORD(str)

  • 作用:從原明文密碼str計算並返回加密後的字符串密碼,注意這個函數的加密是單向的(不可逆),因此不應將它應用在個人的應用程序中而應該只在MySQL服務器的鑒定系統中使用
SELECT PASSWORD('mima')

在這裏插入圖片描述

2. MD5(str)

  • 作用:為字符串算出一個MD5 128比特校驗和,改值以32比特十六進制數字的二進制字符串形式返回
SELECT MD5('mima')

在這裏插入圖片描述

3. ENCODE(str, pswd_str)

  • 作用:使用pswd_str作為密碼,加密str
SELECT ENCODE('fdfdz','mima')

在這裏插入圖片描述

4. DECODE(crypt_str,pswd_str)

  • 作用:使用pswd_str作為密碼,解密加密字符串crypt_str,crypt_str是由ENCODE函數返回的字符串
SELECT DECODE('fdfdz','mima')

在這裏插入圖片描述

七. 其他函數

1. FORMAT(x,n)

  • 作用:將數字x格式化,並以四舍五入的方式保留小數點後n比特,結果以字符串形式返回
SELECT FORMAT(446.454,2)

在這裏插入圖片描述

2. CONV(N,from_base,to_base)

  • 作用:不同進制數之間的轉換,返回值為數值N的字符串錶示,由from_base進制轉換為to_base進制

3. INET_ATON(expr)

  • 作用:給出一個作為字符串的網絡地址的點地址錶示,返回一個代錶該地址數值的整數,地址可以使4或8比特

4. INET_NTOA(expr)

  • 作用:給定一個數字網絡地址(4或8比特),返回作為字符串的該地址的點地址錶示

5. BENCHMARK(count,expr)

  • 作用:重複執行count次錶達式expr,它可以用於計算MySQL處理錶達式的速度,結果值通常是0(0只是錶示很快,並不是沒有速度)。
  • 另一個作用是用它在MySQL客戶端內部報告語句執行的時間

6. CONVERT(str USING charset)

  • 作用:使用字符集charset錶示字符串str

更多用法還請參考:http://www.geezn.com/documents/gez/help/117555-1355219868404378.html

在這裏插入圖片描述

【SQL實戰練習】

  • 題目來自互聯網,建議每道題都在本地敲一遍鞏固記憶 !

創建數據庫

在這裏插入圖片描述

創建錶(並初始化數據)

-- 學生錶
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 課程錶
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教師錶
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成績錶
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入學生錶測試數據
insert into student values('01' , '趙信' , '1990-01-01' , '男');
insert into student values('02' , '德萊厄斯' , '1990-12-21' , '男');
insert into student values('03' , '艾希' , '1990-05-20' , '男');
insert into student values('04' , '德萊文' , '1990-08-06' , '男');
insert into student values('05' , '俄洛依' , '1991-12-01' , '女');
insert into student values('06' , '光輝女郎' , '1992-03-01' , '女');
insert into student values('07' , '崔絲塔娜' , '1989-07-01' , '女');
insert into student values('08' , '安妮' , '1990-01-20' , '女');
-- 課程錶測試數據
insert into course values('01' , '語文' , '02');
insert into course values('02' , '數學' , '01');
insert into course values('03' , '英語' , '03');
-- 教師錶測試數據
insert into teacher values('01' , '死亡歌頌者');
insert into teacher values('02' , '流浪法師');
insert into teacher values('03' , '邪惡小法師');
-- 成績錶測試數據
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);

錶結構

  • 這裏建的錶主要用於sql語句的練習,所以並沒有遵守一些規範。下面讓我們來看看相關的錶結構吧

學生錶(student)

在這裏插入圖片描述

  • s_id = 學生編號,s_name = 學生姓名,s_birth = 出生年月,s_sex = 學生性別

課程錶(course)

在這裏插入圖片描述

  • c_id = 課程編號,c_name = 課程名稱,t_id = 教師編號

教師錶(teacher)

在這裏插入圖片描述

  • t_id = 教師編號,t_name = 教師姓名

成績錶(score)

在這裏插入圖片描述

  • s_id = 學生編號,c_id = 課程編號,s_score = 分數

習題

  • 開始之前我們先來看看四張錶中的數據。

在這裏插入圖片描述

在這裏插入圖片描述
在這裏插入圖片描述在這裏插入圖片描述

1. 查詢"01"課程比"02"課程成績高的學生的信息及課程分數

SELECT
st.*,
sc.s_score AS '語文',
sc2.s_score '數學'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'

在這裏插入圖片描述

2. 查詢"01"課程比"02"課程成績低的學生的信息及課程分數

SELECT
st.*,
s.s_score AS 數學,
s2.s_score AS 語文
FROM
student st
LEFT JOIN score s ON s.s_id = st.s_id
AND s.c_id = '01'
LEFT JOIN score s2 ON s2.s_id = st.s_id
AND s2.c_id = '02'
WHERE
s.s_score < s2.s_score

在這裏插入圖片描述

3. 查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

SELECT
st.s_id AS '學生編號',
st.s_name AS '學生姓名',
AVG( s.s_score ) AS avgScore
FROM
student st
LEFT JOIN score s ON st.s_id = s.s_id
GROUP BY
st.s_id
HAVING
avgScore >= 60

在這裏插入圖片描述

4. 查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績

  • (包括有成績的和無成績的)
SELECT
st.s_id AS '學生編號',
st.s_name AS '學生姓名',(
CASE
WHEN ROUND( AVG( sc.s_score ), 2 ) IS NULL THEN
0 ELSE ROUND( AVG( sc.s_score ), 2 )
END
)
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_score )< 60
OR AVG( sc.s_score ) IS NULL

在這裏插入圖片描述

5. 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

SELECT
st.s_id AS '學生編號',
st.s_name AS '學生姓名',
COUNT( sc.c_id ) AS '選課總數',
sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '總成績'
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id

在這裏插入圖片描述

6. 查詢"流"姓老師的數量

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '流%'

在這裏插入圖片描述

7. 查詢學過"流浪法師"老師授課的同學的信息

SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course cs ON cs.c_id = sc.c_id
LEFT JOIN teacher tc ON tc.t_id = cs.t_id
WHERE tc.t_name = '流浪法師'

在這裏插入圖片描述

8. 查詢沒學過"張三"老師授課的同學的信息

-- 查詢流浪法師教的課
SELECT
cs.*
FROM
course cs
LEFT JOIN teacher tc ON tc.t_id = cs.t_id
WHERE
tc.t_name = '流浪法師'
-- 查詢有流浪法師課程成績的學生id
SELECT
sc.s_id
FROM
score sc
WHERE
sc.c_id IN (
SELECT
cs.c_id
FROM
course cs
LEFT JOIN teacher tc ON tc.t_id = cs.t_id
WHERE
tc.t_name = '流浪法師')
-- 取反,查詢沒有學過流浪法師課程的同學信息
SELECT
st.*
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
sc.s_id
FROM
score sc
WHERE
sc.c_id IN ( SELECT cs.c_id FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法師' )
)

在這裏插入圖片描述

9. 查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息

  • 方法 1
-- 查詢學過編號為01課程的同學id
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = '01';
-- 查詢學過編號為02課程的同學id
SELECT
st2.s_id
FROM
student st2
INNER JOIN score sc2 ON sc2.s_id = st2.s_id
INNER JOIN course cs2 ON cs2.c_id = sc2.c_id
AND cs2.c_id = '02';
-- 查詢學過編號為"01"並且也學過編號為"02"的課程的同學的信息
SELECT
st.*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND sc.c_id = '01'
WHERE
st.s_id IN (
SELECT
st2.s_id
FROM
student st2
INNER JOIN score sc2 ON sc2.s_id = st2.s_id
INNER JOIN course cs2 ON cs2.c_id = sc2.c_id
AND cs2.c_id = '02'
);

在這裏插入圖片描述

  • 方法 2
SELECT
a.*
FROM
student a,
score b,
score c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02';

在這裏插入圖片描述

10. 查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = '01'
WHERE
st.s_id NOT IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = '02'
);

在這裏插入圖片描述

11. 查詢沒有學全所有課程的同學的信息

  • 方法 1
SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
WHERE
st.s_id IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '02'
WHERE
st.s_id
)
AND st.s_id IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '03'
WHERE
st.s_id
)
);

在這裏插入圖片描述

  • 方法 2
SELECT
a.*
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
COUNT( b.c_id ) != '3';

在這裏插入圖片描述

12. 查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

SELECT DISTINCT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.c_id IN ( SELECT sc2.c_id FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id WHERE st2.s_id = '01' );

在這裏插入圖片描述

13. 查詢和"01"號的同學學習的課程完全相同的其他同學的信息

SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
GROUP_CONCAT( sc.c_id )=(
SELECT
GROUP_CONCAT( sc2.c_id )
FROM
student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
WHERE
st2.s_id = '01'
);

在這裏插入圖片描述

14. 查詢沒學過"邪惡小法師"老師講授的任一門課程的學生姓名

SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT
sc.s_id
FROM
score sc
INNER JOIN course cs ON cs.c_id = sc.c_id
INNER JOIN teacher t ON t.t_id = cs.t_id
AND t.t_name = '邪惡小法師');

在這裏插入圖片描述

15. 查詢兩門及其以上不及格課程的同學的學號,姓名及其平均成績

SELECT
st.s_id AS '學號',
st.s_name AS '姓名',
AVG( sc.s_score ) AS '平均成績'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.s_id IN (
SELECT
sc.s_id
FROM
score sc
WHERE
sc.s_score < 60
OR sc.s_score IS NULL
GROUP BY
sc.s_id
HAVING
COUNT( 1 )>= 2
)
GROUP BY
st.s_id

在這裏插入圖片描述

16. 檢索"01"課程分數小於60,按分數降序排列的學生信息

SELECT
st.*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
AND sc.s_score < '60'
ORDER BY
sc.s_score DESC;
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.c_id = '01'
AND sc.s_score < '60'
ORDER BY
sc.s_score DESC;

在這裏插入圖片描述

17. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

  • 方法 1
SELECT
st.*,
AVG( sc4.s_score ) AS '平均分',
sc.s_score AS '語文',
sc2.s_score AS '數學',
sc3.s_score AS '英語'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
LEFT JOIN score sc3 ON sc3.s_id = st.s_id
AND sc3.c_id = '03'
LEFT JOIN score sc4 ON sc4.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc4.s_score ) DESC;

在這裏插入圖片描述

  • 方法 2
SELECT
st.*,
( CASE WHEN AVG( sc4.s_score ) IS NULL THEN 0 ELSE AVG( sc4.s_score ) END ) AS '平均分',
( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '語文',
( CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END ) AS '數學',
( CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END ) AS '英語'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
LEFT JOIN score sc3 ON sc3.s_id = st.s_id
AND sc3.c_id = '03'
LEFT JOIN score sc4 ON sc4.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc4.s_score ) DESC;

在這裏插入圖片描述

18. 查詢各科成績最高分、最低分和平均分:

  • 以如下形式顯示:課程ID,課程name,最高分,最低分,平均分,及格率,中等率,優良率,優秀率
  • 及格為>=60,中等為:70-80,優良為:80-90,優秀為:>=90
SELECT
cs.c_id,
cs.c_name,
MAX( sc1.s_score ) AS '最高分',
MIN( sc2.s_score ) AS '最低分',
AVG( sc3.s_score ) AS '平均分',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 60
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '及格率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 70
AND s_score < 80
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '中等率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 80
AND s_score < 90
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '優良率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 90
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '優秀率'
FROM
course cs
LEFT JOIN score sc1 ON sc1.c_id = cs.c_id
LEFT JOIN score sc2 ON sc2.c_id = cs.c_id
LEFT JOIN score sc3 ON sc3.c_id = cs.c_id
GROUP BY
cs.c_id;

在這裏插入圖片描述

19. 按各科成績進行排序,並顯示排名(實現不完全)

  • mysql沒有rank函數
  • 加@score是為了防止用union all 後打亂了順序
SELECT
c1.s_id,
c1.c_id,
c1.c_name,
@score := c1.s_score,
@i := @i + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = "01"
ORDER BY
sc.s_score DESC
) c1,
( SELECT @i := 0 ) a UNION ALL
SELECT
c2.s_id,
c2.c_id,
c2.c_name,
c2.s_score,
@ii := @ii + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = "02"
ORDER BY
sc.s_score DESC
) c2,
( SELECT @ii := 0 ) aa UNION ALL
SELECT
c3.s_id,
c3.c_id,
c3.c_name,
c3.s_score,
@iii := @iii + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = "03"
ORDER BY
sc.s_score DESC
) c3;
SET @iii = 0;

在這裏插入圖片描述

20. 查詢學生的總成績並進行排名

SELECT
st.s_id,
st.s_name,
( CASE WHEN sum( sc.s_score ) IS NULL THEN 0 ELSE SUM( sc.s_score ) END )
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
ORDER BY
SUM( sc.s_score ) DESC

在這裏插入圖片描述

21. 查詢不同老師所教不同課程平均分從高到低顯示

SELECT
t.t_id,
t.t_name,
AVG( sc.s_score )
FROM
teacher t
LEFT JOIN course c ON c.t_id = t.t_id
LEFT JOIN score sc ON sc.c_id = c.c_id
GROUP BY
t.t_id
ORDER BY
AVG( sc.s_score ) DESC

在這裏插入圖片描述

22. 查詢所有課程的成績第2名到第3名的學生信息及該課程成績

SELECT
a.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON sc.c_id = c.c_id
AND c.c_id = '01'
ORDER BY
sc.s_score DESC
LIMIT 1,
2
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '02'
ORDER BY
sc.s_score DESC
LIMIT 1,
2
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '03'
ORDER BY
sc.s_score DESC
LIMIT 1,
2
) c;

在這裏插入圖片描述

23. 統計各科成績各分數段人數:課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
c.c_id,
c.c_name,
(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 100 AND sc.s_score > 80
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
) AS '100-85',
((
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 85 AND sc.s_score > 70
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
)) AS '85-70',
((
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 70 AND sc.s_score > 60
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
)) AS '70-60',
((
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 60 AND sc.s_score >= 0
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
)) AS '85-70'
FROM
course c
ORDER BY
c.c_id

在這裏插入圖片描述

24. 查詢學生平均成績及其名次

SET @i = 0;
SELECT
a.*,
@i := @i + 1
FROM
(
SELECT
st.s_id,
st.s_name,
round( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END, 2 ) AS agvScore
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
agvScore DESC
) a

在這裏插入圖片描述

25. 查詢各科成績前三名的記錄

SELECT
a.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '01'
ORDER BY
sc.s_score DESC
LIMIT 0,
3
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '02'
ORDER BY
sc.s_score DESC
LIMIT 0,
3
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '03'
ORDER BY
sc.s_score DESC
LIMIT 0,
3
) c

在這裏插入圖片描述

26. 查詢每門課程被選修的學生數

SELECT
c.c_id,
c.c_name,
COUNT( 1 )
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = c.c_id
GROUP BY
c.c_id

在這裏插入圖片描述

27. 查詢出只有兩門課程的全部學生的學號和姓名

SELECT
st.s_id,
st.s_name
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
GROUP BY
st.s_id
HAVING
COUNT( 1 ) = 2

在這裏插入圖片描述

28. 查詢男生、女生人數

SELECT s_sex, COUNT(1) FROM student GROUP BY s_sex

在這裏插入圖片描述

29. 查詢名字中含有"德"字的學生信息

SELECT * FROM student WHERE s_name LIKE '%德%'

在這裏插入圖片描述

30. 查詢同名同性學生名單,並統計同名人數

select st.s_name,st.s_sex,count(1) from student st group by st.s_name,st.s_sex having count(1)>1

在這裏插入圖片描述

31. 查詢1990年出生的學生名單

SELECT st.* FROM student st WHERE st.s_birth LIKE '1990%';

在這裏插入圖片描述

32. 查詢每門課程的平均成績,結果按平均成績降序排列,平均成績相同時,按課程編號昇序排列

SELECT
c.c_id,
c_name,
AVG( sc.s_score ) AS scoreAvg
FROM
course c
INNER JOIN score sc ON sc.c_id = c.c_id
GROUP BY
c.c_id
ORDER BY
scoreAvg DESC,
c.c_id ASC;

在這裏插入圖片描述

33. 查詢平均成績大於等於85的所有學生的學號、姓名和平均成績

SELECT
st.s_id,
st.s_name,
( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END ) scoreAvg
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
scoreAvg > '85';

在這裏插入圖片描述

34. 查詢課程名稱為"數學",且分數低於60的學生姓名和分數

SELECT
*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.s_score < 60
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_name = '數學';

在這裏插入圖片描述

35. 查詢所有學生的課程及分數情况

SELECT
*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
ORDER BY
st.s_id,
c.c_name;

在這裏插入圖片描述

36. 查詢任何一門課程成績在70分以上的姓名、課程名稱和分數

SELECT
st.s_id,st.s_name,c.c_name,sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
WHERE
st.s_id IN (
SELECT
st2.s_id
FROM
student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
GROUP BY
st2.s_id
HAVING
MIN( sc2.s_score )>= 70
ORDER BY
st2.s_id
)

在這裏插入圖片描述

37. 查詢不及格的課程

SELECT
st.s_id,
c.c_name,
st.s_name,
sc.s_score
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.s_score < 60
INNER JOIN course c ON c.c_id = sc.c_id

在這裏插入圖片描述

38. 查詢課程編號為01且課程成績在80分以上的學生的學號和姓名

SELECT
st.s_id,
st.s_name,
sc.s_score
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
AND sc.s_score >= 80;

在這裏插入圖片描述

39. 求每門課程的學生人數

SELECT
c.c_id,
c.c_name,
COUNT( 1 )
FROM
course c
INNER JOIN score sc ON sc.c_id = c.c_id
GROUP BY
c.c_id;

在這裏插入圖片描述

40. 查詢選修"死亡歌頌者"老師所授課程的學生中,成績最高的學生信息及其成績

SELECT
st.*,
sc.s_score
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
INNER JOIN teacher t ON t.t_id = c.t_id
AND t.t_name = '死亡歌頌者'
ORDER BY
sc.s_score DESC
LIMIT 0,1;

在這裏插入圖片描述

41. 查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

SELECT
st.s_id,
st.s_name,
sc.c_id,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
WHERE
(
SELECT
COUNT( 1 )
FROM
student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
LEFT JOIN course c2 ON c2.c_id = sc2.c_id
WHERE
sc.s_score = sc2.s_score
AND c.c_id != c2.c_id
)>1;

在這裏插入圖片描述

42. 查詢每門功成績最好的前兩名

SELECT
a.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '01'
ORDER BY
sc.s_score DESC
LIMIT 0,
2
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '02'
ORDER BY
sc.s_score DESC
LIMIT 0,
2
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '03'
ORDER BY
sc.s_score DESC
LIMIT 0,
2
) c;

在這裏插入圖片描述

寫法 2

SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
WHERE
( SELECT COUNT( 1 ) FROM score b WHERE b.c_id = a.c_id AND b.s_score > a.s_score ) <= 2
ORDER BY
a.c_id;

在這裏插入圖片描述

43. 統計每門課程的學生選修人數(超過5人的課程才統計)

  • 要求輸出課程號和選修人數,查詢結果按人數降序排列,若人數相同,按課程號昇序排列
SELECT
c.c_id,
COUNT( 1 )
FROM
score sc
LEFT JOIN course c ON c.c_id = sc.c_id
GROUP BY
c.c_id
HAVING
COUNT( 1 ) > 5
ORDER BY
COUNT( 1 ) DESC,
c.c_id ASC;

在這裏插入圖片描述

44. 檢索至少選修兩門課程的學生學號

SELECT
st.s_id
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
COUNT( 1 )>= 2;

在這裏插入圖片描述

45. 查詢選修了全部課程的學生信息

SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
COUNT( 1 )=(
SELECT
COUNT( 1 )
FROM
course)

在這裏插入圖片描述

46. 查詢各學生的年齡

SELECT
st.*,
TIMESTAMPDIFF(
YEAR,
st.s_birth,
NOW())
FROM
student st

在這裏插入圖片描述

47. 查詢本周過生日的學生

SELECT
st.*
FROM
student st
WHERE
WEEK (
NOW())+ 1 = WEEK (
DATE_FORMAT( st.s_birth, '%Y%m%d' ))

在這裏插入圖片描述

48. 查詢下周過生日的學生

SELECT
st.*
FROM
student st
WHERE
WEEK (
NOW())+ 1 = WEEK (
DATE_FORMAT( st.s_birth, '%Y%m%d' ));

在這裏插入圖片描述

49. 查詢本月過生日的學生

SELECT
st.*
FROM
student st
WHERE
MONTH (
NOW())= MONTH (
DATE_FORMAT( st.s_birth, '%Y%m%d' ));

在這裏插入圖片描述

50. 查詢下月過生日的學生

SELECT
st.*
FROM
student st
WHERE
MONTH (
TIMESTAMPADD(
MONTH,
1,
NOW()))= MONTH (
DATE_FORMAT( st.s_birth, '%Y%m%d' ));

在這裏插入圖片描述


【阿裏巴巴開發手册】

在這裏插入圖片描述

點擊預覽在線版: 阿裏巴巴開發手册


內容偏向基礎適合各個階段人員的學習與鞏固,如果對您還有些幫助希望給博主點個贊在這裏插入圖片描述支持一下,感謝!

版权声明:本文为[Cs 挽周]所创,转载请带上原文链接,感谢。 https://gsmany.com/2021/08/20210815175600413B.html