Oracle

1.Oracle数据库、实例、用户、表空间、表之间的关系

2.常见的数据库用户：sys、system、scott

3.sqlplus登录服务器： conn 用户名/密码[@服务命名] [as sysdba|sysoper]

　　查看系统变量 show all;

　　查看当前用户 show user;

　　查看错误 show error;

　　多行注释 /**/

　　单行注释 --

　　清屏 - 2种方式 clear screen; host cls;

　　退出 - exit

4.数据表组成：列（字段），行（记录），列值

5.结构化查询语言 (Structured Query Language)简称SQL，功能分类：

　　数据查询语言（DQL:Data Query Language）

　　数据操作语言（DML：Data Manipulation Language）

　　事务控制语言（TCL： Transaction Control Language）

　　数据定义语言（DDL： Data Definition Language）

　　数据控制语言（DCL：Data Control Language）

6.创建表空间

Create tablespace 表空间名

Datafile ‘文件路径’ size 大小

Autoextend on/off next 大小 maxsize 大小;

例如：

create tablespace rjl
logging
datafile 'D:\app\Administrator\oradata\rjl\rjl.dbf'
size 50m
autoextend on next 50m
maxsize 20480m ;

1）查询表空间

//查看所有的表空间

select * from dba_tablespaces;

//查看所有的表空间的文件

select * from dba_data_files;

2）修改表空间

//增加表空间文件

Alter tablespace 表空间名 add datafile ’文件路径’ size 大小

Autoextend on ;

//修改表空间文件的大小

Alter database datafile=’文件路径’ resize 大小;

//修改表空间的扩展大小和最大值

Alter database datafile=’文件路径’ autoextend on next 大小 maxsize 大小;

3）删除表空间

drop tablespace 表空间名；

Drop tablespace 表空间名 including contents and datafiles;

Plsqldev基本使用

7.权限

　　//1.所有用户所对应的权限

　　select * from dba_sys_privs;

　　//2.当前用户的权限

　　select * from user_sys_privs;

　　//3.所有角色对应的权限

　 select * from role_tab_privs;

8.用户

1）查询系统用户

//查询数据库里面所有的用户

select * from dba_users;

//查看当前用户能管理的所有用户

select * from all_users;

//查看当前账户

select * from user_users;

2）解锁用户

alter user xxx account lock/unlock;

3）创建用户

Create user 用户名 identified by 密码 default tablespace 表空间名;

4）修改用户密码

Alter user xxx identified by 新密码;

5）删除用户

Drop user 用户名 cascade;

9.角色

系统默认角色

Oracle提供了三种标准的角色（role）：connect、resource和dba。

1）创建角色的权限

grant create role to 用户名;

2）创建角色

Create role 角色名;

3）删除角色

Drop role 角色名;

4）查询角色

//查询所有角色

select * from dba_roles;

//查询当前用户所拥有的角色

select * from user_role_privs;

5）把角色赋给用户

Grant 角色名 to 用户名

10.DCL数据控制语言

1）授予语法

Grant 角色 [on 表名] to 用户;

Grant 操作 on 表名 to 用户;

2）授予

//授予用户连接权限 -- 用户在创建的时候有默认表空间。

Grant connect,resouce to 用户;

//授予用户的表的操作

Grant select,update on emp to 用户;

//授予某个角色权限

Grant connect,resource to 角色名；

//把角色赋给用户

Grant 角色名 to 用户名；

3）撤销

//撤销权限

Revoke connect,resource from 用户;

Revoke select,update from 用户;

//撤销角色

Revoke 角色名 from 用户名;

注意：在Oracle中，null和空字符串是等价的。

　　　在用avg,max,min这些函数时会把null（这一行记录）忽略掉

11.通用函数

/*
通用函数
*/

--nvl(参数1，参数2)：如果参数1不为空，返回参数1，如果为空，返回参数2
SELECT nvl(1,2) FROM dual;--1
SELECT nvl(NULL,2) FROM dual;--2

--nvl2(参数1，参数2，参数3)：如果参数1不为空，返回参数2，如果为空，返回参数3
SELECT nvl2(1,2,3) FROM dual;--2
SELECT nvl2(NULL,2,3) FROM dual;--3

--nullif(参数1，参数2)：参数1不等于参数2，返回参数1，如果相等，返回空
SELECT nullif(1,2) FROM dual;--1
SELECT nullif(2,2) FROM dual;--NULL

--coalesce(参数1，参数2，……，参数N)：返回第一个非空值，如果都为空，则返回空
SELECT coalesce(1,2,3,4,5) FROM dual;--1
SELECT coalesce(NULL,NULL,3,4,5) FROM dual;--3

12.字符函数

/*
字符函数
*/

--lower,upper：转换大小写
SELECT last_name,
       lower(last_name),
       upper(last_name)
FROM   employees;

--initcap：单词首字母大写，其余小写
SELECT initcap('This IS a book')
FROM   dual;

--concat(字符串1，字符串2)：拼接字符串
SELECT concat('hello','world')
FROM   dual;

