数据定义语言(DDL)
CREATE:CREATE TABLE test(a varchar2(10),b varchar2(10));
ALTER: ALTER TABLE test ADD c number;
DROP: DROP TABLE test 或 ALTER TABLE test DROP COLUMN b;
数据控制语言(DCL)
GRANT: GRANT SELECT ON test TO testuser;
REVOKE: REVOKE SELECT ON test FROM testuser;(收回授权)
数据操作语言(DML)
SELECT: SELECT * FROM test;
INSERT: INSERT INTO test(a,b) values('lcd','er'); ##给test表的a,b列分别插入数据lcd 和 er
DELETE: DELETE FROM test; ##清空表
DELETE FROM test WHERE a='lcd'; ##删除a=lcd这一行的数据
UPDATE: UPDATE test SET b='ttt'; ##把b列所有数据改为ttt
UPDATE test SET b='ttt' WHERE a='lcd'; ##把a=lcd这一行的b改为ttt
ORACLE中常用的系统函数
1.字符
length: SELECT LENGTH('ABCD好F') FROM test; ##结果为6
SELECT LENGTHB('ABCD好F') FROM test; ##结果为7
ltrim: SELECT LTRIM(' ABCD好F') FROM test; ##去掉查询内容最左边的空格 会输出ABCD好F
SELECT LENGTH(LTRIM('ABCD好F')) FROM test; ##会输出7而不是8
同理,rtrim表示去掉最右边的空格,trim表示去掉两边的空格
substr: SELECT SUBSTR('adcdefg',2,3) from test; ##从查询内容第二个字符开始取,取三个 所以输出为bcd
SELECT SUBSTR('abcdefg',LENGTH('abcdefg')-3+1,3) FROM test; ##实现了右取串
2.日期
sysdate: SELECT sysdate FROM test; ##查看当前数据库所在的服务器的当前时间
current_date:功能类似sysdate
next_date: SELECT netxt_date(sysdate,'星期三') FROM test;##输出给定时间的下一个星期三是几号
3.转换
to_char: SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM test; ##将输出结果转换为char类型,并且以固定格式输出
to_date:转换为日期型
to_number:转换为数值型 PS.必须是可以转换为数字的数字字符型参数比如'333'可以 而'sss'不可以
4.聚集函数
sum: SELECT sum(price) FROM books; ##输出price列求和
avg: SELECT avg(price) FROM books; ##输出price列平均值
max: SELECT max(price) FROM books; ##输出price列中最大值
min: SELECT min(price) FROM books; ##输出price列中最小值
count: SELECT count(price) FROM books; ##输出price列行数
SELECT count(*) FROM books; ##输出表的行数 因为有时候字段的行数与表的行数不同
5.其他
user: SELECT user FROM test; ##查看当前登录账号
decode:SELECT SUM(DECODE(SEX,'男',1,0)) FROM test; ##查找test中SEX列,若为'男'则计数,若不为'男'则记0,然后sum统计 即可统计出男有多少人
nvl: SELECT a1,nvl(a2,'未输入') FROM test; ##让a2中没有数据的行显示'未输入'
SELECT * FROM test WHERE a2 IS NULL; ##选择a2没有数据的行输出 PS.不可写为a2=NULL
同理 SELECT * FROM test WHERE a2 IS NOT NULL
order: SELECT * FROM test ORDER BY a1 ASC; ##按a1列的升序输出
SELECT * FROM test ORDER BY a1 DESC; ##按a1列的降序输出
distinct: SELECT DISTINCT a1 FROM test;##输出a1的无重复结果(屏蔽重复的数据)
分组查询:
假设现在有一个books表,有id,name,price,数量,出版社5个字段
1.以出版社为分组,查询每个出版社的图书总价值:
SELECT 出版社, sum(price*数量) FROM books GROUP BY 出版社;
在这里,select后面出现的字段,groupby后一定要出现,相反,groupy后可以出现select后没出现的字段
如:SELECT 出版社, name,sum(price*数量) FROM books GROUP BY 出版社; 就会报错 因为 name
然而 SELECT 出版社, sum(price*数量) FROM books GROUP BY 出版社, name; 就可以,会以name和出版社分组
我们还可以在GROUPBY前加上WHERE条件:
SELECT 出版社,sum(price*数量) FROM books WHERE price>30 GROUP BY 出版社; 会将单价大于30的书以出版社为分组依据,查询每个出版社的单价超过30的图书的总价值
但是,WHERE后不可以跟任何函数,如:
SELECT 出版社,sum(price*数量) FROM books WHERE sum(price)>30 GROUP BY 出版社; 会报错,因为WHERE后不可以跟sum函数 若想实现此功能,可以如下操作:
SELECT 出版社,sum(price*数量) FROM books GROUP BY 出版社 HAVING sum(price)>30; 即可
模糊查询:
SELECT * FROM test WHERE a1 LIKE 'a_'; ##查询test表中a1列形如a7,aa,ac之类a后跟一个字符的数据
SELECT * FROM test WHERE a1 LIKE 'a__'; ##查询test表中a1列形如a78,a8c,acd之类a后跟两个字符的
SELECT * FROM test WHERE a1 LIKE 'a%'; ##查询test表中a1列a后跟任意多个字符的数据
SELECT * FROM test WHERE a1 LIKE '%a%'; ##查询test表中a1列只要包含字符a的数据
表的连接:
表的连接分为内连接和外连接
内连接:连接的两个表,对应的相匹配的字段完全相同
外连接:外连接分为左外连接和右外连接
左外连接:
右外连接:
假设有如下两张表,e为员工表,d为部门表
如果我们想查询如下信息,不使用表的连接的话,就无法展示部门的名字,很不直观
使用表的连接,则可以达成此目的:
但是注意,在表连接时,两张表上同样的列名会引起报错ambigiouslu define 所以需要d.name
PS.我们会发现,由于是内连接,第五行数据e.id是55而d.id是05,所以第五行数据没有显示
本例是内连接的不标准写法:
SELECT eid 编号, ename 姓名, sex 性别, d.name 所在部门 FROM e,d WHERE e.id=d.id;
标准写法如下:
SELECT eid 编号, ename 姓名, sex 性别, d.name 所在部门 FROM e JOIN d ON e.id=d.id;
左外连接:
在右边的表后面加上(+),就可以实现能匹配的正常显示,匹配不上的行左表正常显示,右表空格替代
右连接:
在左边的表后面加上(+),就可以实现能匹配的正常显示,匹配不上的行右表正常显示,左表空格替代
子查询:指查询条件是另一条查询
无关子查询:
指查询条件是另一条查询,并且括号里的查询与外部查询没任何关系,本图查询是指查询id在部门表id字段查询结果里的员工信息
相关子查询:
指查询条件是另一条查询,并且括号里的查询与外部查询紧密相关。
同理,可以取反,NOT IN:
无关子查询的另一种写法:
本图意为:括号里的查询如果有存在数据则无条件输出外部查询,否则不输出外部查询
同上,相关子查询的另一种写法:
PS.子查询的IN写法和EXISTS写法的区别就是是否可以在括号里的查询使用SELECT*
一些其他查询方式:
1.将两个表的查询放在一起输出:UNION
当然,这种方式在查询两个表都存在的字段时也会去冗余:
2.显示两个查询都出现的行:INTERSECT (ORACLE独有)
一些妖路子:
1.多行插入数据的方式:
把另一张表的查询结果插入到本表
INSERT INTO e(eid,ename) SELECT id,name FROM d;
2.创建有数据表的创建方式:
把另一张表的查询结果直接创建到新表中
CREATE TABLE newtable AS (SELECT * FROM e);
关于数据类型:
varchar2可变长 而char定长