MySQL学习-简单查询、条件查询、排序查询、分组查询
用到的表结构
employees表
departments表
jobs表
locations表
job_grades表
简单查询
语法：SELECT 查询列表 FROM 表名
特点：1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表
#1.查询表中单个字段
SELECT last_name FROM employees;
#2.查询表中多个字段
SELECT last_name,salary,email FROM employees;
#3.查询表中所有字段
SELECT * FROM jobs; #顺序与表中的字段一样
SELECT job_id,job_title,max_salary,min_salary FROM jobs;
#当字段名和关键字重名，使用 `` （1左边那个）将字段名隔开
SELECT `last_name` FROM employees;
#4.查询常量值
#SQL不区分字符和字符串，所有字符用 ‘’（单引号）
SELECT 'abc';
#5.查询表达式
SELECT 100*2/50%3; #1.0000
#6.查询函数
SELECT DATABASE();
#7.起别名 关键字 AS（可以省略）
#1.提高可读性。
#2.如果查询的字段有重名的情况，使用别名可以区分开来
SELECT 100*2/50%3 AS 结果;
SELECT first_name AS 姓,last_name AS 名 FROM employees;
#如果别名有关键字，使用""
SELECT last_name AS "SELECT" FROM employees;
#8.去重 关键字 DISTINCT
#查询与员工表中涉及的所有部门编号
SELECT DISTINCT department_id FROM employees;
#9.+ 号的作用在MySQL只是运算符
#两个操作数都为数值型，则作加法运算
SELECT 100+50; #150
#只要其中一个为字符型，则试图将字符型数值转换为数值型
如果转换成功则作加法运算
如果转换失败则将字符型数值转换为0
SELECT 100+'50'; #150
SELECT 100+'abc'; #100
#只要有一个是NULL，结果为NULL
SELECT NULL+50; #null
#IFNULL(可能为NULL的值,要返回的值)
SELECT CONCAT("奖金率",IFNULL(commission_pct,0)) AS "RESULT" FROM employees;
#案例：查询与员工的名和姓连接成一个字段，并显示姓名
SELECT CONCAT(first_name,' ',last_name) AS 姓名 FROM employees;
条件查询
语法：SELECT 查询列表-----3
FROM 表名---------------1
WHERE 筛选条件------2
分类：1.按条件表达式筛选
条件表达式：> < = != <> <= >=
2.按逻辑表达式筛选
逻辑运算符（连接条件表达式）：AND OR NOT && || ！
模糊查询：LIKE：一般与通配符搭配使用，除了支持字符型还支持数值型
通配符：%：任意多个字符，包含0个字符
_：单个字符
BETWEEN AND：在…之间，包含临界值，临界值不能交换顺序
IN：判断某字段的值是否属于IN列表中的某一项,列表中的值必须一致或兼容。且列表的值不可以使用通配符
IS [NOT] NULL：判断是否为NULL
<=>(少用)：可以判断NULL值，=不能判断NULL值
#一、按条件表达式筛选
#查询工资>12000的员工信息
SELECT * FROM employees WHERE salary > 12000;
#查询部门编号不等于90号的员工名和部门编号
SELECT CONCAT(first_name,' ',last_name) AS "NAME",department_id FROM employees WHERE department_id <> 90;
#二、按逻辑表达式筛选
#查询工资在10000~20000的员工信息
SELECT * FROM employees WHERE salary>=10000 AND salary<=20000;
#查询部门编号不是在90~~110之间且工资大于15000的员工信息
SELECT * FROM employees WHERE
NOT (department_id>=90 AND department_id<=110) AND salary>15000;
#三、模糊查询
#LIKE
#查询员工名中包含a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
#查询员工名中第三个字符为e，第五个字符为l的员工信息
SELECT * FROM employees WHERE last_name LIKE '__n_l%';
#查询员工名中第二个字符为_的员工信息
SELECT * FROM employees WHERE last_name LIKE '_\_%';
#转义字符可以是任意的，需要加上ESCAPE
SELECT * FROM employees WHERE last_name LIKE '_?_%' ESCAPE '?';
#查询员工号是200及以上的员工
SELECT * FROM employees WHERE employee_id LIKE '2__';
#BETWEEN AND
#查询工资在10000~20000的员工信息
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;
#IN
#查询员工的工种编号是IT_PROG、AD_VP、AD_PRES
SELECT * FROM employees WHERE job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
SELECT * FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
#IS NULL
#查询没有奖金率的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct <=> NULL;
#查询员工号为176的员工的姓名和部门名和年薪
SELECT last_name,employee_id,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS salary
FROM employees WHERE employee_id=176;
排序查询
语法：SELECT 查询列表 FROM 表 [WHERE 筛选条件] ORDER BY 排序
升序：ASC（默认） 降序：DESC
ORDER BY支持单个或多个字段、表达式、别名、函数
ORDER BY一般放在查询语句的最后面，limit字句除外
#查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#查询部门编号>=90的员工信息，按入职时间先后进行排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
#按年薪的高低显示员工信息（按表达式排序）
SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#按年薪的高低显示员工信息（按别名排序）
SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees ORDER BY 年薪 DESC;
#按姓名的长度显示员工的姓名和工资（按函数排序）
SELECT last_name,LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name) DESC;
#查询员工信息，要求先按工资升序，再按员工编号降序（按多个字段排序）
SELECT last_name,salary,employee_id FROM employees ORDER BY salary ASC,employee_id DESC;
#查询员工的姓名和部门编号和年薪，按年薪降序，按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees ORDER BY 年薪 DESC,last_name ASC;
#选择工资不在8000~17000的员工的姓名和工资，按工资降序
SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
#查询邮箱中包含e的员工信息，并按邮箱的字节数降序，再按部门编号升序
SELECT *,LENGTH(email) AS LENGTH FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;
分组查询
语法：SELECT 分组函数，列（要求出现在group by的后面）-----5
FROM 表---------------------------------------------------------------------1
[WHERE 筛选条件]-------------------------------------------------------2
GROUP BY 分组的列表------------------------------------------------3
HAVING 分组后筛选-----------------------------------------------------4
[ORDER BY 子句]--------------------------------------------------------6
1. SELECT过滤列，WHERE过滤行，HAVING过滤分组
2. WHERE过滤分组前，HAVING过滤分组后
3. 分组函数做条件肯定是放在HAVING子句中，分组函数不可以出现在WHERE
4. 能用分组前筛选就优先使用分组前筛选
5. GROUP BY子句可以支持单个或多个字段分组（多个字段之间没有顺序要求）
6. GROUP BY子句多字段在第一个字段分组后的基础上按第二个字段分组
#查询每个工种的最高工资
SELECT job_id,MAX(salary) FROM employees GROUP BY job_id;
#查询每个部门的员工个数
SELECT department_id,COUNT(*) FROM employees GROUP BY department_id;
#查询邮箱中含有’a’字符的，每个部门的平均工资
SELECT department_id,AVG(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#添加分组后的筛选条件
#查询那些部门员工个数大于2
SELECT department_id,COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*)>2;
#查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT job_id,MAX(salary) FROM employees WHERE commission_pct IS NOT NULL
GROUP BY job_id HAVING MAX(salary)>12000;
#查询领导编号大于102的每个领导手下员工的最低工资大于5000的领导编号，以及其最低工资
SELECT manager_id,MIN(salary) FROM employees WHERE manager_id>120
GROUP BY manager_id HAVING MIN(salary)>5000;
#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些
SELECT LENGTH(last_name) AS 长度,COUNT(*) FROM employees GROUP BY 长度 HAVING COUNT(*)>5;
#按多个字段分组
#查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;
#添加排序
#查询每个部门每个工种的员工的平均工资大于10000的有哪些，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id FROM employees
GROUP BY department_id,job_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) DESC;
#查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) FROM employees
GROUP BY department_id ORDER BY AVG(salary) DESC;
#查询各个job_id的员工个数
SELECT job_id,COUNT(*) FROM employees GROUP BY job_id;