澳门金沙vipMySQL(十五)DDL之常见的约束

mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结

mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结

CREATE TABLE tab2 AS (SELECT * FROM tab1)
这种做法表的存储引擎也会采用服务器默认的存储引擎而不是源表的存储引擎,此种复制方法把表的内容也一起复制过来了。

NOT NULL:非空,该字段的值必填

1. MYSQL数据库

CREATE TALBE tab2 ENGINE=MYISAM, CHARSET=’UTF8′ AS (SELECT * FROM
tab1)
可以自己指定存储引擎和字符集,弥补方法一的不足

UNIQUE:唯一,该字段的值不可重复

1) 创建索引

CREATE INDEX index_name ON table_name(column_list)

CREATE UNIQUE INDEX index_name ON table_name(column_list)

修改表的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY(column_list)

CREATE TABLE tab2 LIKE tab1
使用和tab1表相同的结构来创建一个新表,列名、数据类型、空指和索引也将复制,但是表的内容不会被复制。外键和专用的权限也没有被复制。

DEFAULT:默认,该字段的值不用手动插入有默认值

2) 删除索引

DROP INDEX index_name ON talbe_name

修改表的方式删除索引

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

MySQL复制表结构及数据到新表
CREATE TABLE tab_new SELECT * FROM tab_old

PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null

3) 创建外键

修改表的方式添加外键

ALTER TABLE TABLE_NAME
ADD [CONSTRAINT 外键名]FOREIGN KEY (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)

举例:

ALTER TABLE TABLE_NAME ADD CONSTRAINT FK_NAME(user_id)

REFERENCE sys_user(id)

需要注意两个字段的数据类型一致;

复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO tab1 SELECT * FROM tab2

FOREIGN KEY:外键,该字段的值引用了另外的表的字段

4) 删除外键

ALTER TABLE sys_org DROP FOREIGN KEY fk_s_o_id;

复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO tab1(字段1, 字段2, …) SELECT 字段1, 字段2, … FROM tab2

1、区别:

5) 列操作语法

? 添加列 birthday ,有COLUMN关键字

alter table sys_useradd COLUMN birthday char(19) not null;

? 修改列 birthday ,有MODIFY 关键字

alter table sys_usermodify birthday char(10);

? 删除列 birthday,有 DROP COLUMN关键字

alter table sys_userdrop column birthday;

? 修改列名称及属性;把birthday修改成CSRQ ,并改属性为char(10)
和不允许为空not null

alter table sys_userchange birthday CSRQ char(10) not null;

更改表名
ALTER TABLE employee RENAME TO staff

①、一个表至多有一个主键,但可以有多个唯一

2. SQL SERVER 数据库

更改列类型
ALTER TABLE employee MODIFY COLUMN truename VARCHAR(10) NOT NULL DEFAULT

②、主键不允许为空,唯一可以为空

1) 创建索引

create UNIQUE INDEX un_index_name on sys_user(user_name);

更改列名
ALTER TABLE employee CHANGE COLUMN truename employeename VARCHAR(10) NOT
NULL DEFAULT ”

2、相同点

2) 删除索引

DROP INDEX un_index_name ON sys_user

添加默认值
ALTER TABLE employee ALTER COLUMN truename SET DEFAULT ”

都具有唯一性

3) 创建外键

alter table sys_org add CONSTRAINT fk_s_o_id FOREIGN
key(create_user)

REFERENCES sys_user(id);

与mysql一致,需要注意两个字段的数据类型一致;

删除默认值
ALTER TABLE employee ALTER COLUMN truename DEOP DEFAULT

都支持组合键,但不推荐

4) 删除外键

ALTER TABLE sys_org DROP constraint fk_s_o_id;

与mysql语法不同

mysql修改表
表的结构如下:

1、用于限制两个表的关系,从表的字段值引用了主表的某字段值

5) 列维护语法

6) 添加列 birthday ,有COLUMN关键字

alter table sys_user add birthday char(19) not null;

7) 修改列 birthday属性 ,有alter column 关键字

alter table sys_user altercolumn birthday char(10);

8) 删除列 birthday,有 DROP COLUMN关键字

alter table sys_user dropcolumn birthday ;

9) 修改列名称及属性;把birthday修改成CSRQ ,并改属性为char(10)
和不允许为空not null;需要分成两步执行;

