一、如何设计表****关系

自然界中的关系通过数据对应的模式进行分析之后，得出结果：可以大致分为三种，一对一，一对多（多对一），多对多

一对多

一对多与多对一是一个概念，指的是一个实体的某个数据与另外一个实体的多个数据有关联关系。

班级表（一表）

名称	教室	总人数	学科
PHP141115	A814	53	PHP
PHP140925	A806	55	PHP

学生表（多表）

学号	姓名	性别	年龄
0001	何权森	男	22
0002	朱红林	男	保密
0003	詹曼雪	女	保密
0004	刘星	男	28

将实体间的多对一的关系进行维护：在“多”表中，增加一个字段能够指向“一”表中，唯一字段。

学号	姓名	性别	年龄	班级名称
0001	何权森	男	22	PHP141115
0002	朱红林	男	保密	PHP141115
0003	詹曼雪	女	保密	PHP140925
0004	刘星	男	28	PHP140925

多对多

一个实体的数据对应另外一个实体的多个数据，另外实体的数据也同样对应当前实体的多个数据。

教师表（多）

姓名	性别	年龄
犬夜叉	男	300
戈薇	女	18

班级表（多）

名称	教室	总人数	学科
PHP141115	A814	53	PHP
PHP140925	A806	55	PHP

犬夜叉：教过PHP141115和PHP140925
PHP141115：被犬夜叉和戈薇教过
解决方案：创建一个中间表，专门用来维护多表之间的对应关系，通常是能够唯一标识出数据的字段（主键）

image.png

一对一

一个实体的数据对应另外一个实体的数据，一条对一条

学生表（内部关系）

姓名	性别	年龄	身高	政治面貌	家庭住址	紧急联系人	紧急联系人方式	婚姻情况
张无忌	男	20	175	党员	冰火岛	金毛狮王	吼	未婚
周芷若	女	18	168	共产党	峨眉山	灭绝师太	飞鸽传书	未婚
赵敏	女	18	168	国名党	蒙古	王爷	号角	未婚
宋青书	男	22	178	党员	武当山	周芷若	吹	未婚

为了提升操作的效率会主动的将一张表进行拆分，拆分成两张表：一张经常用于操作，另外一张很少操作。

常用信息

姓名	性别	年龄	身高
张无忌	男	20	175
周芷若	女	18	168
赵敏	女	18	168
宋青书	男	22	178

不常用信息

姓名	政治面貌	家庭住址	紧急联系人	紧急联系人方式	婚姻情况
张无忌	党员	冰火岛	金毛狮王	吼	未婚
周芷若	共产党	峨眉山	灭绝师太	飞鸽传书	未婚
赵敏	国名党	蒙古	王爷	号角	未婚
宋青书	党员	武当山	周芷若	吹	未婚

二、****查询数据

