目录
一、表的基本概念
表是包含数据库中所有数据的数据库对象。其中每一行代表一条唯一的记录,每一列代表记录中的一个字段。
表中的数据库对象包含列、索引和触发器
- 列:也称属性列,在具体创建表时,必须指定列的名字和数据类型。
- 索引:是指根据指定的数据库表建立起来的顺序,提供了快速访问数据的途径且可监督表的数据,使其索引指向的列中的数据不重复。
- 触发器:是指用户定义的事务命令的集合,当对一个表中的数据进行插入、更新或删除时这组命令就会自动执行,可以用来确保数据的完整性和安全性。
1、创建表
语法形式如下:
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
列名称n 数据类型
);
【实例】本例演示在数据库test中创建t_dept表,表中有3个字段,类型分别为varchar和int。
mysql> #选择数据库#
mysql> use test
Database changed
mysql> #创建表格#
mysql> CREATE TABLE t_dept(
-> deptno INT,
-> dname VARCHAR(20),
-> loc VARCHAR(40)
-> );
2、查看表
(1)DESCRIBE语句查看表定义
语法形式如下:
DESCRIBE table_name;
【实例】查看t_dept表结构
mysql> DESCRIBE t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(1)SHOW CREATE TABLE语句查看表定义
语法形式如下:
SHOW CREATE TABLE table_name;
【实例】查看t_dept表的详细信息
mysql> SHOW CREATE TABLE t_dept \G
*************************** 1. row ***************************
Table: t_dept
Create Table: CREATE TABLE `t_dept` (
`deptno` int(11) DEFAULT NULL,
`dname` varchar(20) DEFAULT NULL,
`loc` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
3、删除表
语法形式如下:
DROP TABLE table_name;
【实例】删除t_test表
mysql> #删除表#
mysql> DROP TABLE t_test;
Query OK, 0 rows affected (0.07 sec)
4、修改表
(1)修改表名
语法形式如下:
ALTER TABLE old_table_name RENAME new_table_name;
【实例】修改数据库test中t_dept表的名称为tab_dept,修改后查看表名是否修改成功;
mysql>#选择数据库#
mysql> USE test
Database changed
mysql>#修改表名字#
mysql> ALTER TABLE t_dept RENAME tab_dept;
Query OK, 0 rows affected (0.09 sec)
mysql>#查看表#
mysql> DESC t_dept;
ERROR 1146 (42S02): Table 'test.t_dept' doesn't exist
mysql> DESC tab_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(2)增加字段
a.在表的最后一个位置增加字段
语法形式如下:
ALTER TABLE table_name ADD 属性名 属性类型;
【实例】为数据库test中t_dept表增加一个名为descri,类型为VARCHAR的字段,所增加字段在表中所有字段的最后一个位置;
mysql> #增加字段#
mysql> ALTER TABLE t_dept ADD descri VARCHAR(20);
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> #查看表#
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
| descri | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
b.在表的第一个位置增加字段
语法形式如下:
ALTER TABLE table_name ADD 属性名 属性类型 FIRST;
【实例】为数据库test中t_dept表增加一个名为descri,类型为VARCHAR的字段,所增加字段在表中所有字段的第一个位置;
mysql> #增加字段#
mysql> ALTER TABLE t_dept ADD descri VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> #查看表#
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| descri | varchar(20) | YES | | NULL | |
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
c.在表的指定字段之后增加字段
语法形式如下:
ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名;
【实例】为数据库test中t_dept表增加一个名为descri,类型为VARCHAR的字段,所增加字段在表中deptno字段之后;
mysql> #增加字段#
mysql> ALTER TABLE t_dept ADD descri VARCHAR(20) AFTER deptno;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> #查看表#
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| descri | varchar(20) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(3)删除字段
语法形式如下:
ALTER TABLE table_name DROP 属性名;
【实例】删除数据库test中t_dept表一个名为descri的字段
mysql> ALTER TABLE t_dept DROP descri;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
(4)修改字段
- 修改字段的数据类型
语法形式如下:
ALTER TABLE table_name MODIFY 属性名 数据类型;
【实例】在数据库test中t_dept表中,将deptno字段的类型由原来的int(11)修改为varchar(20)
mysql> ALTER TABLE t_dept MODIFY deptno VARCHAR(20);
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | varchar(20) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- 修改字段的名字
语法形式如下:
ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型;
【实例】在数据库test中t_dept表中,修改字段loc的名字为location
mysql> ALTER TABLE t_dept CHANGE loc location VARCHAR(40);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | varchar(20) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| location | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- 同时修改字段的名字和数据类型
语法形式如下:
ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型;
【实例】在数据库test中t_dept表中,修改字段loc的名字为location,并将数据类型由原来的varchar(40)修改为varchar(20)
mysql> ALTER TABLE t_dept CHANGE loc location VARCHAR(20);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | varchar(20) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| location | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- 修改字段的顺序
语法形式如下:
ALTER TBALE table_name MODIFY 属性名1 数据类型 FTRSE|AFTER 属性名2;
上述语句中,“属性名1”参数表示所要调整顺序的字段名,“FIRST”参数表示将字段调整到表的第一个位置,“ALTER 属性名2”参数表示将字段调整到属性名2字段位置之后。
【实例】在数据库test中t_dept表中,将字段loc调整到表的第一个位置
mysql> ALTER TABLE t_dept MODIFY loc VARCHAR(40) FIRST;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
【实例】在数据库test中t_dept表中,将字段deptno调整到字段dname之后
mysql> ALTER TABLE t_dept MODIFY deptno INT AFTER dname;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
5、操作表的约束
(1)设置非空约束(NOT NULL,NK)
语法形式如下:
CREATE TABLE table_name(
属性名 数据类型 NOT NULL,
…………
)
【实例】在数据库test中创建t_dept表中,通过SQL语句NOT NULL设置字段deptno为NK约束;
mysql> CREATE TABLE t_dept(
-> deptno INT(20) NOT NULL,
-> dname VARCHAR(20),
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected (0.23 sec)
(2)设置字段的默认值(DEFAULT)
语法形式如下:
CREATE TABLE table_name(
属性名 数据类型 DEFAULT 默认值,
…………
);
【实例】在数据库test中创建t_dept表中,通过SQL语句DEFAULT设置字段dname的默认值为liming;
mysql> CREATE TABLE t_dept(
-> deptno INT(20) NOT NULL,
-> dname VARCHAR(20) DEFAULT 'liming',
-> loc VARCHAR(20)
-> );
Query OK, 0 rows affected (0.20 sec)
(3)设置唯一约束(UNIQUE,UK)
语法形式如下:
CREATE TABLE table_name(
属性名 数据类型 UNIQUE,
…………
);
【实例】在数据库test中创建t_dept表中,通过SQL语句UNIQUE设置字段dname为UK约束;
mysql> CREATE TABLE t_dept(
-> deptno INT(20) NOT NULL,
-> dname VARCHAR(20) UNIQUE,
-> loc VARCHAR(20)
-> );
Query OK, 0 rows affected (0.23 sec)
- 如果想给字段dname上的UK约束设置一个名字,可以执行SQL语句CONSTRAINT设置唯一约束的标识符,在为约束设置标识符时候,推荐使用“约束缩写_字段名”;
mysql> CREATE TABLE t_dept(
-> deptno INT,
-> dname VARCHAR(20),
-> loc VARCHAR(20),
-> CONSTRAINT uk_name UNIQUE(dname)
-> );
Query OK, 0 rows affected (0.22 sec)
(4)设置主键约束(PRIMARY KEY,PK)
在数据库中之所以设置主键,是为了便于数据库管理系统快速地查找表中的记录。在具体设置主键约束时,必须要满足主键字段的值是唯一】非空的,由于主键可以是单一字段,也可以是多个字段,因此分为单子段主键和多字段主键。
- 单子段主键
语法形式如下:
CREATE TABLE table_name(
属性名 数据类型 PRIMARY KEY,
…………
);
【实例】在数据库test中创建t_dept表中,通过SQL语句PRIMARY KEY设置字段deptno为PK约束;
mysql> CREATE TABLE t_dept(
-> deptno INT PRIMARY KEY,
-> dname VARCHAR(20),
-> loc VARCHAR(20)
-> );
Query OK, 0 rows affected (0.20 sec)
如果想给字段deptno上的PK约束设置一个名字,可以执行SQL语句CONSTRAINT设置唯一约束的标识符,在为约束设置标识符时候,推荐使用“约束缩写_字段名”;
mysql> CREATE TABLE t_dept(
-> deptno INT,
-> dname VARCHAR(20),
-> loc VARCHAR(20),
-> CONSTRAINT pk_deptno PRIMARY KEY(deptno)
-> );
Query OK, 0 rows affected (0.20 sec)
- 多字段主键
语法形式如下:
CREATE TABLE table_name(
属性名 数据类型,
……
[CONSTRAINT 约束名] PRIMARY KEY(属性名,属性名……)
);
【实例】在数据库test中创建t_dept表中,通过SQL语句PRIMARY KEY设置字段deptno和dname为PK约束;
mysql> CREATE TABLE t_dept(
-> deptno INT,
-> dname VARCHAR(20),
-> loc VARCHAR(20),
-> CONSTRAINT pk_dname_deptno PRIMARY KEY(dname,deptno)
-> );
Query OK, 0 rows affected (0.23 sec)
(5)设置字段值自动增加(AUTO_INCREAMENT)
AUTO_INCREAMENT是MySQL唯一扩展的完整性约束,当为数据库表中插入新纪录时,字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREAMENT约束时,一个数据库表中只能有一个字段使用该约束,该字段的数据类型必须是整数类型。由于设置AUTO_INCREAMENT约束后的字段会生成唯一的ID,所以该字段也经常会设置为PK主键。
语法形式如下:
CREATE TABLE table_name(
属性名 数据类型 AUTO_INCREMENT,
……
);
【实例】在数据库test中创建t_dept表中,通过SQL语句PRIMARY KEY和AUTO_INCREMENT设置字段deptno和dname为PK约束和自动增加;
mysql> CREATE TABLE t_dept(
-> deptno INT PRIMARY KEY AUTO_INCREMENT,
-> dname VARCHAR(20),
-> loc VARCHAR(20)
-> );
Query OK, 0 rows affected (0.20 sec)
(6)设置外键约束(FOREIGN KEY,FK)
设置外键的两个表之间会具有父子关系,即子表中某个字段的取值范围由父表做决定。
在具体设置FK约束时,设置FK约束的字段必须依赖于数据库中已经存在的父表的主键,同时外键可以为NULL。
语法形式如下:
CREATE TABLE table_name(
属性名1 数据类型,
属性名n 数据类型,
CONSTRAINT 外键约束名 FOREIGN KEY(属性名1)
REFERENCES 表名(属性名2)
);
【实例】在数据库test中创建部门表(t_dept)和雇员表(t_emloyee),通设置雇员表字段deptno为外键约束,表示一种部门中有多个雇员的关系;
mysql> CREATE TABLE t_dept(
-> deptno INT PRIMARY KEY,
-> dname VARCHAR(20),
-> loc VARCHAR(20)
-> );
Query OK, 0 rows affected (0.23 sec)
mysql> CREATE TABLE t_employee(
-> empno INT PRIMARY KEY,
-> ename VARCHAR(20),
-> job VARCHAR(40),
-> MSG INT,
-> Hiredate DATE,
-> sal DOUBLE(10,2),
-> comm DOUBLE(10,2),
-> deptno INT,
-> CONSTRAINT fk_deptno FOREIGN KEY(deptno)
-> REFERENCES t_dept(deptno)
-> );
Query OK, 0 rows affected (0.22 sec)