目录
一、基本的查询语句
1.1 SELECT ... FROM
语法:
SELECT 标识选择哪些列 FROM 标识从哪个表中选择
选择全部列:
SELECT * FROM account;
选择特定的列:
SELECT id,NAME FROM account
- 注意:MySQL中的SQL语句是不区分大小写的,有些人习惯将关键字大写、数据列和表名小写,这是一个良好的编程习惯,利于阅读和维护。
1.2 列的别名
重命名一个列,可以空格然后紧跟列名;也可以在列名后面加入AS关键字,后面的别名使用双引号。
SELECT id accountId,NAME AS accountName FROM account
1.3 去除重复行
在SELECT语句中使用关键字DISTINCT去除重复行:
SELECT DISTINCT id ,NAME
FROM account
DISTINCT 需要放到所有列名的前面,如果写成 SELECT id, DISTINCT name FROM account 会报错。
1.4 空值参与运算
所有运算符或列值遇到null值,运算的结果都为null(在 MySQL 里面,空值是占用空间的)。
SELECT ename '姓名',joindate '入职时间', 12*salary '年薪' FROM emp
1.5 着重号
为了避免字段、表名没有和数据库保留字或常用方法冲突,可以在SQL语句中使用一对``(着重号)引起来。
SELECT * FROM `account`
1.6 显示表结构
使用DESCRIBE 或 DESC 命令,查看表结构:
DESCRIBE account
DESC account
各个字段的含义分别解释如下:
- Field:表示字段名称。
- Type:表示字段类型,这里 barcode、goodsname 是文本型的,price 是整数类型的。
- Null:表示该列是否可以存储NULL值。
- Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE索引的一部分;MUL表示在列中某个给定值允许出现多次。
- Default:表示该列是否有默认值,如果有,那么值是多少。
- Extra:表示可以获取的与给定列有关的附加信息,例如AUTO_INCREMENT等。
1.7 过滤数据
SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件
SELECT * FROM emp WHERE joindate > '2023-03-31'
二、多表查询
多表查询是指存在一对一、一对多关系的多个表,通过关联字段完成查询操作。
2.1 笛卡尔积
笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尔积,又称直积,表示为X×Y。假设两个集合 A 和 B,则 A 和 B 的笛卡尔积就是 A 和 B 两个集合中元素的交集。
在SQL的笛卡尔积(交叉连接)有两种,显式的和隐式的,不带ON子句,返回的是两表的乘积。
隐式的交叉连接,没有CROSS JOIN:
SELECT A.a1, A.a2, A.a3, B.b1, B.b2
FROM A , B
显式的交叉连接,使用CROSS JOIN:
SELECT A.a1, A.a2, A.a3, B.b1, B.b2
FROM A CROSS JOIN B
显式的和隐式的查询结果是相同,查询结果如下:
2.2 内连接
内连接(INNER JOIN):返回连接表中符合连接条件和查询条件的数据行,有两种,显式的和隐式的。
语法:
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
隐式的内连接,没有INNER JOIN,结果为两个表的笛卡尔积:
SELECT a1, a2, a3, b1, b2
FROM A , B
WHERE A.a1=B.b1;
显示的内连接(内连接),有INNER JOIN,结果为两个表经过ON条件过滤后的笛卡尔积。
inner join …… on 语句:
SELECT a1, a2, a3, b1, b2
FROM A
INNER JOIN B
ON A.a1=B.b1;
join …… on 语句:
SELECT a1, a2, a3, b1, b2
FROM A
JOIN B
ON A.a1=B.b1;
2.3 外连接
外连接(OUTER JOIN):外连接分三类,左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)。
左外连接:返回左表中不符合连接条件,但符合查询条件的数据行。
SELECT a1, a2, a3, b1, b2
FROM A
LEFT JOIN B
ON A.a1=B.b1;
只存在于A表 不存在与B表的记录:
SELECT a1, a2, a3, b1, b2
FROM A
LEFT JOIN B
ON A.a1=B.b1
where B.b1 is null
右外连接:返回右表中不符合连接条件,但符合查询条件的数据行。
只存在于B表 不存在与A表的记录:
SELECT a1, a2, a3, b1, b2
FROM A
RIGHT JOIN B
ON A.a1=B.b1
where A.a1 is null
只存在于A表 不存在与B表的记录:
SELECT a1, a2, a3, b1, b2
FROM A
RIGHT JOIN B
ON A.a1=B.b1
where B.b1 is null
全外连接:返回左表中不符合连接条件,但符合查询条件的数据行;并且还返回右表中不符合连接条件,但符合查询条件的数据行。
- 将多个表的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相互对应。
- 各个SELECT语句之间使用UNION或UNIONALL关键字分隔。
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION操作符:(两个查询的结果集的并集,去除重复记录。)
SELECT a1, a2, b1, b2
FROM A
OUTER JOIN B
ON A.a1=B.b1
UNION操作符,等于左外和右外的合集:
SELECT a1, a2, b1, b2
FROM A
LEFT OUTER JOIN B
ON A.a1=B.b1
UNION
SELECT a1, a2, b1, b2
FROM A
RIGHT OUTER JOIN B
ON A.a1=B.b1
UNION ALL操作符:返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
SELECT a1, a2, b1, b2
FROM A
LEFT OUTER JOIN B
ON A.a1=B.b1
UNION ALL
SELECT a1, a2, b1, b2
FROM A
RIGHT OUTER JOIN B
ON A.a1=B.b1
Natural join 操作符:
Natural join 即自然连接,等同于inner join或inner using,作用是将两个表中具有相同名称的列进行匹配。
SELECT a1, a2, b1, b2
FROM A
Natural join B
ON A.a1=B.b1
三、子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
3.1 单行子查询
单行比较操作符
操作符 | 含义 |
= | 等于 |
> | 大于 |
>= | 大于或等于 |
< | 小于 |
<= | 小于或等于 |
<> | 不等于 |
表结构:
查询工资大于员工王五的员工信息:
-- 查询工资大于员工王五的员工信息
SELECT id,ename,joindate,salary
FROM `emp`
WHERE salary > (
SELECT salary
FROM `emp`
WHERE ename ='王五'
);
HAVING 中的子查询
查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM emp
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM emp
WHERE department_id = 50);
CASE中的子查询
显式员工的d,ename和location。
其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT id, ename,
(CASE department_id
WHEN
(SELECT department_id FROM dept
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM emp;
3.2 多行子查询
多行比较操作符
操作符 | 含义 |
IN | 等于列表中的任意一个 |
ANY | 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 |
ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 |
SOME | 实际上是ANY的别名,作用相同,一般常使用ANY |
例子
查询job_id中比job_id为‘IT’部门所有工资都低的员工的员工号、姓名、job_id以及salary:
SELECT employee_id,last_name,job_id,salary
FROM em
WHERE salary< ALL(
SELECT salary
FROM em
WHERE job_id='IT')
3.3 EXISTS 与 NOT EXISTS关键字
关联子查询用 EXISTS操作符用来检查在子查询中是否存在满足条件的行:
- 如果在子查询中不存在满足条件的行:条件返回 FALSE,继续在子查询中查找。
- 如果在子查询中存在满足条件的行:不在子查询中继续查找,条件返回 TRUE。
- NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
查询dept表中,不存在于emp表中的部门的department_id和department_name:
SELECT department_id, department_name
FROM dept d
WHERE NOT EXISTS (SELECT 'XX'
FROM emp
WHERE department_id = d.department_id);
3.4 相关更新
依据一个表中的数据更新另一个表的数据。
语法:
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
3.5 相关删除
依据一个表中的数据删除另一个表的数据。
语法:
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);