Oracle学习

Oracle学习

补充：（查询IP地址）

查询IP地址：win+R，调出窗口，输入cmd，接着输入ipconfig

一、表空间、用户和表

1、表空间

（1）创建表空间

create tablespace MM

datafile 'E:\shujuku\tablespace\MM.dbf'

size 100m

autoextend on

next 10m;

（2）删除表空间

drop tablespace MM；

2、用户

（1）创建用户

create user MM

identified by MM

default tablespace MM;

（2）给用户授权

grant dba to MM;

connect：基本角色；resource：开发者角色；dba：超级管理员角色。

（3）切换用户

session-----------log off，log on

补充内容：数据类型

（1）varchar2和char区别：

varchar2是可变长度，char是定长

（2）varchar和varchar2区别：

目前没有区别，但官方文档不支持使用varchar，强烈建议使用varchar2。

• varchar2是oracle提供的独特的数据类型，oracle保证在任何版本中该数据类型向上和向下兼容。但不保证varchar，这是因为varchar是标准sql提供的数据类型，有可能随着sql标准的变化而改变。
• 如果数据库不移植的话，就没有区别。如果数据要移植到别的数据库，可能用varchar比较好。varchar2很多数据库不支持。
• varchar2把所有字符都占两字节处理（一般情况下），varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节。
• varchar2把空串等同于null处理，而varchar仍按照空串处理。
• varchar2字符要用几个字节存储，要看数据库使用使用的字符集，比如GBK，汉字就会占两个字节，英文1个，如果是UTF-8，汉字一般占3个字节，英文还是1个。

3、表

（1）创建表

Create table person(

       pid number(20),
       
       pname varchar2(10)
       
);

可以直接在创建语句后面追加“tablespace name”进行表空间指定。

sql： create table tablename(id int) tablespace tablespacename;

解释：在表空间“tablespacename”上创建表“tablename”。以上语句就实现了为表指定表空间。

（2）修改表结构

1）列操作

• 添加一列

alter table person add (gender number(1));

注意：（）里面可以写很多列，用“，”隔开。

• 修改列类型

alter table person modify gender char(1);

• 修改列名称

alter table person rename column gender to sex;

• 删除一列

alter table person drop column sex;

2）记录操作

• 添加一条记录

insert into person (pid,pname) values (1,'小明');

commit;

注意1：如果添加的数据占据了所有列，那person后面的（）中可以不写内容。

注意2：增删改一定要写commit提交事务;查询不涉及事务。

• 修改一条记录

update person set pname='小马' where pid = 1;

commit;

3）三种删除方式

• 删除表中全部记录

delete from person;

• 删除表结构

drop table person;

• 先删除表，再次创建表，效果等同于删除表中全部记录

truncate table person;

注意1：在数据量大的情况下，尤其是在表中带有索引的情况下，第三种删除方式操作效率高；因为它是先删除索引。

注意2：索引会提高查询效率，但是会影响增删改效率；

（3）查看表

对着表格右击view，column

（4）序列

序列不真的属于任何一张表，但是可以逻辑和表做绑定。

序列的使用：默认从1开始，依次递增，主要用来给主键赋值使用。

dual：虚表，只是为了补全语法，没有任何意义（因为oracle查询必须要打上from）

create sequence s_person;

select s_person.nextval from dual;

insert into person (pid,pname) values (s_person.nextval,'小明');

commit; 

注意：用s_person.currval容易报错，因为第一次使用时，没有当前序列。

补充：（密码重置）

1、Scott用户，密码tiger

2、解锁Scott用户，解锁密码（也可以用来重置密码）

alter user scott account unlock;

alter user scott identified by tiger;	

注意：此句可以用来重置密码。

补充：（三张表）

EMP：EMPNO（员工编号），ENAME（员工姓名），JOB（员工工作），MGR（员工直属领导编号），HIRDATE（入职日期），SAL（工资），COMM（一年奖金），DEPTNO（部门编号）

DEPT：DEPTNO（部门编号），DNAME（部门名称），LOC（部门地址）

SALGRADE：GRADE（工资等级），LOSAL（最低工资），HISAL（最高工资）

注意1：DEPTNO在EMP中是外键，主键在DEPT中

注意2：右击bonus表，点击query data，表中是没有数据的

二、查询函数

1、单行函数

作用于一行，返回一个值

（1）字符函数

1）小写变大写

select upper('yes') from dual;

查询雇员姓名内容为smith的全部员工：
SELECT * FROM emp WHERE ename=UPPER('smith');

2）大写变小写

select lower('YES') from dual;

3）首字母大写函数

select initcap('hello world')from dual;

将雇员表中的全部雇员信息的首字母大写。
SELECT initcap(lower(ename)) FROM emp ;

4）字符串连接函数

select concat('hello','world') from dual;

或者：

select 'hello' || 'world' from dual;

5）字符串截取函数

截取的时候需要注意：要从哪里开始截取，之后取多长的长度。

select substr('hello',1,2) from dual;

select substr('hello',-3,2) from dual;
倒数第三个开始取两位；

检索姓名最后一个字母为N的雇员：
SELECT * FROM emp WHERE substr(ename,-1,1)='N';

检索职务为“SALE”的全部员工信息
SELECT * FROM emp WHERE substr(job,1,4)='SALE';

6）字符串替换函数

select replace('hello','l','x') from dual;

7）获取字符串长度函数

SELECT length(ename) FROM emp ;

注意：其中length()函数获取的是字符串的字符数，lengthb()获取的是字符串的字节数。但是需要注意的是如果你的数据库中的字段是clob，使用lengthb()会报错。

在oracle 中

select lengthb(‘你好’) from dual; 返回的值是 4

select length(‘你好’) from dual; 返回的值是2

8）instr()函数：查找在一个字符串中是否有指定的字符串

如果有此字符串，则返回位置；如果没有，则返回0。

select instr('hello','o') from dual;

SELECT instr('hello','x') FROM dual ;

9）trim()函数：去掉左右空格的函数

select trim(' hello ') from dual;

（2）数值函数

1）四舍五入函数

select round(26.18) from dual;
结果为26；

select round(26.18,1) from dual;
结果为26.2；

select round(26.16,-1) from dual;
结果为30；

select round(26.16,-2) from dual;
注意：此时无法向前进位了，所以结果是0；

select round(56.16,-2) from dual;
此时结果为100；

2）截取函数

select trunc(56.16) from dual;
结果为56