SQL server 一个表当做多个表去绑定 guopby 一个表sql语句一对多

二、表关系

（一）概念和分类

关系主要包括：一对多（多对一）、一对一、多对多三种。

（二）一对多（多对一）

一对多（1:n）。 例如：班级和学生，部门和员工，客户和订单，分类和商品等

一对多建表原则: 在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键

( 三 ) 多对多

多对多（m:n）。例如：老师和学生，学生和课程，用户和角色。

多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

( 四 )一对一

一对一（1:1）。 在实际应用中并不多见，因为一对一可以创建成一张表。

1. 主键关联方式

主表的主键和从表的主键，形成主外键关系。

2.唯一外键方式

主表的主键和从表的外键（唯一），形成主外键关系。

三、多表查询

( 一 ) 什么是多表查询

之前我的查询都是从一张表中返回数据。同时查询多张表获取到需要的数据，就是多表查询。

（二）笛卡尔积

1、什么是笛卡尔积现象

多表查询时左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔积。

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积，笛卡尔积中有很多没有意义的数据。

语法

select * | 列1,列2,...,列n from 表1,表2,...,表n

2、如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

 SELECT * FROM dept, emp WHERE emp.dept_id=dept.id;

（三）连接查询

要清除笛卡尔积现象，可以使用连接查询。连接查询分为内连接和外连接。

1、内连接

用左边表的记录去匹配右边表的记录，符合条件的才显示。内连接又分为隐式内连接和显示内连接两种。

（1）隐式内连接

隐式内连接：不使用JOIN关键字，条件使用WHERE指定。

语法

 SELECT 字段名 FROM 左表, 右表 WHERE 条件

（2）显示内连接

显示内连接：使用INNER JOIN ... ON语句，可以省略INNER。查询的效果和隐式内连接相同。

语法

SELECT 字段名 FROM 左表 INNER JOIN 右表

2、外连接

（1）左外连接

左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略。

左表数据全部显示，右表显示和左表有关联的数据。

语法

 SELECT 字段名 FROM 左表 LEFT OUTER JOIN 右表 ON 条件

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL。

现在要查询员工的信息和所在部门的名称。如果使用内连接查询，没有部门的员工将不会显示，因为不满足连接条件。

select e.*,d.name from emp e,dept d where e.dept_id = d.id;

（2）右外连接

右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略。右表数据全部显示，左表显示和右表有关联的数据。

语法

SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件；

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL

3、多行子查询

子查询结果是单例多行

多行运算符

in、any、all

any表示任意值        all表示全部值

any 和 all 不是单独使用的运算符，要和 比较运算符一起使用

四、数据库备份与还原

（一）备份的应用场景

我们在服务器上进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

• 备份

1. 命令方式备份

语法

mysqldump -u用户名 -p密码 数据库 > 文件的路径/文件名.sql

作业

表结构

