MySQL必知必会 -- 创建和操纵表

创建表

MariaDB [test]> create table dance(user_id int  not null AUTO_INCREMENT,user_name char(30) not null,user_addr char(30) null,PRIMARY KEY (user_id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

MariaDB [test]> desc dance;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| user_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| user_name | char(30) | NO   |     | NULL    |                |
| user_addr | char(30) | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
3 rows in set (0.07 sec)

主键通过 PRIMARY KEY 指定，引擎通过 ENGINE 指定。

而且上面的字段有的是 NULL ，有的是 NOT NULL，允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。

AUTO_INCREMENT 说明本列所有的值只能是唯一的，在为本列指定值为null 或者 0 的时候会自动生成下一个 +1 的编号：

MariaDB [test]> select * from dance;
+---------+-----------+-----------+
| user_id | user_name | user_addr |
+---------+-----------+-----------+
|       1 | cao       | xian      |
|      13 | ao        | NULL      |
+---------+-----------+-----------+
2 rows in set (0.00 sec)

MariaDB [test]> insert into dance value (null,'yuan',null);
Query OK, 1 row affected (0.04 sec)

MariaDB [test]> select * from dance;
+---------+-----------+-----------+
| user_id | user_name | user_addr |
+---------+-----------+-----------+
|       1 | cao       | xian      |
|      13 | ao        | NULL      |
|      14 | yuan      | NULL      |
+---------+-----------+-----------+
3 rows in set (0.00 sec)

当上一个指定为12时，则下一个默认为12+1.

默认值的使用

MariaDB [test]> alter table dance add class int not null default 1;
Query OK, 3 rows affected (0.08 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> desc dance;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| user_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| user_name | char(30) | NO   |     | NULL    |                |
| user_addr | char(30) | YES  |     | NULL    |                |
| class     | int(11)  | NO   |     | 1       |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

通过default 指定默认的列。

使用：

MariaDB [test]> insert into dance (user_id,user_name,user_addr) value(null,'yan',null);
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> select * from dance;
+---------+-----------+-----------+-------+
| user_id | user_name | user_addr | class |
+---------+-----------+-----------+-------+
|       1 | cao       | xian      |     1 |
|      13 | ao        | NULL      |     1 |
|      14 | yuan      | NULL      |     1 |
|      15 | liu       | NULL      |     2 |
|      16 | yan       | NULL      |     1 |
+---------+-----------+-----------+-------+
5 rows in set (0.00 sec)

不用指定，默认为1。如果指定的话就会替换默认值。

数据库中的引擎

以下是几个mysql中的的引擎，他们各自有各自的特点：

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
• MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

要注意 外键 的使用不能跨引擎。

更新表 ALTER TABLE

MariaDB [test]> alter table drop class;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'drop class' at line 1
MariaDB [test]> alter table dance drop class;
Query OK, 5 rows affected (0.04 sec)               
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]> desc dance;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| user_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| user_name | char(30) | NO   |     | NULL    |                |
| user_addr | char(30) | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [test]> alter table dance add  class int not null default 1;
Query OK, 5 rows affected (0.05 sec)               
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]> desc dance;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| user_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| user_name | char(30) | NO   |     | NULL    |                |
| user_addr | char(30) | YES  |     | NULL    |                |
| class     | int(11)  | NO   |     | 1       |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

删除表、重命名表

MariaDB [test]> rename table dance to soft;
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| hour           |
| linux          |
| soft           |
| ss             |
+----------------+
4 rows in set (0.00 sec)

MariaDB [test]> drop table soft;
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| hour           |
| linux          |
| ss             |
+----------------+
3 rows in set (0.00 sec)