目标:掌握创建数据表的方法。
掌握查看数据表结构的方法。
掌握修改数据表的方法。
掌握数据表的其他操作的方法。
掌握删除数据表的方法。
一、创建数据表
在创建数据库之后,接下来就要在数据库中创建数据表。所谓创建数据表,指的是在已经创建的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程,同时也是实施数据完整性(包括实体完整性,引用完整性和域完整性)约束的过程。
1、创建数据表的语法形式
CREATE TABLE <表名>
(
字段名1 数据类型[列级别约束条件][默认值],
字段名2 数据类型[列级别约束条件][默认值],
......
字段名n-1 数据类型[列级别约束条件][默认值],
字段名n 数据类型[列级别约束条件][默认值],
[表级别约束条件]
)
1)创建数据库create database mybase;
2)选择数据库 use mybase;
3)创建数据表db_1;创建语句如下:
Create table db_1
(
Id INT(11),
Name VARCHAR(25),
Sex BOOLEAN,
Salary FLOAT
);
执行过程如下图所示:
4)创建数据表db_1完成后,使用show tables;(不能缺少s)语句查看数据表是否创建成功,执行结果如图所示。
查询结果可以看到,数据表db_1创建成功,数据库mybase中已经有数据表db_1。
2、主键约束
主键又称主码,是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空。主键是能够唯一标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询的速度。主键分为两种类型,即单字段主键和多字段主键。
(1)单字段主键
主键由一个字段组成,设置主键的SQL语句格式分为两种情况。
1)、在定义列的同时指定主键,语法格式如下:
字段名 数据类型 primary key[默认值]
如创建数据表db_2,其主键为ID。
Create table db_2
(
Id INT(11) primary key,
Name VARCHAR(25),
Sex BOOLEAN,
Salary FLOAT
);
2) 、在定义完所有的列之后指定主键,语法格式如下:
[CONSTRAINT<约束名>] primary key [字段名]
如创建数据表db_3,其主键为ID。SQL语句如下:
Create table db_3
(
Id INT(11),
Name VARCHAR(25),
Sex BOOLEAN,
Salary FLOAT,
primary key(id)
);
上面两种方法执行的结果是一样的,都会在ID字段设置主键约束。
(2)多字段主键
主键由多个字段联合组成,语法格式如下:
Primary key[字段1,字段2,字段3,......,字段n]
如定义数据表db_4,假设表中没有主键ID,为了唯一确定一个员工,可以把name、sex联合起来作为主键。SQL语句如下:
Create table db_4
(
Id INT(11),
Name VARCHAR(25),
Sex BOOLEAN,
Salary FLOAT,
primary key(name,sex)
);
3、外键约束
外键用来在两个表数据之间建立连接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性,一个表的的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。外键的主要作用是保证数据引用的完整性,在定义外键后不允许删除在另一个表中具有关联关系的行。外键还保证数据的一致性、完整性。例如部门表tb_dept的主键ID,在员工表db_5中有一个键deptID与这个ID关联。
主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的那个表即是主表。
从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的那个表即是主表。
如定义数据表db_5,并且在该表中创建外键约束。
创建外键的语法格式如下:
[COMSTRAINT<外键名>]FOREIGN KEY 字段名1[,字段名2,......]
REFERENCES<主表名> 主键列1[,主键列2,......]
创建一个部门表tb_dept1,建表的SQL语句如下:
Create table tb_dept1
(
Id INT(11) primary key,
Name VARCHAR(22) NOT Null,
Location VARCHAR(50)
);
定义数据表db_5,让deptID字段作为外键关联到tb_dept1表的主键ID,SQL语句如下:
Create table db_5
(
Id INT(11) primary key,
Name VARCHAR(25),
deptId INT(11),
Salary FLOAT,
CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
以上语句执行成功后在db_5表上添加了名称为fk_emp_dept1的外键约束,外键名称为deptId,其依赖于tb_dept1表的主键id。
4、非空约束
非空约束指字段的值不能为空,使用此约束后,添加数据时没有指定值,数据库系统会报错,非空约束语法格式如下:
字段名 数据类型 NOT NULL
如定义数据表db_6,指定员工的性别不能为空,SQL语句如下:
Create table db_6
(
Id INT(11) primary key,
Name VARCHAR(25) ,
Sex BOOLEAN NOT NULL
);
执行后,数据库创建db_6数据表创建了一个sex字段,其插入的值不能为空。
5、唯一性约束
唯一性约束(Unique Constraint)要求某列唯一,允许为空,但只能出现一个空值。唯一性约束可以确保一列或者几列都不出现重复值。
在定义完列之后指定唯一性约束,语法格式如下:
字段名 数据类型 UNIQUE
如定义数据表db_7,指定name字段唯一,SQL语句如下:
Create table db_7
(
Id INT(11) primary key,
Name VARCHAR(22) UNIQUE,
Sex BOOLEAN NOT NULL,
Age INT(4)
);
6、默认约束
默认约束(Default Constraint)指定某列的默认值。例如,用户列表中的北京人比较多,就可以设置city字段的默认值为“北京”。如果插入一条新纪录时没有为这个字段赋值,那么系统会自动为这个字段赋值“北京”。
默认约束的语法格式如下:
字段名 数据类型 DEFAULT 默认值
如定义数据表db_8,指定员工的城市默认值为“北京”(北京需要用双引号括起来,主要和数据库的版本有关系),SQL语句如下:
Create table db_8
(
Id INT(11) primary key,
Name VARCHAR(25) UNIQUE,
City VARCHAR(20) DEFAULT “北京”
);
1、自增属性
在MySQL数据库设计中会遇到需要系统自动生成字段的主键值得情况。例如用户表中需要id字段自增,需要使用AUTO_INCREMENT关键字来实现。
属性值自动增加的语法格式如下:
字段名 数据类型 AUTO_INCREMENT
如定义数据表db_9,指定员工的编号自动增加,SQL语句如下:
Create table db_9
(
Id INT(11) primary key AUTO_INCREMENT,
Name VARCHAR(25) UNIQUE,
City VARCHAR(20)
);
以上语句执行后会创建名称为db_9的数据表,表中的id字段值在添加记录的时候回自动增加,id字段值默认从1开始,每次添加一条新纪录,改制自动加1。
二、查看数据表结构
1、查看数据表基本结构 使用DESCRIBE/DESC语句可以查看表字段信息,包括字段名、字段数据类型、是否为主键、是否有默认值等,其语法格式如下:
DESCRIBE/DESC <表名>
如查看db_2表的结构,SQL语句:DESCRIBE/DESC db_2;
上图中各个字段的含义分别如下:
NULL:表示该列是否可以存储NULL值。
Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE(唯一性约束)索引的一部分;MUL表示在该列表中某个给定值允许出现多次。
Default:表示该列是否有默认值,如果有,值是多少。
Extra:表示可以获取的与给定列有关的附加信息,如AUTO_INCREMENT等。
2、查看数据表详细结构
SHOW CREATE TABLE语句可以用来查看表的详细信息,语法格式如下:
SHOW CREATE TABLE<表名\G>;
如查看db_1表的详细信息,SQL语句如下:
SHOW CREATE TABLE db_1\G 执行结果如下图
三、修改数据表
常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。
1、修改表名
MySQL是通过ALTER TABLE语句来实现表名的修改的,具体语法格式如下:
ALTER TABLE <新表名>RENAME[TO]<旧表名>;
其中,To为可选参数,使用与否不影响结果。
例如将数据表db_1改名为tb_new.
操作步骤:
查看数据库中所有表:SHOW TABLES;
使用ALTER TABLE将db_1表名修改为tb_new,SQL语句如下:
ALTER TABLE db_1 RENAME tb_new;
操作结果执行完成后,在下图中对比结果发现数据表db_1已经没有了,而多出来了tb_new这个数据表。修改表名成功。
2、修改字段数据类型
修改字段数据类型就是把字段的数据类型转换成另一种数据类型。在MySQL中修改字段数据类型的语法格式如下:
ALTER TABLE <表名>MODIFY<字段名><数据类型>
其中,表名指要修改数据类型的字段所在表的名称,字段名指需要修改的字段,数据类型指修改后字段的新数据类型。
例如:将tb_new中name字段的数据类型由VARCHAR(25)修改成VARCHAR(28)。
修改数据类型的步骤如下:
1)、查看tb_new表的结构;
2)、执行修改语句ALTER TABLE tb_new MODIFY name VARCHAR(28);
3)、再次查看表结构。
执行结果如下图,tb_new表中name字段的数据类型已经修改为VARCHAR(28),修改成功。
3、修改字段名
在MySQL中修改表字段名的语法格式如下:
ALTER TABLE <表名> CHANGE<旧字段名><新字段名><新数据类型>;
其中,“旧字段名”指修改前的字段名;“新字段名”指修改后的字段名;“新数据类型”指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成原来的一样,但数据类型不能为空。
例如:将数据表tb_new中name字段的名称修改为newname。SQL语句如下:
ALTER TABLE tb_new CHANGE name newname VARCHAR(28);
从结果可以看出,name字段的名称已经修改为newname。
提示:由于不同类型的数据在计算机中存储的方式及长度并不相同,修改数据类型可能会影响到数据表中已有的数据记录,因此当数据库中已经有数据时不要轻易地修改数据类型。
4、添加字段
添加字段的语法格式如下:
ALTER TABLE<表名>ADD<新字段名><数据类型>
[约束条件][FIRST|AFTER 已存在字段名];
其中,新字段名为需要添加的字段名称;FIRST为可选参数,其作用是将添加的字段设置为表的第一个字段;AFTER为可选参数,其作用是将新添加的字段添加到已存在字段名指定的字段后面。
例如:在tb_new中添加一个字段city。SQL语句如下:
ALTER TABLE tb_new ADD city VARCHAR(20);
从结果可以看出添加了一个字段city,在默认情况下,该字段放在最后一列。
也可以在数据表的第一列添加字段。
例如:在数据表tb_new中添加一个INT类型的字段newID。SQL语句如下:
ALTER TABLE tb_new ADD newid INT(11) FIRST;
执行结果发现在表的第一列添加了一个名为newid的INT(11)类型的字段,结果如图所示:
例如:在数据表tb_new中的sex列后添加一个INT类型的字段ss.SQL语句如下:
ALTER TABLE tb_new ADD ss INT(11) AFTER sex;
从结果可以看出,tb_new表中添加了一个名称为ss的字段,其位置在指定的sex字段的后面,添加字段成功。
5、删除字段
删除字段是将数据表中的某个字段从表中移除,其语法格式如下:
ALTER TABLE <表名>DROP<字段名>;
其中,“字段名”指需要从表中删除的字段的名称。
例如:删除数据表tb_new中的SS字段,SQL语句如下:
ALTER TABLE tb_new DROP ss;
从结果可以看出,tb_new表中已经不存在名称为ss的字段,删除字段成功。
四、数据表的其他操作
1、修改字段排序
通常使用ALTER TABLE修改字段的排列顺序,修改字段排序的语法格式如下:
ALTER TABLE <表名>MODIFY<字段1><数据类型>FIRST|AFTER<字段2>;
例如:将数据表tb_new中的id字段修改为表的第一个字段,SQL语句如下:
ALTER TABLE tb_new MODIFY id INT(11) FIRST;
也可以修改字段到指定列之后。
例如:将数据表tb_new中的newname字段移动到salary字段后面,SQL语句如下:
ALTER TABLE tb_new MODIFY newname VARCHAR(28) AFTER salary;
2、更改表的存储引擎
更改表的存储引擎的语法格式如下:
ALTER TABLE<表名> ENGINE=<更改后的存储引擎名>;
例如:将db_2数据表的存储引擎修改为MyISAM。SQL语句如下:
ALTER TABLE db_2 ENGINE=MyISAM;
操作步骤:1、先查看db_2表当前的存储引擎(ENGINE=InnoDB);
2、修改存储引擎;
3、再次查看db_2表的存储引擎(ENGINE=MyISAM);
3、删除表的外键约束
对于数据库中定义的外键,如果不在需要,可以将其删除。外键一旦删除,就会解除主表和从表间的关联关系。MySQL中删除外键的语法格式如下:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
其中,“外键约束名”指在定义表时CONSTRAINT关键字后面的参数。
例如:删除数据表db_5中的外键约束fk_emp_dept1.
首先查看db_5的结构,SQL语句如下:
Show CREATE TABLE db_5\G
删除数据表db_5的外键fk_emp_dept1,SQL语句如下:
ALTER TABLE db_5 DROP FOREIGN KEY fk_emp_dept1;
可以看到,db_5表中已经不存在FOREIGN KEY,原有的名称为fk_emp_dept1的外键约束删除成功。
五、删除数据表
对于不在需要的数据表,可以将其从数据库中删除。
1、删除没有被关联的表
在MySQL中使用DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表,语法格式如下:
DROP TABLE [IF EXISTS] 表1,表2,......表n;(n表示要删除的表名称)
例如:删除db_2数据表,SQL语句如下:
DROP TABLE db_2;(删除后数据列表中已经不存在名称为db_2的数据表,删除操作成功)
2、删除被其他表关联的主表
在数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败,原因是直接删除将破坏表的完整性。如果必须删除,可以先删除与它关联的子表,再删除父表,只是这样同时删除了两个表中的数据。有些情况下需要保留子表,这时如果要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表。
在数据库中创建两个关联表,首先创建tb_1表,SQL语句如下:
Create table tb_1
(
Id INT(11) primary key,
Name VARCHAR(22)
);
接下来创建tb_2表,SQL语句如下:
Create table tb_2
(
Id INT(11) primary key,
Name VARCHAR(25),
wwid INT(11),
CONSTRAINT fk_tb_dt FOREIGN KEY (wwid) REFERENCES tb_1(id)
);
使用SHOW CREATE TABLE命令查看tb_2表的外键约束,SQL语句如下:
SHOW CREATE TABLE tb_2\G(执行结果如下图)
从结果可以看到,在数据表tb_2上创建了一个名称为fk_tb_dt的外键约束。
例如:删除父表tb_1.
首先直接删除父表tb_1,输入删除语句如下:
DROP TABLE tb_1;
执行结果如图所示,可以看到,当存在外键约束时主键不能被直接删除。
接下来删除关联子表tb_2的外键约束,SQL语句如下:
ALTER TABLE tb_2 DROP FOREIGN KEY fk_tb_dt;
该语句执行后将取消tb_1和tb_2表之间的关联关系,此时可以输入删除语句,将原来的父表tb_1删除,SQL语句如下:
DROP TABLE tb_1;
最后通过SHOW TABLES查看数据表列表,结果如图所示。
可以看到,数据表列表中已经不存在名称为tb_1的表。