SQL语法与数据库-可乔

[Abbybaby6666]

数据库的基本概念

数据库分类

• 关系型数据库（RDB: Relationship DataBase）
• 非关系型数据库（NoSQL）

常用数据库简介

• 关系型数据库（RDBMS）：
  • MySQL、Oracle、Postgres、SQLite、SQLServer
• NoSQL 数据库：MongoDB、Redis、HBase、Neo4j
• NewSQL

使用场景

• 关系型数据库
  • 需要做复杂处理数据
  • 数据量不是特别大的数据
  • 对安全性能要求高的数据
  • 数据格式单一的数据
• 非关系型数据库（NoSQL）
  • 数据模型比较简单
  • 需要灵活性更强的 IT 系统
  • 对数据库性能要求较高
  • 不需要高度的数据一致性

MySQL 的安装与配置

Windows 系统安装 MySQL

• 官方下载：https://dev.mysql.com/downloads/cluster/
• 网盘下载：

  • 百度网盘-链接不存在

  • 提取码：gxow

• 安装步骤：Mysql8.0.27.1安装 Win ｜ Mac

Windows 系统环境变量配置

• 新建系统变量 mysql，值为 mysql 安装路径
  

  

• path 变量中添加 %mysql%\bin

Windows 系统启动与关闭 MySQL 服务

• 右键此电脑选择管理
• 选择服务
• 找到MySQL 服务
• 鼠标右键选择启动或者停止
  
  

数据库的命令行操作

• 开启 mysql 服务：net start mysql

• 登录：mysql -h主机IP -u用户名 -p密码
  

• 修改密码：alter user 'root'@'localhost' identified by '密码';
  

  

• 退出：exit

• 关闭 mysql 服务：net stop mysql

数据库客户端工具

WorkBench 安装

• 官方提供的图形界面交互工具
• 官网下载：https://dev.mysql.com/downloads/workbench/
• 网盘下载：

  • 百度网盘-链接不存在

  • 提取码：gxow

WorkBench 使用 - 连接 MySQL

• Name: 可以自定义
• Method: TCP/IP
• Hostname: sql 服务地址，如果是本地就保持127.0.0.1
• Port: 3306

SQL 练习数据库

• host：mysql.hogwarts.ceshiren.com
• username：stu
• password：hogwarts_stu

MySQL 介绍

MySQL 目录结构

• 安装目录
• 配置文件：my.ini 文件
• 数据表目录：data

目录	内容
bin	可执行文件
lib	依赖库
docs	文档
include	包含文件
share	存放字符集等信息

数据库表简介

• 表：包含数据库中所有数据的数据库对象
  • 表名：每个表的唯一标识
  • 模式（schema）：关于数据库和表的布局及特性的信息
  • 列：表中每列称为一个字段
  • 行：表中的一个记录

SQL 简介

SQL 是什么

• 结构化查询语言（Structured Query Language）简称 SQL
• 一种特殊目的的编程语言
• 一种数据库查询和程序设计语言
• 用于存取数据以及查询、更新和管理关系数据库系统

通用语法

• 可以单行或者多行书写，以分号结尾 ;
• 可以使用空格和缩进来增加语句的可读性
• 不区分大小写，一般关键字大写，数据库名 表名列名小写
• 注释方式

# 单行注释，MySQL 特有的单行注释

-- 单行注释 

/*
多行注释预先格式化的文本
*/

分类

• 数据定义语言（DDL）：用来定义数据库对象，比如数据库，表，列等
• 数据操作语言（DML）：用来对数据库中表的记录进行更新
• 数据查询语言（DQL）：用来查询数据库中表的记录
• 数据控制语言（DCL）：用来定义数据库的访问权限和安全级别及创建用户

DDL 数据库操作 - 创建

创建数据库语法

• IF NOT EXISTS：可选项，创建前先判断，未存在时才执行创建语句
• 数据库名：必须指定的
• CHARACTER SET =字符集：可选项，用于指定数据库的字符集

-- 创建数据库
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名
CHARACTER SET [=]  字符集

创建数据库注意事项

• 不能与其他数据库重名
• 名称可以由任意字母、阿拉伯数字、下划线（_）和“$”组成，但不能使用单独的数字
• 名称最长可为 64 个字符，别名最长为 256 个字符。
• 不能使用 MySQL 关键字作为数据库名
• 建议采用小写来定义数据库名

创建基本数据库

-- 创建名为 test_db 的数据库
CREATE DATABASE test_db;

创建指定字符集的数据库

– 创建名为 test_db2 的数据库，并指定字符集为 utf8
CREATE DATABASE test_db2 CHARACTER SET utf8;

创建数据库前判断是否存在同名数据库

-- 如果数据库 test_db3 不存在，则创建名为 test_db3 的数据库
CREATE DATABASE IF NOT EXISTS test_db3 CHARACTER SET utf8;

DDL 数据库操作 - 查看

查看数据库语法

• DATABASES：必选项，用于列出当前用户权限范围内所能查看到的所有数据库名称
  – 查看所有数据库
  SHOW DATABASES;

选择数据库语法

– 选择数据库为当前数据库
USE 数据库名;

查看数据库的定义信息语法

– 查看数据库定义信息
SHOW CREATE DATABASE 数据库名;

实例

-- 查看当前所有数据库
SHOW DATABASES;
-- 选择数据库 test_db
USE test_db;
-- 查看 test_db 数据库的定义信息
SHOW CREATE DATABASE 数据库名;

DDL 数据库操作 - 修改

修改数据库语法

• DATABASE：必选项
• 数据库名：可选项，如果不指定要修改的数据库，那么将表示修改当前（默认）的数据库
• CHARACTER SET = 字符集：可选项，用于指定数据库的字符集

