Mysql的學習筆記

一、資料庫概述

1、概述

資料庫：Database ，簡稱DB，是指按照一定格式存盤資料的一些檔案的組合（資料倉庫）
資料庫管理系統：DataBaseManagement ，簡稱DBMS，用來管理資料庫中的資料的，可以對其中的資料進行增刪改查，
- 常見的資料庫管理系統：MySQL 、 Oracle、MS SqlSever 、 DB2 、sybase等
SQL：是一種結構化查詢語言通過撰寫SQL陳述句，由DBMS負責執行SQL陳述句，最終來完成資料庫中資料的增刪改查（相當于編程語言）
關系：DBMS -----> 通過執行SQL陳述句操作資料庫中的資料
CRUD ：增刪改查，對應 create 、retrave(查，檢索)、update 、 delete ；

2、安裝MySQL

免安裝版：
- 第一步：去官網下載安裝
- （重點）第二步：先解壓，然后在mysql下創建一個my.ini檔案，
  更改my.ini檔案里面的兩行安裝目錄，
  第二行加上\data，my.ini檔案不能多或少一個符號，
  在path（環境變數里面）加上mysql路徑（/bin），
- （重點）第三步：進入命令指示符（cmd），
  輸入mysqld --initialize-insecure --user=mysql，
  再輸入mysqld -install，
  出現Service successfully installed.表示配置完成
  啟動資料庫net start mysql，
  輸入mysql -u root -p，不用輸入密碼直接回車
  出現mysql>表示配置完成
  輸入alter user user() identified by “密碼”;
  輸入net stop mysql關閉資料庫
安裝版（msi）：
- 注意事項
  - 1、埠號：是任何一個軟體都會有的，埠號是一個軟體的唯一代表，通常和ip地址在一起，ip地址用來定位計算機，port埠號是用來定位計算機上的服務/應用的，在同一臺計算機上，埠號不能重復，具有唯一性， MySQL占用的默認埠號3306
  - 2、字符集（字符編碼方式）：設定MySQL的字符編碼方式為UTF - 8
  - 3、服務名稱：默認是MySQL
  - 4、環境變數path
  - 5、MySQL超級管理員root名稱不變，設定密碼
  - 6、設計密碼的同時，可以激活root賬戶遠程訪問，激活后，root賬戶可以從外地登錄訪問；不激活則表示root賬戶只能在本地使用

3、 MySQL的卸載

免安裝版：
- 第一步：停止mysql服務
  net stop mysql
- 第二步：卸載mysql
- 第三步：洗掉mysql服務
  sc delete mysql(服務名稱)
  - 如果服務洗掉不了，就從注冊表洗掉
    win+R:regedit打開注冊表
    HEKY_LOCAL_MACHINE-SYSTEM-CurrentControlSet-Service
    找到mysql服務洗掉即可
- 第四步：環境變數洗掉
安裝版（msi）：
- 第一步：雙擊安裝包卸載
- 第二步：洗掉目錄（表層位置，C盤的ProgramDate檔案下的MySQL目錄一起刪掉）
- 第三步：環境變數洗掉

4、計算機中MySQL的服務

流程：計算機右鍵 — 管理 — 服務和應用程式 — 服務 — 找MySQL服務
MySQL的服務默認為啟動，默認為自動啟動，表示啟動作業系統的時候自動啟動

Windows 系統中的啟動方式

net start MySQL							// 啟動MySQL服務
net stop MySQL 							// 關閉MySQL服務

5、如何登錄MySQL服務（客戶端）

使用bin目錄下的mysql.exe命令來連接mysql資料庫服務器

第一步：啟動MySQL服務
第二步：mysql -u 用戶名稱 -p密碼（其中的-u 表示user ， -p表示passward）

本地登錄（顯示密碼的形式）

C:\Users\張中宇>mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

本地登錄（隱藏密碼的形式）

C:\Users\張中宇>mysql -u root -p
Enter password:******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

二、基礎命令

1、登錄與退出

mysql -u root -p								// 本地登錄
exit 										   //  退出 
\q											   //  退出

2、MySQL的常用命令

（命令不區分大小寫）（mysql不見分號不執行）

show databases;							// 查看mysql中有哪些資料庫（默認自帶4個）
use  資料庫名稱 ;						// 使用某一個資料庫
create database 資料庫名稱 ;    			 // 創建一個新的資料庫
show tables ;						   //  查看資料庫下有哪些表 
source + 檔案路徑						//  匯入sql檔案
select * from 表名 ;					  //  查看表中的資料	
desc(describe) 表名	;				  //  不看表的資料，只看表的結構
select version() ;				 	   //   查看mysql資料庫的版本號
select database();					    //  查看當前使用的資料庫
\c									// 中止命令的輸入

三、資料庫基本知識

1、表（table）

概念：類似于Excel
表是資料庫中最基本的單元，資料庫中以表的是形式表示資料；因為表比較直觀
任何表都有行和列
- 行（row , 被稱為資料或者記錄）
- 列（column ，被稱為欄位）
  - 每個欄位都有欄位名、資料型別（字串、數字、日期等）、約束（限制性措施）等屬性

2 、SQL陳述句的分類

分為DQL、DML、DDL、TCL、DCL
- DQL : 資料查詢語言，關鍵字SELECT
- DML：資料操作語言，對表中的資料進行增刪改的關鍵字INSECT；DELETE； UPDATE
- DDL：資料定義語言，主要操作的是表的結構，增刪改關鍵字CREATE、DROP、ALTER
- TCL：事務控制語言：事務提交、事務回滾關鍵字COMMIT 、 ROLLBACK
- DCL：資料控制語言：授權、撤銷權限關鍵字GRANT、REVOKE

3、資料的匯入

source + 檔案目錄
注意：檔案的路徑一定不要有中文！！！！

mysql> source D:\MySQL\sql-documents\pre\bjpowernode.sql
Query OK, 0 rows affected, 1 warning (0.43 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.63 sec)

Query OK, 0 rows affected, 5 warnings (0.38 sec)

Query OK, 0 rows affected (0.14 sec)

Query OK, 1 row affected (0.15 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.12 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)


mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.04 sec)

mysql> select * from emp
    -> ;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

4、表的結構

不看表內容，只看表的結構 desc + 表名；

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |		部門編號
| DNAME  | varchar(14) | YES  |     | NULL    |       |		部門名稱
| LOC    | varchar(13) | YES  |     | NULL    |       |		地理位置
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.15 sec)

mysql> desc emp ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |	員工編號
| ENAME    | varchar(10) | YES  |     | NULL    |       |	員工姓名
| JOB      | varchar(9)  | YES  |     | NULL    |       |	作業崗位
| MGR      | int(4)      | YES  |     | NULL    |       |	上級編號
| HIREDATE | date        | YES  |     | NULL    |       |	入職日期
| SAL      | double(7,2) | YES  |     | NULL    |       |	工資
| COMM     | double(7,2) | YES  |     | NULL    |       |	補助
| DEPTNO   | int(2)      | YES  |     | NULL    |       |	部門編號
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> desc salgrade ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |		工資等級
| LOSAL | int(11) | YES  |     | NULL    |       |		最低工資
| HISAL | int(11) | YES  |     | NULL    |       |		最高工資
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

5、MySQL常用命令

詳情參見第二部分

四、常用命令

1、DQL陳述句

1.1 SELECT陳述句

注意：select陳述句不會更改原表之中的任何內容，只負責檢索、查詢
select 后面可以跟欄位名（自變數），也可以跟字面量/ 字面值（資料）
- 如果select后面跟的是欄位名，那么查詢欄位
- 如果跟的是資料，那么會借助這個表的結構，形成這個資料表

1.1.1 簡單查詢

select 欄位名 from 表名 ;			        	// 查詢一個欄位
select 欄位名1,欄位名2,... from 表名;			  // 查詢多個欄位
select * from 表名 ; 					          //  查詢所有欄位(效率低、可讀性差)
select 欄位名 as 新欄位名 from 表名;				 // 給查詢的列起別名
/*注意：只是將列名的顯示改為新的名稱，原表不變，select陳述句不會更改原表之中的任何內容
	    as可以省略，但只能顯示一個字符，
	    假設起別名是有空格，加上單引號 
	    字串用單引號標起來（標準），雙引號在oracle資料庫中無法使用
	    在列之中的欄位可以使用數學運算式
	    別名是中文，屬于字串，用單引號括起來即可*/

1.1.2 條件查詢

查詢出來符合條件的
格式： select 欄位名1 , 欄位名2 , 欄位名3 … from 表名 where 條件 ;
條件查詢的條件：
- = \ <>或 != \ < \ <= \ > \ >= \ between…and … \ is null \ and \ or \ in \ not \ like \ % \ _

1、 = 等于
 【案例】：查詢薪資等于800的員工的姓名和編號
 select empno,ename from emp where sal=800 ;

2、<> 或 != 不等于
 【案例】：查詢薪資不等于800的員工的姓名和編號
  select empno,ename from emp where sal<>800 ;
  select empno,ename from emp where sal!=800 ;
  
3、<  小于
 【案例】：查詢薪資小于2000的員工的姓名與編號
  select empno,ename from emp where sal<2000 ; 
 
4、<= 小于等于
 【案例】：查詢薪資小于3000的員工的姓名與編號
  select empno,ename from emp where sal<=3000 ; 
 
