-- 创建数据库
CREATE DATABASE test_db;
-- 使用数据库
USE test_db;
-- 外键约束
CREATE TABLE dept
(
id INT PRIMARY KEY,
name VARCHAR(20),
location VARCHAR(50)
)ENGINE=INNODB;
CREATE TABLE emp_3
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
)ENGINE=INNODB;
-- 插入信息
INSERT INTO dept VALUES(1,'小森','广大');
INSERT INTO emp_3 VALUES(2,'小明',1,8000);
-- 查看的结果
-- 非空约束
CREATE TABLE emp_4
(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dept_id INT,
salary FLOAT
);
-- 插入一条姓名null的语句
INSERT INTO emp_4 VALUES(1,'',4,8000);
-- 默认约束
CREATE TABLE emp_5
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT DEFAULT 2000
);
INSERT INTO emp_5(id,name,dept_id) VALUES(2,'你好',4);
-- 属性值自动增长约束
CREATE TABLE emp_6
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
dept_id INT,
salary FLOAT
);
INSERT INTO emp_6(name,dept_id) VALUES('你好',4);
CREATE TABLE emp_7
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT
);