数据表的基本操作(二)

原创 2015年07月08日 16:55:15

使用默认约束:指定某列的默认值

语法为:字段名 数据类型 default 默认值

mysql> create table tb7
    -> (
    ->     id int(11) primary key,
    ->     number int(11) default 888
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql>

设定number默认值为888,假如修改了就以修改后的值为新值

设置表的属性值自动增加,通常用在主键上面,当插入一条新的数据后,系统自动生成主键值。

默认的,在mysql中auto_increment的初始值为1,每新增加一条记录,字段值就是自动加上1,一个表只能有一个自动使用auto_increment约束,而且该字段必须要是主键的一部分

语法规则:字段名 数据类型 auto_increment

mysql> create table tb8
    -> (
    ->    id int(11) primary key auto_increment,
    ->    name varchar(23)
    -> );
Query OK, 0 rows affected (0.07 sec)

查看数据表的结构

DESCRIBE/DESC语句可以查看表的字段信息:字段名、字段数据类型、是否为主键、是否有默认值等等

语法规则:DESCRIBE 表名; DESC 表名;

mysql> desc tb8;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(23) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

其中key表示该列是否已经编制索引。pri表示该列是表主键的一部分,unique表示该列是unique索引的一部分
extra表示可以获取的与给定列有关的附加信息,例如auto_increment

查看表的详细结构使用show create table 不仅可以查看表创建时候的详细语句,而且还可以查看存储引擎和字符编码

语法:show create table <表名\G>;

下面的是不含有\G

mysql> show create table tb8;
+-------+----------------------------------------------------------------
-------------------------------------------------------------------------
----+
| Table | Create Table

    |
+-------+----------------------------------------------------------------
-------------------------------------------------------------------------
----+
| tb8   | CREATE TABLE `tb8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------
-------------------------------------------------------------------------
----+
1 row in set (0.01 sec)

含有\G的情况

mysql> show create table tb8\G;
*************************** 1. row ***************************
       Table: tb8
Create Table: CREATE TABLE `tb8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

修改数据表

常用的修改操作:表名、字段数据类型或者字段名、增加删除字段、修改字段的排列顺序、存储引擎、外键约束

修改表名:alter <旧表名> rename [TO] <新表名>; [TO]为可选

mysql> alter table tb8 rename tb9;
Query OK, 0 rows affected (0.12 sec)

修改名称后的表和修改名称前的表的结构必然是相同的

修改字段的数据类型

语法规则:alter table <表名> modify <字段名> <数据类型>

mysql> desc tb8
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(23) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> alter table tb8 modify name int(12);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb8
    -> ;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | int(12) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

修改字段名

语法规则:alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
这里要注意:即使你不更改数据类型,你也要写上原来的,不可以为空

mysql> desc tb8
    -> ;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | int(12) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> alter table tb8 change name number int(12);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb8;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| number | int(12) | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

当把旧字段和新字段设计为相同时,那么这时候是不会更改字段名字的,因此可以用这种方法来更改字段的数据类型

添加字段

语法规则:alter table <表名> add <新字段名> <数据类型> [约束条件] [first | after 已存在的字段名];

sql语句默认将新添加的字段设置为数据表的最后列

mysql> desc tb8;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| number | int(12) | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> alter table tb8 add name varchar(25);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb8;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| number | int(12)     | YES  |     | NULL    |                |
| name   | varchar(25) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

也可以在上述语句数据类型的后面添加上约束条件not null // first //after 字段名等等

删除字段

语法规则:alter table <表名> drop <字段名>

mysql> desc tb8;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| number | int(12)     | YES  |     | NULL    |                |
| name   | varchar(25) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> alter table tb8 drop number;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb8;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

修改字段的排列位置

语法规则: alter table <表名> modify <字段1> <数据类型> first | after <字段2>

mysql> desc tb8;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> alter table tb8 modify id int(11) after name;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(25) | YES  |     | NULL    |       |
| id    | int(11)     | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

更改表的存储引擎

mysql中主要的存储引擎有:MyISAM、InnoDB、MEMORY、BDB、FEDERATED等,可以用show engines来查看系统支持的存储引擎

tb8的表修改前的详细信息:

mysql> show create table tb8\G;
*************************** 1. row ***************************
       Table: tb8
Create Table: CREATE TABLE `tb8` (
  `name` varchar(25) DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

tb8的表修改后的详细信息,修改为Myisam:

mysql> alter table tb8 engine=myisam;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb8\G;
*************************** 1. row ***************************
       Table: tb8
Create Table: CREATE TABLE `tb8` (
  `name` varchar(25) DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

删除表的外键约束

语法规则: alter table <表名> drop foreign key <外键约束名>

mysql> alter table tb3 drop foreign key empid;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除数据表

语法:drop table [if exit] 表1,表2,表3 。。 。。;

mysql> drop table tb7,tb8;
Query OK, 0 rows affected (0.08 sec)

这里要注意的是:数据表之间存在外键关联的情况下,如果直接删除父表,那么会显示失败,原因是会破坏表的参照完整性。因此要先删除子表,再删除父表。假如需要保留子表,那么可以先把关联表的外键的约束条件取消后再去删除父表

下面是详细过程:

创建有关联的表

mysql> create table tb7
    -> (
    ->     id int(11) primary key
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table tb8
    -> (
    ->    id int(11) primary key,
    ->    dp int(11),
    ->    constraint de foreign key(dp) references tb7(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

删除父表

mysql> drop table tb7;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails

解除外键约束后删除成功

mysql> alter table tb8 drop foreign key de;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table tb7;
Query OK, 0 rows affected (0.06 sec)

注意
并不是每一个表中都要有一个主键的,一般在多个表之间进行连接操作的时候才需要用到

并不是每个表都可以随意选择存储引擎的(外键约束不可以跨引擎使用,为了确保数据的参照完整性)

默认情况下,auto_increment是从1开始增加的,可以修改开始值的。

版权声明:可以随意转载

相关文章推荐

MySQL数据表的基本操作二:表结构修改

MySQL数据表的基本操作-表结构查看,表结构修改,增加字段,删除字段,更改字段类型...

MySQL数据表的基本操作二:表结构查看、修改与表操作

一、查看数据表结构 1) 查看表基本结构语句 DESCRIBE 语法:DESCRIBE 表名; 示例: ? 1 2 3 4 5 6 7 8 ...

数据表的基本操作之创建数据表

数据表是数据库中最重要、最基本的操作对象,是数据存储的基本单位。数据表被定义为列的集合,数据在表中是按照行和列的格式来存储的。每一行代表一条唯一的记录,每一列代表记录中的一个域。 本模块将介绍数据表...

MySQL数据表基本操作

1.打开指定数据库(LZ之前创建了数据库smart );语法:USE db_name; 例如: USE smart; 2.展示当前打开的数据库;语法:SELECT DATABASE(); 例如...

后台学习之路(一) mysql(数据表基本操作以及约束初识)

如果大家在安装mysql过程中出现问题,请看另一篇文章(适用于Windows 版本5.7.16)http://blog.csdn.net/sinat_35670989/article/details...

MongoDB数据表基本操作

MongoDB数据表基本操作 查看全部数据表 > use ChatRoom switched to db ChatRoom > show collections Account ...

MongoDB数据表基本操作

查看全部数据表 > use ChatRoom switched to db ChatRoom > show collections Account Chat system.indexes ...

第 2 篇 核心技术 第 5 章 数据表的基本操作

在数据库中,数据表是数据库中最重要、最基本的的操作对象,是数据存储的基本单位。数据表被定义为列的集合,数据在表中是按照行和列的格式来存储的。每一行代表一条唯一的记录,每一列代表记录中的一个域。5.1创...

数据表的基本操作

一。数据完整性有哪几种?各自作用是什么? 1.实体完整性 能够唯一标识表中的每一条记录 实现方式:主键、唯一键、auto_increment属性 2.域完整性 表中特定列数据的有效性,确保不会输入无效...

数据表的基本操作(一)

数据表是数据库中最重要、最基本的操作对象,是数据存储的基本单位。
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据表的基本操作(二)
举报原因:
原因补充:

(最多只允许输入30个字)