MySQL入门2 约束与多表操作

一、数据的查询

顺序查询
聚合函数
分页查询
分组查询

1.1 order by 字句

 -- order by的作用：
    1) 对查询出来的结果进行排序，不会影响到表中数据的实际顺序
    2)升序 asc (ascend) 默认可以省略
    3)降序 desc (descend)
    4)必须出现在select查询语句的最后面

2.实例：

-- 1) 对数学成绩从小到大排序后输出。
    select * from student order by math;

    -- 2) 对总分按从高到低的顺序输出
    select *,(math+english) as 总分 from student order by 总分 desc;

    -- 3) 姓白的学生成绩从小到大排序输出
    select *,(math+english) as 总分 from student where name like '白%' order by 总分;
    **-- 4) 按数学的降序排序，对成绩相等的学生，再对英语升序排序**
    select * from student order by math desc,english asc;

**
1.2 聚合函数（5个）
聚合函数在所有的关系型数据库中通用的，mysql中聚合函数有5个。聚合函数主要是用来实现数据统计的功能。
**

-- 聚合函数

-- 1. 需求：查询所有学生english的总分
    select sum(english) as 英语总分 from student;

-- 2. 需求：查询所有学生english的平均分
    select avg(english) as 英语平均分 from student;

-- 3. 需求：查询最高的english分数(*as可以省略*)
    select max(english) 英语最高分 from student;

-- 4. 需求：查询最低的english分数
    select min(english) 英语最低分 from student;
-- 5. 需求：一共几个学生
    select count(*) 人数 from student;

-- 指定某一列做为统计
    select count(id) 人数 from student;

-- 如果使用某一列，不要选择可能为null列，为null不会统计在内
    select count(gender) 人数 from student;

1.3 分页查询（limit）
1.格式： limit 起始的行数(从0开始), 返回的行数

2.实例：

-- 需求：每页显示2条数据(limit 起始的行数，每一页的行数)
        -- 显示第一页
        select * from student limit 0,2;
        -- 显示第二页
        select * from student limit 2,2;
        -- 显示第三页
        select * from student limit 4,2;

1.4 分组查询

– 1.4.1 分组查询

    -- 需求： 查询男女的数量各自多少
    select count(*) as 男 from student where gender='男';
    select count(*) as 女 from student where gender='女';


    -- 使用一条语句，按性别进行分组
    select gender 性别，count(gender) 人数 from student group by gender;

– 1.4.2 分组筛选

    -- 需求： 查询男女的数量各自多少
    select count(*) as 男 from student where gender='男';
    select count(*) as 女 from student where gender='女';

    -- 使用一条语句，按性别进行分组
    select  gender as 性别, count(gender) as 人数 from student group by gender;

    -- 1. 按地区进行分组统计
    select city,count(city) from student group by city;

    -- 2. 在结果的基础上进行过滤,having 过滤的条件，这里不能使用Where
    select city,count(city) from student group by city having count(city)>=2;

二、数据库的备份与恢复

2.1 通过命令行方式：
    -- 2.1.1 备份数据库
        语法格式：mysqldump –u用户名 –p密码 数据库名 > 文件名
        mysqldump -uroot -proot sql_text > d:/text.sql

    -- 2.1.2 还原数据库
        语法格式：mysql –u用户名 –p密码 数据库名 < 文件名
        mysql -uroot -proot sql_text < d:/text.sql  

2.2 通过SQLyog工具
**
导出数据库
**

**
导入数据库
**

**

三、数据的约束

**
3.1 数据约束概述

1. 什么是数据约束
   1) 数据类型的作用： 定义整数，定义字符串，在一定的程度上约束数据的正确性。
   2) 数据约束的作用： 保证插入到数据库中的数据是正确的。

2. 约束种类
   1) 主键、默认、非空、唯一、外键、检查约束(在mysql中不支持)
   2) 约束一般在建表的时候创建

**

3.2 默认值约束 default

**

1. 什么是默认约束：如果一个列中没有输入任何的数据，则会自动添加一个默认的约束。