exec sp_rename ‘sys_user.[birthday]’,’CSRQ’,’COLUMN’;

alter table sys_user altercolumn CSRQ char(10) not null;

mysql> show create table person;
| person | CREATE TABLE `person` (
  `number` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
删除列:

2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求

3. ORACLE 数据库

ALTER TABLE person DROP COLUMN birthday;
添加列:

3、主表的被引用列要求是一个key

1) 创建索引

CREATE INDEX index_name ON table_name(column_list)

CREATE UNIQUE INDEX index_name ON table_name(column_list)

create INDEX ind_s_u_sex on sys_user(sex);

ALTER TABLE person ADD COLUMN birthday datetime;
修改列,把number修改为bigint:

4、插入数据,先插入主表

2) 删除索引

DROP INDEX [schema.]indexname;

drop index ind_s_u_sex;

ALTER TABLE person MODIFY number BIGINT NOT NULL;
或者是把number修改为id,类型为bigint:

5、删除数据,先删除从表

3) 创建外键

修改表的方式添加外键

ALTER TABLE TABLE_NAME
ADD [CONSTRAINT 外键名]FOREIGN KEY (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)

举例:

ALTER TABLE TABLE_NAME ADD CONSTRAINT FK_NAME(user_id)

REFERENCE sys_user(id)

需要注意两个字段的数据类型一致;

ALTER TABLE person CHANGE number id BIGINT;
 

可以通过以下两种方式来删除主表的记录

4) 删除外键

ALTER TABLE TABLE_NAME DROP CONSTRAINT FK_NAME;

举例:

ALTER TABLE sys_org drop constraint fk_澳门金沙vip,s_o_id;

添加主键:

#方式一:级联删除

5) 列操作语法

? 添加列 birthday , 有add关键字

alter table sys_useradd birthday char(19) not null;

? 修改列 birthday ,有MODIFY 关键字

alter table sys_usermodify birthday char(10);

? 删除列 birthday,有 DROP COLUMN关键字

ALTER TABLE sys_user DROPCOLUMN birthday;

? 修改列名称及属性;把birthday修改成CSRQ ,并改属性为char(10)
和不允许为空not null;需要分成两步执行;

ALTERTABLE sys_user RENAME COLUMN birthday to CSRQ;

ALTER TABLE sys_user MODIFYCSRQ char(10) ;

mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结 1.
MYSQL数据…

ALTER TABLE person ADD PRIMARY KEY (id);
删除主键:

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY REFERENCES
major ON DELETE CASCADE;

ALTER TABLE person DROP PRIMARY KEY;
添加唯一索引:

#方式二:级联置空

ALTER TABLE person ADD UNIQUE name_unique_index (`name`);
为name这一列创建了唯一索引,索引的名字是name_unique_index.

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY REFERENCES
major ON DELETE SET NULL;

 

create table 表名(

添加普通索引:

字段名 字段类型 not null,#非空

ALTER TABLE person ADD INDEX birthday_index (`birthday`);
 

字段名 字段类型 primary key,#主键

删除索引:

字段名 字段类型 unique,#唯一

ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;
 

字段名 字段类型 default 值,#默认

禁用非唯一索引

constraint 约束名 foreign key references 主表

ALTER TABLE person DISABLE KEYS;
ALTER TABLE…DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。

);

激活非唯一索引

注意:

ALTER TABLE person ENABLE KEYS;
ALTER TABLE … ENABLE KEYS重新创建丢失的索引。

列级约束: 除了外键其他类型都支持,不可以起约束名;

 

表级约束:除了非空和默认其他类型都支持,可以起约束名,但对主键无效。

把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集:

列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求

ALTER TABLE person CONVERT TO CHARACTER SET utf8;
修改表某一列的编码

添加非空

ALTER TABLE person CHANGE name name varchar(255) CHARACTER SET utf8;
仅仅改变一个表的默认字符集

alter table 表名 modify column 字段名 字段类型 not null;

ALTER TABLE person DEFAULT CHARACTER SET utf8;
 修改表名

删除非空

RENAME TABLE person TO person_other;
移动表到其他数据库

alter table 表名 modify column 字段名 字段类型 ;

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
 

添加默认

 

alter table 表名 modify column 字段名 字段类型 default 值;