# 【韩顺平讲Java】Chapter24 - 零基础学MySQL
***
> **即使再小的帆也能遠航**
> 參考自[【零基础 快速学Java】韩顺平 零基础30天学会Java](https://www.bilibili.com/video/BV1fh411y7R8/?vd_source=c5074574112ef27dae243d70aa2175b8)
>###### tags: `韓順平講Java`
***
# MySQL 安裝配置
[網友整理](https://blog.csdn.net/qq_42862019/article/details/128211711?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EYuanLiJiHua%7EPosition-2-128211711-blog-117966454.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EYuanLiJiHua%7EPosition-2-128211711-blog-117966454.pc_relevant_default&utm_relevant_index=3)
- Mysql5.7 地址 : https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置为自己MYSQL的安装目录
basedir=D:\Environment\mysql-5.7.19-winx64
# 设置为MYSQL的数据目录，這個目錄是系統創建
datadir=D:\Environment\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
#跳过安全检查
skip-grant-tables
cd /D D:\Environment\mysql-5.7.19-winx64\bin
mysqld -install
mysqld --initialize-insecure --user=mysql
net start mysql
net stop mysql
mysql -u root -p
---
use mysql;
---
update user set authentication_string=password('Jerry0122') where user='root' and Host='localhost';
上面的语句就是修改 root用户的密码为 hsp
注意：在后面需要带 分号，回车即可执行该指令
---
执行: flush privileges; 刷新权限
退出: quit
---
# 數據庫
---
## 創建數據庫
```sql=
# 演示數據庫操作
# 創建一個名稱為hsp_db01的數據庫。 [圖形化和指令演示]
# 使用指令創建數據庫
CREATE DATABASE hsp_db01;
# 刪除數據庫指令
DROP DATABASE hsp_db01;
# 創建一個utf8字符集的hsp_db02數據庫
CREATE DATABASE hsp_db02 CHARACTER SET utf8
# 創建一個utf8字符集，並帶校對規則的的hsp_db03數據庫
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
# 校對規則 utf8_bin 區分大小 默認 utf8_general_ci 不區分大小寫
# 下面是一條查詢的sql，select 查詢，* 表示所有字段，
# FROM從哪個表，WHERE從哪個字段，NAME = 'tom'查詢名字是tom
SELECT * FROM t1 WHERE NAME = 'tom'
```
---
## 查看/刪除數據庫
```sql=
# 演示刪除和查詢數據庫
# 查看當前數據庫服務器中的所有數據庫
SHOW DATABASES
# 查看前面創建的hsp_db01數據庫的定義訊息
SHOW CREATE DATABASE `hsp_db01`
# 老師說明
# 在創建數據庫和表時，為了規避關鍵字，可以使用反引號解決
create database `CREATE`
DROP DATABASE `CREATE`
# 刪除前面創建的hsp_db01數據庫
DROP DATABASE hsp_db01
```
---
## 備份數據庫
- 這個備份文件，就是對應的sql語句
# 放到DOS執行
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql
# 恢復數據庫(注意 : 進入Mysql命令行在執行)
source d:\\bak.sql
# 第二種方法
複製bak.sql內容到查詢編輯器執行
- 差在 -B
- 恢復表時，先使用 use XXX，更換database
# 表
## 創建表
```sql=
# 指令創建表
CREATE TABLE `user`(
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB
# 刪除表格
DROP TABLE `user`
```
## 修改表
```sql=
# 增加image列，varchar類型(要求在resume後面)
ALTER TABLE `emp`
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER resume
# 修改 job 列，使其長度為60
ALTER TABLE `emp`
MODIFY job VARCHAR(60) NOT NULL DEFAULT '';
# 刪除sex
ALTER TABLE `emp`
DROP sex;
DESC emp -- 顯示表結構，可以查看表的所有字段
# 表名改為 employee
RENAME TABLE `emp` TO employee;
# 修改字符集為 utf8
ALTER TABLE employee CHARACTER SET utf8;
# name -> user_name
ALTER TABLE employee
CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '';
DESC employee -- 顯示表結構，可以查看表的所有字段
```
# Mysql數據類型
- 常用
1. int
2. double
3. decimal
4. char
5. varcchar
6. text
7. datetime
8. timestamp
## 整型
```sql=
# 演示整型的使用
# 使用tinyint 來演示範圍 : 有符號 -128 ~ 127，如果沒有符號 0 ~ 255
# 說明 : 表的字符集，校驗規則，存儲引擎，老師使用默認
# 1. 如果沒有指定 unsigned，則 TINYINT 就是有符號的
# 2. 如果指定 unsigned，則 TINYINT 就是無符號的 0~255
CREATE TABLE t3(
id TINYINT);
CREATE TABLE t4(
id TINYINT UNSIGNED);
INSERT INTO t3 VALUES(127); # 這是非常簡單的添加語句
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4
```
## Bit
```sql=
# 演示bit類型使用
# 說明
# 1. bit(m)，m 在1~64
# 2. 添加數據 範圍按照你給定的位數來確定，比如 m = 8，表示一個字節 0~255
# 3. 顯示按照bit
# 4. 查詢時，仍然可以按照數值來查詢
CREATE TABLE t05(num BIT(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 3;
```
## 小數
```sql=
# 演示decimal類型、float、double使用
# 創建表
create table t06(
num1 float,
num2 double,
num3 decimal(30,20));
# 添加數據
insert into t06 values(88.12345678912345,88.12345678912345,88.12345678912345);
select * from t06
# decimal 可以存放很大的數
CREATE TABLE t07(
num DECIMAL(65));
INSERT INTO t07 VALUES(89555555555555555555555555666666666666666666666660);
SELECT * FROM t07
CREATE TABLE t08(
num BIGINT UNSIGNED);
# Out of range...
INSERT INTO t08 VALUES(89555555555555555555555555666666666666666666666660);
SELECT * FROM t08
```
## 字符串
- 注意: `varchar`的指定大小與**字節**相關，會因編碼方式不同影響最終能存儲的字符數量
```sql=
# 演示字符串類型使用 char varchar
-- CHAR(SIZE)
-- 固定長度字符串，最大255字符
-- VARCHAR(SIZE) 0~65535
-- 可變長度字符串 最大65532字節 [utf8編碼最大21844字符 1-3個字節用於紀錄大小]
-- 如果表的編碼是utf8，varchar(size)，size = (65535-3)/3 = 21844
-- 如果表的編碼是gbk，varchar(size)，size = (65535-3)/2 = 32766
CREATE TABLE t09(
`name` CHAR(255));
CREATE TABLE t10(
`name` VARCHAR(21844));
```
### 字符串細節
```sql=
# 演示字符串類型的使用細節
# char(4) 和 varchar(4) 這個4表示的是字符，而不是字節，不區分字符是漢字還是字母
CREATE TABLE t11(
`name` CHAR(4));
INSERT INTO t11 VALUES('abcd');
INSERT INTO t11 VALUES('韓順平好');
SELECT * FROM t11;
CREATE TABLE t12(
`name` VARCHAR(4));
INSERT INTO t12 VALUES('韓順平好');
INSERT INTO t12 VALUES('ab北京');
SELECT * FROM t12;
# 如果varchar不夠用，可以考慮使用 MEDIUMTEXT 或者 LONGTEXT
# 如果想簡單點，可以直接使用 text
# Text類型的存放空間大小會自動調適
CREATE TABLE t13(
content TEXT, content2 MEDIUMTEXT, content3 LONGTEXT);
INSERT INTO t13 VALUES('hspedu','hspedu100','hspedu1000~~');
SELECT * FROM t13;
```
## 日期
```sql=
# 演示時間相關的類型
# 創建一張表 date, datetime, timestamp
CREATE TABLE t14(
birthday DATE, -- 生日
job_time DATETIME, -- 紀錄年月日時分秒
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP); -- 登入時間，如果希望login_time自動更新，需要配置
SELECT * FROM t14;
INSERT INTO t14(birthday,job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果我們更新 t14 表的某條紀錄，login_time會自動的以當前時間進行更新
```
## 創建表練習
```sql=
CREATE TABLE `emp`(
id INT,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_DATE DATETIME,
job VARCHAR(32),
salary DOUBLE,
resume TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO `emp` VALUES(100,'Jerry','男','1999-01-22','2021-09-01 11:11:11','Research',5000,'考慮未來轉職XD');
SELECT * FROM `emp`;
DROP TABLE `emp`
```
# CRUD
## INSERT
```sql=
# 練習Insert語句
CREATE TABLE goods(
id INT,
goods_name VARCHAR(10),
price DOUBLE);
# 添加數據
INSERT INTO goods (id, goods_name, price)
VALUES(10,'華為手機',2000);
INSERT INTO goods (id, goods_name, price)
VALUES(20,'蘋果手機',3000);
SELECT * FROM goods
INSERT INTO employee VALUES(
200,'test','2020-03-06','2020-03-06 13:20:30','巡山',50,'wanna play','');
INSERT INTO employee VALUES(
300,'test2','2021-03-06','2022-03-06 13:20:40','巡山',50,'wanna play','缺');
SELECT * FROM employee
```
### 細節
```sql=
# 說明 insert語句的細節
-- 1. 插入的數據應與字段的數據類型相同。
-- 比如把 'abc' 添加到 INT 會錯誤
INSERT INTO goods (id, goods_name, price)
VALUES('abc','華為手機',2000); -- 不行
INSERT INTO goods (id, goods_name, price)
VALUES('30','華為手機',2000); -- OK
-- 2. 數據的長度應該在規定範圍內，比如不能將長度80的字符串放到限制大小40的字段中
INSERT INTO goods (id, goods_name, price)
VALUES(40,'VIVO手機VIVO手機VIVO手機VIVO手機',3000); -- WRONG
-- 3. 在 values 中列出的數據位置必須與被加入的列排列位置相對應
INSERT INTO goods (id, goods_name, price)
VALUES('VIVO',40,3000); -- WRONG
-- 4.字符和日期類型數據應包含在單引號中
INSERT INTO goods (id, goods_name, price)
VALUES(40,VIVO手機,3000); -- WRONG
-- 5. 字段可以插入空值[前提是該字段允許為空], INSERT INTO `table` VALUE(NULL)
INSERT INTO goods (id, goods_name, price)
VALUES(40,'VIVO',NULL); -- OK
-- 6. insert into tab_name (字段名...) values (),(),() 形式添加多條紀錄
INSERT INTO goods (id, goods_name, price)
VALUES(50,'三星手機',2300),(60,'谷歌手機',4300); -- OK
-- 7. 如果是給表中的所有字段添加數據，可以不寫前面的字段名稱
INSERT INTO goods
VALUES(70,'HTC手機',300); -- OK
-- 8. 默認值的使用，當不給某個字段值時，如果有默認值就會添加默認值，否則報錯
-- 如果某個列沒有指定 not null，那麼當添加數據時，沒有給定值，則會默認null
-- 如果希望指定某個字段的默認值，可以在創建表時指定
-- e.g. price DOUBLE NOT NULL DEFAULT 100
INSERT INTO goods (id, goods_name)
VALUES(80,'GREEN手機'); -- OK
SELECT * FROM goods
```
## UPDATE
- 除非真的有需求要改全部，不然記得加**WHERE**
```sql=
-- 演示update語句
-- 1. 將所有員工薪水修改為5000。如果沒有帶 where 條件，會修改所有紀錄
-- 因此要小心
UPDATE employee SET salary = 5000;
-- 2. 將姓名為test的員工薪水修改為3000
UPDATE employee SET salary = 3000 WHERE user_name = 'test';
-- 3. 將老妖怪的薪水在原有基礎上加1000
INSERT INTO employee
VALUES(200,'老妖怪','1990-11-11','2000-11-11 10:10:10', '搥背的',5000,
'給大王搥背','');
UPDATE employee
SET salary = salary + 1000
WHERE user_name = '老妖怪';
-- 可以修改多個列
UPDATE employee
SET salary = salary + 1000, job = '出主意的'
WHERE user_name = '老妖怪';
SELECT * FROM employee;
```
## DELETE
```sql=
-- delete 語句演示
-- 1. 刪除表中名為老妖怪的紀錄
DELETE FROM employee WHERE user_name = '老妖怪';
-- 2. 刪除所有紀錄，老師提醒，一定要小心
DELETE FROM employee;
-- Delete語句不能刪除某一列的值 ( 可使用update設為null或者'')
UPDATE employee SET job = '' WHERE user_name = '老妖怪';
SELECT * FROM employee;
```
## SELECT
```sql=
-- select 語句[重點 難點]
create table student(
id int not null default 1,
name varchar(20) not null default '',
chinese float not null default 0.0,
english float not null default 0.0,
math float not null default 0.0
);
insert into student values (1,'韓順平',89,78,90);
insert into student values (2,'張飛',67,98,56);
insert into student values (3,'宋江',87,78,77);
insert into student values (4,'關羽',88,98,90);
insert into student values (5,'趙雲',82,84,67);
insert into student values (6,'歐陽鋒',55,85,45);
insert into student values (7,'黃蓉',75,65,30);
-- 查詢表中所有學生的信息
SELECT * FROM student;
-- 查詢表中所有學生的姓名和對應的英語成績
SELECT `name`,english FROM student;
-- 過濾表中重複數據
SELECT DISTINCT english FROM student;
-- 要查詢的紀錄，每個字段都相同，才會去重
SELECT DISTINCT `name`, english FROM student;
```
```sql=
-- select 語句的使用
-- 統計每個學生的總分
SELECT `name`, (chinese + english + math) FROM student;
-- 在所有學生總分加10分
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用別名表示學生分數
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score FROM student;
```
### 邏輯運算符
```sql=
-- 查詢姓名為趙雲的學生成績
SELECT * FROM student WHERE `name` = '趙雲';
-- 查詢英語成績大於90
SELECT * FROM student WHERE english > 90;
-- 查詢總分大於200的所有同學
SELECT * FROM student WHERE (chinese + english + math) > 200;
-- 查詢 math > 60 and id > 4 的學生成績
SELECT * FROM student WHERE ((math > 60) AND (id > 4))
-- 查詢 english > chinese
SELECT * FROM student WHERE english > chinese
-- 查詢 (english + chinese + math) > 200 and (math < chinese) and '韓%'
-- 韓% 表示名字以韓開頭的就可以
SELECT * FROM student WHERE ((english + chinese + math) > 200
AND (math < chinese)
AND `name` LIKE '趙%')
-- 查詢英文分數在 80 - 90 之間的同學
SELECT * FROM student WHERE english >= 80 AND english <= 90;
SELECT * FROM student WHERE english BETWEEN 80 AND 90; -- between .. and .. 是閉區間
-- 查詢數學分數為89,90,91的同學
SELECT * FROM student WHERE math IN (89,90,91);
-- 查詢所有姓李的學生成績
SELECT * FROM student WHERE `name` LIKE '韓%';
-- 查詢數學分 > 80，語文分 > 80 的同學
SELECT * FROM student WHERE math > 80 AND chinese > 80;
-- 查詢語文分數在 70 - 80 之間的同學
SELECT * FROM student WHERE chinese BETWEEN 70 AND 80;
-- 查詢總分數為189,190,191的同學
SELECT * FROM student WHERE (chinese + english + math) IN (189,190,191);
-- 查詢所有姓李或者姓宋的學生成績
SELECT * FROM student WHERE `name` LIKE '韓%' OR `name` LIKE '宋%';
-- 查詢數學比語文多30分的學生
SELECT * FROM student WHERE math = chinese + 30;
SELECT * FROM student
```
### 排序
```sql=
-- 演示 order by 使用
-- 數學成績 升序輸出
SELECT * FROM student
ORDER BY math;
-- 總分從高到低，可以使用別名
SELECT `name`, (chinese + english + math) AS total_score FROM student
ORDER BY total_score DESC;
-- 對姓李的學生成績排序輸出(升序) where + order by
SELECT `name`, (chinese + english + math) AS total_score FROM student
WHERE `name` like '韓%'
ORDER BY total_score;
```
### group by / having
```sql=
#部门表
CREATE TABLE dept (
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*部门编号*/
dname VARCHAR(20) NOT NULL DEFAULT '', /*部门名字*/
loc VARCHAR(13) NOT NULL DEFAULT '' /*地址*/
);
INSERT INTO dept
VALUES
( 10, 'ACCOUNTING', 'NEW YORK' ),
( 20, 'RESEARCH', 'DALLAS' ),
( 30, 'SALES', 'CHICAGO' ),
( 40, 'OPERATIONS', 'BOSTON' );
# 创建表EMP雇员
CREATE TABLE emp (
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
ename VARCHAR (20) NOT NULL DEFAULT '',/*名字*/
job VARCHAR (9) NOT NULL DEFAULT '',/*工作*/
mgr MEDIUMINT UNSIGNED,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL (7,2) NOT NULL,/*新水*/
comm DECIMAL (7,2),/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/
);
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-22', 2975.00, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28', 1250.00, 1400.00, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1991-5-1', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1991-6-9', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1997-4-19', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1991-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1991-9-8', 1500.00, NULL, 30),
(7900, 'JAMES', 'CLERK', 7698, '1991-12-3', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1991-12-3', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1992-1-23', 1300.00, NULL, 10);
# 工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*工资级别*/
losal DECIMAL(17,2) NOT NULL,/*该级别最低工资*/
hisal DECIMAL(17,2) NOT NULL/*该级别最高工资*/
);
INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);
# 演示 group by + having
-- GROUP BY 用於對查詢結果的結果分組統計
-- HAVING 子句用於限制分組顯示結果
-- 如何顯示每個部門的平均工資和最高工資
-- 老韓分析: avg(sal), max(sal)
SELECT AVG(sal), MAX(sal), deptno
FROM emp GROUP BY deptno;
-- 顯示每個部門的每種崗位的平均工資和最低工資
-- 老韓分析: 1. 顯示每個部門的平均工資和最低工資
-- 2. 顯示每個部門的每種崗位的平均工資和最低工資
SELECT AVG(sal), MIN(sal), deptno, job
FROM emp GROUP BY deptno, job;
-- 顯示平均工資低於2000的部門號和他的平均工資 // 別名
-- 老韓分析:
-- 1. 顯示每個部門的的平均工資和部門號
-- 2. 對 1 進行過濾，保留 AVG(sal) < 2000
-- 3. 可以使用別名進行過濾
SELECT AVG(sal), deptno
FROM emp GROUP BY deptno
HAVING AVG(sal) < 2000;
SELECT AVG(sal) AS avg_sal, deptno
FROM emp GROUP BY deptno
HAVING avg_sal < 2000;
```
### 加強
```sql=
-- 查詢加強
-- 使用 where子句
-- ? 如何查找1992.1.1後入職的員工
-- 老師說明: 在MySQL中，日期類型可以直接比較，需要注意格式
SELECT * FROM emp
WHERE hiredate > '1992-01-01'
-- 如何使用like操作符
-- %: 表示0到多個任意字符， _ : 表示單個任意字符
-- ? 如何顯示首字符為S的員工姓名工資
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%'
-- ? 如何顯示第三個字符為大寫O的所有員工的姓名和工資
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%'
-- 如何顯示沒有上級的僱員的情況
SELECT * FROM emp WHERE mgr IS NULL;
-- 查詢表結構
DESC emp
-- ----------------------------------------
-- 使用order by 子句
-- ? 按照工資由低到高，顯示雇員信息
SELECT * FROM emp ORDER BY sal
-- ? 按照部門號升序且雇員工資降序，顯示雇員信息
SELECT * FROM emp ORDER BY deptno ASC, sal DESC;
```
```sql=
-- 分頁查詢
-- 按雇員的 empno 號升序取出，每頁顯示三條紀錄，分別顯示1~3頁
SELECT * FROM emp ORDER BY empno LIMIT 0,3;
SELECT * FROM emp ORDER BY empno LIMIT 3,3;
SELECT * FROM emp ORDER BY empno LIMIT 6,3;
-- 推導一個公式
SELECT * FROM emp ORDER BY empno LIMIT 每頁顯示紀錄數 * (第幾頁-1),每頁顯示紀錄數;
-- EXERCISE : 按雇員的 empno 號降序取出，每頁顯示5條紀錄，分別顯示3,5頁
SELECT * FROM emp ORDER BY empno DESC LIMIT 10, 5;
SELECT * FROM emp ORDER BY empno DESC LIMIT 20, 5;
```
```sql=
-- 增強group by 的使用
-- 1. 顯示每種崗位的雇員總數、平均薪資
SELECT COUNT(*), AVG(sal), job FROM emp GROUP BY job;
-- 2. 顯示雇員總數、和獲得補助的雇員數
-- 思路 : 獲得補助的雇員數就是 comm 列為非null
SELECT COUNT(*), COUNT(comm) FROM emp;
-- 擴展要求: 統計沒有獲得補助的雇員數
SELECT COUNT(*), COUNT(IF(comm IS NULL,1,NULL)) FROM emp;
SELECT COUNT(*), COUNT(IF(comm IS NULL,'abc',NULL)) FROM emp;
SELECT COUNT(*), COUNT(*) - COUNT(comm) FROM emp;
-- 3. 顯示管理者的總人數
SELECT COUNT(DISTINCT mgr) FROM emp;
-- 4. 顯示雇員工資的最大差額
SELECT MAX(sal) - MIN(sal) FROM emp;
```
---
```sql=
-- 應用案例 : 統計各部門平均工資，並且>1000，由高到低排列，取出前兩行紀錄
SELECT deptno, AVG(sal) AS average_sal FROM emp
GROUP BY deptno
HAVING average_sal > 1000
ORDER BY average_sal DESC
LIMIT 0,2
```
# 多表查詢
## 笛卡爾集
```sql=
-- 多表查詢
-- ?顯示雇員名、雇員工資、所在部門名字[笛卡爾集]
/*
老韓分析 :
1. 雇員名和雇員工資來自emp表
2. 部門的名字來自 dept 表
3. 需要對emp, dept兩表進行查詢 ename, sal, dname, deptno => ambiguous
4. 當我們需要指定顯示某個表的列時，需要 表.列表
*/
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM emp, dept;
-- 老韓小技巧: 多表查詢的條件不能少於表的個數-1，否則會出現笛卡爾集
SELECT ename, sal, dname, emp.deptno FROM emp, dept
WHERE emp.deptno = dept.deptno;
-- ?如何顯示部門號為10的部門名、員工名和工資
SELECT ename, sal, dname, emp.deptno FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10;
-- ?顯示各個員工的姓名，工資，以及工資的級別
-- 思路 : 姓名工資來自 emp, 工資級別來自salgrade
SELECT ename, sal, grade FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal;
-- 練習: 顯示雇員名、工資及所在部門名字，並按部門排序[降序]
SELECT ename, sal, dname FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY emp.deptno DESC
```
## 自連接
```sql=
-- 多表查詢的自連接
-- 思考題: 顯示公司員工和他上級的名字
-- 分析: 員工名字和上級名字都在emp，兩者通過mgr & empno連接
-- 老韓小結:
-- 自連接特點: 1. 把同一張表當作兩張表使用
-- 2. 需要給表取別名 [表明 表別名]
-- 3. 列名不明確，可以指定列的別名，列名AS列的別名
SELECT worker.ename AS '職員名', boss.ename AS '長官名'
FROM emp worker, emp boss -- 169 = 13 * 13
WHERE worker.mgr = boss.empno;
```
## 子查詢
```sql=
-- 子查詢的演示
-- ?如何顯示與SMITH同一部門的所有員工
/*
1. 先查詢到SMITH的部門編號
2. 把上面的 SELECT語句當作一個子查詢
*/
SELECT deptno FROM emp
WHERE ename = 'SMITH';
SELECT * FROM emp
WHERE deptno = (SELECT deptno FROM emp
WHERE ename = 'SMITH');
-- 練習: 查詢和部門10工作相同的雇員的名字、崗位、工資
-- 、部門號，但不包含10號部門本身
/*
1. 查詢到10號部門有哪些工作
2. 把上面查詢的結果當作子查詢使用
*/
SELECT DISTINCT job FROM emp WHERE deptno = 10;
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10)
AND deptno != 10;
```
```sql=
-- 查詢 ecshop各類價格最高的商品
-- 查詢商品表
-- 先得到各個類別中，價格最高的商品 max + GROUP BY cat_id，當作臨時表
-- 把子查詢當作一張臨時表可以解決很多很多複雜的查詢
SELECT cat_id, MAX(shop_price) FROM ecs_shop GROUP BY cat_id;
SELECT goods_id, temp.cat_id, goods_name, shop_price
FROM (
SELECT cat_id, MAX(shop_price) AS max_price
FROM ecs_shop
GROUP BY cat_id
) temp, ecs_goods
WHERE temp.cat_id = ecs_goods.cat_id
AND temp.max_price = ecs_goods.shop_price;
```
```sql=
-- all any 使用
-- 顯示工資比部門30的所有員工高的員工姓名、工資和部門號
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal FROM emp WHERE deptno = 30)
-- 也可以這樣寫
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MAX(sal) FROM emp WHERE deptno = 30)
-- ----------------------------------------------
-- 顯示工資比部門30的任一員工高的員工姓名、工資和部門號
SELECT ename, sal, deptno
FROM emp
WHERE sal > ANY(
SELECT sal FROM emp WHERE deptno = 30)
-- 也可以這樣寫
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MIN(sal) FROM emp WHERE deptno = 30)
```
```sql=
-- 多列子查詢
-- 查詢與ALLEN部門和崗位完全相同的所有雇員(並且不包含ALLEN)
-- (字段1, 字段2) = (SELECT 字段1, 字段2 FROM...)
-- 1. SMITH部門和崗位
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN';
-- 2. 把上面查詢當作子查詢，並且使用多列子查詢的語法進行匹配
SELECT * FROM emp
WHERE (deptno, job) = (
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN';
-- 練習
-- 查詢和宋江所有成績完全相同的學生
SELECT chinese, english, math FROM student WHERE name = '宋江';
SELECT * FROM student
WHERE (chinese, english, math) = (
SELECT chinese, english, math
FROM student WHERE `name` = '宋江');
```
### 練習
```sql=
-- 子查詢練習
-- 查找每個部門雇員當中，薪資高於部門平均的人的資料
-- 1. 先得到每個部門的部門號和平均工資
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno;
-- 2. 上面作為子查詢，和emp進行多表查詢
SELECT emp.deptno, ename, sal, temp.avg_sal FROM emp,(
SELECT deptno, AVG(sal) AS avg_sal
FROM emp GROUP BY deptno
) temp
WHERE emp.deptno = temp.deptno
AND emp.sal > temp.avg_sal;
-- 查找每個部門工資最高的人的資料
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
SELECT ename, sal, temp.max_sal, emp.deptno FROM emp, (
SELECT deptno, MAX(sal) AS max_sal
FROM emp GROUP BY deptno) temp
WHERE emp.sal = temp.max_sal
AND emp.deptno = temp.deptno;
-- 查詢每個部門的信息(包括部門名，編號，地址)和人員數量
-- 1. 部門名，編號，地址來自 dept表
-- 2. 各部門人員數量 -> 構建臨時表
SELECT * FROM dept;
SELECT COUNT(*), deptno FROM emp GROUP BY deptno;
SELECT dname, dept.deptno, loc, tmp.per_num AS '人數'
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
-- 還有一種寫法，表.* 表示將該表所有列都顯示出來
-- 在多表查詢中，當多個表的列不重複十，才可以直接寫列名
SELECT tmp.*, dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
```
## 表複製和去重
```sql=
-- 表的複製
-- 為了對某個sql語句進行效率測試，我們需要海量數據時，可以使用此法為表創建海量數據
CREATE TABLE my_tab01
(id INT, `name` VARCHAR(32), sal DOUBLE, job VARCHAR(32), deptno INT);
DESC my_tab01;
SELECT * FROM my_tab01;
-- 演示如何自我複製
-- 1. 先把emp表的紀錄複製到my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
-- 2. 自我複製
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;
-- --------------------------------
-- 如何去重
-- 1. 先創建一張表 my_tab02
-- 2. 讓此表有重複的紀錄
CREATE TABLE my_tab02 LIKE emp; -- 這個語句把 emp表結構，複製到 my_tab02
DESC my_tab02;
INSERT INTO my_tab02
SELECT * FROM emp;
SELECT * FROM my_tab02
-- 3. 考慮去重
/*
思路:
(1) 先創建一張臨時表 my_tmp，該表結構與my_tab02相同
(2) 把my_tmp的紀錄通過distinct關鍵字處理後，把紀錄複製到my_tmp
(3) 清除掉 my_tab02紀錄
(4) 把my_tmp表紀錄複製到 my_tab02
(5) drop掉 臨時表my_tmp
*/
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
DELETE FROM my_tab02;
INSERT INTO my_tab02
SELECT * FROM my_tmp;
DROP TABLE my_tmp;
SELECT * FROM my_tab02;
```
## 合併查詢
# 函數
## 合計/統計 - count
## 合計/統計 - sum
## 合計/統計 - avg
## 合計/統計 - max/min
```sql=
-- 演示統計函數的使用
-- 統計一個班級有多少學生
SELECT COUNT(*) FROM student;
-- 數學成績 > 90
SELECT COUNT(*) FROM student
WHERE math > 90
-- 統計總分 > 250
SELECT COUNT(*) FROM student
WHERE (chinese + english + math) > 250;
-- count(*) 和 count(列) 的區別
-- 解釋 : count(*) 返回滿足條件的紀錄行數
-- count(列) 返回滿足條件的某列有多少個，但是會排除null
CREATE TABLE t15(`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom'),('jack'),('mary'),(NULL);
SELECT * FROM t15;
SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) FROM t15; -- 3
-- -------------------------------------------------
-- 全班數學總分
SELECT SUM(math) FROM student;
-- 全班三科各科總分
SELECT SUM(math) AS math_total_score, SUM(english) AS english_total_score, SUM(chinese) AS chinese_total_score FROM student;
-- 全班三科總分
SELECT SUM(math + english + chinese) AS total_score FROM student;
-- 語文平均
SELECT SUM(chinese)/COUNT(*) FROM student;
-- -------------------------------------------------
-- 數學平均分
SELECT AVG(math) FROM student;
-- 全班總分平均分
SELECT AVG(math+ chinese + english) FROM student;
-- -------------------------------------------------
-- 全班最高/低分
SELECT MAX(math+ chinese + english), MIN(math+ chinese + english) FROM student;
-- 數學最高/低分
SELECT MAX(math), MIN(math) FROM student;
```
## 字符串相關函數
```sql=
-- 演示字符串相關函數的使用，使用emp來表示
-- charset(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,...]) 連接字串，將多個列拼接成一列
SELECT CONCAT(ename,' job is ',job) FROM emp
-- INSTR (string, substring) 返回 substring在string中出現的位置，沒有則返回0
-- DUAL 亞元表，系統表 可做為測試表使用
SELECT INSTR('hanshunping','ping') FROM DUAL;
-- UCASE (string2) 轉換成大寫
SELECT UCASE(ename) FROM emp
-- LCASE (string2) 轉換成小寫
SELECT LCASE(ename) FROM emp
-- LEFT (string2,length) 從string2中的左邊提取length個字符
SELECT LEFT(ename, 2) FROM emp;
-- RIGHT (string2,length) 從string2中的右邊提取length個字符
SELECT RIGHT(ename, 2) FROM emp;
-- LENGTH (string) string 長度，按照字節
SELECT LENGTH(ename) FROM emp;
-- REPLACE (str, search_str, replace_str) 在str中用replace_str替換search_str
-- 如果是manager就替換成經理
SELECT ename, REPLACE(job, 'MANAGER', '經理') FROM emp;
-- STRCMP (string1, string2) 逐字符比較兩字串大小
SELECT STRCMP('hsp','asp') FROM DUAL;
-- SUBSTRING (str, position [,length])
-- 從str的position開始[從1開始計算]，取length個字符。
-- 從 ename 列的第一個位置開始取出兩個字符
SELECT SUBSTRING(ename,1,2) FROM emp;
-- LTRIM (string2) RTRIM (string2) TRIM(string) 去除前端或後端空格
SELECT LTRIM (' hspedu ') FROM DUAL;
SELECT RTRIM (' hspedu ') FROM DUAL;
SELECT TRIM(' hspedu ') FROM DUAL;
SELECT CONCAT(LCASE(LEFT(ename,1)),RIGHT(ename,LENGTH(ename)-1)) FROM emp;
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) FROM emp;
```
## 數學相關函數
```sql=
-- 演示數學相關函數
-- ABS(num)
SELECT ABS(-10) FROM DUAL;
-- BIN(decimal_number) 十進制轉二進制
SELECT BIN(10) FROM DUAL;
-- CEILING(num)
SELECT CEILING(1.1) FROM DUAL;
-- CONV(num, from_base, to_base) 進制轉換
SELECT CONV(8,10,2) FROM DUAL;
-- FLOOR(num)
SELECT FLOOR(1.1) FROM DUAL;
-- FORMAT(number, decimal_places) 保留小數位數 (四捨五入)
SELECT FORMAT(78.123458, 2) FROM DUAL;
-- HEX(DecimalNumber) 轉十六進制
SELECT HEX(15) FROM DUAL;
-- LEAST(number, number2 [,....])求最小值
SELECT LEAST(0,1,-10,4) FROM DUAL;
-- MOD(numerator, denominator) 求餘
SELECT MOD(10,3) FROM DUAL;
-- RAND([seed]) ，返回隨機數範圍為0 <= v <= 1.0
-- seed 不變，產生隨機數也就不變
SELECT RAND(3) FROM DUAL;
```
## 日期
```sql=
-- 日期時間相關函數
-- CURRENT_DATE() 當前日期
SELECT CURRENT_DATE FROM DUAL;
-- CURRENT_TIME() 當前時間
SELECT CURRENT_TIME FROM DUAL;
-- CURRENT_TIMESTAMP() 當前時間戳
SELECT CURRENT_TIMESTAMP FROM DUAL;
-- 創建測試表
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME);
SELECT * FROM mes;
-- 添加一條紀錄
INSERT INTO mes VALUES(1,'北京新聞', CURRENT_TIMESTAMP);
-- NOW() 返回當前日期加時間
INSERT INTO mes VALUES(2,'上海新聞', NOW());
INSERT INTO mes VALUES(3,'廣州新聞', NOW());
-- -----------------------------------------------------
-- 應用實例
-- 顯示所有新聞信息，發布日期只顯示日期不用顯示時間
SELECT id, content, DATE(send_time) FROM mes;
-- 查詢在十分鐘內發布的帖子
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes WHERE DATE_SUB(NOW(),INTERVAL 20 MINUTE) <= send_time;
-- 求2011-11-11和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;
-- 求出你活多少天 [練習]
SELECT DATEDIFF(NOW(),'1999-01-22') FROM DUAL;
-- 如果能活到80歲，你還能活多少天 [練習]
SELECT DATEDIFF('2079-01-22','1999-01-22') FROM DUAL;
-- INTERVAL 80 YEAR => 此INTERVAL可以是年月日時分秒...
-- DATE_ADD('1999-01-22' 可以是data,datetime,TIMESTAMP
SELECT DATEDIFF(DATE_ADD('1999-01-22',INTERVAL 80 YEAR),'1999-01-22') FROM DUAL;
SELECT TIMEDIFF('10:11:11','06:10:10') FROM DUAL;
-- YEAR | MONTH | DAY | DATE (datetime)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT YEAR('2013-11-10') FROM DUAL;
-- UNIX_TIMESTAMP() 返回 1970-1-1 到現在的秒數
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() 可以把一個unix_timestamp秒數，轉成指定格式的日期
-- '%Y-%m-%d' 格式是規定好的，表示年月日
-- 意義: 在開發中可以存放一個數值，然後表示時間，通過FROM_UNIXTIME轉換
SELECT FROM_UNIXTIME(1678205653, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1678205653, '%Y-%m-%d %H:%i:%s') FROM DUAL;
```
## 加密和系統函數
**密碼一定要加密!!!**
```sql=
-- 演示加密函數和系統函數
-- USER() 查詢用戶
-- 可以查看登入到MySQL的有哪些用戶，以及登陸IP
SELECT USER() FROM DUAL; -- 用戶@IP地址
-- DATABASE() 查詢當前使用數據庫名稱
SELECT DATABASE();
-- MD5(str) 為字符串算出一個 MD5 32 的字符串，(用戶密碼)加密
-- root 密碼是hsp -> 加密 md5 -> 在數據庫中存放的是加密後的密碼
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL; -- 32
-- 演示用戶表，存放密碼時，是md5
CREATE TABLE users(
id INT, `name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO users VALUES (100,'韓順平', MD5('hsp'));
SELECT * FROM users;
SELECT * FROM users -- SQL 注入問題
WHERE `name` = '韓順平' AND pwd = MD5('hsp');
-- PASSWORD(str) 加密函數，MySQL 的數據庫用戶密碼就是PASSWORD函數加密
SELECT PASSWORD('hsp') FROM DUAL;
-- SELECT * FROM mysql.user 從原文密碼str計算並返回密碼字符串
-- 通常用於對mysql數據庫的用戶密碼加密
-- mysql.user 表示數據庫.表
SELECT * FROM mysql.user
```
## 流程控制函數
```sql=
# 演示流程控制語句
# IF(expr1,expr2,expr3) 如果 expr1 = TRUE，則返回 expr2 否則返回 expr3
SELECT IF(TRUE,'北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不為空NULL，則返回expr1,否則返回expr2
SELECT IFNULL(NULL,'hspedu') FROM DUAL;
-- SELECT CASE
-- WHEN when_value THEN
-- statement_list
-- ELSE
-- statement_list
-- END CASE;
-- 可以理解成SWITCH
SELECT CASE
WHEN TRUE THEN 'jack'
WHEN FALSE THEN 'tom'
ELSE 'mary' END;
-- 1. 查詢 emp 表，如果comm = null，則顯示0.0
-- 老師說明 : 判斷是否為null要使用is null，判斷不為空 使用 is not
SELECT ename, IF(comm is NULL, 0.0, comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0) FROM emp;
-- 2. 如果emp表的job是CLERK則顯示職員，如果是MANAGER則表示經理
-- 如果是SALESMAN則顯示銷售人員，其他正常顯示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '職員'
WHEN job = 'MANAGER' THEN '經理'
WHEN job = 'SALESMAN' THEN '銷售人員'
ELSE job END) AS 'job'
FROM emp;
```
---
# 外連接
```sql=
-- 外連接
-- 列出部門名稱和這些部門的員工名稱和工作，同時要求顯示出那些沒有員工的部門
-- 過去方法試試
-- 無法顯示全部部門
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname;
-- -----------------------------------
-- 創建 stu
CREATE TABLE stu(
id INT, `name` varchar(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
-- 創建 exam
CREATE TABLE exam(
id INT, grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;
-- 左連接
-- 顯示所有人的成績，如果沒有成績，也要顯示該人的姓名和id號，成績顯示為空
SELECT `name`, stu.id, grade
FROM stu, exam
WHERE stu.id = exam.id;
-- 改成左外連接
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 使用右外連接
-- 顯示所有成績，如果沒有名字匹配，顯示空
-- 即: 右邊的表(exam)和左表沒有匹配的紀錄，也會把右表的紀錄顯示出來
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
-- ----------------------------------------------
-- Exercise
-- 列出部門名稱和這些部門的員工信息(名字和工作)，同時列出那些沒有員工
-- 的部門名
-- 左外連接
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno
ORDER BY dname;
-- 右外連接
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON emp.deptno = dept.deptno
ORDER BY dname;
```
# 約束
## Primary
```sql=
-- 主鍵使用
-- id name email
CREATE TABLE t17(
id INT PRIMARY KEY, -- 表示id列是主鍵
`name` VARCHAR(32),
email VARCHAR(32));
-- 主鍵列值不可以重複
INSERT INTO t17
VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
VALUES(2,'tom','tom@sohu.com');
INSERT INTO t17
VALUES(1,'hsp','hsp@sohu.com'); -- WRONG
-- ------------------------------
-- 主鍵的使用細節討論
-- PRIMARY KEY 不能重複且不能為null
INSERT INTO t17
VALUES(NULL,'hsp','hsp@sohu.com');
-- 一張表最多只能有一個主鍵，但可以是複合主鍵 (id + name)
CREATE TABLE t18(
id INT PRIMARY KEY, -- 表示id列是主鍵
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32)); -- 錯誤的
-- 演示複合主鍵 (id 和 name 做成複合主鍵)
CREATE TABLE t18(
id INT, -- 表示id列是主鍵
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id, `name`));
INSERT INTO t18
VALUES(1,'tom','tom@sohu.com');
INSERT INTO t18
VALUES(1,'jack','jack@sohu.com');
SELECT * FROM t18;
INSERT INTO t18
VALUES(1,'tom','XX@sohu.com'); -- WRONG
-- 主鍵指定的方式有兩種
-- 1. 直接在字段名後指定: 字段名 primary key
-- 2. 在表定義最後寫primary key(列名);
CREATE TABLE t19(
id INT, -- 表示id列是主鍵
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32));
CREATE TABLE t20(
id INT, -- 表示id列是主鍵
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (`name`));
-- 使用desc表名，可以看到primary key的情況
DESC t20;
```
## Unique
```sql=
-- unique的使用
CREATE TABLE t21
(id INT UNIQUE, -- 表示id 列不可以重複
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t21
VALUES (1, 'jack', 'jack@sohu.com');
INSERT INTO t21
VALUES (1, 'tom', 'tom@sohu.com');
-- unique 細節
-- 1. 如果沒有指定not null，則unique 字段可以有多個 null
-- 如果一個字段，是unique not null 使用效果類似primary key
INSERT INTO t21
VALUES (NULL, 'tom', 'tom@sohu.com');
INSERT INTO t21
VALUES (NULL, 'tom', 'tom@sohu.com'); -- OK
-- 2. 一張表可以有多個unique字段
CREATE TABLE t22
(id INT UNIQUE, -- 表示id 列不可以重複
`name` VARCHAR(32) UNIQUE, -- 表示name 列不可以重複
email VARCHAR(32));
DESC t22;
```
## Foreign Key
```sql=
-- 外鍵演示
-- 創鍵主表 my_class
CREATE TABLE my_class (id INT PRIMARY KEY, -- 班級編號
`name` VARCHAR(32) NOT NULL DEFAULT '');
-- 創建從表 my_stu
CREATE TABLE my_stu (id INT PRIMARY KEY, -- 學生編號
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT, -- 學生所在班級編號
-- 下面指定外鍵關係
FOREIGN KEY (class_id) REFERENCES my_class(id));
-- 測試數據
INSERT INTO my_class
VALUES (100,'java'),(200,'web');
INSERT INTO my_class
VALUES (300,'php');
SELECT * FROM my_class;
INSERT INTO my_stu
VALUES (1,'tom',100);
INSERT INTO my_stu
VALUES (2,'jack',200);
INSERT INTO my_stu
VALUES (3,'hsp',300);
INSERT INTO my_stu
VALUES (4,'mary',400); -- 這裡會失敗，因為400班級不存在
INSERT INTO my_stu
VALUES (5,'jerry',NULL); -- PASS，外鍵沒有寫NOT NULL
SELECT * FROM my_stu;
-- 一旦建立主外鍵關係，數據就不能任意刪除
DELETE FROM my_class
WHERE id = 100;
```
## Check
```sql=
-- 演示 check 的使用
-- mysql5.7目前還不支持check，只做語法校驗，但不會生效
-- 學習 oracle, sql server這兩個數據庫是真的生效
-- 測試
CREATE TABLE t23(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN ('man','woman')),
sal DOUBLE CHECK (sal > 1000 AND sal < 2000));
-- 添加數據
INSERT INTO t23
VALUES (1,'jack','mid',1); -- PASS
SELECT * FROM t23;
```
## 練習
- MyVersion
```sql=
CREATE DATABASE shop_db;
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32),
unitprice DOUBLE CHECK (unitprice BETWEEN 1.0 AND 9999.99),
category INT,
provider VARCHAR(32));
CREATE TABLE customer(
customer_id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
address VARCHAR(32),
email VARCHAR(32) UNIQUE,
sex CHAR(1) CHECK (sex in ('男','女')),
card_id VARCHAR(10));
CREATE TABLE purchase(
order_id INT,
customer_id INT,
goods_id INT,
nums INT,
PRIMARY KEY(order_id, customer_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
```
- hsp
```sql=
CREATE DATABASE shop_db;
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice BETWEEN 1.0 AND 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT '');
CREATE TABLE customer(
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL,
sex ENUM('男','女') NOT NULL, -- 這裡老師使用了枚舉類型，是生效的
card_id CHAR(18) UNIQUE);
CREATE TABLE purchase(
order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
```
## 自增長
```sql=
-- 演示自增長使用
CREATE TABLE t24(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT '');
DESC t24;
-- 測試自增長
INSERT INTO t24
VALUES (NULL, 'jack@qq.com','jack');
INSERT INTO t24
VALUES (NULL, 'tom@qq.com','tom');
INSERT INTO t24
(email, `name`) VALUES ('hsp@qq.com','hsp');
SELECT * FROM t24;
-- 修改默認的自增長初始值
CREATE TABLE t25(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT '');
ALTER TABLE t25 AUTO_INCREMENT = 100;
INSERT INTO t25
VALUES (NULL, 'jack@qq.com','jack'); -- 100
INSERT INTO t25
VALUES (NULL, 'tom@qq.com','tom'); -- 101
INSERT INTO t25
VALUES (666, 'tom@qq.com','tom'); -- 666
INSERT INTO t25
VALUES (NULL, 'tom@qq.com','tom'); -- 667
SELECT * FROM t25;
```
# 索引
---
---
```sql=
-- 演示mysql索引的使用
-- 創建索引
CREATE TABLE t25 (
id INT,
`name` VARCHAR(32));
-- 查詢表是否有索引
SHOW INDEX FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引
CREATE INDEX id_index ON t25 (id);
-- 如何選擇
-- 1. 如果某列的值不重複，優先考慮unique索引
-- 否則使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)
-- 添加主鍵索引
-- 1. 直接在創建表時加上PRIMARY KEY
-- 2.
CREATE TABLE t26 (
id INT,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)
SHOW INDEX FROM t26;
-- 刪除索引
DROP INDEX id_index ON t25
-- 刪除主鍵索引
ALTER TABLE t26 DROP PRIMARY KEY
-- 修改索引，先刪除，在添加新的索引
-- 查詢索引
-- 1.
SHOW INDEX FROM t25;
-- 2.
SHOW INDEXES FROM t25;
-- 3.
SHOW KEYS FROM t25;
-- 4. (這個方是比較沒那麼多資訊)
DESC t25
```
## 練習
```sql=
CREATE TABLE order1(
id INT PRIMARY KEY,
goods VARCHAR(32),
customer VARCHAR(32),
nums INT);
SHOW INDEX FROM order1;
CREATE TABLE order2(
id INT,
goods VARCHAR(32),
customer VARCHAR(32),
nums INT);
ALTER TABLE order2 ADD PRIMARY KEY (id);
SHOW INDEX FROM order2;
-- ------------------------------------
CREATE TABLE menu1(
id INT PRIMARY KEY,
`name` VARCHAR(32),
chef VARCHAR(32),
order_id INT UNIQUE,
price DOUBLE);
SHOW INDEX FROM menu1;
CREATE TABLE menu2(
id INT PRIMARY KEY,
`name` VARCHAR(32),
chef VARCHAR(32),
order_id INT,
price DOUBLE);
CREATE UNIQUE INDEX order_id_index ON menu2(order_id);
SHOW INDEX FROM menu2;
-- -----------------------------
CREATE TABLE sportman1(
id INT PRIMARY KEY,
`name` VARCHAR(32),
strength VARCHAR(32));
CREATE INDEX name_idx ON sportman1(`name`);
SHOW INDEX FROM sportman1;
CREATE TABLE sportman2(
id INT PRIMARY KEY,
`name` VARCHAR(32),
strength VARCHAR(32));
ALTER TABLE sportman2 ADD INDEX name_idx(`name`);
SHOW INDEX FROM sportman2;
```
## 小結
# 事務
```sql=
-- 事務的幾個重要概念和具體操作
-- 看一個圖[看示意圖]
-- 演示
CREATE TABLE t27(
id INT,
`name` VARCHAR(32));
-- 2. 開始事務
START TRANSACTION;
-- 3. 設置保存點
SAVEPOINT a;
-- 4. 執行dml操作
INSERT INTO t27 VALUES (100, 'tom');
SELECT * FROM t27;
SAVEPOINT b;
INSERT INTO t27 VALUES (200, 'jack');
SELECT * FROM t27;
-- 回退到b
ROLLBACK TO b;
-- 回退到a
ROLLBACK TO a;
-- 如果這樣，表示直接回退到事務開始的狀態
ROLLBACK
-- 注意這邊不允許先退到a再到b
-- 提交
COMMIT;
```
## 細節
```sql=
-- 討論事務細節
-- 1. 如果不開始事務，默認情況下，dml操作是自動提交的，不能回滾
INSERT INTO t27 VALUES (300, 'milan'); -- 自動提交commit
SELECT * FROM t27;
-- 2. 如果開始一個事務，你沒有創建保存點，你可以執行rollback
-- 默認就是回到你事務開始的狀態
START TRANSACTION;
INSERT INTO t27 VALUES (400, 'king');
INSERT INTO t27 VALUES (500, 'scott');
ROLLBACK; -- 表示直接回到事務開始的狀態
COMMIT;
-- 3. 可以在事務尚未提交前，創建多個保存點
-- 4. 可以在事務尚未提交前，選擇回退到保存點
-- 5. mysql 的事務機制需要innoDB的存儲引擎才可以使用，myisam不好使
-- 6. 開始一個事務 START TRANSACTION 或者 set autocommit = off;
SET autocommit = off;
```
## 隔離級別
- 髒讀是指可以讀取到他人==尚未提交==的事務(針對update, insert, delete)
- 不可重複讀和幻讀都是針對==已提交==的事務，但是分別是(針對update, delete)和(insert)
## 設置
```sql=
-- 演示mysql的事務隔離級別
-- 1. 開兩個mysql控制台
-- 2. 查看mysql的隔離級別
SELECT @@tx_isolation
/*
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
*/
-- 3. 把其中一個控制台的隔離級別設置成 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 4. 創建表
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT);
-- 查看當前會話隔離級別
SELECT @@tx_isolation
-- 查看系統當前隔離級別
SELECT @@global.tx_isolation
-- 設置當前會話隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 設置系統當前隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
```
## ACID 特性
## 練習
- B (READ UNCOMMITTED)
- 可讀到尚未提交的新增、修改、刪除 (髒讀)
- 
- 
- 因為尚未提交就可以看到更動，因此提交後也一定能看到更動(不可重複讀、幻讀)
- C (READ COMMITTED)
- 沒有讀到未提交的新增、修改、刪除(沒有髒讀)
- 
- 
- 
- 提交後產生影響(有不可重複讀、幻讀)
# 存儲引擎
```sql=
-- 表類型和存儲引擎
-- 查看所有的存儲引擎
SHOW ENGINES;
-- innoDB，是前面使用的
-- 1. 支持事務 2. 支持外鍵 3. 支持行級鎖
-- -----------------------------------------
-- myisam
CREATE TABLE t28(
id INT,
name VARCHAR(32)) ENGINE MYISAM;
-- 1. 添加速度快 2. 不支持外鍵和事務 3. 支持表級鎖
START TRANSACTION;
SAVEPOINT t1;
INSERT INTO t28 VALUES (1,'jack');
SELECT * FROM t28;
ROLLBACK TO t1; -- 沒有生效
-- -----------------------------------------
-- memory
-- 1. 數據存儲在內存中[關閉了mysql服務，數據丟失，但表結構還在]
-- 2. 執行速度很快(沒有IO讀寫) 3. 默認支持索引(hash表)
CREATE TABLE t29(
id INT,
name VARCHAR(32)) ENGINE MEMORY;
INSERT INTO t29
VALUES (1,'tom'),(2,'jack'),(3,'hsp');
SELECT * FROM t29;
-- 指令修改存儲引擎
ALTER TABLE t29 ENGINE = INNODB;
```
# 視圖
```sql=
-- 視圖的使用
-- 創建一個視圖 emp_view01，只能查詢表的(empno, ename, job, deptno) 信息
-- 創建視圖
CREATE VIEW emp_view01
AS SELECT empno, ename, job, deptno FROM emp;
-- 查看視圖
DESC emp_view01;
SELECT * FROM emp_view01;
-- 查看創建視圖的指令
SHOW CREATE VIEW emp_view01;
-- 刪除視圖
DROP VIEW emp_view01;
```
- 1.是指視圖不會產生資料庫相關文件，因為他的資料來自於其他資料庫
- 2.改基表或視圖都會影響到對方
## 應用實例
```sql=
-- 視圖的課堂練習
/*
分析: 使用三表聯合查詢，得到結果
13 * 4 *5 = 260
將得到的結果構建成視圖
*/
CREATE VIEW emp_view03
AS
SELECT empno, ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.deptno = dept.deptno AND
(sal BETWEEN losal and hisal);
DESC emp_view03;
SELECT * FROM emp_view03;
```
# 管理
```sql=
-- Mysql 用戶的管理
-- 原因: 當我們作項目開發時，可以根據不同的開發人員，賦給他相應的操作權限
-- 所以，Mysql數據庫管理人員(root)，根據需要創建不同的用戶，賦給相應的權限，
-- 供開發人員使用
-- 1. 創建新用戶
-- 解讀
-- (1)'hsp_edu'@'localhost' 表示用戶的完整信息，'hsp_edu' 用戶名 'localhost' 登陸的IP
-- (2) 123456 密碼，但是注意 存放到 mysql.user表時，是password('123456') 加密後的密碼
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'
SELECT `host`, `user`, authentication_string FROM mysql.user;
-- 2. 刪除用戶
DROP USER 'hsp_edu'@'localhost';
-- 3. 登入
-- -----------------------------------------------
-- 修改自己的密碼
SET PASSWORD = PASSWORD('abcdef');
-- 修改他人密碼需要權限
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456');
```
---
```sql=
-- 演示用戶權限的管理
-- 創建用戶
CREATE USER 'jyunjie'@'localhost' IDENTIFIED BY '123';
-- 使用root用戶創建 testdb, 表 news
CREATE DATABASE testdb;
CREATE TABLE news(
id INT,
content VARCHAR(32));
INSERT INTO news VALUES(100, '北京新聞');
SELECT * FROM news;
-- 分配權限
GRANT SELECT, INSERT
ON testdb.news
TO 'jyunjie'@'localhost';
-- 增加update權限
GRANT UPDATE
ON testdb.news
TO 'jyunjie'@'localhost';
-- 修改密碼
SET PASSWORD FOR 'jyunjie'@'localhost' = PASSWORD('abc');
-- 回收權限
REVOKE SELECT, INSERT, UPDATE ON testdb.news FROM 'jyunjie'@'localhost';
REVOKE ALL ON testdb.news FROM 'jyunjie'@'localhost';
-- 刪除用戶
DROP USER 'jyunjie'@'localhost';
```
## 細節
```sql=
-- 說明用戶管理細節
-- 在創建用戶的時候，如果不指定Host，則為 % ，%表示所有IP都有連接權限
CREATE USER jack;
SELECT `host`, `user` FROM mysql.user;
-- 你也可以這樣指定
-- create user 'xxx'@'192.168.1.%'，表示xxx用戶在192.168.1.% 的IP可以登入mysql
CREATE USER 'smith'@'192.168.1.%';
-- 在刪除用戶的時候，如果host不是%，需要明確的指定 '用戶'@'host值'
DROP USER jack; -- 默認就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%';
```
# 本章作業
## 1
1. D
(A,B) 對於別稱，如果只有單個字可以不用AS
(C) 對於別稱，如果有多字需要""
(D) 可以不用AS，但必須""
2. B
重點 : null 用 IS NOT
3. C
## 2
```sql=
DESC dept;
DESC emp;
```
## 3
```sql=
-- 3.1
SELECT dname FROM dept;
-- 3.2
-- 重點: 數字與 null 相加會變 null
SELECT ename, 13*(sal+IFNULL(comm,0)) AS '年收入' FROM emp;
```
## 4
```sql=
-- 4.1
SELECT ename, sal FROM emp WHERE sal > 2850;
-- 4.2
SELECT ename, sal FROM emp WHERE sal > 2850 OR sal < 1500;
SELECT ename, sal FROM emp WHERE NOT (sal >= 1500 sal <= 2850);
-- 4.3
SELECT ename, deptno FROM emp WHERE empno = 7566;
-- 4.4
SELECT ename, sal FROM emp WHERE deptno IN (10,30) AND sal > 1500;
-- 4.5
SELECT ename, job FROM emp WHERE mgr IS NULL;
```
## 5
```sql=
-- 5.1
SELECT ename, job, hiredate FROM emp
WHERE hiredate BETWEEN '1991-02-01' AND '1991-05-01' ORDER BY hiredate;
-- 5.2
SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC;
```
## 6
```sql=
-- 6.1
SELECT * FROM emp WHERE deptno = 30;
-- 6.2
SELECT ename, empno, deptno FROM emp WHERE job = 'CLERK';
-- 6.3
SELECT * FROM emp WHERE comm > sal;
SELECT * FROM emp WHERE IFNULL(comm, 0) > sal;
-- 6.4
SELECT * FROM emp WHERE comm > sal*0.6;
-- 6.5
SELECT * FROM emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK');
-- 6.6
SELECT * FROM emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK')
OR (NOT (job IN ('MANAGER','CLERK')) AND sal >=2000);
SELECT * FROM emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK')
OR (job != 'MANAGER' AND job != 'CLERK' AND sal >=2000);
-- 6.7
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
-- 6.8
SELECT * FROM emp WHERE comm IS NULL OR IFNULL(comm,0) < 100;
-- 6.9 (hsp)
-- 老韓提示: LAST_DAY(date)，可以返回該日期所在月份的最後一天
SELECT LAST_DAY(NOW()) - 2;
SELECT * FROM emp WHERE LAST_DAY(hiredate) - 2 = hiredate;
SELECT * FROM emp WHERE hiredate = (SUBDATE(LAST_DAY(hiredate), INTERVAL 2 DAY));
-- 6.10 (hsp)
SELECT * FROM emp WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW();
-- 6.11
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTR(ename, 2)) FROM emp;
-- 6.12
SELECT ename FROM emp WHERE LENGTH(ename) = 5;
```
```sql=
-- 6.13
SELECT ename FROM emp WHERE INSTR(ename,'R') = 0;
SELECT ename FROM emp WHERE ename NOT LIKE '%R%';
-- 6.14
SELECT LEFT(ename,3) FROM emp;
-- 6.15
SELECT REPLACE(ename,'A','a') FROM emp;
-- 6.16
SELECT ename, hiredate FROM emp WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) <= NOW();
-- 6.17
SELECT * FROM emp ORDER BY ename;
-- 6.18
SELECT ename, hiredate FROM emp ORDER BY hiredate;
-- 6.19
SELECT ename, job, sal FROM emp ORDER BY job DESC, sal;
-- 6.20
SELECT ename, YEAR(hiredate), MONTH(hiredate) FROM emp
ORDER BY MONTH(hiredate), YEAR(hiredate);
SELECT ename, CONCAT(YEAR(hiredate),'-', MONTH(hiredate)) FROM emp
ORDER BY MONTH(hiredate), YEAR(hiredate);
-- 6.21 (hsp) -> 一天平均薪資
SELECT ename, FLOOR(sal/30) FROM emp; -- 無條件捨去
SELECT ename, ROUND(sal/30) FROM emp; -- 四捨五入
-- 6.22
SELECT * FROM emp WHERE MONTH(hiredate) = 2;
-- 6.23
SELECT ename, DATEDIFF(NOW(), hiredate) FROM emp;
-- 6.24
SELECT ename FROM emp WHERE INSTR(ename,'A') != 0;
SELECT ename FROM emp WHERE ename LIKE'%A%';
-- 6.25 (hsp)
SELECT ename, FLOOR(DATEDIFF(NOW(), hiredate)/365) AS '工作年',
FLOOR(DATEDIFF(NOW(), hiredate)%365/31) AS '工作月',
DATEDIFF(NOW(), hiredate) % 31 AS '工作日'
FROM emp;
```
## 7
```sql=
-- 7.1
SELECT DISTINCT emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno;
SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno HAVING c >= 1
-- 7.2
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');
-- 7.3
SELECT * FROM emp worker, emp boss
WHERE worker.mgr = boss.empno
AND worker.hiredate > boss.hiredate;
-- 7.4
SELECT dname, emp.* FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;
-- 7.5
SELECT dname, ename FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno;
-- 7.6
SELECT job FROM emp GROUP BY job HAVING MIN(sal) > 1500;
-- 7.7
SELECT ename FROM emp, dept WHERE emp.deptno = dept.deptno AND dname = 'SALES';
-- 7.8
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) from emp);
```
7.13沒做得很好
```sql=
-- 7.9
SELECT * FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT';
-- 7.10
SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
-- 7.11
SELECT deptno, COUNT(*), AVG(sal), AVG(DATEDIFF(NOW(),hiredate))
FROM emp GROUP BY deptno;
SELECT deptno, COUNT(*), AVG(sal), FORMAT(AVG(DATEDIFF(NOW(),hiredate)/365),2)
FROM emp GROUP BY deptno;
-- 7.12
SELECT ename, dname, sal FROM emp, dept WHERE emp.deptno = dept.deptno;
-- 7.13
SELECT dname, loc, COUNT(*) FROM emp, dept
WHERE emp.deptno = dept.deptno GROUP BY dname, loc;
-- 1. 先得到各個部門的人數，把下表當作臨時表和dept表聯合查詢
SELECT COUNT(*) AS c, deptno
FROM emp GROUP BY deptno;
-- 2.
SELECT dept.*, tmp.c
FROM dept, (
SELECT COUNT(*) AS c, deptno
FROM emp GROUP BY deptno) tmp
WHERE dept.deptno = tmp.deptno;
-- 7.14
SELECT job, MIN(sal) FROM emp GROUP BY job;
-- 7.15
SELECT job, MIN(sal) FROM emp WHERE job = 'MANAGER';
-- 7.16
SELECT ename, (sal + IFNULL(comm,0))*12 year_sal FROM emp ORDER BY year_sal;
```
## 8
```sql=
-- 創建表 系DEPARTMENT (departmentid, deptname)
CREATE TABLE DEPARTMENT (
departmentid VARCHAR(32) PRIMARY KEY,
deptname VARCHAR(32) UNIQUE NOT NULL);
-- CLASS (classid, subject, deptname, enrolltime, num)
CREATE TABLE `class`(
classid INT PRIMARY KEY,
`subject` VARCHAR(32) NOT NULL DEFAULT '',
deptname VARCHAR(32), -- 外鍵字段，在表定義後指定
enrolltime INT NOT NULL DEFAULT 2000,
num INT NOT NULL DEFAULT 0,
FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname));
-- STUDENT (studentid, name, age, classid)
CREATE TABLE hsp_student(
studentid INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
classid INT, -- 外鍵
FOREIGN KEY (classid) REFERENCES `class`(classid));
-- 添加測試數據
INSERT INTO department VALUES ('001','MATH');
INSERT INTO department VALUES ('002','COMPUTER');
INSERT INTO department VALUES ('003','CHEMISTRY');
INSERT INTO department VALUES ('004','CHINESE');
INSERT INTO department VALUES ('005','ECONOMICS');
INSERT INTO class VALUES (101,'software','COMPUTER',1995,20);
INSERT INTO class VALUES (102,'micro electronic','COMPUTER',1996,30);
INSERT INTO class VALUES (111,'無機化學','CHEMISTRY',1995,29);
INSERT INTO class VALUES (112,'高分子化學','CHEMISTRY',1996,25);
INSERT INTO class VALUES (121,'statistics','MATH',1995,20);
INSERT INTO class VALUES (131,'modern language','CHINESE',1996,20);
INSERT INTO class VALUES (141,'國際貿易','ECONOMICS',1997,30);
INSERT INTO class VALUES (142,'國際金融','ECONOMICS',1996,14);
INSERT INTO hsp_student VALUES (8101,'張三', 18,101);
INSERT INTO hsp_student VALUES (8102,'錢四', 16,121);
INSERT INTO hsp_student VALUES (8103,'玉玲', 17,131);
INSERT INTO hsp_student VALUES (8105,'李飛', 19,102);
INSERT INTO hsp_student VALUES (8109,'趙四', 18,141);
INSERT INTO hsp_student VALUES (8110,'李可', 20,142);
INSERT INTO hsp_student VALUES (8201,'張飛', 18,111);
INSERT INTO hsp_student VALUES (8302,'周瑜', 16,112);
INSERT INTO hsp_student VALUES (8203,'王亮', 17,111);
INSERT INTO hsp_student VALUES (8305,'董卓', 19,102);
INSERT INTO hsp_student VALUES (8409,'趙龍', 18,101);
SELECT * FROM department;
SELECT * FROM class;
SELECT * FROM hsp_student;
-- 3.1
SELECT * FROM hsp_student WHERE name LIKE '李%';
-- 3.2
SELECT deptname, COUNT(`subject`)
FROM class GROUP BY deptname HAVING COUNT(`subject`) > 1;
SELECT COUNT(*) AS nums, deptname
FROM class GROUP BY deptname HAVING nums > 1;
-- 3.3
SELECT deptname, SUM(num) total FROM class GROUP BY deptname HAVING total >= 30;
SELECT departmentid, tmp.* FROM department,(
SELECT deptname, SUM(num) total FROM class
GROUP BY deptname HAVING total >= 30) tmp
WHERE tmp.deptname = department.deptname;
-- 4.
INSERT INTO department VALUES ('006', 'PHYSICS');
-- 5.
-- 分析: 1. 張三所在班級人數減一 2. 將張三從學生表刪除 3. 需要使用事務控制
START TRANSACTION;
UPDATE class SET num = num - 1
WHERE classid = (SELECT classid FROM hsp_student WHERE `name` = '張三');
DELETE FROM hsp_student WHERE `name` = '張三';
COMMIT;
```
# 補充
## 遞歸
```sql
WITH RECURSIVE pattern AS (
SELECT 1 AS level
UNION ALL
SELECT level + 1 FROM pattern WHERE level < 20
)
SELECT REPEAT('* ', level) FROM pattern;
```
## 設定變數
```sql
SET @ct := (SELECT COUNT(1) FROM STATION);
SELECT ROUND(AVG(LAT_N), 4)
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY LAT_N) rid,
LAT_N
FROM station
) AS tmp
WHERE tmp.rid BETWEEN @ct/2.0 AND @ct/2.0 + 1;
```
Sign in with Wallet
Connect another wallet