数据库基础

一、数据库基础知识

1.1 概述

1.1.1 基础

数据：描述事物的符号记录

数据库：长期存放在计算机内有组织、可共享的大量数据的集合

数据库管理系统：数据管理软件（数据定义DDL、数据操纵DML、数据控制DCL、数据查询DQL）

数据库系统：一般指由数据库、数据库管理系统、应用系统和数据库管理员组成的存储、管理、处理和维护数据的系统

1.1.2 数据模型

数据建模：把现实世界中的具体事务抽象、组织为某一数据库管理系统支持的数据类型。包含建立概念模型和将概念模型转换为数据模型两个步骤

概念模型：用于信息世界的建模（最常用表示方法E-R模型）

数据模型：一般来讲，是严格定义的一组概念的集合（包括数据结构、数据操纵、完整性约束三部分）

层次模型：层次模型数据结构的单位是基本层次联系，即两个记录之间一对多联系

网状模型：允许多对多

关系模型：由一组关系组成，每个关系的数据结构是一张规范化的二维表

1.1.3 数据库系统三级模式结构和两级映像

模式：模式是对所有用户的公共数据视图

外模式：数据库用户（包括程序员和用户）能够看见和使用的局部数据的逻辑结构和特征的描述

内模式：对数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

两级映像保证了数据库系统中的数据能够具有较强的逻辑独立性和物理独立性

外模式/模式映像：对一个模式，可以有任意多个外模式，对于每一个外模式，都有一个该映像来定义该外模式与模式之间的对应关系

模式/内模式映像：定义了数据全局逻辑结构与存储结构之间的对应关系（由于数据库只有一个模式，也只有一个内模式，所以该映像是唯一的）

1.2 数据库完整性

1.2.1 实体完整性

主属性不能去空值（‘不知道’，‘不存在’，‘无意义’）

1.2.2 参照完整性

外码只能取空或等于某个主码值

1.2.3 用户定义的完整性

针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足语义要求

---

二、数据库数据类型

2.1 数字类型

整数：tinyint（极小整数 1）、int（整数 4）、bigint（大整数 8）

小数：float（单精度浮点数 4）double（双精度浮点数 8）decimal（大浮点数 17）

2.2 字符串（在sql中使用''括起来的数据）

char（n）         定长字符串         范围：0~255

varchar（n）         边长字符串         范围：0~65535

text（n）         文本型         范围：0~65535

binary         二进制字符串

2.3 日期

year        yyyy

date        yyyy-mm-dd

time        HH:MM:SS

timestamp        yyyy-mm-dd        HH:MM:SS

datetime        yyyy-mm-dd        HH:MM:SS

2.4 二进制（了解）

tinyblob        0-255

blob        65k

mediumblob        16M

longblob        4G

---

三、数据库约束

3.1 主键约束（primary key）

3.1.1 概念：

数据表中的一个或多个字段的组合

3.1.2 作用：

唯一标识一行记录

排序

3.1.3 用法：

create table student(
    sno int primary key,
    sname varchar(20)
);

#复合主键
create table student(
    sno int,
    sname varchar(20),
    primary key(sno,sname)
);


#修改表结构追加
alter table student add id int primary key;//添加一个新的字段id作为主键
alter table student add primary key(id);

alter table student modify sno int primary key;//将sno设置为主键

3.2 非空约束

3.2.1 概念

字段的数据不允许为空

3.2.2 作用

当被非空约束的字段在插入数据的时候，必须要赋值

3.2.3 用法

定义表的时候

create table student(
    sno int primary key,
    sname varchar(20) not null
);

#修改表结构追加
alter table student modify name varchar(20) not null;

3.3 默认值约束

3.3.1 作用

可以给字段设置默认值，当插入数据的时候，如果没有给字段赋值，就会自动使用默认值

3.3.2 用法

定义表的时候

create table student(
    sno int primary key,
    sname varchar(20) not null,
    sbirthday date default '2003-11-10'
);

#修改表结构追加
alter table student modify sbirthday date default '2003-11-10';

默认值不能和自增长一起用

3.4 自增长

3.4.1 概念：

被约束字段在数据记录增加的时候会自动递增

3.4.2 用法：

定义表

