需求
如下两张表student(学生表)、score(测试成绩表)
现需要统计：2015-03-10日之后，性别 age=1 的测试成绩的 总分 与 平均分。
要求：使用一个SQL统计score表，将结果更新到student表的score_sum和score_avg字段中。
结果如图：
实现：
如果我们只需要更新一个字段，MYSQL和ORACLE语法是一样的，在 set 后面跟一个子查询即可，如下：
UPDATE student D
SET D.score_sum =
(
SELECT
SUM(B.score)
FROM score B
WHERE B.studentId = D.id
AND b.examTime >= '2015-03-10'
GROUP BY B.studentId
)
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
现在我们需要同时更新2个字段，最不经过大脑思考的方法就是 “为每个 set 后面都跟一个子查询”，
假如我们要 set 十个字段或者更多字段呢？很显然，这样在性能上是很不合适的方法。
同时更新多个字段在MYSQL和ORACLE中的方法是不一样，MYSQL需要连接表，ORACLE使用 set(...) 即可
(看了下面的SQL你会发现，还是ORACLE简单易用、易懂)
1) MYSQL 实现我们最终的需求，语句如下：
UPDATE student D
LEFT JOIN (SELECT
B.studentId,
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
GROUP BY B.studentId) C
ON (C.studentId = D.id)
SET D.score_sum = c.s_sum,
D.score_avg = c.s_avg
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
2) ORACLE 实现我们最终的需求，语句如下：
UPDATE student D
SET (D.score_sum, D.score_avg) = (
SELECT
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
AND B.studentId = D.id
GROUP BY B.studentId
)
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
本文中用到的2个知识点：
1、更新多条记录，每条记录不同值。
2、同时更新多个字段的方法。
===== 将 age = 1  并且没有测试成绩的同学给予默认值0，调整SQL如下 =====
UPDATE student D
LEFT JOIN (SELECT
B.studentId,
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
GROUP BY B.studentId) C
ON (C.studentId = D.id)
SET D.score_sum =IFNULL(c.s_sum,0),
D.score_avg =IFNULL(c.s_avg,0)
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
##WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
结果如下：
Test SQL
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.28 : Database - test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
/*Table structure for table `score` */
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`studentId` int(11) DEFAULT NULL COMMENT '学员ID',
`subjectName` varchar(20) DEFAULT NULL COMMENT '科目名称',
`score` float DEFAULT NULL COMMENT '考试成绩',
`examTime` datetime DEFAULT NULL COMMENT '考试时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
/*Data for the table `score` */
insert  into `score`(`id`,`studentId`,`subjectName`,`score`,`examTime`) values (1,1,'语文',70,'2015-02-26 18:11:39'),(2,1,'数学',80,'2015-03-26 18:11:50'),(3,1,'英语',76,'2015-04-26 18:11:56'),(4,1,'历史',96,'2015-05-26 18:12:02'),(5,2,'语文\r\n数学\r\n英语\r\n历史\r\n语文',84,'2015-02-26 18:11:39'),(6,2,'数学',56,'2015-03-26 18:11:50'),(7,2,'英语',86,'2015-04-26 18:11:56'),(8,2,'历史',45,'2015-05-26 18:12:02'),(9,3,'语文',87,'2015-02-26 18:11:39'),(10,3,'数学',98,'2015-03-26 18:11:50'),(11,3,'英语',67,'2015-04-26 18:11:56'),(12,3,'历史',86,'2015-05-26 18:12:02'),(13,4,'语文',97,'2015-02-26 18:11:39'),(14,4,'数学',68,'2015-03-26 18:11:50'),(15,4,'英语',79,'2015-04-26 18:11:56'),(16,4,'历史',83,'2015-05-26 18:12:02'),(17,5,'语文',92,'2015-02-26 18:11:39'),(18,5,'数学',93,'2015-03-26 18:11:50'),(19,5,'英语',65,'2015-04-26 18:11:56'),(20,5,'历史',88,'2015-05-26 18:12:02'),(21,6,'语文',87,'2015-01-05 18:48:48'),(22,6,'数学',67,'2015-01-05 18:48:48'),(23,6,'英语',99,'2015-01-05 18:48:48'),(24,6,'历史',88,'2015-01-05 18:48:48');
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(20) DEFAULT NULL COMMENT '姓名',
`score_sum` varchar(20) DEFAULT NULL COMMENT '总成绩',
`score_avg` varchar(20) DEFAULT NULL COMMENT '平均成绩',
`age` int(11) DEFAULT NULL COMMENT '1男0女',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert  into `student`(`id`,`name`,`score_sum`,`score_avg`,`age`) values (1,'小明','252','84',1),(2,'小王','187','62.3',1),(3,'莉莉','','',0),(4,'柱子','230','76.7',1),(5,'大毛','','',0),(6,'亮子','0','0',1);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

