MySql_Day5—where\from\select子查询—union用法—limit—表的创建、CRUD操作

1.子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表。

1.1.where语句中的子查询

• 1.查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

•   1.首先取得管理者的编号，去除重复的
    	select distinct mgr from emp where mgr is not null;
  

•   2.查询员工编号在”管理者编号“内的员工，即可
     select empno,ename,job from emp where empno in (select distinct mgr from emp where mgr is not null);
  

• 2.查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水

•   1.先取得平均薪水
    select avg(sal) from emp;
  

•   3.再选择薪水大于平均薪水的员工
     select empno,ename,sal from emp where sal>  (select avg(sal) from emp);
  

1.2.from的子查询

在from语句中使用子查询，可以将该子查询看做一张表

• 1.查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

•   1.先筛选出管理者的编号
    select distinct mgr from emp  where mgr is not null;
  

•   2.把步骤1得出的表看为t，与表emp 进行连接，即可
     select a.empno,a.ename from emp a 
      	join (select distinct mgr from emp  where mgr is not null) t 
      	 on a.empno=t.mgr;
  

• 2.查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

•   1.先计算每个部门的平均薪水
    select a.deptno,avg(sal) from emp a group by a.deptno;
  

•   2.把步骤1得出的表看作表e，与salgrade的表进行连接，即可
    	 select e.deptno,e.avgsal,s.grade 
    	 from  (select a.deptno,avg(sal) as avgsal from 	emp a group by a.deptno) e
       join salgrade s
       on e.avgsal between s.losal and hisal;
  

1.3.在select语句中使用子查询

• 1.查询员工信息，并显示出员工所属的部门名称

•   第一种做法（2个表连接）：
    select e.ename, d.dname from emp e join dept d on e.deptno=d.deptno;
  

•   第二种做法，在select语句中再次嵌套select语句完成部分名称的查询
     select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e  ;
  

2.union——查询结果相加

• 案例：找出工作岗位是SALESMAN和MANAGER的员工？

•   第1种方法：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
  

•   第2种方法：select ename,job from emp where job in('MANAGER','SALESMAN');
  

•   第3种方法：select ename,job from emp where job = 'MANAGER'	
    			 union
    			select ename,job from emp where job = 'SALESMAN';
  

• 2张不相关的表拼在一起显示？

•   select ename from emp
    union
    select dname from dept;
  

3.limit的使用

3.1.用法：

•   select * from table limit m,n
  

其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。

•   select * from tablename limit 2,4
  

即取出第3条至第6条，4条记录

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

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

3.3.显示分页记录语法

3.3.1每页显示pageSize条记录：

•   第pageNo页：(pageNo - 1) * pageSize, pageSize
  

• 3.3.2.pageSize是什么？是每页显示多少条记录

• 3.3.3.pageNo是什么？显示第几页

• 显示第2页的10条记录

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

• 从emp表中取出前5条数据

•   select * from emp limit 0,5;(0可以省略，因为默认从第0个开始)
  

• 从emp表中取得工资最高的前3位

•   select * from emp order by sal desc limit 5;
  

4.表

4.1.表的创建

基本语法：

•   create table 表名(
    		字段名  数据类型(字段长度)  字段约束,
    		字段名  数据类型(字段长度),
    		……
    )
  

4.2.MySql常用数据类型：

类型	描述
Char(长度)	定长字符串，存储空间大小固定，适合作为主键、外键
Varchar(长度)	可变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位)	数值型
float	数值型
int(长度)	整型
bigint(长度)	长整型
Date	日期型 年月日
DateTime	日期型 年月日 时分秒 毫秒
time	日期型 时分秒
BLOB	Binary Large OBject（二进制大对象），存储图片、视频等流媒体信息
CLOB	Character Large OBject（字符大对象），存储较大文本，比如存储4GB的字符串

4.3.char和varchar怎么选择？

字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

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

4.4.1.创建学生表：

•   学生信息包括：
    	学号、姓名、性别、班级编号、生日
    	学号：bigint
    	姓名：varchar
    	性别：char
    	班级编号：int
    	生日：char
  

•   create table t_student(
    	no bigint,
    	name varchar(255),
    	sex char(1),
    	classno varchar(255),
    	birth char(10)
    );
  

4.5.CRUD操作(create\retrieve\update\delete)

4.5.1.insert——插入
insert into 表名 (字段名1，字段名2，……) values（字段名1的值，字段名2的值，……）；

• 插入数据：学号2020，姓名zs，性别f，班级编号001，生日2000-1-1

•   insert into t_student(no,name,sex,classno,birth) values (2020,'zs','f',001,'2000-1-1');
  

• 插入数据：学号2021，姓名ww，性别m，班级编号002，生日1999-12-31

•   insert into t_student(  ) values (2021,'ww','w',002,'1999-12-31');
  

• 插入数据：学号2019，姓名zl，性别f，班级编号003，生日1998-11-11

•   insert into t_student(  ) values (2019,'zl','f',003,'1998-11-11');
  

4.5.2.表复制

•   create table 复制后的表 as select 字段1，字段2，… from 待复制的表;
  

• 把表t_student 复制到 t_student_copy
  
• 将查询的数据直接放到已存在的表中

•   insert into 已存在待插入的表  select *  from 待复制的表;
  

•   insert into t_student_copy  select *  from t_student;
  

4.5.3.update——可以修改数据，可以根据条件修改数据

•   update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where …….
  

如果不加where条件，那所有的都会被修改

• 把每个人的学号统一改成2030

•   update t_student set no=2030;
  

• 所有男生的班级编号改为11

•   	update t_student set classno='11' where sex = 'f';
  

4.5.4.delete——可以删除数据，可以根据条件删除数据

• 语法格式：delete from 表名 where …条件…；
• 删除表t_student_copy中所有男生的数据

•   	delete from t_student_copy  where sex='f';
  

• 删除表t_student_copy中所有数据

•   delete from t_student_copy;