SQL语句

1、课程名称：SQL语句

 

2、知识点

2.1、上次课程的主要知识点

1、   Oracle中四个主要用户；

2、   sqlplus命令的使用；

3、   常见服务问题的解决；

2.2、本次预计讲解的知识点

1、   SQL简介与简单查询语句的使用；

2、   限定查询的操作，并且掌握限定查询的操作符；

3、   数据的排序功能；

3、具体内容

3.1、认识SQL（了解）

         SQL（Structured Query Language，结构查询语言）是一个功能强大的数据库语言。SQL通常用于与数据库的通讯。ANSI（美国国家标准学会）声称，SQL是关系数据库管理系统的标准语言。

         实际上对于数据库的发展，最鼎盛的时候有70多种数据库，但是各个数据库的操作方式都完全不一样，所以对于数据库的维护人员而言就增加了许多的负担，而后来由IBM研究出来了SQL语句，并且开始在各个数据库上陆续支持，而这些数据库之中，最早支持SQL的就是oracle数据库，可是需要说明的是SQL一直到了SQL-92标准产生之后才真正的开始在数据库之中成为了一个标准。

SQL功能强大，概括起来，它可以分成以下几组：

• DML（Data Manipulation Language，数据操作语言）—— 用于检索或者修改数据，在程序的开发中此部分的代码为开发人员重点需要编写的地方，也是整个课程中需要掌握的重点部分。

• DDL（Data Definition Language，数据定义语言）—— 用于定义数据的结构，如创建、修改或者删除数据库对象，例如以后要讲解的数据库的表的管理或者是用户管理等，都是属于此类语言，一般开发中都有专门的DBA负责此部分。

• DCL（Data Control Language，数据控制语言）—— 用于定义数据库用户的权限，也是由DBA进行控制的。

3.2、简单查询语句（重点）

         所谓的简单查询语句指的是从一张数据表中检索出全部的数据，而简单查询语句的语法如下：

SELECT [DISTINCT] * | 列 [别名],列 [别名],...

FROM 表名称 [别名] ;

范例：查询雇员表中的全部数据，肯定是所有列都要显示，就使用*

SELECT * FROM dept ;

         如果说现在非要写出各个列也可以：

SELECT deptno,dname,loc FROM dept ;

范例：查询每个雇员的编号、姓名、职位，查询emp表

SELECT empno,ename,job FROM emp ;

范例：查询所有雇员的职位，职位是job字段，所以现在肯定查询job

SELECT job FROM emp ;

         但是这个时候发现数据上有重复，因为本来就是一个职位有多个雇员，那么如果不希望显示重复数据，则可以使用DISTINCT关键字完成。

SELECT DISTINCT job FROM emp ;

         但是在这里必须明确强调的是，DISTINCT关键字可以消除的重复列指的是全部数据都重复。

范例：查询雇员姓名及职位

SELECT DISTINCT ename,job FROM emp ;

         在使用简单查询语句的时候，也支持数学的四则运算，当然，运算本身也一定会存在优先级：先乘除后加减。

范例：现在要求显示出每个雇员的年薪和日薪、月薪

SELECT ename,sal/30,sal,sal*12 FROM emp ;

         直接显示一个“SAL/30”、“SAL*12”，这个实在是看不懂，这个需要换一下，这个时候可以起一个别名。

SELECT ename,sal/30 daily, sal,sal*12 income FROM emp ;

         一般对于别名而言，写与不写意义不大（针对于程序开发而言），但是写上的话会维护方便一些，并且别名允许是中文，但不建议使用：

SELECT ename 姓名,sal/30 日薪, sal 月薪,sal*12 年薪 FROM emp ;

范例：公司为了为雇员增加福利，每个雇员每月有150元的饭食补助，每月还有50元的电话补助，每月还有100元的工交补助，那么现在来算，年薪该是多少？

SELECT ename , (sal+150+50+100) * 12 FROM emp ;

范例：为了更加的鼓励员工工作，于是每年年底发14个月工资（多发两月）