-- 修改数据库相关参数
ALTER {DATABASE} [数据库名] CHARACTER SET [=] 字符集

修改数据库字符集

-- 创建数据库 db1，指定字符集为 GBK
CREATE DATABASE db1 CHARACTER SET GBK;
-- 将数据库 db1 的字符集修改为 utf8 
ALTER DATABASE db1 CHARACTER SET utf8;

DDL 数据库操作 - 删除

删除数据库语法

• DATABASES：必选项
• IF EXISTS：用于指定在删除数据前，先判断该数据库是否已经存在，可以避免删除不存在的数据库时产生异常

-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;

删除某个数据库

-- 查看当前所有数据库
SHOW DATABASES;
-- 删除某个数据库 
DROP DATABASE test_db;
-- 如果某个数据库存在，则删除这个数据库
DROP DATABASE IF EXISTS test_db2;

DDL 数据库表操作简介

MySQL 的数据类型

• 数字类型
• 字符串类型
• 日期和时间类型

数字类型

数据类型	说明
TINTINT	0～255 或 -128～127，1字节，最小的整数
SMALLINT	0～65535 或 -32768～32767，2字节，小型整数
MEDIUMINT	0～16777215 或 -8388608～8388607，3字节，中型整数
INT	0～4294967295 或 -2147683648～2147683647，4字节，标准整数
BIGINT	8字节，大整数
FLOAT	单精度浮点值
DOUBLE	双精度浮点值
BOOLEAN	布尔值

字符串类型

数据类型	说明
CHAR	1～255 个字符，固定长度字符串
VARCHAR	长度可变，最多不超过 255 个字符
TEXT	最大长度为 64K 的变长文本
TINYTEXT	与 TEXT 相同，但最大长度为 255 字节
MEDIUMTEXT	与 TEXT 相同，但最大长度为 16K
LONGTEXT	与 TEXT 相同，但最大长度为 4GB

日期和时间类型

数据类型	说明
DATE	日期，格式 YYYY-MM-DD
TIME	时间，格式 HH:MM:SS
DATETIME	日期和时间，格式 YYYY-MM-DD HH:MM:SS
TIMESTAMP	时间标签，功能和 DATETIME 相同，但范围较小
YEAR	年份可指定两位数字和四位数字的格式

常用数据类型

• INT：整型
• DOUBLE：浮点型
• VARCHAR：字符串型
• DATE：日期类型

DDL 数据库表操作 - 创建

创建表语法

– 创建表
CREATE TABLE 数据表名 (
列名1 属性,
列名2 属性…
);

列属性

• NOT NULL | NULL：该列是否允许是空值
• DEFAULT：表示默认值
• AUTO_INCREMENT：表示是否是自动编号
• PRIMARY KEY：表示是否为主键
  列名 数据类型 [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT]
  [PRIMARY KEY ] [注释]

创建学员表

• 创建在 db1 数据库中
• 表名为 student
• 包含两个字段
  • 学员 id
  • 学员姓名

– 切换到数据库 db1
USE db1;

– 创建学员表
CREATE TABLE student(
id INT,
name VARCHAR(20)
);

复制表语法

• 数据表名：表示新创建的数据表的名
• LIKE 源数据表名：必选项，指定依照哪个数据表来创建新表

-- 复制表
CREATE TABLE数据表名
{LIKE 源数据表名 | (LIKE 源数据表名)}

复制结构相同的表

-- 创建一个表结构与 student 相同的 s2 表 
CREATE TABLE s2 LIKE student;

DDL 数据库表操作 - 查看

查看表名语法

– 查看当前数据库中所有的表名
SHOW TABLES;

查看表结构语法

-- 查看表结构
DESCRIBE 数据表名;
DESCRIBE 数据表名 列名;

-- 查看表结构简写
DESC 数据表名;
DESC 数据表名 列名;

实例

-- 选择数据库
USE db1;
-- 查看数据库中的表
SHOW TABLES;
-- 查看学员表的表结构
DESC student;
-- 查看学员表中 name 列的信息
DESC student name;

DDL 数据库表操作 - 修改

添加新列

-- 添加新列
ALTER TABLE 表名 ADD 列名 列属性;

# 实例
-- 选择数据库 db1
USE db1;
-- 添加新列
ALTER TABLE student ADD email varchar(50) NOT NULL;
-- 查看表结构
DESC student;

修改列定义

-- 修改列定义
ALTER TABLE 表名 MODIFY 列名 列属性;

# 实例
-- 添加分数列，先定义为字符类型
ALTER TABLE student ADD score varchar(10);
-- 修改字段类型
ALTER TABLE student modify score int;
-- 查看表结构
DESC student;

修改列名

-- 修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

# 实例
-- 修改列名并指定列的默认值
ALTER TABLE student
CHANGE COLUMN name stu_name VARCHAR(30) DEFAULT NULL;
-- 查看表结构
DESC student;

删除列

-- 删除列
ALTER TABLE 表名 DROP 列名;

# 实例
-- 将数据表 student 中的列 score 删除
ALTER TABLE student DROP score;
-- 查看表结构
DESC student;

修改表名

-- 修改表名方式一
ALTER TABLE 旧表名 RENAME AS 新表名;

-- 修改表名方式二
RENAME TABLE 旧表名 To 新表名;

# 实例
-- 将数据表 student 更名为 stu
ALTER TABLE student RENAME AS stu;
-- 将数据表 stu 更名为 stu_table
RENAME TABLE stu TO stu_table;
-- 查看表名
SHOW TABLES;

DDL 数据库表操作 - 删除

删除表语法

