mysql数据库常用的基本SQL语句--数据库表操作

一、增、删、改、查数据库表字段的结构

（1）选择要操作的数据库

mysql> USE my_db;

（2）创建数据库表stu_tbl

mysql>CREATE TABLE stu_tbl(
mysql>name VARCHAR(20) NOT NULL PRIMARY KEY, 
mysql>id INT NOT NULL, 
mysql>score TINYINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.81 sec)

（3）查看my_db数据库有多少数据库表

mysql> SHOW TABLES;
+-----------------+
| Tables_in_my_db |
+-----------------+
| stu_tbl         |
+-----------------+
1 row in set (0.00 sec)

（4）查看stu_tbl数据库表的字段结构

mysql> DESC stu_tbl;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| id    | int(11)             | NO   |     | NULL    |       |
| score | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

（5）删除整个数据库表

mysql> DROP TABLE  stu_tbl;
Query OK, 0 rows affected (0.59 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

（5）删除数据库表的一个字段，删除id字段

mysql> ALTER TABLE stu_tbl DROP COLUMN id;
Query OK, 0 rows affected (1.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC stu_tbl;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| score | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

（6）给数据库表stu_tbl添加一个love字段

FIRST说明，把该字段设为数据库表的第一个字段。

mysql> ALTER TABLE stu_tbl ADD love VARCHAR(20) NOT NULL FIRST; 
Query OK, 0 rows affected (1.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC stu_tbl;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| love  | varchar(20)         | NO   |     | NULL    |       |
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| id    | int(11)             | NO   |     | NULL    |       |
| score | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

AFTER说明，把该字段放到id字段后面

mysql> ALTER TABLE stu_tbl ADD love VARCHAR(20) NOT NULL AFTER id;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC stu_tbl;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| id    | int(11)             | NO   |     | NULL    |       |
| love  | varchar(20)         | NO   |     | NULL    |       |
| score | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

（7）把数据库表stu_tbl中score字段名字修改为mathscore，位置放在name后面。

mysql> ALTER TABLE stu_tbl CHANGE score mathscore TINYINT UNSIGNED NOT NULL AFTER name;
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC stu_tbl;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| name      | varchar(20)         | NO   | PRI | NULL    |       |
| mathscore | tinyint(3) unsigned | NO   |     | NULL    |       |
| id        | int(11)             | NO   |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

（8）在数据库表stu_tbl建好之后，修改唯一键。

把id字段，设置为唯一键

mysql> ALTER TABLE stu_tbl ADD UNIQUE (id);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC stu_tbl;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| id    | int(11)             | NO   | UNI | NULL    |       |
| score | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

把id字段的唯一键性质取消，使用INDEX

mysql> ALTER TABLE stu_tbl DROP INDEX id;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC stu_tbl;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| id    | int(11)             | NO   |     | NULL    |       |
| score | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

（9）把数据库表stu_tbl更改名字为stu1_tbl;

mysql> RENAME TABLE stu_tbl TO stu1_tbl;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_my_db |
+-----------------+
| stu1_tbl        |
+-----------------+
1 row in set (0.00 sec)

这一篇博客介绍了有关数据库结构的语句操作，下面介绍有关数据库数据的语句操作。

下一篇博客地址:http://blog.youkuaiyun.com/u010889616/article/details/48286713