doclist 阅读(49) 评论(0)
SQL经典实例笔记
shanzm



第一章 检索记录a

1.1 检索所有行和列

问题:你有一张表,并且想查看表中的所有数据。

解决方案1:

    select * from EMP

解决方案2:

 select empno,ename,job,sal,mgr,hiredate comm,deptno from EMP

说明:

建议使用方案2,因为别人看你的代码的时候不一定知道你查询的表里面的列,所以还是写出查询的具体列更方便阅读。其次从优化的角度来说,尽量避免“ select * ” 的存在,使用具体的列代替*,避免返回多余的列。

【技巧】

  1. 查看SQL语句执行时间:
set statistics time on

select * from EMP  
-- 注释:这里写你要测试执行时间的SQL语句

set statistics time off

运行结束之后,从消息栏,可以查看

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。


  1. 查看SQL语句查询时对I/0的操作情况
set statistics io on

select * from EMP
-- 注释:这里写你要测试的SQL语句

set statistics io of

运行结束之后,从消息栏,可以查看:

表 'EMP'。扫描计数 1,逻辑读取 1 次,物理读取 0 次,
预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。



1.2 筛选行

问题:你有一张表,并且只想查看满足指定条件的行。

解决方案:
使用 WHERE 子句指明保留哪些行。使用 WHERE 子句来筛选出我们感兴趣的行。如果 WHERE 子句的表达式针对某一行的判定结果为真,那么就会返回该行的数据。

例如,下面的语句将查找部门编号为 10 的所有员工。

select * from EMP where DEPNO=10;

结果:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 1981-06-09 00:00:00.000 2450 NULL 10
7839 KING PRESIDENT NULL 1981-11-17 00:00:00.000 5000 NULL 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00.000 1300 NULL 10



1.3 查找满足多个查询条件的行

问题:你想返回满足多个查询条件的行。

解决方案:
使用带有 OR 和 AND 和圆括号()的 WHERE 子句。

例如,如果你想找出部门编号为 10 的所有员工、有奖金的所有员工以及部门编号是 20 且工资低于 2000 美元的所有员工。

【分析】也就是要找满足以下三种情况之一的员工(注意不是要求同时满足)

  • 部门编号=10;
  • 奖金不为Null(注意不是:奖金!=0,因为我们在数据库中,对无奖金的,填写为NULL);
  • 部门编号=20 & 工资<=2000;
select * from EMP
        where DEPTNO=10
                or COMM not NULL
                or SAL<=2000 and DEPTNO=20;

结果:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 00:00:00.000 800 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00.000 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00.000 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00.000 1250 1400 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00.000 2450 NULL 10
7839 KING PRESIDENT NULL 1981-11-17 00:00:00.000 5000 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00.000 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00.000 1100 NULL 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00.000 1300 NULL 10

【补充】检索:20号部门中有奖金的员工和工资不高于2000的员工

SQL语句:

select * from EMP
        where ( 
                or comm is not null
                or sal <= 2000
              )
              and deptno=20;

【分析】以上SQL语句中的where字句的逻辑运算就是

(a|b)&c=(a&c)|(b&c)

where (comm is not null and deptno=20) or (sal <= 2000 and deptno=20)

结果:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 00:00:00.000 800 NULL 20
7876 ADAMS CLERK 7788 1983-01-12 00:00:00.000 1100 NULL 20



1.4 筛选列

问题:你有一张表,并且只想查看特定列的值。

解决方案:
select之后指定你想要查询的列。

例如,只查看员工的名字、部门编号和工资。

select ename,deptno,sal from EMP;

结果:

ename deptno sal
SMITH 20 800
ALLEN 30 1600
WARD 30 1250
JONES 20 2975
MARTIN 30 1250
BLAKE 30 2850
CLARK 10 2450
SCOTT 20 3000
KING 10 5000
TURNER 30 1500
ADAMS 20 1100
JAMES 30 950
FORD 20 3000
MILLER 10 1300



1.5 创建列的别名

问题:你可能想要修改检索结果的列名,使其更具可读性且更易于理解。考虑下面这个查询,它返回的是每个员工的工资。

select ename,sal from emp

ename是什么吗?sal 指的是什么?显然这不方便阅读查询结果。

检索结果应该让人容易理解,所以我们可以在查询的时候自定义查询结果显示的列名(当然这不会改变数据库中表的列名)。

解决方案:
使用 AS 关键字创建别名,以 original_name AS new_name 的形式来修改检索结果的列名。

对于一些数据库而言, AS 不是必需的,但所有的数据库都支持这个关键字。

【注意】自定义的显示列名(别名),不允许有空格

SQL语句:

select  
        ename as EmployeeName,
        sal as salary
from EMP
where deptno=30;

结果:

EmployeeName salary
ALLEN 1600
WARD 125
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 950