MySQL 简单查询记录（二）

#查询工资大于12000的员工 
SELECT * FROM `employees` WHERE salary > 12000

#查询部门编号不为90的员工姓名和部门
SELECT CONCAT(`first_name`,`last_name`) AS 姓名 ,`department_id` FROM `employees` WHERE `department_id` <> 90 

#查询工资在10000到20000的员工名、工资及奖金
SELECT `last_name`,`salary`,`commission_pct` AS 奖金 FROM employees WHERE salary >= 10000 AND salary <= 20000;
SELECT `last_name`,`salary`,`commission_pct` AS 奖金 FROM employees WHERE salary BETWEEN 10000 AND 20000;

#查询部门编号不是在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
SELECT * FROM employees WHERE department_id NOT BETWEEN 90 AND 110 OR salary > 15000

#查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE `last_name` LIKE '%a%'

#查询员工名中第三个字符为n,第五个字符为l的员工名和工资
SELECT `last_name`,`salary` FROM employees WHERE `last_name` LIKE "__n_l%";

#查询员工名中第二个字符为_的员工名 通过\转义或者使用 ESCAPE 自定义转义字符 
SELECT `last_name` FROM employees WHERE `last_name` LIKE "_\_%";
SELECT `last_name` FROM employees WHERE `last_name` LIKE "_!_%" ESCAPE "!";

#查询员工编号在100到120之间的员工信息
SELECT * FROM `employees` WHERE `employee_id` BETWEEN 100 AND 120

#查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT * FROM `employees` WHERE `job_id` IN ('IT_PROG','AD_VP','AD_PRES');

#查询没有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM `employees` WHERE `commission_pct` IS NULL
SELECT `last_name`,`commission_pct` FROM `employees` WHERE `commission_pct` <=> NULL

#查询工资等于12000的员工 使用安全等于
SELECT * FROM `employees` WHERE salary <=> 12000

#查询员工号为176的员工姓名和部门号和年薪
SELECT employee_id,last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) FROM employees WHERE employee_id = 176

#查询没有奖金,且工资小于18000的salary,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中涉及到哪些位置编号
SELECT DISTINCT location_id FROM departments;

#查询员工的姓名和部门号和年薪,按年薪降序 按名字升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC , last_name ASC

#选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name,salary FROM employees WHERE salary<8000 OR salary >17000 ORDER BY salary DESC
SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC

#查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT * FROM employees WHERE email LIKE "%e%" ORDER BY LENGTH(email) DESC,department_id ASC

#常用函数 单行函数
#1、length() 获取参数值的字节个数
SELECT LENGTH('joih'); #4
SELECT LENGTH('测试'); #6

#2、concat() 拼接字符串
SELECT CONCAT(last_name,"_",first_name) FROM employees;

#3、upper、大写  lower小写
SELECT UPPER('joih');
#示例：将姓变大写、名变小写、然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM employees;

#4、substr 截取字符 索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR("李莫愁爱上了陆展元",7) AS out_put #陆展元
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR("李莫愁爱上了陆展元",1,3) AS out_put #李莫愁 
#案例:姓名中首字符大写，其他字符小写然后用_拼接 显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",LOWER(SUBSTR(last_name,2))) AS out_put FROM employees

#5、instr 第一次出现索引的位置 不存在返回0
SELECT INSTR("杨不悔爱上了殷六侠",'殷六侠');

#6、trim 去掉前后空格
SELECT TRIM(" 张翠山 ") AS out_put
SELECT TRIM('a' FROM "a张翠山a") AS out_put

#7、lpad 左填充 rpad 右填充
SELECT LPAD("殷素素",2,"*") AS out_put
SELECT RPAD("殷素素",12,"ab") AS out_put

#8、replace 替换
SELECT REPLACE("张无忌爱上了周芷若","周芷若","赵敏") AS out_put;

