数据库概要思维导图模板_ProcessOn思维导图、流程图

常用数据库

Oracle(甲骨文)

定义

关系型数据库管理系统，目前世界上主流的关系型数据库之一，高效率、可靠性好、适应高吞吐量的数据库方案

优点

1.可移植性好，完全支持所有工业标准。

2.获得最高认证级别的ISO标准认证，安全性高。

3.性能最高

4.多层次网络计算，可以用ODBC、JDBC、OCI等网络客户连接

5.完全向下兼容，因此被广泛应用，且风险低。

缺点

1.对硬件的要求高

2.价格比较昂贵

3.管理维护麻烦

4.操作比较复杂

SQL Server(微软)

定义

关系型数据库系统，主要应用于大型的管理系统中

优点

1.与Windows系列操作系统的兼容性很好

2.高性能设计，能充分利用WindowsNT的优势

3.管理系统先进，支持Windows图形化管理工具，支持本地和远程的系统管理和配置

4.强壮的事务处理功能，采用各种方法保证数据的完整性

5.支持堆成多处理器结构、存储过程、ODBC、并具有自主的SQL语言

缺点

1.只能在Windows上运行，没有丝毫开放性

2.没有获得任何安全证书

3.多用户时性能不佳

4.只支持C/S模式，SQL Server C/S结构只支持Windows客户用ADO、DAO、OLEDB、ODBC连接

MySQL(Oracle)

定义

主流关系型数据库之一，在WEB应用方面，MySQL是最好的应用软件之一

优点

1.性能卓越服务稳定，很少出现异常宕机

2.开放源代码且无版权制约，自主性强，使用成本低

3.历史悠久，社区及用户非常活跃，遇到问题，可以很快获得帮助

4.软件体积小，安装使用简单，并且易于维护，安装及维护成本低

5.支持多种操作系统，提供多种API接口，支持多种开发语言

缺点

1.MySQL最大的缺点就是安全系统，主要是复杂而非标准，只有调用mysqladmin来重读用户权限才会发生改变

2.MySQL不允许调试存储过程，并发和维护存储过程很难

3.MySQL不支持热备份

4.MySQL的价格随平台和安装方式变化

Access(微软)

定义

小型关系型数据库管理系统，是微软把数据库引擎的图形用户界面和软件开发工具结合在一起的一个数据库管理系统

优点

1.存储方式简单，易于维护管理。Access 的对象有表、查询、窗体、报表、页、宏和模块，以上对象都存放在后缀为（.mdb 或 .accdb）的数据库文件中，便于用户的操作和管理

2.Access是一个面向对象的开发工具，这种基于面向对象的开发方式，使得开发应用程序变得更为简便

3.界面友好，易操作。Access是一个可视化工具，操作简单，容易使用和掌握

4.集成环境，可以处理多种数据信息。

5.支持广泛，易于扩展，弹性大。

缺点

1.不支持并发处理。

2.数据库存储量小安全性不够高

3.小型数据库，数据量一般百M以上性能会变差

4.理论支持255个用户，实际只读访问大概100个用户，并发编辑10-20个

5.不能编译成可执行文件，必须安装Access运行环境才能使用

DB2(IBM)

定义

支持多媒体、Web的关系型数据库管理系统，主要应用于大型应用系统，具有较好的可伸缩性，可支持从大型机到单用户环境

优点

1.相比较MySQL和Oracle来说。DB2提供了高层次的数据利用性、完整性、安全性、可恢复性，以及小规模到大规模的应用程序执行能力，具有于平台无关的基本功能和SQL命令

2.DB2采用了数据分级技术，能够使大型数据很方便的下载到数据库服务器，使数据库本地化和远程连接透明化

3.拥有非常完备的查询优化器，改善查询性能，并支持多任务并行查询

4.具有很好的网络支持能力，每个子系统可以连接十几万个分布式用户，可同时激活上千个活动线程，对大型分布式应用系统更好使用。

5.可跨平台使用

缺点

1.配置文件和参数多，且命名不规范

2.一些DB2产品开发不方便

3.和Oracle相比，命令多，没Oracle统一规范好

4.由于去设计框架的问题，如果用户对数据库本身优化和应用程序优化做的不足，那么DB2容易出现锁等待现象

