数据库期末复习

数据库MySQL

发布日期:2020-06-22 16:47:40

阅读量:191

所属分类: 数据库设计

前言：关于数据库的期末复习，侧重点在代码，存储函数，存储过程，触发器等等。

> SQL语句

```
-- 在语法中：[] 表示可选    <> 表示必选
-- 在进行很多操作时，我们是不知道表名的，所以在书写语句时需要限定一下表名或者列名，而存储函数和存储过程是依赖于数据库的，所以不需要指定表名
-- 所有的代码是伪代码，只是为了方便期末复习查看，并不应能执行成功。

-- 一、第1部分：数据库，表，视图，索引，存储函数，存储过程，约束，触发器

-- 1. 创建删除数据库
create database 数据库名;
drop database 数据库名;

-- 2. 创建，修改，删除表； 后面 4 行代码是 MySQL 提供的约束，将在第 7 点中详细解释，这是是在创建表的同时增加约束，后面将在创建表结束之后修改约束。
-- 代码都是伪代码，方便记忆，所以将多种情况融在了一起。
create table student
(
		number varchar(60) primary key,
		sname varchar(60) not null unique,
		gender varchar(60),
		age int(10) default 0,
		time timestamp default current_timestamp,
		-- 1. 主键约束
		constraint 约束名称 primary key(number, sname),
		-- 2. 唯一约束
		constraint 约束名称 unique(gender),
		-- 3. 检查约束
		constraint 约束名册 check(age > 20 and age < 60),
		-- 4. 外键约束(创建外键约束时，可以不需要前面的 constraint 约束名称 也可以正常运行)
		constraint 约束名称 foreign key(本表中的列名) references 依赖的表名(依赖的表中的列名) on update cascade
);

-- 或者 2.1 
create table 表名 select_语句;

-- 2.2 修改表结构  add, modify, drop
alter table 表名 add 列名 varchar(60) [default 'test'];
alter table 表名 modify 列名 varchar(60) [default 'test'];
alter table 表名 drop 列名

-- 2.3 截断表
truncate table 表名
-- 2.4 删除表
drop table 表名

-- 3. 创建索引
create index 索引名称 on 表名(属性名);
drop index 索引名称 on 表名;

-- 4. 创建视图，修改视图，删除视图;  
-- with check option 表示说插入或修改的数据行必须满足视图所定义的约束条件
create [or replace] view 视图名称 [别名] as sql语句 [with check option];
alter view 视图名称 [别名] as select_语句;
drop view 视图名称;

--------------------------------------------------

-- 5. 存储函数的创建
create function 函数名([参数名 参数类型])
returns 返回值类型
begin
	函数体;
	return 语句;
end;

-- 5.1 调用存储函数
select 函数名([参数值]);

-- 5.2 删除存储函数，后面不需要带括号
drop function 函数名称;

--------------------------------------------------
-- 5. 存储函数举例， 使用 declare 创建变量，使用 set 为变量赋值
-- 5.1 创建存储函数
-- delimiter@@
-- create function name_fn(dno decimal(2), a decimal(5,2), b decimal(5,2))
-- returns varchar(14)
-- begin
-- 	declare x decimal(5,2);
-- 	declare y decimal(5,2);
-- 	set x = a, y = b;
--   return x+y;
-- end@@

delimiter@@
create function name_fn(a decimal(5,2), b decimal(5,2))
returns decimal
begin
	declare x decimal(5,2);
	declare y decimal(5,2);
	set x = a, y = b;
  return x+y;
end@@

deleimter;

set global log_bin_trust_function_creators=TRUE;

-- 5.2 调用存储函数
select name_fno(1, 2);

-- 5.3 删除存储函数
drop function name_fn;

-----------------------------------------------------
-- 6. 创建存储过程
create procedure 存储过程名(
	[in | out | inout] 参数名 参数类型,
	[in | out | inout] 参数名 参数类型
)
begin
	过程体;
end

-- 6.1 调用存储过程
call procedure 存储过程名();
drop procedure 存储过程名;

-------------------------------------------------------

-- 6. 存储过程举例, select ... into ... 给变量赋值，结合 out 参数使用
-- 关于 in， out，inout 更详细的例子可以查看课本 P88
delimiter @@
create procedure process_name(
		in i_1 decimal(2, 0),
		out o_1 varchar(14),
		out o_2 varchar(14)
)
begin
	select test_1, test_2 into o_1, o_2 from test_table where test_3 = i_1;
end@@
delimiter;

call process_name(10, @p_1, @p_2);
select @p_1, @p_2;

select concat('MySql版本信息：', @@version);
---------------------------------------------------------

-- 7. 约束，需要使用 constraint 关键字
-- 这里介绍 4 中约束，在创建表的时候增加约束已经在第 1 点中详细介绍了，这里只介绍在创建表之后如何建立约束
-- 7.1 创建表之后，删除，增加主键约束
alter table 表名 drop primary key;			-- 删除主键约束，主键只有一个，所以不需要指定主键名称
alter table 表名 add constraint 主键名称 primary key(列名);			-- 增加主键约束

-- 7.2 创建表之后，删除，增加唯一约束
alter table 表名 drop index 约束名称；			-- 删除 “唯一约束” ， 注意：这里使用的是 index，而且需要指定约束名称；
alter table 表名 add constraint 约束名称 unique(列名);		-- 增加 “唯一约束”

-- 7.3 创建表之后，删除，增加外键约束
alter table 表名 drop foreign kye 外键约束名称;			-- 删除外键约束，需要指定约束名称
alter table 表名 add constraint 约束名册 foreign key(本表中的列名) references 依赖的表名(依赖的表中的列名) on update cascade;		-- 增加外键约束

---------------------------------------------------------
-- 8. 触发器
create trigger 触发器名称
	before | after 
	insert | delete | update
	on 表名
	for each row
	<触发体>;
	
-- 8.1 删除触发器
drop trigger 触发器名册;			-- 触发器是数据库级别的，所以这里不需要指定表名
	
---------------------------------------------------------
-- 8. 触发器举例,配合 new，old 使用
create trigger test_trigger
	after update
	on department
	for each row
	update employee set deptno = new.deptno where deptno = old.deptno;
	
	
-- 二、第2部分：用户，权限管理，数据的备份与恢复

-- 1. 用户管理：创建用户,修改密码，修改用户名
-- 用户 == 用户名@主机名
grant user 用户名@主机名 identified by 'password';
set password for 用户 = '新密码';
rename user 旧用户 to 新用户;
drop user 用户;

-----------------------------------------------------------
-- 2. 权限管理; 后面可以加上 with grant option
-- 2.1 授予 "MySql 字段级别" 的权限：由于是 “字段级别”，所以要指定数据库名，表名，列名
grant 权限名称(列名)
	on table 数据库名.表名
	to 用户
	[with grant option]
	
-- 2.1  实例
grant select(ename, sal), update(sal)
	on table scott1.emp
	to test_usr@localhost
	with grant option;

-----------------------------------------------------------
-- 2.2 授予 “MySql 表级别” 的权限：由于是 “表级别”，所有只需要指定数据库名，表名
grant 权限名称
	on table 数据库名.表名
	to 用户
	[with grant option]
	
-- 2.2 实例
grant alter, select, insert(empno, ename)
	on table scott1.emp
	to test_usr@localhost
	with grant option;
------------------------------------------------------------
-- 2.3 授予 “MySql 存储程序级别” 的权限“由于是存储过程，存储函数级别的权限，所以不需要指定表名，只需要执行数据库名，存储函数名/存储过程名
grant 权限名称
	on function | references 数据库名.存储函数名/存储过程名
	to 用户
	with grant option;
	
-- 2.3 实例
grant execute
	on procedure scott1.emp_p
	to test_usr@localhost
	with grant option;
	
-----------------------------------------------------------
-- 2.4 授予 “MySql 数据库级别” 的权限：因为是数据库级别，所有范围直接写成 数据库名.*
grant 权限名称
 on 数据库名.*
 to 用户
 with grant option;
 
 -- 2.5 授予 “MySql 服务器管理”
--------------------------------------------------------------------
grant 权限名册
 on *.*
 to 用户
 with grant option;
 
-- 2.6 撤销所有权限
revoke all privileges, grant option from 用户;
-- 2.6.1 撤销指定权限
revoke 权限名称
	on *.* | 数据库名.* | 数据库名.表名
	from 用户;
	
	
-- 3. 角色管理;   角色格式：'角色名'@'主机名'  ；这个角色的格式和用户的格式十分相似
create role 角色;
-- 3.1 授予角色权限：语法和授予用户权限的语法一样
-- 3.2 授予角色给用户
grant 角色 to 用户;
-- 3.3 撤销用户角色
drop role 角色;

-- 4. 数据的备份与恢复：这些命令不能写到查询里面，而是要在控制台的命令行中输入并执行。
-- 如果通过 -p 指定了密码，则可以直接执行；如果没有指定密码，则后面需要输入密码
-- 4.1 备份单个数据库
mysqldump -u 用户名 -h 主机名 -p 密码 数据库名>备份文件名.sql
-- 4.2 备份多个数据库
mysqldump -u 用户名 -h 主机名 -p 密码 --database 数据库名 数据库名>备份文件名.sql
-- 4.3 备份所有数据库
mysqldump -u 用户名 -h 主机名 -p 密码 --all -database>备份文件名.sql
-- 4.4 恢复shuj1
mysql -u 用户名 -p 数据库名<备份文件名.sql			-- 之后会要求输入密码

-- 5. 表数据的导入和导出
mysqldump -u root -p -T '目标路径' 数据库名 表名
-- “目标路径” 可以通过下面语句查询得到
select @@secure_file_priv;

```

JackLin的博客

数据库期末复习