- 第一天 SQL查询语句
CMD——SQLPLUS----账号 soctt 密码 tiger,输入完成后进入SQL数据库,如图显示即连接成功。
连接成功后 ,我们首先设置一下基本界面,
--设置 每列显示的字符数
set LINES 1000;
set pages 100;
--清屏
host cls
---------------------SQL语句中包含什么信息
DQL
数据库查询语言
SELECT
DML
数据库操作语言
INSERT DELETE UPDATE
DDL
数据库定义语言
CREATE ALTER
DCL
数据库控制语言
GRANT
今天我们主要学习的就是DQL,数据库查询语言。
默认情况下,Orale数据库中有4张表我们需要关注分别是
--查询表
SELECT TABLE_NAME FROM USER_TABLES;
DEPT
EMP
SALGRADE
BONUS
--查询表对应的列
输入DESC DEPT;
DEPTNO
DNAME
LOC
输入DESC EMP;
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
输入DESC SALGRADE;
GRADE
LOSAL
HISAL
/*
DQL语言之列的查询
*/
SELECT 列名 FROM 表名 WHERE 条件
--简单的查询列
SELECT ENAME FROM EMP;(查找EMP表中的ENAME列)
SELECT ENAME,SAL,JOB FROM EMP;(查找EMP表中 ENAME,SAL,JOB三列)
SELECT * FROM EMP;(查找EMP表中所有列)
*通配符:优点简便,缺点效率低
--设置别名的三种方式
1、SELECT ENAME "员工姓名",SAL "薪资" FROM EMP;
2、SELECT ENAME AS "员工姓名",SAL AS "薪资" FROM EMP;
3、SELECT ENAME 员工姓名,SAL "薪资" FROM EMP;
推荐第三种方式,具体效果见下图。
--去掉重复的列
SELECT DISTINCT JOB FROM EMP;
/*
DQL语言之条件查询
*/
有时候我们需要根据一定的条件对数据库中的数据进行查询,下面就简单的介绍一下DQL中的条件查询。
在SQL中,查询语句多用WHERE关键字
--等值查询
SELECT ENAME,SAL FROM EMP WHERE SAL = 3000;
SELECT ENAME,SAL FROM EMP WHERE ENAME = 'SMITH';
SELECT ENAME,SAL FROM EMP WHERE JOB = 'SALESMAN'
--非等值查询
SELECT ENAME,SAL FROM EMP WHERE SAL > 3000;
SELECT ENAME,SAL FROM EMP WHERE SAL < 3000;
SELECT ENAME,SAL FROM EMP WHERE SAL <= 3000;
SELECT ENAME,SAL FROM EMP WHERE SAL >= 3000;
SELECT ENAME,SAL FROM EMP WHERE SAL != 3000;
SELECT ENAME,SAL FROM EMP WHERE SAL <> 3000;
SELECT ENAME,SAL FROM EMP WHERE JOB <> 'SALESMAN';
<>符的意思也为不等号,其他符号意思同JAVA。
--区间
SELECT ENAME,SAL FROM EMP WHERE SAL BETWEEN 800 AND 1600;
--空值判断 null
SELECT ENAME,COMM FROM EMP WHERE COMM IS NULL;
SELECT ENAME,COMM FROM EMP WHERE COMM IS NOT NULL;
--从集合中选取所需要的
SELECT ENAME,SAL FROM EMP WHERE ENAME IN('SMITH','SCOTT','ABCD');
SELECT ENAME,SAL FROM EMP WHERE ENAME NOT IN('SMITH','SCOTT','ABCD');
SELECT ENAME FROM EMP WHERE JOB IN('SALESMAN','CLERK');
--模糊查询
--%代表了任意字符的任意次数
SELECT ENAME FROM EMP WHERE ENAME LIKE 'C%';(开头为C的数据)
SELECT ENAME FROM EMP WHERE ENAME LIKE '%S';(结尾为S的数据)
SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%';(开头为S的数据)
SELECT ENAME FROM EMP WHERE ENAME LIKE '%S%';(数据中任意位置包含S的数据)
--_代表了任意字符的一次
SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%';
SELECT ENAME FROM EMP WHERE ENAME LIKE '__A%';
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A__';
--转义字符
SELECT ENAME FROM EMP WHERE ENAME LIKE '%\%%' ESCAPE '\';
--排序
--ASC(默认)升序desc降序
SELECT * FROM EMP ORDER BY SAL;
SELECT * FROM EMP ORDER BY SAL ASC;
SELECTX * FROM EMP ORDER BY SAL DESC;
SELECT * FROM EMP ORDER BY HIREDATE;
SELECT * FROM EMP ORDER BY ENAME;
SELECT * FROM EMP ORDER BY JOB;
SELECT * FROM EMP ORDER BY JOB,EMPNO;
SELECT * FROM EMP ORDER BY DEPTNO,JOB DESC,EMPNO;
SELECT * FROM EMP WHERE ENAME LIKE '%A%' ORDER BY SAL;
--函数,类似于java中的方法
SELECT ENAME || '的年薪为' || SAL*12 FROM EMP;
SELECT ENAME,(SAL+COMM)*12 FROM EMP;
SELECT * FROM EMP ORDER BY COMM;
COMM=NULL?0:COMM;
--NVL (expr1, expr2)
--若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。
--单行函数
-- 进去一个出来一个
/*
DQL语言之单行函数--数字
(JAVA) ABS CEIL FLOOR ROUND SQRT POW MOD
*/
SELECT SAL/1000,CEIL(SAL/1000) 向上取整,FLOOR(SAL/1000) 向下取整 FROM EMP;
SELECT SAL/1000,ROUND(SAL/1000) 四舍五入 FROM EMP;
SELECT SAL/1000,TRUNC(SAL/1000),TRUNC(SAL/1000,1),TRUNC(SAL/10,-2) FROM EMP;
SELECT MOD(SAL,8000) FROM EMP;
/*
DQL语言之单行函数--字符串
(JAVA) TRIM,REPLACE,SPLIT,SUBSTRING,TOUPPERCASE,TOLOWERCASE,CONCAT,INDEXOF,LIX,CHARAT
*/
SELECT CONCAT(CONCAT('员工的姓名为:',ENAME),'--AAAA') FROM EMP;
SELECT ENAME,UPPER(ENAME),LOWER(ENAME),INITCAP(ENAME) FROM EMP;
SELECT LTRIM(' A B '),RTRIM(' A B '),TRIM(' A B ') FROM EMP;
SELECT ENAME ,LPAD(ENAME,10,'#'),RPAD(ENAME,10,'&') FROM EMP;
SELECT ENAME,INSTR(ENAME,'A') FROM EMP;
SELECT ENAME,LENGTH(ENAME) FROM EMP;
/*
DQL语言之单行函数--日期
(Java)
*/
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE + 13 FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,12) FROM DUAL;
SELECT SYSDATE-HIREDATE FROM EMP;
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;
SELECT LAST_DAY(HIREDATE) FROM EMP;
SELECT NEXT_DAY(SYSDATE,'星期二') FROM DUAL;
/*
DQL语言之单行函数--转换函数
*/
--将日期转成字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DY') FROM DUAL;
--将数字转成字符串
SELECT TO_CHAR(1234.5678,'99999.99999') FROM DUAL;
SELECT TO_CHAR(1234.5678,'00000.00000') FROM DUAL;
SELECT TO_CHAR(1234.5678,'00.00') FROM DUAL;
SELECT TO_CHAR(1234.5678,'99.99') FROM DUAL;
SELECT TO_CHAR(1234.5678,'0000.00') FROM DUAL;
SELECT TO_CHAR(1234.5678,'9999.99') FROM DUAL;
SELECT TO_CHAR(1234.5678,'$00000.00000') FROM DUAL;
SELECT TO_CHAR(1234.5678,'L00000.00000') FROM DUAL;
SELECT TO_CHAR(1234567890,'$000,000,000,000.00') FROM DUAL;
--相反的
SELECT TO_DATE('2012-11-11','YYYY-MM-DD') FROM DUAL;
SELECT TO_NUMBER('123.45600','9999.99999') FROM DUAL;
SELECT TO_NUMBER('123.45600','0000.99999') FROM DUAL;
/*
DQL语言之单行函数--其他函数
*/
--多行(组)函数
-- 无论进去多少个,都出来一个
SELECT MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP;
SELECT COUNT(SAL),SUM(SAL) FROM EMP;
SELECT MAX(ENAME),MIN(ENAME) FROM EMP;
--AVG和sum只能计算纯数字
SELECT AVG(ENAME),SUM(ENAME) FROM EMP;
--空值的处理
SELECT MIN(COMM),MAX(COMM) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
SELECT SUM(COMM) FROM EMP;
SELECT AVG(COMM) FROM EMP;
部分练习:
--1.找出各月倒数第3天受雇的所有员工.
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=LAST_DAY(HIREDATE)-2;
--2.找出早于12年前受雇的员工.
SELECT ENAME,HIREDATE FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12>13;
--3.以首字母大写的方式显示所有员工的姓名.
SELECT INITCAP(ENAME) FROM EMP;
--4.显示正好为5个字符的员工的姓名.
SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=5;
--5.显示不带有"R"的员工的姓名
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%R%';
--6.显示所有员工姓名的前三个字符.
SELECT SUBSTR(ENAME,1,3)FROM EMP;
--7.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ENAME,'A','a')ENAME FROM EMP;
--8.显示满10年服务年限的员工的姓名和受雇日期.:
SELECT ENAME,HIREDATE FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12>10;
--9.显示员工的详细资料,按姓名排序.
SELECT * FROM EMP ORD;
--10.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE ASC;
--11.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
SELECT ENAME,JOB,SAL FROM EMP ORDER BY JOB DESC,SAL ASC;
--12.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
SELECT ENAME,TO_CHAR(HIREDATE,'MM-YYYY') FROM EMP ORDER BY TO_CHAR(HIREDATE,'MM')DESC,TO_CHAR(HIREDATE,'YYYY')ASC;
--14.找出在(任何年份的)2月受聘的所有员工。
SELECT ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,'mm')=2;
--15.对于每个员工,显示其加入公司的天数.
SELECT ENAME,ROUND(SYSDATE-HIREDATE)Day FROM EMP;
--16.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';