MySQL入门基础二

一.多表概念

1.数据完整性:数据准确性和安全性(可靠性)。数据完整性包括：实体完整性,域完整性,引用完整性,自定义完整性

1.1:实体完整性:主键约束(parmary key),唯一约束(unique),自动增长列(auto_increment)，标识符

1.2:域完整性:数据类型,是否null,默认值

注意：check约束（mysql 5.5不支持）check(sex='男’or sex=‘女’)

1.3:引用完整性:通过建立主键关系来确保多个表之间数据准备性和安全性

优点:保证多张表之间数据安全；
缺点:效率低(每次操作子表时,会先检查主表)。
综合外键约束的优缺点:一般不建立外键约束。

1.4:自定义完整性:规则、存储过程、触发器。

2.多表连接查询:两张及以上表之间进行联合查询,前提条件是多张表之间有公共列

2.1:几个常用概念:

主表:被其他表引用一个列（主键）的表叫主表,主表中一定要有主键；
子表(从表):引用其他表的列的表叫子表；
公共列:两张表之间有相同的列,这个列表示意思是一样,但是列名可以相同也可以不同；一般情况下,公共列的列名一样。

2.2:表与表之间关系:

1. 一对多(多对一):在多中引用一的主键来建立关系；如:一个班级有多个学生,在学生表中引入班级编号；
2. 一对一:两张表中可以根据需要在任意一个表中建立关系；
3. 多对多:创建第三张表来建立这两张表之间的关系。如:一个老师教多个学生,一个学生有多位老师教,这时就可以建立第三张表来维护老师表和学生表之间关系。

二.联表查询:多张表连在一起共同查询数据。

联表查询条件:表与表之间一定要有公共列,才能进行联表查询

2.1:内联接查询:表与表之间是平等的关系,通过公共列建立桥梁来联合查询

语法一:

select 列名列表 
from 表名1,表名2,表名3
where 表名1.公共列=表名2.公共列 and 表名2.公共列=表名3.公共列 

语法二:

select 列名列表 
from 表名1
inner join 表名2 on 表名1.公共列=表名2.公共列
inner join 表名3 on 表名2.公共列=表名3.公共列;  (推荐使用)

注意:n张表,只能建立n-1个桥梁(公共列关系)。

#查询所有学生的姓名及班级名称
SELECT name,cname,c.cid
from t_student as s,t_class as c
where s.cid=c.cid;

SELECT name,cname,c.cid
from t_student as s
inner join t_class as c on s.cid=c.cid;

2.2:外联接:以其中一张表为主表,另一张表为子表,要求主表中数据全显示,子表中有与主表中对应的数据就显示,没有与之对应的数据就以null显示;子表中有的数据,主表中没有对应数据就不显示。

2.2.1:左外连接:以左边表为主,右边为子表

语法:

select 列名列表 
from 表名1
left [outer] join 表名2 on 表名1.公共列=表名2.公共列
left [outer] join 表名3 on 表名2.公共列=表名3.公共列;

2.2.2:右外连接:以右边表为主,左边为子表

语法:

select 列名列表 
from 表名1
right [outer] join 表名2 on 表名1.公共列=表名2.公共列
right [outer] join 表名3 on 表名2.公共列=表名3.公共列;

#查询所有学生学号和姓名,以及与学生对应班级编号和班级名称
select s.id,s.`name`,c.cid,c.cname
from t_student as s
left join t_class as c on s.cid=c.cid;

select s.id,s.`name`,c.cid,c.cname
from  t_class as c
right join t_student as s on s.cid=c.cid;

#查询所有班级编号和班级名称,以及与班级对应的学生
select c.cid,c.cname,s.id,s.`name`
from t_class as c
left join t_student as s on c.cid=s.cid;

select c.cid,c.cname,s.id,s.`name`
from t_student as s
right join t_class as c on c.cid=s.cid;

三、子查询:在一个查询语句中完整包含了另一个查询语句

子查询执行顺序:由内到外执行的。
注意:联表查询包含子查询;能用子查询解决的问题都能用联表查询解决；能用联表查询的问题不一定能用子查询解决。

#查询java01班所有学生学号和姓名
select s.id,s.`name`
from t_student as s
INNER JOIN t_class as c on s.cid=c.cid
where c.cname='java01';

##先查询出java01班对应班级编号,再根据班级编号查询学生学号和姓名
select s.id,s.`name`
from t_student as s
where s.id=(select c.cid from t_class as c where c.cname='java01');

注意:

1. 能用子查询解决的问题一般情况下可以用联表查询解决，能用联表查询的问题,不一定能用子查询解决；
2. 在查询语句中,能用精确查询的就不要用模糊查询；
3. 在查询语句中,如果一个题目既可以用联表查询解决,也可以用子查询解决,优先选用子查询解决；
4. 子查询除了order by后面不能用,其他关键字后面都可以用的。

四、查询总语法:

select 列名1,列名2...
from 表名1
[inner join 表名2 on 表名1.公共列=表名2.公共列]
[left join 表名2 on 表名1.公共列=表名2.公共列]
[right join 表名2 on 表名1.公共列=表名2.公共列]
[where 条件//边查询边筛选]
[group by 列名 //查询完后再将结果进行分组]
[having 条件 //查询完分完组后再筛选 ]
[order by 列名[asc/desc] //对结果排序]
[limit start [,count]]//只有mysql可用;

五、联合查询(在oracle,mysql中):将查询出来的结果集合并。前提条件:两个或以下查询结果的列名相同才能合并

#查询年龄小于17岁和大于17岁所有学生信息
select id,name,age,address,hobby,cid 
from t_student
where age<17 or age>17;

#union合并后去重
select id,name,age,address,hobby,cid 
from t_student
where age<=18
UNION
select id,name,age,address,hobby,cid 
from t_student
where age>10;

#union all合并后不去重
select id,name,age,address,hobby,cid 
from t_student
where age<=18
UNION ALL
select id,name,age,address,hobby,cid 
from t_student
where age>10;

六、DCL:数据库的控制语言

6.1：创建用户：

create user 用户名 identified by 密码;

6.2：删除用户：

drop user 用户名；

#创建用户
create user 'ls' IDENTIFIED by '123'
#删除用户
drop user 'ls';	

6.3: 给用户授权：

grant 权限 on 库.表 to 用户名@主机 identified by 密码；

6.4：撤消权限：

revoke 权限 on 库.表 from 用户名@主机

#给用户授权
grant all on userinfo.t_user to 'ls'@localhost identified by '123';

#撤消用户的权限
REVOKE all on userinfo.t_user from 'ls'@localhost;

七、数据库备份

1. 第一种用sql命令备份:用管理员身份运行cmd->cd MySql安装目录\bin->输入命令:

mysqldump -u 用户名 -p 数据库名>文件路径\\文件名.sql;

2. 第二种用Navicat备份:在Navicat中选中数据库名->右键->转存为Sql文件->选择文件保存的文件夹->关闭

八、数据库还原(还原数据库中表及数据)

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

2. 第二种用Navicat还原:在Navicat中选中链接->创建数据库->选中数据库->右键->运行Sql文件->选择之前备份的Sql文件->开始->关闭