一. 创建表的语法形式
数据表属于数据库,在创建数据表之前,应该使用语句“ USE <数据库名> ”指定操作在哪个数据库中进行。
创建数据库的语句为 CREATE TABLE , 语法规则为:
CREATE TABLE <表明>
(
字段名1, 数据类型 [ 列级别约束条件 ] [ 默认值 ] ,
字段名2, 数据类型 [ 列级别约束条件 ] [ 默认值 ] ,
…
[ 表级别约束条件]
);
使用 CREATE TABLE 创建表时,必须指定以下信息:
(1) 要创建的表的名称,不区分大小写,不能使用SQL 语言中的关键字,如 DROP、ALTER、INSERT 等。
(2) 数据表中每一个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开。
例:创建员工表 tb_emp1
tb_temp1表的结构
字段名称 | 数据类型 | 备注 |
id | INT(11) | 员工编号 |
name | VARCHAR(25) | 员工名称 |
deptId | INT(11) | 所在部门编号 |
salary | FLOAT | 工资 |
首先创建数据库,SQL语句如下:
CREATE DATABASE test_db;
选择创建表的数据库:
USE test_db;
创建tb_emp1 表:
mysql> CREATE TABLE tb_emp1
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
Query OK, 0 rows affected (1.53 sec)
使用 SHOW TABLES 语句查看数据表是否创建成功:
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1 |
+-------------------+
1 row in set (0.00 sec)
二. 使用主键约束
主键,又称主码,是表中一列或多列的组合。主键约束要求主键列的数据要唯一,并且不允许为空。主键能唯一地标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快查询数据库的速度。主键和记录之间一 一对应。
主键分为; 单字段主键和多字段联合主键。
1.单字段主键
主键由一个字段组成,SQL语句的格式分为两种
(1) 在定义列的同时指定主键,语法规则为:
字段名 数据类型 PRIMARY KEY [默认值]
【例】定义数据表 tb_emp3. 其主键为 id,使用的 SQL 语句为:
mysql> CREATE TABLE tb_emp3
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
Query OK, 0 rows affected (0.27 sec)
(2) 在定义完所有列之后指定主键。
[ CONSTRAINT < 约束名 > ] PRIMARY KEY [ 字段名 ]
【例】 定义数据表 tb_emp2. 其主键为 id,使用的 SQL 语句为:
mysql> CREATE TABLE tb_emp2
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (1.26 sec)
2. 多字段联合主键
主键由多个字段联合组成,语法规则为:
PRIMARY KEY [字段1,字段2,… ,字段n ]
【例】定义数据表 tb_emp4,假设表中间没有主键id ,为了唯一确定一个员工,可以把 name、deptId 联合起来作为主键,SQL 语句如下:
mysql> CREATE TABLE tb_emp4
-> (
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> PRIMARY KEY(name,deptId)
-> );
Query OK, 0 rows affected (1.01 sec)
三. 使用外键约束
外键用来在两个表的数据之间建立连接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。
外键:首先它是表中的一个字段,它可以不是本表的主键,但对应另外一个表的主键。外键的主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行。
主表(父表):对于具有关联关系的两个表而言,相关联字段中主键所在的表为主表。
从表(子表):对于具有关联关系的两个表而言,相关联字段中外键所在的表为主表。
创建外键的语法规则:
[ CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ , 字段名2,... ]
REFERENCES <主表名> 主键列1 [ , 主键列2 , ... ]
外键名:定义的外键约束的名称,一个表中不能有相同名称的外键。
字段名:子表需要添加外键约束的字段列。
主表名:被外键所依赖的表的名称。
主键列:主表中定义的主键列,或者列组合。
【例】定义数据表 tb_emp5,并在tb_emp5 表上创建外键约束。
创建一个部门表 tb_dept1,表的结构如下:
字段名称 | 数据类型 | 备注 |
id | INT(11) | 部门编号 |
name | VARCHAR(22) | 部门名称 |
location | VARCHAR(50) | 部门位置 |
使用的SQL语句如下:
mysql> create table tb_dept1(
-> id int(11) primary key,
-> name varchar(22) not null,
-> location varchar(50)
-> );
Query OK, 0 rows affected (0.37 sec)
定义数据表 tb_emp5, 让它的键 deptId 作为外键关联到 tb_dept1 表的主键 id ,SQL语句如下:
mysql> create table tb_emp5(
-> id int(11) primary key,
-> name varchar(25),
-> deptId int(11),
-> salary float,
-> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
-> );
Query OK, 0 rows affected (1.31 sec)
子表的外键必须关联父表的主键,且关联字段的数据类型必须匹配。
四. 使用非空约束
非空约束是指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统会报错。
非空约束的语法规则如下:
字段名 数据类型 NOT NULL
【例】定义数据表tb_emp6, 指定员工的名称不能为空,SQL 语句如下:
mysql> create table tb_emp6(
-> id int(11) primary key,
-> name varchar(25) not null ,
-> deptId int(11),
-> salary float
-> );
Query OK, 0 rows affected (0.49 sec)
执行后,在tb_emp6 中创建一个name 字段,其插入值不能为空。
五. 使用唯一性约束
唯一性约束要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
非空约束的语法规则如下:
1. 定义完列之后直接指定唯一约束,语法规则如下:
字段名 数据类型 UNIQUE
【例】 定义数据表 tb_dept2, 指定部门的名称唯一,SQL语句如下:
mysql> create table tb_dept2(
-> id int(11) primary key,
-> name varchar(22) unique,
-> location varchar(50)
-> );
Query OK, 0 rows affected (0.35 sec)
2. 在定义完所有列之后指定唯一约束,语法规则如下:
[ CONTRAINT <约束名> ] UNIQUE (<字段名>)
【例】 定义数据表 tb_dept3, 指定部门的名称唯一,SQL语句如下:
mysql> create table tb_dept3(
-> id int(11) primary key,
-> name varchar(22) ,
-> location varchar(50),
-> constraint sth unique (name)
-> );
Query OK, 0 rows affected (0.34 sec)
六. 使用默认约束
默认约束可以指定某列的默认值。
默认值约束的语法规则为:
字段名 数据类型 DEFAULT 默认值
【例】 定义数据表 tb_emp7,指定员工的部门编号默认为 1111,SQL语句如下:
mysql> create table tb_emp7(
-> id int(11) primary key,
-> name varchar(22) not null,
-> deptId int(11) default 1111,
-> salary float
-> );
Query OK, 0 rows affected (0.36 sec)
七. 设置表的属性值自动增加
在数据车应用中,经常希望在每次插入新记录时,系统会自动生成字段的主键值,这可以通过为表主键添加 AUTO_INCREMENT 关键字来实现。默认的,在MySQL 中AUTO_INCREMENT 的初始值为1,每新增一条记录,字段值自动加1,一个表只能有一个字段使用 AUTO_INCREMENT 约束,且该宇段必须为主键的一部分。AUTO_INCREMENT约束的学段可以是任何整数类(TINYINT 、SMALLINT、INT、BIGINT等)。
语法规则如下:字段名 数据类型 AUTO_INCREMENT
【例4】定义数据表tb_emp8,指定员工的编号自动递增,使用的SQL 语句如下:
mysql> create table tb_emp8(
-> id int(11) primary key auto_increment,
-> name varchar(22) not null,
-> deptId int(11) ,
-> salary float
-> );
执行以下插入语句:
mysql> insert into tb_emp8 (name , salary)
-> values ('Lucy' , 1000) , ('Lura' ,1200) , ('Kevin' , 1500);
使用SELECT 语句查看记录:
mysql> select * from tb_emp8;
+----+-------+--------+--------+
| id | name | deptId | salary |
+----+-------+--------+--------+
| 1 | Lucy | NULL | 1000 |
| 2 | Lura | NULL | 1200 |
| 3 | Kevin | NULL | 1500 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)
八. 删除数据表
1. 删除没有被关联的表
使用 DROP TABLE 语句可以一次删除一个或多个没有被其它表关联的数据表。
语法格式如下:
DROP TABLE [ IF EXISTS ] 表1, 表2, ... , 表n;
参数 IF EXISTS 用于在删除前判断删除的表是否存在,加上该参数后,在删除表的时候,如果表不存在,SQL 语句可以顺利执行,但是会发出警告( warning )。
2. 删除被其他表关联的主表
数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败。原因是直接删除,将破坏表的参照完整性。如果必须要删除,可以先删除与它关联的子表,再删除父表,只是这样会同时删除两个表中的数据。但有的情况下可能要保留子表,这时如果单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表了。
【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版