Java教程
数据库常见操作集
目录
数据库应用
1.1 概念
1.1.1 什么是数据库
1.1.2 关系型和非关系型
1.1.3 关系型数据库
1.2 Mysql数据库
1.2.1 MySQL数据存放在哪里？
1.2.2 MySQL服务端
1.2.3 MySQL客户端1：DOS窗口
1.2.4 MySQL客户端2：可视化工具
1.3 数据库的结构
1.3.1 数据库结构
1.4 SQL语句
1.4.1 定义
1.4.2 分类
1.5 数据库常用操作
1.5.1 建库
1.5.2 删库
1.5.3 查看所有数据库
1.6 表的常用操作
1.6.1 表设计
1.6.2 创建表
1.6.3 修改表
1.6.4 删除表
1.6.5 查看所有表
1.6.6 查看表结构/设计表
1.7 表记录的常用操作
1.7.1 插入记录
1.7.2 查询记录
1.7.3 修改记录
1.7.4 删除记录
1.7.5 排序
1.7.6 记录总数
1.8 数据类型
1.8.1 命名规则
1.8.2 字符
1.8.3 数字
1.8.4 日期
1.8.5 图片
1.9 字段约束
1.9.1 主键约束
1.9.2 非空约束
1.9.3 唯一约束
1.10 准备数据
1.10.1 部门表 dept
1.10.2 员工表 emp
1.11 基础函数
1.11.1 lower
1.11.2 upper
1.11.3 length
1.11.4 substr
1.11.5 concat
1.11.6 replace
1.11.7 ifnull
1.11.8 round & ceil & floor
1.11.9 uuid
1.11.10 now
1.11.11 year & month & day
1.11.12 转义字符
1.12 条件查询
1.12.1 distinct
1.12.2 where
1.12.3 like
1.12.4 null
1.12.5 between and
1.12.6 limit
1.12.7 order by
1.13 统计案例
1.13.1 入职统计
1.13.2 年薪统计
1.14 聚合 aggregation
1.14.1 count
1.14.2 max / min
1.14.3 sum / avg
1.15 分组 group
1.15.1 group by
1.15.2 having
1.16 小结
1.16.1 char和varchar有什么区别？
1.16.2 datetime和timestamp有什么区别？
1.16.3 中文乱码
1.16.4 注释
1.16.5 主键、外键、唯一索引的区别？
1.16.6 drop、delete和truncate之间的区别？
1.17 事务 transaction
1.1.1 什么是事务
1.17.1 事务4个特性ACID
1.17.2 隔离级别
1.17.3 查询mysql的隔离级别
1.17.4 事务处理
1.17.5 提交 commit
1.17.6 回滚 rollback
1.18 表关联 association
1.18.1 概念
1.19 考试系统mysql版
1.19.1 表设计
1.19.2 创建数据库
1.19.3 创建表
1.19.4 生成建表SQL
1.19.5 插入测试数据
1.20 表强化：6约束 constraints
1.20.1 非空约束 not null
1.20.2 唯一约束 unique
1.20.3 主键约束 primary key
1.20.4 外键约束 forgrein key
1.20.5 默认约束 default
1.20.6 检查约束 check
1.21 多表联查 join
1.21.1 笛卡尔积 Cartesian product
1.21.2 三种连接 join
1.21.3 案例：列出research部门下的所有员工的信息
1.21.4 案例：怎么用内链接 INNER JOIN 实现上面的需求？
1.21.5 案例：列出tony的扩展信息
1.21.6 inner join、left join、right join的区别？
1.22 索引 index
1.22.1 定义
1.22.2 分类
1.22.3 创建索引
1.22.4 索引扫描类型
1.22.5 最左特性
1.22.6 为何索引快？
1.22.7 小结
1.23 SQL面试题
1.23.1 查询所有记录
1.23.2 只查询指定列
1.23.3 查询id为100的记录
1.23.4 模糊查询记录
1.23.5 查询之间范围之间的所有记录
1.23.6 查询满足两个条件的记录
1.23.7 查询用户住址
1.23.8 查询19岁人的名字
1.23.9 按age升序查询记录
1.23.10 以name升序、age降序查询记录
1.23.11 查询总人数
1.23.12 查询各个城市的人数
1.23.13 查询至少有2人的地址
1.23.14 查询记录中最年长和最年轻
1.23.15 查询大于平均年龄的记录
1.23.16 查询年龄最大的用户信息
1.23.17 查询各部门的最高薪
1.23.18 查询各科的平均工资
1.23.19 查询id是100或200的记录
1.23.20 查询存在部门的员工信息
1.23.21 查询没划分部门的员工信息
1.23.22 查询同名的员工记录
1.23.23 全部学生按出生年月排行
1.23.24 每个班上最小年龄的学员
1.23.25 查询学生的姓名和年龄
1.23.26 查询男教师及其所上的课程
1.23.27 查询每个老师教的课程
1.23.28 查询女老师的信息
1.23.29 查询得分前3名的学员信息
1.23.30 查询课程是“计算机导论”的，得分前3名的学员信息
1.23.31 课程号“3-105”的倒数最后3名学员排行
1.24 SQL的执行顺序
1.24.1 SQL语句
1.25 考试系统oracle版
1.25.1 PD创建Oracle模型
1.25.2 表设计
1.26 Oracle数据库
1.26.1 安装
1.26.2 Oracle10g EX
1.26.3 配置本地服务
1.26.4 链接错误
1.27 创建数据库
1.27.1 注意事项
1.27.2 密码安全
1.27.3 创建用户
1.28 PL/SQL客户端操作
1.28.1 常见错误
1.28.2 选择自己的内容
1.28.3 常用对象
1.28.4 SQL窗口
1.29 准备数据
1.30 SQL差异
1.30.1 概念
1.30.2 日期
1.30.3 性别
1.30.4 decode()
1.30.5 casewhen
1.30.6 第一条
1.31 独特技术
1.31.1 过气技术
1.31.2 虚表 dual
1.31.3 序列 sequence
1.31.4 授权视图
1.31.5 视图 view
1.31.6 触发器 trigger
1.31.7 存储过程 procedure
1.32 分页
1.32.1 rownum伪列
1.32.2 分页SQL
1.33 存储过程实现分页
1.33.1 创建包同时创建游标
1.33.2 创建分页存储过程
1.33.3 测试
1.33.4 预习TestJDBC.java
1.34 JDBC
1.34.1 概念
1.34.2 idea 创建项目导驱动包
1.34.3 Statement 语句
1.34.4 PreparedStatement 语句
1.35 Git版本控制
1.35.1 开发难题
1.35.2 主流的版本控制产品
1.35.3 介绍
1.35.4 组成结构图
1.35.5 命令速查
1.35.6 常用命令
1.36 码云配置环境
1.36.1 注册账号
1.36.2 登录
1.36.3 安装GIT
1.36.4 设置字体
1.36.5 查询git
1.36.6 配置身份信息
1.36.7 查看配置信息
1.37 码云创建仓库
1.37.1 创建远程仓库
1.37.2 创建本地仓库
1.38 每日任务
1.38.1 新文件
1.38.2 推送
1.38.3 拉取
1.39 常见错误
1.39.1 Authentication failed for
1.39.2 To the same ref
1.40 SQL表准备
1.40.1 mysql-db库
1.40.2 student表
1.40.3 tb_dept表
1.40.4 tb_user表
1.41 基础SQL优化
1.41.1 查询SQL尽量不要使用select *，而是具体字段
1.41.2 避免在where子句中使用or来连接条件
1.41.3 使用varchar代替char
1.41.4 尽量使用数值替代字符串类型
1.41.5 查询尽量避免返回大量数据
1.41.6 使用explain分析你SQL执行计划
1.41.7 是否使用了索引及其扫描类型
1.41.8 创建name字段的索引
1.41.9 优化like语句
1.41.10 字符串怪现象
1.41.11 索引不宜太多，一般5个以内
1.41.12 索引不适合建在有大量重复数据的字段上
1.41.13 where限定查询的数据
1.41.14 避免在索引列上使用内置函数
1.41.15 避免在where中对字段进行表达式操作
1.41.16 避免在where子句中使用!=或<>操作符
1.41.17 去重distinct过滤字段要少
1.41.18 where中使用默认值代替null
1.42 高级SQL优化
1.42.1 批量插入性能提升
1.42.2 批量删除优化
1.42.3 伪删除设计
1.42.4 提高group by语句的效率
1.42.5 复合索引最左特性
1.42.6 排序字段创建索引
1.42.7 删除冗余和重复的索引
1.42.8 不要有超过5个以上的表连接
1.42.9 inner join 、left join、right join，优先使用inner join
1.42.10 in子查询的优化
1.42.11 尽量使用union all替代union
数据库应用
1.1 概念
1.1.1 什么是数据库
简而言之，就是存储数据，管理数据的仓库。
常见的数据库分为：
l 关系型数据库， Oracle、MySQL、SQLServer、Access
l 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
1.1.2 关系型和非关系型
早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）。业界总在争论nosql能否干掉传统数据库，很多初学者也有这个困惑。以我来看，两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle。
说明: http://img0.imgtn.bdimg.com/it/u=701779361,2797565197&fm=21&gp=0.jpg
既然干不掉，很多传统项目的还是围绕关系型数据库的居多，所以我们先来学习关系型数据库，目前最流行的关系型数据库是MySQL。
1.1.3 关系型数据库
关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。
1.2 Mysql数据库
1） mysql服务端，它来处理具体数据维护，保存磁盘
2） mysql客户端，CRUD新增，修改，删除，查询
1.2.1 MySQL数据存放在哪里？
在MySQL的配置文件my.ini中会进行默认配置
1.2.2 MySQL服务端
mysql-5.5.27-winx64.msi
Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8
说明: C:\Users\Administrator\AppData\Local\Temp\1608528587(1).png
说明: C:\Users\Administrator\AppData\Local\Temp\1608528712(1).png
注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限。
1.2.3 MySQL客户端1：DOS窗口
mysql -uroot -proot
语法：mysql.exe执行文件
代表参数
-u 用户名，紧接着写的
-p 密码，紧接着写的
1.2.4 MySQL客户端2：可视化工具
说明: C:\Users\Administrator\AppData\Local\Temp\1608529000(1).png
1.3 数据库的结构
1.3.1 数据库结构
1.4 SQL语句
1.4.1 定义
结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。
1.4.2 分类
Ø DML（Data Manipulation Language）数据操纵语言
如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
Ø DDL（Data Definition Language）数据库定义语言
如：create table之类
Ø DCL（Data Control Language）数据库控制语言
如：grant、deny、revoke等，只有管理员才有相应的权限
Ø 数据查询语言（DQL:Data Query Language）：
注意：SQL不区分大小写
1.5 数据库常用操作
1.5.1 建库
Ø 创建数据库，数据库名称：cbg2011
create database cbg2011 DEFAULT CHARACTER SET utf8;
1.5.2 删库
Ø 删除名称是cbg2011的数据库
drop database cbg2011;
1.5.3 查看所有数据库
Ø 查看所有数据库
show databases;
1.6 表的常用操作
使用数据库：use cbg2011;
1.6.1 表设计
门店表：tb_door
订单详情表：tb_order_detail
1.6.2 创建表
Ø 创建tb_door表，有id,door_name,tel字段
create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);
1.6.3 修改表
Ø 添加列
alter table tb_door add column see NUMERIC(7,2)
1.6.4 删除表
Ø 删除名称是tb_door的表
drop table tb_door;
1.6.5 查看所有表
Ø 查看所有表
show tables;
1.6.6 查看表结构/设计表
Ø 查看tb_door表结构
desc tb_door;
1.7 表记录的常用操作
1.7.1 插入记录
Ø 向tb_door表中插入2条记录
insert into tb_door values(null,’永和大王1店’,666);
insert into tb_door values(null,’ 永和大王2店’,888);
1.7.2 查询记录
Ø 查询tb_door表中的所有记录
SELECT * FROM tb_door;
1.7.3 修改记录
Ø 修改tb_door表中id为1的记录
update tb_door set tel=555 where id=1;
1.7.4 删除记录
Ø 删除tb_door表中id为2的数据
Delete from tb_door where id=2;
1.7.5 排序
Ø 将tb_door表记录按照tel排序
Select * from tb_door order by tel desc;
1.7.6 记录总数
Ø 查询tb_door表中的总记录数
Select count(*) from tb_door;
1.8 数据类型
1.8.1 命名规则
l 字段名必须以字母开头，尽量不要使用拼音
l 长度不能超过30个字符（不同数据库，不同版本会有不同）
l 不能使用SQL的保留字，如where,order,group
l 只能使用如下字符az、AZ、0~9、$ 等
l Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
l 多个单词用下划线隔开，而非java语言的驼峰规则
1.8.2 字符
l char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
l varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
l 大文本: 大量文字（不推荐使用，尽量使用varchar替代）
以utf8编码计算的话，一个汉字在u8下占3个字节
注：不同数据库版本长度限制可能会有不同
1.8.3 数字
l tinyint,int整数类型
l float,double小数类型
l numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
l decimal和numeric表示精确的整数数字
1.8.4 日期
l date 包含年月日
l time时分秒
l datetime包含年月日和时分秒
l timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数
1.8.5 图片
l blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。
1.9 字段约束
1.9.1 主键约束
主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。
添加主键约束，例如将id设置为主键：
主键自增策略:当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1
create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;
说明: C:\Users\Administrator\AppData\Local\Temp\1608529938(1).png
1.9.2 非空约束
非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
添加非空约束，例如为password添加非空约束：
create table user(
id int primary key auto_increment, password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,‘123’);//OK
1.9.3 唯一约束
唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
添加唯一约束，例如为username添加唯一约束及非空约束：
create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,‘lisi’);
insert into test values(null,‘lisi’);–username的值要唯一,重复会报错的
select * from test;
1.10 准备数据
1.10.1 部门表 dept
字段名称
数据类型
是否为空
备注
deptno
int
部门编号，PK主键
dname
varchar(20)
Y
部门名称
loc
varchar(13)
Y
部门所在地点
CREATE TABLE dept(
deptno int primary key auto_increment NOT NULL,
dname VARCHAR(20),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(1,‘accounting’,‘一区’);
INSERT INTO dept VALUES(2,‘research’,‘二区’);
INSERT INTO dept VALUES(3,‘operations’,‘二区’);
1.10.2 员工表 emp
字段名称
数据类型
是否为空
备注
empno
int
员工编号，PK主键
ename
varchar(10)
Y
员工名称
job
varchar(10)
Y
职位
mgr
int
Y
上级编号
hiredate
datetime
Y
月工资
sal
double
Y
奖金
comm
NUMERIC(8,2)
Y
奖金
deptno
int
Y
所属部门 FK外键
Mysql:
CREATE TABLE emp(
empno int primary key auto_increment NOT NULL,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),
deptno int
);
INSERT INTO emp VALUES(100,‘jack’,‘副总’,NULL,‘2002-05-03’,90000,NULL,1);
INSERT INTO emp VALUES(200,‘tony’,‘总监’,100,‘2015-02-02’,10000,2000,2);
INSERT INTO emp VALUES(300,‘hana’,‘经理’,200,‘2017-02-02’,8000,1000,2);
INSERT INTO emp VALUES(400,‘leo’,‘员工’,300,‘2019-02-22’,3000,200.12,2);
INSERT INTO emp VALUES(500,‘liu’,‘员工’,300,‘2019-03-19’,3500,200.58,2);
1.11 基础函数
1.11.1 lower
SELECT ‘ABC’,LOWER(‘ABC’) from dept; --数据转小写
1.11.2 upper
select upper(dname) from dept --数据转大写
1.11.3 length
select length(dname) from dept --数据的长度
1.11.4 substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
1.11.5 concat
select dname,concat(dname,‘123’) X from dept --拼接数据
1.11.6 replace
select dname,replace(dname,‘a’,‘666’) X from dept --把a字符替换成666
1.11.7 ifnull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
1.11.8 round & ceil & floor
round四舍五入，ceil向上取整，floor向下取整
–直接四舍五入取整
select comm,round(comm) from emp
–四舍五入并保留一位小数
select comm,round(comm,1) from emp
–ceil向上取整,floor向下取整
select comm,ceil(comm) ,floor(comm) from emp
1.11.9 uuid
SELECT UUID()
返回uuid：a08528ca-741c-11ea-a9a1-005056c00001
1.11.10 now
select now() – 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
1.11.11 year & month & day
–hour()时 minute()分 second()秒
select now(),hour(now()),minute(now()),second(now()) from emp ;
–year()年 month()月 day()日
select now(),year(now()),month(now()),day(now()) from emp ;
1.11.12 转义字符
’作为sql语句符号，内容中出现单撇就会乱套，进行转义即可
select ‘ab’cd’ – 单引号是一个SQL语句的特殊字符
select ‘ab’cd’ --数据中有单引号时,用一个\转义变成普通字符
1.12 条件查询
1.12.1 distinct
使用distinct关键字，去除重复的记录行
SELECT loc FROM dept;
SELECT DISTINCT loc FROM dept;
1.12.2 where
注意:where中不能使用列别名!!
select * from emp
select * from emp where 1=1 --类似没条件
select * from emp where 1=0 --条件不成立
select * from emp where empno=100 --唯一条件
select * from emp where ename=‘tony’ and deptno=2 --相当于两个条件的&关系
select * from emp where ename=‘tony’ or deptno=1 --相当于两个条件的|关系
select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
– 或
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);
1.12.3 like
通配符%代表0到n个字符，通配符下划线_代表1个字符
select * from emp where ename like ‘l%’ --以l开头的
select * from emp where ename like ‘%a’ --以a结束的
select * from emp where ename like ‘%a%’ --中间包含a的
select * from emp where ename like ‘l__’ --l后面有两个字符的 _代表一个字符位置
1.12.4 null
select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的
1.12.5 between and
SELECT * FROM emp
select * from emp where sal>3000 and sal<10000
select * from emp where sal>=3000 and sal<=10000–等效
select * from emp where sal between 3000 and 10000–等效
1.12.6 limit
分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：
select * from emp limit 2 --列出前两条
select * from emp limit 1,2 --从第二条开始,展示2条记录
select * from emp limit 0,3 --从第一条开始,展示3条记录–前三条
1.12.7 order by
SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序
1.13 统计案例
1.13.1 入职统计
#2015年以前入职的老员工
SELECT * FROM emp WHERE DATE_FORMAT(hiredate,’%Y-%m-%d’)<‘2015-01-01’;
SELECT * FROM emp WHERE YEAR(hiredate)<2015
#2019年以后签约的员工，日期进行格式转换后方便比较
SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,’%Y-%m-%d’))>=2019;
#2015年到2019年入职的员工
SELECT * FROM emp
WHERE
STR_TO_DATE(hiredate,’%Y-%m-%d’)>=‘2015-01-01’
AND
STR_TO_DATE(hiredate,’%Y-%m-%d’)<=‘2019-12-31’
1.13.2 年薪统计
公司福利不错13薪，年底双薪，统计员工的年薪=sal13+comm13
SELECT empno,ename,job,sal13+comm13 FROM emp;
SELECT empno,ename,job,sal13+comm13 as 年薪 FROM emp;–用as给列起个别名
SELECT empno,ename,job,sal13+comm13 年薪 FROM emp; --as也可以省略
select ename, sal+comm from emp
select ename, sal , comm, sal+ifnull(comm,0) from emp–用0替换掉null
1.14 聚合 aggregation
根据一列统计结果
1.14.1 count
select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(comm) from emp --慢,只统计非NULL的
1.14.2 max / min
select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组
1.14.3 sum / avg
平均值
select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数
1.15 分组 group
用于对查询的结果进行分组统计
group by表示分组, having 子句类似where过滤返回的结果
1.15.1 group by
#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对
SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno #按照deptno分组
SELECT job,MAX(sal),AVG(sal) FROM emp
GROUP BY job #按照job分组
SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都满足的
1.15.2 having
#平均工资小于8000的部门
select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal) <8000 #查询条件,类似where,但是group by只能配合having
#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp
GROUP BY deptno #按deptno分组
HAVING COUNT(deptno)>1 #次数多的
1.16 小结
1.16.1 char和varchar有什么区别？
char为定长字符串，char(n)，n最大为255
varchar为不定长字符串，varchar(n)，n最大长度为65535
char(10)和varchar(10)存储abc，那它们有什么差别呢？
char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。
1.16.2 datetime和timestamp有什么区别？
数据库字段提供对日期类型的支持，是所有数据类型中最麻烦的一个，慢慢使用就会体会出来。
date 是 年与日
time是 时分秒
datetime年月日时分秒，存储和显示是一样的
timestamp时间戳，存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数
1.16.3 中文乱码
如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：
set names utf8;
set names gbk;
设置客户端字符集和服务器端相同。如果不知道它到底用的什么编码？怎么办呢？很简单，两个都尝试下，哪个最后操作完成，查询数据库不乱码，就用哪个。
那为何会造成乱码呢？
Mysql数据库默认字符集是lantin1，也就是以后网页中遇到的ISO8859-1，它是英文字符集，不支持存放中文。我们创建库时，可以指定字符集：
create database yhdb charset utf8;
但这样很容易造成服务器和客户端编码集不同，如服务器端utf8，客户端ISO8859-1。mysql和客户端工具都有习惯的默认编码设置，好几个地方，要都统一才可以保证不乱码。
我们只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。
1.16.4 注释
/* 很多注释内容 */
一行注释内容
– 一行注释内容，这个使用较多
1.16.5 主键、外键、唯一索引的区别？
l Primary Key 主键约束，自动创建唯一索引
l Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
l Unique Index 唯一索引，唯一值但不是主键
对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。
1.16.6 drop、delete和truncate之间的区别？
drop删除库或者表，数据和结构定义
delete和truncate只是删除表的数据
delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数
1.17 事务 transaction
1.1.1 什么是事务
数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。
下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：
l 给A的账户减去100元；
update 账户表 set money=money-100 where name=‘A’;
l 给B的账户加上100元。
update 账户表 set money=money+100 where name=‘B’;
如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。
如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。
也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。
1.17.1 事务4个特性ACID
一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
l 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
l 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
l 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
l 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
1.17.2 隔离级别
事务隔离分为不同级别，包括
l 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
l 读提交（read committed） Oracle默认的隔离级别
l 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
l 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发
1.17.3 查询mysql的隔离级别
在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。
l 开启事务：start transaction；
l 结束事务：commit（提交事务）或rollback（回滚事务）。
在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！
SELECT @@tx_isolation;
Repeatable Read（可重读）
MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
1.17.4 事务处理
l 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
l 事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
l 事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
l MySQL默认数据库的事务是开启的，执行SQL后自动提交。
l MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。
1.17.5 提交 commit
#多条语句时，批量执行，事务提交
#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱
#mysql的事务默认就是开启的 – 多条语句一起操作时,要么一起成功要么一起失败
BEGIN; #关闭事务的自动提交
INSERT INTO user (id) VALUES(25);#成功
INSERT INTO user (id) VALUES(5);#已经存在5了，会失败
COMMIT; #手动提交事务
1.17.6 回滚 rollback
#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚
BEGIN;
INSERT INTO user (id) VALUES(15);
INSERT INTO user (id) VALUES(35);#存在了
ROLLBACK;#事务回滚，就不会再提交了
1.18 表关联 association
1.18.1 概念
表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。
同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。
下面我们讨论表的关系分为四种：
l 一对一 one to one QQ和QQ邮箱，员工和员工编号
l 一对多 one to many 最常见，部门和员工，用户和订单
l 多对一 many to one 一对多反过来，员工和部门，订单和用户
l 多对多 many to many 老师和学生，老师和课程
1.19 考试系统mysql版
1.19.1 表设计
l 外键：由子表出发向主表拖动鼠标，到达主表后松手，PD会自动添加外键字段
l 讲师表和课程表：一对多，两张表。关联关系体现：子表存储主表的主键，称外键
l 课程表和学生表：多对多，三张表。关联关系体现：子表无法存储主表的多条关联信息，只能再创建一张表来存储其信息
l 中间表：存储两张表各自的主键，某一张表的主键无法标识记录的唯一性，两个一起才可以标识唯一，这种主键为多个字段的称为复合主键
1.19.2 创建数据库
1.19.3 创建表
表设计特点：
l 表都以s结束，标识复数
l 字段多以表的首字母作为开头，在多表联查时，方便标识出是哪个表的字段
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2020 */
/*==============================================================*/
drop table if exists courses;
drop table if exists scores;
drop table if exists students;
drop table if exists teachers;
/*==============================================================*/
/* Table: courses */
/*==============================================================*/
create table courses
(
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(3) not null,
primary key (cno)
);
/*==============================================================*/
/* Table: scores */
/*==============================================================*/
create table scores
(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10,1) not null,
primary key (sno, cno)
);
/*==============================================================*/
/* Table: students */
/*==============================================================*/
create table students
(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5),
primary key (sno)
);
/*==============================================================*/
/* Table: teachers */
/*==============================================================*/
create table teachers
(
tno varchar(3) not null,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),
primary key (tno)
);
alter table courses add constraint FK_Reference_3 foreign key (tno)
references teachers (tno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_1 foreign key (sno)
references students (sno) on delete restrict on update restrict;
alter table scores add constraint FK_Reference_2 foreign key (cno)
references courses (cno) on delete restrict on update restrict;
1.19.4 生成建表SQL
1.19.5 插入测试数据
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,‘曾华’ ,‘男’ ,‘1977-09-01’,95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,‘匡明’ ,‘男’ ,‘1975-10-02’,95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,‘王丽’ ,‘女’ ,‘1976-01-23’,95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,‘李军’ ,‘男’ ,‘1976-02-20’,95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,‘王芳’ ,‘女’ ,‘1975-02-10’,95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,‘陆君’ ,‘男’ ,‘1974-06-03’,95031);
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,‘易天’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’);
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,‘王旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’);
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,‘李萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’);
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,‘陈冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘3-105’ ,‘计算机导论’,825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘3-245’ ,‘操作系统’ ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘6-166’ ,‘模拟电路’ ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘6-106’ ,‘概率论’ ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘9-888’ ,‘高等数学’ ,831);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,‘3-245’,86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,‘3-245’,75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,‘3-245’,68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,‘3-105’,92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,‘3-105’,88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,‘3-105’,76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,‘3-105’,64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,‘3-105’,91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,‘3-105’,78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,‘6-166’,85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,‘6-106’,79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,‘6-166’,81);
1.20 表强化：6约束 constraints
1.20.1 非空约束 not null
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user(
id INT AUTO_INCREMENT,
NAME VARCHAR(30) UNIQUE NOT NULL,
age INT,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(30) UNIQUE NOT NULL,
PRIMARY KEY (id)
);
DESC tb_user;
#id为自增主键，null值无效，数据库会自动用下一个id值替代
#age因为运行为null，所以可以设置为null
INSERT INTO tb_user (id,age) VALUES(NULL,NULL);
1.20.2 唯一约束 unique
Name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user(
id INT,
NAME VARCHAR(30) UNIQUE NOT NULL,
phone VARCHAR(20) UNIQUE NOT NULL,
email VARCHAR(30) UNIQUE NOT NULL,
PRIMARY KEY (id)
);
DESC tb_user;
INSERT INTO tb_user (id,NAME) VALUES(1,‘tony’);
INSERT INTO tb_user (id,NAME) VALUES(2,‘tony’);
执行上面语句出错：
Query : INSERT INTO tb_user (id,NAME) VALUES(2,‘tony’)
Error Code : 1062
Duplicate entry ‘tony’ for key ‘name’
展示表结构：
DESC tb_user;
1.20.3 主键约束 primary key
主键是一条记录的唯一标识，具有唯一性，不能重复
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user(
id INT,
NAME VARCHAR(30),
PRIMARY KEY (id)
);
INSERT INTO tb_user (id,NAME) VALUES(1,‘tony’);
INSERT INTO tb_user (id,NAME) VALUES(1,‘hellen’);
第二句插入就会报错：
Query : INSERT INTO tb_user (id,NAME) VALUES(1,‘hellen’)
Error Code : 1062
Duplicate entry ‘1’ for key ‘PRIMARY’
提示主键1的值已经存在，重复了
1.20.4 外键约束 forgrein key
DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT ‘男’, #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
createdTime DATE DEFAULT NOW()
);
CREATE TABLE tb_user_address (
user_id INT PRIMARY KEY NOT NULL,
address VARCHAR(200),
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
DESC tb_user;
tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错
1.20.5 默认约束 default
默认值
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT ‘男’, #默认值
phone CHAR(18),
age INT,
createdTime DATE DEFAULT NOW()
);
DESC tb_user;
1.20.6 检查约束 check
很少使用，了解即可，录入age超过200将报错
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT ‘男’, #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
createdTime DATE DEFAULT NOW()
);
DESC tb_user;
1.21 多表联查 join
1.21.1 笛卡尔积 Cartesian product
多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。
#把两个表的数据都拼接起来
SELECT * FROM dept,emp
上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。
这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。
这就是阿里规范中禁止3张表以上的联查的原因：
1.21.2 三种连接 join
l 内连接 inner join
l 左（外）连接 left join
l 右（外）连接 right join
1.21.3 案例：列出research部门下的所有员工的信息
SELECT * FROM emp
WHERE deptno = ( SELECT deptno FROM dept WHERE dname=‘research’ )
1.21.4 案例：怎么用内链接 INNER JOIN 实现上面的需求？
SELECT d.dname,e.ename,e.job
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno
WHERE d.dname=‘research’
换成left join和right join，看看有什么不同呢？
1.21.5 案例：列出tony的扩展信息
SELECT *
FROM emp e INNER JOIN empext t
ON e.empno=t.empno
WHERE e.ename=‘tony’
换成left join和right join，看看有什么不同呢？
1.21.6 inner join、left join、right join的区别？
l INNER JOIN两边都对应有记录的才展示，其他去掉
l LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
l RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充
1.22 索引 index
1.22.1 定义
索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
1.22.2 分类
l 单值索引：一个索引只包括一个列，一个表可以有多个列
l 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
l 复合索引：一个索引同时包括多列
1.22.3 创建索引
#查看索引，主键会自动创建索引
SHOW INDEX FROM empext
#创建索引
#CREATE INDEX empext_index ON empext (cardno)
创建唯一索引
ALTER TABLE empext ADD UNIQUE (phone)
ALTER TABLE empext ADD UNIQUE (cardno)
创建复合索引
ALTER TABLE empext ADD INDEX idx_phone_cardno (phone,cardno)
创建复合唯一索引
ALTER TABLE empext
ADD UNIQUE idx_phone_cardno(phone,cardno)
删除索引
ALTER TABLE empext DROP INDEX cardno
1.22.4 索引扫描类型
type：
l ALL 全表扫描，没有优化，最慢的方式
l index 索引全扫描，其次慢的方式
l range 索引范围扫描，常用语<，<=，>=，between等操作
l ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
l eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
l const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
l null MySQL不访问任何表或索引，直接返回结果
从最好到最差的类型：
system > const > eq_ref > ref > range > index > ALL
possible_keys：
l 显示可能应用在这张表中的索引
key：
l 真正使用的索引方式
1.22.5 最左特性
EXPLAIN
SELECT * FROM empext WHERE phone=‘13572801415’
EXPLAIN
SELECT * FROM empext WHERE cardno=‘610113’
EXPLAIN
SELECT * FROM empext WHERE phone=‘13572801415’ AND cardno=‘610113’
EXPLAIN
SELECT * FROM empext WHERE cardno=‘610113’ AND phone=‘13572801415’
当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。
1.22.6 为何索引快？
明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。
其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。
l 排序，tree结构，类似二分查找
l 索引表小
1.22.7 小结
优点:
l 索引是数据库优化
l 表的主键会默认自动创建索引
l 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
l 索引事先对数据进行了排序，降低查询数据排序的成本，降低CPU的消耗
缺点:
l 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
l 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
l 虽然索引大大提高了查询的速度，但反向影响了增、删、改操作的效率。如表中数据变化之后，会造成索引内容不正确，需要更新索引表信息，如果数据量非常巨大，重新创建索引的时间就大大增加
l 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引
1.23 SQL面试题
1.23.1 查询所有记录
select * from emp
1.23.2 只查询指定列
SELECT id,ename,sal from emp
1.23.3 查询id为100的记录
select * from emp where id=100
1.23.4 模糊查询记录
select * from emp where ename like ‘j%’ #以j开头的记录
select * from emp where ename like ‘%k’ #以k结束的记录
select * from emp where ename like ‘%a%’ #包含a的记录
select * from emp where ename not like ‘j%’ #不 以j开头的记录
1.23.5 查询之间范围之间的所有记录
select * from emp where sal between 8000 and 20000 #[8000,20000]
select * from emp where sal>8000 and sal<20000 #(8000,20000)
1.23.6 查询满足两个条件的记录
SELECT * from user where age=19 or age=20 #或者关系
SELECT * from user where age in (19,20)
SELECT * from user where age=20 and name=‘xiongda’ #并且关系
1.23.7 查询用户住址
SELECT distinct addr from user
1.23.8 查询19岁人的名字
SELECT distinct name from user where age=19
1.23.9 按age升序查询记录
SELECT * from user order by age asc #升序,默认
SELECT * from user order by age desc #降序
1.23.10 以name升序、age降序查询记录
SELECT * from user order by name asc,age desc #name升序,age降序
1.23.11 查询总人数
SELECT count(*) from user
SELECT count(1) from user
SELECT count(id) from user
1.23.12 查询各个城市的人数
select addr,count(addr) from user group by addr #聚合函数以外的列要分组
1.23.13 查询至少有2人的地址
SELECT addr,count(name) from user GROUP BY addr
SELECT addr,count(name) X from user GROUP BY addr having X>2 #条件过滤
1.23.14 查询记录中最年长和最年轻
select max(age),min(age) from user
1.23.15 查询大于平均年龄的记录
select * from user where age > (select avg(age) from user)
1.23.16 查询年龄最大的用户信息
select * from user where age = (select max(age) from user)
1.23.17 查询各部门的最高薪
select id,name,sal,max(sal) from emp GROUP BY deptno
1.23.18 查询各科的平均工资
select avg(comm) from emp
select ROUND(avg(comm),1) from emp #保留一位小数
SELECT * from emp where comm > (select avg(comm) from emp)
1.23.19 查询id是100或200的记录
select * from emp where id=100
select * from emp where id=200
select * from emp where id=100 or id=200
select * from emp where id in(100,200)
select * from emp where id=200
#UNION #合并重复内容
union all #不合并重复内容
select * from emp where id=200
1.23.20 查询存在部门的员工信息
select * from emp where deptno in (select id from dept)
1.23.21 查询没划分部门的员工信息
select * from emp where deptno not in(select id from dept)
1.23.22 查询同名的员工记录
select * from emp WHERE ename in (
select ename from emp GROUP BY ename HAVING count(ename)>1
)
1.23.23 全部学生按出生年月排行
select * from students order by sbirthday #数值从小到大，年龄就是大到小了
1.23.24 每个班上最小年龄的学员
select sname,class,max(sbirthday) from students group by class #数字最大，年龄是最小的
1.23.25 查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) age from students
1.23.26 查询男教师及其所上的课程
SELECT * from teachers a inner JOIN courses b on a.tno=b.tno AND a.tsex=‘男’
SELECT * from teachers a,courses b where a.tno=b.tno AND a.tsex=‘男’
1.23.27 查询每个老师教的课程
SELECT c.cname,t.tname,t.prof,t.depart
FROM teachers t
LEFT JOIN courses c ON t.tno = c.tno
1.23.28 查询女老师的信息
SELECT *
FROM teachers t
LEFT JOIN courses c ON t.tno = c.tno
where t.tsex=‘女’
第一种先连接数据后过滤数据，假如数据量很大，第一种中间过程要构建巨大的临时表。而第二种方式先过滤数据，构建的中间结果集自然就变的很小。所占内存，所加工的时间所网络传输的时间都变少了，所以效率高。
1.23.29 查询得分前3名的学员信息
select * from scores order by degree desc limit 3 #前三条
select * from scores order by degree desc limit 1,3
#从1位置(第二条)开始,总共取3条
1.23.30 查询课程是“计算机导论”的，得分前3名的学员信息
select * from scores where cno = (select cno from courses where cname=‘计算机导论’)
order by degree desc limit 3
1.23.31 课程号“3-105”的倒数最后3名学员排行
select * from scores where cno=‘3-105’ order by degree limit 3
1.24 SQL的执行顺序
1.24.1 SQL语句
(8) SELECT (9) DISTINCT column,… 选择字段、去重
(6) AGG_FUNC(column or expression),… 聚合
(1) FROM [left_table] 选择表
(3) <join_type> JOIN <right_table> 链接
(2) ON <join_condition> 链接条件
(4) WHERE <where_condition> 条件过滤
(5) GROUP BY <group_by_list> 分组
(7) HAVING <having_condition> 分组过滤
(9) ORDER BY <order_by_list> 排序
(10) LIMIT count OFFSET count; 分页
1.25 考试系统oracle版
1.25.1 PD创建Oracle模型
1.25.2 表设计
1.26 Oracle数据库
1.26.1 安装
一定要“以管理员身份运行”，Oracle是系统级别的，要安装服务，要求权限大。一般安装时把防火墙关闭，否则很容易安装失败。
1.26.2 Oracle10g EX
安装OracleXEUniv简版.exe，会自动创建EX服务。如果安装安装版本，需要手动创建本地服务。
安装完成后，侦听Listener和ServiceXE自动启动
安装时，最好不要安装到c盘，选择d盘。安装时需要输入system的密码，这个一定要记住，oracle的密码是无法破解的。忘记了只能重新安装。切记。
注意：
1）默认oracle会开启8080网页服务，它跟tomcat默认8080端口冲突。可以关闭oracle的8080，或者换tomcat的默认端口。
2）oracle安装完成后，不能使用sql-plus测试是否安装成功，因为它权限非常大。可以直接和oracle通讯。可以使用plsql工具，如果可以连接，则写的程序才能正常访问。
3）如果安装失败，先停掉侦听服务，再停掉Service服务，然后运行安装包，进行卸载。
1.26.3 配置本地服务
访问远程Oracle服务端。
远程访问前，在服务器上安装Oracle服务器端，客户端安装Oracle客户端，使用PL/SQL通过Oracle客户端访问Oracle服务端。
修改tnsnames.ora文件配置远程访问的地址：
D:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
XEremote =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) )
)
1.26.4 链接错误
长时间没有访问，oracle会自动断开连接，遇到这样的情况，重新登录即可。
1.27 创建数据库
1.27.1 注意事项
l oracle的数据库概念和mysql不同，它是创建一个账号，将所有表等都放在这个账号下。所以在oracle中的账户等同于mysql中的数据库。
l 不要将system给用户直接使用，权限太大，造成安全隐患，数据泄露，甚至删除别人的账号（数据库）。切不可给非法分子留下删库跑路的机会。所以通常给每个业务单独创建账号，单独分配权限。
1.27.2 密码安全
System账号不能修改，它是超级管理员，密码必须安全，否则非常危险，那实际开发中企业怎么设置的呢？
14yHl9t-hjCMT 疑似银河落九天-回家吃馒头
1.27.3 创建用户
使用新账号ht重新登录：
1.28 PL/SQL客户端操作
1.28.1 常见错误
如果太久未访问oracle，oracle会自动关闭链接，如上午访问，中午一直未用，下午再执行SQL就报下面错误。关闭，重新打开即可。
1.28.2 选择自己的内容
1.28.3 常用对象
1.28.4 SQL窗口
输入SQL语句执行，可以输入多条SQL，通过选中的内容进行执行，不选中执行所有。
1.29 准备数据
1.30 SQL差异
1.30.1 概念
SQL是有国际标准，如著名的SQL92
下面是SQL发展的简要历史：
1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86
1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89
1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）
1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）
2003年，ISO/IEC 9075:2003，SQL:2003
2008年，ISO/IEC 9075:2008，SQL:2008
2011年，ISO/IEC 9075:2011，SQL:2011
从SQL:1999开始，标准简称中的短横线（-）被换成了冒号（:），而且标准制定的年份也改用四位数字了。前一个修改的原因是ISO标准习惯上采用冒号，ANSI标准则一直采用短横线。
标准是用来打破的，所以注意各数据库厂商虽然遵循了规范标准，但也自己实现了个性的内容，一些函数，存储过程等。
1.30.2 日期
Oracle默认日期格式为：dd-mon-yy 日月年。09-6月-99日期为1999年6月9日
–日期数据格式,oracle默认是:日-月-年,也可以改
insert into stu values(‘王五’,‘1’,‘10-8月-1990’,‘php’,2)
–修改日期格式成:年-月-日
alter session set nls_date_format=‘yyyy-mm-dd’;
–以前的格式就错了,必须新格式才行
insert into stu values(‘王8’,‘1’,‘1999-8-10’,‘ios’,5)
1.30.3 性别
注意性别输入汉字，在不同的oracle版本会稍有差异，我们使用的oracle 10g简版中汉字为unicode码，占3个字节。所以sex char(2)是无法存储的，修改为char(3)就可以。
1.30.4 decode()
decode函数语法：decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
要求：显示性别，0代表女，1代表男
–如果ssex的值时0就返回男,1就返回女
select sname,ssex,decode(ssex,0,‘男’,1,‘女’) sex from stu
1.30.5 casewhen
–case…when
select sname,ssex,
case ssex when ‘0’ then ‘女’ else ‘男’ end sex
from stu
1.30.6 第一条
select top 1 * from stu --sqlServer数据库
select * from stu limit 1 --mysql数据库
select * from stu where rownum<=2 --oracle数据库
1.31 独特技术
1.31.1 过气技术
下面的技术在历史长河中已经被淘汰，但老项目，小项目依然再用，大家了解下即可。这些技术很多阿里的开发手册中已经禁止使用。
1.31.2 虚表 dual
dual是一个虚拟表，mysql没有哦，用来构成select的语法规则，oracle保证dual里面永远只有一条记录，用它可以做很多事情。
将结果存储在dual表中：
select 1 from dual #虚拟出一个数字列
select ‘abc’ from dual #虚拟出一个字符串列
select sysdate from dual #获取系统当前日期
select sys_guid() from dual #获取uuid
1.31.3 序列 sequence
Oracle自身提供的自增主键支持，和mysql的实现思路完全不同
–第一次访问先执行nextval
select STU_ID_SEQ.Nextval from dual
–获取当前值(必须执行过nextval)
select STU_ID_SEQ.CURRVAL from dual
–给id赋值,序列会自动+1
insert into stu(sid) values(STU_ID_SEQ.nextval)
1.31.4 授权视图
使用system登录，修改ht的权限为dba，否则无法创建视图。
或者
使用system账户执行，分配视图权限。
grant create any view to ht;
1.31.5 视图 view
视图本质就是一个查询，和我们自己查询的区别是，它执行完会有缓存，下次查询就直接使用。但其也因为事先缓存，无法做优化，大型项目中禁止使用。
注意:视图只需创建一次，后面就可以类似表来使用，只是用来查询不能更新和删除
–创建视图 ,本质上就是缓存一个查询结果
create or replace view STU_V as
select * from stu where ssex=0
–查询表结构
select * from stu where ssex=0
–直接查询视图,高效,但是无法优化
select * from STU_V
可以看到两者查询结果没有分别
1.31.6 触发器 trigger
记录生效点：BEFORE/AFTER
记录的操作：INSERT/UPDATE/DELETE
需求:当修改sex值时触发逻辑。
–设置触发器 —禁用,因为无法控制程序员的权限,可能有安全隐患
create or replace trigger UPDATE_STU_TRI
before update on stu
for each row
declare
– local variables here
begin
–如果 SEX>0,统一设置成1
IF :NEW.SSEX>5 THEN
:NEW.SSEX := 1;
END IF;
end UPDATE_STU_TRI;
–使用触发器:
SELECT * FROM STU
update stu set ssex=10;–触发器执行,都是1了
update stu set ssex=3;–不满足触发器,就是3
1.31.7 存储过程 procedure
概念：
存储过程（Stored Procedure）其实就是数据库端的编程，在数据库为王的时代，虽然已经过去，当时数据库大集中，部署在超级好的服务器，甚至是小型机，所以执行的性能超群，运行在上面的程序自然也就性能极佳。但当分布式架构兴起后，数据库在整个架构中的作用一再衰落，逐步边缘化。再者存储过程中的SQL是依赖数据库厂商，每个厂商都有其个性的SQL，导致程序迁移数据库时，如从oracle迁移到mysql时基本重写，工作量巨大，造成修改的风险。于是主流方式已经将其从数据库端前置到java程序端实现，这样迁移数据库变得轻松。
案例：
大家浏览下就好，难不？这样的东西非常难维护和调试，这就是被禁止的原因。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_TMS_AUTO_BILLING] (
@V_TMS_ORDER_ID VARCHAR (32), --订单主ID
@V_RETUREN_VALUE VARCHAR (20) output --状态返回
)
AS
/***********************************************************************
** Stored Procedure Documentation
** Stored Procedure Name: SP_TMS_AUTO_BILLING
** Creation Date: 2010-3-1
** Author:
** Program ID:
** Input Parameters:
** Output Parameters:
** Return Status Values:
** Usage:
** Local Variables:
** Called By:
** Calls:
** Data Modifications:
** Updates:
************************************************************************/
BEGIN
DECLARE @V_OPERATION_ITEM VARCHAR (30) ----订单上的费用协议操作项目
DECLARE @V_SERVICE_TYPE VARCHAR (50) ----订单上的服务类型
DECLARE @V_CUSTOMER_CONTRACT VARCHAR(30) ----订单上的费用协议号
DECLARE @V_SERVICE_LEVEL VARCHAR(30) ----订单上的服务时限
DECLARE @V_CHARGE_UNIT VARCHAR(20) ----订单上的计件单位
DECLARE @V_CARGO_TYPE VARCHAR(50) ----订单上的货物类型
DECLARE @V_ESTIMATED_CARGO_PACKAGES NUMERIC ---订单上的货物件数
DECLARE @V_ESTIMATED_CARGO_WEIGHT NUMERIC ---订单上的货物重量
DECLARE @V_ESTIMATED_CARGO_CUBE NUMERIC ---订单上的货物体积
DECLARE @V_BILLING_OFFICE VARCHAR(50) ---订单上的结算公司
DECLARE @V_HOME_CURRENCY VARCHAR(3) ---结算公司本位币
DECLARE @V_CHARGE_CODE VARCHAR(20) ---客户计费协议费用项目中的费用代码
DECLARE @V_CHARGE_ITEM_NAME VARCHAR(50) ---客户计费协议费用项目中的费用名称
DECLARE @V_RATES_NUMBER VARCHAR(20) ---客户计费协议费用项目中的费率号
DECLARE @V_FRT_NAME_EN VARCHAR(50) ---费用英文名称
DECLARE @V_CUST_OPERATION_ITEM_ID VARCHAR(50) ----操作项的ID
DECLARE @V_CUST_CONTRACT_ID VARCHAR(50) ---计费协议ID
DECLARE @V_CRM_CUST_ID VARCHAR(50) ---客户ID
DECLARE @V_CHARGE_QUANTITY NUMERIC ----结算总量
DECLARE @V_UNIT_PRICE NUMERIC ----费用单价
DECLARE @V_CHARGE_CURRENCY VARCHAR(20) ----费用币别
DECLARE @V_CUST_CODE VARCHAR(50) ----客户代码
DECLARE @V_EXCHANGE_RATE NUMERIC ----汇率
DECLARE @V_CUST_INNER_OUTER VARCHAR(50) ----对内或者对外
DECLARE @V_MIN_RATES NUMERIC ----计费协议里面的最低起运价
DECLARE @V_AMOUNT NUMERIC ----费用表里面的amount
---取去订单下需要的数据
SELECT @V_SERVICE_TYPE = SERVICE_TYPE, @V_CUSTOMER_CONTRACT = CUSTOMER_CONTRACT,
@V_SERVICE_LEVEL = SERVICE_LEVEL, @V_CHARGE_UNIT = CHARGE_UNIT,
@V_CARGO_TYPE = CARGO_TYPE, @V_ESTIMATED_CARGO_PACKAGES =ESTIMATED_CARGO_PACKAGES,
@V_ESTIMATED_CARGO_WEIGHT = ESTIMATED_CARGO_WEIGHT, @V_ESTIMATED_CARGO_CUBE = ESTIMATED_CARGO_CUBE,
@V_BILLING_OFFICE = BILLING_OFFICE, @V_HOME_CURRENCY = HOME_CURRENCY
from TMS_ORDER
LEFT JOIN SYS_OFFICE ON BILLING_OFFICE = OFFICE_CODE
where TMS_ORDER_ID = @V_TMS_ORDER_ID
---判断费用单位对应的结算总量
IF(@V_CHARGE_UNIT = '件数')
set @V_CHARGE_QUANTITY = @V_ESTIMATED_CARGO_PACKAGES
ELSE IF(@V_CHARGE_UNIT = '体积')
SET @V_CHARGE_QUANTITY = @V_ESTIMATED_CARGO_CUBE
ELSE
SET @V_CHARGE_QUANTITY = @V_ESTIMATED_CARGO_WEIGHT
----将符合的费用查找出来
DECLARE
V_CHARGE_CURSOR CURSOR FOR
SELECT CRM.CHARGE_CODE, CRM.CHARGE_ITEM_NAME, SFD.FRT_NAME_EN,
CRM.RATES_NUMBER,
CUOI.CUST_OPERATION_ITEM_ID, CUOI.CUST_CONTRACT_ID,
CC.CUST_CODE, CCC.CRM_CUST_ID, CC.CUST_INNER_OUTER
FROM CRM_CUST_CHARGE_ITEM CRM
LEFT JOIN CRM_CUST_OPERATION_ITEM CUOI ON CRM.CUST_OPERATION_ITEM_ID = CUOI.CUST_OPERATION_ITEM_ID
LEFT JOIN CRM_CUST_CONTRACT CCC ON CUOI.CUST_CONTRACT_ID = CCC.CUST_CONTRACT_ID
LEFT JOIN CRM_CUST CC ON CC.CRM_CUST_ID = CCC.CRM_CUST_ID
LEFT JOIN SB_FRT_DEF SFD ON SFD.FRT_CODE = CRM.CHARGE_CODE
LEFT JOIN TMS_OPERATION_ITEM TOI ON CUOI.OPERATION_ITEM=TOI.OPERATION_ITEM
WHERE CCC.CONTRACT_NUMBER = @V_CUSTOMER_CONTRACT
AND CUOI.SERVICE_TYPE = @V_SERVICE_TYPE
AND CUOI.SERVICE_PERIOD = @V_SERVICE_LEVEL
AND CUOI.CARGO_TYPE = @V_CARGO_TYPE
AND TOI.TMS_ORDER_ID = @V_TMS_ORDER_ID
/**
AND EXISTS (SELECT 1 FROM TMS_OPERATION_ITEM TOI
WHERE CUOI.OPERATION_ITEM = TOI.OPERATION_ITEM
AND TMS_ORDER_ID = @V_TMS_ORDER_ID
)
**/
OPEN V_CHARGE_CURSOR
FETCH NEXT FROM V_CHARGE_CURSOR
INTO @V_CHARGE_CODE, @V_CHARGE_ITEM_NAME, @V_FRT_NAME_EN, @V_RATES_NUMBER,
@V_CUST_OPERATION_ITEM_ID, @V_CUST_CONTRACT_ID, @V_CUST_CODE, @V_CRM_CUST_ID,
@V_CUST_INNER_OUTER
IF(@@FETCH_STATUS!=0)
set @V_RETUREN_VALUE = '没有匹配操作项'
WHILE(@@FETCH_STATUS = 0)
BEGIN
set @V_RETUREN_VALUE = '自动计费完成'
---取出费率表中符合条件的记录
SELECT @V_UNIT_PRICE = UNIT_PRICE, @V_CHARGE_CURRENCY = CHARGE_CURRENCY, @V_MIN_RATES = MIN_RATES
FROM CRM_CUST_CHARGE_RATE
WHERE CHARGE_UNIT = @V_CHARGE_UNIT
AND CUST_CONTRACT_ID = @V_CUST_CONTRACT_ID
AND RATES_NUMBER = @V_RATES_NUMBER
AND CHARGE_UNIT_FROM < @V_CHARGE_QUANTITY
AND CHARGE_UNIT_TO >= @V_CHARGE_QUANTITY
---看是否有对应的协议汇率维护
IF(@V_UNIT_PRICE IS NULL)
PRINT 'NO DATA'
ELSE
BEGIN
----查询汇率
SELECT @V_EXCHANGE_RATE = EXCHANGE_RATE
from CRM_CUST_EXCHANGERATE
WHERE CRM_CUST_ID = @V_CRM_CUST_ID
AND LOCAL_CURRENCY_CODE = @V_HOME_CURRENCY
AND FOREIGN_CURRENCY_CODE = @V_CHARGE_CURRENCY
---如果不存在取系统中的汇率
IF(@V_EXCHANGE_RATE IS NULL)
BEGIN
IF(@V_CUST_INNER_OUTER = 'INTERIOR') ----判断是对内的还是对外的汇率
SELECT @V_EXCHANGE_RATE = RATE_IN
FROM SB_RATE
WHERE STANDARD_CUR_CODE = @V_HOME_CURRENCY
AND ORIGINAL_CUR_CODE = @V_CHARGE_CURRENCY
ELSE
SELECT @V_EXCHANGE_RATE = RATE
FROM SB_RATE
WHERE STANDARD_CUR_CODE = @V_HOME_CURRENCY
AND ORIGINAL_CUR_CODE = @V_CHARGE_CURRENCY
END
IF(@V_EXCHANGE_RATE IS NULL)
BEGIN
PRINT @V_HOME_CURRENCY
print @V_CHARGE_CURRENCY
PRINT 'SYS NOT EXCHANGE RATE'
RETURN 1
END
print @V_EXCHANGE_RATE
----判断费用amount是否比最低起运价低，如果低，就取最低起运价，否则却当前值
SET @V_AMOUNT = @V_UNIT_PRICE*@V_CHARGE_QUANTITY
IF(@V_AMOUNT IS NOT NULL AND @V_MIN_RATES IS NOT NULL)
BEGIN
IF(@V_AMOUNT<@V_MIN_RATES)
SET @V_AMOUNT = @V_MIN_RATES
END
----插入费用表
INSERT INTO TMS_FREIGHT(TMS_FREIGHT_ID,
TMS_ORDER_ID,
FRT_CODE,
FRT_NAME,
FRT_NAME_CN,
RP_IND,
UNIT_PRICE,
CHARGE_QUANTITY,
CHARGE_UNIT,
BILLING_STATION,
CUST_CODE,
CURRENCY,
EXCHANGE_RATE,
AMOUNT,
LOCAL_CURRENCY,
LOCAL_CURRENCY_AMOUNT,
IS_SHARE,
NEED_SHARE,
IS_AUTO,
IS_SETTLE,
IS_REVICED,
RECORD_VERSION
)
SELECT NEWID(),
@V_TMS_ORDER_ID,
@V_CHARGE_CODE,
@V_FRT_NAME_EN,
@V_CHARGE_ITEM_NAME,
'0',
@V_UNIT_PRICE,
@V_CHARGE_QUANTITY,
@V_CHARGE_UNIT,
@V_BILLING_OFFICE,
@V_CUST_CODE,
@V_CHARGE_CURRENCY,
@V_EXCHANGE_RATE,
@V_AMOUNT,
@V_HOME_CURRENCY,
@V_AMOUNT*@V_EXCHANGE_RATE,
0,
0,
1,
0,
0,
1
END
-----插入表结束
FETCH NEXT FROM V_CHARGE_CURSOR
INTO @V_CHARGE_CODE, @V_CHARGE_ITEM_NAME, @V_FRT_NAME_EN, @V_RATES_NUMBER,
@V_CUST_OPERATION_ITEM_ID, @V_CUST_CONTRACT_ID, @V_CUST_CODE, @V_CRM_CUST_ID,
@V_CUST_INNER_OUTER
END
CLOSE V_CHARGE_CURSOR
DEALLOCATE V_CHARGE_CURSOR
END
1.32 分页
1.32.1 rownum伪列
rownum它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
利用这个伪列和两次子查询实现分页查询，如果有排序必须在线排序在分页
1.32.2 分页SQL
SELECT * FROM
(
SELECT t.*,ROWNUM r FROM TABLE t
WHERE ROWNUM <= pageNumber*pageSize
)
WHERE r > (pageNumber-1)*pageSize
–每页3条记录，第1页
select * from
(select t.*,rownum r from dept_p t where rownum <=3)
where r>0
–每页3条记录，第2页
select * from
(select t.*,rownum r from dept_p t where rownum <=6)
where r>3
Oracle实现分页非常变态，需要嵌套查询才能筛选出来，合理吗？而MySQL多么的轻盈。Oracle 8时语法这么写，现在都Oracle 20c了，还没有变化。Oracle为什么会被击倒，可见一斑。
1.33 存储过程实现分页
1.33.1 创建包同时创建游标
概念：
游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行读写。
案例：
create or replace package pagingPackage is
type paging_cursor is ref cursor;
end;
/
1.33.2 创建分页存储过程
l In输入参数
l Out返回值
create or replace procedure paging_cursor
(
v_in_table in varchar2, v_in_pagesize in number, v_in_pagenow in number, v_out_result out pagingPackage.paging_cursor, v_out_rows out number, v_out_pagecount out number
) is
--定义需要的变量 v_sql varchar2(4000); v_sql_select varchar2(4000); v_start number; v_end number;
begin
--计算v_start和v_end是多少
v_start:=v_in_pagesize*(v_in_pagenow-1)+1;
v_end:=v_in_pagesize*v_in_pagenow;
v_sql:='select t2.* from (select t1.*,rownum rn from (select * from '||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
--打开游标，让游标指向结果集
open v_out_result for v_sql;
--查询共有多少条记录
v_sql_select:='select count(*) from '||v_in_table;
execute immediate v_sql_select into v_out_rows;
--统计多少页记录
if mod(v_out_rows,v_in_pagesize)=0 then
v_out_pagecount:=v_out_rows/v_in_pagesize;
else
v_out_pagecount:=v_out_rows/v_in_pagesize+1;
end if;
end;
/
1.33.3 测试
1.33.4 预习TestJDBC.java
package test;
import org.junit.Test;
import java.sql.*;
public class TestJDBC {
@Test //基本查询语句
public void statement() throws Exception {
//驱动
String driver = "com.mysql.jdbc.Driver";
//数据库链接，localhost，ip：127.0.0.1本机
String url = "jdbc:mysql://localhost:3307/jtdb2007";
String sql = "select * from dept";
String username = "root";
String password = "root";
//访问Oracle
driver = "oracle.jdbc.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:XE";
sql = "select * from dept_p";
username = "ht";
password = "ht";
Class.forName(driver);
//得到链接对象
Connection cn = DriverManager.getConnection(url,
username,password);
//得到语句对象，它来执行sql语句
Statement stat = cn.createStatement(); //创建语句对象
ResultSet rs = stat.executeQuery(sql); //执行查询语句
//打印数据库表元数据，列名，dept字段数
int cols = rs.getMetaData().getColumnCount();
//数据库返回对象，起始值1，不是0
for(int i=1; i<=cols; i++){
System.out.print(rs.getMetaData().getColumnName(i)
+"\t"); //\转义字符，tab键
}
System.out.println(); //换行
//打印表中数据
while(rs.next()){ //每次调用会返回下一条记录，直到遍历结束为止
for(int i=1; i<=cols; i++){
//rs.getString(i); //获取第几列值，返回字符串类型
System.out.print( rs.getString(i) +"\t");
}
System.out.println(); //每一条数据打印完成加换行
}
}
@Test //预编译查询语句
public void prepareStatement() throws Exception {
String driver = "com.mysql.jdbc.Driver"; //驱动
String url = "jdbc:mysql://localhost:3307/jtdb2007"; //链接
//预编译sql中用?作为占位符，将来会被真实值替换掉
//SELECT * FROM dept WHERE deptno ='1' AND dname = 'accounting'
String sql = "select * from dept where deptno = ? and dname = ?";
Class.forName(driver);
Connection cn = DriverManager.getConnection(url, //获取链接
"root","root");
//带参数
PreparedStatement ps = cn.prepareStatement(sql); //返回预编译对象
ps.setString(1,"1"); //?位置 1第一个问号，参数值
ps.setString(2,"accounting"); //2第二个问号，参数值
//查询
ResultSet rs = ps.executeQuery();
//打印列标题
int cols = rs.getMetaData().getColumnCount(); //列的总数
for(int i=1; i<=cols; i++){
//打印列的名称
System.out.print( rs.getMetaData().getColumnName(i)+"\t");
}
//打印数据
while(rs.next()){ //每次向下取一条，直到结尾
System.out.println(); //换行
for(int i=1; i<=cols; i++){
System.out.print( rs.getString(i)+"\t");
}
}
}
@Test //调用存储过程，访问分页存储过程，oracle
public void call() throws Exception{
String driver = "oracle.jdbc.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe"; //注意SID
String sql = "{call paging_cursor(?,?,?,?,?,?)}";
String user = "ht";
String password = "ht";
Class.forName(driver);
Connection cn = DriverManager.getConnection(url, user, password);
//调用存储过程 prepareCall
CallableStatement cs = cn.prepareCall(sql);
// 给in?赋值
cs.setString(1, "dept_p");// 传表名
cs.setInt(2, 3); // 传入pagesize，每页显示多少条记录
cs.setInt(3, 2); // 传入pagenow，显示第几页。
// 给out?注册
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
// 执行
cs.execute();
// 这里是关键所在，java没有接收结果集的get方法，所以只能用getObject来接收结果集，接收到后需要使用ResultSet强转才可以
ResultSet rs = (ResultSet) cs.getObject(4);
// 循环取出
while (rs.next()) { //直接取记录，下标i，列的名称获取对应值
System.out.println(
rs.getString("dept_id")
+ " " + rs.getString("dept_name"));
}
// 取出总记录数
int rowCount = cs.getInt(5);
// 取出总页数
int pageCount = cs.getInt(6);
System.out.println("共有记录：" + rowCount + "条! " + "共有记录：" + pageCount + "页!");
}
}
1.34 JDBC
1.34.1 概念
我们学习了数据库，数据库实现了数据的持久化，但我们最终要在程序里处理数据啊，那java代码中怎么去访问数据库读写数据呢？
这就要用到sun公司设定的一套数据库标准了，这套标准就是JDBC（Java Database Connectivity）。但它只是规范，不做具体实现。于是数据库厂商又根据JDBC标准，实现自家的驱动Driver。如：mysql驱动com.mysql.cj.jdbc.Driver，Oracle的驱动oracle.jdbc.OracleDriver。有了这套解决方案，java就可以访问数据库中的数据了。
public interface Connection extends Wrapper, AutoCloseable {}
public interface Statement extends Wrapper, AutoCloseable {}
public interface PreparedStatement extends Statement {}
public interface CallableStatement extends PreparedStatement {}
public interface ResultSet extends Wrapper, AutoCloseable {}
Java中提倡面向接口开发，而最经典的接口设计莫过于JDBC数据库接口。
Connection链接、Statement语句、PreparedStatement预处理语句、CallableStatement存储过程、ResultSet结果集。
调用方式有三种：Statement语句、PreparedStatement预处理语句、CallableStatement存储过程，推荐使用第二种PreparedStatement，防止SQL注入，其也是预编译性能高。
1.34.2 idea 创建项目导驱动包
l 创建stage2 Java工程
l 创建lib目录，拷贝驱动objbc6-11.1.0.7.0到lib目录下
l 项目引用这个外部jar包
1.34.3 Statement 语句
@Test //基本查询语句
public void statement() throws Exception {
//驱动
String driver = "com.mysql.jdbc.Driver";
//数据库链接，localhost，ip：127.0.0.1本机
String url = "jdbc:mysql://localhost:3307/jtdb2007";
String sql = "select * from dept";
String username = "root";
String password = "root";
Class.forName(driver);
//得到链接对象
Connection cn = DriverManager.getConnection(url,
username,password);
//得到语句对象，它来执行sql语句
Statement stat = cn.createStatement(); //创建语句对象
ResultSet rs = stat.executeQuery(sql); //执行查询语句
//打印数据库表元数据，列名，dept字段数
int cols = rs.getMetaData().getColumnCount();
//数据库返回对象，起始值1，不是0
for(int i=1; i<=cols; i++){
System.out.print(rs.getMetaData().getColumnName(i)
+"\t"); //\转义字符，tab键
}
System.out.println(); //换行
//打印表中数据
while(rs.next()){ //每次调用会返回下一条记录，直到遍历结束为止
for(int i=1; i<=cols; i++){
//rs.getString(i); //获取第几列值，返回字符串类型
System.out.print( rs.getString(i) +"\t");
}
System.out.println(); //每一条数据打印完成加换行
}
}
1.34.4 PreparedStatement 语句
@Test //预编译查询语句
public void prepareStatement() throws Exception {
String driver = "com.mysql.jdbc.Driver"; //驱动
String url = "jdbc:mysql://localhost:3307/jtdb2007"; //链接
//预编译sql中用?作为占位符，将来会被真实值替换掉
//SELECT * FROM dept WHERE deptno ='1' AND dname = 'accounting'
String sql = "select * from dept where deptno = ? and dname = ?";
Class.forName(driver);
Connection cn = DriverManager.getConnection(url, //获取链接
"root","root");
//带参数
PreparedStatement ps = cn.prepareStatement(sql); //返回预编译对象
ps.setString(1,"1"); //?位置 1第一个问号，参数值
ps.setString(2,"accounting"); //2第二个问号，参数值
//查询
ResultSet rs = ps.executeQuery();
//打印列标题
int cols = rs.getMetaData().getColumnCount(); //列的总数
for(int i=1; i<=cols; i++){
//打印列的名称
System.out.print( rs.getMetaData().getColumnName(i)+"\t");
}
//打印数据
while(rs.next()){ //每次向下取一条，直到结尾
System.out.println(); //换行
for(int i=1; i<=cols; i++){
System.out.print( rs.getString(i)+"\t");
}
}
}
1.35 Git版本控制
1.35.1 开发难题
在实际开发中我们会遇到一些问题，电脑蓝屏，代码丢了，懊悔不？磁盘坏了，文件没了，懊悔不？时间长了，文件找不到了，懊悔不？手欠，之前代码运行好好的，非要去优化下，结果还不如以前，信心满满，之前代码没保存，懊悔不？怎么解决呢？版本控制就可以解决这些难题了。
版本控制（Version Control System），它功能强大，不仅能备份你的文件，还可以实现很多：
l 记录文件的所有历史变化
l 错误恢复到某个历史版本
l 多人协作开发编辑同一个文件
l 实现功能差异版本（分支），如：mysql、mariadb、percona三个分支
1.35.2 主流的版本控制产品
名称
模型
并发
模式
历史
模式
变更
范围
网络协议
原子
提交性
CVS
Client
-server
Merge
Changeset
File
Pserver,ssh
No
SVN
Client
-server
3-way merge, recursive merge, octopus merge
Changeset and Snapshot
Tree
custom (svn), custom (svn) over ssh, HTTP and SSL (usingWebDAV)
Yes
Git
Distri
buted
Merge
or lock
Snapshot
Tree
custom, custom over ssh, rsync, HTTP/HTTPS, email, bundles
Yes
简而言之，各有优缺点，git要配合github远程仓库，可以避免分布式损坏。显然最大的不同在于git是分布式的。svn有权限控制，避免全被clone克隆走。git适合纯代码，svn适合综合性文档管理，git+svn结合起来，完美。
1.35.3 介绍
说明: Git logo
Linus在1991年创建了开源的Linux，从此，Linux系统不断发展，已经成为最大的服务器系统软件了。Linus虽然创建了Linux的核心，但Linux的壮大是靠全世界热心的志愿者参与的，这么多人在世界各地为Linux编写代码，那Linux的代码是如何管理的呢？
事实是，在2002年以前，世界各地的志愿者把源代码文件通过diff的方式发给Linus，然后由Linus本人通过手工方式合并代码！你也许会想，为什么Linus不把Linux代码放到版本控制系统里呢？不是有CVS、SVN这些免费的版本控制系统吗？因为Linus坚定地反对CVS和SVN，这些集中式的版本控制系统不但速度慢，而且必须联网才能使用。有一些商用的版本控制系统，虽然比CVS、SVN好用，但那是付费的，和Linux的开源精神不符。不过，到了2002年，Linux系统已经发展了十年了，代码库之大让Linus很难继续通过手工方式管理了，社区的弟兄们也对这种方式表达了强烈不满，于是Linus选择了一个商业的版本控制系统BitKeeper，BitKeeper的东家BitMover公司出于人道主义精神，授权Linux社区免费使用这个版本控制系统。
安定团结的大好局面在2005年就被打破了，原因是Linux社区牛人聚集，不免沾染了一些梁山好汉的江湖习气。开发Samba的Andrew试图破解BitKeeper的协议（这么干的其实也不只他一个），被BitMover公司发现了（监控工作做得不错！），于是BitMover公司怒了，要收回Linux社区的免费使用权。Linus可以向BitMover公司道个歉，保证以后严格管教弟兄们，嗯，这是不可能的。实际情况是这样的：Linus花了两周时间自己用C写了一个分布式版本控制系统，这就是Git！一个月之内，Linux系统的源码已经由Git管理了！牛是怎么定义的呢？吃瓜群众可以体会一下。
Git迅速成为最流行的分布式版本控制系统，尤其是2008年，GitHub网站上线了，它为开源项目免费提供Git存储，无数开源项目开始迁移至GitHub，包括jQuery，PHP，Ruby等等。历史就是这么偶然，如果不是当年BitMover公司威胁Linux社区，可能现在我们就没有免费而超级好用的Git了。
1.35.4 组成结构图
l 工作空间：用来保存项目的元数据和对象数据库的地方。 这是 Git 中最重要的部分，从其它计算机克隆仓库时，拷贝的就是这里的数据。
l 本地索引：保存了下次将提交的文件列表信息，一般在 Git 仓库目录中。有时候也被称作“索引”，不过一般说法还是叫暂存区域。
l 本地仓库，之所以说git 快，大部分提交都是对本地仓库而言的，不依赖网络，最后一次会推送的到远程仓库。
l 远程仓库：可以看做是github，它是一个远程仓库，它提供web服务供大家方便下载、查看、提交、存储。
1.35.5 命令速查
说明: https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1498324567731&di=38c12e2cec3790708b3a2e350fe80eed&imgtype=0&src=http%3A%2F%2Fwww.114390.com%2Fupload_article%2Ffile_images%2Farticle%2F201409%2Fgit_big_jb51.jpg
1.35.6 常用命令
l 克隆仓库（第一次时） clone 克隆
l 对工作目录中文件新增、修改、删除操作
l 暂存文件，将文件的快照放入暂存区 add 新增
l 把暂存区的内容提交到本地仓库 commit 提交
l 把本地仓库的内容提交到远程仓库 push 推送
l 从远程仓库下载最新内容 pull 拉取
1.36 码云配置环境
1.36.1 注册账号
每个人必须有自己的账号，先官网注册账号：
https://github.com
https://gitee.com/
1.36.2 登录
1.36.3 安装GIT
Git-2.27.0-64-bit，一路next，安装完桌面右键菜单有下面两项，安装完成。选择Git Bash，进入git客户端。
Windows开始菜单：
1.36.4 设置字体
1.36.5 查询git
1.36.6 配置身份信息
提交文件时，就知道这个文件是谁提交的。出了问题，就知道谁干的！
1.36.7 查看配置信息
1.37 码云创建仓库
1.37.1 创建远程仓库
1.37.2 创建本地仓库
mkdir j2ee #创建本地仓库目录和远程仓库目录一致
cd j2ee #进入目录
git init #初始化
touch README.md #创建文件
git add README.md #新增文件到git中处理
git commit -m “first commit” #提交，设置说明信息
git remote add origin https://gitee.com/nutony/j2ee.git #远程仓库
第一次执行会弹出下面的用户名、密码框，填写码云的账号信息就可以
说明: C:\Users\tony\Documents\Tencent Files\1920952078\Image\C2C\UUM5CGYWZ(@LE2WK3L9D3BF.png
刷新页面，如果创建成功，界面发生变化如下样子，这样环境就完成了
1.38 每日任务
每日早上开始工作前拉取最新团队其他人提交的内容，每日下班前提交可用的内容
1.38.1 新文件
创建项目目录，创建新文件，或者复制已有文件或者复制已有目录：
1.38.2 推送
把本地文件上传到远程仓库中：
$ git add . #添加当前目录下文件
$ git commit -m “j2ee part” #提交，设置备注
$ git push -u origin master #推送到远程仓库
1.38.3 拉取
把远程仓库中的内容下载到本地：
$ cd cgb #进入到要管理的目录中
$ git pull #从服务器拉取最新内容
$ cat README.md #进行文件内容查看
1.39 常见错误
1.39.1 Authentication failed for
任何路径下输入都可以：
git config --system --unset credential.helper
然后提交操作时，会出现询问框，重新输入用户名提示
1.39.2 To the same ref
这是是因为readme.md 没有存在在本地git中
git pull --rebase origin master #
$ git push -u origin master #推送到远程仓库
–rebase用在合并代码的时候其作用就是在一个随机创建的分支上处理冲突，避免了直接污染原来的分区。
1.40 SQL表准备
1.40.1 mysql-db库
CREATE DATABASE /!32312 IF NOT EXISTS/mysql-db /*!40100 DEFAULT CHARACTER SET utf8 */;
USE mysql-db;
1.40.2 student表
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id varchar(4) NOT NULL,
NAME varchar(20) DEFAULT NULL,
sex char(2) DEFAULT NULL,
birthday date DEFAULT NULL,
salary decimal(7,2) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into student(id,NAME,sex,birthday,salary) values (‘1’,‘张慎政’,‘男’,‘2020-01-01’,‘10000.00’),(‘2’,‘刘沛霞’,‘女’,‘2020-01-02’,‘10000.00’),(‘3’,‘刘昱江’,‘男’,‘2020-01-03’,‘10000.00’),(‘4’,‘齐雷’,‘男’,‘2020-01-04’,‘20000.00’),(‘5’,‘王海涛’,‘男’,‘2020-01-05’,‘20000.00’),(‘6’,‘董长春’,‘男’,‘2020-01-06’,‘10000.00’),(‘7’,‘张久军’,‘男’,‘2020-01-07’,‘20000.00’),(‘8’,‘陈子枢’,‘男’,‘2020-10-11’,‘3000.00’);
1.40.3 tb_dept表
DROP TABLE IF EXISTS tb_dept;
CREATE TABLE tb_dept (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
parent_id int(11) DEFAULT NULL,
sort int(11) DEFAULT NULL,
note varchar(100) DEFAULT NULL,
created timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
updated timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into tb_dept(id,name,parent_id,sort,note,created,updated) values (1,‘集团’,0,1,‘集团总部’,‘2018-10-02 09:15:14’,‘2018-09-27 16:35:54’),(2,‘财务部’,1,2,‘财务管理’,‘2018-09-27 16:35:52’,‘2018-09-27 16:34:15’),(3,‘软件部’,1,3,‘开发软件、运维’,‘2018-09-27 16:35:54’,‘2018-09-27 16:34:51’);
1.40.4 tb_user表
DROP TABLE IF EXISTS tb_user;
CREATE TABLE tb_user (
id int(11) NOT NULL AUTO_INCREMENT,
dept_id int(11) DEFAULT NULL,
username varchar(50) DEFAULT NULL,
password varchar(100) DEFAULT NULL,
salt varchar(50) DEFAULT NULL,
email varchar(100) DEFAULT NULL,
mobile varchar(100) DEFAULT NULL,
valid tinyint(4) DEFAULT NULL,
created timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
updated timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into tb_user(id,dept_id,username,password,salt,email,mobile,valid,created,updated) values (1,1,‘陈集团’,‘123456’,NULL,‘tony@sina.com’,‘13572801415’,1,‘2018-09-30 09:32:18’,‘2018-09-30 09:32:18’),(2,3,‘牛软件’,‘567890’,NULL,‘niu@sina.com’,‘13208737172’,0,‘2018-10-02 09:23:19’,‘2018-09-20 09:32:18’);
1.41 基础SQL优化
1.41.1 查询SQL尽量不要使用select *，而是具体字段
反例：
SELECT * FROM student
正例：
SELECT id,NAME FROM student
理由：
l 字段多时，大表能达到100多个字段甚至达200多个字段
l 只取需要的字段，节省资源、减少网络开销
l select * 进行查询时，很可能不会用到索引，就会造成全表扫描
1.41.2 避免在where子句中使用or来连接条件
查询id为1或者薪水为3000的用户：
反例：
SELECT * FROM student WHERE id=1 OR salary=30000
正例：
使用union all
SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000
分开两条sql写
SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000
理由：
l 使用or可能会使索引失效，从而全表扫描
l 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的
1.41.3 使用varchar代替char
反例：
deptname char(100) DEFAULT NULL COMMENT ‘部门名称’
正例：
deptname varchar(100) DEFAULT NULL COMMENT ‘部门名称’
理由：
l varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
l char按声明大小存储，不足补空格
l 其次对于查询来说，在一个相对较小的字段内搜索，效率更高
1.41.4 尽量使用数值替代字符串类型
l 主键（id）：primary key优先使用数值类型int，tinyint
l 性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
l 支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
l 服务状态（state）：1-开启、2-暂停、3-停止
l 商品状态（state）：1-上架、2-下架、3-删除
1.41.5 查询尽量避免返回大量数据
如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。
1.41.6 使用explain分析你SQL执行计划
SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。
EXPLAIN
SELECT * FROM student WHERE id=1
返回结果：
1.41.7 是否使用了索引及其扫描类型
type：
l ALL 全表扫描，没有优化，最慢的方式
l index 索引全扫描
l range 索引范围扫描，常用语<，<=，>=，between等操作
l ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
l eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
l const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
l null MySQL不访问任何表或索引，直接返回结果
possible_keys：
l 显示可能应用在这张表中的索引
key：
l 真正使用的索引方式
1.41.8 创建name字段的索引
提高查询速度的最简单最佳的方式
ALTER TABLE student ADD INDEX index_name (NAME)
1.41.9 优化like语句
模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效
反例：
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE ‘%1’
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE ‘%1%’
正例：
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE ‘1%’
理由：
未使用索引：故意使用sex非索引字段
EXPLAIN
SELECT id,NAME FROM student WHERE NAME=1 OR sex=1
主键索引生效
EXPLAIN
SELECT id,NAME FROM student WHERE id=1
索引失效，type=ALL，全表扫描
EXPLAIN
SELECT id,NAME FROM student WHERE id LIKE ‘%1’
1.41.10 字符串怪现象
反例：
#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123
正例：
#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=‘123’
理由：
l 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较
1.41.11 索引不宜太多，一般5个以内
l 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
l 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
l 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
l insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
l 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要
1.41.12 索引不适合建在有大量重复数据的字段上
如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
1.41.13 where限定查询的数据
数据中假定就一个男的记录
反例：
SELECT id,NAME FROM student WHERE sex=‘男’
正例：
SELECT id,NAME FROM student WHERE id=1 AND sex=‘男’
理由：
l 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销
1.41.14 避免在索引列上使用内置函数
业务需求：查询最近七天内新生儿（用学生表替代下）
给birthday字段创建索引：
ALTER TABLE student ADD INDEX idx_birthday (birthday)
当前时间加7天：
SELECT NOW()
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)
反例：
EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
正例：
EXPLAIN
SELECT * FROM student
WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
理由：
l 使用索引列上内置函数索引失效：
l 索引有效：
1.41.15 避免在where中对字段进行表达式操作
反例：
EXPLAIN
SELECT * FROM student WHERE id+1-1=+1
正例：
EXPLAIN
SELECT * FROM student WHERE id=+1-1+1
EXPLAIN
SELECT * FROM student WHERE id=1
理由：
l SQL解析时，如果字段相关的是表达式就进行全表扫描
l 字段干净无表达式，索引生效
1.41.16 避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。
反例：
EXPLAIN
SELECT * FROM student WHERE salary!=3000
EXPLAIN
SELECT * FROM student WHERE salary<>3000
理由：
l 使用!=和<>很可能会让索引失效
1.41.17 去重distinct过滤字段要少
#索引失效
EXPLAIN
SELECT DISTINCT * FROM student
#索引生效
EXPLAIN
SELECT DISTINCT id,NAME FROM student
EXPLAIN
SELECT DISTINCT NAME FROM student
理由：
l 带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间
1.41.18 where中使用默认值代替null
环境准备：
#修改表，增加age字段，类型int，非空，默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
#修改表，增加age字段的索引，名称为idx_age
ALTER TABLE student ADD INDEX idx_age (age);
反例：
EXPLAIN
SELECT * FROM student WHERE age IS NOT NULL
正例：
EXPLAIN
SELECT * FROM student WHERE age>0
理由：
l 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关
l 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的
l 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点
1.42 高级SQL优化
1.42.1 批量插入性能提升
大量数据提交，上千，上万，批量性能非常快，mysql独有
多条提交：
INSERT INTO student (id,NAME) VALUES(4,‘齐雷’);
INSERT INTO student (id,NAME) VALUES(5,‘刘昱江’);
批量提交：
INSERT INTO student (id,NAME) VALUES(4,‘齐雷’),(5,‘刘昱江’);
理由：
l 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
l 数据量小体现不出来
1.42.2 批量删除优化
避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。
反例：
#一次删除10万或者100万+？
delete from student where id <100000;
#采用单一循环操作，效率低，时间漫长
for（User user:list）{
delete from student;
}
正例：
//分批进行删除，如每次500
for(){
delete student where id<500;
}
delete student where id>=500 and id<1000;
理由：
l 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作
1.42.3 伪删除设计
商品状态（state）：1-上架、2-下架、3-删除
理由：
l 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
l 同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
l 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
l 操作速度快，特别数据量很大情况下
1.42.4 提高group by语句的效率
可以在执行到该语句前，把不需要的记录过滤掉
反例：先分组，再过滤
select job，avg（salary） from employee
group by job
having job =‘president’ or job = ‘managent’;
正例：先过滤，后分组
select job，avg（salary） from employee
where job =‘president’ or job = ‘managent’
group by job;
1.42.5 复合索引最左特性
创建复合索引，也就是多个字段
ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
满足复合索引的左侧顺序，哪怕只是部分，复合索引生效
EXPLAIN
SELECT * FROM student WHERE NAME=‘陈子枢’
没有出现左边的字段，则不满足最左特性，索引失效
EXPLAIN
SELECT * FROM student WHERE salary=3000
复合索引全使用，按左侧顺序出现 name,salary，索引生效
EXPLAIN
SELECT * FROM student WHERE NAME=‘陈子枢’ AND salary=3000
虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化
EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME=‘陈子枢’
理由：
l 复合索引也称为联合索引
l 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
l 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的
1.42.6 排序字段创建索引
什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。
#使用*，包含了未索引的字段，导致索引失效
EXPLAIN
SELECT * FROM student ORDER BY NAME;
EXPLAIN
SELECT * FROM student ORDER BY NAME,salary
#name字段有索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME
#name和salary复合索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME,salary
EXPLAIN
SELECT id,NAME FROM student ORDER BY salary,NAME
#排序字段未创建索引，性能就慢
EXPLAIN
SELECT id,NAME FROM student ORDER BY sex
1.42.7 删除冗余和重复的索引
SHOW INDEX FROM student
#创建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)
#删除student表的index_name索引
DROP INDEX index_name ON student ;
#修改表结果，删除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;
#主键会自动创建索引，删除主键索引
ALTER TABLE student DROP PRIMARY KEY ;
1.42.8 不要有超过5个以上的表连接
l 关联的表个数越多，编译的时间和开销也就越大
l 每次关联内存中都生成一个临时表
l 应该把连接表拆开成较小的几个执行，可读性更高
l 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
l 阿里规范中，建议多表联查三张表以下
1.42.9 inner join 、left join、right join，优先使用inner join
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小
l inner join 内连接，只保留两张表中完全匹配的结果集
l left join会返回左表所有的行，即使在右表中没有匹配的记录
l right join会返回右表所有的行，即使在左表中没有匹配的记录
理由：
l 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
l 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优
1.42.10 in子查询的优化
日常开发实现业务需求可以有两种方式实现：
l 一种使用数据库SQL脚本实现
l 一种使用程序实现
如需求：查询所有部门的所有员工：
#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：
#先查询部门表
SELECT id FROM tb_dept
#再由部门dept_id，查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：
List<> resultSet;
for(int i=0;i<B.length;i++) {
for(int j=0;j<A.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。
理由：
l 数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿
1.42.11 尽量使用union all替代union
反例：
SELECT * FROM student
UNION
SELECT * FROM student
正例：
SELECT * FROM student
UNION ALL
SELECT * FROM student
理由：
l union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
l union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
l union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
-
Java中定时任务实现方式及源码剖析11-24
-
Java中定时任务实现方式及源码剖析11-24
-
鸿蒙原生开发手记：03-元服务开发全流程（开发元服务，只需要看这一篇文章）11-24
-
细说敏捷：敏捷四会之每日站会11-24
-
Springboot应用的多环境打包入门11-23
-
Springboot应用的生产发布入门教程11-23
-
Python编程入门指南11-23
-
Java创业入门：从零开始的编程之旅11-23
-
Java创业入门：新手必读的Java编程与创业指南11-23
-
Java对接阿里云智能语音服务入门详解11-23
-
Java对接阿里云智能语音服务入门教程11-23
-
JAVA对接阿里云智能语音服务入门教程11-23
-
Java副业入门：初学者的简单教程11-23
-
JAVA副业入门：初学者的实战指南11-23
-
JAVA项目部署入门：新手必读指南11-23
