-- 默认值约束
CREATE TABLE teacher(
tid INT,
tname VARCHAR(10),
addr VARCHAR(30) DEFAULT '山东淄博'
)
SELECT * FROM teacher
INSERT INTO teacher VALUES(1,'李老师','山东济南')
INSERT INTO teacher(tid,tname) VALUES(2,'张老师')
INSERT INTO teacher VALUES(3,'徐老师',NULL)
-- 费控约束
DROP TABLE teacher
CREATE TABLE teacher(
tid INT,
tname VARCHAR(10) NOT NULL, -- 非空元素
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher VALUES(1,'小红帽','山东青岛')
INSERT INTO teacher(tid,addr) VALUES(2,'山东枣庄')
INSERT INTO teacher VALUES(2,NULL,'山东枣庄')
-- 唯一约束
CREATE TABLE teacher(
tid INT,
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher VALUES(1,'李老师','370303xx','山东淄博')
INSERT INTO teacher(tid,tname) VALUES(2,'张老师')
INSERT INTO teacher VALUES(3,'徐老师',NULL,'山东枣庄')
-- 主键约束
CREATE TABLE teacher(
tid INT PRIMARY KEY, -- 主键约束
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher VALUES(1,'小红帽','1','山东济南')
INSERT INTO teacher VALUES(1,'大灰狼','2','山东淄博')
INSERT INTO teacher VALUES(NULL,'大灰狼','2','山东淄博')
-- 自增长约束
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT, -- 主键约束
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
INSERT INTO teacher(tname,idcard) VALUES('小红帽','1');
INSERT INTO teacher(tname,idcard) VALUES('大灰狼','2');
INSERT INTO teacher(tname,idcard) VALUES('小灰狼','3');
INSERT INTO teacher(tid,tname,idcard) VALUES(10,'大灰狼','4')
-- 清空表
DELETE FROM teacher
TRUNCATE TABLE teacher

-- 零填充
CREATE TABLE teacher(
tid INT(5) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 主键约束
tname VARCHAR(10) NOT NULL, -- 非空元素
idcard VARCHAR(20) UNIQUE, -- 唯一约束
addr VARCHAR(30) DEFAULT '山东淄博'
)
-- 创建主表
CREATE TABLE dept01(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(10),
mgr VARCHAR(10)
)
-- 创建副表
CREATE TABLE emp01(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(10),
esex CHAR(5),
did INT,
CONSTRAINT fk_emp01_dept01 FOREIGN KEY(did)
REFERENCES dept01(did) -- 外键约束
)
-- 新增数据
INSERT INTO dept01(dname,mgr) VALUES('开发部','tom');
INSERT INTO dept01(dname,mgr) VALUES('测试部','jerry');

INSERT INTO emp01(ename,esex,did) VALUES('小红帽','女',3);
INSERT INTO emp01(ename,esex,did) VALUES('大灰狼','男',3);
INSERT INTO emp01(ename,esex,did) VALUES('海绵宝宝','男',4);
SELECT * FROM dept01
SELECT * FROM emp01
-- 修改数据(先改副表，再改主表)
UPDATE emp01 SET did=2 WHERE did=1
UPDATE dept01 SET did=3 WHERE did=1
-- 删除数据(先删副表，再删主表)
DELETE FROM emp01 WHERE did=2
DELETE FROM dept01 WHERE did=2
DELETE FROM dept01
DELETE FROM emp01

-- 查询大灰狼的部门经理
-- 交叉连接
SELECT * FROM emp01 JOIN dept01
-- 内连接
SELECT * FROM emp01 JOIN dept01 ON emp01.did=dept01.did
-- 查询大灰狼的部门经理
SELECT mgr FROM emp01 JOIN dept01 ON emp01.did=dept01.did
WHERE ename='大灰狼'
-- 查询开发部所有员工的信息
SELECT * FROM emp01 JOIN dept01 ON emp01.did=dept01.did WHERE dname='开发部'
-- 查询每个部门的人数
SELECT dname,COUNT(*) FROM emp01 JOIN dept01 ON emp01.did=dept01.did GROUP BY dname
-- 多表
FROM 表1 JOIN 表2 ON 表1.字段=表2.字段 JOIN 表3 ON 表2.字段=表3.字段
-- 左外连接
INSERT INTO dept01(dname,mgr) VALUES('产品部','tony')
-- 查询所有用户信息及其订单信息
SELECT * FROM dept01 LEFT JOIN emp01 ON dept01.did=emp01.did
-- 右外连接
SELECT * FROM emp01 RIGHT JOIN dept01 ON dept01.did=emp01.did