MySQL：(二)

多表查询－笛卡尔积

对于数据库中 针对于两张表的记录数的所有记录进行匹配，获得笛卡尔积！⚠️笛卡尔积结果是无效的，必须从笛卡尔积中选取有效的数据结果 ！！！

-- 显示结果就是笛卡尔积，两个表记录乘积
select * from emp,dept; 

内链接概念

• 一般有效的数据 通常采用内链接的方式获取有效数据
  

  select * from emp e,dept d where e.deptno = d.deptno;

• 针对于内链接的语法，一般可以使用表别名进行查询 也可以通过 join on 关键字进行链接
  

  -- join 用来链接两张表，on 后添加两张表需要链接的条件
  select * from emp join dept on emp.deptno = dept.deptno;

子查询

• 子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用；
• 子查询可以嵌入以下SQL子句中：where子句、having子句和from子句；

单行子查询

　　内部select语句只返回一行结果的查询（单列）。

• 主查询的where子句使用单行子查询返回结果要采用单行比较运算符（=、>、>=、<、<=、<>）
  

  -- 查询工资比编号为7566雇员工资高的雇员姓名。
  select ename
  from   emp
  where sal>
        (select sal
          from emp
          where empno=7566)
  order by ename;
  
  -- 显示和雇员scott同部门的雇员姓名、工资和部门编号。
  select ename,sal,deptno
  from   emp
  where deptno=
        (select deptno
          from emp
          where ename='SCOTT');
  
  -- 显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作和工资
  select ename,job,sal
  from   emp
  where job=
        (select job from emp where ename='SCOTT')
  and   sal>
        (select sal from emp where ename='JAMES');

  • 子查询要用括号括起来；
  • 将子查询放在比较运算符的右边；
  • 不要在子查询中使用order by子句，select语句中只能有一个order by子句，并且它只能是主select语句的最后一个子句。

单行子查询中使用组函数

-- 显示工资最低的雇员姓名、工作和工资。
select ename,job,sal 
from emp 
where sal=(select min(sal) from emp);

having子句中使用单行子查询

-- 显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资。
-- 1、按部门显示部门编号、部门最低工资
select deptno as 部门编号,
       min(sal) as 最低工资
from emp
group by deptno;

-- 2、查询20部门最低工资
select min(sal) from emp where deptno=20;

-- 3、使用having子句把2作为1的子查询
select deptno as 部门编号,
       min(sal) as 最低工资
from emp
group by deptno
having min(sal)>(select min(sal) 
                 from emp 
                 where deptno=20);

问题思考：

• 当单行子查询返回的结果为null时，主查询是否正确？
  

  -- 查询和SMITH从事相同工作的雇员姓名和工作，如果SMITH误拼写成SMITHS则返回结果为null
  select ename,job
  from emp
  where job=(select job 
             from emp
             where ename='SMITHS');

• 子查询中使用group by子句，主查询中是否可以使用单行比较符？
  

  -- 下面的SQL语句能正确执行吗？
  select ename,job
  from emp
  where sal=(select min(sal)
             from emp
             group by deptno);

  ORA-01427: 单行子查询返回多个行

from子句中使用子查询

　　在from子句中使用子查询时，必须给子查询指定别名。

-- 显示工资高于部门平均工资的雇员姓名、工作、工资和部门号select ename,job,sal,emp.deptno 
from emp, (select deptno,avg(sal) avgsal
           from  emp
           group by deptno)s
where emp.deptno=s.deptno and sal>s.avgsal;

多表设计

E-R 图的设计说明:

结论： 必须创建第三张关系表，在关系表中引用两个实体主键作为外键。

建表原则：创建的第三方关系表，将每张表的主键作为第三章表的联合主键。

多对多关系

案例演示说明: 老师和学员

图解：

表设计：

--   多对多    老师 和学员 
create  table if not exists student(
id int primary key auto_increment,
name varchar(20)
);
create  table if not exists teacher(
id int primary key auto_increment,
name varchar(20)
);

-- 第三张 表  
create table if not exists student_teacher(
sid int,
tid int,
constraint student_teacher_student_fk foreign key (sid)  references student(id),
constraint student_teacher_teacher_fk foreign key (tid) references teacher(id),
primary key(sid,tid)
);

-- 苍老师  李四  张三     题目 苍老师有几个学员  显示学员信息  
insert  into  student values(null,'李四');
insert  into  student values(null,'张三');
insert  into  student values(null,'王五');
insert  into  student values(null,'赵六');
insert into teacher  values(null,'苍老师');
insert into teacher  values(null,'文老师');
insert into teacher  values(null,'陈老师');

