mysql地理数据库_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_ci1 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_ci2 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.00sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> 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 functionstatus\G;*************************** 1. row ***************************Db: hellodb

Name: simpleFun

Type:FUNCTIONDefiner: root@localhost

Modified:2018-09-06 01:31:30Created:2018-09-06 01:31:30Security_type: DEFINER

Comment:

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci1 row in set (0.00 sec)

(3)查看指定自定义函数的定义

mysql> show create functionsimplefun\G;*************************** 1. row ***************************

Function: simplefun

sql_mode:

CreateFunction: CREATE DEFINER=`root`@`localhost` FUNCTION `simplefun`() RETURNS varchar(20) CHARSET utf8RETURN "Hello World!"character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci1 row in set (0.00 sec)

(4)带参自定义函数

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

mysql>delimiter ; 重新定义为；

mysql> select addtwonumber(10,20);+-------------------------------+

| addtwonumber(10,20) |

+-------------------------------+

| 30 |

+-------------------------------+

1 row in set (0.03 sec)

(5)删除自定义函数

MariaDB [mysql]> drop functionsimplefun;

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

Query OK,0 rows affected (0.01sec)

create table student_count( student_count int(11) default 0);

Query OK,0 rows affected (0.01sec)

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.01sec) 这里需要注意的是触发器的名字尽量起与之相关的，容易看懂。

mysql> insert into student_info values (1,'wsy');

Query OK,1 row affected (0.00sec)

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+1Timing: AFTER

Created: NULL

sql_mode:

Definer: root@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci1 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.02sec)

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.00sec)

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:37Created:2018-09-06 02:15:37Security_type: DEFINER

Comment:

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: utf8_general_ci1 row in set (0.00 sec)

(5)存储过程的优势

存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中, 当需要时从数据库中直接调用,省去了编译的过程 

提高了运行速度

同时降低网络数据传输量

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