MySQL学习笔记-day02

1、关于查询结果集的去重?

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

结果:

+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
select ename,distinct job from emp;# 错误

记住:distinct只能出现在所有字段的最前面。

distinct name1,name2 # 联合去重
select distinct deptno,job from emp;

结果:

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

案例:统计岗位的数量?

+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

2、连接查询

2.1、连接查询:也叫跨表查询,需要关联多个表进行查询。

在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。

在实际业务中,一般一个业务都会对应多张表,比如:学生和班级,起码两张表。

显示每个员工信息,并显示所属的部门名称。

select ename,dname from emp,dept;
2.2、连接查询的分类?

根据语法出现的年代来划分的话,包括:

SQL92(一些老的DBA可能还在使用这种语法。DBA:DataBase Adminstrator。数据库管理员)

SQL99(比较新的语法)

根据表的连接方式来划分,包括:

内连接
  • 等值连接

  • 非等值连接

自连接
外连接
  • 左外连接(左连接)

  • 右外连接(右连接)

  • 全连接(这个很少用)

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

笛卡尔积现象:当两张表进行连接查询的时候,如果没有任何条件限制,查询结果是两张表中的记录条数的乘积。
案例:找出每一个员工的部门信息,要求显示员工名和部门名。

select ename,dname from emp,dept order by ename;# 发生笛卡尔积现象
+--------+-------------+
| ename  | dname       |
+--------+-------------+
| ADAMS  | SALES       |
| ADAMS  | OPERATIONS  |
| ADAMS  | ACCOUNTING  |
| ADAMS  | RESEARCHING |
| ALLEN  | OPERATIONS  |
| ALLEN  | ACCOUNTING  |
| ALLEN  | RESEARCHING |
| ALLEN  | SALES       |
| BLAKE  | OPERATIONS  |
| BLAKE  | ACCOUNTING  |
| BLAKE  | RESEARCHING |
| BLAKE  | SALES       |
| CLARK  | SALES       |
| CLARK  | OPERATIONS  |
| CLARK  | ACCOUNTING  |
| CLARK  | RESEARCHING |
| FORD   | ACCOUNTING  |
| FORD   | RESEARCHING |
| FORD   | SALES       |
| FORD   | OPERATIONS  |
| JAMES  | RESEARCHING |
| JAMES  | SALES       |
| JAMES  | OPERATIONS  |
| JAMES  | ACCOUNTING  |
| JONES  | RESEARCHING |
| JONES  | SALES       |
| JONES  | OPERATIONS  |
| JONES  | ACCOUNTING  |
| KING   | ACCOUNTING  |
| KING   | RESEARCHING |
| KING   | SALES       |
| KING   | OPERATIONS  |
| MARTIN | ACCOUNTING  |
| MARTIN | RESEARCHING |
| MARTIN | SALES       |
| MARTIN | OPERATIONS  |
| MILLER | OPERATIONS  |
| MILLER | ACCOUNTING  |
| MILLER | RESEARCHING |
| MILLER | SALES       |
| SCOTT  | RESEARCHING |
| SCOTT  | SALES       |
| SCOTT  | OPERATIONS  |
| SCOTT  | ACCOUNTING  |
| SIMITH | ACCOUNTING  |
| SIMITH | RESEARCHING |
| SIMITH | SALES       |
| SIMITH | OPERATIONS  |
| TURNER | OPERATIONS  |
| TURNER | ACCOUNTING  |
| TURNER | RESEARCHING |
| TURNER | SALES       |
| WARD   | SALES       |
| WARD   | OPERATIONS  |
| WARD   | ACCOUNTING  |
| WARD   | RESEARCHING |
+--------+-------------+
56 rows in set (0.00 sec)
扩展

关于表的别名:

select  e.ename,d.dname from emp as e,dept as d;# 可省略as
select  e.ename,d.dname from emp e,dept d;

