SQL语句01
SQL(Structured Query Language)：结构化查询语言
SQL分类：
数据操纵语言DML（Data Manipulation Language）
SELECT INSERT UPDATE DELETE
数据定义语言DDL（Data definition language）
CREATE ALTER DROP RENAME TRUNCATE
数据控制语言DCL （Data Control Language）
GRANT REVOKE
select关键字
--select[列1，列2,...列N]from 表
--oracle中，列名和表名默认不区分大小写，数据是区分大小写的
作用：检索“列”
注意：1.select后面的列可以起别名（查询的显示结果）
1) 列名后面一个空格后添加别名（别名中不许有“空格”）
例：select ename,sal*12 "年薪" from emp;
2) 列名后面一个空格后使用双引号添加别名
例：select ename,sal*12 "ysal" from emp;
3) 列名后面一个空格后使用as关键字，在as后面添加别名
例：select ename,sal*12 as "ysal" from emp;
2.distinct用于对显示结果的去重
1) distinct必须放在select后面
2) 如果查询有多列，必须满足多列值都相同时，方可去重。
--例子：查询公司有哪些职位
select job from emp;
select distinct job from emp;
--错误的SQL:select ename,distinct job from emp;
from关键字
作用：检索“表”
注意：检索的表后可以添加别名（别名不需要被双引号引起）
--给表取别名
select ename,sal,e.deptno from emp e,dept d;
where关键字
作用：过滤“行”记录（record）
--例：查询大于2000的员工信息
select * from emp where sal <= 5000;
用法：
1.＝，！＝,<>，<,>,<=,>=,any,some,all
例子：查询员工信息：条件：薪水大于1500，薪水还要大于2000
select * from emp where sal != any(1000,1500,2000);
some和any用法一样，all表示所有
2. is null,is not null
例子：
select * from emp where comm is not null;
select * from emp where comm is null;
3.between x and y
例子：查询员工薪水在2000-3000的员工信息
select * from emp where sal between 2000 and 3000;
4.and（都满足） 、 or（满足其一） 、 not
例子：
select * from emp where sal >= 2000 and sal <=3000;
select * from emp where sal >= 1000 or sal >=2000;
5.in（list），not in（list）
例子：
--查询职务为MANAGER和ANALYST的员工信息
select * from emp where job in('MANAGER','ANALYST');
--查询工资不为3000和5000的员工信息
select * from emp where sal not in(3000,5000);
6.exists（子查询）、not exists（子查询）
例子：
select * from emp where exists(select* from dept where deptno !=50);
select * from emp where not exists(select * from dept where deptno = 20);
7.like模糊查询
“%”：匹配零个或若干个字符
“_”：匹配一个字符
在模糊查询中，如果查询的数据中有“%”，“_”时，可以使用escape自定义转义字符
例子：
--查询：员工姓名中含有"M"的员工信息
select * from emp where ename like '%A%';
--查询:员工姓名中第二个字母是"M"的员工信息
select * from emp where ename like '_M%';
--查询：员工姓名中第三个字母是"O"的员工信息
select * from emp where ename like '__O%';
--查询：员工姓名中倒数第二个字母为"E"的员工信息
select * from emp where ename like '%E_';
--查询：员工姓名中含有“%”的员工信息
insert into emp(empno,ename) values(9527,'huan%an');
select * from emp where ename like '%\%%' escape '\';
order by关键字
作用：用于对查询结果进行排序
select * from emp where deptno = 20 order by sal;
用法：
1.利用asc 、desc对排序列进行升序或降序
2.order by后可以添加多个列（逗号分隔），当一个列的值相同时，在按第二列进行排序，依次类推
--1.如何决定升序还是降序？
select * from emp where deptno = 20 order by sal asc;--升序
select * from emp where deptno = 20 order by sal desc;--降序
--2.如果排序的列值相同时，如何处理？
select * from emp where deptno = 20 order by sal desc,ename asc;
--先按薪水降序，再按名字字典顺序升序排列
--计算字段 （列）：不在于表中，通过+、-、*、/操作和列进行计算得到的列
--获取员工年薪
select (ename || '年薪为：' || sal * 12) info from emp;
--集合：每次查询结果可以看作一个集合
select * from emp where deptno = 20;
select * from emp where sal > 2000;
集合操作：
1.union 并集
例：
select * from emp where deptno = 20
union
select * from emp where sal > 2000;
2.union all 全集
select * from emp where deptno = 20
union all
select * from emp where sal>2000
--union 和 union all 的区别在于：
union all 会重复显示两个集合相同的部分
3.intersect 交集
select * from emp where deptno = 20
intersect
select * from emp where sal>2000;
4.minus 差集
--返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
--注意两条sql语句的顺序
注意：
1.保证两个sql查询的列数是个数一致的
2.保证两个sql查询的列的数据类型是一致的
3.保证两个sql查询的列是相同的，否则查询的结果是无意义的
函数
单行函数：对单个数值进行操作，并返回一个值。
--dual是一个虚表，为了满足sql句式而设置这么一个表
要求：我讲完函数，你练习。
分类：
1.字符函数
1)concat(a,b) 拼接a,b两个字符串数据
例：select concat(concat(ename,'的职位是')，job) from emp;
2)initcap(x) 将每个单词x首字母大写
例：select initcap('reece zang')from dual;
3)lower() / upper() 将字符串小写/将字符串大写
例：
select lower('REECE')from dual;
select upper('reece') from dual;
4)length() 获取字符串的长度
例：select ename,length(ename) from emp;
5)lpad(a,b,c) /rpad() 将a字符串左边填充至b长度，用c字符填充
例：
select lpad(ename,10) from emp;
select rpad(ename,10,'*')from emp;
--注意：第二个参数要设定合理的值，否则会导致数据显示不完整
select lpad(ename,5) from emp;--只能显示从左数五个字符
6)ltrim(a,b) / rtrim() 去除a字符串左边的b字符，如果b不传参，默认去除空格
例：
select ltrim('a abcd','a') from dual;
select rtrim(' abcdef aaaa','a f')from dual;
7)replace(a,b,c) 将a中的b字符串替换为c
例：select replace('he love you','he','I')test from dual;
8)substr(a,b,c) 将a的字符串，从b位置开始截取，截c个长度
例：select substr('123444567',3,4) from dual;
9)trim( a from b) 将b左右两边的a字符去除掉
例：select trim('a'from'a ba a') from dual;
2.数字函数
abs() 求取绝对值
例： select abs(-5)from dual;
ceil() 向上取整
例：select ceil(3.1) from dual;
floor() 向下取整
例：select floor(2.7)from dual;
round() 四舍五入
例：select round(4.6)from dual;
power(x,y) x的y次幂
例：select power(2,10)from dual;
3.日期函数
sysdate 返回系统当前日期，注意没有括号
select sysdate from dual;
add_months(d1,d2) 在d1日期上，增加d2个月份
select hiredate,add_months(hiredate,12) from emp;
select add_months(sysdate,6)from dual;
months_between(d1,d2) 返回d1和d2之间的相隔月份
如果d1>d2，则返回正数，如果d1<d2，则返回负数
select months_between(sysdate,hiredate)from emp;
last_day(d) 返回d日期所在月份最后一天的日期
select hiredate,last_day(hiredate) from emp;
next_day(d,X) 返回下一个星期X的日期
select sysdate,next_day(hiredate,'星期五')from emp;
4.转换函数
to_char() 将数字、或日期转化为字符串
select to_char(sal,'$9,999.00')from emp;
to_date() 将字符串转化为日期
select to_date('2019-04-16 23:16:16','yyyy-mm-dd hh24:mi:ss')from dual;
to_number() 将字符串转化为数字
select to_number('876')from dual;
5.其他函数
nvl(x,y) 如果x为null，则显示为y，x不为null,则返回x,x和y的类型保持一致
select ename,nvl(comm,0)from emp;
sys_guid() 生成一个的32位随机字符串
select sys_guid() from dual;
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 条件取值，类同java的switch
select ename,sal,decode(sal,800,'屌丝',2000,'白领',3000,'小资',5000,'高富帅'，'一般人')from emp;
case when then else end 条件取值，类同java的if-else if-else
select ename,sal,case when sal<1000 then '屌丝'
when sal<2000 then '白领'
when sal<3000 then '小资'
when sal<4000 then '高富帅'
else '王宝强' end from emp;
【注意点】
1、以CASE开头，以END结尾
2、分支中WHEN 后跟条件，THEN为显示结果
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名
5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略，得注意条件先后顺序
组函数
组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果
avg()求平均值，只能对数字类型进行处理，不处理空字段
例：--求20部门的平均薪水为多少？
select avg(sal) avgsal from emp where deptno = 20;
sum()求和，只能对数字类型进行处理
例：--求20部门的员工的总薪水
select sum(sal) sumsal from emp where deptno = 20;
count()计数，对任何类型生效，不处理空字段
例：--求20部门的员工有几个
select count(1) from emp where deptno = 20;
max() 求最大值，对任何类型生效
例：--求20部门员工工资最高的是多少
select max(sal) from emp where deptno = 20;
select max(hiredate) from emp;
min() 求最小值，对任何类型生效
例：--求20部门员工工资最低的是多少
select min(sal) from emp where deptno = 20;
group by关键字
作用：对查询结果进行分组处理
select deptno,job from emp group by deptno,job;
select deptno,avg(sal) from emp group by deptno
select deptno,count(1) from emp group by deptno;
用法：
1.分组之后，不能将除分组字段之外的字段放在select后面
--select deptno from emp group by deptno;
--select deptno,count(1) from emp group by deptno;
2.group by 后面可以跟多个字段，则这多个字段值都相同时，才分为一组
--select deptno,job from emp group by deptno,job
3.分组之后，可以使用组函数对每个组进行数据处理
--select deptno,avg(sal) from emp group by deptno
--select deptno,count(1) from emp group by deptno;
having 关键字
作用：用于对分组数据进行过滤
用法：
类似于where的用法
--例：求平均薪水在2000以上的部门编号
select deptno from emp group by deptno having avg(sal)>2000;
--where 和 having关键字不冲突！
select avg(sal),deptno from emp where sal>1500
group by deptno having avg(sal)>2500
order by deptno desc;
-- select from where group by having order by [asc/desc]
--where 后不能使用分组函数
--select e.deptno from emp e where avg(sal) > 1500 group by deptno;
sql顺序分为两类：
1.sql的书写顺序
select from where group by having order by [asc/desc]
2.sql的执行顺序
from where group by having select order by [asc/desc]