5、> 大于
 【案例】：查詢薪資小于2000的員工的姓名與編號
  select empno,ename from emp where sal>4000 ; 
 
6、>= 大于等于
 【案例】：查詢薪資小于2000的員工的姓名與編號
  select empno,ename from emp where sal>=4000 ; 
 
7、between... and... 兩個值之間，等同于 >= and <=
注意：使用此陳述句時必須保證左小右大 ， 閉區間，包含兩端的值
 【案例】：查詢薪資小于2000的員工的姓名與編號
  select empno,ename from emp where sal between 2000 and 4000 ; 
  select empno,ename from emp where sal >= 2000 and sal <= 4000 ; 
 
8、is (not) null 
 【案例】：查詢津貼（補助）（不）為null的員工姓名和員工編號
  select empno,ename from emp where comm is null;
  select empno,ename from emp where comm is not null ;
  
9、and 并且
 【案例】：查詢作業崗位是manager并且工資大于2500的員工資訊
 select * from emp where job = 'manager' and sal > 2500 ;

10、or 或者
 【案例】：查詢作業崗位是manager或者是salesman的員工資訊
 select * from emp where job = 'manager' or job = 'salesman';
 
 【注意】：and 和or 同時出現，and 優先級高 ， 先執行and，
 【陳述句】： select * from emp where sal > 2500 and deptno = 10 or deptno = 20 ;
      表達的意義是工資大于2500并且dept = 10 的人，或者是deptno = 20 的所有人
       select * from emp where sal > 2500 and (deptno = 10 or deptno = 20) ;
       表達的意義是工資大于2500的人，同時是dept = 10 或者deptno = 20 的所有人
 
11、(not) in 包含，相當于多個or
 【案例】：查詢作業崗位是manager或者是salesman的員工資訊
  select * from emp where job in ('manager', 'salesman');
  【注意】：in后面是具體的值，不是區間

12、not   not 可以取非 ， 主要用在is和in之中
 
13、like   like稱為模糊查詢，支持% 或者_匹配 
			%    匹配任意個字符
			_    一個下劃線只匹配一個字符
【案例】： 找出姓名中含有'o'的員工資訊
  select * from emp where ename like '%o%' ;
【案例】：找出姓名中以T結尾的
  select * from emp where ename like '%t';
【案例】：找出姓名以k開始的
  select * from emp where ename like 'k%';
【案例】：找出姓名第二個字母是A的
  select * from emp where ename like '_a%';
【案例】：找出第三個字母是r的
  select * from emp where ename like '__r%';
【案例】：找出名字中含有下劃線的
  select * from emp where ename like '%\_%';     // 反斜杠代表轉義， 類似于轉義字符

1.1.3 排序

格式： select 欄位名1 , 欄位名2 , 欄位名3 … from 表名 order by 欄位名 ;（默認升序排列）
格式： select 欄位名 from 表名 where 條件 order by 排序方式 ;

【案例】：查詢所有員工薪資
  select ename , sal from emp order by sal ;			// 默認升序
  select ename , sal from emp order by sal asc ;		// 指定升序排列 
  select ename , sal from emp order by sal desc ;		// 指定降序排列 
【案例】：按照員工名字和薪資排列，按照薪資升序排列，如果相同按照姓名升序排列
  select ename , sal from emp order by sal asc , ename asc ;	// 多欄位排序
【案例】：按照查詢結果的第二列排列 
  select ename , sal from emp order by 2 ;				// 不建議，因為不健壯
  
【案例】：找出工資在1250到3000之間的員工資訊，按照薪資的降序排列
格式： select 欄位名 from 表名 where 條件 order by 排序方式 ;
  select ename , sal from emp where sal between 1500 and 3000 order by sal desc ;

1.1.4 資料處理函式

資料處理函式又被稱為單行處理函式

特點：一個輸入對應一個輸出

函式名	函式功能
lower	轉換小寫
upper	轉換大寫
substr	取子串（substr（被截取的字符子串，起始下標，截取的長度））
length	取長度
trim	去空格
str_to_date	將字串轉換成日期
date_format	格式化日期
format	設定千分位
round	四舍五入
rand()	生成亂數
ifnull	可以將null轉換成一個具體值

// 單行處理函式
1、lower / upper  轉換大小寫
 【案例】：將員工姓名轉換成小寫
 select lower(ename) from emp ;

2、substr  取子串
【格式】：substr(字串 , 起始下標 , 截取長度) 		起始下標從1開始
【案例】：請找出員工名字第一個字母是A的員工資訊
 select ename from emp where ename like 'a%' ;
 select ename from emp where substr(ename , 1  1) = 'A' ;      // 注意是一個等號

3、length
【格式】：length(欄位名)
【案例】：求取員工姓名的長度，并更名為enamelength
 select length(ename) as enamelength from emp ;

4、trim         去空格
【格式】： select 字符段 from 表名 where 字符段 = trim('傳過來的資料');
【案例】： 查詢king的資料
 select * from emp where ename = trim('  king '); 

5、str_to_date   將字串varchar轉換成date 型別
 【格式】：str_to_date('字串日期' ， '日期格式') ;
 		日期格式：年月日時分秒分別為： %Y , %m , %d , %h , %i , %s ;
 【案例】：向t_user表中插入一個資料，將字串型別轉換成日期型別
 insert into t_user (id , name , birth) values (1 , 'zhangsan' , str_to_date('01-10-1990' , '%d-%m-%Y')) ;
 【注意】：如果輸入的格式是 %Y-%m-%d ， 則轉換函式可以省略，

6、date_format   將date型別轉換成具有一定格式的varchar字串型別
 【格式】：date_format(欄位名 ,'日期格式')
 【案例】：查詢t_user表中的名字和生日
 select name , date_format(birth , '%Y-%m') as birth from t_user ;

7、format	數字格式化
【格式】：format (數字 ， ‘格式’);
mysql> select empno , ename , Format(sal , '$999,999') from emp ;
+-------+--------+--------------------------+
| empno | ename  | Format(sal , '$999,999') |
+-------+--------+--------------------------+
|  7369 | SMITH  | 800                      |
|  7499 | ALLEN  | 1,600                    |
|  7521 | WARD   | 1,250                    |
|  7566 | JONES  | 2,975                    |
|  7654 | MARTIN | 1,250                    |
|  7698 | BLAKE  | 2,850                    |
|  7782 | CLARK  | 2,450                    |
|  7788 | SCOTT  | 3,000                    |
|  7839 | KING   | 5,000                    |
|  7844 | TURNER | 1,500                    |
|  7876 | ADAMS  | 1,100                    |
|  7900 | JAMES  | 950                      |
|  7902 | FORD   | 3,000                    |
|  7934 | MILLER | 1,300                    |
+-------+--------+--------------------------+
14 rows in set, 14 warnings (0.06 sec)


8、round        四舍五入（可以有負數，負數表示相應的整數位數 ； 可以有小數，也是四舍五入保留位數 ： 0.4 和 0.6 保留的位數就不一樣）
【格式】： select round(值， 保留的小數位數) from 表名 ;
【案例】： 測驗  1236.567  的四舍五入值
  select round(1236.567 , 0) as result from emp ;

9、rand()	  生成亂數  ， 也會借助表的結構
【格式】：  select rand() as result from emp ;
【案例】：生成 100 以內的 亂數
  select round(rand() * 100) as result from emp ;

10、ifnull		可以將null轉換成一個具體值
【注意】：在資料庫之中，只要有null參與的數學運算 ， 結果均為null
【格式】：ifnull(資料 , 被當做的值) 
【案例】：將null 改為0 , 計算員工的年薪 ， 并重命名為yearsal
 select ename , (sal + ifnull(comm , 0)) * 12 as yearsal from emp ;

10、concat    字串拼接
【案例】：將員工的姓名和員工的編號拼接起來
 select concat(ename , empno) from emp ;
 
11、case.. when... then... when... then...else... end ....
【案例】：當員工的崗位是manager的時候，工資上調10% ， 當作業崗位是salesman的時候，工資上調50% （注意：不修改資料庫）
 select ename , job , sal as oldsal ,(case job when 'manager' then  sal*1.1 when 'salesman' then sal * 1.5  else sal end) as newsal from emp order by sal asc , ename  desc ;
 
 
【案例】：將員工資訊以首字母大寫的形式展現出來			
 select concat(upper(substr(ename , 1 , 1)) , lower(substr(ename , 2 , length(ename) - 1)) ) as ename from emp ;

1.1.5 分組函式

概念：分組函式又被稱為聚合函式、多行處理函式
特點：多個輸入對應一個輸出

函式名函式功能
count 取得記錄數
sum 求和
avg 取平均值
max 取最大的數
min 取最小的數
注意：
- 1、分組函式在使用的時候必須先進行分組，然后才能用；如果進行分組，整張表為一個組
- 2、分組函式會自動忽略null ，不需要提前進行處理
- 3、分組函式count（*）和count（欄位）的區別
  - count(具體欄位) : 表示統計該欄位下所有不為null的元素的總數
  - count(*) : 只要有一行資料，count則++
- 4、分組函式不能夠直接使用在where子句中
  - 執行順序：from、 where 、 group by、select 、 order by
  - 因為執行where 陳述句的時候尚未分組，所以會報錯
