oracle函数mysql没有的_0033.Oracle函数和MySQL004

1回顾

1.1多表联合查询

内连接 ： inner join on 条件

查询两张表都存在的数据(交集)

左外连接： LeftTable lt left join RightTable rt on 条件

查询两张表都存在的数据，以及左边表(LeftTable )存在的数据

右外连接 LeftTable lt right join RightTable rt on 条件

查询两张表都存在的数据，以及右边表(RightTable )存在的数据

1.2DDL

索引的创建和删除

create index 索引名称 on 表名称(列名称)；

drop index 索引名称;

视图的创建和删除

create view 视图名称

as

SQL

-- 不让外界修改视图with read only;

​

drop view 视图名称;

-- 向外界隐藏真实数据

序列的创建和删除

为我们自动生成ID值

create sequence 序列名称

increment by 大小 -- 步长start width 大小 -- 开始值minvalue 大小 -- 最小值(不能大于start with)maxvalue 大小 -- 最大值nocache / cache -- 不使用/使用 缓存nocycle / cycle -- 不使用/使用 循环

进入system用户，对scott用户授予创建视图的授权

grant create view to scott;

1.3PL/SQL

过程语言/结构化查询语言

使用面向过程的方式编写SQL(结构化查询语言)

变量的定义

分支结构

循环结构

匿名PL/SQL只能编写一次运行一次，不能重复调用

如果想让PL/SQL重复调用必须使用“署名”的PL/SQL

1 过程

类似于Java中的方法，没有返回值

完成某个独立功能的指令集合，有一个或者一系列的SQL语句、结构化语句(顺序、分支、循环)组成

2 触发器

发生一件事情，触发一个事件，执行对应的行为

特征：被动执行(没有调用也会执行)

当触发一个事件(insert事件)，就会自行

观察者模式：

例如：classes表 是一个被观察者角色

trigger_classes触发器是一个观察者角色

trigger_classes触发器会一直监视着classes表，当classes表发生insert事件的时候，触发器会自动创建一个序列的id，将其插入到classes表的主键

观察者模式的核心在于注册

create trigger trigger_classes

before

insert

on classes -- 将classes表注册到 trigger_classes触发器的insert事件上-- 此时classes作为被观察者向trigger_classes观察者进行注册-- hookfor each row

begin

-- 伪表：单行单列-- from dual 从伪表开始查询-- seq_classes.nextval从伪表开始查询序列的下一个值(1000) 赋给新表的id(new classes) select seq_classes.nextval into :new.id from dual;

end;

5Oracle提供函数

5.1 数学相关函数

相当于Java中的Math类的静态方法

abs(args)：参数的绝对值

负数的绝对值是正数，正数绝对值也是正数

select abs(-11) from dual;

select abs(empno) from emp;

ceil(args) 天花板:向上取整 3.14----》4

floor(args)地板：向下取整3.14----》3

select ceil(3.14)天花板 , floor(3.14)地板 from dual;

mod (args): 类似于java的%运算符 取余数

-- 取参数1除以参数2的余数select mod(10,3) from dual;

-- 数学相关函数可以用在select的列上select sal,mod(sal,7) from emp;

round ：四舍五入

​

-- 对参数1进行四舍五入， 参数2是参数1四舍五入保留的小数位数-- 此时就是对参数1进行四舍五入，并且会保留两位小数select round(456.678 ,2) from dual;

sqrt:平方根

​

--取9的平方根：结果3

select sqrt(9) from dual;

select sal,sqrt(sal)工资的平方根 from emp;

power :幂

-- power 取参数1的幂，幂的次数由参数2决定-- 此时3的3次幂select power(3,3) from dual;

select sal, power(sal,2) from emp;

5.2 字符串相关函数

相当于Java中String类型的成员方法

initcap:字符串首字母大写，其余字母都变为小写

-- 如果其他字母是大写也转换为小写 TOM ---->Tomselect ename ,initcap(ename)员工姓名 from emp;