索引

定义

索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息，
如果想按职员的姓来查找他或她。则在表中搜索所有的行相比，索引有助于更快地获取信息

缺点

创建和维护(增删改数据)索引需要耗费时间，这种事件随数据量的增加而增加

索引需要物理空间，如果要建立聚簇索引更加耗费空间

优点

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性

可以大大加快数据的检索速度

加速表和表的连接，特别是实现数据的参考完整性方面特别有意义

在使用分组和排序子句进行数据检索是，同样可以显著减少查询中分组和排序的时间

使用索引，在查询的过程中，使用优化隐藏器，提高系统的性能

添加索引的原则

在查询中很少使用或者参考的列不应该创建索引；相反的，反而降低了系统的维护速度和增大了空间需求

只有很少数据值的列也不应该增加索引，即需要在表中搜索的数据行的比例很大，并不能加快检索速度；

定义为text、image和bit数据类型的列不应该增加索引，因为这些列的数据两要么相当大，要么取值很少

当修改性能远远大于检索性能是，不应该创建索引

sql

普通索引

没有任何限制

创建索引

create index indexname on mytable(username(length));

如果是chanr，varchar类型，length可以小于字段实际长度，
如果是blob和text类型，必须指定length

修改表结构（添加索引）

alter table tablename
add index indexname（columnname）

删除索引

drop index indexname
on mytable；

唯一索引

创建索引

create unique index indexname
on mytable(username(length))

修改表结构

alter table mytable
add unique indexname（username（length）

显示索引信息

show index from table_name; \G

\G格式化输出信息

事务

定义

指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行，

作用

事务处理将一组相关操作组合为一个要么成功要么失败的单元，简化了错误恢复并使用应用程序更加可靠

特性

事务是DBMS(数据库管理系统)中最基础的单元，事务不可分割

基本特征(ACID)

1.原子性

指事务包含的所有操作要么全部成功，要么全部失败回滚
成功就必须完全应用带数据库中，失败就不能对数据库造成任何影响

2.一致性

指事务必须使数据库从一个一致性状态变换到另一个一致性转态，
也就是说一个事务执行之前和执行之后都必须处于一致性状态

3.隔离性

当多个用户并发访问数据库时，数据路为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

隔离级别

3.1 读取未提交

最低隔离级别，一个事务能够读取到其他事务未提交的结果

3.2 读取提交内容

只有在事务提交之后，更新结果才会被其他事务看见，
可以解决脏读问题

3.3 可重复读

在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交，
可以解决脏读、不可重复读

3.4 可串行化

事务串行化执行，隔离级别最高，牺牲了系统的并发性，可以解决并发事务的所有问题

4.持久性

指一个事务一旦被提交，那么对数据库的数据改变就是永久的，即使是数据库系统遇到故障也不会丢失提交事务的操作

相关命令

查看支持的存储引擎

show engines；(MySQL)

InnoDB支持事务

范式

第一范式

当关系模式R的所有属性都不能分解为基本的数据单位时，即属性不可分

第二范式

如果关系模式R满足第一范式时，并且R的所有非主属性都完全依赖于R的每个候选关键属性

第三范式

设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，即非主属性不依赖于键码

MySQL锁

S锁(共享锁)

读锁，共享

X锁(排他锁)

写锁，独占

死锁

条件

1.资源不能共享，需要只能由一个进程或者线程使用

2.请求和保持，已经锁定的资源保持着不释放

3.不剥夺，自己申请到的资源不能被别人剥夺

4.循环等待

预防死锁

1.尽量避免并发地执行设计到修改数据的语句

2.要求每个事务一次就将所有要使用的数据全部加锁，否则就不执行

3.预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁

4.每个事务的执行时间不可太长，在业务允许的情况下将事务分割成几个小事务来执行

用户管理

用户创建

create user 用户名 @'IP地址' identified by '密码'

用户授权

grant 权限 on 数据库名.* to 用户名 @'IP地址'

撤销权限

revoke 权限 on 数据库名.* from 用户名 @'IP地址'

查看权限

show grants for 用户名@'IP'

删除用户

drop user 用户名@'IP'

SQL

注意

SQL语句大小写不敏感

SQL可以写在一行或者多行

关键字不能被缩写也不能分行