#常用函数 数学函数
#1、round 四舍五入
SELECT ROUND(1.5) #2
SELECT ROUND(1.5,2) #1.50 保留两位小数
#2、ceil 向上取整 返回>=该参数的最小整数
SELECT CEIL(4.01) #5
SELECT CEIL(-4.01) #-4
#3、floor 向下取整 返回<=该参数的最大整数
SELECT FLOOR(4.01) #4
SELECT FLOOR(-4.01) #-5
#4、truncate 截断
SELECT TRUNCATE(1.6991,1); #1.6
#5、mod 取余 mod(a,b) ==> a-a/b*b
SELECT MOD(10,3) 
SELECT 10%3

#常用函数 日期函数
#1、now 返回当前系统日期+时间
SELECT NOW();
#2、curdate 返回当前系统日期不包含时间
SELECT CURDATE();
#3、curtime 返回当前系统时间不包含日期
SELECT CURTIME();
#4、可以获取指定的部分，年月日时分秒
SELECT YEAR(NOW()) #2018
SELECT YEAR("1993-1-1") #1993
SELECT MONTH(NOW()) #6
SELECT MONTHNAME(NOW()) #June
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
#5、str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE("6/23/2018","%m/%d/%Y"); #2018-06-23
SELECT * FROM employees WHERE hiredate = '1992-4-3'
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992',"%m-%d %Y");
#6、date_format 将日期转换成字符串
SELECT DATE_FORMAT("1992-01-04","%Y年%m月%d日");
#案例 查询有奖金的员工名和入职日期(xx年/xx月 xx日)
SELECT last_name,DATE_FORMAT(hiredate,'%Y年/%m月/%d日') FROM employees WHERE commission_pct IS NOT NULL
#7、datediff 返回两个日期相差的天数
SELECT DATEDIFF("2018-6-23","2018-6-21") #2

#常用函数 流程控制
#1、if()函数
SELECT IF(2>1,"大","小"); #大
SELECT last_name,commission_pct,IF(commission_pct IS NULL,"无奖金","有奖金") AS 备注 FROM employees;
#2、case 情况一
#案例：查询员工的工资,要求
#部门号=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 情况二
#查询员工的工资情况
#如果工资大于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 

#单行函数测试
#1、显示系统时间(注:日期+时间)
SELECT NOW();
#2、查询员工号、姓名、工资、以及工资提高百分之20%后的结果(new salary)
SELECT employee_id,last_name,salary,salary*1.2 AS 'new salary' FROM employees
#3、将员工的姓名按首字母排序,并写出姓名的长度
SELECT SUBSTR(last_name,1,1) AS 首字符,LENGTH(last_name) AS 长度 FROM employees ORDER BY 首字符 ASC
#4、拼接
SELECT CONCAT(last_name," earns ",salary,' monthly but wants ',salary*3) AS "Dream Salary" FROM employees WHERE salary=24000;
#5、使用case-when,按照下面的条件
#job   	    gread
#AD_PRES      A
#ST_MAN       B
#IT_PROG      C
#SA_REP       D
#ST_CLERK     E
SELECT last_name,job_id AS job,CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP ' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS gread FROM employees WHERE job_id = 'AD_PRES';

#常用函数 分组函数 
SELECT SUM(salary) AS 总和, MAX(salary) AS 最大工资, MIN(salary) AS 最小工资, ROUND(AVG(salary),2) AS 平均工资, COUNT(salary) AS 个数 FROM employees
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#查询员工工资的最大值、最小值、平均值、总和
SELECT MAX(salary),MIN(salary),ROUND(AVG(salary),2),SUM(salary) FROM employees
#查询员工表中的最大入职日期和最小的入职日期相差的天数
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFRENCE FROM employees;
#查询部门编号为90的员工个数
SELECT COUNT(*) AS 个数 FROM employees WHERE department_id = '90'
#查询每个部门的平均工资
SELECT AVG(salary),department_id FROM employees GROUP BY department_id;
#查询每个工种的最高工资
SELECT MAX(salary),job_id AS 工种 FROM employees GROUP BY job_id;
#查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_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  having添加分组后的筛选条件
SELECT department_id,COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 2;
#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000
#查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,及最低工资
SELECT MIN(salary),manager_id FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING MIN(salary)>5000 
#按员工姓名的长度分组,查询每一组的个数,筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5
#查询每个部门每个工种的员工的平均工资 部门不为空 平均工资大于10000 降序显示
SELECT AVG(salary),department_id,job_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id,job_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) 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 ASC
#查询员工最高工资和最低工资的查询
SELECT MAX(salary)-MIN(salary) difference FROM employees;
#查询各个管理者手下员工的最低工资,其中最低工资不低于6000,没有管理者的员工不计算在内
SELECT MIN(salary),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >= 6000
#查询所有部门的编号,员工的数量和工资的平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC
#选择具有各个job_id的员工人数
SELECT COUNT(*),job_id FROM employees GROUP BY job_id

