27.mysql高级

@一贤不穿小鞋

一.多表概念

1.多表中常用概念

• 主表:如果当前表中主键列被其他表引用,当前表就叫其他表的主表.(被引用的表叫主表)
• 从表(子表):如果当前表中引用其他表中主键列,当前表叫从表(子表).(引用的表叫从表)
• 公共列:两张表中引用的列叫公共列,公共列在主表中充当主键列,在从表中充当外键列.
• 对于同一个引用关系中两张表而言,主表中主键列与从表中外键列,列名可以相同也可以不同,但是列的数据类型必须相同,值匹配.

2.多表之间的关系:

2.1: 一对一关系

• A表中一条记录对应B表一条记录,B表中一条记录对应A表中一条记录,A表与B表之前就一对一关系.在一对一关系中,只要其他一张表引用另一张表的主键列就可.
• eg:人员表和身份证表,它们之间一对一关系.

2.2:一对多关系

• A表中一条记录对B表中多条记录,B表中一条记录对应A表中一条记录.A表与B表之间就一对多的关系,B表与A表之间就是多对一的关系.在一对多关系中在多的表中引入一的表中主键.
• eg:班级表和学生表,它们一对多关系.

2.3:多对多关系

• A表中一条记录对应B表中多条记录,B表中一条记录对应A表中多条记录,A表与B表之间就是多对多关系.在多对多关系中,用第三张表来建立A表与B表的关系.
• eg:学生表和老师表,它们多对多关系.
  

二.联表查询:前提是表与表之间一定要有关系,通过公共列进行连接查询.

• 注意:联表查询时,如果查询的列是公共列,一定要用表名或表别名调用;
• 给表取别名有两种方式,第一种:表名 别名,第二种:表名 as 别名

1.内连接:表与表之间是平等的关系,通过公共列建立关系,将两张及以上表中相关数据显示.

• 语法一:select 列名1,列名2…
  from 表名1,表名2,表名3
  where 表名1.公共列=表名2.公共列 and 表名1.公共列=表名3.公共列;

• 语法二:select 列名1,列名2…
  from 表名1
  inner join 表名2 on 表名1.公共列=表名2.公共列
  inner join 表名3 on 表名1.公共列=表名3.公共列; (推荐)

eg:#查询所有学生学号,姓名,班级名称,班级编号
select sid,sname,cname
from t_student,t_class
where t_student.class_id=t_class.cid;

• #联表查询时,如果查询的列是公共列,一定要用表名或表别名调用;
• #给表取别名有两种方式,第一种:表名 别名,第二种:表名 as 别名

select sid,sname,cname,s.cid
from t_student as s
inner join t_class c on s.cid=c.cid;

• #查询班级编号为1的所有学生学号,姓名,班级名称,班级编号

select sid,sname,cname,s.cid
from t_student s,t_class c
where s.cid=c.cid and s.cid=1

select sid,sname,cname,s.cid
from t_student s
inner join t_class c on s.cid=c.cid
where s.cid=1;

2.外连接:表与表之间有主次之分,主要查询表中数据要全部显示,次要查询的表中有与主要查询表中相关的数据就显示,没有相关数据以null显示;次要查询的表中有的数据,主要查询表中没有与之对应数据就不显示

• 左外连接:以左边的表为主要查询的表,右边的表为次要查询的表.
  语法:select 列名1,列名2…
  from 表名1
  left join 表名2 on 表名1.公共列=表名2.公共列
  left join 表名3 on 表名1.公共列=表名3.公共列

• 右外连接:以右边的表为主要查询的表,左边的表为次要查询的表.
  语法:select 列名1,列名2…
  from 表名1
  right join 表名2 on 表名1.公共列=表名2.公共列
  right join 表名3 on 表名1.公共列=表名3.公共列

eg:
#查询所有学生编号和姓名以及与之相关班级名称->学生表为主要查询,班级表为次要查询
SELECT s.sid,s.sname,c.cname
from t_student s
left join t_class c on s.cid=c.cid;

SELECT s.sid,s.sname,c.cname
from  t_class  c
right join t_student s on s.cid=c.cid;

