mysql

Mysql:

数据库定义语言 DDL create、alter、drop、truncate

数据库操纵语言 DML insert、update、delete

事务处理语言 TPL commit、rollback、savepoint

数据查询语言DQL select

数据控制语言 DCL create创建 grant授予权限 revoke回收权限

创建数据库 

create database 数据库名 default character set utf8;

增加字段 alter table 表名 add 字段名 类型;

修改字段 alter table 表名 modify 字段名 新类型;

alter table 表名 change 字段名 新名字 新类型;

删除字段 alter table 表名 drop 字段;

drop删除彻底 truncate删除数据保留表结构

insert into 表名 values();

update 表名 set 字段=新值 where 条件;

delete from 表名 where 条件;

空值null

Ifnull(e1,e2)如果e1不存在取e2代替

去重distinct

Between 低值 and 高值

In满足一项

模糊查询 使用like %表示0到多个字符 _表示1个字符

肯定is null 否定 is not null

not in 不等于列表项中所有项 即同时满足

abs(数字):返回的数字的绝对值

rand():返回0-1随机数

sqrt(数字):返回数字的平方根

pow(x,y):返回x的y次方

mod(x,y):返回x除以y的余数

排序:对查询结果集进行排序(先有结果集再排序)

使用:order by 字段

规则:asc 升序(默认)   desc 降序

select ename,salary from emp_xu where salary is not null  order by salary desc;//降序

select deptno d,salary s

from emp_xu

where deptno is not null

order by d asc,s desc;//列别名

group by分组

having:对分组之后的数据再进行过滤

select deptno,avg(ifnull(salary,0)) avg_salary

from emp_xu

where deptno is not null

group by deptno

having avg(ifnull(salary,0))>5000;

select->from(表)->where(记录过滤)->group by(分组)->having(分组后过滤)->select(结果集)->order by(排序)

子查询：

一条SQL语句中嵌套select查询语句

非关联子查询:

嵌套的子查询是独立语句不依赖主查询

先执行嵌套子查询(独立SQL、不依赖、仅执行一次)->

返回查询结果作为条件->再执行主查询

select ename,salary

from emp_xu

where salary=(

select min(salary) from emp_xu

);

关联子查询:

嵌套的子查询不是独立语句依赖主查询(两者有关系)

select ename,deptno,salary

from emp_xu e where salary<(

select avg(ifnull(salary,0)) from emp_xu where deptno=e.deptno);//e.deptno表示动态数据，由主查询传递过来的(依赖主查询)

先执行主查询(传递依赖数据)->执行嵌套子查询(依赖数据 执行多次)->

返回查询结果->再执行主查询

部门表中部门号等于员工表中部门号

非关联:

select deptno,dname

from dept_xu

where deptno in(

select deptno from emp_xu

);

关联：

select deptno,dname from dept_xu d where exists( select 1 from emp_xu e where e.deptno=d.deptno);

//union 自动进行去重

select ename,salary from emp_xu where deptno=10

union

select ename,salary from emp_xu where salary>6000

order by salary desc;//排序放在最后

//union all 不去重

二、Mysql中分页查询

limit:限制查询记录数

语法:

select 字段  from 表 limit 数量;

或

select 字段 from 表 limit 开始,数量;

//计算公式

page:第几页

pageSize:每页的记录数

int beginIndex=(page-1)*pageSize;//每页开始下标·

select empno,ename from emp_xu limit beginIndex,pageSize;

内连接

语法:[]可选项

表1 [inner] join 表2 on 条件

查询的结果集来自emp_xu和dept_xu

select ename,d.deptno,dname

from emp_xu e join dept_xu d

on e.deptno=d.deptno;//d.deptno需要指明，两张表中都有deptno

表1 join 表2 on 条件

a.表1为驱动表，表2为匹配表

b.执行过程

遍历驱动表在匹配表中找匹配记录

匹配上的记录保留，匹配不上的记录丢掉

c.等值连接(条件中使用等号)中，驱动表和匹配表可以互换不会

外连接(严格区分哪个表是驱动表的)

语法:[]表示可选项

//左外连接 左边的表为驱动表

表1 left [outer] join 表2 on 条件

//右外连接 右边的表为驱动表

表1 right [outer] join 表2 on 条件

说明:

驱动表中的全部数据会出现在外连接的结果集中

如果驱动表在匹配表中找不到匹配记录，则匹配一行空行

外连接的结果集=内连接的结果集(匹配上的记录)+驱动表在匹配表

中匹配不上的记录(匹配不上的记录)

Primary key 主键约束

stu_xu_id_pk 主键约束的名字  表名_字段名_约束名

非空约束:not null 简称NN

唯一约束:unique 简称UK

外键约束:foreign key 简称FK

外键是用来实现参照完整性，外键约束将两个表紧密的结合在一起，

保证数据的完整性和一致性。

表添加外键之后，两个表就建立关系(主表\从表)。创建表或插入数据

或删除时，这些操作有先后顺序。

自动创建索引:主键约束和唯一约束

手动创建索引

create index 索引名 on 表(字段);

索引那些情况会失效:包含or、隐式类型转换、like通配符、联合索引、进行函数运算、is null，is not null join两个表字符编码不同

Mysql遇见死锁怎么解决？

1. 查看死锁日志2.找出死锁sql3.模拟案发4.分析死锁日志5.分析死锁结果

优化sql:1.加索引2.避免不必要数据3.批量进行4.优化sql结构5.分库分表(水平、垂直)

事务的隔离级:多个用户并发事务访问同一个数据库，一个事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离

Mysql事务四大特性:原子性(要不不执行要不全部被执行)。一致性(事务开始和结束数据不会被破坏)。隔离性(多个事务疺，事务之间相互隔离)。持久性()

数据库的三范式?1.强调的是列的原子性，每一列都是不可分割的原子数据项2.依赖于主关键字3.任何非主属性不依赖与其它非主属性

select  version();查看版本

==================================================================================================================================================

数据库第一天
一、数据库介绍
1)数据库定义
通俗:存放数据的仓库
简单:电子化的文件柜
定义:数据库(DataBase 简称DB)是一个软件产品，数据库按照数据结构
来组织、存储和管理数据的，建立在计算机存储设备上的仓库。

2)数据库特点
数据共享
数据一致性和可维护性
    安全性
    完整性控制
    并发控制
数据恢复

3)主流的数据库产品(了解)
关系型数据库
    Mysql:一个小型的关系型数据库管理系统，开发者为瑞典MysqlAB公司，
    属于Oracle旗下产品(收购)。
    特点:
    体积小、速度快、总体拥有成本低、开放源码等
    
    Oracle:著名的甲骨文公司(oracle)的数据产品，它是世界上第一个
    商品化的数据库管理系统。
    
    DB2:IBM公司的关系型数据库管理系统，有很多版本，运行在掌上
    产品到大型机不同的终端机器上。
    
    Sybase:美国Sybase公司的关系型数据库管理系统，较早采用
    客户端\服务器结构的数据库厂商，运行在Unix或Window NT平台上。
    
    Sql Server:微软产品，运行在Window NT平台上，支持客户端\服务器
    结构的关系型数据库，采用标准的SQL语言。
    
    Access:微软发布的关系型数据管理系统，把数据库引擎的图形用户
    界面和软件开发工具结合。

非关系型数据库
    Redis:缓存
    MongoDB:面向文档的开源数据

二、SQL语言
1)关系型数据库
描述两个元素之间的关联或对应关系，使用关系模型把数据
组织到二维表中。

举例:
学生信息管理系统
学生表:学号、姓名、年龄。。。
课程表:课程号、课程名。。。
成绩表:学号、课程号、成绩。。。

2)二维表 table
表是一个二维结构，由行和列组成。
横向为行(row)也叫记录，用来表示实体数据
纵向为列(column)也叫字段，用来表示实体属性

补充:
一个关系型数据库由多个数据表组成的，数据表是关系型数据的基本
存储结构。

3)SQL语言
结构化查询语言:Structured Query Language
专门用于跟数据库通讯的语言，用来存取数据、查询、更新等操作。

4)SQL特点
关系型数据库都是能够支持的，有细微不同。

5)SQL划分(重点)
数据定义语言:DDL(Data Definition Language)
负责数据库对象的定义
常用语句:[跟结构相关]
create:创建数据库对象(表、视图、索引。。)  创建表结构
alter:修改表的结构
drop:删除表结构
truncate:清空表数据保留表结构

数据操纵语言:DML(Data Manipulation Language)
实现对表中数据进行增加、修改、删除操作
常用语句:[跟表中数据相关]
insert:增加数据
update:更新数据
delete:删除数据

事务处理语言:TPL(Transaction Processing Language)
结合DML使用，用于对数据进行确认提交或取消操作
常用语句:
commit:确认提交
rollback:回滚取消
savepoint:保存点 用于回滚到指定的地方

