SQL 语句，DCL，DDL，mysql 数据类型
关系：候选键，主键，外键，索引
约束：域约束，实体完整性，引用完整性
实体 - 联系 ER，一对多，多对多，一对一
视图
MariaDB的安装
DML 数据库操作：增加 (Create)、读取(Retrieve)、更新(Update) 和删除(Delete)
insert、update、delete 、select（limit,where,order by,distinct, 聚合函数，分组查询，子查询，连接 join）
存储过程、触发器

关系型数据库

SQL语句

• SQL是结构化查询语言Structured Query Language
• 所有主流的关系型数据库都支持SQL，NoSQL也有很大一部分支持SQL

SQL语句的分类

• DDL(Data Definition Language)：数据定义语言，负责数据库定义、数据库对象定义，由CREATE、ALTER与DROP三种语句组成
• DML(Data Manipulation Language)：数据操作语言，负责对数据库对象的操作，CRUD增删改查
• DCL(Data Control Language)：数据控制语言，负责数据库权限访问控制，由 GRANT 和 REVOKE 两个指令组成
• TCL(Transaction Control Language)：事务控制语言，负责处理ACID事务，支持commit、rollback指令

其中TCL的相关介绍请参考数据库事务

SQL语言规范

• SQL语句大小写不敏感：一般建议，SQL的关键字、函数等大写
• SQL语句末尾应该使用分号结束
• 注释
  • 多行注释 /*注释内容*/
  • 单行注释 -- 注释内容
  • MySQL 注释可以使用#
  • 其他
    • 可使用空格或缩进来提高可读性
    • 命名规范：必须以字母开头，可以使用数字、#、$和_，不可使用关键字

DCL(Data Control Language)

• GRANT授权
• REVOKE撤销

# 授权jerry用户(to jerry）在任意地址(@'%')上databasename下所有表(databasename.*)的所有权限(all)
GRANT ALL ON employees.* TO 'jerry'@'%' IDENTIFIED by 'jerry';
REVOKE ALL ON *.* FROM jerry;

* 为通配符，指代任意库或者任意表。*.* 所有库的所有表； employees.* 表示employees库下所有的表，% 为通配符，它是SQL语句的通配符，匹配任意长度字符串

DDL(Data Definition Language)

• 删除用户

DROP USER jerry;

• 创建数据库和删除数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE IF EXISTS test;

• 创建表
  • 行Row，也称为记录Record，元组
  • 列Column，也称为字段Field、属性
  • 字段的取值范围叫做 域Domain

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

反引号标注的名称，会被认为是非关键字，使用反引号避免冲突

• DESC
  查看列信息DESC tbl_name <col_name>

DESC employees;
DESC employees '%name';

MySQL数据类型

char可以将字符串定义为固定长度，空间换时间，效率略高；varchar为变长，省了空间

可视化操作工具

• 以上创建表操作一般通过可视化工具完成
• 如Navicat、DataGrip等

关系和约束

关系

• 关系就是二维表，由行和列组成
• 关系的维数：关系中列 (属性) 的个数
• 关系的基数：关系中行 (元组) 的个数

候选键

• 关系中，能唯一标识一条行（元组）的列属性或属性集合，称为候选键
• 简单说就是能够唯一确定某一行的列属性
• 候选键可以有多个

这里 id 和 id_no（身份证号）都可以作为候选键
class_no+s_id 联合起来也可以作为候选键（班级 + 学号可唯一确定某一行）

主键 PRIMARY KEY

• 表中一列或者多列组成唯一的 key，也就是通过这一个或者多个列能唯一的标识一条记录
• 简单说就是从候选键中挑一个出来，作为主键即可
• 主键的列不能包含空值 null，主键往往设置为整型、长整型，可以为自增AUTO_INCREMENT字段
• 表中可以没有主键，但是，一般表设计中，往往都会有主键，以避免记录重复

外键 Foreign KEY

• 严格来说，当一个关系中的某个属性或属性集合与另一个关系（也可以是自身）的候选键匹配时，就称作这个属性或属性集合是外键
• 简单可以理解为 B 表中引用了 A 表的候选键（往往是主键），则 B 表中的列就是外键
• A 表称为主表，B 表称为从表

索引 Index

• 空间换时间，显著提高查询效率
• 可以对一列或者多列字段设定索引
• 主键索引：主键会自动建立主键索引，唯一且不能为空
• 唯一索引：表中的索引列组成的索引必须唯一，但可以为空，非空值必须唯一
• 普通索引：没有唯一性的要求，就是建了一个字典的目录而已
• 在 MySQL 中，InnoDB 和 MyISAM 的索引数据结构可以使用 Hash 或 BTree，默认是 BTree，以 B + 树为存储结构
• 建索引影响增删改的效率，因为需要索引更新或重构
• 频繁出现在 where 子句中的列可以考虑使用索引