SELECT ename , (sal+150+50+100) * 12 + sal * 2 FROM emp ;

         在使用简单查询语句的时候，也可以使用“||”进行数据显示的连接，例如，现在希望的显示方式：姓名：SMITH，职位：CLERK，很明显，以后红色字体的部分要换成不同的数据（ename，job字段提供），而其他的内容肯定是原样输出的，所以对于所有原样输出的数据都要使用“'”括起来，即：现在的程序实现如下：

SELECT '姓名：' || ename || '，职位：' || job details FROM emp ;

         以后只要是“'”声明的在Oracle之中都表示字符串。

         但是在此时有两个重要的概念先解释一下：

                   · 如果现在是为字段起别名，则这个别名是不加“'”，而如果是要显示的字符串数据则必须加“'”；

         · 关于子句：经常会听到如下的语句：SELECT子句、FROM子句，所谓的子句指的就是这些SELECT、FROM之后所编写的代码，例如：

SELECT empno,ename,job

FROM emp ;

         如果说的是SELECT子句，指的就是“empno,ename,job”，而FROM子句指的就是“emp”。

         一定要记住的是简单查询的主要特点是将数据表中的指定列的全部数据显示。

3.3、限定查询（重点）

         对于简单查询而言是将一张表的全部数据进行了显示，而限定查询指的是对查询做一个条件的过滤，满足条件的数据可以显示出来，而限定查询的SQL语句格式如下所示：

SELECT [DISTINCT] * | 列 [别名],列 [别名],...

FROM 表名称 [别名]

[WHERE 限定条件(s)];

         可以发现，设置限定条件的时候可以同时设置多条过滤。

         在使用限定查询的时候提供了以下的几个主要的过滤条件操作：

                   · 关系操作：>、<、>=、<=、=、!=（<>）；

                   · 逻辑操作：AND、OR、NOT；

1、   关系运算操作  （数字和字符串）

范例：现在希望查询出所有雇员的信息，但是要求这些雇员的基本工资高于2000，很明显，此时一定要对数据执行过滤，要过滤的字段是sal字段

SELECT *

FROM emp

WHERE sal>2000 ;

范例：要求查询出雇员编号是7566的雇员信息，此时过滤的是empno字段。

SELECT *

FROM emp

WHERE empno=7566 ;

范例：查询出姓名是smith的雇员信息，要对ename字段进行过滤

SELECT *

FROM emp

WHERE ename='smith' ;

         但是这个时候并没有任何的查询结果显示，因为真正在emp表中保存的数据姓名都是大写字母：

SELECT *

FROM emp

WHERE ename='SMITH' ;

         在oracle数据库之中，所有的数据都是区分大小写的。

范例：希望查询职位是销售人员，并且基本工资高于1200的所有雇员信息

         现在这两个条件肯定都必须同时满足，那么肯定使用的是AND连接符。

SELECT *

FROM emp

WHERE job='SALESMAN' AND sal>1200 ;

范例：要求查询出所有10部门的全部雇员或者是20部门的业务员的信息

         条件分为两个部分，有一个条件满足即可：

                   · 10部门的全部雇员；

                   · 20部门的业务员，但是此处需要同时对职位过滤，所以使用AND；

         有一个满足即可的话，那么肯定使用OR连接符。

SELECT *

FROM emp

WHERE deptno=10 OR (deptno=20 AND job='CLERK') ;

范例：要求查询出工资范围在1500~3000之间的雇员信息

SELECT *

FROM emp

WHERE sal>=1500 AND sal<=3000 ;

范例：查询不是办事员的雇员信息，并且这些雇员的基本工资大于2000

SELECT *

FROM emp

WHERE job!='CLERK' AND sal>2000 ;

SELECT *

FROM emp

WHERE job<>'CLERK' AND sal>2000 ;

SELECT *

FROM emp

WHERE NOT(job='CLERK' OR sal<=2000) ;

         所以通过以上的程序可以观察到，所有的关系运算符并不一定只在数字上使用，字符串上照样应用。

2、   范围运算符：BETWEEN…AND…   （字符串和日期上）

         BETWEEN…AND…操作符主要是在一个指定的范围之中查询数据，其语法如下：

BETWEEN 最小值 AND 最大值；  à 比较中包含了=，即：>=最小值，<=最大值

范例：查询出工资范围在1500~3000之间的全部雇员信息

SELECT *

FROM emp