- 5、所用的分組函式可以組合起來一起用

函式名	函式功能
count	取得記錄數
sum	求和
avg	取平均值
max	取最大的數
min	取最小的數

// 多行處理函式
1、count	
【案例】：求出員工數量總和
select count(ename) from emp ;

2、sum
3、avg
4、max
5、min

【案例】：同時計算所有員工的工資總和、最高工資、最低工資、平均工資、總數
  select sum(sal) , max(sal) , min(sal) , avg(sal) , count(sal) from emp ;
  +----------+----------+----------+-------------+------------+
 | sum(sal) | max(sal) | min(sal) | avg(sal)    | count(sal) |
 +----------+----------+----------+-------------+------------+
 | 29025.00 |  5000.00 |   800.00 | 2073.214286 |         14 |
 +----------+----------+----------+-------------+------------+

1.1.6 分組查詢（重點）

概念：在實際的應用之中，可能有這樣的需求，需要先進行分組，之后對每一個分組進行操作，這個時候就要用到分組查詢
使用having子句，可以對分完組之后的資料進一步過濾，不能單獨使用，要和group by一起使用，并且位置不能改變，不能代替where
格式： select 欄位名 from 表名 group by …
格式： select 欄位名 from 表名 where 條件 group by … order by 排序；

【案例】：計算每個作業崗位的工資和
 select job , sum(sal) from emp group by job ;
 
【案例】：計算每個作業的平均薪資
 select job , avg(sal) from emp group by job ;
 
【案例】：計算每個部門的最高薪資
 select deptno , max(sal) from emp group by deptno ;
  
 [注意]：在一條select陳述句后面有 group by ，則在 select 后面只能跟分組的欄位，以及分組函式
 
 
 【案例】：找出每個部門，不同作業崗位的最高工資
 select deptno , job , max(sal) from emp group by deptno , job order by deptno asc;
	 +--------+-----------+----------+
	| deptno | job       | max(sal) |
	+--------+-----------+----------+
	|     10 | CLERK     |  1300.00 |
	|     10 | MANAGER   |  2450.00 |
	|     10 | PRESIDENT |  5000.00 |
	|     20 | ANALYST   |  3000.00 |
	|     20 | CLERK     |  1100.00 |
	|     20 | MANAGER   |  2975.00 |
	|     30 | CLERK     |   950.00 |
	|     30 | MANAGER   |  2850.00 |
	|     30 | SALESMAN  |  1600.00 |
	+--------+-----------+----------+
	
 【案例】：找出每個部門的最高薪資，要求顯示最高薪資大于3000 
  select deptno , max(sal) from emp where sal > 3000 group by deptno ;(效率比下面高)
  使用having子句 ，可以對分完組之后的資料進一步過濾, 不能單獨使用，要和group by一起使用 ，并且位置不能改變，不能代替where
  select deptno , max(sal) from emp group by deptno having max(sal) > 3000 ;(效率低)
  
  
 【案例】：找出每個部門平均薪資，要求顯示最高薪資大于2500
 【分析】：因為是平均薪資，無法提前過濾，where失效，用having
  select deptno , avg(sal) from emp group by deptno having avg(sal) > 2500 ;

1.1.7 單表查詢總結

格式: select…from…where…group by…having…order by…
執行順序： from — where — group by ---- having ----- select ----- order by ；

【案例】：找出每個崗位的平均薪資，要求現實平均工資大于1500的，除manager崗位之外，要求按照平均薪資的降序排列
 select job , avg(sal) as avgsal from emp where job != 'manager' group by job having avg(sal) > 1500 order by avgsal desc ;

1.1.8 去除重復記錄

格式：select distinct 欄位名 from 表明 ;
注意：distinct 只能出現在所有欄位的最前面，欄位有多個時，表示聯合去重

【案例】：統計作業崗位的數量
 select distinct job from emp ;

1.1.9 連接查詢（重點）

概念：兩張表或多張表中聯合起來查詢資料，從一個表中取一個資訊，從另外的表中查詢其余資訊的查詢方式叫做連接查詢，聯合查詢
分類：
- 按照年代分類：SQL92 、 SQL99（主要學習）
- 按照表的連接方式分為：內連接、外連接、全連接
  - 內連接：等值連接、非等值連接、自連接
  - 外連接：左外鏈接（左連接）、右外鏈接（右鏈接）
特殊現象：當兩張表連接查詢，如無限制，會發生笛卡爾積現象

? 當兩張表進行連接查詢，沒有限制的話，結果是兩張表條數的乘積；

// 演示笛卡爾積現象

【案例】：查詢兩張表的資訊
 select ename ,empno from emp ,dept ;
+--------+-------+
| ename  | empno |
+--------+-------+
| SMITH  |  7369 |
| SMITH  |  7369 |
| SMITH  |  7369 |
| SMITH  |  7369 |
| ALLEN  |  7499 |
| ALLEN  |  7499 |
| ALLEN  |  7499 |
| ALLEN  |  7499 |
| WARD   |  7521 |
| WARD   |  7521 |
| WARD   |  7521 |
| WARD   |  7521 |
| JONES  |  7566 |
| JONES  |  7566 |
| JONES  |  7566 |
| JONES  |  7566 |
| MARTIN |  7654 |
| MARTIN |  7654 |
| MARTIN |  7654 |
| MARTIN |  7654 |
| BLAKE  |  7698 |
| BLAKE  |  7698 |
| BLAKE  |  7698 |
| BLAKE  |  7698 |
| CLARK  |  7782 |
| CLARK  |  7782 |
| CLARK  |  7782 |
| CLARK  |  7782 |
| SCOTT  |  7788 |
| SCOTT  |  7788 |
| SCOTT  |  7788 |
| SCOTT  |  7788 |
| KING   |  7839 |
| KING   |  7839 |
| KING   |  7839 |
| KING   |  7839 |
| TURNER |  7844 |
| TURNER |  7844 |
| TURNER |  7844 |
| TURNER |  7844 |
| ADAMS  |  7876 |
| ADAMS  |  7876 |
| ADAMS  |  7876 |
| ADAMS  |  7876 |
| JAMES  |  7900 |
| JAMES  |  7900 |
| JAMES  |  7900 |
| JAMES  |  7900 |
| FORD   |  7902 |
| FORD   |  7902 |
| FORD   |  7902 |
| FORD   |  7902 |
| MILLER |  7934 |
| MILLER |  7934 |
| MILLER |  7934 |
| MILLER |  7934 |
+--------+-------+
56 rows in set (0.00 sec)


【分析】：當兩張表進行連接查詢，沒有限制的話，結果是兩張表條數的乘積；
作業原理：兩張表會從一張表的所有資訊分別匹配另一張表的所有資訊

【如何避免笛卡爾積現象】：進行表連接時增加條件
 select emp.ename , dept.dname from emp , dept where emp.deptno = dept.deptno ;
 
 【注意】：匹配的次數并沒有減少，只是顯示的記錄減少了
 【注意2】：進行表查詢的時候，注意起別名
 select e.ename , d.dname from emp , dept where e.deptno = d.deptno ;  // 92語法
 【注意3】：表的連接次數越多、效率越低；盡量減少表的連接

內連接 (inner) join
- 分類：等值連接、非等值連接、自連接
- 特點：完全匹配上條件的資料可以查出來

// 等值連接
····// 條件是一種等量關系
【案例】：查詢每個員工所在部門名稱，顯示員工名和部門名
 select emp.ename , dept.dname from emp , dept where emp.deptno = dept.deptno;  //92
 select emp.ename , dept.dname from emp join dept on emp.deptno = dept.deptno;  //99
 
 //非等值連接
 ····// 條件不是一種等量關系
 【案例】：找出每個員工的薪資等級 ， 要求顯示員工名，薪資，薪資等級
 select e.ename , e.sal , s.grade from emp e join salgrade s on e.sal between s.losal  and s.hisal ;
 
 //自連接
 ····// 一張表看作兩張表，起別名加以區分
 【案例】：查詢員工的上級領導，顯示對應地員工名和領導名
  select a.ename , b.ename mgrname from emp a join emp b on a.mgr = b.empno  ;
 【案例2】：查詢員工的上級領導，顯示對應地員工名和領導名，要求名稱首字母大寫，其余小寫
  select concat(upper(substr(a.ename , 1 , 1)) , lower(substr(a.ename , 2 , length(a.ename) - 1)) ) as ename , concat(upper(substr(b.ename , 1 , 1)) , lower(substr(b.ename , 2 , length(b.ename) - 1)) ) as mgrname from emp a join emp b on a.mgr = b.empno;

外連接：left / right（outer）join
- 分類：左外鏈接（左連接）、右外鏈接（右鏈接）
- 左外連接：select … from … left join … on … 其中的left表示join左面的表的資訊全部顯示
- 右外連接：select … from … right join … on … 其中的right表示join右面的表的資訊全部顯示

// 左外連接
····// 表示左表為主表，主表的資訊會全部顯示出
 【案例】：查詢員工的上級領導，顯示對應地員工名和領導名，要求名稱首字母大寫，其余小寫.要求顯示所有的名
 select concat(upper(substr(a.ename , 1 , 1)) , lower(substr(a.ename , 2 , length(a.ename) - 1)) ) as ename , concat(upper(substr(b.ename , 1 , 1)) , lower(substr(b.ename , 2 , length(b.ename) - 1)) ) as mgrname from emp a left join emp b on a.mgr = b.empno;