总结

• 主键（Primary Key）是用来唯一标识每一行记录的一列或一组列，它具有唯一性、非空性、不变性等特点。每个表只能有一个主键，主键可以自动创建索引，通过主键查询数据的效率很高
• 外键（Foreign Key）是一个表中的某个字段，它指向另一个表中的主键，用于建立表与表之间的关系。外键可以保证数据的完整性和一致性，但是在查询、插入和更新等操作中可能会影响性能
• 索引（Index）是数据库中用来提高查询速度的一种数据结构，它是对表中某些列的值进行排序的一种结构，可以理解为是一张表的快速查找入口。索引分为聚集索引和非聚集索引，其中聚集索引是按照主键排序的索引，而非聚集索引则是按照其他列排序的索引
  在使用主键、外键和索引时，需要注意一些细节问题。例如，主键不能重复，否则会破坏表的一致性；外键需要建立表与表之间的关系，并且需要进行索引优化；索引的建立需要考虑到查询的频率、数据的变化率等因素，避免过多或过少的索引对性能产生负面影响
• 为了提高查询速率，索引越多越好吗？
  并不是索引越多越好。虽然索引可以提高查询速率，但是索引也会占用磁盘空间，并增加插入、更新、删除操作的开销。如果过多地使用索引，可能会导致数据库的性能下降，甚至出现一些异常情况，如死锁等
  另外，索引还有一些其他的限制和注意事项，如单个索引大小的限制、使用索引的顺序、索引的选择等。在使用索引时，需要根据具体情况进行权衡和优化，以达到最优的性能和存储空间利用效率
• 索引一定唯一吗？
  作为索引的键不一定是唯一的，可以包含重复的值，这种索引被称为非唯一索引。但是，唯一索引在插入新数据时需要做额外的唯一性检查，因此可能会降低插入数据的速度。非唯一索引可以用于加快查询速度，但需要注意的是，它可能会导致以下问题：

1. 查询结果不准确：当使用非唯一索引进行查询时，返回的结果可能会包含重复的数据
2. 查询性能下降：如果非唯一索引中包含大量重复的数据，那么查询性能可能会下降，因为数据库需要扫描更多的数据块
3. 索引占用更多的空间：如果索引中包含大量重复的数据，那么它会占用更多的存储空间，从而增加数据存储的开销

约束 Constraint

为了保证数据的完整和正确，数据模型必须支持完整性约束。如设置某列值不能为空NOT NULL

域约束 Domain Constraint

• 限定了表中字段的取值范围

实体完整性 Entity Integrity

• PRIMARY KEY 约束定义了主键，就定义了主键约束
• 主键不重复且唯一，不能为空

引用完整性 Referential Integrity

• 外键约束
  class 表 A：

student 表 B：

B 表 class_no 引用了 A 表中的主键 id，class_no 即为外键
A 表为主表，B 表称为从表

1. 插入规则
   • 不需要指定
   • 在 B 表中插入一条记录，B 的外键列插入了一个值，这个值必须是表 A 中存在的主键值
2. 更新和删除规则
   • 定义外键约束时指定该规则
   • 外键约束操作

• 举例说明
  还是上面的 A 表和 B 表：
  • CASCADE：删除 / 更新 A 表中的 id 1，则 B 表中的 tom 所在行 class_no 会被删除 / 更新
  • SET NULL：删除 / 更新 A 表中的 id 1，则 B 表中的 tom 所在行 class_no 会被设置成 NULL 或更新
  • RESTRICT：删除 / 更新 A 表中的 id 1，由于 B 表中的 tom 所在行 class_no 引用了该 id，则拒绝 A 表做删除或更新操作
  • NO ACTION：同上
• 外键约束作用
  为了保证数据完整性和一致性，杜绝数据冗余和错误

数据库的“实体-联系(E-R)”

数据库建立，需要收集用户需求，设计符合企业要求的数据模型。而构建这种模型需要方法，这种方法需要成为E-R实体-联系建模。也就出现了一种建模语言——UML（Unified Modeling Language）统一建模语言

• 实体(Entity)：现实世界中具有相同属性的一组对象，可以是物理存在的事物或者抽象的事物
• 联系(Relationship)：实体之间的关联集合
• 简单理解就是建立表与表之间的关系

一对多联系