• IF EXISTS：可选项，先判断是否存在要删除的表，存在时才执行删除操作
• 数据表名：用于指定要删除的数据表名

DROP TABLE [IF EXISTS] 数据表名;

实例

-- 切换到数据库 db1 
USE db1;

-- 创建 student 表
CREATE TABLE student( 
  id INT,
  name VARCHAR(20) 
);

-- 直接删除 student 表 
DROP TABLE student;

-- 先判断再删除 student 表 
DROP TABLE IF EXISTS student;

DML 表数据操作 - 插入

表数据插入语法

• INTO 数据表名：指定被操作的数据表
• (列名1, 列名2…)：可选项，向数据表的指定列插入数据
• VALUES(值1, 值2…)：需要插入的数据

-- 插入数据
INSERT INTO 数据表名 
(列名1, 列名2...)
VALUES(值1, 值2...);

完整插入数据

向数据表中插入一条完整的数据

-- 选择数据库
USE db1;

-- 创建 user 表
CREATE TABLE user( 
  id INT,
  name VARCHAR(20),
  age INT, 
  sex CHAR(1), 
  address VARCHAR(40)
);

-- 插入一条完整数据，写出全部列名
INSERT INTO user (id,name,age,sex,address) 
VALUES(1,'张三',20,'男','北京');

-- 插入一条完整数据，不写列名
INSERT INTO user
VALUES(2,'李四',22,'女','上海');

插入数据记录的一部分

只插入表的一行中的某几个字段的值

-- 插入表一行中的某几列的值
INSERT INTO user (id,name,address) 
VALUES(3,'王五','深圳');

插入多条记录

一次性插入多条数据记录

-- 一次插入多条数据
INSERT INTO user (id,name,address)
VALUES(4,'赵六','天津'),(5,'小红','成都'),(6,'小白','杭州');

注意事项

1. 值与字段必须要对应，个数相同并且数据类型相同
2. 值的数据大小，必须在字段指定的长度范围内
3. VARCHAR CHAR DATE 类型的值必须使用单引号包裹
4. 如果要插入空值，可以忽略不写，或者插入 NULL
5. 如果插入指定字段的值，必须要上写列名

DML 表数据操作 - 修改

表数据修改语法

• SET 子句：必选项，用于指定表中要修改的字段名及其字段值
• WHERE 子句：可选项，用于限定表中要修改的行

-- 修改表中数据
UPDATE 数据表名
SET 列名1=值1 [, 列名2=值2...]
[WHERE 条件表达式]

实例

-- 选择 db1 为当前数据库
USE db1;

-- 创建 student 表
CREATE TABLE student( 
  id INT,
  name VARCHAR(20),
  sex CHAR(1),
  age TINYINT,
  city VARCHAR(50)
);

-- 插入 5 条数据
INSERT INTO student
VALUES(1,'小李','男', 18, '北京'),
(2,'小白','女', 20, '成都'),
(3,'小王','男', 23, '上海'),
(4,'小赵','女', 21, '深圳'),
(5,'小周','男', 25, '杭州');

-- 不带条件修改，将所有的性别改为女
UPDATE student SET sex = '女';

-- 带条件的修改，将 id 为 3 的学生，性别改为男
UPDATE student SET sex = '男' WHERE id = 3;

-- 一次修改多个列， 将 id 为 2 的学员，年龄改为 30，地址改为 北京
UPDATE student SET age = 30, city = '北京' WHERE id = 2;

DML 表数据操作 - 删除

通过 DELETE 语句删除数据

• 数据表名：指定要删除的数据表的表名
• WHERE 子句：限定表中要删除的行

-- 删除表中指定行的数据
DELETE FROM 数据表名
WHERE 条件表达式

通过 TRUNCATE TABLE 语句删除数据

-- 删除表中全部数据
TRUNCATE TABLE 数据表名

实例

-- 选择 db1 为当前数据库
USE db1;

# 数据准备
-- 创建 student 表
CREATE TABLE student( 
  id INT,
  name VARCHAR(20),
  sex CHAR(1),
  age TINYINT,
  city VARCHAR(50)
);

-- 插入 5 条数据
INSERT INTO student
VALUES(1,'小李','男', 18, '北京'),(2,'小白','女', 20, '成都'),(3,'小王','男', 23, '上海'),(4,'小赵','女', 21, '深圳'),(5,'小周','男', 25, '杭州');

-- 删除 id 为 1 的数据 
DELETE FROM student WHERE id = 1;

-- 删除 student 表中所有数据
DELETE FROM student;

-- 删除 student 表中所有数据（更推荐使用这个方法删除表数据，效率更高）
TRUNCATE TABLE student;

DQL 表查询操作 - 简介

数据准备

• 测试数据库：
  • https://github.com/datacharmer/test_db
• 网盘下载：

  • 网盘下载地址

  • 提取码：gxow

cd 数据所在目录
mysql -h 127.0.0.1 -uroot -p < employees.sql

格式刷

单表查询

• 单表查询：从一张表中查询所需要的数据，所有查询操作都比较简单
• * 代表所有的列
• 语法：SELECT * FROM 表名;

-- 查询部门表中的信息
SELECT * FROM departments;

字段查询

• 查询多个字段（列），可以使用 , 对字段进行分隔
• 语法：SELECT 列名 FROM 表名;

-- 查询部门的名称
SELECT dept_name FROM departments;

起别名

• 为表起别名：
  • SELECT 列名 FROM 表名 表别名;
• 为字段起别名：
  • SELECT 列名 AS 别名 FROM 表名;

-- 查询员工信息，并将列名改为中文
SELECT 
    emp_no AS '编号',
    first_name AS '名',
    last_name AS '姓',
    gender AS '性别',
    hire_date AS '入职时间'
