Oracle数据库之SQL基础

scott------tiger

使用系统用户system登录（数据库和服务器都安装在同一台机器上就不用server）

connect [username/password] [@server] [as sysdba|sysoper]

 

 

-----------查看登录用户------------

show user命令（命令不需要分号结尾，SQL语句要）

dba_users数据字典（是数据库提供的表，用于查看数据库的信息desc dba_users）

查看数据库字典中包含哪些用户（select username from dba_users;）

 

---------启用scott用户（默认是锁定的，要先启用）-------

启用/锁定用户的语句

alter user username account unlock|lock；  (username可以是任意用户)

即alter user scott account unlock；

使用scott登录

connect scott/tiger

 

-----------------表空间------------------

表空间是数据库的逻辑存储空间（在数据库中开辟一个空间用于存放数据库的对象）

表空间是由一个或多个数据文件构成的

 

表空间的分类：

永久表空间：数据库中要永久存储的一些对象

临时表空间：数据执行过程中存储

UNDO表空间：被修改前的数据

 

查看用户的表空间

系统用户dba_tablesspaces、普通用户user_tablespaces数据字典（用于查看表空间的信息 如desc dba_tablesspaces）

作为系统管理员的表空间有哪些：select tablespace_name from dba_tablespaces(普通用户同理)

 

dba_users、user_users数据字典

system用户的默认表空间和临时表空间是什么：

select default_tablespace,temporary_tablespace from dba_user where username

 

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

ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name(普通用户没有修改表空间的权限，要修改只能设置权限，否则只能使用系统用户修改表空间)

 

------------------创建表空间--------------------

