[大数据]mysql基础知识点总结（二）

二.多表查询

1.去除重复记录（把查询结果去重，不改变原表的记录）distinct

select ename，distinct job from emp；//这样写是错误的，distinct只能出现在所有字段的最前方。

select distinct job，deptno from emp；//distinct出现在job，deptno两个字段之前，表示两个字段联合起来去重。

统计一下工作岗位的数量 select count（distinct job）from emp；

2.连接查询（很重要）

2.1什么是连接查询？多张表联合起来查询数据，被称为连接查询。

2.2连接查询的分类？

根据语法的年代分类？1992年sql92；1999年 sql99；

内连接（等值连接、非等值连接、自连接）和外连接（左外连接，右外连接）

2.3当两张表进行连接时，没有任何限制，则按照笛卡儿积的方式进行一个连接。最终查询结果的条数时两张表条数的乘积；

2.4 避免笛卡儿积现象，就是条件数目必须大于等于连接表数量-1，eg 两种表至少一个条件限制。三张表至少两个条件限制；

注意因为笛卡儿积现象的存在，表的连接次数越多效率越低，尽量避免表的连接次数。

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//要起别名，提高性能 sql92语法

2.5、内连接之等值连接

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//sql92

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。句子杂糅。

select e.name,d.dname from emp e join dept d on e.deptno=d.depton;//sql99

//sql99的语法 ?select ... from a inner join b on a和b的连接条件 ?where 筛选条件 //inner可以省略，带着可读性更好

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，在往后继续添加where条件

2.6、内连接之非等值连接

??案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

2.7、内连接之自连接

案例：查询员工的上级领导，要求显示员工和对应的领导名？//技巧：一张表看成两张表

select a.ename as '员工名'，b.ename as '领导们' from emp a join emp b on a.mgr=b.empno;//员工的领导编号==领导的员工编号

//内连接的特点：完成能够匹配上这个条件的数据查询出来。

2.8、外连接（）

右外连接（右连接）带有right

select e.ename,d.dname from emp e ?right join dept d on e.deptno=d.deptno;//right代表什么，表示将join关键字右边的这张表

看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。

左外连接（左连接）带有left

select e.ename,d.dname from dept d left join emp e ?on e.deptno=d.deptno;

任何一个右连接都会有左连接的写法。任何一个左连接都会有右连接的写法。

结论：外连接的查询结果条数一定是>=内连接的查询结果条数

案例:查询每个员工的上级领导，要求显示所有的员工的名字和领导名？

select a.ename as '员工名'，b.ename as '领导名' from emp a left join emp b on a.mgr=b.empno;//左边是主表

2.9、三张表或者四张表怎样连接？

select ... from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件

一条sql中内连接和外连接都可以混合。都可以出现！

案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？

select e.ename,e.sal,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 e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno =d.deptno join salgrade s on ?e.sal between s.losal and s.hisal left join emp l on e.mgr=l.empno;

3、子查询

3.1、什么是子查询？

select语句中嵌套select语句，被嵌套的select 语句被称为子查询。

3.2、子查询都乐意出现在哪里呢？

select...（select）from ... （select）where ...（select）；

3.3、where子句中的子查询

案例：找出比最低工资高的员工姓名和工资？

select ename，sal from emp where sal>(select min(sal) from emp);

3.4、from子句的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

案例：找出每个岗位的平均工资的自信等级。

select t.*,s.grade from (select job,avg（sal）as avgsal from emp group by job) as t join salgrade s on t.avgsal between s.losal and s.hisal;

3.5、select子句的子查询（只需要了解即可）

案例：找出每个员工的部门名称，要求显示员工名，部门名？

select e.ename,e.deptno,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

//要求嵌套的子语句中的查询结果必须只有一个结果，才可以在select后使用子查询

3.6、 union合并查询结果集

案例：查询工作岗位是manger和salesman的员工？

select ename，job from emp where job=‘manger’or job='salesman';

select ename，job from emp where job in（‘manger','salesman'）;

select ename，job from emp where job=‘manger’

union

