与MySQL的零距离接触(一.约束修改数据表)

---

八月中秋薄露，路上行人凄凉，小桥流水桂花香，日夜千思万想，
心中不得宁静，清早览罢文章，十年寒苦在书房，方显才高志广！

---

初涉MySQL

MySQL的安装与配置：

Mac系统安装MySQL及Navicat Premium—http://blog.csdn.net/wtdask/article/details/79025674

操作数据库

数据类型与操作数据表

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

MySQL创建数据表

数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础。

终端操作——

登录数据库

mysql -uroot -proot -P3306 -h127.0.0.1

展示数据库

SHOW DATABASES

打开数据库：

USE shopping

显示用户当前打开的数据库

SELECT DATABASE();

 CREATE TABLE tb1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );

MySQL查看数据表

SHOW TABLES FROM mysql; //查看mysql数据库当中所有的数据表

MySQL查看数据表结构

SHOW COLUMNS FROM tb1;

MySQL记录的插入与查找

INSERT tb1 VALUES('Tom',25,7863.25);

INSERT tb1(username,salary) VALUES('John',4500.79);

SELECT * FROM tb1;

MySQL空值与非空

NULL，字段值可以为空
NOT NULL，字段值禁止为空

CREATE TABLE tb2(
    -> age TINYINT UNSIGNED NULL
    -> );

MySQL自动编号

MySQL初涉主键约束

CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL
    -> );

MySQL初涉唯一约束

CREATE TABLE tb5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,                                    -> age tinyint UNSIGNED)                                                        -> ;

MySQL初涉默认约束

 CREATE TABLE tb6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'
    -> );

约束以及修改数据表

MySQL外键约束的要求解析

1、约束保证数据的完整性和一致性。
2、约束分为表级约束和列级约束。
3、约束类型包括：
NOT NULL（非空约束）
PRIMARY KEY （主键约束）
UNIQUE KEY (唯一约束)
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

外键约束的要求：

1、父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2、数据表的存储引擎只能为InnoDB。
3、外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
4、外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

mac系统参照下面的文档进行配置
Mac上安装Mysql配置文件的添加及修改配置文件

CREATE DATABASE test  创建test数据库

USE test  使用test数据库

 CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );  //创建省份表

查看是否满足上述要求，满足条件2存储引擎为InnoDB

SHOW CREATE TABLE provinces;

创建users表，添加外键，由上图可以看出必须满足外键约束的条件3：外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

 CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,//设置id为主键，自动编号 参照列
    -> username VARCHAR(10) NOT NULL,//字符长度10，禁止为空
    -> pid SMALLINT UNSIGNED,//设置外键pid(省份id)为id的相同类型  外键列
    -> FOREIGN KEY (pid) REFERENCES provinces(id)  //设置外键约束pid，参照provinces表中的id字段   有外键的表称之为字表 users，而参照的表称为父表 provinces                         
    -> );

参照列自动创建索引

查看是否创建索引

provinces表

users表

MySQL外键约束的参照操作

CREATE TABLE users1(                                                         -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE
    -> );

查看user1表

分别在两张表中插入记录，首先要在父表中插入，然后才可以在子表中插入。

父表插入记录

子表插入数据

INSERT users1(username,pid) VALUES('Tom',3);//3对应父表中的C省份

删除父表中的id=3

由此我们可以看出，删除父表也会影响子表中的记录，更新亦是如此。

在外键约束当中，在实际的开发过程中，我们很少使用物理的外键约束，很多都去使用逻辑的外键约束，因为物理的外键约束只有INNODB这种引擎才会支持，像我们另外的一种引擎MYISAM的引擎则不支持，反过来说，如果我想创建的数据表，假设存储引擎为MYISAM，而且又想使用外键约束的话，其实是不可能实现的，所以说，我们在实际的项目开发中，我们不去定义物理的外键，所谓的逻辑外键指的是就是我们在定义两张表的结构的时候，我们是按照存在的某种结构的方式去定义，但是不去使用FOREIGN KEY这个关键词来定义。

MySQL表级约束与列级约束

MySQL修改数据表-添加/删除列

ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

 ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;

ALTER TABLE users1 DROP truename;

删除多列

ALTER TABLE users1 DROP password,DROP age;

MySQL修改数据表-添加约束

 //创建新表
CREATE TABLE users2(
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED
    -> );

//添加字段
ALTER TABLE users2 ADD id smallint unsigned;

//设置id为主键
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

AFTER TABLE users2 ADD UNIQUE (username);

ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces(id);

//添加age字段
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;

//设置默认值为15
ALTER TABLE users2 ALTER age SET DEFAULT 15;

//删除age字段
ALTER TABLE users2 ALTER age DROP DEFAULT;

MySQL修改数据表-删除约束

ALTER TABLE users2 DROP PRIMARY KEY;

SHOW INDEXES FROM users2;//查看数据表的名字

//删除唯一约束
ALTER TABLE users2 DROP INDEX username;

//查看外键名称
SHOW CREATE TABLE users2;

 ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;

MySQL修改数据表-修改列定义和更名数据表

我们让id位于第一行

 ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

此时我们亦可以修改数据类型，但是要注意，由大类型修改到小类型有可能会造成数据的丢失。

//修改pid为p_id，类型为TINYINT
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

ALTER TABLE users2 RENAME users3;

//同时可以使用这种方式
 RENAME TABLE users3 TO users2;