oracle数据库

目录

一.创建用户

二.查询用户

三.解锁用户

四.删除用户

五.数据库的三大语法（DDL DML DCL）

1.数据库常用类型([ ]表示可有可无的意思)

【1】Number(length,[length1])

【2】字符串

【3】日期

2.表的操作

【1】创建表

【2】修改表结构

【3】删除表

【4】表的约束

3.DML对于表数据的操作

【1】Select 基本查询

【2】insert基本插入

【3】update基本修改

【4】delete 基本删除

4.基础查询

5.过滤查询

6.排序

7.函数

【1】数值函数

【2】日期函数

【3】转换函数

【4】通用函数

【5】多行函数（分组函数）

六.多表查询

七.子查询

1.单行子查询

2.多行子查询

八.事务管理

1.提交

2.回滚

九.视图

1.视图分类

2.创建视图

3.删除视图

4.修改视图

十.序列

1.如何创建序列

十一.触发

十二.索引

---

Sys 数据库最大的BOSS，权限最高，Sys登录的时候，权限必须选sysdba

System数据库管理员拥有DBA权限（管理员权限）

Scott测试用户，在scott用户中会自带测试表

一.创建用户

 --创建用户关键字  create    user用户名  identified by 密码

(创建完后必须给予connect的权限，不然是连不上数据库的)

Default tablespace  users  --默认表空间为users，如果用户没有设置表空间，默认也为users

Temporary  tablespace  temp      --临时表空间temporary   temp（临时表）

Profile default         Profile；表示概要文件

在没有给予权限时，账户无法登录

--授予权限关键字grant  

格式 ：Grant   权限   to  某一个用户

1、创建表空间：
格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小
                create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;
                create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M;
                (*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆) 
2、建好tablespace, 就可以建用户了
          格式: create user 用户名 identified by 密码 default tablespace 表空间表;
                create user study identified by study default tablespace data_test;
                (*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)
                (*缺省表空间表示 用户study今后的数据如果没有专门指出，其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)

创建用户并指定表空间
CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
授权给新用户
GRANT connect, resource TO cici;
grant create session to cici;

1、创建表空间：
格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小
                create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;
                create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M;
                (*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆) 
2、建好tablespace, 就可以建用户了
          格式: create user 用户名 identified by 密码 default tablespace 表空间表;
                create user study identified by study default tablespace data_test;
                (*我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.)
                (*缺省表空间表示 用户study今后的数据如果没有专门指出，其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中)

创建用户并指定表空间
CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
授权给新用户
GRANT connect, resource TO cici;
grant create session to cici;

二.查询用户

--查询关键字  select

Select * from  all_users         all_users  所有用户的表

--修改密码   --修改关键字 alter

格式：Alter user 用户名 identified by 新的密码

三.解锁用户

--解锁关键字account unlock

  Alter user  用户名 account unlock 

四.删除用户

--删除用户关键字 drop

Drop user 用户名

五.数据库的三大语法（DDL DML DCL）

DDL   对于数据库表结构的操作语句

（其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上）

DML  对于表数据的操作

（就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。）

DCL  数据库事务的操作

(是用来设置或更改数据库用户或角色权限的语句，包括（grant , deny , revoke等）语句。)

Oracle中的DDL语句

      DDL：定义数据库及表结构的语句，自动提交（保存）的语句

创建与管理表

      表名和列名的规则：

           1.必须以字母开头，

           2.长度在1-30个字符之间，

           3.只能包含数字、字母、_、$和#

           4.不能和用户定义的其他对象重名

           5.不能是oracle关键字和保留关键字

1.数据库常用类型([ ]表示可有可无的意思)

【1】Number(length,[length1])

范围： -10的38次方到 10的38次方

可以表示整数，也可以表示小数

number(5,2)    表示一位小数有5位有效数，2位小数

范围：-999.99到999.99

number(5) 则表示一个5位整数

【2】字符串

char（l）固定长度的字符 ，最大2000个字符

Varchar2（l）可变长度的字符， 最大4000个字符（一般情况下用这个）

【3】日期

date（精确到时分秒） timestamp（精确到毫秒）

大数据：blob 存储2进制数据，最大达到4G

2.表的操作

【1】创建表

Create table 表名(

   列名1   数据类型（长度）    [primary key]，

   列名2   数据类型（长度），

   ……….

);

【2】修改表结构

Alter table  表名

      新追加一列

           例：alter table 表名

                 Add(列名数据类型 [列名数据类型（大小）…..])

      修改列（修改列的用modify）

           Alter table 表名  Modify(列名数据类型[列名数据类型。。。])；

           对于修改，可以修改默认值、数据大小、数据类型

           数据大小修改后只能对修改列后的数据产生影响

           修改类型必须在插入数据之前进行修改

      删除列：

           Alter table 表名  Drop column 列名