数据查询语言:DQL(Data Query Language)  重点!!!
实现数据进行查询操作
常用语句:
select:查询数据

数据控制语言:DCL(Data Control Language)
实现权限的授予和回收
常用语句:[跟权限相关]
create user:创建用户
grant:授予权限
revoke:回收权限

数据库(DB)->关系型数据库(关系/表)->二维表->
SQL语言(实现与数据库通讯)->SQL划分

划分:
DDL(结构):create\alter\drop\truncate
DML(数据):insert\update\delete
TPL(事务):commit\rollback 结合DML使用
DQL(查询):select  重点!!!
DCL(权限):create user\grant\revoke

三、Mysql数据库介绍
开发的首选数据库
mysql是最流行的关系型数据库管理系统之一

mysql安装(参考安装指南)
mysql5.5低版本
1)自定义编码选择 utf-8
2)用户名:root  密码:1234

特点:
体积小(内存小)、速度快、成本低、开放源码、
支持多线程处理、支持大型数据库、为多种编程语言提供API、
多种数据库连接的途径

四、Mysql客户端操作(命令行->图形化)
1)打开客户端(命令行)
开始->mysql->命令行

2)登录 
密码:1234   默认用户名:root
mysql>光标 

服务没启动
计算机->右击->管理->服务->Mysql->查看是否已启用

3)查看数据库
show databases;//默认自带4个数据库

4)创建数据库 
create database 数据库名 default character set utf8;
//自己的库
create database jsd default character set utf8;

5)切换指定的数据库(重要!!!)
use 数据库名;
use jsd;

6)查看表
show tables;

7)创建表(结构)
语法:
create table 表名(
字段名 类型,
字段名 类型
);

查看结构
desc 表名;//常用
或者
show columns from 表名;

8)查看当前系统时间
//查询 select
//第一条查询语句(SQL语句) 
  select....from....;
  
//now()表示函数 获取当前时间
//dual表示特殊表  仅用于做测试的
select now() from dual;//'2021-08-20 10:22:02'
select 1+1 from dual;
或者
select 1+1;//mysql中可以省略dual

补充:
复制:先标记(选中)->回车键(复制)

五、常用数据类型
1)数值
int
double(m,d):m表示数字的总位数
            d表示小数位数(自动进行四舍五入处理)
            
举例:
salary double(7,2)
表示的最大salary:99999.99

2)字符
char(n):定长字符串
varchar(n):变长字符串(节省空间)
n表示"字符"的个数

字符类型必须加引号(单引号\双引号)
"张三丰"    '张三丰'

3)日期时间
date 日期
datetime  日期时间

六、表结构操作(DDL)
1)创建表结构
语法:
create table 表名(
字段名 类型及范围,
字段名 类型及范围
);

注意:
a.表名不能重复
b.关键字不能写错  create
c.最后一个字段后面不能加逗号

//创建部门表(dept_xu)
分析:
10 "研发部" "南京"
部门号:deptno
部门名称:dname
部门地址:location

create table dept_xu(
deptno int,
dname varchar(10),
location varchar(12)
);

//查看结构 
desc dept_xu;//常用
或 
show columns from dept_xu;

2)修改表结构
增加字段
语法:
alter table 表名 add 字段名 类型;

//部门表中增加'描述'字段  des->varchar(20)
alter table dept_xu add des varchar(20);

修改字段
语法:
alter table 表名 modify 字段名 新类型;
alter table 表名 change 字段名 新名字 新类型;

//部门表中修改'描述'字段
alter table dept_xu modify des char(10);
alter table dept_xu change des abc varchar(20);

删除字段
语法:
alter table 表名 drop 字段;

//部门表中删除"描述"字段
alter table dept_xu drop abc;

3)删除表
drop table dept_xu;//删除结构  彻底删除  常用  删除表对象
truncate table dept_xu;//清空表数据保留表结构

总结:DDL
create创建表结构
alter修改结构 (add\modify\change\drop)
drop\truncate 删除表

七、表数据的操作DML
语法很重要(对应三个功能点)！必须记住!!

1)增加数据 insert
语法:
//方式一:全部字段 按照表结构一一对应给值
insert into 表名 values(值1,值2,...);

//部门表中插入数据
insert into dept_xu values(10,"研发部","南京");
insert into dept_xu values(20,'行政部','苏州');
commit;

//方式二:指定字段 按照指定字段一一对应给值
insert into 表名(字段1,字段2) values(值1,值2);

//插入数据
insert into dept_xu(deptno,dname,location) 
values(30,'销售部','无锡');
insert into dept_xu(deptno,dname,location) 
values(40,"市场部","杭州");
commit;//手动提交

修改编码:
set names gbk;//当前会话有效

//查询部门表 select....from...
select deptno,dname,location from dept_xu;

mysql客户端操作默认是自动提交，可以不需要手动提交!!!

补充:
mysql扩展:insert可以一次插入多行数据
create table temp(
name varchar(10),
age int
);
//了解
insert into temp(name,age) 
values("张三",22),("李四",23),("王五",24);

//查看 
select name,age from temp;

注意:
a.两种写法  全部字段\指定字段
b.字符数据必须加引号(mysql支持单、双引号)

2)更新数据 update
语法:
update 表名 set 字段=新值,字段=新值 where 条件;
注意：
如果语句中没有where则表示更新表中所有记录

//更新部门表中40号部门将部门地址改为"上海"
update dept_xu set location='上海' where deptno=40;

select deptno,dname,location from dept_xu;

3)删除数据 delete
语法:
delete from 表名 where 条件;

注意:
如果语句中没有where则表示删除表中全部记录

//插入50 '后勤部'  null
方式一:全部字段
insert into dept_xu values(50,"后勤部",null);
方式二:指定字段
insert into dept_xu(deptno,dname) values(50,'后勤部');

//删除50号部门
delete from dept_xu where deptno=50;
commit;

总结:DML 
insert:两种写法  字符数据必须加引号
update:是否有where条件
delete:是否有where条件
DML在使用需要结合TPL使用，commit确认提交或rollback取消

补充:
操作效果相同:删除表中所有数据，表结构是保留
truncate table dept_xu;
delete from dept_xu;

truncate和delete区别:
truncate:DDL  没有where条件  立即生效
delete:DML    可有where条件  没有提交可以回滚取消

练习:
根据模拟数据创建员工表(emp_xu)
考虑对应字段的类型及长度范围

8个字段:
    员工号:empno
    员工姓名:ename
    员工职位:position
    员工薪水:salary
    员工奖金:bonus
    入职日期:hiredate
    领导:leader
    部门号:deptno

//创建员工表
create table emp_xu(
empno int,
ename varchar(8),
position varchar(12),
salary double(7,2),
bonus double(5,2),
hiredate date,
leader int,
deptno int
);

drop table emp_xu;//彻底删除 

八、基础查询
1)简单查询
//查询 
select empno from emp_xu;//单列
select empno,ename from emp_xu;//多列 逗号隔开
select * from emp_xu;//通配符*表示查询全部字段
ps:
SQL优化
通常不建议使用通配符*，会降低查询速度和影响应用程序的性能

//查询1005员工信息
分析:
带有条件的查询语句
select empno,ename from emp_xu where empno=1005;

执行顺序(分析过程):
from(指定表)->where(记录过滤)->select(结果集)

补充:
操作符  说明
 =       等于
!=或<>   不等于
 >       大于号
 >=      大于等于
 <       小于
 <=      小于等于

2)列别名
//查询员工的月薪和年薪(月薪*12)
select ename,salary,salary*12 yearSalary
from emp_xu;

补充:限定表名
select ename from emp_xu;
select e.ename from emp_xu e;

3)空值null
a.任何数据类型可以取空值
b.空值参与算术运算结果为空
c.空值参与连接操作结果为空

//查询员工的月薪(salary+bonus)
select ename,salary,bonus,salary+bonus monthSalary from emp_xu;

4)空值处理函数
ifnull(e1,e2):如果e1为空则取e2代替

//查询员工的月薪(salary+bonus)
select ename,salary,bonus,
salary+ifnull(bonus,0) monthSalary from emp_xu;

5)插入一条记录
empno:1013
ename:"欧阳锋"
其他的字段都是空值

//全部字段(执行一条SQL)
insert into emp_xu 
values(1013,"欧阳锋",null,null,null,null,null,null);
//指定字段
insert into emp_xu(empno,ename) values(1013,"欧阳锋");

//查询员工姓名、职位，要求没有职位则显示"No Position"
select ename,
 ifnull(position,'No Position') position 
from emp_xu;

6)连接操作
concat(a,b,...)

//测试  'abc'
select concat('a','b','c') from dual;
select concat('a','b','c',null) from dual;//null

//查询员工信息，要求将员工姓名和职位连接在一起
select empno,
 concat(ename,ifnull(position,'No Position')) message
from emp_xu;

