| 数据库问题 | ||
15.2 连接查询的分类 15.2.1 根据语法年代分类 SQL92：1992年的时候出现的语法 SQL99：1999年的时候出现的语法 select ... from 表一 (left/right 这两个在外连接的时候使用) (inner\outer)join inner 、outer都可以省略，写inner表示内连接 outer表示外连接 表二 on 表一和表二的条件 where 复制代码 SQL92的缺点和SQL99的优点 SQL92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件都放到了where后面 SQL99的优点：表连接的条件是独立的，连接以后，如果还需要进一步筛选，可以继续往后添加where条件。 在这里重点学习SQL99 15.2.2 根据表连接的方式分类 15.2.2.1 内连接 等值连接 非等值连接 自连接 15.2.2.2 外连接 左外连接(左连接) 右外链接(右连接) 15.2.2.3 全连接 15.3 表连接的引入问题(笛卡尔积现象) 案例：当两个表进行连接查询的时候，没有任何条件会出现什么现象? mysql> select ename,loc from emp,dept; +--------+----------+ | ename | loc | +--------+----------+ | SMITH | NEW YORK | | SMITH | DALLAS | | SMITH | CHICAGO | | SMITH | BOSTON | | ALLEN | NEW YORK | | ALLEN | DALLAS | | ALLEN | CHICAGO | | ALLEN | BOSTON | | WARD | NEW YORK | | WARD | DALLAS | | WARD | CHICAGO | |...... | +--------+----------+ 56 rows in set (0.00 sec) 最总显示出的数据是56条 复制代码 出现的现象：当两个表查询的时候，没有任何条件的限制的话话，最终显示出来的条数是两个表条数的乘积。也被称为笛卡尔积现象。 15.3.1 笛卡尔积的避免 注意：使用where条件虽然显示出来的数据减少了，但是在匹配的时候依然按照笛卡尔积现象进行匹配，只不过进行了四选一，次数没有减少。 添加where条件 mysql> select ename,loc from emp,dept where emp.deptno=dept.deptno; +--------+----------+ | ename | loc | +--------+----------+ | CLARK | NEW YORK | | KING | NEW YORK | | MILLER | NEW YORK | | SMITH | DALLAS | | JONES | DALLAS | | SCOTT | DALLAS | | ADAMS | DALLAS | | FORD | DALLAS | | ALLEN | CHICAGO | | WARD | CHICAGO | | MARTIN | CHICAGO | | BLAKE | CHICAGO | | TURNER | CHICAGO | | JAMES | CHICAGO | +--------+----------+ 14 rows in set (0.00 sec) 复制代码 使用别名 mysql> select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno; +--------+----------+ | ename | loc | +--------+----------+ | CLARK | NEW YORK | | KING | NEW YORK | | MILLER | NEW YORK | | SMITH | DALLAS | | JONES | DALLAS | | SCOTT | DALLAS | | ADAMS | DALLAS | | FORD | DALLAS | | ALLEN | CHICAGO | | WARD | CHICAGO | | MARTIN | CHICAGO | | BLAKE | CHICAGO | | TURNER | CHICAGO | | JAMES | CHICAGO | +--------+----------+ 14 rows in set (0.00 sec) 复制代码 根据笛卡尔积现象，表的连接次数越多效率越低 15.4 内连接(inner) 15.4.1 等值连接 案例：查询每个员工所在部门名称，显示员工名和部门名。 SQL92： select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; 复制代码 SQL99: select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; //条件是一个等量关系 复制代码 15.4.2 非等值连接 案例：找出每个员工的薪资等级，要求显示员工名，薪资和薪资等级。 select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; //条件不是一个等量关系 复制代码 15.4.3 自连接 技巧：把一张表看成两张表。 案例：查询员工的上级领导，要求显示员工名和对应的领导名。 mysql> select -> a.ename,b.ename -> from -> emp a -> join -> emp b -> on -> a.mgr= b.empno; +--------+-------+ | ename | ename | +--------+-------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+-------+ 13 rows in set (0.00 sec) 因为KING是老板，没上级领导 复制代码 15.5 外连接(outer) 案例：查询每个员工所在部门名称，显示员工名和部门名，并且没有员工的部门也要显示出来。 15.5.1 右外连接 (右连接) select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | | NULL | OPERATIONS | +--------+------------+ 15 rows in set (0.00 sec) 复制代码 15.5.2 左外连接(左连接) select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | CLARK | ACCOUNTING | | KING | ACCOUNTING | | MILLER | ACCOUNTING | | SMITH | RESEARCH | | JONES | RESEARCH | | SCOTT | RESEARCH | | ADAMS | RESEARCH | | FORD | RESEARCH | | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | | NULL | OPERATIONS | +--------+------------+ 15 rows in set (0.00 sec) 复制代码 注意：!!!!!!!!!!!! 外连接中right 和left的区别： right：表示将join关键字的右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表 left：表示将join关键字的左边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询右边的表 在外连接中，两张表连接，产生了主次关系。 左右连接可以互相转换 15.5.3 案例 查询员工的上级领导，要求显示员工名和对应的领导名以及没有领导的员工也要显示。 mysql> select -> a.ename as '员工姓名' -> ,b.ename as '领导名' -> from -> emp a -> left join emp b -> on -> a.mgr=b.empno; +----------+--------+ | 员工姓名 | 领导名 | +----------+--------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | KING | NULL | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +----------+--------+ 14 rows in set (0.00 sec) 复制代码 15.6 多表连接 select ... from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件 .... .... 复制代码 一条SQL语句内外连接可以混合使用 15.6.1 案例 找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级 select e.ename,d.dname,e.sal,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and hisal; 复制代码 找出每个员工的部门名称以及工资等级，还有上级领导。要求显示员工名、领导名、部门名、薪资、薪资等级 select e.ename,d.dname,e.sal,s.grade,l.ename as '领导' from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and hisal left join emp l on e.mgr=l.empno; +--------+------------+---------+-------+-------+ | ename | dname | sal | grade | 领导 | +--------+------------+---------+-------+-------+ | SMITH | RESEARCH | 800.00 | 1 | FORD | | ALLEN | SALES | 1600.00 | 3 | BLAKE | | WARD | SALES | 1250.00 | 2 | BLAKE | | JONES | RESEARCH | 2975.00 | 4 | KING | | MARTIN | SALES | 1250.00 | 2 | BLAKE | | BLAKE | SALES | 2850.00 | 4 | KING | | CLARK | ACCOUNTING | 2450.00 | 4 | KING | | SCOTT | RESEARCH | 3000.00 | 4 | JONES | | KING | ACCOUNTING | 5000.00 | 5 | NULL | | TURNER | SALES | 1500.00 | 3 | BLAKE | | ADAMS | RESEARCH | 1100.00 | 1 | SCOTT | | JAMES | SALES | 950.00 | 1 | BLAKE | | FORD | RESEARCH | 3000.00 | 4 | JONES | | MILLER | ACCOUNTING | 1300.00 | 2 | CLARK | +--------+------------+---------+-------+-------+ 14 rows in set (0.00 sec) 复制代码 十六、子查询 16.1 什么是子查询 select语句中嵌套select语句，被嵌套的select语句称为子查询。 16.2 子查询可以出现在哪里? select ...(select)... from ...(select)... where ...(select)... 复制代码 16.2.1 where中的子查询 案例：找出比最低工资高的员工姓名和工资。 第一步：先找出最低工资 select min(sal) from emp; mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ 1 row in set (0.01 sec) 复制代码 第二步：找出大于800的工资 select ename,sal from emp where sal>800; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 13 rows in set (0.00 sec) 复制代码 第三步：合并 select ename,sal from emp where sal>(select min(sal) from emp); +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 13 rows in set (0.00 sec) 复制代码 16.2.2 from中的子查询 注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。(技巧) 案例：查询每个工作岗位平均工资的工资等级 第一步： 查询每个工作岗位的平均工资 select job,avg(sal) from emp group by job; +-----------+-------------+ | job | avg(sal) | +-----------+-------------+ | ANALYST | 3000.000000 | | CLERK | 1037.500000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | | SALESMAN | 1400.000000 | +-----------+-------------+ 复制代码 第二步:把查找出来的每个工作岗位的平均工资得这张表，看成是数据库存在的一张表t,然后查找t表中的工资等级 select t.* ,s.grade from t join salgrade s on t.avg(sal) between s.losal and s.hisal; 复制代码 第三步：合并 select t.* ,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal; +-----------+-------------+-------+ | job | avgsal | grade | +-----------+-------------+-------+ | CLERK | 1037.500000 | 1 | | SALESMAN | 1400.000000 | 2 | | ANALYST | 3000.000000 | 4 | | MANAGER | 2758.333333 | 4 | | PRESIDENT | 5000.000000 | 5 | +-----------+-------------+-------+ 5 rows in set (0.10 sec) 复制代码 16.2.3select后面出现的子查询(了解) 案例：找出每个员工的部门名称，要求显示员工名，部门名 select e.ename,(select d.dname from dept as d where e.deptno=d.deptno) as dname from emp e; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec) 复制代码 注意!!!!!!!!!!!! 对于select后面的子查询来说，这个子查询只能一次返回一条结果，多余一条就会报错。 十七、union(合并查询结果集) 案例：查询工作岗位是’MANAGER’和’SALESMAN’的员工。 不使用union select ename,job from emp where job='MANAGER' or job='SALESMAN'; 或者使用in() select ename,job from emp where job in('MANAGER','SALESMAN'); +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+ 7 rows in set (0.00 sec) 复制代码 使用union(效率高) select ename,job from emp where job='MANAGER' union select ename,job from emp where job='SALESMAN'; +--------+----------+ | ename | job | +--------+----------+ | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | | ALLEN | SALESMAN | | WARD | SALESMAN | | MARTIN | SALESMAN | | TURNER | SALESMAN | +--------+----------+ 7 rows in set (0.00 sec) 复制代码 使用union的注意事项 union在进行结果集合并的时候，要求两个结果集的列数相同，不然会报错 错误写法示范： select ename,job from emp where job='MANAGER' union select ename from emp where job='SALESMAN'; 复制代码 结果集合并时列和列的数据类型也要相同。 错误写法示范： select ename,job from emp where job='MANAGER' union select ename,sal from emp where job='SALESMAN'; 虽然在MySQL中依然能运行，但是在Oracle中不能运行，报错。 复制代码 十七、limit(重点!!!!!!!!!) 17.1 用法 完整用法：limit startIndex ,length startIndex：起始下标 ，从零开始 length：长度 缺省用法：limit 5 取出前五的数据 注意：!!!!!!!!!!!!!!!!!!!!!!!!! limit在 order by 之后执行!!!!!!!!!!!!!!!!!!!!! SQL语句中 order by 出现在 limit 前面 select ename,sal from emp order by sal limit 5 ; 复制代码 17.2 分页 int pageNO //第几页 int pageSize //每页显示的条数 limit (pageNO-1)*pageSize ,pageSize; 复制代码 十八、DDL 18.1 表的创建 18.1.1 正常创建表 create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型, 字段名4 数据类型 ); 复制代码 表名：建议以t_ 或者tbl_开始，可读性强，见名知意 表名：见名知意 表名和字段名都属于标识符 18.1.2 快速创建表(表的复制) create table 新建的表名 as select 字段名 from 要复制的表名; 复制代码 原理：把查询结果当做一张表新建!!!!!! 18.2 常见的数据类型 vachar(最长255) 可变长度的字符串，比较智能，节省空间 会根据司机的数据长度动态的分配空间 优点：节省空间 缺点“需要动态的分配空间，速度慢 char(最长255) 定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据 使用不恰当的时候，可能会导致空间的浪费。 优点：不需要动态分配空间，速度快 缺点：使用不当可能会导致空间的浪费 vachar和char的选择： 固定长度选char 例如：性别 不固定选vachar int(最长11) 数字中的整数型，等同于java中的int bigint 数字中的长整型，相当于java中的long float 单精度浮点型数据 double 双精度浮点型数据 date 短日期类型 datetiom 长日期类型 clob 字符大对象 最多可存储4G的字符串 比如存储一篇文章，存储一个说明 blob 二进制大对象 专门用来存储图片、声音、视频等流媒体数据 往BLOB类型的字段插入数据的时候 例如插入一个图片、视频等 你需要使用IO流 18.3 删除表 drop table 表名;//当这张表不存的的时候会报错 //如果这张表存在的话，删除 drop table if exists 表名; 十九、DML 19.1 插入数据(insert) 19.1.1一条记录插入 insert into 表名(字段名1，字段名2，字段名3，字段,4) values (值1，值2，值3，值4) 注意： insert语句一旦执行成功，就意味着数据库中会多一条数据 insert into 表名 values(值); 如果表名后面不写字段名的话，values后面的值必须是把该表中的字段一一对相应，数据都要进行添加。 案例如下： insert into t_student values(2021,'ww',25,'m','11@qq.com'); 复制代码 19.1.2 插入日期(MySQL默认日期格式是%Y-%m-%d) 19.1.2.1 MySQL日期格式 %Y 年 %m 月 %d 日 %h 时 %i 分 %s 秒 str_to_date('1990-10-10','%Y-%m-%d'); 复制代码 如果字符串格式按年-月-日来写的话，str_to_date可以省略 insert into t_user(birth) values('1990-12-15'); //因为这样mysql会进行自动类型转换 +------+------------+------+ | id | birth | name | +------+------------+------+ | NULL | 1999-12-05 | NULL | | NULL | 1985-10-10 | NULL | | NULL | 1990-12-15 | NULL | +------+------------+------+ 复制代码 注意： 如果插入的日期格式是MySQL的默认格式，不用使用str_to_date函数进行转换，MySQL会自动转换，将字符串类型转化为日期 如果查询日期的格式是MySQL的默认格式，不用使用date_format函数进行转换，MySQL会自动转换，将日期类型转化为字符串 19.1.2.2 date 和datetime的区别 date是短日期：只包括年月日 默认格式：%Y-%m-%d datetime是长日期:包括年月日时分秒 默认格式：%Y-%m-%d %h:%i:%s 19.1.2.3 获取系统当前时间 now() now() 带有时分秒信息 19.1.2.4 将查询结果插入到一张表中 insert into dept_no select * from dept; 将dept表中的数据插入到dept_no中; 注意：两张表得字段要一样 +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 复制代码
|
关注官方微信