1、关系数据的各种操作并（union）把相同字段数目和字段类型的表合并到一起笛卡尔积（cartesian product）内连接 inner jion所谓连接就是在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。连接又分为内连接，外连接，交叉连接。内连接：在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配的条件可以分成自然连接，等值连接，和不等连接自然连接：表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。等值连接表关系的笛卡尔积中，选择所匹配字段值相等(=符号)的数据记录。不等连接表关系的笛卡尔积中，选择所匹配字段值(!=)的数据记录。外连接 outer jion在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为：left outer union，right outer union ，full outer jion左连接：表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。右连接：表关系的笛卡尔积中，除了选择相匹配（相等）的数据记录，还包含右边表中不匹配的数据记录。全连接：表关系的笛卡尔积中，除了选择相匹配（相等）的数据记录，还包含左右表中不匹配的数据记录。交叉连接 cross jion2、内连接查询1、在from子句里利用逗号(,)区分多个表，在where子句里通过逻辑表达式来实现匹配条件，从而实现表的连接2、ANSI连接语法形式，在from子句中使用”jion…on”关键字，而连接条件写在关键字on子句里，这是推荐使用的连接语法。select field1,…,fieldnfrom join_tablename1inner join join_tablename2 [inner join join_tablenamen]on join_condition*等值连接自连接：一种特殊的等值连接，自身与自身连接为表取别名机制select field1,…,fieldn [AS] otherfieldnfrom tablename1 [as] othertablename1,…,tablenamen [AS] othertablenamen*不等连接可以使用的关系运算符包含”>” “>=” “<” “<=” “!=”3、外连接查询select field1,…,fieldnfrom join_tablename1 left|right|full [outer] join join_tablename2on join_condition左外连接新关系中执行匹配条件时，以关键字left join左边的表为参考表select e.ename empolyee,e.job,l.ename leaderfrom t_emplyee e left outer join t_employee lon e.mgr=l.ename;+————–+———–+——–+| employe| job| leader |+————–+———–+——–+| smith| clerk| ford || alen| salesman | black || ward| salesman | black || jones| manager | king || martin| salesman | black || ford| analyst | jones || black| manager | ford || king| president | NULL |+————–+———–+——–+8 rows in set (0.00 sec)select e.ename empolyee,e.job,l.ename leaderfrom t_emplyee e inner join t_employee lon e.mgr=l.ename;+————–+———-+——–+| employee | job| leader |+————–+———-+——–+| ford| analyst | jones || smith| clerk | ford || black| manager | ford || alen| salesman | black || ward| salesman | black || martin| salesman | black || jones| manager | king |+————–+———-+——–+7 rows in set (0.00 sec)观察发现：虽然等值连接sql语句也显示出雇员的相应信息，但是没有显示出雇员为king的信息。右外连接新关系中执行匹配条件时，以关键字right join右边的表为参考表4、合并查询数据记录select field1 field2,…,fieldnfrom tablename1union |union allselect field1 field2 … fieldnfrom tablename2union | union allselect field1 field2 … fieldnfrom tablename3…通过union来实现并操作，即可通过其将多个select语句的查询结果合并在一起组成新的关系。例：mysql> create table t_cstudent(-> name varchar(20),-> sex varchar(5));Query OK, 0 rows affected (0.09 sec)mysql> create table t_mstudent(-> name varchar(20),-> sex varchar(5));Query OK, 0 rows affected (0.07 sec)创建两张表，t_cstudent,t_mstudentselect * from t_cstudent;+———-+——-+| name| sex |+———-+——-+| ccjgong1 | man || ccjgong2 | woman || ccjgong3 | man || ccjgong4 | woman || ccjgong5 | woman || cmcjgong | man |+———-+——-+select * from t_mstudent;+———-+——-+| name| sex |+———-+——-+| cmcjgong | man || mcjgong1 | women || mcjgong2 | man || mcjgong3 | man || mcjgong4 | woman || mcjgong5 | woman |+———-+——-+select * from t_cstudentunionselect * from t_mstudent+———-+——-+| name| sex |+———-+——-+| ccjgong1 | man || ccjgong2 | woman || ccjgong3 | man || ccjgong4 | woman || ccjgong5 | woman |********| cmcjgong | man |*******| mcjgong1 | women || mcjgong2 | man || mcjgong3 | man || mcjgong4 | woman || mcjgong5 | woman |+———-+——-+执行结果显示出合并后的数据记录，同时去除了重复数据记录，使新关系里没有任何重复的数据记录。通过union all 把查询结果集直接合并在一起。select * from t_cstudentunion allselect * from t_mstudent+———-+——-+| name| sex |+———-+——-+| ccjgong1 | man || ccjgong2 | woman || ccjgong3 | man || ccjgong4 | woman || ccjgong5 | woman |********| cmcjgong | man |****************| cmcjgong | man |********| mcjgong1 | women || mcjgong2 | man || mcjgong3 | man || mcjgong4 | woman || mcjgong5 | woman |+———-+——-+与union相比，执行结果成功显示出合并后的数据记录，但是没有去掉了重复数据记录，即新关系里存在重复的数据记录 5、子查询在mysql中虽然可以通过连接查询实现多表查询数据记录，但是不建议使用。这是因为连接查询的性能很差。因此出现了连接查询的替代者子查询。在具体应用中，mysql软件推荐使用子查询来实现多表查询数据记录。两个表执行查询时，会对表先进行笛卡尔积，然后再选取符合匹配条件的数据记录。进行笛卡尔积操作时，会生成两个数表数据记录的乘积条数据记录，如果这两张表的数据记录比较大，则在进行笛卡尔积操作时就会造成死机。对于有经验的用户，首先会通过count()函数来统计操作表笛卡尔积后的数据记录数，，然后才会进行多表查询，因此多表查询一般会经过如下步骤：1、通过统计函数查询所关联表笛卡尔积后的数据记录数：select count(*) from t_dept,t_employee2、如果查询到的数据记录数mysql软件可以接受，然后进行多表连接查询，否则就应该考虑通过其他方式来实现。3、如果数据记录数mysql不能接受，则使用子查询来实现多表查询。子查询：就是在一个查询之中嵌套了其他的若干查询，即在select查询语句的where或from子句中包含另一个select查询语句。在查询语句中，外层的select查询语句成为主查询，where子句中的select查询语句被称为子查询，也被称为嵌套查询。通过子查询可以实现多表查询，该查询语句中可能包含in，any，all，exist等关键字，除此之外还可能包含比较运算符。理论上子查询可以出现在查询语句的任意位置，但是在实际开发中，子查询经常出现在where和from中where:该位置的子查询一般返回单行单列，多行单列，单行多列数据记录from：该位置的子查询一般返回多行多列数据记录，可以当做一张临时表+——-+——–+———–+——+———————+———+——–+——–+| empno | ename | job| mgr | hiredate| sale | comm | deptno |+——-+——–+———–+——+———————+———+——–+——–+| 7369 | smith | clerk| 7902 | 1981-03-12 00:00:00 | 800.00 | NULL |20 || 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 |30 || 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 |30 || 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL |20 || 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL |30 || 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL |30 || 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL |30 || 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL |10 |+——-+——–+———–+——+———————+———+——–+——–+a、返回结果为单行单列和单行多列子查询1、返回结果为单行单列子查询select sale from t_employee where ename=’smith’;select * from t_employeewhere sale>(select salefrom t_employeewhere ename=’smith’);+——-+——–+———–+——+———————+———+——–+——–+| empno | ename | job| mgr | hiredate| sale | comm | deptno |+——-+——–+———–+——+———————+———+——–+——–+| 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 |30 || 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 |30 || 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL |20 || 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL |30 || 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL |30 || 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL |30 || 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL |10 |+——-+——–+———–+——+———————+———+——–+——–+2、单行多列子查询where子句中的子查询除了是返回单行单列的数据记录外，还可以返回单行多列的数据记录，不过这种子查询很少出现mysql> select ename,sale,job-> from t_employee-> where (sale,job)=(->select sale,job->from t_employee->where ename=’smith’);+——-+——–+——-+| ename | sale | job |+——-+——–+——-+| smith | 800.00 | clerk |+——-+——–+——-+b、返回结果为多行单列子查询当子查询的返回结果为多行单列数据记录时，该子查询语句一般会在主查询语句的where子句里出现，通常会包含in，any，all，exist等关键字1、带有关键字in的子查询mysql> select * from t_employee;+——-+——–+———–+——+———————+———+——–+——–+| empno | ename | job| mgr | hiredate| sale | comm | deptno |+——-+——–+———–+——+———————+———+——–+——–+| 7369 | smith | clerk| 7902 | 1981-03-12 00:00:00 | 800.00 | NULL |20 || 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 |30 || 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 |30 || 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL |20 || 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL |30 || 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL |30 || 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL |30 || 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL |10 || 7676 | sandy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL |50 || 7678 | edy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL |60 |+——-+——–+———–+——+———————+———+——–+——–+当主查询的条件在子查询的查询结果里时，就可以通过关键字in来进行判断。相反，可以使用not inselect ename,deptofrom t_employeewhere deptno in (select deptno from t_dept);+——–+——–+| ename | deptno |+——–+——–+| smith |20 || alen |30 || ward |30 || jones |20 || martin |30 || ford |30 || black |30 || king |10 |+——–+——–+可以发现sandy和edy并没有打印出来select ename,deptofrom t_employeewhere deptno not in (select deptno from t_dept);+——-+——–+| ename | deptno |+——-+——–+| sandy |50 || edy |60 |+——-+——–+2、带有关键字any的子查询关键字any用来表示主查询的条件为满足子查询返回查询结果中任意一条数据记录，该关键字有三种匹配方式：=ANY：其功能与关键字IN一样>ANY(>=ANY):比子查询中返回数据记录中最小的还要大于（大于等于）数据记录<ANY(<=ANY):比子查询中返回数据记录中最大的还要小于（小于等于）数据记录例：select sale from t_employee where job=’manager’;+———+| sale |+———+| 2893.00 || 2850.00 || 3500.00 || 3500.00 |+———+select ename,job,sale from t_employee where sale < ANY (select sale from t_employee where job=’manager’);+——–+———-+———+| ename | job| sale |+——–+———-+———+| smith | clerk | 800.00 || alen | salesman | 1600.00 || ward | salesman | 1250.00 || jones | manager | 2893.00 |*****| martin | salesman | 1250.00 || ford | analyst | 3000.00 |***| black | manager | 2850.00 |+——–+———-+———+通过例子得知，any的意思满足子查询的任意一条记录，而不是所有的记录。3、带有ALL的子查询关键字ALL用来表示主查询的条件为满足子查询返回结果中所有数据记录，该关键字有两种匹配方式，分别为：>ALL(>=ALL):比子查询中返回数据记录中最大的还要大于（大于等于）数据记录<ALL(<=ALL):比子查询中返回数据记录中最小的还要小于（小于等于）数据记录select ename,job,sale from t_employee where sale < ALL (select sale from t_employee where job=’manager’);+——–+———-+———+| ename | job| sale |+——–+———-+———+| smith | clerk | 800.00 || alen | salesman | 1600.00 || ward | salesman | 1250.00 || martin | salesman | 1250.00 |+——–+———-+———+与any进行比较。4、带有关键字exist的子查询EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。c、返回结果为多行多列子查询当子查询的返回结果为多行多列数据记录时，该子查询语句一般会在主查询语句的from子句里，被当做一张临时表的方式来处理。例：执行sql语句select，于数据库company中，查询雇员表t_employee中各部门的部门号，部门名称，部门地址，雇员人数和平均工资。select * from t_employee;+——-+——–+———–+——+———————+———+——–+——–+| empno | ename | job| mgr | hiredate| sale | comm | deptno |+——-+——–+———–+——+———————+———+——–+——–+| 7369 | smith | clerk| 7902 | 1981-03-12 00:00:00 | 800.00 | NULL |20 || 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 |30 || 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 |30 || 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL |20 || 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL |30 || 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL |30 || 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL |30 || 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL |10 || 7676 | sandy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL |50 || 7678 | edy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL |60 |+——-+——–+———–+——+———————+———+——–+——–+select * from t_dept;+——–+————+———-+| deptno | dname| loc|+——–+————+———-+|10 | accounting | new york ||20 | researcher | dalls ||30 | sales| chicago ||40 | operation | boston |+——–+————+———-+t_dept和t_employee的结构和数据如上所示。解法一：内连接法select d.deptno,d.dname,d.loc,count(e.ename) number,avg(e.sale) averagefrom t_dept d inner join t_employee e on d.deptno=e.deptnogroup by d.deptno;+——–+————+———-+——–+————-+| deptno | dname| loc| number | average|+——–+————+———-+——–+————-+|10 | accounting | new york |1 | 5000.000000 ||20 | researcher | dalls |2 | 1846.500000 ||30 | sales| chicago |5 | 1990.000000 |+——–+————+———-+——–+————-+解法二：外连接法select d.deptno,d.dname,d.loc,count(e.ename) number,avg(e.sale) averagefrom t_dept d left outer join t_employee e on d.deptno=e.deptnogroup by d.deptno;+——–+————+———-+——–+————-+| deptno | dname| loc| number | average|+——–+————+———-+——–+————-+|10 | accounting | new york |1 | 5000.000000 ||20 | researcher | dalls |2 | 1846.500000 ||30 | sales| chicago |5 | 1990.000000 ||40 | operation | boston |0 |NULL |+——–+————+———-+——–+————-+解法三：子查询mysql> select d.deptno,d.dname,d.loc,number,average-> from t_dept d inner join->(select deptno dno,count(empno) number,avg(sale) average from t_employee group by deptno desc) employee->on d.deptno=employee.dno;+——–+————+———-+——–+————-+| deptno | dname| loc| number | average|+——–+————+———-+——–+————-+|30 | sales| chicago |5 | 1990.000000 ||20 | researcher | dalls |2 | 1846.500000 ||10 | accounting | new york |1 | 5000.000000 |+——–+————+———-+——–+————-+
并且如此真实的活着——这，就是旅行的意义。
