oracle基础知识(一)

oracle基础入门学习笔记：视频链接

目录

数据库登录角色及表结构

  1. 用户名称
    系统用户：sys,sytem
    sysman:企业管理级
    scott: tiger

权限顺序：

sys>system>sysman>scott
  1. 用户登录
--使用system用户登录
--数据库不在本机上，需要@orcl ，orcl就是自己设置的服务名；
[username/password][@server][as sysdba|sysoper]

--在sqlplus中直接登录sys的简便方法：
conn  / as sysdba
--sys,system
connect 用户名/密码 as sysdba
--查看当前用户
show user;
--用户字典，用于查看数据库的信息dba_users,user_users
desc dba_users
--查询用户字典的子项
select username from dba_users;

--启用用户的语句
alter user username acoount unlock;

表空间

表空间分类 描述
永久表空间 表、视图、存储过程...
临时表空间 中间操作的临时表
UNDO表空间 可以回滚操作的表
查看用户表空间数据字典 代号
系统管理员 dba_tablespaces
普通用户 user_tablespaces
创建表空间
--创建表空间
create [temporary] tablespace tablespace_name
tempfile|dataFile 'xx.dbf' size xx;

--创建永久表空间
create tablespace test1_tablespace
datafile 'test1file.dbf' size 10m;

--创建临时表空间
create temporary tablespace temptest1_tablespace
tempfile 'tempfile1.dbf' size 10m;


--查看创建的表空间
desc dba_data_files;
--查看具体的存放位置
select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';

--修改表空间
--修改表空间的状态
--设置联机或脱机状态
alter tablespace tablespace_name
online|offline;

--
alter tablespace test1_tablespace
offline;

--查看
desc dba_tablespaces;
select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

alter tablespace test1_tablespace
online;

--设置只读或者可读写的状态
alter tablesapce tablespace_name
read only|read write

alter tablespace test1_tablespace
read only;
select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

alter tablespace test1_tablespace
read write;

--修改表空间中的数据文件
--增加
alter tablesapce tablespace_name 
add detafile 'xx.dbf' size xx;

alter tablespace test1_tablespace
add|drop datafile 'test2_file.dbf' size 10m;
--drop不能删除第一个表空间数据文件，否则需要删除整个表空间

--查看system 默认的表空间：
select default_tablespace,temporary_tablespace from user_users;

select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';

认识表

约定
1 每一列数据必须具有相同数据类型；
2 列名唯一；
3 每一行数据的唯一性；
  • 变量类型
    (1) 字符型：
    固定型： char(n) max(n)=2000, nchar(n) max(n)=1000
    可变型： varchar2(n) max(4000) nvarchar2(n) max(2000)
    (2)数值型
    数值型：number(p,s) p表示有效数字，
    float(n):
    (3)日期：
    date:范围公元前4712年1月1日到公元前9999年12月31日；
    timestamp:精确到小数秒
    (4)其它
    Blob:大数据 4G,二进制
    clob:大数据 4G ,字符串

管理表

包括表的创建、增、删、改、查.

表操作

  • 创建表设置的默认值
--语法结构
create table table_name(
column_name datatype default value1,
....)
列的增，删、改
--增加列
alter table table_name add column_name datatype;
--更改数据类型
alter table table_name modify column_name datatype;
--删除字段
alter table table_name drop column column_name;
--修改字段名
alter table table_name rename column column_name to new_column_name;
表的重命名
--修改表名
rename table_name to new_table_name;
删表
--删除表
--删除表中所有数据
truncate table table_name;
--删除整个表结构
drop  table table_name;
--删除数据
--删除全部数据,truncate table tabel_name，这种方法更快
delete from table_name [where conditions]
向表中插入数值
--操作表中的数据
--添加数据
insert into table_name (column1,column2,...) values(value1,value2,...)
复制表
--复制表中的数据
--创建表的时候复制
create table tabel_new as 
select column1,...| * from table_old [where 1=1 |1=2];
--默认时where1=1表示复制表结构和表数据
-- where 1=2表示只复制表结构
--在添加表的时候复制
insert into table [(column1,...)] 
select column1,...|* from table old;
表中数据的更新
--修改数据
-update语句
update table_name set column1=value1,... [where conditions]；
--无where时将更新全部，需要慎重处理

表结构中的五大约束

约束：作用，定义规则；确保数据的完整性；

约束
非空约束
主键约束
外键约束
唯一约束
检查约束
非空约束
非空约束:
--创建表时设置非空约束
...column_name datatype not null,...
--修改表添加非空约束
alter table table_name modify column_nama datatype not null;
--修改表是去除非空约束
alter table table_name modify column_name datatype null;
主键约束
主键约束:作用，确保表当中每一行数据的唯一性；
一个主键约束，可以由多个字段构成(联合主键)
--在创建表时设置主键
primary key,
--在创建表时设置主键约束
constraint constraint_name(主键名) primary key(column_name1,...)
--查看
desc user_constraints;
select constraint_name from user_constraints where table_name='大写表名'；
--修改表时添加主键约束
add constraint constraint_name primary key(column_name1,...);
--更改约束的名称
alter table table_name
rename constraint old_name to new_name;
--删除主键约束
--禁用
alter table table_name
disable|enable|drop constraint constraint_name;
drop primary key【cascade级联删除】
--查看
desc user_constraints;
select constraint_name,status from user_constraints where table_name='大写表明';
外键约束
外键约束：
--创建表时创建约束
create table table1
(column_name datatype references table(column_name),...)

