4.1 创建数据表
在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据 表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数 据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整 性和域完整性等）约束的过程。 数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。
创建数据表的语句为CREATE TABLE，语法规则如下：
CREATE TABLE TAB_NAME(
字段1 类型 约束 默认值,
字段2 类型 约束 默认值,
字段n 类型 约束 默认值
)使用CREATE TABLE创建表时，必须指定以下信息： （1） 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键 字，如DROP、ALTER、INSERT 等。 （2） 数据表中每一列（字段）的名称和数据类型，如果创建多列，就要用逗号隔开。 案例：创建员工表：employee
| 字段名 | 数据类型 | 备注 |
|---|---|---|
| emp_id | int | 员工编号 |
| emp_name | varchar(50) | 员工名字 |
| emp_sex | char(2) | 员工性别 |
| emp_salary | float | 员工工资 |
| emp_phone | varchar(50) | 员工电话 |
| emp_birth | date | 员工生日 |
| dept_id | int | 所在部门 |
创建表的sql语句如下：
CREATE TABLE employee(
emp_id INT,
emp_name VARCHAR(50),
emp_sex CHAR(2),
emp_salary FLOAT,
emp_phone VARCHAR(50),
emp_birth DATE,
dept_id INT
);
此时可以通过以下命令查看该库下的表
SHOW TABLES;
通过上图可以看到表中还有一些默认值，主键，非空等。这些都是属于表的约束。
4.2 表约束
1.数据完整性
数据的完整性是指数据的可靠性和准确性 1.实体完整性：实体的完整性强制表的标识符列或主键的完整性(通过索引,唯一约束,主键约束或标识列属性). 2.域完整性：限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则). 3.引用完整性：在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完整性确保键值在所有表中一致.这样的一致辞性要求不能引用不存在的值.如果一个键值更改了,那么在整个数据库中,对该键值的引用要进行一致的更改. 4.自定义完整性：用户自己定义的业务规则. 四种完整性约束：
实体完整性：唯一约束、主键约束、标识列
域完完整性：限制数据类型、外键约束、默认值、非空约束
引用完整性：外键 自定义完整性：过程，触发器等
2.约束 约束是在表上强制执行的一些数据校验规则，被插入、修改或删除的数据必须符合在相关字段上设置的这些约束条件。 五类完整性约束： NOT NULL 非空 UNIQUE 唯一 PRIMARY KEY 主键 FOREIGN KEY 外键 CHECKED 检查(mysql不支持的)
4.2.1 主键约束
主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一地标 识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以 加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关 系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。
1.单子段主键
主键由一个字段组成，SQL语句格式分为以下两种情况。 1)定义列的同时指定主键，语法如下
字段名 字段类型 primary key如：创建部门表 dept，表结构如下
| 字段名 | 数据类型 | 备注 |
|---|---|---|
| deptid | int | 部门编号 主键 |
| dept_name | varchar(50) | 部门名称 |
| dept_desc | varchar(200) | 部门描述 |
| dept_num | int | 部门人数 |
sql语句如下：
CREATE TABLE dept(
deptid INT PRIMARY KEY,
dept_name VARCHAR(50),
dept_desc VARCHAR(200),
dept_num INT
);
通过上图，我们还看到有个自增，自增是什么？ 在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的 主键值。可以通过为表主键添加 AUTO_INCREMENT关键字来实现。默认 的，在MySQL中AUTO_INCREMENT的初始值是1，每新增一条 记录，字段 值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字 段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数 类型（TINYINT、SMALLIN、INT、BIGINT 等）。
CREATE TABLE dept(
deptid INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50),
dept_desc VARCHAR(200),
dept_num INT
);
2)定义表之后指定主键
ALTER TABLE 表名 ADD CONSTRAINT主键名 PRIMARY KEY(字段);ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY(emp_id);
2.联合主键
主键由多个字段联合组成，语法规则如下：
primary key(字段1,字段2,...字段n)表关系详细讲解。 主键选取原则：
-
主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
-
主键应该是单列的，以便提高连接和筛选操作的效率。
-
永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
-
主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
-
主键应当有计算机自动生成 我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过就不在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。
4.2.2 非空约束
非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空 约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。
字段名 数据类型 not null
4.2.3 唯一约束
唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出 现一个空值。唯一约束可以确保一列或者几列不出现重复值。 唯一性约束的语法规则如下
字段名 数据类型 UNIQUE如果定义完表后也可以给某个字段添加唯一约束，语法如下：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (约束字段);ALTER TABLE employee ADD CONSTRAINT uk_phone UNIQUE (emp_phone);
删除唯一索引的语法：
ALTER TABLE 表名 DROP INDEX 约束名;ALTER TABLE employee DROP INDEX uk_phone;4.2.4 默认值约束
默认约束（Default Constraint）指定某列的默认值。如男性同学较多， 性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。 默认约束的语法规则如下：
字段名 字段类型 default 默认值CREATE TABLE employee(
emp_id INT,
emp_name VARCHAR(50) NOT NULL ,
emp_sex CHAR(2) DEFAULT '男',
emp_salary FLOAT,
emp_phone VARCHAR(50) UNIQUE ,
emp_birth DATE,
dept_id INT
);
4.2.5 外键约束
外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表 可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空 值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。 外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另 外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后， 不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致 性、完整性。例如，部门表tb_dept的主键是id，在员工表tb_emp5中有一个键deptId与这个id关联。 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所 在的那个表即是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所 在的那个表即是从表。 语法：
ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY (外键) REFERENCES 主表(主键);ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
REFERENCES dept(deptid);
以上案例就是一个一对多的表关系。一个部门下有多个员工，一个员工从属一个部门。 常用的表关系【重点】有三种：一对一，一对多【自关联】，多对多
一对一：人与身份证
/*
一对一
人 身份证
1 1
*/
CREATE TABLE person(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(50),
pphone VARCHAR(50)
);
CREATE TABLE idcard(
cid INT PRIMARY KEY,
cnum VARCHAR(50),
cstart DATE,
cend DATE,
cpublish VARCHAR(200)
);
ALTER TABLE idcard ADD CONSTRAINT fk_card_person FOREIGN KEY(cid) REFERENCES person(pid);一对多：部门与员工
/*
1.一对多
部门 员工
1 N
1 1
*/
CREATE TABLE department(
departid INT PRIMARY KEY AUTO_INCREMENT,
departname VARCHAR(50),
departnum INT DEFAULT 0,
departdesc VARCHAR(200)
);
CREATE TABLE employee(
empid INT PRIMARY KEY AUTO_INCREMENT,
empname VARCHAR(50),
empsex CHAR(2) DEFAULT '男',
empbirth DATE,
empphone VARCHAR(50),
deptid INT -- FK
);
/*
alter table tabname add constratin 约束名 foreign key(字段) references 主表
(主键);
*/
ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptid) REFERENCES department(departid);多对多：用户与角色【中间表多列主键】
/* 多对多的关系
用户 角色
1 N
N 1
N N
*/
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(50) NOT NULL UNIQUE,
upwd VARCHAR(50) NOT NULL,
ustatus INT
);
CREATE TABLE roles(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(50) NOT NULL
);
CREATE TABLE userroles(
uid INT ,
rid INT,
PRIMARY KEY(uid,rid)
);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_user FOREIGN KEY(uid) REFERENCES users(uid);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_role FOREIGN KEY(rid) REFERENCES roles(rid);自关联：省市
CREATE TABLE cities(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(50),
pid INT ,
CONSTRAINT fk_pro_ci FOREIGN KEY(pid) REFERENCES cities(cid)
)4.2.6 查看表结构
DESCRIBE/DESC语句可以查看表的字段信息，其中包括字段名、字段 数据类型、是否为主键、是否有默认值等。语法规则如下：
DESCRIBE 表名;或者简写为
DESC 表名; 
其中，各个字段的含义分别解释如下 NULL：表示该列是否可以存储NULL值。 Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分； UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定 值允许出现多次。 Default：表示该列是否有默认值，有的话指定值是多少。 Extra：表示可以获取的与给定列有关的附加信息，例如 AUTO_INCREMENT等。 查看表详细结构语句： SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE 语句，语法格式如下：
SHOW CREATE TABLE employee; 
CREATE TABLE `employee` (
`emp_id` int(11) NOT NULL AUTO_INCREMENT,
`emp_name` varchar(50) NOT NULL,
`emp_sex` char(2) DEFAULT '男',
`emp_salary` float DEFAULT NULL,
`emp_phone` varchar(50) DEFAULT NULL,
`emp_birth` date DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`emp_id`),
UNIQUE KEY `emp_phone` (`emp_phone`),
KEY `fk_emp_dept` (`dept_id`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept`
(`deptid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf84.2.7 修改表结构
修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用 ALTER TABLE语句修改表。常用的修改表的操作有修改表名、修改字段数 据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。本节将对和修改表有关的操作进行讲解。
-
修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;ALTER TABLE employee RENAME TO emp; -
修改字段的数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>ALTER TABLE emp MODIFY emp_name VARCHAR(30); -
修改字段名
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;ALTER TABLE emp CHANGE dept_id deptid INT; -
添加字段
ALTER TABLE <表名> ADD <新字段名> <新字段类型> <约束条件> [FIRST|AFTER 已存在字段名];ALTER TABLE emp ADD empaddr VARCHAR(20); ALTER TABLE emp ADD empaddr VARCHAR(20) AFTER empsex; ALTER TABLE emp ADD empnum VARCHAR(20) FIRST;比如条件null
ALTER TABLE tabname ADD column1 VARCHAR(12) not null; -
删除字段
ALTER TABLE <表名> DROP <字段名> ;ALTER TABLE emp DROP empno; -
修改字段的排序位置
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;ALTER TABLE emp MODIFY deptid INT AFTER empphone; -
删除表的外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept; -
更改表的存储引擎
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;ALTER TABLE tb_deptment3 ENGINE=MyISAM; -
删除数据表
DROP TABLE [IF EXISTS]表1, 表2,…表n;建表的时候，如果选择数据类型
-
整数和浮点数 如果不需要小数部分，就使用整数来保存数据；如果需要表示小数部分，就使用浮点数类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，假设列的值的范围为1~99999，若使用整数，则 MEDIUMINT UNSIGNED是 好的类型；若需要存储小数，则使用FLOAT 类型 浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此要求存储精度较高时应选择DOUBLE类型
-
浮点数和定点数浮点数
FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因 此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。 在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题， 好不要这样使用。另外，两个浮点数进行减法和比较运算时也容易出 问题，因此在进行计算的时候，一定要小心。进行数值比较时， 好使用DECIMAL类型
-
日期与时间类型 MySQL对于不同种类的日期和时间有很多数据类型，比如YEAR和TIME。如果只需要记录年份，则使用YEAR类型即可；如果只记录时间，则使用TIME类型。 如果同时需要记录日期和时间，则可以使用TIMESTAMP或者DATETIME类型。由于 TIMESTAMP列的取值范围小于DATETIME的取值范围，因此存储范围较大的日期 好使用DATETIME。 TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录的同时插入当前时间时，使用TIMESTAMP是方便的。另外，TIMESTAMP在空间上比 DATETIME更有效。
-
CHAR与VARCHAR之间的特点与选择 CHAR和VARCHAR的区别如下 CHAR是固定长度字符，VARCHAR是可变长度字符。 CHAR会自动补空格，VARCHAR不自动补。 CHAR是固定长度，所以它的处理速度比VARCHAR的速度要快，但是它的缺点是浪费存储空间，所以对存储不大但在速度上有要求的可以使用CHAR类型，反之可以使用 VARCHAR类型来实现。
305
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