数据库外键定义的一个可选项，用来设置当主键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的。
update 是主键表中被参考字段的值更新，delete则是指在主键表中删除一条记录：

on update 和 on delete  后面可以跟的词语有四个
no action  ， set null ，  set default  ，cascade
no action 表示 不做任何操作，
set null    表示在外键表中将相应字段设置为null
set default 表示设置为默认值
cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除，主要运用在多对多的表关系中

在执行事务的过程中，update是会自动加锁的，而select不会，除非后面加for update,也就是加锁。

在执行事务中加锁后，直到事务提交前，其他更新都不会执行。但是查询就不一样了，正常查询，即没有用事务也没有加锁的，不会受影响，但是加了for update的select就会跟更新一样收到影响。

1.alter和update关键字：alter（更改）这个关键字仅仅是用来修改表结构的，对表中的内容无法修改，修改表中的内容要用update（跟新）。
2.desc和asc:desc(降序排序)，asc(升序排序)。跟在order by 后面。

例：select name from student order by math desc;
is null , is not null

select empno,ename,sal from emp where mgr is null;
select ename,sal,comm from emp where comm is null;
select * from emp where comm is not null and comm>0;
别名

把查询到的员工姓名ename 改成 ‘名字’

select ename as ‘名字’from emp;

select ename ‘名字’ from emp;

select ename 名字 from emp;
去重 distinct

查询emp表中出现的所有的职位，去除重复的

select distinct job from emp;
比较运算符 >,<,>=,<=,!=和<>
and和or

and和java 中的&& 一样

or和java 中的|| 效果一样
in 和 not in

当查询某个字段的值为多个时使用in表示等于，not in 表示不等于
between x and y 包括x,y   not between x and y 不在x和y之间
模糊查找 like

_:代表单个未知字符
%：代表0或多个未知字符
select title from t_item where sal<100 and like ‘%记事本%’;

导入文件source d:/tables.sql;
排序 order by(默认升序);  order by xx desc降序/asc升序；

order by 字段名1 desc/asc , 字段2 desc/asc;
分页查询  limit

limit 跳过的数目，返回的数目
数值计算 + - * / % 7%2等效 mod(7,2)
日期相关函数

1，获取当前时间 now() 日期+时间

2，获取当前的日期 curdate()   cur(current 当前)

3，获取当前时间 curtime()

4, 从年月日时分秒中提取年月日 和 时分秒

select date(now());  select time(now());

5,从年月日时分秒中提取时间分量 extract(year/month/day/hour/minute/second from now());

6, 日期格式化   %Y 四位年 %y 两位年

%m 两位月 %c 一位月

%d 日

%H 24小时 %h 12小时

%i 分    %s 秒

date_format(时间，格式)

select date_format(now(),’%Y年%m月%d日 %H时%i分%s秒’)

7，datediff是计算两个时间间的差,如差几天或是几个月,或是几年等

select datediff(day,‘2008.9.2’,‘2008.10.28’)

8，dateadd则是算出在已知时间的基础上再加多少天是几月几日.

select dateadd(year,10,getdate())

select dateadd(month,10,getdate())

