慕课网oracle基础教程总结

 

Oracle的安装和删除省略；

用户：

SYS ,system

Sysman

Scott

其中sys,比较特殊

 

 

Connect sys/toor as sysdba;

 

查看用户：show user ， dba_users数据字典（数据库提供的表，用于查看数据库的信息）

Desc dba_user  这里可不用分号，因为desc非sql语句  

Select username from dba_users;

 

 

解锁并启用用户并登陆SQL plus： alter user username（scott） account unlock(安装时没有解锁的情况下)

Connect scott/password

 

以下为表空间：

数据库与表空间

表空间与数据文件

 

分类：

永久表空间   （存放数据库中永久存储的内容，表、视图、触发器等） 

临时表空间：（存放数据库操作过程中临时执行的过程，执行结束后，表空间会被释放，不进行永久保存）

UNDO表空间：用于事物回滚，更改时对之前的进行保存（存放事务所修改数据的旧值，也就是被修改之前的数据，即回滚的来源）

 

 

 

查看用户的表空间<br>

dba_tablespaces、user_tablespaces（分别指数据库和用户的表空间数据字段信息）<br>

dba_user、user_users（系统用户和普通用户的数据字段信息）<br>

1.desc dba_tablespaces 查看数据库全库表空间中的字段信息<br>

2.select tablespace_name from dba_tablespaces查看数据库中包含的对应类型的表空间详细信息；<br>

3.desc user_tablespaces查看普通用户表空间中的字段信息<br>

4.select tablespace_name from user_tablespaces查看普通用户包含的对应类型的表空间详细信息<br>

5.desc dba_users查看dba_users的用户的字段信息<br>

查看系统用户名下对应的默认表空间和临时表空间的详细信息（这里以system为例）<br>

select default_tablespace,temporary_tablespace from dba_user where username='system';<br>

设置用户的默认或临时表空间<br>

1.alter user 用户名 default(temporary )tablespace 表空间名

表空间：

SYSTEM：存放sys这个用户的表、视图及存储过程等这些数据库对象，称为系统表空间。主要用于存储系统信息。

SYSAUX：作为EXAMPLE的辅助表空间。

EXAMPLE：用于安装oracle实例及数据库实例的表空间。

UNDOTBS1：用于存储撤销信息，属于UNDO表空间。

TEMP：存储SQL语句处理的表、索引信息，属于临时表空间。

USERS：属于永久性表空间，用于存储数据库用户创建的数据库对象。与sys表空间差不多。

 

 

 

创建表空间：

CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SIZE xx;

永久：CREATE TABLESPACE tablespace_name DATAFILE 'xx.dbf' SIZE xx;

临时：CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE 'xx.dbf' SIZE xx;

没有指定路径时，默认在oracle安装目录下。

 

查看表空间具体路径

desc dba_data_file查看数据字典字段

select file_name from dba_data_file where tablespace_name='表空间名字要大写';查看永久表空间数据文件

select file_name from dba_temp_file where tablespace_name='表空间名字要大写';查看临时表空间数据文件

 

 

 

 

修改表空间状态

1.设置联机或脱机状态

alter tablespace tablespace_name offline  online; //脱机状态是不能使用的

2.查看表空间状态

select status from dba_tablespaces where tablespace_name='xxx'; //表空间名字要大写

3.设置只读或者可读写状态（表空间必须为联机状态，联机状态默认为读写状态）：

alter tablespace tablespace_name read only(只读)/read write(读写);

 

eg:

alter tablespace test1_tablespace offline;     脱机    

desc dba_tablespaces;

select status from dba_tablespaces where   tablespace_name='TEST1_TABLESPACE';            查看状态

alter tablespace test1_tablespace read only;   只读

select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';            查看状态

 

 

 

 

修改数据文件

1、增加数据文件

alter tablespace tablespace_name add datafile 'xx.dbf' size xx;

2、查看表空间的数据文件

select file_name from dba_data_file where tablespace_name='表空间名字要大写';

3、删除数据文件

alter tablespace tablespace_name drop datafile '表空间名字';不能删除表空间的第一个数据文件，如果要删除的话，则必须删除整个表空间。

 

 

 

 

删除表空间：

DROP TABLESPACE tablespace_name [INCLUDING CONTENTS];

当选中INCLUDING CONTENTS时，该表空间下的数据文件也一并删除

 

 

Oracle管理表：

表：表都会存放在oracle数据库的表空间中，是存储数据的最基本单位，是一个二维结构。约定：每一列数据必须具有相同数据类型，列名唯一，每一行数据的唯一性。

1、表是基本存储单位

2、二维结构

3、行和列

