MySQL基础篇

MySQL基础篇（2）

1. 关于查询结果集的去重？
   　　　使用distinct关键字去重，并且distinct只能出现在所有字段的前面。
   　　　select distinct 字段名1,字段名2… from 表名；
   　　　select distinct job from emp;
   　　　select ename,distinct job from emp; // error
   　　　select distinct deptno,job from emp; // 多字段相当于看成一个大的字段来去重
   　　　统计岗位的数量？
   　　　select count(distinct job) from emp; //分组函数和distinct的联合使用
2. 连接查询
   　　2.1 什么是连接查询？
   　　　　在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终结果。
   　　　　在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。若将学生信息和班级信息存储到一张表中，数据就会存在大量的重复，导致数据冗余。
   　　　　
   　　2.2 连接查询的分类？
   　　　　根据语法出现的年代来划分，包括：
   　　　　　　SQL92（一些老的DBA可能还在使用这种语法。 DBA：DataBase Administrator，数据库管理员）
   　　　　　　SQL99（比较新的语法）
   　　　　根据表的连接方式来划分，包括：
   　　　　　　内连接：
   　　　　　　　　　等值连接
   　　　　　　　　　非等值连接
   　　　　　　　　　自连接
   　　　　　　外连接：
   　　　　　　　　　左外连接（左连接）
   　　　　　　　　　右外连接（右连接）
   　　　　　　全连接（极少用）
   　　　　　　
   　　2.3 在表的连接查询方面有一种现象被称为：笛卡尔积现象（笛卡尔乘积现象）。
   　　　　　笛卡尔积现象： 当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
   　　　　　关于表的别名：
   　　　　　　　select e.ename,d.dname from emp e,dept d;
   　　　　　　　表的别名有什么好处？
   　　　　　　　　　第一：执行效率高。
   　　　　　　　　　第二：可读性好。
   　　　　　　　　　
   　　2.4 怎样避免笛卡尔积现象？
   　　　　加条件进行过滤。
   　　　　思考：避免了笛卡尔积现象，会减少记录的匹配次数么？
   　　　　　　　不会，次数还是n表中记录数的乘积，只不过显示的是有效记录。
   　　　　找出员工的部门名称，要求显示员工名和部门名？
   　　　　　　　SQL92：select e.ename d.dname from emp e,dept d where e.deptno = d.deptno; //尽量少用SQL92
   　　　　　　　
   　　2.5 内连接之等值连接：最大特点是：条件是等量关系。
   　　　　查询每个员工的部门名称，要求显示部门名和员工名？
   　　　　　　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 inner join dept d on e.deptno = d.deptno ;
   　　　　　　　　　inner可以省略，带着inner的目的是可读性好一些。
   　　　　　　　　　select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
   　　　　　　　　　语法：
   　　　　　　　　　　　　…
   　　　　　　　　　　　　　　　Ａ
   　　　　　　　　　　　　join
   　　　　　　　　　　　　　　　Ｂ
   　　　　　　　　　　　　on
   　　　　　　　　　　　　　　　连接条件
   　　　　　　　　　　　　where
   　　　　　　　　　　　　　　　…
   　　　　　　　　　SQL99语法结构更加清晰一些：表的连接条件和后来的where条件分离了。
   　　　　　　　　　
   　　　2.6 内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。
   　　　　　找出每个员工的工资等级，要求显示员工名，工资，工资等级？
   　　　　　　　select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal; //其中inner可以省略
   　　　　　　　
   　　　2.7 内连接之自连接：最大的特点是：一张表看作两张表，自己连接自己。
   　　　　　找出每个员工的上级领导，要求显示员工名和对应的领导名？
   　　　　　select e1.ename as '员工名',e2.ename as '领导名’ from emp e1 inner join emp e2 on e1.mgr = e2.empno;

2.8 外连接？
　　　　　什么是外连接，和内连接有什么区别？
　　　　　　　内连接：
　　　　　　　　　假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
　　　　　　　　　AB两张表没有主副之分，两张表是平等的。
　　　　　　　外连接：
　　　　　　　　　假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带这查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
　　　　　　　　　
　　　　　外连接的分类？
　　　　　　　左外连接（左连接） ：表示左边的这张表是主表。
　　　　　　　右外连接（右连接）：表示右边的这张表是主表。
　　　　　　　左连接有右连接的写法，右连接也会有左连接的写法。
　　　　　　　
　　　　　找出每一个员工的上级领导？（所有的员工必须全部查询出来。）
　　　　　外连接（左外连接/左连接写法）：
　　　　　　　select e1.ename '员工‘,e2.ename '领导' from emp e1 left outer join emp e2 on e1.mgr =e2.empno; //outer可以省略
　　　　　外连接（右外连接/右连接写法）：
　　　　　　　select e1.ename '员工',e2.ename'领导‘ from emp e2 right outer join emp e1 on e1.mgr = e2.empno;//outer可以省略
　　　　　外连接最重要的特点是：主表的数据无条件的全部查询出来。
　　　　　找出哪个部门没有员工？
　　　　　　　select d.* from emp e right join dept d on e.deptno = d.deptno where e.deptno is null;//注意null的使用。
　　　　　　　
　　　2.9 三张表怎样连接查询？
　　　　　原理：
　　　　　　　…
　　　　　　　　　A
　　　　　　　join
　　　　　　　　　B
　　　　　　　on
　　　　　　　　　…
　　　　　　　join
　　　　　　　　　C
　　　　　　　on
　　　　　　　　　…
　　　　　　　表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接.
　　　　　找出每一个员工的部门名称以及工资等级?
　　　　　　　select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
　　　　　找出每一位员工的部门名称,工资等级,以及上级领导?
　　　　　　　select e1.ename '员工',d.dname,s.grade,e2.ename '领导' from emp e1 join dept d on e1.deptno = d.deptno join salgrade s on e1.sal between s.losal and s.hisal left join emp e2 on e1.mgr = e2.empno;