修改列名：

           Alter table 表名  Rename column 名字 to 新的名字

修改表名：

           Rename 表名 to 新的表名

【3】删除表

Drop  table 表名

【4】表的约束

      主键     primary key  主键（包含非空、唯一）

      外键     foreign key （主键id） references 表名（外键列）

      非空     not  null

      唯一     unique

      检查     check

建立约束

1. 在创建表结构的时候直接写到列后面的列级约束
2. 在表结构的后面写的约束叫表级

列级约束

create table emp2(

       id number(8) primary key,  --主键约束

       ename varchar(20) unique, --唯一键约束

       job varchar(20) not null,  --非空约束

       hiredate date,

       sal number(10),

       deptno number(8) references dept  --外键约束

)

表级约束

create table student(
id number(8),
name varchar(20),
sex char(2),
age number(2),
gid number(5),
constraint stu_id_pk primary key(id),
constraint stu_sex_ck check(sex = '男' or sex = '女'),
constraint stu_age_ck check(age>0),
constraint stu_gid_fk foreign key(gid) references grade(gid)
);

约束关键字  constraint

  格式：

    Constraint   约束名 primary key（主键列名）

    Constraint   约束名foreign key （主键id） references 表名（外键列）

    Constraint   约束名 unique（列名）

    Constraint   约束名Check（约束条件）

约束名：列级中没有取约束名，系统自动的取一个约束名，表级约束中需要自己取一个标准的约束名，也可以不取（primary key（主键名））

创建一个表，并将查询出来的数据插入到新的表中

      Crete table  表名

      As

      Select * from 表名

     

去掉数据复制表结构

      Create table 表名

      As

      Select * from 表名

      Where 添加一个没有结果的条件  empno=null   

3.DML对于表数据的操作

【1】Select 基本查询

格式： select    列名    as    列名的别名   from  表名

别名：如果别名在列中代表列名的别名，如果别名在表中，则认为是表名的别名并可以通过表名的别名点出表中的列

格式：select  a.列名1,a.列名2。。。。。      from 表名   a（表名的别名不用加as）

注意：在取别名的时候，别名不能有空格，如果必须要空格，将别名用双引号引起来，as可以省略

【2】insert基本插入

格式：insert into 表名[列名1，列名2，列名3…..] values(列名1,列名2，列名3,列名4)；

如果：当表名后面有列名的时候，表示为插入指定的列的数据，但是其他没有指出的列，必须可以为空；如果表名后没有指定列，values中则插入表中所有的列值

【3】update基本修改

格式：update 表名  set  列名=值[,列名1=值，。。。。。][where 列名=值]

【4】delete 基本删除

格式：delete  from 表名 [where 列名=值]

4.基础查询

||连接查询结果   “||” 表示将两个数据或者值连接起来

关键字：

【1】nvl（comm,0）空值处理     第一个参数为列，第二参数表示为如果列中的值为空，用0来代替

注意：在sql中，所有的空值运算之后都为空

查询所有工资并不能有重复工资出现

【2】去掉重复 关键字：distinct

格式：  select distinct sal from emp

5.过滤查询

1. 加where条件

在sql语句中，字符、日期都必须用单引号引起来

where条件后面可以跟的格式：

1.数值的比较

2.字符串的比较

3.日期的比较

4.between…and….(在两者之间，包含两个边界)

5.like(_,%)(模糊查询，关键字查询，_表示的是匹配单个字符，而%表示的是匹配一个到多个字符)

6.in（精确匹配查询）

7.is null表示空   is not null 表示非空

格式： select * from 表明 where 列明 between  XX and  XX

比较符：>    <     >=     <=    !=或<>     =      

sql中的赋值符号：    :=  赋值符号

逻辑运算符： and 与     or或    not非

6.排序

关键字：order by    

asc 升序 (默认排序可以省略asc)       desc 降序

7.函数

单行函数（字符、数值、日期、转换、通用）

单行：只对一行数据影响的函数叫单行函数

字符函数：lower  将字符转换为小写

                  upper 将字符转换为大写

                  instr(列，str1)  查询字符   str1在列中的字符,返回位置

（这里查询的话是有分大小写的，查询的是位置，不是下标,字符串中有俩个或以上的要查询的字符，只会显示第一个字符的位置）

【1】concat  连接   将两个字符连接    类似于||

select concat(ename,job) from emp

【2】length 返回字符串的长度 

select length（ename) from emp

【3】initcap将首字符大写，其他字符全部小写

select initcap(e.ename),e.ename from emp e

【4】substr截取字符串，第一个单位为起始位置，不是下标位置，第二个单位为截取的长度，长度默认到末尾

select substr(e.ename,2,2),e.ename from emp e

【1】数值函数

--dual 这个表没有具体的数据，只是在查询的时候，我们查询的格式为 select 列 from 表名，是虚拟表，当不需要用到表而用到查询结构的时候用dual占位

