Oracle-Sql基本语法

1、SQL书写规范

1） sql语句的所有表名、字段名全部小写；系统保留字、内置函数名、sql保留字大写。

2） 连接符or、in、and以及=、<=、>=等前后加上一个空格。

3） 对较为复杂的sql语句、过程、函数加上注释，说明算法、功能。

4） 多表连接时，使用表的别名来引用列。

2、数据操纵语言(DML)-用来操纵数据库中数据的命令

2.1、     select语句

语法结构：

SELECT * | {[DISTINCT] column | expression [alias],…}

FROM     table

[WHERE condition(s)]

[ORDER BY {column, expr, alias} [ASC|DESC]];

1）查询全部列

SELECT * FROM departments;

2）查询特定列

SELECT department_id, department_name FROM departments;

3）带有数学运算符(+-*/)的查询

SELECT last_name, salary + 300 FROM employees;

4）使用别名

SELECT last_name AS name FROM employees;

5） 使用连接符”||”合并列

SELECT last_name || “ is a ” || job_id AS emp FROM employees;

6） 使用DISTINCT去掉重复行

SELECT DISTINCT department_id FROM employees;

 

2.2、     insert语句

1） 直接插入一行记录

INSERT INTO Persons VALUES (‘Gates’,’Bill’,’10’,’Beijing’);

2） 指定列插入记录

INSERT INTO Persons (LastName, Address) VALUES (‘Wilson’,’Beijing’);

3） 使用select into select插入数据

INSERT INTO test2 (id,testname) SELECT SEQ_TEST.NEXTVAL,testname FROM test1

2.3、     update语句

1） 常用的标准语法

UPDATE table1 SET state = ‘1’ WHERE year = ‘2012’;

2） 使用update set from更新语句

UPDATE table2 SET (t2Name) = (SELECT t1Name FROM table1 WHERE table2.id = table1.id);

2.4、     delete语句

1） 基本用法

DELETE FROM courses WHERE courseName = ‘Java’;

2.5、     where子句

1）一般条件筛选

SELECT ename, job FROM emp WHERE ename = ‘JAMES’;

2）使用BETWEEN显示某一值范围内的记录

SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500;

3）使用IN获取匹配列表值的记录

SELECT empno, ename, sal, mgr FROM emp WHERE mgr IN (79, 44, 78);

4）使用LIKE进行通配查询

SELECT ename FROM emp WHERE ename LIKE ‘_A%’;

5）查询包含空值的记录

SELECT ename, mgr FROM emp WHERE mgr IS NULL;

6） AND和OR运算符

SELECT empno FROM emp WHERE sal > 100 AND job = ‘CLERK’;

注：运算符优先级

小括号>所有比较运算符>NOT>AND>OR

2.6、     ORDER BY子句

SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC;

2.7、     GROUP BY子句

SELECT departmentId, count(*) FROM emp GROUP BY departmentId;

注：聚合函数有avg()、max()、min()、sum()、count()

2.8、     HAVING子句

SELECT customer, SUM(orderPrice) FROM orders GROUP BY customer HAVING SUM(orderPrice)<2000;

2.9、     LEFT JOIN、RIGHT JOIN、INNER JOIN和FULL JOIN

SELECT * FROM a;

No            Name

1000        张三

2000        李四

3000        王五

SELECT * FROM b;

No            Commodity

1000        电视机

2000        录像机

4000        自行车

SELECT a.*, b.* FROM a LEFT JOIN b ON a.No = b.No;

No            Name     No          Commodity

1000        张三       1000      电视机

2000        李四       2000      录像机

3000        王五       null        null

SELECT a.*, b.* FROM a RIGHT JOIN b ON a.No = b.No;

No            Name     No          Commodity

1000        张三       1000      电视机

2000        李四       2000      录像机

null          null        4000      自行车

SELECT a.*, b.* FROM a INNER JOIN b ON a.No = b.No;

No            Name     No          Commodity

1000        张三       1000      电视机

2000        李四       2000      录像机

SELECT a.*, b.* FROM a FULL JOIN b ON a.No = b.No;

No            Name     No          Commodity

1000        张三       1000      电视机

2000        李四       2000      录像机

3000        王五       null        null

null          null        4000      自行车

2.10、  UNION和UNION ALL

SELECT * FROM student;

Id             Name     Score

1                            Aaron    78

2                            Bill         76

3                            Cindy    89

4                            Damon  90

5                            Ella        73

6                            Frado     61

7                            Gill        99

8                            Hellen   56

9                            Ivan      93

10            Jay        90