FROM
    employees emp;

去重

• DISTINCT 关键字：去掉重复信息
• 语法：SELECT DISTINCT 列名 FROM 表名;

-- 去掉重复职级信息 
SELECT DISTINCT title FROM titles;

运算查询

• 查询结果参与运算
  • SELECT (列名 运算表达式) FROM 表名;

-- 所有员工的工资 +1000 元进行显示
SELECT emp_no , salary + 1000 FROM salaries;

DQL 表查询操作 - 条件查询

条件查询语法

-- 条件查询
SELECT 列名 FROM 表名 WHERE 条件表达式

比较运算符

运算符	说明
> < <= >= = <> !=	大于、小于、小于等于、大于等于、等于、不等于
BETWEEN…AND…	范围限定
IN	子集限定
LIKE ‘%or%’	模糊查询
IS NULL	为空

比较大小

• 语法：WHERE <列名> [> < <= >= = <> !=] <值>

-- 查询出生日期晚于 1965-01-01 的员工编号、姓名和生日
SELECT 
    emp_no, first_name, last_name, birth_date
FROM
    employees
WHERE
    birth_date > '1965-01-01';

使用 BETWEEN 进行模糊查询

• 语法：WHERE <列名> [NOT] BETWEEN <起始表达式> AND <结束表达式>
• <起始表达式> 和 <结束表达式> 的顺序不能颠倒

-- 查询年薪介于 70000 到 70003 之间的员工编号和年薪
SELECT 
    emp_no, salary
FROM
    salaries
WHERE
    salary BETWEEN 70000 AND 70003;

使用 IN 进行模糊查询

• 语法：WHERE <列名> IN <(常量列表)>
• (常量列表) 中各常量值用逗号隔开

-- 查询入职日期为 1995-01-27 和 1995-03-20 日的员工信息
SELECT 
    *
FROM
    employees
WHERE
    hire_date IN ('1995-01-27', '1995-03-20');

判断是否为空

• 语法：WHERE <列名> IS [NOT] NULL

-- 选择 hog_demo 为当前数据库
USE hog_demo;

-- 更新 student 表中 id 为 2 的 age 值为 NULL
UPDATE student SET age = NULL WHERE id = 2;

-- 查询学生表中年龄为 NULL 的学生信息
SELECT 
    *
FROM
    student
WHERE
    age IS NULL;

逻辑运算符

运算符	说明
AND &&	多个条件同时成立
OR ｜｜	多个条件任一成立
NOT	不成立

-- 查询名字为 Lillian 并且姓氏为 Haddadi 的员工信息
SELECT 
    *
FROM
    employees
WHERE
    first_name = 'Lillian'
        AND last_name = 'Haddadi';

-- 查询名字为 Lillian 或者姓氏为 Terkki 的员工信息
SELECT 
    *
FROM
    employees
WHERE
    first_name = 'Lillian'
        OR last_name = 'Terkki';

-- 查询名字为 Lillian 并且性别不是女的员工信息
SELECT 
    *
FROM
    employees
WHERE
   first_name = 'Lillian'
   and not gender='F';

通配符

运算符	说明
%	匹配任意多个字符
-	匹配一个字符

-- 查询名字中包含 fai 的员工的信息
SELECT 
    *
FROM
    employees
WHERE
    first_name LIKE '%fai%';

-- 查询名字中 fa 开头的名字长度为 3 位的员工信息
SELECT 
    *
FROM
    employees
WHERE
    first_name LIKE 'fa_';

DQL 表查询操作 - 排序

排序语法

• ASC 表示升序排序（默认）
• DESC 表示降序排序

-- 对查询结果进行排序
SELECT 列名 FROM 表名 
[WHERE 条件表达式] 
ORDER BY 列名1 [ASC / DESC],
列名2 [ASC / DESC]

单列排序

• 只按照某一个列进行排序, 就是单列排序

-- 使用 salary 字段,对 salaries 表数据进行升序排序
SELECT * FROM salaries ORDER BY salary;

-- 使用 salary 字段,对 salaries 表数据进行降序排序
SELECT * FROM salaries ORDER BY salary DESC;

-- 查询员工的编号和入职日期，按照员工入职日期从晚到早排序
SELECT 
    emp_no, hire_date
FROM
    employees
ORDER BY hire_date DESC;

组合排序

• 同时对多个字段进行排序
• 如果第一个字段相同，就按照第二个字段进行排序

-- 在入职时间排序的基础上，再使用 emp_no 进行排序
-- 组合排序 
SELECT 
    emp_no, hire_date
FROM
    employees
ORDER BY hire_date DESC, emp_no DESC;

DQL 表查询操作 - 聚合函数

聚合函数

• COUNT()：统计指定列不为 NULL 的记录行数
• MAX()：计算指定列的最大值
• MIN()：计算指定列的最小值
• SUM()：计算指定列的数值和
• AVG()：计算指定列的平均值

聚合查询

• 语法：SELECT 聚合函数(列名) FROM 表名;

-- 查询职级名称为 Senior Engineer 的员工数量
SELECT 
    COUNT(*)
FROM
    titles
WHERE
    title = 'Senior Engineer';