// 右外連接
···· //  表示右表為主表，主表的資訊會全部顯示出
【案例】：查詢每個員工所在部門名稱，顯示員工名和部門名，要求顯示所有的部門名
 select e.ename , d.deptno from emp e right join d.deptno on e.deptno = d.depto ;

多表連接：
- 格式：select… from a join b on … join c on … join d on … ;

【案例】：找出每個部門的員工名稱以及工資等級，要求顯示員工名、部門名、薪資、薪資等級
 select e.ename , d.dname , e.sal , s.grade from emp e left join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal ; 
 
 【案例】找出每個員工的員工名稱以及工資等級、上級領導，要求顯示員工名、領導名、部門名、薪資、薪資等級
 select e.ename , a.ename mgrname , d.dname , e.sal , s.grade from emp e left join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal left join emp a on e.mgr = a.empno;

1.1.10 子查詢

概念：select 陳述句的嵌套，被嵌套的陳述句稱為子查詢
位置：select 、 from 、 where 后面可以出現

//where字句中的子查詢
【案例】：找出比最低工資高的員工姓名和薪資
 select ename , sal from emp where sal > (select min(sal) from emp);
 
 //from字句中的子查詢
 ····//from后面的子查詢可以將子查詢的結果當做一張查詢表來看待
 【案例】：找出每個崗位的平均工資的薪資等級
 select job , a.avgsal , s.grade from (select job , avg(sal) avgsal from emp group by job)  as a join salgrade s on a.avgsal between s.losal and hisal ;
 
 //select字句的子查詢
 ····// 只能一次回傳一個結果 ， 如果多于一條，就會報錯 
 【案例】：找出每個員工的部門名稱，要求顯示員工名，部門名
 select e.ename , d.dname from emp e join dept d on e.deptno = d.deptno ;
 select e.ename , (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e ;

1.1.11 union 合并查詢結果

注意：union 在進行結果集的合并的時候，列數要相同，列的資料型別也相同

【案例】：查詢作業崗位是manager和salesman的員工
select ename , job from emp where job in ('manager','salesman');
select ename , job from emp where job = 'manager' union select ename , job from emp where job = 'salesman' ;     			// 效率更高，因為可以減少匹配的次數

1.1.12 limit（重點）

概念：limit 是將查詢的一部分取出來，通常使用在分頁查詢之中
- 分頁的作用是提高用戶的體驗
格式：
- 完整用法： limit（startIndex , length） startIndex：起始下標，默認從0開始 ; 沒有括號
- 預設用法：limit + 數字，表示前幾；
注意：在mysql之中，limit在order by 之后執行

【案例】：按照薪資降序，輸出排名在前5的員工
 select ename , sal from emp order by sal desc limit 5;
【案例】：按照薪資降序，輸出薪資排名 [3 , 5] 的員工
 select ename , sal from emp order by sal desc limit 2 , 3 ; 
【案例】：按照薪資降序，輸出薪資排名 [5 , 9] 的員工
 select ename , sal from emp order by sal desc limit 4 , 5 ;
 
 
 // 通用分頁的寫法
 每頁顯示pageSize條資料
    第pageNo頁 ： limit (pageNo - 1) * pageSize , pageSize ;
    
Java寫法：
public static void main(String[] args){
	// 用戶提交過來的一個頁碼，以及每頁顯示的記錄條數
	int pageNo = 5 ;     // 第5頁
	int pageSize = 10 ;  // 每頁顯示10條
	
	int startIndex = (pageNo - 1) * pageSize ;
	String sql = "secelt ... limit" + startIndex + "," + pageSize ;
}

1.2 DQL的總結

格式：select … from… where … group by … having … order by … limit …
執行順序：from ---- where ---- group by ---- having ---- select ---- order by ---- limit

2、DDL陳述句

2.1 CREAT陳述句

用于表的創建

2.1.1 表的創建

格式：creat table 表名（欄位名資料型別，欄位名2 ，資料型別，欄位名3 ，資料型別）；
- 建議以t_ 或者是 tbl_ 開始，可讀性強
- 欄位名、表名：見名知意
- 表名和欄位名都屬于識別符號
- 規范：所有的識別符號都是小寫，單詞與單詞之間通過下劃線進行銜接

【案例】：在test下創建一個學生表，其中性別的默認值設定為“男”
 create table t_student(
 	no int ,
 	name varchar(32),
 	sex char(1) default 'm' ,
 	age int(3),
 	email varchar(255)
 );
 
 
【案例】：創建一個用戶表
create table t_user(
    id int ,
    name varchar(32),
    birth date ,
    create_time  datetime 
);


// 快速創建表
可以將一個查詢結果制成一個新的表
【案例】：將emp復制一份，成為新表，表名為emp2
create table emp2 as select * from emp ;

2.1.2 mysql常見的資料型別

varchar（0~255）：可變長度的字串，根據實際長度動態分配空間
char（0~255）：定長字串，效率高，但是使用不當會造成空間浪費
int（0~11）：整型
bigint：長整型
float：單精度浮點型
double：雙精度浮點型
date：短日期型別，只包括年月日資訊默認格式：%Y-%m-%d
datetime：長日期型別，包括年月時時分秒日期 %Y-%m-%d %h:%i:%s
clob（>=255）：字符大物件，最多存盤4個G的字串，比如簡介、說明等
blob：二進制大物件，存盤圖片、聲音、視頻等流媒體資料【插入資料時，用IO流】

2.2 DROP陳述句

格式：
- drop table t_student ; // 當表不存在時，會報錯
- drop table if exists t_student 更加健壯

2.3 TRUNCATE陳述句

格式：truncate table 表名
特點：物理洗掉，效率高，表會被一次截斷；不支持回滾

 // 快速洗掉表中的資料 ---- truncate陳述句
【案例】：洗掉dept_bak表中的所有資料
 truncate table dept_bak ;
 
 當表非常大，上億條記錄時，可以選擇使用此方法，

2.4 ALTER陳述句

欄位的增刪改
- 欄位的增加
  - 格式：alter table 表名 add 新欄位名新欄位資料型別；
- 欄位的洗掉
  - 格式：alter table 表名 drop 欄位名；
- 欄位的修改
  - 對資料型別的修改
    - 格式：alter table 表名 modify 欄位名欄位名新的資料型別；
  - 對資料名稱的修改
    - 格式：alter table 表名 change 欄位名新欄位名新欄位名的資料型別；

// 對表的增加
【案例】：向dept_bak中增加一個欄位STURCT
alter table dept_bak add STURCT varchar(40);

// 對表的洗掉
【案例】：洗掉欄位名STRUCT
alter table dept_bak drop STRUCT ;

// 對表的修改
【案例】：將STRUCT欄位的資料型別修改為int型
alter table dept_bak modify STRUCT int ;

【案例】：將STRUCT欄位的名稱修改為S
alter table dept_bak change STRUCT S char(2)  ;

對約束的增刪改
- 增加
  - 添加外鍵約束： alter table 從表 add constraint 約束名稱 foreign key 從表(外鍵欄位) references 主表(主鍵欄位);
  - 添加主鍵約束： alter table 表 add constraint 約束名稱 primary key 表(主鍵欄位);
  - 添加唯一性約束： alter table 表 add constraint 約束名稱 unique 表(欄位);
- 洗掉
  - 洗掉外鍵約束： alter table 表名 drop foreign key 外鍵（區分大小寫） ;
  - 洗掉主鍵約束： alter table 表名 drop primary key ;
  - 洗掉約束約束： alter table 表名 drop key 約束名稱 ;
- 修改
  - mysql 對有些約束的修改時不支持，所以我們可以先洗掉，再添加

【案例】：向t_student表中添加約束
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id);
alter table t_student add constraint pk primary key(student_id);
alter table t_student add constraint uk unique(email);

【案例】：去除t_student表中的外鍵、主鍵、約束的約束
alter table t_student drop foreign key classno;
alter table t_student drop primary key no;
alter table t drop key foreign key;

【案例】：修改t_student中的約束
alter table t_student modify student_name varchar(30) unique;

3、DML陳述句

3.1 INSERT陳述句

insert into 表名（欄位名1 ，欄位名2 ，欄位名3…）values (值1 ，值2 ，值3 …) ；
insert into 表名（欄位…）values （值1 ，值2…），（值3，值4…），（…）；
注意：
- 欄位名要一一對應
- insert陳述句執行成功之后，一定會產生一行新的資料
- 欄位名可以省略，但表示全部都寫，順序不能變，

【案例】：向創建出來的學生表中插入五個值 
  insert into t_student values(1 , 'zhangsan' , 'm' , 20 , '21zhangsan@123.com');
  insert into t_student values(2 , 'lisi' , 'f' , 20 , '21lisi@123.com');
  insert into t_student values(3 , 'wangwu' , 'f' , 20 , '21wangwu@123.com' );
  insert into t_student values(4 , 'zhaoliu' , 'm' , 18 , '21zhaoliu@123.com');
  insert into t_student values (5 , 'songqi' , 'm' , 19 , '21songqi@123.com');
  
  mysql> select * from t_student;
