SQL查询

ORACLE...

SQL登录

conn scott/tiger ;

SQL简单查询

SELECT[DISTINCT] * | 列名称[别名],列名称[别名],... FROM 表名称[别名];  

SELECT * FROM emp;    查询全部
SELECT empno,ename,job,sal FROM emp;     查询部分
SELECT DISTINCT job FROM emp;    消除重复内容，如是多个列，则多个列全相同才能消除
SELECT empno,ename,sal*12 income FROM emp;    查询可以进行四则运算，定义别名
SELECT '雇员',empno,ename FROM emp;    查询可以设置常量'字符串'，20，日期：日-月-年
SELECT empno||ename FROM emp;       ||表示合并

SQL限定查询

SELECT[DISTINCT] * | 列名称[别名],列名称[别名],... FROM 表名称[别名] [WHERE 过滤条件(s)];  

关系运算符：>、<、>=、<=、<>(!=);

逻辑运算符：AND、OR、NOT;

范围运算符：BETWEEN...AND;

谓词范围：IN、NOT IN;

空判断：IS NULL、IS NOT NULL;

模糊查询：LIKE。

SELECT * FROM emp WHERE sal>1500;
SELECT * FROM emp WHERE ename='SMITH';  数据区分大小写
SELECT * FROM emp WHERE sal>=1500 AND sal<=3000; 匹配两个条件
SELECT * FROM emp WHERE sal>2000 OR job='CLERK';
SELECT * FROM emp WHERE NOT sal>=2000;

BETWEEN 最小值（数字、日期） AND 最大值；（均为包含最小与最大）

SELECT * FROM emp WHERE sal BETWEEN 1500 AND 2000; 匹配一个条件
SELECT * FROM emp WHERE hiredate BETWEEN '01-1月-81' AND '31-12月-81';

空不能用关系运算符完成

SELECT * FROM emp WHERE comm IS NOT NULL;
SELECT * FROM emp WHERE NOT comm IS NULL;

IN操作符是给出一个指定的可选范围

SELECT * FROM emp WHERE empno IN (7369,7566,7788,9999,NULL);    范围内有NULL没有影响
SELECT * FROM emp WHERE empno NOT IN (7369,7566,7788,9999,NULL);    范围内有NULL不能查询    
SELECT * FROM emp WHERE NOT empno IN (7369,7566,7788,9999);
SELECT * FROM emp WHERE sal>2000 OR job='CLERK';
SELECT * FROM emp WHERE NOT sal>=2000;

LIKE通配符

”_“：匹配任意的一位字符；

”%“：匹配任意的零位、一位或多位字符。

SELECT * FROM emp WHERE ename LIKE 'A%'; 以字母A开头的雇员信息
SELECT * FROM emp WHERE ename LIKE '_A%'; 姓名第二个字母为A的雇员信息
SELECT * FROM emp WHERE ename LIKE '%A%'; 姓名包含字母为A的雇员信息

·LIKE 不仅仅可以查询字符串，也可查询各种数据类型；

·使用LIKE如不设置关键字，则表示查询全部。

查询排序

SELECT[DISTINCT] * | 列名称[别名],列名称[别名],... FROM 表名称[别名] [WHERE 过滤条件(s)][ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];  

ASC:(默认)，按照升序的方式排列；

DESC：按照降序的方式排列。

SELECT * FROM emp ORDER BY sal DESC;
SELECT * FROM emp WHERE job='SALESMAN' ORDER BY sal DESC; 一个字段排序
SELECT * FROM emp ORDER BY sal DESC,hiredate ASC; 多个字段排序
SELECT empno,ename,sal*12 income FROM emp ORDER BY income; 可以按照SELECT别名排序

练习

select ename,empno,deptno from emp where job='CLERK';
select * from emp where comm>sal*0.6;
select * from emp where (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');
select * from emp where (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK') OR (job NOT IN ('MANAGER','CLERK') AND sal>=2000);
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
select * from emp where comm is null or comm <100;
select ename from emp where ename not like '%R%';
select * from emp where ename like '%A%' order by sal desc,hiredate asc,job asc;