# 16.4 视图、事务、索引、函数 ## 1 视图【view】 1、 视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。 2、 视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。 ### 1.1 创建视图 语法 ``` create [or replace] view 视图的名称 as select语句; ``` 例题： ``` mysql> create view vw_stu -> as -> select stuname,stusex,writtenexam,labexam from stuinfo inner join stumarks using(stuno); Query OK, 0 rows affected (0.00 sec) ``` ``` 多学一招：因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件 ``` ### 1.2 使用视图 视图是一张虚拟表，视图的用法和表的用法一样的 ``` mysql> select * from vw_stu; +----------+--------+-------------+---------+ | stuname | stusex | writtenexam | labexam | +----------+--------+-------------+---------+ | 李斯文 | 女 | 80 | 58 | | 李文才 | 男 | 50 | 90 | | 欧阳俊雄 | 男 | 65 | 50 | | 张秋丽 | 男 | 77 | 82 | | 争青小子 | 男 | 56 | 48 | +----------+--------+-------------+---------+ mysql> update vw_stu set writtenexam=88 where stuname='李斯文'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` ### 1.3 查看视图的结构 语法： ``` desc 视图名; ``` 例题 ``` mysql> desc vw_stu; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | stuname | varchar(10) | NO | | NULL | | | stusex | char(2) | NO | | NULL | | | writtenexam | int(11) | YES | | NULL | | | labexam | int(11) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ ``` ### 1.4 查看创建视图的语法 语法： ``` show create view 视图名 ``` 例题 ![1537067071676](https://2471267620-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfNyxSFSDaHeCRaQx74%2F-M-hqclgWtKU5XFPCmye%2F-M-hqePZmo_rmt5Q3Mi2%2F1537067071676.png?generation=1581317270553484\&alt=media) ### 1.5 显示所有视图 ``` #方法一： mysql> show tables; +------------------+ | Tables_in_itcast | +------------------+ | stu | | stuinfo | | stumarks | | t1 | | t2 | | vw_stu | # 方法二 mysql> select table_name from information_schema.views; +------------+ | table_name | +------------+ | vw_stu | +------------+ 1 row in set (0.05 sec) +------------------+ #方法三 mysql> show table status where comment='view' \G *************************** 1. row *************************** Name: vw_stu Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULL Max_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW 1 row in set (0.00 sec) ``` ### 1.6 更改视图 语法： ``` alter view 视图名 as select 语句 ``` 例题： ``` mysql> alter view vw_stu -> as -> select * from stuinfo; Query OK, 0 rows affected (0.00 sec) ``` ### 1.7 删除视图 语法： ``` drop view [if exists] 视图1,视图2,… ``` 例题 ``` mysql> drop view vw_stu; Query OK, 0 rows affected (0.00 sec) ``` ### 1.8 视图的作用 1. 筛选数据，防止未经许可访问敏感数据 2. 隐藏表结构 3. 降低SQL语句的复杂度 ### 1.9 视图的算法 场景：找出语文成绩最高的男生和女生 ``` mysql> select * from (select * from stu order by ch desc) as t group by stusex; +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | +--------+----------+--------+--------+---------+------------+------+------+ ``` 我们可以将子查询封装到视图中 ``` mysql> create view vw_stu -> as -> select * from stu order by ch desc; Query OK, 0 rows affected (0.00 sec) ``` 可以将上面的子查询更改成视图，但是，结果和上面不一样 ``` mysql> select * from vw_stu group by stusex; +--------+---------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+---------+--------+--------+---------+------------+------+------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | 80 | NULL | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 | +--------+---------+--------+--------+---------+------------+------+------+ ``` 原因：这是因为视图的算法造成的 ``` 1. merge：合并算法，将视图的语句和外层的语句合并后在执行。 2. temptable：临时表算法，将视图生成一个临时表，再执行外层语句 3. undefined：未定义，MySQL到底用merge还是用temptable由MySQL决定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。 ``` 解决：在创建视图的时候指定视图的算法 ``` create algorithm=temptable view 视图名 as select 语句 ``` 指定算法创建视图 ``` mysql> create algorithm=temptable view vw_stu -> as -> select * from stu order by ch desc; Query OK, 0 rows affected (0.00 sec) mysql> select * from vw_stu group by stusex; # 结果是一致的 +--------+----------+--------+--------+---------+------------+------+------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | ch | math | +--------+----------+--------+--------+---------+------------+------+------+ | s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | 86 | 92 | +--------+----------+--------+--------+---------+------------+------+------+ ``` ## 2 事务【transaction】 1. 事务是一个不可分割的执行单元 2. 事务作为一个整体要么一起执行，要么一起回滚 插入测试数据 ``` mysql> create table bank( -> cardid char(4) primary key, -> money int -> ); Query OK, 0 rows affected (0.00 sec) mysql> insert into bank values ('1001',1000),('1002',100); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 ``` ### 2.1 事务操作 ``` 开启事务：start transaction或begin [work] 提交事务：commit 回滚事务：rollback ``` 例题： ``` mysql> delimiter // # 更改定界符 mysql> start transaction; # 开启事务 -> update bank set money=money-100 where cardid='1001'; -> update bank set money=money+100 where cardid='1002' // Query OK, 0 rows affected (0.00 sec) mysql> commit // # 提交事务 mysql> rollback // # 回滚事务 ``` ``` 思考：事务什么时候产生？什么时候结束？ 答：开启的时候产生，提交事务或回滚事务都结束 脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。 ``` ### 2.2 设置事务的回滚点 语法： ``` 设置回滚点： savepoint 回滚点名 回滚到回滚点： rollback to 回滚点 ``` 例题： ``` mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into bank values ('1003',1000); Query OK, 1 row affected (0.00 sec) mysql> savepoint aa; # 设置回滚点 aa Query OK, 0 rows affected (0.00 sec) mysql> insert into bank values ('1004',500); Query OK, 1 row affected (0.00 sec) mysql> savepoint bb; # 设置回滚点bb Query OK, 0 rows affected (0.00 sec) mysql> rollback to aa; # 回滚到aa点 Query OK, 0 rows affected (0.00 sec) mysql> commit; # 提交事务 mysql> select * from bank ; +--------+-------+ | cardid | money | +--------+-------+ | 1001 | 800 | | 1002 | 200 | | 1003 | 1000 | +--------+-------+ ``` ### 2.3 事务的特性（ACID） 1. 原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。 2. 一致性（Consistency）：事务完成时，数据必须处于一致的状态。 3. 隔离性（Isolation）：每个事务都是相互隔离的 4. 永久性（Durability）：事务完成后，对数据的修改是永久性的。 ## 3 索引【index】 索引的优点：查询速度快 索引的缺点： 1. 增、删、改（数据操作语句）效率低了 2. 索引占用空间 ### 3.1 索引的类型 1. 普通索引 2. 唯一索引（唯一键） 3. 主键索引：只要主键就自动创建主键索引，不需要手动创建。 4. 全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。 ### 3.2 创建普通索引【create index】 语法： ``` create index [索引名] on 表名 （字段名） alter table 表名 add index [索引的名称] （列名） ``` 例题： ``` # 创建索引方法一 mysql> create index ix_stuname on stuinfo(stuname); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 # 创建索引方法二 mysql> alter table stuinfo add index ix_address (stuaddress); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 # 创建表的时候就添加索引 mysql> create table emp( -> id int, -> name varchar(10), -> index ix_name (name) # 创建索引 -> ); Query OK, 0 rows affected (0.00 sec) ``` ### 3.3 创建唯一索引 ``` 语法一：create unique index 索引名 on 表名 （字段名） 语法二：alter table 表名 add unqiue [index] [索引的名称] （列名） 语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。 ``` 例题 ``` # 方法一： mysql> create unique index UQ_stuname on stu(stuname); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 # 方法二： mysql> alter table stu add unique UQ_address (stuaddress); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 # 方法三 mysql> create table stu2( -> id int, -> name varchar(20), -> unique UQ_name(name) -> ); Query OK, 0 rows affected (0.01 sec) ``` ### 3.4 删除索引 语法 ``` drop index 索引名 on 表名 ``` 例题 ``` mysql> drop index ix_stuname on stuinfo; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ### 3.5 创建索引的指导原则 1. 该列用于频繁搜索 2. 改列用于排序 3. 公共字段要创建索引 4. 如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。 5. 如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。 ## 4 函数 ### 4.1 数字类 ``` mysql> select rand(); # 生成随机数 +---------------------+ | rand() | +---------------------+ | 0.18474003969201822 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from stuinfo order by rand(); # 随机排序 mysql> select * from stuinfo order by rand() limit 2; # 随机抽两个学生 +--------+----------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+----------+--------+--------+---------+------------+ | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | +--------+----------+--------+--------+---------+------------+ 2 rows in set (0.00 sec) mysql> select round(3.5); #四舍五入 +------------+ | round(3.5) | +------------+ | 4 | +------------+ 1 row in set (0.00 sec) mysql> select ceil(3.1); # 向上取整 +-----------+ | ceil(3.1) | +-----------+ | 4 | +-----------+ 1 row in set (0.00 sec) mysql> select floor(3.9); # 向下取整 +------------+ | floor(3.9) | +------------+ | 3 | +------------+ 1 row in set (0.00 sec) mysql> select truncate(3.1415926,3); # 截取数字 +-----------------------+ | truncate(3.1415926,3) | +-----------------------+ | 3.141 | +-----------------------+ 1 row in set (0.00 sec) ``` ### 4.2 字符串类 ``` mysql> select ucase('i am a boy!'); # 转成大写 +----------------------+ | ucase('i am a boy!') | +----------------------+ | I AM A BOY! | +----------------------+ 1 row in set (0.00 sec) mysql> select lcase('I Am A Boy!'); #转成小写 +----------------------+ | lcase('I Am A Boy!') | +----------------------+ | i am a boy! | +----------------------+ 1 row in set (0.00 sec) mysql> select left('abcde',3); # 从左边开始截取，截取3个 +-----------------+ | left('abcde',3) | +-----------------+ | abc | +-----------------+ 1 row in set (0.00 sec) mysql> select right('abcde',3); # 从右边开始截取，截取3个 +------------------+ | right('abcde',3) | +------------------+ | cde | +------------------+ 1 row in set (0.00 sec) mysql> select substring('abcde',2,3); #从第2个位置开始截取，截取3个【位置从1开始】 +------------------------+ | substring('abcde',2,3) | +------------------------+ | bcd | +------------------------+ 1 row in set (0.00 sec) mysql> select concat('中国','上海'); # 字符串相连 +-----------------------+ | concat('中国','上海') | +-----------------------+ | 中国上海 | +-----------------------+ 1 row in set (0.00 sec) mysql> select concat(stuname,'-',stusex) from stuinfo; # 将表中的姓名和性别连接起来 +----------------------------+ | concat(stuname,'-',stusex) | +----------------------------+ | 张秋丽-男 | | 李文才-男 | | 李斯文-女 | | 欧阳俊雄-男 | | 诸葛丽丽-女 | | 争青小子-男 | | 梅超风-女 | +----------------------------+ 7 rows in set (0.00 sec) # coalesce(字段1，字段2) 如果字段1不为空就显示字段1，否则，显示字段2 mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks; # 将考试成绩为空的显示为缺考 +----------+------------------------------+--------------------------+ | stuname | coalesce(writtenexam,'缺考') | coalesce(labexam,'缺考') | +----------+------------------------------+--------------------------+ | 张秋丽 | 77 | 82 | | 李文才 | 50 | 90 | | 李斯文 | 88 | 58 | | 欧阳俊雄 | 65 | 50 | | 诸葛丽丽 | 缺考 | 缺考 | | 争青小子 | 56 | 48 | | 梅超风 | 缺考 | 缺考 | +----------+------------------------------+--------------------------+ mysql> select length('锄禾日当午'); # 字节长度 +----------------------+ | length('锄禾日当午') | +----------------------+ | 10 | +----------------------+ 1 row in set (0.00 sec) mysql> select char_length('锄禾日当午'); # 字符个数 +---------------------------+ | char_length('锄禾日当午') | +---------------------------+ | 5 | +---------------------------+ 1 row in set (0.00 sec) ``` ### 4.3 时间类 ``` mysql> select unix_timestamp(); #获取时间戳 +------------------+ | unix_timestamp() | +------------------+ | 1537084508 | +------------------+ 1 row in set (0.00 sec) mysql> select from_unixtime(unix_timestamp()); # 将时间戳转成年-月-日 小时:分钟:秒的格式 +---------------------------------+ | from_unixtime(unix_timestamp()) | +---------------------------------+ | 2018-09-16 15:55:56 | +---------------------------------+ 1 row in set (0.00 sec) mysql> select now(); # 获取当前日期时间 +---------------------+ | now() | +---------------------+ | 2018-09-16 15:57:04 | +---------------------+ 1 row in set (0.00 sec) mysql> select year(now()) 年,month(now()) 月, day(now()) 日,hour(now()) 小,minute(now()) 分钟,second(now()) 秒; +------+------+------+------+------+------+ | 年 | 月 | 日 | 小时 | 分钟 | 秒 | +------+------+------+------+------+------+ | 2018 | 9 | 16 | 15 | 59 | 14 | +------+------+------+------+------+------+ 1 row in set (0.00 sec) mysql> select dayname(now()) 星期,monthname(now()),dayofyear(now()) 本年的第几天; +--------+------------------+--------------+ | 星期 | monthname(now()) | 本年的第几天 | +--------+------------------+--------------+ | Sunday | September | 259 | +--------+------------------+--------------+ 1 row in set (0.00 sec) mysql> select datediff(now(),'2008-8-8'); # 日期相减 +----------------------------+ | datediff(now(),'2008-8-8') | +----------------------------+ | 3691 | +----------------------------+ 1 row in set (0.00 sec) mysql> select convert(now(),date),convert(now(),time); # 将now()转成日期和时间 +---------------------+---------------------+ | convert(now(),date) | convert(now(),time) | +---------------------+---------------------+ | 2018-09-16 | 16:07:24 | +---------------------+---------------------+ mysql> select cast(now() as date),cast(now() as time); # 将now()转成日期和时间 +---------------------+---------------------+ | cast(now() as date) | cast(now() as time) | +---------------------+---------------------+ | 2018-09-16 | 16:08:03 | +---------------------+---------------------+ 1 row in set (0.00 sec) ``` ### 4.4 加密函数 ``` +----------------------------------+------------------------------------------+ | md5('root') | sha('root') | +----------------------------------+------------------------------------------+ | 63a9f0ea7bb98050796b649e85481845 | dc76e9f0c0006e8f919e0c515c66dbba3982f785 | +----------------------------------+------------------------------------------+ 1 row in set (0.00 sec) ``` ### 4.5 判断函数 语法 ``` if(表达式,值1,值2) ``` 例题： ``` mysql> select if(10%2=0,'偶数','奇数'); +--------------------------+ | if(10%2=0,'偶数','奇数') | +--------------------------+ | 偶数 | +--------------------------+ 1 row in set (0.00 sec) # 语文和数学都超过60分才通过 mysql> select stuname,ch,math,if(ch>=60 && math>=60,'通过','不通过') '是否通过' from stu; +----------+------+------+----------+ | stuname | ch | math | 是否通过 | +----------+------+------+----------+ | 张秋丽 | 80 | NULL | 不通过 | | 李文才 | 77 | 76 | 通过 | | 李斯文 | 55 | 82 | 不通过 | | 欧阳俊雄 | NULL | 74 | 不通过 | | 诸葛丽丽 | 72 | 56 | 不通过 | | 争青小子 | 86 | 92 | 通过 | | 梅超风 | 74 | 67 | 通过 | | Tom | 65 | 67 | 通过 | | Tabm | 88 | 77 | 通过 | +----------+------+------+----------+ 9 rows in set (0.00 sec) ``` ## 5 预处理 预编译一次，可以多次执行。用来解决一条SQL语句频繁执行的问题。 ``` 预处理语句：prepare 预处理名字 from ‘sql语句’ 执行预处理：execute 预处理名字 [using 变量] ``` 例题一： ``` mysql> prepare stmt from 'select * from stuinfo'; # 创建预处理 Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> execute stmt; # 执行预处理 +--------+----------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+----------+--------+--------+---------+------------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | +--------+----------+--------+--------+---------+------------+ 7 rows in set (0.00 sec) ``` 例题二：传递参数 ``` mysql> delimiter // mysql> prepare stmt from 'select * from stuinfo where stuno=?' // -- ?是位置占位符 Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> set @id='s25301'; -- 变量以@开头，通过set给变量赋值 -> execute stmt using @id // -- 执行预处理，传递参数 Query OK, 0 rows affected (0.00 sec) +--------+---------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+---------+--------+--------+---------+------------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | +--------+---------+--------+--------+---------+------------+ 1 row in set (0.00 sec) ``` ``` 脚下留心： 1、?是位置占位符 2、变量以@开头 3、通过set给变量赋值 ``` 例题三：传递多个参数 ``` mysql> prepare stmt from 'select * from stuinfo where stusex=? and stuaddress=?' // Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> set @sex='男'; -> set @addr='北京'; -> execute stmt using @sex,@addr // Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +--------+---------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+---------+--------+--------+---------+------------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | +--------+---------+--------+--------+---------+------------+ 1 row in set (0.00 sec) ``` ## 6 存储过程【procedure】 ### 6.1 存储过程的优点 1. 存储过程可以减少网络流量 2. 允许模块化设计 3. 支持事务 ### 6.2 创建存储过程 语法： ``` create procedure 存储过程名(参数) begin //sql语句 end; 脚下留心：由于过程中有很多SQL语句，每个语句的结束都要用（；）结束。默认情况下，分号既表示语句结束，又表示向服务器发送SQL语句。我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter来更改结束符。 ``` 例题 ``` mysql> delimiter // mysql> create procedure proc() -- 创建存储过程 -> begin -> select * from stuinfo; -> end // Query OK, 0 rows affected (0.00 sec) ``` ### 6.3 调用存储过程 语法： ``` call 存储过程名() ``` 例题： ``` mysql> call proc() // -- 调用存储过程 +--------+----------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+----------+--------+--------+---------+------------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | | s25302 | 李文才 | 男 | 31 | 3 | 上海 | | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | | s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | | s25318 | 争青小子 | 男 | 26 | 6 | 天津 | | s25319 | 梅超风 | 女 | 23 | 5 | 河北 | +--------+----------+--------+--------+---------+------------+ 7 rows in set (0.00 sec) ``` ### 6.4 删除存储过程 语法 ``` drop procedure [if exists] 存储过程名 ``` 例题： ``` mysql> drop procedure proc // -- 删除存储过程 Query OK, 0 rows affected (0.00 sec) ``` ### 6.5 查看存储过程的信息 ``` show create procedure 存储过程名\G ``` 例题 ``` mysql> show create procedure proc \G *************************** 1. row *************************** Procedure: proc sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`() begin select * from stuinfo; end character_set_client: gbk collation_connection: gbk_chinese_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) ``` ### 6.6 显示所有的存储过程 ``` mysql> show procedure status \G ``` ### 6.7 存储过程的参数 存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout） 存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。 例题一：传递学号，获取对应的信息 ``` mysql> create procedure proc(in param varchar(10)) -- 输入参数 -> select * from stuinfo where stuno=param // Query OK, 0 rows affected (0.00 sec) mysql> call proc('s25301') // +--------+---------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+---------+--------+--------+---------+------------+ | s25301 | 张秋丽 | 男 | 18 | 1 | 北京 | +--------+---------+--------+--------+---------+------------+ 1 row in set (0.00 sec) ``` 例题二：查找同桌 ``` mysql> create procedure proc(name varchar(10)) -> begin -> declare seat tinyint; -- 声明局部变量 -> select stuseat into seat from stuinfo where stuname=name; -- 将座位号保存到变量中 -> select * from stuinfo where stuseat=seat+1 or stuseat=seat-1; -- 查找同桌 -> end // Query OK, 0 rows affected (0.00 sec) mysql> call proc('李文才') // +--------+----------+--------+--------+---------+------------+ | stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress | +--------+----------+--------+--------+---------+------------+ | s25303 | 李斯文 | 女 | 22 | 2 | 北京 | | s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 | +--------+----------+--------+--------+---------+------------+ 2 rows in set (0.00 sec) ``` 强调 ``` 1、通过declare关键字声明局部变量；全局变量@开头就可以了 2、给变量赋值有两种方法 方法一：set 变量名=值 方法二：select 字段 into 变量 from 表 where 条件 3、声明的变量不能与列名同名 ``` 例题三：输出参数 ``` mysql> create procedure proc(num int, out result int) //out 表示输出参数 -> begin -> set result=num*num; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call proc(10,@result) // Query OK, 0 rows affected (0.00 sec) mysql> select @result // +---------+ | @result | +---------+ | 100 | +---------+ 1 row in set (0.00 sec) ``` 例题四：输入输出参数 ``` mysql> create procedure proc(inout num int) # inout 表示是输入输出参数 -> begin -> set num=num*num; -> end // Query OK, 0 rows affected (0.00 sec) mysql> set @num=10; -> call proc(@num); -> select @num // Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +------+ | @num | +------+ | 100 | +------+ 1 row in set (0.00 sec) ``` ## 7 GO连接MySQL 1. 因为Go语言没有提供任何官方数据库驱动，所以需要安装第三方函数库。 2. 由于在github上安装，所以需要安装git软件，安装过程一直点击下一步即可。安装完成后需要配置环境变量 ### 7.1 安装git git软件 ![1537011074242](https://2471267620-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfNyxSFSDaHeCRaQx74%2F-M-hqclgWtKU5XFPCmye%2F-M-hqePm9OxPZdpaiUNv%2F1537011074242.png?generation=1581317273577998\&alt=media) 安装完毕后，配置git的环境变量，这样可以使用`get`的指令 ![1537094210154](https://2471267620-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfNyxSFSDaHeCRaQx74%2F-M-hqclgWtKU5XFPCmye%2F-M-hqePo6uA8rhyFK74B%2F1537094210154.png?generation=1581317272585504\&alt=media) ### 7.2 安装数据库驱动 设置GOPATH的环境变量，这样驱动才会下载到项目根目录中。 ![1537094342573](https://2471267620-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfNyxSFSDaHeCRaQx74%2F-M-hqclgWtKU5XFPCmye%2F-M-hqePrxvp5TaU0rQc1%2F1537094342573.png?generation=1581317271595232\&alt=media)*\*\** 在cmd命令窗口中输入如下命令安装驱动 ```go go get github.com/go-sql-driver/mysql 这一命令会从代码中获取驱动的具体代码，并将这些代码放置到包库中，当需要用到驱动的时候，编译器会把驱动代码与用户编写的代码一同编译。 ``` 安装完毕后，会在GOPATH下看到下载的驱动 ![1537011722807](https://2471267620-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LfNyxSFSDaHeCRaQx74%2F-M-hqclgWtKU5XFPCmye%2F-M-hqePtPhA18p_UE88o%2F1537011722807.png?generation=1581317274593667\&alt=media) ### 7.3 连接数据库 1、在src目录下创建demo.go文件，导入数据库驱动 ```go import ( "database/sql" _ "github.com/go-sql-driver/mysql" ) 注意：程序在操作数据库的时候只需要用到database/sql，而不需要直接使用数据库驱动，所以程序在导入数据库驱动的时候将这个包的名字设置成下划线。 ``` 2、通过sql.open()连接数据库 ``` sql.open(驱动名,数据源dsn)(*DB,err) 数据源语法："用户名:密码@[连接方式](主机名:端口号)/数据库名" 注意：open()在执行时不会真正的与数据库进行连接，只是设置连接数据库需要的参数 ping()方法才是连接数据库 ``` 3、执行SQL语句 ​ 创建测试表 ``` mysql> create table stu( -> id int primary key, -> name varchar(10) -> ); Query OK, 0 rows affected (0.02 sec) ``` 操作数据 ``` package main import ( "database/sql" _ "github.com/go-sql-driver/mysql" "fmt" ) func main(){ //"用户名:密码@[连接方式](主机名:端口号)/数据库名" db,_:=sql.Open("mysql","root:root@(127.0.0.1:3306)/itcast") // 设置连接数据库的参数 defer db.Close() //关闭数据库 err:=db.Ping() //连接数据库 if err!=nil{ fmt.Println("数据库连接失败") return } //操作一：执行数据操作语句 /* sql:="insert into stu values (2,'berry')" result,_:=db.Exec(sql) //执行SQL语句 n,_:=result.RowsAffected(); //获取受影响的记录数 fmt.Println("受影响的记录数是",n) */ //操作二：执行预处理 /* stu:=[2][2] string{{"3","ketty"},{"4","rose"}} stmt,_:=db.Prepare("insert into stu values (?,?)") //获取预处理语句对象 for _,s:=range stu{ stmt.Exec(s[0],s[1]) //调用预处理语句 } */ //操作三：单行查询 /* var id,name string rows:=db.QueryRow("select * from stu where id=4") //获取一行数据 rows.Scan(&id,&name) //将rows中的数据存到id,name中 fmt.Println(id,"--",name) */ //操作四：多行查询 rows,_:=db.Query("select * from stu") //获取所有数据 var id,name string for rows.Next(){ //循环显示所有的数据 rows.Scan(&id,&name) fmt.Println(id,"--",name) } } ``` ## 8 例题 1、 显示地区及每个地区参加考试的人数，并按人数降序排列。 ``` select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc; ``` 2、 显示有学生参加考试的地区 方法一：过滤 ``` select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c>0; ``` 方法二：链接查询 ``` select distinct stuaddress c from stuinfo right join stumarks using(stuno) ; ``` 3、 显示男生和女生的人数 方法一： ``` select stusex,count() from stuinfo where stusex='男' union select stusex,count() from stuinfo where stusex='女'; ``` 方法二： ``` select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo; ``` 4、 显示每个地区的男生和女生和总人数 ``` select stuaddress,count(stusex) 总人数, sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress ```