Mysql基础编写sql案例(一)

数据库里面的数据库sql文件：https://blog.csdn.net/GongmissYan/article/details/102937816

#基础查询


# 查询单个字段
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  FROM employees

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

# 条件查询


# 按条件表达式筛选
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

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
    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
    
# 常见函数

# 案例：将姓变大写，名就能小写，然后拼接
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
#分组函数

# 查询员工表中的最大入职时间和最小入职时间，并计算出它们的相差的天数
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