Oracle学习笔记——第五天

oracle学习笔记——第五天

一．DDL命令补充

1）删除表

   drop table 表名;   --删除表
   drop table 表名 cascade constraint;  --删表的同时 级联删除 所有约束

2）修改表

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）与字符串相关的函数:

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里函数也可以嵌套调用,只要参数类型匹配即可. 从里向外逐层执行

2） 数字类型相关函数:

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; 

3）日期类型相关函数:

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 )  )

4） 类型转换相关函数

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会补齐
如果数字范围超出字符串表示能力,结果显示#####

5）处理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命令

1. 可以将需要执行的多条sql命令保存到以"***.sql"为结尾的文件里
2. 需要执行时,首先先进入dos命令行
3. 输入sqlplus 用户名/密码
4. 登陆成功后,输入命令"start sql文件的绝对路径",执行指定的sql文件

4.修改oracle主页的默认端口

1)运行cmd;
2)输入sqlplus / as sysdba
3)输入exec dbms_xdb.sethttpport(8087);
或者 begin dbms_xdb.sethttpport(8087) end;