4.4 MySQL表的操作

MySQL表的操作

(1) 创建表

创建数据表可使用CREATE TABLE命令。

语法格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name

[ ( [ column_definition ], … | [ index_definition ] ) ]

[table_option] [select_statement];

注意：在同一个数据库中，表名不能有重名。[例]

USE mytestdb;
　　CREATE TABLE IF NOT EXISTS goods(
　　id INT COMMENT ‘编号’,
　　NAME VARCHAR(32) COMMENT ‘商品名’,
　　price INT COMMENT ‘价格’,
　　description VARCHAR(255) COMMENT ‘商品描述’,
　　PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=gbk;

CREATE TABLE IF NOT EXISTS test.goods(
id INT COMMENT ‘编号’ PRIMARY KEY,
NAME VARCHAR(32) COMMENT ‘商品名’,
price INT COMMENT ‘价格’,
description VARCHAR(255) COMMENT ‘商品描述’
) ENGINE INNODB DEFAULT CHARSET gbk;

(2) 查看表

1)显示表的名称

语法格式： SHOW TABLES ;

2)显示表的结构

查看表结构有简单查询和详细查询，可使用DESCRIBE/DESC语句和SHOW CREATE TABLE语句

语法格式： DESCRIBE 表名; 或者 DESC 表名; 或者 SHOW CREATE TABLE 表名; [例]

DESCRIBE test.goods;
DESC test.goods;
SHOW CREATE TABLE test.goods;

(3) 修改表

语法格式：

alter [ ignore] table table_name alter_specification [,

alter_specification]

add [column] column_definition [ first | after col_name] //添加字段

| alter [column] col_name {set default literal | drop default}//修改字段

| change [column] old_col_name column_definition [first| after col_name] //重命名字段

| modify [column] column_definition [first | after col_name] //修改字段

| drop [column] col_name //删除列

| rename [to|as] new_table_name//对表重命名

| order by col_name//按字段排序

| convert to character set character_name [collate collation_name]

//将字段集转化为二进制

| [default] character set charset_name [collate collation_name]//修改字符集

[例] USE mytestdb;
#将goods表的名称修改为my_goods
RENAME TABLE goods TO my_goods;
或 ALTER TABLE goods RENAME AS my_goods;
或 ALTER TABLE goods RENAME TO my_goods;

#将my_goods数据表的字符集改为gb2312，校对规则为gb2312_bin
ALTER TABLE mytestdb.my_goods CHARSET = gb2312 COLLATE =gb2312_bin;
或 ALTER TABLE mytestdb.my_goods DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_bin;
#将my_goods数据表中description的字段名修改为des
ALTER TABLE mytestdb.my_goods CHANGE description des VARCHAR(255);
#查看字段名的修改情况
DESC mytestdb.my_goods;

#修改my_goods数据表中des字段的数据类型，将VARCHAR (255)修改为CHAR(255)
ALTER TABLE mytestdb.my_goods MODIFY des CHAR(255);
#查看字段类型的修改情况
DESC mytestdb.my_goods des;

#将my_goods表中最后一个字段des移动到name字段后
ALTER TABLE mytestdb.my_goods MODIFY des VARCHAR(255) AFTER NAME;
#查看字段位置的修改结果
DESC mytestdb.my_goods;
#在my_goods数据表中字段name后新增一个num字段，表示商品的数量
ALTER TABLE mytestdb.my_goods ADD num INT AFTER NAME;
#查看新增的字段
DESC mytestdb.my_goods;
#删除my_goods数据表中num字段
ALTER TABLE mytestdb.my_goods DROP num;
#看删除num字段后数据表中的字段
DESC mytestdb.my_goods;
(4) 复制表

语法格式：

create [temporary] table [if not exists] table_name

[ like old_table_name ]| [AS (select_statement)]; [例]

CREATE TABLE IF NOT EXISTS mytestdb.new_goods LIKE mytestdb.my_goods;
CREATE TABLE IF NOT EXISTS mytestdb.my_new_goods AS (SELECT * FROM mytestdb.my_goods);

(5) 删除表

删除表可以用DROP TABLE 命令

语法格式：drop table [if exists] table_name [,table_name] …; [例]

DROP TABLE IF EXISTS mytestdb.new_goods,mytestdb.my_new_goods;

(6) 表管理中的注意事项

1）关于空值（NULL）的说明

空值通常用于表示未知、不可用或将在以后添加的数据，切不可将它与数字0或字符类型的空字符混为一谈。

2）关于列的标志(IDENTITY)属性

任何表都可以创建一个包含系统所生成序号值的标志列。该序号值唯一标志表中的一列，且可以作为键值。

3）关于列类型的隐含改变

在MySQL中，系统会隐含地改变在CREATE TABEL语句或ALTER TBALE语句中所指定的列类型。

长度小于4的VARCHAR类型会被改变为CHAR类型。