1、约束
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment,
ename VARCHAR(50) NOT NULL UNIQUE,
joindate DATE NOT NULL,
salary DOUBLE(7, 2) NULL,
bonus DOUBLE(7, 2) DEFAULT 0
);
INSERT INTO emp(id, ename, joindate, salary, bonus) values (1, '张三', '1999-11-11', 8800, 5000);
show databases;
show tables;
select * from emp;
INSERT INTO emp(id, ename, joindate, salary, bonus) values (null, '张三', '1999-11-11', 8800, 5000);
INSERT INTO emp(id, ename, joindate, salary, bonus) values (1, '张三', '1999-11-11', 8800, 5000);
INSERT INTO emp(id, ename, joindate, salary, bonus) values (2, '李四', '1999-11-11', 8800, 5000);
INSERT INTO emp(id, ename, joindate, salary, bonus) values (3, null, '1999-11-11', 8800, 5000);
select * from emp;
INSERT INTO emp(id, ename, joindate, salary, bonus) values (3, '李四', '1999-11-11', 8800, 5000);
INSERT INTO emp(id, ename, joindate, salary) values (3, '王五', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('张三', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('李四', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('王五', '1999-11-11', 8800);
INSERT INTO emp(ename, joindate, salary) values ('赵六', '1999-11-11', 8800);
2、外键约束
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS emp;
CREATE TABLE dept (
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
CREATE TABLE emp (
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
insert into dept (dep_name, addr) values
('研发部', '广州'), ('销售部', '深圳');
insert into emp (name, age, dep_id) values
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);
select * from emp;
select * from dept;
delete from dept WHERE id = 1;
delete from emp WHERE dep_id = 1;
delete from dept WHERE id = 1;
alter table emp drop foreign key fk_emp_dept;
desc emp;
alter table emp add constraint fk_emp_dept foreign key(dep_id) REFERENCES dept(id);
3、多对多的关系
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;
CREATE TABLE tb_order (
id int primary key auto_increment,
payment double(10, 2),
payment_type TINYINT,
status TINYINT
);
CREATE TABLE tb_goods (
id int primary key auto_increment,
title varchar(100),
price double(10, 2)
);
CREATE TABLE tb_order_goods (
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
alter table tb_order_goods add constraint fk_order_id foreign key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) REFERENCES tb_goods(id);