turnc（） 数字截断，与四舍五入唯一不同就是他是直接去掉，而不是逢5进1

select e.empno, e.mgr,e.hiredate,trunc(e.sal*1.03,-2) from emp e

round（）四舍五入后面的参数表示要保留的小数点前后的位置，整数表示小数点后几位，负数表示小数点前面几位

select e.empno,e.ename,e.job,e.mgr,e.hiredate,round(e.sal*1.03,2)  from emp e

mod（） 求余

select mod(36.13444,2)  from dual

floor ()向下取整,不管小数位是多少，都比这个数小的最大整数

ceil ()向上取整

【2】日期函数

sysdate（当前时间）   分为日期和时间

1.Months_between  获得两者之间的月份数

      --获得1987/4/19到1981/2/20这个日期间的月份差

      select  months_between('19-4月-1987','19-4月-1981') from dual

2.add_months   向指定的日期中添加若干月份

      select  add_months(e.hiredate,2),e.hiredate from emp e

3.last_day 获得本月的最后一天

      select  last_day('01-2月-2001')--这里写入其他月份也是可以的 from dual

4.round （日期，年/月/日）   日期同样有四舍五入

      select round(sysdate,'mi')--后面的参数表示四舍五入到多少参数，也就是参数的下一位在四舍五入 from dual

5.trunc     日期的截断

【3】转换函数

分为两种形式：隐式转换   显示转换

            隐式转换：自动类型转换

            显示转换：对数据用方法进行转换

                 to_char      转换为字符

                 to_number       转换为数字

                 to_date      转换为日期

                 to_char(date\number,’字符格式’)

将日期转换为字符串  to_char(sysdate,’yyyy-mm-dd’)；

将数字转换为字符串  to_char(sal,’00000.00或者9999.99’)；

格式中的9的位数，一定要大于等于要转换的数字的位数

如果是货币可以在前面加上$或者L（本地货币）为字符串

 to_date(‘字符’,‘日期格式’)            yyyy  代表年  mm代表月  dd代表日  hh时(hh24表示24小时制)  mi分  ss秒

      --插入一条数据到数据库表emp   将日期转换为指定格式

      insert into emp   values(7856,'lisi','SALESMAN',7698,to_date('1982-12-12','yyyy-mm-dd'),6000,200,10)

to_number(‘字符’,’数字格式’)

      --将￥1234.55转换为数字

      select to_number('￥1234.55','L999999999.000') from dual

      如果字符含有货币符号，那么格式中也必须有货币符号

【4】通用函数

nvl  （参数1，参数2）处理空值

nvl2（参数1，参数2，参数3）  参数1列中的数据，如果不为空执行参数2，如果为空执行参数3

Case  参数  when 列值 then 返回数据(类似于switch…case)

           [when  列值2  then 返回数据2

           。。。。。]

           Else 返回数据

End [别名]

通过每一列查出的值作为参数匹配when后面的值，入果匹配上了则返回then后面的值，如果都没匹配上 则返回else中的值，整个函数只是一列

Decode（参数,列值1，返回值1，列值2，返回值2，列值3，返回值3，返回值）

【5】多行函数（分组函数）

多行：对多行进行影响并且最终得出一个结果的函数多行函数

      函数：max    min    count     avg     sum

1.max 求出列中最大值

2.min 求出列中最小值

3.sum  求和

4.avg  求平均忽略空值

5.count  总记录不会记录空值

6.group by 分组

规则：group by使用的时候，select列表中有单个的列出现，group by中就必须有

           Group by中有的列，在select中不一定有

           Group by中可以包含多个列

           Where 条件依然在  from语句之后,在group by之前

7.Having   在分组之后，对数据进行再次过滤

Having于where之间的区别：having是分组后过滤条件，并且不能使用别名作为过滤条件     where  在分组之前进行过滤可以使用别名

总体格式：

      Select 列名,组函数…

      From 表名

      Group by 列名

      Having 条件

六.多表查询

笛卡尔集

           省略连接、连接无效、所有表中的所有行进行了互相连接

           如何避免：在查询的时候用where 加入有效的连接条件

等值查询

           相关联的列，在select列中出现的时候必须指明为哪个表的列

           用两个表所对应的数据进行一对一的连接匹配

           如果有多个表进行查询：条件：n-1个条件，n代表表的个数

Iinner join 内连接查询：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表的不匹配的行

      格式：select 列  from 表名1 [inner] join表名2 on 等值条件

外连接：分为左外连接右外连接查询

        左外连接(left outer join)：两个表在连接的过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接查询叫做左外连接查询

        右外连接(right outer join)：两个表在连接的过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接查询叫做右外连接查询

七.子查询

子查询又称为内查询，在主查询之前执行，并将结果返回给主查询使用

