mysql进阶
一．视图
视图是一个虚拟的表，不存储任何数据，存储的是一条select语句，视图是对若干个表的引用，是根据我们需要只显示指定数据的一个虚拟的表
视图的作用：
- 同MySQL用户绑定后，使用户方便取得需要的数据，而用户也只能对视图指定的数据进行操作，增加了安全性
- 将复杂的问题简化，在实际使用时我们可能会经常查询某些数据，而这些数据的查询语句十分复杂，我们很难在每次需要时都现场编写select语句，通过视图我们可以将这些复杂的select语句进行存储，再次使用时我们只需调用视图即可
创建视图
格式：creat view [view_name] as [select_statement] from [table]；
其中view_name是我们要创建的视图的名称，select_statement是我们的查询语句，table是我们要查询的表
由于我们创建的视图本质上是一个虚拟的表，所以当我们show tables；时也会被纳入表的列表里
查询视图
格式：select * from [view_name];
我们的视图在使用时和普通的表是一样的，直接用普通的查询语句即可查询
修改视图
视图在本质上是对原表的处理，所以我们在修改或删除视图的数据时原表的数据也会更改。具体操作和普通表的delete和update语句相同，因为视图只是调用数据，不存储数据所以某些情况下无法修改数据。
以下情况无法修改视图的数据：
1.select 子句中包括distinct
2.select 子句中包含组函数
3.select 子句中包含group by
4.select 子句中包含union
例：
有如下表，创建一个只显示学生姓名和班级的视图
MariaDB [hellodb]> select *from students; +-------+----------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+----------------+-----+--------+---------+-----------+ | 1 | Hou Yi | 22 | M | 2 | 3 | | 2 | Ya Se | 22 | M | 1 | 7 | | 3 | An Qila | 53 | F | 2 | 16 | | 4 | Da Ji | 32 | F | 4 | 4 | | 5 | Sun Shangxiang | 26 | F | 3 | 1 | | 6 | Huang Zhong | 46 | M | 5 | NULL | | 7 | Liu Bei | 19 | M | 3 | NULL | | 8 | Guan Yu | 17 | M | 7 | NULL | | 9 | Zhang Fei | 20 | M | 6 | NULL | | 10 | Di Renjie | 39 | M | 3 | NULL | | 11 | Li Yuanfang | 23 | M | 6 | NULL | | 12 | Lan Lingwang | 19 | M | 1 | NULL | | 13 | Wang Zhaojun | 33 | F | 2 | NULL | | 14 | Bai Qi | 17 | M | 3 | NULL | | 15 | A Ke | 19 | F | 4 | NULL | | 16 | Cai Wenji | 21 | F | 1 | NULL | | 17 | Lv Bu | 25 | M | 4 | NULL | | 18 | Diao Chan | 23 | F | 7 | NULL | | 19 | Gong Sunli | 18 | F | 6 | NULL | | 20 | Ming Shiyin | 19 | F | 7 | NULL | | 21 | Dun Shan | 22 | M | 6 | NULL | | 22 | Zhou Yu | 20 | M | 1 | NULL | | 23 | Mi Yue | 0 | F | 4 | NULL | | 24 | Kai | 27 | M | NULL | NULL | | 25 | Sun Wukong | 100 | M | NULL | NULL | +-------+----------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]> create view view_student_class as select name,classid from students; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> select * from view_student_class; +----------------+---------+ | name | classid | +----------------+---------+ | Hou Yi | 2 | | Ya Se | 1 | | An Qila | 2 | | Da Ji | 4 | | Sun Shangxiang | 3 | | Huang Zhong | 5 | | Liu Bei | 3 | | Guan Yu | 7 | | Zhang Fei | 6 | | Di Renjie | 3 | | Li Yuanfang | 6 | | Lan Lingwang | 1 | | Wang Zhaojun | 2 | | Bai Qi | 3 | | A Ke | 4 | | Cai Wenji | 1 | | Lv Bu | 4 | | Diao Chan | 7 | | Gong Sunli | 6 | | Ming Shiyin | 7 | | Dun Shan | 6 | | Zhou Yu | 1 | | Mi Yue | 4 | | Kai | NULL | | Sun Wukong | NULL | +----------------+---------+ 25 rows in set (0.00 sec)
二．触发器
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。简单的来说，就是当数据库发生某种变化时，触发触发器并执行触发器的语句集合。例如我们创建一个日志表，每当我们修改其他表的数据时，就将这次修改存入日志表，这就是一个触发器。
创建触发器
格式：create trigger [trigger_name] [trigger_time] [trigger event] on [table_name] for each row [trigger_body]；
其中
trigger_name：为我们要创建的触发器名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
table_name：表名
trigger event：{ INSERT |UPDATE | DELETE }触发的具体事件
trigger body：触发后执行的语句集合
如果触发的执行语句多余一条则trigger body中的内容应由begin开始，end结束
查看触发器
格式：show trigger\g；
删除触发器
格式：drop trigger [trigger_name]；
例：
//创建一个student_info表 MariaDB [hellodb]> create table student_info( stu_id int(11) primary key auto_increment, stu_name varchar(255) default null); Query OK, 0 rows affected (0.00 sec) //创建一个student_count表 MariaDB [hellodb]> create table student_count( student_count int(11) default 0); Query OK, 0 rows affected (0.00 sec) //在student_count表中添加一行数据0 MariaDB [hellodb]> insert into student_count values (0); Query OK, 1 row affected (0.01 sec) //创建一个触发器，当student_info表中添加数据后student_count中的值加一 MariaDB [hellodb]> create trigger trigger_student_count_insert after insert on student_info for each row update student_count set student_count=student_count+1; Query OK, 0 rows affected (0.00 sec) //创建一个触发器，当student_info表中删除数据后student_count中的值减一 MariaDB [hellodb]> create trigger trigger_student_count_delete after delete on student_info for each row update student_count set student_count=student_count-1; Query OK, 0 rows affected (0.02 sec)
三．mysql用户与权限
1.用户
mysql中账号由两部分组成，一是用户名二是主机名，比如本机的root的账号是“root@localhost”root是用户名，localhost是主机名。在mysql中默认有一个root用户，但由于其权限较大，我们一般使用一个普通的用户。mysql中用户的信息是由mysql.user表来存储的,我们可以通过select user， host， password from mysql.user来查看用户信息，同时我们对用户的操作都可以通过对这个表进行添加，删除或修改来实现，只不过对这个表进行修改后我们需要让数据库重读用户信息。
创建用户
格式：create user [username] identified by ‘[password]’；
重命名
格式：rename user [old_user_name] to [new_user_name]；
删除用户
格式：drop user [username@host]
修改密码
格式：
- set password for [username@host]=password(‘[password]’)；
- update mysql.user set password=password(“[password]”) where host=‘[host]’；
flush privileges；//重新读取用户数据或者重启mysql也可以生效
第一种方法是通过mysql的命令修改密码，第二种方法是通过直接修改用户的数据来修改密码，但第二种方法在改过密码后需要重新读取用户数据才能生效
2.权限
个用户对指定的数据库都有特定的权限，我们可以对一个用户设置权限让他只可以访问某些数据库或者对数据库只有添加，查询的操作
给用户授权
格式：grant [privileges] on [databasename].[tablename] to [username@host]；
其中privileges为用户的操作权限，可选select，insert，delete等若要授予所有权限则为all，databasename和tablename可用*，表示所有数据库或者指定数据库的所有表。privilege可以在后面追加字段以表示只授权这些字段。
使用户可以将自己的权限授予他人
格式：grant [privileges] on [databasename].[tablename] to [username@host] with grant option;
撤销用户权限
格式：revoke [privilege] on [databasename].[tablename] from [username@host]；
查看用户获得的授权
格式：show grants for [username@host];
例：
//创建一个用户，名为xiaozhang，密码为xuexiao，创建用户时如果不指定host则默认为%，即任意host MariaDB [(none)]> create user xiaozhang identified by 'xuexiao'; Query OK, 0 rows affected (0.00 sec) //将用户xiaozhang@%改为xiaozhang@1.1.1.1 MariaDB [(none)]> rename user xiaozhang to xiaozhang@1.1.1.1; Query OK, 0 rows affected (0.00 sec) //给用户xiaozhang授权，获得所有xuexiao.student的权限 MariaDB [(none)]> grant all on xuexiao.student to xiaozhang; Query OK, 0 rows affected (0.00 sec) //查看用户xiaozhang的权限 MariaDB [(none)]> show grants for xiaozhang@1.1.1.1; +----------------------------------------------------------------------------------------------------------------+ | Grants for xiaozhang@1.1.1.1 | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'xiaozhang'@'1.1.1.1' IDENTIFIED BY PASSWORD '*57502EB82B91C6C7D70CC031F3BE79E691B6ABFE' | | GRANT ALL PRIVILEGES ON `xuexiao`.`student` TO 'xiaozhang'@'1.1.1.1' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
浙公网安备 33010602011771号