MySQL中的约束_DML_事务以及修改表
1.修改表
1.使用 DDL 语句修改表名
ALTER TABLE 旧表名 RENAME 新表名
将 employees 表名修改为 emp。
alter table employees rename emp;
2.使用 DDL 修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型
将 emp 表中的 last_name 修改为 name
alter table emp change column last_name name varchar(30);
3.使用 DDL 语句修改列类型
ALTER TABLE 表名 MODIFY 列名 新类型
将 emp 表中的 name 的长度指定为 40
alter table emp modify name varchar(40);
4.使用 DDL 语句添加列
ALTER TABLE 表名 ADD COLUMN 新列名 类型
在 emp 表中添加一个新的列为 commission_pct
alter table emp add column commission_pct float(4,2);
5.使用 DDL 语句删除列
ALTER TABLE 表名 DROP COLUMN 列名
删除 emp 表中的 commission_pct
alter table emp drop column commission_pct;
2.MySQL 中的约束
1.约束类型
- 非空约束(not null)
- 唯一性约束(unique)
- 主键约束(primary key) PK
- 外键约束(foreign key) FK
- 检查约束(目前 MySQL 不支持、Oracle 支持)
2.创建表时添加约束
查询表中的约束信息
SHOW KEYS FROM 表名
创建 departments 表包含 department_id 该列为主键且自动增长，department_name 列不允许重复，location_id 列不允含有空值。
create table departments(
department_id int primary key auto_increment,
dep artment_name varchar(30) unique,
location_id int not null);
创建 employees 表包含 employees_id 该列为主键且自动增长，last_name 列不允许含有空值，email 列不允许有重复不允许含有空值，dept_id 为外键参照 departments 表的主键。
create table employees
(employees_id int primary key auto_increment,
last_n ame varchar(30) not null,
email varchar(40) not null unique,
dept_id int,
constrain t emp_fk foreign key(dept_id) references departments(department_id));
3.修改表实现约束的添加与删除
1.主键约束
1.添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名)
将 emp 表中的 employee_id 修改为主键且自动增长
添加主键：alter table emp add primary key(employee_id);
添加自动增长：alter table emp modify employee_id int auto_increment;
2.删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY
注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后再删除主键。
删除 employee_id 的主键约束。
去掉自动增长： alter table emp modify employee_id int;
删除主键：alter table emp drop primary key;
2.非空约束
1.添加非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL
向 emp 表中的 salary 添加非空约束。
alter table emp modify salary float(8,2) not null;
2.删除非空约束
ALTER TABLE 表名 MODIFY 列名 类型 NULL
删除 salary 的非空约束。
alter table emp modify salary float(8,2) null
3.唯一约束
1.添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
向 emp 表中的 name 添加唯一约束。
alter table emp add constraint emp_uk unique(name);
2.删除唯一约束
ALTER TABLE 表名 DROP KEY 约束名
删除 name 的唯一约束。
alter table emp drop key emp_uk;
4.外键约束
1.添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 参照的表名(参照的列名)
向 emp 表中的 dept_id 列添加外键约束。
alter table emp add constraint e_fk foreign key(dept_id) references depar tments(department_id);
2.删除外键约束
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 约束名
删除外键索引(索引名与约束名相同):
ALTER TABLE 表名 DROP INDEX 索引名
删除 dept_id 的外键约束。
删除外键：
alter table emp drop foreign key e_fk;
删除索引：
alter table emp drop index e_fk;
3.MySQL 中的 DML 操作
1.添加数据(INSERT)
1.插入数据
1.选择插入
INSERT INTO 表名(列名 1，列名 2，列名 3.....) VALUES(值 1，值 2，值 3......)
向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。
insert into departments(department_name,location_id) values("market",1);
2.完全插入
INSERT INTO 表名 VALUES(值 1，值 2，值 3......)
如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。
向 departments 表中添加一条数据，部门名称为 human，工作地点 ID 为 3。使用 null 占位。
insert into departments values(null,"human",3);
2.自动增长(auto_increment)
MySQL 中的自动增长类型要求：
- 一个表中只能有一个列为自动增长。
- 自动增长的列的类型必须是整数类型
- 自动增长只能添加到具备主键约束与唯一性约束的列上
- 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后再删除约束
创建一个 emp2 表。包含 id 该列为主键，包含 name，包含 seq_num 要求该列为具备唯 一性约束，该列的值自动增长。
create table emp2(id int primary key ,name varchar(30),seq_num int unique auto_increment);
3.默认值处理
在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。
1.创建表时指定列的默认值
创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name，包含 address 该列默认 值为”未知”。
create table emp3(
emp_id int primary key auto_increment,
name varchar(30),
address varchar(50) default 'Unknown');
2.修改表添加列的默认值
修改 emp3 表，添加 job_id 该列默认值为 0。
alter table emp3 add column job_id int default 0;
3.插入数据时的默认值处理
如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是 完全项插入需要使用 default 来占位。
向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。
insert into emp3(name) values("admin");
insert into emp3 values(default,"oldlu",default,default);
2.更新数据(UPDATE)
UPDATE 表名 SET 列名=值，列名=值 WHERE 条件
1.mysql 的 update 的特点
- 更新的表不能在 set 和 where 中用于子查询;
- pdate 后面可以做任意的查询
更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同
Oracle:update emp3 e set e.address =
(select address from emp3 where emp_id = 1) where e.emp_id = 2;
MySQL: update emp3 e ,
(select address from emp3 where emp_id = 1)t
set e.address = t.address where e.emp_id =2;
update emp3 e set e.address =
(select t1.address from (select emp_id, add ress from emp3)t1 where t1.emp_id = 1 )
where e.emp_id = 2;
3.删除数据(DELETE)
1.使用 DELETE 子句
DELETE FROM 表名 WHERE 条件
删除 emp3 表中 emp_id 为 1 的雇员信息。
delete from emp3 where emp_id = 1
2.使用 TRUNCATE 清空表
TRUNCATE TABLE 表名
删除 emp3 表中的所有数据
truncate table emp3;
3.DELETE 与 TRUNCATE 区别
- truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；
- truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因
- truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加
4.MySQL 中的事务处理
在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句就开启了事物，并且提交了事务
1.关闭 MySQL 的事务自动提交
START TRANSACTION
DML....
COMMIT|ROLLBACK
向 emp3 表中添加一条数据，要求手动提交事务。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into emp3 values(default,"oldlu",default,default);
Query OK, 1 row affected (0.00 sec)
mysql> commit; Query OK,
0 rows affected (0.01 sec)
转载：https://blog.csdn.net/qq_45017999/article/details/106112548