Mysql基礎撰寫sql案例(一)-有解無憂

資料庫里面的資料庫sql檔案：https://blog.csdn.net/GongmissYan/article/details/102937816
#基礎查詢
/*
    語法：、
        select 查詢串列
        from 表名；
        
查詢串列可以是，表中的欄位，常量值 運算式，函式
查詢的表格是一個虛擬的表
*/

# 查詢單個欄位
select last_name from employees

# 查詢多個欄位
select last_name ,salary,email from employees

#查詢表中的所有欄位(盡量不要使用*如果欄位明確使用欄位)使用*號的順序是和原始表的順序是一樣的
select * from employees

#查詢常量值
SELECT 100 as 這個是100

#查詢函式
select VERSION()

#起別名（在查詢的時候如果有重名的情況，可以通過別名來區分）
select 100 as 這里是別名
select 100 這里是別名
> 如果別名里面包含了關鍵字最好通過引號參考起來

#去重:通過DISTINCT來去重
SELECT DISTINCT department_id from employees

# +號的作用
#sql中的+號的作用只是做為運算子
/*
* select 100+90兩個運算元都是數值型，則做加法運算
* select '123' + 90 如果一方為字串型別，字符數值轉換成數值型別
*    如果轉換成功就進行加法運算
*         如果轉換失敗就將字符型數值置為0
* select null + 90 在加法運算中不管那一方為Null結果都是null
*/
SELECT  FROM employees

#字串拼接(在字串拼接的時候使用的是CONCAT)
select CONCAT(last_name,first_name) from employees

# 條件查詢
/*
select 查詢串列 From 表名 where 篩選條件
*/

# 按條件運算式篩選
select * from employees where salary > 12000

#查詢員工部門編號不是90的員工我姓名和部門的ID
select CONCAT(last_name,first_name) as 員工名,e.department_id from employees e WHERE department_id <> 90

#查詢工資在10000到20000之間的員工名，工資以及獎金
SELECT
    CONCAT( last_name, first_name ) AS 員工名,
    salary AS 工資,
    commission_pct * salary AS 獎金 
FROM
    employees 
WHERE
    salary > 10000 
    AND salary < 20000
    
# 查詢部門編號不是在90到110之間，或者工資高于15000的員工資訊
SELECT
    * 
FROM
    employees 
WHERE
    department_id < 90 AND department_id > 110 
    OR salary > 15000

# 模糊查詢
/*
* like
* between and
* in
* is null |is not null
*/
# LIKE
/*
* %任意多的字符包含0個
* _ 任意單個字符
*/
SELECT
    * 
FROM
    employees 
WHERE
    last_name LIKE ( '%a%' )
#查詢員工名字第三個字符為e每5個字符為a的
SELECT * from employees WHERE last_name LIKE '__e_a%'
#查詢第二個字符是_的
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

#between and
# 包含兩個臨界值的
# 兩個臨界值不可以互換的
#查詢員工編號在100    到120之間的員工資訊
SELECT * FROM employees WHERE department_id BETWEEN 100 and 120

# in
# 查詢員工的工種編號
SELECT
    last_name,
    job_id 
FROM
    employees 
WHERE
    job_id IN ( 'AD_PRES', 'AD_VP', 'IT_PROT' )

# is null
# 案例：查詢沒有獎金的員工名和獎金率
SELECT
    last_name,
    commission_pct 
FROM
    employees 
WHERE
    commission_pct IS NULL
#案例：查詢有獎金的員工名和獎金率
SELECT
    last_name,
    commission_pct 
FROM
    employees 
WHERE
    commission_pct IS NOT NULL
    
# 案例：查詢員工號為176的員工的姓名和部門號和年薪
SELECT
    last_name,
    salary * 12 *(1+IFNULL(commission_pct,0))
    FROM employees
    WHERE employee_id = '176'
    
# 查詢沒有獎金，且工資小于18000的salay,last_name
SELECT
    salary,
    last_name 
FROM
    employees 
WHERE
    commission_pct IS NULL 
    AND salary < 18000
    
# 查詢employees表中，job_id不為'IT'或者工資為12000的員工資訊
SELECT
    * 
FROM
    employees 
WHERE
    job_id <> 'IT' 
    OR salary = 12000
    
# 查看Departments結構
DESC departments
    
# 查詢departments表中涉及到了哪些位置的編號
SELECT DISTINCT
    location_id 
FROM
    departments 
WHERE
    location_id IS NOT NULL
    
# 排序查詢
/*
* select 查詢串列 from 表 【where篩選】 order by 排序串列[asc 升序|desc 降序]
* 默認是asc asc可以不寫
*/
# 案例：查詢員工的資訊，要求工資從高到低排序
SELECT
    * 
FROM
    employees 
ORDER BY
    salary DESC
    
# 案例：查詢部門編號 >= 90 的員工資訊，按入職時間
SELECT * FROM employees WHERE department_id >= 90 
ORDER BY
    hiredate DESC
    
# 案例：按年薪的高低顯示員工的資訊和年薪【按運算式】
SELECT
    *,
    salary * 12 + ( 1+ IFNULL( commission_pct, 0 ) ) a 
