MySQL传智播客--Lession31-Lession43笔记

=========================lession 31=outfile=====================
通过：select * into outfile '文件地址' from xxx 保存查询结果到文件


mysql> select * from teacher_class;
+----+-----------+--------+---------+------+------+------------+------------+
| id | t_name    | gender | c_name  | room | days | begin_date | end_date   |
+----+-----------+--------+---------+------+------+------------+------------+
|  1 | 韩信    | male   | php0115 |  207 |   21 | 2013-01-15 | 2013-02-20 | 
|  2 | 韩信    | male   | php0228 |  106 |   18 | 2013-02-28 | 2013-03-30 | 
|  3 | 韩信    | male   | php0331 |  102 |   22 | 2013-03-31 | 2013-05-05 | 
|  5 | 李白    | male   | php0115 |  207 |   20 | 2013-02-22 | 2013-03-25 | 
|  6 | 李白    | male   | php0228 |  204 |   22 | 2013-03-31 | 2013-04-29 | 
|  7 | 韩非子 | male   | php0115 |  207 |   15 | 2013-03-27 | 2013-04-18 | 
|  8 | 韩信    | male   | php0331 |  106 |   15 | 2013-05-28 | 2013-06-15 | 
|  9 | 韩非子 | male   | php0331 |  106 |   15 | 2013-05-28 | 2013-06-15 | 
| 10 | 李白    | male   | php0115 |  102 |    5 | 2013-05-04 | 2013-05-15 | 
+----+-----------+--------+---------+------+------+------------+------------+
9 rows in set (0.00 sec)


//将查询的结果导出,不知道为何名字是乱码
mysql> select * into outfile '/usr/local/mysql/test_outfile_one' from teacher_class;
Query OK, 9 rows affected (0.00 sec)
1 韩信 male php0115 207 21 2013-01-15 2013-02-20
2 韩信 male php0228 106 18 2013-02-28 2013-03-30
3 韩信 male php0331 102 22 2013-03-31 2013-05-05
5 李白 male php0115 207 20 2013-02-22 2013-03-25
6 李白 male php0228 204 22 2013-03-31 2013-04-29
7 韩非子 male php0115 207 15 2013-03-27 2013-04-18
8 韩信 male php0331 106 15 2013-05-28 2013-06-15
9 韩非子 male php0331 106 15 2013-05-28 2013-06-15
10 李白 male php0115 102 5 2013-05-04 2013-05-15


//将自己需要的数据保存，比如我需要韩信的数据
mysql> select * into outfile '/usr/local/mysql/my_test/outflie_hanxin' from teacher_class where t_name='韩信';
Query OK, 4 rows affected (0.00 sec)


gec@ubuntu:/usr/local/mysql/my_test$ cat outflie_hanxin 
1 韩信 male php0115 207 21 2013-01-15 2013-02-20
2 韩信 male php0228 106 18 2013-02-28 2013-03-30
3 韩信 male php0331 102 22 2013-03-31 2013-05-05
8 韩信 male php0331 106 15 2013-05-28 2013-06-15


注意：可以自动创建文件，但是如果要写入的文件已经存在则失败。
mysql> select * into outfile '/usr/local/mysql/my_test/outflie_hanxin' from teacher_class where t_name='韩信';
ERROR 1086 (HY000): File '/usr/local/mysql/my_test/outflie_hanxin' already exists


生成的文件格式：
默认的，采用行来区分记录，而采用制表符，来区分字段。


为了满足某种特别的需求，会采用不同的分割方式。
支持，在导出数据时，设置记录，与字段的分割符。


通过如下的选项：
fields:设置字段选项
lines:设置行选项(记录选项)
先看默认值:
字段:fields terminated by '\t' enclosed by '' escaped by '\\'
记录：lines terminated by '\n' starting by ''


可以自己设定：


mysql> select * into outfile '/usr/local/mysql/my_test/three'
    -> fields terminated by ','
    -> lines terminated by '\n' starting by 'start:'
    -> from teacher_class where t_name = '韩信';
Query OK, 4 rows affected (0.00 sec)
start:1,韩信,male,php0115,207,21,2013-01-15,2013-02-20
start:2,韩信,male,php0228,106,18,2013-02-28,2013-03-30
start:3,韩信,male,php0331,102,22,2013-03-31,2013-05-05
start:8,韩信,male,php0331,106,15,2013-05-28,2013-06-15


// enclosed by 'x' 设置数据被什么包裹，默认是什么都没有即‘’
mysql> select * into outfile '/usr/local/mysql/my_test/four' fields terminated by ',' enclosed by 'x' lines terminated by '\n' starting by 'start:' from teacher_class where t_name = '韩信';
Query OK, 4 rows affected (0.00 sec)


gec@ubuntu:/usr/local/mysql/my_test$ cat four
start:x1x,x韩信x,xmalex,xphp0115x,x207x,x21x,x2013-01-15x,x2013-02-20x
start:x2x,x韩信x,xmalex,xphp0228x,x106x,x18x,x2013-02-28x,x2013-03-30x
start:x3x,x韩信x,xmalex,xphp0331x,x102x,x22x,x2013-03-31x,x2013-05-05x
start:x8x,x韩信x,xmalex,xphp0331x,x106x,x15x,x2013-05-28x,x2013-06-15x


注意：
常规的，所有的记录，应该通过行来显示
例外是保存二进制数据：(一般来说图片数据都是二进制形式保存的，图片可以保存到
数据库里面的，但是，一般来说只数据库只保存图片的存储路径)
blob binary


