SQL基础语法
Docker运行MySQL
$ docker run --name local-mysql -p 3306:3306 --privileged -v /path/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql
[mysqld]
default-time_zone = '+8:00'
示例数据库源
employee data (large dataset, includes data and test/verification suite)
SQL 简介
什么是空值？
SQL 约束
数据完整性
- 实体完整性：表中没有重复行。
- 域完整性：通过限制值的类型、格式或范围来强制执行给定列的有效条目。
- 引用完整性：不能删除其他记录使用的行。
- 用户定义的完整性：强制执行一些不属于实体、域或引用完整性的特定业务规则。
数据库规范化
- 消除冗余数据，例如，将相同的数据存储在多个表中。
- 确保数据依赖关系是有意义的。
SQL 语法
SELECT DISTINCT
示例：
-- 查询员工分哪些岗位
SELECT DISTINCT title
FROM titles;
title |
------------------+
Senior Engineer |
Staff |
Engineer |
Senior Staff |
Assistant Engineer|
Technique Leader |
Manager |
示例：
-- 查询有多少个岗位分工
SELECT COUNT(DISTINCT title)
FROM titles;
COUNT(DISTINCT title)|
---------------------+
7|
示例：
-- 查询有多少个岗位分工
SELECT COUNT(DISTINCT title) as count
FROM titles;
count|
-----+
7|
WHERE、运算符
算术运算符
比较运算符
逻辑运算符
示例：
select 2 * salary as salary
from salaries;
示例：
-- 【=】条件查询(查询岗位为Senior Engineer的数据)
SELECT *
FROM titles
WHERE title = 'Senior Engineer';
-- 【<>、!=】条件查询(查询岗位不为Senior Engineer的数据)
SELECT *
FROM titles
WHERE title <> 'Senior Engineer';
SELECT *
FROM titles
WHERE title != 'Senior Engineer';
-- 【>=、<=、AND、BETWEEN】条件查询(查询薪资在 salary >= 140700 AND salary <= 141000 的数据)
SELECT *
FROM salaries
WHERE salary >= 140700
AND salary <= 141000;
SELECT *
FROM salaries
WHERE salary BETWEEN 140700 AND 141000;
-- 【>、<、OR、AND】条件查询(查询薪资为 salary > 158000 OR (salary > 40010 AND salary < 40012) 的数据)
SELECT *
FROM salaries
WHERE salary > 158000
OR (salary > 40010 AND salary < 40012);
-- 【LIKE】条件查询(查询岗位中后缀带Engineer的岗位数据)
SELECT DISTINCT title
FROM titles
WHERE title LIKE '%Engineer';
-- 【in】条件查询(查询为开发或销售部门的数据)
SELECT *
FROM departments
WHERE dept_name IN ('开发', '销售');
-- 【=、in】条件查询(查询在开发部门的员工管理者姓名)
SELECT emp_no, first_name, last_name
FROM employees
WHERE emp_no in (SELECT emp_no
FROM dept_manager
WHERE dept_no in (SELECT dept_no
FROM departments
WHERE dept_name = '开发'));
-- 【not】条件查询(查询部门不为开发的数据)
SELECT *
FROM departments
WHERE NOT dept_name = '开发';
-- 【not、in】条件查询(查询部门不为开发和销售的数据)
SELECT *
FROM departments
WHERE NOT dept_name in ('开发', '销售');
示例(all、in)：
-- 查询【salary <= 155000】的【薪资数据】
select salary
from salaries
where salary <= 155000;
-- 查询【salary >= 【all (salary <= 155000)中最大值】】的【员工编号】
select distinct emp_no
from salaries
where salary >= all (select salary from salaries where salary <= 155000);
-- 查询【员工信息】在【【salary >= 【all (salary <= 155000)中最大值】】的【员工编号】】中的【员工信息数据】
select *
from employees
where emp_no in (select emp_no from salaries where salary >= all (select salary from salaries where salary <= 155000));
+--------+
| salary |
+--------+
| 60117 |
| 62102 |
| 66074 |
......
+--------+
| emp_no |
+--------+
| 43624 |
| 47978 |
| 109334 |
| 253939 |
| 254466 |
+--------+
5 rows in set (0.68 sec)
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 43624 | 1953-11-14 | Tokuyasu | Pesch | M | 1985-03-26 |
| 47978 | 1956-03-24 | Xiahua | Whitcomb | M | 1985-07-18 |
| 109334 | 1955-08-02 | Tsutomu | Alameldin | M | 1985-02-15 |
| 253939 | 1957-12-03 | Sanjai | Luders | M | 1987-04-15 |
| 254466 | 1963-05-27 | Honesty | Mukaidono | M | 1986-08-08 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (1.12 sec)
示例(exists)：
-- 查询【员工表】在【【薪资表中存在的数据】并且【薪资大于155000】】的【员工信息数据】
select *
from employees
where exists (select * from salaries where emp_no = employees.emp_no and salary > 155000);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 43624 | 1953-11-14 | Tokuyasu | Pesch | M | 1985-03-26 |
| 47978 | 1956-03-24 | Xiahua | Whitcomb | M | 1985-07-18 |
| 109334 | 1955-08-02 | Tsutomu | Alameldin | M | 1985-02-15 |
| 253939 | 1957-12-03 | Sanjai | Luders | M | 1987-04-15 |
| 254466 | 1963-05-27 | Honesty | Mukaidono | M | 1986-08-08 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.38 sec)
示例(any)：
-- 查询【员工编号 在【薪资大于等于155000的员工编号数据中】】的【员工信息数据】
select *
from employees
where emp_no = any (select distinct emp_no from salaries where salary >= 155000);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 43624 | 1953-11-14 | Tokuyasu | Pesch | M | 1985-03-26 |
| 47978 | 1956-03-24 | Xiahua | Whitcomb | M | 1985-07-18 |
| 109334 | 1955-08-02 | Tsutomu | Alameldin | M | 1985-02-15 |
| 253939 | 1957-12-03 | Sanjai | Luders | M | 1987-04-15 |
| 254466 | 1963-05-27 | Honesty | Mukaidono | M | 1986-08-08 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.40 sec)
ORDER BY 关键字
示例(创建表)：
create table order_table
(
id int auto_increment comment 'ID' primary key,
date_01 date null comment '日期 01',
date_02 date null comment '日期 02'
);
select *
from order_table;
+----+------------+------------+
| id | date_01 | date_02 |
+----+------------+------------+
| 1 | 2023-07-01 | NULL |
| 2 | 2023-07-02 | NULL |
| 3 | 2023-07-03 | 2023-07-07 |
| 4 | 2023-07-03 | 2023-07-09 |
| 5 | 2023-07-03 | 2023-07-08 |
| 6 | 2023-07-06 | NULL |
| 7 | 2023-07-05 | NULL |
| 8 | 2023-07-04 | NULL |
+----+------------+------------+
8 rows in set (0.00 sec)
示例(排序)：
-- date_01按正序排列，date_02按倒序排列
select *
from order_table
order by date_01 ASC, date_02 DESC;
+----+------------+------------+
| id | date_01 | date_02 |
+----+------------+------------+
| 1 | 2023-07-01 | NULL |
| 2 | 2023-07-02 | NULL |
| 4 | 2023-07-03 | 2023-07-09 |
| 5 | 2023-07-03 | 2023-07-08 |
| 3 | 2023-07-03 | 2023-07-07 |
| 8 | 2023-07-04 | NULL |
| 7 | 2023-07-05 | NULL |
| 6 | 2023-07-06 | NULL |
+----+------------+------------+
8 rows in set (0.00 sec)
INSERT INTO
示例：
-- 使用另一个表填充一个表
insert into order_table (date_01, date_02)
SELECT from_date, to_date
FROM dept_manager;
select *
FROM order_table;
+----+------------+------------+
| id | date_01 | date_02 |
+----+------------+------------+
| 1 | 2023-07-01 | NULL |
| 2 | 2023-07-02 | NULL |
| 3 | 2023-07-03 | 2023-07-07 |
| 4 | 2023-07-03 | 2023-07-09 |
| 5 | 2023-07-03 | 2023-07-08 |
| 6 | 2023-07-06 | NULL |
| 7 | 2023-07-05 | NULL |
| 8 | 2023-07-04 | NULL |
| 9 | 1985-01-01 | 1991-10-01 |
| 10 | 1991-10-01 | 9999-01-01 |
| 11 | 1985-01-01 | 1989-12-17 |
......
| 31 | 1992-09-08 | 1996-01-03 |
| 32 | 1996-01-03 | 9999-01-01 |
+----+------------+------------+
32 rows in set (0.01 sec)
NULL Values（空值）
示例：
select *
from order_table
where date_02 is null;
+----+------------+---------+
| id | date_01 | date_02 |
+----+------------+---------+
| 1 | 2023-07-01 | NULL |
| 2 | 2023-07-02 | NULL |
| 6 | 2023-07-06 | NULL |
| 7 | 2023-07-05 | NULL |
| 8 | 2023-07-04 | NULL |
+----+------------+---------+
5 rows in set (0.00 sec)
示例：
select *
from order_table
where date_02 is not null;
+----+------------+------------+
| id | date_01 | date_02 |
+----+------------+------------+
| 3 | 2023-07-03 | 2023-07-07 |
| 4 | 2023-07-03 | 2023-07-09 |
| 5 | 2023-07-03 | 2023-07-08 |
+----+------------+------------+
3 rows in set (0.00 sec)
UPDATE
示例：
-- 修改Staff为普通员工
update titles
set title = '普通员工'
where title = 'Staff';
+--------+-----------------+------------+------------+
| emp_no | title | from_date | to_date |
+--------+-----------------+------------+------------+
| 10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
| 10002 | 普通员工 | 1996-08-03 | 9999-01-01 |
| 10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
| 10004 | Engineer | 1986-12-01 | 1995-12-01 |
| 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
| 10005 | Senior Staff | 1996-09-12 | 9999-01-01 |
| 10005 | 普通员工 | 1989-09-12 | 1996-09-12 |
| 10006 | Senior Engineer | 1990-08-05 | 9999-01-01 |
| 10007 | Senior Staff | 1996-02-11 | 9999-01-01 |
| 10007 | 普通员工 | 1989-02-10 | 1996-02-11 |
......
Delete
示例：
delete
from order_table
where date_02 is null;
+----+------------+------------+
| id | date_01 | date_02 |
+----+------------+------------+
| 3 | 2023-07-03 | 2023-07-07 |
| 4 | 2023-07-03 | 2023-07-09 |
| 5 | 2023-07-03 | 2023-07-08 |
+----+------------+------------+
3 rows in set (0.00 sec)