FROM
    employees 
ORDER BY
    a DESC
    
# 查詢姓名的長度來顯示員工的姓名和工資【按函式排序】
SELECT LENGTH(last_name) a,salary FROM employees ORDER BY a DESC

# 查詢員工資訊，要求先按員工資排序，再按員工編號排序
SELECT
    * 
FROM
    employees 
ORDER BY
    salary ASC,
    employee_id DESC
    
# 查詢員工的姓名和部門號和年薪，按年薪降序，按姓名升序
SELECT
    last_name,
    department_id,
    12 * salary + ( 1+ IFNULL( commission_pct, 0 ) ) 年薪
FROM
    employees
ORDER BY 年薪 DESC,last_name ASC

# 選擇工資不在8000到17000的員工的姓名和工資，按工資降序
SELECT
    last_name,
    salary 
FROM
    employees 
WHERE
    !(salary BETWEEN 8000 
    AND 17000)
ORDER BY
    salary DESC
    
# 查詢郵箱中包含e的員工資訊，并按郵箱的位元組數降序，再按部門號升序
SELECT
    * 
FROM
    employees 
WHERE
    email LIKE '%e%' 
ORDER BY
    LENGTH( email ) DESC,
    department_id ASC
    
# 常見函式
/*
*    單行函式：
*            字符函式
*                    length、concat、upper、lower、substr、instr、trim、lpad、rpad、replace
*            數學函式
*                    round、ceil、floor、truncate
*            日期函式
*                    now、curdate、curtime、year、day、month、monthname、str_to_date、date_format
*            其它函式
*                    version、database、user
*         流程控制函式
*                    if、case
*
*/
# 案例：將姓變大寫，名就能小寫，然后拼接
SELECT CONCAT(UPPER(first_name),'_',LOWER(last_name)) FROM employees;
    
# 姓名中首字母大寫，其它的字符小寫，然后通過_拼接，顯示出來
SELECT
    CONCAT(
    UPPER( SUBSTR( last_name, 1, 1 ) ),
    '_',
    LOWER( SUBSTR( last_name, 1 ) ) 
    ) 
FROM
    employees;
    
# instr用于回傳子串在父串里面的起始索引(第一次出現的索引)
    
# trim 去掉前后面的空格
# 案例去掉字串里面的前后的a
SELECT TRIM('a' FROM 'aaaaaaaBaaaa')
    
# lpad 如果字串的長度不為二參值，就會通過&來填滿左側
SELECT lpad('兔兔',10,'&')
# rpad 如果字串的長度不為二參值，就會通過&來填滿左側
SELECT rpad('兔兔',10,'&')
#把下面字串中的cc替換成zz
SELECT replace('bbccaa','cc','zz')

#四舍五入
SELECT ROUND(-1.5)
#小數點后面保留兩位
SELECT ROUND(-1.5456,2)

