数据库系统开发与安全防护-D2

一、SQL语句

前言

DDL——数据定义语言(CREATE，ALTER，DROP，DECLARE)

DML——数据操作语言(SELECT，DELETE，UPDATE，INSERT)

DCL——数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

SQL基础语句

DDL数据定义语言

操作库
create database db1; -- 创建库
create database if not exists db1; -- 创建库是否存在，不存在则创建
show databases; -- 查看所有数据库
show create database db1; -- 查看某个数据库的定义信息
alter database db1 character set utf8; -- 修改数据库字符信息
drop database db1; -- 删除数据库

操作表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp ); --创建表
desc 表名; -- 查看表结构
show create table 表名; -- 查看创建表的SQL语句
alter table 表名 rename to 新的表名; -- 修改表名
alter table 表名 add 列名 数据类型; -- 添加一列
alter table 表名 drop 列名; -- 删除列
drop table 表名;
drop table if exists 表名 ; -- 删除表

DML数据操作语言

插入insert
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n); -- 写全所有列名
insert into 表名 values(值1,值2,...值n); -- 不写列名（所有列全部添加）
insert into 表名(列名1,列名2) values(值1,值2); -- 插入部分数据

DCL数据控制语言

删除 delete
delete from 表名 where 列名 = 值; -- 删除表中数据
delete from 表名; -- 删除表中所有数据
truncate table 表名; -- 删除表中所有数据（高效，先删除表，然后再创建一张一样的表）

修改update
update 表名 set 列名 = 值; -- 不带条件的修改(会修改所有行)
update 表名 set 列名 = 值 where 列名=值; -- 带条件的修改

管理用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; -- 添加用户 DROP USER '用户名'@'主机名'; -- 删除用户

权限管理
SHOW GRANTS FOR '用户名'@'主机名'; --查询权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; --授予权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; --撤销权限

二、数据库恢复与备份

数据库恢复模式

完整恢复模式
最高等级，对数据库的所有操作都记录在事务日志中，可以利用事务日志将数据库还原到具体时间点
但会消耗大量的磁盘存储空间
利用存储成本获得更高的数据安全成本，生产型数据库通常采用

大容量日志记录恢复模式
完整恢复模式的补充模式，通过数据备份和日志备份来还原数据库，使用最小方式记录大多数大容量操作，减少日志空间使用量，比完整恢复模式节省了日志存储空间
create index，select into 等操作不会记录在事务日志中，理论上可以恢复到任何备份的结尾，但不支持具体时间点恢复
可以暂时切换成大容量日志记录恢复模式可以提高性能并减少日志空间使用量
需要在增大日志备份与增加数据丢失风险之间进行权衡

简单恢复模式
仅适用于规模较小或数据不经常改变的数据库
可以通过执行完全数据库备份和增量数据库备份来还原数据库，但只能还原到执行备份操作的时间点，只能恢复到已丢失数据的最新备份，无法恢复到具体时间点
工作丢失风险大
执行备份操作之后的所有数据修改都丢失并需要重建
耗费较少磁盘空间，恢复模式最简单
在此模式下，备份间隔应尽可能短

数据库备份

完整备份
按照常规定期备份数据库（所有内容）
如果数据库很大，在完整备份时就需要花费很多时间和占用很大存储空间

差异备份
对前一次完整备份后的差异数据进行备份，差异备份必须要在完整备份后才能成功（以完整备份为基础，备份差异）
比完整备份数据量小，恢复数据速度也快

事务日志备份
事务日志文件和数据文件是sql server数据库两个基本文件
先做一个数据库的完整备份，第一次事务日志备份记录的是当前数据库与完整数据库备份的事务日志差异，以后每次事务日志备份都是对上一次事务日志备份的差异备份，
事务日志备份与差异备份的基准点不一样，且它支持数据恢复到具体的时间点

文件和文件组备份（少见）
当数据库很大需要使用很多文件存储，且一个晚上备份不完时，可以使用文件和文件组备份，每晚备份一部分

备份模式恢复特点描述
完整备份：还原时直接还原到备份时间点上
完整备份+差异备份：有一个完整备份和多个差异备份文件，还原时可以还原至差异备份时间点上
完整备份+事务日志备份：最大的优势是可以还原到任意时间点上
完整备份+差异备份+事务日志备份：可以灵活选择，可以根据不同的要求采取不同的还原策略

数据库备份要素

备份频率
原则是数据备份库必须尽可能地减少数据损失
应该根据数据库的重要性来决定

自动备份
SQL Server提供的SQL作业代理功能可以完成自动备份工作，极大减少了数据库备份的工作量

本地备份与异地备份

三、MySQL数据库

MySQL数据库登录、退出方式

登录
cmd> mysql -u用户名 -p密码
cmd> mysql --host=ip地址 --user=用户名 --password=密码
退出
quit或exit或Ctrl+D

mysqladmin命令

MySQL命令

-A --no-auto-rehash 禁止补全
-u --user= 用户名，默认为root
-h --host= 服务器主机，默认为localhost
-p --password= 用户密码，建议使用-p，默认为空密码
-P --port= 服务器端口
-S --socket= 指定连接socket文件路径
-D --database= 指定默认数据库
-C --compress 启用压缩
-e "SQL" 执行SQL命令
-V --version 显示版本
-v --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置

mysqladmin命令

mysqladmin -u用户 -p密码 ping #查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -uroot -pcento shutdown #关闭mysql服务，但mysqladmin命令无法开启
mysqladmin -uroot -pcentos create testdb #创建数据库testdb
mysqladmin -uroot -pcentos drop testdb #删除数据库testdb
mysqladmin -uroot -pcentos password 'magedu' #修改root密码
mysqladmin -uroot -pcentos flush-logs #日志滚动，生成新文件