用户与权限

创建用户

--创建用户
CREATE USER USER_NAME IDENTIFIED BY PASSWD;

修改密码

--修改新密码
ALTER USER USER_NAME IDENTIFIED BY NEW_PASSWD;

--修改用户密码
PASSWD USER_NAME;

删除用户

--可选参数 CASCADE
DROP USER USER_NAME [CASCADE];

--注意:
/*
在进行删除用户操作时,如果此用户已创建表,删除时需要加参数“CASCADE”,它具有级联的作用
*/

给用户赋权限

GRANT 权限/角色 TO USER_NAME;

收回用户权限

REVOKE 权限/角色 FROM USER_NAME;

系统权限

-- “系统权限是数据库管理相关的权限”
CREATE SESSION --登录权限
CREATE TABLE --建表权限
CREATE INDEX --创建索引权限
CREATE VIEW --创建视图权限
CREATE SEQUENCE --创建序列权限
CREATE TRRIGER --创建触发器权限

连接角色

--“是授予用户的最基本的权利,能够连接到Oracle数据中,能够访问其他用户的表权限时”
CREATE SESSION --创建会话
CREATE VIEW --创建视图
CREATE SEQUENCE --创建序列

资源角色

--“具有创建表、序列、视图的权限”
CREATE TABLE --创建表
CREATE TRIGGER --创建触发器
CREATE PROCEDURE --创建过程
CREATE SEQUENCE --创建序列
CREATE TYPE --创建类型

DBA角色

--“是授予系统管理员的,拥有该角色的用户即系统管理员,拥有系统的所有权限”

表空间

--创建表空间
CREATE TABLESPACE SPACE_NAME

--DATAFILE '/' 指向数据文件路径
--SIZE N(M) 表示初始化表空间为N(M)
--AUTOEXTEND ON NEXT 2M 自动扩展,每次扩展2M
--MAXSIZE UNLIMITED UNLIMITED最大扩展没有限制,N(M)最大扩展到N(M)

创建用户指定默认表空间

CREATE USER USER_NAME IDENTIFIED BY PASSWD DEFAULT TABLESPACE SPACE_NAME

修改用户默认表空间

ALTER USER USER_NAME IDENTIDIED BY PASSWD DEFAULT TABLESPACE SPACE_NAME

查看表表空间

SELECT * FROM v$TABLESPACE

查看用户默认表空间

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME = 'SCOTT';
--用户名SCOTT必须为大写

表结构操作

--创建表1
CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE
);

--创建表2
CREATE TABLE TABLE_NAME AS SELECT COLUMN_1, COLUMN_2 ...FROM TABLE_NAME;

--修改表
ALTER TABLE 语句添加、修改或删除列的语法

--添加列
ALTER TABLE TABLE_NAME ADD(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE
);

ALTER TABLE TABLE_NAME ADD COLUMN_1 DATA_TYPE;

--修改列
ALTER TABLE TABLE_NAME MODIFY(

 COLUMN_1 DATA_TYPE

);

--删除列
ALTER TABLE TABLE_NAME DROP(

 COLUMN_1,

COLUMN_2
);

--修改表名称
RENAME TABLE TO NEW_TABLE_NAME

--修改列名
ALTER TABLE TABLE_NAME RENAME COLUMN OLD_COLUMN TO NEW COLUMN;

--查看表结构
DESC 表名;

约束

约束

  • 非空约束(NOT NULL)
  • 唯一约束(UNIQUE)
  • 主键约束(PRIMARY KEY)
  • 外键约束(FOREIGN KEY)
  • 条件约束(CHECK)
  • 约束存在表中(USER_CONSTRAINTS)

非空约束(NOT NULL)

--添加非空
CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE NOT NULL
);

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NOT NULL;

--删除非空约束
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NULL;

唯一约束(UNIQUE)

--添加唯一
CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE UNIQUE
);

--修改唯一
ALTER TABLE TABLE_NAME ADDCONSTRAINT sUNIQUE_NAME UNIQUE (COLUMN_1, COLUMN_2);

--删除唯一
ALTER TABLE TABLE_NAME DROP CONSTRINT UNIQUE_NAME;
--备注:Oracle中,UNIQUE可以为单个NULL,也可多行为NULL

主键约束(PRIMARY KEY)

--添加主键
CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE PRIMARY KEY
);

CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE,
CONSTRAINT 约束名 PRIMARY KEY (COLUMN_1, COLUMN_2)
);

--修改主键
ALTER TABLE TABLE ADD CONSTRAINT 约束名 PRIMARY KEY (COLUMN_1, COLUMN_2);

