SELECT * FROM emp;
DESC emp;
SELECT empno,ename,sal,job FROM emp;
SELECT job FROM emp;
SELECT DISTINCT job FROM emp;
SELECT empno AS id,ename AS name,sal * 12 "Annual Salary" FROM emp; SELECT empno AS id,ename AS "Name",sal * 12 "Annual Salary" FROM emp; SELECT ename || '的职位是' ||job FROM emp;
SELECT ename || '的职位是' ||job "Employee" FROM emp;--查询语句中使用连接表达式
SELECT ename,sal,comm,sal + comm FROM emp;
SELECT ename,sal,comm,sal + nvl(comm,0) FROM emp; --NVL函数处理NULL值，comm为奖金项，有NULL值，用0代替
SELECT ename || '-' || comm || '-' || sal AS employee FROM emp;
SELECT ename ,hiredate FROM emp;
SELECT ename,TO_CHAR(hiredate,'YYYY-MM-DD') hiredate FROM emp; SELECT ename,sal,comm FROM emp WHERE sal < 2000;
SELECT ename,sal,job FROM emp WHERE job = 'SALESMAN';
SELECT ename,sal,hiredate FROM emp WHERE hiredate > '01-1月-82';
SELECT ename,sal,hiredate FROM emp WHERE hiredate > to_date('1982-1-1','YYYY-MM-DD');--日期类型数据的比较，
SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;
SELECT ename,job FROM emp WHERE job IN('MANAGER','CLERK');
SELECT ename,job FROM emp WHERE ename LIKE '_A%';--%表示0到多个字符，_标识单个字符
INSERT INTO emp (empno,ename) VALUES(5566,'JHON_SMITH');
SELECT ename FROM emp WHERE empno = 5566;
SELECT ename FROM emp WHERE ename LIKE '%\_%' ESCAPE '\';
SELECT ename FROM emp WHERE ename LIKE '%U_%' ESCAPE 'U'; SELECT ename,sal,comm FROM emp WHERE comm IS NULL;
SELECT ename,sal,comm FROM emp WHERE comm = NULL;--无返回结果
SELECT ename,sal,job FROM emp WHERE sal > 1000 AND job = 'CLERK'; SELECT ename,sal,job FROM emp WHERE sal > 1000 OR job = 'CLERK'; SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL;
SELECT ename,sal,job FROM emp WHERE job = 'SALESMAN' OR job = 'ANALYST' AND sal > 2000;--运算符的优先级规则：比较操作符>NOT>AND>OR
SELECT ename,sal FROM emp ORDER BY sal;--默认为升序
SELECT ename,comm FROM emp ORDER BY comm;--在排序时，NULL值是最大的！
SELECT ename,sal FROM emp ORDER BY sal DESC;--降序排列
SELECT empno,ename,mgr FROM emp WHERE deptno = 10 ORDER BY mgr DESC;--有NULL值存在是，此列排在最前面
SELECT ename FROM emp ORDER BY sal DESC;
SELECT ename,sal,sal * 12 FROM emp ORDER BY sal * 12 DESC;
SELECT ename,sal,sal * 12 annual_salary FROM emp ORDER BY annual_salary DESC;
SELECT deptno,dname FROM dept UNION SELECT empno,ename FROM emp ORDER BY 1;
SELECT ename,deptno,sal FROM emp ORDER BY deptno ASC,sal DESC; 分组查询：
SELECT COUNT(*) total_num FROM emp;
COUNT函数用来计算表中的总记录条数。其中total_num为查询出的值的列名。 SELECT AVG(sal) avg_sal ,SUM(sal) sum_sal FROM emp; SELECT MAX(sal) max_sal,MIN(sal) min_sal FROM emp;
SELECT deptno,AVG(sal) avg_sal,SUM(sal) sum_sal FROM emp GROUP BY deptno;
SELECT deptno,job,AVG(sal) avg_sal,MAX(sal) max_sal FROM emp GROUP BY deptno,job;
SELECT deptno,job,AVG(sal) avg_sal FROM emp GROUP BY ROLLUP(deptno,job);
SELECT deptno,job,AVG(sal) avg_sal FROM emp GROUP BY CUBE(deptno,job); SELECT deptno,MAX(sal) max_sal FROM emp GROUP BY deptno HAVING MAX(sal) >4000;
SELECT MAX(sal) max_sal FROM emp GROUP BY deptno;
SELECT deptno,ename,comm FROM emp ORDER BY deptno;
连接查询
SELECT COUNT(*) FROM emp;
SELECT COUNT(*) FROM dept;
SELECT emp.ename,dept.dname FROM emp,dept;
SELECT emp.ename,dept.dname FROM emp,dept WHERE emp.deptno = dept.deptno;
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno;
SELECT e.ename,e.job,d.dname,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno;
SELECT e.ename,e.job,d.dname,d.loc ,e.deptno FROM emp e,dept d WHERE e.deptno = d.deptno AND e.deptno = 10;
SELECT e.last_name ename,d.department_name dname,l.city
FROM employee e, departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.department_id <50;
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal ;
SELECT worker.empno w_empno,worker.ename w_ename,manager.empno m_empno,manager.ename m_ename
FROM emp worker,emp manager
WHERE worker.mgr = manager.empno;--自连接
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno;--内连接
SELECT e.ename,d.dname FROM emp e NATURAL JOIN dept d;
SELECT e.ename,d.dname FROM emp e JOIN dept d USING(deptno);
SELECT e.ename,d.dname FROM emp e JOIN dept d ON(e.deptno = d.deptno);--内连接
INSERT INTO emp(empno,ename) VALUES(2234,'MATHA');
SELECT ename FROM emp WHERE empno = 2234;
SELECT e.ename,d.dname FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;--左外连接
SELECT e.ename,d.dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;--外连接
SELECT e.ename,d.dname FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;--全外连接
SELECT e.ename,e.job FROM emp e WHERE e.job = (
SELECT job FROM emp WHERE ename = 'SCOTT');--单行子查询
SELECT deptno,ename,sal
FROM emp e
WHERE sal > (SELECT AVG(SAL) FROM emp
WHERE deptno = e.deptno)
ORDER BY deptno;--单行子查询，子查询中有分组函数
SELECT deptno,MIN(SAL) min_sal
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal) FROM emp WHERE deptno = 30);
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE deptno IN (
SELECT deptno FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN';--多行子查询，使用IN操作符
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE sal > ANY(
SELECT sal FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALEMAN';
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE sal > ALL(
SELECT sal FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN';
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE (deptno,job) = (
SELECT deptno,job FROM emp WHERE ename ='SCOTT');--多列子查询 SELECT e.deptno,e.ename,e.sal
FROM emp e,
(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) x WHERE e.deptno = x.deptno
AND e.sal > x.avg_sal
ORDER BY e.deptno;--行内视图
第四章、SQL单行函数
SELECT UPPER('hello world')FROM DUAL;
SELECT ename,job FROM emp WHERE LOWER(ename) = LOWER('scott'); SELECT LOWER('HELLO WORLD')FROM DUAL;
SELECT ename,job FROM emp WHERE UPPER(ename) = UPPER('scott'); SELECT INITCAP('HELLO WORLD')FROM DUAL;-每个单词首字符大写 SELECT CONCAT('HELLO','WORLD')FROM DUAL;
SELECT CONCAT(CONCAT(ename,':'),sal)emp_info FROM emp WHERE deptno = 10;
SELECT SUBSTR('Doctor Who travels in TARDIS',8,25)FROM DUAL;-字符函数SUBSTR(char,m，n)用于获取字符串的子串。返回从char中m位开始取的n个字符。若m为负数，从尾部开始取。
SELECT SUBSTR('Doctor Who travels in TARDIS',-9,5) FROM DUAL; SELECT LENGTH('Doctor Who travels in TARDIS') len FROM DUAL;
SELECT ename,LENGTH(ename)FROM emp WHERE deptno = 10;
SELECT INSTR('Doctor Who','Who')words FROM DUAL;-获取子串在字符串中的位置
SELECT LPAD('travels in TARIDS',28,'Doctor Who')words FROM DUAL;-结果：Doctor WhoDtravels in TARIDS。LPAD左补位函数，用于在字符串的左端或右端用后面的参数补足到n位。
SELECT RPAD('travels in TARIDS',17,'TARDIS')words FROM DUAL;
SELECT ename,LPAD(sal,6,'$')sal FROM emp WHERE deptno = 10;
SELECT TRIM('半'FROM'半月二更半')AS 上联 FROM DUAL;-结果：月二更。从头部和尾部截去
SELECT TRIM(leading'半'FROM'半月二更半')AS 上联 FROM DUAL;-从头部截去
SELECT TRIM(trailing'半'FROM'半月二更半')AS 上联 FROM DUAL;-从尾部截去
SELECT LTRIM('中秋八月中','中')AS 上联 FROM DUAL;-从头部截去
SELECT '***' || TRIM('中秋八月中')||'***'诗 FROM DUAL;