简单复习Mysql(上)

简单复习Mysql

emp表

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | 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 |    0.00 |     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 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade表

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

-- 分组函数/聚合函数/多行处理函数
-- 会自动忽略null
count() 计数
sum() 求和
avg()平均值
max()最大值
min()最小值
-- 不可以用在 where 后面  因为 group by 是在where执行之后才执行
-- 注意:分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
-- 并且任何一个分组函数（(count sum avg max min)都是在group by语句执行结束之后才会执行的。

select  	5
from  	 	1
where   	2
group by	3
having		4
order by	6



ifnull(可能为null的数据,被当作什么处理)
select ename,(sal+ifnull(comm,0))*12 year from emp;

group by 和 having

搭档关系~

group by :按照某个字段或者某些字段进行分组。
having :是对分组之后的数据进行再次过滤。

-- 记住一个规则:当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。


案例:找出每个工作岗位的最高薪资。
select job,max(sal) from emp group by job;


案例:找出每个部门不同工作岗位的最高薪资。
select deptno,job,max(sal) from emp group by deptno,job;


找出每个部门的最高薪资,要求显示薪资大于2900的数据。
-- 这种方式不好 效率低
select max(sal),deptno from emp group by deptno having max(sal) > 2900; 
-- 这种好
select max(sal),deptno from emp where sal > 2900 group by deptno; 

找出每个部门的平均薪资,要求显示薪资大于2900的数据。
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;

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

连接查询

内连接:

-- 这种写法结构清晰  后面可以继续写where过滤条件

等值连接  条件是等量关系
案例:查询每个员工的部门名称，要求显示员工名和部门名。
select e.ename,d.dname
from emp e
inner join dept d   -- inner可以省略
on e.deptno = d.deptno


非等值连接  条件是非等量关系
案例:找出每个员工的工资等级，要求显示员工名、工资、工资等级。
select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;


自连接:一张表看做两张表。自己连接自己。
案例:找出每个员工的上级领导，要求显示员工名和对应的领导名。
select a.ename '员工名',b.ename '领导名'
from emp a
join emp b
on a.mgr = b.empno;

外连接

什么是外连接,和内连接有什么区别?
内连接:
假设a和B表进行连接，使用内连接的话，凡是A表和s表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分,两张表是平等的。
外连接:
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

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

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

案例:找出每个员工的上级领导，要求显示员工名和对应的领导名。(所有员工必须查询出来)
select a.ename '员工名',b.ename '领导名'
from emp a
left (outer) join emp b  -- outer可以省略
on a.mgr = b.empno;

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

三张表连接

案例:找出每一个员工的部门名称以及工资等级h
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语句

-- where后面
案例:找出高于平均薪资的员工信息。
select ename,sal from emp where sal > (select avg(sal) from emp);

-- from后面
案例:找出每个部门平均薪水的薪资等级。
select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t 
join salgrade s 
on avgsal between s.losal and s.hisal;


案例:找出每个部门平均的薪水等级。
-- 活学活用
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 d.dname,avg(t.grade) from 
(select e.ename,e.sal,e.deptno,s.grade 
from emp e
join salgrade s
on e.sal between s.losal and hisal) t
join dept d
on t.deptno = d.deptno
group by t.deptno;

-- select后面
案例:找出员工以及对应的部门
select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) dname
from emp e;

union

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

-- 将结果合并起来  但是必须两个结果列数一致  表头是第一个查询的关键字 建议改名
select ename '结果' from emp 
union 
select dname from dept;  

+------------+
| 结果       |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+

limit

mysql特有的 取结果集的部分数据

语法: limit startIndex,length
		limit(起始位置,取几个)
		

案例:找出工资排名在第4到第9名的员工?
mysql> select ename,sal from emp order by sal limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
+--------+---------+


通用的标准分页sql
每页3条记录
第一页 0,3
第二页 3,3
第三页 6,3
...

每页pageSize 第pageNum页
((pageNum-1)*pageSize,pageSize)

建表

create table 表名(
	字段,类型,约束,
    ...
);

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

将查询结果插入到一张表中?
insert into 表名 select * from dept;

删表

删除所有记录?
delete from dept;

怎么删除大表中的数据?(重点）
truncate table emp ;

约束

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

非空约束(not nul1):约束的字段不能为NULL
唯一约束(unique):约束的字段不能重复
主键约束(primary key):约束的字段既不能为NULL，也不能重复
外键约束(foreign key): …
检查约束(check):注意oracle数据库有check约束，但是mysq1没有，目前mysq1不支持该约束。

存储引擎

show ENGINES

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。MyISAM采用三个文件组织一张表:
xxx.frm (存储格式的文件)
xxx.MYD (存储表中数据的文件)
xxx.MYI (存储表中索引的文件)
优点:可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。缺点:不支持事务。

优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MysQr数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。