数据库第二天
回顾:
数据库(DB)->关系型数据库->表(Table)->SQL语言
划分:
    DDL:结构 create\alter\drop
    DML:数据 insert\update\delete 语法必须记住!!!
    TPL:事务 commit\rollback
    DQL:查询 select 基础查询\子查询\表间关联查询 (重要!!!)
    DCL:权限 create user\grant\revoke
    
查询:
    select(结果集)....from(表)....where(记录过滤)....
    
空值 null
a.任何类型可以取空值 insert
b.空值参与运算结果为空
c.空值参与连接操作结果为空  concat(a,b)

ifnull(e1,e2):e1为空则e2代替
    
一、基础查询
7)去重
distinct:只能跟在select后面

//查询有哪些职位
select distinct position from emp_xu;

//查询每个部门不重复职位
select distinct deptno,position from emp_xu;
说明:
distinct后面跟两个字段，需要对两个字段进行联合去重，全部列的
唯一组合

8)大小写
Mysql查询中默认是不区分大小写的
如果需要区分大小写，必须在创建表时候通过binary标识敏感字段
形如:
drop table temp;

create table temp(
name varchar(20) binary
);

insert into temp values("abc");

select * from temp where name="Abc";//abc->Abc

//查询职位为'Analyst'的员工信息
SELECT ENAME,position
from emp_xu
where binary lower(position)='analyst';

9)介于两者之间
between 低值 and 高值
肯定形式:
    [低值,高值]

//查询薪水大于等于5000并且小于等于10000的员工信息
补充:
并且(同时满足):and   
或者(满足一个即可):or
select ename,salary
from emp_xu
where salary>=5000 and salary<=10000;

换一种写法:
select ename,salary
from emp_xu
where salary between 5000 and 10000;

10)in使用
in(列表项):判断等于列表项中任意一项,即满足任意一个即可。

//查询职位是'Manager'或者'Analyst'的员工姓名和职位
select ename,position
from emp_xu
where position='Manager' or position='Analyst';

换一种写法:
select ename,position
from emp_xu
where position in("Manager","Analyst");

修改:
select ename,position
from emp_xu
where position in("Manager","Analyst",null);

分析:
'欧阳锋'的position为空，但该记录没有被查询出来

结论:
a.使用in时列表项中有空值对结果没有影响
b.空值不能用等于或不等于跟任何数据(自己本身)进行比较

select ename from emp_xu
where position=null;//Empty 等号不能用

11)模糊查询
条件不明确
使用like
占位符  %表示0到多个字符   _表示1个字符

//查询员工姓名包含'张'字员工信息
select ename
from emp_xu
where ename like '%张%';

//查询职位中第2个字符'a'的员工姓名和职位
select ename,position
from emp_xu
where position like '_a%';

12)空值判断
肯定形式:is null
否定形式:is not null
空值不能用等于或不等于跟任何数据进行比较!!!!

//查询哪些员工没有奖金
select ename,bonus
from emp_xu
where bonus=null;//不能用等于

select ename,bonus
from emp_xu
where bonus is null;

13)否定形式
//查询哪些人有奖金
select ename,bonus
from emp_xu
where bonus is not null;

//查询薪水不在5000到10000之间的员工信息
select ename,salary
from emp_xu
where salary not between 5000 and 10000;//否定形式临界值不包括

14)not in使用
not in(列表项):不等于列表项中所有项，即同时满足

//查询不是20号部门和30号部门的员工信息
select ename,deptno
from emp_xu
where deptno!=20 and deptno!=30;

换一种写法:
select ename,deptno
from emp_xu
where deptno not in(20,30);

修改:
select ename,deptno
from emp_xu
where deptno not in(20,30,null);

说明:
使用not in时列表项中有空值，最终结果一条记录没有被查询出来(Empty)。
所以使用时列表项中的空值必须去掉!!!!

总结:
in(列表项):肯定  判断等于任意  空值没影响
not in(列表项):否定  判断不等于所有  空值有影响列表项中空值必须去掉

二、基础查询_函数
单行函数
    每一行数据处理后返回一个结果
    
1)数字函数
round(数字,位数):用于对数字进行四舍五入处理保留到小数点后指定位数
//测试
select round(123.456,2) from dual;//123.46
select round(123.456,-2) from dual;//100
//保留到整数位 第二个参数可以省略
select round(123.456) from dual;//123 

truncate(数字,位数):用于对数字进行截取
//测试 
select truncate(123.456,2) from dual;//123.45
select truncate(123.456,0) from dual;//123

abs(数字):返回的数字的绝对值
rand():返回0-1随机数
sqrt(数字):返回数字的平方根
pow(x,y):返回x的y次方
mod(x,y):返回x除以y的余数
//测试
select abs(-2);
select rand();
select sqrt(16);
select pow(2,3);
select mod(7,3);

2)去除空格函数
trim(字符串):去掉字符串中左右空格

//'( abc )'->'(abc)'

select concat('(',' abc ',')') from dual;
select concat('(',trim(' abc '),')') from dual;
select concat('(',ltrim(' abc '),')') from dual;//left
select concat('(',rtrim(' abc '),')') from dual;//right

3)文本处理函数
upper(小写):转换成大写
lower(大写):转换成小写
//测试
select upper("abc");
select lower("ABC");

length(字符串):返回字符串的字节长度(编码)
select ename,length(ename) from emp_xu;

substring(字符串,起始位置,最大字符数量):返回指定部分内容
//从第2个字符开始，最多返回2个字符长度
select ename,substring(ename,2,2) from emp_xu;

4)日期\时间函数
now():获取当前系统日期时间
select now();// '2021-08-24 15:37:39'

date(时间):返回日期部分
time(时间):返回时间部分
//测试 
select date(now());//'2021-08-24'
select time(now());
select year(now());

adddate():增加一个日期(天day、周week、月month、年year)
//测试 
select now(),adddate(now(),interval -3 day);//interval间隔
select now(),adddate(now(),2);//默认表示'天'
或者
select date_add('2021-08-08',interval 3 day);

//重要
date_format(时间,格式):格式日期时间的
select date_format(now(),'%X-%m-%d %H:%i:%s') from dual;
select date_format(now(),'%X') from dual;
select date_format(now(),'%X-%m-%d') from dual;

组函数(重要)
    多行数据处理后返回一个结果
    count(字段):求记录数
    sum(字段):求和
    avg(字段):求平均
    max(字段):求最大
    min(字段):求最小
    
    //测试
    select count(empno) from emp_xu;//13
    select count(position) from emp_xu;//12  忽略空值
    select count(*) from emp_xu;//13 count(*)不忽略空值
    
    //查询员工表中薪水总和
    select sum(salary) from emp_xu;
    
    //查询员工表中人数总和、薪水总和、平均薪水
    select count(*),sum(salary),avg(salary)
    from emp_xu;//avg求平均忽略空值影响结果
    修改:
    select count(*),sum(salary),avg(ifnull(salary,0)) avgSalary
    from emp_xu;
    
    //查询员工表中最高薪水、最低薪水
    select max(salary),min(salary) from emp_xu;
    
    改动:名字有多个数据默认返回第一个值
    //mysql没有报错，但是数据有问题，不建议使用!!!
    select ename,max(salary),min(salary) from emp_xu;

总结:
count\sum\avg\max\min如果函数中写字段名，默认都是忽略空值
count(*)不忽略空值
avg(ifnull(字段,内容))
sum\avg用于处理数值类型

三、基础查询_排序
排序:对查询结果集进行排序(先有结果集再排序)
使用:order by 字段
规则:asc 升序(默认)   desc 降序

//查询员工姓名和薪水，要求薪水从低到高进行排序
select ename,salary
from emp_xu
order by salary;//默认 升序 asc

select ename,salary
from emp_xu
where salary is not null
order by salary desc;//降序

//按照部门号升序，同一个部门按照薪水降序查询
select deptno,salary 
from emp_xu
where deptno is not null
order by deptno asc,salary desc;//字段名

select deptno d,salary s
from emp_xu
where deptno is not null
order by d asc,s desc;//列别名

select deptno,salary 
from emp_xu
where deptno is not null
order by 1 asc,2 desc;//数字 

说明:
排序语句的执行在select之后的，因此排序可以使用列名、列别名、
函数、表达式，还可以使用数字(查询结果集对应的列的顺序，
第1列用1表示)。

补充:
select empno,ename
from emp_xu
order by convert(ename using gbk);//按照首个字母进行排序

四、基础查询_分组
使用:group by 字段 

//查询每个部门的最高薪水和最低薪水，要求没有部门的不算在内
select deptno,max(salary),min(salary)
from emp_xu
where deptno is not null
group by deptno;