基本语法
select 字段列表/* from 表名 where条件;
完整语法
select 选择模式 表达式[字段列表(别名)/*] [from 子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]
建立班级表与学生表
班级表：

 create table  if not exists class(
  c_id smallint unsigned  primary key auto_increment,
  class_name varchar(30)
);

学生表：

create table  if not exists student1(
  stu_id smallint unsigned primary key auto_increment,
  stu_name varchar(30),
  age tinyint,
  class_id smallint  
);

create table  if not exists student(
  id smallint unsigned primary key auto_increment,
  name varchar(30),
  age tinyint,
  c_id smallint  
);

分别插入数据：

 insert into class values(null,'PHP');     1
 insert into class values(null,'JAVE');     2
 insert into class values(null,'HTML5');  3
 insert into class values(null,'python');  4
 insert into student1 values(null,'张三',28,1);
 insert into student1 values(null,'李四',20,1);
 insert into student1 values(null,'王五',38,2);
 insert into student1 values(null,'张三丰',98,2);
 insert into student1 values(null,'张无忌',18,2);
 insert into student1 values(null,'张国荣',28,3);
 insert into student1 values(null,'谭咏麟',28,3);
 insert into student1 values(null,'张学友',28,4);
 insert into student1 values(null,'刘德华',28,4);
insert into student values(null,'张三',28,1);
 insert into student values(null,'李四',20,1);
 insert into student values(null,'王五',38,2);
 insert into student values(null,'张三丰',98,2);
 insert into student values(null,'张无忌',18,2);

选择模式

选择模式指的是当select查到数据之后，对数据的处理方式：查询全部（默认的all），去重（去掉重复的：记录重复，distinct）

image.png

表达式

select指定的要返回数据形式
通常要查询的数据，都是字段列表或者*（全部字段），有时候会因为同时查询多个表而出现字段重名的问题。需要使用字段别名来决绝问题。
语法：字段 [as] 别名

image.png

from子句

from子句指 的是数据源
from后面必须接数据源，数据源不一定是一张表

1. from后面可以跟多张表，使用逗号分隔

   
   image.png
   

   形成笛卡尔积，特点：字段上进行数目相加，数据形成数目的相成，尽量避免

2. 表别名

   
   image.png

3. 数据源不一定是一张已经存在的表，需要的只是一个二维表的结构。from后面可以跟一个子查询。

   
   image.png

where子句

where子句用于筛选条件，对数据（记录）进行逐行判断，返回是一个类似布尔的结果。
Where子句在数据加入到内存中之前就进行了数据的筛选
where判断：返回的结果是布尔类型
比较运算：=，>，>=，<，<=，between 左边较小 and 较大，in，is null/is not null
逻辑运算：&&and和||or，用于多条件联合判断
模糊匹配：like ‘patter’，匹配符：%和_
% 匹配所有
_ 匹配单个，如果要匹配多个的话，使用多个’_’

order by子句

显示数据的一种样式（没有对数据进行筛选）
排序子句，对对应的字段进行排序。
语法
order by 字段 [asc|desc];

limit子句

limit的基本使用用于限制数据的访问量。
limit标准使用语法
limit offset,length;
从指定位置（offset）开始，获取对应长度（length）条记录
limit的应用：分页
分页必须知道的条件：页码，每页显示的数据长度
顺序： where子句 => order by 子句=> limit 子句

image.png

group by分组查询（重点）

1、使用group by对class_id进行分组
select * from student group by class_id;

image.png

2、group by应用场景
特别说明：在SQL语句中，我们并不会直接对某个字段进行分组操作，这样是没有意义的。在实际项目开发中，我们的group by分组主要是结合统计函数进行使用的。
常用统计函数：
count(字段) ：统计总记录数
max() ：统计最大值
min() ：统计最小值
sum() ：求和
avg() ：求平均值
例如：统计每个班级分别有多少个学生（按班级进行统计group by class_id）

image.png
统计每个班级中年龄的最大值和最小值分别是多少
image.png
求每个班级的年龄总和、平均年龄
image.png

having****子句

在SQL语句中的，我们可以使用where子句进行条件判断。
① 其实在SQL语句还有一个having子句，其也可以进行条件判断，几乎所有使用where语句的地方都可以使用having进行替换。
1）使用where子句获取年龄大于18岁的学生信息

image.png

2）使用having子句获取年龄大于18岁的学生信息

image.png
② 虽然where子句与having子句都是用于进行条件判断，但是where是发生数据查询在进入内存之前，而having子句是发生在进入内存之后执行的。所以两者的作用点不同。
在实际项目开发中，where子句主要用于进行条件判断，而having子句主要用于对数据进行筛选操作。
例如：统计班级总人数大于2的班级信息
① 对所有班级进行分组进行人数的统计
image.png

② 对分组后的数据进行筛选（筛选总人数大于2的班级信息）
where子句（错误演示）：

image.png
having子句（正确演示）：
image.png

三****、****多表****联合查询

1、联合****查询****(union)

所谓的联合查询就是把两个或多个表的查询结果进行合并操作。基本语法：
select */字段 from 数据表
union [all | distinct]
select */字段 from 数据表
union all ：对查询结果进行合并，保留所有的查询结果，不去重
union distinct ：对查询结果进行合并，去除重复的查询结果（去重），默认去重
示例代码：

image.png
image.png

特别说明：使用union联合查询必须有一个前提：查询的结果的字段数量必须一致，否则会产生以上错误。

2****、union联合****查询的应用场景

1）如果在select查询语句中，需要分条件查询，但是其如果写在一条select语句中会出现语句冲突，这个时候就可以使用union进行合并操作。
例如：编写SQL语句，要求：
对于class_id = 1中的学员信息对age进行升序排列
且对于class_id = 4中的学员信息对age进行降序排列

image.png

使用union进行合并操作
默认情况下union不能结合order进行使用。
解决方案：如果在union合并查询时出现order by，必须对每一个select语句用圆括号括起来。通过圆括号可以解决union与order by的结合问题。但是运行后发现，order by失效了。这是因为，默认情况下，union与order by结合，其order by会失效。如果想让order by生效必须结合limit使用，否则order by不会生效。

image.png
image.png
2）使用union合并解决分表后获取所有数据
在实际项目开发中，我们的数据表所存储的记录总数可能在千万级以上（1个亿）。如果这些数据都放在一个表中，其数据查询时要查询1亿次。为了达到优化的问题，我们可以创建多个结构相同的表，把1亿条假设分为10个表，每个表只需要存储1000万条数据。这样在数据检索时，更加高效。我们把这种就称之为“物理分表”。在读取所有表信息时，我们还可以通过union进行合并操作。

四****、SQL中的多表****(有关系两者，主要是多对一)****连接****查询

1、什么****是多表连接查询

所谓的多表连接查询，就是拿一个表中的数据与另外一个或多个表中的数据进行匹配，如果有与之匹配的结果，则返回。反之，则自动忽略。
在SQL语句中，查询一共分为三大类：内连接查询、外连接查询、自然连接查询

3****、为****数据表其别名

基本语法：
select 别名.* from 数据表1 （空格+别名 | as别名）

4****、****内****连接查询（内连接）

