一.多表概念
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:表与表之间关系:
- 一对多(多对一):在多中引用一的主键来建立关系;如:一个班级有多个学生,在学生表中引入班级编号;
- 一对一:两张表中可以根据需要在任意一个表中建立关系;
- 多对多:创建第三张表来建立这两张表之间的关系。如:一个老师教多个学生,一个学生有多位老师教,这时就可以建立第三张表来维护老师表和学生表之间关系。
二.联表查询:多张表连在一起共同查询数据。
联表查询条件:表与表之间一定要有公共列,才能进行联表查询
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');
注意:
- 能用子查询解决的问题一般情况下可以用联表查询解决,能用联表查询的问题,不一定能用子查询解决;
- 在查询语句中,能用精确查询的就不要用模糊查询;
- 在查询语句中,如果一个题目既可以用联表查询解决,也可以用子查询解决,优先选用子查询解决;
- 子查询除了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;
七、数据库备份
- 第一种用sql命令备份:用管理员身份运行cmd->cd MySql安装目录\bin->输入命令:
mysqldump -u 用户名 -p 数据库名>文件路径\\文件名.sql;
- 第二种用Navicat备份:在Navicat中选中数据库名->右键->转存为Sql文件->选择文件保存的文件夹->关闭
八、数据库还原(还原数据库中表及数据)
-
第一种用sql命令还原:用管理员身份运行cmd->用sql命令登录MySql数据库->创建数据库->use 数据库名->用命令还原数据:source 文件路径\文件名.sql;
-
第二种用Navicat还原:在Navicat中选中链接->创建数据库->选中数据库->右键->运行Sql文件->选择之前备份的Sql文件->开始->关闭