#向上取整
SELECT ceil(1.10)
#向下取整
SELECT FLOOR(1.1)
# truncate 截斷
SELECT TRUNCATE(1.8999,1)
# mod取余
SELECT MOD(10,3)
# now回傳當前系統日期+時間
SELECT now();
# curdate回傳當彰系統的日期 不包含時間
SELECT CURDATE()
#curtime 回傳當前的時間，不包含日期
SELECT CURTIME()
#可以獲取指定的部分
SELECT year(NOW())
SELECT MONTH(NOW()
SELECT DAY(now())
SELECT MONTHNAME(NOW())

# 將字串的日期轉換成date格式
SELECT STR_TO_DATE('2020-07-9','%Y-%c-%d')
#將日期轉換成字符
SELECT DATE_FORMAT(NOW(),'%y年%c月%d日')
#查詢有獎金的員工名和入職日期(xx月/xx日 xx年)
    SELECT
        last_name,
        DATE_FORMAT( hiredate, '%m月/%d日 %Y年' ) 
    FROM
        employees 
    WHERE
        commission_pct IS NOT NULL

    # 查詢員工是否有獎金有就顯示有沒有就顯示沒有
    SELECT
        last_name,
        commission_pct,
    IF
        ( commission_pct, '有', '沒有' ) 
    FROM
    employees
    
# 查詢員工名，姓名，工資，以及工資提高百分之20%后的結果
SELECT last_name,salary,salary * 1.2 FROM employees 
# 將員工的姓名按首字符排序，并寫出姓名的長度(length)
    SELECT
        last_name
    FROM
        employees ORDER BY SUBSTR( last_name, 1, 1 ) ASC
#分組函式
/*
* 功能：用作統計使用，又稱為聚合函式或統計函式或組函式
*	分類：
*			sum求和 avg 平均 Max min count
*			sum avg 不可以放字符型別 日期也不可以
*			max min 是可以使用字符的  日期也支持
*     count 計算不為null個數，支持所有的型別 myisam存盤引擎下效率最高，因為它有一個內部的計數器
*/
# 查詢員工表中的最大入職時間和最小入職時間，并計算出它們的相差的天數
SELECT
	DATEDIFF( MAX( hiredate ), MIN( hiredate ) ) 
FROM
	employees;
# 查詢員工編號為90的員工個數
SELECT
	COUNT( * ) 
FROM
	employees 
WHERE
	department_id = 90;
	
# 分組查詢
#查詢每個部門的平均工資
SELECT
	AVG( salary ) 平均工資,
	department_id 部門編號 
FROM
	employees 
GROUP BY
	department_id;
# 查詢每個工種的最高工資
SELECT
	MAX( salary ) 最高工資,
	job_id 
FROM
	employees 
GROUP BY
	job_id
#查詢郵箱中包含a字符的，每個部門的平均工資
SELECT
	AVG( salary ),
	department_id
FROM
	employees 
WHERE
	email LIKE '%a%'
GROUP BY department_id
#查詢有獎金的每個領導手下員工的最高工資
SELECT
	MAX( salary ),
	manager_id
FROM
	employees 
WHERE
	commission_pct IS NOT NULL
GROUP BY manager_id
	
# 查詢那個部門的員工個數大于2
SELECT
	count( * ) a,
	department_id 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	a > 2
# 查詢每個工種有獎金的員工的最高工資>12000的工種編號和最高工資
# 1. 查詢每個工種有獎金的員工的最高工資
SELECT
	MAX( salary ),
	job_id 
FROM
	employees 
	WHERE
commission_pct is not NULL
GROUP BY
	job_id
# 在1的基礎上結果上篩選 最高工資大于12000的
SELECT
	MAX( salary ) a,
	job_id 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
GROUP BY
	job_id 
HAVING
	a > 12000
# 查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，
#以及其最低工資
SELECT
	MIN( salary ) a,
	manager_id 
FROM
	employees 
WHERE
	manager_id > 102 
GROUP BY
	manager_id 
HAVING
	a > 5000
	
# 案例：按員工姓名的長度分組，查詢每一組的員工個數，
# 篩選員工個數大于5的有那些
SELECT
	count(*) c,LENGTH(last_name)
FROM
	employees 
GROUP BY
	LENGTH( last_name )
HAVING c > 5
	
# 查詢每個部門每個工種的員工的平均工資
SELECT AVG(salary) a,department_id,job_id
FROM employees
GROUP BY department_id,job_id
# 查詢每個部門每個工種的員工的平均工資 根據平均工資的高低來排序
SELECT AVG(salary) a,department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY a DESC
#查詢各job_id的員工工資的最大值，最小值，平均值，總和，并按job_id升序
SELECT
	MAX( salary ),
	MIN( salary ),
	AVG( salary ),
	SUM( salary ),
	job_id
FROM
	employees 
GROUP BY
	job_id
ORDER BY job_id
	
# 查詢員工最高工資和最低工資的差距
SELECT MAX(salary) - MIN(salary) FROM employees;
#查詢各管理者手下員工的最低工資，其中最低工資不能低于6000
# 沒有管理者的員工不能計算在內
SELECT
	MIN( salary ) m,
	manager_id 
FROM
	employees 
WHERE
	manager_id IS NOT NULL 
GROUP BY
	manager_id 
HAVING
	m > 6000
	
# 查詢所有部門的編號，員工數量和工資平均值，并按平均工資降序
SELECT
	department_id,
	COUNT( * ) , AVG( salary ) a
FROM
	employees 
GROUP BY
	department_id 
ORDER BY
	a DESC
# 選擇具有各個job_id 的員工人數
SELECT
	COUNT(*) 
FROM
	employees 
GROUP BY
	job_id
#自連接
# 查詢 員工名和上級的名稱
SELECT
	e.last_name,
	m.last_name
FROM
	employees e,
	employees m
WHERE e.manager_id = m.employee_id

# 顯示員工表的最大工資，工資平均值
SELECT MAX(salary),AVG(salary) FROM employees
#查詢員工表的employee_id,job_id,
# last_name 按department_id降序，按salay升序
SELECT
	employee_id,
	job_id,
	last_name 
FROM
	employees 
ORDER BY
	department_id DESC,
	salary ASC
#查詢員工表的Job_id包含,a和e的，并且a在e前面
SELECT * FROM employees WHERE job_id like '%a%e%'
	
# 顯示當前的日期，以及去前后空格，截取字串的函式
SELECT TRIM(NOW()) 
#查詢90號部門員工的job_id和90號部門的Location_id
SELECT
	e.job_id,
	d.location_id 
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id 
	AND e.department_id = 90
# 顯示所有有獎金的員工的last_name,
#department_name.location_id,city
SELECT
	e.last_name,
	d.department_name,
	d.location_id,
	l.city 
FROM
	employees e,
	departments d,
	locations l 
WHERE
	e.commission_pct IS NOT NULL 
	AND e.department_id = d.department_id
	and d.location_id = l.location_id
#內連接
#查詢部門的個數>3的城市名和部門個數
SELECT
	city,
	COUNT( * ) c 
FROM
	departments d
	INNER JOIN locations l ON d.location_id = l.location_id 
GROUP BY
	city 
HAVING
	c > 3
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8711.html
標籤：MySQL
上一篇：MySQL基礎架構分析
下一篇：Mysql兩個不同庫同結構表的合并問題求助