Mysql & oracle

最新推荐文章于 2023-09-04 12:40:32 发布

大脸猫aaaaa

最新推荐文章于 2023-09-04 12:40:32 发布

阅读量179

点赞数

文章标签： mysql oracle

本文链接：https://blog.csdn.net/weixin_43832730/article/details/111384092

版权

Mysql & Oracle

常用的数据库

mysql、oracle、sqlServer、mongodb

数据类型

数字类型

mysql

1、tinyint：占用1个字节，相对于java中的byte

2、smallint：占用2个字节，相对于java中的short

3、int：占用4个字节，相对于java中的int

4、bigint：占用8个字节，相对于java中的long

5、float：4字节单精度浮点类型，相对于java中的float

6、double：8字节双精度浮点类型，相对于java中的double

oracle

number()：定义为number(precision,scale)

p是数字的总位数，取值1-38；

s是小数点后面的位数。

例如：salary number(6,2) 整数位是4位，小数位是2位。

变种数据类型：用number实现，为了和其他数据库兼容

numeric(p,s)：number(p,s)

decimal(ps)或dec(p,s)：number(p,s)

int：number(38)

smallint：number(38)

float(b)：number

double：number

字符类型

mysql

1、char(n) 定长字符串，最长255个字符。n表示字符数

2、varchar(n) 变长字符串，n表示字符数，最长不超过65535个字符，当超过255个字符时，会转化成text类型，例如：

iso8859-1码表：一个字符占用1个字节，1n < 65535， n最多等于 65535
utf8码表：一个中文汉字占用3个字节， 3n < 65535，n最多等于 65535/3
GBK码表：一个中文汉字占用2个字节， 2n < 65535，n最多等于 65535/2

3、text 大文本（长文本）类型

最长65535个字节，一般超过255个字符列的会使用text。

oracle

默认是字节B，可指定为字符

char(10) == char(10 byte)

指定为字符时，char(10 char) = 20B，(java中char是2B，c中char是1B)

一个英文字符占用1B

一个中文字符 GBK：2B，utf-8：2-4B

1、char(n) 定长字符类型，n表示字节数，最大长度2000B。

2、varchar2(n) 变长字符类型，n表示字节数。最大长度是4000B。

3、long 变长字符类型，存储2GB，long的限制很多，每个表只能有一个LONG字段，不能作为主键、不能建立索引、不能出现在查询条件中

4、clob 存储定长或变长字符串类型，最多存储4GB，建议使用clob

日期类型

mysql

date()：格式：YYYY-MM-DD，支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’

time()：格式：HH:MM:SS，支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’

datetime()：格式：YYYY-MM-DD HH:MM:SS，支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’

timestamp()：时间戳(实际存储的是一个时间毫秒值)，TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。

支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC

与datetime存储日期格式相同。两者的区别是：

timestamp最大表示2038年，而datetime范围是1000~9999
timestamp在插入数据、修改数据时，可以自动更新成系统当前时间

oracle

date 7个字节，最多精确到秒，默认格式：DD-MON-RR，例如：11-apr-20，18-4月-17

世纪+100 年月日小时+1 分+1 秒+1

1 2 3 4 5 6 7 （字节）

timestamp 时间戳类型，7或11个字节，最多精确到ns，精度为0，7个字节存储，精度>0，用11个字节存储

多出的4个字节存储纳秒。

sysdate函数 返回当前系统时间，精确到秒，默认格式：DD-MMM-RR

systimestamp函数 返回当前系统时间，精确到ms

oracle字符串函数

concat(char1,char2) 和 ||

-- concat(char1,char2)
select concat( concat(name,':'),salary) 
from employee;

-- || 可以连接字符串
select name || ':' || sal
from employee;

-- length() 字符串长度
select name length(name)
from employee;

upper() lower() initcap()

-- upper() lower() initcap()
-- upper(char) 转化为全大写
-- lower(char) 转化为全小写
-- initcap(char) 将字符串首字母大写，其他小写，单词之间空格隔开
-- 伪表 dual，不是一张真实存在的表，当查询的数据和任何表没关系时使用。
select upper('hello world'),lower('HELLO WORLD'),initcap('hello world')
from dual;
-- 例2：查看rose的信息
select name,sal,job,deptnum from emp
where name=lower('ROSE');

trim() ltrim() rtrim()

