文章目录
- 1.连接查询
- 1.1 概念
- 1.2 连接查询的分类
- 1.3 连接查询原理以及笛卡尔积现象【笛卡尔乘积现象】
- 1.4 避免笛卡尔积现象
- 1.5 内连接
- 1.5.1 等值连接
- 1.5.2 非等值连接
- 1.5.3 自连接
- 1.6 外连接
- 1.7 三张表进行连接查询
- 2.子查询
- 2.1 where后面嵌套子查询
- 2.2 from后面嵌套子查询
- 2.3 select后面嵌套子查询
- 3.union的用法
- 4.limit【分页查询全靠它】
- 4.1 基础概念
- 4.2 通用的标准分页sql
- 5.表
- 5.1 基础概念
- 5.2 创建表
- 5.3 插入数据
- 5.4 表的复制以及批量插入
- 5.5 修改表中的数据
- 5.6 删除表中的数据
- 5.7 关于表结构的修改
1.连接查询
1.1 概念
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果
在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表【如果存在一张表中，会存在大量重复的数据，导致数据的冗余】
1.2 连接查询的分类
根据语法出现的年代划分：
- SQL92(一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator,数据库管理员)
- SQL99(比较新的语法)
根据表的连接方式来划分：
- 内连接：
等值连接
非等值连接
自连接 - 外连接
左外连接(左连接)
右外连接(右连接) - 全连接【这个不讲，很少用】
1.3 连接查询原理以及笛卡尔积现象【笛卡尔乘积现象】
笛卡尔现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积
案例：找出每个员工的部门名称，要求显示员工名和部门名
表的别名:select e.ename,d.dname from emp e ,dept d;
表的别名优点：
- 执行效率高
- 可读性好
1.4 避免笛卡尔积现象
方法：加条件进行过滤【不会减少记录的匹配次数，只不过显示的是有效记录】
上面案例的解决语句：
select e.ename,d.dname
from emp e ,dept d
where e.deptno = d.deptno;【SQL92语法，以后不用】
1.5 内连接
1.5.1 等值连接
最大特点：条件是等量关系
案例：找出每个员工的部门名称，要求显示员工名和部门名
SQL99【常用的】：
select e.ename,d.dname
from emp e
join dept d
on e.deptno = d.deptno;
语法：
...
A
inner join //inner可以省略，带着inner目的是可读性好一些
B
on
连接条件
where
...注意：SQL99语法结构更清晰一些，表的连接条件和后来的where条件分离了
1.5.2 非等值连接
最大特点：连接条件中的关系是非等量关系
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级
1.5.3 自连接
最大特点：一张表看做两张表，自己连接自己
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名
1.6 外连接
外连接最重要的特点：主表的数据无条件的全部查询出来
连接 | 概念 |
内连接 | 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分,两张表是平等的 |
外连接 | 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配 |
外连接 | 概念 |
左外连接(左连接) | 表示左边的这张表是主表 |
右外连接(右连接) | 表示右边的这张表是主表 |
注意：左连接有右连接的写法，右连接也会有对应的左连接的写法
案例：找出每个员工的上级领导(所有员工必须全部查询出来)
案例：找出哪个部门没有员工
1.7 三张表进行连接查询
案例：找出每一个员工的部门名称以及工资等级
表示：A表和B表先进行连接，连接之后A表继续和C表进行连接
...
A
join
B
join
C
on
...
案例：找出每一个员工的部门名称、工资等级、以及上级领导
2.子查询
子查询：select语句当中嵌套select语句，被嵌套的 select语句是子查询
子查询可以出现在：
select
..(select)
from
..(select)
where
..(select)2.1 where后面嵌套子查询
案例：找出高于平均薪资的员工信息
select * from emp where sal > avg(sal)；//错误的写法，where后面不能直接使用分组函数
正确步骤：
- 第一步：找出平均薪资 select avg(sal) from emp;
- 第二步：where过滤 select * from emp where sal > 2073.214286;
- 第一步和第二部合并：select * from emp where sal > *(select avg(sal) from emp);
2.2 from后面嵌套子查询
案例：找出每个部门平均薪水的薪资等级
- 第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno; - 第二步：将以上的查询结果当作临时表t，让t表和salgrade表连接，条件是：t.avgsal between s.losal and s.hisal
案例：找出每个部门平均的薪水等级
- 第一步：找出每个员工的薪水等级
- 第二步：基于以上的结果，继续按照deptno分组，求grade平均值
2.3 select后面嵌套子查询
案例：找出每个员工所在的部门名称，要求显示员工名和部门名
原来方法：
现在方法：
3.union的用法
作用：将查询结果集相加
案例：找出工作岗位是SALESMAN和MANAGER的员工
第一种写法：select ename, job from emp where job = ‘MANAGER’ or job = ‘SATESMAN’ ;
第二种写法：select ename , job from emp where job in ( ‘MANAGER’,‘SALESMAN’ ) ;
第三种写法：
select ename ,job from emp where job = ‘MANAGER’
union
select ename , job from emp where job = ‘SAL,ESMAN’ ;
注意：
- union可以将两张不相干的表中的数据拼接在一起显示
- 拼的时候列数需要一致
4.limit【分页查询全靠它】
4.1 基础概念
limit是mysql特有的，其它数据库中没有，不通用。(Oracle中有一个相同的机制，叫做rownum)
作用：limit取结果集中的部分数据
语法机制：limit startIndex,length【startIndex：表示起始位置（从0开始，0表示第一条数据）,length：表示取几个】
案例：取出工资前五名的员工
思路：降序取前五个
select ename,sal from emp order by sal desc limit 0, 5;
等价于： select ename,sal from emp order by sal desc limit 5;【直接写一个数字，前面默认为0】
注意：limit是sql语句中最后执行的一个环节
案例：取出工资排名在第四到第九名的员工
select ename,sal from emp order by sal desc limit 3, 6;
4.2 通用的标准分页sql
每页显示pagesize条记录:
第pageNo页: (pageNo -1)* pagesize, pagesize
5.表
5.1 基础概念
建表语句的语法结构：
create table 表名{
字段名1 数据类型,
字段名2 数据类型,
字段名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(Character Large OBject) | 字符大对象 (存储较大文本，比如：可以存储4G的字符串)(java中的Object) |
char 和 varchar之前的选择：
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如:性别、生日等都是采用char。
当一个字段的数据长度不确定，例如:简介、姓名等都是采用varchar
注意：表名在数据库当中一般建议以：t_或者tbl_开始
5.2 创建表
创建学生表：学生信息包括【学号、姓名、性别、班级编号、生日】
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日： char
语句：
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar (255),
birth char (10)
);5.3 插入数据
语法格式: insert into 表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…)
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
实战:insert into t_student(no ,name ,sex,classno,birth) values(1, ‘zhangsan’ , ‘1’ , ‘gaosan1ban’，‘1950-10-12’);
insert into t_student (name , sex,classno ,birth ,no) values(‘lisi’ , ‘1’ , ’ gaosan1ban’,‘1950-10-12’,2);
insert into t_student (name) values ( 'wangwu ’ ) ;【除name字段之外，剩下的所有字段自动插入NULL】
删除表格：drop table if exists t_student;
可以设置默认值：
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1,
classno varchar (255),
birth char (10)
);注意：当一条insert语句执行成功之后,表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了,只能使用update进行更新。
注意：字段可以省略不写，但是后面的value对数量和顺序都有要求
insert into t_student values (1, ‘jack ’ , ’ 0’ , ’ gaosan2ban’, ‘1986-10-23’);
注意：一次插入多行数据
insert into t_student (no , name , sex ,classno ,birth) values (3 , ’ rose’ , ‘1’, ’ gaosi2ban’ , ‘1952-12-14’),(4,’ laotie’ , ‘1’ , ’ gaosi2ban’, ‘1955-12-14’);
5.4 表的复制以及批量插入
表的复制语句：create table 表名 as select语句；【将查询结果当做表创建出来】
将查询结果插入到一张表中：
insert into dept1 select * from dept；
select * from dept1；
5.5 修改表中的数据
语法格式：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’;
5.6 删除表中的数据
语法格式：delete from 表名 where 条件；
注意：没有条件全部删除
案例:删除10部门数据
语法：delete from dept1 where deptno = 10；
案例:删除所有数据
语法：delete from dept1；
案例:删除大表中的数据
语法：truncate table emp1；【表被截断，不可回滚，永久丢失】
案例:删除表
语法：drop table 表名；//通用
drop table if exists 表名；//Oracle不支持
5.7 关于表结构的修改
- 对于表结构的修改，这里不陈述，使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，也可以直接使用工具操作。
- 修改表结构的语句不会出现在Java代码当中，出现在Java代码中的sql包括：insert、delete、update、select（这些都是对表中的数据操作）
- 增删改查有一个术语：CRUD操作【Create(曾) Retrieve(检索) Update(修改) Delete(删除)】
