4
JackLin的博客
首页
分类
标签
生活
时间轴
关于我
友链
搜索
管理员
数据库期末复习
数据库
MySQL
发布日期:
2020-06-22 16:47:40
阅读量:
232
所属分类:
数据库设计
前言:关于数据库的期末复习,侧重点在代码,存储函数,存储过程,触发器等等。 > 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; ```