//查询每个部门的薪水总和和平均薪水，要求没有部门的不算在内
select deptno,sum(salary),avg(ifnull(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno;

//按照职位分组，每个职位的最高薪水、最低薪水、人数总和，要求
没有职位的不算在内
select position,max(salary),min(salary),count(*)
from emp_xu
where position is not null
group by position;

说明:
select后面内容要么被组函数包围，要么出现在group by之后的。

select position,group_concat(ename)
from emp_xu
where position is not null
group by position;//group_concat(字段)处理多个数据

having:对分组之后的数据再进行过滤

//查询平均薪水大于5000的部门和平均薪水，要求没有部门的不算在内
分析:
13->12->4->having->结果集

select deptno,avg(ifnull(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000;

//查询薪水总和大于20000的部门号和薪水总和，要求没有部门的不算在内

//查询按照职位的人数超过2个人，没有职位的不算在内，计算每个职位的
平均薪水并且按照平均薪水降序排

数据库第三天
回顾:
基础查询
select....from...
select...from...where...
select...from..where...group by...having...order by...

空值 null
结论:
a.任何类型可以取空值 (insert全部字段)
b.空值参与算术运算结果为空 (处理 ifnull(e1,e2))
c.空值参与连接操作结果为空 (处理 ifnull(e1,e2))

空值处理函数
ifnull(e1,e2)
avg(ifnull(salary,0))

空值判断
不能用等于或不等于进行比较
肯定:is null
否定:is not null

in(列表项):肯定  等于任意
not in(列表项):否定 不等于所有  使用not in时列表项中空值必须去掉!!
=========================================================
一、基础查询
//查询薪水总和大于20000的部门号和薪水总和，要求没有部门的不算在内
select deptno,sum(salary)
from emp_xu
where deptno is not null
group by deptno
having sum(salary)>20000;

//查询按照职位的人数超过2个人，没有职位的不算在内，计算每个职位的
平均薪水并且按照平均薪水降序排
select position,count(*),avg(ifnull(salary,0)) avg_salary
from emp_xu
where position is not null
group by position
having count(*)>2
order by avg_salary desc;

select position,count(*) total,avg(ifnull(salary,0)) avg_salary
from emp_xu
where position is not null
group by position
having total>2
order by avg_salary desc;//having后面跟组函数，也可以用group by 
后面字段，也可以使用列别名(只有mysql支持)

总结:基础查询
写法顺序:
select->from->where->group by->having->order by

执行顺序:(分析过程)
from(表)->where(记录过滤)->group by(分组)->having(分组后过滤)->
select(结果集)->order by(排序)

二、子查询
子查询：
    一条SQL语句中嵌套select查询语句
    
非关联子查询:
    嵌套的子查询是独立语句不依赖主查询
    
1)查询最高薪水是谁
分步:
//找最高薪水
select max(salary) from emp_xu;//99999.99

//根据最高薪水找人
select ename,salary 
from emp_xu
where salary=99999.99;

合并:
select ename,salary
from emp_xu
where salary=(
select max(salary) from emp_xu
);

非关联子查询执行过程:(理解 记住!!)
先执行嵌套子查询(独立SQL、不依赖、仅执行一次)->
返回查询结果作为条件->再执行主查询

2)查询最低薪水是谁
select ename,salary
from emp_xu
where salary=(最低薪水);

select ename,salary
from emp_xu
where salary=(
select min(salary) from emp_xu
);

3)查询谁的薪水比'张无忌'高
select ename,salary
from emp_xu
where salary>('张无忌'薪水);

select ename,salary
from emp_xu
where salary>(
select salary from emp_xu where ename='张无忌'
);

4)查询'研发部'有哪些职位
select deptno,position
from emp_xu
where deptno=('研发部'部门号);

select deptno,position
from emp_xu
where deptno=(
select deptno from dept_xu where dname='研发部'
);

5)查询谁的薪水比'张无忌'高，如果有多个'张无忌'
//插入记录
insert into emp_xu values(
1014,'张无忌','Clerk',8000,800,now(),1013,null);

select empno,ename,salary
from emp_xu
where ename='张无忌';

//满足大于最大的 8000
select ename,salary
from emp_xu
where salary>(
select max(salary) from emp_xu where  ename='张无忌'
);

//满足大于所有的 5000 8000
select ename,salary
from emp_xu
where salary>all(
select salary from emp_xu where  ename='张无忌'
);//嵌套子查询返回多个值，大于号不能使用

6)查询哪些人的薪水比'张无忌'高，如果有多个'张无忌'
//满足大于最小的  5000
select ename,salary
from emp_xu
where salary>(
select min(salary) from emp_xu where  ename='张无忌'
);//单值

//满足大于任意一个的  5000或者8000
select ename,salary
from emp_xu
where salary>any(
select salary from emp_xu where  ename='张无忌'
);//多值

7)查询谁和'郭靖'同部门，列出除了'郭靖'之外的员工(单值)
select ename,deptno
from emp_xu
where deptno=(
select deptno from emp_xu where ename='郭靖'
) and ename!='郭靖';
    
8)查询谁和'郭靖'同部门，列出除了'郭靖'之外的员工(多值)    
select ename,deptno
from emp_xu
where deptno=any(
select deptno from emp_xu where ename='郭靖'
) and ename!='郭靖';

select ename,deptno
from emp_xu
where deptno in(
select deptno from emp_xu where ename='郭靖'
) and ename!='郭靖';

ps:
in和=any两者可以互换(满足等于任意一个)

9)查询谁是'张三丰'的下属
分析:
找员工->满足该员工的leader等于'张三丰'的员工号

select ename,leader
from emp_xu
where leader=(
select empno from emp_xu where ename='张三丰'
);//单值

select ename,leader
from emp_xu
where leader=any(
select empno from emp_xu where ename='张三丰'
);//多值

补充:
非关联子查询中比较运算符选择，根据嵌套子查询返回的值决定的!
单值:= > >= < <= !=
多值:=any in >all >any

10)查询每个部门拿最高薪水是谁
分步:
//查询每个部门最高薪水
select deptno,max(salary)
from emp_xu
where deptno is  not null
group by deptno;//多值多列

//根据部门号、最高薪水找人
select ename,deptno,salary
from emp_xu
where (deptno,salary)=any();

合并:
select ename,deptno,salary
from emp_xu
where (deptno,salary)=any(
select deptno,max(salary)
from emp_xu
where deptno is  not null
group by deptno
);//=any可以用in替换

说明:
多值:主查询不关心嵌套子查询返回的字段，只关心查询返回单值还是多值
多列:使用时规则相同的(部门号等于部门号，薪水等于最大薪水)

11)查询哪些部门的平均薪水比20号部门平均薪水高
select deptno,avg(ifnull(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>(
select avg(ifnull(salary,0)) from emp_xu
where deptno=20
);

12)查询员工所在部门的平均薪水大于5000的员工姓名和职位
select deptno
from emp_xu
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000;//10 40

select ename,position,deptno
from emp_xu
where deptno in(
select deptno
from emp_xu
where deptno is not null
group by deptno
having avg(ifnull(salary,0))>5000
);

13)查询哪些员工的薪水是本部门的平均薪水
select deptno,avg(ifnull(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno;//多值多列

select ename,deptno,salary
from emp_xu
where (deptno,salary) in(
select deptno,avg(ifnull(salary,0)) avg_salary
from emp_xu
where deptno is not null
group by deptno
);
    
总结:非关联子查询
a.理解执行过程
b.比较运算符选择(单值、多值)
c.多值多列(比较规则相同)

分析:
//查询哪些员工的薪水比本部门的平均薪水值低
要求部门号相等(=)，薪水小于平均薪水(<)
两者比较规则不一致非关联子查询实现不了!!!

关联子查询:
    嵌套的子查询不是独立语句依赖主查询(两者有关系)

1)查询哪些员工的薪水比本部门的平均薪水值低
select ename,deptno,salary
from emp_xu
where salary<(本部门的平均薪水);

select ename,deptno,salary
from emp_xu e
where salary<(
select avg(ifnull(salary,0))
from emp_xu
where deptno=e.deptno
);//e.deptno表示动态数据，由主查询传递过来的(依赖主查询)

关联子查询执行过程:
先执行主查询(传递依赖数据)->执行嵌套子查询(依赖数据 执行多次)->
返回查询结果->再执行主查询

2)查询哪些人有下属
分析:
找领导->满足该员工的empno等于别人leader

非关联:
select leader from emp_xu;

select empno,ename
from emp_xu
where empno in(
select leader from emp_xu
);//多值

关联:
select empno,ename
from emp_xu e
where exists(
select 1 from emp_xu
where leader=e.empno
);

说明:
exists用于判断子查询有没有数据返回。如果满足关系则有数据返回，
不满足则没有数据返回。

exists不关心嵌套子查询返回的结果，嵌套子查询中select后面写什么都
可以，通常用1表示。

3)查询哪些人没有下属(否定)
非关联:
select empno,ename
from emp_xu
where empno not in(
select leader from emp_xu
where leader is not null
);//使用not in时列表项中空值必须去掉!!!