concat:拼接字符串

-- 将参数1的值和参数2的值连接起来 7788 scott ---> 7788scottselect empno ,ename , concat(empno,ename) from emp;

lower:所有字母变为小写字母，如果本身是小写字母，保持不变

-- TOM tomselect ename ,lower(ename)员工姓名 from emp;

upper :所有字母变成大写字母，如果本身是大写字母，保持不变

-- TOM tom TOMselect ename ,lower(ename)员工姓名 ,upper(lower(ename))员工姓名 from emp;

replace:字符串的替换

​

-- 参数1是原始字符串，参数2是需要替换的字符串， 参数3是目的字符串-- 将原始字符串中，所有包含C的字符，替换为字符v--注意：Oracle数据库关键字不区分大小写，但是字符串内容会区分大小写

-- Scott Svottselect ename ,replace(ename,'C','v') from emp;

​

trim ：去掉空格

-- 去掉字符串左边和右边的空格select trim(' vac ') from dual;

length ：返回字符串的长度

-- SMITH 5select ename ,length(ename)名称长度 from emp ;

substr : 取子字符串

-- 参数1是原始字符串，从参数2开始截取，截取的长度是参数3-- 注意：Oracle数据库字符串索引下标从1开始 ，此时：从第三个字符后面截取5个字符-- 13898767543 ----> 89876select substr('13898767543',3,5) from dual;

instr：

-- 参数1：原始字符串

-- 参数2：需要查找的字符串

-- 返回值：是需要查找的字符串在原始字符串中的位置，如果返回结果为0，表示要查找的字符串在原始字符串中不存在

-- 9

select instr('What is your name?','your') from dual;

-- 参数1：原始字符串

-- 参数2：需要查找的字符串

-- 参数3：从原始字符串第几个下标开始查找 10

-- 参数4：查找第几个 2

-- 结果：45

-- 返回值：是需要查找的字符串在原始字符串中的位置，如果返回结果为0，表示要查找的字符串在原始字符串中不存在

select instr('What is your name?What is your name?What is your name?','your',10,2) from dual;

5.3 日期相关的函数

相当于Java中Date类和Calendar类的成员方法

sysdate:返回当前日期

-- 21-AUG-2019select sysdate from dual;

extract :根据指定格式提取时间相关的数据

-- 提取系统日期中的天数 21select extract(day from sysdate) from dual;

-- 提取系统日期中的年份 2019select extract(year from sysdate) from dual;

-- 提取系统日期中的月份 8select extract(month from sysdate) from dual;

-- 计算每个员工的工龄-- 提取系统日期中的年份，提取入职时间的年份， 相减select ename ,hiredate 入职日期,extract(year from sysdate) -extract(year from hiredate)工龄 from emp;

last_day

--返回当前日期月份的最后一天

-- 31-AUG-2019select last_day(sysdate) from dual;

select hiredate 入职日期, last_day(hiredate) from emp;

months_between :返回两个日期中间的月份

-- 返回参数1(21-AUG-2019)到参数2(17-DEC-80)之间的月份。结果：464.144207select hiredate, months_between(sysdate,hiredate)from emp;

--将两个日期之间的月份向下取整 17-DEC-80 464.144274 464

select hiredate, months_between(sysdate,hiredate), floor(months_between(sysdate,hiredate))from emp;

-- 计算工龄select hiredate,

floor(months_between(sysdate,hiredate))工龄月, floor(months_between(sysdate,hiredate)/12)工龄年 from emp;

add_months:对当前日期累加月份

-- 在当前年份基础上，增加3个月 21-NOV-19select add_months(sysdate,3)from dual;

-- 在当前年份基础上，减少3个月 21-MAY-19select add_months(sysdate,-3)from dual;

select hiredate, add_months(hiredate,1) from emp;

5.4 转换函数

to_date(args1,args2) :将其他类型转换为日期

select empno,ename,sal,hiredate

from emp