使用into dumpfile
select * into dumpfile '/usr/local/mysql/my_test/four' fields terminated by ',' enclosed by 'x' lines terminated by '\n' starting by 'start:' from teacher_class where t_name = '韩信';


=========================lession 32=增加数据===========================
Insert 插入
insert into tbl_name(字段列表) values (值列表)


1.可以不将所有的字段都插入数据。
如果说需要完成部分字段的插入，需要必须存在字段列表


没有插入的字段，使用默认值：
insert into teacher_class (t_name) values ('张三丰');
mysql> select * from teacher_class;


| 11 | 张三丰 | NULL   | NULL    | NULL | NULL | NULL       | NULL       | 
+----+-----------+--------+---------+------+------+------------+------------+


2.如果是插入部分字段，可以使用下面的set语句：
insert into teacher_class set t_name='张无忌';
insert into teacher_class set t_name='赵敏',c_name='武术';
mysql> select * from teacher_class;
+----+-----------+--------+---------+------+------+------------+------------+
| id | t_name    | gender | c_name  | room | days | begin_date | end_date   |
+----+-----------+--------+---------+------+------+------------+------------+




| 11 | 张三丰 | NULL   | NULL    | NULL | NULL | NULL       | NULL       | 
| 12 | 张无忌 | NULL   | NULL    | NULL | NULL | NULL       | NULL       | 
| 13 | 赵敏    | NULL   | 武术  | NULL | NULL | NULL       | NULL       | 
+----+-----------+--------+---------+------+------+------------+------------+


3.值列表的语法，可以一次性插入多条数据：
每条数据采用记录构造符括号完成即可：


insert into teacher_class (t_name,c_name) values
('张君宝','太极'),
('杨过','黯然销魂掌');
mysql> select * from teacher_class;
+----+-----------+--------+------------+------+------+------------+------------+
| id | t_name    | gender | c_name     | room | days | begin_date | end_date   |
+----+-----------+--------+------------+------+------+------------+------------+
| 14 | 张君宝 | NULL   | 太极     | NULL | NULL | NULL       | NULL       | 
| 15 | 杨过    | NULL   | 黯然销� | NULL | NULL | NULL       | NULL       | 
+----+-----------+--------+------------+------+------+------------+------------+


4.插入数据时，如果主键冲突会如何？




mysql> insert into teacher_class (id, t_name, c_name) values
    -> (13, '杨露婵','太极');
ERROR 1062 (23000): Duplicate entry '13' for key 1




5.默认有主键约束，不会插入成功，但是可以在insert语法内，控制在主键冲突时，
改成执行更新操作。


mysql> select * from teacher_class;
+----+-----------+--------+------------+------+------+------------+------------+
| id | t_name    | gender | c_name     | room | days | begin_date | end_date   |
+----+-----------+--------+------------+------+------+------------+------------+
|  1 | 韩信    | male   | php0115    |  207 |   21 | 2013-01-15 | 2013-02-20 | 
|  2 | 韩信    | male   | php0228    |  106 |   18 | 2013-02-28 | 2013-03-30 | 
|  3 | 韩信    | male   | php0331    |  102 |   22 | 2013-03-31 | 2013-05-05 | 
|  5 | 李白    | male   | php0115    |  207 |   20 | 2013-02-22 | 2013-03-25 | 
|  6 | 李白    | male   | php0228    |  204 |   22 | 2013-03-31 | 2013-04-29 | 
|  7 | 韩非子 | male   | php0115    |  207 |   15 | 2013-03-27 | 2013-04-18 | 
|  8 | 韩信    | male   | php0331    |  106 |   15 | 2013-05-28 | 2013-06-15 | 
|  9 | 韩非子 | male   | php0331    |  106 |   15 | 2013-05-28 | 2013-06-15 | 
| 10 | 李白    | male   | php0115    |  102 |    5 | 2013-05-04 | 2013-05-15 | 
| 11 | 张三丰 | NULL   | NULL       | NULL | NULL | NULL       | NULL       | 
| 12 | 张无忌 | NULL   | NULL       | NULL | NULL | NULL       | NULL       | 
| 13 | 赵敏    | NULL   | 武术     | NULL | NULL | NULL       | NULL       | 
| 14 | 张君宝 | NULL   | 太极     | NULL | NULL | NULL       | NULL       | 
| 15 | 杨过    | NULL   | 黯然销� | NULL | NULL | NULL       | NULL       | 
+----+-----------+--------+------------+------+------+------------+------------+
14 rows in set (0.00 sec)


ql> insert into teacher_class (id, t_name, c_name) values
    -> (13,'杨露婵','太极')
    -> on duplicate key update
    -> t_name = '杨露婵',c_name='太极';
Query OK, 2 rows affected (0.00 sec)
mysql> select  * from teacher_class;+----+-----------+--------+------------+------+------+------------+------------+
| id | t_name    | gender | c_name     | room | days | begin_date | end_date   |
+----+-----------+--------+------------+------+------+------------+------------+
| 13 | 杨露婵 | NULL   | 太极     | NULL | NULL | NULL       | NULL       | 
| 14 | 张君宝 | NULL   | 太极     | NULL | NULL | NULL       | NULL       | 
| 15 | 杨过    | NULL   | 黯然销� | NULL | NULL | NULL       | NULL       | 
+----+-----------+--------+------------+------+------+------------+------------+
14 rows in set (0.00 sec)


6.内部存在的业务逻辑：
判断是否插入成功：
失败(主键冲突唯一索引冲突)，更新


