本文共 11488 字，大约阅读时间需要 38 分钟。

数据库学习笔记2

1、查询结果集的去重

distinct 关键字取出重复记录

案例：显示有工作的岗位

mysql> select distinct job from emp;+-----------+| job       |+-----------+| CLERK     || SALESMAN  || MANAGER   || ANALYST   || PRESIDENT |+-----------+5 rows in set (0.00 sec)mysql> select job from emp group by job;+-----------+| job       |+-----------+| CLERK     || SALESMAN  || MANAGER   || ANALYST   || PRESIDENT |+-----------+5 rows in set (0.00 sec)

distinct 只能出现在所有的字段的最前面

多个字段的在一起的时候：只有多个字段同时满足重复就可以去重

mysql> 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     |+--------+-----------+9 rows in set (0.00 sec)mysql> select deptno, job from emp;+--------+-----------+| deptno | job       |+--------+-----------+|     20 | CLERK     ||     30 | SALESMAN  ||     30 | SALESMAN  ||     20 | MANAGER   ||     30 | SALESMAN  ||     30 | MANAGER   ||     10 | MANAGER   ||     20 | ANALYST   ||     10 | PRESIDENT ||     30 | SALESMAN  ||     20 | CLERK     ||     30 | CLERK     ||     20 | ANALYST   ||     10 | CLERK     |+--------+-----------+14 rows in set (0.00 sec)

2、连接查询之内连接

1.为什么使用连接查询：多表联合在一起查询取出最终的结果。

2.连接查询的分类：

​ 年代：SQL92 、SQL99

​ 连接方式：

​ 内连接（等值连接、非等值连接、自连接）和外连接（左外连接、右外连接) 和全连接

*3.在表的连接中有一种现象叫做：*笛卡尔积现象

（1）内连接之等值连接

案列： 查询每一个员工的部门名称，要求显示员工名和部门名。

select e.ename,d.dnamefrom emp e,dept d where  e.deptno= d.deptno;

SQL 92 的语法：mysql> select    -> 		e.ename,d.dname    -> from    -> 		emp e,dept d    ->  where    ->  	e.deptno= d.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,d.dname from  emp e join  dept d on e.deptno=d.deptno;

SQL 99语言：mysql> select    ->  	e.ename,d.dname    ->  from    ->  	emp e    ->  join    ->  	dept d    ->  on    ->		e.deptno=d.deptno;+--------+------------+| ename  | dname      |+--------+------------+| SMITH  | RESEARCH   || ALLEN  | SALES      || WARD   | SALES      |.......................+--------+------------+14 rows in set (0.00 sec).................................................................mysql> select    -> e.ename,d.dname    -> from    -> emp e    -> inner join    -> dept d    -> on    -> e.deptno= d.deptno;

表的别名： 执行的效率高、可读性好

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

使用 SQL 99语法的好处：on后面的条件是连接查询的条件，和where条件跟容易分离；

（2）内连接之非等值连接：

案例：找出每个员工的工资等级，要求显示员工名、工资、工贸等级。

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

mysql> select    -> e.ename,e.sal,s.grade    ->  from    ->  emp e    ->  inner join    ->  salgrade s    ->  on e.sal>=s.losal and e.sal<=s.hisal;+--------+---------+-------+| ename  | sal     | grade |+--------+---------+-------+| SMITH  |  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 |+--------+---------+-------+14 rows in set (0.00 sec)

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

（3）内连接之自连接：一张表看做两张表

案例：找出每一位员工的上级领导，要求显示员工名和对应的领导名

select  distinct  	b.mgr as ‘领导编号’from 	emp ainner join 	emp bon a.empno= b.mgr;

mysql> select    ->  distinct    ->  b.mgr as ‘领导编号’    -> from    -> emp a    -> inner join    -> emp b    -> on a.empno= b.mgr;+--------------+|   ‘领导编号’  |+--------------+|         7902 ||         7698 ||         7839 ||         7566 ||         7788 ||         7782 |+--------------+6 rows in set (0.00 sec)