-- trim() ltrim() rtrim()
-- trim(c2 from c1) 从字符串c1前后去掉c2 ，c2只能是一个字符
-- ltrim(c1[,c2])   从字符串c1的左边去掉c2
-- rtrim(c1[,c2])   从字符串c1的右边去掉c2
select trim('e' form 'eeebiteee') from dual; -- 得到bit，只能去除一个字符
select trim('es' form 'esesesbiteseses') from dual; -- 报错

？？？？ select ltrim('qqweewqlitee','qwe') from dual; -- 得到litee，把字符串中含有qwe的字符全部去掉
？？？？ select rtrim('eelitqqweewq','qwe') from dual; -- 得到eelit，把字符串中含有qwe的字符全部去掉

lpad() rpad()

-- lpad() rpad() 补位函数
-- lpad(char1,n,char2) 在char1的左边用char2补足到n位，char2能重复多次
-- rpad(char1,n,char2) 在char1的右边用char2补足到n位，char2能重复多次
select name,lpad(salary,6,'$') from employee;
select name,rpad(salary,6,'$') from employee;

select name,lpad(salary,6,' ') from employee; -- 左边加空格，右对齐
select name,rpad(salary,6,' ') from employee; -- 右边加空格，左对齐

select name,lpad(salary,3,'$') from employee; -- 从左边开始计数，去掉最后一位
select name,rpad(salary,3,'$') from employee; -- 从左边开始计数，去掉最后一位

substr(char,m[,n])

-- substr(char,m[,n]) 用于获取字符串字串，含头含尾，下标从1开始,
-- 					java中是从m位到n位，含头不含尾，下标从0开始
-- 从m位开始截取，截取n个字符；若m=0，从首字符开始；若m为负数，从尾部-1开始计数。
-- 若没有设置n，或者n的长度超过了char的长度，取到字符串末位为止
select substr('think in java',10,2) from dual; -- 得到in
select substr('think in java',-7,2) from dual; -- 得到in

instr(char1,char2[,n[,m]])

-- instr(char1,char2[,n[,m]]) 返回char2在char1的位置
-- 从n的位置开始搜索，若n为null，从1开始
-- m用于指定字串第m次出现的次数，若为null，m=1
-- 如果在char1中没找到char2，返回0
select instr('thinking in java','in',2) from dual; -- 得到3
select instr('thinking in java','in',4，2) from dual; -- 得到10

oracle数字函数

round(n[,m]) 四舍五入

-- m取正数，到小数点后第m位；
-- m取0，到整数位；m为null，默认是0
-- m取负数，到小数点前第m位；
select round(45.438,2) from dual;  -- 45.43
select round(45.438,0) from dual;  -- 45
select round(45.438,-1) from dual; -- -1是保留十位，50
select round(45.438,-2) from dual; -- -2是保留百位，0

trunc(n[,m]) 截取数字

select round(45.438,2) from dual;  -- 45.43
select round(45.438,0) from dual;  -- 45
select round(45.438,-1) from dual; -- -1是保留十位，50

mod(m,n) 取余数/求模

-- 返回m/n之后的余数；n=0直接返回m
select name,salary,mod(sal,1000) from employee;

ceil(n) 和floor(n)

-- ceil()  返回>=n 的最小整数
-- floor() 返回<=n 的最大整数
select ceil(45.844) from dual; 
select floor(45.844) from dual;

oracle日期函数

to_date()

-- to_date() 将字符串按照指定的日期格式转换为date类型
YYYY 4位数字的年
MM 两位数字的月
DD 2位数字的日
HH24 24小时制的小时
HH12 12小时制的小时
MI 分钟
SS 秒

select to_date('1992-08-03 15:22:33','YYYY-MM-DD HH24:MI:SS') from dual;