SELECT * FROM student WHERE id < 4 UNION SELECT * FROM student WHERE id > 2 AND id < 6;

Id             Name     Score

1                            Aaron    78

2                            Bill         76

3                            Cindy    89

4                            Damon  90

5                            Ella        73

SELECT * FROM student WHERE id < 4 UNION ALL SELECT * FROM student WHERE id > 2 AND id < 6;

Id             Name     Score

1                            Aaron    78

2                            Bill         76

3                            Cindy    89

3                            Cindy    89

4                            Damon  90

5                            Ella        73

2.11、ANY(SOME)：

SELECT * FROM student WHERE age < ANY (20,22,25,27);

2.12、ALL：

SELECT * FROM student WHERE age > ALL(20,22,25,27);

2.13、IN(NOT IN)：

       SELECT * FROM persons WHERE lastName IN (‘Ada’,’Carter’);

2.14、EXISTS(NOT EXISTS)：返回真/假

        表A：

              ID          NAME

1                A1

2                A2

3                A3

表B：

ID          AID       NAME

1            1            B1

2            2            B2

3            2            B3

SELECT ID,NAME FROM A WHERE EXIST(SELECT * FROM B WHERE A.ID = B.AID);

执行结果：

1                A1

2                A2

分析如下：

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据
NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3       A3

2.15、函数的使用

SELECT TO_CHAR(sal, ‘$99.999’) AS SALARY,NVL(job,’NO JOB!’)

FROM emp

WHERE UPPER(ename) = ‘SCOTT’

AND createDate >= TO_DATE(‘2012-10-25’,’yyyy-MM-dd’);

 

select value,lead(value,1) over(order by vtime) value2,

vtime,lead(vtime,1) over(order by vtime) vtime2 from leadtest

3、数据定义语言(DDL)-用来建立数据库、数据库对象和定义列的命令

3.1、create语句

1）创建一个表空间

create tablespace tablespaceName datafile ‘f:\oracle\oradata\aa.dbf’ size 2000M;

2）创建一个用户

create user userName identified by password default tablespace tablespaceName;

3）授权给新用户

grant connect, resource to userName;

4）创建一个用户表

