oracle笔记

2-1
启用scott用户
alter user username account unlock  #lock锁定  unlock解锁  account账号
例：alter user scott account unlock
conn scoot/tiger

进入表
select table_name from user_tables；  #user_tables 当前表信息

2-2
表空间分类：永久、临时、UNDO   #UNDO回退
创建表空间
create tablespace 名称 datafile '文件名' size 10m;  #size 后设定文件大小
临时
create temporary  tablespace 名称 tempfile '文件名' size 10m;
查询表空间位置
select file_name from dba_data_files where tablespace_name = '表空间名';

2-3
创建用户
create user 名字 identified by 密码  default tablespace 表名 temporary tablespace 临时表名  #dentified by 设置密码   ，已存在的表空间，default tablespace   #指定默认永久表空间   temporary tablespace #指定临时表空
查看用户
select unsername from dba_users
创建的用户授权
grant 权限 to 用户名
例:grant connect to yan;  #connect 连接权限，不能创建  resource 连接并创建实体  dba 连接创建数据库结构
连接创建用户
connect 用户/密码
更改密码
alter user 用户名 identified by 新密码;
不希望某用户登入，而又不删除，锁定用户
alter user 用户名 account lock;  # 用2-1相反
删除用户
drop user 用户名 cascade;  #加cascade则将用户连同其创建的东西全部删除

2-4
创建角色
create role 名字 ; 有了角色就能赋给多个权限，然后直接分配给用户
给角色权限：grant create table,create view to 角色名;  #赋予创建表创建视图权限
角色赋予用户权限：grant 角色名 to 用户；
拿回权限：revoke 角色名 from 用户;
删除角色：drop role 角色名;

2-5
查询oracle所有系统权限；
select *from sysyem_privilege_map;
查询oracle所有对象权限；
select *from table_privilege_map;  #常用权限select ,update,insert ,delete,all 所有权限

2-6
dba_tablespaces  #系统用户
user_tablespaces #
查看用户表空间
select tablespace_name from dba_tablespaces;
修改用户默认或临时表空间
alter user username default|temporary tablespace table_name;
例:alter user user01 default tablespace test1_tablespace temporary tablespace temptest1_tablespace;

2-7
修改表空间的状态
联机或脱机
alter tablespace 表空间名 offline;  #offline 脱机  ，read only 只读  ，read write  可读写，默认状态
修改数据文件
增加 删除文件
alter tablespace table_name add datafile '名称' size xx;  #add datafile 增加文件，drop datafile 删除文件

3-1
oracle sql developer工具下载

3-2
DDL 数据定义语音 create table  ,create index,drop table ,drop index ,truncate ,alter table,alter table add constraint.
DML 数据操作语言insert 添加. update修改,delete删除 ,select查询
DCL 数据控制语言grant授予权限,revoke回收权限,lock锁定
TCL 事务控制语言commit提交事务处理.rollback事务处理回退,savepoint设置保存点


3-3
创建表 create table table_name
        (
             column_name datatype,...            #column_name列名
             );
 数据类型
 字符类型：char 固定长度1-2000字节   varchar2 可变长度 最大4000字节
 数值类型：NUMBER[(p[,s])]  # p精度，s小数位，可以存储整数、浮点数等数值类型，最高精度38位
 例：number(5,0)最多可存储5位整数  number(5,2)   999.99
日期类型：date
lob类型：clob能存储大量字符数据   blob可存储较大二进制，如图形、视频剪辑和声音文件

3-4
约束constraint
主键约束 primary key     
创建constraint sid_pk primary key(sid) ;
修改 alter table table_name add constraint sid_pk primary key(sid);
删除主键约束：drop primary key

唯一性约束 unique
默认约束  default
非空约束 not null
检查  check
外部键 foreign key

3-5
非空约束：not null   #直接在列级后加not null
列级约束：colum [constraint constraint_name] constraint_type;   在列后面直接添加
表级约束：column sid_pk primary key(sid);  单独一行添加
修改表添加约束：
alter table table_name
   add  [constraint constraint_name] constraint_type (column);
alter table table_name   #非空约束使用
   modify (column datatype not null) ;
 删除约束：
 无效化或激活：disable | enable constraint constraint_name
 彻底删除：drop constraint constraint_name
                 alter table table_name           #非空约束推荐使用此方法删除
                         modify column_name datatype NULL;  
 
3-6
唯一性约束
创建
列级约束：直接加unique
表级约束：constraint uk_cardid unique(cardid)
修改：alter table table_name add constraint uk_cardid unique(cardid);
禁用约束:alter table table_name disable constraint constraint_name;
彻底删除:alter table table_name drop constraint constraint_name;