约定

1、每一列数据必须具有相同数据类型

2、列名唯一

3、每一行数据的唯一性

 

 

 

NUMBER（5，2）有效数字5位，保留2位小数，如123.45

数据类型：字符型、数值型、日期型、其它类型

1.字符型：

CHAR(n),定长字符串,字符串的最大长度(n)为2000;

NCHAR(n),以unicode编码的定长字符串,最大长度(n)为1000;

【若n=10 实际为3 占用是10，后面补充7】

VARCHAR2(n),变长字符串,字符串的最大长度(n)为4000;

NVARCHAR2(n),以unicode编码的变长字符串,最大长度(n)为2000;

【varchar()和NVARCHAR()比上面两个节省空间，若n=10 实际为3 占用就是3】

2.数值型：

NUMBER(p,s) p有效数字，s小数点后面的位数。当s>0时,表示小数点后数字的位数,当s<0时,表示对小数点前s位数字进行舍入;

FLOAT(n)存储二进制的数值的，表示二进制位数0-126位，若是转化为十进制通常是乘以0.30103得到

3.日期型：date（常用） timestamp（更精确）

4.其他类型：BLOB(以二进制,4GB)  CLOB（以字符串,4GB）

nvarchar2和varchar2都可以表示可变长度的字符，其中nvarchar2用于存储Unicode格式的数据，更适合存储中文数据。

 

 

 

创建表的基本语法

 

CREATE TABLE table_name

(

column_name datatype,...     所需字段 + 字段的类型，

);

 

例：

create table userinfo

( in number(6,0),

username varchar2(20),

userpassword vachar2(20),

email vachar2(30),

regdate date);

查看表结构：desc userinfo

1.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；

2.VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；

3.VARCHAR2字符要用几个字节存储，要看数据库使用的字符集，

大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

 

 

 

修改表：

添加字段：AlTER TABLE table_name ADD column_name datatype;

更改字段数据类型：ALTER TABLE table_name MODIFY column_name datatype;(修改时没有存放数据)

删除字段：ALTER TABLE table_name DROP COLUMN column_name;

1.修改表名

rename 表名 to 新的表名

2.列改名

alter table 表名 rename column 列名 to  新列名

3.增加列

alter table table_name add 列名(数据类型)

4.删除列

alter table table_name drop column 列名

5.修改列

alter table table_name modify 列名 数据类型;（没有数据才可以修改，修改属性类型）

 

truncate table 表名  这只是将表截断 数据库中还存在

drop table 表名       彻底删除

 

 

操作表数据：

插入所有数据：Insert into userinfo values（1，’xxx’,’123’,’xxx@126.com’,sysdate）;

向表中字段添加值   insert into 表名（column1,column2,...）values(value1,value2,...)

向表中字段添加值insert into 表名（value1,value2,...）

sysdate （一个函数）获取当前时间；添加默认值时使用

向表中字段添加默认值（1）在创建是添加 create table userinfo1（id number(6,0),regdate date default sysdate） ;

添加数据 insert into userinfo1 (id) values(1);需要指定添加的字段名，默认的会自动创建

（2）将默认值更改alter table userinfo modify email default'无';

 

 

复制表：

复制表结构

（1）在建表时 create table 表名  as select column1,...|*  FROM   要复制的表（将旧表中的全部数据复制了过来）

 Create table userinfo_new1 as select id,uername from userinfo;

（2）在添加时 insert into 表名[(column1,...)] select column1,...|* from 要复制的表;insert into uerinfo_new select* from uerinfo;

 

 

 

 

 

修改表数据

修改表数据 update 表名set colimn1=value1,...[where 子句](不加where子句就会将表中数据全部更改)

有条件更改：例 update userinfo set psw='12345' where username='sss';（这条语句时建username为sss 的psw改为12345）

Update userinfo set uesrpwd=’111’,email=’111@126.com’;

 

 

 

删除数据;

delete语句：

delete from table_name

[where condition];             //不加where全删除，加where删除指定行

无条件删除：

delete from userinfo;

 

有条件删除：

delete from userinfo

where username='yyy';

 

 

约束

约束：

1、约束的作用:  定义规则  、 确保完整性（精确性和可靠性）   

2、非空约束：

   在创建表时设置非空约束：

   create table table_name(

       column_name datatype not null,.....);

 

   create table userinfo_1(

   id number(6,0),

   username varchar2(20) not null,

   userpwd  varchar2(20) not null);

   在修改表时添加非空约束:

   ALTER TABLE table_name MODIFY column_name datatype（数据类型） NOT NULL;设置非空约束要求表中是没有任何数据的。

   alter table userinfo modify username varchar2(20) not null;

   在修改表时去除非空约束：

   alter table table_name modify column_name datatype NULL;