插入(失败)，更新，更新完成
注意：update 不跟set


改变插入的数据源：


除了使用自定义数据外，还可以是使用select语句，查询到数据，作为插入的数据源.


mysql> select * from teacher_class;
14 rows in set (0.00 sec)


// 这个叫蠕虫复制
insert into teacher_class (t_name, c_name) select t_name, c_name from teacher_class;


mysql> select * from teacher_class;
28 rows in set (0.00 sec)


mysql> select * from teacher;
3 rows in set (0.02 sec)


7.数据可以来源于其他数据表，要求，字段数量和类型一致即可 ：


insert into teacher_class (t_name, c_name) select t_name, class_name from teacher;


mysql> select * from teacher_class;
31 rows in set (0.00 sec)


8.通过强制使用default 关键字，或者default函数，使用默认值：
insert into teacher values
(11, 'xxx', 'yyy', default),
(12, 'xxx', 'yyy', default(days));


mysql> select * from teacher;
+------+--------+------------+------+
| t_id | t_name | class_name | days |
+------+--------+------------+------+
|    1 | 韩A   | 0331       |   25 | 
|   -1 | 李A   | 0228       |   22 | 
|    3 | a      | 123        |   22 | 
+------+--------+------------+------+
3 rows in set (0.00 sec)


mysql> alter table teacher modify days tinyint(3) unsigned default 10;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> insert into teacher values
    -> (10, 'xxx', 'yyy', default);
Query OK, 1 row affected (0.03 sec)


mysql> select * from teacher;
+------+--------+------------+------+
| t_id | t_name | class_name | days |
+------+--------+------------+------+
|    1 | 韩A   | 0331       |   25 | 
|   -1 | 李A   | 0228       |   22 | 
|    3 | a      | 123        |   22 | 
|   10 | xxx    | yyy        |   10 | 
+------+--------+------------+------+
4 rows in set (0.00 sec)


mysql> insert into teacher values
    -> (11, 'xxx', 'yyy', default),
    -> (12, 'xxx', 'yyy', default(days));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from teacher;
+------+--------+------------+------+
| t_id | t_name | class_name | days |
+------+--------+------------+------+
|    1 | 韩A   | 0331       |   25 | 
|   -1 | 李A   | 0228       |   22 | 
|    3 | a      | 123        |   22 | 
|   10 | xxx    | yyy        |   10 | 
|   11 | xxx    | yyy        |   10 | 
|   12 | xxx    | yyy        |   10 | 
+------+--------+------------+------+
6 rows in set (0.00 sec)


9.Replace 主键或唯一索引冲突，则替换，否则插入。
mysql> insert into teacher values
    -> (1,'韩非','法家',30);
ERROR 1062 (23000): Duplicate entry '1' for key 1


mysql> replace into teacher values
    -> (1,'韩非','法家',30);
Query OK, 2 rows affected, 1 warning (0.00 sec)


mysql> select * from teacher;
+------+--------+------------+------+
| t_id | t_name | class_name | days |
+------+--------+------------+------+
|    1 | 韩��  | 法家     |   30 | 


mysql> replace into teacher values
    -> (13,'韩非','法家',30);
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> select * from teacher;
|   13 | 韩��  | 法家     |   30 | 
+------+--------+------------+------+


10.导入数据load infile


// 第一个null是为主键的自动增长做准备的
select null,t_name, class_name, days from teacher;


//自动增长只能在主键上面做
mysql> alter table teacher modify t_id int auto_increment;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0


mysql> load data infile '/usr/local/mysql/five' into table teacher;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from teacher;
+------+--------+------------+------+
| t_id | t_name | class_name | days |
+------+--------+------------+------+
|    1 | 韩��  | 法家     |   30 | 
|   -1 | 李A   | 0228       |   22 | 
|    3 | a      | 123        |   22 | 
|   10 | xxx    | yyy        |   10 | 
|   11 | xxx    | yyy        |   10 | 
|   12 | xxx    | yyy        |   10 | 
|   13 | 韩��  | 法家     |   30 | 
|   14 | 韩��  | 法家     |   30 | 
|   15 | 李A   | 0228       |   22 | 
|   16 | a      | 123        |   22 | 
|   17 | xxx    | yyy        |   10 | 
|   18 | xxx    | yyy        |   10 | 
|   19 | xxx    | yyy        |   10 | 
|   20 | 韩��  | 法家     |   30 | 
+------+--------+------------+------+
14 rows in set (0.00 sec)


总结：
load data infile 'file' into table tbl_name;
导入select * into outfile 'file' 命令 导出的内容呢


注意：
导入时，涉及到数据增加，需要考虑，是否冲突的情况.


通常，可以在导出时，将主键导出成null,
利用自动增长的特性，可以形成新的主键；


=========================lession 33=删除更新===========================
1.删除


允许使用条件(删除符合条件的数据)


1.1 允许使用limit:
限制删除的记录数。
limit N;


常见的是
1.2 limit 配合 order by 来使用：
先将结果排序，再删除固定数量的记录：
delete from teacher order by days limit 10;
只有order by 是没有意义的。


1.3 允许连接删除：
允许使用类似的join语法，同时删除多个表内的记录。
//先提供表名，再提供连接条件，可拆分成 delete one,delete two
delete from one, two using one join two on 
one.public_field=two.public_field where one_id=2;


1.4 清空表：Truncate类似：delete from table; (truncate tbl_name)
(1). 不会返回删除的记录数.
(2). 重建自动增长的主键.