2. 示例：创建一个学生表 s1，字段：(编号，姓名，地址（默认值是：广州)），插入2条记录，地址使用默认值。

3. 插入默认值的写法：default

create table s1 (
    id int,
    name varchar(20),
    address varchar(50) default '广州'
);

select * from s1;

-- 没有这一列的值，使用默认值
insert into s1 (id,name) values (1,'张三');

-- Column count doesn't match value count at row 1
insert into s1 values (2,'李四', default);

insert into s1 values (3,'李白', '深圳');

insert into s1 values (4,'王五', null);

3.3 非空约束：not null

1. 非空约束：not null 表示这一列必须要有数据，不能为null

2. 示例：创建表学生表s2，字段(id，name, gender)，其中姓名不能为null

 create table s2 (
    id int,
    name varchar(20) not null,   -- 非空约束
    gender char(1)
)

desc s2;    -- 查看表s2

insert into s2 values (1,'张三', '男');

select * from s2;  -- 查看表s2的所有

-- Column 'name' cannot be null
insert into s2 values (2,null, '男');

3.4 唯一约束：unique

1. 唯一：表中的某一列不能出现重复的数据

2. 示例：创建学生表s3，列(id,name)，学生姓名这一列设置成唯一约束，即不能出现同名的学生。

 -- 创建学生表s3，列(id,name)，学生姓名这一列设置成唯一约束，即不能出现同名的学生。
create table s3 (
    id int,
    name varchar(20) unique    -- 唯一约束
)

select * from s3;

insert into s3 values (1, '小乔');

-- Duplicate entry '小乔' for key 'name'
insert into s3 values (2, '小乔');
-- null表示没有数据
insert into s3 values (4, null);

3.5 主键约束：primary key

1. 什么是主键：几乎所有的表都应该创建主键
   1) 主键的作用：用来唯一标签每一条记录，通过主键对记录进行查询。一旦创建了主键，数据库会自动对主键进行索引，索引可以提高查询的速度。
   2) 主键的特点：唯一+非空

2. 问题：哪个字段应该作为表的主键？
   1) 使用与业务相关的列：学号、身份证号、车牌号码。不建议使用与业务有关的列。
   2) 使用与业务无关的列，主键一旦确定，不建议去修改。建议使用与业务无关的列做为主键。
   额外创建一列专门做为主键。主键是给程序员使用的，而不是给最终用户使用。

-- 主键
-- 示例：创建表学生表s4(id, name)将id做为主键
create table s4(
    id int primary key,
    name varchar(20) not null
)

insert into s4 values(1, '张飞');

select * from s4;

-- 唯一：插入重复的主键值  Duplicate entry '1' for key 'PRIMARY'
insert into s4 values (1, '关羽');

-- 非空：插入NULL的主键值   Column 'id' cannot be null
insert into s4 values (null, '李白'); 

1. 删除主键约束，一般情况下不建议删除主键

alter table s4 drop primary key;

3.6 自增长字段：AUTO_INCREMENT

1. 什么是自增长字段：
1) 特点： 表中整数类型列，可以指定一个自动增长功能。下一行在上一行的基础上自动加1。
自增长的列，必须是主键。

2). AUTO_INCREMENT
1) 作用： 让主键自动产生，不需要程序员自己添加。
2) 修改起始值，设置起始值 auto_increment=100;
3). 示例：创建学生表s5，id为整数，长4位，主键，自动增长。名字varchar(20)，唯一约束

create table s5(
    id int(4) zerofill primary key auto_increment,   -- 主键，自动增长,长4位,  zerofill 如果位数没有4位，则使用0进行填充
    name varchar(20) unique
)

drop table s5;

-- 长4位，不会影响到整数的位数，还是11位，只会影响显示的效果
desc s5;

insert into s5 (name) values ('张三'),('李四'),('王五');

select * from s5;

-- 有可能出现重复
insert into s5 values (99999,'田七');

-- 删除第2行
delete from s5 where id=2;
delete from s5 where id=0002;

-- 不再使用2这个编号 
insert into s5 (name) values ('刘六');