--在日期格式中，有中文用双引号
select to_date('1992"年"08"月"03"日" 15:22:33','YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual;

-- YY和RR的区别：YY不考虑世纪，RR考虑世纪
select to_char(to_date('92-08-03 15:22:33','YY-MM-DD HH24:MI:SS'),'YYYY-MM-DD') 
from dual; -- 得到2092-08-03
select to_char(to_date('92-08-03 15:22:33','RR-MM-DD HH24:MI:SS'),'YYYY-MM-DD') 
from dual; -- 得到1992-08-03，
select to_char(to_date('49-08-03 15:22:33','RR-MM-DD HH24:MI:SS'),'YYYY-MM-DD') 
from dual; -- 得到2049-08-03


假如插入时间是to_date('98-07-06','RR-MM-DD')，当前系统时间是2020年，插入的时间是1998还是2098？
答：插入的时间是2098年。

user\sys	0-49	50-99
0-49	本世纪	下世纪
50-99	上世纪	本世纪

to_char()

-- to_char() 将date按照给定日期格式转化为字符串
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

日期类型的计算

-- 查看明天的日期 
select sysdate+1 from dual;
-- 查看员工入职至今的时间(天)
select name, sysdate-hire_date from employee;

--last_day(date) 返回date所在月的最后一天
select last_day('20-02-09') from dual;
--查看当月月底
select last_day(sysdate) from dual;

-- add_months(date,i) 返回date加上i个月后的日期值
-- 计算入职20周年纪念日
select name,add_months(hire_date,20*12) as '20周年' from employee;

-- month_between(date1,date2) 计算date1和date2两个日期间隔了多少月
-- 若两个日期间隔时间不是整数月，会得到带小数位的结果
-- 计算员工入职时间（月）
select name,month_between(sysdate,hire_date) as '入职时间' from employee;

-- next_day(date,char) 返回date日期数据的下一个周几的日期
-- 查询下周三是几号？1-7分别对应周日、周一、周二
select next_day(sysdate,4) as '下周三' from dual;

-- greatest(s1,s2...) least(s1,s2...) 比较最大值和最小值，并返回所求值
-- 比较1997和当前时间，返回最小时间
select least(sysdate,to_date('1997-08-03 15:22:33','YYYY-MM-DD HH24:MI:SS'))
from dual;

-- extract(date from datetime) 从datetime中提取date指定的数据，比如年月日
-- 虽然date类型包含年月日 时分秒，但是只能提取年月日
-- timestamp类型能提取年月日 时分秒，
-- 查看今年是那一年？
select extract (year from sysdate) from dual;
-- 查看1980年入职的员工？
select name,hire_date 
from emp 
where extract(year from hire_date)=1980;

oracle的DDL

创建表

create table employee(
	id number(4),
	name varchar2(20) not null,
	gender char(1) default 'm', -- 默认是m，不设置默认是null
    birth date default 5000,  --默认是5000
    salary number(8,4),
    job varchar2(20) default devlop,
    dept_num number(2)
);

非空约束

not null，在任何情况下都不能为null

修改表

修改表名 rename old_name to new_name;

修改表结构

1) 添加新字段添加字段hire_date和account

alter table employee
add(
	hire_time date,
	phone_number number(11) not null
);

2) 删除已有字段删除hire_time

alter table employee
drop(
	phone_number
);

3) 修改字段，可以修改类型、长度、默认值、非空约束。

若表中已存在数据，尽量不修改类型，长度只能增不能减。

   alter table employee
   modify(
   	job varchar2(30) not null
   );

truncate

删除表数据，立即生效，无法rollback。

效率比delete高

oracle的DML

insert (2种)

insert into employee
(id, name,salary,dept_num)
values
(1,'jack',3000,10);

insert into employee
values
(2,'jack','f',sysdate,5000,'manager',20);

-- 使用to_date()函数
insert into employee
values
(2,'rose','f',to_date('1992-8-2','yyyy-mm-dd'),7000,'manager',20);
-- sql中不区分 yyyy-mm-dd 和 YYYY-MM-DD
-- java中MM是月份，mm是分钟，sql中mm MM都是月份，分钟是mi MI

update

update employee
set salary =8000,dept_num=30
where name='rose'

delete

delete from employee
where name='jack'

oracle的DQL

select

字段顺序：select, from, where, group by, having, order by, limit

group by 通常和聚合函数SUM()、AVG()、MAX()、MIN()、COUNT()等结合在一起，后接限制条件语句 having，不可用where语句！

-- 查看员工年薪
select name,job,salary,salary*12, 
from employee

as 别名

-- sql语句设置列名的别名，使用或不使用as均可。
-- 如果希望别名区分大小写，或者别名包含字符或者空格，必须用" "
select id  as "Id",name as "Name", sal*12 "Annual Salary" from employee;

where子句

-- 和数字类型比较，使用或不使用'' 均可
-- 和字符或date类型比较，必须使用''

-- 查询部门10下的员工信息
select * from emp where dept_num=10;
-- 查询职位是salesMan的员工
select name,salary,job from employee where job='salMan';

-- 使用 > < >= <= != <> =
-- 一般不使用 != ,用 <> 代替