--删除主键
ALTER TABLE TABLE_NAME DROP PRIMARY KEY CASCADE;
--备注:如果两表存在主从关系,删除主键约束时,需要加上CASCADE

/*
注意:每张表有且只有一个主键约束。
特别说明:

 PRIMARY KEY 与 UNIQUE 的区别:
 1、一张表可以对应多个UNIQUE(唯一约束)
 2、一张表只要存在一个主键
 3、设置为主键的列不能存在NULL值

*/

外键约束(FOREIGN KEY)

外键约束的作用:用来维护从表与主表之间的引用完整性,能够维护数据库的数据一致性,数据完整性,防止错误数据进库。

解释:用于定义主表和欧辰那个表之间的关系,外键约束要定义在从表上,主表则必须具有主键约束或UNIQUE约束,当i当以外键约束后,要求外键列数据必须在主表的主键列存在或为NULL。

--添加主键
CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE REFERENCES MAIN_TABLEE_NAME(COLUMN)
);

CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE,
CONSTRAINT 约束名 FOREIGN KEY(COLUMN) REFERENCES, MAIN_TABLE_NAME(COLUMN))
);

--修改主键
ALTER TABLE TABLE_NAME CONSTRAINT 约束名 FOREIGN KEY(COLUMN) REFERENCES MAIN_TABLE_NAME(COLUMN);

--删除主键
ALTER TABLE TABLE_NAME DROP CONSTRAINT 约束名称;
/*
特别说明:FOREIGN KEY外键细节

 1、外键指向主键列
 2、外键可指向UNIQUE列
 3、建表时先建主表,再建立从表,删除时,先删除从表,再删除主表
 4、外键列属性值要与主键或UNIQUE列属性值的类型保持一致
 5、外键列的值,必须再主键列中存在,但外键列的值运行为NULL

*/

条件约束(CHECK)

--添加条件
CREATE TABLE TABLE_NAME(

 COLUMN_1 DATA_TYPE,

COLUMN_2 DATA_TYPE CHECK (COLUMN_2 IN (VALUE1, VALUE2...))
);

--修改条件
ALTER TABLE TABLE_NAME ADD CONSTRAINT 约束名 CHECK(COLUMN IN (VALUE1, VALUE2...))

--删除条件
ALTER TABLE TABLE_NAME DROP CONSTRAINT 约束名称;

约束命名规则

  • 非空约束:NN_表名__列名
  • 唯一约束:UK_表名__列名
  • 主键约束:PK_表名
  • 外键约束:FK_表名__列名
  • 条件约束:CK_表名__列名

查看约束信息

SELECT * FROM USER_CONSTRAINT WHERE TABLE_NAME = "TABLE_NAMWE";

数据操作

插入数据

INSERT INTO TABLE_NAME[(COLUMN [, COLUMN 2...])] VALUE(VALUE [, VALUE 2...]);

插入全部数据

INSERT INTO TABLE_NAME VALUES(VALUE[, VALUE 2...]);

从另一张表中导入数据

INSERT INTO TABLE_NAME1 (SELECT VALUE1, VALUE2, VALUE3,....FROM TABLE_NAME2);

修改数据

UPDATE TABLE_NAME SET COLUMN_NAME = EXP(表达式) , COLUMN 2 = EXP 2,....;

删除数据

DELETE FROM TABLE_NAME [WHERE 条件表达式];

--删除的几种方式
DELETE FROM TABLE_NAME;

--删除所有记录,表结构还在,写日志,可以恢复,但速度较慢
DROP TABLE TABLE_NAME

--删除表的结构和数据
TRUNCATE TABLE TABLE_NAME;
--删除表中的所有记录,表接哦古还在,不屑日志,无法找回删除记录,速度块

查询数据

基本语法

SELECT

 [DISTINCT] *|{COLUMN1(), COLUMN2()}

FROM TABLE_NAME AS AS_NAME

 [WHERE {条件}];
 

/*
“解释”

 1、SELECT 指向列进行查询
 2、COLUMN 指定列名
 3、* 代表查询所有数据
 4、FROM 指定查询表源
 5、DISTINCT 是否进行数据去重
 6、WHERE 条件
 7、AS 别名    

*/

--使用列别名
SELECT

 ENAME AS "姓名",
 SAL * 12 + NVL(COMM, 0) * 13 AS "年收入"

FROM

 EMP;

/*
解释:

 Oracle在使用别名时,可以使用双引号或不使用, 或使用 AS 来表明别名,但是不能使用单引号。

*/

处理NULL值