3、主键约束：

     一张表只能设计一个主键约束，主键约束可以由多个字段构成（联合主键或复合主键）。

表级约束：CONSTRAINT constraint_name constraint_type(column_name1,...);

联合主键：CONSTRAINT constraint_name PRIMARY KEY(column_name1,...);

 

1.在创建表时设置主键约束(列级)

语法：CREATE TABLE table_name(

column_name datatype PRIMARY KEY,...);

2.在创建表时设置主键约束（表级，联合主键）

语法：CREATE TABLE table_name(

column_name datatype,...,

CONSTRAINT pk_name PRIMARY KEY(column_name1,...)); //pk_name 通常用pk_column1_column2表示；PK为主键约束的字母，后面为联合字段；

 

在修改表时添加主键约束

 alter table table_name add constraint constraint_name primary key (column_name1,...)（在执行这条语句时，已建好的表中值是唯一的）.

更改约束名称 alter table 表名  rename constraint old_name to new_name

禁用/启用主键约束  alter table 表名 disable(禁用)|enable （启用）constraint 约束名;

   删除约束：ALTER TABLE tbl_name DROP CONSTRAINT constraint_name;

    查看约束的状态 select constraint_name,status from user_constraints where table_name='表名';

    完全删除约束 alter table 表名 drop constraint 约束名

               alter table 表名 drop primary key[cascade有外键时用（级联删除）]

 

3.数据字典：user_constraints 可以查看某个表中的主键约束信息。desc user_constraints

查看约束的名字：select constraint_name from user_constraints where table_name='tbl_name';表名要大写

 

4、外键约束：

      三、外键约束（表示主从表之间的关系）<br>

1、在创建表时设置外键约束（列级）<br>

语法：CREATE TABLE table1(column_name datatype REFERENCES table2(column_name),...); --其中table2为主表，table1为从表<br>

注：（1）设置外键约束时，主表字段必须为该表的主键字段<br>

（2）主从表中相应的字段必须是同一个数据类型<br>

（3）从表中外键字段的值必须来自主表中相应字段的值，或者为null值

 

在创建表时设置外键约束2（表级）

语法：CREATE TABLE table_name(

column_name datatype,...,

CONSTRAINT constraint_name FOREIGN KEY(column_name)

REFERENCES table_name2(column_name)[ON DELETE CASCADE]);使用级联参数创建

注：1、table_name2为主表名；

2、[ON DELETE CASCADE]表示级联删除的意思；

3、约束的名字也是唯一的。

 

 

在修改表时添加外键约束：

alter table table_name

add constraint constraint_name FOREIGN KEY(column_name)REFERENCES

table_name(column_name)[on delete cascade（这部分可不加）]

 

在修改表的时候添加外键约束：

alter table 从表名

add constraint 约束的名字 foreign key(要设成外键的数据名)

references 主表名(主表的外键数据名)on delete cascade；

 

 

查询约束的名字：

select constraint_name,constraint_type,status from user_constraints where table_name='表名'；

P字母表示为主键约束，R表示为外键约束。

删除外键约束：

1.禁用：

alter table 表名

disable constraint 外键约束的名字；  如果要启用，就把disable改成enable就能启用。

2.彻底删除：

alter table 表名

drop constraint 约束的名字；

 

5、唯一约束：

     唯一约束和主键约束的区别：

  1.主键字段值必须是非空的，唯一约束允许有一个空值；

  2.主键在每张表中只能有一个，唯一约束在每张表中可以有多个。

 

   1、在创建表时设置唯一约束（列级）

语法：CREATE TABLE table_name(column_name datatype UNIQUE,...);

2、在创建表时设置唯一约束（表级）

语法：CREATE TABLE table_name(

column_name datatype,...,

CONSTRAINT u_name

UNIQUE(column_name));

注：（1）唯一约束的名称（u_name）必须唯一；

（2）如果想设置多个唯一约束字段，需要将子句（CONSTRAINT u_name UNIQUE(column_name)）重复书写即可。

在表集设置唯一约束（用户名约束）：

create table userinfo_u1

(id varchar2(10) primary key,

username varchar2(20),

constraint un_username uinque(username));

 

在修改表时添加唯一约束

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE(column_name);

 

 

禁用/启用唯一约束：alter table table_name

disable/enable constraint constraint_name;

删除唯一约束：alter table table_name

drop constraint constraint_name

 

 

6、检查约束：

作用：表中的值更具有实际意义，使输入的字段能够满足CHECK后面表达式的要求。