WHERE sal BETWEEN 1500 AND 3000 ;

         但是需要注意的是，BETWEEN..AND操作符并不只是单单的针对于数字这种类型，对于日期类型也可以使用。

范例：查询所有在1981年雇佣的雇员信息

         如果要查询，肯定要有一个范围：1981-01-01 ~ 1981-12-31，但是现在一个最麻烦的问题是DATE型数据的表示，观察一下原本的数据的表示“02-4月 -81”，而且日期可以和字符串自动转换（此种格式下的字符串会自动发生转换成DATE型），但是要求字符串必须按照与之一样的格式编写，即：现在的范围应该是“01-1月 -81 ~ 31-12月 -1981”。

SELECT *

FROM emp

WHERE hiredate BETWEEN '01-1月 -81' AND '31-12月 -1981' ;

3、   判断为null

         null表示的是没有内容，null并不表示为数字0，而如果说现在要想判断是否为空，则可以使用IS NULL操作完成，而如果不为空则可以使用IS NOT NULL完成。

范例：查询出所有领取奖金的雇员，领取奖金意味着comm不为null。

SELECT *

FROM emp

WHERE comm IS NOT NULL ;

SELECT *

FROM emp

WHERE NOT comm IS NULL ;

范例：查询所有不领取奖金的雇员

SELECT *

FROM emp

WHERE comm IS NULL ;

4、   IN操作符：表示的是进行一个范围的指定判断

         下面首先先通过一个程序来研究一下IN的作用。

范例：现在要求查询出雇员编号是7369、7566、7788、9098的雇员信息，现在很明显要使用OR进行不同条件的连接，有一个条件满足即可。

SELECT *

FROM emp

WHERE empno=7369 OR empno=7566 OR empno=7788 OR empno=9098 ;

         这个时候查询已经成功的完成了，但是这种查询很明显已经给出了数据的选择范围，如果这样一直使用OR编写虽然可以完成，但是整个代码实在是太长了。

范例：通过IN操作符完成

SELECT *

FROM emp

WHERE empno IN (7369,7566,7788,9098) ;

         同样，如果现在要对数据取反的话，可以使用NOT或者是NOT IN：

SELECT *

FROM emp

WHERE NOT empno IN (7369,7566,7788,9098) ;

SELECT *

FROM emp

WHERE empno NOT IN (7369,7566,7788,9098) ;

注意：关于IN与NOT、NULL的问题

         NULL表示的是空，如果出现的是NOT NULL表示的非空。

范例：在IN操作中使用null

SELECT *

FROM emp

WHERE empno IN (7369,7566,7788,null) ;

         这个时候可以正常的完成数据的查询。

范例：在NOT IN中使用null

SELECT *

FROM emp

WHERE empno NOT IN (7369,7566,7788,null) ;

         按照正常的思路现在NOT IN NULL，表示的是不是空，那么就应该显示全部的数据，可是这种语法不能使用。

         结论：使用NOT IN操作的时候不能出现null。

5、   LIKE操作符：是进行模糊匹配的  （任意数据类型）

         例如：某些系统提供了关键字检索，那么这些检索都可以利用LIKE子句完成，但是必须提醒的是在搜索引擎上的关键字查询可不是依靠的LIKE，是依靠着程序的分词操作。

         如果要使用LIKE，那么首先必须介绍两个匹配的符号：

                   · “_”：表示匹配任意的一个字符；

                   · “%”：表示匹配任意多个字符，0个、1个或多个；

范例：希望查询出雇员姓名中是以字母“A”开头的全部雇员信息 —— A%

SELECT *

FROM emp

WHERE ename LIKE 'A%' ;

范例：希望查询出雇员姓名中第二个字母是“A”的雇员信息

SELECT *

FROM emp

WHERE ename LIKE '_A%' ;

范例：要求查询姓名中任意位置上包含字母A的

SELECT *

FROM emp

WHERE ename LIKE '%A%' ;

注意：模糊查询本身的功能就是关键字匹配，但是有以下两个注意点：

         · 注意点一：模糊查询的时候可以是任意的数据类型（数字、日期），并不一定只是针对于字符串

SELECT *

FROM emp

