事务：
事务用于处理操作量大，复杂的数据。如公司中处理离职人员的资料，需要删除基本资料，和与他相关的邮箱，帐号等众多信息，所有这些数据库的操作就构成一个事务。
事务具有原子性、稳定性、隔离性和可靠性四个特点。
事务支持insert、update、delete语句
使用：
begin； #开启事务
insert、update、delete数据库操作
rollback； #回滚，事务终止
commit； #提交，事务终止
mysql> select * from students;+----+--------+--------+---------------------+----------+| id | name | gender | birthday | isDelete |+----+--------+--------+---------------------+----------+| 1 | 鲤鱼 | | 1991-01-01 00:00:00 | || 4 | mike | | NULL | |+----+--------+--------+---------------------+----------+2 rows in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.01 sec)mysql> delete from students where name='mike';Query OK, 1 row affected (0.03 sec)mysql> select * from students;+----+--------+--------+---------------------+----------+| id | name | gender | birthday | isDelete |+----+--------+--------+---------------------+----------+| 1 | 鲤鱼 | | 1991-01-01 00:00:00 | |+----+--------+--------+---------------------+----------+1 row in set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from students;+----+--------+--------+---------------------+----------+| id | name | gender | birthday | isDelete |+----+--------+--------+---------------------+----------+| 1 | 鲤鱼 | | 1991-01-01 00:00:00 | || 4 | mike | | NULL | |+----+--------+--------+---------------------+----------+2 rows in set (0.00 sec)
索引：
索引提高查询速度，分单列索引（一个索引对应一列）和组合索引（多列对应一个索引）
创建，查看索引：
create index indexname on tablename（索引字段(length)）； #在index前加unique就是唯一索引
show index from tablename；
mysql> select * from students;+----+--------+--------+---------------------+----------+| id | name | gender | birthday | isDelete |+----+--------+--------+---------------------+----------+| 1 | 鲤鱼 | | 1991-01-01 00:00:00 | || 4 | mike | | NULL | |+----+--------+--------+---------------------+----------+mysql> create index sex on students(name); #创建索引，把创建的索引命名为sexQuery OK, 0 rows affected (0.21 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc students;+----------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || gender | bit(1) | YES | | b'1' | || birthday | datetime | YES | | NULL | || isDelete | bit(1) | YES | | b'0' | |mysql> create index se on students(gender); #创建索引Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc students;+----------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || gender | bit(1) | YES | MUL | b'1' | || birthday | datetime | YES | | NULL | || isDelete | bit(1) | YES | | b'0' | |+----------+----------+------+-----+---------+----------------+mysql> show index from students; #查看索引，此时有三个索引。主键自动就是索引+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| students | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | || students | 1 | sex | 1 | name | A | 2 | NULL | NULL | | BTREE | | || students | 1 | se | 1 | gender | A | 2 | NULL | NULL | YES | BTREE | | |+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)
删除索引：
drop index se on students; #se是索引名称