连接查询

表与表的关系

公用的字段来关联表

一对一

一对多

多对多

• 一对一的关系

老婆和老公（法律上）

人和身份证号

• 一对多的关系

父亲和孩子

• 多对多的关系

学生和老师

连接查询的概念

什么是连接查询

【通过公用字段】把两张表进行横向的拼接就是连接查询

比如A表有3个字段，B表有5个字段，那么连接查询后的结果最多有8个字段

连接查询分类

交叉连接

[Cross] join 交差连接没有条件，这里没有公用字段

交差连接就是左边*右表

语法实现: select ….from A cross join B……….

内连接

Inner join / join

根据公用字段来进行横向连接，只返回满足条件的部分

语法 select …. From 表1 inner join 表2 on 连接的条件….

内连接：只返回满足条件 （公用字段班级id相同的）记录横向连接的结果

内连接如果不加on条件就变成了交差连接

说白了，如果有条件就是内连接，没有条件就是交差连接

左连接

左连接就是返回左表的全部数据，和满足条件的右表的数据

语法: select … from 表1 left join 表2 on条件 …….

select * from class left join student on class.id=student.class_id;

如果左边表没有满足条件的右边表数据，会自动补null，并且显示出来

右连接

右连接，返回右表全部数据，和满足条件的左表数据

右边表如果没有满足条件的左表数据，会在左边自动补充Null 并且显示出来

语法 :select … from 表1 right join 表2 on条件 …….

Select * from class right join student on class.id=student.class_id;

总结比较，内连接 左连接 右连接

内连接：只返回满足条件的数据

左连接：返回满足条件的数据和左表的所有数据 （比内连接多数据—-左边中不满足条件的记录）

右连接：返回满足条件的数据和右表的所有数据 （比内连接多数据—-右表中不满足条件的数据）

全连接

MySQL没有全连接 full join

返回的是左表全内容，和右表全部内容，满足条件的内容

左连接的结果 union 右连接的结果

课堂练习

• 谁考试不及格
• 猪八戒都有哪些学生
• 谁参加了2门以上的考试
• 哪个几个班级有姓周的同学
• 年龄最大前三位同学在那间教室上课

以下练习：要求有能力的学生自己再琢磨一种更好的连接方式

• 列出相对应的班级名,学生名,考试成绩信息

子查询

子查询的概念

子查询就在一个查询select语句中还有select语句，那么这些select都是子查询

一般来说子查询的结果一般在主查询中用于，数据来源，条件数，结果数据

主查询

确定结果字段的select语句就是主查询

子查询分类

按返回结果

标量子查询

子查询返回的结果是一个数据，一行一列

• 301房间都有谁上课

select * from t_student where class_id =(select id from t_class where classroom=’301房间’);

子查询中返回的数据是一行一列，这种子查询就是标量查询

• 猪八戒都有哪些学生

列子查询

从子查询中返回的结果是多行一列就是列子查询

• 班级有女生的班主任是谁

select * from t_class where id in (select class_id from t_student where sex=’女’);

子查询返回的是多行一列的数据—列子查询

• 哪些班级学生年龄超过了30岁

select * from t_class where id in (select class_id from t_student where age>30);

行子查询

子查询的结果返回的是多列一行

• 和周杰伦性别一样，在同一间教室上课的人

第一步：找到周杰伦的性别和班级id

select sex,class_id from t_student where name=’周杰伦’;

第二步：构建子查询

select * from t_student where (sex,class_id)=(select sex,class_id from t_student where name=’周杰伦’);

第三步：把周杰伦本省过滤掉

select * from t_student where (sex,class_id)=(select sex,class_id from t_student where name=’周杰伦’)

and name <> ‘周杰伦’;

表子查询

子查询是作为数据的来源使用 一般是在from后边，返回的信息是多行多列

• 用表子查询，找所有姓周的男生

select * from (select * from t_student where sex=’男’) as tb where tb.name like ‘周%’;

或select * from (select * from t_student where name like ‘周%’) as tb where tb.sex=’男’;

Exists子查询

Exitsts 是存在，如果存在返回1，如果不存在返回0

• 有考试满分的学生显示所有班级名称

select class from t_class exists (select * from t_result where result=100);

如果查询出来的结果有那么条件成立

如果查询不出来信息那么条件不通过

按试用场合

作为主查询的结果数据

作为主查询的条件数据

子查询在where条件里边

Having

on

作为主查询的来源数据

子查询在from后边

子查询中特定关键字

in关键字

在什么范围中

Not in 标识不再范围中

• 有男生的班级都有哪些

第一步：先找男生的班级id 去重

Select distinct class_id from t_student where sex=’男’;

第二步：根据男生班级的id在班级表中找到班级的名字

select class from t_class where id in (Select distinct class_id from t_student where sex=’男’);

• 没有男生的班级都有哪些

any关键字

表示满足任意一个条件即可

• 找出比任意一个姓周的年龄大的学生

第一步：找出所有姓周的年龄