关联:
select empno,ename
from emp_xu e
where not exists(
select 1 from emp_xu
where leader=e.empno
);

4)查询哪些部门有员工(肯定)
分析:
部门表中部门号等于员工表中部门号

非关联:
select deptno,dname
from dept_xu
where deptno in(
select deptno from emp_xu
);

关联：
select deptno,dname
from dept_xu d
where exists(
select 1 from emp_xu e
where e.deptno=d.deptno
);

5)查询哪些部门没有员工(否定)
insert into dept_xu values(50,'后勤部',null);

非关联:
select deptno,dname
from dept_xu
where deptno not in(
select deptno from emp_xu
where deptno is not null
);

关联：
select deptno,dname
from dept_xu d
where not exists(
select 1 from emp_xu e
where e.deptno=d.deptno
);

数据库第四天
回顾:
子查询
    非关联:
        a.嵌套的select语句独立的不依赖主查询(没关系)
        b.执行过程
          先执行嵌套子查询(独立)->返回查询结果(单值、多值)->
          再主查询(查询结果)
        c.比较运算符选择
          =    >    
          =any >all >any
        d.多值多列
        比较规则必须相同 = =->=any\in
    
    关联:
        a.嵌套的select语句不是独立依赖主查询(有关系)
        b.依赖的数据 d.deptno(10 20 30 40)
        c.exists   关系满足->有数据返回
        d.执行过程
=================================================================
一、组合查询
组合查询由两条或两条以上的select语句组成，中间使用union进行分隔。

组合查询的前提条件，两个结果集必须有相同结构(列的个数，列的顺序，
列的类型)。        
        
//查询10号部门的员工姓名和薪水
select ename,salary from emp_xu where deptno=10;
ename  | salary   |
-------+----------+
张三丰       | 99999.99
张无忌      |  5000.00 |
杨过       |  8000.00 |

//查询薪水大于6000的员工姓名和薪水
select ename,salary from emp_xu where salary>6000;
 ename  | salary   |
--------+----------+
 张三丰       | 99999.99
 杨过       |  8000.00 |
 乔峰      |  8000.00 |
 段誉       | 15000.00 |
 孙悟空       | 50000.00
 燕小六      | 12000.00 |
 张无忌      |  8000.00 |        

//union 自动进行去重        
select ename,salary from emp_xu where deptno=10    
union        
select ename,salary from emp_xu where salary>6000
order by salary desc;//排序放在最后
        
//union all    不去重
select ename,salary from emp_xu where deptno=10    
union all
select ename,salary from emp_xu where salary>6000;

//结构不相同:mysql不会报错 数据有问题
select ename,salary from emp_xu where deptno=10    
union        
select ename,position from emp_xu where salary>6000;

二、Mysql中分页查询
limit:限制查询记录数

语法:
select 字段  from 表 limit 数量;
或
select 字段 from 表 limit 开始,数量;//常用 记住!!!

//测试
select empno,ename from emp_xu limit 5;
//开始下标从0开始
select empno,ename from emp_xu limit 4,2;

举例:
//查询每页显示5条记录
第1页  0-4    limit 0,5
  2    5-9    limit 5,5
  3    10-14  limit 10,5
  4    15-19  limit 15,5
  ....

//计算公式
page:第几页
pageSize:每页的记录数

int beginIndex=(page-1)*pageSize;//每页开始下标·

//分页查询核心SQL
select empno,ename from emp_xu limit beginIndex,pageSize;
//查询第6页2条记录
select empno,ename from emp_xu limit 10,2;

三、表间关联查询(多表联合查询)
内连接
    语法:[]可选项
    表1 [inner] join 表2 on 条件
    
    //查询员工姓名和其部门的名字
    分析:
    查询的结果集来自emp_xu和dept_xu 
    select ename,d.deptno,dname
    from emp_xu e join dept_xu d 
    on e.deptno=d.deptno;//d.deptno需要指明，两张表中都有deptno
    
    说明:
    12记录不是14记录原因
    内连接的结果集中数据一定是在两张表中都能找到的匹配记录
    内连接结果集保留匹配上的记录，匹配不上的记录被丢掉!!!
    
    补充:
    //改动
    select ename,d.deptno,dname
    from emp_xu e,dept_xu d
    where e.deptno=d.deptno;
    ps:
    如果不加where条件关联，得到笛卡尔积的结果
    结果=第一个表行数*第二个表行数
    
    //查询员工姓名和其领导的名字
    分析:
    关联两张emp_xu表
    select e1.ename,e1.leader,e2.empno,e2.ename
    from emp_xu e1 join emp_xu e2
    on e1.leader=e2.empno;
    
    select e1.ename,e1.leader,e2.empno,e2.ename
    from emp_xu e2 join emp_xu e1
    on e1.leader=e2.empno;//表1和表2可以互换
    
    说明:
    表1 join 表2 on 条件
    a.表1为驱动表，表2为匹配表
    b.执行过程
    遍历驱动表在匹配表中找匹配记录
    匹配上的记录保留，匹配不上的记录丢掉
    c.等值连接(条件中使用等号)中，驱动表和匹配表可以互换不会
     影响结果
    
    //查询员工的姓名和其部门的名字，要求没有部门的员工也要
    被查询出来
    分析:
    组合查询=内连接+基础查询
    查询全部员工(14)=有部门的员工(12)+没有部门的员工(2)
    
    select ename,dname
    from emp_xu e join dept_xu d
    on e.deptno=d.deptno
    union
    select ename,'No Dept' from emp_xu where deptno is null;
    

外连接(严格区分哪个表是驱动表的)
    语法:[]表示可选项
    //左外连接 左边的表为驱动表
    表1 left [outer] join 表2 on 条件
    //右外连接 右边的表为驱动表
    表1 right [outer] join 表2 on 条件 

    //查询员工的姓名和其部门的名字，要求没有部门的员工也要被
      查询出来
     分析:
     查询全部员工，员工表作为驱动表
     select ename,d.deptno,dname
     from emp_xu e left join dept_xu d 
     on e.deptno=d.deptno;

     select ename,d.deptno,dname
     from dept_xu d right join emp_xu e
     on e.deptno=d.deptno;//左外连接、右外连接可以互换，必须明确
     哪个表是驱动表
     
    //查询部门名字和员工姓名，要求没有员工的部门也要被查询出来
    分析:
    查询全部部门->部门表作为驱动表
    
    select d.deptno,dname,ifnull(ename,'No Emp') ename
    from emp_xu e right join dept_xu d 
    on e.deptno=d.deptno;
    
    说明:
    驱动表中的全部数据会出现在外连接的结果集中
    如果驱动表在匹配表中找不到匹配记录，则匹配一行空行
    
    外连接的结果集=内连接的结果集(匹配上的记录)+驱动表在匹配表
            中匹配不上的记录(匹配不上的记录)
    
    //查询哪些部门没有员工  50 '后勤部'
     非关联:
     select deptno,dname
     from dept_xu
     where deptno not in(
     select deptno from emp_xu
     where deptno is not null
     );
     
     关联:
     select deptno,dname
     from dept_xu d
     where not exists(
     select 1 from emp_xu
     where deptno=d.deptno
     );
     
     外连接:
     查询全部部门->部门表作为驱动表
     select d.deptno,dname
     from dept_xu d left join emp_xu e 
     on d.deptno=e.deptno
     where empno is null;//匹配不上匹配一行空记录
    
    注意点:
    a.不要关联不必要的表，处理关联表非常消耗资源
    b.关联的表越多可能导致性能下降
    c.获取到同样的结果，可能存在很多SQL实现方式，找最优方式
    
四、约束类型
创建表结构时字段添加对应约束类型

1)主键约束
主键:primary key 简称PK
特点:
不能为空+不能重复

一张表只能有一个主键，主键可以是一列或多列组合。

两种定义方式:列级和表级

//列级(常用) 
create table stu_xu(
id int primary key auto_increment,
name varchar(20)
);

insert into stu_xu values(1001,'张三');
insert into stu_xu values(1001,'李四');//不能重复
insert into stu_xu values(null,'王五');//不能为空

//表级(了解)
drop table stu_xu;

create table stu_xu(
id int,
name varchar(20),
email varchar(20),
constraint stu_xu_name_pk primary key(id),
constraint stu_xu_email_uk unique(email)
);

insert into stu_xu values(1001,'张三');
insert into stu_xu values(1001,'李四');
insert into stu_xu values(null,'王五');

补充:
stu_xu_id_pk 主键约束的名字  表名_字段名_约束名

mysql支持主键自增
primary key auto_increment

2)非空约束
非空约束:not null 简称NN
只有列级定义

drop table stu_xu;

//列级
create table stu_xu(
id int primary key auto_increment,
name varchar(20) not null
);