基本语法：
select 数据源1.字段，数据源2.字段 from 数据源1 【inner】 join 数据源2 on 查询条件;
主要功能：从其中一个表中读取数据，然后到另外一个表中进行匹配，如果条件成立，则返回该条记录。不匹配数据不返回

image.png

5****、左右****外链接查询（外链接查询）

有两张表：左表与右表，基本语法：
左外链接查询：select 左表.字段，右表.字段 from 左表 left join 右表 on 匹配条件;
主要功能：
① 以左表作为主表，读取所有记录信息
② 拿主表中的每一条记录与右表中的记录进行匹配，如果匹配成功，则返回关联信息，反之则返回null。

image.png

右外链接查询：select 左表.字段，右表.字段 from 左表 right join 右表 on 匹配条件;
主要功能：
① 以右表作为主表，读取所有记录信息
② 拿主表中的每一条记录与左表中的记录进行匹配，如果匹配成功，则返回关联信息，反之则返回null。

image.png

五、备份恢复数据库（命令的方式）

备份数据库：

mysqldump ****-h服务器地址 -u登录名 -p**** 要备份的数据库名 > 要保存为的文件（带有路径）

image.png
备份的结果：
image.png
说明：数据库需要每天利用定时脚本进行定时定点的进行数据备份，一般选择凌晨访问量少的时候进行

恢复数据库：

将刚才的数据恢复到另一个数据库中:

mysql -h服务器地址 -u登录名 -P端口号 -p 数据库名****【****事先去创建****】****<**** 带有路径的****文件名****（****已经存在****）

image.png
结果：
image.png
扩展：仅适用于学习阶段
数据库没有及时备份，原数据库故障，需要进行数据迁移时。
image.png
1、把要迁移的数据的存储引擎改为myisam,使结构和数据实现分离，这样我们进行迁移时，结构和数据都和ibdata1进行了分离
2、把迁移的文件目录放入到新的数据库的data目录下即可

六、数据控制语言（DCL）

问题描述：
在实际开发中，有多人进行开发，此时每个开发者都要连接数据库，每个开发者都是在自己的电脑上开发的，即拥有不同的IP地址，按照实际，是无法连接到数据库服务器

image.png

解决方案：
创建多个用户，允许特定的IP地址进行访问，并且要进行权限控制。
root用户名是超级管理员，所以一般不会给开发超级管理员的权限
数据控制，其实就是“分配权限”——就涉及到用户。
则主要就是2个问题：
用户管理： 创建一个用户名和一个密码
权限分配：
在mysql中，权限是系统内定的一些“名词”（单词），大约３０个，每个权限表示“可以做什么工作”。　
则分配权限就是相当于让某个用户可以做哪些工作。
主要权限如下：

image.png

MySQL中的用户

mysql中的用户信息都存储在系统数据库mysql的user表中：

创建用户

语法形式：
create user ‘用户名’@’允许其登录的地址’ identified by ‘密码’;
说明：
1，创建的用户需同时指定该用户可以在哪个地址进行登录。
其中“%”代表“任何地址”。
2，用户创建之后，自动在mysql的user表中添加了一条记录，但该用户还没有权限。

image.png

删除用户

drop user ‘用户名’@’允许其登录的地址’；
没有权限：

image.png

有权限：

image.png

权限分配

增加权限：

简短语法：
用户已经创建好了，直接分配权限
grant show databases,select,update,delete,insert on . to ‘lau’@’localhost’
使用root账户进行权限分配：

image.png
image.png
image.png
登陆lau用户：
image.png
完整语法：
分配权限的同时也创建了用户
grant 权限名1，权限名2， .... on 数据库名．对象名　to 用户名@允许其登录的地址 identified by 密码;
给特定的权限grant select,delete on school.student to ‘admin’@’192.168.2.24’ indentified by ‘123’ image.png

** 给所有的权限（超级管理员的权限）**

grant all privileges on . to ‘root’@’%’ indentified by ‘123’

image.png
说明：
1权限名就是：’select’, ‘update’, ‘delete’,等等。其中ALL 表示“所有权限”，或all privileges也一样
2对象名：就是一个数据库中“装”的东西，表是最常见的，也可以是视图，存储过程，存储函数等。
其中：．表示所有数据中的所有对象
某数据库名．*表示该数据库中的所有对象——这个商业上常用。
3，identified by ‘密码’用于给一个用户在此时修改密码，不写也可以，那就不修改密码。
4，但同时该语句也可以创建用户（如果不存在），但此时identified by ‘密码’必须写。
强调：1、在实际开发中，尽量不要使用root账号进行操作
2、分配用户权限时尽量限制在特定的库、特定的表
3、权限尽量分配合理，不要分配其结构方面权限。
经常犯的错误：买了服务器之后，如果你的项目的服务器和数据库服务器不一致，如果你想项目服务器连接数据库服务器，请记住一定要给项目服务器分配特定的用户并且授权。
删除权限
remove 权限名1，权限名2， .... on 数据库名.表名　from ‘用户名’@’允许其登录的地址’ ;
表示从某个用户身上“取消”某些权限（也许还保留了其他权限）。