MySQL中的约束,添加约束,删除约束,以及一些其他修饰

创建数据库：

CREATE DATABASES 数据库名；

选择数据库：

USE 数据库名；

删除数据库：

DROP DATAVBASE 数据库名；

创建表：

CREATE TABLE IF NOT NULL EXISTS 表名 （字段1 数据类型 【字段属性|约束|索引|注释】,
.............）;

字段的约束及属性
CREATE TABLE IF NOT NULL 表名(字段1 数据类型 PRIMARY KEY //单字段主键）;

多字段联合主键：
CREATE TABLE IF NOT NULL 表名
　　( 字段1 数据类型 ,
 　　 字段2 数据类型
　　  PRIMARY KEY (字段1，字段2)//多联合复合主键
);

注释：

 CREATE TABLE TEXT （ `id` int(11) UNSIGNED COMMENT'编号'）COMMENT'测试表';

编码格式设置：

CREATE TABLE TEXT（ `id` int(11) UNSIGNED COMMENT'编号'）CHARSET=字符集名;

查看表：

SHOW TABLES;

查看表定义：

DESCRIBE 表名；或 DESC 表名；

如果是在DOC窗口下执行，最好先执行 SET NAMES gbk;避免产生乱码

删除表：

DROP TABLE IF EXISTS 表名；

查看默认存储引擎：

SHOW VARIABLES LIKE `storage_engine%`;

指定表的存储引擎：

CREATE TANLE 表名（#省略代码）ENGINE=存储引擎；

常用引擎：InnoDB 和 MyISAM
安装板MySQL5.5默认存储引擎是InnoDB

常用修改语法：

#修改表名：

ALTER TABLE 旧表明 RENAME [TO] 新表明； TO：可选项

#添加字段：

ALTER TABLE 表名 ADD 字段名 数据类型 [属性]; 属性：可选项

#修改字段：

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

#删除字段：

ALTER TABLE 表名 DROP 字段名；

mysql中replace函数直接替换mysql数据库中某字段中的特定字符串：

UPDATE `ts_weibo` SET

 `weibo_id` = replace (`weibo_id`,'19782687','14204277'),
 `uid` = replace (`uid`,'19782687','14204277'
  .....
  WHERE
 `weibo_id` LIKE '%19782687%' or 
 `uid` LIKE '%19782687%'

非空约束（NOT NULL)：添加非空约束

1)建表时直接添加

CREATE TABLE t_user(user_id INT(10) NOT NULL);

2)通过ALTER 语句

ALTER TABLE t_user MODIFY user_id INT(10) NOT NULL;
ALTER TABLE t_user CHANGE user_id user_id INT(10) NOT NULL;

删除非空约束

1)：ALTER TABLE t_user MODIFY user_id INT(10);
2)：ALTER TABLE t_user CHANGE user_id user_id INT(10);

删除主键和唯一约束：在mysql中删除主键需要两步

(1)：如果有auto_increment自增,先删除之；

(2)：删除主键约束 primary key；

首先要删除auto_increment语法如下：

alter table 表名 modify id int（11);

这里用的modify，只改变数据类型，也可以用change，改变列名的同时输入新的数据类型。

下一步就是删除主键：语法如下：

alter table 表名 drop primary key;

 添加唯一约束：

1)建表时直接添加

CREATE TABLE t_user(user_id INT(10) UNIQUE);

CREATE TABLE t_user(
　　user_id INT(10),
　　user_name VARCHAR(30),
　　CONSTRAINT UN_PHONE_EMAIL UNIQUE(user_id,user_name)#复合约束
);

CREATE TABLE t_user (user_id INT(10),UNIQUE KEY(user_id) );

通过ALTER语句添加：

ALTER TABLE t_user MODIFY user_id INT(10) UNIQUE;

ALTER TABLE t_user CHANGE user_id user_id INT(10) UNIQUE;

ALTER TABLE t_user ADD UNIQUE(user_id);

ALTER TABLE t_user ADD UNIQUE KEY(user_id);

ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE(user_id);

ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE KEY(user_id);

这里用的modify，只改变数据类型，也可以用change,改变列名的同时输入新的数据类型。

删除唯一性约束：

ALTER TABLE t_user DROP INDEX user_id;//指定要删除的字段名称

ALTER TABLE tab_name  DROP  [INDEX | KEY]  index_name ;//指定要删除的索引名称

DROP INDEX <索引名> ON <表名>

注：唯一但是可以为空(空和空不相等)

PRIMARY KEY(主键约束)：

