<form id="3hdj5"></form>

<form id="3hdj5"></form>

        <noframes id="3hdj5"><form id="3hdj5"><nobr id="3hdj5"></nobr></form>

          <form id="3hdj5"></form>

          <noframes id="3hdj5">

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

          14 篇文章 0 訂閱
          14 篇文章 0 訂閱
          • MySQL 是最流行的關系型數據庫管理系統,在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System:關系數據庫管理系統)應用軟件之一

          在這里插入圖片描述

          MySQL實戰文章目錄


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

          在這里插入圖片描述

          評論區評論要資料三個字即可獲得MySQL全套資料 !


          【介紹】

          在這里插入圖片描述

          什么是數據庫

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

          MySQL數據庫

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

          • MySQL 是開源的,目前隸屬于 Oracle 旗下產品。
          • MySQL 支持大型的數據庫??梢蕴幚頁碛猩锨f條記錄的大型數據庫。
          • 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密碼;

            root@243ecf24bd0a:/ 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的數據表類型:MyISAM、InnoDB、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值。列的順序可以調換。列數和值的個數必須一致??墒÷粤忻?#xff0c;默認所有列,并且列的順序和表中列的順序一致。

          案例:

          -- 插入學生表測試數據
          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 * from  表1 inner join 表2;
          

          3. 左外連接查詢

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

          在這里插入圖片描述

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

          4. 右外連接查詢

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

          在這里插入圖片描述


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

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

          關鍵字

          • GRANT
          • REVOKE

          查看用戶權限

          當成功創建用戶賬戶后,還不能執行任何操作,需要為該用戶分配適當的訪問權限??梢允褂?code>SHOW GRANTS FOR語句來查詢用戶的權限。

          例如:

          mysql> SHOW GRANTS FOR test;
          +-------------------------------------------+
          | Grants for test@%                         |
          +-------------------------------------------+
          | 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' ));
          

          在這里插入圖片描述


          【阿里巴巴開發手冊】

          在這里插入圖片描述

          點擊預覽在線版: 阿里巴巴開發手冊


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

          評論 29 您還未登錄,請先 登錄 后發表或查看評論

          “相關推薦”對你有幫助么?

          • 非常沒幫助
          • 沒幫助
          • 一般
          • 有幫助
          • 非常有幫助
          提交
          ??2022 CSDN 皮膚主題:深藍海洋 設計師:CSDN官方博客 返回首頁

          打賞作者

          m0_67393295

          你的鼓勵將是我創作的最大動力

          ¥2 ¥4 ¥6 ¥10 ¥20
          輸入1-500的整數
          余額支付 (余額:-- )
          掃碼支付
          掃碼支付:¥2
          獲取中
          掃碼支付

          您的余額不足,請更換掃碼支付或充值

          打賞作者

          實付
          使用余額支付
          點擊重新獲取
          掃碼支付
          錢包余額 0

          抵扣說明:

          1.余額是錢包充值的虛擬貨幣,按照1:1的比例進行支付金額的抵扣。
          2.余額無法直接購買下載,可以購買VIP、C幣套餐、付費專欄及課程。

          余額充值
          狠狠激情五月丁香免费视频