#查询所有班级信息以及与之相关的学生编号和姓名->班级表为主要查询,学生表为次要查询
SELECT c.cid,c.cname,s.sid,s.sname
from t_class c
left join t_student s on c.cid=s.cid;

SELECT c.cid,c.cname,s.sid,s.sname
from  t_student s
right join t_class c on c.cid=s.cid;

三.子查询:在一个查询语句中包含另一个查询语句.

1.子查询的执行顺序:由内而外.

• 先执行内层的查询语句,现将结果代入到外层查询中查询.

2.能用子查询解决的所有问题都可以用联表查询解决.能用联表查询解决的问题不一定能用子查询解决.

3.一般情况下,子查询的效率高于连表查询.能用子查询就不要用连表查询.

4.子查询不能用在group by和order by后面,其他关系字后面都可以用.

eg:#查询java2001班的所有学生的学号和姓名
	#分析:查询学生表中学号和姓名,条件是班级名称在班级表中, 找出学生和班级表关系
	#经过上面分析要查的列在学生表中,条件又在班级表中,而学生表与班级表有班级编号这个公共列
	#用子查询:先查询java2001对应的班级编号,再根据班级编号查询学生
select s.sid,s.sname
from t_student s
inner join t_class c on s.cid=c.cid
where c.cname='java2001';

select sid,sname
from t_student
where cid=(select cid from t_class where cname='java2001');

#将子查询结果作为一张表
select sname
from (select sid,sname,sage from t_student where cid=1) as s
where sage>99;

#将子查询结果作为固定列
select sname,sid,(select saddress from t_student where sid=1) as '地址' 
from t_student;

四.联合查询:将查询多个结果合并一个结果集.前提条件是:每个查询的列名相同.

1.union合并结果并去重

2.union all 合并结果不去重

eg:#查询出年龄大于2岁的所有学生信息和年龄小于10岁的所有学生信息,合并结果
#union合并且去重
select sid,sname,sage from t_student where sage>=2
union
select sid,sname,sage from t_student where sage<10;

#union all 合并不去重
select sid,sname,sage from t_student where sage>=2
union all
select sid,sname,sage from t_student where sage<10;

五.查询的总语法:

select 列名1,列名2...
from 表名1
[inner join 表名2 on 表名1.公共列=表名2.公共列]
...
[left/right join 表名3 on 表名1.公共列=表名3.公共列]
...
[where 条件]
[group by 列名]
[having 条件]
[order by 列名1 asc/desc,列名2 asc/desc]
[limit 参数1 [,参数2]];

六.DCL:数据库控制语言.

1.创建用户:create user 用户名 identified by 密码;

2.删除用户:drop user 用户名;

3.给用户授权:grant 权限 on 库名.表名 to 用户名@主机名 identified by 密码;

4.撤消用户权限:revoke 权限 on 库名.表名 from 用户名@主机名

eg:#创建用户
create user 'yjy' IDENTIFIED by '123';

#删除用户
drop user 'yjy';

#给用户授权
grant all on 2008myschool.* to 'yjy'@localhost IDENTIFIED by '123';

#撤消用户权限
revoke all on 2008myschool.* from 'yjy'@localhost;

5.数据库备份:

5.1:第一种用sql命令备份

• 用管理员身份运行cmd->cd MySql安装目录\bin->输入命令:
  mysqldump -u 用户名 -p 数据库名>文件路径\文件名.sql;

5.2:第二种用Navicat备份

• 在Navicat中选中数据库名->右键->转存为Sql文件->选择文件保存的文件夹->关闭.

6.数据库还原(还原数据库中表及数据):

6.1:第一种用sql命令还原

• 用管理员身份运行cmd->用sql命令登录MySql数据库->创建 数据库->use 数据库名->用命令还原数据:source 文件路径\文件名.sql;

6.2:第二种用Navicat还原

• 在Navicat中选中链接->创建数据库->选中数据库->右键->运行Sql文件->选择之前备份的Sql文件->开始->关闭.

总结:
1.单表查询(查询,条件,聚合函数,分组,分组筛选,排序,去重,limit,模糊)(重点)
2.数据完整性
3.多表概念和关系(重点)
4.连表查询(重点)
5.子查询(重点)
6.联合查询