表的别名有什么好处?

  • 第一:执行效率高。

  • 第二:可读性好。

2.4、怎么避免笛卡尔积现象?当然是加条件进行过滤。

找出每一个员工的部门信息,要求显示员工名和部门名。

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;# SQL92,以后不用。

避免笛卡尔积现象,匹配次数不会减少,只不过显示的是有效记录!

2.5、内连接值等值连接:最大特点是:条件是等量关系。

案例:查询每个员工的部门名称,要求显示员工名和部门名。

  • SQL92:(太老了,不用了)
select 
	e.ename,d.dname 
from 
	emp e,dept d 
where 
	e.deptno = d.deptno;
  • SQL99:(常用的)
select 
	e.ename,d.dname 
from 
	emp e
inner join	# inner可以省略,带着inner目的是可读性好一些。
	dept d 
on 
	e.deptno = d.deptno;

on后面跟表连接的条件。

结果:

+--------+-------------+
| ename  | dname       |
+--------+-------------+
| CLARK  | ACCOUNTING  |
| KING   | ACCOUNTING  |
| MILLER | ACCOUNTING  |
| SIMITH | RESEARCHING |
| JONES  | RESEARCHING |
| SCOTT  | RESEARCHING |
| ADAMS  | RESEARCHING |
| FORD   | RESEARCHING |
| ALLEN  | SALES       |
| WARD   | SALES       |
| MARTIN | SALES       |
| BLAKE  | SALES       |
| TURNER | SALES       |
| JAMES  | SALES       |
+--------+-------------+

语法:

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

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

2.6、内连接中的非等值连接,最大特点是:连接条件是非等量关系。

案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。

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

结果:

+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SIMITH |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
2.7、自连接:最大的特点是:一张表看做两张表。自己连接自己。

案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。(所有员工必须全部查询出来。)

empno 员工编号
ename 员工名称
mgr 领导编号

select empno,ename,mgr from emp a;

emp a 员工表

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SIMITH | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

FORD既是员工(empno = 7902,即FORD的员工编号为7902),也是(员工SIMITH的)领导(SIMITH:mrg = 7902,即SIMITH的=领导编号为7902,也就是FORD)

emp b 领导表

+-------+--------+
| empno | ename  |
+-------+--------+
|  7566 | JONES  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7902 | FORD   |
+-------+--------+

员工的领导编号 = 领导的员工编号

即:a.mgr = b.empno;

内连接方式:

select
	a.ename as '员工名',b.ename as '领导名'
from
	emp a
inner join
	emp b
on
	a.mgr = b.empno;

结果:

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
2.8、外连接 (以后的开发中外连接用的多一些。)

什么是外连接,和内连接有什么区别?

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

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

外连接的分类?

  • 左外连接(左连接):表示左边的这张表是主表。

  • 右外连接(右连接):表示右边的这张表是主表。

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

案例:找出每个员工的上级领导?

emp a 员工表

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SIMITH | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

emp b 领导表

+-------+--------+
| empno | ename  |
+-------+--------+
|  7566 | JONES  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7902 | FORD   |
+-------+--------+

案例:找出每个员工的上级领导?

  • 外连接方式之左连接:join前面的outer可以省略
select
	a.ename as '员工名',b.ename as '领导名'
from
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;

结果:

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
  • 外连接方式之右连接:join前面的outer可以省略
select
	a.ename as '员工名',b.ename as '领导名'
from
	emp b
right outer join
	emp a
on
	a.mgr = b.empno;

结果:

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

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

案例:找出哪个部门没有员工?

emp员工表

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SIMITH | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept部门表

+--------+-------------+----------+
| deptno | dname       | loc      |
+--------+-------------+----------+
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
+--------+-------------+----------+

外连接:

select 
	d.*
from 
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

结果:

+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
ifnull函数

ifnull() 空处理函数

ifnull(可能为NULL的数据,被当做什么处理):属于单行处理函数。