#一、sql92标准 连接查询(内连接)
#1.等值连接
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName FROM beauty,boys WHERE beauty.`boyfriend_id` = boys.`id`;
#案例2：查询员工名对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`;
#查询员工名,工种号,工种名
SELECT e.last_name,e.job_id,j.job_title FROM employees AS e,jobs AS j WHERE e.`job_id` = j.`job_id`;
#查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL	
#查询城市名中第二个字符为o的部门名和城市名
SELECT d.`department_name`,l.city FROM departments d,locations l WHERE d.`location_id` = l.location_id AND l.city LIKE "_o%"
#查询每个城市的部门个数
SELECT COUNT(*),l.city FROM departments d,locations l WHERE d.`location_id`=l.`location_id` GROUP BY l.city; 
#查询有奖金的每个部门名和部门领导编号和该部门的最低工资
SELECT d.department_name,d.manager_id,MIN(e.salary) FROM employees e,departments d WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY d.`department_name`,d.manager_id;
#查询每个工种的工种名和员工的个数,并按员工的个数降序
SELECT job_title,COUNT(*) AS 人数 FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` GROUP BY job_title ORDER BY 人数 DESC;
#查询员工名,部门名和所在的城市
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` AND l.`city` LIKE 's%' ORDER BY d.`department_name` DESC
#2.非等值连接
#查询员工的工资和工资级别
SELECT e.salary,j.grade_level FROM employees e,job_grades j WHERE e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;
#3.自连接 查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m WHERE m.employee_id = e.manager_id

#显示员工表的最大工资,工资平均值
SELECT MAX(salary),AVG(salary) FROM employees
#查询员工表中的employee_id,job_id,last_name,按department_id降序,salary升序
SELECT employee_id,job_id,last_name,department_id FROM employees ORDER BY department_id DESC ,salary ASC
#查询员工表的job_id中包含a和e的,并且a在e前面
SELECT job_id FROM employees WHERE job_id LIKE "%a%e%"
#显示当前日期,以及去掉前后空格,截取字符串函数
SELECT TRIM('字符' FROM NOW())
#显示所有的员工姓名,部门号,和部门名称
SELECT e.`last_name`,e.`department_id`,d.department_name FROM employees e,departments d WHERE e.`department_id` = d.department_id;
#查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,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.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id` AND e.`commission_pct` IS NOT NULL
#查询每个工种、每个部门的部门名、工种名和最低工资
SELECT e.job_id,d.`department_name`,j.job_title,MIN(e.`salary`) FROM employees e,departments d,jobs j WHERE e.`department_id` = d.`department_id` AND e.`job_id` = j.job_id GROUP BY e.job_id,d.`department_name`
#查询每个国家下的部门个数大于2的国家编号
SELECT l.`country_id`,COUNT(*) FROM departments d,locations l WHERE d.`location_id` = l.`location_id` GROUP BY country_id HAVING COUNT(*) > 2 
#选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号。
SELECT e.`last_name`,e.`employee_id`,m.last_name,m.employee_id FROM employees e,employees m WHERE e.`employee_id` = m.`manager_id`