insert into stu_xu values(1001,'张三');
insert into stu_xu values(1002,null);

3)唯一约束
唯一约束:unique 简称UK

//列级 
drop table stu_xu;

create table stu_xu(
id int primary key auto_increment,
name varchar(20) not null,
email varchar(30) unique
);

insert into stu_xu values(1001,'张三','123@qq.com');
insert into stu_xu values(1002,'张三','123@qq.com');
insert into stu_xu values(1003,'张三',null);
insert into stu_xu values(1004,'张三',null);

//表级(参考主键表级写法)

4)外键约束
外键约束:foreign key 简称FK

外键约束定义在两张表的两个字段上，用于保证这两个字段的关系。

如果表A的主键字段是表中的字段，则该字段称为表B的外键，表A为主表
表B为从表

A(主表)                     B(从表)
部门表                    员工表
deptno(主键)              empno(主键)
                          deptno(外键)

//创建部门表
create table temp_dept(
deptno int primary key auto_increment,
dname varchar(10) not null
);

//创建员工表
create table temp_emp(
empno int primary key auto_increment,
ename varchar(10) not null,
deptno int,
constraint temp_emp_deptno_fk foreign key(deptno) 
references temp_dept(deptno) 
);

//插入
insert into temp_dept values(100,'研发部');
insert into temp_emp values(1001,'张三',100);

//删除
delete from temp_emp;
delete from temp_dept;

说明:
外键是用来实现参照完整性，外键约束将两个表紧密的结合在一起，
保证数据的完整性和一致性。
表添加外键之后，两个表就建立关系(主表\从表)。创建表或插入数据
或删除时，这些操作有先后顺序。
    

数据库第五天
回顾:
一、组合查询
结果集结构相同
union分隔

二、分页查询(重要 功能)
核心SQL
select 字段 from 表 limit 每页开始下标,每页记录数;

//公式
page
pageSize
int begin=(page-1)*pageSize;

三、表间关联查询(多表联合查询)
内连接
    表1(驱动表) join 表2(匹配表) on 条件
    遍历驱动表在匹配表在找匹配记录
    匹配上的记录保留，匹配不上被丢掉

外连接
    表1(驱动表) left join 表2 on 条件
    表1 right join 表2(驱动表) on 条件
    严格区分哪个表是驱动表
    匹配上的记录保留，匹配不上的匹配一行空记录
    外连接的结果集是驱动表中所有数据
    
四、约束类型
创建表结构

主键自增  primary key auto_increment  不能重复+不能为空
非空      not null  只能用列级
唯一      unique    null不影响
外键      foreign key  两张表中两个字段的关系(主\从)->操作有先后顺序

================================================================
一、事务
事务是一组DML操作的逻辑单元，用于保证数据的一致性，要么一起成功
提交，要么一起失败撤销。

事务处理语言TPL
    commit
    rollback
    savepoint
    
事务的开始和中止
    开始:事务开始于上一个事务的结束或
         第一条DML操作(insert\update\delete)
    中止:事务中止于显示操作(commit\rollback)

事务中数据的状态
    如果多个会话操作同一张表数据，当用户与服务器连接成功后，服务器
    和客户端建立一个会话(session),所有交互在此会话中进行。
    
事务演示:
//会话默认是自动提交，修改提交方式变成不自动提交
set autocommit=0;

步骤1:开启会话A，创建表并插入数据不提交
create table temp(
id int
);

set autocommit=0;

insert into temp values(1);

select * from temp;

步骤2:开启会话B，查看表数据
select * from temp;
看不到数据，只能看到表结构

步骤3:会话A进行提交
会话B可以查看到数据
commit;

步骤4:会话A更新数据不提交，会话B看不到数据改变
update temp set id=2;//A
select * from temp;//B

步骤5:会话A提交，会话B可以看到数据改变
commit;

步骤6:会话A中更新数据不提交，会话B删除数据不提交
update temp set id=3;//A

set autocommit=0;//B 不自动提交
delete from temp;//B  发生阻塞状态

步骤7:会话A提交，会话B结束阻塞状态
commit;

步骤8:会话B关闭，会话A更新，不提交，回滚
update temp set id=4;

rollback;//取消

select * from temp;//3

总结:
1)事务内部数据的改变如果没有提交，只能在当前会话中可以看到数据
改变，其他会话中是看不到数据的改变。
2)事务会对操作的数据进行加锁，不允许其他事务操作(发生阻塞)。
3)如果commit提交，数据的改变得到了确认，其他会话可以看到数据
改变;数据上的锁被释放;保留数据的临时空间被释放。
4)如果rollback回滚，数据的改变得到了取消;数据上的锁被释放;
保留数据的临时空间被释放。

保存点演示:
drop table temp;

create table temp(
id int
);

insert into temp values(1);
savepoint A;

insert into temp values(2);
savepoint B;

insert into temp values(3);

rollback to A;//回滚到保存点A

select * from temp;//A后面的保存点会被自动取消

ps:
如果提交了那么回滚无效!!!

二、Mysql中常用对象
1)表 table
表是关系型数据库的基本存储结构，表是一个二维结构，由行和列组成，
横向为行也叫记录，纵向为列也叫字段。

2)视图 view
视图是虚表(没有数据),其内容由查询定义，视图对应一条select查询
语句，此语句得到的结果集赋予一个名字，即视图的名字。可以像
操作表一样操作视图。

//查询20号部门员工的员工号和姓名
select empno,ename
from emp_xu
where deptno=20;

 1004 | 郭靖
 1005 | 黄蓉
 1006 | 洪七公
 
//创建视图
create view emp_view as
select empno,ename
from emp_xu
where deptno=20;
 
//查看视图
select * from emp_view;
 
说明:
视图的使用和表相同，视图的好处能够简化查询，隐藏表中不需要列，
视图不包括任何数据，视图是表的投影!!!

//更新表数据，查看视图
update emp_xu set ename='郭靖1' where empno=1004;

select * from emp_view;

//更新视图，查看表数据
update emp_view set ename='郭靖' where empno=1004;

select empno,ename from emp_xu where empno=1004;

ps:
表进行DML操作会改变视图的显示，对视图进行DML操作同一会改变表
中的数据。(视图只是表的投影!!)

3)索引 index
索引是用来在数据库中加速表查询的数据库对象，通过快速访问路径
方式快速定位数据，可以减少磁盘的I\O操作，提高访问性能。

ps:
索引结构
//用空间换取时间
数据+地址

自动创建索引:主键约束和唯一约束
手动创建索引
create index 索引名 on 表(字段);

//根据员工号查询员工姓名
select ename from emp_xu where empno=1004;

//给empno字段添加索引
create index empno_index on emp_xu(empno);

补充:查看语句执行时间
show variables like '%pro%';

//开启
set profiling=1;

//查看时间
show profiles;

注意:
索引演示时间对比效果不是很明显，当前数据量有限(比较小)。

4)存储过程 procedure  (了解)
存储过程是在大型数据库系统中，一组为完成特定功能的SQL语句集。
存储过程存储在数据库中，经过第一次编译后再次调用不需要编译，
用户直接通过指定存储过程名字并给参数来执行。

//创建存储过程
create procedure 名字([参数])
begin
....
end

//创建存储过程:查询员工表中员工的最高薪水
delimiter //
create procedure maxSalary()
begin
select max(salary) from emp_xu;
end //
delimiter ;

//调用存储过程
call maxSalary();

ps:
delimiter声明分隔符
mysql默认';'为分隔符，如果没有声明分隔符，编译器会把存储过程
当成SQL语句进行处理，则存储过程会报错。

//创建带有输出参数的存储过程:
//查询员工表中员工的最高薪水、最低薪水、平均薪水
delimiter //
create procedure empSalary(
out max_salary double(7,2),
out min_salary double(7,2),
out avg_salary double(7,2)
)
begin
select max(salary) into max_salary from emp_xu;
select min(salary) into min_salary from emp_xu;
select avg(ifnull(salary,0)) into avg_salary from emp_xu;
end //
delimiter ;

//调用存储过程
call empSalary(@max_salary,@min_salary,@avg_salary);

//查看 
select @max_salary,@min_salary,@avg_salary from dual;

//创建带有输入输出参数的存储过程
//根据员工号查询员工姓名
delimiter //
create procedure getName(in id int,out name varchar(10))
begin
select ename into name from emp_xu where empno=id;
end //
delimiter ;

//调用存储过程
call getName(1004,@name);

//查看 
select @name from dual;

三、用户管理
//切换数据库
use mysql;

//创建用户abc
create user abc@'localhost' identified by '1234';

//分配权限:操作jsd库中所有表的所有权限
grant all on jsd.* to abc@'localhost';

//回收权限
revoke delete on jsd.* from abc@'localhost';

//以abc用户身份登录
打开cmd->进入mysql的bin目录

C:\MySQL\MySQL Server 5.5\bin