eg:

设置津贴为NULL的数据为0。

select ename,ifnull(comm,0) as comm from emp;

案例:设置没有上级领导的员工名为BOSS。

select a.ename as '员工名',ifnull(b.ename,'BOSS') as '领导名' from emp a left join emp b on a.mgr = b.empno;

结果:

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | BOSS   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
2.9、三张表怎么连接查询?

案例:找出每一个员工的部门名称以及工资等级。

拆解――:

select empno,ename,deptno from emp;

emp e

+-------+--------+---------+--------+
| empno | ename  | sal     | deptno |
+-------+--------+---------+--------+
|  7369 | SIMITH |  800.00 |     20 |
|  7499 | ALLEN  | 1600.00 |     30 |
|  7521 | WARD   | 1250.00 |     30 |
|  7566 | JONES  | 2975.00 |     20 |
|  7654 | MARTIN | 1250.00 |     30 |
|  7698 | BLAKE  | 2850.00 |     30 |
|  7782 | CLARK  | 2450.00 |     10 |
|  7788 | SCOTT  | 3000.00 |     20 |
|  7839 | KING   | 5000.00 |     10 |
|  7844 | TURNER | 1500.00 |     30 |
|  7876 | ADAMS  | 1100.00 |     20 |
|  7900 | JAMES  |  950.00 |     30 |
|  7902 | FORD   | 3000.00 |     20 |
|  7934 | MILLER | 1300.00 |     10 |
+-------+--------+---------+--------+

dept d

+--------+-------------+----------+
| deptno | dname       | loc      |
+--------+-------------+----------+
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
+--------+-------------+----------+

salgrade s

+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  5000 |
+-------+-------+-------+

注意,这里解释一下:

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

结果:

+--------+-------------+-------+
| ename  | dname       | grade |
+--------+-------------+-------+
| SIMITH | RESEARCHING |     1 |
| ALLEN  | SALES       |     3 |
| WARD   | SALES       |     2 |
| JONES  | RESEARCHING |     4 |
| MARTIN | SALES       |     2 |
| BLAKE  | SALES       |     4 |
| CLARK  | ACCOUNTING  |     4 |
| SCOTT  | RESEARCHING |     4 |
| KING   | ACCOUNTING  |     5 |
| TURNER | SALES       |     3 |
| ADAMS  | RESEARCHING |     1 |
| JAMES  | SALES       |     1 |
| FORD   | RESEARCHING |     4 |
| MILLER | ACCOUNTING  |     2 |
+--------+-------------+-------+	

案例:找出每一个员工的部门名称、工资等级以及上级领导。

员工对应的领导

select e.ename '员工', e1.ename '领导' from emp e left outer join emp e1 on e.mgr = e1.empno;

结果:

+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
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;

3、子查询

什么是子查询?

select语句当中嵌套select语句,被嵌套的select语句是子查询。

子查询都可以出现在哪里?

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

比如:查询员工信息,查询哪些人是管理者,要求显示其员工编号和员工姓名。

3.2、在where语句中使用子查询,也就是在where语句中加入select语句。

案例:找出高于平均薪资的员工信息。

select * from emp where sal > avg(sal);# 错误的写法。where后面不能直接使用分组函数。

注意:分组函数不能直接使用在where关键字后面。因为group by是在where执行之后才会执行的。必须先分组,再执行分组函数。

任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行。

第一步:找出平均薪资

select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

第二步:where过滤

select * from emp where sal > (select avg(sal) from emp);

结果:

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
3.3、from后面嵌套子查询,可以将该子查询看做一张表

案例:找出每个部门平均薪资等级。(按照部门编号分组,求sal的平均值) avg(sal)

第一步:找出每个部门平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二步:将以上的查询结果当做临时表t,让t表和salgrade表连接,条件是: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;

结果:

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+

案例:找出每个部门平均的薪水等级。avg(grade)