delete: 逐行删除。保存删除的记录数.
truncate: 删除表, 新建表.


2.更新数据


replace, insert on duplicate key update


Update
where
order by
limit


多表更新：
update one join two on one.public_field=two.public_field
set one_data='x', two_data='y' where one_id=3;


=========================lession 34=备份还原===========================
1.方案1：
适用于mysiam表：


直接将tbl_name.frm, tbl_name.myd, tbl_name.mysi 三个文件,保存，备份即可.
需要的时候，直接解压到移动到相应的数据库目录内即可


注意：如果是同样的方法，处理innodb表结构的文件。
则使用showtable时，也可以看到,但是不能使用的。




2.方案2：
通用的方案：
思路是：将建表结构，与插入数据的sql语句生成并保存，下次如果需要该结构和数据
直接将数据语句执行即可。


利用mysql提供的工具完成的。
不是sql语言的一部分。


所以不需要在mysql命令行客户端执行。直接运行即可。


备份当前数据库test1;


//在执行下面这句话的时候我遇到一个问题(1)，就是提示bash: /usr/local/mysql/test1.sql: Permission denied 
//后来我切换到root下执行就没问题了,在切换用户的时候又遇到了问题(2)
//su: Authentication failure ,
//解决办法 sudo passwd root
//Enter new UNIX password:
//Reptype new UNIX password:
//passwd:password updated successfully


mysqldump -uroot -p test1 > /usr/local/mysql/test1.sql (在bin目录里面)


将备份的数据库还原：
就是将刚刚生成的sql语句，再执行一遍.


在mysql客户端，直接执行即可：
如何执行保存在文件内的sql语句:
使用source指令，可以指定需要执行sql语句的源代码文件.
mysql> create database bak;
mysql> use bak;
mysql> source /usr/local/mysql/test1.sql


3.常用的备份操作
3.1 备份整个数据内的表
mysqldump -uroot -p db_name > bak.sql
3.2.1 备份数据库内的某张表：
mysqldump -uroot -p db_name tbl_name > bak.sql
3.2.2 备份数据库内的多张表：
mysqldump -uroot -p db_name tbl_name1 tbl_name2 ...tbl_nameN > bak.sql


=========================lession 35=视图入门===========================


1.视图的作用：隐藏部分信息，只是显示部分信息


2.创建视图：


create view view_name as select_statement;


create view v_teacher as select id, t_name from info_teacher;
相当于下面这条语句:
select * from (select id, t_name from info_teacher) as temp;


视图就是一个存在于数据库中的虚拟表了；


视图，本身没有数据，只是通过执行相应的select 语句完成获得相应的数据。


=========================lession 36=视图操作===========================
1. 视图管理


1.1 删除视图：
Drop view if exists view_name; (drop view v_teacher)


1.2 修改视图


1.2.1 修改视图列
alter view v_name as select_statement;
//修改视图表v_t1改为info_teacher
alter view v_t1 as select * from info_teacher;


1.2.2 修改视图列名称
//视图名称后，使用(字段列表即可)
//修改视图表v_teacher列的名称为v1,v2
alter view v_teacher (v1,v2) as select id, t_name from info_teacher;


2.缩减业务逻辑
通过视图还可以，是复杂的业务逻辑，简单的完成，先使用视图完成一定的逻辑，
再在视图的基础上，完成另外的逻辑。
通常，视图完成的逻辑，都是相对来说的比较基础的逻辑。


比如：
mysql> create view join_info as select tc.id as tc_id, t_name, c_name, days 
    -> from join_teacher_class as tc left join join_teacher as t 
    -> on tc.t_id=t.id left join join_class as c on tc.c_id=c.id;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from join_info;
+-------+-----------+---------+------+
| tc_id | t_name    | c_name  | days |
+-------+-----------+---------+------+
|     1 | 韩信    | php0115 |   15 | 
|     2 | 韩信    | php0228 |   18 | 
|     3 | 韩信    | php0331 |   22 | 
|     4 | 李白    | php0115 |   20 | 
|     5 | 李白    | php0228 |   22 | 
|     6 | 韩非子 | php0115 |   15 | 
|     7 | 韩信    | php0115 |   15 | 
|     8 | 韩非子 | php0331 |   15 | 
|     9 | 李白    | php0115 |    5 | 
+-------+-----------+---------+------+
9 rows in set (0.00 sec)


3.视图的执行算法
存在两种执行算法：(1)merge (合并) (2)temptable(临时表)


指的是一个视图是在什么时候执行，依据哪些方式执行：


merge:合并的执行方式，每当执行的时候，现将我们视图的sql语句与外部查询视图
的sql语句，混合在一起，最终执行：


Temptable:临时表，模式，每当查询的时候，将视图所使用select 语句，生成一个
结果的临时表.再在当前的临时表内进行查询.


当用户创建视图时，mysql默认使用一种undefine的处理算法:就是会自动在合并和
临时表内进行选择。


//班级内，代课天数最多的信息
select c_name , max(days) from teacher_class group by c_name order by 
days asc;


create view c_days as select c_name, max(days) as max_days from 
teacher_class
group by c_name order by days asc; 


select * from c_days group by max_days;


select * from (select c_name, max(days) as max_days from teacher_class
group by c_name order by days asc) as temp group by max_days;


select c_name, sum(days) from teacher_class group by c_name;


总结：
注意：
1.尽量使用视图完成读操作
2.如果使用视图，则需要注意，对视图的修改，也是真实表数据的修改，会即时生效.
3.删除视图时，是不会销毁实体表内的数据的。
4.如果大家做的事外部接口，一个数据库多个应用,采用不同的视图接口。


