连接查询
表与表的关系
公用的字段来关联表
一对一
一对多
多对多
- 一对一的关系
老婆和老公(法律上)
人和身份证号
- 一对多的关系
父亲和孩子
- 多对多的关系
学生和老师
连接查询的概念
什么是连接查询
【通过公用字段】把两张表进行横向的拼接就是连接查询
比如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 用户名@’主机地址’;
修改用户密码
第一种直接修改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’;
- 重启服务
- 重新连接