MySQL 学习笔记（二）

关于查询结果集的去重

	mysql> select distinct job from emp;            // distinct关键字去除重复记录。

记住：distinct只能出现在所有字段的最前面，代表后面所有字段联合起来去重
	mysql> select ename,distinct job from emp; 		//错误
	mysql> select distinct deptno,job from emp;		//正确

统计岗位的数量？
	select count(distinct job) from emp;

连接查询

表的连接方式来划分，包括：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接（左连接）
			右外连接（右连接）
		全连接（这个不讲，很少用！）

在表的连接查询方面有一种现象被称为：笛卡尔积现象。

查询每个员工的部门名称，要求显示员工名和部门名
select ename,dname from emp,dept;		//笛卡尔积现象。56条

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

关于表的别名：
	select e.ename,d.dname from emp e,dept d;
	表的别名有什么好处？
		第一：执行效率高。
		第二：可读性好。

怎么避免笛卡尔积现象？当然是加条件进行过滤
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;

SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。

内连接之等值连接

最大特点是：条件是等量关系

案例：查询每个员工的部门名称，要求显示员工名和部门名。
select e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;
		
// inner可以省略的，带着inner目的是可读性好一些。
	select 
		e.ename,d.dname
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno;

语法：
		...
			A
		join
			B
		on
			连接条件
		where
			...

内连接之非等值连接

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

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
// inner可以省略
select 
	e.ename,e.sal,s.grade
from
	emp e
inner join
	salgrade s
on
	e.sal between s.losal and s.hisal;

自连接

最大的特点是：一张表看做两张表。自己连接自己。

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

外连接

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

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

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

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

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

外连接：（左外连接/左连接）
select 
	a.ename '员工', b.ename '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

// outer可以省略。
select 
	a.ename '员工', b.ename '领导'
from
	emp b
right outer 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
	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 
		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;

子查询

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

where子句中使用子查询

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

from后面嵌套子查询

案例：找出每个部门平均薪水的等级。

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal

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

案例：找出每个部门平均的薪水等级。
第一步：找出每个员工的薪水等级 : select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
第二步：基于以上结果，继续按照deptno分组，求grade平均值。

select 
	e.deptno,avg(s.grade)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	e.deptno;

在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;

union （可以将查询结果集相加）

案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in('MANAGER','SALESMAN');

第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

优势：两张不相干的表中的数据拼接在一起显示

limit (重点中的重点，以后分页查询全靠它了。)

limit是mysql特有的，其他数据库中没有，不通用

limit取结果集中的部分数据，这是它的作用。

语法机制：
	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;


limit是sql语句最后执行的一个环节：
	select		5
		...
	from			1
		...		
	where			2
		...	
	group by		3
		...
	having		4
		...
	order by		6
		...
	limit			7
		...;

案例：找出工资排名在第4到第9名的员工
select ename,sal from emp order by sal desc limit 3,6;

通用的标准分页sql

每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3

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

pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页

java代码{
	int pageNo = 2; // 页码是2
	int pageSize = 10; // 每页显示10条
	
	limit (pageNo - 1) * pageSize, pageSize
}

创建表

建表语句的语法格式：
		create table 表名(
			字段名1 数据类型,
			字段名2 数据类型,
			字段名3 数据类型,
			....
		);

	关于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。
	
	BLOB和CLOB类型的使用？
		电影表: t_movie
		id(int)	name(varchar)		playtime(date/char)		haibao(BLOB)		history(CLOB)
		----------------------------------------------------------------------------------------
		1			蜘蛛侠	
		2
		3

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

	创建学生表：
		学生信息包括：
			学号、姓名、性别、班级编号、生日
			学号：bigint
			姓名：varchar
			性别：char
			班级编号：int
			生日：char
		
		create table t_student(
			no bigint,
			name varchar(255),
			sex char(1),
			classno varchar(255),
			birth char(10)
		);

insert语句插入数据

语法格式：
		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');

表的复制

语法：
		create table 表名 as select语句;
		将查询结果当做表创建出来。

create table t_stu1 as select * from t_student;

create table emp1 as select ename, sal from emp;

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

create table dept1 as select * from dept;

insert into dept1 select * from dept;

修改数据：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';
		没有where 直接更新所有记录，全部改变数据。

删除数据

语法格式：
		delete from 表名 where 条件;
	
	注意：没有条件全部删除。

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

对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦
设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使
需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。
出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

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