-- 查询员工编号为 10002 的员工的最高年薪
SELECT 
    MAX(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

-- 查询员工编号为 10002 的员工的最低年薪
SELECT 
    MIN(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

-- 查询员工编号为 10002 的员工的薪水总和
SELECT 
    SUM(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

-- 查询员工编号为 10002 的员工的平均年薪
SELECT 
    AVG(salary)
FROM
    salaries
WHERE
    emp_no = 10002;

DQL 表查询操作 - 分组

分组查询语法

• 分组列：按哪些列进行分组
• HAVING：对分组结果再次过滤

-- 分组查询
SELECT 分组列/聚合函数 FROM 表名 
GROUP BY 分组列 
[HAVING 条件];

实例

-- 查询每个员工的薪资和
SELECT 
    emp_no, SUM(salary)
FROM
    salaries
GROUP BY emp_no;

-- 查询员工编号小于 10010 的，薪资和小于 400000 的员工的薪资和
SELECT 
    emp_no, SUM(salary)
FROM
    salaries
WHERE
    emp_no < 10010
GROUP BY emp_no
HAVING SUM(salary) < 400000;

子句区别

• WHERE 子句：从数据源中去掉不符合其搜索条件的数据
• GROUP BY 子句：搜集数据行到各个组中，统计函数为各个组计算统计值
• HAVING 子句：去掉不符合其组搜索条件的各行数据行

where：是在分组之前进行过滤
group by：是用来进行分组
having：是在分组之后再进行过滤

DQL 表查询操作 - LIMIT 关键字

LIMIT 关键字

• 限制查询结果的数量
  • 开始的行数：从 0 开始记数, 如果省略则默认为 0
  • 查询记录的条数：返回的行数

-- 限制查询结果行数
SELECT 列名1, 列名2... 
FROM 表名 
LIMIT [开始的行数], <查询记录的条数>

-- 使用 OFFSET 关键字指定开始的行数
SELECT 列名1, 列名2... 
FROM 表名 
LIMIT <查询记录的条数> OFFSET <开始的行数>

实例

-- 展示前 10 条员工信息
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 0, 10;
SELECT * FROM employees LIMIT 10 OFFSET 0;

-- 显示年薪从高到低排序，第 15 位到第 20 位员工的编号和年薪
SELECT 
    emp_no, salary
FROM
    salaries
ORDER BY salary DESC
LIMIT 14, 6;

SELECT 
    emp_no, salary
FROM
    salaries
ORDER BY salary DESC
LIMIT 6 OFFSET 14;

单表查询总结

-- 基础查询语法
SELECT DISTINCT <列名>
FROM <表名>
WHERE <查询条件表达式>
GROUP BY <分组的列名>
HAVING <分组后的查询条件表达式>
ORDER BY <排序的列名> [ASC / DESC]
LIMIT [开始的行数], <查询记录的条数>

SQL 语句执行顺序

@startuml
autonumber

原始数据库 -> 虚拟表1: FROM 子句
虚拟表1 -> 虚拟表2: WHERE 子句
虚拟表2 -> 虚拟表3: GROUP BY 子句
虚拟表3 -> 虚拟表4: HAVING 子句
虚拟表4 -> 虚拟表5: SELECT
虚拟表5 -> 虚拟表6: DISTINCT
虚拟表6 -> 虚拟表7: ORDER BY 子句
虚拟表7 -> 最终结果: LIMIT

@enduml

SQL 约束 - 主键约束

SQL 约束

• 对表中的数据进行进一步的限制
• 保证数据的正确性、有效性、完整性
• 违反约束的不正确数据无法插入到表中
• 常见的约束
  • 主键：PRIMARY KEY
  • 非空：NOT NULL
  • 唯一：UNIQUE
  • 默认：DEFAULT
  • 外键：FOREIGN KEY

主键约束

• 主键：一列（或一组列），其值能够唯一标识表中每一行
• 特点：不可重复，唯一，非空
• 语法：列名 字段类型 PRIMARY KEY

添加主键约束

-- 创建一个带主键的表
CREATE TABLE emp1(
    -- 设置主键 唯一 非空 
    eid INT PRIMARY KEY, 
    ename VARCHAR(20), 
    sex CHAR(1)
);

-- 给存在的表添加主键
CREATE TABLE emp2( 
    eid INT , 
    ename VARCHAR(20), 
    sex CHAR(1) 
)

-- 通过 DDL 语句进行设置 
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

主键非空

主键不唯一

自增：

创建主键自增的表

• AUTO_INCREMENT：表示自动增长(字段类型必须是整数类型)

-- 创建主键自增的表 
CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    ename VARCHAR(20), 
    sex CHAR(1) 
);

修改主键自增的起始值

-- 创建主键自增的表,自定义自增其实值 
CREATE TABLE emp4( 
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    ename VARCHAR(20), 
    sex CHAR(1) 
)AUTO_INCREMENT=100;

删除主键约束

-- 删除表中的主键
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 使用 DDL 语句删除表中的主键 
ALTER TABLE emp2 DROP PRIMARY KEY; 
-- 查看表结构
DESC emp2;

选择主键原则

• 针对业务设计主键，往建议每张表都设计一个主键
• 主键可以没有业务意义，只需要保证不重复

SQL 约束 - 非空约束

非空约束

• 非空约束特点: 某一列不予许为空
• 语法：列名 字段类型 NOT NULL

添加非空约束

-- 添加非空约束
CREATE TABLE emp5( 
    eid INT PRIMARY KEY AUTO_INCREMENT, 
    -- ename 字段不能为空 
    ename VARCHAR(20) NOT NULL, 
    sex CHAR(1) 
);

SQL 约束 - 唯一约束

唯一约束

• 唯一约束: 表中的某一列的值不能重复
• 对 NULL 不做唯一的判断
• 语法：列名 字段类型 UNIQUE

添加唯一约束

-- 创建带有唯一约束的表  
CREATE TABLE emp6(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- 为 ename 字段添加唯一约束
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1) 
);

主键约束与唯一约束的区别

1. 主键约束，唯一且不能够为空
2. 唯一约束，唯一但是可以为空
3. 一个表中只能有一个主键，但是可以有多个唯一约束

SQL 约束 - 默认值

默认值

• 默认值约束：用来指定某列的默认值
• 语法：列名 字段类型 DEFAULT 默认值

字段指定默认值

-- 创建带有默认值的表 
CREATE TABLE emp7( 
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20), 
    -- 为 sex 字段添加默认值 
    sex CHAR(1) DEFAULT '女'
);