1)建表时直接添加
CREATE  TABLE  text(`user_id`  INT(10)  PRIMARY  KEY);
CREATE TABLE `text`
  (`user_id` INT(10),
   user_name` VARCHAR(30),
   CONSTRAINT PK_ID_NAME PRIMARY KEY(user_id,user_name)#复合约束
);
CREATE TABLE `text`
  (`user_id` INT(10),
  PRIMARY KEY(user_id)
);

通过ALTER语句添加：

ALTER TABLE t_user MODIFY user_id INT(10) PRIMARY KEY;

ALTER TABLE t_user CHANGE user_id user_id INT(10) PRIMARY KEY;

ALTER TABLE t_user ADD PRIMARY KEY(user_id);

ALTER TABLE t_user ADD CONSTRAINT PK_ID PRIMARY KEY(user_id);

这里用的modify，只改变数据类型，也可以用change,改变列名的同时输入新的数据类型。

ALTER TABLE  text  ADD  CONSTRAINT  pk_textId  PRIMARY KEY  `text`(`textId`); 

删除主键约束：

ALTER TABLE  `text`  DROP PRIMARY KEY;

ALTER TABLE t_user DROP PRIMARY KEY;

FOREIGN KEY(外键约束）：

首先创建两张表:A,B
从表A
CREATE TABLE `A` 
(
`textId` INT PRIMARY KEY,
`textId1` INT(30),
`textName` NVARCHAR(30)
 CONSTRAINT FK_A_B FOREIGN KEY(`textId1`) REFERENCES `b`(`textId1`)#添加外键约束
);

主表B
CREATE TABLE `B` 
(
`textId1` INT PRIMARY KEY,
`textName` NVARCHAR(30)
);

另外一种写法：

ALTER TABLE `a` ADD CONSTRAINT fk_a_b FOREIGN KEY (`textId1`) REFERENCES `b`(`textId1`);

删除外键约束：

第一步：
ALTER TABLE `a` DROP FOREIGN KEY fk_a_b;

第二步：
DROP INDEX fk_a_b ON `a`;

注意事项: 删除外键约束的时候需要先删除外键约束，在删除索引才可以

键中的级联关系有以下几种情况：

#ON DELETE CASCADE 删除主表中的数据时，从表中的数据随之删除

#ON UPDATE CASCADE 更新主表中的数据时，从表中的数据随之更新

#ON DELETE SET NULL 删除主表中的数据时，从表中的数据置为空

#默认 删除主表中的数据前需先删除从表中的数据，否则主表数据不会被删除

CREATE TABLE students(
　　stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
　　stu_name VARCHAR(30) NOT NULL,
　　stu_score FLOAT(5,2) DEFAULT 0.0,
　　cla_id INT(10),
　　CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE CASCADE
);
CREATE TABLE students(
　　stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
　　stu_name VARCHAR(30) NOT NULL,
　　stu_score FLOAT(5,2) DEFAULT 0.0,
　　cla_id INT(10),
　　CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON UPDATE CASCADE
);
CREATE TABLE students(
　　stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
　　stu_name VARCHAR(30) NOT NULL,
　　stu_score FLOAT(5,2) DEFAULT 0.0,
　　cla_id INT(10),
　　CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE SET NULL
);

注：插入数据时，先插入主表中的数据，再插入从表中的数据。删除数据时，先删除从表中的数据，再删除主表中的数据

 

check约束：

低于8.0的mysql版本 不支持

AUTO_INCREMENT(自增长)：

在创建表的时候添加：

CREATE TABLE t_user(user_id INT(10) AUTO_INCREMENT PRIMARY KEY); 

通过ALTER语句：

ALTER TABLE t_user MODIFY user_id INT(10) AUTO_INCREMENT;

ALTER TABLE t_user CHANGE user_id user_id INT(10) AUTO_INCREMENT;

删除自增长：

ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

注意：

注：There can be only one auto column and it must be defined as a key.

一张表只能有一个自增长列，并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束);不过自增长一般配合主键使用，并且只能在数字类型中使用
 

DEFAULT(默认属性值) ：

1)在创建表的时候添加

CREATE TABLE t_user(user_id INT(10) DEFAULT  3); 

2)通过ALTER语句

ALTER TABLE t_user MODIFY user_id INT(10) DEFAULT  2;

ALTER TABLE t_user CHANGE user_id user_id INT(10) DEFAULT  2;

删除默认约束：

ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

这里用的modify，只改变数据类型，也可以用change， 改变列名的同时输入新的数据类型。

UNSIGNED(无符号位)：

1)在创建表的时候添加

CREATE TABLE t_user(user_id INT(10) UNSIGNED); 

2)通过ALTER语句

ALTER TABLE t_user MODIFY user_id INT(10) UNSIGNED; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) UNSIGNED;

删除无符号：

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

无符号作用于数值类型

ZEROFILL(零填充)：

添加零填充

1）在创建表的时候添加

CREATE TABLE t_user(user_id INT(10) ZEROFILL);

2）通过ALTER语句

ALTER TABLE t_user MODIFY user_id INT(10) ZEROFILL;
ALTER TABLE t_user CHANGE user_id user_id INT(10) ZEROFILL;

3）删除零填充：

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

注：零填充会将未将有效位以外的位用零来显示,比如某字段数据类型为INT(5),而插入的值为2,那么零填充会显示00002；但是，这个效果在Navicat for MySQL中显示不出来,只有在DOS窗口下才能显示

#从查询information_schema中查询指定表中的约束

USE INFORMATION_SCHEMA;

SELECT CONSTRAINT_NAME FROM TABLE_CONSTRAINTS WHERE TABLE_NAME='student';