Oracle数据库知识讲解
第一章:用户和表空间
1：登录部分
打开SQL PLUS(Oracle数据库里不分大小写)
Sys（权限要高于SYSTEM）和system两个用户名，密码默认是不显示的
当登录SYSTEM用户名时。我设置的密码是*1482675166,可以用connect sys/*1482671566 as sysdba; 进入最高权限的sys，而sys进入system可以不用输入as sysdba;
2：语法
显示当前用户名：Show user
启用用户的语句：Alter user username account unlock(lock)（scott用户默认的密码是tiger）
3：表空间概述
表空间的分类：
3.1永久表空间
3.2临时表空间
3.3 UNDO表空间
如何查看用户的表空间：
dba_tablespaces和user_tablespaces数据字典
dba_users和user_users数据字典
设置用户的默认和临时表空间：
ALTER USER username
DEFAULT|TEMPORARY
TABLESPACE tablespace_name
4创建表空间
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’temptest1file.dbf’ size 10M;
5修改表空间
修改表空间的状态：
设置联机和脱机状态:
ALTER TABLESPACE tablespace_name
ONLINE|OFFLINE;
设置只读或可读写状态：
ALTER TABLESPACE tablespace_name
READ ONLY|READ WRITE
修改数据文件：
增加数据文件：
ALTER TABLESPACE tablespace_name
ADD DATAFILE ’xx.dbf’ SIZE xx;
删除数据文件
ALTER TABLESPACE tablespace_name
DROP DATAFILE ’xx.dbf’ SIZE ;(不能删除创建表空间时的第一个文件，如果要删除，则把整个表空间删除掉)
删除表空间：
DROP TABLESPACE
tablespace_name [INCLUDING CONTENTS]
第二章:管理表
1 认识表
基本存储单位
二维结构
行和列
约定：
每一列数据必须是相同的数据类型
列名唯一
每一行数据的唯一性
2 数据类型
字符型，数值型，日期型，其他类型。
字符型：
CHAR(n),NCHAR(n) n代表字符的长度;
VARCHAR2(n) ,NVARCHAR2(n)
数值型:
NUMBER(p,s) p为有效数字，s为小数点后面的位数，如NUMBER(5，2),代表有效数字是5，保留2位有效数字，如123.45
FLOAT(n)
DATA(常用的日期类型) sysdate语句获取当前日期
TIMESTAMP(时间的精确描述)
其他类型：
BLOB
CLOB
3 管理表
创建表
基本语法：
CREATE TABLE table_name(column_name datatype , ...)
管理表
举例创建用户信息表：
所需字段
字段类型
编号 用户名 密码 邮箱 注册时间
代码如下：
Create table userinfo
(id number(6,0),
Username varchar(20),
Userpwd varchar2(20),
Email varchar2(30),
Regdate date);
表已创建
修改表
添加字段：
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;(这条语句是删除表中的全部数据，并不是将表删除掉，要比DELECT删除表快很多)
DROP TABLE table_name;(删除整个表结构)
第三章：操作表中的数据
1操作表中的数据
添加数据：
INSERT语句：
INSERT INTO table_name
(column1,column2,...)
VALUES(value1,value2,...)
操作实例
向表中的所有字段添加值:
Valuse(1,’xxx’,’123’,’xxx@126.com’,sysdate);
查看表的所有值：
Select * from userinfo;
向表中指定的字段添加值：
Insert into userinfo
Valuse(1,’xxx’,’123’,’xxx@126.com’,sysdate);
向表中添加默认值：
如：create table userinfo1
(id number(6,0),
Regdate date default sysdate);
表示日期默认值是当前日期
2复制表数据
在建表时复制:
CREATE TABLE table_new
AS
SELECT column1,...|* from table_old
在添加时复制:
INSERT INTO table_new
[(column1,column2,...)]
SELECT column1,column2,...|* from table_old;
3修改数据
UPDATE语句:
UPDATE table_name
SET column1=value1,...
[WHERE conditions]
操作实例
Update userinfo
Set userpwd=’111’;(将userpwd所有列的密码都设置为111)；
Update useinfo set userpwd=’123456’ where username=’xxx’;(将用户名是xxx的密码设置为123456)
4删除数据
DELECT FROM table_name [WHERE conditions]
无条件的删除：没有加where
有条件的删除：加了where语句
禁用唯一约束：DISABBLE|ENABLE CONSTRAINT constraint_name
彻底删除唯一约束：DROP CONSTRAINT constraint_name
1约束概述
1.1约束的作用：
1.1.1定义规则
1.1.2确保完整性
1.2非空约束
1.3主键约束
1.4外键约束
1.5唯一约束
1.6检查约束
2非空约束
2.1在创建表时设置非空约束：
CREATE TABLE table_name(column_name datatype NOT NULL,...);
2.2在修改表时添加非空约束:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
2.3在修改表时去除非空约束:
ALTER TABLE table_name MODIFY column_name datatype NULL;
3主键约束
3.1作用：
确保表当中每一行的数据的唯一性，一张表只能设置一个主键约束，主键约束可由多个字段构成（联合主键或复合主键）
3.2 在创建表时设置主键约束：
CREATE TABLE table_name(column_name datatype PRIMARY KEY);
或者CREATE TABLE table_name(column_name datatype,CONSTRAINT constraint_name PRIMARY KEY(column_name1,...));
4在修改表时添加主键约束
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name,...);
4.1 更改约束的名称:
RENAME CONSTRAINT old_name TO new_name
4.2 删除主键约束:
DISABLE|ENABLE CONSTRAINT constraint_name
或者DROP CONSTRAINT constraint_name
或者DROP PRIMARY KEY[CASCADE]
5.1 CREATE TABLE table1(column_name datatype REFERENCES table2(column_name),...);
说明：table1称为从表，table2称为主表，设置外键约束时，主表的字段必须是主键，主从表中相应的字段必须是同一数据类型，从表中外键字段的值必须来自主表中相应字段的值，或为null值
5.2 在创建表时设置外键约束
CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name(column_name)[ON DELETE CASCADE]
ADD CONSTRAINT constraint_name FOREIGN KEY(column) REFERENCES table_name(column_name)[ON DELETE CASCADE]
说明：constraint_name是自己设置的约束名称
7 删除外键约束
7.1禁用外键约束：DISABBLE|ENABLE CONSTRAINT constraint_name
7.2 彻底删除外检约束：DROP CONSTRAINT constraint_name
8 唯一约束
作用：保证字段的唯一性
唯一约束和主键约束的区别：
主键约束字段值必须是非空的
唯一约束允许有一个空值
主键约束在每张表中只能有一个
唯一约束可以有多个
9 在创建表时设置唯一约束
CREATE TABLE(column_name,datatype UNIQUE,...)
或者CONSTRAINT constraint_name UNIQUE(column_name)
10 在修改表时添加唯一约束
ADD CONSTRAINT constraint_name UNIQUE(column_name);
11 删除唯一约束
禁用唯一约束：DISABBLE|ENABLE CONSTRAINT constraint_name
彻底删除唯一约束：DROP CONSTRAINT constraint_name
12 在创建表时设置检查约束
作用：让字段的值有实际意义，比如：年龄：1000，肯定不行
CREATE TABLE table_name(column_name datatype CHECK(expressions),...);
或者CONSTRAINT constraint_name CHECK(expressions);
13 在修改表时添加检查约束
ADD CONSTRAINT constraint_name CHECK(expressions);
14 删除检查约束
禁用唯一约束：DISABBLE|ENABLE CONSTRAINT constraint_name
彻底删除唯一约束：DROP CONSTRAINT constraint_name
总结本章内容：
非空约束 NOT NULL
主键约束外键约束 PRIMARY KEY 一张表只能有一个
外键约束 FOREIGN KEY ... REFERENCES... 涉及两张表的关系
唯一约束 UNIQUE
检查约束 CHECK
常用到的数据字典：user_constraints
第四章：查询语句
1基本查询语句
查询语句：查询某张表中含有哪些约束
SELECT[DISTINCT] column_name1,...|* FROM table_name [WHERE conditions]
说明distinct的作用是不显示重复的字目
1在SQL/PLUS中设置格式
COLUMN column_name HEADING new_name(COLUMN可以简写成col)
COLUMN column_name FORMAT dataformat(说明：字符类型的只能设置显示的长度)
如：col username format a10;(用a开头，设置字段的长度)，
数值类型的用‘9’代表一个数字
如：col salary format 9999.9;
col salary format $9999.9;(在前面加上美元符号)
COLUMN column_name CLEAR(清除格式)；
所有字段：SELECT * FROM ..
指定字段：SELECT A,B,C... FROM ..
3给字段设置别名
SELECT column_name AS new_name,.... FROM table_name;
4 运算符和表达式
表达式=操作数+运算符
算术运算符（+，-，*，/）
Eg:select id,name,salary+200 from userinfo_3;(但是不会改变原来表中的数据)
比较运算符（<,>,<=,>=,=,<>）
select name from userinfo_3 where salary>15000;
逻辑运算符（and ,or,not）
select name from userinfo_3 where salary>15000 and salary>30000;
5 带条件的查询
条件查询：
select salary,... from userinfo_3 where name='*' or ...;
6 模糊查询
LIKE关键字
通配符的使用（_,%）
一个_只能代表一个字符，%可以代表0到多个任意字符
使用LIKE查询
Eg: select * from userinfo_3 where name like 'w%'(查询以w开头的用户名的信息)
Eg: select * from userinfo_3 where name like '_u%'(查询第二个字符是u的用户名的信息)
Eg: select * from userinfo_3 where name like '%u%'(含有u字符的用户名的信息)
BETWEEN AND(表示从...到...,闭合区间)：
Eg:select * from userinfo_3 where salary between 100000 and 70000;
Eg:select * from userinfo_3 where salary not between 100000 and 70000;
IN/NOT IN语句：
Eg:select * from userinfo_3 where name in(‘aaa’,’bbb’);(查询用户名是aaa或者是bbb的信息)
Eg:select * from userinfo_3 where name not in(‘aaa’,’bbb’);(查询用户名不是aaa且是bbb的信息)
8 对查询结果排序
SELECT ...FROM...[WHERE...]
ORDER BY column1 DESC/ASC,....
9 case...when语句的使用
CASE column_name When value1 then result1...[ELSE result] END；(在name和case之间必须加，号，在when 处不要加,)
Eg:
select name,case name when '*' then '计算机部门'
when 'lisi' then '市场部'
else '其他部门' end as 部门 from userinfo_3;
结果：
NAME 部门
---------------------------------------- ------------------------------
* 计算机部门
zhangsan 其他部门
lisi 市场部
* 计算机部门
CASE
WHEN column_name=values THEN result1,...[ELSE result] END;
Eg:
select name,case when salary>50000 then '高工资人群'
when salary<30000 and salary>15000 then '白领人群！'
else '你得加油了' end as 工资水平 from userinfo_3;
10 decode函数的使用
decode(column_name,values1,result1,...,defalutvalue)
Eg:
select name,decode(name,'*','计算机部门','lisi','市场部门'，'其他') as 部门 from userinfo_3;
oracle中如何插入date类型的数据？
insert into tabname(datecol) value(sysdate) ; -- 用date值
insert into tabname(datecol) value(sysdate+1)<img id="selectsearch-icon" src="https://gss0.bdstatic.com/70cFsjip0QIZ8tyhnq/img/iknow/qb/select-search.png" alt="搜索"> ; -- 用date值
insert into tabname(datecol) value(to_date('2014-02-14','yyyy-mm-dd')) ; -- 用to_date
insert into tabname(datecol) value(to_date('2014-02-14 20:47:00','yyyy-mm-dd hh24:mi:ss')) ; -- 用to_date
insert into tabname(datecol) value(to_date('20140214','yyyymmdd')) ; -- 用to_date
insert into tabname(datecol) value(to_date('20140214204700','yyyymmddhh24miss')) ; -- 用to_date