如 一个员工属于一个部门，一个部门有多个员工
部门表 A，部门 id 为主键
员工表 B，部门 id 为外键

多对多联系

如一个员工属于多个部门，一个部门有多个员工
部门表 A，部门 id 为主键
员工表 B，部门 id 为外键
部门 - 员工表 C，部门 id 和员工 id 组成联合主键

一对一联系

如一个管理者管理一个部门，一个部门只有一个管理者

• 分表，将一张表多列分割并产生成了多张表，合起来是完整的信息
• 或为了方便查询，或为了数据安全隔离一部分字段的数据等

视图

视图，也称虚表

• 由 SQL 语句生成，一般用可视化工具完成
• 可以进一步通过视图进行 CRUD 操作

视图的作用

• 简化操作，将复杂查询 SQL 语句定义为视图，可以简化查询
• 数据安全，可以只显示真实表的部分列或计算后的结果，从而隐藏真实表的数据

MariaDB的安装（CentOS)

# yum list | grep mariadb
# yum install mariadb-server          # 安装mariadb 服务会自动安装mariadb
# systemctl start mariadb-service
# ss -tanl
# systemctl enable mariadb-service     # 开机启动
# mysql_secure_installation      # 安全设置mysql服务
# mysql -u root -p
mysql> grant all on *.* to 'jerry'@'%' identified by 'jerry';
mysql> flush privileges;

# mysql -u root -p < test.sql  # 导入测试脚本

如果不能远程连接，查看是否关闭了虚拟机的防火墙

• systemctl status firewalld.service查看防火墙状态
• systemctl disable firewalld.service永久关闭

官方测试数据库

• 官方testdb
• 导入官方测试数据库

# 我提前的远程机器上根目录创建了testdb目录
# 拷贝下载到本地测试数据库文件到远程虚拟机（在本地执行(我的mac))
$ scp ~/Downloads/test_db-1.0.7.tar.gz root@10.15.45.50:/testdb

# 连接远程虚拟机并解压文件
# cd /testdb
# tar -zxvf test_db-1.0.7.tar.gz
# cd testdb

# mysql -uroot -p < employees.sql  # 导入数据库
# mysql -u root -p -t < test_employees_md5.sql  # 测试数据库

DML(Data Manipulation Language)

关系操作

关系：关系数据库中，关系就是二维表
关系操作：对表操作

• 选择 (selection)
  又称限制，是从关系中选择出满足给定条件的元组（行）
• 投影 (projection)
  从选择出若干属性列组成新的关系
• 连接 (join)
  将不同的两个关系连接成一个关系

insert 语句

-- 自增字段、缺省字段、可为空的字段可以不写
INSERT INTO reg (`loginname`, `name`, `password`) VALUES ('jerry', 'jerry', 'jerry');
-- 如果主键冲突、唯一键冲突就执行update后的设置
INSERT INTO reg (id, loginname, `name`, `password`) VALUES (1, 'tom', 'tom', 'tom') ON DUPLICATE KEY UPDATE name = 'jerry';
-- 如果主键冲突、唯一键冲突就忽略错误，返回一个警告
INSERT IGNORE INTO reg (id, loginname, `name`, `password`) VALUES (1, 'tom', 'tom', 'tom');

update 语句

-- 更新一定要加条件否则更新所有数据
UPDATE reg SET name = 'mali', password = 'mali' WHERE id = 1;

delete 语句

-- 删除一定要有一定条件
DELETE FROM reg WHERE id = 1;

select 语句

SELECT    
    [DISTINCT]    
    select_expr, ...    
    [FROM table_references
    [WHERE where_definition]    
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]] 
    [HAVING where_definition]    
    [ORDER BY {col_name | expr | position}     
        [ASC | DESC] , ...]    
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]    
    [FOR UPDATE | LOCK IN SHARE MODE]]

-- 字符串合并CONCAT
SELECT emp_no, CONCAT(first_name,' ',last_name) FROM employees;
-- AS 定义别名，可选
SELECT emp_no a, CONCAT(first_name,' ',last_name) as name FROM employees;

limit

-- 返回5条记录
SELECT * FROM employees emp LIMIT 5;
-- 返回5条记录，偏移18条，返回第19-23条
SELECT * FROM employees emp LIMIT 5 OFFSET 18;
SELECT * FROM employees emp LIMIT 18, 5;

where

-- 运算符包含= <> > < <= >= BETWEEN AND LIKE IN AND OR
-- 条件查询
SELECT * FROM employees WHERE emp_no < 10015 and last_name LIKE 'P%';
SELECT * FROM employees WHERE emp_no BETWEEN 10010 AND 10015 AND last_name LIKE 'P%';
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010);

