MySQL约束与设计

最新推荐文章于 2022-08-28 01:34:16 发布

忘记自我介绍

最新推荐文章于 2022-08-28 01:34:16 发布

阅读量1.8k

点赞数 2

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/vlucky_long/article/details/109565163

版权

1、DQL查询语句

1.1、排序

通过ORDER BY 子句，可以将查询的结果进行排序(排序只是显示方式，不会影响数据库数据的顺序)

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 排序字段 ASC|DESC

ASC：升序，默认值

DESC：降序

1.1.1、单列排序

就是只按某一个字段进行排序。

select * from students order by id asc;

1.1.2、组合排序

同时对多个字段进行排序，如果第一个字段相等，则按第二个字段排序，以此类推。

SELECT 字段名 FROM 表名 ORDER BY 字段1 [asc|desc],字段2 [asc|desc];

select * from students order by id asc ,age desc;

1.2、聚合函数

前面的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数是纵向查询，它是对一列的值进行计算，然后返回一个结果值，聚合函数都会忽略NULL值。

五个聚合函数

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列进行求和

语法：
SELECT 聚合函数(列名) FROM 表名;

聚合函数对于NULL的记录不会统计，但是如果需要把NULL也统计进去怎么办？

IFNULL(列名,默认值) 如果列名不为空，返回列值，如果为NULL，则返回默认值

select ifnull(age,0) from students;

select count(ifnull(age,0)) from students;

select count(age) from students;

select max(age) from students;

select min(age) from students;

select sum(age) from students;

select avg(age) from students;

select max(stuName) from students;

select min(stuName) from students;

1.3、分组

分组查询是指使用GROUP BY 语句对查询信息进行分组，相同数据作为一组。

SELECT 字段1,字段2,… FROM 表名 GROUP BY 分组字段 [HAVING 条件]

-- 根据年龄分组，查询每组的人数
select count(id),age from students group by age;

select count(id),birthday From students group by birthday;

-- 根据年龄分组，查询每组的人数，以及平均年龄>15
select count(id),age from students   group by age having avg(age)>20;

having与where的区别

名	作用
where子句	对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组 where后面不可以使用聚合函数
having子句	having子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤 having后面可以使用聚合函数

1.4、limit语句

limit是限制的意思，所以limit的作用是限制查询记录的条数

语法格式

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句] [limit 子句]

LIMIT offset,length;
offset：起始行数，从0开始计数，如果省略，默认就是0
length：返回的行数

select * from students limit 2,2;

LIMIT的使用场景：分页查询

2、数据库备份和还原

2.1、备份的应用场景

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障，比如发生意外停机或存储介质损耗。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造车的损失是无法弥补的。

2.2、备份与还原的语句

2.2.1、备份格式

DOS下，不要登录MYSQL数据库，执行：
mysqldump –u用户名 –p密码数据库>文件的路径

2.2.2、还原格式

mysql中的命令，需要登录后才可以操作
USE 数据库;
SOURCE 导入文件的路径;

备份
mysqldump -uroot -pRootbdit628@# db1>e:\db1.sql
还原，需要登录数据库
use db1;
source e:\db1.sql

3、数据库表的约束

3.1、约束概述

3.1.1、约束作用

对表中的数据进行限制。保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据无法插入到表中。约束在创建表的时候添加比较合适。

3.1.2、约束分类

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check

3.2、主键约束

3.2.1、主键作用

用来唯一标识数据库中的每一条记录。

3.2.2、那个字段应该做为表的主键

通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。
如：身份证，学号等都不建议做成主键

3.2.3、创建主键

主键关键字：primary key

主键的特点：非空、唯一

创建主键的方式：

1、在创建表的时候给字段添加主键

字段名字段类型 primary key

2、在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名)

-- 给已存在的表中的字段添加主键
alter table students add primary key(id);

create table teachers(
 id int primary key,
 name varchar(20)
);

insert into teachers(id,name) values(1001,'张三');

3.2.4、删除主键约束

alter table 表名 drop primary key;

3.2.5、主键自增

主键如果让我们自己添加很有可能重复，我们希望每次插入新纪录时，数据库自动生成主键字段的值。

在mysql数据库中，可以通过AUTO_INCREMENT在创建表的时候指定主键自增。AUTO_INCREMENT默认值是1

create table teachers(
 id int primary key auto_increment,
 name varchar(20)
);

3.2.6、修改AUTO_INCREMENT的默认值

创建表时指定起始值：

create table teachers(
 id int primary key auto_increment,
 name varchar(20)
)AUTO_INCREMENT=起始值;

创建好以后修改起始值：
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

3.2.7 、DELETE和TRUNCATE对自增的影响

DELETE,删除所有的记录之后，自增没有影响

TRUNCATE,删除以后，自增又重新开始

3.3、唯一约束

就是表中某一列不能出现重复的值

唯一约束的基本格式

字段名字段类型 UNIQUE

