MySQL查询

1.常量

SELECT 6;

2.字符常量

SELECT 'a';
SELECT "aaa";

3.表达式

SELECT 100 * 98;

4.函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

5.别名

SELECT 'A' AS B;
SELECT 'A' B;

SELECT last_name AS 姓, last_name AS 名 FROM employees;

别名有特殊字符，加双引号

SELECT last_name AS "SELECT #" FROM employees;

6.去重

查员工表涉及的部门编号，字段前加上 DISTINCT

SELECT DISTINCT department_id FROM employees;

7.拼接

加号的作用是数学运算不能连接字符串，两个操作数都为数值，则进行运算，其中一方为字符型，则试图将字符转换为数值，如果转换成功，继续运算，如果转换失败，则字符型的值转换为 0，只要其中一方为 NULL，结果为 NULL

SELECT NULL + 10;

SELECT last_name + first_name AS 姓名
FROM employees;


SELECT CONCAT(last_name, first_name) AS 姓名 FROM employees AS 姓名;

拼接

SELECT CONCAT('a', 'b', 'c');

NULL 和任何值拼接，结果都是 NULL

SELECT CONCAT('a', 'b', NULL);

ifnull() 如果为空值

SELECT IFNULL(NULL, 'default') AS ifn;

8.条件查询

SELECT * FROM employees WHERE salary > 12000;

SELECT * FROM employees WHERE department_id <> 90;

SELECT * FROM employees WHERE salary >= 10000 AND salary <= 20000;

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;

9.模糊查询

百分号代表通配符，任意字符，也包含 0 个字符，下划线 _ 代表任意单个字符，

SELECT * FROM employees WHERE last_name LIKE '%o%';

查询第三个字母是 n，第五个字母是 n

SELECT * FROM employees WHERE last_name LIKE '__n_l%';

查询第二个字符就是下划线的特殊情况，要进行转义或切换通配符，否则直接__%查询会把所有查出

SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

10.范围查询

BETWEEN AND 可以提高简捷度，结果包含区间值，临界值的位置不能颠倒，否则零行

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
SELECT * FROM employees WHERE salary NOT BETWEEN 8000 AND 17000;

11.IN 查询

IN 值必须相同或兼容，不能使用通配符

SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP');

12.NULL 查询

查询 NULL 用 IS / IS NOT，等于和不等于号不能判断空值

SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

安全等于可用于普通值也可以判断空值

SELECT * FROM employees WHERE commission_pct <=> NULL;

13.排序查询

升序， ASC 可以省略

SELECT * FROM employees ORDER BY salary ASC; 

降序

SELECT * FROM employees ORDER BY salary DESC;

条件排序

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

表达式排序

SELECT
   last_name,
   first_name,
   salary * 12 * (1 + IFNULL(commission_pct, 0)) AS annual_salary 
FROM
   employees
ORDER BY annual_salary ASC;

按照姓名长度

SELECT
   LENGTH(CONCAT(last_name, first_name)) AS length,
   last_name,
   first_name,
   salary
FROM
   employees
ORDER BY length;

多字段排序

SELECT * FROM employees ORDER BY salary ASC, employee_id DESC;

14.子查询

出现在其他语句中的查询语句就是子查询，也叫内查询。

子查询分为几种

• 标量子查询：结果集一行一列
• 列子查询：结果集一列多行
• 行子查询：结果集一行多列
• 表子查询:只要是查询结果就构成

子查询的使用规则

1. 子查询必须放在小括号内。
2. 子查询通常放在条件的右侧，例如 WHERE、HAVING 子句中的条件判断。
3. 标量子查询：通常配合单行操作符（如 =、<> 等）使用。
4. 列子查询：通常配合多行操作符（如 IN、ANY、SOME、ALL）使用。

子查询位置与支持情况

• SELECT 后面：仅支持标量子查询。
• FROM 后面：支持表子查询。
• WHERE 和 HAVING ：支持标量子查询、列子查询，并且能支持行子查询。
• EXISTS ：后面支持表子查询，通常用于判断某个条件是否存在。

14.1标量子查询

工资大于107号员工的人

SELECT last_name FROM employees WHERE salary > (
SELECT salary FROM employees WHERE employee_id = 107
)

与141号相同工种，工资比143号大的人

SELECT employee_id, last_name, job_id, salary FROM employees 
WHERE job_id = (
select job_id FROM employees WHERE employee_id = 141
)
AND salary > (
SELECT salary FROM employees WHERE employee_id = 143
)

哪个部门最低工资大于50号部门的最低工资

SELECT job_id, MIN(salary) AS min FROM employees GROUP BY job_id
HAVING min > (
SELECT MIN(salary) FROM employees WHERE department_id = 50
)

