sql

sql

实例（sqldeveloper/plsql工具连上oracle实例）操作数据库(dbf文件)

SQL 92/99分类

DML（CRUD）/DDL（create drop alter）/DCL(grant revoke)/TCL(rollback commit )

登陆

sqlplus / as sysdba(超级管理员)

sqlplus scott/tiger（普通用户）

清屏

host cls

查询

语句：；

命令：可以不写； 可以简写

必须有from

记录命令以及命令结果

spool e:/oracle-day01.sql;

spool off;

从硬盘读取文件依次执行

@ e:/crm.sql

注释

单行--

多行 /* */

null

null不显示（只要有null）：null与具体数字运算时，结果为null

NVL(a,b)

if a==null 返回b

if a!=null 返回a

查询别名

select empno "编号"，ename "姓名",sal "工资" from emp;

""可省：则别名中间不可以有空格

查询某列（去重）

select distinct job from emp;

select where字句

where ename='smi';(大小写敏感)

where hiredate='17-12月-08'

大于 >

不等于 != 或者 <>

 

1300-1600之间（包括） between x and y 或者 （sal>=1300）and (sal<=1600)

不在1300-1600（不包括） not between x and y

不在 1981-2月-20 到 1982-3月-5 between '20-2月-1981' and '5-3月-1982'

 

20或者30 符合括号之一则成功in(20,30) or

不是20或者30 not int

 

以大写字母S开头 like 'S%'

以大写字母S结束 like '%S'

姓名为4个字符 第二个字符是I like '_I__'(_表示1个字符)

查询姓名含有_的员工 like '%\_%' escape '\'

 

佣金为null is null

！null不能参与精确=运算 null能参与任何类型运算 number/date/varchar

 

orderby字句

order by sal asc(升序)

order by sal desc(降序)

数字 日期，列名，别名，表达式，列号（5 desc,第几列）都可排序

null 看作最大值

 

order by sal desc,hiredte desc

只有在sal相同的情况下，hiredate才会排序

 

 

'123'--->123(可以转)

函数

单行函数：只有一个参数输入，只有一个结果输出

多行函数/分组函数：可有多个参数输入，只有一个结果输出

单行函数

lower 转小写 upper 转大写 initcap首字母大写

concat2个字符串的连接 concat('hello','你好')；参数只有2个

||字符串的连接 无限

substr(''，1,5) 1：从第几个字符开始取，中英文统一处理 5：连续取几个字符

length几个字符，中英文统一处理

lengthbyte几个字节

instr('',w) 字符w从左到右首次出现的位置（有的话），找不到返回0，大小写敏感

Lpad('hello',10,'#') #####hello 不足10位补足

Rpad('hello',10,'#') hello#####

Lpad('hello',3,'#') hel 超过3位截取

trim() 去掉两边，中间不去 trim('o' from 'oohoooffoo')

replace('hello','l','L') 替代，找不到原样输出

 

round(3.1415,3) 四舍五入小数点后3位

trunc(3.1415,3) 截取小数点后三位

mod(10,3) 10%3

 

round，trunc也可用于日期型（sysdate当前日期）

select sysdate from dual(sysdate 常量)

round(sysdate ,'month') 25-JUL-95----------> 01-AUG-95

round(sysdate,'year') 25-JUL-95----------> 01-JAN-96(月归1，一月)

2015-4-26 年 2015-1-1

2015-7-1 年 2016-1-1

2015-4-26 月 2015-5-1

trunc

2015-4-26 month 2015-4-1

2015-4-26 year 2015-1-1

 

 

日期四则运算

时间

mysql 当前时间 select now()

oracle select sysdate from dual

昨天，今天，明天 sysdate-1，sysdate，sysdate+1

months_between（‘31-12月-15’，sysdate） 精确到年底有多少月

months_between（sysdate，hiredate） 精确月工龄

add_month(sysdate,1) 下月今天日期

add_month(sysdate,-1) 上月今天日期 2015-3-31 2015-2-28

next_day(sysdate.'星期三') 下个星期三的日期2015-4-26日 2015-4-29

last_day（sysdate） 本月最后一天

 

日期+-天数=日期

日期-日期=天数

 

 

三大类型转换

隐式

显式

隐式 字符串-->数字 、日期（'123'）

日期----->字符串(17-12月-80)

123||'123' 123123 123-->'123'(数字转字符串)

varchar

varchar2存任何语言文字 变长 指可以由长变短

char 定长：补满

 

显式

to_char('日期'，'格式')（格式中大小写无所谓）

to_char(sysdate,'yyyy"年"mm"月"dd"日"day') 2015 04 26 星期日 “”里是常量

to_char(sysdate,'yyyy mm dd day hh24:mi:ss')

to_char(sysdate,'yyyy mm dd day hh12:mi:ss AM')(AM PM自动显示)

to_char(数字,'格式')(常量用" ")

to_char(1234,'$9,999') $1,234

to_date('字符串'，'格式')(常量用" ")

to_date('1980#12#1'，'yyyy"#"mm"#"dd')

to_date('1980-12-1'，'yyyy-mm-dd')

to_number('字符串') 字符串转数字

to_number('123')

 

NVL(a,b) a为null 用b代替 否则为a

 

case 字段

when 条件 表达式

when 条件 表达式

end

dual

哑表(伪表)：只有1行

字符串连接符||

select ‘hello’ | |'word' from dual

select ename || '的薪水是' || sal '美元'

单引号出现地方

1.字符串型 ‘hello’

2.日期型 '25-4月-15'

双引号出现地方

别名

 

插入

insert into emp(,) values();

插入姓名叫 ' insert into emp(ename) values(''''');

' '' '('' 相当于 ')

插入姓名叫 '' insert into emp(ename) values('''''');

' '' '' '('' '' 相当于'')