数据库-函数
一、函数
1. distinct去除重复列
SELECT DISTINCT age FROM student;
2. 使用算数运算符+-*/
SELECT age,age+2 FROM person;
3. 字符串的拼接 ||
SELECT 'hello' || 'oracle' FROM dual;
SELECT NAME || '的年龄是'|| age FROM person;
4. in等于值列表
SELECT * FROM person WHERE age IN(19,20,31);
SELECT * FROM person WHERE (NAME,age) IN(('jack',19),('tom',24));
5. is null 空值
SELECT * FROM person WHERE age IS NULL;
6. 模糊查询
#开头
SELECT * FROM person WHERE NAME LIKE 'j%';
#中间
SELECT * FROM person WHERE NAME LIKE '%j%;
#查询姓名的第三个字符
SELECT * FROM person WHERE NAME LIKE '__l%'
练习
--查询所有姓张的同学
SELECT * FROM student WHERE NAME LIKE '张%';
--查询所有名字带刚的24岁以下的同学
SELECT * FROM student WHERE age <=24 AND NAME LIKE '%刚%';
--查询成绩在60到65之间的记录（两种写法）
SELECT * FROM score WHERE score >=60 AND score <=65;
SELECT * FROM score WHERE score BETWEEN 60 AND 65;
--查询成绩超过60、不到65的记录（不
7. order by DESE ASC
-- desc 降序
SELECT age FROM student ORDER BY age DESC;
-- asc 升序
SELECT age FROM student ORDER BY age ASC;
-- desc asc
SELECT * FROM student ORDER BY age DESC ,id ASC
8. 截取字符串SUBSTR
-- 从第2个字符开始，截取5个字符
SELECT SUBSTR('helloworld',2,5) FROM dual;
SELECT NAME,SUBSTR(NAME,2,3) FROM person;
9. 获取长度length
SELECT LENGTH('helloworld') FROM dual;
10. 数值函数round
-- round 四舍五入
SELECT ROUND(3.131492,3) FROM dual;
11. 转换函数to_date/to_char
-- 字符串->日期 to_date
SELECT * FROM person WHERE birth > '24-7月 -19';
SELECT * FROM person WHERE birth > to_date('2019-07-24','yyyy-mm-dd');
SELECT * FROM person WHERE birth > to_date('2019-07-24 00:00:00','yyyy-mm-dd hh24:mi:ss');
--查询出生日期在2015年12月20号到2018年1月10号8点10的人
SELECT * FROM person WHERE birth >= to_date('2015-12-20','yyyy-mm-dd') AND birth <= to_date('2018-01-10 08:10:00','yyyy-mm-dd hh24:mi:ss');
-- 日期->字符串 to_char
SELECT birth,to_char(birth,'yyyy-mm-dd hh:mi:ss') FROM person;
SELECT * FROM person WHERE to_char(birth,'yyyy-mm') = '2019-09'
12. 通用函数，适用于任何数据类型，包括空值nvl(e1,e2)
--nvl(e1,e2)当e1位null时，用e2代替，需要e1和e2数据类型一致
SELECT age,nvl(age,0) FROM person;
13. 分组函数avg/sum/max/min/count
-- avg平均值、sum总计
SELECT AVG(age),SUM(age) FROM person;
-- max最大值、min最小值
SELECT MAX(age),MIN（age) FROM person;
-- count计数
SELECT COUNT(*) FROM person;
-- 查询score表中有多少个学生考试
SELECT COUNT(DISTINCT sno) FROM scor
14. 每、各、不同，需要使用group by
--统计每个学生的选课门数，并按选课门数的递增顺序显示结果
SELECT sno,COUNT(*)
FROM score
GROUP BY sno
ORDER BY COUNT(*);
--查询每个同学的总分、考试科目数量
SELECT sno,SUM(score),COUNT(*)
FROM score
GROUP BY sno;
--查询每门课总分
SELECT lno,SUM(score)
FROM student
GROUP BY lno;
--查询每门课的选课人数
SELECT lno,COUNT(*)
FROM score
GROUP BY ln
15. 内连接，等值连接
多表查询，假设要显示两个列，其中一个列来自一张表，另一个列来自令一张表。
需要将这两张表都显示出来的话，需要将这两张表进行关联之后再显示出来。
相对而言，隐式连接好理解好书写，语法简单，担心的点较少。
但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显
-- 隐式内连接
SELECT * FROM stu,tea WHERE stu.tid=tea.tid;
-- 显式内连接
SELECT * FROM stu JOIN tea ON stu.tid=tea.tid;
16. 左右内连接
-- left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
-- 左连接：left outer join，以左表为主，无论能否关联到右表记录都要被查询到，右表关联到就显示关联记录，关联不到就显示空。
左连接就是以from左边为主，拿上面两个例子来说，
第一种是以学生（students）为主，假如学生成绩为空，也能查询到学生信息，只不过成绩为空。
而第二种则是以成绩为主（sc）所以先查学生成绩，如果成绩为空就算学生存在，也查不到记录。
-- right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
-- 右连接：right outer join，以右表为主，无论能否关联到左表记录都要被查询到，左表关联到就显示关联记录，关联不到就显示空。
-- inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
-- full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。
-- 全连接：full outer join，任一边表有记录都要被查询到，无论另一边能否关联到。另一边关联到就显示关联记录，关联不到就显示为空。
17. 子查询
子查询，假如要显示一个表中的重要数据。
需要跟另一个表中的数据进行对比之后才能显示出来，但是不需要显示对比的表的内容
-- 在where子句中使用,=、!=、>、<、<=、>=
---- select mgr from emp where ename='SMITH';=7902
---- select ename from emp where empno=7902;
SELECT ename FROM emp WHERE empto=(SELECT mgr FROM emp WHERE ename = 'SMITH');
-- 在from子句中
SELECT * FROM (SELECT * FROM person ORDER BY age) WHERE rownum<=5;
-- 在having子句中
--- 求平均年龄大于江苏省平均年龄的省份及平均年龄
SELECT address,AVG(age) FROM student GROUP BY address HAVING AVG(age)>(SELECT AVG(age) FROM student WHERE address='江苏省');
二、查询语句语法
1. 在where中不能使用多行函数
--报错
select sex from person where count(*)>0;
2. group by总结
--总结：语句中如果有group by，那么在select语句中
--不可以放：
--不是group by后面的列，
--以及对不是group by后面的列使用单行函数情况
-- 一般放：group by后面的列直接展示、多行函数(背诵二)
select sex,max(age),min(age),sum(age),avg(age),count(age)
from person
group by sex;
3. select后面
--select后不可以放的列，就不能出现在order by后面
4. 书写顺序
--其中select和from是必须的，其他关键词是可选的
select--from--where--group by--having--order by
5. 执行顺序（与书写顺序不一样）
from--where--group by--having--select--order by,
6. having使用前提
语句中使用group by
7. 语句中如果有group by
那么在having中，与select后面可以放的内容相同
8. 两个过滤
where（不能使用多行函数，其它的都可以）
having(多行函数必须使用having)
浙公网安备 33010602011771号