create table table1 (

              no          varchar(15)         not null,

              name     varchar(50),

              age        number(3),

              state      char(1)

）;

5）创建一个索引

create index indexName on table1(no);

6） 创建一个存储过程

CREATE OR REPLACE PROCEDURE procedureName

IS

BEGIN

insert into test2(username,password) (select username,password from test);

END;

调用存储过程：

call procedureName();

如果经常需要特定操作，那么可以建立存储过程，可以简化开发和维护。

安全性高。

7）创建一个自定义函数

CREATEORREPLACEFUNCTIONfunctionName(v_nameinvarchar2)

RETURNVARCHAR2

IS

BEGIN

return(v_name||' hello');

END;

自定义函数调用：

selectfunctionName(username) fromtestt

8）创建一个视图

CREATEORREPLACEVIEWviewNameAS

SELECTt.yh_mc,d.bm_mcFROMyh_xxbt,dm_bmgldwheret.bm_dm=d.bm_dm;

使用视图的好处：

l  有时后会有非常复杂的逻辑，视图可以用来方便理解，可以当作中间层简化逻辑。

l  基于安全考虑，可以针对特定的视图赋给用户权限，而不是给用户赋给一个或几个表的权限，这样可以避免误操作更新数据的不安全因素

9）创建一个触发器

Createorreplacetriggeryh_xxb_delete_log

Beforeinsertorupdateordelete

Onyh_xxb

Begin

Insertintotest_log(Who,when) Values( user, sysdate);

End;

触发器的触发：

deletefromyh_xxbtwheret.yh_dm='001100'

3.2、alter语句

1） 表添加一列

alter table employ add(weight number(38,0));

2）表修改列类型

alter table employ modify(weight number(13,2));

3）表修改列名称

alter table emp rename column weight to weight_new;

4）表删除列

alter table emp drop column weight_new;

5）表修改表名

alter table emp rename to emp_new;

3.3、drop语句

1）删除表空间

drop tablespace tablespaceName;

2） 删除用户

drop user userName;

3） 删除表

drop table tableName;

4、数据控制语言(DCL)-用来控制数据库组件的存取许可、权限等的命令

4.1、grant语句

grant create table to userName;

4.2、revoke语句

revoke create table from userName;

 

补充知识点：

1、 查看当前用户下所有表占用的空间情况：

Select segment_name, tablespace_name, bytes, blocks from user_segments

2、 按用户查看所占用的数据库空间大小：

Select owner, SEGMENT_TYPE, sum(BYTES)/1024/1024 as SIZE_M from dba_segments group by owner, SEGMENT_TYPE;

3、TOP、LIMIT、ROWNUM

    一个表userinfo，一个自增长主键userId，一个字段userName.

    3.1、查询其中前10条记录

       1）SqlServer数据库：

Select top 10 * from userinfo;

2） oracle数据库：

select * from userinfo where rownum <= 10;

注：rownum是oracle数据库先查到结果集之后再加上去的一个伪列。

所以Select * from userinfo where rownum > 10是查不出结果的。

3） Mysql数据库：

Select * from userinfo limit(0,10);

3.2、查询第10—20条记录

1） SqlServer数据库：

Select top 10 * from userinfo where userId in(select top 20 userId from userinfo) order by userId desc;

Select top 10 * from userinfo where userId >

(select max(userId) from (select top 10 userId from userinfo order by userId) a)

2） oracle数据库：

select * from (select rownum as num, u.* from userinfo u where rownum <= 20) where num >= 10;

3）Mysql数据库：

    Select * from userinfo limit(10,10);

 

 

附录1：

Oracle常用方法

abs(m) m的绝对值

mod(m,n) m被n除后的余数

power(m,n) m的n次方

round(m[,n]) m四舍五入至小数点后n位的值（n缺省为0）

trunc(m[,n]) m截断n位小数位的值（n缺省为0）

 

字符函数：

initcap(st) 返回st将每个单词的首字母大写，所有其他字母小写

lower(st) 返回st将每个单词的字母全部小写

upper(st) 返回st将每个单词的字母全部大写

concat(st1,st2) 返回st为st2接st1的末尾（可用操作符"||"）

lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格

rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格

ltrim(st[,set]) 返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格

rtrim(st[,set]) 返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格

replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st替换，返回一个st。缺省时，删除search_st

substr(st,m[,n]) n=返回st串的子串，从m位置开始，取n个字符长。缺省时，一直返回到st末端

length(st) 数值，返回st中的字符数

instr(st1,st2[,m[,n]]) 数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1

 

转换函数：

nvl(m,n) 如果m值为null,返回n,否则返回m

decode(var,1,value1,2,value2,3,value3,last) 如果var等于1返回value1,等于1返回value2,等于3返回value3,否则返回last

to_char(m[,fmt]) m从一个数值转换为指定格式的字符串fmt缺省时，fmt值的宽度正好能容纳所有的有效数字

to_number(st[,fmt]) st从字符型数据转换成按指定格式的数值，缺省时数值格式串的大小正好为整个数

 

分组函数：

avg([distinct/all] n) 列n的平均值

count([all] *) 返回查询范围内的行数包括重复值和空值

count([distinct/all] n) 非空值的行数

max([distinct/all] n) 该列或表达式的最大值

min([distinct/all] n) 该列或表达式的最小值

stdev([distinct/all] n) 该列或表达式的标准偏差，忽略空值

sum([distinct/all] n) 该列或表达式的总和

variance([distinct/all] n) 该列或表达式的方差，忽略空值

 

日期函数：

>

add_months(d,n) 日期d加n个月

last_day(d) 包含d的月份的最后一天的日期

month_between(d,e) 日期d与e之间的月份数，e先于d

new_time(d,a,b) a时区的日期和时间d在b时区的日期和时间

next_day(d,day) 比日期d晚，由day指定的周几的日期

sysdate 当前的系统日期和时间

greatest(d1,d2,...dn) 给出的日期列表中最后的日期

least(d1,k2,...dn) 给出的日期列表中最早的日期

to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串

to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式

round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期

trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期

附：

日期格式：

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

格式代码　说明举例或可取值的范围

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

DD 该月某一天 1－3

DY 　　　三个大写字母表示的周几 SUN，...SAT

DAY 　　　完整的周几，大写英文 SUNDAY，...SATURDAY

MM 月份 1－12

MON　　　　　三个大写字母表示的月份 JAN，...DEC

MONTH 完整 JANUARY,...DECEMBER

RM 月份的罗马数字 I,...XII

YY或YYYY 两位，四位数字年

HH:MI:SS　　　时：分：秒

HH12或HH24　　以12小时或24小时显示

MI　　　　　　分

SS　　　　　　秒

AM或PM　　　　上下午指示符

SP　　　　　　后缀SP要求拼写出任何数值字段

TH　　　　　　后缀TH表示添加的数字是序数　4th,1st

FM 前缀对月或日或年值，禁止填充

 

练习题：

1、 表结构

Emp 员工表

Ename   varchar2(30)       姓名

Empno   number(5)           编号

Deptno  number(5)           部门

Job        varchar2(20)       工种(manager-经理，clerk-办事员)

Hiredate Date                   雇佣日期

Comm.   Number(6,2)       佣金

Sal         number(6,2)        薪金

 

Dept 部门表

Dname  varchar2(30)       部门名称

Deptno  number(5)           部门号

Loc         varchar2(50)       位置

 

1） 找出佣金高于薪金60%的员工信息

2） 找出部门为10的所有经理和部门为20的所有办事员的信息

3） 找出收取佣金的员工的不同工作

4） 找出各月最后一天受雇的所有员工

5） 查询只有首字母大写的所有员工的姓名

6） 查询所有员工姓名的前三个字符

7） 查询员工的姓名、工作和薪金，按工作降序排列，工作相同时按薪金升序排列

8） 查询每年2月份雇佣的员工信息

 

1） select * from emp where comm. > sal*0.6;

2） select * from emp where(deptno=10 and job=’manager’) or (deptno=20 and job=’clerk’)

3） select distinct job from emp where comm. > 0;

4） select * from emp where hiredate = last_day(hiredate);

5） select ename from emp where ename = initcap(ename);

6） select substr(ename,1,3) from emp;

7） select ename,job,sal from emp order by job desc,sal asc;

8） select * from emp where to_char(hiredate,’MM’)=’02’;

 

 

2、 以下三个表的关系：

S (SNO,SNAME)学生表。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER)课程表。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE)选课表。SCGRADE 为成绩

 