-- to_date('1980-01-01','YYYY-MM-DD') 将字符串'1980-01-01'转换为指定的格式'YYYY-MM-DD'where hiredate between to_date('1980-01-01','YYYY-MM-DD') and to_date('1981-10-01','YYYY-MM-DD');

to_char(args1,args2):将其他类型转换为字符串

-- to_char(hiredate,'YYYY-MM-DD') 将日期hiredate，按照指定格式('YYYY-MM-DD')转换为字符串select hiredate ,to_char(hiredate,'YYYY-MM-DD')

from emp;

3MySQL

特征：轻盈(磁盘空间占用少，内存空间占用少)

快捷(检索数据快)

简练(MySQL有自己的方言，简化SQL的编写)

3.1数据库操作

Oracle在安装的时候就会为你安装一个全局的数据库(orcl)

数据都会存放在表空间的逻辑结构中

我们需要创建表空间、创建用户、为用户授权来登录

需要我们自己创建数据库，一旦你创建了数据库，MySQL服务器就会为你创建对应的数据文件

3.1.1 数据库创建

-- create database 数据库名称;create database abcdefg;

-- 创建之前先检查要创建的数据库名称是否存在，如果不存在才创建，并且指定默认的字符集编码-- create database if not exists 数据库名称 default charset 字符集编码;create database if not exists J0704 default charset utf8;

注意：第二种创建方式比第一种好，容错性高，工作中全部使用第二种方式创建数据库

MySQL行注释两个减号加一个空格

MySQL第二种行注释#

3.1.2使用数据库

-- 语法：use 数据库名称;use J0704;

注意：执行DDL、DML、DQL(Data Query Language)、DCL之前一定要执行使用数据库命令,例如：use J0704 ;

3.1.3显示MySQL所有的数据库

show databases;

information_schema : 该数据库存储了MySQL的元数据信息

mysql：该数据库存储了MySQL服务器相关的信息

performance_schema：该数据库存储了MySQL性能相关信息

test：MySQL的测试数据库

以上4个数据库都是在安装MySQL服务器系统帮你创建的

3.1.3删除数据库

-- 语法： drop database 数据库名称;drop database 123;

-- 语法： drop database if exists 数据库名称;-- 删除之前先检查要删除的数据库是否存在，如果存在将其删除，不错不存在不会删除drop database if exists abcdefg;

注意：第二种比第一种容错性好，工作中建议使用第二种

3.2SQL

3.2.1MySQL数据类型

整数类型

tinyint 大小8bit

smalint 大小 16bit

mediumint 大小 24bit

int 大小32bit

bigint 大小64bit

小数类型

float 单精度浮点型

double 双精度浮点型

decimal 货币

日期时间类型

date 日期 年月日

datetime 日期时间 年月日时分秒

字符串类型

char(大小)

varchar(大小)

注意：MySQL没有varchar2类型

工作中如果字符长度超过500，建议不要使用varchar, 需要使用text

text

longtext

boolean类型

bit 0/1

3.2.2创建表

create table 表名称(

列名称 数据类型 auto_increment, --该列为自动增长

列名称 数据类型,

列名称 数据类型,

..............

)ENGINE=INNODB,DEFAULT CHARSET=字符集;

-- ENGINE=INNODB 指定表的存储引擎 ，支持外键、支持事务、支持行级别锁-- DEFAULT CHARSET=字符集 设置表中的数据以何种字符编码

create table tb_class(

id int(11) auto_increment,

class_name varchar(30) not null,

primary key (id)

)ENGINE=INNODB,DEFAULT CHARSET=utf8;

小结：MySQL可以在创建表的时候完成自动增长：auto_increment

一旦你将某个列设置为自动增长，必须将该列定义为主键列，例如：id为自动增长，并且id为主键

Oracle默认为手动提交，MySQL默认是自动提交。

根据客户需求创建History、Worker、Customer表

