一、视图
视图:VIEW,虚表,保存有实表的查询结果,在视图插入的内容都会存入表中。
创建方法:
CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图定义:SHOW CREATE VIEW view_name
删除视图:
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
视图中的数据事实上存储于“基表”中,因此,其修改操作也会针对基表实现;其修改操作受基表限制
(1) 创建一个视图:
create view v_students as select stuid,name,age from students; 创建一个新的视图,起名为v_students
create view v_old_students as select stuid,name,age from students where age > 50; 将视图大于50岁以上的名字进行显示。
inster v_old_students values(27,'li',20);此时可以在视图中添加一个20岁的内容,实际添加到表里边,而视图中不会显示。
二、函数
1、函数:系统函数和自定义函数
系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
2、自定义函数 (user-defined function UDF)
保存在mysql.proc表中 创建UDF CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...]) RETURNS {STRING|INTEGER|REAL} runtime_body
说明:参数可以有多个,也可以没有参数必须有且只有一个返回值
创建函数
示例:无参UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!”; 查看函数列表: SHOW FUNCTION STATUS; 查看函数定义 SHOW CREATE FUNCTION function_name 删除UDF: DROP FUNCTION function_name 调用自定义函数语法: SELECT function_name(parameter_value,...)
示例:有参数UDF
DELIMITER // CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) BEGIN DELETE FROM students WHERE stuid = uid; RETURN (SELECT COUNT(stuid) FROM students); END// DELIMITER ;
自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明:局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义
示例:
DELIMITER // CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) RETURNS SMALLINT BEGIN DECLARE a, b SMALLINT UNSIGNED; SET a = x, b = y; RETURN a+b; END// DELIMITER ;
为变量赋值语法
SET parameter_name = value[,parameter_name = value...
SELECT INTO parameter_name
示例:
... DECLARE x int; SELECT COUNT(id) FROM tdb_name INTO x; RETURN x; END//
三、存储过程
存储过程优势
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程提高了运行速度,同时降低网络数据传输量 存储过程与自定义函数的区别 存储过程实现的过程要复杂一些,而函数的针对性较强 存储过程可以有多个返回值,而自定义函数只有一个返回值 存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用
存储过程:存储过程保存在mysql.proc表中
创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) routime_body proc_parameter : [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型
查看存储过程列表
SHOW PROCEDURE STATUS;
查看存储过程定义
SHOW CREATE PROCEDURE sp_name
调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter ...]]) CALL sp_name
说明:当无参时,可以省略"()",当有参数时,不可省略"()”
存储过程修改
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改 存储过程体,所以要修改存储过程,方法就是删除重建
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
存储过程示例:
创建无参存储过程
delimiter // CREATE PROCEDURE showTime() BEGIN SELECT now(); END// delimiter ; CALL showTime;
创建含参存储过程:只有一个IN参数
delimiter // CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED) BEGIN SELECT * FROM students WHERE stuid = uid; END// delimiter ; call selectById(2);
示例:
delimiter // CREATE PROCEDURE dorepeat(n INT) BEGIN SET @i = 0; SET @sum = 0; REPEAT SET @sum = @sum+@i; SET @i = @i + 1; UNTIL @i > n END REPEAT; END// delimiter ; CALL dorepeat(100); SELECT @sum;
创建含参存储过程:包含IN参数和OUT参数:
delimiter // CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED) BEGIN DELETE FROM students WHERE stuid >= uid; SELECT row_count() into num; END// delimiter ; call deleteById(2,@Line); SELECT @Line;
说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数。
四、触发器
触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行
创建触发器
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
说明:
trigger_name:触发器的名称 trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发 trigger_event::{ INSERT |UPDATE | DELETE },触发的具体事件 tbl_name:该触发器作用在表名
示例:
CREATE TABLE student_info ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (stu_id) ); CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); INSERT INTO student_count VALUES(0);
示例:创建触发器,在向学生表INSERT数据时,学生数增加,DELETE学生时,学生数减少
CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count+1; CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count-1;
查看触发器
SHOW TRIGGERS
查询系统表information_schema.triggers的方式指定查询条件,查看指定的触发器信息。
mysql> USE information_schema; Database changed mysql> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';
删除触发器
DROP TRIGGER trigger_name;
五、MySQL用户和权限管理
1、元数据数据库:mysql
系统授权表:
db, host, user columns_priv, tables_priv, procs_priv, proxies_priv
用户账号:
'USERNAME'@'HOST' 允许用户通过哪些主机远程连接mysqld 服务 @'HOST': 主机名 IP地址或Network 通配符: % _ 示例:172.16.%.%
2、用户管理
创建用户:CREATE USER
示例:
create user test@'192.168.34.%' identified by 'centos';添加test账号在192.168.34这个网段,可以输centos密码连接 select user,host from user; 在创建新数据库上查询哪些host主机登陆了当前的mysql数据库。
默认权限:USAGE
用户重命名:RENAME USER
RENAME USER old_user_name TO new_user_name;
删除用户:
DROP USER 'USERNAME'@'HOST‘
示例:
drop user ''@'localhost'; 删除空的匿名用户 drop user ''@'centos7-1'; 空值部分要加单引号。
示例:删除默认的空用户
DROP USER ''@'localhost';
(1)创建用户:
create user test@'192.168.34.%' identified by 'centos';
(2)在另一台主机上登陆mysql数据库:
mysql -utest -pcentos -h192.168.34.102;(IP地址是要远程连接到主机的IP地址)
(3)查询当前登陆到mysql的账号信息:
select user,host from user;
(4)删除空的匿名用户账号:
drop user ''@'localhost'; 删除空的匿名用户
drop user ''@'centos7-1'; 空值部分要加单引号。
修改密码:
SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
示例:
1、set password for test@'192.168.34.%'=password('biubiu'); 此方法立即生效 2、UPDATE mysql.user SET password=PASSWORD('password') WHERE clause; 此方法需要执行下面指令才能生效: FLUSH PRIVILEGES;
#mysqladmin -u root -poldpass password ‘newpass’ 也可以改口令。
忘记管理员密码的解决办法:
1、启动mysqld进程时,为其使用如下选项: skip-grant-tables 忽略授权表,取消数据库的授权 skip-networking 数据库没有网络功能,避免其他用户连接不输入口令连接。 2、使用UPDATE命令修改管理员密码 3、关闭mysqld进程,移除上述两个选项,重启mysqld
破解数据库步骤:
(1)vim /etc/my.cnf 将mysql数据库配置文件打开
vim /etc/my.cnf [mysqld] skip-grant-tables skip-networking
(2)重启mysql服务
systemctl restart mariadb
(3)更新数据库密码:
update mysql.user set password=password('magedu') where user='root';
(4)再将之前写入Mysql配置文件内容注释掉:
[root@centos7~]#vim /etc/my.cnf [mysqld] #skip-grant-tables
(5)重启Mysql服务
systemctl restart mariadb
(6)用修改后的密码就可以登录了
mysql -pmagedu
3、权限类别:
管理类
程序类
数据库级别
表级别
字段级别
1、管理类:
CREATE TEMPORARY TABLES CREATE USER FILE SUPER SHOW DATABASES RELOAD SHUTDOWN REPLICATION SLAVE REPLICATION CLIENT LOCK TABLES PROCESS
2、程序类: FUNCTION、PROCEDURE、TRIGGER
CREATE ALTER DROP EXCUTE
3、库和表级别:DATABASE、TABLE
ALTER CREATE CREATE VIEW DROP INDEX SHOW VIEW GRANT OPTION:能将自己获得的权限转赠给其他用户
4、数据操作
SELECT INSERT DELETE UPDATE
5、字段级别
SELECT(col1,col2,...) UPDATE(col1,col2,...) INSERT(col1,col2,...)
6、所有权限
ALL PRIVILEGES 或 ALL
4、授权和回收授权
授权:
参考:https://dev.mysql.com/doc/refman/5.7/en/grant.html
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION]; 授权并创建账
(1) priv_type: ALL [PRIVILEGES] 授权类型: insert增,delete删 , update改,select查,all所有权限 (2) db_name.tb_name: 对哪个数据库的哪个表授权: *.*: 所有库的所有表 db_name.*: 指定库的所有表 db_name.tb_name: 指定库的指定表 db_name.routine_name :指定库的存储过程和函数、触发器
示例:
grant all on hellodb.* to test2@'192.168.34.%' identified by 'centos'; 创建test2用户,允许其在所有主机通过centos密码登录,对hellodb库的所有表有所有权限 grant select(name,age) on hellodb.students to test3@'192.168.34.%' identified by 'centos'; 创建test3用户,允许其在所有主机通过centos密码登录,对hellodb库的name和age有查看权限。 show grants for test2@'192.168.34.%' G 可以查看当前用户的授权情况,G换行显示
回收授权:
REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host 示例: revoke select on *.* from test3@'192.168.34.%'; 收回test3的select 权限 revoke delete on *.* from test3@'192.168.34.%'; 收回test3 的delete 权限
查看指定用户获得的授权
Help SHOW GRANTS SHOW GRANTS FOR 'user'@'host'; SHOW GRANTS FOR CURRENT_USER[()];
注意:
① MariaDB 服务进程启动时会读取mysql 库中所有授权表至内存
② GRANT 或REVOKE 等执行权限操作会保存于系统表中,MariaDB 的服务进程通常会自动重读授权表,使之生效
③ 对于不能够或不能及时重读授权表的命令,可手动让MariaDB 的服务进程重读授权表:
mysql> FLUSH PRIVILEGES;