select ename，job from emp where job=‘saleman’;

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡儿积，成倍的翻... 10*10=100 ?100*100=10000

但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。100+100=200；

//union在进行结果集合并的时候，要求两个结果集的列数和数据类型要相同。

4.limit

4.1、limit作用：将查询结果集的一部分取出来，通常使用在分页查询当中。

4.2 limit怎么用呢？

完整用法：limit startIndex ，length （startIndex是起始下标，默认从0开始，length是长度）

缺省用法：limit 5；这是取前5。

select ename，sal from emp order by sal desc limit 5;//取前5条

select ename，sal from emp order by sal desc limit 0,5;//取前0-5条

?4.3、mysql中limit在order by之后执行！！！！！

4.4、取出工资排名在[3-5]名的员工？

select ename，sal from emp order by sal desc limiit 2，3；（2表示起始下标，3表示长度）

4.5、分页

每页显示3条记录 第一页 limit 0,3 ??第二页 limit 3,3 第三页 limit 6,3;

每页显示pageSize条记录

第pageNo页：limit （pageNo-1）*pageSize ?，pageSize

int pageNo=5；//第5页

int pageSize=10;//每页显示10条

int startIndex=（page-1）*pageSize；

String sql=”select ...limit“+startIndex+”，“+pageSize；

5、关于DQL语句的大总结：

select ... from ...where...group by...having...order by ...limit ... //语法顺序

from>where>group by>having>select>order by>limit ??//执行顺序

6、表的创建（建表）

6.1、建表的语法格式：（建表属于DDL语句，DDL包括 create drop alter）

create table 表名（字段名1 数据类型，字段名2 数据类型）；

6.2 关于mysql的数据类型？

varchar 可变的字符类型，动态的分配空间，性能较低，优点，不浪费空间

char 分配一个固定的字符长度，性能高，定长字符串，使用不当，浪费空间

varchar 和char什么时候使用哪个？ 当字符固定用char，字符是变化的情况下用varchar；

int ?相当于java中的int

bigint 相当于java中的long

float 单精度浮点型

double 双精度浮点型

date 短日期类型 默认输入格式 1910-12-08

datetime 长日期类型 默认输入格式 1999-12-08 09:53:32

clob 字符大对象，最多可以存储4G的字符串。

blob 二进制大对象，专门用来存储图片、声音、视频等媒体流数据。往Bloob类型的字段上插入数据的时候，例如插入图片、视频等，你需要使用IO流才行。

删除表不报错办法：drop table ?if exists t_student;

6.3创建学生表？

create table t_student(no int,name varchar(32),sex char(1),age int(3),email varchar(255));

6.4、插入数据insert （DML）

语法格式：insert into 表名 （字段名1，字段名2...）values （值1，值2...）；

注意：字段名和值要一一对相应，数量要对应。数据类型要对应。

insert into t_student(email,name,sex,age,no) values ('lise@123.com','lise','f',20,2);

insert 一次插入多行记录方法？

instert into t_user(id,name,birth,create_time) values (1,'zs','1980-10-11',now()),(2,'lisi','1981-10-11',now()),(3,'niuniu','1971-10-11',now());

建表的时候给默认值

create table t_student(no int,name varchar(32),sex char(1) default 'm',age int(3),email varchar(255));

insert into t_student values(2,'lise','f',20,'lise@123.com');//注意 前面的字段名省略的话，等于都写上了！，所以值也要都写上！

6.5、insert 插入日期

格式化数字：format（数字，'格式'）

select ename,format(sal,'$999,999') as sal from emp;

str_to_date:将字符串varchar类型转换成date类型

date_format:将date类型转换成具有一定格式的varchar字符串类型。

str_to_date('字符串日期','日期格式') ?eg:str_to_date('01-10-1990','%m/%d/%Y')

mysql的日期格式：%Y年 %m月 %d 日 %h 时 %i 分 %s 秒

date_format 这个函数可以将日期类型转换成特定格式的字符串。

date_format（日期类型数据，'日期格式'）eg：date_format(birth,'%m/%d/%Y')

