目录
1、sql书写规范………………..…………………………………………………………………….3
2、数据操纵语言(DML)………………………………………………………………………..…..3
3、数据定义语言(DDL)……………………………………………………………………………..7
4、数据控制语言(DCL)……………………………………………………………………………..9
5、补充知识点和练习……………………………………………………………………………….10
6、附录……………………………………………………………………………………………….11
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 * fromS,C,SCwheresc.sno=s.snoandsc.cno=c.cnoandc.cteacher='liming'
select * fromsjoinscons.sno = sc.snojoinconsc.cno = c.cnowherec.cteacher = 'liming'
Select * FROMSWhereSnoIN (SelectSnoFROMC, SCWhereC.Cno = SC.CnoANDCTEACHER = 'liming')
2） SelectSNameFROMSWhereSnoNOTIN( SelectSC.SnoFROMSC,CWhereSC.CNO=C.CNOANDCTEACHER='liming')
3） SelectSNameFROMSWhereSnoNOTIN( SelectSnoFROMSCWhereCNO='1')
4） selectcount(*),S.sname,avg(SCGRADE) fromS,SCwhereS.SNO=SC.SNOandSCGRADE<60groupbysnamehavingcount(*)>=2
5） SelectS.SNO,S.SNAMEFROMS,(selectsc.snofromscinnerjoinconsc.cno=c.cnowherec.cnamein('yuwen','shuxue') groupbysc.snohavingcount(*)=2)SCWhereS.SNO=SC.SNO
6） SelectS.SNO,S.SNAMEFROMS,(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） SelectCOUNT(DISTINCTSno) FROMSC
扫一扫
2374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
