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

短命女 2022-08-05 07:50 240阅读 0赞

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

> （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.csdn.net/u010889616/article/details/48286713][http_blog.csdn.net_u010889616_article_details_48286713]  
> 
> 
>   
> 
> 
>   
>   
>

[http_blog.csdn.net_u010889616_article_details_48286713]: http://blog.csdn.net/u010889616/article/details/48286713