create table worker(

id int(11) auto_increment,

worker_name varchar(30) not null,

worker_salary int(5) not null,

worker_ticheng double not null,

worker_image varchar(100), # 图片的路径primary key(id)

)ENGINE=INNODB,DEFAULT CHARSET=utf8;

create table customer(

id int(11) auto_increment,

cust_name varchar(30) not null,

cust_address varchar(30) not null,

cust_mobile varchar(30) ,# 客户联系方式cust_ticket int not null, #客户水票primary key(id)

)ENGINE=INNODB,DEFAULT CHARSET=utf8;

create table history(

id int(11) auto_increment,

cust_id int(11),

worker_id int(11),

send_water_time datetime not null, #送水时间send_water_count int(5) not null, # 送水数量primary key(id),

-- 创建外键foreign key(cust_id) references customer(id),

foreign key(worker_id) references worker(id)

)ENGINE=INNODB,DEFAULT CHARSET=utf8;

插入数据：

insert into customer(cust_name,cust_address,cust_mobile,cust_ticket)values('Scott','ShangHai','15698789871',10);

-- 在一个insert语句插入多行insert into customer(cust_name,cust_mobile,cust_address,cust_ticket)

values

('Tom','1385678987','ShangHai',120),

('Jerry','1385678985','ChongQin',200),

('Jim','1385678957','DaLian',102),

('Terry','1385674987','QHD',103),

('Carry','1385673987','WH',105),

('William','1383678987','SZ',106),

('Jack','1387678987','GZ',107),

('Jason','1585678987','CD',108),

('Simon','1885678987','ty',105),

('Tune','1395678987','st',105);

insert into worker(worker_name,worker_salary,worker_ticheng)values

('Lucy',1500,1.5),

('Lili',1600,2),

('Adam',1200,1),

('Bob',900,1),

('Merry',1800,1),

('Nancy',2100,1),

('Robert',1900,1.5),

('Edwin',1800,1),

('Frank',1800,1);

insert into history(cust_id,worker_id,send_water_time,send_water_count)values

(1,1,'2010-01-01',100),

(1,1,'2010-01-02',121),

(1,2,'2010-01-03',122),

(1,3,'2010-01-04',113),

(2,5,'2010-01-05',111),

(3,4,'2010-01-06',113),

(3,3,'2010-01-07',116),

(4,6,'2010-01-08',116),

(5,7,'2010-01-08',114),

(6,1,'2010-01-09',11),

(7,2,'2010-11-10',12),

(8,3,'2010-01-11',13),

(5,1,'2010-01-12',101),

(5,3,'2010-01-13',106),

(2,8,'2010-01-14',156),

(3,1,'2010-01-16',176),

(4,7,'2010-01-13',143),

(5,6,'2010-01-18',184),

(6,5,'2010-01-21',132),

(7,4,'2010-01-22',132),

(6,3,'2010-01-31',131);

3.2.3返回限制的函数limit

-- 查询限制的函数 第一条到第五条-- 显示第10行之后的5行数据select * from history limit 10,5;

-- 显示第0行之后的5行数据select * from history limit 0,5;

-- 只显示1行数据select * from history limit 1;

工作中：limit主要用于分页，例如：百度的分页

注意：limit关键字是MySQL数据库的一个方言

3.2.4MySQL的列别名

-- 统计每个送水工送了多少通水-- Tom 10-- Jerry 20-- 需要用到Worker(worker_name)和History(send_water_count)表-- 对worker_name进行分组 ， 对send_water_count进行汇总select w.worker_name workerName,

-- 如果送水工没有给客户送水，默认为0-- oracle nvl() mysql ifnull() 判断值是否为null，如果为空给一个默认值ifnull(sum(h.send_water_count) ,0)WaterCount

from worker w left join history h on w.id = h.worker_id

-- mysql的group by支持列别名group by workerName;

3.2.4MySQL多表查询

场景：统计 XX送水工在某年某月某日为XX客户送了多少桶水