//登录命令
mysql -h localhost -u abc -p1234

//当前用户
select user();

use jsd;
set names gbk;
select * from emp_xu;

数据库第六天
回顾:
一、事务
结合DML操作(insert\update\delete)

commit;//提交
rollback;//回滚

二、Mysql中常用对象
1)表 table

2)视图 view
简化查询
视图是表的投影

3)索引 index
大量数据中能优化查询语句!!
加速查询
创建:
    自动创建
    手动创建
4)存储过程 procedure
完成特定功能的语句集

三、用户管理
use mysql;//该数据库下进行操作
create user\grant\revoke
==================================================================================================================================================

Oracle数据库(参照Mysql补充Oracle)

一、数据库

数据库：DB
关系型数据库：描述元素间关系，组织数据到二维表
表：行(记录)和列(字段)

二、SQL语言

所有关系型数据库使用相同或相似语言

数据定义语言 DDL 结构
    create\alter\drop\truncate

数据操纵语言 DML 数据
    insert\update\delete(语法)
    
事务处理语言 TPL 结合DML
    commit\rollback\savepoint
    
数据查询语言 DQL 查询
    select
    
数据控制语言 DCL 权限
    create\grant\revoke

三、Oracle常用数据类型

数值
number(p,s)：p表示总位数，s表示小数位数(如果s=0，则表示整数)

字符(Oracle里面只能用单引号！！！)
char(n)：定长
varchar2(n)：变长

日期
date
oracle默认日期格式"DD-MON-YY"

四、客户端操作

//打开
oracle 11g->sql plus（sql plus 命令行；sql developer 图形化界面）

//登陆
用户名：scott
密码：1234

/*mysql自带四个库，操作需要指定库；
oracle只有一个库
*/

//查看当前用户
show user; //USER 为 "SCOTT"

//当前用户下有哪些表
select table_name from user_tables;

//查看表结构
desc 表名;

//查看系统时间(默认日期格式 'DD-MON-YY')
select sysdate from dual; //08-3月-21

//修改当前会话默认日期格式(当前会话有效)
alter session set nls_date_format='yyyy-mm-dd'; //会话已更改。
select sysdate from dual; //2021-03-08

五、表结构操作

//创建表(oracle数据类型，参考之前的模拟数据)
创建部门表(dept_tao)
create table dept_tao(
deptno number(2),
dname varchar2(6),
location varchar2(4)
);

创建员工表(emp_tao)
create table emp_tao(
empno number(4),
ename varchar2(6),
position varchar2(10),
salary number(7,2),
bonus number(5,2),
hiredate date,
leader number(4),
deptno number(2)
);

//修改表结构(了解)
增加字段
alter table dept_tao add des varchar2(20);

修改字段
alter table dept_tao modify des char(30); //oracle没有change

删除字段
alter table dept_tao drop(des); //mysql没有()

//删除表
drop table dept_tao;

六、表数据操作

//插入数据
    insert into 表 values(值1,值2);
    insert into 表(字段1,字段2) values(值1,值2);
    
    ps：
    oracle不能自动提交，需要手动提交。
    
    默认日期格式：'DD-MON-YY'
    日期数据格式：'yyyy-mm-dd'
    alter session set nls_date_format='yyyy-mm-dd';
    
    //希望记录一行显示，结合命令和窗口宽度(80->150)
    set linesize 200; //默认80 当前会话有效
    
dept_tao
insert into dept_tao values(10,'研发部','南京');
insert into dept_tao values(20,'销售部','苏州');
insert into dept_tao values(30,'行政部','无锡');
insert into dept_tao values(40,'市场部','杭州');
commit;
emp_tao
insert into emp_tao values(1001,'张三丰','Analyst',99999.99,999.99,'2010-03-11',NULL,10);
insert into emp_tao values(1002,'张无忌','Programmer',5000,NULL,'2011-07-01',1001,10);
insert into emp_tao values(1003,'杨过','Manager',8000,500,'2008-05-15',NULL,10);
insert into emp_tao values(1004,'郭靖','Salesman',4500,999,'2009-11-10',1005,20);
insert into emp_tao values(1005,'黄蓉','Manager',6000,NULL,'2009-09-01',NULL,20);
insert into emp_tao values(1006,'洪七公','Clerk',3000,NULL,'2009-02-01',1005,20);
insert into emp_tao values(1007,'韦小宝','Salesman',4000,800,'2009-02-20',NULL,30);
insert into emp_tao values(1008,'乔峰','Analyst',8000,600,'2009-06-01',1007,30);
insert into emp_tao values(1009,'小龙女','Manager',1500,NULL,'2012-09-10',1008,30);
insert into emp_tao values(1010,'段誉','President',15000,100,'2008-02-20',NULL,40);
insert into emp_tao values(1011,'孙悟空','Salesman',50000,300,'2010-06-28',1010,40);
insert into emp_tao values(1012,'燕小六','Analyst',12000,999.99,'2014-11-11',1010,40);
insert into emp_tao values(1013,'燕小六','Analyst',12000,999.99,'2014-11-11',1010,40);

//更新数据
    update 表 set 字段1=新值1, 字段2=新值2 where 条件;

//删除数据
    delete from 表 where 条件;

七、基础查询
1）简单查询
select * from emp_tao;
select empno,ename from emp_tao where empno = 1004;

2）空值
a.任何数据类型可以取空值(插入数据)
b.空值参与算术运算结果为空(空值处理)
c.空值参与连接操作(||)结果相当于空值不存在(不影响)

select ename || bonus from emp_tao;

3）空值处理函数
nvl(e1,e2)：如果e1为空用e2替代，两个数据类型必须一致

select ename,salary,bonus,salary+nvl(bonus,0) money
from emp_tao;

e.g:
select ename,salary,bonus,salary+nvl(bonus,'ab') money
from emp_tao;
/*第 1 行出现错误:
  ORA-01722: 无效数字*/

4）连接操作
select ename || bonus from emp_tao;

ENAME||BONUS
-------------
张三丰999.99
张无忌
杨过500
郭靖999
黄蓉
洪七公
韦小宝800
乔峰600
小龙女
段誉100
孙悟空300
燕小六999.99
-------------

5）去重
select distinct position from emp_tao;

6）大小写
SQL语句不区分大小写，数据(单引号内容)区分大小写！

upper('a')：转成大写
lower('A')：转成小写

select ename,position
from emp_tao
where position='Analyst';

若
select ename,position
from emp_tao
where position='analyst'; //未选定行

7）介于两者之间
between 低值 and 高值

8）in和not in使用
in(列表项)：判断等于任意一项 列表项中有空值不影响
in可以换成=any

not in(列表项)：判断不等于所有项 列表项中空值必须去掉！！！

select ename,position
from emp_tao
where position in('Analyst','Manager');

select ename,deptno
from emp_tao
where deptno not in(20,30);

9）模糊查询
like
_表示一个字符，%表示0到多个字符

10）空值判断
肯定：is null
否定：is not null

11）数字函数
round(数字,位数)：四舍五入
trunc(数字,位数)：截取
//测试
select trunc(123.456,2) from dual; //from dual不可省！

12）日期函数
sysdate：当前系统时间
months_between(d1,d2)：两个日期相隔月份(一般大的在前)
add_months(d,n)：日期上增加月份
last_day(d)：当前(日期所在)月份的最后一天

//测试
select sysdate from dual;
select months_between(add_months(sysdate,2),sysdate) from dual; //2
select add_months(sysdate,2) from dual;
select last_day(sysdate) from dual;

13）转换函数
to_char(日期,格式)：
to_date(字符,格式)：将时间字符数据按照指定格式转成日期数据

//测试
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; //2021-03-08 11:49:53

//to_date必须保证数据和格式两者格式匹配
//转换得到date数据按照会话格式显示
select to_date('2021-03-08','yyyy-mm-dd') from dual;

14）组函数
    count(字段)
    max(字段)
    min(字段)
数值
    sum(字段)
    avg(字段)

//建议避免使用 mysql不报错数据有问题，oracle是报错的。
select ename,max(salary) from emp_tao;

//select后面数据要么被组函数包围，要么出现在group by之后
select deptno,max(salary)
from emp_tao
group by deptno;

15）排序
先有结果集再排序
order by 字段 asc|desc

16）分组
group by 字段 having...

//查询平均薪水大于5000部门，平均薪水降序排序
select deptno,avg(nvl(salary,0)) avg
from emp_tao
where deptno is not null
group by deptno
having avg(nvl(salary,0))>5000
order by avg desc;
    DEPTNO        AVG
---------- ----------
        10 37666.6633
        40 25666.6667

17）基础查询
执行过程：
from->where->group by->having->select->order by

18）子查询
非关联：
select ename, salary
from emp_tao
where salary=(
select max(salary) from emp_tao
);
ENAME      SALARY
------ ----------
张三丰   99999.99