创建表时设置：CREATE TABLE table_name

列级：(column_name data_type CHECK(expressions),...);

Create table userinfo_c (id varchar2(10) primary key,

Username varchar2(20),

Salary number(5,0) check(salary>0));

 

表级：(CONSTRAINT constraint_name CHECK(expressions));

expressions中写上字段需要满足的条件。

 

修改表时添加检查约束:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK(expressions);

 

通过数据字典查看检查约束：

select constraint_name,constraint_type,status from user_constraints

where table_name='大写的表名';

其中：constraint_name:是约束的名字 constraint_type:是约束的类型,检查约束为C

 

禁用检查约束

语法：ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT ck_name;

删除检查约束

语法：ALTER TABLE table_name DROP CONSTRAINT ck_name;

 

 

约束小结：

总结（非空约束，主键约束，外键约束，唯一约束，检查约束）：

1.主键约束，每张表中只能有一个，可以由多个字段构成

2.外键约束，唯一一个涉及两张表之间关系的一个约束

3.在创建表时设置约束，只有非空约束只能在列级设置，不能在表级设置，其他约束既可以在列级设置也可以在表级设置，而且非空约束是没有名字的

 

4.在修改表时添加约束，唯一一个和其他约束不同的约束即非空约束，通过修改字段实现

5.更改约束的名称，由于非空约束没有名称，所以不涉及更改名称，其他都可以

  数据字典（user_constraints） 

RENAME CONSTRIANT old_name TO new_name

6.删除约束，非空约束特殊（修改字段，经NOT NULL改成NULL即可，MODIFY关键字），其他都可以通过两种方式分别禁用和删除约束

7.在创建表时设置约束：非空约束和默认约束只能在列级设置，不能在表级设置。其他约束既可以在表级设置，也可以在列级设置。并且非空约束没有名字。

在修改表时添加约束：非空约束实际上使用的是修改字段的语句。

更改约束的名称：除了非空约束都可以。约束的名字可通过数据字典USER_CONSTRAINTS来查看。ALTER TABLE tbl_name RENAME CONSTRAINT old_name TO new_name;

删除约束：非空约束实际上使用的是修改字段的语句。禁用约束，ALTER TABLE tbl_name DISABLE|ENABLE CONSTRAINT constraint_name;删除约束，ALTER TABLE tbl_name DROP CONSTRAINT constraint_name;删除主键约束（特殊）：ALTER TABLE tbl_name DROP PRIMARY KEY;

 

 

 

 

设置外键约束时，主表的字段必须是主键，因此不能为空值。从表中外键字段的值必须来自主表中的相应字段的值，或者为空值，空值可以有多个。

设置唯一约束后只能有唯一一个空值；

 

 

查询语句：

查询：

1.基本查询语句：

    给字段设置别名：

    运算符和表达式：

    在select语句中使用运算符：

    带条件的查询：

    模糊查询：

    范围查询：

    对查询结果进行排序：

    case ... when   语句:

    decode函数:

2.在SQL*plus中设置格式：

3.查询表中的所有字段及置顶字段：

 

 

