1、DB:數據庫(database)，儲存了一系列有組織的數據
2、DBMS:數據庫管理系統(database management system)，又稱爲數據庫軟體（產品），用於管理DB中的數據
3、SQL:結構化查詢語言(structure query language)，用於和DBMS通訊的語言
1、將數據放到表中，表再放到庫中
2、一個數據庫中可以有多個表，每個表都有一個的名字，用來標識自己。表名具有唯一性
3、表具有一些特性，這些特性定義了數據在表中如何儲存，類似python中類的設計
4、表由列組成，我們也稱爲欄位。所有表都是由一個或多個列組成的，每一列類似python中的屬性
5、表中的數據是按行儲存的，每一行類似於python中的物件
1. 下載壓縮包mysql-5.7.26-winx64.zip
2. 解壓並進入C:\MySQL\mysql-5.7.26-winx64
3. 在第2步的目錄下，建立data資料夾和my.ini檔案
4. 編輯my.ini檔案
[mysqld]
character-set-server=utf8
bind-address = 0.0.0.0
port = 3306
basedir=C:\MySQL\mysql-5.7.26-winx64
datadir=C:\MySQL\mysql-5.7.26-winx64\data
max_connections=2000
default-storage-engine=INNODB
skip-grant-tables
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[client]
default-character-set=utf8
5. 在cmd中，進入到bin目錄
C:\Users\lenovo>cd C:\MySQL\mysql-5.7.26-winx64\bin
6. 執行安裝命令mysqld --install
(解除安裝命令mysqld --remove)
如果遇到報錯Install/Remove of the Service Denied!
解決方案，使用管理員開啓cmd，win+X+A就可以以管理員身份開啓cmd
執行成功如下
C:\MySQL\mysql-5.7.26-winx64\bin>mysqld --install
Service successfully installed.
7. mysql數據庫初始化，執行mysqld --initialize --user=root --console
在最後一行可以看到隨機密碼
8. 啓動mysql服務，net start mysql
9. 使用隨機密碼登錄mysql，執行mysql -u -root -p
10. 登錄成功後修改密碼，執行use mysql;
11. 執行update user set authentication_string=PASSWORD("Mall@960610") where user='root';
12. 設定mysql的環境變數
在系統變數下，新建變數名MYSQL_HOME，變數值C:\MySQL\mysql-5.7.26-winx64
在path中編輯新建%MYSQL_HOME%\bin
13. 使用自己設定的密碼登錄mysql，執行mysql -u -root -p
必須以管理員身份開啓cmd
# 啓動mysql服務
net start 服務名
# 停止mysql服務
net stop 服務名
mysql 【-h主機名 -P埠號】 -u使用者名稱 -p密碼
mysql -h localhost -P 3306 -u -root -p
mysql -u -root -p
exit 或 ctrl+C
1. 檢視當前所有的數據庫
show databases;
2. 開啓指定的庫
use 庫名;
3. 檢視當前庫的所有表
show tables;
4. 檢視其它庫的所有表
show tables from 庫名;
5. 檢視當前所在的庫
select database();
6. 檢視錶結構
desc 表名;
7. 檢視伺服器的版本
方式一：登錄到mysql伺服器端
select version();
方式二：沒有登錄到mysql伺服器端
mysql --version
或
mysql --V
1. 不區分大小寫，但建議關鍵字大寫，表名、列名小寫
2. 每條命令最好用分號結尾
3. 每條命令根據需要，可以進行縮排或換行
4. 註釋
單行註釋：#註釋文字
單行註釋：-- 註釋文字
多行註釋：/* 註釋文字 */
DQL（Data Query Language）：數據查詢語言
主要涉及：select
/*
Navicat MySQL Data Transfer
Source Server : mysql5.7
Source Server Version : 50726
Source Host : localhost:3306
Source Database : myemployees
Target Server Type : MYSQL
Target Server Version : 50726
File Encoding : 65001
Date: 2020-07-05 18:46:43
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT,
`department_name` varchar(3) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`location_id` int(4) DEFAULT NULL,
PRIMARY KEY (`department_id`),
KEY `loc_id_fk` (`location_id`),
CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(25) DEFAULT NULL,
`email` varchar(25) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`job_id` varchar(10) DEFAULT NULL,
`salary` double(10,2) DEFAULT NULL,
`commission_pct` double(4,2) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`department_id` int(4) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
PRIMARY KEY (`employee_id`),
KEY `dept_id_fk` (`department_id`),
KEY `job_id_fk` (`job_id`),
CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- Table structure for jobs
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL,
`job_title` varchar(35) DEFAULT NULL,
`min_salary` int(6) DEFAULT NULL,
`max_salary` int(6) DEFAULT NULL,
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT,
`street_address` varchar(40) DEFAULT NULL,
`postal_code` varchar(12) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state_province` varchar(25) DEFAULT NULL,
`country_id` varchar(2) DEFAULT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
CREATE TABLE job_grades (
grade_level VARCHAR (3),
lowest_sal INT,
highest_sal INT
);
INSERT INTO job_grades
VALUES
('A', 1000, 2999);
INSERT INTO job_grades
VALUES
('B', 3000, 5999);
INSERT INTO job_grades
VALUES
('C', 6000, 9999);
INSERT INTO job_grades
VALUES
('D', 10000, 14999);
INSERT INTO job_grades
VALUES
('E', 15000, 24999);
INSERT INTO job_grades
VALUES
('F', 25000, 40000);
/*
Navicat MySQL Data Transfer
Source Server : mysql5.7
Source Server Version : 50726
Source Host : localhost:3306
Source Database : girls
Target Server Type : MYSQL
Target Server Version : 50726
File Encoding : 65001
Date: 2020-07-08 20:02:08
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for beauty
-- ----------------------------
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for boys
-- ----------------------------
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
employees表 員工表
employee_id 員工編號
first_name 名
last_name 姓
email 郵箱
phone_number 電話號碼
job_id 工種編號
salary 月薪
commission_pct 獎金率
manager_id 上級領導的員工編號
department_id 部門編號
hiredate 入職日期
departments表 部門表
department_id 部門編號
department_name 部門名稱
manager_id 部門領導的員工編號
location_id 位置編號
locations表 位置表
location_id 位置編號
street_address 街道
postal_code 郵編
city 城市
state_province 州/省
country_id 國家編號
jobs表 工種表
job_id 工種編號
job_title 工種名稱
min_salary 最低工資
max_salary 最高工資
select 查詢列表 from 表名;
1. 查詢列表可以是：表中的欄位、常數值、表達式、函數
2. 查詢的結果是一個虛擬的表格
1. 查詢表中的單個欄位
SELECT last_name FROM employees;
2. 查詢表中的多個欄位
SELECT last_name,email,salary FROM employees;
3. 查詢表中的所有欄位
SELECT * FROM employees;
4. 查詢常數值
SELECT 100;
SELECT 'tom';
5. 查詢表達式
SELECT 10*3;
6. 查詢函數
SELECT VERSION();
7. 爲欄位起別名
便於理解；如果要查詢的欄位有重名的情況，使用別名可以區分開來
方式一：SELECT last_name AS 姓 FROM employees;
方式二：SELECT last_name 姓 FROM employees;
查詢salary，顯示結果爲 out put
SELECT salary AS "out put" FROM employees;
8. 去重
查詢員工表中涉及到的所有的部門編號
SELECT DISTINCT department_id FROM employees;
9. +的作用
mysql中的+號：僅僅只有一個功能，運算子
SELECT 100+10; 兩個運算元都爲數值型，則做加法運算
SELECT "100"+10; 只要其中一方爲字元型，檢視將字元型轉換爲數值型，如果轉換成功，則繼續做加法運算；如果轉換失敗，則將字元型轉換爲0
SELECT "tom"+10; 結果爲10
SELECT NULL+10; 只要其中一方爲null，則結果肯定爲null
查詢員工名和姓連線成一個欄位，並顯示爲 姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
select 查詢列表 from 表名 where 篩選條件;
執行順序
from --> where --> select
分類
一、按條件表達式篩選
簡單條件運算子：> < = != <>(推薦) >= <=
二、按邏輯表達式篩選
邏輯運算子：
&& || ! (不推薦)
and or not (推薦)
三、模糊查詢
like, between and, in, is null, is not null
按條件表達式篩選
1. 查詢工資大於12000的員工資訊
SELECT * FROM employees WHERE salary > 12000;
2. 查詢部門編號不等於90號的員工名和部門編號
SELECT last_name,department_id FROM employees WHERE department_id <> 90;
按邏輯表達式篩選
1. 查詢工資在10000到20000之間的員工名、工資、獎金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000
AND salary <= 20000;
2. 查詢部門編號不是在90到110之間，或者工資高於15000的員工資訊
SELECT
*
FROM
employees
WHERE
department_id < 90 OR department_id > 110 OR salary > 15000;
或者
SELECT
*
FROM
employees
WHERE
NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000;
模糊查詢
like, between and, in, is null, is not null
一、like
特點：一般和萬用字元搭配使用，其中%表示任意多個字元；_表示任意單個字元
1. 查詢員工名中包含字元a的員工資訊
SELECT * FROM employees WHERE last_name LIKE '%a%';
2. 查詢員工名中第三個字元爲n，第五個字元爲l的員工名和工資
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';
3. 查詢員工名中第二個字元爲_的員工名
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
也可以自己指定跳脫字元
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
二、between and
可以提高語句的簡潔度
包含臨界值
兩個臨界值不要調換順序，否則結果不一樣
1. 查詢員工編號在100到120之間的員工資訊
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
2. 查詢員工編號在100到120之間的員工資訊
SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 120;
三、in
判斷某欄位的值是否屬於in列表中的某一項
特點：
使用in提高語句簡潔度
in列表的值型別必須一致或相容(可以隱式轉換)
不能使用萬用字元
1. 查詢員工的工種編號是 IT_PROG、AD_VP、AD_PRES中的一個的員工名和工種編號
SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');
四、is null
= 或 <> 不能用於判斷null值
is null 或 is not null 可以判斷null值
1. 查詢沒有獎金的員工名和獎金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
或
SELECT last_name,commission_pct FROM employees WHERE ISNULL(commission_pct);
五、安全等於 <=>
安全等於可以判斷null值，也可以判斷普通值
1. 查詢沒有獎金的員工名和獎金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
2. 查詢工資爲12000的員工資訊
SELECT * FROM employees WHERE salary <=> 12000;
問 SELECT * FROM employees; 和 SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%'; 結果是否一樣，說明理由
答：不一樣，如果判斷的欄位有null值，結果不一樣；如果判斷的欄位沒有null值，結果一樣。
select 查詢列表 from 表 [where 篩選條件] order by 排序列表 [asc|desc]
1. asc表示升序，desc表示降序。預設是升序
2. order by子句中可以支援單個欄位、多個欄位、表達式、函數、別名
3. order by子句一般是放在查詢語句的最後面，limit子句除外
4. 執行順序from --> where --> select --> order by
1. 查詢員工資訊，要求工資從高到低排序
SELECT * FROM employees ORDER BY salary DESC;
2. 查詢部門編號大於等於90的員工資訊，按入職時間的先後 先後進行排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate;
3. 按年薪的高低顯示員工的資訊和年薪【按別名排序】
SELECT
*, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
年薪 DESC;
4. 按姓名的長度顯示員工的姓名和工資【按函數排序】
SELECT
LENGTH(last_name) AS 位元組長度,
last_name,
salary
FROM
employees
ORDER BY
LENGTH(last_name) DESC;
5. 查詢員工資訊，要求先按工資升序，再按員工編號降序【按多個欄位排序】
SELECT * FROM employees ORDER BY salary,employee_id DESC;
select 函數名(實參列表) [from 表]
分類
單行函數
分組函數(統計函數、聚合函數、組函數)
單行函數
一、字元函數
1. length獲取參數值的位元組個數
SHOW VARIABLES LIKE '%char%'; -- 用來檢視用戶端字元集
SELECT LENGTH('lucy'); -- 4
SELECT LENGTH('哈哈'); -- 6
2. concat拼接字串
SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
3. upper、lower
SELECT UPPER('lucy');
SELECT LOWER('LuCy');
-- 將姓變大寫，名變小寫，然後拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
4. substr/substring
注意：索引從 1 開始
# 擷取從指定索引處後面所有字元
-- SUBSTR(str,pos)
SELECT SUBSTR('李莫愁愛上了陸展元',7) AS out_put; -- 陸展元
# 擷取從指定索引處指定字元長度的字元
-- SUBSTR(str,pos,len)
SELECT SUBSTR('李莫愁愛上了陸展元',4,3) AS out_put; -- 愛上了
-- SUBSTR(str FROM pos FOR len)
-- SUBSTR(str FROM pos)
# 案例：姓名中首字元大寫，其他字元小寫，用_拼接並顯示出來
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
)
FROM
employees;
5. instr
# 返回子串第一次出現的索引，如果找不到返回0
-- INSTR(str,substr)
SELECT INSTR('楊不悔愛上了殷六俠','愛上了'); -- 4
6. trim
# 去除兩端空格或指定字元
-- TRIM([remstr FROM] str)
-- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
SELECT LENGTH(TRIM(' 大陝北 ')); -- 9
SELECT TRIM('a' FROM 'aa大a陝北aaaa'); -- 大a陝北
7. lpad
# 用指定的字元實現左填充指定長度，若指定長度小於str長度，則截斷
-- LPAD(str,len,padstr)
SELECT LPAD('哈哈哈',5,'*'); -- **哈哈哈
SELECT LPAD('哈哈哈',2,'*'); -- 哈哈
8. rpad
同上
9. replace
# 替換
-- REPLACE(str,old,new)
SELECT REPLACE('周芷若張無忌愛上了周芷若', '周芷若','趙敏'); -- 趙敏張無忌愛上了趙敏
二、數學函數
1. round
# 四捨五入
# 如果是負數，先把負號去掉，四捨五入後在新增負號
SELECT ROUND(1.65); -- 2
SELECT ROUND(-1.65); -- -2
SELECT ROUND(1.567,2); -- 1.57
SELECT ROUND(-1.567,2); -- -1.57
2. ceil
# 向上取整
# 返回大於等於該參數的最小整數
SELECT CEIL(1.00); -- 1
SELECT CEIL(-1.02); -- -1
3. floor
# 向下取整
# 返回小於等於該參數的最小整數
SELECT FLOOR(1.02); -- 1
SELECT FLOOR(-1.02); -- -2
4. truncate
# 截斷
-- TRUNCATE(X,D)
SELECT TRUNCATE(1.699,2); -- 1.69
SELECT TRUNCATE(-1.699,2); -- -1.69
5. mod
# 取餘
# mod(a,b) 等價於 a - a//b*b
# 被除數的正負決定結果的正負
SELECT MOD(-10,3); -- -1
SELECT MOD(-10,-3); -- -1
SELECT MOD(10,-3); -- 1
SELECT MOD(10,3); -- 1
三、日期函數
1. now
# 返回當前系統日期+時間
SELECT NOW(); -- 2020-07-06 23:35:50
2. curdate
# 返回當前系統日期，不包括時間
SELECT CURDATE(); -- 2020-07-06
3. curtime
# 返回當前時間，不包括日期
SELECT CURTIME(); -- 23:38:57
4. 可以獲取指定的部分，年、月、日、時、分、秒
SELECT YEAR(NOW()); -- 2020
SELECT MONTH(NOW()) 月; -- 7
SELECT MONTHNAME(NOW()); -- July
5. str_to_date
# 將日期格式的字元轉換成指定格式的日期
-- STR_TO_DATE(str,format)
SELECT STR_TO_DATE('1996-6-10','%Y-%c-%d'); -- 1996-06-10
查詢入職日期爲1992-4-3的員工資訊
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
6. date_format
# 將日期轉換成字元
-- DATE_FORMAT(date,format)
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日'); -- 20年07月07日
查詢有獎金的員工名和入職日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') FROM employees WHERE commission_pct IS NOT NULL;
7. datediff
# 計算相差天數，expr1 - expr2
-- DATEDIFF(expr1,expr2)
查詢員工表中的最大入職時間和最小入職時間的相差天數
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
四、其他函數
SELECT VERSION(); -- 5.7.26
SELECT DATABASE(); -- myemployees
SELECT USER(); -- root@
五、流程控制函數
1. if函數
-- IF(expr1,expr2,expr3)
SELECT IF(10>3,'正確','錯誤'); -- 正確
2. case函數
使用一：等值判斷
case 要判斷的欄位或表達式
when 常數1 then 要顯示的值1或語句1
when 常數2 then 要顯示的值2或語句2
......
else 要顯示的值n或語句n;
end
查詢員工的工資，要求：部門號=30，顯示的工資爲1.1倍；部門號=40，顯示的工資爲1.2倍；部門號=50，顯示的工資爲1.3倍；其他部門，顯示的工資爲原工資。
SELECT
salary 原工資,
department_id,
CASE department_id
WHEN 30 THEN
salary * 1.1
WHEN 40 THEN
salary * 1.2
WHEN 50 THEN
salary * 1.3
ELSE
salary
END AS 新工資
FROM
employees;
使用二：區間判斷
case
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
......
else 要顯示的值n或語句n
end
查詢員工的工資的情況，如果工資大於20000，顯示A級別；如果工資大於15000，顯示B級別；如果工資大於10000，顯示C級別；否則顯示D級別
SELECT
salary,
CASE
WHEN salary > 20000 THEN
'A'
WHEN salary > 15000 THEN
'B'
WHEN salary > 10000 THEN
'C'
ELSE
'D'
END AS 工資級別
FROM
employees;
用作統計使用，又稱爲聚合函數或統計函數或組函數。傳入一組值，經過統計處理得到一個值。
分類
sum求和、avg平均值、max最大值、min最小值、count計算個數
特點
1. sum和avg一般用於處理數值型，max、min、count可以處理任何型別
2. 這五個分組函數都忽略null值
3. 可以和distinct搭配使用，實現去重的運算
4. 和分組函數一同查詢的欄位要求是group by後的欄位
1. 簡單的使用
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
2. 參數支援哪些型別
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;
3. 是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
4. 和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
count函數的詳細介紹
SELECT COUNT(salary) FROM employees;
-- 統計行數，只要這一行有一個不爲null，結果就加1
SELECT COUNT(*) FROM employees;
-- count(常數值)，相當於在表中新加了一列這樣的常數值，然後統計常數值的個數
SELECT COUNT(1) FROM employees;
效率
MYISAM儲存引擎下，count(*)的效率高
INNODB儲存引擎下，count(*)和count(1)的效率差不多，比count(欄位)要高
可以使用group by子句將表中的數據分爲若幹組
select 分組函數,列(要求出現在group by的後面)
from 表
[where 篩選條件]
group by 分組的列表
[order by子句]
注意：查詢列表要求是分組函數和group by後出現的欄位
特點：
1. 分組查詢中的篩選條件分爲兩類
數據源 位置 關鍵字
分組前篩選 原始表 group by子句的前面 where
分組後篩選 分組後的結果 group by子句的後面 having
Ⅰ 分組函數做條件肯定是放在having子句中
Ⅱ 能用分組前篩選的，就優先考慮使用分組前篩選
2. group by子句支援單個欄位分組、多個欄位分組(多個欄位之間用逗號隔開，沒有順序要求)、表達式或函數(用的較少)
3. 也可以新增排序(排序放在整個分組查詢的最後)
簡單使用
1. 查詢每個工種的最高工資
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
2. 查詢每個位置上的部門個數
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
新增篩選條件
1. 查詢郵箱中包含a字元，每個部門的平均工資
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
2. 查詢有獎金的每個領導手下員工的最高工資
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
新增分組後的篩選條件
1. 查詢哪個部門的員工個數大於2
Ⅰ查詢每個部門的員工個數
SELECT COUNT(*) AS 員工個數,department_id FROM employees GROUP BY department_id;
Ⅱ 根據Ⅰ的結果進行篩選，查詢哪個部門的員工個數大於2
SELECT COUNT(*) AS 員工個數,department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
2. 查詢每個工種有獎金的員工的最高工資大於12000的工種編號和最高工資
Ⅰ 查詢每個工種有獎金的員工的最高工資
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id;
Ⅱ 根據Ⅰ的結果進行篩選，篩選出最高工資大於12000
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
3. 查詢領導編號大於102的每個領導手下的最低工資大於5000的領導編號是哪個，以及其最低工資
Ⅰ 查詢每個領導手下的員工最低工資
SELECT MIN(salary),manager_id FROM employees GROUP BY manager_id;
Ⅱ 新增篩選條件：領導編號大於102
SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id;
Ⅲ 新增篩選條件：最低工資大於5000
SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;
按表達式或函數分組
1. 按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數大於5的有哪些
SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;
按多個欄位分組
1. 查詢每個部門每個工種的員工的平均工資
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;
新增排序
1. 查詢每個部門每個工種的員工的平均工資，並按平均工資的高低顯示
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;
連線查詢又稱爲多表查詢，當查詢的欄位來自於多個表時，就會用到連線查詢。
笛卡爾乘積現象：表1 有m行，表2 有n行，結果爲 m*n 行
發生原因：沒有有效的連線條件
如何避免：新增有效的連線條件
分類
1. 按年代分類
Ⅰsql92標準: 僅僅支援內連線
Ⅱsql99標準【推薦】: 支援內連線+外連線(左外和右外)+交叉連線
2. 按功能分類
Ⅰ內連線
等值連線
非等值連線
自連線
Ⅱ外連線
左外連線
右外連線
全外連線
Ⅲ交叉連線
1. 多表等值連線的結果爲多表的交集部分
2. n表連線，至少需要n-1個連線條件
3. 多表的順序沒有要求
4. 一般需要爲表起別名
5. 可以搭配前面介紹的所有子句使用，比如排序、分組、篩選
1. 查詢女神名和對應的男神名
SELECT name,boyName FROM boys,beauty WHERE boys.id = beauty.boyfriend_id;
2. 查詢員工名和對應的部門名
SELECT last_name,department_name FROM employees,departments WHERE employees.department_id=departments.department_id;
3. 查詢員工名、工種號、工種名
注意:如果爲表起了別名，則查詢的欄位就不能使用原來的表名去限定
SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.job_id=j.job_id;
【可以加篩選條件】
4. 查詢有獎金的員工名、部門名
SELECT e.last_name,d.department_name FROM employees e,departments d WHERE e.department_id=d.department_id AND e.commission_pct IS NOT NULL;
5. 查詢城市名中第二個字元爲o的部門名和城市名
SELECT
d.department_name,
l.city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
AND l.city LIKE '_o%';
【可以加分組】
6. 查詢每個城市的部門個數
SELECT city,COUNT(*) 個數 FROM departments d,locations l WHERE d.location_id=l.location_id GROUP BY l.city;
7. 查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資
SELECT
d.department_name,
d.manager_id,
MIN(salary)
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND commission_pct IS NOT NULL
GROUP BY
e.department_id;
【可以加排序】
8. 查詢每個工種的工種名和員工的個數，並且按員工個數降序
SELECT
j.job_title,
COUNT(*) 員工個數
FROM
employees e,
jobs j
WHERE
e.job_id = j.job_id
GROUP BY
e.job_id
ORDER BY
COUNT(*) DESC;
【可以實現三表連線】
9. 查詢員工名、部門名和所在的城市
SELECT
e.last_name,
d.department_name,
l.city
FROM
employees e,
departments d,
locations l
WHERE
e.department_id = d.department_id
AND d.location_id = l.location_id;
1. 查詢員工的工資和工資級別
SELECT
e.salary,
jg.grade_level
FROM
employees e,
job_grades jg
WHERE
salary BETWEEN jg.lowest_sal
AND jg.highest_sal;
1. 查詢員工名和上級的名稱
SELECT
e.last_name 員工,
m.last_name 領導
FROM
employees e,
employees m
WHERE
e.manager_id = m.employee_id;
select 查詢列表
from 表1 別名 [連線型別]
join 表2 別名
on 連線條件
[where 篩選條件]
[group by 分組]
[order by 排序列表]
連線型別
內連線:inner (inner可以省略) 用來求多表的交集
外連線
左外:left[outer]
右外:right[outer]
全外:full[outer] mysql不支援
交叉連線:cross
兩表連線，沒有順序要求
多表連線，只要後面的表和前面的表能夠連線就行
等值連線
1. 查詢員工名、部門名
SELECT
last_name,
department_name
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id;
2. 查詢名字中包含e的員工名和工種名【篩選】
SELECT
last_name,
job_title
FROM
employees e
INNER JOIN jobs j ON e.job_id = j.job_id
WHERE
last_name LIKE '%e%';
3. 查詢部門個數大於3的城市名和部門個數【新增分組+篩選】
注意:這個題是按城市名分組
SELECT
city,
COUNT(*) 部門個數
FROM
locations l
INNER JOIN departments d ON l.location_id = d.location_id
GROUP BY
city
HAVING
COUNT(*) > 3;
4. 查詢哪個部門的部門員工個數大於3的部門名和員工個數，並按個數降序【排序】
SELECT
department_name,
COUNT(*) 員工個數
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY
department_name
HAVING
COUNT(*) > 3
ORDER BY
COUNT(*) DESC;
5. 查詢員工名、部門名、工種名，並按部門名降序【多表查詢+排序】
SELECT
last_name,
department_name,
job_title
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY
department_name DESC;
非等值連線
1. 查詢員工的工資級別
SELECT
e.employee_id,
j.grade_level
FROM
employees e
JOIN job_grades j ON salary BETWEEN j.lowest_sal
AND j.highest_sal;
自連線
1. 查詢員工的名字以及上級的名字
SELECT
e1.last_name 員工名,
e2.last_name 領導名
FROM
employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
應用場景
用於查詢一個表中有，另一個表沒有的記錄
特點
1. 外連線的查詢結果爲主表中的所有記錄
如果從表中有和它匹配的，則顯示匹配的值
如果從表中沒有和它匹配的，則顯示null
所以，外連線查詢結果 = 內連線結果 + 主表中有而從表中沒有的記錄
2. 左外連線，left join左邊的是主表
右外連線，right join右邊的是主表
3. 左外和右外交換兩個表的順序，可以實現同樣的效果
4. 全外連線 = 內連線結果 + 表1中有但表2沒有的 + 表2中有但表1沒有的
注意:mysql不支援全外連線
1. 查詢男朋友 不在男神表的女神名
SELECT
b.`name`
FROM
beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE
bo.id IS NULL;
2. 查詢哪個部門沒有員工
SELECT
department_name
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE
e.employee_id IS NULL;
兩表進行笛卡爾乘積的結果
SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;
出現在其他語句中的select語句，稱爲子查詢或內查詢
外部的查詢語句，稱爲主查詢或外查詢
select後面:
僅僅支援標量子查詢
from後面:
支援表子查詢
where或having後面:【重要，用得較多】
標量子查詢(單行子查詢)
列子查詢(多行子查詢)
行子查詢(用的很少)
exists後面(相關子查詢):
表子查詢
標量子查詢(結果集只有一行一列)
列子查詢(結果集一列，多行)
行子查詢(結果集多行，多列，但一般情況下指一行多列)
表子查詢(結果集一般爲多行多列)
特點
1. 子查詢放在小括號內
2. 子查詢一般放在條件的右側
3. 標量子查詢，一般搭配單行操作符(> < >= <= = <>)使用
4. 列子查詢，一般搭配多行操作符(in any/some all)使用
5. 子查詢的執行優先於主查詢執行，因爲主查詢的條件用到了子查詢的結果
多行比較操作符
操作符 含義
in/not in 等於列表中的任意一個
any|some 和子查詢返回的某一個值比較
all 和子查詢返回的所有值比較
IN 等價於 = ANY
NOT IN 等價於 <> ALL
標量子查詢(單行子查詢)
1. 誰的工資比Abel高
Ⅰ 查詢Abel的工資
SELECT salary FROM employees WHERE last_name = 'Abel';
Ⅱ 查詢員工的資訊，滿足salary > Ⅰ的結果
SELECT
*
FROM
employees
WHERE
salary > (
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel'
);
2. 返回job_id與141號員工相同，salary比143號員工多的員工姓名、job_id、工資
Ⅰ 查詢141號員工的job_id
SELECT job_id FROM employees WHERE employee_id=141;
Ⅱ 查詢143號員工的salary
SELECT salary FROM employees WHERE employee_id=143;
Ⅲ 查詢員工的姓名、job_id、工資，要求job_id = Ⅰ並且salary > Ⅱ
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
job_id = (
SELECT
job_id
FROM
employees
WHERE
employee_id = 141
)
AND salary > (
SELECT
salary
FROM
employees
WHERE
employee_id = 143
);
3. 返回公司工資最少的員工的last_name,job_id和salary
Ⅰ 查詢工資的最低工資
SELECT MIN(salary) FROM employees;
Ⅱ 查詢last_name,job_id和salary，要求salary = Ⅰ
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
salary = (
SELECT
MIN(salary)
FROM
employees
);
4. 查詢最低工資大於50號部門最低工資的部門id和其最低工資
Ⅰ 查詢50號部門的最低工資
SELECT MIN(salary) FROM employees WHERE department_id=50;
Ⅱ 查詢每個部門的最低工資
SELECT MIN(salary),department_id FROM employees GROUP BY department_id;
Ⅲ 在Ⅱ的基礎上篩選，滿足最低工資大於Ⅰ
SELECT
MIN(salary),
department_id
FROM
employees
GROUP BY
department_id
HAVING
MIN(salary) > (
SELECT
MIN(salary)
FROM
employees
WHERE
department_id = 50
);
列子查詢(多行子查詢)
1. 返回location_id是1400或1700的部門中的所有員工姓名
Ⅰ 查詢location_id是1400或1700的部門編號
SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700);
Ⅱ 查詢員工姓名，要求部門編號是Ⅰ列表中的某一個
SELECT
last_name
FROM
employees
WHERE
department_id IN (
SELECT DISTINCT
department_id
FROM
departments
WHERE
location_id IN (1400, 1700)
);
或
SELECT
last_name
FROM
employees
WHERE
department_id = ANY (
SELECT DISTINCT
department_id
FROM
departments
WHERE
location_id IN (1400, 1700)
);
2. 返回其他工種中比job_id爲'IT_PROG'工種任一工資低的員工的員工號、姓名、job_id、salary
方法一:
Ⅰ 查詢job_id爲'IT_PROG'工種任一工資
SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG';
Ⅱ 查詢員工號、姓名、job_id、salary，要求salary < (Ⅰ)的任意一個
SELECT
last_name,
employee_id,
job_id,
salary
FROM
employees
WHERE
salary < ANY (
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
方法二:
Ⅰ 查詢job_id爲'IT_PROG'工種最高工資
SELECT MAX(salary) FROM employees WHERE job_id='IT_PROG';
Ⅱ 查詢員工號、姓名、job_id、salary，要求salary < (Ⅰ)的任意一個
SELECT
last_name,
employee_id,
job_id,
salary
FROM
employees
WHERE
salary < (
SELECT
MAX(salary)
FROM
employees
WHERE
job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
行子查詢(結果集一行多列或多行多列)
要求多個篩選條件都使用一樣的操作符。
1. 查詢員工編號最小並且工資最高的員工資訊
方法一(行子查詢):
SELECT
*
FROM
employees
WHERE
(employee_id, salary) = (
SELECT
MIN(employee_id),
MAX(salary)
FROM
employees
);
方法二(傳統方法):
Ⅰ 查詢最小的員工編號
SELECT MIN(employee_id) FROM employees;
Ⅱ 查詢最高工資
SELECT MAX(salary) FROM employees;
Ⅲ 查詢員工資訊
SELECT
*
FROM
employees
WHERE
employee_id = (
SELECT
MIN(employee_id)
FROM
employees
)
AND salary = (
SELECT
MAX(salary)
FROM
employees
);
僅僅支援標量子查詢（一行一列）
1. 查詢每個部門的員工個數
SELECT
d.*, (
SELECT
COUNT(*)
FROM
employees e
WHERE
e.department_id = d.department_id
) AS 個數
FROM
departments d;
2. 查詢員工號=102的部門名
SELECT
(
SELECT
d.department_name
FROM
departments d
JOIN employees e ON d.department_id = e.department_id
WHERE
e.employee_id = 102
) 部門名;
注意：將子查詢結果充當一張表，要求必須起別名，因爲這個表本來不存在，如果不起別名的話，找不到。
1. 查詢每個部門的平均工資的工資等級
Ⅰ 查詢每個部門的平均工資
SELECT AVG(salary),department_id FROM employees GROUP BY department_id;
Ⅱ 連線Ⅰ的結果集和job_grades表，篩選條件平均工資 在lowest_sal和highest_sal之間
SELECT
av_dep.*, j.grade_level
FROM
job_grades j
JOIN (
SELECT
AVG(salary) av,
department_id
FROM
employees
GROUP BY
department_id
) av_dep ON av_dep.av BETWEEN j.lowest_sal AND j.highest_sal;
語法:
exists(完整的查詢語句)
結果:
1或0
簡單使用:
SELECT EXISTS(SELECT employee_id FROM employees);
1. 查詢有員工的部門名
方法一(使用exists):
SELECT
d.department_name
FROM
departments d
WHERE
EXISTS (
SELECT
*
FROM
employees e
WHERE
e.department_id = d.department_id
);
方法二(使用in):
SELECT
department_name
FROM
departments d
WHERE
d.department_id IN (
SELECT
e.department_id
FROM
employees e
);
2. 查詢沒有女朋友的男神資訊
方法一(使用in):
SELECT
bo.*
FROM
boys bo
WHERE
bo.id NOT IN (
SELECT
boyfriend_id
FROM
beauty
);
方法二(使用exists):
SELECT
bo.*
FROM
boys bo
WHERE
NOT EXISTS (
SELECT
boyfriend_id
FROM
beauty b
WHERE
bo.id = b.boyfriend_id
);
1. 查詢和Zlotkey相同部門的員工姓名和工資
Ⅰ 查詢Zlotkey的部門編號
SELECT department_id FROM employees WHERE last_name='Zlotkey';
Ⅱ 查詢部門編號 = Ⅰ的員工姓名和工資
SELECT
last_name,
salary
FROM
employees
WHERE
department_id = (
SELECT
department_id
FROM
employees
WHERE
last_name = 'Zlotkey'
);
2. 查詢工資比公司平均工資高的員工的員工號，姓名和工資
Ⅰ 查詢平均工資
SELECT AVG(salary) FROM employees;
Ⅱ 查詢工資 > Ⅰ的員工號，姓名和工資
SELECT
employee_id,
last_name,
salary
FROM
employees
WHERE
salary > (
SELECT
AVG(salary)
FROM
employees
);
3. 查詢各部門中工資比本部門平均工資高的員工的員工號，姓名和工資
Ⅰ 查詢各部門的平均工資
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
Ⅱ 連線Ⅰ結果集和employees表，進行篩選
SELECT
e.department_id,
employee_id,
last_name,
salary
FROM
employees e
JOIN (
SELECT
department_id,
AVG(salary) ag
FROM
employees
GROUP BY
department_id
) ag_dep ON e.department_id = ag_dep.department_id
WHERE
salary > ag_dep.ag;
4. 查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名
Ⅰ 查詢姓名中包含字母u的員工的部門編號
SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%';
Ⅱ 查詢部門編號 = Ⅰ中的任意一個的員工號和姓名
SELECT
employee_id,
last_name
FROM
employees
WHERE
department_id IN (
SELECT DISTINCT
department_id
FROM
employees
WHERE
last_name LIKE '%u%'
);
5. 查詢在部門的location_id爲1700的部門工作的員工的員工號
Ⅰ 查詢location_id爲1700的部門編號
SELECT DISTINCT department_id FROM departments WHERE location_id=1700;
Ⅱ 查詢部門號 = Ⅰ中的任意一個的員工號
SELECT
employee_id
FROM
employees
WHERE
department_id = ANY (
SELECT DISTINCT
department_id
FROM
departments
WHERE
location_id = 1700
);
6. 查詢管理者是K_ing的員工姓名和工資
Ⅰ 查詢姓名爲K_ing的員工編號
SELECT employee_id FROM employees WHERE last_name='K_ing';
Ⅱ 查詢哪個員工的manager_id = Ⅰ
SELECT
last_name,
salary
FROM
employees
WHERE
manager_id IN (
SELECT
employee_id
FROM
employees
WHERE
last_name = 'K_ing'
);
7. 查詢工資最高的員工的姓名，要求first_name和last_name顯示爲一列，列名爲姓.名
Ⅰ 查詢最高工資
SELECT MAX(salary) FROM employees;
Ⅱ 查詢工資 = Ⅰ的姓.名
SELECT
CONCAT(first_name, '.', last_name)
FROM
employees
WHERE
salary = (
SELECT
MAX(salary)
FROM
employees
);
1. 查詢工資最低的員工資訊:last_name,salary
SELECT
last_name,
salary
FROM
employees
WHERE
salary = (
SELECT
MIN(salary)
FROM
employees
);
2. 查詢平均工資最低的部門資訊
方法一:
Ⅰ 查詢每個部門的平均工資
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
Ⅱ 查詢Ⅰ結果上的最低平均工資
SELECT MIN(ag_dep.ag) FROM (SELECT department_id,AVG(salary) ag FROM employees GROUP BY department_id) ag_dep;
Ⅲ 查詢哪個部門的平均工資=Ⅱ
SELECT
department_id
FROM
employees
GROUP BY
department_id
HAVING
AVG(salary) = (
SELECT
MIN(ag_dep.ag)
FROM
(
SELECT
department_id,
AVG(salary) ag
FROM
employees
GROUP BY
department_id
) ag_dep
);
Ⅳ 查詢部門資訊
SELECT
d.*
FROM
departments d
WHERE
d.department_id = (
SELECT
department_id
FROM
employees
GROUP BY
department_id
HAVING
AVG(salary) = (
SELECT
MIN(ag_dep.ag)
FROM
(
SELECT
department_id,
AVG(salary) ag
FROM
employees
GROUP BY
department_id
) ag_dep
)
);
方法二:
Ⅰ 查詢每個部門的平均工資
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
Ⅱ 查詢最低平均工資的部門編號
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1;
Ⅲ 查詢部門資訊
SELECT
*
FROM
departments
WHERE
department_id = (
SELECT
department_id
FROM
employees
GROUP BY
department_id
ORDER BY
AVG(salary)
LIMIT 1
);
3. 查詢平均工資最低的部門資訊和該部門的平均工資
Ⅰ 查詢每個部門的平均工資
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
Ⅱ 查詢最低平均工資的部門編號
SELECT AVG(salary),department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1;
Ⅲ 查詢部門資訊
SELECT d.*,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY ag
LIMIT 1
) ag_dep
ON d.department_id=ag_dep.department_id;
4. 查詢平均工資最高的job資訊
Ⅰ 查詢最高的job的平均工資
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
Ⅱ 查詢job資訊
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
5. 查詢平均工資高於公司平均工資的部門有哪些
Ⅰ 查詢公司的平均工資
SELECT AVG(salary)
FROM employees;
Ⅱ 查詢每個部門的平均工資
SELECT AVG(salary)
FROM employees
GROUP BY department_id
Ⅲ 篩選Ⅱ的結果集，滿足平均工資>Ⅰ
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);
6. 查詢出公司中所有manager的詳細資訊
Ⅰ 查詢所有manager的員工編號
SELECT DISTINCT manager_id
FROM employees
Ⅱ 查詢詳細資訊，滿足employee_id=Ⅰ
SELECT *
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
7. 各個部門中，最高工資中最低的那個部門的最低工資是多少
Ⅰ 查詢各個部門的最高工資中最低的部門編號
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
Ⅱ 查詢Ⅰ結果的那個部門的最低工資
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);
8. 查詢平均工資最高的部門的manager的詳細資訊:last_name,department_id,email,salary
Ⅰ 查詢平均工資最高的部門編號
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
Ⅱ 將employees和departments連線查詢，篩選條件是Ⅰ
SELECT last_name, d.department_id, email, salary
FROM employees e
INNER JOIN departments d
ON d.manager_id = e.employee_id
WHERE d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
應用場景
當要顯示的數據，一頁顯示不全，需要分頁提交sql請求
語法
select 查詢列表
from 表1 別名
[ [連線型別] join 表2 別名 ]
[on 連線條件]
[where 篩選條件]
[group by 分組]
[having 分組後的篩選]
[order by 排序列表]
limit [offset,] size;
其中，offset表示要顯示條目的起始索引(起始索引從0開始)，size表示要顯示的條目個數
執行順序:from-->join-->on-->where-->group by-->having-->select-->order by-->limit
特點
1. limit語句放在查詢語句的最後
2. 公式:
要顯示的頁數page，每頁的條目數size
假如size=10
page
1 0
2 10
3 20
select 查詢列表 from 表 limit (page-1)*size,size;
1. 查詢前五條員工資訊
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
2. 查詢第11條至第25條
SELECT * FROM employees LIMIT 10,15;
3. 查詢有獎金的員工資訊，並且工資較高的前10名顯示出來
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;
已知表stuinfo，id學號，name姓名，email郵箱，gradeId年級編號，sex性別，age年齡
已知表grade，id年級編號，gradeName年級名稱
一、查詢所有學員的郵箱的使用者名稱(注:郵箱中@前面的字元)
select substr(email,1,instr(email,'@')-1) 使用者名稱 from stuinfo;
二、查詢男生和女生的個數
select sex,count(*) 個數 from stuinfo group by sex;
三、查詢年齡>18歲的所有學生的姓名和年級名稱
select name,gradeName from stuinfo s join grade g on s.gradeId=g.id where age>18;
四、查詢哪個年級的學生最小年齡>20歲
1. 每個年級的最小年齡
select min(age),gradeId from stuinfo group by gradeId;
2. 在1的結果集上篩選
select min(age),gradeId from stuinfo group by gradeId having min(age)>20;
五、試說出查詢語句中涉及到的所有關鍵字，以及執行先後順序
select 查詢列表
from 表1 別名
連線型別 join 表2 別名
on 連線條件
where 篩選條件
group by 分組列表
having 分組後的篩選
order by 排序列表
limit 偏移,條目數;
執行順序:from-->join-->on-->where-->group by-->having-->select-->order by-->limit
union(聯合，合併): 將多條查詢語句的結果合併成一個結果
語法:
查詢語句1
union
查詢語句2
union
......
使用場景:
要查詢的結果來自於多個表，且多個表沒有直接的連線關係，但查詢的資訊(查詢列表)一致。
特點:
1. 要求多條查詢語句的查詢列表是一致的
2. 要求多條查詢語句的查詢的每一列的型別和順序最好一致
3. union關鍵字預設去重，union all可以包含重複項
練習: 查詢部門編號>90或郵箱中包含a的員工資訊
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
DML（Data Manipulate Language）：數據操作語言
主要涉及：insert 、update、delete
解決方案: [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause
執行下面 下麪sql語句:
select version(),@@sql_mode;
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
方式一:
insert into 表名(列名1,...) values(值1,...);
案例:
1. 插入的值的型別要與列的型別一致或相容
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐藝昕','女','1990-04-23','18712344321',NULL,2);
2. 可以爲null的列如何插入值
方式一:
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐藝昕','女','1990-04-23','18712344321',NULL,2);
方式二:
INSERT INTO beauty(id,name,sex,borndate,phone,boyfriend_id)
VALUES(14,'金星','男','1990-04-23','18712344321',6);
3. 列的順序可以調換
INSERT INTO beauty(name,id,sex,borndate,phone,boyfriend_id)
VALUES('金星',14,'男','1990-04-23','18712344321',6);
4. 列數和值的個數必須一致
5. 可以省略列名，預設所有列，而且列的順序和表中列的順序一致
INSERT INTO beauty
VALUES(13,'唐藝昕','女','1990-04-23','18712344321',NULL,2);
方式二:
insert into 表名
set 列名1=值1,列名2=值2......
案例:
INSERT INTO beauty
SET id=20,name='lucy',phone='17609222432';
兩種方式大PK
1. 方式一支援插入多行
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
VALUES(23,'唐藝昕','女','1990-04-23','18712344321',NULL,2),
(24,'唐藝昕','女','1990-04-23','18712344321',NULL,2),
(25,'唐藝昕','女','1990-04-23','18712344321',NULL,2);
2. 方式一支援子查詢，方式二不支援
INSERT INTO beauty(id,name,phone)
SELECT 30,'lucy','17674125896';
語法
update 表名
set 列1=新值1,列2=新值2,...
where 篩選條件;
執行順序
update-->where-->set
1. 修改beauty表中姓唐的女神的電話爲13899888899
UPDATE beauty SET
phone='13899888899'
WHERE `name` LIKE '唐%';
2. 修改boys表中id爲2的名稱爲張飛，魅力值爲10
UPDATE boys
SET boyName='張飛',userCP=10
WHERE id=2;
語法
sql92語法
update 表1 別名,表2 別名
set 列1=值1,...
where 連線條件
and 篩選條件;
sql99語法
update 表1 別名
inner|left|right join 表2 別名
on 連線條件
set 列1=值1
where 篩選條件;
1. 修改張無忌的女朋友的手機號爲110
UPDATE boys bo
JOIN beauty b
ON bo.id=b.boyfriend_id
SET b.phone=110
WHERE bo.boyName='張無忌';
2. 修改沒有男朋友的女神的男朋友編號都爲2號
UPDATE boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.boyfriend_id=2
WHERE b.boyfriend_id IS NULL;
方式一:delete
語法:
1. 單表的刪除
delete from 表名 [where 篩選條件];
2. 多表的刪除
sql92語法
delete 表1的別名[,表2的別名]
from 表1 別名,表2 別名
where 連線條件
and 篩選條件;
sql99語法
delete 表1的別名[,表2的別名]
from 表1 別名
inner|left|right join 表2 別名 on 連線條件
where 篩選條件;
方式二:truncate
語法:
truncate table 表名;
1. 刪除手機號以9結尾的女神資訊
DELETE FROM beauty WHERE phone LIKE '%9';
1. 刪除張無忌的女朋友的資訊
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id=bo.id
WHERE bo.boyName='張無忌';
2. 刪除黃曉明的資訊以及他女朋友的資訊
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id=bo.id
WHERE bo.boyName='黃曉明';
1. 刪除beauty表中所有數據
TRUNCATE TABLE beauty;
1. delete可以加where條件，truncate不能加
2. truncate刪除效率高一點
3. 假如要刪除的表中有自增長列。如果用delete刪除後，再插入數據，自增長列的值從斷點開始，而truncate刪除後，再插入數據，自增長列的值從1開始
4. truncate刪除沒有返回值(顯示共0行受到影響)，delete刪除有返回值(顯示共n行受到影響)。
5. truncate刪除不能回滾，delete刪除可以回滾