=========================lession37=事务使用===========================
1.事务：
一组sql语句操作单元，组内所有sql语句完成一个业务，如果整租成功，意味这
全部都实现。
如果其中任何一个失败，意味着整个操作都失败，失败，意味这整个过程都是没有意
义的。应该使数据库回到操作前的初始状态。


上面的特性就是事务。


2.如何处理：
2.1 失败后可以回到开始位置
2.2 没成功之前，别的用户(进程,会话)是不能看到操作内的数据修改的。


3.思路：
就是在一组操作之间，设计一个记号，备份点。


4.实现：
利用innodb存储引擎的事务日志功能：


SQL 事务日志内：如果成功，则将结果提交到数据库内，
就意味这更改了数据库内容


4.1 执行语句：
SQL 的执行分2个阶段：
1.执行阶段
2.将执行结果，提交的数据库的阶段


其中我们的事务日志，就是保存执行阶段的结果
如果用于选择提交，则才将执行结果提交到数据库。


默认的执行方式叫，自动提交，执行完毕，自动完成提交工作。


因此需要关闭自动提交功能。


存在一个系统的变量，
auto_commit可以对自动提交进行配置(show variables like 'auto_commit')


关闭(set auto_commit=0)后，再次执行相应的更新语句：
发现，在其他连接中查看数据，没有发生变化，因为结果没有提交。


4.2 提交或回滚：
在此基础上执行完所有的sql语句。
判读是否都成功(出现错误，包括语法错误，和逻辑错误，服务器错误)。


成：将结果提交--利用commit，然后set auto_commit = 1;
败：回到开始位置--Rollback即可


5.常见的事务的指令
开启事务
start transaction；可以使用begin，但是不建议用begin
关闭自动提交，如果事务结束了，成或败，
都会将自动提交机制，回到start时的状态。


6.限定:
在innodb下生效（DBD）


7.事务的特点
7.1 原子性。
7.2 一致性（可以理解为在一个进程，会话执行事务的时候，被执行的表是上锁的）
7.3 隔离性。(一个事务操作时，影响的只是它自己，不用影响到其他事务)
7.4 持久性。
ACID (特点)
mysql> create table class (
    -> id int primary key auto_increment,
    -> cz_money decimal(10,2) comment '班费'
    -> );


insert into class values (null, 1250);


create table czbk_student(
id int primary key auto_increment,
stu_money decimal(10,2) comment '学生拥有的金钱'
);


insert into czbk_student values (null,5000);


//班费 30
update czbk_student set stu_money=stu_money-30 where id=1;
update class set cz_money=cz_money+30 where id=1;


=========================lession38=触发器===========================
监听数据进行操作：


在当前的表上，设置一个对每行数据的一个监听器，监听相关事件
每当事件发生时，会执行一段由sql完成的一段功能代码。


触发器的元素：
事件：执行代码.


1.创建触发器
create trigger 名字，事件， 执行性代码


2.事件：
插入 insert 
删除 delete
修改 update


事件的时机：执行之前和执行之后。After Before


由时机和事件在一起形成了六中事件。
before insert, before delete, before update
after insert, after delete, after update


create trigger jiaobanfei after update on czbk_student
for each row 
update class set cz_money=cz_money-20;


规定，在哪个表上的什么时机的什么动作上。


可执行代码：
SQL语句组成的代码。


3.触发，触发程序：
特定事件发生，即触发：
update czbk_student set stu_money=stu_money+20 where id =1;


4.注意：
4.1 触发器不能同名
4.2 目前mysql只支持一类事件设置一个触发器


5.管理触发器：
删除：
drop trigger trigger_name;
查看：
show create trigger trigger_name


6.在触发器内，获得触发该触发器程序时的数据
利用触发程序内的 new 和 old 来完成。


old:监听事件所在表上的数据，在事件发生之前时的数据，旧的数据。


new:监听表上，事件发生之后，新处理 完毕的数据。


数据，就是触发该事件的记录。


drop trigger jiaobanfei;
create trigger jiaobanfei after update on czbk_student
for each row 
update class set cz_money=cz_money+(old.stu_money - new.stu_money);
update czbk_student set stu_money=stu_money-500 where id =1;


注意：
事件是insert呢？  不能使用old


drop trigger ruxue;
create trigger ruxue after insert on czbk_student
for each row
update class set stu_count = stu_count + 1;


drop trigger ruxue;
create trigger ruxue after insert on czbk_student
for each row
update class set stu_count = stu_count + new.id;


事件是delete 呢？ 不能使用new


drop trigger ruxue;
create trigger tuixue after delete on czbk_student
for each row
update class set stu_count = stu_count + old.id;


7.如果一个触发程序由多条sql语句组成：
应该：
7.1 语句组成语句块(begin end) 用来标识语句块
7.2 语句块的语句需要独立的语句结束符，分号，


命令行：由于触发器程序内使用分号作为语句结束符，
那么当命令行客户端碰到分号是，就应该理解成
触发器程序内语句结束，而不是整个创建触发器的语句结束。


应该通过修改命令行的语句结束符达到目的。
Delimiter语句可以完成设置语句结束符。


drop trigger ruxue;


delimiter $$
create trigger ruxue after insert on czbk_student
for each row
begin
update class set stu_count = stu_count + 1;
update class set cz_money = cz_money + 20;
end
$$
delimiter ;


insert into czbk_student values (2,1980);