--substr(字符串,起始位置[，截取个数])：截取一个字符串的子串，起始位置可以为负数（右数第N位）
SELECT substr('abcdefg',3) FROM   dual;--cdefg
SELECT substr('abcdefg',3,2) FROM dual;--cd
SELECT substr('abcdefg',-3) FROM   dual;--efg
SELECT substr('abcdefg',-3,2) FROM   dual;--ef

--length：返回字符串长度
SELECT length('abcd') from dual;--4
SELECT length('') from dual;--null

--instr(字符串,查找子串[，起始位置[,第几次出现]])：查找字符串中子串的起始位置，如果找不到返回0
--起始位置也可以为负数（从右向左反向搜索）
SELECT instr('abcdefg','cd') FROM dual;--3
SELECT instr('abcdefg','cdf') FROM dual;--0
SELECT instr('abcdefgcdefg','cd') FROM dual;--3
SELECT instr('abcdefgcdefg','cd',4) FROM dual;--8
SELECT instr('abcdefgcdefg','cd',-1) FROM dual;--8
SELECT instr('abcdefgcdefgcde','cd',1,2) FROM dual;--8

--lpad(字符串,固定长度,填充字符)：左填充
--rpad：同上，右填充
SELECT lpad('abcd',7,'#') FROM dual;--###abcd
SELECT lpad('abcd',3,'#') FROM dual;--abc
SELECT rpad('abcd',7,'#') FROM dual;--abcd###
SELECT lpad('',7,'*')     FROM dual;--null
SELECT lpad(' ',7,'*')    FROM dual;--****** 
SELECT lpad('abcd',7.9,'*')  FROM dual;--***abcd

--trim(关键字 from 字符串)：修剪字符串两边的关键字
SELECT trim('a' FROM 'aabcdaaxyza') FROM dual;--bcdaaxyz

SELECT 123||ltrim('   abcd   ')||456 FROM dual;--123abcd   456
SELECT 123||rtrim('   abcd   ')||456 FROM dual;--123   abcd456

SELECT rtrim('aabcdaaxyza','a') FROM dual;--aabcdaaxyz
SELECT ltrim('aabcdaaxyza','a') FROM dual;--bcdaaxyza

--replace(字符串,查找字符串[,替换字符串])：替换字符串中的子串，默认是替换为空字符串
SELECT replace('abcdefgabcd','cd') FROM dual;--abefgab
SELECT replace('abcdefgabcd','cd','#') FROM dual;--ab#efgab#

--chr：把编码转换为字符
SELECT chr(65) FROM dual;--A

--ascii：把字符转换为编码
SELECT ascii('A') FROM dual;--65
SELECT ascii('国') FROM dual;

http://www.zhimengzhe.com/shujuku/Oracle/248318.html

13.数学函数

/*
数学函数
*/

--round(数字[,小数位数])：按照指定小数位数，四舍五入，默认保留到整数位
SELECT round(3.1415927) FROM dual;--3
SELECT round(3.5415927) FROM dual;--4
SELECT round(3.5465927,2) FROM dual;--3.55

--trunc(数字[,小数位数])：截断到指定位数，不四舍五入，默认保留到整数位
SELECT trunc(3.1415927) FROM dual;--3
SELECT trunc(3.5415927) FROM dual;--3
SELECT trunc(3.5465927,2) FROM dual;--3.54
SELECT trunc(-3.5) FROM dual;---3

--floor(数字)：返回不大于本身的最大整数
SELECT floor(-3.5) FROM dual;-- -4

--ceil(数字)：进位取整
SELECT ceil(3.00000001) FROM dual;--4
SELECT ceil(3.0) FROM dual;--3

--mod(被除数，除数)：求模
SELECT mod(5,3) FROM dual;--2

14.日期函数

/*
日期函数
*/

--sysdate：返回当前系统的日期时间
SELECT sysdate FROM dual;

--日期类型和数字类型可以做加减运算：一个日期加减一个数字返回的还是一个日期(单位是天)
SELECT sysdate+3 FROM dual;
SELECT sysdate-100 FROM dual;
SELECT sysdate+1/24/60*25 FROM dual;--25分钟以后

--一个日期减去另外一个日期返回的是两个日期间隔的天数
SELECT hire_date,trunc(sysdate-hire_date) AS 间隔天数 FROM employees;

--months_between(日期1，日期2)：返回两个日期间隔多少个月
--查询每个员工的编号，姓名，入职日期，工龄
SELECT employee_id,last_name,hire_date,
       trunc(months_between(sysdate,hire_date)/12) AS 工龄
FROM   employees;

--add_months(日期，N)：给一个日期加减若干个月，返回一个新日期，N为正数表示加，负数表示减
SELECT add_months(sysdate,-15) FROM dual;

--查询入职超过20年的员工信息（months_between和add_months两种方式）
SELECT *
FROM   employees
WHERE  trunc(months_between(sysdate,hire_date)/12)>=20;
--
SELECT *
FROM   employees
WHERE  add_months(hire_date,20 * 12)<=sysdate;