CREATE[TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SIZE xx;(不指定路径，xx.dbf存放在默认的路径)

查看数据文件路径：desc dba_data_files

select file_ name from dba_data_files where tablespace_name="表空间名"

 

 

----------------------修改表空间----------------------

修改表空间的状设置联机或脱机状态（表空间处在脱机状态就不能使用它了）：

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE

 

设置表空间的只读或可读写状态

ALTER TABLESPACE tablespace_name READ ONLY|READ WRITE

 

查看表空间的状态：

select status（状态） from dba_tablespaces(表空间信息的数据字典) where tablespace_name='表空间名字'

 

-------------------修改表空间的数据文件------------------

增加数据文件

ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xxm;

 

删除数据文件（不能删除表空间中的第一个创建的数据文件，否则整个表空间删除）

ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf'

 

---------------删除表空间----------------

DROP TABLESPACE tablespace_name[INCLUDING CONTENTS(把数据文件也删除)]

 

-------------管理表----------------

【认识表】

表：基本存储单位，是二位结构，行（记录）列（域和字段）

约定：

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

2、列名唯一

3、每一行数据的唯一性

 

【数据类型】

字符型

CHAR(n<=2000)、用于存储汉字比较多NCHAR(n<=1000)

都是固定长度的类型（如果设置的n比较大，而输入的值长度又比较小就会浪费空间）

 

VARCHAR2(n<=4000)、NVARCHAR2(n<=2000)

是可变长度的数据

 

数值型

NUMBER(p,s) p:有效数字，s:小数点后的位数

 p表示数字中的有效位;

 如果s>0，表示数字精确到小数点右边的位数；s默认设置为0；如果scale<0，Oracle将把该数字取舍到小数点左边的指定位数。

 p的取值范围为【1---38】；s的取值范围为【-84---127】。

 NUMBER整数部分允许的长度为（p-s），无论s是正数还是负数。

 如果p<s，表示存储的是没有整数的小数。

 p表示有效位数，有效数位：从左边第一个不为0的数算起，小数点和负号不计入有效位数；s表示精确到多少位，指精确到小数点左边或右边多少位(+-决定)。

 

FLOAT(n)：主要用于存储二进制数（可以表示1~126位

 

日期型

DATE类型表示范围（可精确到秒）：公元前4712年1月1日~公元9999年12月31日

TIMESTAMP(可精确到小数秒)

 

其他类型

BLOB(可存放4G的数据，以二进制的形式存放)

CLOB(可存放4G的数据，以字符串的形式存放)

 

--------------创建表-----------------

CREATE TABLE table_name(同一用户下表名要唯一)

(

       column_name datatype，...（最后一列是没有符号的，中间用逗号隔开）

);

【创建用户信息表】

所需字段 编号，用户名，密码，邮箱，注册时间

create table userinfo

( id number(6,0)

username varchar2(20),

userpwd varchar2(20),

email varchar2(30),

regdate date);

 

------------------修改表--------------------

添加字段

ALTER TABLE table_name ADD column_name datatype;

如增加一个备注字段

alter table userinfo add remarks varchar2(500);

 

更改字段数据类型（长度或数据类型都可以更换）

ALTER TABLE table_name MODIFY column_name datatype;

如，更改remarks的长度为400

alter table userinfo modify remarks varchar2(400);

 

删除字段

ALTER TABLE table_name DROP COLUMN column_name;

如，删除remarks字段

alter table userinfo drop column remarks;

 

修改字段名

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

如，修改email字段名

alter table userinfo rename column email to new_email;

 

修改表名

RENAME table_name TO new_table_name;

 

-------------------------删除表-----------------------------

TRUNCATE TABLE table_name(删除表中的全部数据，并不是将表删除掉，也叫截断表)

DROP TABLE table_name(删除整张表)

 

-------------操作表中的数据------------------

【添加数据】

INSERT语句

INSERT INTO table_name

(column1,column2,...)

VALUES(value1,value2,...)

 

【操作实例】

1、向表中所有字段添加值：

insert into userinfo

values(1,'xxx','123','xxx@126.com',sysdate);

sysdate:用于获取系统当前时间

 

查看所有字段信息

select * from userinfo;

 

2、向表中指定字段添加值：

insert into userinfo(id,username,userpwd)

values(2,'yyy','123');

查看指定字段信息

select username，userpwd from userinfo;

 

3、向表中添加默认值

create table userinfo1

(id number(6,0),

regdate date default sysdate);

 

insert into userinfo1(id)

value(1);

 

然后regdate的值会赋上默认值

 

修改指定字段的默认值

alter table userinfo  modify email default '无';

 

【复制表数据】

在建表时复制

CREATE TABLE table_new

AS

SELECT column1,...|* FROM table_old

 

在添加时复制

INSERT INTO table_new [(column1,...)] SELECT column1,...|* FROM table_old

 

【修改数据】

UPDATE语句

UPDATE table_name SET column1=value1,...

[WHERE conditions]

无条件的更新：update userinfo set userpwd='111111';

有条件的更新：update userinfo set userpwd='123456' where username='XXX';

 

【删除数据】

DELETE语句

DELETE FROM table_name（删除表的所有数据）

[WHERE conditions]

 

--------------约束--------------

【非空约束】

在创建表时设置非空约束

CREATE TABLE table_name(

       column_name datatype NOT NULL,...

);

 

 

在修改表时添加非空约束

ALTER TABLE table_name

MODIFY column_name datatype NOT NULL；

 

在修改表时去除非空约束

ALTER TABLE table_name

MODIFY column_name datatype NULL;

 

 

【主键约束】

作用:确保表当中每一行数据的唯一性

一张表只能设计一个主键约束,并且非空和唯一

主键约束可以由多个字段构成（联合主键或复合主键）

 

 

【在创建表时设置主键约束】

CREATE TABLE table_name(

       column_name datatype PRIMARY KEY,...

)

 

 

【在创建表时设置联合主键约束】

CONSTRAINT constraint_name

PRIMARY KEY(column_name1,...)

 

constraint_name的名字一般以pk_开头

 

 

【在修改表时添加主键约束】

ADD CONSTRAINT constraint_name

PRIMARY KEY(column_name1,...);

 

更改约束的名称：

RENAME CONSTRAINT old_name TO new_name

 

禁用|启用主键约束：

ALTER TABLE table_name

DISABLE|ENABLE CONSTRAINT constraint_name

删除主键约束:

ALTER TABLE table_name

DROP CONSTAINT constraint_name

DROP PRIMARY KEY[CASCADE]       CASCADE 级联删除 其他表引用该字段的地方也删除

 

【在创建表时设置外键约束】

在列级设置外键约束：

CREATE TABLE table1

(column_name datatype REFERENCES

table2(column_name),...);

 

table1:从表

table2:主表

设置外键约束的时，主表的字段必须为主键

主从表中相应的字段必须是同一个数据类型

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

 

在表级设置外键约束：

CONSTRAINT constraint_name FOREIGN

KEY(column_name) REFERENCES

table_name(column_name)[ON DELETE CASCADE]

 

constraint_name:外键约束的名字，通常以fk开头，约束的名字要唯一

ON DELETE CASCADE：级联删除，主表当中的一条数据被删除之后，从表中使用了该字段的数据所在的行也会被删除

 

 

【在修改表时添加外键约束】

ALTER TABLE table_name

ADD CONSTRAINT constraint_name FOREIGN

KEY(column_name) REFERENCES

table_name(column_name)[ON DELETE CASCADE]

 

 

【删除外键约束】

禁用|启用|删除外键约束

DISABLE|ENABLE|DROP CONSTRAINT constraint_name

 

 

【在创建表时设置唯一约束】

唯一约束作用：保证字段值得唯一性

 

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

主键字段值必须是非空的

唯一约束允许有一个空值

主键在每张表中只能有一个

唯一约束在每张表中可以有多个

 

在列级进行唯一约束的设置：

CREATE TABLE table_name

(column_name datatype UNIQUE,...)

在表级进行唯一约束的设置：

CONSTRAINT constraint_name

UNIQUE(column_name)

 

 

【在修改表时添加检查约束】

ADD CONSTRAINT constraint_name

UNIQUE(column_name);

 

 

【删除唯一约束】

禁用|启用|删除唯一约束

DISABLE|ENABLE|DROP CONSTRAINT constraint_name

constraint_name:约束名字，与外键约束语法一样

 

 

【检查约束】

作用：让表中的值更具有实际意义

 

在创建表时设置检查约束

在列级设置检查约束：

CREATE TABLE table_name

(column_name datatype CHECK(expressions),

...)

expressions:约束条件

 

在表级设置检查约束：

CONSTRAINT constraint_name

CHECK(expressions);

 

在修改表时添加检查约束

ADD CONSTRAINT constraint_name

CHECK(expressions);

 

删除检查约束

禁用|启用|删除检查约束

DISABLE|ENABLE|DROP CONSTRAINT constraint_name

constraint_name:约束名字，与外键约束语法一样

 

--------------查询语句----------------

【基本查询语句】

SELECT [DISTINCT] column_name1,...|*

FROM table_name

[WHERE conditions]

 

[DISTINCT] :不显示重复的信息

 

 

【查询语句之在sql/plus中设置格式】

COLUMN可以简写COL

 

COLUMN column_name HEADING new_name

 

COLUMN column_name FORMAT dataformat

--字符类型只能设置显示的长度

如：col username format a10;

--数值类型用“9”代表一个数字

col salary format 9999.9;

 

清除之前设置的格式

COLUMN column_name CLEAR

 

 

【查询表中的所有字段及指定字段】

查询所有字段：

SELECT * FROM table_name;

 

查询指定字段

SELECT column_name1,column_name2,... from users;

 

 

【给字段设置别名】

SELECT column_name AS new_name,...

FROM table_name

AS可以省略，用空格隔开原来的字段名和新字段名即可

 

 

【运算符和表达式】

表达式=操作数+运算符

Oracle中的操作数可以有变量、常量和字段

 

算术运算符（+，-，*，/)