--在创建表时设置外键约束(表级)
constraint constraint_name foreign key (column_name) references table_name(column_name)
[on delete cascade]

--修改表时田间外键约束
--注：约束的名字不能重名
alter table table_name
add constraint constraint_name foreign key (column_name)
references table_name(column_name)[on delete cascade];
--删除外键约束
alter table table_name
disable|enable|drop constraint constraint_name;
drop primary key【cascade级联删除】
唯一约束
唯一约束：
唯一约束和主键约束的区别：
前者允许有一个非空；后者必须是非空的；
前者可以有多个；后者只能有一个
--在创建表时设置唯一性约束（列级）
create table table_name
(column_name datatype unique,...)
--在创建表时设置唯一约束(表级
create table table_name(
.....
constraint constaint_name unique(column_name));
--修改表时添加唯一约束
alter table table_name
add constraint constraint_name unique(column_name);
--删除唯一约束
alter table table_name
disable|enable|drop constraint constraint_name;
检查约束
检查约束：让数值更有意义
--在创建表时设置检查约束(列级)
create table table_name(
column_name datatype check(expressions),...)

--创建表时设置检查约束(表级)
create table table_name(
.....
constraint constraint_name check(expressions,如sal>0));

--修改表时添加约束
alter table table_name
add constraint constraint_name check(expression);
--删除检查约束
alter table table_name
disable|enable|drop constraint constraint_name;

小结 ：五个约束中只有非空约束只能在列级约束设置，不能在表级设置；

查看约束的名称
数据字典
desc user_constraints;
--更改约束名称
alter table table_name
rename constraint old_contraint_name to new_constraint_name;

sqlplus查询中的报表设置

--在sql*plus中的设置格式
column(可以简写为col) column_name heading new_name
col column_name format dataformat(字符型只能显示长度，数值型，用9代替一位数字)
col column_name clear（格式清除）

重命名

--给字段设置别名
select column_name as(可以省略) new_name,... from table_name

运算优先符
逻辑运算符的优先级：按not,and ,or顺序以此递减
计算运算符优于逻辑运算符；

范围查询

--模糊查询
--关键字：like
--通配符:(_,%(0到多个字符))
select * from users where username like 'a%';

--范围查询
select * from emp sal between min_ and max_;
in (列表值)/not in

case ...when 语句

--case ...when 语句；
case column_name when value1 then result1,...
[else result] end;

select username,case username when 'aaa' then '计算机部门'
when 'bbb' then '市场部门' else '其它部门' end as 部门 from users;

case when column_name=value1 then result1,...[else result] end;
--案例

select sal,case when sal<3000 then '低收入'
		when sal>=3000 and sal <5000 then '中等收入'
		else '高收入' end as 收入等级 from emp;

decode 函数

--decode函数的使用
decode(column_name,value1,result1,...,defaultvalue)

--案例1
select deptno,decode(deptno,10,'10号',20,'20号',30,'30号','40号') from emp;

--案例2
--用入职年份中提取年
--to_char用来将日期转化成字符串
select empno,decode(to_char(hiredate,'yyyy'),'1980',1,0) from emp;

oracle 中的单双引号问题

参考单双引号的总结1
总结2
注：

  • 双引号：
    ① 表示其内部的字符串严格区分大小写
    ② 用于特殊字符或关键字、对象名、别名
    ③ 不受标识符规则限制
    ④ 会被当成一个列来处理
    ⑤ 当出现在to_char的格式字符串中时，双引号有特殊的作用，就是将非法的格式符包装起来
    避免出现ORA-01821: date format not recognized错误， to_char在处理格式字符串时，会忽略双引号`

  • 单引号： ① 表示字符串常量和转译字符 ② 字符串中的双引号仅仅被当作一个普通字符进行处理。此时，双引号不需要成对出现

  • ③ 动态SQL: 在一对单引号包含的语句中，必须有一对相邻的单引号表示一个单引号 两个相邻的单引号的作用，第一个是用来表示转义字符，后面一个表示真正的单引号 单引号里要用单引号应该是两个连续的单引号,而不是双引号

  • 如果创建对象的时候，对象名、字段名加双引号，则表示Oracle将严格区分大小写，否则Oracl都默认大写。

  • 双引号用在字符串中当成普通字符处理

  • 当表中字段与oracle数据库关键字相同时，可以用双引号引注，这样可以避免冲突

posted @ 2020-04-03 16:54  LgRun  阅读(193)  评论(0)    收藏  举报