表结构相关操作

 表结构相关操作1.111、增、删字段：
添加字段时，FIRET表示放在首位，AFTER...表示放在...之后。

CREATE TABLE IF NOT EXISTS user1(
id INT UNSIGNED AUTO_INCREMENT KEY
);

ALTER TABLE user1
ADD username VARCHAR(20);

ALTER TABLE user1
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;

ALTER TABLE user1
ADD password CHAR(32) NOT NULL AFTER id;

​

ALTER TABLE user1
DROP test;

​

 或者连起来写：一次选中表完成不同操作

ALTER TABLE user1
ADD username VARCHAR(20)，
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST，
ADD password CHAR(32) NOT NULL AFTER id，
DROP test;

 2、增、删默认值：

先选中表，再选中字段

CREATE TABLE user2(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL DEFAULT 'A',
age TINYINT UNSIGNED NOT NULL 
);

ALTER TABLE user2
ALTER age SET DEFAULT 18,
ALTER username DROP DEFAULT;

​

3、

• 修改字段类型、字段属性：

ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL UNIQUE,
MODIFY email VARCHAR(50) NOT NULL FIRST;

• 修改字段名称、字段类型、字段属性

ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL UNIQUE,
CHANGE password pwd CHAR(32) NOT NULL;

4、增、删主键：

• 本来没有主键：

CREATE TABLE user4(
id INT UNSIGNED,
username VARCHAR(5) NOT NULL UNIQUE,
password CHAR(32) NOT NULL
);

ALTER TABLE user4
ADD PRIMARY KEY(id);

ALTER TABLE user4
DROP PRIMARY KEY;

 

​

•  本来有主键：

CREATE TABLE user5(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(5) NOT NULL UNIQUE
);

ALTER TABLE user5
DROP PRIMARY KEY;

会报错：
​

因为自增字段必须是索引列，所以需要先删除自增，再删主键：

ALTER TABLE user5
MODIFY id INT UNSIGNED,
drop PRIMARY KEY;

​

5、增、删唯一索引：

首先，默认的唯一索引名称是字段名：

​

ALTER TABLE user6
drop index username;

​

ALTER TABLE user6
ADD UNIQUE KEY(username),
ADD UNIQUE INDEX uni_password(password);

​

ALTER TABLE user6
drop UNIQUE KEY(username);

 6、修改表名：

ALTER TABLE user7
RENAME user6;

 或：

ALTER TABLE user7
RENAME to user6;

 或：

ALTER TABLE user7
RENAME as user6;

 ​7、外键：

 外键：只有InnoDB存储引擎支持外键。

• 创建：注意子表的外键关联必须是父表的主键。子表的外键字段和主表的主键字段类型要相似，如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同。如果外键字段没有创建索引，MySQL会自动创建索引。

①建表添加外键：

主表：

CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);

从表：

CREATE TABLE news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(100) NOT NULL UNIQUE,
content VARCHAR(1000) NOT NULL,
cateId TINYINT UNSIGNED NOT NULL,
FOREIGN KEY(cateId) REFERENCES news_cate(id)
);

​

 

 

测试外键效果：

​

​

这种情况应该先删掉子表的相关记录！ 要删父表得先删子表！

​

只有对子表没影响的父表字段可以更新！

②建表时可以指定外键名称：一般是：外键字段_参照_主表

CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)

③动态删除外键：

ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

④动态添加外键：注意添加前，表中的记录一定是合法的，不能有脏值，否则外键添加不成功！

ALTER TABLE news
 ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

 没给外键命名时，系统会自动命名：

​

动态添加时指定外键名：

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

 

•  外键约束的参照操作:

①CASCADE：级联操作，从父表删除或更新，子表也跟着删除或更新。

ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;

​

②SET NULL：从父表删除或更新记录，并设置子表的外键列为NULL，前提是该字段可以为空。

③NO ACTION | RESTRICT：拒绝对父表更新或删除。