各子句一般要分行写

使用缩进提高语句可读性

查询相关操作

基本select语句

select *
from student；

列的别名

重命名一个列，便于计算

写法

紧跟列名，也可以再列命和别名之间加入关键字'AS',别名使用双引号，以便在别名中包含空格或特殊字符并区分大小写

select last_name AS name, commoment comm
from employees;

字符串

字符串可以是select列表中的一个字符，数字，日期
日期和字符只能在单引号中出现
每当返回一行时，字符串被输出一次

查看表结构

desc 表名

过滤和排序数据

过滤

使用where子句，将不满足条件的行过滤掉

select *
from student
where age = 18;

where子句紧随from子句

比较运算

常规

大于等于。。 <>(不等于也可是!=)

特殊

between...and...

表示两个值之间

select *
from stu
where age between 18 and 20;

in(set)

等于值列表中的一个

like

模糊查询

select *
from stu
where name like '_n%'

% 代表零个或多个字符

_代表一个字符

is null

空指

select *
from stu
where name is (not) null

is not null

非空

逻辑运算

and

or

not

select name
from stu
where name not in ('zhang', 'wang', 'li');

order by子句

排序

asc(升序)(默认)

desc(降序)

order by子句再select语句的结尾

select name
from stu
order age desc;

分组函数

定义

作用于一组数据，并对一组数据返回一个值

类型

avg() --平均值

sum() --合计

max() --最大记录

min() --最小记录

count() --计数

count(*)返回表中记录总数，适用于任意数据类型

select count(*)
from stu
where age = 15;

分组数据

group by子句

可以使用group by 子句将表中数据分成若干组

select column
from table
where condition
group by group_by_expression
order by column;

where一定放在from后面

注意

在select列表中所有未包含再组函数中的列都应该包含再group by子句中

select id，avg(age)
from stu
group by id;

包含在group by子句中的列不必包含在select列表中

非法使用

不能在where子句中使用组函数

可以在having子句中使用组函数

过滤分组

having子句

条件

1.行已经被分组

2.使用了分组函数

3.满足having子句中条件的分组将被显示

语法

select column，group_function
from table
where condition
group by group_by_expression
having group_condition
order by column;

select department_id, max(salary)
from employees
group by department_id
having max(salary) > 10000;

连接

笛卡尔集产生条件

省略连接条件

连接条件无效

所有表中的所有行互相连接

避免笛卡尔集
在where加入有效的连接条件

在表中有相同列时，在列名之前加上表名前缀

等值连接

表的别名

使用别名可以简化查询

子主题

使用表名前缀可以提高执行效率

连接多个表

使用on子句来连接

自然连接中是以具有相同名字的列为连接条件的

可以使用on指定额外的连接条件

这个连接条件是与其他条件分开的

on子句使语句具有更高的易读性

分类

内连接

join on

select employee_id, city, department_name
from employees e
join department d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;

外连接

左外连接

left join on

右外连接

right join on

子查询

定义

出现再其他语句内部的select语句，成为子查询或内查询

内部嵌套其他select语句的查询，称为外查询或主查询

注意

子查询要包含在括号内

将子查询放在比较条件的右侧

单行操作符对应单行子查询，多行操作符对应对行子查询

单行子查询

只返回一行

使用单行比较操作符

操作符

< > = <> <= >=

语法

select select_list
from table
where expr_operator
(select select_list
from table);

谁的工资比abel高

select last_name
from employees
where salary >
(select salary
from employees
where last_name = ''abel)

多行子查询

返回多行

使用多行比较操作符

操作符

in/not in

等于列表中的任意一个

any|some

和子查询返回的某一个值比较

all

和子查询返回的所有值比较

语法

any

返回其他部门中比job_id为'it_prog'部门任一工资低的员工的员工号、姓名、job_id以及salary

