MySQL：(二)

多表查询－笛卡尔积

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 图的设计说明:

MySQL：(二)

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

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

多对多关系

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

图解：

MySQL：(二)

表设计：

MySQL：(二)

--   多对多    老师 和学员

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='苍老师';

一对多关系

案例说明：员工和部门

MySQL：(二)

--  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 '';

-- 创建本地和远程用户

CREATE USER user1@'%' IDENTIFIED BY '';

删除用户

-- 语法：drop USER 用户名;

-- 示例：删除用户user1

drop USER user1;

修改用户密码

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

-- FLUSH PRIVILEGES;

-- 示例：

UPDATE USER SET PASSWORD=PASSWORD('') 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

多表查询－笛卡尔积

内链接概念

子查询

单行子查询

单行子查询中使用组函数

having子句中使用单行子查询

from子句中使用子查询

多表设计

多对多关系

一对多关系

一对一关系

用户管理

创建用户

删除用户

修改用户密码

用户授权

案例练习

相关文章