第一步:找出每个员工的薪水等级。

select 
	e.ename,e.sal,e.deptno,s.grade 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| JONES  | 2975.00 |     20 |     4 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| CLARK  | 2450.00 |     10 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| KING   | 5000.00 |     10 |     5 |
| TURNER | 1500.00 |     30 |     3 |
| ADAMS  | 1100.00 |     20 |     1 |
| JAMES  |  950.00 |     30 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+

第二步:基于以上结果,继续按照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
	deptno;

结果:

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3.4、在select后面嵌套子查询

案例:找出每个员工所在的部门名称,要求显示员工名和部门名。

不嵌套方式:

select
	e.ename,d.dname
from
	emp e
join
	dept d
on
	d.deptno = e.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

嵌套方式:

select 
	e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

4、union(可以将查询结果集相加)

案例:找出工作岗位是SALESMAN和MANAGER的员工?

第一种:or

select e.ename,e.job from emp e where job = 'SALESMAN' or job = 'MANAGER';

第二种:in

select e.ename,e.job from emp e where job in('SALESMAN','MANAGER');

第三种:union

select ename,job from emp where job = 'SALESMAN'
union
select ename,job from emp where job = 'MANAGER';

结果:

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

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

select ename from emp
union
select dname from dept;

结果:

+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
select ename,sal from emp
union
select dname,loc from dept;# 列数要相同
5、limit(重点中的重点,以后的分页查询全靠它了。)
5.1、limit是mysql特有的,其他数据库中没有,不通用。(Oracle中有一个相同的机制,叫做rownum)
5.2、limit 取结果集中的部分数据 这是它的作用。
5.3、语法机制:
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;

结果:

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5.4、limit 是 sql 中最后执行的一个环节。
select		5
	...
from		1
	...
where		2
	...
group by	3
	...
having		4
	...
order by	6	
	...
limit		7
	...;
5.5、案例:找出工资排名在第4到第9的员工?
select ename,sal from emp order by sal desc limit 3,6;
5.6、通用的标准分页sql?

每页显示3条记录:

第1页:0,3
第2页:3,3
第3页:6,3
第4页:9,3
第5页:12,3

每页显示pageSize条记录:

pageNo页:(pageSize - 1) * pageSize,pageSize

pageSize是什么?

  • 是每页显示多少条记录

pageNo是什么?

  • 显示第几页

java代码

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

6、创建表

建表语句的语法格式:

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型,
    ...
);

创建表的时候,表中有字段:

  • 字段名
  • 字段数据类型
  • 字段长度限制
  • 字段约束

关于MySQL当中字段的数据类型?

常见的字段名数据类型:

int	整数型
bigint	长整型(对应java的long)	
float	浮点型	
double	浮点型	例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;
char	定长字符串(对应java中的String)
varchar	可变长字符串(对应java中的StringBuffer/StringBuffer)
date	日期类型(对应Java中的java.sql.Date类型)
BLOB(Binary Large OBject)		二进制大对象	按二进制来存储 通常像图片、文件、音乐等信息就用BLOB字段来存储,先将文件转为二进制再存储进去。
CLOB(Character Large OBject)	字符大对象	可以直接存储文字 像文章或者是较长的文字,就用CLOB存储。

建立学生信息表,字段包括:学号、姓名、性别、班级标识、出生日期。

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

charvarchar怎么选择?

在实际开发中,当某个字段中的数据长度不发生改变的时候,是定长的。例如:性别、生日等都是采用char

当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar

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

7、insert语句插入数据

语法格式:

insert into 表名(字段名1,字段名2,字段名3,...) values (值1,值2,值3,...)

要求:字段的数量和值的数量相同,并且数据类型要对应相同。