1） 找出选修过’liming’老师教授课程的所有学生的姓名。

2） 找出没有选修过’liming’老师教授课程的所有学生的姓名。

3） 找出没有选修课程号为’1’的所有学生。

4） 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩。

5） 找出即学过语文又学过数学的所有学生。

6） 找出选修了所有课程的学生。

7） 找出选修课程超过2门的学生学号。

8） 找出选修课程超过2门的学生学号及姓名。

9） 找出选修人数大于3的各科的最高成绩列表(显示科目号、成绩字段)。

10）找出语文课比数学课成绩高的所有学生的学号及语文、数学成绩。

11）查询选修了课程的学生人数。

1）select * from S,C,SC where sc.sno=s.sno and sc.cno=c.cno and c.cteacher='liming'
select * from s join sc ons.sno = sc.sno join c on sc.cno = c.cno where c.cteacher = 'liming'
Select * FROM S Where Sno IN (Select Sno FROM C, SC Where C.Cno = SC.Cno AND CTEACHER = 'liming')
2）Select S Name FROM S Where Sno NOT IN( Select SC.Sno FROM SC,C WhereSC.CNO=C.CNO AND CTEACHER='liming')
3）Select SName FROM S Where Sno NOT IN( Select Sno FROM SC Where CNO='1')
4）select count(*),S.sname,avg(SCGRADE) from S,SC where S.SNO=SC.SNO and SCGRADE<60 group by sname havingcount(*)>=2
5）Select S.SNO,S.SNAME FROM S,(select sc.sno from sc inner join c on sc.cno=c.cno where c.cname in('yuwen','shuxue') group by sc.sno having count(*)=2)SC Where S.SNO=SC.SNO
6）Select S.SNO,S.SNAME FROM S,(SelectSC.SNOFROMSC,CWhereSC.CNO=C.CNOGROUPBYSNOHAVINGCOUNT(*)=(selectcount(*) fromc) )SCWhereS.SNO=SC.SNO
7）SelectSno  FROMSCGROUPBYSnoHAVINGCOUNT(Cno)>2
8）SelectSNo,SNAMEFROMSWhereSnoIN(SelectSno  FROMSCGROUPBYSnoHAVINGCOUNT(Cno)>2)
9）Selectcno,max(scgrade) asmaxstoreFROMSCWherecnoIN (SelectcnoFROMSCGROUPBYcnoHAVINGCOUNT(sno)>3) groupbycnoorderbycno
10）select * from (selects.sno, sc.SCGRADEfroms, sc, cwheres.sno = sc.snoandsc.cno = c.cnoandc.cname = 'yuwen') t1, (selects.sno, sc.SCGRADEfroms, sc, cwheres.sno = sc.snoandsc.cno = c.cnoandc.cname = 'shuxue') t2wheret1.sno = t2.snoandt1.SCGRADE > t2.SCGRADE
11）Select COUNT(DISTINCT Sno) FROM SC