INSERT INTO：
第一种：
INSERT INTO tb_name [(col1,col2,...)] {VALUES|VALUE} (val1,val2,...)[,(val21,val22,...),...]
第二种：
INSERT INTO tb_name SET col1=val1,col2=val2,...
第三种
INSERT INTO tb_name SELECT clause
REPLACE的工作机制：与INSERT相同，除了在新插入的数据与表中的主键或唯一索引定义的数据形同会替换老的行：
UPDATE：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1=val1 [,col_name2=val2] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
UPDATE通常情况下，必须要使用WHERE字句,或者使用LIMIT限制要更改的行数：
--safe-update
DELETE:
用于删除数据
DELETE：
DELETE [LOW_PRIORITY][QUICK][IGNORE] FROM tb1_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
TRUNCATE tb_name
crud:
explain
MYSQL锁：
执行操作时施加的锁模式
读锁：共享锁
写锁：独占锁。排它锁
锁粒度：
表锁：table lock
锁定了整张表
行锁：row lock
锁定了需要的行
粒度越小，开销越大，但并发型越好：
粒度越大，开销越小，但并发型越差：
锁的实现位置：
MYSQL锁：可以使用显示锁
存储引擎锁：自动进行的（隐式锁）：
显示锁（表级锁）：
LOCK TABLES
UNLOCK TABLES
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY]
UNLOCK TABLES
锁类型：READ|WRITE
InnoDB存储引擎也支持另外一种显示锁(锁定挑选的部分行，行级锁）：
SELECT ... LOCK IN SHARE MODE;
SELECT .. FOR UPDATE;
事务：Transaction
事务就是一组原子性的查询语句，也即将多个查询当作一个独立的工作单元。
ACID测试：能满足ACID测试就表示其支持事务，或兼容事务。
A：Atomicity，原子性
C：Consistency，一致性
I：Isolation，隔离性，一个事务的所有修改操作在提交之前对其它事务是不可见的
D：Durability，持久性，一旦事务得到提交，其所作的修改会永久有效
隔离级别：
READ UNCOMMITTED（读未提交）
脏读，不可重读
READ COMMITTED（读提交）
不可重读
REPEATABLE READ（可重读）
SERIALIZABLE（可串行化）
强制事务的串行执行避免幻读：
跟事务相关的常用命令
mysql> START TRANSACTION
mysql> COMMIT
mysql>ROLLBACK
mysql>SAVEPOINT identifier
mysql>ROLLBACK [WORK] TO [SAVEPOINT] identifier
如果没有显式启动事务，每个语句都会当作一个独立的事务，其执行完成后会被自动提交：
关闭自动提交，请记住启动事务，手动进行提交：
查看MySQL的事务隔离级别
mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation';
1、找张classes的表
mysql> select * from classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.01 sec)
2、使用读锁把该表锁定
mysql> LOCK TABLES classes READ;
Query OK, 0 rows affected (0.03 sec)
3、自己做查询
mysql> select * from classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.04 sec)
4、使用另一个终端插入数据时发现已经阻塞
mysql> INSERT INTO classes VALUES(10,'TaoYuan',23);
^CCtrl-C -- sending "KILL QUERY 6" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
5、使用unlock tables 解锁
mysql> UNLOCK tables ;
Query OK, 0 rows affected (0.01 sec)
6、插入数据立即成功
mysql> INSERT INTO classes VALUES(10,'TaoYuan',23);
Query OK, 1 row affected (0.00 sec)
7、对表classes施加一个写锁
mysql> LOCK TABLES classes WRITE;
Query OK, 0 rows affected (0.01 sec)
8、使用另一个终端读取该表时，阻塞（写锁具有排它性）
mysql> select * from classes;
9、再对表解锁
mysql> UNLOCK tables ;
Query OK, 0 rows affected (0.00 sec)
10、查询语句立即执行成功
mysql> select * from classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
| 10 | TaoYuan | 23 |
+---------+----------------+----------+
10 rows in set (1 min 20.68 sec)
11、存储引擎是Innodb的存储引擎，可以使用行级锁，锁定前几行，
这里先修改表的存储引擎
mysql> ALTER TABLE classes ENGINE 'InnoDB';
Query OK, 10 rows affected (0.10 sec)
Records: 10 Duplicates: 0 Warnings: 0
查看表的存储引擎，已看到存储引擎换成InnoDB
mysql> SHOW TABLE STATUS LIKE 'classes'\G
*************************** 1. row ***************************
Name: classes
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10
Avg_row_length: 1638
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2017-03-23 05:48:29
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
把classes表的前三行锁定
mysql> SELECT * FROM classes WHERE ClassID <= 3 LOCK IN SHARE MODE;
+---------+---------------+----------+
| CLASSID | Class | NumOfStu |
+---------+---------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
+---------+---------------+----------+
3 rows in set (0.00 sec)
12、启动一事务，测试回滚的机制
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
显示默认打开的数据库
mysql> select database();
+------------+
| database() |
+------------+
| success |
+------------+
1 row in set (0.01 sec)
查看表classes
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
| 10 | TaoYuan | 23 |
+---------+----------------+----------+
10 rows in set (0.01 sec)
删除第10条记录
mysql> DELETE FROM classes WHERE CLASSID=10;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.01 sec)
执行下回滚，删除的第10条记录又还原在表中
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
| 10 | TaoYuan | 23 |
+---------+----------------+----------+
10 rows in set (0.02 sec)
13、启动事务，测试提交的机制
启动事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
查看表里的所有数据
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
| 10 | TaoYuan | 23 |
+---------+----------------+----------+
10 rows in set (0.00 sec)
删除第十行
mysql> DELETE FROM classes WHERE CLASSID=10;
Query OK, 1 row affected (0.00 sec)
提交
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
再查看classes表
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.08 sec)
使用回滚
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
再查看classes表，已滚不回去了
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.00 sec)
14、启动事务，测试SAVEPOINT机制
启动事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
删除第四行
mysql> DELETE FROM classes WHERE CLASSID=4;
Query OK, 1 row affected (0.00 sec)
保存为a
mysql> SAVEPOINT a;
Query OK, 0 rows affected (0.00 sec)
删除第9行
mysql> DELETE FROM classes WHERE CLASSID=9;
Query OK, 1 row affected (0.00 sec)
保存为b
mysql> SAVEPOINT b;
Query OK, 0 rows affected (0.00 sec)
删除第一行，不保存
mysql> DELETE FROM classes WHERE CLASSID=1;
Query OK, 1 row affected (0.00 sec)
查看表classes
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
+---------+----------------+----------+
6 rows in set (0.00 sec)
回滚到b的位置
mysql> ROLLBACK TO b;
Query OK, 0 rows affected (0.01 sec)
查看表classes
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
+---------+----------------+----------+
7 rows in set (0.00 sec)
回滚到a的位置
mysql> ROLLBACK TO a;
Query OK, 0 rows affected (0.00 sec)
查看classes表
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
单独执行ROLLBACK，会滚回含有所有记录的情况
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)
再查看表classes
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.00 sec)
15、查看autocommit的情况，自动提交功能是开的
mysql> SHOW GLOBAL VARIABLES LIKE '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | ON |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
3 rows in set (0.02 sec)
16、查看mysql的事务隔离级别
mysql> SHOW GLOBAL VARIABLES LIKE '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
17、测试下事务隔离，读未提交
设置隔离为READ-UNCOMMITTED
mysql> SET GLOBAL tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE '%iso%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
启动两个会话，测试下隔离和脏补（能够看到别人尚未提交的修改）的效果
session1:
启动事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
选择classes表
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.00 sec)
删除classes表中的第三行
mysql> DELETE FROM classes WHERE CLASSID=3;
Query OK, 1 row affected (0.00 sec)
选择classes表
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
session2:
启动事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
查看到会话1删除的第三行数据已经隔离，每有显示
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
session1:
查看删除完第三行数据的classes表
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
回滚
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)
session2：
查看classes表，第三行的数据又在了
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| CLASSID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Liangshan | 22 |
+---------+----------------+----------+
9 rows in set (0.00 sec)
阅读(1283) | 评论(0) | 转发(0) |
