mysql 第十七节 mysql补充

mysql 补充

mysql 高级应用

 

表结构 和 表数据 复制

create table t2 like t1;
insert into t2 select * from t1;

mysql 视图

view 相当于一个临时表

表存在视图可用
表删除 视消失

表恢复 视图回复
mysql> create view v_t1 as select * from t1 where id>4 and id<8;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_t1;

mysql 预处理
mysql> select * from student;
+----+------+------+--------+--------------+
| id | name | age | gender | address |
+----+------+------+--------+--------------+
| 1 | 小红 | NULL | NULL | NULL |
| 2 | 小名 | 21 | 男生 | 北京昌平区 |
| 3 | 小狗 | 28 | 保密 | 北京市海淀区 |
+----+------+------+--------+--------------+
3 rows in set (0.00 sec)

mysql> prepare test from "select * from student where id >?";
# 创建预处理,?是一个占位符号.
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @i=2;
# 设置这个占位符号应该是多少.
Query OK, 0 rows affected (0.00 sec)

mysql> execute test using @i;
# 执行预处理,此时该句sql语句应该是 select * from students where id>2.
+----+------+------+--------+--------------+
| id | name | age | gender | address |
+----+------+------+--------+--------------+
| 3 | 小狗 | 28 | 保密 | 北京市海淀区 |
+----+------+------+--------+--------------+
1 row in set (0.00 sec)

mysql> set @i=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute test using @i;
+----+------+------+--------+--------------+
| id | name | age | gender | address |
+----+------+------+--------+--------------+
| 2 | 小名 | 21 | 男生 | 北京昌平区 |
| 3 | 小狗 | 28 | 保密 | 北京市海淀区 |
+----+------+------+--------+--------------+
2 rows in set (0.00 sec)

mysql> drop prepare test;# 删除预处理
Query OK, 0 rows affected (0.00 sec)

MySQL 事务

事务之前 先要关闭数据库 自动提交事务
set autocommit = 0;
mysql> insert into student(name) values("user11"),("user12"),("user13"),("user14"),("user15");
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> savepoint p1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name) values("user21"),("user22"),("user23"),("user24"),("user25");
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> savepoint p2;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name) values("user31"),("user32"),("user33"),("user34"),("user35");
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> savepoint p3;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from student;
Query OK, 18 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> rollback to p2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+--------+------+--------+---------+
| id | name | age | gender | address |
+----+--------+------+--------+---------+
| 19 | user11 | NULL | NULL | NULL |
| 20 | user12 | NULL | NULL | NULL |
| 21 | user13 | NULL | NULL | NULL |
| 22 | user14 | NULL | NULL | NULL |
| 23 | user15 | NULL | NULL | NULL |
| 24 | user21 | NULL | NULL | NULL |
| 25 | user22 | NULL | NULL | NULL |
| 26 | user23 | NULL | NULL | NULL |
| 27 | user24 | NULL | NULL | NULL |
| 28 | user25 | NULL | NULL | NULL |
+----+--------+------+--------+---------+
10 rows in set (0.00 sec)

MySQL 存储
mysql> \d // 更改结束符

mysql> select * from student//
+----+--------+------+--------+---------+
| id | name | age | gender | address |
+----+--------+------+--------+---------+
| 19 | user11 | NULL | NULL | NULL |
| 20 | user12 | NULL | NULL | NULL |
| 21 | user13 | NULL | NULL | NULL |
| 22 | user14 | NULL | NULL | NULL |
| 23 | user15 | NULL | NULL | NULL |
| 24 | user21 | NULL | NULL | NULL |
| 25 | user22 | NULL | NULL | NULL |
| 26 | user23 | NULL | NULL | NULL |
| 27 | user24 | NULL | NULL | NULL |
| 28 | user25 | NULL | NULL | NULL |
+----+--------+------+--------+---------+
10 rows in set (0.00 sec)

mysql> \d ;
mysql> select * from student;
+----+--------+------+--------+---------+
| id | name | age | gender | address |
+----+--------+------+--------+---------+
| 19 | user11 | NULL | NULL | NULL |
| 20 | user12 | NULL | NULL | NULL |
| 21 | user13 | NULL | NULL | NULL |
| 22 | user14 | NULL | NULL | NULL |
| 23 | user15 | NULL | NULL | NULL |
| 24 | user21 | NULL | NULL | NULL |
| 25 | user22 | NULL | NULL | NULL |
| 26 | user23 | NULL | NULL | NULL |
| 27 | user24 | NULL | NULL | NULL |
| 28 | user25 | NULL | NULL | NULL |
+----+--------+------+--------+---------+
10 rows in set (0.00 sec)

MySQL 的自动存储调用 begin end 语法使用

mysql> \d // # 将;结束符号改为//结束符号
mysql> create procedure inserts() # 创建程序 insets()
-> begin # 开始程序
-> set @i=1; # 设置循环的初始值
-> while @i<=1000 do # 循环次数为1000次
-> insert into student(name) values(concat("user",@i)); # 插入数据的信息值
-> set @i=@i+1; # 循环一次加1
-> end while; # 结束语
-> end // # 使用//标志结束
mysql> \d ; # 改变结束符号

Query OK, 0 rows affected (0.01 sec)
mysql> truncate student; # 清空表student
Query OK, 0 rows affected (0.03 sec)
mysql> call inserts; # 调用程序insets
Query OK, 0 rows affected (0.02 sec)

mysql> select * from student; # 查询自动插入的程序
+------+----------+------+--------+---------+
| id | name | age | gender | address |
+------+----------+------+--------+---------+
| 1 | user1 | NULL | NULL | NULL |
| 2 | user2 | NULL | NULL | NULL |
| 3 | user3 | NULL | NULL | NULL |
| 4 | user4 | NULL | NULL | NULL |
| 5 | user5 | NULL | NULL | NULL |
以下会自动生成
| 996 | user996 | NULL | NULL | NULL |
| 997 | user997 | NULL | NULL | NULL |
| 998 | user998 | NULL | NULL | NULL |
| 999 | user999 | NULL | NULL | NULL |
| 1000 | user1000 | NULL | NULL | NULL |
+------+----------+------+--------+---------+
1000 rows in set (0.00 sec)