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

来源：互联网发布：mac使用github pages 编辑：程序博客网时间：2024/06/10 14:40

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

（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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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.csdn.net/u010889616/article/details/48286713

0 0