Oracle 补充
一．DDL命令补充
删除表
drop table 表名; --删除表
drop table 表名 cascade constraint; --删表的同时 级联删除 所有约束
修改表
1、添加列
alter table 表名 add(列名 数据类型 [默认值] [约束1] [约束1],
列名2 数据类型2)
2、删除列
alter table 表名 drop (字段名1，字段名2)
3、修改列名
alter table 表名 rename column 旧名 to 新名
4、修改列的其他属性
alter table 表名 modify (列名 数据类型 [默认值] [约束1] [约束1],
列名2 数据类型2)
5、添加约束
alter table 表名 add 约束类型(列名1,列名2)
--示例
alter table students30_FK add unique(name) --Unique
alter table students30_FK add foreign key (clazzid) references clazz(clzid) --FK
6、删除约束
alter table 表名 drop constraint 约束名;
--建表时，添加约束同时指定约束名
create table 表名(
列名 数据类型 [constraint 约束名 ] primary key
)
二．Case…when语法结构 ( 自学 )
语法 case…when : 分支流程 , 类似于java里switch…case.
case when boolean表达式(条件1) then 结果1
when 条件2 then 结果2
…
else 结果n
end
注意: 1. else不是必须的, 如果所有条件都不满足,语法结构的结果为null.
\2. 结果值可以是数字,字符串,日期. 但一定不能是boolean
\3. 所有结果类型必须一致
例如: —打印员工性别( 员工编号是奇数为男士,反之为女), 提示mod(n1,n2)
select employee_id,first_name,
case when mod(employee_id,2)=0 then ‘女’
when mod(employee_Id,2)=1 then ‘男’
else ‘不男不女’
end
from employees;
— 打印员工编号,名,工资,工资级别( A: <=6000 B: <=10000 C: >10000 )
select employee_id,first_name,salary,
( case when salary<=6000 then ‘A’
when salary<=10000 then ‘B’
else ‘C’ end
) “级别”
from employees;
三．常见单行函数
与字符串相关的函数:
\1. length(str) — 求str的长度
— 打印’String’的长度
select length(‘String’) from dual;
— 打印姓是由四个组成的员工信息
select * from employees where last_name like ‘____’;
select * from employees where length(last_name)=4;
\2. substr(str,begin,length) — 对str进行截取,从begin位置开始, 结果length个字符.
注意: str的下标从1开始
— select substr(‘String’,3,4) from dual; —> 结果: ring
select substr(‘String’,-3,2) from dual; —> 结果: in
— 打印名是以’S’开头的员工信息
select * from employees where first_name like ‘S%’;
select * from employees where substr(first_name,1,1)=‘S’;
\3. instr(str1,str2,begin) — 在str1里查找str2,从begin处开始. 如果找到返回str2
首字母下标, 反之返回0
— select instr(‘abcdabcd’,‘bc’,1) from dual; --> 结果: 2
\4. lower(str)/ upper(str) — 大小写转换
— 查询名是以’s’或’S’开头的人
select * from employees where lower( substr(first_name,1,1) ) = ‘s’;
注意: sql里函数也可以嵌套调用,只要参数类型匹配即可. 从里向外逐层执行
数字类型相关函数:
\1. abs(num) — 获取num的绝对值
\2. mod(num1,num2) — 计算 num1%num2的结果
— 打印员工编号为奇数的员工信息
select * from employees where mod(employee_id,2)=1;
\3. trunc(num1,num2) : 对num1做向下截断操作,保留小数点后num2位
round(num1,num2) : 对num1做四舍五入操作,保留小数点后num2位
— select round(3.1415,2) from dual; ----> 结果: 3.14
select round(3.1415,3) from dual; ----> 结果: 3.142
\4. dbms_random.random() — 获得一个随机整数( 很大的,可正,可负 )
— 请随机获得一个0~100之间的随机数
select mod( abs( dbms_random.random() ) ,101) from dual;
— 请随机打印表里的一个员工信息( 重点: 获取一个100~207之间的随机数)
select * from employees
where employee_id =mod( abs(dbms_random.random()) ,108)+100;
日期类型相关函数:
\1. sysdate — 表示当前系统时间
— 打印当前系统时间
select sysdate from dual;
注意: oracle默认的日期格式为 dd-mon-rr
\2. months_between(time1,time2) — 计算两个时间之间相隔几个月(结果为小数)
\3. last_day(time) — 计算当前月的最后一天
\4. add_months(time,num) — 在time的基础上加上num个月
\5. round(time,‘截取方式’) — 四舍五入
trunc(time, ‘截取方式’) — 向下截断
截断方式: ‘year’ ‘month’ ‘day’(星期) 什么都不写(回到某一天的零时)
— 打印当前二月的最后一天
last_day( add_month( trunc(sysdate,‘year’) , 1 ) )
类型转换相关函数
\1. to_number( str值 , 字符串格式 ) — 将给定的str按照指定格式转换成数字类型值
— select to_number(’$24,000.00’,’$99,999.00’) / 22 from dual;
select to_number(‘12345’) / 22 from dual;
\2. to_char() — 将其他类型值转换成指定格式的字符串
1) to_char(num,‘字符串格式’) : 将数字转换成字符串
— 按照科学计数法显示员工工资
select employee_id,first_name, to_char( salary,’$999,999.00’)
from employees;
注意: 占位数字可以用9或者0, 位数不够时,0会补齐
如果数字范围超出字符串表示能力,结果显示#####
处理null的函数:
nvl( 值1 , 值2 ) — 处理null,如果值1不为null,函数结果为值1,反之,结果为值2
— 打印员工本月工资( salary+salary*提成 )
select employee_id, salary+salary*commission_pct from employees;
如果提成为null, 数学表达式的结果为null
select employee_id,salary+salary*nvl(commission_pct,0)
from employees;
— select nvl(‘hehe’,‘haha’) from dual; —>结果:hehe
select nvl(null,‘haha’) from dual; —> 结果haha
四．集合运算符
1. union ：合并两个查询结果，重复数据只保留一份
– select * from employees where department_id in(70,80)
union
select * from employees where department_id in(80,90); --> 70,80,90
2. union all: 合并查询结果，保留重复数据
– select * from employees where department_id in(70,80)
union all
select * from employees where department_id in(80,90); --> 70,80,80,90
3. minus : 求差集，用result1-result2 ，以result1为主
– select * from employees where department_id in(70,80)
minus
select * from employees where department_id in(80,90); --> 70
4. intersect：求交集
– select * from employees where department_id in(70,80)
intersect
select * from employees where department_id in(80,90); --> 80
注意：1. 执行效率差
\2. 要求所有查询结果的列数，列的数据类型必须一致
3. 合并后的结果，它的列名，列的类型以第一个result为准。
提示： 行列转换
1） 行—列 ：使用集合运算符
2） 列—行【重点】：case…when 和 组函数
五．常见的dos命令
1.修改管理员密码:
1)进入dos命令行
2)输入 sqlplus /nolog
3)输入 conn sys as sysdba
4)无需输入口令,直接回车
5)连接成功后,输入以下命令
alter user system identified by 密码;
注意密码不能以数字开始
6)修改成功后退出
2.命令行解锁hr用户
1)进入dos命令行
2)输入 sqlplus /nolog
3)输入 conn sys as sysdba
4)无需输入口令,直接回车
5)连接成功后,输入以下命令
alter user hr account unlock;
6)alter user hr identified by 密码;
注意密码不能以数字开始
6)修改成功后退出
3.start命令
-
可以将需要执行的多条sql命令保存到以"***.sql"为结尾的文件里
-
需要执行时,首先先进入dos命令行
-
输入sqlplus 用户名/密码
-
登陆成功后,输入命令"start sql文件的绝对路径",执行指定的sql文件
4.修改oracle主页的默认端口
1)运行cmd;
2)输入sqlplus / as sysdba
3)输入exec dbms_xdb.sethttpport(8087);
或者 begin dbms_xdb.sethttpport(8087) end;