创建数据库luowei
mysql> CREATE DATABASE luowei;
使用数据库
mysql> use luowei
创建表study
mysql> CREATE TABLE study ( ID bigint(20) NOT NULL AUTO_INCREMENT UNIQUE, Name varchar(255) NOT NULL, Age int(10),Gender enum('F','M') DEFAULT 'M');
查询表study;
mysql> SELECT * FROM study;
查看表的结构：
mysql> DESC study;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | NO | | NULL | |
| Age | int(10) | YES | | NULL | |
| Gender | enum('F','M') | YES | | M | |
+--------+---------------+------+-----+---------+----------------+
插入数据；
mysql> INSERT INTO study (Name) VALUE ('luowei'); //单个插入
mysql> INSERT INTO study (Name) VALUE ('Qi'),('Ro'),('RQ'); //批量插入
mysql> INSERT INTO study SET Name='Hua'; //使用SET插入
mysql> SELECT * FROM study;
+----+--------+------+--------+
| ID | Name | Age | Gender |
+----+--------+------+--------+
| 1 | luowei | NULL | M |
| 2 | Qi | NULL | M |
| 3 | Ro | NULL | M |
| 4 | RQ | NULL | M |
| 5 | Hua | NULL | M |
+----+--------+------+--------+
5 rows in set (0.00 sec)
这是插入数据后的一个简单的表的内容
修改表的数据
mysql> UPDATE study SET Gender='F' WHERE ID=2; //把ID=2的Gender改为F
mysql> UPDATE study SET Gender='F' WHERE ID IN (4,5);//批量处理
mysql> SELECT * FROM study;
+----+--------+------+--------+
| ID | Name | Age | Gender |
+----+--------+------+--------+
| 1 | luowei | NULL | M |
| 2 | Qi | NULL | F |
| 3 | Ro | NULL | M |
| 4 | RQ | NULL | F |
| 5 | Hua | NULL | F |
+----+--------+------+--------+
删除表中的数据
mysql> DELETE FROM study WHERE ID=5;//删除指定的行
mysql> SELECT * FROM study;
+----+--------+------+--------+
| ID | Name | Age | Gender |
+----+--------+------+--------+
| 1 | luowei | NULL | M |
| 2 | Qi | NULL | F |
| 3 | Ro | NULL | M |
| 4 | RQ | NULL | F |
+----+--------+------+--------+
但是这个时候如果我们在再次插入的话ID号会从原来删除的编号增加
mysql> SELECT LAST_INSERT_ID();//显示上次插入的是第几个
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 5 |
+------------------+
mysql> INSERT INTO study (Name) VALUE ('aQ');
mysql> SELECT * FROM study;
+----+--------+------+--------+
| ID | Name | Age | Gender |
+----+--------+------+--------+
| 1 | luowei | NULL | M |
| 2 | Qi | NULL | F |
| 3 | Ro | NULL | M |
| 4 | RQ | NULL | F |
| 6 | aQ | NULL | M |
+----+--------+------+--------+
看到了吧，不是依次增长的，而是有间隔，现在我们在删除了ID为6的行，然后插入设置他的ID为5，接着再自动插入；
mysql> DELETE FROM study WHERE ID=6;
mysql> INSERT INTO study SET ID=5,Name='pk';
mysql> INSERT INTO study (Name) VALUE ('jk');
mysql> SELECT * FROM study;
+----+--------+------+--------+
| ID | Name | Age | Gender |
+----+--------+------+--------+
| 1 | luowei | NULL | M |
| 2 | Qi | NULL | F |
| 3 | Ro | NULL | M |
| 4 | RQ | NULL | F |
| 5 | pk | NULL | M |
| 7 | jk | NULL | M |
+----+--------+------+--------+
可以看出还是不会按照我们的意思让他接着自动增长，这个时候我们可以通过设置，让他重新按照我们设置的为起点自动增长
mysql> DELETE FROM study WHERE ID=7;
mysql> ALTER TABLE study AUTO_INCREMENT=5;//设置自动增长的起点
mysql> INSERT INTO study (Name) VALUE ('jk');
mysql> INSERT INTO study (Name) VALUE ('OL');
mysql> SELECT * FROM study;
+----+--------+------+--------+
| ID