mysql数据库3

2021-11-11 15:53:02

数据约束

默认值约束

当用户对使用默认值的字段不插入值的时候，就使用默认值。

注意:1）对默认值字段插入null是可以的。

　　2）对默认值字段可以插入非null
CREATE TABLE teacher(
   tid INT,
   tname VARCHAR(12),
   addr VARCHAR(20) DEFAULT '张店区'
)
INSERT INTO teacher VALUES(1,'小红帽','博山区');
INSERT INTO teacher(tid,tname) VALUES(2,'大灰狼');
INSERT INTO teacher VALUES(3,'外婆',NULL);
SELECT * FROM teacher
DROP TABLE teacher

非空约束

限制字段必须赋值

注意:1）非空字符必须赋值

　　2）非空字符不能赋null
CREATE TABLE teacher(
   tid INT,
   tname VARCHAR(12) NOT NULL,
   addr VARCHAR(20) DEFAULT '张店区'
)
INSERT INTO teacher VALUES(1,'小红帽','博山区');
-- Field 'tname' doesn't have a default value
INSERT INTO teacher(tid,addr) VALUES(2,'淄川区');
-- Column 'tname' cannot be null
INSERT INTO teacher VALUES(3,NULL,'临淄区');
SELECT * FROM teacher

-- 唯一约束

对字段的值不能重复

注意：1）唯一字段可以插入null

　　2）唯一字段可以插入多个null
CREATE TABLE teacher(
   tid INT,
   tname VARCHAR(12) NOT NULL,
   idcard VARCHAR(20) UNIQUE,
   addr VARCHAR(20) DEFAULT '张店区'
)
INSERT INTO teacher VALUES(1,'小红帽','370303x','淄川区');
INSERT INTO teacher VALUES(2,'小蓝帽','370303x','临淄区');
-- 允许存入null值,允许存入多个null值
INSERT INTO teacher VALUES(3,'小白帽',NULL,'博山区');
INSERT INTO teacher VALUES(4,'小黑帽',NULL,'博山区');
SELECT * FROM teacher

-- 主键约束

非空+唯一

注意：1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。

　　2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业
CREATE TABLE teacher(
   tid INT PRIMARY KEY,
   tname VARCHAR(12) NOT NULL,
   idcard VARCHAR(20) UNIQUE,
   addr VARCHAR(20) DEFAULT '张店区'
)
INSERT INTO teacher VALUES(1,'小红帽','370303x','博山区');
-- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO teacher VALUES(1,'小白帽','370303y','博山区');
-- Column 'tid' cannot be null
INSERT INTO teacher VALUES(NULL,'小白帽','370303y','博山区');

SELECT * FROM teacher
DROP TABLE teacher

-- 自增长约束（主键的基础上）

自动递增

自增长字段可以不赋值，自动递增
CREATE TABLE teacher(
   tid INT PRIMARY KEY AUTO_INCREMENT,
   tname VARCHAR(12) NOT NULL,
   idcard VARCHAR(20) UNIQUE,
   addr VARCHAR(20) DEFAULT '张店区'
)
INSERT INTO teacher(tname,idcard) VALUES('小红帽','370303x');
INSERT INTO teacher(tname,idcard) VALUES('小蓝帽','370303y');
INSERT INTO teacher(tname,idcard) VALUES('小蓝帽','370303z');
-- 清空表
DELETE FROM teacher
TRUNCATE TABLE teacher

INSERT INTO teacher VALUES(10,'小小帽','370303y','博山区');
INSERT INTO teacher(tname,idcard) VALUES('小蓝帽','370303z');

SELECT * FROM teacher

DROP TABLE teacher

-- 零填充（自增长约束的基础上）
CREATE TABLE teacher(
   tid INT(5) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
   tname VARCHAR(12) NOT NULL,
   idcard VARCHAR(20) UNIQUE,
   addr VARCHAR(20) DEFAULT '张店区'
)

INSERT INTO teacher(tname,idcard) VALUES('小红帽','370303x');
INSERT INTO teacher(tname,idcard) VALUES('小蓝帽','370303y');
INSERT INTO teacher(tname,idcard) VALUES('小蓝帽','370303z');
SELECT * FROM teacher

-- 外键约束

约束两种表的数据

出现两种表的情况：

解决数据冗余高问题：独立出一张表

如：员工表和部门表

问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！！！！！

使用外键约束：约束插入员工表的部门ID字段值

解决办法：在员工表的部门ID字段添加一个外键约束。

注意1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！

　　2）主表的参考字段通用为主键！

　　3）添加数据：先添加主表，再添加副表

　　4）修改数据：先修改副表，再修改主表

　　5）删除数据：先删除副表，再删除主表

-- 创建主表
CREATE TABLE dept(
   did INT PRIMARY KEY AUTO_INCREMENT,
   dname VARCHAR(10),
   mgr VARCHAR(10)
)
-- 创建副表
CREATE TABLE emp(
   eid INT PRIMARY KEY AUTO_INCREMENT,
   ename VARCHAR(10),
   esex CHAR(5),
   edate DATE,
   money DOUBLE,
   did INT, -- 外键
   CONSTRAINT emp_dept_fk FOREIGN KEY(did)
   REFERENCES dept(did) -- 外键约束
)
-- 添加数据(先添加主表，再添加副表)
INSERT INTO dept(dname,mgr) VALUES('开发部','tom');
INSERT INTO dept(dname,mgr) VALUES('测试部','jerry');
INSERT INTO dept(dname,mgr) VALUES('产品部','tony');

INSERT INTO emp(ename,esex,edate,money,did) VALUES('张三','男','1999-09-09',6000,1);
INSERT INTO emp(ename,esex,edate,money,did) VALUES('李四','女','1995-05-05',4500,1);
INSERT INTO emp(ename,esex,edate,money,did) VALUES('王五','男','2006-06-06',5000,2);
INSERT INTO emp(ename,esex,edate,money,did) VALUES('赵六','男','2002-02-02',3000,3);
SELECT * FROM emp
SELECT * FROM dept
-- 修改数据(先改副表，在改主表)
UPDATE emp SET did=1 WHERE did=3
UPDATE dept SET did=4 WHERE did=3
-- 删除数据(先删副标在删主表)
DELETE FROM emp WHERE did=2
DELETE FROM dept WHERE did=2

连接查询（多表查询）

连接就是指两个或2个以上的表（数据源）“连接起来成为一个数据源”。

实际上，两个表的完全的连接是这样的一个过程：

左边的表的每一行，跟右边的表的每一行，两两互相“横向对接”后所得到的所有数据行的结果。

注意：连接之后，并非形成了一个新的数据表，而只是一种“内存形态”。

-- 交叉连接
SELECT * FROM emp JOIN dept
-- 内连接
SELECT * FROM emp JOIN dept ON emp.did=dept.did
-- 查询李四的部门经理
SELECT mgr FROM emp JOIN dept ON emp.did=dept.did WHERE ename='李四'
-- 查询测试部产品部员工名称
SELECT ename FROM emp JOIN dept ON emp.did=dept.did WHERE dname='产品部'
-- 查询所有男生所属的部门信息
SELECT dname FROM emp JOIN dept ON emp.did=dept.did WHERE esex='男'
-- 左外连接
SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.did
-- 右外连接
SELECT * FROM emp RIGHT JOIN dept ON emp.did=dept.did

码农公寓

相关文章