1.数据库模型:
一对多:
一对一:
多对多: 特殊的一对多 需要有一张中间表来描述和管理两张表的关系
主表和从表:
学生(主表) 老师(从表),员工表(主表) 部门表(从表)
2.外键:
a.存了别的表中的主键,外键的值在关联的表中必须存在
b.外键可以重复
c.外键可以为空
d.可以有多个外键
数据库提供了外键的约束设置:但是实际工作中基本不用.
3.默认值约束 DEFAULT
缺省值 default “值”:给某一列设置默认值之后 如果添加数据的为空,则赋值为默认值
4.唯一约束 UNIQUE
某些列要求不能重复 则设置为唯一
mysql中有一个概念:索引(字典的目录) 提高查询效率 主键索引,通过主键查询效率是非常快的.
mysql中 唯一约束是通过唯一索引来实现的
5.数据库的备份与恢复
5.1备份:
mysqldump -uroot -p密码 数据库名称>备份位置
1
5.2恢复:
mysql -u用户名 -p密码 新的数据库名称<脚本文件
1
数据库的多表联查
问题:-- 查询每个员工在那个部门 具体要求显示部门名称 发现查询不了!
select ename,deptno from emp
1
两张表联查:在from后面多个表名称之间通过,分割 然后使用wheret条件 通过外键进行过滤 就可以拿到我们要的正确数据.
– 查询每个员工在那个部门 具体要求显示部门名称
select ename,deptno from emp
1
笛卡尔积
select * from emp,dept
1
2
– 获取每个员工的部门信息 通过外键deptno能够把笛卡尔积中的错误数据过滤掉
select emp.ename,dept.dname from emp,dept where emp.deptno =dept.deptno
1
6.1内连接查询
语法格式: select 查询列 [*] from 表1 ,表2 where 表1.外键= 表2.主键[方言]
select 查询列[*] from 表1 inner join 表2 on 表1.外键= 表2.主键
1
2
3
select emp.ename,dept.dname from emp INNER join dept on emp.deptno =dept.deptno
1
内连接的特点:
只能查询到满足外键关联条件的数据,不满足的不会查询出来.
6.2外连接查询
左外连接:查询结果以左表为主
查询的时候即使左表的某一条数据不满足外键条件也会查询
select 列名称 [*] from 表名称1 left join 表名称2 on 表1外键=表2主键
1
– 查询所有的员工所属的部门,如果部门不存在也展示出来
select emp.ename,dept.dname from emp left join dept on emp.deptno =dept.deptno
1
右外连接:查询结果以右表为主
查询的时候即使由表的某一条数据不满足外键条件也会查询
select 列名称 [*] from 表名称1 right join 表名称2 on 表1外键=表2主键
1
– 查询各部门的员工姓名
select e.ename,d.dname
from emp e right JOIN dept d
on e.deptno = d.deptno
1
2
3
左连接和右连接 是可以等价转换的.
– 查询所有的员工所属的部门,如果部门不存在也展示出来
select emp.ename,dept.dname from dept right JOIN emp on emp.deptno =dept.deptno
1
案例:
– 查询每个老师的姓名以及他们所带的课程
select t.tname,c.Cname from teacher t inner join course c on t.TId =c.TId
1
– 统计学校所有的老师以及带的课程
select t.Tname,c.Cname
from teacher t left join course c
on t.tid = c.cid
1
2
3
– 统计学校所有的科目以及任课老师
select t.tname,c.cname
from teacher t right join course c
on t.tid = c.tid
1
2
3
6.3 子查询
一个sql查询中有查询,一个sql语句中有多个select,
子查询出现的位置:
where:当一个查询的结果作为另外的一个查询的条件的时候,我们会在where后面使用一个查询语句.
– 聚合函数 max 可以获取员工最高薪资 获取最高薪资的员工姓名以及薪资
select max(sal) from emp
select * from emp where sal =(select max(sal) from emp )
1
2
统计员工中薪资高于甘宁的员工信息
分析：1.甘宁的薪资信息
2.要判断薪资是否高于甘宁
select sal from emp where ename = "甘宁"
select * from emp where sal > (select sal from emp where ename = "甘宁")
1
2
from:查询的内容是另外一个查询的结果中的:
查询员工的编号为1006的员工名称/员工薪资/员工部门名称/部门地址
分析：1.只查员工表 查询编号为1006的员工
2.拿到上述结果之后与部门表进行连接查询 获取其他的信息
select * from emp where empno = 1006
select e.ename,e.sal,d.dname,d.loc
from dept d inner join (
select * from emp where empno = 1006) e on d.deptno = e.deptno
1
2
3
4
5
练习:
– 查询薪资高于部门编号为30的所有员工的姓名和薪资
select max(sal) from emp where deptno=30
select emp.ename,emp.sal
from emp where emp.sal >(select max(sal) from emp where deptno=30)
1
2
3
– 查询薪资和庞统 以及孙尚香一样的所有员工的姓名
select sal from emp where ename = "庞统" or ename = "孙尚香"
-- in
select e.ename,e.sal
from emp e where sal
in(select sal from emp where ename = "庞统" or ename = "孙尚香")
1
2
3
4
5
– 以及部门信息
select e.ename,d.dname
from emp e INNER JOIN dept d on e.deptno =d.deptno and sal
in(select sal from emp where ename = "庞统" or ename = "孙尚香")
1
2
3
7.数据库设计
E-R模型: 表:相当于类 数据:对象
E:entry 实例 一条数据
r:relationship 联系 一对多 多对多 一对一
数据库设计三大范式:
第一范式:无重复的列 不可拆分 不能用一列数据表示多个含义
第二范式:主外键关系创建,每一条数据都要有能够标识的唯一主键.
第三范式:只能应用主键列,不能出现非主键的其他列.
8.数据库的存储过程(了解)
存储过程是指带有业务逻辑的sql语句.他里面有流程控制语句 :if while
特点:
1.执行效率非常快
2.移植性比较差
创建存储过程的格式: create procedure 存储过程名称(参数)
begin 业务sql end
参数: 入参 in 出参 out
案例1
– 创建存储过程的格式:
create PROCEDURE selEmp(in id int)
BEGIN
select * from emp where empno = id;END
1
2
3
4
– 存储过程的调用 call 存储过程名称(参数)
call selEmp(1003)
1
– 删除存储过程
drop PROCEDURE selEmp
1
– 创建存储过程的格式:
create PROCEDURE getEmp(in id int,out str varchar(32))
BEGIN
select ename into str from emp where empno = id;END
call getEmp(1003,@str)
select @str
1
2
3
4
5
6
9.Mysql的事务
作为一个逻辑工作单元的一系列操作,由多个sql语句组成,要么全部成功,要么全部失败,数据库通过事务来保证安全性.
Begin;[开启事务]
sql1
sql2
Commit;[提交事务]
RollBack;[事务回滚]
1
2
3
4
5
– 事务 张飞给关羽转3000元
BEGIN;
-- 开启事务 执行sql时 并不会直接修改到数据库
update emp set sal = 13000 where ename= "张飞";
update emp set sal = 15500 where ename= "关羽";
ROLLBACK;
-- 事务提交后 同时把整个事务中的sql执行完毕
COMMIT;
1
2
3
4
5
6
7
10.Mysql的视图(了解)
对于复杂的sql查询,如果在多个地方重复被使用,维护起来也不太好.使用视图来解决.视图的本质不是表,理解成一张临时表,是对查询的结果的封装.
create view 视图名称 as 查询的sql语句
例如:
create view v_st as select * from student
1
-- sql查看视图
select * from v_st
1
2
11.用户名的密码的管理
1.已知密码情况下修改密码
使用root登录到数据库 修改表
password(“密码”):把密码进行加密
密码加密:防止数据丢失
-- 修改密码
update user set password = password("123") where user = "root"
-- 修改完数据之后 需要刷新生效才可以
FLUSH PRIVILEGES
1
2
3
4
2.忘记密码的情况下
1.mysql的核心配置文件:my.ini文件:
C:\ProgramData\MySQL\MySQL Server 5.7
2.找到文件中的[mysqld],在改内容下面添加:skip -grant -tables,注意要重新启动
不需要密码就可以登录
update user set password = password("root") where user = "root"