基本查询：select [distinct] column_name,.../* from tablename[where conditions]

去重复查询distinct

 

 

在SQLPLUS中设置格式（一次只能设置一个字段）

 

1、作用：配合select语句使用，设置查询结果显示的格式，不会修改数据库表字段。

2、设置字段显示样式，没有改变表中的值，只用于显示

语法：COLUMN column_name HEADING new_name;

Eg:column username heading 用户名

注：COLUMN 可以简写成COL

3、设置字段的格式化样式

语法：COLUMN column_name FORMAT dataformat;

注：如果是字符类型的值，只能设置它显示的长度,其中a后面跟数字表示字符串显示长度，例：a10 表示显示10位的长度的字符串；数字则用‘9’代表一位，例：999.9 表示整数位为三位，小数位为一位。如果设置的长度比数据的长度小，则数据显示为‘#’。还可以在数字前面加‘$’,例：$999.9 则查询后显示的结果会在结果前加上'$'符。

 

4、清除设置格式

语法：COLUMN column_name CLEAR;

 

 

查询表中所有字段及指定字段：

1.查询所有字段：

select * from table_name;

2.查询指定字段：

select column1, ... from table_name;

 

给字段设置别名：<br>

select column_name as(可省略) new_name, ... <br>

from table_name;<br>

去掉表中重复的数据：<br>

select distinct column_name as(可省略) new_name, ... <br>

from table_name;

 

 

表达式 = 操作数 + 运算符

操作数：常量、变量和字段

运算符：算术运算符（+,-,*,/）、比较运算符(>,<,=,>=,<=,<>)返回true false、逻辑运算符(and,or,not)返回true false

 

 

 

 

在select语句中使用运算符  

使用算术运算符，  

select id,username,salary+200 from users;这里是把salary里的值都加200，这个修改只是在查询结果中进行了修改，表中的原数值没有修改。

使用比较运算符，

select username from users where salary>800;在users表中查询salary的数值大于800的，显示出username。

使用逻辑运算符，   实际上就是连接表达式的，并且表达式都是通过比较运算符进行连接的表达式。

select username from users where salary>800 and salary<>1800.5;

"<>"是不等于

 

 

带条件的查询

单一条件的查询

select salary from users where username='aaa';查询在users表中username为aaa的salary的数值。

查询在users表中id为3的username和salary的数值：select salary,username from users  where id='3';

select username from users where salary<'5000';

多条件的查询：

查询username是aaa，或者salary的数值大于2000的信息，

select * from users where salary>2000or username='aaa';

查询username是aaa或者salary的数值在800到2000之间的信息，

select * from users where username='aaa' or (salary>800 and salary<=2000);

在逻辑运算符中not先运行在是and最后是or，比较运算符的优先级高于逻辑运算符。

查询除了username为aaa的其他信息。

select * from users where not(username='aaa');

select * from users where username<>'aaa';

 

模糊查询：

1、通配符的使用：

一个_只能代表一个字符，一个%可以代表0到多个任意字符

2、查询以a开头的用户名：

select * from users where username like 'a%';

3、查询第二个字符为a 的用户名：

select * from users where username like '_a%';

4，查询含有a的用户名：

select * from users where username like '%a%';

 

范围查询：

BETWEEN...AND   可以把小的值放到BETWEEN后面大的值放到AND后面，表示从什么值到什么值之间。

范围查询  

select * from users where salary between 800 and 2000;这个查询是一个闭合空间，包括800和2000

select * from users where salary not between 800 and 2000;查询不在800到2000之间。

 

IN/NOT IN   IN后面是一个小括号里面是一个列表的值，IN后面代表的不是一个范围而是一个具体的值。

查询username是aaa或者bbb的信息

select * from users where username in('aaa','bbb');等价与用或

查询username不是aaa或者bbb的信息

select * from users where username not in ('aaa','bbb');

 

 

对查询结果排序：

语法：SELECT [DISTINCT] column_name1,...* FROM table_name [WHERE conditions]

ORDER BY column_name1 DESC/ASC（升降）,...

其中，DESC是降序排序，ASC是升序排序，默认升序；当按多个字段排序时，order by col1,col2.也可以写成order by 1,2，...。（按表结构顺序中的第一个，第二个字段）

Select * from users order by id desc,salary asc;

 

 

1) 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;

2) CASE WHEN column_name=value1 THEN result1,...[ELSE result] END

 如：select username,

case when salary<800 then '工资低'

when salary>5000 then '工资高'

end as 工资水平 from users;

************第二种case...when语句更灵活，表达式运算符可以更丰富***********；

************字段值为字符时，需要用单引号括起来，但是对于as后的别名，作为字段名，不需要单引号括起来*************

 

decode与case...when类似

decode(列名，判断条件，条件为真的值，默认值)

decode(col_name,value1,result1,...,defaulvalue)

例如：

select username,decode(username,'aaa','计算机部门','bbb','市场部门','其他') as 部门 from users;

 

 

 

在SQL*Plus中，查询结果中给字段设置别名的方法有（）

在字段和别名之间使用AS关键字或使用column命令设置字段的别名。

 

课程总结：

1.用户与表空间：1）如何查看登录用户：show user命令 dba_users数据字典

2）启用scott用户 alter user scott account unlock

3）如何查看某个用户的默认表空间和临时表空间

4）表空间管理：创建、修改、删除表空间

2.表与约束：1)字段类型：①字符型：CHAR(n)、NCHAR(n)、VARCHAR2(n)、NVARCHAR2(n)

②数值型：NUMBER(p,s)、FLOAT(n)

③日期型：DATE、TIMESTAMP

④其他类型：BLOB、CLOB

2）对表的创建、修改和删除

3)对表中数据的操作：添加数据（INSERT)、修改数据（UPDATE)、删除数据（DELETE)

4)约束：非空约束(NOT NULL)，主键约束(PRIMARY)，外键约束(FOREIGN)，唯一约束（UNIQUE),检查约束（CHECK)

3.查询语句：模糊查询：LIKE关键字，通配符_和%

范围查询：(NOT)BETWEEN ...AND...和（NOT)IN

case...when语句和decode函数