mysql 基本操作３

１，事物

就是类似于缓存，一个事物就是缓存区，一个事物中的操作暂时存进表中，最后提交．如果最后不提交，则没有存进表中．

事物的操作有　insert,delete,update．

事物是以begin 开始

rollback 撤回，也就是不提交

commit 事物确认，也就是提交

mysql> select * from stu;
+------+-----------------------+------------+
| name | addr                  | birthday   |
+------+-----------------------+------------+
| 丁   | 海南省海口市          | 1995-08-12 |
| 丙   | 江苏省南京市          | 1997-12-22 |
| 乙   | 北京市东城区          | 1997-09-22 |
| 己   | 河北省石家庄市        | 1996-09-02 |
| 戊   | 四川省成都市          | 1996-09-02 |
| 甲   | 浙江省杭州市          | 1998-02-22 |
+------+-----------------------+------------+
6 rows in set (0.00 sec)

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

mysql> insert into stu (name,addr,birthday) values('喵喵喵','喵星','2019.01.01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+-----------+-----------------------+------------+
| name      | addr                  | birthday   |
+-----------+-----------------------+------------+
| 丁        | 海南省海口市          | 1995-08-12 |
| 丙        | 江苏省南京市          | 1997-12-22 |
| 乙        | 北京市东城区          | 1997-09-22 |
| 喵喵喵    | 喵星                  | 2019-01-01 |
| 己        | 河北省石家庄市        | 1996-09-02 |
| 戊        | 四川省成都市          | 1996-09-02 |
| 甲        | 浙江省杭州市          | 1998-02-22 |
+-----------+-----------------------+------------+
7 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from stu;
+------+-----------------------+------------+
| name | addr                  | birthday   |
+------+-----------------------+------------+
| 丁   | 海南省海口市          | 1995-08-12 |
| 丙   | 江苏省南京市          | 1997-12-22 |
| 乙   | 北京市东城区          | 1997-09-22 |
| 己   | 河北省石家庄市        | 1996-09-02 |
| 戊   | 四川省成都市          | 1996-09-02 |
| 甲   | 浙江省杭州市          | 1998-02-22 |
+------+-----------------------+------------+
6 rows in set (0.00 sec)
＃ｒollback之后发现并没有写入到表class 中

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

mysql> insert into stu (name,addr ,birthday) values ('汪汪汪','汪星','2019.03.22');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from stu;
+-----------+-----------------------+------------+
| name      | addr                  | birthday   |
+-----------+-----------------------+------------+
| 丁        | 海南省海口市          | 1995-08-12 |
| 丙        | 江苏省南京市          | 1997-12-22 |
| 乙        | 北京市东城区          | 1997-09-22 |
| 己        | 河北省石家庄市        | 1996-09-02 |
| 戊        | 四川省成都市          | 1996-09-02 |
| 汪汪汪    | 汪星                  | 2019-03-22 |
| 甲        | 浙江省杭州市          | 1998-02-22 |
+-----------+-----------------------+------------+
7 rows in set (0.00 sec)
＃可以看到commit以后就完全插入了．

2,索引

索引是为了查询大数据设置的，能大大提高大量数据下的查询效率．

主键就是索引．

索引创建的三种方式．

2.1，创建表的时候直接创建

mysql> create table language(id int auto_increment,                                                                                                -> lang char(32) not null,
    -> region char(32) not null,
    -> primary key (id),
    -> index index_name (lang(32))); #插入索引名 (索引字段（字段长))                                                                                                          Query OK, 0 rows affected (0.42 sec)

mysql> show index from language;   #查看表中索引
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| language |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| language |          1 | index_name |            1 | lang        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2.2，删除索引

mysql> drop index index_name on language;   #删除索引　索引名称
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from language;                                                                                                               +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| language |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

2.3，直接创建索引

create index index_name on table_name (field(length));

mysql> create index index_name on language(lang(32));
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from language;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| language |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| language |          1 | index_name |            1 | lang        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2.4，通过修改列表结构创建索引

alter table table_name  add index index_name (field_name(length));

mysql> show index from language;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| language |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| language |          1 | index_name |            1 | lang        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)