WHERE sal LIKE '%7%' OR hiredate LIKE '%7%' ;

         ·注意点二：在使用LIKE的时候如果没有设置任何的查询关键字，则表示查询全部：

SELECT *

FROM emp

WHERE sal LIKE '%%' OR hiredate LIKE '%%' ;

         对于这一特性，在日后的开发之中，可以帮助用户解决不少问题。

3.4、限定查询练习题（重点）

1、   选择部门30中的所有员工。

SELECT * FROM emp WHERE deptno=30 ;

2、   列出所有办事员（CLERK）的姓名，编号和部门编号。

SELECT ename,empno,deptno FROM emp WHERE job='CLERK' ;

3、   找出佣金高于薪金的员工。

SELECT * FROM emp WHERE comm>sal ;

4、   找出佣金高于薪金的60%的员工。

SELECT * FROM emp WHERE comm>sal*0.6 ;

5、   找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。

SELECT * FROM emp

WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK') ;

6、   找出部门10中所有经理（MANAGER），部门20中所有办事员（CLERK），既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。

SELECT * FROM emp

WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK')

OR (job NOT IN('MANAGER','CLERK') AND sal>2000) ;

7、   找出收取佣金的员工的不同工作。

SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL ;

8、   找出不收取佣金或收取的佣金低于100的员工。

SELECT * FROM emp WHERE comm IS NULL OR comm < 100 ;

9、   显示不带有“R”的员工的姓名。

SELECT * FROM emp WHERE ename NOT LIKE '%R%' ;

10、 显示姓名字段的任何位置包含“A”的所有员工的姓名。

SELECT * FROM emp WHERE ename LIKE '%A%' ;

         以上的只是把一些基本的知识进行了巩固，不熟悉的代码当天解决。

3.5、数据排序（重点）

         如果现在希望查询出来的数据可以按照指定的列进行由高到低或者是由低到高的排序的话，那么就可以使用ORDER BY子句完成，而此时的SQL语句格式如下：

SELECT [DISTINCT] * | 列 [别名],列 [别名],...

FROM 表名称 [别名]

[WHERE 限定条件(s)]

[ORDER BY 排序字段 [ASC | DESC] [,排序字段 [ASC | DESC] ...]];

         但是需要说明的，在数据排序的时候有两种方式：

                   · ASC（默认，不写也是它）：数据由低到高排序；

                   · DESC：数据由高到低排序；

         在使用ORDER BY子句的时候，一定要记住，此语句必须放在所有查询语句的最后，而且此语句可以针对于任何数据类型完成，包括数字、字符串、日期。

范例：查询数据，按照姓名排序

SELECT * FROM emp ORDER BY ename ;

范例：查询数据，按照工资由高到低排序

SELECT * FROM emp ORDER BY sal DESC ;

范例：查询数据，按照雇佣日期由早到晚排序

SELECT * FROM emp ORDER BY hiredate ASC ;

         在执行排序的时候，实际上也可以同时指定多个排序的列。

范例：要求显示的数据按照职位排序，如果职位相同，则按照工资由高到低排序，如果工资相同，则按照雇佣日期由早到晚进行排序。

SELECT *

FROM emp

ORDER BY job,sal DESC ,hiredate ;

         对于排序的操作并没有过于复杂，即便有了WHERE子句，那么ORDER BY也找可以使用。

范例：要求查询出工资范围在1500~2500之间的所有雇员信息，显示的结果按照雇佣日期由早到晚排序

SELECT *

FROM emp

WHERE sal BETWEEN 1500 AND 2500

ORDER BY hiredate ;

4、总结

1、   数据查询的标准格式：

SELECT [DISTINCT] * | 列 [别名],列 [别名],...

FROM 表名称 [别名]

[WHERE 限定条件(s)]

[ORDER BY 排序字段 [ASC | DESC] [,排序字段 [ASC | DESC] ...]];

         要求可以将各个字句的使用熟练；

2、   在使用限定查询的时候所讲解的若干个限定条件：关系运算、逻辑运算、BETWEEN..AND、LIKE、IN、NULL；

5、预习任务

         单行函数、多表查询、分组统计查询、子查询、数据更新操作。

6、作业

1、   将scott用户中的全部表结构记下，为明天准备；

2、   将所有讲解过的SQL语句熟悉三遍以上；