CREATE DATABASE test1; USE test1; ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT( DEPTNO int PRIMARY KEY,##部门编号 DNAME VARCHAR(14) , ##部门名称 LOC VARCHAR(13) ##部门地址 ) ; INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); ##员工表 #DROP IF EXISTS TABLE EMP; CREATE TABLE EMP( EMPNO int PRIMARY KEY, #员工编号 ENAME VARCHAR(10), #员工姓名 JOB VARCHAR(9), #员工工作 MGR int, #员工直属领导编号 HIREDATE DATE, #入职时间 SAL double, #工资 COMM double, #奖金 DEPTNO int #对应dept表的外键 ); ## 添加 部门 和 员工 之间的主外键关系 ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO); INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20); INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20); INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- （1）查找部门是30的员工详细信息。 select * from emp where DEPTNO = 30 -- （2）找出从事clerk工作的员工的编号、姓名、部门号。 select * from emp where JOB = 'clerk' -- （3）检索出奖金多于基本工资的员工信息。 SELECT*FROM emp WHERE comm > sal; -- （4）检索出奖金多于基本工资60%的员工信息。 SELECT*FROM emp WHERE comm > (sal*0.6); -- （5）找出10部门的经理、20部门的职员 的员工信息。 SELECT*FROM emp WHERE deptno=10 and job = 'MANAGER' OR deptno=20 and job = 'CLERK'; -- （6）找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。 SELECT*FROM emp WHERE deptno=10 and job = 'MANAGER' OR deptno=20 and job = 'CLERK' or (deptno=10 and job != 'MANAGER' or deptno=20 and job != 'CLERK'); -- （7）找出获得奖金的员工的信息。 select * from emp where COMM is not null; -- （8）找出奖金少于100或者没有获得奖金的员工的信息。 select * from emp where COMM is null or comm < 100; -- （9）找出姓名以A、B、S开始的员工信息。 select*from emp where ename like 'A%' or ename like 'B%' or ename like 'S%'; -- （10）找到名字长度为6个字符的员工信息。 select*from emp where ename like '______'; -- （11）名字中不包含R字符的员工信息。 select*from emp where ename not like '%R%'; -- （12）返回员工的详细信息并按姓名排序。 select*from emp ORDER BY ename asc; -- （13）返回员工的信息并按工作降序工资升序排列。 select*from emp ORDER BY job desc,sal asc; -- （14）计算员工的日薪(按30天)。 select ename,sal/30 from emp; -- （15）找出姓名中包含A的员工信息。 select*from emp where ename like '%A%'; ASE test1; USE test1; ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT( DEPTNO int PRIMARY KEY,##部门编号 DNAME VARCHAR(14) , ##部门名称 LOC VARCHAR(13) ##部门地址 ) ; INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); ##员工表 #DROP IF EXISTS TABLE EMP; CREATE TABLE EMP( EMPNO int PRIMARY KEY, #员工编号 ENAME VARCHAR(10), #员工姓名 JOB VARCHAR(9), #员工工作 MGR int, #员工直属领导编号 HIREDATE DATE, #入职时间 SAL double, #工资 COMM double, #奖金 DEPTNO int #对应dept表的外键 ); ## 添加 部门 和 员工 之间的主外键关系 ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO); INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20); INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20); INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- （1）查找部门是30的员工详细信息。 select * from emp where DEPTNO = 30 -- （2）找出从事clerk工作的员工的编号、姓名、部门号。 select * from emp where JOB = 'clerk' -- （3）检索出奖金多于基本工资的员工信息。 SELECT*FROM emp WHERE comm > sal; -- （4）检索出奖金多于基本工资60%的员工信息。 SELECT*FROM emp WHERE comm > (sal*0.6); -- （5）找出10部门的经理、20部门的职员 的员工信息。 SELECT*FROM emp WHERE deptno=10 and job = 'MANAGER' OR deptno=20 and job = 'CLERK'; -- （6）找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。 SELECT*FROM emp WHERE deptno=10 and job = 'MANAGER' OR deptno=20 and job = 'CLERK' or (deptno=10 and job != 'MANAGER' or deptno=20 and job != 'CLERK'); -- （7）找出获得奖金的员工的信息。 select * from emp where COMM is not null; -- （8）找出奖金少于100或者没有获得奖金的员工的信息。 select * from emp where COMM is null or comm < 100; -- （9）找出姓名以A、B、S开始的员工信息。 select*from emp where ename like 'A%' or ename like 'B%' or ename like 'S%'; -- （10）找到名字长度为6个字符的员工信息。 select*from emp where ename like '______'; -- （11）名字中不包含R字符的员工信息。 select*from emp where ename not like '%R%'; -- （12）返回员工的详细信息并按姓名排序。 select*from emp ORDER BY ename asc; -- （13）返回员工的信息并按工作降序工资升序排列。 select*from emp ORDER BY job desc,sal asc; -- （14）计算员工的日薪(按30天)。 select ename,sal/30 from emp; -- （15）找出姓名中包含A的员工信息。 select*from emp where ename like '%A%';