子查询注意事项：

           1.子查询要写到括号之内

           2.将子查询放在比较条件的右侧，相对主查询往右缩进

主查询和子查询可以不是一张表，只要子查询返回结果主查询使用就行

语法：

           Select 列名[,子查询]

           From  表名[,子查询]

           Where 条件[,子查询]

          Group by 列名

          Having  条件[,子查询]

      如：

select*from emp where deptno =(

select deptno

from dept

where lower(dname)='research')

子查询可以出现的地方：1.select    2.from    3.where    4.having   【 Group by 不能使用子查询    Where,having子查询不能使用order by(排序)】

单行操作符对应单行子查询，多行操作对应多行子查询

1.单行子查询

 查询出来之后只与一个值进行匹配

子查询返回空值：如果子查询返回一个空值，那么整个查询都为空

2.多行子查询

在子查询中返回多行结果

 多行子查询，用的操作符：

          【1】 In  ：匹配返回结果中的任意一个结果，只要能匹配上都进行显示

          【2】Any（大于最小值，小于最大值）：  比结果中的任一一个小或任一一个大

                    如果是大于，大于最小的一个值 。 如果是小于，小于最大的一个值

          【3】All： 比结果任意（所有）一个小或者所有的大

                    如果是小于，小于最小值。如果是大于，大于最大值

查询插入insert

      格式： insert into 表名 select 列  from 表名

      将查询出来的数据插入到一张新建好的表中

Update 

      Update 表名 set 列=值[，列2=值 where  条件]

      修改数据库中相应的值，where、set中可以使用子查询

删除

      Delete  删除表中的数据

      Trunct 删除表结构  但是表空间还在

      Drop   删除   完全删除 表空间不存在

八.事务管理

事务的作用：确保数据的完整性，数据改变被提交之前预览

1.提交

         隐式提交：DDL语句  自动保存

         显示提交：commit手动方式进行提交

2.回滚

        隐式回滚：异常关闭数据库，自动回滚

        显示回滚：rollback  回滚到前一个提交点

Savepoint 名字 :   创建一个保存点

Rollback to 名字:  rollback通过名字回滚到相应的保存点

九.视图

概念：从表中抽出的逻辑上相关的数据集合，是一种虚表，可以从不同的角度查看不同的数据，主要是授权使用的

1. 视图的数据来自于源表
2. 视图一般用于查询数据

1.视图分类

简单视图(单表)：一般可以做insert、delete、update、select操作，但是不推荐通过视图来做增删改数据

复杂视图：只能做查询操作，满足以下条件之一，就是复杂的视图：

1. 涉及到多张表的视图
2. 视图有组函数
3. 视图的数据分组

2.创建视图

Create [or replace] view 视图名

  As

  Select 列 from 表

3.删除视图

Drop view 视图名

4.修改视图

修改关键字 replace

Create or replace view 同名视图

As 

重新设置数据

十.序列

可以供多个用户来产生唯一数值的数据库对象，提供有规律的数值

      1.自动提供唯一数据

      2.共享对象

      3.主要用于提供主键值

      4.将序列放入内存可以提高访问效率

1.如何创建序列

Create sequence 序列名

           [Increment by n]  --序列的步长，默认值为1

           [start with n]     --序列初始位置，不能小于最小值

           [minvalue n]          --最小值

           [maxvalue n]         --最大值

           [cycle/nocycle]      --序列是否可以循环再取

           [cache n]         --序列的缓存

【1】获取序列的下一个值： select 序列名.nextval from dual

【2】获取到当前序列值：select 序列名.currval from dual

修改：

      Alter sequence 序列名

           Increment by 1

           Maxvalue 99999

           Minvalue 1

           Nocycle

删除

      Drop sequence 序列名

十一.触发

--创建触发

create or replace trigger trg_emp

--emp表插入的时候触发

before insert on emp 

for each row

declare

begin

--将查询出来的下一个序列值，复制给新的一个主键列

select  emp_seq.nextval into:new.empno from dual;

end  trg_emp;

十二.索引

索引的作用：提高查询的性能

索引是什么：

    1.一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中

    2.索引被删除或损坏，不会对表产生影响，影响的只是查询的速度

    3.索引一旦建立，oracle管理系统会对其进行自动维护，而且由oracle管理系统决定如何使用索引

    4.在删除一个表的时候，所有基于该表的所以自动被删除

    5.通过指针加速oracle服务器的查询速度

什么时候不需要索引：

       1.表很小、数据很少

       2.列不经常作为连接条件或出现在where字句中

       3.查询的数据在2%到4%之间

       4.经常要更新的表

创建索引：

    自动创建：在定义主键或唯一键的时候系统会自动的为相应的列加上索引

    手动创建：用户可以在其他列上创建非唯一的索引，以加速查询

    Create index 索引名  on 表（列）

查看索引：select * from user_indexes