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