多表简介

多表及应用场景介绍

• 多表顾名思义就是在数据库设计中使用多张表格来实现数据存储的要求
• 在实际的项目开发中，数据量大而且复杂，需要分库分表
• 分表：按照一定的规则，对原有的数据库和表进行拆分
• 表与表之间可以通过外键建立连接

多表设计案例

• 假定我们现在需要创建一张员工信息表，包含字段：
  • eid 员工ID (自增主键)
  • ename 员工姓名
  • age 年龄
  • gender 性别
  • dept_name 所在部门
  • dept_id 部门ID
  • dept_manager 部门主管
  • dept_location 所在地点

以单表的形式完成建表

• 创建员工信息表

CREATE TABLE emp(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),  
dept_name VARCHAR(20),  
dept_id INT,  
dept_manager VARCHAR(20),  
dept_location VARCHAR(20)  
);  

插入数据

INSERT INTO emp VALUES (1,'张三', 20, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location)
VALUES ('李四', 25, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('宋江', 40, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('林冲', 25, '男','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('林徽因', 25, '女','研发部',1,'张无忌','北京');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('周芷若', 25, '女','运营部',2,'赵敏','深圳');
INSERT INTO emp(ename, age,gender,dept_name,dept_id,dept_manager, dept_location) 
VALUES ('任盈盈', 25, '女','运营部',2,'赵敏','深圳');

单表数据冗余

多表设计模式

• 将数据拆分为员工信息表employee和部门信息表dept
• 两个表之间通过部门id：dept_id字段连接

# 创建员工信息表
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT
);

# 创建部门表
CREATE TABLE dept(  
id INT PRIMARY KEY AUTO_INCREMENT,  
dept_name VARCHAR(20),  
dept_manager VARCHAR(20),  
dept_location VARCHAR(20)  
); 

插入数据

# 向部门表插入数据
INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('研发部','张无忌','北京');
INSERT INTO dept(dept_name,dept_manager,dept_location) VALUES('运营部','赵敏','深圳');

# 向员工信息表插入数据
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('李四', 25, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('宋江', 40, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('张三', 20, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林冲', 25, '男',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('林徽因', 25,'女',1);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('周芷若', 25,'女',2);
INSERT INTO emp_part(ename,age,gender,dept_id) VALUES ('任盈盈', 25, '女',2);

多表关系

多表的优点

• 简化数据
• 提高复用性
• 方便权限控制
• 提高系统的稳定性和负载能力

SQL约束-外键约束

外键约束

• 主键：可以唯一标识一条记录的列

• 外键：从表中与主表的主键对应的字段

• 主表：外键所指向的表,约束其他表的表

• 从表：外键所在的表，被约束的表

• 价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

建立外键约束

• 创建表时添加外键约束：CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]
• 添加外键约束：ALTER TABLE [表名] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]

# 创建一个关联到主表的从表
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
);
# 插入一条非法数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','4')

删除外键约束

• 语法：
  • ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]
• 注意事项
  • 从表外键数据类型必须与主表的主键一致
  • 删除数据时，需先删除从表数据再删除主表的数据
  • 添加数据时先添加主表数据，再添加从表数据

![image|800x440](upload://hKONZQqTwWbvjQYEFBqfgNgxyF3.jpeg)


# 删除外键约束 

ALTER TABLE emp_part DROP FOREIGN KEY emp_dept 

# 插入一条非法数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','4') 

SELECT * FROM emp_part  

# 向主表中插入一条数据
INSERT INTO dept VALUES(2,'运营部','张三','北京') 
# 向从表中插入一条数据
INSERT INTO emp_part VALUES(1,'cindy',20,'female','2') 
# 删除主表中的数据 
DELETE FROM dept WHERE id=2

级联删除

• 删除主表数据的同时,也删除掉从表中相关的数据
  • ON DELETE CASCADE ```sql

创建员工信息表并添加级联删除的外键约束

CREATE TABLE emp_part(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
gender VARCHAR(10), dept_id INT, – 添加外键约束 CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id) – 设置允许级联删除 ON DELETE CASCADE ); # 向员工信息表中添加一条数据 INSERT INTO emp_part VALUES(1,‘cindy’,20,‘female’,‘2’) #删除主表中部门id=2的部门 DELETE FROM dept WHERE id=2 # 查看从表中的数据是否同时被删除 SELECT * FROM emp_part ```

多表关系简介

目录

• 一对多
• 多对多
• 一对一

一对多

• 定义： 主表的一条记录可以对应从表的多条记录
• 例子： 部门表，员工表
• 建表原则：在一对多关系中，多的表定位从表，设置外键指向主表

多对多

• 定义：主表的多条记录可以对应从表的多条记录
• 例子：商品信息表，客户表，订单表
• 建表原则：需要创建第三张表作为中间表，中间表需要包含两张表的主键。

一对一

• 定义：从表的一条记录对应主表的一条记录
• 例子：员工信息表与身份证表,联系方式
• 建表原则： 这种对应关系的数据，通常放在单表里

多表查询之笛卡尔积

多表查询的定义

• 定义： 通过查询多张表格获取数据，至少涉及两张表
• 数据准备：
  • 创建部门表，插入三条数据
  • 创建员工信息表添加外键约束，允许级联删除，并向三个部门插入对应的员工信息

# 创建部门信息表 
CREATE TABLE dept(  
id INT PRIMARY KEY AUTO_INCREMENT,  
dept_name VARCHAR(20),  
dept_manager VARCHAR(20),  
dept_location VARCHAR(20)  
); 
INSERT INTO dept VALUES(1,'研发部','张无忌','北京');
INSERT INTO dept VALUES(2,'运营部','赵敏','深圳');
INSERT INTO dept VALUES(3,'销售部','周芷若','成都');

# 创建员工信息表并添加级联删除的外键约束 
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
salary INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
-- 设置允许级联删除 
ON DELETE CASCADE
);

向员工信息表中插入数据

INSERT INTO emp_part VALUES(1,'令狐冲',25,'男','1',20000);
INSERT INTO emp_part VALUES(2,'任盈盈',23,'女','1',15000);  
INSERT INTO emp_part VALUES(3,'岳不群',45,'男','1',40000);
INSERT INTO emp_part VALUES(4,'任我行',40,'男','1',30000); 
INSERT INTO emp_part VALUES(5,'岳灵珊',21,'女','1',10000);
INSERT INTO emp_part VALUES(6,'赵灵儿',21,'女','2',7000); 
INSERT INTO emp_part VALUES(7,'林月如',22,'女','2',10000); 
INSERT INTO emp_part VALUES(8,'阿奴',20,'女','2',7000); 
INSERT INTO emp_part VALUES(9,'李逍遥',25,'男','2',15000); 
INSERT INTO emp_part VALUES(10,'景天',28,'男','2',20000);
INSERT INTO emp_part VALUES(11,'邱莹莹',21,'女','3',5000);
INSERT INTO emp_part VALUES(12,'关雎尔',22,'女','3',8000);
INSERT INTO emp_part VALUES(13,'曲筱绡',23,'女','3',10000);
INSERT INTO emp_part VALUES(14,'樊胜美',30,'女','3',10000);
INSERT INTO emp_part VALUES(15,'安迪',28,'女','3',20000);

笛卡尔积

• 定义: 笛卡尔积是一个数学概念，又称直积，它是指两个集合元素所有可能有序对的集合。
• 例子：
  • A={a,b},B={c,d}
  • A*B ={(a,c),(b,c),(a,d),(b,d)}
• 语法：select 字段名称 from 表1， 表2

案例

• 查询出运营部的部门信息及该部门下的员工信息
  
  

多表查询-内连接查询

内连接

• 内连接（INNER JOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。匹配上显示，匹配不上不显示。
• 例子： 比如使用外键=主键这个条件过滤掉无效数据
• 按语法结构分为： 隐式内连接和显式内连接

隐式内连接

• 在笛卡尔积的的基础上，使用where条件过滤无用的数据，这种连接方式是隐式内连接.
• 语法：select [字段名称] from 表1，表2 where [条件]
• 例1： 筛选出运营部的员工的id，姓名以及所在城市

SELECT emp_id,ename,dept_location 
FROM emp_part,dept 
WHERE dept_id=id and dept_name="运营部";

隐式内连接：

显式内连接

• 显式内连接： 使用 select [字段名称] from [表1]inner join [表2] on [条件] 这种方式
• 列子： 用显式内连接查询运营部的员工的id，姓名以及所在城市

SELECT emp_id,dept_location,ename 
FROM emp_part 
INNER JOIN dept ON dept_id=id AND dept_name="运营部"

显示内连接：

多表查询-外连接查询

外连接

• 外连接查询：查询多个表中相关联的行，有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左连接）、右表（右连接）中的所有数据行。
• 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
• 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

示例

左连接

• 左连接：以左表为基准匹配右表的数据，右表中没有的项，显示为空
• 语法：SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]
• 例子：公司新成立人力资源部，还未招聘员工，请使用左连接查询方式查询出公司所有部门员工的员工号，姓名，性别以及他们所在的部门名称和城市

