学习数据库第五天

学习回顾

1. 数学相关

• 向下取整 floor
• 四舍五入 round(num,m)
• 非四舍五入 truncate(num,m)
• 随机数 rand() 0-1

2. 分组查询 group by
3. having
4. 子查询 嵌套查询

• 写在where或having后面当做查询条件的值
• 写在创建表的时候
• 写在from后面 当成虚拟表 必须起别名

5. 关联查询

• 查询方式：

1. 等值连接和内连接：当需要查询两张表的交集数据时使用，推荐使用内连接
2. 外连接：当需要查询一张表的全部数据和对应表的交集数据时使用

表设计：关联关系

外键

• 用于建立关系的字段称为外键

一对一

• 什么是一对一：有AB两张表，A表中的一条数据对应B表中的一条数据，同时B表一条数据也是对应A表中的一条数据，称为一对一

• 应用场景： 用户表和用户信息扩展表 商品表和商品详情表

• 如何建立关系： 在从表中添加外键，指向主表的主键

• 练习：

  1. 创建表保存以下数据
     创建user表(id,username,password)
     创建userinfo表(user_id,nick,age)

  create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
  create table userinfo(user_id int,nick varchar(10),age int);

  1, libai, admin , 诗仙,38
  2, liubei, 123456, 大耳贼,50
  insert into user values(null,‘libai’,‘admin’),(null,‘liubei’,‘123456’);
  insert into userinfo values(1,‘诗仙’,38),(2,‘大耳贼’,50);

  2. 查询每个用户的用户名和对应的昵称
     select u.username,ui.nick
     from user u join userinfo ui
     on u.id=ui.user_id;
  3. 查询诗仙的用户名和密码是什么
     select u.username,u.password
     from user u join userinfo ui
     on u.id=ui.user_id
     where ui.nick=‘诗仙’;
  4. 查询liubei的年龄
     select ui.age
     from user u join userinfo ui
     on u.id=ui.user_id
     where u.username=‘liubei’;
     ####一对多

• 什么是一对多：有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A表的一条数据

• 应用场景： 员工表和部门表 ， 商品表和分类表

• 如何建立关系： 在多的表中添加外键指向另外一张表的主键

• 练习：

  1. 创建员工表t_emp(id,name,dept_id)和部门表(id,name)
     create table t_emp(id int primary key auto_increment,name varchar(10),dept_id int);
     create table t_dept(id int primary key auto_increment,name varchar(10));
     保存以下数据
     部门表：1 神仙，2 妖怪
     员工表：1 猪八戒，2 孙悟空，3 蜘蛛精，4 白骨精
     关系：神仙部门的猪八戒和孙悟空
     妖怪部门的蜘蛛精和白骨精
     insert into t_dept values(null,‘神仙’),(null,‘妖怪’);
     insert into t_emp values(null,‘猪八戒’,1),(null,‘孙悟空’,1),(null,‘蜘蛛精’,2),(null,‘白骨精’,2);
  2. 查询每个员工的姓名和对应的部门名
     select e.name,d.name
     from t_emp e join t_dept d
     on e.dept_id=d.id;
  3. 查询神仙部的员工姓名
     select e.name
     from t_emp e join t_dept d
     on e.dept_id=d.id
     where d.name=‘神仙’;
  4. 查询蜘蛛精的部门名
     select d.name
     from t_emp e join t_dept d
     on e.dept_id=d.id
     where e.name=‘蜘蛛精’;

多对多

• 什么是多对多：有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条对应A表中的多条，称为多对多
• 应用场景： 用户表和权限表 老师表和学生表
• 如何建立关系：新建关系表，在表中两个外键指向另外两个表的主键
• 练习：
  创建三张表 teacher(id,name) student(id,name) t_s(tid,sid)
  苍老师：小明，小红，小绿
  传奇哥：小明，小黄
  create table teacher(id int primary key auto_increment,name varchar(10));
  create table student(id int primary key auto_increment,name varchar(10));
  create table t_s(tid int,sid int);
  insert into teacher values(null,‘苍老师’),(null,‘传奇哥’);
  insert into student (name) values(‘小明’),(‘小红’),(‘小绿’),(‘小黄’);
  insert into t_s values(1,1),(1,2),(1,3),(2,1),(2,4);
  1. 查询每个学生名字和对应的老师名字
     select s.name,t.name
     from teacher t join t_s ts
     on t.id=ts.tid
     join student s
     on s.id=ts.sid;
  2. 查询苍老师的学生都有谁
     select s.name
     from teacher t join t_s ts
     on t.id=ts.tid
     join student s
     on s.id=ts.sid
     where t.name=‘苍老师’;
  3. 查询小明的老师都有谁
     select t.name
     from teacher t join t_s ts
     on t.id=ts.tid
     join student s
     on s.id=ts.sid
     where s.name=‘小明’;

