mysql事务、索引
mysl事务（innodb支持事务处理）在Mysql控制台使用事务来操作
mysql> begin； ＃开始一个事务
mysql> insert into a (a) values(555);
mysql>rollback; 回滚 ， 这样数据是不会写入的
当然如果上面的数据没问题，就输入commit提交命令就行；
mysql> use oldboydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> begin; （开启事务）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex|
+----+----------+---------------+------+
|1 | ZhanYang | 2018-06-20 | NULL |
|4 | LiuJia | 2018-05-30 | NULL |
|8 | JiaLiu | 2018-06-20 | NULL |
|9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
+----+----------+---------------+------+
8 rows in set (0.00 sec)
mysql> insert into student (name,register_date,sex) values("Alex","2016-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> mysql> insert into student (name,register_date,sex) values("Jack","2016-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex|
+----+----------+---------------+------+
|1 | ZhanYang | 2018-06-20 | NULL |
|4 | LiuJia | 2018-05-30 | NULL |
|8 | JiaLiu | 2018-06-20 | NULL |
|9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
| 14 | Alex | 2016-05-21 | M |
| 15 | Jack | 2016-05-21 | M |
+----+----------+---------------+------+
10 rows in set (0.00 sec)
mysql> rollback;（回滚）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex|
+----+----------+---------------+------+
|1 | ZhanYang | 2018-06-20 | NULL |
|4 | LiuJia | 2018-05-30 | NULL |
|8 | JiaLiu | 2018-06-20 | NULL |
|9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
+----+----------+---------------+------+
8 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student (name,register_date,sex) values("ZhanXing","2018-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (name,register_date,sex) values("XingYan","2018-05-21","M");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex|
+----+----------+---------------+------+
|1 | ZhanYang | 2018-06-20 | NULL |
|4 | LiuJia | 2018-05-30 | NULL |
|8 | JiaLiu | 2018-06-20 | NULL |
|9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
| 16 | ZhanXing | 2018-05-21 | M |
| 17 | XingYan| 2018-05-21 | M |
+----+----------+---------------+------+
10 rows in set (0.00 sec)
mysql> commit;（从内存写入磁盘）
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex|
+----+----------+---------------+------+
|1 | ZhanYang | 2018-06-20 | NULL |
|4 | LiuJia | 2018-05-30 | NULL |
|8 | JiaLiu | 2018-06-20 | NULL |
|9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
| 16 | ZhanXing | 2018-05-21 | M |
| 17 | XingYan| 2018-05-21 | M |
+----+----------+---------------+------+
10 rows in set (0.00 sec)
MySQL索引：
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构
ALTER mytable ADD INDEX ON (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX (username(length))
);
删除索引的语法
DROP INDEX ON mytable;
mysql> mysql> show index from student; （查看索引）
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY| 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> create index index_name on student (name(32));（创建索引）
Query OK, 0 rows affected (0.05 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
| student | 1 | index_name | 1 | name | A | 10 | NULL | NULL | | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index index_name on student;（删除索引）
Query OK, 0 rows affected (0.04 sec)
Records: 0Duplicates: 0Warnings: 0
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY| 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
唯一索引：
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。
如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER mytable ADD UNIQUE ON (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE (username(length))
);
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
页:
[1]