首页 > 代码库 > SQL文练习

SQL文练习

查询中用到的关键词主要包含六个,并且他们的顺序依次为 
select -- from -- where -- group by -- having -- order by 
 
 
如:当职员工资小于或等于 2000美元时,就返回信息“过低”,大于或等于 4000美元时,就返回消息“过高”,如果在这两者之间,就返回“ok”。
select name,sal
case 
 when sal <= 2000 THEN 过低
 when sal >= 4000 THEN 过高
 ELSE  OK
END AS stats FROM emp WHERE deptno = 10;
 
工资分档次统计人数:
SELECT 档次,count(*) AS 人数 FROM(SELECT (CASE WHEN sal<=1000 THEN 0000-1000 WHEN sal<=2000 THEN 1000-2000 WHEN sal<=3000 THEN 2000-3000 ELSE 好高 END) AS 档次,ename,sal FROM emp) GROUP BY 档次 ORDER BY 1 ;
进行抽查的时候要求只返回两条数据中的第二行
SELECT * FROM(SELECT rownum AS sn,emp.* FROM emp WHERE rownum <=2) WHERE sn=2; //rownum是一次对数据进行标识,必须先有第一名再有第二名
进行抽查的时候要求只返回两条数据
SELECT * FROM emp WHERE rownum<=2;
从表中随机返回N条记录(用dbms_random 来对数据进行随机排序,然而取其中的三行)
SELECT empno,aname FROM(SELECT empno,ename FROM emp ORDER BY dbms_random.value()) WHERE rownum <= 3;
常见的模糊查询(查出vname中包含字符串“CED”的)
SELECT * FROM v WHERE vname LIKE %CED%; 查出vname中包含字符串“_BCE”的 错误:SELECT * FROM v WHERE vname LIKE _BCE%; 正确:SELECT * FROM v WHERE vname LIKE \_BCE% ESCAPE \; 因为在LIKE语句中%和_都是通配符,我的理解就是个数不同而已 查出vname包含字符串“_/BCE” 错误:SELECT * FROM v WHERE vname LIKE _\BCE% ESCAPE \ ; 正确:SELECT * FROM v WHERE vname LIKE _\\BCE% ESCAPE \ ; 结果:_\BCDF 解释:双写转义字符,百分号和下划线的处理方式一样 按照第三列顺序排序: SELECT EMPNO,ENAME,HIREDATE FROM emp WHERE deptno=10 ORDER BY 3 ASC; 按照部门编号升序,并按工资降序排列: SELECT empno,deptno,sal,ename,job FROM emp ORDER BY 2 ASC, 3 DESC; 替换字母字符串 SELECT TRANSLATE(ab 你好 bcadefg , abcdefg , 1234567) AS NEW_STR FROM DUAL; 按照字母和数字的混合列中的字母排序 data:6767ADAMS → ename:ADAMS SELECT data,translate(data,-0123456789,-) AS ename FROM v ORDER BY 2 ; 思路:先将列中字母,也就是取出排序的依据另成一列ename,然后在排序,把数字和字母都替换为空; 查询每个月倒数第二天入职的员工信息
select 信息 feom 表 where 时间列= last_day(时间列)-1 查询 last_name 等于chen的manager信息 ---- lower表示把字符都编程小写 select 信息 fromwhere lower(last_name)=chen
通过一条sql查询自连接 select m.* from employess e,employess m where e.manager_id = m.manasger_id and e.last_name = chen
通过sql查询子查询 select * from employess where employess_id = ( select manager_id from employess where last_name = "chen")
查询平均工资高于8000的部门id和平均工资(使用
having 原因就是 where 关键字无法和合计函数一块使用) select employess_id , avg(salary) from employess group by employess_id HAVING avg(SALARY)>8000

查询平均工资最低的员工信息 select * from employess where employess_id = ( select employess_id from employess group by employess_id having salary = min(avg(salary)) )

 

 

SQL文练习