SQL（八）：SQL基础语法-Toy模板网

这篇具有很好参考价值的文章主要介绍了SQL（八）：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 约束

用于限制可以进入表中的数据类型，约束可以是列级别，也可以是表级别（列级约束应用于一列；表级约束应用于整个表）

约束	描述
NOT NULL	保证列中数据不能有 NULL 值
DEFAULT	提供该列数据未指定时所采用的默认值
UNIQUE	保证列中的所有数据各不相同
PRIMARY KEY 主键	唯一标识数据表中的行/记录
FOREIGN KEY 外键	唯一标识其他表中的一条行/记录，一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
CHECK	此约束保证列中的所有值满足某一条件
INDEX 索引	用于在数据库中快速创建或检索数据

数据完整性

实体完整性：表中没有重复行。
域完整性：通过限制值的类型、格式或范围来强制执行给定列的有效条目。
引用完整性：不能删除其他记录使用的行。
用户定义的完整性：强制执行一些不属于实体、域或引用完整性的特定业务规则。

数据库规范化

消除冗余数据，例如，将相同的数据存储在多个表中。
确保数据依赖关系是有意义的。

SQL 语法

SELECT DISTINCT

用于仅返回不同的（different）值

示例：文章来源地址https://www.toymoban.com/news/detail-541894.html

-- 查询员工分哪些岗位
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、运算符

WHERE 子句用于提取满足指定标准的记录

算术运算符

运算符	描述
+、-、*、/、%	加、减、乘、除、余

比较运算符

运算符	描述
=	等于
<>	不等于。注意：在某些版本的 SQL 中，这个操作符可能写成!=
<	大于
>	小于
>=	大于等于
<=	小于等于
!<	不小于
!>	不大于

逻辑运算符

运算符	描述
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	为列指定多个可能的值
OR	或
AND	且
NOT	非
IS NULL	用于将值与NULL值进行比较，为空
IS NOT NULL	用于将值与NULL值进行比较，不为空
ALL	用于将值与另一个值集中的所有值进行比较
ANY	用于根据条件将值与列表中的任何适用值进行比较
EXISTS	用于搜索指定表中是否存在满足特定条件的行

示例：

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 关键字

用于按升序或降序对结果集进行排序，默认情况下按升序排序记录（ASC升序、DESC降序）

示例(创建表)：

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（空值）

具有NULL值的字段是没有值的字段，NULL 值与 0 或者包含空白（spaces）的字段是不同的，需要使用IS NULL和IS NOT NULL
运算符来判断比较null

示例：

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)

到了这里，关于SQL（八）：SQL基础语法的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！