比较运算符（>,>=,<,<=,=,<>）用在where语句中  <>:表示不等于

逻辑运算符（优先级递减：not,and,or）比较运算符的优先级高于逻辑运算符

 

 

【在select语句中使用运算符】

使用算术运算符：

select id,username,salary+200 from users;

只是在查询结果中修改

 

使用比较运算符：

select username from users where salary>800;

 

使用逻辑运算符：

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

 

 

【带条件的查询】

单一条件的查询：

多条件的查询：

 

 

【模糊查询】

通配符的使用（_,%）:一个_只能代表一个字符，%可以代表0到多个任意字符

使用LIKE查询

 

例：查询用户名以a开头的用户信息

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

例：查询用户名第二个字符为a的用户信息

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

例：查询用户名含有a的用户信息

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

 

 

【范围查询】

BETWEENT...AND

例：查询薪水在800到2000的用户信息(是闭合区间)

select * from users where salary between 800 and 2000;

例：查询薪水不在800到2000的用户信息

select * from users where salary not between 800 and 2000;

 

IN/NOT IN:里面的值并不是范围，而是一个具体的值

例：查询用户名是aaa或者bbb的用户信息

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

 

 

【对查询结果进行排序】

SELECT...FROM...[WHERE...]

ORDER BY column1 DESC/ASC,...

 

DESC：降序排列

ASC：升序排列

可以根据多个字段进行排序，只要第一个字段的值相同时，才排第二个字段，以此类推

 

 

【case...when语句】

--第一种形式

CASE colum_name

WHEN value1 THEN result1,...

[ELSE result] END

 

select username,case username when 'aaa' then '计算机部门'

when 'bbb' then '市场部门' else '其他部门' end as 部门

from users;

 

--第二种形式

CASE

WHEN colum_name=value1

THEN result1,...[ELSE result] END

 

selecct usename,case when username='aaa' then '计算机部门'

when username='bbb' then '市场部门' else '其他部门' end as 部门

from users;

 

 

【decode函数的使用】

decode(columu_name,value1,result1,

...,defaultvalue)

 

defaultvalue:默认值

 

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

from users;