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函数