--next_day(日期，星期几)：返回以指定日期为准，下一个最近的星期几的日期
SELECT next_day(sysdate,'星期五') FROM dual;

--可以用数字1~7代表日~六
SELECT next_day(sysdate,7) FROM dual;

--last_day(日期)：返回指定日期的月最后一天的日期
SELECT last_day(sysdate) FROM dual;

--round(日期[,日期单位])：对日期进行四舍五入
SELECT round(sysdate) FROM dual;
SELECT round(sysdate,'month') FROM dual;
SELECT round(sysdate,'year') FROM dual;

--trunc(日期[,日期单位])：对日期进行截断
SELECT trunc(sysdate) FROM dual;
SELECT trunc(sysdate,'month') FROM dual;
SELECT trunc(sysdate,'year') FROM dual;

15.转换函数

/*
转换函数
*/

--to_char(日期|数字,'模式')：把一个日期或者数字按照指定模式转换为字符串

SELECT '现在时间:'||to_char(sysdate,'yyyy-mm-dd') FROM dual;
SELECT '现在时间:'||to_char(sysdate,'mm/dd/yyyy') FROM dual;
SELECT '现在时间:'||to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') FROM dual;--10/28/2017 15:48:51
SELECT '现在时间:'||to_char(sysdate,'yyyy-mm-dd day') FROM dual;--1980-12-17 星期三
SELECT '现在时间:'||to_char(sysdate,'yyyy-mm-dd d') FROM dual;--数字模式1980-12-17 4
SELECT '现在时间:'||to_char(sysdate,'year-month-ddspth day') FROM dual;--单词模式
SELECT '现在时间:'||to_char(sysdate,'yyyy"年"mm"月"dd"日"') FROM dual;--双引号转义非法日期分隔符2017年10月28日
SELECT '现在时间:'||to_char(sysdate,'fmyyyy-mm-dd') FROM dual;--FM去掉多余的前导0或者空格2017-9-29

SELECT '现在时间:'||to_char(sysdate,'fmmm/dd/yyyy hh12:mi:ss') FROM dual;

SELECT '数字：'|| to_char(&input,'fm9990.99') FROM dual;--最大4位整数，两位小数，个位数必须有一个数字
SELECT '数字：'|| to_char(&input,'fm9990.0099') FROM dual;--最大4位整数，两位小数，个位数必须有一个数字，小数必须有两位
SELECT '数字：'|| to_char(&input,'fmL9990.0099') FROM dual;--本地货币符号
SELECT '数字：'|| to_char(&input,'fmL9,990.0099') FROM dual;--千分位

--to_date(日期字符串,'模式')：把一个日期字符串按照指定模式解析为一个日期型

select to_Date('2018-8-8 22:37:44','yyyy-mm-dd hh24:mi:ss') from dual;

--to_number(数字字符串,'模式')：把一个字符串解析为一个数字型
select to_number('000012134') from dual;  --12134
select to_number('88877') from dual;   

SELECT *
FROM   employees
WHERE  salary>to_number('$5,600','$9,999');
--
SELECT *
FROM   employees
WHERE  salary>to_number('￥5,600','L9,999');

16.条件语句

--if elsif else语句
DECLARE
  num NUMBER := -15;
BEGIN
  IF num > 10 THEN
    dbms_output.put_line('a');
  ELSIF num > 0 THEN
    dbms_output.put_line('b');
  ELSE
    dbms_output.put_line('c');
  END IF;
END;

--case语句
DECLARE
  num NUMBER := 2;
BEGIN
  CASE num
    WHEN 1 THEN
      dbms_output.put_line(11);
    WHEN  2 THEN
      dbms_output.put_line(22);
    WHEN  3 THEN
      dbms_output.put_line(33);
    ELSE
      dbms_output.put_line(110);
  END case;
END;

DECLARE
  num NUMBER := 2;
BEGIN
  CASE 
    WHEN num>1 THEN
      dbms_output.put_line(11);
    WHEN  num>2 THEN
      dbms_output.put_line(22);
    WHEN  num>3 THEN
      dbms_output.put_line(33);
    ELSE
      dbms_output.put_line(110);
  END case;
END;

17.循环语句

--简单的循环 loop
DECLARE
  num NUMBER;
BEGIN
  num := 1;
  LOOP
    num := num + 1;
    dbms_output.put_line(num);
    EXIT WHEN num > 10;
  END LOOP;
END;

--while循环
DECLARE
  num NUMBER;
BEGIN
  num := 2;
  WHILE num <= 11 LOOP
    dbms_output.put_line(num);
    num := num + 2;
  END LOOP;
END;

--for循环,自带变量，该变量不可修改
DECLARE
  num NUMBER;
BEGIN
  FOR num IN reverse 6 .. 11 LOOP
    dbms_output.put_line(num);
    
  END LOOP;
END;

posted @ 2018-10-22 17:21 57容杰龙阅读(181) 评论(0) 编辑收藏举报

指间灵动，快码加编

刷新页面返回顶部

57容杰龙

Oracle

公告