MySQL 数据表的基本操作

Question

1、创建数据表

use test_db;
create table tb_emp1
(
	id int(11),
	name varchar(15),
	deptID int(11),
	salary 	float
);

使用下面语句查看此数据库存在的表

show tables;

1)、主键约束

主键，又称主码，是表中一列或多列的组合。主键约束〈Primary Key Constraint) 要求主键列的数据唯一，并且不允许为空 != null。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系， 并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型: 单字段主键和多字段联合主键。

• 单字段主键；
• 在定义完所有列之后定义主键；
• 多字段联合主键；

单字段约束：

create table tb_emp2
(
	id int(11) primary key,
	name varchar(15),
	deptID int(11),
	salary 	float
);

后面约束：

create table tb_emp3
(
	id int(11),
	name varchar(15),
	deptID int(11),
	salary 	float,
	primary key(id)
);

联合约束：假设没有主键 id ，可以通过 name 和 deptID 来确定一个唯一的员工。

create table tb_emp4
(
	id int(11),
	name varchar(15),
	deptID int(11),
	salary 	float,
	primary key(name,deptID)
);

2)、外键约束

• 外键用来在两个表的数据之间建立链接， 它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
• 外键 : 首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保证数据引用的完整性， 定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是 id ，在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。

有关主表和从表：

• 主表（父表) : 对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
• 从表（子表) : 对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

需要注意：

• 子表的外键必须要关联父表的主键；
• 相关联的数据类型必须匹配；
• 先删子表，再删父表；

下面的例子 tb_emp5(员工表) 中的 deptID 关联部门表中的 ID(主键)：

//父表
create table tb_dept1
(
	id int(11)primary key,
	name varchar(22) not null,
	location varchar(50)
)

//子表
create table tb_emp5
(
	id int(11) primary key,
	name varchar(25),
	deptID int(11),
	salary float,
	constraint fk_emp5_dept foreign key(deptID) references tb_dept1(id)
)

3)、非空约束

非空约束指定的字段不能为空，如果添加数据的时候没有指定值，会报错。

create table tb_emp6
(
	id int(11) primary key,
	name varchar(15) not null,
	deptID int(11),
	salary 	float
);

4)、唯一性约束

• 唯一性要求该列唯一；
• 允许为空，但只能出现一个空值；
• 唯一性可以确保一列或几列不出现重复值；

create table tb_dept2
(
	id int(11)primary key,
	name varchar(22) unique,
	location varchar(50)
);

create table tb_dept3
(
	id int(11)primary key,
	name varchar(22),
	location varchar(50),
	constraint N_uq unique(name)  #N_uq 是约束名
);

注意 UNIQUE 和主键约束( PRIMARY KEY ) 的区别:

• 一个表中可以有多个字段声明为 UNIQUE ，但只能有一个 PRIMARY KEY 声明；
• 声明为 PRIMAY KEY 的列不允许有空值，但是声明为 UNIQUE 的字段允许空值 (NULL) 的存在。

5)、默认约束

指定了默认约束之后，如果没有指定值，就用默认的。

create table tb_emp7
(
	id int(11) primary key,
	name varchar(15) not null,
	deptID int(11) default 111,
	salary 	float
); 

6)、设置表的属性自加

• 在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加 AUTO_INCREMENT 关键字来实现。
• 默认的，在 MySQL 中 AUTO _INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须为主键的一部分。
• AUTO_INCREMENT 约束的字段可以是任何整数类型 (TINYINT、SMALLIN、INT、BIGINT 等) 。

create table tb_emp8
(
	id int(11) primary key auto_increment,
	name varchar(15) not null,
	deptID int(11),
	salary 	float
); 

7)、查看表的结构

desc 可以查看表的字段名，数据类型，是否为主键，是否默认值。

desc tb_emp8;

效果如图

查看表的详细结构，可以看储存引擎，和字符编码

show create table tb_emp8;

2、修改数据表

1)、修改表名

将表 tb_dept3 改为 tb_deptment3

alter table tb_dept3 rename tb_deptment3;

查看数据库中的表

show tables;

修改表名不会改变结构， desc 前后结果一样。

2)、修改字段的数据类型

# 修改表字段的数据类型,把 name 列的数据类型改为 varchar(33)
alter table tb_dept1 modify name varchar(33);

3)、修改字段名

# 修改表的字段名,不改数据类型 将 tb_dept1 中的 location 字段改成 loc
alter table tb_dept1 change location loc varchar(50);

# 修改表的字段名,并且改变数据类型, 同时改变数据类型
alter table tb_dept1 change loc location varchar(60);

change 也可以只改变数据类型，但是一般不要轻易改变数据类型。

4)、添加字段

有三种添加方式：

• 默认在最后面添加；
• 在第一个位置添加 first ；
• 和指定的位置添加 after ；

# 添加字段（默认在最后面添加)
alter table tb_dept1 add managerID int(10);

# 添加字段（默认在最后面添加)(非空约束)
alter table tb_dept1 add column1 int(10) not null;

# 添加字段（在第一个位置添加)
alter table tb_dept1 add column2 int(10) first;

# 添加字段（在指定位置后面添加)
alter table tb_dept1 add column3 int(10) after name;

5)、删除字段

# 删除字段, 删除 tb_dept1 的 column3 字段
alter table tb_dept1 drop column3;

6)、修改字段的排列位置

# 修改字段的排列位置（改到第一个位置)
alter table tb_dept1 modify column1 int(10) first;
# 修改字段的位置为指定的位置
alter table tb_dept1 modify column2 int(10) after name;

7)、更改表的储存引擎

# 查看数据表的定义
show create table tb_deptment3;
# 更改数据表的引擎
alter table tb_deptment3 engine = MyISAM;

8)、删除表的外键约束

create table tb_emp9
(
	id int(11)primary key,
	deptID int(11),
	name varchar(25),
	salary float,
	constraint fk_emp9_dept foreign key(deptID) references tb_dept1(id)
)

# 删除外键约束
alter table tb_emp9 drop foreign key fk_emp9_dept;

3、删除数据表

# 删除表
drop table if exists tb_emp9;

注意注意: 删除有关联的数据表的父表的时候，先删除外键再删除父表

4、综合案例小结

create database company;
use company;
create table offices
(
	officeCode int(10) primary key not null unique,
	city varchar(50) not null,
	address varchar(50),
	country varchar(50) not null,
	postalCode varchar(15) unique
)

create table employees
(
	employeeNumber int(11) primary key not null unique auto_increment,
	lastName varchar(50) not null,
	firstName varchar(50) not null,
	mobile varchar(25) unique,
	officeCode int(10) not null,
	jobTitle varchar(50) not null,
	birth datetime not null,
	note varchar(255),
	sex varchar(5)
)

show tables;
desc employees;

#将 mobile 字段修改到 officeCode 后面
alter table employees modify mobile varchar(25) after officeCode;
#将 birth 的字段名改为 employee_birth
alter table employees change birth employee_birth datetime;
#修改 sex 字段为 char(1) 类型，非空约束
alter table employees modify sex char(1) not null;
#删除字段 note 
alter table employees drop note;
#增加字段名
alter table employees add favoriate_activity varchar(100);

#为 employee 增加一个外键
alter table employees add constraint fk_em_off foreign key(officeCode) references offices(officeCode);

#删除表的外键约束
alter table employees drop foreign key fk_em_off;

#更改 employee 的数据引擎
alter table employees engine = MyISAM;

#更改 employee 的表名
alter table employees rename employees_info;