select employee_id, last_name,job_id, salary
from employees
where salary < any
(select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

all

返回其他部门中比job_id为'it_prog'部门所有工资低的员工的员工号、姓名、job_id以及salary

select employee_id, last_name,job_id, salary
from employees
where salary < all
(select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

limit实现分页

前十条记录

select *
from table limit 0,10;

第11条至20条记录

select *
from table limit 10,10;

第20至30条记录

select *
from table limit 20,10;

公式

（当前页数-1）*每页条数，每页条数

select * from table limit(pageno -1)*pagesize, pagesize;

limit子句必须放在整个查询语句最后

insert

语法

insert into table（column...）
values(value,value...)

使用改语法一次只能向表中插入一条数据

向表中插入空值

隐式方法：在列名表中省略该列的值

显式方法：在values子句中指定空指

插入指定的值

NOW()函数：记录当前系统的日期和时间

从其他表中拷贝数据

在insert语句中插入子查询

insert into sales(id, name, salary)
select employee_id, last_name, salary
from employees
where job_id like '%s%';

不必书写values子句

子查询的值列表应与insert子句中的列名对应

update

语法

update table
set column = value,column = value
where condition;

可以一次更新多条数据

如果需要回滚数据，需要保证在DML之前，进行设置：set autocommit = false；

使用where子句指定需要更新的数据

如果省略where子句，则表中的所有数据将被更新

更新中的数据完整性错误

update stu
set id = 15
where id = 100;

不存在15号学生

delete

语法

使用delete语句从表中删除数据

delete from table
where condition；

使用where子句删除指定的记录

delete from departments
where department_name = 'fin';

如果省略where子句，则表中的全部数据将被删除

delete from stu；

删除中的数据完整性错误

delete from stu
where name = 'zhang';

删除的行包含其余表的外键

创建和管理表

创建

create table语句

必须具备

create table权限

存储空间

必须指定

表明

列名，数据类型，尺寸

语法

create table dept
(septno int(2),
dname varchar(14),
loc varchar(13)
);

create table emp(
#int类型，自增
emp_id int auto_increment,
#最多保存20个中英文字符
emp_name char(20),
#总位数不超过15位
salary double,
#日期类型
brithday date,
#主键
primary key（emp_id）
);

确认

describe dept

显示表的所有属性信息

管理

alter table语句

可实现

向已有的表中添加列

修改现有表中的列

删除现有表中的列

重命名现有表中的列

追加一个新列

alter table dept80
add job_id varchar(15);

删除一个列

alter table dept80
drop column job_id;

修改一个列

alter table dept80
modify column time2 varchar(30);

重命名一个列

使用change old_column new_column dateType子句重命名列

alter table dept80
change department_name dept_name varchar(15);

删除表

可实现

数据和结构都被删除

所有正在运行的相关事务被提交

所有相关索引被删除

drop table语句不能回滚

drop table dept80;

清空表

truncate table语句

可实现

删除表中所有的数据

释放表的存储空间

truncate table detail_dept；

truncate语句不能回滚

可以使用delete语句删除数据，可以回滚

delete from emp2；
selete * from emp2；
rollback;
select * from emp2;

truncate table detail_dept；

改变对象名称

rename语句

可实现

改变表，视图的名称

必须是对象的拥有者

alter table dept
rename to detail_dept;

数据库完整性

not null约束

unique约束

可以有多个唯一约束，多个列组合约束

表级约束语法

constraint uk_name_pwd unique(name, password)

表示用户名和密码组合不能重复

primary key约束

主键约束相当于唯一约束+非空约束的组合

列级模式

create table emp4(
id int auto_increment primary key,
name varchar(20));

表级模式

create table emp5(
id int not null auto_increment,
name varchar(20),
constraint emp5_id_pk primary key(id)
);

组合模式

cerate table emp6(
id int not null,
name varchar(20),
pwd varchar(15),
constraint emp6_pk primary key(name ,pwd)
);

删除主键约束

alter table emp5
drop primary key;

添加主键约束

alter table emp5
add primary key(name, pwd);

修改主键约束

alter table emp5
modify id int primary key;

foreign key约束

创建外键

create tabel classes(
id int primary ket,
name varchar(20)
)
desc classes
drop table stu
create table stu(
sid int,
sname varchr(20),
cid int,
foreign key(cid) peferences classes(id)
)

视图

定义

一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且在使用视图时动态生成的，至保存了sql的逻辑，不保存查询结果

应用场景

多个地方用到同样的查询结果

该查询结果使用的sql语句较复杂

sql

创建视图

create view view_name
as select_statement

修改视图

alter view view_name
as select_statement

删除视图