-- Worker(worker_name)Customer(cust_name)History(send_water_timer,send_water_count)-- 三张表如何做多表联合查询select w.worker_name workerName,

ifnull(h.send_water_time,'NONE') sendWaterTime,

ifnull(c.cust_name,'NONE') custName,

ifnull(h.send_water_count,0) sendWaterCount

from worker w left join history h on w.id = h.worker_id

left join customer c on c.id =h.cust_id;

场景：统计某个送水工为那些客户送过水

-- 送水工 客户列表 送水数量-- TOm Jerry,Merry,Terry 20-- group_concat(distinct cust_name) 分组并且连接 distinct cust_name去掉重复的名称-- group_concat里面包含了聚合函数所以不用再group by中定义select w.worker_name WorkerName,

ifnull(group_concat(distinct c.cust_name),'-') CustomerList,

sum(h.send_water_count) SendWaterCount

from worker w left join history h on w.id= h.worker_id

left join customer c on c.id= h.cust_id

group by WorkerName;

计算工资：

-- 送水工 底薪 提成 送水数量 实发工资-- Tom 1000 1.5 100 1150-- worker(worker_name,worker_salary,worker_ticheng) history(send_water_count)-- 工资计算：送水数量*每桶水提成+底薪select w.worker_name,w.worker_salary,w.worker_ticheng,

sum(h.send_water_count)SendWaterCount,

sum(h.send_water_count*w.worker_ticheng)+w.worker_salary FinalSalary

from worker w left join history h on w.id=h.worker_id

-- group by w.worker_name,w.worker_salary,w.worker_ticheng;-- MySQL 方言做了足够的简化 ，只需要在group by后面加上一个唯一标识group by w.id;

统计：工资最高的某一个送水工信息

select w.worker_name,w.worker_salary,w.worker_ticheng,

sum(h.send_water_count)SendWaterCount,

sum(h.send_water_count*w.worker_ticheng)+w.worker_salary FinalSalary

from worker w left join history h on w.id=h.worker_id

-- group by w.worker_name,w.worker_salary,w.worker_ticheng;-- MySQL 方言做了足够的简化 ，只需要在group by后面加上一个唯一标识group by w.id

order by FinalSalary desc;

3.2.5MySQL日期和字符串转换

MySQL中日期类型和字符串类型可以相互转换

场景：查询2010-01-01 ~ 2010-01-10之间所有的送水历史信息

select id,worker_id,cust_id,send_water_count,send_water_time

from history

where send_water_time between '2010-01-01' and '2010-01-10';

3.2.6备份表

场景：customer表很重要，为其创建一张备份表

备份语法

create table 备份表

as

select 原始表；

-- 表结构和表中的数据都备份了

create table customer_bak

as

select * from customer;

-- 只备份表结构不备份表中的数据

-- 不会备份主键列

create table customer_bak2

as

select * from customer

-- 1<>1 条件永远都是false，就不会查询出数据

where 1<>1;

-- 只备份表结构，不备份表数据

-- 备份主键列

-- 语法：

-- create table 备份表 like 原始表;

create table customer_bak3 like customer;

3.2 存储过程

就是Oracle的过程

delimiter $$

create procedure 存储过程名称 (参数类型 变量名 数据类型(长度))

begin

-- 过程体

end;

$$ delimiter

注意：MySQL存储过程以delimiter $$开始，以$$ delimiter结束

场景：计算工资 送水工 底薪 提成 送水数量 实发工资

要求：统计某一段时间的工资 ’2010-01-01‘ ’2010-01-31‘

delimiter $$

create procedure calcSalary3(in beginTime varchar(30) , in endTime varchar(30))

begin

select w.worker_name,w.worker_salary,w.worker_ticheng,

--没有送水的工人，送水总数为0

ifnull(sum(h.send_water_count),0)SendWaterCount,

-- 没有送水的工人，默认就是基本工资

ifnull(sum(h.send_water_count*w.worker_ticheng)+w.worker_salary,w.worker_salary) FinalSalary