-- 创建表的时间指定自增长的起始值，起始值为1000
create table s6(
    id int primary key auto_increment,   -- 主键，自动增长,长4位,  zerofill 如果位数没有4位，则使用0进行填充
    name varchar(20) unique
) auto_increment = 1000;   -- 设置自动增长的起始值为1000

insert into s6 (name) values ('张三'),('李四'),('王五');

select * from s6;

2. 删除所有的记录
**
对比delete和truncate的区别
**

-- 使用delete删除所有的记录，delete不会影响自增长的值，后续编号还是使用原来的值加1
delete from s6;

-- 使用truncate删除所有的记录，自增长清0，从1开始重新编号
truncate s6;

注：自增长的情况下删除主键

-- 在自增长存在的情况下，删除主键 Incorrect table definition; there can be only one auto column and it must be defined as a key
-- mysql中规定，只能有一个自增长的列，而且这一列必须是主键
alter table s6 drop primary key;

-- 删除自动增长，改id的类型相当于去掉自增长
alter table s6 change id cid int;

desc s6;

3.7 外键约束：foreign key

1. 为什么要有外键约束？
   示例：创建一个员工表包含如下列 (ID主键自增长,ENAME,DNAME,DLOC) 插入5条记录，观察部门字段的数据。

create table employee(
    id int primary key auto_increment,
    ename varchar(20),  -- 员工名
    dname varchar(20),  -- 部门名
    dloc  varchar(30)   -- 部门所在地
)

select * from employee;

desc employee;

-- id自动产生
insert into employee values (null, '孙悟空','武装部','广州');
insert into employee values (null, '猪八戒','武装部','广州');
insert into employee values (null, '白骨精','外交部','上海');
insert into employee values (null, '唐僧','小卖部','北京');
insert into employee values (null, '蜘蛛精','外交部','上海');

drop table employee;
-- 部门信息大量重复，产生更新异常，产生删除异常，导致数据不准确

-- 解决方案：将与部门有关的信息再创建一张表

-- 创建2张表：一张员工表，一张部门表
-- 创建部门表
create table depart (
    id int primary key auto_increment,  -- 主键
    dname varchar(20),   -- 部门名字
    dloc varchar(50)   -- 部门所在地
)

-- 创建员工表
create table employee(
    id int primary key auto_increment,
    ename varchar(20),  -- 员工名字
    depart_id int   -- 所有部门的主键
)

-- 插入部门数据
insert into depart values(null, '武装部','广州'),(null, '外交部','上海'),(null, '小卖部','北京');

select * from depart;

-- 插入员工记录
insert into employee values(null, '孙悟空', 1);
insert into employee values(null, '猪八戒', 1);
insert into employee values(null, '白骨精', 2);
insert into employee values(null, '唐僧', 3);
insert into employee values(null, '蜘蛛精', 2);

select * from employee;     

-- 在逻辑上有问题的数据，没有4号部门
insert into employee values(null, '嫦娥', 4);

2 解决方法：使用外键约束，避免添加逻辑上有错误的数据

 -- 部门表主表，员工表是从表
-- 部门是一方，员工是多方，一个部门对应多个员工，两者是一对多的关系。一方是主表，多方是从表

drop table employee, depart;
-- 先创建主表，再创建从表，主表创建没有区别
-- 创建部门表
create table depart (
    id int primary key auto_increment,  -- 主键
    dname varchar(20),   -- 部门名字
    dloc varchar(50)   -- 部门所在地
)

-- 创建员工表
create table employee(
    id int primary key auto_increment,
    ename varchar(20),  -- 员工名字
    depart_id int,   -- 所有部门的主键，称为外键，对应部门表中的主键 添加外键约束
    -- 语法： foreign key (外键列) references 主表(主键)
    foreign key (depart_id) references depart(id)
)

select * from employee;

select * from depart;

-- Cannot add or update a child row: a foreign key constraint fails ，不存在4号部门
insert into employee values(null, '嫦娥', 4);

3.8 外键约束下对表记录的操作