create table student(
    sno int primary key auto_increment,
    sname varchar(20) not null,
    sbirthday date default '2003-11-10'
);

#修改表结构追加
alter table student modify sno int auto_increment;

3.4.3 注意事项：

一般搭配主键使用

每张表只允许一个字段使用这个约束

删除一条记录，并不会减一（5删除 再增加是6，而不是5）

3.5 检查约束

3.5.1 作用

给字段设置检查约束后，字段的值只能在指定的范围内挑选

3.5.2 用法

定义表时

create table student(
    sno int primary key auto_increment,
    sname varchar(20) not null,
    sbirthday date default '2003-11-10',
    ssex enum('f','m')
);


#修改表结构追加
alter table student modify ssex enum('f','m');//只能选一个


alter table student modify sname set('name1','name2');//可以多选

3.6 唯一约束

3.6.1 作用

被唯一约束字段，它的值不可重复

3.6.2 用法

定义表

create table student(
    sno int primary key auto_increment,
    sname varchar(20) not null,
    ssex enum('f','m'),
    sbirthday date default '2003-11-10',
    scard varchar(20) unique
);


#修改表结构追加
alter table student add scard int unique;

3.6.3 注意事项

unique 使用的时候，字段的含义需要符合唯一要求

unique不能和默认值约束一起使用 

3.7 外键约束

3.7.1 概念

从表中用来和主表建立关联关系的字段

主表是拥有主键的表，引用主表主键的表称为从表

3.7.2 用法

定义表的时候

create table score(
    sno int,
    cno int,
    grade int,
    foreign key (sno) references student(sno),
    foreign key (cno) references course(cno)
);

#修改表结构追加
alter table course add foreign key(sno) references student(sno);

3.7.3 注意事项

2个行为

update 更新数据

delete 删除数据

4种约束方式

restrict 拒绝变更，没有删除或变更从表的数据，主表不允许变化

no action 效果同上

cascade 级联操作：如果主键的值发生变化，外键跟随变化

set null 设置外键为空：如果主键发生变化，外键的值设为空（前提是外键没有非空约束）

---

四、DDL（Data Definition Language）

4.1数据库

创建数据库：

create database <dbname> character set utf8;

修改数据库字符集：

alter database <dbname> character set utf8;

删除数据库：

drop database <dbname>;

4.2 表

创建表：

create table <tbname>(
    字段名    数据类型（长度）    约束类型,
    ......                             ,
    字段名    数据类型（长度）    约束类型
)engine=innodb //存储引擎修改一下，禅道有的不支持

---

五、DML（Data Manipulation Language）

5.1 insert

5.1.1 作用

把数据插入到数据表中

5.1.2 语法

#最完整语法
insert into <tbname> (字段列表) values (值列表);

#除了自增长字段，其他字段必须给数据
insert into <tbname> values(值列表);

#批量插入数据
insert into <tbname> (字段列表) values (值列表),(值列表),......;

5.1.3 原则

字段列表中个数和值列表的个数一一对应

字段列表中个字段的数据类型和值列表的数据类型一一对应

字段列表中字段和值列表中字段对应顺序一一对应

5.2 update

5.2.1 作用

对数据表中的数据进行修改

5.2.2 语法

update <tbname> set 字段名=值 where 条件表达式;

5.3 delete

5.3.1 作用

删除表中的数据记录

5.3.2 语法

delete from <tbname> where 子句;#删除满足条件的数据记录

        

六、DCL（Data Control Language）

6.1 grant（授权）

6.1.1 权限概念

用户可以使用的数据库操作或命令

6.1.2 语法

grant <权限列表> on 数据库.对象名 to '用户名' @ '允许登录的地址' identified by '密码';

#实例：给root用户分配远程访问权限
grant all privilege on *.* to 'root'@'%' identified by 'root123';
flush privileges;

权限列表

细分：select、update、delete、insert

使用 all privilege 代替所有权限

对象名：

表table、视图view、过程procedure，        *.*代表所有数据库中所有对象

如果用户名不存在，grant可以创建用户

6.2 revoke（解除权限）

语法

revoke <权限列表> on 数据库.对象 from '用户名'@'允许登录地址';

#实例：收回dbtester对sales数据库插入权限
revoke insert on sales.* from 'dbtester'@'%';