+------+----------+------+------+--------------------+
| no   | name     | sex  | age  | email              |
+------+----------+------+------+--------------------+
|    1 | zhangsan | m    |   20 | 21zhangsan@123.com |
|    2 | lisi     | f    |   20 | 21lisi@123.com     |
|    3 | wangwu   | f    |   20 | 21wangwu@123.com   |
|    4 | zhaoliu  | m    |   18 | 21zhaoliu@123.com  |
|    5 | songqi   | m    |   19 | 21songqi@123.com   |
+------+----------+------+------+--------------------+
5 rows in set (0.00 sec)

【學生表的結構】：

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int(11)      | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)



now() 獲取系統當前時間
【案例】：插入李四的資訊
insert into t_user (id ,name , birth , create_time) values (2 ,'lisi','2002-10-2',now());



//insert 一次插入多條資料
【案例】：向t_user中一次插入多條資料
 insert into t_user values(1 , 'zhangsan' , '2000-10-01' , now()),
 (2 , 'lisi' , '2001-01-11' , now()) , (3 , 'wangwu' , '2002-10-02' , now()),
 (4 , 'zhaoliu' , '2003-6-8' , now()), (5 , 'songqi' , '2000-12-21' , now());
 
 
 【案例】：將查詢結果插入到一張表之中
  create table dept_bak as select * from dept ;
  insert into dept_bak select * from dept ;

3.2 DELETE陳述句

格式：delete from 表名 where 條件；
注意：不加條件表示清空表中的所有資料
原理：資料洗掉，空間不會釋放 ----- 可以恢復（rollback）

【案例】：洗掉表中id = 2 的資料
 delete from t_user where id = 2 ;
 
【案例】：洗掉t_user表中的所有資料，注意不能洗掉表的存在，
 delete from t_user ;   			// 效率比較慢

3.3 UPDATE陳述句

格式：update 表名 set 欄位名1 = 值1 ，欄位2 = 值2 ，欄位3 = 值3 … where 條件；
注意：如果沒有條件，資料會全部更新

【案例】：修改第三條李四的資訊為王五，生日為“2001-01-11”
 update t_user set name = 'wangwu', birth = '2001-01-11' where id = 3 ;
 
【案例】：將所有資訊的創建日期修改為此時
 update t_user set create_time = now();

4、約束

概念：約束（constraint）是指在創建表的時候，可以給一些欄位加入一些約束
作用：保證表中的資料有效
常見的約束：非空約束(not null)、唯一性約束(unique)、主鍵約束(primary key)、外鍵約束(foreign key)、檢查約束(check , mysql不支持，oracle支持)

4.1 非空約束

not null ：約束的欄位不能為空，只有列級約束，沒有表級約束

【案例】：測驗非空約束
drop table if exists t_vip ; 
create table t_vip(
	id int ,
	name varchar(255) not null 
); 
insert into t_vip (id , name) values(1 , 'zhangsan');
insert into t_vip (id , name) values(2 ,'lisi');
insert into t_vip (id) values (3); 
// ERROR 1364 (HY000): Field 'name' doesn't have a default value

4.2 唯一性約束

unique ：約束的欄位不能重復，但可以為null （列級約束）

【案例】：測驗唯一性約束
drop table if exists t_vip ;
create table t_vip(
    id int ,
    name varchar(255) unique ,			// 列級約束
    email varchar(255)
);
insert into t_vip (id , name , email) values (1 , 'zhangsan' , 'zahngsan@123.com');
insert into t_vip (id , name , email) values (2 , 'lisi' , 'lisi@123.com');
insert into t_vip (id , name , email) values (3 , 'wangwu' , 'wangwu@123.com');
select * from t_vip ;

insert into t_vip (id , name , email) values (4 , 'wangwu' , 'wangwu@xinlang.com');
//ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

insert into t_vip (id) values (4);
insert into t_vip (id) values (5);
select * from t_vip ;		// 可以執行

多個欄位聯合唯一性（表級約束）
- 格式：create table 表名（欄位1 ，欄位2 … ，unique（欄位1 ，欄位2））；

【案例】：測驗兩個欄位聯合唯一性約束
drop table if exists t_vip ;
create table t_vip(
    id int ,
    name varchar(255)  ,
    email varchar(255) ,
    unique(name , email)			// 表級約束
);

insert into t_vip (id , name , email) values (1 , 'zhangsan' , 'zhangsan@123.com');
insert into t_vip (id , name , email) values (2 , 'zhangsan' , 'zhangsan@xina.com');
select * from t_vip ;

insert into t_vip (id , name , email) values (3 , 'zhangsan' , 'zhangsan@123.com');
//ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@123.com' for key 'name'

一個欄位，被not null 和 unique 聯合約束
- 注意：被這兩個欄位聯合約束的欄位在desc中會在KEY中顯示PRI(primary key) ，會被自動當做主鍵，成為主鍵約束，（mysql專屬）

【案例】：測驗一個欄位的多種聯合約束
drop table if exists t_vip ;
create table t_vip (
	id int ,
    name varchar(255) not null unique 
);
desc t_vip ;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |	      PRI表示主鍵
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


insert into t_vip (id , name) values (1 , 'zhangsan');
insert into t_vip (id , name) values (2 , 'zhangsan');  // 錯誤
insert into t_vip (id) values (2) ;	   // 錯誤

4.3 主鍵約束

primary key (pk) :
相關概念：
- 主鍵約束：在主鍵上加的約束，一張表只能添加一個（復合主鍵也算一個）
- 主鍵欄位：含有主鍵的欄位
- 主鍵值：主鍵欄位中的每一個值，每一行記錄的唯一標識，類似于身份證號不能重復、不能為null ，具有唯一性，主鍵值建議使用int \ bigint \ char 型別，不建議用varchar，主鍵值一般為數字，一般為定長
注意：任意一張表都要有主鍵，如果沒有主鍵，表無效

【案例】：測驗主鍵約束
 drop table if exists t_vip ;
 create table t_vip (
 	id int primary key,
     name varchar(255)
 );
 insert into t_vip (id ,name) values (1 , 'zhangsan');
 insert into t_vip (id ,name) values (2 , 'lisi');
 select * from t_vip ;
 
 insert into t_vip (id ,name) values (2 , 'wangwu')
 //ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
 
 insert into t_vip (name) values ('zhaoliu');
 //ERROR 1364 (HY000): Field 'id' doesn't have a default value

添加聯合主鍵約束 (可以添加聯合主鍵) ------- 復合主鍵
- 一個欄位 ----- 單一主鍵
- 多個欄位 ------ 復合主鍵（不建議）

【案例】：展示添加聯合主鍵約束
 drop table if exists t_vip ;
 create table t_vip (
 	id int ,
     name varchar(255),
     email varchar(255),
     primary key(id ,name)
 );
 desc t_vip ;
 +-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

insert into t_vip (id , name , email) values (1 , 'zhangsan' , 'zhangsan@123.com');
insert into t_vip (id , name , email) values (1 , 'lisi' , 'lisi@xina.com');
select * from t_vip ; 	 // 可以運行

分類：自然主鍵、業務主鍵
- 自然主鍵：主鍵值是一個自然數，和業務沒關系
- 業務主鍵：主鍵值和業務緊密關聯，例如主鍵值是銀行卡號，為業務主鍵
在開發中，用自然主鍵使用多一點，（因為主鍵只要做到不重復就行）
機制：自動維護主鍵值 auto_increment 表示自增（從1 開始）；如果第一個數已經賦值，那么賦值的資料會被拋棄，報錯，其余正常執行