3-7
检查约束
列级约束：check(sex='男' or sex='女')
表级约束：constraint ck_sex check(sex='男' or sex='女')
修改：alter table table_name add constraint ck_sex check(sex='男' or sex='女')
禁用约束:alter table table_name disable constraint constraint_name;
彻底删除:alter table table_name drop constraint constraint_name;

3-8
外键约束
列级约束：create table (column_name datatype peferences   #从表
                (column_name) [on delete cascade],...);               #主表
表级约束：constraint constraint_name foreign key (column_name)
peferences 主表(column_name)
[on delete cascade]
修改外键：alter table table_name add constraint fk_depid foreign key(depid)
             references department(depid)
             on delete cascade;
禁用约束:alter table table_name disable constraint constraint_name;
彻底删除:alter table table_name drop constraint constraint_name;

 3-9
 修改表
 添加列 alter table table_name add 新增列名 数据类型;
 例：alter table student add tal varchar2(11);
 修改列alter table table_name modify 列名 新数据类型
 例:alter table student modify tel number(11,0);
 删除列 alter table table_name drop column 列名;
 例:alter table student drop column tel;
 修改列名alter table table_name rename column 列名 to 新列名;
 例:alter table student rename column sex to gender;
 修改表名: rename 表名 to 新表名;
 例:rename student to studentinfo;
 删除表
 truncate table 表名;   #用于删除表中的全部数据，并不是把表结构删除掉，速度比delete快，叫截断表
 drop table 表名;   删除表结构
 
 3-10
 表的增删改查
 DML 数据操作语言insert 添加. update修改,delete删除 ,select查询
 添加信息:insert into 表名[(列1，列2...列n)] values(值1,值2...值n);
 例：insert into student(sid,name,sex) values(20010001,'张三','女');
 查询select *| column[,...] from 表名;
 例：select sid,name from student;
 select * from student;
 修改信息：update table set column = value [where condition];
 例:update student set tel = '13599998888' where sid = 20010001
 update student set address = '辽宁省大连市'
 删除信息：delete from table [where condition];
 例：delete from student;
 delete from student where sid = 20010011;
 
 3-11
