1、函数查询
(1)字符串函数
ascii() 返回ascii值
select ascii('a') from dual;
进制转化:将2进制转化为10进制
select conv(10,2,10) from dual;
将字符串进行连接
select concat('hello','world') from dual;
返回字符串的长度
select length('abc') from dual;
返回后面的字符串在前面字符串的位置
select instr('hello world','hello') from dual;
字符串的替换
select replace('hello world','hello','world');
返回最左边的几个字符
select left(ename,1) from emp;
返回最右边的几个字符
select right(ename,2) from emp;
返回字符串的字串
select substring('hello world',1,5) from dual;
字符串的替换
select replace('hello','l','*');
字符串的转大写
select upper('hello') from dual;
字符串的转小写
select lower('hello') from dual;
(2)数学函数
返回绝对值
select abs(-1) from dual;
返回10被3除后的余数
select mod(10,3) from dual;
返回不大于x的最小整数
select floor(-12.3) from dual;
返回不小于x的最小整数
select ceiling(-12.3) from dual;
四舍五入(第二个参数为精确到的位数)
select round(12.3) from dual;
select round(12.333,2) from dual;
返回0-1之间的随机浮点数
select rand() from dual;
(3)日期函数
返回系统的当前时间
select now() from dual;
select date_add(now(),interval 1 year) from dual;
select date_add(now(),interval 1 month) from dual;
select date_add(now(),interval 1 day) from dual;
格式化时间
select date_format(now(),'%Y年%m月%d日 %h:%i:%s');
select date_format(now(),'%y年%m月%d日 %h:%i:%s');
(4)聚合函数
select sum(sal) from emp;
select avg(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select count(*) from emp;
2、复杂查询
(1)模糊查询(like)
select sname AS 姓名 from students where sname like '张%';
(2)范围查询(between)
select id,name,age from student where age between 18 and 22;
(3)列表查询(in)
select name as 姓名,address as 地址 from student where address in ('北京','广州','上海');
(4)分组查询(group by)
select id, avg(score) from score group by CourseID;
分组前限制条件用where,分组后限制条件用having
(5)联合查询
多表联合查询分为:内连接(inner join)和外连接,外连接又分为左外链接(left outer join)和右外连接(right outer join)
内连接
select a.no,a.name,b.name from student a
inner join class b
on a.cid = b.id;
外连接
左|右外连接(left | right outer join),结果集中除了包括满足连接条件的行外,还包括左表或右表的所有行
查询全部班级及每个班级的学员信息
select a.name,b.name from student a right outer join class b on a.cid=b.id;
select a.name,b.name from student a left outer join class b on a.cid=b.id;
3、视图
视图可以显示来自一个或多个表的数据,可以视为“虚拟表”或“存储的查询”
视图的优点有:
隐藏的数据的复杂性
简化的用户的SQL命令
创建视图的语法:
create view view_name AS select_statement
删除视图的语法:
drop view view_name;