解答：

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

mysql> select    -> 		a.ename as '员工名',    -> 		b.ename as '领导名'    -> from    -> 		emp a    -> join    -> 		emp b    -> on    -> 		a.mgr=b.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  |+--------+--------+13 rows in set (0.00 sec)

3、连接查询之外连接

外连接：假设有两张表：Ａ和B 表进行连接查询，其中一张是主表，另一张是副表，主要查询的是主表中的数据，附带的查询副表中的内容，当副表中没有数据与主表的数据与之对应的时候，副表会模拟null与之匹配。

内连接：查询的数据显示的都是两个表都能匹配上的。没有主副之分。

左右连接的区别：关键显示的主表位置

案例：找出每一位员工的上级领导，要求显示员工名和对应的领导名(要求的是：每一位员工都必须查出来，领导也是员工哦！！！！！！！)

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

mysql> select    ->		 a.ename as'员工名',    -> 		 b.ename as'领导名'    ->  from    ->  		emp a    ->  left join    ->  		emp b    ->  on    ->  		a.mgr= b.empno;+--------+--------+| 员工名  | 领导名  |+--------+--------+| SMITH  | 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  |+--------+--------+14 rows in set (0.00 sec)

外连接的特点：主表的数据无条件的全部查询出来！！！

案例:没有员工的部门，显示部门编号、部门名称

分析：

mysql> select->  e.empno,->  e.ename,->  d.deptno,->  d.dname->  from->   dept d->  left outer join->  emp e->  on->  e.deptno= d.deptno;+-------+--------+--------+------------+| empno | ename  | deptno | dname      |+-------+--------+--------+------------+|  7369 | SMITH  |     20 | RESEARCH   ||  7499 | ALLEN  |     30 | SALES      ||  7521 | WARD   |     30 | SALES      ||  7566 | JONES  |     20 | RESEARCH   ||  7654 | MARTIN |     30 | SALES      ||  7698 | BLAKE  |     30 | SALES      ||  7782 | CLARK  |     10 | ACCOUNTING ||  7788 | SCOTT  |     20 | RESEARCH   ||  7839 | KING   |     10 | ACCOUNTING ||  7844 | TURNER |     30 | SALES      ||  7876 | ADAMS  |     20 | RESEARCH   ||  7900 | JAMES  |     30 | SALES      ||  7902 | FORD   |     20 | RESEARCH   ||  7934 | MILLER |     10 | ACCOUNTING ||  NULL | NULL   |     40 | OPERATIONS |+-------+--------+--------+------------+15 rows in set (0.00 sec)

select 	 d.deptno,     d.dname from  	 dept d left outer join  	emp e on 	 e.deptno= d.deptnowhere e.empno is null;

mysql> select    ->  d.deptno,    ->  d.dname    ->  from    ->   dept d    ->  left outer join    ->  emp e    ->  on    ->  e.deptno= d.deptno    -> where    ->  e.empno is null;+--------+------------+| deptno | dname      |+--------+------------+|     40 | OPERATIONS |+--------+------------+1 row in set (0.00 sec)

4、三张表的查询

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

select e.ename,e.sal,d.dname,s.grade from  emp e join  dept d join salgrade s on e.deptno= d.deptno  where  e.sal between s.losal and s.hisal;

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

selecte.ename as '员工名',d.dname as '部门名',s.grade,e.mgr as '领导名'fromemp ejoindept done.deptno=d.deptnojoinsalgrade sone.sal between s.losal and s.hisalleft join emp e1on e.ename=e1.mgr;