NVL函数:用于处理NULL值使用

SELECT ENAME, SAL 13(COMM, 0) 13 FROM EMP;
--NVL(VALUE1, VALUE2)
--解释:NVL值VALUE1为NULL时则取值VALUE2,VALUE1部位NULL时则取值VALUE1原值

拼接字符串

在进行查询时,希望将多列内容结果作为一列内容结果进行返回时。可使用 ” || “进行连接。

SELECT ENAME || "年收入" || SAL 13 + NVL(COMM, 0) 13 "雇员的年收入" FROM EMP;

取范围内的值

在进行查询时,想获取一个范围内的数据时,可使用BETWEEN

SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 2500;
--BETWEEN AND 指定区间内取值

LIKE操作符

%:表示任意0到多个字符

_:表示任意单个字符

--如何显示首字符为S的员工姓名和工资
SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE 'S%';

--如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ENAME, SAL FROM EMP WHERE ENAME LIKE 'O%';

区间判断

在WHERE语句中使用IN

SELECT * FROM EMP WHERE EMPNO IN (123, 345, 800);
--注意:IN只能放1000个值

操作符(IS NULL)

在查询语句中不能使用=或者!= NULL来进行判空

SELECT * FROM EMP WHERE MAGE IS NULL;

SELECT * FROM EMP WHERE MAGE IS NOT NULL;

逻辑操作符

--查询薪资高于100或职位为CODE的人员,同时还要满足姓名首字母大写J的
SELECT * FROM EMP WHERE (SAL > 100 OR JOB = 'CODE') AND (ENAME LIKE 'J%');

排序

使用ORDER BY进行排序(ASC写或不屑都是升序排序即从小到大,DESC则是降序排序从大到小排序)

SELECT * FROM EMP ORDER BY SAL SAC;

--使用别名排序
SELECT ENAME, SAL * 12 "年薪" FROM EMP ORDER BY "年薪" ASC;
--备注:别名使用需要用 ""

分组查询

在进行复杂的数据统计时,往往需要使用分组函数,如:MAX(), MIN(), AVG(), SUM(), COUNT()等

MAX(), MIN()

取最大值和取最小值

--如何显示所有员工最高工资和最低工资
SELECT MAX(SAL) "最高工资", MIN(SAL) "最低工资" FORM EMP;

--查询最高年工资
SELECT MAX(SAL 13 + NVL(COMM, 0) 13) "最高年工资", MIN(SAL 13 + NVL(COMM, 0) 13) "最低年工资" FROM EMP;

AVG()

求平均值

--显示平均工资和工资总和
SELECT AVG(SAL) "平均工资", SUM(SAL) "工资总和" FROM EMP;
--备注:AVG(SAL)不会把SAL为NULL的行进行统计,如果为空值也需要考虑,则可以
SELECT SUM(SAL) COUNT(*) FROM EMP;

COUNT(*)

计算总数

SELECT COUNT(*) "共有员工" FROM EMP;

GROUP BY

对查询结果进行分组统计

HAVING:限制(过滤)分组显示结果

--显示每个部门的平均工资和最高工资
SELECT AVG(SAL) "平均工资", MAX(SAL) "最高工资", DEMPNO "部门编号" FROM EMP GROUP BY DEPTNO;

/*
分组函数总结:

 1、分组函数(AVG)只能出现在选中列表、HAVING、ORDER BY子句中
 2、如果在SELECT 语句中同时包含GROUP BY/ HAVING/ ORDER BY,其顺序为GROUP BY/ HAVING/ ORDER BY
 3、在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在GROUP BY子句中,否则会报错

*/

函数

ASCII()

返回与指定的字符对应的十进制

SELECT ASCII('A') A, ASCII('a') a, ASCII('0') ZERO, ASCII(' ') SPACE FROM DUAL;

CHR()

给出整数,返回对应的字符

SELECT CHR(54740) ZHAO, CHR(65) CHR65 FROM DUAL;

CONCAT()

连接连个字符串,与 || 作用相同

SELECT CONCAT('HELLO', 'WORLD') FROM DUAL;

INITCAP()

返回字符串并将字符串的第一个字母变成大写

SELECT INITCAP('SMITH') UPP FROM DUAL;

INSTR(C1, C2, J)

在一个字符中搜索指定的字符,返回发现指定的字符位置

/*
C1 被搜索的字符串
C2 希望被搜索的字符串
L 搜索的开始位置,默认为1
J 第J次出现的位置,默认为1
*/
SELECT INSTR('ORACLE TRAING', 'RA', 1, 2) INSTRING FROM DUAL;

