Python测开28期-Camille-学习笔记-SQL语法与数据库

[Camille (吴梅)]

数据库的基本概念

• 数据库简介
• 数据库分类
• 常用数据库简介
• 使用场景
• 数据库(DataBase) 就是一个以某种有组织的方式存储的数据集合
• 是存储和管理数据的仓库
• 其本质是一个文件系统
• 数据库管理系统（DBMS）是一款管理软件

数据库分类

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

常用数据库简介

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

使用场景

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

数据库分类

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

常用数据库简介

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

使用场景

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

MySQL 的安装与配置

• Mac 系统安装 MySQL
• Mac 系统环境变量配置
• Mac 系统启动与关闭 MySQL 服务
• Windows 系统安装 MySQL
• Windows 系统环境变量配置
• Windows 系统启动与关闭 MySQL 服务
• 数据库的命令行操作

Mac 系统安装 MySQL

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

  • 百度网盘-链接不存在

  • 提取码：gxow

• 安装步骤：Mysql8.0.27.1安装 Win ｜ Mac - #4，来自 haozi

Mac 系统环境变量配置

• 进入 .bash_profile 文件
• 添加内容

export PATH=$PATH:/usr/local/mysql/bin
export PATH=$PATH:/usr/local/mysql/support-files

Mac 系统启动与关闭 MySQL 服务

• 系统偏好设置
• 点击 MySQL 图标
• 点击 Start MySQL Server 启动服务
• 点击 Stop MySQL Server 关闭服务

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
• Navicat

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

WorkBench 使用 - 创建新数据库

WorkBench 使用 - 创建表

WorkBench 使用 - 查看表数据

Navicat 安装

• 非常流行的数据库管理
• 官网下载：Navicat | 产品

Navicat 使用 - 连接数据库

Navicat 使用 - 创建新数据库

Navicat 使用 - 创建表

Navicat 使用 - 查看表数据

MySQL 目录结构

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

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

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

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;

查看数据库语法

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

-- 查看所有数据库
SHOW DATABASES;

选择数据库语法

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

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

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

实例

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

改数据库语法

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

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

修改数据库字符集

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

删除数据库语法

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

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

删除某个数据库

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

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：日期类型

创建表语法

-- 创建表
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;

查看表名语法

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

查看表结构语法

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

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

实例

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

表数据修改语法

• 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 的学

通过 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;

数据准备

• 测试数据库：
  • GitHub - datacharmer/test_db: A sample MySQL database with an integrated test suite, used to test your applications and database servers
• 网盘下载：

  • 网盘下载地址

  • 提取码：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 子句：去掉不符合其组搜索条件的各行数据行

DQL 表查询操作 - LIMIT 关键字

LIMIT 关键字

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

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

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

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) 
);

主键约束与唯一约束的区别
主键约束，唯一且不能够为空
唯一约束，唯一但是可以为空
一个表中只能有一个主键，但是可以有多个唯一约束
SQL 约束 - 默认值：
默认值
字段指定默认值
默认值
默认值约束：用来
指定某列的默认值
语法：列名 字段类型 DEFAULT 默认值
字段指定默认值
-- 创建带有默认值的表 
CREATE TABLE emp7( 
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20), 
    -- 为 sex 字段添加默认值 
    sex CHAR(1) DEFAULT '女'
);
多表查询-内连接查询：
内连接
内连接（INNER JOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。匹配上显示，匹配不上不显示。
例子： 比如使用外键=主键这个条件过滤掉无效数据
按语法结构分为： 隐式内连接和显式内连接

内连接
内连接（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 [条件]
使用右连接的方式查询出所有员工信息以及他们所在的部门名称和城市

总结
内连接: inner join
左连接: left join
右连接: right join
内连接和左连接使用居多
![image|799x607](upload://dvw8CWEPEpb26GxIzLvtED5Kd5B.png)
子查询简介：
定义：子查询指一个查询语句嵌套在另一个查询语句内部，在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;
带IN关键词的子查询
将子查询作为where语句后的过滤条件，常用于子查询结果是单列多行的情况
子查询语句必须用（）包裹
in/not in
查询出北京地区所有的员工信息
SELECT *
FROM emp_part
WHERE  dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')
带比较运算符的子查询
将子查询的结果作为过滤条件，放在比较运算符的一端
常用于子查询结果为单个结果的情况
子查询语句必须用（）包裹

#查询出薪资大于公司平均薪资的员工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);

创建数据库并导入相关数据
部门表字段
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);