表设计案例： 权限管理

• 总共有5张表，三张主表：用户表、角色表、权限表 两张关系表：用户角色关系表，和角色权限关系表

• 创建以上五张表
  create table t_user(id int primary key auto_increment,name varchar(10));
  create table t_role(id int primary key auto_increment,name varchar(10));
  create table t_module(id int primary key auto_increment,name varchar(10));

  create table u_r(uid int,rid int);
  create table r_m(rid int,mid int);
  插入以下数据：
  苍老师、传奇哥
  男游客、男管理员、女会员、女管理员
  男浏览、男发帖、男删帖、女浏览、女发帖、女删帖
  insert into t_user (name) values(‘苍老师’),(‘传奇哥’);
  insert into t_role (name) values(‘男游客’),(‘男管理员’),(‘女会员’),(‘女管理员’);
  insert into t_module (name) values(‘男浏览’),(‘男发帖’),(‘男删帖’),(‘女浏览’),(‘女发帖’),(‘女删帖’);
  -以下是关系表中保存的关系
  苍老师-男管理员和女会员 传奇哥-女管理员
  男游客-男浏览 男管理员-男浏览、男发帖、男删帖
  女会员-女浏览、女删帖 女管理员-女浏览、女发帖、女删帖
  insert into u_r values(1,2),(1,3),(2,4);
  insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(3,6),(4,4),(4,5),(4,6);

  1. 查询每个用户名字和对应的角色名
     select u.name,r.name
     from t_user u join u_r ur
     on u.id=ur.uid
     join t_role r
     on r.id=ur.rid;
  2. 查询每个用户名字和对应的权限名
     select u.name,m.name
     from t_user u join u_r ur
     on u.id=ur.uid
     join r_m rm
     on ur.rid=rm.rid
     join t_module m
     on m.id=rm.mid;
  3. 查询苍老师的权限有哪些
     select u.name,m.name
     from t_user u join u_r ur
     on u.id=ur.uid
     join r_m rm
     on ur.rid=rm.rid
     join t_module m
     on m.id=rm.mid
     where u.name=‘苍老师’;
  4. 查询有女浏览权限的用户都是谁
     select u.name,m.name
     from t_user u join u_r ur
     on u.id=ur.uid
     join r_m rm
     on ur.rid=rm.rid
     join t_module m
     on m.id=rm.mid
     where m.name=‘女浏览’;

视图

• 什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图的数据来自于原表，视图的本质实际上就是取代了一段SQL语句

• 视图的作用：1. 可以起到复用的作用 提高开发效率 2. 可以隐藏敏感信息

• 创建视图
  create view 视图名 as (子查询);
  create table 表名 as (子查询); 创建了一个真实存在的表 而且表中有自己独立的数据

  • 创建10号员工的视图
    create view v_emp_10 as (select * from emp where deptno=10);
    select * from v_emp_10;
  • 创建一个没有工资的员工表视图
    create view v_emp_nosal as(select empno,ename,mgr,job,deptno from emp);
    select * from v_emp_nosal;

• 视图分类：

1. 简单视图： 创建视图的子查询中不包含：去重、聚合函数、分组查询、关联查询 的视图称为简单视图，可以对视图中的数据进行增删改查操作
2. 复杂视图： 和简单视图相反，只能对视图中的数据进行查询操作
   -创建一个显示每个部门平均工资，最高工资，最低工资的复杂视图
   create view v_emp_info as(select deptno,avg(sal),max(sal),min(sal) from emp group by deptno);

回顾

1. 表设计：关联关系

• 一对一： AB两张表，A表中一条数据对应B表中的一条数据，同时B表中一条对应A表中的一条， 建立关系：在从表中添加外键 指向主表的主键
• 一对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的一条数据， 建立关系：在多的表中添加外键指向另外一张表的主键
• 多对多：AB两张表，A表中的一条对应B 表中的多条数据，同时B表中一条对应A表中的多条数据， 建立关系：新建一个中间关系表，在关系表中添加两个外键指向另外两个表的主键

2. 权限管理案例：

• 总共5张表 3张主表：用户表 角色表 权限表 2张关系表：用户角色关系表，角色权限关系表

3. 视图

• 视图和表都是数据库中的对象，视图可以理解成一张虚拟的表，视图没有自己的数据，数据来自于原表，视图本质就是替换了一段SQL查询语句
• 作用： 可以服用SQL，可以隐藏敏感信息
• 视图分类：1. 简单视图 2. 复杂视图