select dateadd(day,10,getdate())
7, 把非标准格式转成标准格式： str_to_date(‘非标准格式的时间’,非标准时间的格式）；
8，ifnull(x,y)函数

age = ifnull(x,y)  如果x的值为null则age=y,否则age=x;

聚合函数

用于对多行数据进行统计，平均值，最大值，最小值，求和，统计数量；

1，平均值：avg(字段名称)；

2，最大值：max(字段名);

3, 最小值：min(字段名);

4, 求和：sum(字段名);

5, 统计数量：count(字段名);

字符串相关

1，字符串拼接 concat(‘aa’,‘bb’) -> aabb

2, 获取字符串长度 char_length(‘abc’);

3, 获取字符串在另一个字符串中的位置：

格式一：instr(str,substr);

格式二：locate(substr,str);

4, 插入字符串 insert(str,start,length,newstr);

5, 转大写：upper(str)  转小写：lower(str)

6, 去两端空白：trim(str);

7, 截取字符串：left(str,num); right(str,num);substring(str,start,length);

8, 重复：repeat(str,num);

9, 替换：replace   select replace(‘asdfds’,‘s’,‘x’);把s替换成x；

10，反转 reverse(str);
数学相关函数

1，向下取整  floor(num)

select floor(3.83)   3

2, 四舍五入 round(num) / round(num,m) m表示保留几位小数

select round(23.8) 24        select round(23.879,2) 23.88

3, 非四舍五入  truncate(num,m) 不进行四舍五入，直接保留几位小数

select truncate(23.879,2)  23.87

4，随机数 rand() 0-1
分组查询

1，group by
多字段查询 group by 字段 ， 字段 ，…;
having后面跟聚合函数的条件，需结合group by使用
子查询（嵌套查询）

select max(sal) from emp;

select * from emp sal=5000;
select * from emp where sal=(select max(sal) from emp);ext

嵌套在SQL语句中的查询语句称为子查询

子查询可以嵌套n层

写在那些位置：

写在where或having后面作为查询条件的值
写在from后面当成一张表使用，必须有别名

select * from emp where deptno=10;

select ename from (select * from emp where deptno=10) t1;
写在创建表的时候

create table newemp as(select ename,sal,deptno from emp where deptno=20);
关联查询：同时查询多张表

select e.ename,d.dname //查询的内容

from emp e,dept d  //哪几张表

where e.deptno=d.deptno;  //表之间的建立关系的桥梁
笛卡尔积

如果关联查询不写关联关系，则得到两张表的乘积，是错误的查询方式，工作中切记不要出现
等值连接和内连接

1，等值连接：select * from A,B where A.x=B.x and A.age=18;

2, 内连接：select * from A join B on A.x=B.x where A.age=18;

这两种查询方式得到的结果是一样的，推荐使用内连接(左右表各数据都能匹配的上，使用内连接）
外连接：select * from A left(左外连接)/right(右外连接) join B on A.x=B.x where A.age=18;

左外连接：查询左边表A的全部，而表B只查询满足关系的部分。反之亦然。
表设计之关联关系

主键：主键是表示数据唯一性的字段

外键：是用于建立关系的字段，值通常指向另一张表的主键

一对一：有A，B两张表，A表一条数据对应B表中一条数据
一对多：A，B两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应B表中的一条数据

如何建立关系：在多的表中添加外键指向另一张表的主键

多对多：A，B两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中

如何建立关系：创建一个关系表，两个外键字段分别指向另外两个表的主键
自关联：当前表的外键指向当前表的主键

应用场景：需要保存上下级关系时

select e.ename,m.ename from emp e join emp m on e.mgr=m.deptno;//把一张表当成两张表使
连接方式和关联关系

连接方式：包括等值连接，内连接，外连接，是指多张表查询时使用的查询方式

关联关系：包括一对一，一对多，多对多，是指表设计时，两张表存在的逻辑关系
表设计案例：权限管理
视图：数据库中的表和视图都是其内部对象，视图可以理解成一张虚拟的表，视图本质就是取代一条

SQL语句（可以起到代码复用的效果，可以隐藏敏感信息）

创建格式：create view 视图名 as 子查询

create table 表名 as 子查询

视图的分类：

1：简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询。可以对视图中的数据进行增删改查操作

2：复杂视图：创建视图的子查询中包含以上一种，只能对视图中数据进行查询操作
简单视图的增删改查操作方式和table一样
如果插入一条在视图中不可见，在原表中可见的数据称为，数据污染。通过 with check option 解决数据污染问题。
视图中只能修改和删除视图中有的数据
创建或替换视图：create or replace view 视图名 as();
如果创建视图的子查询中使用了别名，则对视图操作时只能使用别名

例：create view v_emp_10 as(select ename name(别名+6) from emp where deptno=10);

select name from v_emp_10;//成功

select ename from v_emp_10;//失败
约束：就是给表字段添加的限制条件。

1，主键约束 primary key

作用：唯一且非空
2，非空约束：not null

作用：该字段的值不能为null。create table t_null (id int,age int not null);
3,唯一约束：unique

作用：该字段不能重复
4，默认约束 default

作用：给字段设置默认值 create table t_default (id int,age int default 20);

insert into t_default(id) values(1);//默认值会生效
5,外键约束：用来建立关系的字段称为外键，外键约束：添加外键约束的字段，值可以是null，可以重复，但是

不能是表中不存在的数据，外键指向的数据不能先删除，外键指向也不能先删除

格式：constraint 约束名称 foregin key（外键字段名）references 依赖的表名（依赖的字段名）
索引：是数据库中用来提高查询效率的技术，类似于目录。

好处：如果不使用索引，数据会零散的保存再磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到

数据为止，使用索引后会将磁盘块以树状结构保存，查询数据时会大大降低磁盘块的访问数量，从而提高

效率。

坏处：如果表中的数据少，使用索引反而会降低查询效率。索引会占用磁盘空间，所以不是越多越好。
创建索引：create index 索引名 on 表名（字段名（字符长度））
分类：

1，聚集索引：通过主键创建的索引称为聚集索引，其保存数据，只要给表添加主键约束，则会自动创建聚集索引

2，非聚集索引：通过非主键创建的索引，非聚集索引中没有数据，只有地址

查看索引：show index from 表名；

删除索引：drop index 索引名 on 表名；
复合索引：通过多个字段创建的索引

格式：create index 索引名 on 表名（字段1，字段2）；
事物的ACID特性：是保证数据库事物正确执行的四大基本要素。

1，Atomicty:原子性，最小不可拆分，保证全部成功或全部失败

2，Consistency：一致性，保证事物从一个一致状态到另一个一致状态

3，Isolation：隔离性，多个事物互不影响

4，Durality：持久性，事物提交之后数据保存到数据库文件中持久生效
group_concat():组连接
作业：

1，每个部门的人数，根据人数降序排序

select deptno,count() from emp group by deptno order by count() desc;

2，每个部门中，每个主管的手下人数

select deptno,count(),mgr from emp where mgr is not null group by mgr,deptno;

3，每种工作的平均工资

select job,avg(sal) from emp group by job;

4，每年的入职人数

select extract(year from hiredate) year,count() from emp group by year;

5，少于等于3个人的部门信息

select deptno from emp group by deptno having count()<=3;

select * from dept where deptno in (select deptno from emp group by deptno having count()<=3);

6，拿最低工资的员工信息

select * from emp where sal=(select min(sal) from emp);

7，只有一个下属的主管信息

select mgr from emp where mgr is not null group by mgr having count()=1;//得到每一个主管的下属人数

select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count()=1);

8，每月发工资最多的部门信息

select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1;//得到最多的钱数

select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1); //通过钱数获得部分编号