a.执行过程
b.比较符选择
c.多值多列

关联：
//查询哪些员工薪水比本部门平均薪水低
select ename, salary
from emp_tao e
where salary<(
select avg(nvl(salary,0))
from emp_tao
where deptno=e.deptno
);

exists：根据题目判断满足某种条件

19）表间关联查询
内连接：
    表1(驱动表) join 表2(匹配表) on 条件
    遍历驱动表在匹配表中找匹配记录，保留匹配上记录
    
外连接：(严格区分哪个表是驱动表)
    匹配上的记录被保留，匹配不上匹配一行空行
    驱动表中数据会全部出现在外连接的结果集中
    
    //左外连接
    //右外连接
    //全外连接
    表1 full join 表2 on 条件
    
    insert into dept_tao(deptno,dname) values(50,'营销部');
    insert into emp_tao(empno,ename) values(1013,'欧阳锋');
    commit;
    
    //两个表中数据全部出现在全外连接结果集中
    select nvl(ename,'No Emp'),nvl(dname,'No Dept')
    from emp_tao e full join dept_tao d
    on e.deptno=d.deptno;
    ------ -------
    张三丰 研发部
    张无忌 研发部
    杨过   研发部
    郭靖   销售部
    黄蓉   销售部
    洪七公 销售部
    韦小宝 行政部
    乔峰   行政部
    小龙女 行政部
    段誉   市场部
    孙悟空 市场部
    燕小六 市场部
    欧阳锋 No Dept
    No Emp 营销部
    ------ -------
    
八、Oracle分页查询

关键字：rownum
rownum是一个伪列，对查询返回数据进行编号，从1开始

//查询员工表中前5条数据
select empno,ename
from emp_tao
where rownum<6;

 EMPNO ENAME
------ ------
  1001 张三丰
  1002 张无忌
  1003 杨过
  1004 郭靖
  1005 黄蓉
  
//查询员工表中第4、5、6记录
select empno,ename
from emp_tao
where rownum>3 or rownum<7; //>不能用

//集合(union合集\minus差集\intersect交集)
select empno,ename
from emp_tao
where rownum<7
minus
select empno,ename
from emp_tao
where rownum<4;
或
select empno,ename
from(select empno,ename,rownum rn
from emp_tao
where rownum<7)
where rn>3;
 EMPNO ENAME  
------ ------
  1004 郭靖  
  1005 黄蓉  
  1006 洪七公
//修改
select empno,ename,position
from(select e.*,rownum rn
from emp_tao e
where rownum<7)
where rn>3;
 EMPNO ENAME  POSITION
------ ------ ---------
  1004 郭靖   Salesman
  1005 黄蓉   Manager
  1006 洪七公 Clerk

oracle分页查询核心SQL语句：(oracle分页查询依赖 伪列 rownum 在from 子查询之中为rownum取别名 rownum是伪列无法直接进行between等操作 要赋予实体)
select empno,ename
from(select empno,ename,rownum rn
from emp_tao)
where rn between ? and ?;
/*where rn between 1 and 5;*/
 EMPNO ENAME
------ ------
  1001 张三丰
  1002 张无忌
  1003 杨过
  1004 郭靖
  1005 黄蓉

selectempno,ename,position,rn

ps：
第一问号表示每页开始(从1开始)
第二问号表示每页结束

//每页显示5条记录
第1页    1-5
  2        6-10
  3        11-15

page：第几页
pageSize：每页记录数

计算公式
int begin=(page-1)*pageSize+1;
int end=page*pageSize;

九、约束条件

主键 primary key
    不能重复+不能为空
    oracle主键不支持自增！！！
    
非空 nut null

唯一 unique

外键 foreign key 关系：主表\从表

检查约束 check

演示：
create table stu_tao(
id number(4) primary key,
name varchar2(10) not null,
email varchar2(20) unique,
sex char(1) check(sex in('F','M'))
);

insert into stu_tao values(1001,'张三','123@qq.com','F');

十、Oracle常用对象

1）表 table

2）视图 view
视图中没有数据，视图是表的投影。
视图隐藏不需要列，简化查询。

select empno,ename
from emp_tao
where deptno=20;

//创建视图
create view emp_view as
select empno,ename
from emp_tao
where deptno=20;

ps：
权限不足

//登陆 分配权限(不需要密码，直接回车进)
sqlplus /as sysdba

//给scott分配创建视图的权限
grant create view to scott;

//回收
revoke create view from scott;

//连接scott
conn scott;

3）索引
加速数据库查询的对象，提高查询效率

set timing on; //显示语句执行时间 当前会话有效

4）序列 sequence（mysql中有自增，不需要序列）
序列是一种用于生成唯一数字值的数据库对象，
序列可以按照递增或者是递减自动生成。

通常用来自动生成表的主键值，是一种高效率获取唯一主键值的途径。

特点：
产生连续不同的数字值，序列是数据库中独立的对象。
表可以用序列产生的值作为主键值，也可以不同。序列可以为
一个表或多个表产生主键值，建议一个序列为一个表产生主键值。

//创建第一个序列
create sequence first_sequence;
//测试(目前在第一个)(要先看next值)
select first_sequence.nextval from dual; //下一个值 1
select first_sequence.currval from dual; //当前值 1

//创建第二个序列
//从100开始，步进为10，递增
create sequence second_sequence
start with 100
increment by 10;
//测试
select second_sequence.nextval from dual;
select second_sequence.currval from dual;

//创建第三个序列
//递减
create sequence third_sequence
start with 100
maxvalue 100
increment by -10; // START WITH 不能大于 MAXVALUE
//测试
select third_sequence.nextval from dual;
select third_sequence.currval from dual;

演示：
利用序列产生的值作为表的主键值

//创建表
create table temp(
id number(4) primary key,
name varchar2(10) not null
);
//创建序列
create sequence temp_sequence
start with 1000;
//插入记录
insert into temp values(temp_sequence.nextval,'张三');
insert into temp values(temp_sequence.nextval,'李四');
//查看数据
select * from temp;

ps：
利用序列产生的值插入表中，存在'延迟段'特性，
序列自动跳过第一个值。

Mysql和Oracle对比
1）数据库
mysql：体积小、速度快、免费、开源
oracle：占很大内存、费用昂贵

2）数据类型
mysql：int/double/char/varchar/date/datetime

oracle：number/char/varchar2/date

3）客户端操作
mysql：
    root\1234
    show database;
    create database jsd default character set utf8;
    use jsd;
    set names gbk;
    show tables;
    desc 表名;
    select user(); //看用户
    select now();

oracle：
    scott\1234
    show user; //看用户
    select table_name from user_tables;
    desc 表名;
    select sysdate from dual; //dual不能省
    alter session set nls_date_format='格式'; //当前会话有效
    
4）空值
mysql：
    a.算术运算结果为空
    b.连接操作结果为空
    c.插入数据
    
    ifnull(e1,e2)
    
oracle：
    a.算术运算结果为空
    b.连接(||)没有影响
    c.插入数据
    
    nvl(e1,e2):e1和e2类型一致
    
5）大小写
mysql：默认不区分大小写；binary标识

oracle：SQL语句不区分，数据区分

6）单行函数
mysql：
    date_format(日期,'%X-%m-%d %H:%i:%s')
    
oracle：
    to_date(日期,'yyyy-mm-dd hh24-mi-ss'):格式匹配

7）集合
mysql：
    union 组合查询

oracle：
    union 合集
    minus 差集
    intersect交集

8）分页查询
mysql：limit
select 字段 from 表 limit 每页开始下标,每页记录数;

oracle：rownum
select 字段
from(
select 字段,rownum rn
from 表
)
where rn between 每页开始 and 每页结束;

9）约束条件
mysql：
    primary key/not null/unique/foreign key

oracle：
    primary key/not null/unique/foreign key/check

10）主键自增
mysql：支持主键自增
    primary key auto_increment
    
oracle：不支持主键自增
    利用序列对象来实现自增
    
11）分组
mysql：group_concat()一对多数据
select deptno,group_concat(position)
from emp_tao
where deptno is not null
group by deptno;

oracle：没有concat()函数，会直接报错，position不能用
select deptno,position
from emp_tao
where deptno is not null
group by deptno;

12）事务
mysql：
自动提交
在innodb(默认)存储引擎级别情况下可以支持事务

oracle：完全支持事务
commit; //需要手动提交

13）SQL语句
mysql：
mysql对sql语句有很多非常常用而方便的扩展，比如limit使用，
insert可以一次插入多行数据，select查询数据可以不加from(比如：select time();)。

oracle：
sql语句显得更加稳重传统

补充：
JDBC实现操作Oracle数据库(oracle架包)
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//连接
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","1234");
localhost->127.0.0.1 本机
1521->orcal端口号
orcl->数据库应用名(安装设置名字)

练习：JDBC实现Oracle，以及完成序列创建与查询等