东莞市网站建设哪家好,域名推荐,铜陵app网站做营销招聘,织梦网站更改网站的导航文章目录mysql高级1#xff0c;约束1.1 概念1.2 分类1.3 非空约束1.4 唯一约束1.5 主键约束1.6 默认约束1.7 约束练习1.8 外键约束1.8.1 概述1.8.2 语法1.8.3 练习2#xff0c;数据库设计2.1 数据库设计简介2.2 表关系(一对多)mysql高级
今日目标 掌握约束的使用 掌握表关系…
文章目录mysql高级1约束1.1 概念1.2 分类1.3 非空约束1.4 唯一约束1.5 主键约束1.6 默认约束1.7 约束练习1.8 外键约束1.8.1 概述1.8.2 语法1.8.3 练习2数据库设计2.1 数据库设计简介2.2 表关系(一对多)mysql高级
今日目标 掌握约束的使用 掌握表关系及建表原则 重点掌握多表查询操作 掌握事务操作 1约束 上面表中可以看到表中数据存在一些问题 id 列一般是用标示数据的唯一性的而上述表中的id为1的有三条数据并且 马花疼 没有id进行标示 柳白 这条数据的age列的数据是3000而人也不可能活到3000岁 马运 这条数据的math数学成绩是-5而数学学得再不好也不可能出现负分 柳青 这条数据的english列英文成绩值为null而成绩即使没考也得是0分
针对上述数据问题我们就可以从数据库层面在添加数据的时候进行限制这个就是约束。
1.1 概念 约束是作用于表中列上的规则用于限制加入表的数据 例如我们可以给id列加约束让其值不能重复不能为null值。 约束的存在保证了数据库中数据的正确性、有效性和完整性 添加约束可以在添加数据的时候就限制不正确的数据年龄是3000数学成绩是-5分这样无效的数据继而保障数据的完整性。
1.2 分类
非空约束 关键字是 NOT NULL
保证列中所有的数据不能有null值。
例如id列在添加 马花疼 这条数据时就不能添加成功。 唯一约束关键字是 UNIQUE 保证列中所有数据各不相同。 例如id列中三条数据的值都是1这样的数据在添加时是绝对不允许的。 主键约束 关键字是 PRIMARY KEY
主键是一行数据的唯一标识要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。
例如上图表中id就可以作为主键来标识每条数据。那么这样就要求数据中id的值不能重复不能为null值。 检查约束 关键字是 CHECK 保证列中的值满足某一条件。 例如我们可以给age列添加一个范围最低年龄可以设置为1最大年龄就可以设置为300这样的数据才更合理些。 注意MySQL不支持(check)检查约束。 这样是不是就没办法保证年龄在指定的范围内了从数据库层面不能保证以后可以在java代码中进行限制一样也可以实现要求。 默认约束 关键字是 DEFAULT 保存数据时未指定值则采用默认值。 例如我们在给english列添加该约束指定默认值是0这样在添加数据时没有指定具体值时就会采用默认给定的0。 外键约束 关键字是 FOREIGN KEY 外键用来让两个表的数据之间建立链接保证数据的一致性和完整性。 外键约束现在可能还不太好理解后面我们会重点进行讲解。
1.3 非空约束 概念 非空约束用于保证列中所有数据不能有NULL值 语法 添加约束 -- 创建表时添加非空约束
CREATE TABLE 表名(列名 数据类型 NOT NULL,…
); -- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;删除约束 ALTER TABLE 表名 MODIFY 字段名 数据类型;1.4 唯一约束 概念 唯一约束用于保证列中所有数据各不相同 语法 添加约束 -- 创建表时添加唯一约束
CREATE TABLE 表名(列名 数据类型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 当不指定值时自动增长…
);
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [约束名称] UNIQUE(列名)
); -- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;删除约束 ALTER TABLE 表名 DROP INDEX 字段名;1.5 主键约束 概念 主键是一行数据的唯一标识要求非空且唯一 一张表只能有一个主键 语法 添加约束 -- 创建表时添加主键约束
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],…
);
CREATE TABLE 表名(列名 数据类型,[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
); -- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);删除约束 ALTER TABLE 表名 DROP PRIMARY KEY;1.6 默认约束 概念 保存数据时未指定值则采用默认值 语法 添加约束 -- 创建表时添加默认约束
CREATE TABLE 表名(列名 数据类型 DEFAULT 默认值,…
); -- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;删除约束 ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;1.7 约束练习
根据需求为表添加合适的约束
-- 员工表
CREATE TABLE emp (id INT, -- 员工id主键且自增长ename VARCHAR(50), -- 员工姓名非空且唯一joindate DATE, -- 入职日期非空salary DOUBLE(7,2), -- 工资非空bonus DOUBLE(7,2) -- 奖金如果没有将近默认为0
);上面一定给出了具体的要求我们可以根据要求创建这张表并为每一列添加对应的约束。建表语句如下
drop table if exists emp;create table emp(id INT primary key , -- 员工id主键且自增长 (自增先不管了)ename VARCHAR(50) not null unique, -- 员工姓名非空且唯一 (中间不用and连接 空格直接写就行了)joindate DATE not null, -- 入职日期非空salary DOUBLE(7,2) not null, -- 工资非空bonus DOUBLE(7,2) default 0 -- 奖金如果没有将近默认为0
);通过上面语句可以创建带有约束的 emp 表约束能不能发挥作用呢。接下来我们一一进行验证先添加一条没有问题的数据
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,张三,1999-11-11,8800,5000);验证主键约束非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,张三,1999-11-11,8800,5000);执行结果如下 从上面的结果可以看到字段 id 不能为null。那我们重新添加一条数据如下
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,张三,1999-11-11,8800,5000);执行结果如下 从上面结果可以看到1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据
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);执行结果如下 从上面结果可以看到ename 字段的非空约束生效了。
验证唯一约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,李四,1999-11-11,8800,5000);执行结果如下 从上面结果可以看到ename 字段的唯一约束生效了。
验证默认约束
INSERT INTO emp(id,ename,joindate,salary) values(3,王五,1999-11-11,8800);没有插入bonus列的值
执行完上面语句后查询表中数据如下图可以看到王五这条数据的bonus列就有了默认值0。 注意默认约束只有在不给值时才会采用默认值。如果给了null那值就是null值。
如下
INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,赵六,1999-11-11,8800,null);执行完上面语句后查询表中数据如下图可以看到赵六这条数据的bonus列的值是null。 验证自动增长 auto_increment 当列是数字类型 并且唯一约束 才可以用auto_increment来修饰
重新创建 emp 表并给id列添加自动增长
drop table if exists emp;
-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY auto_increment, -- 员工id主键且自增长ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名非空并且唯一joindate DATE NOT NULL , -- 入职日期非空salary DOUBLE(7,2) NOT NULL , -- 工资非空bonus DOUBLE(7,2) DEFAULT 0 -- 奖金如果没有奖金默认为0
);接下来给emp添加数据分别验证不给id列添加值以及给id列添加null值id列的值会不会自动增长
INSERT INTO emp(ename,joindate,salary,bonus) values(赵六,1999-11-11,8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,赵六2,1999-11-11,8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,赵六3,1999-11-11,8800,null);1.8 外键约束
1.8.1 概述
外键用来让两个表的数据之间建立链接保证数据的一致性和完整性。
如何理解上面的概念呢如下图有两张表员工表和部门表 员工表中的dep_id字段是部门表的id字段关联也就是说1号学生张三属于1号部门研发部的员工。现在我要删除1号部门就会出现错误的数据员工表中属于1号部门的数据。而我们上面说的两张表的关系只是我们认为它们有关系此时需要通过外键让这两张表产生数据库层面的关系这样你要删除部门表中的1号部门的数据将无法删除。
1.8.2 语法
添加外键约束
-- 创建表时添加外键约束
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
); -- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;1.8.3 练习
根据上述语法创建员工表和部门表并添加上外键约束
-- 删除表
drop table if exists emp;
drop table if exists dept;-- 部门表 (先建立)
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,-- 添加外键 dep_id, 关联 dept 表的id主键constraint fk_emp_deptID foreign key(dep_id) references dept(id));添加数据
-- 给部门表添加数据 添加2个部门
insert into dept(dep_name,addr) values(研发部,广州),(销售部,深圳);-- 给员工表添加数据 dpt_id就是所在部门编号
insert into emp(name,age,dep_id) values
(张三,20,1),
(李四,20,1),
(王五,20,1),
(赵六,20,2),
(孙七,22,2),
(周八,18,2);此时删除 研发部 这条数据会发现无法删除。
删除外键
alter table emp drop FOREIGN key fk_emp_dept;重新添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);dept部门表是主表 emp员工表是从表
Tips 2数据库设计
2.1 数据库设计简介 软件的研发步骤 数据库设计概念 数据库设计就是根据业务系统的具体需求结合我们所选用的DBMS为这个业务系统构造出最优的数据存储模型。建立数据库中的表结构以及表与表之间的关联关系的过程。有哪些表表里有哪些字段表和表之间有什么关系 数据库设计的步骤 需求分析数据是什么? 数据具有哪些属性? 数据与属性的特点是什么 逻辑分析通过ER图对数据库进行逻辑建模不需要考虑我们所选用的数据库管理系统 如下图就是ER(Entity/Relation)图 物理设计根据数据库自身的特点把逻辑设计转换为物理设计 维护设计1.对新的需求进行建表2.表优化 表关系 一对一 如用户 和 用户详情一对一关系多用于表拆分将一个实体中经常使用的字段放一张表不经常使用的字段放另一张表用于提升查询性能 上图左边是用户的详细信息而我们真正在展示用户信息时最长用的则是上图右边红框所示所以我们会将详细信息查分成两周那个表。 一对多 如部门 和 员工 一个部门对应多个员工一个员工对应一个部门。如下图 多对多 如商品 和 订单 一个商品对应多个订单一个订单包含多个商品。
2.2 表关系(一对多) 一对多 如部门 和 员工一个部门对应多个员工一个员工对应一个部门。 实现方式 在多的一方建立外键指向一的一方的主键 案例 我们还是以 员工表 和 部门表 举例: 经过分析发现员工表属于多的一方而部门表属于一的一方此时我们会在员工表中添加一列dep_id指向于部门表的主键id 建表语句如下
