数据库关系模型和SQL
为例介绍关系模型,以Mysql数据库为例。
安装
MariaDB 安装
1.安装mariadb-server,会自动安装mariadb
>yum install mariadb-server
2.启动服务
>systemctl start mariadb.service
3.开机启动
>systemctl enable mariadb.service
4.为了安全设置mysql服务
>mysql_secure_installation
5.数据库密码登录
>mysql -u root -p
6.导入测试脚本
mysql -uroot -p < test.sql
显示所有数据库
show databases;
创建并授权用户
grant all on *.* to '用户名'@'%' identified by '密码';
flush privileges;
SQL语句
SQL是结构化查询语言Structured Query Language。1987年被ISO组织标准化。
所有主流的关系型数据库都支持SQL,NoSQL也有很大一部分支持SQL。
SQL语句分为
- DDL 数据定义语言,负责数据库及数据库对象定义,由create(创造),alter(更改),drop(终止)等三种语句组成。
- DML 数据操作语言,负责对数据库对象的操作,CRUD增删改查。
- DCL 数据控制语言,负责数据库权限访问控制,由grant(授权),revoke(撤销)两个指令组成。
- TCL 事务控制语言,负责处理ACID事务,支持commit(提交),rollback(回滚)指令。
语言规范
- SQL语句大小写不敏感
- SQL语句末尾应该使用分号结束
- 注释
- 多行注释
/*message*/
- 单行注释
-- message
- MySQL 注释可以使用#
- 使用空格或缩进提高可与读性
- 命名规范
- 必须以字母开头
- 可以使用数字,#,$ 和 _
- 不可使用关键字
DCL
grant授权,revoke撤销
1.授权 all权限 on employees库的所有表 to qy用户 在任意ip 以*963.为密码来访问
>grant all on employees.* to 'qy'@'%' identified by '*963.';
2.撤销 qy用户 的所有权限 on 所有库
>revoke all on *.* from qy;
符号 * 为通配符,指代任意库或者任意表。*.*
所有库的所有表;employees.*
表示employees库下所有的表
% 为通配符,它是SQL语句的通配符,匹配任意长度字符串
DDL
删除用户(慎用)
drop user qy;
创建数据库
库是数据的集合,所有数据按照数据模型组织在数据库中。
create database if not exists test character set utf8mb4 collate utf8mb4_general_ci;
create database if not exists test character set utf8;
character(特性) set 指定字符集。
utf8mb4是utf8的扩展,支持4字节utf8mb4,需要MySQL5.5.3+。
collate(校验)指定字符集的校对规则,用来做字符串的。例如a, A谁大?
删除数据库
drop database if exists test;
创建表
表分为行和列,MySQL是行存数据库。数据是一行行存的,列必须固定多少列。
行row,也称为记录(record),元组。
列column,也称为字段(Field),属性。
字段的取值范围叫做domain(域),例如下图gender字段的取值就是M或F连个值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RPA65tio-1575001748506)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20191128205300123.png)]
create table `employees` (
`emp_no` int(5) not null,
`birth_date` date not null,
`first_name` varchar(15) 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
查看列信息
{DESCRIBE| DESC} tbl_name [col_name | wild]
desc employees;
desc employees '%name';
练习
设计一张表,记录登录账户的注册信息,应该存储用户的姓名,登录名,密码
drop database if exists login;
create database if not exists login character set utf8mb4 collate utf8mb4_general_ci;
create table `reg`(
`id` int(10) not null,
`loginname` varchar(50) not null,
`name` varchar(20) not null,
`password` varchar(128) not null,
primary key (`id`)
)engine=innodb
关系
上面讲了行(元组,记录),列(字段,元组),字段范围(域)等。
维数:关系的维数指关系中属性的个数
基数:元组的个数
注意在关系中,属性的顺序并不重要,理论上,元组顺序也不重要,但是由于元组顺序与存储相关,会影响查询效率
候选键
关系中,能唯一标识一条元组的属性(列)或属性集合(列的集合),称为候选键。
候选键,表中一列或者多列组成唯一的key, 通过这一个或者多个列能唯一的标识一条记录。
表中可能有多个候选键。
PRIMARY KEY 主键
从候选键中选择出主键。
主键的列不能包含空值null。主键往往设置为整型、长整型,可以为自增AUTO_INCREMENT字段。
表中可以没有主键,但是,一般表设计中, 往往都会有主键,以避免记录重复。
InnoDB的表要求使用主键。
Foreign KEY 外键
当一个关系中的某个属性或属性集合与另一个关系(也可以是自身)的候选键匹配时,就称作这个属性或属性集合是外键。
索引Index
可以看做是一本字典的目录, 为了快速检索用的。空间换时间,显著提高查询效率。
可以对一列或者多列字段设定索引。
主键索引,主键会自动建立主键索引,主键本身就是为了快速定位唯一记录的。
唯一索引,表中的索引列组成的索引必须唯一,但可以为空,非空值必须唯一
普通索引,没有唯一性的要求, 就是建了一个字典的目录而已。
联合索引,多个字段组合创建索引,使用条件查询时,先匹配左边字段
全文索引,MyISAM使用,对Char、Varchar、 TEXT类型使用
空间索引,SPATIAL, 基本不用。
在MySQL中,InnoDB和MyISAM的索引数据结构可以使用Hash或BTree, innodb默认是BTree.
Hash时间复杂度是0(1),但是只能进行精确匹配,也就是Hash值的匹配,比如范围匹配就没办法了,
hash值无序所以无法知道原有记录的顺序。Hash问题较多。
BTree索引,以B+树为存储结构。
虽然,索引可以提高查询所读,但是却影响增删改的效率,因为需要索引更新或重构。频繁出现在
where子句中的列可以考虑使用索引。要避免把性别这种字段设索引。
B+树
B+树节点组织成一棵树。节点分为内部节点和叶子节点。
内部节点不存储数据,叶子节点不存储指针。
每个leaf node保存数据,所有的leaf node组织成链表。假设读取16到22的数据,找到18后,顺着链表
往后遍历读取即可。
InnoDB中,数据文件本身就是按主键索引存储的,叶子节点中保存的就是数据记录。
如果在其他字段上定义B+Tree索引,叶子节点的数据记录的是主键,这种称为辅助索引。
约束Constraint(数据库的三大完整性)
为了保证数据的完整性,数据模型还必须支持完整性约束。
“必须有值”约束
某写列的值必须有值,不为null
域约束Domain Constraint(域完整性)
限定了表中字段的取值范围
实体完整性Entity Integrity
PRIMARY KEY 约束定义了主键,就定义了主键约束。 即实体完整性要求每个关系(表)有且仅有一个主键,每一个主键值必须唯一,而且不允许为“空”(NULL)或重复。
引用完整性Referential Integrity
指在外键定义中,可以不是引用另一张表的主键,但是往往实际只会关注引用主键。
外键:在表B中的某列,引用了表A中的主键,表B中的该列就是外键。
A表称为主表,B表称为从表。
插入规则
不需要指定。
如果在表B插入一条记录,B的外键列插入了一个值,这个值必须是表A中存在的主键值。
更新规则
定义外键约束时指定该规则。
删除规则
定义外键约束时指定该规则。
定义外键约束的操作
设定值 |
说明 |
CASCADE |
级联,从父表删除或更新会自动删除或更新子表中匹配的行 |
SET NULL |
从父表删除或更新行,会设置子表中的外键列为NULL,但必须保证子表列没有指定NOT NULL,也就是说子表的字段可以为NULL才行 |
RESTRICT |
如果从父表删除主键,如果子表引用了,则拒绝对父表的删除或更新操作 |
NO ACTION |
标准SQL的关键字,在MySQL中与RESTRICT相同。拒绝对父表的删除或更新操作 |
外键约束的目的是为了保证数据完整性、一致性, 杜绝据数冗余(rongyu,多余的或重复的)、数据错误。
实体-联系模型(E-R模型)
数据库建立,需要收集用户需求,设计符合企业要求的数据模型。而构建这种模型需要方法,这种方法需要成为E-R实体-联系建模。也出现了一种建模语言一UML (UnifiedModeling Language)统一建模语言。
实体Entity:现实世界中具有相同属性的一组对象,可以是物理存在的事物或抽象的事物。
联系Relationship:实体之间的关联集合。
实体间联系类型
假设有实体部门,实体员工
类型 |
描述 |
解决方案 |
一对多联系 |
一个员工属于一个部门,一个部门有多个员工 |
员工外键;部门主键 |
多对多联系 |
一个员工属于多个部门,一个部门有多个员工 |
建立第三表 |
一对一 联系 |
假设有实体管理者,一个管理者管理一个部门, 一个部门只有一个管理者 |
字段建在哪张表都行 |
一对一关系用的较少,往往表示表A的一条记录唯一关联表B的一 条记录,反之亦然。
它往往是为了将一张表多列分割并产生成了多张表,合起来是完整的信息,或为了方便查询,或为了数据安全隔离一部分字段的数据等等。
视图
视图,也称虛表,看起来像表。它是由查询语句生成的。可以通过视图进行CRUD操作。
视图的作用
- 简化操作,将复杂查询SQL语句定义为视图,可以简化查询。
工 | 建立第三表 |
| 一对一 联系 | 假设有实体管理者,一个管理者管理一个部门, 一个部门只有一个管理者 | 字段建在哪张表都行 |
一对一关系用的较少,往往表示表A的一条记录唯一关联表B的一 条记录,反之亦然。
它往往是为了将一张表多列分割并产生成了多张表,合起来是完整的信息,或为了方便查询,或为了数据安全隔离一部分字段的数据等等。
视图
视图,也称虛表,看起来像表。它是由查询语句生成的。可以通过视图进行CRUD操作。
视图的作用
- 简化操作,将复杂查询SQL语句定义为视图,可以简化查询。
- 数据安全,视图可以只显示真实表的部分列,或计算后的结果,从而隐藏真实表的数据