#向部门表中插入人力资源部
INSERT INTO dept VALUES(4,'人力资源部','甄嬛','北京');
#查询出需要的数据
SELECT emp_id,ename,gender,dept_name,dept_location 
FROM dept LEFT JOIN emp_part ON dept.id=emp_part.dept_id

左连接：

右连接

• 右连接：以右表为基准匹配左表的数据，左表中没有的项，显示为空
• 语法：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]
• 使用右连接的方式查询出所有员工信息以及他们所在的部门名称和城市

右连接：

子查询简介

简介

• 定义：子查询指一个查询语句嵌套在另一个查询语句内部，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。 这个特性从MySQL 4.1开始引入。
• 子查询作为过滤条件时需要用（） 包裹

子查询的常见分类

• From型子查询：将子查询的结果作为父查询的表来使用
• in/not in 型子查询：子查询的结果是单列多行，作为where的过滤条件
• where型子查询：查询结果作为过滤条件出现在比较运算符的一端

带From关键词的子查询

• 子查询是一张多行多列的表，将子查询作为父查询的表来嵌套查询
• 子查询语句必须用（）包裹且需要有别名
• 计算出各部门性别为男性的员工人数

select dept_name,count(emp_id) from 
(select dept_name, emp_id,ename,gender from 
dept inner join emp_part
 where id=dept_id and gender='男')b group by dept_name;

from：

带IN关键词的子查询

• 将子查询作为where语句后的过滤条件，常用于子查询结果是单列多行的情况
• 子查询语句必须用（）包裹
• in/not in
• 查询出北京地区所有的员工信息

SELECT *
FROM emp_part
WHERE  dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')