工资最少

select * from employees	WHERE salary = (select MIN(salary) FROM employees )

14.2 列子查询

location_id 是1400,1700的部门的员工

SELECT last_name, department_id FROM employees WHERE department_id IN (
	SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700)
) 

比job_id 是 ‘IT_PROG’的任意一个员工工资少的其他工种的员工

SELECT * FROM employees WHERE salary < ANY(
	SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'

比job_id 是 ‘IT_PROG’的所有的员工工资都少的其他工种的员工

SELECT * FROM employees WHERE salary < ALL(
	SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'

查询编号最小，工资最高的人 行子查询

SELECT * FROM employees WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary) FROM employees
)

14.3 SELECT 后的子查询

每个部门员工个数

SELECT 
	dept.department_id AS deptId, 
	(SELECT COUNT(*) FROM employees WHERE department_id = dept.department_id) AS count
FROM departments dept

102号员工的部门名

SELECT 
	employees.employee_id, 
	employees.last_name,
	(SELECT department_name FROM departments WHERE department_id = employees.department_id) AS deptName
FROM employees WHERE employee_id = 102

from后的子查询

每个部门平均工资等级

SELECT 
	jg.grade_level AS lavel ,
	avg_dep.salary AS salary,
	avg_dep.deptId AS deptId
	
FROM job_grades jg
RIGHT JOIN 
(SELECT AVG(salary) AS salary, department_id AS deptId FROM employees GROUP BY department_id) avg_dep
ON avg_dep.salary BETWEEN jg.lowest_sal AND highest_sal

14.4 EXISTS后子查询

相关子查询，布尔类型，看查询是否有值，括号内写查询语句

SELECT EXISTS(
	SELECT * FROM employees
)

SELECT EXISTS(
	SELECT * FROM employees WHERE salary = 251526
)

查询有员工的部门

SELECT department_name FROM departments WHERE EXISTS (
	SELECT last_name, department_id FROM employees WHERE employees.department_id = departments.department_id
)

没有配偶的男生

SELECT * FROM boys WHERE NOT EXISTS (
	SELECT * from beauty WHERE boyfriend_id = boys.id
)

15.连接查询

连接查询的分类:

按年代

• sql92标准
• sql99标准（推荐）

按功能

• 内连接 ：等值连接 非等值连接 自连接
• 外连接 ：左外连接 右外连接 全外连接
• 交叉连接

15.1 笛卡尔积

所有行全部连接，因缺少条件造成

select `name`,boyName from beauty, boys

15.2 等值连接sql92

员工名和部门名

SELECT last_name, department_name FROM departments, employees
WHERE employees.department_id = departments.department_id

有奖金的员工名，工种号和工种名

SELECT last_name, jobs.job_id, job_title FROM jobs, employees
WHERE employees.job_id = jobs.job_id
AND employees.commission_pct is not null

每个城市的部门数量

SELECT COUNT(departments.department_id) , locations.city FROM locations, departments 
WHERE departments.location_id = locations.location_id
GROUP BY locations.city

15.3 非等值连接sql92

SELECT salary, employee_id ,grade_level from employees, job_grades AS grades
WHERE salary BETWEEN grades.lowest_sal AND grades.highest_sal

15.4 自连接sql92

员工名和上级的名字

SELECT emp.last_name , emp.employee_id , emp.manager_id 
FROM employees emp , employees mang
WHERE emp.manager_id = mang.employee_id

15.5 等值连接sql99

SELECT job_title, last_name FROM employees emp
INNER JOIN jobs job ON job.job_id = emp.job_id

INNER省略

SELECT job_title, last_name FROM employees emp
 JOIN jobs job ON job.job_id = emp.job_id

15.6 非等值连接sql99

SELECT salary, employee_id ,grade_level from employees
INNER JOIN job_grades AS grades 
ON salary BETWEEN grades.lowest_sal AND grades.highest_sal

15.7 外连接

外连接
查一个表有一个表没有，主表都显示，副表和主表匹配的显示出来，没有匹配的用空填充。

15.7.1左外连接

left join 左边是主表

select beauty.`name`, boys.boyName FROM beauty
LEFT OUTER JOIN boys ON beauty.boyfriend_id = boys.id

15.7.2 右外连接

right join 右边的是主表

select beauty.`name`, boys.boyName FROM boys
RIGHT OUTER JOIN beauty ON beauty.boyfriend_id = boys.id

没有员工的部门

select emp.last_name , dept.department_id FROM departments dept 
LEFT OUTER JOIN employees emp
ON dept.department_id = emp.department_id WHERE emp.employee_id is NULL

15.7.3 交叉连接

笛卡尔积

SELECT beauty.*, boys.* FROM beauty
CROSS JOIN boys