【案例】：展示自動維護主鍵值的機制
 drop table if exists t_vip ;
 create table t_vip (
 	id int primary key auto_increment ,
    name varchar(255)
 );
 
 insert into t_vip (id , name) values (2 ，'zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 insert into t_vip (name) values ('zhangsan');
 select * from t_vip ;

4.4 外鍵約束

foreign key
相關概念：
- 外鍵約束：
- 外鍵欄位：含有外鍵約束的欄位
- 外鍵值：外鍵當中的每一個值
被參考的表為父表，另一個表子子表
順序：
- 創建表的順序：先父后子
- 洗掉表的順序：先子后父
- 洗掉資料的順序：先子后父
- 插入資料的順序：先父后子
格式： foreign key (外鍵欄位) references 父表名(父表欄位)；
注意：
- 1、子表外鍵參考的主表的欄位不一定是主表的主鍵，但至少具有唯一性（unique約束）
- 2、外鍵值可以為NULL

【案例】：請設計資料庫表，來描述“班級和學生”的資訊
 // 第一種方案：同表存盤
 	缺點：資料冗余，空間浪費
 	
 // 第二種方案：異表存盤
 班級表：t_class		學生表：t_student
 drop table if exists t_student ;
 drop table if exists t_class ;
 create table t_class (
 	classno int primary key ,
    classname varchar(255) 
 );
 create table t_student (
 	 no int primary key auto_increment ,
     name varchar(255),
     cno int ,
     foreign key (cno) references t_class(classno)
 );
 
 insert into t_class (classno,classname) values (100 , '山東省濰坊市昌邑一中3年1班');
 insert into t_class (classno,classname) values (101 , '山東省濰坊市昌邑一中3年2班');
 
 insert into t_student(name , cno) values ('jack' , 100);
 insert into t_student(name , cno) values ('lucy' , 100);
 insert into t_student(name , cno) values ('lilei' , 100);
 insert into t_student(name , cno) values ('hanmeimei' , 100);
 insert into t_student(name , cno) values ('zhangsan' , 101);
 insert into t_student(name , cno) values ('lisi' , 101);
 insert into t_student(name , cno) values ('wangwu' , 101);
 insert into t_student(name , cno) values ('zhaoliu' , 101);
 
 select * from t_class ;
 select * from t_student ;
 
 
 【案例】：通過sql陳述句添加一條新的資訊 (songqi ,102);
 insert into t_student (name,cno) values ('songqi' , 102) ;
 //ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)) 
 
 【分析】: 因為在classno之中沒有102這個選項，所以在外鍵約束下的t_student 表中的cno欄位無法添加，
 
 // 正確的做法：
 insert into t_class (classno,classname) values (102 , '山東省濰坊市昌邑一中3年3班');
 insert into t_student (name, cno) values ('songqi' , 102) ;
 select * from t_class ;
 select * from t_student ;

5、存盤引擎

概念：mysql中特有的術語，其他資料庫中沒有，是存盤/組織資料的方式，不同的存取引擎
mysql支持的存盤引擎：
- MEMORY
- MRG_MYISAM
- CSV
- FEDERATED
- PERFORMANCE_SCHEMA
- MyISAM
- InnoDB
- BLACKHOLE
- ARCHIVE
格式：
- 添加：在建表的時候的‘）’外面使用ENGINE進行指定引擎

【案例】：查看建表陳述句
 show create table t_student ;
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE `t_student` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cno` int(11) DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `cno` (`cno`),
  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)





其中的ENGINE=InnoDB AUTO_INCREMENT=11 是引擎指定陳述句，可以在建表的時候進行修改
其中的DEFAULT CHARSET=utf8 是字符編碼方式 ， 也可以進行修改

mysql默認的存盤引擎是InnoDB，默認的字符編碼方式是utf8 ；

添加存盤引擎：

【案例】：創建一個含有主鍵為id；欄位為id、name；引擎為InnoDB 編碼方式為utf8的商品表
 drop table if exists t_shop ;
 create table t_shop (
 	id int primary key ,
    name varchar(255)
 ) engine = InnoDB default charset = gbk ;
 
 gbk : 可以存盤中文 ;

查看mysql支持的存盤引擎：

【案例】：查看mysql支持的存盤引擎
 show engines \G ;
 mysql> show engines \G;
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.05 sec)

ERROR:
No query specified

mysql常用的存盤引擎：MyISAM、InnoDB、MEMORY
- MyISAM：
- InnoDB：
- MEMORY：

MyISAM 存盤引擎是 MySQL 最常用的引擎，
? 它管理的表具有以下特征：
– 使用三個檔案表示每個表：
? 格式檔案 — 存盤表結構的定義（mytable.frm）
? 資料檔案 — 存盤表行的內容（ mytable.MYD）
? 索引檔案 — 存盤表上索引（ mytable.MYI）
– 靈活的 AUTO_INCREMENT 欄位處理
– 可被轉換為壓縮、只讀表來節省空間



InnoDB 存盤引擎是 MySQL 的預設引擎，
? 它管理的表具有下列主要特征：
– 每個 InnoDB 表在資料庫目錄中以.frm 格式檔案表示
– InnoDB 表空間 tablespace 被用于存盤表的內容
– 提供一組用來記錄事務性活動的日志檔案
– 用 COMMIT(提交)、 SAVEPOINT 及 ROLLBACK(回滾)支持事務處理
– 提供全 ACID 兼容
– 在 MySQL 服務器崩潰后提供自動恢復
– 多版本（ MVCC）和行級鎖定
– 支持外鍵及參考的完整性，包括級聯洗掉和更新




使用 MEMORY 存盤引擎的表，其資料存盤在記憶體中，且行的長度固定，這兩個特點使得 MEMORY 存盤引擎非
常快，
? MEMORY 存盤引擎管理的表具有下列特征：
– 在資料庫目錄內，每個表均以.frm 格式的檔案表示，
– 表資料及索引被存盤在記憶體中，
– 表級鎖機制，
– 不能包含 TEXT 或 BLOB 欄位，
? MEMORY 存盤引擎以前被稱為 HEAP 引擎，


MyISAM 表最適合于大量的資料讀而少量資料更新的混合操作， MyISAM 表的另一種適用情形是使用壓縮的只讀表，
? 如果查詢中包含較多的資料更新操作，應使用 InnoDB，其行級鎖機制和多版本的支持為資料讀取和更新的混合 操作提供了良好的并發機制，
? 可使用 MEMORY 存盤引擎來存盤非永久需要的資料，或者是能夠從基于磁盤的表中重新生成的資料，

6、事務

概念：transaction ，一個事務就是一個完整的業務邏輯
- 完整的業務邏輯：不可再分的最小的作業單元
只有DML陳述句和事務有關系，因為只有DML陳述句是操作資料庫中的資料的，涉及資料的改動，一定要保證安全，資料安全第一位
理解：
- 1、事務需要多條陳述句協同完成，相當于封裝
- 2、一個事務就是多條DML陳述句同時成功、同時失敗
- 3、在事務的執行程序中可以提交、回滾事務
  - 提交：清空事務性活動的日志檔案（臨時檔案），將資料徹底持久化到資料庫表之中，提交事務標志著事務的結束，并且是全部成功的結束，
    - commit 陳述句
  - 回滾：清空事務性活動的日志檔案（臨時檔案），將之前的所有DML操作全部撤銷，回滾事務標志著事務的結束，并且是全部失敗的結束，
    - rollback 陳述句，只能回滾到上一次的提交點
- 4、mysql中默認的事務行為：自動提交（每執行一次，提交一次）
- 5、關閉自動提交機制： start transaction ；

【案例】：測驗提交事務和回滾事務
drop table if exists dept_bak ;
create table dept_bak(
	DEPTNO int(2) not null ,
    DNAME varchar(14) ,
    LOC varchar(13) ,
    primary key (DEPTNO)
);
select * from dept_bak ;		// 記為表1

start transaction ;
insert into dept_bak (deptno , dname , loc) values (10 , 'SALES' , 'SHANDONG');
insert into dept_bak (deptno , dname , loc) values (20 , 'SALES' , 'SHANDONG');
insert into dept_bak (deptno , dname , loc) values (30 , 'SALES' , 'SHANDONG');
insert into dept_bak (deptno , dname , loc) values (40 , 'SALES' , 'SHANDONG');
select * from dept_bak ;	// 記為表2 ， 查看與表1的區別

rollback ;
select * from dept_bak ;	// 記為表3 ， 查看與表2的區別

insert into dept_bak (deptno , dname , loc) values (10 , 'SALES' , 'SHANDONG');
insert into dept_bak (deptno , dname , loc) values (20 , 'SALES' , 'SHANDONG');
insert into dept_bak (deptno , dname , loc) values (30 , 'SALES' , 'SHANDONG');
insert into dept_bak (deptno , dname , loc) values (40 , 'SALES' , 'SHANDONG');
select * from dept_bak ;

commit;
rollback ;
select * from dept_bak ;	// 記為表4 ， 查看與表2，3的區別

【結論】：rollback可以回滾到上一次的commit處
無論是提交事務還是回滾事務都會結束事務

事務的特性：ACID
- 原子性：說明事務是最小的作業單元，不可再分
- 一致性：在同一個事務中，所有的資料必須一次成功或者同時失敗
- 隔離性：A事務和B事務之間具有一定的隔離（兩個事務同時操作同一張表，類似于多執行緒并發訪問同一張表）
  - 事務隔離級別：
    - 讀未提交：read uncommitted ;
      - 描述：（最低事務隔離級別）事務A可以讀取到事務B未提交的資料
      - 存在問題：臟讀現象（dirty read）稱讀到了臟資料（未提交的資料），基本不用
    - 讀已提交：read committed ;
      - 描述：事務A只能讀取到事務B提交之后的資料
      - 解決問題：解決了臟讀現象
      - 存在問題：不可重復讀取資料
      - 資料100% 的真實，是oracle資料庫默認的事務隔離級別
    - 可重復讀：repeatable read ;
      - 描述：事務A開啟之后，讀取到的事務B中的資料都是一致的，即使在A開啟后B事務的資料修改并且提交了，事務A讀取到的資料還是不變
        
        相當于把表復制了一份，使用復印件進行操作，退出事務之后再次查詢，或者開啟新的事務，資料才會更新，
      - 解決問題：解決了不可重復讀取資料的問題
      - 存在問題：可能會出現幻影讀現象（讀取到的資料都是幻象，資料不真實）
      - 是 mysql 中默認的事務隔離級別
  - 序列化 / 串行化：serializable ;
    - 描述：（最高事務隔離級別，效率最低），表示事務排隊，類似于synchronized（執行緒同步）
    - 解決問題：解決了所有級別
    - 存在問題：效率最低
- 持久性：事務最終結束的一個保障
查看隔離級別：select @@transaction_isolation ;;

以下四個是測驗資料，測驗的時候1 ， 2 ， 4分別對3，

【案例】：驗證read uncommited
 drop table if exists t_user ;
 create table t_user(
 	name varchar(255) 
 );
 
 select @@transaction_isolation ;
 set session transaction isolation level read uncommitted ;  // 設定全域事務的隔離級別
 select @@transaction_isolation ;
 
 use test ; 
 start trabsaction ;
 select * from t_user ;

【案例】：驗證read commited
 drop table if exists t_user ;
 create table t_user(
 	name varchar(255) 
 );
 
 select @@transaction_isolation ;
 set session transaction isolation level read committed ;  // 設定全域事務的隔離級別
 select @@transaction_isolation ;
 
  use test ; 
  start trabsaction ;
  select * from t_user ;
  insert into t_user (name) values ('zhangsan');

【案例】：驗證repeatable read
 drop table if exists t_user ;
 create table t_user(
 	name varchar(255) 
 );
 
 select @@transaction_isolation ;
 
  use test ; 
  select * from t_user ;
  insert into t_user (name) values ('zhangsan');

【案例】：驗證serializable
 drop table if exists t_user ;
 create table t_user(
 	name varchar(255) 
 );
 
 select @@transaction_isolation ;
 set session transaction isolation level serializable ;  // 設定全域事務的隔離級別
 select @@transaction_isolation ;
 
  use test ; 
  select * from t_user ;
  insert into t_user (name) values ('zhangsan');

7、索引

概念：Index ，索引是在資料庫中的欄位上添加，是為了提高查詢效率而存在的一種機制，一張表的一個欄位可以添加一個索引，也可以多個欄位聯合起來也可以添加索引，相當于目錄，是為了縮小掃描范圍的一種機制，通過索引檢索，效率較高，
【注意】：索引需要排序，目的是生成區間查詢，縮小掃描范圍就是掃描區間和TreeSet資料結構相同（底層是一個自平衡的二叉樹）；在mysql之中索引是一個B-Tree結構，遵循左小右大原則存放，
實作原理：[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-baxFsf3Y-1614766137964)(%E6%95%B0%E6%8D%AE%E5%BA%93Mysql.assets/%E7%B4%A2%E5%BC%95%E7%9A%84%E5%AE%9E%E7%8E%B0%E5%8E%9F%E7%90%86-1613111420473.png)]
【注意】：
- 1、在所有資料庫之中，主鍵上會自動添加索引；在mysql之中，如果有unique約束的欄位也會自動添加索引，
- 2、在任何資料庫之中，任何一張表的任何一條記錄在硬碟存盤上都有一個硬碟的物理存盤編號
- 3、在mysql之中，索引是一個單獨的物件，不同的引擎以不同的形式存在，
  - MyISAM -------- .MYI 檔案之中
  - InnoDB --------- tablespace
  - MEMORY -------- 記憶體之中
  無論存盤在何處，索引在mysql之中都以自平衡二叉樹 B-Tree 的形式存在
添加索引的條件：
- 1、資料量龐大
- 2、該欄位經常出現在where后面，以條件的形式存在，（欄位總會被掃描）
- 3、該欄位有很少的DML操作，因為DML陳述句之后，會重新排序
建議不要隨意添加索引，建議通過unique約束的欄位或者通過主鍵查詢
索引的創建與洗掉
- 創建：create index 索引名 on 表名(欄位);
- 洗掉：drop index 索引名 on 表名；

【案例】：展示索引的創建和洗掉
// 添加
 create index emp_ename_index on emp(ename);
 mysql> desc emp ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  | MUL | NULL    |       |    其中的MUL表示索引
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

// 洗掉
 drop index emp_ename_index on emp ;

索引失效：
- 1、模糊查詢的時候并且以 ‘%’ 開頭的時候，索引失效，所以盡量避免這種情況出現(優化策略)
- 2、使用or的時候，or兩面欄位至少有一個沒有索引，索引失效，所以盡量少用or（優化策略)
- 3、使用復合索引的時候，沒有使用左側的列進行查找，索引失效，
- 4、在where當中索引添加了數學運算，索引失效，
- 5、在where當中索引列使用了函式(單行函式，分組函式不能出現在where子句中!)，索引失效
索引的分類：
- 單一索引
- 復合索引： create index emp_job_sal_index from emp(job , sal) ;
- 主鍵索引:
- 唯一性索引
- …

8、視圖

概念：view , 站在不同的角度去看同一份資料，以檔案的形式存在
視圖物件的創建與洗掉
- 創建：create view 視圖名 as select 欄位名 from 表明；
- 洗掉： drop view 視圖名 ;
【注意】：只用DQL陳述句才能用view來創建，可以面向視圖物件進行增刪改查，對視圖物件的增刪改查會導致原表被操作
作用：簡化SQL陳述句

【案例】：展示視圖的創建與洗掉
 drop table if exists dept2 ;
 create table dept2 select * from dept ;
 create view dept2_view as select * from dept2 ;
 drop view dept2_view ;
 
 
 【案例】：面向視圖進行創建、更新資料
 drop view if exists emp_dept_view ;
 create view emp_dept_view as select e.ename , e.sal , d.dname from emp e join dept d on e.deptno = d.deptno ;
 select * from emp_dept_view ; 
 update emp_dept_view set sal = 200 where ename = 'SMITH';

9、DBA常用命令

創建用戶：create user 用戶名 identified by ‘密碼’ ；

–可以登錄但是只可以看見一個庫 information_schema
匯入匯出：
- 匯入：source 檔案位置
- 匯出：mysqldump 資料庫名稱 > 檔案路徑檔案名.sql -u 用戶名 -p 密碼

【案例】展示DBA常用命令
 create user zzyyui identified by '20zzy100588A' ;
 grant all on *.* to 'zzyyui'@'%' with grant option;
 
 // 回收權限
 revoke all  on *.* from guest;
 
 //慎用
 use mysql;
 select * from user;
 update user set password = password('') where user = '';
 flush privileges;
 
 // 匯入匯出
 mysqldump -u 用戶名 -p 資料庫名 > 匯出的檔案名 
 source D:\ bjpowernode.sql

10 資料庫設計范式

概念：資料庫設計的依據（面試常考）
第一范式：任何一張表必須要有主鍵，每一個欄位原子性不可再分；
第二范式：建立在第一范式之上，要求所有非主鍵欄位完全依賴主鍵，不要產生部分依賴；
第三范式：建立在第二范式之上，要求所有非主鍵欄位直接依賴主鍵，不要產生傳遞依賴，

10.1 第一范式

地位：最核心、最重要的范式，所有表的設計都需要滿足
要求：必須有主鍵，并且每一個欄位原子性不可再分

【案例】：判斷是否滿足第一范式

學生編號 	學生姓名 	聯系方式
-------------------------------------
1001 		張三 		zs@gmail.com,1359999999
1002 		李四 		ls@gmail.com,13699999999
1001 		王五 		ww@163.net,13488888888

【存在問題】：
1、最后一條記錄和第一條重復（不唯一，沒有主鍵）
2、聯系方式欄位可以再分，不是原子性的

【更改】：
學生編號(pk) 	學生姓名 	email 			聯系電話
-------------------------------------------------------------
	1001 		張三 		zs@gmail.com 	1359999999
	1002 		李四 		ls@gmail.com 	13699999999
	1003 		王五 		ww@163.net 		13488888888

10.2 第二范式

要求：建立在第一范式之上，要求所有非主鍵欄位完全依賴主鍵，不要產生部分依賴
多對多三張表，關系表兩個外鍵

【案例】：判斷是否滿足第一范式

學生編號 學生姓名 教師編號 教師姓名
1001 張三 001 王老師
1002 李四 002 趙老師
1003 王五 001 王老師
1001 張三 002 趙老師
確定主鍵：
學生編號(PK) 教師編號(PK) 學生姓名 教師姓名
1001 001 張三 王老師
1002 002 李四 趙老師
1003 001 王五 王老師
1001 002 張三 趙老師

/**以上雖然確定了主鍵，但此表會出現大量的冗余，主要涉及到的冗余欄位為“學生姓名”和“教師姓名”，出現冗余的
原因在于，學生姓名部分依賴了主鍵的一個欄位學生編號，而沒有依賴教師編號，而教師姓名部門依賴了主鍵的一個
欄位教師編號，這就是第二范式部分依賴，***/

解決方案如下：

學生資訊表
學生編號（ PK） 學生姓名89 / 103
1001 				張三
1002 				李四
1003 				王五

教師資訊表
教師編號（ PK） 	教師姓名
001 			王老師
002 			趙老師

教師和學生的關系表
學生編號(PK) fk?學生表的學生編號 教師編號(PK) fk?教師表的教師編號
		1001						001
		1002 						002
		1003 						001
		1001 						002
/*如果一個表是單一主鍵，那么它就復合第二范式，部分依賴和主鍵有關系
以上是一種典型的“多對多”的設計*/

10.3 第三范式

要求：建立在第二范式之上，要求所有非主鍵欄位直接依賴主鍵，不要產生傳遞依賴

【案例】：判斷是否滿足第三范式

學生編號（ PK） 學生姓名 班級編號 班級名稱
1001 張三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 趙六 03 一年三班

/*從上表可以看出，班級名稱欄位存在冗余，因為班級名稱欄位沒有直接依賴于主鍵，班級名稱欄位依賴于班級編號，
班級編號依賴于學生編號，那么這就是傳遞依賴，解決的辦法是將冗余欄位單獨拿出來建立表，如：*/

學生資訊表
學生編號（ PK） 學生姓名 班級編號（ FK）
1001 張三 01
1002 李四 02
1003 王五 03
1004 趙六 03

班級資訊表
班級編號（ PK） 班級名稱
01 一年一班
02 一年二班
03 一年三班

10.4 三范式總結

第一范式：有主鍵，具有原子性，欄位不可分割
第二范式：完全依賴，沒有部分依賴
第三范式：沒有傳遞依賴
【注意】：資料庫設計盡量遵循三范式，但是還是根據實際情況進行取舍，有時可能會拿冗余換速度，最終用目的要滿足客戶需求，
一對一設計，有兩種設計方案：
- 第一種設計方案：主鍵共享 (較少)
- 第二種設計方案：外鍵唯一
一對多設計：兩張表，多的表加外鍵
多對多設計：三張表，關系表兩個外鍵

【注意】：設計是理論上的，實踐和理論都是為了滿足客戶的需求，有的時候會拿冗余換速度，實際開發中，一切以客戶需求為準，

五、34道作業題

5.1 — 5.5

【習題1】取得每個部門最高薪水的人員名稱
····// 先取得每個部門的最高薪資作為一張表 ， 再和emp連接查詢等值等部門的人員名稱
 select e.ename , a.sal , e.deptno from (select max(sal) sal , deptno from emp group by deptno) as a join emp e on a.sal = e.sal and a.deptno = e.deptno ;

【習題2】哪些人的薪水在部門的平均薪水之上
 select distinct e.ename , e.sal from  (select avg(sal) as sal , deptno from emp group by deptno) as a join emp e on (e.sal > a.sal and a.deptno = a.deptno);
 
【習題3】取得部門中（所有人的）平均的薪水等級
 select e.deptno , avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno order by deptno asc ;

【習題4】不準用組函式（ Max），取得最高薪水（給出兩種解決方案）
 方案一： select sal from emp order by sal desc limit 1 ;
 方案二： select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);  
 
【習題5】取得平均薪水最高的部門的部門編號（至少給出兩種解決方案）
 方案一： select deptno from emp group by deptno order by avg(sal) desc limit 1 ;
 方案二： select deptno  from emp group by deptno having avg(sal) = (select max(e.sal) from (select deptno , avg(sal) sal from emp group by deptno) as e);

5.6 — 5.10

【習題6】取得平均薪水最高的部門的部門名稱
 select d.dname from emp e join dept d  on e.deptno = d. deptno  group by e.deptno order by avg(e.sal) desc limit 1 ; 
 
【習題7】求平均薪水的等級最低的部門的部門名稱
 select d.dname  from emp e join dept d on e.deptno = d.deptno group by e.deptno order by avg(e.sal) asc limit 1; 

【習題8】取得比普通員工(員工代碼沒有在 mgr 欄位上出現的)的最高薪水還要高的領匯入姓名
 select ename , sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null ));

【習題9】取得薪水最高的前五名員工
 select max(sal) from emp order by sal desc limit 5 ;

【習題10】取得薪水最高的第六到第十名員工
 select max(sal) from emp order by sal desc limit 5 ,5 ;

5.11 — 5.15

【習題11】取得最后入職的 5 名員工
 select ename , hiredate from emp order by hiredate desc limit 5; 

【習題12】取得每個薪水等級有多少員工
 select s.grade , count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade order by s.grade asc ;

【習題13】面試題
有 3 個表 S(學生表)， C（課程表）， SC（學生選課表）
S（ SNO， SNAME）代表（學號，姓名）95 / 103
C（ CNO， CNAME， CTEACHER）代表（課號，課名，教師）
SC（ SNO， CNO， SCGRADE）代表（學號，課號，成績）
問題：
1，找出沒選過“黎明”老師的所有學生姓名，
2，列出 2 門以上（含 2 門）不及格學生姓名及平均成績，
3，即學過 1 號課程又學過 2 號課所有學生的姓名，
請用標準 SQL 語言寫出答案，方言也行（請說明是使用什么方言），
-----------------------------------------------------------------------------
CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '語文', '張');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英語', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '數學', '趙');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '學生 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '學生 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '學生 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '學生 4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit;
問題 1.找出沒選過“黎明”老師的所有學生姓名，


問題 2:列出 2 門以上（含 2 門）不及格學生姓名及平均成績，
問題 3:即學過 1 號課程又學過 2 號課所有學生的姓名

【習題14】列出所有員工及領導的姓名
 select e.ename , ifnull(m.ename,'沒有上級') from emp e join emp m on e.mgr = m.empno ;

【習題15】列出受雇日期早于其直接上級的所有員工的編號,姓名,部門名稱
 select e.empno , e.ename , d.dname from emp e join emp m on e.hiredate < m.hiredate and e.mgr = m.empno join dept d on e.deptno = d.deptno ;

5.16 — 5.20

【習題16】列出部門名稱和這些部門的員工資訊,同時列出那些沒有員工的部門.
 select d.dname , e.* from emp e right join dept d on e.deptno = d.deptno order by e.deptno; 

【習題17】列出至少有 5 個員工的所有部門
 select d.dname , count(*) from emp e join dept d on e.deptno = d.deptno group by e.deptno having count(*) >= 5;

【習題18】列出薪金比"SMITH"多的所有員工資訊.
 select * from emp where sal > (select sal from emp where ename = 'SMITH');

【習題19】列出所有"CLERK"(辦事員)的姓名及其部門名稱,部門的人數
 select e.ename , d.dname , c.cc from emp e join dept d on e.deptno = d.deptno join (select deptno , count(*) as cc from emp group by deptno) as c on d.deptno = c.deptno where job = 'CLERK' ;

【習題20】列出最低薪金大于 1500 的各種作業及從事此作業的全部雇員人數
 select job ,count(*) from emp group by job having min(sal) > 1500 ;

5.21 —5 .25

【習題21】列出在部門"SALES"<銷售部>作業的員工的姓名,假定不知道銷售部的部門編號
 select e.ename from emp e join dept d on d.dname = 'SALES' and e.deptno = d.deptno  ;
 
【習題22】列出薪金高于公司平均薪金的所有員工,所在部門,上級領導,雇員的工資等級
 select e.ename '姓名', d.dname '部門名稱', ifnull(m.ename , '無') '上級領導', s.grade '工資等級' from emp e join dept d on e.deptno = d.deptno left join emp m on e.mgr = m.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) avgsal from emp) ;

【習題23】列出與"SCOTT"從事相同作業的所有員工及部門名稱.
select e.ename , d.dname from emp e join dept d on e.deptno = d.deptno where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT';

【習題24】列出薪金等于部門 30 中員工的薪金的其他員工的姓名和薪金
select ename , sal from emp where sal in (select sal from emp where deptno = 30) and deptno != 30 ;

【習題25】列出薪金高于在部門 30 作業的所有員工的薪金的員工姓名和薪金.部門名稱.
 select e.ename , e.sal , d.dname from emp e join dept d on d.deptno = e.deptno where e.sal > (select max(sal) from emp where deptno = 30) ;

5.26 — 5.30

【習題26】列出在每個部門作業的員工數量,平均工資和平均服務期限.
// 服務期限：從入職到現在服務了多長時間
 select d.dname, count(*) , avg(sal) avgsal , avg(now() - e.hiredate ) avgser from emp e join dept d on e.deptno = d.deptno group by e.deptno order by e.deptno asc;

【習題27】出所有員工的姓名、部門名稱和工資，
 select e.ename , d.dname , e.sal from emp e join dept d on e.deptno = d.deptno ;

【習題28】列出所有部門的詳細資訊和人數
 select d.deptno , d.dname ,d.loc, ifnull(c.cc , '0') '人數' from dept d left join (select deptno , count(*) cc from emp group by deptno) as c on d.deptno = c.deptno ;

【習題29】列出各種作業的最低工資及從事此作業的雇員姓名
 select e.empno , e.ename , e.job , e.mgr , e.hiredate , e.sal , e.comm , e.deptno from emp e join (select min(sal) minsal from emp group by job ) as m where e.sal = m.minsal;

【習題30】列出各個部門的 MANAGER(領導)的最低薪金
 select deptno , min(sal) from emp where job = 'manager' group by deptno order by deptno asc ;

5.31 — 5.34

【習題31】列出所有員工的年工資,按年薪從低到高排序
 select ename , sal*12 as income from emp order by income asc ; 

【習題32】求出員工領導的薪水超過 3000 的員工名稱與領導名稱
 select e.ename , m.ename from emp e join emp m on m.sal > 3000 where e.mgr = m.empno;

【習題33】求出部門名稱中,帶'S'字符的部門員工的工資合計、部門人數
 select d.dname , ifnull(sum(sal), '0') as sumsal, count(e.ename) from emp e right join dept d on d.deptno = e.deptno where d.dname like '%S%' group by d.deptno , d.dname ;

【習題34】給任職日期超過 30 年的員工加薪 10%
 start transaction ;
 update emp set sal = sal*1.1 where timestampdiff(YEAR , hiredate , now()) > 30 ;
 rollback ;

六、相關檔案

本文前期匯入的資料庫連接：
https://pan.baidu.com/s/1rbHotfQMnBYWwgbDLCMjNQ 提取碼:9wpf

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/265992.html

標籤：其他

上一篇：PHP 撰寫 MYSQL 新增并且更新的批量入庫方法

下一篇：求助大佬們

基于命令列mysql的基礎學習