视图函数存储过程和触发器和用户管理

视图 view

MariaDB [hellodb]> create view view_stumessage as (select st.name,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid);
MariaDB [hellodb]> desc view_stumessage;
MariaDB [hellodb]> select * from view_stumessage;
MariaDB [hellodb]> show table status like 'view_stumessage'\G;

MariaDB [hellodb]> create view view_oldstudents as (select * from students where age>30);
MariaDB [hellodb]> select * from view_oldstudents;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+

//插入到了视图中，也插入到表中
MariaDB [hellodb]> insert into view_oldstudents(name,age)values('zhangsan',50);
//插入到了表中，插入视图中失败
MariaDB [hellodb]> insert into view_oldstudents(name,age)values('lisi',20);
MariaDB [hellodb]> drop view view_oldstudents;

自定义函数 UDF

MariaDB [hellodb]> create function simpleFun()returns varchar(20) return "Hello World!";
MariaDB [hellodb]> select simpleFun();
+--------------+
| simpleFun()  |
+--------------+
| Hello World! |
+--------------+

MariaDB [hellodb]> show function status\G
MariaDB [hellodb]> show create function simpleFun\G
MariaDB [hellodb]> select * from mysql.proc\G  //函数的存放位置
MariaDB [hellodb]> drop function simpleFun;

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create function deleteById(uid smallint unsigned)returns varchar(20)
    -> begin
    -> delete from students where stuid = uid;
    -> return (select count(stuid) from students);
    -> end //
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;

MariaDB [hellodb]> select deleteByid(10);
+----------------+
| deleteByid(10) |
+----------------+
| 26             |
+----------------+

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> 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 //
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> select addTwoNumber(10,20);
+---------------------+
| addTwoNumber(10,20) |
+---------------------+
|                  30 |
+---------------------+

procedure 存储过程

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure showTime()
    -> begin
    -> select now();
    -> end //
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call showTime;
+---------------------+
| now()               |
+---------------------+
| 2020-01-23 21:09:24 |
+---------------------+

MariaDB [hellodb]> show procedure status\G

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure selectById(in uid smallint unsigned)
    -> begin
    -> select * from students where stuid = uid;
    -> end //
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call selectById(9);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
+-------+--------------+-----+--------+---------+-----------+

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> 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 //
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call dorepeat(100);
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select @sum;
+------+
| @sum |
+------+
| 5050 |
+------+

MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure deleteById(in uid smallint unsigned, out num smallint unsigned)
    -> begin
    -> delete from students where stuid >=uid;
    -> select row_count() into num;
    -> end//
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call deleteById(12,@Line);
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> select @Line;
+-------+
| @Line |
+-------+
|    16 |
+-------+

trigger 触发器

MariaDB [hellodb]> create table student_info(
    -> stu_id int(11) not null auto_increment,
    -> stu_name varchar(255) default null,
    -> primary key(stu_id)
    -> );
MariaDB [hellodb]> create table student_count(
    -> student_count int(11) default 0
    -> );
MariaDB [hellodb]> insert into student_count values(0);

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;
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;

MariaDB [hellodb]> insert into student_info values(1,'a');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
|      1 | a        |
+--------+----------+
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
|             1 |
+---------------+

MariaDB [hellodb]> show triggers\G
MariaDB [hellodb]> select * from information_schema.triggers\G
MariaDB [hellodb]> drop trigger trigger_student_count_insert;
MariaDB [hellodb]> drop trigger trigger_student_count_delete;

用户账号和权限管理

MariaDB [mysql]> select user,host,password from user;
+------+----------------+----------+
| user | host           | password |
+------+----------------+----------+
| root | localhost      |          |
| root | centos7-19.com |          |
| root | 127.0.0.1      |          |
| root | ::1            |          |
|      | localhost      |          |
|      | centos7-19.com |          |
+------+----------------+----------+

版本高的数据库密码存放在 mysql.user.authentication_string字段中
MariaDB [mysql]> create user test@'192.168.209.%' identified by 'centos';
-- 连接远程数据库主机
[root@Centos6-9 ~]# mysql -utest -pcentos -h 192.168.209.19 -P3306 
MariaDB [mysql]> rename user test@'192.168.209.%' to alice@'192.168.209.%';//改名
MariaDB [mysql]> drop user ''@'centos7-19.com'; // 删除用户
MariaDB [mysql]> set password for 'root'@'localhost'=password('centos'); //设置密码
[root@centos7-19 ~]# mysql -uroot -pcentos   //默认以 root@localhost

[root@centos7-19 ~]# mysql -uroot -p123 -h 127.0.0.1  //不能登陆到数据库，作反向解析了
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
skip_name_resolve 写入到配置文件中重启，root@127.0.0.1 就可以登陆了

MariaDB [mysql]> update user set password=password('123') where user='root' and host='centos7-19.com'; //修改密码
MariaDB [mysql]> flush privileges;

-- 忘记了数据库root的密码怎么办？
[root@centos7-19 ~]# vim /etc/my.cnf //修改，重启生效
[mysqld]
skip-grant-tables
skip-networking
关闭了3306端口号，只能本地连接数据库，不用密码
然后修改mysql.user.password字段的值或重新设置密码
删除配置文件/etc/my.cnf以上两行，重新启动数据库

-- 创建用户并授权
MariaDB [hellodb]> grant all on hellodb.* to test@'192.168.209.29' identified by '123';
-- 查看权限
MariaDB [hellodb]> show grants for test@'192.168.209.29';
MariaDB [hellodb]> show grants for current_user();
-- 取消删除的权限
MariaDB [hellodb]> revoke delete on hellodb.* from test@'192.168.209.29';
MariaDB [hellodb]> revoke drop on hellodb.* from test@'192.168.209.29';