七、DQL(Data Query Language)

select [distinct]字段名，字段名，... from 表明 [where子句] [group by 分组字段] [having子句] [order by子句] [limit分页条件];

7.1 单表查询

7.1.1 单标简单查询

select * from student;//查询student表中所有数据

select sno,sname from student;//查询student表中sno和sname

select sname,sbirthday from student where sbirthday > '2003-11-10' and ssex='f' and sage between 18 and 30;//使用where语句的查询

select sname from socre order by grade desc,sbirthday asc;//使用order by的查询（缺省ASC，升序   DESC 降序）

select sname from student where sname like '张%' or sname like '刘__';//模糊查询  %代表任意个任意字符 _代表任意一个字符

select distinct sname from student;//查重 去掉返回的数据中重复记录

select sname from student order by grade desc limit 0,3//分页查询 控制查询返回的记录数量

7.1.2 聚合函数

select count(*) from student where sses='f';//count（）计数

select sum(grade) from student where sses='m'//sum（）求和

select avg(isnull(grade,0)) from student;//avg（）求平均值    isnull如果空，则返回0

select max(grade) from student;//max（）求最大值

select min(grade) from student;//min（）求最小值
 

7.1.3 分组查询

select ssex,count(*) from student group by ssex;//分组查询
select cno,avg(grade) from student group by cno;//每个课程的平均分

select sno,avg(grade) from student group by sno having avg(grade)>=60 and sno >100;//having 分组之后的数据进行筛选 选出学生平均成绩大于等于60的sno和avg（grade）

7.2 多表查询

7.2.1 连接查询

select * from a left join b on a.sno = b.sno;//左外连接 左表数据全部显示，右表和

select * from a right join b on a.sno = b.sno;//右外连接

//内连接
select * from a,b where a.pk=b.fk [and 条件表达式];

select * from a inner join b on a.pk=b.fk [where 子句]

7.2.2 子查询（嵌套查询）

嵌入到其他sql语句中的查询语句

特性：

子查询返回的是一个虚表

//挑选总成绩大于150的学生信息：先找出总成绩大于150的学生的学号，再根据学号虚表查找学生信息
select * from student where sno in(
select sno from score group by sno having sum(degree)>150);

7.2.3 组合查询（不同数据表中同类数据）

union操作符：多个查询返回结果进行组合显示

（select语句 union | union [all | distinct] select语句）

all:返回所有结果集，包含重复数据

distinct:可选，删除结果集中重复的数据。

select tname,tbirthday from teacher where tsex='f'
union
select sname,sbirthday from student where ssex='f';

八、三大范式（一共实际有6大范式）

8.1 概念

范式：normal format 设计数据表的标准格式

8.2 意义

如果要设计一套关系型数据库表，必须要严格按照范式执行

8.3 第一范式

8.3.1 定义

表中的字段不可拆分（每一个数据项都不可拆分）

8.3.2 存在的问题

数据增加、删除、修改都会产生异常

8.4 第二范式

8.4.1 定义

非主键必须完全依赖主键（联合主键 非主属性不能只依赖主键中的某一个）

8.4.2 存在的问题

第二范式继承了第一范式的问题

8.5 第三范式

8.5.1 定义

每列都和主键直接相关，而不是间接相关

拆分表格：拆完之后用外键约束保证完整性。

九、数据库高阶概念

9.1 存储过程和函数

9.1.1 基础概念

（1）C/S架构：client客户端，server服务器端

（2）存储过程是一段完成指定功能的sql语句的集合

（3）怎么实现一个存储过程或函数？

变量：用来保存数据的符号

//在存储过程的外面或者内部都可以
set @变量名=值；

//只能在存储过程的内部使用
declare 变量名 数据类型 [default 默认值];

特点：
所有的declare要挨着写，而且只能写在存储过程最前面（先声明，后使用）
使用这种变量不需要@符号，直接使用变量名即可

存储过程

create procedure [if not exists] <proname> （形参列表）
begin
    代码块；
end

形参列表：定义存储过程或函数的时候，用来占位的变量名
实参：在调用函数或存储过程的时候，实际上使用的数据或变量