in：

not in ：

带比较运算符的子查询

• 将子查询的结果作为过滤条件，放在比较运算符的一端
• 常用于子查询结果为单个结果的情况
• 子查询语句必须用（）包裹

#查询出薪资大于公司平均薪资的员工id,姓名及薪资
SELECT emp_id,ename,salary FROM emp_part 
WHERE salary > (SELECT AVG(salary) FROM emp_part);

with as

• 如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示出来
• 语法： with [表名] as （select…)

# 查询出部门平均薪资大于公司平均薪资的部门名称，部门主管，所在地及部门平均薪资

# 不使用 with ...as
select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_id 
and avg_salary > (select avg(avg_salary) from 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);

# 使用 with ...as
with dept_avg as
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)select
dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg 
on id=dept_id 
and avg_salary > (select avg(avg_salary) from dept_avg);

子查询实战

项目介绍

• A公司是一家软件产品销售公司，在北京，上海，深圳，成都，杭州都设有销售部门，其中销售部门分布如下：
• 北京有3个销售部门，分别为bj001，bj002，bj003
• 上海有三个销售部门为：sh001，sh002,sh003
• 深圳有两个销售部门为：sz001，sz002
• 成都有一个销售部门为：cd001
• 杭州有一个销售部门为：hz001
• department表中记录了部门相关的信息
• sales_list表中记录了最近2周各部门的销售订单相关数据

项目需求

• 需求1：在mysql中创建数据库hogwarts_db1 并导入相关数据
• 需求2：计算出各部门最近两周的的总销售业绩，并按业绩由高到低显示
• 需求3：查询出最近两周的销售额超过全公司平均销售额的部门

创建数据库并导入相关数据

• 部门表字段
  • dept_id 部门id
  • city 所在城市
  • manager 部门经理
• 订单表
  • dept_id 部门id
  • order_id 订单号
  • volume 客单价
  • sales_date 销售日期

计算销售业绩并排序

# 计算出各部门最近两周的总业绩，并按业绩由高到低排名
SELECT
    order_list.dept_id,
    city,
    manager,
    SUM( volume ) total_volume
FROM
    order_list
    INNER JOIN department ON order_list.dept_id = department.dept_id 
GROUP BY
    order_list.dept_id 
ORDER BY
    SUM( volume ) DESC

查询出最近两周销售额超平均销售额的部门

WITH temp_dept AS (
    SELECT
        order_list.dept_id,
        city,
        manager,
        SUM( volume ) total_volume 
    FROM
        order_list
        INNER JOIN department ON order_list.dept_id = department.dept_id 
    GROUP BY
        order_list.dept_id 
    ORDER BY
        SUM( volume ) DESC 
    ) SELECT
    * 
FROM
    temp_dept 
WHERE
    total_volume >(
    SELECT
        AVG( total_volume ) 
FROM
    temp_dept)

知识扩展-视图

• 定义：视图是一种虚拟的表，它并不会在你的存储空间复制一份数据，而是对原有数据的一种引用。可以将视图理解为一种存储起来的sql语句
• 视图可以简化多表查询
• 视图也可以用于控制用户权限
• 使用关键词view来创建视图
• 语法：CREATE VIEW [视图名称] AS SELECT……

使用视图简化练习

CREATE VIEW temp_dept AS (
    SELECT
        order_list.dept_id,
        city,
        manager,
        SUM( volume ) total_volume 
    FROM
        order_list
        INNER JOIN department ON order_list.dept_id = department.dept_id 
    GROUP BY
        order_list.dept_id 
    ORDER BY
        SUM( volume ) DESC 
    );
    
SELECT
    * 
FROM
    temp_dept 
WHERE
    total_volume >(
    SELECT
        AVG( total_volume ) 
FROM
    temp_dept);

#查询出最近两周的冠军销售部门

SELECT * FROM temp_dept WHERE total_volume=(SELECT max(total_volume) FROM temp_dept);

数据库进阶

优化器中的索引：

索引是一个数据结构，相当于是一本书的目录
索引是以文件的形式存储在磁盘上

一个表中可以有多个单列索引
一个表中包含有多个单列索引就是一种组合索引
全文索引可以检索一些包含一个或者多个单词的数据结构
组合索引是优于单列索引的，一个表中的索引也不要创建太多，最好不要超过5个，超过5个效率就没有那么好了。

索引本身就是一个很大的文件，有很多张表。
会提高查找的速度，但是会降低更新表的速度

经常查询或者经常排序的字段可以创建索引
表记录特别少，经常要进行增删改的表就不适合创建索引

explain

通过explain可以看到整个sql的执行情况，可以分析sql语句，让我们知道查询效率低的原因是什么

id：查询的序列号，id越大优先级越高
select_type：查询的类型
table：输出结果的结果集
type：访问类型，查询使用了什么类型
possible_keys：mysql能在表中使用哪些索引，为空表示没有索引可以用。
key：表示实际使用的索引是什么
key_len：实际使用索引的长度
ref：索引中的哪一列被使用了
rows：返回请求数据的行数
extra：其他额外的参数

事务

事务是用来处理数据量非常大，复杂度非常高的数据

日志

打开慢查询会消耗数据库性能，如果不是调优优化，不建议启动这个参数

redis 内存数据库

切换redis

mongodb nosql 数据库

非关系型数据库是存储在内存中，读写快

非关系型数据库

CPA不能三者同时使用

MongoDB

启动mongo

新建一个新的数据库

在表中插入数据

删除数据库

集合

创建/查看/删除集合

如何往集合中插入数据

文档就是集合中的数据

插入/查看文档

修改/删除文档

修改操作符

更新

neo4j 图数据库