select  *  from student;
select * from teacher;


--  维护关系   苍老师有两个学员 
insert into student_teacher values(1,1);
insert into student_teacher values(2,1);
insert into student_teacher values(3,1);

--  题目 显示苍老师的所有学员信息 
select s.*
from student s,teacher t,student_teacher st
where st.sid=s.id and st.tid=t.id and t.name='苍老师';

一对多关系

案例说明：员工和部门

--  emp  设计 
drop table if exists emp;
create table if not exists emp(
empno int primary key auto_increment,
ename varchar(10) not null,
job varchar(20),
salary double(8,2),
mgr int(10),
bonus double(6,2),
hiredate date,
deptno int,
--  添加外键约束    constraint  约束   pk  primary key   fk foreigin key    references  参照
constraint  emp_dept_fk foreign key(deptno)  references dept(deptno)
);
--  新建dept表
drop table if exists dept;
create table if not exists dept(
deptno int primary key,
dname varchar(30),
dlocation varchar(255)
);

select * from emp;
select * from dept;
desc  emp;
show  tables;
--  外键约束  两张表产生关联   2种方式   1:  建表语句条件外键约束(常用)

-- 2: 表已经建好 添加外键约束  删除外键约束
alter table emp drop foreign key emp_dept_fk;
alter  table  emp add constraint emp_dept_fk foreign key (deptno) references dept(deptno);
--
delete from emp;
delete from dept where deptno=10;

--  一对多的删除  问题  
--   1:  部门删除  员工全部干掉     cascade  级联  删除 部门删除  该部门所有的员工全部自动删除
alter table emp drop foreign key emp_dept_fk;
alter  table  emp add constraint emp_dept_fk foreign key (deptno) references dept(deptno) on delete  cascade;
                
--  2: 部门删除 员工留  更换部门号   部门删除  员工保留 但是员工deptno --->null
   alter table emp drop foreign key emp_dept_fk;
alter  table  emp add constraint emp_dept_fk foreign key (deptno) references dept(deptno) on delete set null;
delete from dept where deptno=20;

建表原则：不需要创建第三方关系表，只需要在多方添加 一方主键作为 外键；

一对一关系

这种关系很少见到  负责人和工作室  夫妻关系

一个负责人 管理一个工作室

一个工作室 只有一个负责人

 

建表规则：在任一方添加对方主键 作为外键

--  1:1     夫妻  一夫一妻 
create  table husband(
id int primary key auto_increment,
name varchar(20)
);
create  table wife(
id int primary key auto_increment,
name varchar(20),
h_id int,
constraint husband_wife_fk foreign key(h_id) references husband(id)
);

insert into husband values(null,'a');
insert into husband values(null,'b');
insert into wife values(null,'A',1);


--  查找出A的老公是谁?
select h.*
from husband h , wife w
where h.id=w.h_id and w.name='A';

用户管理

创建用户

-- 语法：CREATE USER 用户名[@地址] IDENTIFIED BY '密码';

-- 示例：创建一个名叫user1，密码是123的用户
-- 创建本地用户:
CREATE USER user1@localhost IDENTIFIED BY '123';

-- 创建本地和远程用户
CREATE USER user1@'%' IDENTIFIED BY '123';

删除用户

-- 语法：drop USER 用户名;

-- 示例：删除用户user1
drop USER user1;

修改用户密码

-- 语法：UPDATE USER SET PASSWORD=PASSWORD('密码') WHERE USER='用户名';
-- FLUSH PRIVILEGES;

-- 示例：
UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE USER='user1';
FLUSH PRIVILEGES;

用户授权

• 授权
  

  -- 语法：Grant 权限1,权限2…… on 数据库名.表名 to 用户名@IP;
  
  -- 示例：
  GRANT SELECT,INSERT,UPDATE ON mydb1.* TO 'user1'@'localhost';
  GRANT ALL ON mydb1.* TO 'user1'@'localhost';

• 取消授权
  

  -- 语法：REVOKE 权限1,权限2…… on 数据库名.表名 from 用户名@IP;
  
  -- 示例：
  REVOKE SELECT,INSERT,UPDATE ON mydb1.* FROM 'user1'@'localhost';

案例练习

　　链接: https://pan.baidu.com/s/1miaJuQ0 密码: xeb4