语法：（in|out|inout 变量名 数据类型（宽度），...）
in：表示数据通过这个变量传入存储过程里面
out：表示数据通过这个变量传出到存储过程外面
inout：表示数据可以通过这个变量传入和传出

9.2 事务（由一系列操作步骤组成逻辑单元）

9.2.1 特性

（1）多个步骤组成

（2）逻辑单元：要么一起执行完成，要么一起失败

9.2.2 4个要素

（1）原子性：事务的步骤是不可拆分的（银行转账：转出和转入是不可拆分的）

（2）隔离性：事务与事务之间不允许互相干扰（A有500 要转给B 同时C要来A取500）（加锁）

隔离级别	读数据一致性及允许的并发副作用	脏读	不可重复读	幻读
read uncommitted	最低级别，只能保证不读取物理上损坏的数据，事务可以看到其他事务没有被提交的数据（脏数据）	是	是	是
read committed	语句级，事务可以看到其他事务已经提交的数据	否	是	是
repeatable read	事务级，事务中两次查询的结果相同	否	否	是
serializable	最高级别，事务级。顺序执行	否	否	否

（3）一致性：事务提交前后的数据库的变化必须一致（银行A转给B钱   钱不会凭空消失）

（4）持久性：事务执行的效果是永久的

9.2.3 事务控制语句

1.begin 或 start transaction：显式开启一个事务
2.commit：提交事务，使已经对数据库进行的所有修改变成永久性
3.rollback：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
4.savepoint identifier：savepoint允许在事务中创建一个保存点，一个事务中可以有多个savepoint
5.rollback to identifier:把事务回滚到标记点
6.set transaction：设置事务的隔离级别

9.3 视图

9.3.1 定义：

视图可以看作定义在mysql上的虚拟表。视图正如其名字的含义一样，是另一种查看数据的入口。常规视图本身并不存储实际的数据，而仅仅存储一个select语句和所涉及表的data

9.3.2 操作：

//创建视图
create view 视图名（列1，列2...） as select（列1，列2...）from...;

//查看视图
show tables(like...);
select * from 视图名;（直接把视图当正常表用即可）

//删除视图
drop view 视图名

//修改视图
create or replace view 视图名 as select（...）from（...）;

9.4 索引

9.4.1 定义：

一种可以排序的数据结构

9.4.2 算法：

hash表
B+ tree

9.4.3 类型：

//主键索引 primary key
alter table table_name add primary key (col);

//唯一索引 unique
alter table table_name add unique (col);

//普通索引 index 
alter table table_name add index index_name(col);

//全文索引 fulltext
alter table table_name add fulltext(col);

//组合索引
alter table table_name add index index_name(col1,col2,col3);

9.4.4 执行计划explain

id：选择标识符

select_type：表示查询的类型

table：输出结果集的表

partitions:匹配的分区

type：表示表的连接类型

possible_keys：表示查询时，可能使用的索引

key：表示实际使用的索引

key_len：索引字段的长度

ref：列与索引的比较

rows：扫描出的行数

filtered：按表条件过滤的行百分比

extra：执行情况的描述和说明

type：

all：mysql将遍历全表以找到匹配的行

index：index类型只遍历索引树

range：只检索给定范围的行，使用一个索引来选择行

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref：类似ref，区别在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配

const、system：当mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。system是const类型的特例，当查询的表只有一行的情况下，使用system

null：mysql在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys：

指出mysql能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询使用

key：

key列显示mysql实际决定使用的键（索引），必然包含在possible_keys中，如果没有选择索引，键是null。

rows：

估算出结果集行数，表示mysql根据表统计信息及索引选用情况，估算找到所需记录需要读取的行数

9.4.5 索引失效的原因

查询语句中使用or关键字

查询语句中使用select *

复合索引，未遵循最左原则：复合索引分开用的时候，最左边的字段有效，其他的字段不会索引

like使用%或_开头

需要类型转换

where中索引列有运算

where中索引列使用了函数

mysql觉得全表扫描更快（数据极少）

9.4.6 索引的作用

提高查询效率

9.4.7 索引的缺点

（1）会增加空间占用，因为索引也要占磁盘空间

（2）会使得DML效率变低：因为每次变动索引需要重新排序（读写分离：数据库做两个，一个用来查询，一个用来做DML）