6.6、datetime与date大体类似 只是datetime显示的更加具体。

在mysql中有一个可以获取系统当前时间的函数 ?????now（）函数 是datetime类型的

6.7、修改update（DML）

语法格式：

update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3 ... where 条件；

注意：没有条件限制会导致所有的数据全部更新。

update t_user set name='jack',birth='2000-10-11' where id=2;

更新所有？ update t_user set name='abc';

6.8、删除数据delete（DML）

语法格式？delete from 表名 where 条件；

注意：没有条件，整张表的数据会全部删除！

delete from t_user where id=2;

insert into t_user(id) values(2);

delete from t_user;//删除所有！

7、快速创建表？

create table emp2 as select * from emp；

原理：将一个查询结果当做一张表新建！！这个可以完成表的快速复制！！表创建出来，同时表中的数据 也存在了！！

insert into dept_bak select * from dept;//很少用！

将查询结果插入到dept_bak表中。

8、快速删除表中的数据？

8.1

delete from dept dept_back;//这种删除数据的方式比较慢，属于DML语句。

delete删除表中数据的原理，一个一个删，空间不会释放，数据可以恢复，缺点是删除效率比较低。优点是：支持回滚，后悔了可以恢复数据。

8.2

truncate语句删除数据的原理？

这种删除效率比较高，表被一次截断，物理删除。

删除的缺点是：不支持回滚。

这种删除优点：快速。

用法：truncate table dept_bak;(这种操作属于DDL操作。)！！！！注意无法恢复，要提醒用户。

9、约束

9.1、什么是约束？constraint（约束 五颗星*****）

约束的作用是为了保证：表中的数据有效！！

9.2、约束的种类？

非空约束：not null ?*

唯一性约束：unique ?*

主键约束：primary key（简称pk） *

外键约束：foreign key（简称fk） ?*

检查约束：check（mysql不支持，oracle支持）

9.3、非空约束写法？

create table t_vip(id int,

name varchar(255) not null);

9.4、唯一性约束写法？

create table t_vip（

id int，

name varchar(255) unique,

email varchar(255)）;

name字段必须唯一，不可以出现重复的字符串，但是可以为null。

两个字段联合起来具有唯一性。

create table t_vip（

id int，

name varchar(255) ,

email varchar(255)，

unique（name，email）

）;

unique和not null联合起来使用之后会变成一个主键

create table t_vip（

id int，

name varchar(255) not null unique ,

email varchar(255)，

）;

9.5、主键约束（primary key，简称pk）

主键值是每一行记录的唯一标识，主键是每一行记录的身份证号！！！

记住：任何一张表都应该有主键，没有主键，表无效！！

主键的特征：not null+unique（主键值不能是NULL,同时也不能重复！）

给一张表添加主键的语法？

drop table if exists t_vip; ????| ???drop table if exists t_vip; ?

create table t_vip( ??????????????| ??create table t_vip(

id int primary key, ??????????????| ??id int ,

name varchar(255) ??????????| ??name varchar(255) ,

); ????????? ??????????| primary key(id,name)

//id 作主键，也可以单独写 ??| ); ?//id和name联合作主键

在实际开发中不建议使用：复合主键，建议使用单一主键！因为主键值的作用是身份证号，意义达到即可。

一张表中，主键约束只能添加1个。（主键只能有一个） 建议使用int bigint，char类型作主键，不建议使用varchar类型

作主键。在实际开发中最好使用自然主键，因为主键只要做到不重复，不需要有意义。

9.6、外键约束（foreign key，简称fk）非常重要五颗星*****

为了保证子表跟父表的数据一致，所以要给子表添加外键约束。删除表的时候，要先删子表，才能删父表。

create table t_class( create table t_student(

classno int primary key, no int primary key auto_increment,

classname varchar(255) name varchar(255),

); cno int,

//父表，外键语法如后所示 foreign key(cno) references t_class(classno)

//外键只能写绑定的字段的数据 );

注意：外键值可以为null，外键绑定的字段必须具有唯一性，但不要求一定是主键

---------------------------------------------------------------------------------------------------------------------------------