LENGTH()

返回字符串的长度

SELECT ENAME, LENGTH(ENAME) FROM EMP;

LOWER()

返回字符串,并将所有的字符小写

SELECT LOWER('AaBbCc') "LOWER" FROM DUAL;

UPPER()

返回字符串,并将所有的字符大写

SELECT UPPER('AaBbCc') "UPPER" FROM DUAL;

粘贴字符

RAPD在列的右边粘贴字符, RAPD(“显示内容”或字段, 显示长度, “填充占位符”)

LAPD在列的右边粘贴字符, LAPD(“显示内容”或字段, 显示长度, “填充占位符”)

SELECT LAPD(RAPD('MNB', 10, "*"), 17, "=") FROM DUAL;

删除字符

ltrim 删除左边出现的字符串 ltrim('原内容’或字段,‘要删除的字符串’)

rtrim 删除右边出现的字符串 rtrim('原内容’或字段,‘要删除的字符串’)

SELECT RTRIM('NBA CODE', '*') FROM DUAL;

截取字符串

SUBSTR截取子字符串,从STRAT开始,取COUNT个

SELECT SUBSTR('1110000000', 3, 8) FROM DUAL;

替换字符串

REPLACE(‘STRING’, ‘S1’, ‘S2’)

/*
STRING 希望被替换的字符或常量
S1 被替换的字符串
S2 要替换的字符串
*/
SELECT REPLACE('i L C', 'i', 'I') FROM DUAL;

TRIM

TRIM(‘S’ FROM ‘STRING’)

如果不指定参数,默认为空格符

SELECT TRIM(0 FROM 0009) "TRIM EXAMPLE" FROM DUAL;

数学函数

CEIL()

向上取整:返回大于或等于给出数字的最小整数

SELECT CEIL(3.14159) FROM DUAL

FLOOR()

向下取整:对给定的数字取整数

SELECT FLOOR(2345.67) FROM DUAL;

TRUNC()

精度截取:按照指定的精度截取一个数

SELECT TRUNC(124,1666, -2), TRUNC(124,16666, 2) FROM DUAL;

ROUND()

按照指定的精度进行舍入

ROUND函数为四舍五入

TRUNC函数直接截取

SELECT ROUND(55.5), ROUND(-55.4), TRUNC(-55.5), TRUNC(55.5) FROM DUAL;

ABS()

返回指定值的绝对值

SELECT ABS(-100) FROM DUAL;

ACOS()

返回反余弦的值

SELECT ACOS(-1) FROM DUAL;

ASIN()

返回正余弦的值

SELECT ASIN(0.5) FROM DUAL;

ATAN()

返回反正切的值

SELECT ATAN(1) FROM DUAL;

COS()

返回余弦值

SELECT COS(-3.14159265) FROM DUAL;

MOD()

返回一个N1除以N2的余数(取模)

SELECT MOD(10, 3), MOD(3,3) FROM DUAL;

日期函数

日期函数用于处理DATE类型的数据

ADD_MONTHS(日期值, 增加(减少))

减去或减去月份

SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD') FROM DUAL;

MONTHS_BETWEEN(DATE2, DATE1)

给出DATE2-DATE1的月份,计算总的月份数

SELECT MONTHS_BETWEEN('1999-12-01', '2000-02-19') MON_BET FROM DUAL;

SYSDATE

系统的当前时间

SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

日期转换函数

TO_CHAR(DATE, ‘FORMAT’)

日期类型转换成字符串格(主要用于将日期以习惯的格式输出显示)

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
/*
TO_CHAR()
解释说明:

 1、YY:两位数字的年份 2004 --04
 2、YYYY:四位数字的年份 2004年
 3、MM:两位数字的月份 8月 --08
 4、DD:两位数字的天数 30日 --30
 5、HH24:二十四小时制 8点 --20
 6、HH12:十二小时制 8点 --08 MI:SS --显示分钟/秒
 7、DAY:显示星期几
 8、MONTH:显示月份
 9、YEAR:显示年

数字格式:

 1、9:显示数字,并忽略前面的0
 2、0:显示数字,如位数不足,则用0补齐
 3、.:在指定的位置显示小数点
 4、,:在指定的位置显示逗号
 5、$:在数字前面加美元符号
 6、L:在数字前面加本地货币符号
 7、C:在数字前面加国际货币符号
 8、G:在指定位置显示组分隔符
 9、D:在指定位置显示小数点符号(.)
 

说明:
,逗号.和小数点可以合在一起使用,G 分隔符和 D 小数点符可以合在一起使用,但,.不能和 GD, 综合使用,否则报错。
*/

TO_DATE(STRING, ‘FORMAT’)

将字符串转换成日期(主要用于将日期按习惯的格式输入到数据库中)

TO_NUMBER

将给出的数字类型的字符串转换为数字

系统函数

DECODE()

类似与JAVA中的SWITCH CASE分支语句

DECODE(

 VALUE, IF1, THEN1, IF2, THEN2, IF3, THEN3, ... ELSE

)

--若VALUE为0,则为1,否则为2
DECODE(VALUE, '0', '1', '2')

表连接

自连接

指在同一种表的来凝结查询(把一张表看两张表)

--显示职员上级领导的姓名
SELECT E2.ENAME FROM EMP E1, EMP E2 WHERE E1.MAGE = E2.EMPNO;

内连接(INNER JOIN ON)

基本语法:

SELECT COLUMN_NAME1, ... FROM TABLE_NAME INNER JOIN TABLE_NAME ON CONDITION;
--说明:内连接只要两张表同时满足条件曹辉被查询到

--显示职员的信息和部门名称
SELECT E.*, D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO

左外连接(LEFT JOIN ON)

如果查询出左表完全显示,称为左外连接

基本语法:

SELECT COLUMN_NAME_1, COLUMN_NAME_2,...FROM TABLE_NAME_1 LEFT JOIN TABLR_NAME_2 ON CONDITION;

SELECT COLUMN_NAME_1, COLUMN_NAME_2,...FROM TABLE_NAME_1, TABLE_NAME_2 WHERE CONDITON_1 = CONDITION_2(+);

右外连接

如果查询的表右侧完全显示,称为右外连接

基本语法:

SELECT COLUMN_NAME_1, COLUMN_NAME_2, ...FROM TABLE_NAME_1 RIGHT JOIN TABLE_NAME_2 ON CONDITION;

SELECT COLUMN_NAME_1, COLUMN_NAME_2 TABLR_NAME_1, TABLE_NAME_2 WHERE CONDITION_1(+) = CONDITION_2;

完全外连(FULL OUTER JOIN ON)

完全显示两张表,没有匹配的记录记录为空

基本语法:

SELECT COLUMN_NAME_1, COLUMN_NAME_2, ... FROM TABLE_NAME_1 FULL OUTER JOIN TABLE_NAME_2 ON CONDITION;

分页查询

SELECT T2. FROM (SELECT T1., ROWNUM RN FROM (SELECT * FROM TABLE_NAME) t1

WHERE RN<=大范围(取到多少条数据)) T2 WHERE RN>=小范围(从第几条数据开始取);

/*
说明:Oracle分页查询时通过三层筛选进行查询的。每一次都可以带WHERE条件来对目标数据进行筛选。
1、第一层:构建所需查询字段信息并排序
2、第二层:构建ROWNUM, 别名为RN
3、第三层:加WHERE条件, RN >= M AND M <= N
*/

视图

创建视图基本语法:

CREATE VIEW VIEW_NAME AS SELECT 语句 [WITH READ ONLY]
--说明:WITH READ ONLY表示只进行查询操作

创建或修改视图的基本语法:

CREATE OR REPLACE VIEW VIEW_NAME AS SELECT 语句 [WITH READ ONLY]
--说明:WITH READ ONLY表示只进行查询操作

删除视图的基本语法:

DROP VIEW VIEW_NAME

创建序列

基本语法:

-----------------------------------------
------创建序列
-----------------------------------------
CREATE SEQUENCE CREATE_SEQUENCE_NAME_ROW_NUM_AUTOINC --序列名称
INCREMENT BY 1 --每次增长多少
START WITH 1 --从几开始
MINVALUE 1 --最小值
NOMAXVALUE --无最大值
NO CYCLE --序列到达最大值之后怎么办,CYCLE
CACHE 10 --需要不需要使用缓

创建触发器

基本语法:

-----------------------------------------
------创建触发器
-----------------------------------------
CREATE OR REPLACE TRIGGER insert_CREATE_TRIGGER_NAME_ROW_NUM_AUTOINC
BEFORE INSERT ON CREATE_TRIGGER_NAME
FOR EACH ROW
BEGIN
SELECT CREATE_TRIGGER_NAME_ROW_NUM_autoinc.NEXTVAL INTO :NEW.ROW_NUM FROM DUAL;
END;

除了这些命令,还可以参考命令手册]http://reference.ch1na.top/

分类: 数据库 标签: 数据库Oracle

评论

-- 评论已关闭 --

目录