MySQL学习总结2

1、连接查询

概念：对多张表进行联合查询取出最终的结果的操作叫连接查询

分类：

• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接（左连接）
  • 右外连接（右连接）
  • 全连接（了解即可）

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

加条件进行过滤可以避免笛卡尔积现象。

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
回答：不会减少匹配次数。

关于表的别名：

select e.ename,d.dname from emp e,dept d;

表的别名有什么好处？
第一：执行效率高。
第二：可读性好。

1.1、内连接

1、等值连接

特点：连接条件中的关系是等量关系

语法：

	...
			A
		join
			B
		on
			连接条件
		where
			...

# 找出每一个员工的部门名称，要求显示员工名和部门名。
select e.ename, d.dname from emp e,dept d where e.deptno = d.deptno; # SQL92，以后不用。
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno; # 推荐写法
select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno; # 完整写法，inner一般省略

2、非等值连接

特点：连接条件中的关系是非等量关系

# 找出每个员工的工资等级，要求显示员工名、工资、工资等级
select e.ename,e.sal,s.grade 
from emp e 
join salgrade s 
on e.sal between s.losal and s.hisal;

3、自连接

特点：一张表看做两张表。自己连接自己

# 找出每个员工的上级领导，要求显示员工名和对应的领导名
select a.ename as '员工名',b.ename as '领导名' 
from emp a 
join emp b 
on a.mgr = b.empno;

1.2、外连接

什么是外连接，和内连接有什么区别？

内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。

外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。

左连接有右连接的写法，右连接也会有对应的左连接的写法。

# 找出每个员工的上级领导？（所有员工必须全部查询出来。）
select a.ename '员工名',b.ename '领导名' 
from emp a 
left join emp b 
on a.mgr = b.empno; # 左连接


select 
	a.ename '员工', b.ename '领导'
from
	emp b
right join
	emp a
on
	a.mgr = b.empno; # 右连接

外连接最重要的特点是：主表的数据无条件的全部查询出来。

# 找出哪个部门没有员工
select d.* 
from emp e 
right join dept d 
on e.deptno = d.deptno 
where e.empno is null;

三张表的连接查询

格式：

....
		A
	join
		B
	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 
e.ename '员工',d.dname,s.grade,e1.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 e1
on
e.mgr = e1.empno;

2、子查询

什么是子查询？子查询都可以出现在哪里？
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？

		select
			..(select)
		from
			..(select)
		where
			..(select)

where子句中使用子查询

# 找出高于平均薪资的员工信息
select * from emp where sal > (select avg(sal) from emp);

from后面嵌套子查询

# 找出每个部门平均薪水的等级
Step1.找到每个部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
Step2.将Step1中结果当成临时表与salgrade表连接查询
select t.deptno, s.grade 
from (select deptno,avg(sal) avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;

select后面嵌套子查询

# 找出每个员工所在的部门名称，要求显示员工名和部门名
select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;

3、union

可以将结果集进行并集操作

# 找出工作岗位是SALESMAN和MANAGER的员工
select ename from emp where job = 'SALESMAN'
union
select ename from emp where job = 'MANAGER';

# 两张不相干的表中的数据拼接在一起显示
select ename from emp
union
select dname from dept;

错误示范：ERROR 1222 (21000): The used SELECT statements have a different number of columns
select ename,sal from emp
union
select dname from dept;

注意：拼接的数据必须两两相对应。不能一张表是一个数据，另一张表是两个数据，这样无法拼接！

4、limit

limit用于取出结果集的部分数据，常用在分页功能中，mysql所独有的。

语法：

limit startIndex,length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个

# 取出工资前5名的员工
select ename,sal from emp order by sal desc limit 5;
select ename,sal from emp order by sal desc limit 0,5;
# 找出工资排名在第4到第9名的员工
select ename,sal from emp order by sal desc limit 3,6;

limit是sql语句最后执行的一个环节：

	select	       	5
		...
	from			1
		...		
	where			2
		...	
	group by		3
		...
	having		    4
		...
	order by		6
		...
	limit			7
		...;

每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize

5、建表

MySQL中创建的数据类型

数据类型	介绍
int	整数型(java中的int)
bigint	长整型(java中的long)
float	浮点型(java中的float double)
char	定长字符串(String)
varchar	可变长字符串(StringBuffer/StringBuilder)
date	日期类型 （对应Java中的java.sql.Date类型）
BLOB	二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB	字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）

char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

表名在数据库当中一般建议以：t_或者tbl_开始。

创建学生表：

# 当这个表存在的话删除
drop table if exists t_student;
create table t_student(
	no bigint,
	name varchar(255),
	sex char(1),
	classno varchar(255),
	birth char(10)
);

6、insert语句

# 错误示范 ERROR 1136 (21S01): Column count doesn't match value count at row 1
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban');
# 正确写法：
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');
# 除name字段之外，剩下的所有字段自动插入NULL。
insert into t_student(name) values('wangwu'); 
# 字段可以省略不写，但是后面的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');

7、复制表

语法格式：

		create table 表名 as select语句;

将查询结果当做表创建出来。

8、将查询结果插入到一张表中

insert into dept1 select * from dept;

9、update语句

语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;

注意：没有条件整张表数据全部更新。

# 将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update dept1 set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;

10、delete语句

语法格式：

delete from 表名 where 条件;

注意：没有条件全部删除。

删除10部门数据
delete from dept1 where deptno = 10;
	
删除所有记录
delete from dept1;
	
删除大表（重点）
truncate table 表名; // 表被截断，不可回滚。永久丢失。