-- 唯一约束
create table teachers(
 id int primary key auto_increment,
 name varchar(20) unique
)auto_increment=1001;
insert into teachers(name) values('张三');
insert into teachers(name) values('李四');

3.4、非空约束

就是某一列不能为null

3.4.1、基本语法

字段名字段类型 NOT NULL

create table teachers(
 id int primary key auto_increment,
 name varchar(20) unique,
 age int not null
)auto_increment=1001;
insert into teachers(name) values('张三');

3.4.2 、默认值

字段名字段类型 DEFAULT 默认值;

create table teachers(
 id int primary key auto_increment,
 name varchar(20) unique,
 age int not null default 0
)auto_increment=1001;
insert into teachers(name) values('张三');

如果一个字段设置了非空与唯一约束，该字段与主键的区别？

1、主键在一个表中，只能有一个，不能出现多个主键，主键可以是单列，也可以是多列（复合主键）

2、自增长只能用在主键上

3.5、外键约束

3.5.1、单表的特点

创建一个员工表包含(id,name,age,dept_name,dept_location)，id主键并且自增

use db1;
CREATE TABLE emp(
 id INT PRIMARY KEY auto_increment,
 name varchar(20),
 age int,
 dept_name varchar(30),
 dept_location varchar(30)
);

INSERT INTO emp(name,age,dept_name,dept_location) values('张三',20,'开发部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('李四',21,'开发部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('王五',22,'开发部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('赵六',20,'销售部','上海');
INSERT INTO emp(name,age,dept_name,dept_location) values('田七',21,'销售部','上海');
INSERT INTO emp(name,age,dept_name,dept_location) values('小明',22,'销售部','上海');

以上数据表的缺点：数据冗余、后期还会出现增删改的问题

3.5.2、解决方案

分成两张表，分别是员工表(emp)、部门表(dept)

-- 部门表  主表
CREATE TABLE dept(
 id int primary key auto_increment,
 dept_name varchar(30),
 dept_location varchar(30)
);

-- 员工表
CREATE TABLE employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dept_id int
);

-- 添加部门
INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
INSERT INTO dept(dept_name,dept_location) values('销售部','上海');

-- 添加员工
INSERT INTO employee(name,age,dept_id) values('张三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);

select * from employee;

问题：当我们在employee的dept_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门。实际应用中不能出现这种情况，employee的dept_id中的数据只能是dept表中存在的id。

目标：需要约束employee中的dept_id只能是dept表中已经存在的id

解决方式：使用外键约束

3.5.3、什么是外键约束

什么是外键：在从表中与主表主键对应的那一列，如员工表中的dept_id

主表：用来约束别人的表

从表：被别人约束的表

3.5.4、添加外键约束

新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

use db1;
-- 部门表  主表
CREATE TABLE dept(
 id int primary key auto_increment,
 dept_name varchar(30),
 dept_location varchar(30)
);

-- 员工表
CREATE TABLE employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dept_id int,
 constraint dept_fk_emp foreign key(dept_id) references dept(id)
);

-- 添加部门
INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
INSERT INTO dept(dept_name,dept_location) values('销售部','上海');

-- 添加员工
INSERT INTO employee(name,age,dept_id) values('张三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
INSERT INTO employee(name,age,dept_id) values('小红',22,5);

select * from employee;

3.5.5、删除外键约束

ALTER TABLE 从表 drop foreign key 外键约束名称;

-- 删除employee表的外键约束
alter table employee drop foreign key dept_fk_emp;

-- 表存在的情况下添加外键约束
alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);

3.5.6、外键的级联

出现新的问题

-- 要把部门表中的id值为2，改成5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update dept set id=5 where id=2;

-- 要删除部门id等于1的部门，能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from dept where id=1;

什么是级联操作：
在修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作

级联操作	说明
ON UPDATE CASCADE	级联更新，只能是创建表的时候创建级联关系，更新主表中的主键，从表中的外键列也自动同步更新
ON DELETE CASCADE	级联删除

use db1;
-- 部门表  主表
CREATE TABLE dept(
 id int primary key auto_increment,
 dept_name varchar(30),
 dept_location varchar(30)
);

-- 员工表
CREATE TABLE employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dept_id int,
 constraint dept_fk_emp foreign key(dept_id) references dept(id) on update cascade on delete cascade
);

-- 添加部门
INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
INSERT INTO dept(dept_name,dept_location) values('销售部','上海');

-- 添加员工
INSERT INTO employee(name,age,dept_id) values('张三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
INSERT INTO employee(name,age,dept_id) values('小红',22,5);
delete from employee where name='小红';
select * from employee;

-- 删除employee表的外键约束
alter table employee drop foreign key emp_deptid_fk;

-- 表存在的情况下添加外键约束
alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);

select * from employee;

select * from dept;

-- 要把部门表中的id值为2，改成5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update dept set id=5 where id=2;

-- 要删除部门id等于1的部门，能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from dept where id=1;