select * from dept where deptno in(select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1));

9，下属最多的人，查询其个人信息

select count() from emp group where mgr is not null by mgr order by count() desc limit 0,1;

select mgr from emp group by mgr having count()=();

select * from emp where deptno in ();

10，拿最高工资员工的同事信息

select max(sal) from emp;

select job from emp where sal=(select max(sal) from emp);

select * from emp where job=(select job from emp where sal=(select max(sal) from emp)) and sal!=(select max(sal) from emp);

11，和最后入职的员工在同一部门的员工信息

select max(hiredate) from emp;

select deptno from emp where hiredate=();

select * from emp where deptno=();

12，查询平均工资高于20号平均工资的部门信息

13，查询员工信息和员工对应部门名称

select e.,d.dname from emp e jion dept d on e.deptno=d.deptno;

14，查询员工信息，部门名称，所在城市

15，查询Dallas市所有员工信息

16, 查询每个城市的员工人数（需要查询出波士顿0人）

select d.loc,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.loc;

17, 查询每个员工的名字和对应的主管名

//如果表中建立关系的字段指向当前表的主键，则称为自关联，关联查询时把一张表当成两张表

select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno;

18，查询每个员工的名字，对应主管的名字和对应部门的名字

select e.ename,m.ename,d.dname from emp e left join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno;

19, 查询emp表中所有员工姓名以及员工的领导编号，领导姓名和职位

select e.ename,m.empno,m.ename,m.job from emp e left join emp m on e.mgr=m.empno;

20，查询emp表名字中没有字母k的多有员工编号姓名职位和工资

select e.empno,e.ename,e.job,e.sal.d.dname from emp e left join dept d on e.deptno=d.deptno where

e.ename not like ‘%k%’;

21，查询dept表中所有部门的信息和与之相关的员工名字和工资，排除掉10号部门，根据部门编号降序排序，

根据部门编号降序排序，如果部门编号一致则按照工资升序排序