测试：


究竟可以触发哪些触发器:
insert into on duplicate key update
replace;


=========================lession39=php操作mysql========================
1.准备工作
mysql C/S


mysql 服务器存在。
利用mysql客户端操作.


1.1解决的问题：
(1).mysql服务器，开发接口，允许其他语言操作。
(mysql-server mysql-client,mysql-development)
Windows下，需要将mysql提供的libmysql.dll(在lib文件夹里面)放置到可以被
别的程序使用的地方。通常是系统盘符的windows下


(2).相应的客户端，利用mysql服务器开放的接口，完成编程，操作mysql数据库。
在php内，增加一个模拟客户端的功能。


常见的,php操作mysql的客户端有三个：mysql,mysqli,PDO使用其中之一，即可。


mysql,PDO.


步骤：
载入相应的功能：
载入php的相关扩展：




可以通过测试，得知，mysql是否被载入：
1.通过phpinfo
2.利用php代码测试


<?php


var_dump(function_exists('mysql_connect'));


=========================lession40=mysql函数(1)========================
PHP编码对数据库的操作：
1.连接和认证


mysql_connect(连接目标，用户，密码);


连接目标:服务器地址+端口号


//连接
$target = 'localhost:3306';  //mysql默认端口是3306
$user = 'root';
$pass = '1234abcd';


//mysql_connect('localhost:3306','root','1234abcd');
$link = msyql_connect($server, $user, $pass);


var_dump($link);


2.执行相应的sql语句
mysql_query(); //发送一条sql语句，到服务器端。
当语句执行成功后，会有结果返回给php;


结果有2种形式，取决于当前执行的sql,是否具有返回结果：


$query = "show databases";
$result = mysql_query($query);
var_dump($result); // resource(4) fo type (mysql result)


执行sql语句：
$query = "set names gbk";
$result = mysql_query($query);
var_dump($result);


3.处理结果.
决定于执行结果，两种处理方式：


a.只返回布尔值，表示是否执行成功的：
直接更具布尔值，给出用户提示即可：


b.返回结果集的，存在返回数据。
在结果集内，提取我们需要的数据。
提取，通常就是fetch操作。


mysql_fetch_assoc();
mysql_fetch_row();
mysql_fetch_array();


上面的三个函数，功能一致：在结果集内，取得一个条记录。
区别在于返回值的形式上。
都是数组，但是数组元素的下标是不同的。


assoc关联数组，下标是字段名(字符串)
row索引数组，下标是字段位置(0,1,2)
array混合数组，下标2部分，字段名，和索引位置。


通常需要获得所有的数据：
fetch只能获得一条，但是可以自动移动，结果集的记录指针。
配合循环结构，完成所有数据的操作。


常见的：
show，desc, select 返回结果集的


返回布尔值：
insert，update， delete， ddl


4.如果所有的操作都完成，将连接释放，
// 关闭
mysql_close();


存在变化的就是，大家需要完成各种各样的sql语句，进行处理相应的功能。


//一般的处理流程
<?php


//1
$link = mysql_connect('localhost:3306','root','1234abcd');


//2
//执行语句
$query = "set names utf8";
mysql_query($query);
$query = "select * from php_one.teacher_class";
$result = mysql_query($query);


//3 select count(*)
var_dump($row = mysql_fetch_assoc(result));


//4
mysql_close();


5.注意的细节：
（1）连接资源，原则上，执行任何一个操作，都应该指明当前所使用的连接资源，
但是，php可以自动帮助我们找到(如果连接资源有2个或以上的话，php就会不知道
连接哪一个，所以一般来说建议指定连接资源)，


已经存在的连接资源。


建议就是，尽量指定数据连接资源,在mysql_query内，使用第二个参数：


大多数的mysql函数都有这link参数。但是fetch除外
//那么如何指定连接资源，如下：
$result = mysql_query($query,$link);


(2)注意，结果都是以记录的形式返回的即使，返回值，
只有一条记录的一个字段，也是如此。


=========================lession41=mysql函数(2)========================
功能函数


1.错误处理


php利用mysql扩展执行sql语句时，一但sql语句发生错误，不会显示错误信息。
称之为静默模式的错误处理机制。


也是可以获得错误信息的：
mysql_error();错误信息 //写上 echo mysql_error();即可
//example
$query = "set names utf8";
if(!mysql_query($query)){
// failed
//echo mysql_error();
exit(mysql_errno() . ':' . mysql_error());
//echo '<br>';
}
mysql_errno();错误编码


常用的，封装一个执行sql的方法，所有的操作都调用这个方法，在方法内部做
错误处理。


2.获得执行结果额外的信息


2.1 获得结果集内部的数据数量
mysql_num_rows(结果集);
$rows_in_set = mysql_num_rows($result);
echo "$rows_in_set rows in set"
适用于：查询


2.2 操作后，受影响的行数：
mysql_affected_rows();
//example
$query = "delete from php_one.teacher_class where id > 8";
mysql_query($query);
echo '当前删除了', mysql_affected_rows(), '条';
适用于：增删改


2.3 获得最新的自动增长的字段的值。
通常，每个 表，都会有，自动增长的ID作为主键


但是该值，在插入数据时，是不能知道的，是数据库通过计算生成。


如果说，在插入后，需要依赖刚刚插入的数据，进行运算操作的话，需要确定数据，
利用生成的主键ID确定：


需要一个：在数据插入后，获得刚刚生成的主键的操作
mysql_inseret_id();