3. 子查询
   　　3.1 什么是子查询?子查询都可以出现在哪里?
   　　　　select语句当中嵌套select语句,被嵌套的select语句是子查询.
   　　　　子查询可以出现子哪里?
   　　　　　　select
   　　　　　　　　　…(select)
   　　　　　　from
   　　　　　　　　　…(select)
   　　　　　　where
   　　　　　　　　　…(select)
   　　　　　　　　　
   　　3.2 where子句中使用子查询。
   　　　　找出高于平均薪资的员工信息?
   　　　　select * from emp where sal > avg(sal) ❌分组函数不可直接用于where语句中。
   　　　　select * from emp where sal > (select avg(sal) from emp);
   　　　　
   　　3.3 from后面嵌套子查询。
   　　　　找出每个部门平均薪水的等级？
   　　　　思路：先找出每个部门的平均薪水，将其结果当作一个临时表
   　　　　select t.*,s.grade from (select deptno,avg(sal) as avg from emp group by deptno) t join salgrade s on t.avg between s.losal and s.hisal;
   　　　　找出每个部门平均的薪水等级？
   　　　　思路：并不是所有的处理都要使用临时表，要灵活。
   　　　　select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and hisal group by e.deptno;
   　　3.4 在select后面嵌套子查询。
   　　　　找出每个员工所在的部门名称，要求显示员工名和部门名
   　　　　select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
   　　　　或
   　　　　select e.ename ,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;//极少用看懂即可，可以参考动力节点杜老师

4. union(可以将查询的结果集相加）
   　　找出工作岗位是SALESMAN和MANAGER的员工？
   　　　　select ename,job from emp where job ='salesman' or job = 'manager';
　　　　select ename,job from emp where job in('salesman','manager');
　　　　select ename,job from emp where job = 'salesman' union select ename,job from emp where job = 'manager';
   　　将两张不相干的表中数据拼接在一起显示？
   　　select ename from emp union select dname from dept;
   　　但是查询的列数应该匹配，A表查询一个字段，则B表也应该查询一个字段。

5. limit（重点中的重点，分页查询主力）
   　　5.1 limit 是MySQL所特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
   　　
   　　5.2 limit取结果集中部分数据，这是它的作用。
   　　
   　　5.3 语法机制：
   　　　　limit startIndex,length
   　　　　　　startIndex表示起始位置，从0开始，0表示第一条数据。
   　　　　　　length表示取几个。
   　　　　取出工资前5名的员工（思路：降序取前5个）
   　　　　　　select ename,sal from emp order by sal desc limit 0,5;
　　　　　　select ename,sal from emp order by sal desc limit 5;
   　　　　　　
   　　5.4 limit 是SQL语句最后执行的环节：
   　　　　　select　　　５
   　　　　　　　　…
   　　　　　from　　　 １
   　　　　　　　　…
   　　　　　where　　　２
   　　　　　　　　…
   　　　　　group by　　３
   　　　　　　　　…
   　　　　　having　　　４
   　　　　　　　　…
   　　　　　order by　　６
   　　　　　　　　…
   　　　　　limit
   　　　　　　　　…;
   　　5.5 找出工资排名在第四到第九的员工？
   　　　　select ename,sal from emp order by sal desc limit 3,6;
   　　　　
   　　5.6 通用的标准分页SQL？
   　　　　每页显示pageSize条记录：
   　　　　第pageNo页：（pageNo - 1）* pageSize，pageSize
   　　　　pageSize是什么？是每页显示多少条记录
   　　　　pageNo是什么？显示第几页

Java代码｛
　　　　　　　int pageNo = 2 ; // 页码是2
　　　　　　　int pageSize = 10; //每页显示10条
　　　　　　　limit（pageNo-1）*pageSize，pageSize
　　　　｝

6. 创建表：
   　　　建表语句的语法格式：
   　　　　　create table 表名 (
   　　　　　　　　字段名1　数据类型　约束1,
   　　　　　　　　字段名2　数据类型　约束2,
   　　　　　　　　字段名3　数据类型　约束3,
   　　　　　　　　…
   　　　　　　　　);
   　　　　　其中约束根据建表的具体情况进行要求，没有可不写。
   　　　　　关于MySQL当中字段的数据类型？（常见的几种）
   　　　　　　　　int　　　整数型（Java中的int）
   　　　　　　　　bigint　　　长整型（Java中的long）
   　　　　　　　　float　　　浮点型（Java中的float double）
   　　　　　　　　char　　　定长字符串（Java中的String）
   　　　　　　　　varchar　　　可变长字符串（Java中的StringBuffer/StringBuilder）
   　　　　　　　　date　　　日期类型（Java中的java.sql.Date类型）
   　　　　　　　　BLOB　　　二进制大对象（存储图片、视频等流媒体信息）Binary Large OBject （Java中的Object）
   　　　　　　　　CLOB　　　字符大对象（存储较大的文本，比如可以存储4G的字符串） Character Large OBject （Java中的Object）
   　　　　　　　　…
   　　　　　　　　
   　　　　　char和varchar怎样选择？
   　　　　　　　　在实际开发中，当某个字段的数据长度不发生改变的时候，是定长的。例如：性别、生日（不带时分秒定长10，带时分秒定长19）等都是采用char。
   　　　　　　　　当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
   　　　　　　　　
   　　　　　BLOB和CLOB类型的使用？
   　　　　　　　　如电影表的创建，可以用BLOB来存储海报，简视频等。用CLOB用来存储电影的简介，但实际开发中考虑到多重因素像海报、简视频等一般放在文件夹里，表里存储的是相关路径。
   　　　　　
   　　　　　表名在数据库中起名的建议？
   　　　　　　　　以t_或者tbl_ 开始。

7.insert 语句插入数据
　　　语法格式：
　　　　　insert into 表名（字段名1，字段名2…)values(值1，值2…)
　　　　　要求：字段的数量和值的数量相同，并且数据类型要求对应相同。
　　　insert into t_student (no,name,sex,birth) values(1,‘lisi’,‘1’); ❌
　　　　　当列出字段名时插入值的数量及顺序一定要和字段名的数量和顺序相同。
　　　insert into t_student (no,name,sex,birth) values(1,'lisi','1','1950-08-02');
　　　insert into t_student(name) values('wangwu'); / / 除name字段,其余字段自动插入NULL
　　　需要注意的地方:
　　　　　当一条insert语句执行成功之后,表格当中必然会多一行记录.
　　　　　即使多的这一行记录当中的某些字段时NULL,后期也没有办法在执行insert语句插入数据了,只能使用update进行更新。
　　　
　　　字段名可以省略不写，但是后面的value对数量和顺序都有要求。不能少写，当插入值对应的字段有默认值时，可以不插入相关值，表中默认插入默认值。
　　　
　　　一次也可以插入多行数据。
　　　　　insert into 表名（字段名1，字段名2…) values(值1，值2…),(值1，值2…);
　
8. 表的复制
　　　语法：
　　　　　　create table 表名 as select 语句；
　　　　　　将查询结果当作表创建出来。

