数据库
2020-12-24 18:39:40 1 举报AI智能生成
常见数据库的优缺点，常用的数据库操作
作者其他创作
大纲/内容
数据库概要
常用数据库
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 indexnameon 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；
列的别名
重命名一个列，便于计算
写法
字符串
字符串可以是select列表中的一个字符，数字，日期日期和字符只能在单引号中出现每当返回一行时，字符串被输出一次
查看表结构
desc 表名
过滤和排序数据
过滤
使用where子句，将不满足条件的行过滤掉
select *from studentwhere age = 18;
where子句紧随from子句
比较运算
常规
大于 等于。。 <>(不等于也可是!=)
特殊
between...and...
表示两个值之间
select *from stuwhere age between 18 and 20;
in(set)
等于值列表中的一个
like
模糊查询
select * from stuwhere name like '_n%'
% 代表零个或多个字符
_代表一个字符
is null
空指
select *from stuwhere name is (not) null
is not null
非空
逻辑运算
and
or
not
order by子句
排序
asc(升序)(默认)
desc(降序)
order by子句再select语句的结尾
select namefrom stuorder age desc;
分组函数
作用于一组数据，并对一组数据返回一个值
类型
avg() --平均值
sum() --合计
max() --最大记录
min() --最小记录
count() --计数
count(*)返回表中记录总数，适用于任意数据类型
select count(*)from stuwhere age = 15;
分组数据
group by子句
可以使用group by 子句将表中数据分成若干组
select columnfrom tablewhere conditiongroup by group_by_expressionorder by column;
where一定放在from后面
在select列表中所有未包含再组函数中的列都应该包含再group by子句中
select id，avg(age)from stugroup by id;
包含在group by子句中的列不必包含在select列表中
非法使用
不能在where子句中使用组函数
可以在having子句中使用组函数
过滤分组
having子句
1.行已经被分组
2.使用了分组函数
3.满足having子句中条件的分组将被显示
语法
select column，group_functionfrom tablewhere conditiongroup by group_by_expressionhaving group_conditionorder by column;
连接
笛卡尔集产生条件
省略连接条件
连接条件无效
所有表中的所有行互相连接
避免笛卡尔集在where加入有效的连接条件
在表中有相同列时，在列名之前加上表名前缀
等值连接
表的别名
使用别名可以简化查询
子主题
使用表名前缀可以提高执行效率
连接多个表
使用on子句来连接
自然连接中是以具有相同名字的列为连接条件的
可以使用on指定额外的连接条件
这个连接条件是与其他条件分开的
on子句使语句具有更高的易读性
分类
内连接
join on
外连接
左外连接
left join on
右外连接
right join on
子查询
出现再其他语句内部的select语句，成为子查询或内查询
内部嵌套其他select语句的查询，称为外查询或主查询
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应对行子查询
单行子查询
只返回一行
使用单行比较操作符
操作符
< > = <> <= >=
select select_listfrom tablewhere expr_operator (select select_list from table);
谁的工资比abel高
select last_namefrom employeeswhere salary >(select salaryfrom employeeswhere last_name = ''abel)
多行子查询
返回多行
使用多行比较操作符
in/not in
等于列表中的任意一个
any|some
和子查询返回的某一个值比较
all
和子查询返回的所有值比较
any
返回其他部门中比job_id为'it_prog'部门任一工资低的员工的员工号、姓名、job_id以及salary
返回其他部门中比job_id为'it_prog'部门所有工资低的员工的员工号、姓名、job_id以及salary
limit实现分页
前十条记录
第11条至20条记录
第20至30条记录
公式
（当前页数-1）*每页条数，每页条数
limit子句必须放在整个查询语句最后
insert
使用改语法一次只能向表中插入一条数据
向表中插入空值
隐式方法：在列名表中省略该列的值
显式方法：在values子句中指定空指
插入指定的值
NOW()函数：记录当前系统的日期和时间
从其他表中拷贝数据
在insert语句中插入子查询
不必书写values子句
子查询的值列表应与insert子句中的列名对应
update
可以一次更新多条数据
如果需要回滚数据，需要保证在DML之前，进行设置：set autocommit = false；
使用where子句指定需要更新的数据
如果省略where子句，则表中的所有数据将被更新
更新中的数据完整性错误
update stuset id = 15where id = 100;
不存在15号学生
delete
使用delete语句从表中删除数据
delete from tablewhere condition；
使用where子句删除指定的记录
delete from departmentswhere department_name = 'fin';
如果省略where子句，则表中的全部数据将被删除
delete from stu；
删除中的数据完整性错误
delete from stuwhere name = 'zhang';
删除的行包含其余表的外键
创建和管理表
创建
create table语句
必须具备
create table权限
存储空间
必须指定
表明
列名，数据类型，尺寸
确认
describe dept
显示表的所有属性信息
管理
alter table语句
可实现
向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列
追加一个新列
alter table dept80add job_id varchar(15);
删除一个列
alter table dept80drop column job_id;
修改一个列
alter table dept80modify column time2 varchar(30);
重命名一个列
使用change old_column new_column dateType子句重命名列
alter table dept80change 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;
改变对象名称
rename语句
改变表，视图的名称
必须是对象的拥有者
alter table deptrename to detail_dept;
数据库完整性
not null约束
unique约束
可以有多个唯一约束，多个列组合约束
表级约束语法
表示用户名和密码组合不能重复
primary key约束
主键约束相当于唯一约束+非空约束的组合
列级模式
表级模式
组合模式
删除主键约束
alter table emp5drop primary key;
添加主键约束
修改主键约束
alter table emp5modify id int primary key;
foreign key约束
创建外键
视图
一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且在使用视图时动态生成的，至保存了sql的逻辑，不保存查询结果
应用场景
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
创建视图
create view view_nameas select_statement
修改视图
alter view view_nameas select_statement
删除视图
用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限
作用和好处
1.提高重用性，就像一个函数
2.对数据库重构，却不影响程序的运行
3.提高了安全性，可以对不同的用户设定不同的视图
4.让数据更加清晰，想要什么样的数据，就创建什么样的视图
0 条评论
回复 删除
下一页