TCL 事务控制语言commit提交事务处理.
rollback事务处理回退,
savepoint设置保存点: savepoint a;设置保存点 ，rollback to a; 回滚部分事务 ，roolback 回滚全部事务


 3-12
 数据字典：user_*、all_*、dba_*
 查看相应数据库信息
 查看当前用户下的用户信息select * from user_users;
 当前用户有权访问的所有用户的基本信息select * from all_users;
 数据库所有用户的用户信息:select * from dba_users;
 
 
 4-1
 单表查询
 select * [{distinct] column | expression [alias],...} from table [where condition]
 table 用户名   column 用于指定列名 expression 用于指定表达式  alias   用于指定列的别名  condition  用于指定查询条件
 特定列查询
 全部列查询
 排除重复行distinct   ,select distinct depton,job from emp;
 日期列的查询:默认格式DD-MON-RR  ,RR代表年,  
     简体中文显示：nls_language参数设置成simplified chinese，alter session set nle_language = 'simplified chinese';       #'AMERCAN'美国显示
     特定格式显示：alter session set nls_date_format = 'YYYY/MM/DD';
 条件查询
 对查询结果排序
 
 
 4-2
 算术运算符查询
 select empno,ename,sal,sal * 12 from emp;
 例：需求，查看雇员编号，雇员名，年收入(年薪加半月工资)
 select empno, ename , sal*12+sal/2 from emp;
 
 
 4-3
 列别名 as
 使用列的别名：select empno "雇员编号", ename "雇员名", sal*12 "全年工资" from emp;
 select empno as "雇员编号", ename as "雇员名", sal*12 as"全年工资" from emp;
 连接符 ||
 列与列 列与字符连接
 例：select ename || sal || job from emp;
 
 4-4
 where 子句使用数字值
 select * from emp where deptno = 20;
 where 子句使用字符值
 select * from emp where job = 'MANAGER';
  where 子句使用日期值
 select * from emp where hiredate = '02-4月-81';
 比较运算符

 
 4-5
 范围查询between..and
 between  较小值 and 较大值
 例select empno , ename ,sal from emp where sal between 1500 and 3000;
 范围查询in
 in(值1, 值2...值n)
 select empno ,ename , job from emp;
 where job in ('salesman','MANAGER','clerk');
 等价于select empno,ename,job from emp where job = 'salesman' or job = 'MANAGER' or job = 'clerk';
 模糊查询like
 需要%和_搭配使用 %表示0个或多个字符  _表示单个字符
 例：select * from emp where ename like 'J%';
 select * from emp where ename like '_AR%';
 模糊查询中特殊符号的处理
 字符本身有_和%就要用escape和转义符
 回避特殊字符：使用转移符，例：[%]转为[\%]、[_]转为[\_],然后再加上[escape '\']即可
 例：select * from emp where ename like 'G\_' escape '\';
 判断空值is null
 操作符用于检测列或表达式的结果是否为NULL，如果为NULL，返回true，否则false
 判断列或表达式的结果是否为空可以用is null 或is not null 但是不能用= null或<>null 来判断
 例select empno, ename ,sal .comm from emp where comm is null;
 select empno, ename,sal,comm from emp where comm is not null;
 
 
 4-6
 逻辑操作符and 优先级2  or优先级3    not 优先级1
 
 
 4-7
 单列排序
 多列排序
 使用列别名排序
 排序数据order by 子句
 select * | column[,column...] from table [where condition] [order by column [asc|desc]]; #asc 升序 desc降序
 注意：order by必须是最后一条语句
 升序：如果排序列存在NULL行，那么NULL显示在最后面
  select ename,sal from emp order by sal asc;
  select ename,sal from emp order by sal;   #默认升序
  降序:NULL在最前面
  select ename,sal from emp order by sal desc;
   使用列别名排序
   select empno,ename,sal * 12 年收入 from emp order by 年收入 desc;
   多列排序:首先按第一列排序，第一列相同后以第二列进行排序
   例:select empno,ename,deptno,sal from emp order by deptno asc,sal desc;
   
 4-8
 SQL PLUS
 
 5-1
 SQL函数：单行函数和多行函数
 字符函数：
 大小写控制函数：upper大写 、lower小写、initcap首字母大写
 字符控制函数: concat（str1、str2）连接、
 substr（char,m[,n]）截取 #char指定源字符串，m用于指定从哪开始截取，n用于截取长度，m为0首字符开始，m负数，从尾部开始
 length长度、
 instr（char1,char2[,n[,m]]） #用于取得字符串位置，char1 用于指定源字符串，char2指定子串，n指定搜索位置，m第m次出现的次数
 lpad（char1,n,char2）左端填充,#n用于填充后char1的总长度
 rpad (char1,n,char2) 右端填充
 trim
 replac(char,search_string[,replacement_string]):  替换
 例：replac('hello oracle','oracle','world')
      结果：hello world
 
 
 5-2
 数值函数：round(n,[,m])四舍五入  #n任意数，m整数  例：round(25.328,2) 结果：25.33
 trunc(n,[m]) 截取数字，#n任意数字，m必须整数截取位， 例：trunc(25.328) 结果:25
 mod(m,n)  求余数：例mod(25,6) 结果1
 
 5-3

sysdate:当前系统时间 可-1 +1

months_between(d1,d2)： 按月计算  ，两值的时间差，round（）四舍五入，

add_month(d,n)：返回特定时期时间之后或之前的月份所对应的日期时间

next_day(d,char): 必须和系统时间对应 ，例：(sysdate,'星期一')

last_day(d):当前的最后一天

trunc(d[,fmt])：用于截断日期时间数据，d用于指定日期时间值,fmt用于指定截断日期时间数据的方法trunc，例(sysdate,'year')，(sysdate,'month')
 

5-4

转换函数

to char(d[,fmt[,'nlsparams']]) d指定日期值,fmt 指定日期格式模型(默认dd-mon-rr) ,nlsparams指定日期语音          

todate()

to_number()    to_char()

 

5-5

通用函数

NVL(expr1,expr2):如果expr1是null，返回expr2

NVL(expr1,expr2,expr3)如果expr1是null，返回expr2,如果expr1不是null返回3

NVLLIF(expr1,expr2) 1=2 返回null 否则返回 expr1

coalesce(expr1,expr2)返回表达式列表第一个not null表达式的结果

 

5-6

条件表达式

case表达式

case 列名

  when（当它是）  ‘’  then （显示为）‘’

else 否则显示 ‘’

end

decode函数

decode（） 当列等于条件1返回result1 条件2返回result2 ，不满足返回default

 

5-7

嵌套函数

6

分组函数

行转列：wm_concat(列名)

group by 不包含组函数都需要加入group by

having：where后不允许使用分组函数，需加having

 

7-1