9. 将查询结果插入到一张表中？
   　　　　语法：
   　　　　　　insert into 表名 select 字段名 from 表名
   　　　　　　例：insert into dept1 select * from dept;

10. 修改数据：update
    　　　语法格式：
    　　　　　　update 表名 set 字段名1=值1,字段名2=值2 …where 条件；
    　　　注意：当没有添加条件时整张表数据全部更新。
    　　　
    　　　将部门10的LOC修改成为SHANGHAI，将部门名称修改为RENSHIBU?
    　　　　　　update dept1 set loc ='SHANGHAI',dname='RENSHIBU' where deptno =10;
    　　　更新所有记录： update dept1 set loc='x',dname = 'y';

11. 删除数据：delete
    　　　语法格式：
    　　　　　delete from 表名 where 条件；
    　　　注意：没有条件全部数据删除，但表还存在。
    　　　删除10部门数据？
    　　　　　delete from dept1 where deptno=10;
    　　　删除所有记录？
    　　　　　delete from dept1;
    　　　怎样删除大表中的数据？（重点）
    　　　　　大表：上亿万、千万、百万等大量的数据
    　　　　　truncate table 表名; // 表被截断，不可回滚，永久丢失。
    　　　删除表？
    　　　　　drop table 表名; // 这个通用。
    　　　　　drop table if exists 表名; // Oracle不支持这种写法。

12. 增删改查
    　　增删改查有一个术语：CRUD操作
    　　Create（增），Retrieve（检索），Update（修改），Delete（删除）

13. 约束（Constraint）
    　　 什么是约束？常见的约束有哪些？
    　　　　　在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中的数据的合法性、有效性、完整性。
    　　　　　常见的约束有哪些？
    　　　　　　　非空约束（not null) : 约束的字段不能为NULL。
    　　　　　　　唯一约束（unique）：约束的字段不能重复。
    　　　　　　　主键约束（primary key)：约束的字段既不能为NULL，也不能重复（简称PK）。
    　　　　　　　外键约束（foreign key)：…简称（FK）。
    　　　　　　　简称约束（check）：注意Oracle数据库中有check约束，但MySQL没有，目前MySQL不支持该约束。

注：全文根据动力节点杜聚宾老师的资料整理的个人笔记，交流学习之需，切勿与利益挂钩，再次感谢杜老师的详细讲解。