SQL简单查询
表中字符串常量区分大小写,表名、字段名和SQL命令不区分大小写。
以oracle
里这两张EMP
表和DEPT
表为例:
EMP表的表头:
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
EMP表的表体:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
DEPT表的表头:
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
DEPT表的表体:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1、SELECT
子句和FROM
子句:
按列查询:
SELECT [ALL|DISTINCT] 列名1,列名2 //SELECT后面的列名就是查询出来后显示的列
FROM 表名|视图名 //ALL一般省略,DISTINCT是结果去掉重复行
SQL> SELECT empno,ename
2 FROM emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
查询所有列:
SELECT * // * 就是查询表中所有列
FROM 表名
SQL> SELECT *
2 FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
AS
可以改变查询到的列的标题:
SELECT 表中字段名1 AS 你想要的标题名1,表中字段名2 AS 你想要的标题名2
FROM 表名
SQL> SELECT empno AS 员工编号,hiredate AS "入职 时间" //标题有空格需要加" "
2 FROM emp;
员工编号 入职 时间
---------- --------------
7369 17-12月-80
7499 20-2月 -81
7521 22-2月 -81
7566 02-4月 -81
7654 28-9月 -81
7698 01-5月 -81
7782 09-6月 -81
7788 19-4月 -87
7839 17-11月-81
7844 08-9月 -81
7876 23-5月 -87
7900 03-12月-81
7902 03-12月-81
7934 23-1月 -82
可以显示计算后的列:
SELECT 字段名[+|-|*|/|%] //把查询到的结果进行算术运算再输出
FROM 表名
SQL> SELECT sal+300 AS 工资加三百,sal+comm AS 工资和奖金
2 FROM emp;
工资加三百 工资和奖金
---------- ----------
1100
1900 1900
1550 1750
3275
1550 2650
3150
2750
3300
5300
1800 1500
1400
1250
3300
1600
DISTINCT
去掉重复行:
SELECT DISTINCT 列名,列名
FROM 表名
SQL> SELECT DISTINCT sal //去掉了sal这一字段重复的行
2 FROM emp;
SAL
----------
2450
5000
1300
1250
2850
2975
1100
3000
800
1600
1500
950
SQL> SELECT DISTINCT deptno //去掉了deptno这一字段重复的行
2 FROM emp;
DEPTNO
----------
30
20
10
SQL> SELECT DISTINCT sal,deptno //去掉了sal和deptno都重复的行
2 FROM emp; //只重复sal或deptno不会被去掉
SAL DEPTNO
---------- ----------
5000 10
1300 10
1600 30
1250 30
2450 10
3000 20
800 20
2850 30
1500 30
1100 20
950 30
2975 20
2、WHERE
子句:
基本格式:
SELECT 列名
FROM 表名|视图
WHERE 条件
SQL> SELECT *
2 FROM emp
3 WHERE ename='SMITH'; //字符串必须用单引号括起来
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
SQL> SELECT *
2 FROM emp
3 WHERE hiredate='17-12月-80'; //日期必须单引号括起来,默认格式'DD-MM月-YYYY'
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
比较运算符:
=
等于,<
小于,>
大于,<=
小于等于,>=
大于等于,<>
或!=
不等于,!>
不大于,!<
不小于。
SQL> SELECT *
2 FROM emp
3 WHERE sal>2000; //查询sal大于2000的所有列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
逻辑运算符:
AND
与,OR
或,NOT
非
SELECT 列名
FROM 表名
WHERE [NOT] 条件1 {AND|OR} [NOT] 条件2
SQL> SELECT *
2 FROM emp
3 WHERE sal>1000 AND NOT deptno=20; //查询sal大于1000并且deptno不等于20的所有列
等价于WHERE sal>1000 AND deptno!=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
SQL> SELECT *
2 FROM emp
3 WHERE NOT (sal>1000 AND job='SALESMAN');
//查询不是(工资大于1000且工作是SALESMAN)的所有列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
字符串匹配:
SELECT 列名
FROM 表名
WHERE 字段 = 'ABCDFG' //查询字段='ABCDEF'
字段 LIKE '%A' //匹配字段前面任意字符且最后是A
字段 LIKE 'A%' //匹配字段后面任意字符且首个是A
字段 LIKE '%A%' //匹配字段前后任意字符且中间是A
字段 LIKE 'A_' //匹配字段倒数第二个字符是A
字段 LIKE '_A' //匹配字段第二个字符是A
字段 LIKE '%A_' //匹配字段前面任意字符但倒数第二是A
字段 NOT LIKE '%A' //匹配字段不是前面任意字符且最后是A
SQL> SELECT *
2 FROM emp
3 WHERE ename LIKE '%M%'; //查询ename中间包含M的所有列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
范围比较:
SELECT 列名
FROM 表名
WHERE 字段 [NOT] BETWEEN 表达式 AND 表达式 //闭区间
SQL> SELECT *
2 FROM emp
3 WHERE hiredate BETWEEN '1-1月-1981' AND '1-1月-1983';
//查询入职日期是在1981.1.1到1983.1.1之间的所有列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
SQL> SELECT *
2 FROM emp
3 WHERE sal NOT BETWEEN 1500 AND 3000;
//查询工资不在1500到3000之间的所有列
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
查询列表:(多个类似条件一起查询)
SELECT 列名
FROM 表名
WHERE 字段 [NOT] IN(值1,值2)
SQL> SELECT *
2 FROM emp
3 WHERE job IN('CLERK','SALESMAN','MANAGER');
//查询工作是CLERK或SALESMAN或MANAGER
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
判断空值:
SELECT 列
FROM 表
WHERE 字段 IS [NOT] NULL //判断字段是否为空
3、ORDER BY
子句:
SELECT 列
FROM 表
ORDER BY 字段1|表达式1 [ASC|DESC][,字段2|表达式2 [ASC|DESC]]
//ASC升序排序(可省略),DESC降序排序,默认升序
SQL> SELECT ename,sal
2 FROM emp
3 ORDER BY 2; //根据第二列排序,也就是根据sal升序排序
等价于ORDER BY sal
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
KING 5000
SQL> SELECT ename,sal,deptno
2 FROM emp
3 WHERE job='SALESMAN'
4 ORDER BY deptno,sal DESC; //根据deptno升序排序,sal降序排序
//如果deptno有相同值,再根据sal排序
ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
TURNER 1500 30
MARTIN 1250 30
WARD 1250 30
统计函数:
函数名 | 描述 |
---|---|
AVG([DISTINCT]表达式) | 求平均值,DISTINCT是去掉重复值再求平均值,忽略空值 |
MAX(表达式) | 返回最大值 |
MIN(表达式) | 返回最小值 |
SUM([DISTINCT]表达式) | 求和,DISTINCT是不算重复的值 |
COUNT([DISTINCT]字段) | 返回该字段的记录数,也就是有多少行,DISTINCT忽略重复行 |
COUNT(*) | 返回所有记录的行数 |
SQL> SELECT AVG(sal) AS 平均工资,SUM(sal) AS 工资总和
2 FROM emp;
平均工资 工资总和
---------- ----------
2073.21429 29025
SQL> SELECT COUNT(*) AS 总人数
2 FROM emp;
总人数
----------
14
4、GROUP BY
子句:
SELECT 列
FROM 表
WHERE 条件
GROUP BY 条件1[,条件2] //先按条件1分组再按条件2分组
优先级是WHERE
>GROUP BY
:先将满足WHERE条件的结果查询出来再按GROUP BY进行分组
SQL> SELECT deptno AS 部门编号,COUNT(*) AS 员工数,AVG(sal) AS 平均工资,AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) AS 平均工作年限
2 FROM emp
3 GROUP BY deptno;
部门编号 员工数 平均工资 平均工作年限
---------- ---------- ---------- ------------
30 6 1566.66667 38.7781976
20 5 2175 36.5058802
10 3 2916.66667 38.4797153
先将deptno相同的行分成一组,一共有3组,分别是deptno=10的一组,deptno=20的一组,deptno=30的一组,然后再分别在组里使用COUNT(*)求所有记录数函数求出各组的行数(也就是每组的人数),同理也在会在组里使用AVG(sal)函数求出该组的sal列的平均值(也就是该组的平均工资)。
5、HAVING
子句:
进一步将GROUP BY
子句的结果进行筛选然后再输出
SELECT 列
FROM 表
WHERE 条件
GROUP BY 条件
HAVING 条件
SQL> SELECT deptno AS 部门编号,COUNT(*) AS 员工数,AVG(sal) AS 平均工资,AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) AS 平均工作年限
2 FROM emp
3 GROUP BY deptno
4 HAVING AVG(sal)>2000;
部门编号 员工数 平均工资 平均工作年限
---------- ---------- ---------- ------------
20 5 2175 36.5058989
10 3 2916.66667 38.479734
就是刚才的GROUP BY分组后的结果再按平均工资大于2000的条件筛选后再输出
优先级是WHERE
>GROUP BY
>HAVING
:先查询符合WHERE条件的结果,再按GROUP BY排序,再按HAVING的条件筛选然后输出