//example
$query = "insert into php_one.teacher values (null, 'xiaohan', 'php0330')";
mysql_query($query);
echo mysql_insert_id();
// in common use
$new_id = mysql_inseret_id();
mysql_query("update php_one.teacher set days = 20 where id = $new_id");


2.4 如果资源使用完毕，也应该释放资源。
mysql_free_result(结果集);


=========================lession42=MySQL编程========================


SQL:结构化查询语言。
是一门编程语言，是用于管理数据的编程语言


1.元素：
数据
数据类型
变量
函数
控制流程
运算符
注释


1.1 注释：
行：
#
//example
select * from teacher ;#where id > 10;
--[空格]
//example
select * from teacher ; -- where id > 10;
块：
/**/


1.2 结束符：
命令行："\g" , "\G", "';"


可以使用delimiter 来修改语句的结束符
delimiter $$


1.3 变量：
字段名就是变量。
php有许多系统默认变量例如：
mysql> show variables like 'char%';


用户自定义变量：


定义一个变量：
set 变量名=变量值.
注意：为了区分系统变量和字段与用户自定义变量，需要在用户变量前，增加@标识符
//example


mysql> set @who = '韩当';
Query OK, 0 rows affected (0.00 sec)


mysql> set who = '韩当';
ERROR 1193 (HY000): Unknown system variable 'who'


通过select语句可以获得当前的变量的值：
mysql> select @who;
+--------+
| @who   |
+--------+
| 韩当 | 
+--------+
1 row in set (0.00 sec)


Set是专门的为变量赋值的形式，甚至可以子查询：
set @total = (select count(*) from join_teacher);


定义一个变量select info.


select 字段 表达式...into 变量列表.
// example
select 10, 15, 20 into @a, @b, @c;
select  @a, @b, @c;


mysql> select 10, 15, 20 into @a, @b, @c;
Query OK, 1 row affected (0.00 sec)
mysql> select  @a, @b, @c;
+------+------+------+
| @a   | @b   | @c   |
+------+------+------+
| 10   | 15   | 20   | 
+------+------+------+
1 row in set (0.00 sec)


// example
select c_name from join_class where id=1 into @c_name;
select @c_name;
注意：select into @var 要求，只能返回一行，如果返回多行，会语法错误，
或者只将最后一行的数据，注入到变量内.


while($a = 10);


利用select语句的部分表达式达到为变量赋值的目的：
mysql> select @who = 'xiaoli'; //是关系判断
| @who = 'xiaoli' |
+-----------------+
|               0 | 
+-----------------+
1 row in set (0.00 sec)
使用：=的形式.
mysql> select @who := 'xiaoli';
+------------------+
| @who := 'xiaoli' |
+------------------+
| xiaoli           | 
+------------------+
1 row in set (0.00 sec)
mysql> select @who;
+--------+
| @who   |
+--------+
| xiaoli | 
+--------+
1 row in set (0.00 sec)
注意，=应该是赋值，但是在select语句内，就成了关系等于,使用专门的
赋值运算符:= 同样适用于set.
mysql> set @i := 'han';
Query OK, 0 rows affected (0.00 sec)


mysql> select @i;
+------+
| @i   |
+------+
| han  | 
+------+
1 row in set (0.00 sec)


使用变量是在表达式，或者使用select查询即可.
1.作用域.用户定义的函数,是全局的(函数内可用).存在局部作用域变量,函数内
定义的变量.
2.有效期.会话结束(连接结束).


1.4 运算符


1.5 函数
1.5.1 内置函数


数值：
rand() 得到1-0之间的随机数.
如何得到5到10之间的数？
5+（0-5）
5+rand()*5;


取整：
floor(5+rand()*5); //取得5到10之间的随机的整数
mysql> select floor(5+rand()*5);
+-------------------+
| floor(5+rand()*5) |
+-------------------+
|                 7 | 
+-------------------+
1 row in set (0.00 sec)


格式化：
format: //千分位
mysql> select format(1234567.1234,2);
+------------------------+
| format(1234567.1234,2) |
+------------------------+
| 1,234,567.12           | 
+------------------------+
1 row in set (0.01 sec)


时间日期：
(1).now();


(2).Unix_timestamp();


mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1468391945 | 
+------------------+
1 row in set (0.01 sec)


(3).from_unixtime();
mysql> select from_unixtime(12345);
+----------------------+
| from_unixtime(12345) |
+----------------------+
| 1970-01-01 11:25:45  | 
+----------------------+
1 row in set (0.00 sec)


select from_unixtime(unix_timestamp());
mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2016-07-13 14:42:10             | 
+---------------------------------+
1 row in set (0.00 sec)


字符串：
(1).concat(); 字符串连接
mysql> select concat('abcd','传智博客');
+-------------------------------+
| concat('abcd','传智博客') |
+-------------------------------+
| abcd传智博客              | 
+-------------------------------+
1 row in set (0.00 sec)


(2).substring();
mysql> select substring('传智博客',3,2);
+-------------------------------+
| substring('传智博客',3,2) |
+-------------------------------+
| 博客                        | 
+-------------------------------+
1 row in set (0.00 sec)


(3).char_length(); //字符为单位
mysql> select char_length('传智博客');
+-----------------------------+
| char_length('传智博客') |
+-----------------------------+
|                           4 | 
+-----------------------------+
1 row in set (0.00 sec)


(4).length(); //字节为单位
mysql> select length('传智博客');
+------------------------+
| length('传智博客') |
+------------------------+
|                     12 | 
+------------------------+
1 row in set (0.00 sec)


