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 1insert 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;    -- 查看表s2insert into s2 values (1,'张三', '男');select * from s2;  -- 查看表s2的所有-- Column 'name' cannot be nullinsert 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 nullinsert 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 ('刘六');-- 创建表的时间指定自增长的起始值，起始值为1000create table s6(    id int primary key auto_increment,   -- 主键，自动增长,长4位,  zerofill 如果位数没有4位，则使用0进行填充    name varchar(20) unique) auto_increment = 1000;   -- 设置自动增长的起始值为1000insert into s6 (name) values ('张三'),('李四'),('王五');select * from s6;

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

-- 使用delete删除所有的记录，delete不会影响自增长的值，后续编号还是使用原来的值加1delete 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 failsupdate depart set id=5 where id=4;-- 先修改从表中的数据，再修改主表中的数据，把外键设置为nullupdate 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 failsdelete 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))

关系图：

**

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

---

一起进步

**