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数据类型
类型 | 含义 |
---|---|
tinyint | 1 字节,带符号的范围是 - 128 到 127。无符号的范围是 0 到 255。bool 或 boolean,就是 tinyint,0 表示假,非 0 表示真 |
smallint | 2 字节,带符号的范围是 - 32768 到 32767。无符号的范围是 0 到 65535 |
int | 整型,4 字节,同 Integer,带符号的范围是 - 2147483648 到 2147483647。无符号的范围是 0 到 4294967295 |
bigint | 长整型,8 字节,带符号的范围是 - 9223372036854775808 到 9223372036854775807。无符号的范围是 0 到 18446744073709551615 |
float | 单精度浮点数精确到大约 7 位小数位 |
double | 双精度浮点数精确到大约 15 位小数位 |
DATE | 日期。支持的范围为'1000-01-01'到'9999-12-31' |
DATETIME | 支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59' |
TIMESTAMP | 时间戳。范围是'1970-01-01 00:00:00'到 2037 年 |
char | 固定长度,右边填充空格以达到长度要求。M 为长度,范围为 0~255。M 指的是字符个数 |
varchar(M) | 变长字符串。M 表示最大列长度。M 的范围是 0 到 65,535。但不能突破行最大字节数 65535 |
text | 大文本。最大长度为 65535(2^16-1) 个字符 |
char可以将字符串定义为固定长度,空间换时间,效率略高;varchar为变长,省了空间
可视化操作工具
- 以上创建表操作一般通过可视化工具完成
- 如Navicat、DataGrip等
关系和约束
关系
- 关系就是二维表,由行和列组成
- 关系的维数:关系中列 (属性) 的个数
- 关系的基数:关系中行 (元组) 的个数
候选键
- 关系中,能唯一标识一条行(元组)的列属性或属性集合,称为候选键
- 简单说就是能够唯一确定某一行的列属性
- 候选键可以有多个
id | class_no | s_id | name | id_no | age |
---|---|---|---|---|---|
1 | 5 | 010 | jerry | 110000199505103911 | 18 |
2 | 6 | 010 | jerry | 110000199305103912 | 20 |
3 | 7 | 001 | tom | 110000199505103913 | 18 |
这里 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)是数据库中用来提高查询速度的一种数据结构,它是对表中某些列的值进行排序的一种结构,可以理解为是一张表的快速查找入口。索引分为聚集索引和非聚集索引,其中聚集索引是按照主键排序的索引,而非聚集索引则是按照其他列排序的索引
在使用主键、外键和索引时,需要注意一些细节问题。例如,主键不能重复,否则会破坏表的一致性;外键需要建立表与表之间的关系,并且需要进行索引优化;索引的建立需要考虑到查询的频率、数据的变化率等因素,避免过多或过少的索引对性能产生负面影响 - 为了提高查询速率,索引越多越好吗?
并不是索引越多越好。虽然索引可以提高查询速率,但是索引也会占用磁盘空间,并增加插入、更新、删除操作的开销。如果过多地使用索引,可能会导致数据库的性能下降,甚至出现一些异常情况,如死锁等
另外,索引还有一些其他的限制和注意事项,如单个索引大小的限制、使用索引的顺序、索引的选择等。在使用索引时,需要根据具体情况进行权衡和优化,以达到最优的性能和存储空间利用效率 - 索引一定唯一吗?
作为索引的键不一定是唯一的,可以包含重复的值,这种索引被称为非唯一索引。但是,唯一索引在插入新数据时需要做额外的唯一性检查,因此可能会降低插入数据的速度。非唯一索引可以用于加快查询速度,但需要注意的是,它可能会导致以下问题:
- 查询结果不准确:当使用非唯一索引进行查询时,返回的结果可能会包含重复的数据
- 查询性能下降:如果非唯一索引中包含大量重复的数据,那么查询性能可能会下降,因为数据库需要扫描更多的数据块
- 索引占用更多的空间:如果索引中包含大量重复的数据,那么它会占用更多的存储空间,从而增加数据存储的开销
约束 Constraint
为了保证数据的完整和正确,数据模型必须支持完整性约束。如设置某列值不能为空NOT NULL
域约束 Domain Constraint
- 限定了表中字段的取值范围
实体完整性 Entity Integrity
- PRIMARY KEY 约束定义了主键,就定义了主键约束
- 主键不重复且唯一,不能为空
引用完整性 Referential Integrity
- 外键约束
class 表 A:
id | class_name |
---|---|
1 | python 班 |
2 | java 班 |
3 | go 班 |
student 表 B:
id | name | age | class_no |
---|---|---|---|
1 | jerry | 18 | 2 |
2 | tom | 20 | 1 |
3 | mali | 16 | 2 |
4 | herry | 17 | 3 |
B 表 class_no 引用了 A 表中的主键 id,class_no 即为外键
A 表为主表,B 表称为从表
- 插入规则
- 不需要指定
- 在 B 表中插入一条记录,B 的外键列插入了一个值,这个值必须是表 A 中存在的主键值
- 更新和删除规则
- 定义外键约束时指定该规则
- 外键约束操作
id | class_name |
---|---|
CASCADE | 级联删除或更新,从父表删除或更新会自动删除或更新子表中匹配的行 |
SET NULL | 从父表删除或更新行,会设置子表中的外键列为 NULL,但必须保证子表列没有指定 NOT NULL,也就是说子表的字段可以为 NULL 才行 |
RESTRICT | 如果从父表删除主键,如果子表引用了,则拒绝对父表的删除或更新操作 |
NO ACTION | 标准 SQL 的关键字,在 MySQL 中与 RESTRICT 相同。拒绝对父表的删除或更新操作 |
- 举例说明
还是上面的 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;
- 外连接
- 左连接
- 右连接
- 全连接
-- 左连接
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