MySQL数据库管理

上一篇博客我们介绍了数据库的一些基本操作，那么这次就让我们一次来认识一下操作数据库的一些更深入的方法。

一、视图

视图通常分为三种：

简单视图，单张表
复杂视图，多张表
物化视图

（1）简单视图的创建

mysql> create view v1_students as select name,age from students;
Query OK, 0 rows affected (0.01 sec)

（2）复杂视图的创建

mysql> create view v2_students as select s.name student_name,t.name teacher_name from students s join teachers t on s.teacherid=t.tid;
Query OK, 0 rows affected (0.00 sec)

（3）查看指定的视图信息

mysql> show create view v2_students\G;
*************************** 1. row ***************************
                 View: v2_students
          Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2_students` AS select `s`.`Name` AS `student_name`,`t`.`Name` AS `teacher_name` from (`students` `s` join `teachers` `t` on((`s`.`TeacherID` = `t`.`TID`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

（4）查看所有的视图信息

MariaDB [hidb]> select * from information_schema.views\G;
mysql> select * from information_schema.views\G;
*************************** 1. row ***************************
        TABLE_CATALOG: NULL
         TABLE_SCHEMA: hellodb
           TABLE_NAME: v1_students
      VIEW_DEFINITION: select `hellodb`.`students`.`Name` AS `name`,`hellodb`.`students`.`Age` AS `age` from `hellodb`.`students`
         CHECK_OPTION: NONE
         IS_UPDATABLE: YES
              DEFINER: root@localhost
        SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
*************************** 2. row ***************************
        TABLE_CATALOG: NULL
         TABLE_SCHEMA: hellodb
           TABLE_NAME: v2_students
      VIEW_DEFINITION: select `s`.`Name` AS `student_name`,`t`.`Name` AS `teacher_name` from (`hellodb`.`students` `s` join `hellodb`.`teachers` `t` on((`s`.`TeacherID` = `t`.`TID`)))
         CHECK_OPTION: NONE
         IS_UPDATABLE: YES
              DEFINER: root@localhost
        SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
2 rows in set (0.05 sec)

（5）删除视图

MariaDB [hidb]> drop view v1_students;
Query OK, 0 rows affected (0.00 sec)

（6）更新视图数据

mysql> update v2_students set teacher_name='Tie Muzhen' where student_name='da ji';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from teachers;
+-----+------------+-----+--------+
| TID | Name       | Age | Gender |
+-----+------------+-----+--------+
|   1 | Liu Bang   |  45 | M      |
|   2 | Ying Zheng |  94 | M      |
|   3 | Wu Zetian  |  77 | F      |
|   4 | Tie Muzhen |  93 | F      |
+-----+------------+-----+--------+
4 rows in set (0.00 sec)

由这个例子我们可以看出，修改视图实际上修改的是基表，那么需要注意的就是有些情况下是不能修改视图的数据的。分为以下几种情况，select字句中不能包含distinct，组函数，union以及group by 等语句。

二、自定义函数

函数有多种，大致分为自定义和内置函数两种，内置函数也有很多，不需要定义，可直接进行调用，那么我们一起来详细的认识一下自定义函数的用法。

（1）自定义函数的创建

create +function 然后加函数名后跟renturn内容即可。

mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!";
Query OK, 0 rows affected (0.04 sec)

(2) 查看所有函数信息

mysql> show function status\G;
*************************** 1. row ***************************
                   Db: hellodb
                 Name: simpleFun
                 Type: FUNCTION
              Definer: root@localhost
             Modified: 2018-09-06 01:31:30
              Created: 2018-09-06 01:31:30
        Security_type: DEFINER
              Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
   Database Collation: utf8_general_ci
1 row in set (0.00 sec)

（3）查看指定自定义函数的定义

mysql> show create function simplefun\G;
*************************** 1. row ***************************
             Function: simplefun
             sql_mode: 
      Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `simplefun`() RETURNS varchar(20) CHARSET utf8
RETURN "Hello World!"
character_set_client: latin1
collation_connection: latin1_swedish_ci
   Database Collation: utf8_general_ci
1 row in set (0.00 sec)

（4）带参自定义函数

mysql> delimiter //    将分割符定义为//
mysql> 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.03 sec)

mysql> delimiter ;   重新定义为；
mysql> select addtwonumber(10,20);
+-------------------------------+
| addtwonumber(10,20) |
+-------------------------------+
|                  30                    |
+-------------------------------+
1 row in set (0.03 sec)

（5）删除自定义函数

MariaDB [mysql]> drop function simplefun;
Query OK, 0 rows affected (0.00 sec)

三、触发器trigger

触发器是基于两个表之间的联系，一个表发生变化触动触发器然后引起的另一个表也发生变化。

先建两个表

mysql> create table student_info( stu_id int(11) primary key auto_increment,
     -> stu_name varchar(255) default null);
Query OK, 0 rows affected (0.01 sec)
create table student_count( student_count int(11) default 0);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student_count values(0);
Query OK, 1 row affected (0.00 sec) 表的基本操作已完成，接下来就是建立触发器了。

（1）触发器创建

mysql> 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.01 sec)   这里需要注意的是触发器的名字尽量起与之相关的，容易看懂。
mysql> insert into student_info values (1,'wsy');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student_count;
+---------------+
| student_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

（2）查看现有触发器

mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
              Trigger: trigger_student_count_insert
                Event: INSERT
                Table: student_info
            Statement: update student_count set student_count=student_count+1
               Timing: AFTER
              Created: NULL
             sql_mode: 
              Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

（3）删除触发器

mysql>DROP TRIGGER trigger_student_count_insert;

四、存储过程

存储过程：存储过程保存在mysql.proc表中

（1）创建一个无参存储过程

mysql> delimiter //
mysql> create procedure showtime()  begin  select now();    -> end //
Query OK, 0 rows affected (0.00 sec)

(2) 创建一个含参存储过程

mysql>create procedure deletebyld(in uid smallint unsigned,out num smallint unsigned)  begin delete from hellodb.students where stuid=uid; select row_count() into num; end//
mysql> delimiter ;
mysql> call deletebyld(2,@line);
Query OK, 0 rows affected (0.02 sec)
mysql> select @line;
+-------+
| @line |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

（2）存储过程的调用

mysql> call showtime;
+---------------------+
| now()               |
+---------------------+
| 2018-09-06 02:15:54 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

（2）查看指定的存储过程创建

mysql>show create procedure showtime\G;

（3）删除存储过程

mysql> drop procedure showtime;

（4）查看存储过程列表

mysql> SHOW PROCEDURE STATUS\G;
*************************** 1. row ***************************
                   Db: hellodb
                 Name: showtime
                 Type: PROCEDURE
              Definer: root@localhost
             Modified: 2018-09-06 02:15:37
              Created: 2018-09-06 02:15:37
        Security_type: DEFINER
              Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
   Database Collation: utf8_general_ci
1 row in set (0.00 sec)

(5)存储过程的优势

存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中, 当需要时从数据库中直接调用,省去了编译的过程 
提高了运行速度
同时降低网络数据传输量

有关MySQL的相关函数及触发器内容就介绍到这里了。

posted @ 2018-09-25 09:41 www岩阅读(176) 评论(0) 编辑收藏举报

www岩

MySQL数据库管理

公告