5天手把手教你，MySql从入门到精通：06.MySql修改表的结构-谜妹韩漫漫画
SQL:Structued Query Language,结构化查询语言
SQL语言分为4类子语言：
1、DDL：Data Definition Language数据定义语言
create,drop,alter,truncate table
2、DML:Data Manipulation Language数据操纵语言
insert,delete,update,select
3、DCL:Data Control Language数据控制语言，授权
grant,revoke
-- 授权：将用户1(当前用户)表test的查询和添加记录的权限授予给user2
grant select,insert on user1.test to user2;
-- 撤销权限：将用户1(当前用户)表test的insert权限收回
revoke insert on user1.test from user2;
4、TCL:Transaction Control Language事务处理语言
commit,rollback
MySql
修改表的结构：
alter table 表名 <action操作行为>;
操作行为包括如下：
说明：action 可以是如下语句：
A、add 列名 建表语句 [first | after 列名]
可以为表添加一列，如果没指定first或者after，则在列尾添加一列，
否则在指定列添加新列
B、add primary key (列名)为表添加一个主键，如果主键已经存在，则出现错误
C、add foreign key(列名) references 表名(列名)为表添加一个外键
D、alter 列名 set default 默认值可以更改指定列默认值
E、change 旧列名 新列名 <建表语句> [first | after 列名]
可以更改列类型和列名称，如果原列的名字和新列的名字相同
F、modify 列名 <建表语句> [first | after 列名]
和change的作用相同
G、drop 列名 //可以删除一列
H、drop primary key//可以删除主键
I、engine 类型名 //可以改变表类型，引擎类型
在创建一个新的MySQL数据表时，可以为它设置一个类型，其中最重要的3种类型是
MyISAM：成熟、稳定和易于管理
InnoDB：加入事务、数据行级锁定机制、外键约束条件、崩溃恢复等新功能
HEAP：只存在于内存中，可做临时表
create table tmp(…) ENGINE=MyISAM
J、rename as 新表名 //可以将表名更改
案例如下：
1、创建部门表和员工表
use oa;
create table departments
(
id int primary key,-- 部门编号
name varchar(50) not null-- 部门名称
);
-- 添加记录
insert into departments values(10,'研发部');
insert into departments values(20,'人事部');
insert into departments values(30,'销售部');
insert into departments values(40,'总裁部');
create table employees9
(
id int, -- 员工编号，主键
name varchar(50), -- 员工姓名，不能为空
sex enum('男','女'), -- 员工性别，枚举值
hiredate date, -- 入职日期
salary float(10,2), -- 工资,缺省值是1000
pid varchar(18) unique-- 身份证号码，唯一性
);
-- 添加测试记录
insert into employees9 values(1001,'刘备','男','1983-01-31',1000.38,123);
insert into employees9(hiredate,id,name,sex) values('1985-3-28',1002,'关羽','男');
insert into employees9 values(1003,'张飞','男','1987-11-30',3000.38,1);
insert into employees9 values(1004,'赵子龙',null,'1997-11-30',null,2);
insert into employees9(id,name,hiredate) values(1005,'诸葛孔明','2000-11-30');
2、在员工表中添加列departmentid
alter table employees9 add departmentid int; -- 默认添加的列放在最后
alter table employees9 add departmentid int first; -- 放在最前面
alter table employees9 add departmentid int after salary; -- 放在工资字段后面
3、为员工表设置id列为主键
alter table employees9 add primary key(id);
4、为员工表添加列departmentid为外键列，引用departments表的id列
alter table employees9 add foreign key(departmentid) references departments(id);
5、将员工表的salary的缺省值设置为1000
alter table employees9 alter salary set default 1000;
alter table employees9 alter salary set default null;
-- 通过修改列的方式添加缺省值
alter table employees9 modify salary float(10,2) default 1000;
-- 取消缺省值列
alter table employees9 modify salary float(10,2);
6、将员工表的name列修改为非空列
-- 修改列的数据类型、宽度精度、是否为空、调整字段的位置顺序
alter table employees9 modify name varchar(50) not null;
-- 将修改的列为非空并放在首部，作为第一个字段
alter table employees9 modify name varchar(50) not null first;
-- 将修改的列为非空并放在尾部，作为最后一个字段
alter table employees9 modify name varchar(50) not null after departmentid;
-- 还原到原始位置，在id的后面
alter table employees9 modify name varchar(50) not null after id;
7、将员工表的name列名改成employeename
alter table employees9 change name employeename varchar(50);
alter table employees9 change employeename name varchar(50) not null;
alter table employees9 change name employeename varchar(50) first;
alter table employees9 change employeename name varchar(50) not null after id;
8、在员工表中添加列address,varchar,250个长度
alter table employees9 add address varchar(250);
alter table employees9 add (phone varchar(11),remark varchar(250));
9、删除员工表中列address
alter table employees9 drop address;
10、删除主键
alter table employees9 drop primary key;
添加主键约束，指定约束名称
alter table employees9 add constraint pk_e9_id primary key(id);
11、删除外键
show create table employees9; -- 查看建表语句信息，找到外键约束名称
mysql> SHOW CREATE TABLE employees9;
+------------+--------------------------------------------------------------------------------
| Table | Create Table
+------------+--------------------------------------------------------------------------------
| employees7 | CREATE TABLE `employees7` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(50) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`salary` float DEFAULT '1000',
`pid` varchar(18) DEFAULT NULL,
`departmentid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `pid` (`pid`),
KEY `departmentid` (`departmentid`),
CONSTRAINT `employees9_ibfk_1` FOREIGN KEY (`departmentid`) REFERENCES `departments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------------------------
1 row in set (0.00 sec)
-- 删除外键
alter table employees9 drop foreign key employees9_ibfk_1;
-- 删除外键后，外键字段在KEY列还有MUL属性，删除方式如下
alter table employees9 drop key departmentid;
12、删除列pid的唯一性约束
-- 通过删除索引的方式来删除唯一性约束
alter table employees9 drop index pid; -- 直接删除唯一性约束的字段名即可
13、添加唯一性约束
alter table employees9 modify pid varchar(18) unique;
14、修改表名
alter table 表名 rename 新表名;
alter table employees9 rename employees99;
15、在建表的同时创建约束
create table employees10
(
id int primary key auto_increment, -- 员工编号，主键
name varchar(50) not null, -- 员工姓名，不能为空
sex enum('男','女'), -- 员工性别，枚举值
hiredate date, -- 生日
salary float default 1000, -- 工资,缺省值是1000
pid varchar(18) unique,-- 身份证号码，唯一性
departmentid int,-- 部门编号，外键
foreign key(departmentid) references departments(id)-- 设置外键
);