目录
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=1)
--->SELECT * FROM B WHERE B.AID=1有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=2有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=3无值返回真所以没有数据
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