mysql> select * from user;
+—-+———————+——+——+——+—————————————————————–+—————————+—————-+——+———-+———-+
| id | username | pass | uid | gid | comment | homedir | shell | sex | homedir2 | pay |
+—-+———————+——+——+——+—————————————————————–+—————————+—————-+——+———-+———-+
| 1 | root | x | 0 | 0 | root | NULL | /bin/bash | boy | /123 | 5000.00 |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | boy | /123 | 5000.00 |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | boy | /123 | 5000.00 |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | boy | /123 | 5000.00 |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | boy | /123 | 5000.00 |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync | boy | /123 | 5000.00 |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | boy | /123 | 5000.00 |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | boy | /123 | 5000.00 |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | boy | /123 | 5000.00 |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin | girl | /123 | 99999.99 |
| 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | girl | /123 | 99999.99 |
| 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | girl | /123 | 99999.99 |
| 13 | nobody | x | 99 | 99 | Nobody | / | /123456 | girl | /123 | 99999.99 |
| 14 | systemd-network | x | 192 | 192 | systemd Network Management | / | /sbin/nologin | boy | /123 | 5000.00 |
| 15 | dbus | x | 81 | 81 | System message bus | / | /123456 | girl | /123 | 99999.99 |
| 18 | rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin | girl | /123 | 99999.99 |
| 21 | abrt | x | 173 | 173 | | /etc/abrt | /sbin/nologin | boy | /123 | 5000.00 |
| 22 | rtkit | x | 172 | 172 | RealtimeKit | /proc | /sbin/nologin | boy | /123 | 5000.00 |
| 23 | radvd | x | 75 | 75 | radvd user | / | /123456 | girl | /123 | 99999.99 |
| 25 | tss | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null | /123456 | girl | /123 | 99999.99 |
| 26 | usbmuxd | x | 113 | 113 | usbmuxd user | / | /sbin/nologin | boy | /123 | 5000.00 |
| 28 | qemu | x | 107 | 107 | qemu user | / | /sbin/nologin | boy | /123 | 5000.00 |
| 29 | rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin | girl | /123 | 99999.99 |
| 32 | pulse | x | 171 | 171 | PulseAudio System Daemon | /var/run/pulse | /sbin/nologin | boy | /123 | 5000.00 |
| 33 | gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin | girl | /123 | 99999.99 |
| 34 | gnome-initial-setup | x | 992 | 987 | | /run/gnome-initial-setup/ | /sbin/nologin | boy | /123 | 5000.00 |
| 35 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /123456 | girl | /123 | 99999.99 |
| 36 | avahi | x | 70 | 70 | Avahi mDNS/DNS-SD Stack | /var/run/avahi-daemon | /123456 | girl | /123 | 99999.99 |
| 37 | postfix | x | 89 | 89 | | /var/spool/postfix | /123456 | girl | /123 | 99999.99 |
| 38 | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | girl | /123 | 99999.99 |
| 39 | tcpdump | x | 72 | 72 | | / | /123456 | girl | /123 | 99999.99 |
| 41 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | girl | /123 | 99999.99 |
| 42 | zhangsan | NULL | 111 | NULL | NULL | NULL | NULL | boy | /123 | 5000.00 |
+—-+———————+——+——+——+—————————————————————–+—————————+—————-+——+———-+———-+
33 rows in set (0.00 sec)
创建视图
mysql> create view v1 as select username,pass,uid,gid from user;
Query OK, 0 rows affected (0.24 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| a_t |
| b_t |
| db1 |
| intab |
| name1 |
| name2 |
| name3 |
| name4 |
| name5 |
| t_a |
| t_b |
| tea |
| tea2 |
| tea4 |
| tea6 |
| tea7 |
| tea71 |
| tea8 |
| tea9 |
| user |
| v1 |
+—————-+
21 rows in set (0.00 sec)
如何知道是不是视图，看下创建结构就知道了
mysql> show create table v1;
+——+—————————————————————————————————————————————————————————————————————-+———————-+———————-+
| View | Create View | character_set_client | collation_connection |
+——+—————————————————————————————————————————————————————————————————————-+———————-+———————-+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `user`.`username` AS `username`,`user`.`pass` AS `pass`,`user`.`uid` AS `uid`,`user`.`gid` AS `gid` from `user` | utf8 | utf8_general_ci |
+——+—————————————————————————————————————————————————————————————————————-+———————-+———————-+
1 row in set (0.00 sec)
对视图进行操作，基表也会进行相应的操作
mysql> select * from v1;
+———————+——+——+——+
| username | pass | uid | gid |
+———————+——+——+——+
| root | x | 0 | 0 |
| bin | x | 1 | 1 |
| daemon | x | 2 | 2 |
| adm | x | 3 | 4 |
| lp | x | 4 | 7 |
| sync | x | 5 | 0 |
| shutdown | x | 6 | 0 |
| halt | x | 7 | 0 |
| mail | x | 8 | 12 |
| operator | x | 11 | 0 |
| games | x | 12 | 100 |
| ftp | x | 14 | 50 |
| nobody | x | 99 | 99 |
| systemd-network | x | 192 | 192 |
| dbus | x | 81 | 81 |
| rpc | x | 32 | 32 |
| abrt | x | 173 | 173 |
| rtkit | x | 172 | 172 |
| radvd | x | 75 | 75 |
| tss | x | 59 | 59 |
| usbmuxd | x | 113 | 113 |
| qemu | x | 107 | 107 |
| rpcuser | x | 29 | 29 |
| pulse | x | 171 | 171 |
| gdm | x | 42 | 42 |
| gnome-initial-setup | x | 992 | 987 |
| sshd | x | 74 | 74 |
| avahi | x | 70 | 70 |
| postfix | x | 89 | 89 |
| ntp | x | 38 | 38 |
| tcpdump | x | 72 | 72 |
| mysql | x | 27 | 27 |
| zhangsan | NULL | 111 | NULL |
+———————+——+——+——+
33 rows in set (0.00 sec)
修改视图表
mysql> update v1 set username=”root2″ where username=”root”;
Query OK, 1 row affected (10.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看基表
mysql> select username from user limit 5;
+———-+
| username |
+———-+
| root2 |
| bin |
| daemon |
| adm |
| lp |
+———-+
5 rows in set (0.01 sec)
案例：创建2张name字段相同的表
mysql> create table t1 select username,uid,shell from user limit 3;
Query OK, 3 rows affected (0.37 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table t2 select username,uid,homedir from user limit 3;
Query OK, 3 rows affected (0.44 sec)
Records: 3 Duplicates: 0 Warnings: 0
查询一下看看
mysql> select * from t1;
+———-+——+—————+
| username | uid | shell |
+———-+——+—————+
| root2 | 0 | /bin/bash |
| bin | 1 | /sbin/nologin |
| daemon | 2 | /sbin/nologin |
+———-+——+—————+
3 rows in set (0.00 sec)
mysql> select * from t2;
+———-+——+———+
| username | uid | homedir |
+———-+——+———+
| root2 | 0 | NULL |
| bin | 1 | /bin |
| daemon | 2 | /sbin |
+———-+——+———+
3 rows in set (0.00 sec)
正常情况下，这两张表的username字段是相同的
mysql> select * from t1,t2 where t1.username=t2.username;
+———-+——+—————+———-+——+———+
| username | uid | shell | username | uid | homedir |
+———-+——+—————+———-+——+———+
| root2 | 0 | /bin/bash | root2 | 0 | NULL |
| bin | 1 | /sbin/nologin | bin | 1 | /bin |
| daemon | 2 | /sbin/nologin | daemon | 2 | /sbin |
+———-+——+—————+———-+——+———+
3 rows in set (0.00 sec)
如果用视图直接显示，就会报错，即视图不能有相同的字段
mysql> create view v3 as select * from t1,t2 where t1.username=t2.username;
ERROR 1060 (42S21): Duplicate column name ‘username’
表名起别名，即表名字太常，直接起个简单的别名 例如 ********** from t1 a left join t2 b ，就是把t1 起名叫a t2 起名叫b
mysql> create view v3 as select a.username as anme,b.username as bname,a.uid as auid,b.uid as buid from t1 a left join t2 b on a.username=b.username;
Query OK, 0 rows affected (0.38 sec)
mysql> select * from v3;
+——–+——–+——+——+
| anme | bname | auid | buid |
+——–+——–+——+——+
| root2 | root2 | 0 | 0 |
| bin | bin | 1 | 1 |
| daemon | daemon | 2 | 2 |
+——–+——–+——+——+
3 rows in set (0.00 sec)
方法2，不用别名
mysql> create view v4(aname,bname) as select t1.username,t2.username from t1 left join t2 on t1.username=t2.username;
Query OK, 0 rows affected (0.12 sec)
mysql> select * from v4;
+——–+——–+
| aname | bname |
+——–+——–+
| root2 | root2 |
| bin | bin |
| daemon | daemon |
+——–+——–+
3 rows in set (0.00 sec)
重要选项
or replace
mysql> create view v5 as select * from t1;
Query OK, 0 rows affected (0.12 sec)
mysql> create view v5 as select * from t1;
ERROR 1050 (42S01): Table ‘v5’ already exists
mysql>
mysql> create or replace view v5 as select * from t1;
Query OK, 0 rows affected (0.10 sec)
定义处理视图的方式
具体化方式：先执行as后面的查询语句，并放入查询缓存，然后再去执行对视图表进行操作的语句。
没有指定，则默认为替换方式。没有执行as后面查询语句，并放入查询缓存的操作。
检查选项
local只检查当前视图的限制
cascaded同时检查基表和视图表的限制
案例：重新创建一个新表，记录弄少一点
mysql> create table user2 select * from user where uid>=10 and uid <=100;
Query OK, 16 rows affected (0.39 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select * from user2;
+—-+———-+——+——+——+—————————————————————–+———————–+—————+——+———-+———-+
| id | username | pass | uid | gid | comment | homedir | shell | sex | homedir2 | pay |
+—-+———-+——+——+——+—————————————————————–+———————–+—————+——+———-+———-+
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin | girl | /123 | 99999.99 |
| 11 | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | girl | /123 | 99999.99 |
| 12 | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | girl | /123 | 99999.99 |
| 13 | nobody | x | 99 | 99 | Nobody | / | /123456 | girl | /123 | 99999.99 |
| 15 | dbus | x | 81 | 81 | System message bus | / | /123456 | girl | /123 | 99999.99 |
| 18 | rpc | x | 32 | 32 | Rpcbind Daemon | /var/lib/rpcbind | /sbin/nologin | girl | /123 | 99999.99 |
| 23 | radvd | x | 75 | 75 | radvd user | / | /123456 | girl | /123 | 99999.99 |
| 25 | tss | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon | /dev/null | /123456 | girl | /123 | 99999.99 |
| 29 | rpcuser | x | 29 | 29 | RPC Service User | /var/lib/nfs | /sbin/nologin | girl | /123 | 99999.99 |
| 33 | gdm | x | 42 | 42 | | /var/lib/gdm | /sbin/nologin | girl | /123 | 99999.99 |
| 35 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /123456 | girl | /123 | 99999.99 |
| 36 | avahi | x | 70 | 70 | Avahi mDNS/DNS-SD Stack | /var/run/avahi-daemon | /123456 | girl | /123 | 99999.99 |
| 37 | postfix | x | 89 | 89 | | /var/spool/postfix | /123456 | girl | /123 | 99999.99 |
| 38 | ntp | x | 38 | 38 | | /etc/ntp | /sbin/nologin | girl | /123 | 99999.99 |
| 39 | tcpdump | x | 72 | 72 | | / | /123456 | girl | /123 | 99999.99 |
| 41 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false | girl | /123 | 99999.99 |
+—-+———-+——+——+——+—————————————————————–+———————–+—————+——+———-+———-+
16 rows in set (0.00 sec)
mysql> create view v6 as select * from user where uid<=100 with local check option;
Query OK, 0 rows affected (0.38 sec)
默认不指选项则为cascaded
mysql> select username,uid from v6;
+———-+——+
| username | uid |
+———-+——+
| root2 | 0 |
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
| sync | 5 |
| shutdown | 6 |
| halt | 7 |
| mail | 8 |
| operator | 11 |
| games | 12 |
| ftp | 14 |
| nobody | 99 |
| dbus | 81 |
| rpc | 32 |
| radvd | 75 |
| tss | 59 |
| rpcuser | 29 |
| gdm | 42 |
| sshd | 74 |
| avahi | 70 |
| postfix | 89 |
| ntp | 38 |
| tcpdump | 72 |
| mysql | 27 |
+———-+——+
25 rows in set (0.00 sec)
重点:修改V6表,把uid修改为101，即大于上面的选项<=100，可以发现报错了
mysql> update v6 set uid=101 where username=”ftp”;
ERROR 1369 (HY000): CHECK OPTION failed ‘test.v6’
因为验证方式是检查<=100，所以就不满足条件
改成符合条件的uid，则可以成功
mysql> update v6 set uid=77 where username=”ftp”;
Query OK, 1 row affected (0.38 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select username,uid from v6 where uid=77;
+———-+——+
| username | uid |
+———-+——+
| ftp | 77 |
+———-+——+
1 row in set (0.00 sec)
现在验证casecaded
先创建一个视图V7，并在V7的基础上再创建V8；
mysql> create view v7 as select username,uid,shell from user2 where uid>=10 and uid<=50;
Query OK, 0 rows affected (0.35 sec)
mysql> select * from v7;
+———-+——+—————+
| username | uid | shell |
+———-+——+—————+
| operator | 11 | /sbin/nologin |
| games | 12 | /sbin/nologin |
| ftp | 14 | /sbin/nologin |
| rpc | 32 | /sbin/nologin |
| rpcuser | 29 | /sbin/nologin |
| gdm | 42 | /sbin/nologin |
| ntp | 38 | /sbin/nologin |
| mysql | 27 | /bin/false |
+———-+——+—————+
8 rows in set (0.00 sec)
mysql> create view v8 as select * from v7 where uid >=20 with cascaded check option;
Query OK, 0 rows affected (0.12 sec)
mysql> select * from v8;
+———-+——+—————+
| username | uid | shell |
+———-+——+—————+
| rpc | 32 | /sbin/nologin |
| rpcuser | 29 | /sbin/nologin |
| gdm | 42 | /sbin/nologin |
| ntp | 38 | /sbin/nologin |
| mysql | 27 | /bin/false |
+———-+——+—————+
5 rows in set (0.00 sec)
插入验证
mysql> update v8 set uid=16 where username=”rpc”;
ERROR 1369 (HY000): CHECK OPTION failed ‘test.v8’
插入16，不满足V8的条件>=20
mysql> update v8 set uid=51 where username=”rpc”;
ERROR 1369 (HY000): CHECK OPTION failed ‘test.v8’
插入51，不满足v8的基表v7的条件 uid>=10 and uid<=50
重新再插入值21，这时候就都满足条件了
mysql> update v8 set uid=21 where username=”rpc”;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v8;
+———-+——+—————+
| username | uid | shell |
+———-+——+—————+
| rpc | 21 | /sbin/nologin |
| rpcuser | 29 | /sbin/nologin |
| gdm | 42 | /sbin/nologin |
| ntp | 38 | /sbin/nologin |
| mysql | 27 | /bin/false |
+———-+——+—————+
5 rows in set (0.00 sec)
存储过程
数据库中保存的一系列 sql 命令的集合
编写存储过程时,可以使用变量、条件判断、流程控制等
存储过程,就是 MySQL 中的脚本
案例：把user表里第3到第9行的uid是偶数的行，显示一下。
mysql> delimiter //
mysql> create procedure p20(in x int(2),in y int(2))
-> begin
-> while x<=y do
-> select username,uid from test.user where id=x and uid%2=0;
-> set x= x+1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.06 sec)
mysql> call p20(3,9)//
+———-+——+
| username | uid |
+———-+——+
| daemon | 2 |
+———-+——+
1 row in set (0.01 sec)
Empty set (0.01 sec)
+———-+——+
| username | uid |
+———-+——+
| lp | 4 |
+———-+——+
1 row in set (0.01 sec)
Empty set (0.01 sec)
+———-+——+
| username | uid |
+———-+——+
| shutdown | 6 |
+———-+——+
1 row in set (0.01 sec)
Empty set (0.01 sec)
+———-+——+
| username | uid |
+———-+——+
| mail | 8 |
+———-+——+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>