mysql> select    -> e.ename as '员工名',    -> d.dname as '部门名',    -> s.grade,    -> e.mgr as '领导名'    -> 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.ename=e1.mgr;+--------+------------+-------+--------+| 员工名  | 部门名      | grade |  领导名 |+--------+------------+-------+--------+| SMITH  | RESEARCH   |     1 |   7902 || ADAMS  | RESEARCH   |     1 |   7788 || JAMES  | SALES      |     1 |   7698 || WARD   | SALES      |     2 |   7698 || MARTIN | SALES      |     2 |   7698 || MILLER | ACCOUNTING |     2 |   7782 || ALLEN  | SALES      |     3 |   7698 || TURNER | SALES      |     3 |   7698 || JONES  | RESEARCH   |     4 |   7839 || BLAKE  | SALES      |     4 |   7839 || CLARK  | ACCOUNTING |     4 |   7839 || SCOTT  | RESEARCH   |     4 |   7566 || FORD   | RESEARCH   |     4 |   7566 || KING   | ACCOUNTING |     5 |   NULL |+--------+------------+-------+--------+14 rows in set, 196 warnings (0.00 sec)

5、子查询

(1)where子句使用子查询

案例： 找出高于平均工资的员工信息

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

(2) from 后面嵌套子查询

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

select e.deptno,avg(sal) as '平均工资'fromemp egroup by deptno;...........................................................selectp.deptno,p.avgsal,s.gradefrom (select e.deptno,avg(sal) as 'avgsal'from emp e group by deptno) pleft outer joinsalgrade son p.avgsal between s.losal and s.hisal;

mysql> select    -> e.deptno,    -> avg(sal) as '平均工资'    -> from    -> emp e    -> group by deptno;+--------+-------------+| deptno | 平均工资    |+--------+-------------+|     20 | 2175.000000 ||     30 | 1566.666667 ||     10 | 2916.666667 |+--------+-------------+.................................................mysql> select    -> p.deptno,p.avgsal,s.grade    -> from    -> (select e.deptno,avg(sal) as 'avgsal'from emp e group by deptno) p    -> left outer join    -> salgrade s    -> on    -> p.avgsal between s.losal and s.hisal;+--------+-------------+-------+| deptno | avgsal      | grade |+--------+-------------+-------+|     30 | 1566.666667 |     3 ||     20 | 2175.000000 |     4 ||     10 | 2916.666667 |     4 |+--------+-------------+-------+

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

分析：先按照每一个员求薪水等级，

select 	e.ename,	e.sal,	e.deptno,	s.gradefrom 	emp ejoin	salgrade son 	e.sal between  s.losal and s.hisal;......................................................................select 	e.deptno,	avg(s.grade)from 	emp ejoin	salgrade son 	e.sal between  s.losal and s.hisalgroup by 	e.deptno;

(3) 在select后面嵌套的子查询

案例：找出每一个员工所在的部门名称， 要求显示员工名和部门名

案例

略…

(4) union（将查询的结果集相加）（两张不相干的表示的数据的拼接在一起【要求两个表的列字段的的数量相同】）

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

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

6、limit分页查询

limit是mysql特有的，Oracle中有一个相同的机制，叫做rownum

语法：

limit（startIndex , length）

​ startIndex： 表示开始的位置 0 表示第一天数据

​ length:表示显示几条数据

关系式：startIndex=（pageNo-1)*pageSize

pageNo：第pageSize页。pageSize：每一页显示的记录条数

sql 语法执行顺序：.......................................................    select		    5		...	from			1		...			where			2		...		group by		3		...	having		    4		...	order by		6		...	limit			7		...  ;

7、DDL

(1)创建表：

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

关于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的选择？

实际开发中，某个字段的数据长度不发生变化时，使用是定长的数据类型，例如，性别，出生日期

某个字段的数据长度不确定的时候，选择varchar 例如：简介、姓名

(2)插入数据

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

插入的方式：略…

(3)更新数据

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

(4)删除数据

delete from 表名 where 条件;

拓展：

表的复制：

create table 新表名 as select 语句;

怎么删除大表中的数据？（重点）

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

删除表？

drop table 表名; // 这个通用。drop table if exists 表名; // oracle不支持这种写法。

(5)表结构的修改…使用工具

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

Create（增） Retrieve（检索） Update（修改） Delete（删除）

转载地址：http://ofcki.baihongyu.com/