Linux下MySQL的简单使用（二）
上一篇我们介绍到一些Linux的简单命令的使用，今天我们更深入了解一下MySQL！
一.多表查询
这篇通过实例来讲述多表查询，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 首先先列举本篇用到的分类(内连接，外连接，交叉连接)和连接方法：
A）内连接：join，inner join
B）外连接：left join，left outer join，right join，right outer join，union
C）交叉连接：cross join
首先导入hellodb.sql，以下的操作都是基于此：
1.内连接：仅查看有老师的学员和有学员的老师的信息
mysql> select s.name student_name,t.name teacher_name from students s inner join teachers t on s.teacherid=t.tid;+----------------+---------------+
| student_name | teacher_name |
+----------------+---------------+
| Sun Shangxiang | Liu Bang |
| Hou Yi | Wu Zetian |
| Da Ji | Cheng Jisihan |
+----------------+---------------+
3 rows in set (0.67 sec)
2.左内连接：显示所有学员信息，包含有老师的学员和没老师的学员
mysql> select s.name student_name,t.name teacher_name from students s left join teachers t on s.teacherid=t.tid;
+----------------+---------------+
| student_name | teacher_name |
+----------------+---------------+
| Hou Yi | Wu Zetian |
| Ya Se | NULL |
| An Qila | NULL |
| Da Ji | Cheng Jisihan |
| Sun Shangxiang | Liu Bang |
| Huang Zhong | NULL |
| Liu Bei | NULL |
| Guan Yu | NULL |
| Zhang Fei | NULL |
| Di Renjie | NULL |
| Li Yuanfang | NULL |
| Lan Lingwang | NULL |
| Wang Zhaojun | NULL |
| Bai Qi | NULL |
| A Ke | NULL |
| Cai Wenji | NULL |
| Lv Bu | NULL |
| Diao Chan | NULL |
| Gong Sunli | NULL |
| Ming Shiyin | NULL |
| Dun Shan | NULL |
| Zhou Yu | NULL |
| Mi Yue | NULL |
| Kai | NULL |
| Sun Wukong | NULL |
+----------------+---------------+
25 rows in set (0.05 sec)
3.右内连接：显示所有的老师信息，包含有学员的老师，和没学员的老师
mysql> select s.name student_name,t.name teacher_name from students s right join teachers t on s.teacherid=t.tid;
+----------------+---------------+
| student_name | teacher_name |
+----------------+---------------+
| Sun Shangxiang | Liu Bang |
| NULL | Ying Zheng |
| Hou Yi | Wu Zetian |
| Da Ji | Cheng Jisihan |
+----------------+---------------+
4 rows in set (0.00 sec)
4.左外连接：仅显示没老师的学员
select s.name student_name,t.name teacher_name from students s left join teachers t on s.teacherid=t.tid where t.name is null;
+--------------+--------------+
| student_name | teacher_name |
+--------------+--------------+
| Ya Se | NULL |
| An Qila | NULL |
| Huang Zhong | NULL |
| Liu Bei | NULL |
| Guan Yu | NULL |
| Zhang Fei | NULL |
| Di Renjie | NULL |
| Li Yuanfang | NULL |
| Lan Lingwang | NULL |
| Wang Zhaojun | NULL |
| Bai Qi | NULL |
| A Ke | NULL |
| Cai Wenji | NULL |
| Lv Bu | NULL |
| Diao Chan | NULL |
| Gong Sunli | NULL |
| Ming Shiyin | NULL |
| Dun Shan | NULL |
| Zhou Yu | NULL |
| Mi Yue | NULL |
| Kai | NULL |
| Sun Wukong | NULL |
+--------------+--------------+
22 rows in set (0.00 sec)
5.右外连接：仅显示没学员的老师
select s.name student_name,t.name teacher_name from students s right join teachers t on s.teacherid=t.tid where s.name is null;
+--------------+--------------+
| student_name | teacher_name |
+--------------+--------------+
| NULL | Ying Zheng |
+--------------+--------------+
1 row in set (0.00 sec)
6.并集：显示所有的老师和学员
mysql> select s.name student_name,t.name teacher_name from students s left join teachers t on s.teacherid=t.tid
-> union
-> select s.name student_name,t.name teacher_name from students s right join teachers t on s.teacherid=t.tid;+----------------+---------------+
| student_name | teacher_name |
+----------------+---------------+
| Hou Yi | Wu Zetian |
| Ya Se | NULL |
| An Qila | NULL |
| Da Ji | Cheng Jisihan |
| Sun Shangxiang | Liu Bang |
| Huang Zhong | NULL |
| Liu Bei | NULL |
| Guan Yu | NULL |
| Zhang Fei | NULL |
| Di Renjie | NULL |
| Li Yuanfang | NULL |
| Lan Lingwang | NULL |
| Wang Zhaojun | NULL |
| Bai Qi | NULL |
| A Ke | NULL |
| Cai Wenji | NULL |
| Lv Bu | NULL |
| Diao Chan | NULL |
| Gong Sunli | NULL |
| Ming Shiyin | NULL |
| Dun Shan | NULL |
| Zhou Yu | NULL |
| Mi Yue | NULL |
| Kai | NULL |
| Sun Wukong | NULL |
| NULL | Ying Zheng |
+----------------+---------------+
26 rows in set (0.00 sec)
二.视图
视图是一种虚拟存在的表，视图可以理解为是一个容器，表通过条件查询之后，将查询后的结果放入这个容器内，然后给容器命名后即为视图。
视图相对于表的优势:
1.简单：使用视图的用户不必关系后面的表，只需要使用过滤好的内容就行了；
2.安全：因为对表的全新不能限制到表的行或者是列，所以可以通过视图来限制用户对表的访问权限；
3.数据独立：确定了视图的结构之后，如果给原来的表增加了列，并不会影响视图，增加行，视图的相对于的行也会增加，如果源表的列名称发生了改变，可以通过修改视图来解决。
例：如下图在单表上创建视图，选择表的name，age列作为视图v1_student的列。
mysql> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> create view v1_students as select name,age from students;
Query OK, 0 rows affected (0.00 sec)
查看已创建的视图
mysql> show create view v1_students\G;
*************************** 1. row ***************************
View: v1_students
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_students` AS select `students`.`Name` AS `name`,`students`.`Age` AS `age` from `students`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)
删除已创建的视图
mysql> drop view v1_students;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.views\G;
Empty set (0.06 sec)
三.函数
函数分为系统函数和自定义函数
系统函数：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary ref.html，在这个官方的MySQL网站中，可以查看所有的系统函数。
自定义函数：(user-defined function UDF），保存在mysql.proc表中
1.创建一个自定义函数：
mysql> CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!";
Query OK, 0 rows affected (0.05 sec)
mysql> select simplefun();
+--------------+
| simplefun() |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.05 sec)
2.查看所有的函数列表
mysql> show function status;
+---------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| hellodb | simpleFun | FUNCTION | root@localhost | 2018-09-26 23:33:33 | 2018-09-26 23:33:33 | DEFINER | | latin1 | latin1_swedish_ci | 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> drop function simplefun;
Query OK, 0 rows affected (0.00 sec)
mysql> show function status\G;
Empty set (0.00 sec)
四.用户管理和授权
1.创建用户
语法：create user 用户名@主机 identified by 密码；
mysql> create user momoda@localhost identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+--------+--------------------+-------------------------------------------+
| user | host | password |
+--------+--------------------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | centos6.magedu.com | |
| root | 127.0.0.1 | |
| | localhost | |
| | centos6.magedu.com | |
| momoda | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
+--------+--------------------+-------------------------------------------+
6 rows in set (0.01 sec)
2.删除用户
mysql> drop user momoda@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+------+--------------------+-------------------------------------------+
| user | host | password |
+------+--------------------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | centos6.magedu.com | |
| root | 127.0.0.1 | |
| | localhost | |
| | centos6.magedu.com | |
+------+--------------------+-------------------------------------------+
5 rows in set (0.00 sec)
3.授权
grant不仅可以用来授权，还可以用来创建用户；
授权的语法：grant 权限列表 on 库名.表名 to 用户@主机 identified by '密码';
刷新权限：flush privileges;
mysql> grant all on *.* to haha@'192.168.%.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+------+--------------------+-------------------------------------------+
| user | host | password |
+------+--------------------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| root | centos6.magedu.com | |
| root | 127.0.0.1 | |
| | localhost | |
| | centos6.magedu.com | |
| haha | 192.168.%.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+--------------------+-------------------------------------------+
6 rows in set (0.00 sec)
4.查看授权
mysql> show grants for haha@'192.168.%.%';
+------------------------------------------------------------------------------------------------------------------------+
| Grants for haha@192.168.%.% |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'haha'@'192.168.%.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
5.回收授权
回收权限有两种方法：删除用户和revoke
回收语法： revoke 权限 on 库名.表名 from 用户@主机；
mysql> revoke all on *.* from haha@'192.168.%.%';
Query OK, 0 rows affected (0.00 sec)
