mysql-表的基本操作(三)

创建数据表

• 数据表命名原则 ：

1. 长度最好不超过30个字符；

2. 多个单词之间使用下划线“_”分隔，不允许有空格；

3. 不允许为MySQL关键字；

4. 不允许与同一数据库中的其他数据表同名。

5. 建表（带约束）

create table goods (
	id int (11) primary key auto_increment,
	type varchar (30) not null,
	name varchar (30) unique,
	price decimal (7, 2) unsigned,
	num int (11) default 0,
	add_time datetime
);

2. 建表（不带约束）

create table good (
	id int (11),
	type varchar (30),
	name varchar (30),
	price decimal (7, 2),
	num int (11),
	add_time datetime
);

• 建约束：

1. 创建primary key约束

-- 格式：
alter table 表名 add primary key (主键字段名);

alter table good add primary key (id);

2. 自增约束

-- 格式：
alter table 表名 modify 字段名 字段类型 约束...;

alter table good modify id int(11) auto_increment;

3. 不为空约束

alter table good modify type VARCHAR(30) not null;

4. 唯一约束

-- 格式：
alter table 表名 add unique (字段名);

alter table good add constraint ui_gname unique (name);(增加约束名) 

alter table good add unique (name);

5. 无符号约束

-- 格式：
alter table 表名 modify 字段名 字段类型 约束...;

alter table good modify price DECIMAL (7, 2) unsigned;

6. 默认约束

-- 格式：
alter table 表名 alter 字段名
set default 默认内容;

alter table good alter num
set default 0;

-- 创建orders表
create table orders (
	o_id int (11) primary key,
	add_time datetime,
	goods_id int (11)
);

7. 设置外键约束：

alter table orders add constraint fk_gid foreign key (goods_id) references good (id);

alter table orders add foreign key (goods_id) references goods (id);

8. 设置表的存储引擎：

create table category (
	id int (11) primary key,
	name varchar (30),
	p_id int (11)
) engine = myisam;

9. 范围约束

create table 表名(
id int check(id>0 and id<100),
sex char(2) check(sex='女' or sex='男')
);

10. 检查约束

-- 格式：
alter table 表名 add [constraint 约束名] check(字段范围);

查看表结构

1. 查看表基本结构：

-- 格式：
desc 表名;
describe 表名;

-- 例子：
describe goods;

2. 查看建表语句：

-- 格式：
show create table 表名;

-- 例子：
show create table goods;

使用图形化工具查看表结构

1. 查看表基本结构
   启动Navicat for MySQL并连接MySQL后，双击打开localhost_3306连接，
   然后双击选择db_shop数据库，系统会在右侧“对象”选项卡中打开数据表列表，
   选中要查看的goods表，单击“设计表”按钮，即可查看数据表结构。

2. 查看建表语句
   进入数据表列表页面后，右击要查看的goods表，在弹出的快捷菜单中选择“对象信息”，
   表下方会出现“常规”和“DDL”选项卡，单击“DDL”切换到该选项卡，即可查看建表语句。

修改表

1. 修改表名：

-- 格式
alter table 旧表名 rename 新表名;

-- 实例：
alter table goods rename tb_goods;

2. 修改字段数据类型：

-- 格式
alter table 表名 modify 字段名 数据类型;

-- 实例：
alter table tb_goods modify name char(20);

3. 修改字段名：

-- 格式
alter table 表名 change 旧字段名 新字段名 数据类型;

-- 实例：
alter table tb_goods change name g_name varchar(30);

4. 添加字段

-- 格式
alter table 表名 add 新字段名 数据类型;

-- 实例：
alter table tb_goods add picture varchar(255);

-- 在表的第一列添加字段
alter table tb_goods add state tinyint(4) first;

-- 在表的指定列之后添加字段
alter table tb_goods add intro text after num;

查询表

desc tb_goods;
describe tb_goods;

5. 删除字段

-- 格式
alter table 表名 drop 删除字段名;

-- 实例：
alter table tb_goods drop picture;

6. 修改字段顺序

alter table tb_goods modify num int(11) after id;
alter table 表名 modify 字段名 数据类型 first; ——放置开头
alter table 表名 modify 字段名 数据类型 after 字段名; ——放置某字段后

7. 修改存储引擎

alter table 表名 engine=e_name;

alter table category engine=innodb;

在修改存储引擎之前，往往需要首先查看表当前的存储引擎，语法形式如下：

show create table 表名;

show create table category;

删除表

1.删除表

-- 格式：
DROP TABLE 表1,表2...;
-- 注意：
-- 如果有关联：先删从表，再删主表。

注意：
如果删除的数据表不存在，系统会提示错误信息并中断执行，加上“IF EXISTS”参数后，
系统会在执行删除命令之前判断表是否存在，如果表不存在，命令仍可以顺利执行，
但系统会提示警告。

-- 格式：
drop table if exists 表1,表2,...;

-- 实例：
drop table if exists category,tb_goods;

2. 删除外键

-- 格式：
alter table 表名 drop foreign key 外键名;

-- 实例：
alter table orders drop foreign key fk_gid;

• 案例：

以学生表为例：
CREATE TABLE student(
	student_id INT(11),
	student_name VARCHAR(50),
	student_gentle CHAR(2),
	student_age INT(11),
	student_dept VARCHAR(50),
	grade_id INT(11)
);

1. 把student_age字段放置开头

alter table student modify student_age int(11) first;

2. 添加字段student_address，数据类型自定义

alter table student add student_address INT(11);

3. 修改字段名student_gentle，改为student_sex

alter table student change student_gentle student_sex char(2);

4. 修改字段student_name的数据类型，改为varchar(20)

alter table student modify student_name varchar(20);

5. 删除字段student_address

alter table student drop student_address;

6. 把grade_id放置student_id的后面

alter table student modify grade_id int(11) after student_id;

7. 查看学生表的建表语句

show create table student;

8. 修改表名，student改为students

alter table student rename students;

9. 查看学生表基本结构

describe students2;

10. 删除学生表

drop table students2;