order by

-- 降序，默认升序ASC，可不写
SELECT * FROM employees WHERE emp_no in (10001, 10002, 10010) ORDER BY emp_no DESC;

distinct

• 不返回重复记录

-- DISTINCT使用
SELECT DISTINCT dept_no from dept_emp;

聚合函数

• count(expr): 返回记录中记录的数目，如果指定列，则返回非 NULL 值的行数
• count(distinct expr): 返回不重复的非 NULL 值的行数
• avg: 返回平均值，返回不同值的平均值
• min,max: 最大值，最小值
• sum: 求和

-- 聚合函数
SELECT COUNT(*), AVG(emp_no), SUM(emp_no), MIN(emp_no), MAX(emp_no) FROM employees;

分组查询

• group by
• having

-- 聚合所有
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries;
-- 聚合被选择的记录
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries WHERE emp_no < 10003;
-- 分组
SELECT emp_no FROM salaries GROUP BY emp_no;
SELECT emp_no FROM salaries WHERE emp_no < 10003 GROUP BY emp_no;
-- 按照不同emp_no分组，每组分别聚合，分组投影的数据只投影分组字段，其他没意义
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries WHERE emp_no < 10003 GROUPBY emp_no;

-- HAVING子句对分组结果过滤
SELECT emp_no, SUM(salary), AVG(salary), COUNT(emp_no) from salaries GROUP BY emp_no HAVING AVG(salary) > 45000;
-- 使用别名
SELECT emp_no, SUM(salary), AVG(salary) AS sal_avg, COUNT(emp_no) from salaries GROUP BY emp_no HAVING sal_avg > 60000;
-- 最后对分组过滤后的结果排序
SELECT emp_no, SUM(salary), AVG(salary) AS sal_avg, COUNT(emp_no) from salaries GROUP BY emp_no HAVING sal_avg > 60000 ORDER BY sal_avg;

总结

SELECT 
    emp_no,
    avg(salary) AS avg_salary
FROM
    salaries
WHERE 
    salary > 70000
GROUP BY 
    emp_no
HAVING
    avg(salary) > 50000
ORDER BY
    avg_salary DESC
LIMIT 1;

子查询

-- 子查询
SELECT * FROM employees WHERE emp_no in (SELECT emp_no from employees WHERE emp_no > 10015) ORDER BY emp_no DESC;
SELECT emp.emp_no, emp.first_name, gender FROM (SELECT * from employees WHERE emp_no > 10015) AS emp WHERE emp.emp_no < 10019 ORDER BY emp_no DESC;

连接 join

• 内连接
  • 等值连接，只选某些 field 相等的元组（行），使用 On 限定关联的结果
  • 自然连接，特殊的等值连接，会去掉重复列，用的少

-- ON等值连接
SELECT * from employees JOIN salaries ON employees.emp_no = salaries.emp_no;

-- 自然连接，去掉了重复列，且自行使用employees.emp_no = salaries.emp_no的条件,用的少
SELECT * from employees NATURAL JOIN salaries;

• 外连接
  1. 左连接
  2. 右连接
  3. 全连接

-- 左连接
SELECT * from employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no;
-- 右连接
SELECT * from employees RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no;

综上：

• 左连接（LEFT JOIN）：以左表为基础，将左表中的所有数据和右表中匹配的数据连接起来。如果右表中没有与左表匹配的数据，那么右表的对应列会被填充为 NULL
• 右连接（RIGHT JOIN）：以右表为基础，将右表中的所有数据和左表中匹配的数据连接起来。如果左表中没有与右表匹配的数据，那么左表的对应列会被填充为 NULL
• 全连接（FULL JOIN 或者 FULL OUTER JOIN）：将左表和右表中的所有数据都连接起来，即使左表中没有匹配的右表数据，右表中也没有匹配的左表数据。如果左表或右表中没有匹配的数据，对应的列会被填充为 NULL
• 内连接（INNER JOIN）：只返回两个表中共有的行，即两个表都满足连接条件的行

扩展阅读：左连接、右连接、内连接

存储过程

• 存储过程（Stored Procedure），数据库系统中，一段完成特定功能的 SQL 语句。编写成类似函数的方式，可以传参并调用。支持流程控制语句
• 触发器（Trigger），由事件触发的特殊的存储过程，例如 insert 数据时触发
• 很少用，因为移植性查，使用时占用服务器资源，排错维护不方便，不建议把逻辑放在数据库中

参考

• magedu