爱站程序员基地1 案例1：视图的基本使用
1.1 问题
• 把/etc/passwd文件的内容存储到db9库下的user表里
• 添加新字段id 存储记录的行号(在所有字段的前边)
• 创建视图v1 结构及数据user表的字段、记录一样。
• 创建视图v2 只有user表shell是/bin/bash用户信息 。
• 分别对视图表和基表执行insert update delete 操作。
• 删除视图v1 和 v2
1.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：视图的基本使用
什么是视图：是一种虚拟存在的表
内容与真实的表相似，包含一系列带有名称的列和行数据。
视图并不在数据库中以存储的数据的形式存在。
行和列的数据来自定义视图时查询所引用的基本表，并且在具体引用视图时动态生成。
更新视图的数据，就是更新基表的数据
更新基表数据，视图的数据也会跟着改变
1）把/etc/passwd文件的内容存储到db9库下的user表里
1. [root@zlz ~] # mysql -u root -p1234562. mysql> create database db9;3. Query OK, 1 row affected (10.00 sec)4. mysql> create table db9.user(username char(20),password char(1),uid \\5. int(2),gid int(2),comment char(100),homedir char(100),shell char(50));6. //创建存储数据的表结构7. Query OK, 0 rows affected (0.02 sec)8. [root@zlz ~] # cp /etc/passwd /var/lib/mysql-files/9. [root@zlz ~] # ls /var/lib/mysql-files/10. passwd11. mysql> load data infile \"/var/lib/mysql-files/passwd\" into table db9.user fields terminated by \":\" lines terminated by \"\\n\"; //导入文件内容到db9.user12. Query OK, 41 rows affected (0.02 sec)13. Records: 41 Deleted: 0 Skipped: 0 Warnings: 0
2）添加新字段id 存储记录的行号(在所有字段的前边)
1. mysql> alter table db9.user add id int(2) primary key auto_increment first;2. Query OK, 0 rows affected (0.04 sec)3. Records: 0 Duplicates: 0 Warnings: 04. mysql> use db9;5. mysql> desc user;6. +----------+-----------+------+-----+---------+----------------+7. | Field | Type | Null | Key | Default | Extra |8. +----------+-----------+------+-----+---------+----------------+9. | id | int(2) | NO | PRI | NULL | auto_increment |10. | username | char(20) | YES | | NULL | |11. | password | char(1) | YES | | NULL | |12. | uid | int(2) | YES | | NULL | |13. | gid | int(2) | YES | | NULL | |14. | comment | char(100) | YES | | NULL | |15. | homedir | char(100) | YES | | NULL | |16. | shell | char(50) | YES | | NULL | |17. +----------+-----------+------+-----+---------+----------------+18. 8 rows in set (0.00 sec)
3）创建视图v1 结构及数据user表的字段、记录一样
1. mysql> create view v1 as select * from user;2. Query OK, 0 rows affected (0.00 sec)
4）创建视图v2 只有user表shell是/bin/bash用户信息
1. mysql> create view v2 as select shell from user;2. Query OK, 0 rows affected (0.01 sec)
5）分别对视图表和基表执行insert update delete 操作
1. mysql> insert into v1(username,uid) values(\"jarry\",9); //插入记录2. Query OK, 1 row affected (0.00 sec)3.4. mysql> update v1 set uid=9 where username=\"adm\"; //更新记录5. Query OK, 1 row affected (0.01 sec)6. Rows matched: 1 Changed: 1 Warnings: 07.8. mysql> delete from v1 where uid=9; //删除记录9. Query OK, 2 rows affected (0.01 sec)
6）删除视图v1 和 v2
1. mysql> drop view v1;2. Query OK, 0 rows affected (0.00 sec)3. mysql> drop view v2;4. Query OK, 0 rows affected (0.00 sec)
注意：对视图操作即是对基本操作，反之亦然！！！
2 案例2：视图进阶操作
2.1 问题
• 练习OR REPLACE的选项使用
• 练习WITH LOCAL CHECK OPTION 选项的使用
• 练习WITH CASCADED CHECK OPTION 选项的使用
2.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：视图进阶操作
1）创建视图完全格式
1. mysql> create table user2 select username,uid,gid from user limit 3;2. //快速建表（user2表）3. Query OK, 3 rows affected (0.01 sec)4. Records: 3 Duplicates: 0 Warnings: 05.6. mysql> create table info select username,uid,homedir,shell from user limit 5;7. //快速建表（info表）8. Query OK, 5 rows affected (0.02 sec)9. Records: 5 Duplicates: 0 Warnings: 0
查询user2.username=info.username的字段
1. mysql> select * from user2 left join info on user2.username=info.username;2. +----------+------+------+----------+------+---------+---------------+3. | username | uid | gid | username | uid | homedir | shell |4. +----------+------+------+----------+------+---------+---------------+5. | root | 0 | 0 | root | 0 | /root | /bin/bash |6. | bin | 1 | 1 | bin | 1 | /bin | /sbin/nologin |7. | daemon | 2 | 2 | daemon | 2 | /sbin | /sbin/nologin |8. +----------+------+------+----------+------+---------+---------------+9. 3 rows in set (0.00 sec)
2）关联查询建的视图 默认不允许修改视图字段的值
1. mysql> create view v4 as select * from user2 left join info on user2.username=info.username; //创建失败2. ERROR 1060 (42S21): Duplicate column name \'username\'3.4. mysql> create view v4 as select a.username as ausername,b.username as busername, a.uid as auid,b.uid as buid from user2 a left join info b on a.username=b.username;5. //创建成功6. Query OK, 0 rows affected (0.00 sec)7.8. mysql> select * from v4;9. +-----------+-----------+------+------+10. | ausername | busername | auid | buid |11. +-----------+-----------+------+------+12. | root | root | 0 | 0 |13. | bin | bin | 1 | 1 |14. | daemon | daemon | 2 | 2 |15. +-----------+-----------+------+------+16. 3 rows in set (0.00 sec)17.18. mysql> desc v4;19. +-----------+----------+------+-----+---------+-------+20. | Field | Type | Null | Key | Default | Extra |21. +-----------+----------+------+-----+---------+-------+22. | ausername | char(20) | YES | | NULL | |23. | busername | char(20) | YES | | NULL | |24. | auid | int(2) | YES | | NULL | |25. | buid | int(2) | YES | | NULL | |26. +-----------+----------+------+-----+---------+-------+27. 4 rows in set (0.00 sec)
3）OR REPLACE的选项使用
创建时，若视图已存在，会替换已有的视图
语法格式：create or replace view视图名as select 查询； //达到修改已有视图的目的
1. mysql> create or replace view v4 as select a.username as ausername,b.username as busername, a.uid as auid,b.uid as buid from user2 a left join info b on a.username=b.username;2. Query OK, 0 rows affected (0.00 sec)
4）WITH LOCAL CHECK OPTION
LOCAL和CASCADED关键字决定检查的范围
LOCAL 仅检查当前视图的限制
CASCADED 同时要满足基表的限制（默认值）
1. mysql> create table user1 select username,uid,shell from user where uid>=5 and uid <=40;2. Query OK, 11 rows affected (0.01 sec)3. Records: 11 Duplicates: 0 Warnings: 04.5. mysql> create view v1 as select username,uid from user1 where uid<=20;6. Query OK, 0 rows affected (0.01 sec)7.8. mysql> update v1 set uid=21 where username=\"sync\";9. //操作超过视图表的条件限制（uid<=20）之后，在视图表里面查看不到，在基表里可以查看到10. Query OK, 1 row affected (0.01 sec)11. Rows matched: 1 Changed: 1 Warnings: 012.13. mysql> update user1 set uid=41 where username=\"ftp\";14. //基表在超过条件限制（uid>=5 and uid <=40),在基表里依然可以查看到15. Query OK, 1 row affected (0.00 sec)16. Rows matched: 1 Changed: 1 Warnings: 017.18. mysql> create table a select * from user where uid < 10;19. //快速创建一个新表a20. Query OK, 7 rows affected (0.01 sec)21. Records: 7 Duplicates: 0 Warnings: 022.23. mysql> create view v3 as select * from a where uid < 10 with check option;24. //不写默认为CASCADED检查自己和a要满足的要求即可25. Query OK, 0 rows affected (0.00 sec)26.27. mysql> update v3 set uid=9 where username=\"adm\"; //更改成功28. Query OK, 0 rows affected (0.01 sec)29. Rows matched: 0 Changed: 0 Warnings: 030.31.32. mysql> create view v2 as select * from v1 where uid >= 5 with local check option;33. //满足自身v2的要求34. Query OK, 0 rows affected (0.00 sec)35. mysql> update v2 set uid=9 where username=\"sync\";36. Query OK, 0 rows affected (0.00 sec)37. Rows matched: 0 Changed: 0 Warnings: 0
5）WITH CASCADED CHECK OPTION
1. mysql> create view v5 as select * from v1 where uid >= 5 with cascaded check option;2. Query OK, 0 rows affected (0.00 sec)
3 案例3：创建存储过程
3.1 问题
• 存储过程名称为p1
• 功能显示user表中 shell是/bin/bash的用户个数
• 调用存储过程p1
3.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：存储过程基本使用
1）创建存储过程
1. mysql> delimiter // //定义定界符2. mysql> create procedure say() //say()随便写括号一定要有3. -> begin4. -> select * from user where id<=10;5. -> end6. -> //7. Query OK, 0 rows affected (0.01 sec)8. mysql> delimiter ; //把命令的定界符改回来，分号前有空格9. mysql> call say(); //调用存储过程名,在括号里面不写参数时，可以不加括号
2）查看存储过程
方法一：
1. mysql> show procedure status\\G;
方法二：
1. mysql> select db,name,type from mysql.proc where name= \"say\";
3）删除存储过程
1. mysql> drop procedure say;2. Query OK, 0 rows affected (0.00 sec)
4）Ÿ创建存储过程名称为p1
Ÿ 功能显示user表中 shell是/bin/bash的用户
Ÿ 调用存储过程p1
1. mysql> delimiter //2. mysql> create procedure p1()3. -> begin4. -> select count(username) from user where shell=\"/bin/bash\";5. -> end6. -> //7. mysql> delimiter ;8. mysql> call p1();9. +-----------+10. | shell |11. +-----------+12. | /bin/bash |13. | /bin/bash |14. +-----------+15. 2 rows in set (0.01 sec)16.17. Query OK, 0 rows affected (0.01 sec)
4 案例4：存储过程参数的使用
4.1 问题
• 创建名为p2的存储过程
• 可以接收用户输入shell的名字
• 统计user表中用户输入shell名字的个数
4.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：存储过程参数的使用
1）参数类型
MySQL存储过程，共有三种参数类型IN,OUT,INOUT
Create procedure 名称(
类型 参数名 数据类型,
类型 参数名 数据类型
）
in 输入参数 传递值给存储过程，必须在调用存储过程时指定，在存储过程中修改该参数的值不能；默认类型是in
out 输出参数 该值可在存储过程内部被改变，并可返回
inout 输入/输出参数 调用时指定，并且可被改变和返回
1. mysql> delimiter //2. mysql> create procedure say2(in username char(10))3. -> begin4. -> select username;5. -> select * from user where username=username;6. -> end7. -> //8. Query OK, 0 rows affected (0.00 sec)9.10. mysql> delimiter ;11. mysql> call say2(\"tom\");
2）创建名为p2的存储过程，可以接收用户输入shell的名字，统计user表中用户输入shell名字的个数
1. mysql> delimiter //2. mysql> create procedure p2(out number int)3. -> begin4. -> select count(username) into @number from user where shell!=\"/bin/bash\";5. -> select @number;6. -> end7. -> //8. Query OK, 0 rows affected (0.01 sec)9. mysql> delimiter ;10. mysql> call p2(@number);11. +---------+12. | @number |13. +---------+14. | 38 |15. +---------+16. 1 row in set (0.00 sec)17.18. Query OK, 0 rows affected (0.00 sec)
5 案例5：使用循环结构
5.1 问题
• 定义名称为p3的存储过程
• 用户可以自定义显示user表记录的行数
• 若调用时用户没有输入行数，默认显示第1条记录
5.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：算数运算
1）算 数运算符号，如图-1所示：
图-1
1. mysql> set @z=1+2;select @z;2. Query OK, 0 rows affected (0.00 sec)3.4. +------+5. | @z |6. +------+7. | 3 |8. +------+9. 1 row in set (0.00 sec)10. mysql> set @x=1; set @y=2;set @z=@x*@y; select @z;11. Query OK, 0 rows affected (0.00 sec)12.13. Query OK, 0 rows affected (0.00 sec)14.15. Query OK, 0 rows affected (0.00 sec)16.17. +------+18. | @z |19. +------+20. | 2 |21. +------+22. 1 row in set (0.00 sec)23.24. mysql> set @x=1; set @y=2;set @z=@x-@y; select @z;25. Query OK, 0 rows affected (0.00 sec)26.27. Query OK, 0 rows affected (0.00 sec)28.29. Query OK, 0 rows affected (0.00 sec)30.31. +------+32. | @z |33. +------+34. | -1 |35. +------+36. 1 row in set (0.00 sec)37. mysql> set @x=1; set @y=2;set @z=@x/@y; select @z;38. Query OK, 0 rows affected (0.00 sec)39.40. Query OK, 0 rows affected (0.00 sec)41.42. Query OK, 0 rows affected (0.00 sec)43.44. +-------------+45. | @z |46. +-------------+47. | 0.500000000 |48. +-------------+49. 1 row in set (0.00 sec)
declare调用变量不需要@其他都需要
调用变量时，有@符号的变量 如@x：调用的是用户自定义变量
没有@符号的变量 如x：调用的是存储过程的参数变量
1. mysql> delimiter //2. mysql> create procedure say5(in bash char(20), in nologin char(25), out x int , out y int)3. -> begin4. -> declare z int ;5. -> set z=0;6. -> select count(username) into @x from user where shell=bash;7. -> select count(username) into @y from user where shell=nologin;8. -> set z=@x+@y;9. -> select z;10. -> end11. -> //12. Query OK, 0 rows affected (0.00 sec)13.14. mysql> delimiter ;15. mysql> call say5(\"/bin/bash\",\"/sbin/nologin\",@x,@y);16. +------+17. | z |18. +------+19. | 36 |20. +------+21. 1 row in set (0.00 sec)22.23. Query OK, 0 rows affected (0.00 sec)
2）条件判断，数值的比较 如图-2所示：
图-2
逻辑比较、范围、空、非空、模糊、正则，如图-3所示：
图-3
顺序结构（if判断）当“条件成立”时执行命令序列,否则，不执行任何操作
1. mysql> delimiter //2. mysql> create procedure say6(in x int(1) )3. -> begin4. -> if x <= 10 then5. -> select * from user where id <=x;6. -> end if;7. -> end8. -> //9. Query OK, 0 rows affected (0.01 sec)10.11. mysql> delimiter ;12. mysql> call say6(1); //条件判断成立，等于1是否成立13. +----+----------+----------+------+------+---------+---------+-----------+14. | id | username | password | uid | gid | comment | homedir | shell |15. +----+----------+----------+------+------+---------+---------+-----------+16. | 1 | root | x | 0 | 0 | root | /root | /bin/bash |17. +----+----------+----------+------+------+---------+---------+-----------+18. 1 row in set (0.00 sec)19.20. Query OK, 0 rows affected (0.00 sec)21.22. mysql> call say6(2);23. +----+----------+----------+------+------+---------+---------+---------------+24. | id | username | password | uid | gid | comment | homedir | shell |25. +----+----------+----------+------+------+---------+---------+---------------+26. | 1 | root | x | 0 | 0 | root | /root | /bin/bash |27. | 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |28. +----+----------+----------+------+------+---------+---------+---------------+29. 2 rows in set (0.00 sec)30.31. Query OK, 0 rows affected (0.00 sec)
3）定义名称为p3的存储过程，用户可以自定义显示user表记录的行数，若调用时用户没有输入行数，默认显示第1条记录
1. mysql> delimiter //2. mysql> create procedure p3(in linenum char(10) )3. -> begin4. -> if linenum is null then5. -> set @linenum=1;6. -> select * from user where id=@linenum;7. -> else8. -> select linenum;9. -> select * from user where id=linenum;10. -> end if;11. -> end12. -> //13. Query OK, 0 rows affected (0.00 sec)14.15. mysql> delimiter ;16. mysql> call p3(null); //不输入查看的行数17. +----+----------+----------+------+------+---------+---------+-----------+18. | id | username | password | uid | gid | comment | homedir | shell |19. +----+----------+----------+------+------+---------+---------+-----------+20. | 1 | root | x | 0 | 0 | root | /root | /bin/bash |21. +----+----------+----------+------+------+---------+---------+-----------+22. 1 row in set (0.00 sec)23.24. Query OK, 0 rows affected (0.00 sec)25.26. mysql> call p3(3); //输入查看的行数27. +---------+28. | linenum |29. +---------+30. | 3 |31. +---------+32. 1 row in set (0.00 sec)33.34. +----+----------+----------+------+------+---------+---------+---------------+35. | id | username | password | uid | gid | comment | homedir | shell |36. +----+----------+----------+------+------+---------+---------+---------------+37. | 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |38. +----+----------+----------+------+------+---------+---------+---------------+39. 1 row in set (0.00 sec)40.41. Query OK, 0 rows affected (0.00 sec)