eg:

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1950-10-12');
select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+
insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban','1950-12-10',2);
select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-12-10 |
+------+----------+------+------------+------------+
insert into t_student(name) values('wangwu');# 除name字段之外,剩下的所有字段自动插入NULL
select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-12-10 |
| NULL | wangwu   | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+
insert into t_student(no) values(3);
select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-12-10 |
| NULL | wangwu   | NULL | NULL       | NULL       |
|    3 | NULL     | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+

创建表赋默认值/删除表

删除表

drop table if exists t_student;# 如果t_student表存在的话,就删除该表。

再建一张表:

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1,	# default指定默认值
    classno varchar(255),
    birth char(10)
);
select * from t_student;
+------+----------+------+---------+-------+
| no   | name     | sex  | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1    | NULL    | NULL  |
+------+----------+------+---------+-------+

注意:当一条insert语句执行成功之后,表格当中必然会多出一行记录。不能使用insert语句插入数据了,只能使用update进行更新。

插入字段省略不写

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23'); # 字段可以省略不写,但是后面的values对数量和顺序都有要求。

一次插入多行数据

insert into t_student
	(no,name,sex,classno,birth) 
values
	(3,'rose','1','gaosan2ban','1952-12-14'),(4,'laotie','1','gaosan2ban','1955-12-14');

8、表的复制

语法:

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

eg:

create table emp1 as select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SIMITH | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
create table emp2 as select empno,ename from emp;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SIMITH |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

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

create table dept1 as select * from dept;
select * from dept1;
+--------+-------------+----------+
| deptno | dname       | loc      |
+--------+-------------+----------+
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
+--------+-------------+----------+
insert into dept1 select * from dept;
select * from dept1;
+--------+-------------+----------+
| deptno | dname       | loc      |
+--------+-------------+----------+
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
+--------+-------------+----------+

10、修改数据:update

语法格式:

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

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

案例:将部门10的住址loc修改为SHANGHAI,将部门名称dname修改为RENSHIBU

update dept1 set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;

更新所有记录

update dept1 set loc = 'x',dname = 'y';

11、删除数据

语法格式:

delete from 表名 where 条件;

注意:没有条件,全部删除。

删除10部门数据?

delete from dept1 where deptno = 10;

删除所有记录?

delete from dept1;

怎么删除大表中的数据?(重点)

truncate table 表名;# 表被截断,不可回滚。永久丢失。

删除表?

drop table 表名;# 这个通用
drop table if exists 表名;# Oracle不支持这种写法

常见SQL分类

DQL(select) DML(insert delete update) DDL(create drop alter)

12、表结构的修改 DDL(create drop alter)

建议使用工具,比如Navicat for MySQL

对于表结构的修改,这里就不讲了,使用工具即可。因为在实际开发中一旦设计好之后,对表结构的修改是很少的,修改表结构就是对之前的设计进行否定,即使需要修改表结构,我们也可以直接使用工具进行操作。修改表结构的语句不会出现在Java代码当中。

出现在java代码当中的sql包括:insert delete update select(这些都是表中的数据操作。)

13、CRUD

增删改查有一个术语:CRUD操作

Create(增) Retrieve(检索) Update(修改) Delete(删除)

14、约束(Constraint)

14.1、什么是约束?常见的约束有哪些?

在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

常见的约束有哪些?
  • 非空约束(not null):约束的字段不能为NULL
  • 唯一约束(unique):约束的字段不能重复
  • 主键约束(primary key):约束的字段既不能为NULL,也不能重复。(简称PK)
  • 外键约束(foreign key):(简称FK)
  • 检查约束(check):注意:Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。
14.2、非空约束 not null
drop table if exists t_user;
create table t_user(
	id int,
	username  varchar(255) not null,
	password  varchar(255)
);
insert into t_user(id,password) values(1,'123');
# 错误:ERROR 1364 (HY000): Field 'username' doesn't have a default value
insert into t_user(id,username,password) values(1,'lisi','123');

结果:

+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | lisi     | 123      |
+------+----------+----------+
posted @   学者莱维  阅读(61)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示