-- 查询员工表中2015年7月1号以后入职的员工
select name,salary,hire_time 
from employee 
where hire_time >to_date('2015-7-1','YYYY-MM-DD');
-- 查询员工表中不属于部门10的员工信息
select name,salary,job 
from employee 
where dept_num <> 10;


-- 使用and or，and优先级高于or

-- 查询薪水大于5000并且职位是'develop'或'clerk'的员工
select name,salary,job 
from employee
where salary>5000 and job='develop' or job='clerk'; -- 此查询结果错误，因为数据库理解为工资>5000的job=develop加上job=clerk的员工，job=develop没有工资限制。
select name,salary,job 
from employee
where salary>5000 and (job='develop' or job='clerk');


-- 使用 in 和 not in

-- 查询职位是manager和clerk的员工
select name,salary,job from employee
where job in('manager','clerk');
-- 查询部门不是5或6的员工
select name,job,salary from employee 
where dept_num not in(5,6);


-- between...and... 查询符合某个范围的数据
-- 数字、字符串、日期均可

-- 查询工资在5000-9000之间的员工信息
select name,salary,job from employee 
where salary between 5000 and 9000;

like 模糊查询

-- %: 表示0个或多个字符
-- _: 表示1个字符

-- 查询名字是张xx的员工
select name,job from employee 
where name like '张%';

null 操作

-- 插入空值：略

-- is null  is not null
-- 判断空值：
select * from student where gender is null
-- 判断非空值：
select * from student where gender is not null

/*
	null的运算：
	与字符串相加为原字符串
	与数字相加结果为 null
*/

-- 查看员工总收入（工资+绩效）
select name,sal,comm,sal+comm as "总工资" 
from employee; 						--总工资为null，错误

-- nvl(arg1,arg2) 空值函数，将arg1替换为arg2
select name,sal,comm,sal+nvl(null,0) 
from employee;

-- nvl2(arg1,arg2,arg3)
-- 当arg1<>null时，返回arg2，arg1=null，返回arg3

-- 查看员工绩效，有绩效显示‘有绩效’，没绩效显示‘无绩效’
selct name,comm,nvl(comm,'有绩效','无绩效')
from employee;

any all

-- any all 不能单独使用，需要配合> < 等使用
> any: 大于最小
< any: 小于最大 
> all: 大于最大
< all: 小于最小

-- 查询工资>5000的员工
select name,job,salary from employee
where salary> any(5000,7000,10000);

distinct 去重

-- 去除重复值，distinct前不能有其他字段，

-- 单字段去重
--查看公司的所有职位
select distinct job from employee;

-- 多字段去重 单一字段有重复，多个字段组合没有重复的组合
select distinct job dept_num from employee;

order by 排序

-- asc 升序,desc 降序
-- null被认为时最大值

-- 单字段排序

-- 查询员工工资，按降序排列
select name,job,salary from employee
order by salary desc;

-- 多字段排序 
-- 有优先级，先按照第一个字段排序，当第一个字段有重复值时，按第二个字段排序，以此类推。

-- 按照部门号的升序，工资降序查询员工工资
select name,job,dept_num,salary from employee
order by dept_num asc,salary desc;

聚合函数

又称分组函数，查询时需要做数据统计，对结果集的指定字段进行统计得出结果。比如最大值，最小值，平均值，记录总数等。

所有聚合函数都忽略null

-- 查询最低工资和最高工资
select max(salary),min(salary) from employee;

-- 查询平均工资和总工资
select avg(salary),sum(salary) from employee;

-- 查看总员工数
select count(name) from employee;
select count(1) from employee; -- count(1) 不是count第一个字段，而是将第一个字段全部填上1

-- 聚合函数忽略null
-- 查询平均绩效
select avg(comm) from employee;  -- 错误，忽略了comm=null的员工
select avg(nvl(comm,0)) from emoloyee; --

大脸猫aaaaa

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql & oracle

Mysql & Oracle常用的数据库mysql、oracle、sqlServer、mongodb数据类型数字类型mysql1、tinyint：占用1个字节，相对于java中的byte2、smallint：占用2个字节，相对于java中的short3、int：占用4个字节，相对于java中的int4、bigint：占用8个字节，相对于java中的long5、float：4字节单精度浮点类型，相对于java中的float6、double：8字节双精度浮点类型，相对于java中的
复制链接

扫一扫