select age from t_student where name like ‘周%’;

第二：根据所有姓周的学生年龄来找比其年龄大的人

select name,age from t_student where age > any (select age from t_student where name like ‘周%’);

all关键字

表示满足所有的条件

• 找出比所有姓周的年龄小的学生

第一步：找出所有姓周的年龄

select age from t_student where name like ‘周%’;

第二步：根据所有姓周年龄找结果字段数据

select name,age from t_student where age < all (select age from t_student where name like ‘周%’);

数据的备份还原

备份还原的意义

防范于未然，保证数据的安全性和完整性，网站最重要的就是数据，如果没有数据，你这个就是镜中花水中月

文件备份

数据库的本质就是文件，数据都是存放在文件中，我们把这个文件夹中的文件复制出来一份，是不是就是把当前数据库进行了备份呢？—–不一定

这样的备份是有要求的，要这个数据库的表数据引擎必须是myisam的

测试：

创建数据库，数据表

在test表中添加测试数据

复制lzh文件夹，重命名

查看数据库

操作lzh1数据库

这种文件备份数据库只能备份myisam数据库引擎的数据表

整库备份

用到了一个mysqldump工具来进行备份数据库，备份出来的数据是保存到一个.sql文件中的

具体的使用方式是如下

Mysqldump -h主机名 -P端口 -u 用户名 -p 数据库名 > sql文件的地址

如果主机名和端口号都是默认的那么可以省略

案例:把db1数据库备份

备份的时候一定要退出数据库连接

Mysqldump -uroot -proot db1 >C:/db1.sql;

单表备份

使用MySQLdump工具来备份

使用方式

Mysqldump -h主机名 -P端口 -u 用户名 -p 数据库名 表名 > sql文件的地址

如果主机名和端口号都是默认的那么可以省略

Db1.sql里边只用class表的定义sql和数据插入的sql没有其他表的sql

多表备份

使用MySQLdump工具来备份

使用方式

Mysqldump -h主机名 -P端口 -u 用户名 -p 数据库名 表名1 表名2….. > sql文件的地址

如果主机名和端口号都是默认的那么可以省略

Db1.sql文件中只有class 和 student表的相关sql语句没有其他表的sql

还原

工具还原

Mysql工具还原数据

使用方式 使用还原的时候要退出mysql连接

Mysql -h主机名 -P端口号 -u用户名 -p密码 数据库名 < sql文件的地址路径

查看bbss数据库

source还原

source是一个命令，用来还原数据文件到数据库

用法: source 数据库sql文件路径;

前提：你得连接数据库进入到你需要还原的数据库里边

增量备份了解

例如：

九月份我备份了30次

1号备份直到一号的数据库

2号备份 一号到二号的数据 —-一号一天的数据

3号备份 二号到三号的数据 —-二号一天的数据

………

这样的备份就是增量备份—每天只备份不重复的一部分，这些部分加起来就是整个数据库

每次备份，数据量小，备份速度快

还原的时候，比较麻烦，还有就是还原的数据有时间的先后问题，只能先还原先备份的数据，在根据时间的顺序还原，如果有一次还原失败，整体数据都会失败，也就是还原不成功

用户权限管理

用户权限管理的必要性

在项目开发中不同的人员对数据库有不同的权限需求，我们要尽量给这些工作人员小的权限

避免误删，或是破坏数据库的安全性和完整性

用户管理

用户信息的保存&含义

创建用户

语法：

Create user user1@’主机地址’ identified by ‘密码’

删除用户

语法：

Drop user 用户名@’主机地址’;

测试删除uer1@’127.0.0.1’;

修改用户密码

第一种直接修改mysql系统数据下的user表

update user set password=password(‘root’) where user=’user1′ and host=’127.0.0.1′;

测试使用老密码登录（注意先关闭cmd ,在打开cmd）

注意重启服务

第二种修改密码

给自己修改密码

Set password=password(‘abc’);

给别修改密码（管理员权限）

set password for user1@’127.0.0.1′ =password(‘root’) ;

权限管理

Mysql权限表

给用户授权

Grant 权限名 on 库名.表名 to 用户名@’主机地址’；

如果想把某一个数据库下的所有表的select给user1怎么做呢？

grant select on bbss.* to user1@’127.0.0.1‘; —*代表所有的表

如果想把所有数据的所有数据表的select权限都给user1;

grant select on *.* to user1@’127.0.0.1‘;

注意：赋予权限后，当前的连接不会生效，从新连接数据库后才会生效，有权限缓存的问题

收回权限

Revoke 权限名on 数据库名.表名 from 用户名@’主机地址’；

刷新权限

把权限从数据表中刷新的内存

Flush privilege;

用户密码丢失处理

• 开启服务 跳过权限认证

mysqld –skip-grant-tables

• 连接服务器 这时候不需要用户名 和密码

Mysql

• 修改密码

update mysql.user set password=password(‘abc’) where user=’root’ and host=’localhost’;

• 重启服务

• 重新连接