(5).lpad(); //前端补齐
lpad(需要补足的字符串,补足后的长度，用于补足的字符串);
mysql> select lpad('1',3,'0');
+-----------------+
| lpad('1',3,'0') |
+-----------------+
| 001             | 
+-----------------+
1 row in set (0.00 sec)


mysql> select lpad('1',5,'sbsbsb');
+----------------------+
| lpad('1',5,'sbsbsb') |
+----------------------+
| sbsb1                | 
+----------------------+
1 row in set (0.00 sec)


其他：
(1).MD5();
mysql> select md5('1');
+----------------------------------+
| md5('1')                         |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b | 
+----------------------------------+
1 row in set (0.00 sec)


(2).password(); //专门供sql用的
mysql> select password('1');
+-------------------------------------------+
| password('1')                             |
+-------------------------------------------+
| *E6CC90B878B948C35E92B003C792C46C58C4AF40 | 
+-------------------------------------------+
1 row in set (0.00 sec)


(3).sha1();
mysql> select sha1('1');
+------------------------------------------+
| sha1('1')                                |
+------------------------------------------+
| 356a192b7913b04c54574d18c28d46e6395428ab | 
+------------------------------------------+
1 row in set (0.00 sec)


练习：随机获得一位老师的信息.


1.5.2 用户定义函数


=========================lession43=自定义函数========================


自定义函数
要素：
函数名
参数列表
函数体
返回值


1.语法:
1.1 定义：
create function 函数名 （参数列表）返回值类型
函数体


--自定义函数
delimiter $$
create function sayHello() returns varchar(20)
begin
return 'hello!';
end
$$
delimiter ;


会产生一个错误：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)


解决办法：动态设置log_bin_trust_function_creators =1 服务器重启后就无效
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.01 sec)


mysql> delimiter $$
mysql> create function sayHello() returns varchar(20)
    -> begin
    -> return 'hello!';
    -> end
    -> $$
Query OK, 0 rows affected (0.02 sec)


mysql> delimiter ;


1.2 调用
mysql> select sayHello();
+------------+
| sayHello() |
+------------+
| hello!     | 
+------------+
1 row in set (0.01 sec)


注意：函数是与当前的数据库绑定的，可以使用 "库名.函数名" 的形式调用：
切换到另外的数据库 test2 的时候，它是补存在的
mysql> select sayHello();
ERROR 1305 (42000): FUNCTION test2.sayHello does not exist


mysql> select test1.sayHello();
+------------------+
| test1.sayHello() |
+------------------+
| hello!           | 
+------------------+
1 row in set (0.00 sec)


1.3 sql中的流程控制
1.3.1 分支
if 条件1 then
条件1满足执行的语句
elseif 条件2 then
条件2 满足执行的语句
...
else
上面的条件全都补满足，执行的语句
end if;


elseif 和 else都是可以省略的.
// example
delimiter $$
create function func1() returns varchar(20)
begin
-- hour 可以获得当前时间的小时部分
if hour(now()) >= 18 then
return '晚';
else
return '早';
end if;
end
$$
delimiter ;


删除自定义函数：
drop function if exists func2;


1.3.2 循环
(1).while 条件 does
循环体
delimiter $$
create function func3() returns varchar(20)
begin
-- 1-10的和
set @i = 1;
set @sum = 0;


while @i <= 10 do
set @sum = @sum + @i;
set @i = @i + 1;
end while;


return @sum;
end
$$
delimiter ;


(2).循环的提前终止
leave,break //终止循环 
iterate,continue //终止当前循环
注意，不是根据leave和iterate所在的位置来决定终止那个循环，而是由循环的
标签来决定的.


(3).循环的标签，给循环起名字.


标签：while
end while
// example
(
w:while
...
leave w;
end while w ;
)


delimiter $$
create function func4() returns varchar(20)
begin
-- 1-10的和
set @i = 1;
set @sum = 0;


w:while @i <= 10 do
if @i = 5 then
--set @i = @i + 1;
--iterate w
l eave w;
set @sum = @sum + @i;
set @i = @i + 1;
end while w;
return @sum;
end
$$
delimiter ;


函数内使用的变量
@var的形式,相当与全局变量，函数内和函数外通用.


函数的参数；




delimiter $$
drop function if exists sayHello;
create function sayHello(user_name varchar(10)) returns varchar(20)
begin
return concat('hello',user_name);
end
$$
delimiter ;


函数声明的局部变量：
使用declare声明局部变量，需要指定类型，可以指定默认值default.
drop function if exists func2；
delimiter $$
create function func() return int
begin
--1-10的和
declare i int default 0;


while i <= 10 do
set total = total + i;
set i = i + 1；
end while;


return total;
end
$$
delimiter ;


insert into join_student values
(null,'php0331004','3','李寻欢','some info');


insert into join_student values
(null, );


1.获取当前班级内，最大的学号(或得到，或者没有)
2.有：增1
2.没有：从001开始


已知条件，班级id


delimiter $$
create function sno(c_id int) returns char(10)
begin
declare s_no char(10); #保存当前班级内，最大的学号，如果没有就是null
declare class_name char(7);
select stu_no from join_student where class_id = c_id order by 
stu_no desc into s_no;


if isnull(s_no) then
--没有学生，从1开始,获得班级名字
select c_name from join_class where id=c_id into class_name;
return '001';
else
--有，最大值+1
return concat(left(s_no,7),lpad(substring(s_no,-3,3) + 1,3,'0'));
end
$$
delimiter ;