from worker w left join history h on w.id=h.worker_id

-- 除了在区间时间段送水的工人，还要查询不在区间段的工人

where send_water_time between beginTime and endTime or h.worker_id is null

group by w.id

order by FinalSalary desc;

end;

$$ delimiter;

如何执行存储过程：

-- oracle exec 过程名(参数列表)；

-- mysql call 过程名(参数列表);

call calcSalary('2010-01-01','2010-01-31');

小结：mysql存储过程必须以 MySQL存储过程以delimiter $$开始，以$$ delimiter结束

mysql存储过程没有as

mysql存储过程参数：参数类型 变量名 数据类型， 数据类型必须填写长度

mysql存储过程没有游标的情况下也可以返回多行

3.3 触发器

场景：customer表的数据很重要，但我向customer表插入一行数据同时将数据同步到备份表中。

触发器语法：

delimiter $$

create trigger 触发器名称

触发时间

触发事件

on 表名称

触发级别 -- for each row

begin

-- 触发语句

end;

$$ delimiter

delimiter $$

create trigger trigger_customer_bak

after -- 在customer表插入数据之后触发

insert -- 触发的是insert事件

on customer -- 在customer表上触发

for each row -- 没插入一行就会出发一个事件

begin

-- 当customer表插入一行数据会触发一个insert事件，将customer表的新插入的数据同步到customer_bak3表

insert into customer_bak3(id,cust_name,cust_address,cust_mobile,cust_ticket)values

(new.id,new.cust_name,new.cust_address,new.cust_mobile,new.cust_ticket);

end;

$$ delimiter

作业：向history表插入一行数据(送水数量为20)，对应的customer就会减少多少张水票(减少20张水票)

1、查找部门30中员工的详细信息。

-- 2、找出从事职员工作的员工的编号、姓名、部门号。

-- 3、检索出奖金多于基本工资的员工信息。

-- 4、检索出奖金多于基本工资60%的员工信息。

-- 5、找出姓名中包含A的员工信息。

-- 6、找出姓名以A、B、S开始的员工信息。

-- 7、找到名字长度为7个字符的员工信息。

-- 8、名字中不包含R字符的员工信息。

-- 9、返回员工的详细信息并按姓名升序排序。

-- 10、返回员工的信息并按姓名降序,工资升序排列。

-- 11、计算员工的日薪(按30天)。

-- 12、找出获得奖金的员工的工作。

-- 13、找出奖金少于100或者没有获得奖金的员工的信息。

-- 14、找出10部门的经理、20部门的职员 的员工信息。

-- 15、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。

-- 注意：员工如果没有特殊指明，指的就是所有人。

-- 分组查询

-- 1、返回部门号及其本部门的最低工资。

-- 2、查询员工姓名和年薪，并且按年薪降序排序。

-- 3、返回员工工作及其从事此工作的最低工资。

-- 子查询

-- 4、查找和SCOTT从事相同工作的员工信息

-- 5、工资水平多于JAMES的员工信息。

-- 6、返回工资大于平均工资的员工信息。

-- 7、返回销售部(SALES)所有员工的姓名。

-- 8、返回工资高于30部门所有员工工资水平的员工信息。

-- 多表连接

-- 9、返回拥有员工的部门名、部门号。

-- 显示内连接

-- 省略inner

-- 隐式内连接

-- 10、返回员工的姓名、所在部门名及其工资。

-- 11、返回从事职员工作的员工姓名和所在部门名称。

-- 12、返回部门号、部门名、部门所在位置及其每个部门的员工总数。

-- 13、返回员工(职员或者销售员)和所属经理的姓名。

-- 14、返回员工(职员或者销售员)的入职日期早于其经理入职日期的员工及其经理姓名。

-- 15、返回最高工资和最低工资的职员信息

-- 16、返回工资处于第四级别的员工的姓名和工资。

Mysql存储引擎 MySQL索引 MySQL优化 MySQL事务