3.8.1 插入数据
1) 需求：向部门表中插入一个新部门（行政部），插入一个新员工，分配到新部门中。
2). 插入数据的顺序： 先插入主表中的数据，再插入从表中的数据

-- 插入新的记录
-- 先插入主表中的数据，再插入从表中的数据
insert into depart values (null, '行政部','深圳');

select * from depart;
-- 再插入从表的数据
insert into employee values(null, '嫦娥', 4);

select * from employee;

3.8.2 修改数据表
1) 需求：要把部门表中的id值4，改成5，能不能直接修改呢？

-- Cannot delete or update a parent row: a foreign key constraint fails
update depart set id=5 where id=4;

-- 先修改从表中的数据，再修改主表中的数据，把外键设置为null
update employee set depart_id=null where depart_id=4;

update depart set id=5 where id=4;

-- 将员工调整回行政部
update employee set depart_id=5 where depart_id is null;

3.8.3 删除数据
1) 需求：要删除部门id等于2的某个部门

-- 要删除部门id等于2的某个部门 Cannot delete or update a parent row: a foreign key constraint fails
delete from depart where id=2;

-- 先删除2号部门所有的员工
delete from employee where depart_id=2;

-- 再删除部门
delete from depart where id=2;

3.9 级联操作
**
操作主表，从表中相应的数据也会发生变化
**
分成两种级联的操作：
级联更新：更新主表中的主键，从表中的外键也自动更新
语法：on update cascade
级联删除：
语法：on delete cascade

-- 级联操作
-- 先删除从表，再删除主表
drop table employee;
drop table depart;

-- 创建部门表
create table depart (
    id int primary key auto_increment,  -- 主键
    dname varchar(20),   -- 部门名字
    dloc varchar(50)   -- 部门所在地
)

-- 创建员工表，使用级联
create table employee(
    id int primary key auto_increment,
    ename varchar(20),  -- 员工名字
    depart_id int,   -- 所有部门的主键，称为外键，对应部门表中的主键 添加外键约束
    -- 语法： foreign key (外键列) references 主表(主键)
    foreign key (depart_id) references depart(id) on delete cascade on update cascade
)

select * from employee;
select * from depart;

-- 更新武装部1号改成99号
update depart set id=99 where id=1;

-- 删除99号部门
delete from depart where id=99;

四、多表操作（重点）

4.1 表与表之间的关系
表与表之间有三种关系：1对多、多对多、1对1

**

4.1.1 一对多(1:n)

**
常见实例：老师和学生，部门和员工，分类和产品

一对多之间关系的维护：通过外键来维护两张表之间的关系

代码：

-- 创建分类和产品表

-- 主表：分类表
create table category (
    id int primary key auto_increment,
    name varchar(20)
)


-- 从表：产品表
create table product(
    id int primary key auto_increment,
    name varchar(20),
    c_id int,  -- 外键
    foreign key (c_id) references category(id)
)

关系图：

4.1.2 多对多（n:n）

常见实例： 学生和课程

多对多之间关系的维护：通过中间表进行维护，将多对多转成了2个1对多。
代码：

-- 多对多的关系
-- 学生表
create table student(
    id int primary key,
    name varchar(20)
)

-- 课程表
create table course(
    id int primary key,
    name varchar(20)
)

-- 创建关系表
create table student_course (
   s_id int,  -- 学生编号
   c_id int,  -- 课程编号
   primary key(s_id,c_id),   -- 复合主键
   foreign key(s_id) references student(id),
   foreign key(c_id) references course(id)
)

关系图：

4.1.3 一对一（1:1）了解

常见实例：学生和简历，身份信息和护照信息
有两种建表原则：
外键唯一： 特殊的一对多的关系，将外键列设置一个唯一约束，变成了一对一的关系。
外键是主键：从表主键又是外键

代码：

-- 一对一的关系
create table stu (
    id int primary key,
    name varchar(20)
)

drop table resume;

-- 创建简历表
create table resume(
    id int primary key,  -- 主键，又是外键
    content text, 
    foreign key(id) references stu(id)
)

关系图：

**

谢谢支持，欢迎点赞和留言

---

一起进步

**