sql培训

目录
1．SQL ..................................................................................................... 错误!未定义书签。

、SQL简介 (1)
、scott用户下的所有表 (2)
1、emp (2)
2、dept (2)
3、salgrade (2)
4、bonus (3)
、简单查询查询语句 (3)
、限定查询 (4)
、对结果进行排序 (5)
、单行函数 (6)
、字符函数 (6)
、数值函数 (7)
、日期函数 (7)
、转换函数 (8)
、通用函数 (9)
、多表查询 (9)
、多表查询的根本概念 (9)
、左、右连接 (10)
、组函数分组统计 (10)
、组函数 (16)
、分组统计 (16)
、数据库更新操作 (18)
、增加数据 (18)
、删除数据 (19)
、事务处理操作 (19)
、ROWNUM ............................................................................................ 错误!未定义书签。

、常见的数据类型 (19)
、建立表 (20)
、删除表 (20)
、SQL简介
SQL〔Structured Query Language，结构查询语言〕是一个功能强大的数据库语言。

SQL通常用于与数据库的通讯。

ANSI〔美国国家标准学会〕声称，SQL是关系数据库管理系统的标准语言。

对于各个数据库来讲，现在的数据库根本上都是支持SQL语法的，也就是说其已经形成了一个操作
的标准，只要掌握了标准的SQL语法，并且掌握了一些数据库的使用方法，各个数据库根本上都可以使用。

SQL功能强大，概括起来，它可以分成以下几组
•DML〔Data Manipulation Language，数据操作语言〕——用于检索或者修改数据
•DDL〔Data Definition Language，数据定义语言〕——用于定义数据的结构，如创立、修改或者删除数据库对象
•DCL〔Data Control Language，数据控制语言〕——用于定义数据库用户的权限
、scott用户下的所有表
在scott用户下本身存在emp〔雇员表〕、dept〔部门表〕、salgrade〔工资等级表〕、bonus〔奖金表〕，这四张表的结构
1、emp
2、dept
3、salgrade
4、bonus
、简单查询查询语句(select)
SQL语法的查询标准，标准的格式
查询了全部的记录
要求查询雇员的编号、姓名、根本工资——此时需要限制查询的列
如果现在要为每一个查询的显示列起一个别名的话，那么直接在后面编写即可。

别名
查询全部工作——查询job字段
查询结果非常的明显，有些内容出现了重复。

此时，消除掉重复的查询结果，直接使用DISTINCT去除即可。

下面需要按照以下的格式进行内容的显示
·编号是7369的雇员姓名为SMITH，职位是CLERK，根本工资是800那么，此时肯定需要进行一个连接操作，将各个查询结果进行连接，形成一个完整的内容显示出来。

可以使用“||〞进行连接的操作。

以上的显示格式中，只有四块的数据是活动的，其他的内容都应该是原样显示，而其他的内容因为是字符串的显示，所以需要加上“'〞，所以，此时格式已经可以出来
进行查询操作
在SQL语法中，本身也是支持四那么运算的。

计算一个人的根本年薪——sal * 12
、限定查询(where)
之前的查询是将一张表中的全部数据进行显示，那么如果现在要求只显示局部数据，而且是有条件显示的话，那么可以使用WHERE子句，进行条件的设置，此时的SQL语法格式如下
在进行条件设置的时候，可以同时设置多个查询条件，多个条件之间，可以使用AND或OR进行连接。

查询出根本工资大于2000的所有雇员信息——此时的查询条件是一个
查询出根本工资不小于2000的所有雇员信息——此时的查询条件是一个，>=
那么，除了以上的关系，还有=、<=、<>、!=、>、<、>=。

要求查询出姓名是allen的根本信息
将查询条件变为大写
要求查询出工作不是业务员的所有雇员信息——job不是CLERK
要求查询出工作不是业务员的并且根本工资大于2000的所有雇员信息——条件是两个，这两个条件需要同时满足，所以只能使用AND进行连接。

要求查询出领取奖金的雇员——comm字段表示奖金〔佣金〕。

可以发现局部雇员的comm字段的内容是null，所以在SQL中提供了专门的null操作，使用IS NULL判断是否为空，以及使用IS NOT NULL 判断是否不为空。

可以发现“NOT〞可以进行取反的操作，真变假、假变为真。

要求查询出不领取奖金，而且工作不是业务员的，并且根本工资范围在1500~300-之间的全部雇员信息
要求查询出根本工资大于1500，或者可以领取奖金的雇员信息——此时的条件是二选一，使用OR 操作
查询出工资范围在1500~3000的雇员也可以使用BETWEEN…AND进行查询操作
要求查询出在1981年雇佣的全部雇员信息——进行hiredate字段的查询，使用BETWEEN..AND需要指定一个期限1981-01-01 ~ 1981-12-31。

但是，在写日期的时候需要跟已有数据的格式保持一致。

BETWEEN…AND不光可以应用在数字上，也可以应用在日期上，日期可以和数字相互转换。

要求查询出雇员编号是7369、7566、7788的雇员信息
也就是说，此时查询的结果给定了一个查询的范围，面对这样的查询范围，在SQL语法中可以使用IN操作进行指定，指定的格式如下
使用IN操作修改以上的查询
要求查询出雇员编号不是7369、7566、7788的雇员信息——NOT IN
在各个程序中经常会使用一种模糊查询的语法，例如输入某些关键字，之后进行数据的查找操作，那么这样的程序可以通过LIKE语句完成，LIKE本身就表示模糊查询的操作，但是在LIKE中有两种匹配符号
·匹配单个字符“_〞
·匹配任意多个字符“%〞
要求查询出以字母S开头的全部雇员信息
要求查询出姓名的第二个字符是M全部雇员信息
要求查询出姓名中包含字母S的雇员信息
在进行模糊查询的时候，如果没有设置任何的查询关键字，那么表示查询全部〔'%%'〕
、对结果进行排序(order by)
之前的查询的数据，本身并没有明确的指定进行排序的操作，例如
是按照编号排序，但是其他的字段并没有进行排序的操作，那么现在要想对指定的内容进行排序的话，那么可以使用ORDER BY 子句完成，此语法如下
如果，现在没有指定是按照升序或者是降序排列的话，那么默认是升序排列。

要求查询出所有的销售人员，按照根本工资和奖金排序
可以发现，如果进行排序的时候，第一个条件相等了，那么按照第二个条件继续排序。

、单行函数
单行函数的根本语法
函数的解释
·function_name 函数名称，只要是函数，后面肯定有“()〞
·在函数中可以直接操作一个表的列，或者是一个表达式〔column|expression〕
·而且在进行函数操作的时候，可以同时指定操作的假设干个其他参数。

在Oracle中，单行函数主要分为以下几类
·字符函数提供字符的操作，可能是传入一个字符并返回字符，例如一个小写字母变为大写字母
·数值函数接收一些数值，并进行一系列的操作，例如四舍五入
·日期函数对日期进行操作
·转换函数将一种数据类型变为其他的数据类型
·通用函数NVL
、字符函数(lower、upper)
在Oracle中常见的字符函数主要有以下几个lower()、upper()、initcap、replace、substr、length、concat。

lower()和upper()函数
·lower()函数是将输入的内容变为小写再输出
·upper()函数是将输入的内容变为大写再输出
在Oracle中专门提供了一个dual表，此表为虚拟表，专门用于验证函数使用的，因为在Oracle里所有的函数必须以标准的SQL语句才可以使用，所以就提供了这样的表
在查询中可以使用此函数
·例如现在要查询一个雇员的姓名，用户输入的时候肯定不会去思考是大写还是小写
开头首字母大写，让每一个雇员的姓名以首字母大写的形式出现
可以将一个字符串的指定内容进行替换，使用repalce()函数，将所有的l变为x
字符截取可以使用substr()函数，此函数是非常重要的一个函数，此函数的使用语法如下
要求显示一个字符串的前三个字母
求出字符串的长度，length()
字符串连接，在oracle中可以使用concat()函数进行两个字符串的连接操作
要求显示每一个雇员的最后三个字母
、数值函数(ROUND、TRUNC)
数值函数就是操作数字的，主要提供了以下的三种操作
·四舍五入ROUND()
·截取小数TRUNC()
·取模MOD()
取模——MOD，取余数
、日期函数(sysdate)
日期函数主要是操作DATE型数据的，但是在操作之前，必须给出以下的几个公式
1、日期–数字= 日期
2、日期+ 数字= 日期
3、日期–日期= 数字〔天数〕
如果要想进行日期的操作，那么首先必须明确一点，如何求出当前的日期？
求出当前日期——sysdate
要求求出10部门雇员进入公司的星期数
·〔今天的日期–雇佣的日期〕/ 7
除此之外，Oracle中也提供了以下的几个常用的函数
·MONTHS_BETWEEN() 求出给定两个日期中的间隔月
·ADD_MONTHS() 在指定的日期上参加指定的月份，求出新的日期
·LAST_DAY() 求出给定日期的最后一天
·NEXT_DAY() 求出下一个的今天的日期
求出每个雇员被雇佣的月数
求出三个月后的今天
求出下一个的今天
现在的语言环境是中文环境，所以现在编写星期的时候，可以直接写“星期一〞。

求出给定日期的最后一天
求出在每月倒数第二天雇佣的雇员信息
、转换函数(TO_CHAR、TO_DATE)
转换函数有三个TO_CHAR()、TO_DATE()、TO_NUMBER()
分开显示年、月、日。

所以，使用TO_CHAR()函数帮助，将内容变为字符串显示，但是如果现在要进行日期的提取，那么必须进行日期的通配符的设置。

·年通过y表示，年是4位，使用yyyy表示年
·月通过m表示，月是2位，使用mm表示月
·日通过d表示，日是2位，使用dd表示日
进行拆分
TO_CHAR()函数除了可以在日期上操作之外，还可以在数字上进行操作，进行数字的格式化显示。

此时，按照每三位一个“,〞的形式，将数字的显示进行分割，那么就需要对数字进行一个通配符的设置，使用9表示数字的格式。

但是，现在的语言环境是中文，最好可以根据本地的环境进行显示。

那可以通过字母“L〞进行本地的货币样式显示。

TO_DATE()函数表示将一个字符串变为一个日期类型。

将字符串变为日期
给定一个日期，判断此日期距今天多少月？
以上是将两个字符串变为数字，并将相加的结果进行显示，但是在Oracle中已经非常的智能了，不使用TO_NUMBER()程序照样好使。

1、通用函数(nvl)
在Oracle中NVL()、DECODE()两个函数非常重要
要求求出每个雇员的姓名及年薪
但是，这样求完之后，可以发现，某些没奖金的人连根本工资都没了。

这是因为奖金为null，所以计算的结果也是null，但是并不能说没有奖金就没有年薪，此时就必须对数据进行处理，如果内容为null，最好将其变为0，那么就必须使用NVL()函数完成了。

如果comm的内容为null，那么按照0进行处理，如果不为空，那么使用其本身的内容处理。

DECODE函数绝对是Oracle中的重点内容，DECODE函数非常类似于程序中的IF…ELSE IF语句。

DECODE语法
要求显示雇员的姓名和雇员的工作，但是所有的工作都要求换成中文显示
·CLERK，业务员
·SALESMAN，销售
·ANAL YST，分析员
·MANAGER，经理
·PRESIDENT，总裁
但是，在使用DECODE函数的时候需要注意的是，如果一个内容没有在DECODE()中定义的话，那么无法显示。

以上显示的时候没有设置'PRESIDENT'的显示结果，所以最终的查询的内容就是null。

2、多表查询
之前所进行的全部查询都是在emp表中执行查询操作的，只有一张表进行查询，但是从实际来看，有可能需要多张表同时进行查询之后才可能得出最后的查询结果，那么此时就需要进行多表查询。

2.1、多表查询的概念
多表查询的语法
可以发现，唯一的区别点就在于FROM语句之后。

直接进行多表查询——dept、emp
但是以上的代码执行之后，发现返回的结果有56条记录，但是实际上雇员表中只有14条记录，而部门表中只有4条记录，但是返回的结果明显不正确了。

可以发现56条查询的结果= 雇员表的全部记录数* 部门表的全部记录数，实际上这样的结果称为笛卡尔积，那么此时，如果要想消除掉笛卡尔积的话，那么可以通过关联字段进行取消。

Emp通过deptno 字段与Dept表有所关联。

此时的查询结果可以发现已经没有任何的重复内容出现了。

此时已经消除掉了笛卡尔积。

要求查询出雇员的编号、姓名、部门编号、部门位置、部门名称
在进行多表查询的时候最好指定好表的别名以方便操作。

要求查询出每一个雇员姓名及其上级领导的姓名
所以，此时的根本思路，雇员的领导编号= 领导的雇员编号，通过mgr进行关联的查询。

要求查询出每一个雇员的姓名、职位、领导的姓名、部门的名称及位置
从此程序中，可以发现，如果表的关联越多，那么消除笛卡尔积的条件也就越多。

2.2、连接
、INNER JOIN
连接组合两个表中的字段记录。

、LEFT JOIN
连接组合两个表中的字段记录，并将包含了LEFT JOIN左边表中的全部记录。

、RIGHT JOIN
连接组合两个表中的字段记录，并将包含了RIGHT JOIN右边表中的全部记录。

INNER JOIN设定两个表相关连的运算式，以连接组合两个表中的字段记录。

INNER JOIN语法如下：
两个表连接的字段，譬如 [表1.字段1=表2.字段2]，必须具有相同的字段类型，但是字段名称不需要相同。

例如，自动编号字段类型可以连接Long 的字段类型，但是单精整数字段类型不能连接双精整数的字段类型。

比拟运算子可为=、<、>、<=、>=、或<>。

JOIN连接的字段，不可以包含MEMO字段类型或OLE对象类型，否那么会发生错误。

在一个JOIN表达式中，可以连结多个ON子句：
SELECT fields
FROM 表1 INNER JOIN 表2
ON 表1.字段1 比拟运算子表2.字段1 AND
ON 表1.字段2 比拟运算子表2.字段2) OR
ON 表1.字段3 比拟运算子表2.字段3)
JOIN表达式中，可以为巢状式：
SELECT fields
FROM 表1 INNER JOIN
(表2 INNER JOIN [( ]表3
[INNER JOIN [( ] 表x [INNER JOIN ...)]
ON 表3.字段3 比拟运算子表x.字段x)]
ON 表2.字段2 比拟运算子表3.字段3)
在一个INNER JOIN中，可以包括巢状式的LEFT JOIN或RIGHT JOIN，但是在一个LEFT JOIN或RIGHT JOIN 中不能包括巢状式的INNER JOIN。

条件 : 假设在 city 列上联接 authors 表和 publishers 表。

使用左向外联接 :
FROM authors a LEFT OUTER JOIN publishers p
特点 : 不管第二个表中是否有匹配的数据，结果将包含第一个表中的所有行。

使用右向外联接 :
FROM authors a RIGHT OUTER JOIN publishers p
特点 : 不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。

使用完整外部联接 :
sql语句 :
SELECT a.au_fname, a.au_lname, p.pub_nameFROM authors a FULL OUTER JOIN publishers pON a.city = p.city特点:显示两个表中的所有行，如果匹配，那么写在一起，不匹配，那么各自写null
--------------------------------------------------------------------------------------------------------------------------------
A left join
B 的连接的记录数与A表的记录数同
A right join
B 的连接的记录数与B表的记录数同
A left join
B 等价B right join A
table A:
Field_K, Field_A
1 a
3 b
4 c
table B:
Field_K, Field_B
1 x
2 y
4 z
select
Field_K Field_A Field_K Field_B
---------- ---------- ---------- ----------
1 a 1 x
3 b NULL NULL
4 c 4 z
select
Field_K Field_A Field_K Field_B
---------- ---------- ---------- ----------
1 a 1 x
NULL NULL 2 y
4 c 4 z
这样的
table1 table2
id,sex1 id sex2
a 1 a 4
b 0
select
那么:
id sex1 sex2
a 1 4
b 0 null
也就是说left join 那么连接左边表中所有记录都会出现，如果根据连接条件在table2中找不到相关记录，那么显示为null。

right join 那么显示右边表中的全部记录。

inner join 那么只有符合条件的记录才会出现在结果集中。

EG2∶有两表a和b，前两字段完全相同：(id int,name varchar(10)...)
id name
----------- ----------
1 a
2 b
3 c
以下的查询语句，你知道它的运行结果吗？：
1.
select * from a left join b on a.id=b.id where a.id=1
2.
select * from a left join b on a.id=b.id and a.id=1
3.
select * from a left join b on a.id=b.id and b.id=1
4.
select * from a left join b on a.id=1
结果:
id name id name
----------- ----------- ----------- -----------
1 10 1 10
(1 row(s) affected)
id name id name
----------- ----------- ----------- -----------
1 10 1 10
2 20 NULL NULL
3 30 NULL NULL
(3 row(s) affected)
id name id name
----------- ----------- ----------- -----------
1 10 1 10
2 20 NULL NULL
3 30 NULL NULL
(3 row(s) affected)
id name id name
----------- ----------- ----------- -----------
1 10 1 10
1 10
2 20
1 10 3 30
2 20 NULL NULL
3 30 NULL NULL
(5 row(s) affected)
思路:
left join , 无非是左边表为根底, 扫描右边表匹配的记录
先是左边表的第1条记录
1 a
按条件 a.id=1, 来扫描右边表的记录
对于右边表的每条记录, 显然 a.id=1 这个条件都是成立的, 所以第1条记录匹配后的结果是:
1 a 1 a
1 a
2 b
1 a 3 c
---------------------------------------------
然后再扫描第2条记录
2 b
对于条件 a.id=1, 在边表中没有与之匹配的记录, 所以右边表为NULL
因此第2条记录匹配的结果为
2 b NULL NULL
----------------------------------------------
第3条记录与第2条记录一样, 匹配的结果是
3 c NULL NULL
---------------------------------------
因此最终结果是5条记录
1 a 1 a
1 a
2 b
1 a 3 c
2 b null null
3 c null null
内联接〔典型的联接运算，使用像 = 或 <> 之类的比拟运算符〕。

包括相等联接和自然联接。

内联接使用比拟运算符根据每个表共有的列的值匹配两个表中的行。

例如，检索 students 和 courses 表中学生标识号相同的所有行。

外联接,外联接可以是左向外联接、右向外联接或完整外部联接。

在 FROM 子句中指定外联接时，可以由以下几组关键字中的一组指定：
LEFT JOIN 或 LEFT OUTER JOIN。

左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。

如果左表的某行在右表中没有匹配行，那么在相关联的结果集行中右表的所有选择列表列均为空值。

RIGHT JOIN 或 RIGHT OUTER JOIN。

右向外联接是左向外联接的反向联接。

将返回右表的所有行。

如果右表的某行在左表中没有匹配行，那么将为左表返回空值。

FULL JOIN 或 FULL OUTER JOIN。

完整外部联接返回左表和右表中的所有行。

当某行在另一个表中没有匹配行时，那么另一个表的选择列表列包含空值。

如果表之间有匹配行，那么整个结果集行包含基表的数据值。

交叉联接。

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。

交叉联接也称作笛卡尔积。

、组函数分组统计
分组将一组相关的信息进行分类，并且利用一些函数进行相关的统计，这样的操作就称为分组，分组中需要使用各个分组函数。

2.3、组函数(count、sum、avg、max、min)
在SQL语法中，可以使用以下的几种组函数进行数据的统计
验证以上的函数
1、求出emp表中的所有记录数
2、求出所有人的工资总和
3、求出最高、最低、平均工资
、分组统计(group by)
如果要想分组的话，那么需要使用GROUP BY子句完成，此时的SQL语法如下
一定要记住GROUP BY子句要放在WHERE子句之后。

求出每个部门的平均工资
注意
2、在进行分组的时候，只能查询的是分组条件、各个分组函数
但是，以上的查询结果只返回了三个部门的信息，不是很正确，缺少了40部门。

需要参加连接操作之后，才可以让40部门进行显示。

因为40部门没有雇员，所以显示的结果是0。

要求查询出平均工资大于2000的部门名称和平均工资
以上的程序提示了以下的错误
在WHERE子句上不能出现任何的分组函数，实际上对于以上的要求来讲，A VG(sal)>2000属于分组的条件，而WHERE控制的查询的条件，那么如果要想进行分组条件设置的话，那么必须使用HA VING子句，此时，语法修改如下
分组条件中可以使用分组函数，所以以上的程序修改如下
注意
除了以上的要求外，在使用分组函数的时候还有一个注意点分组函数可以嵌套使用，但是嵌套之后的查询中，不能出现任何的其他字段。

求出平均工资最高的工作的工资
思考
要求查询出非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作的雇员的月工资的合计大于5000，输出的结果按月工资的合计的升序排列
·显示非销售人员的工作名称
·将以上的查询进行分组统计，参加GROUP BY子句
·将SUM(sal)作为条件查询，编写HA VING子句。

·ORDER BY完成排序
3、数据库更新操作
下面来看一下关于数据库的更新操作，为了保证emp表中的数据不被破坏，下面先将emp表复制一份，新的表名称称为myemp。

以上的语句只是在Oracle中使用。

数据库的更新操作主要就是三种增加〔INSERT〕、修改〔UPDA TE〕、删除〔DELETE〕
3.1、增加数据(insert)
增加数据的语法
对于字符串使用“'〞括起来，而对于数字不使用“'〞，对于日期，可以根据已有的日期格式编写。

向myemp表中增加一条新的数据
·按照标准的语法完成
而且在以上的操作中可以发现日期型只要符合日期的格式即可插入。

添加一个新的雇员，但是要求雇佣日期为今天
如果需要将一个指定的日期插入到记录之中，那么此时可以通过TO_DATE()函数进行字符串 日期格式的转换。

、修改数据(update)
可以对数据表中已经存在的内容进行修改操作，修改数据的语法如下
特别要注意的是，在更新的时候如果没有写更新条件，那么意味着所有的数据都将被更新，而且一般的更新条件都是取一个唯一的编号表示，例如在myemp表中empno属于唯一的，所以，可以直接按照empno 进行更新操作。

、删除数据(delete)
可以直接使用DELETE语句进行数据的删除
与更新一样，如果没有删除条件的话，那么表示删除表中的全部记录。

删除雇员编号是7777的雇员
删除雇员编号是8888、9999、5678的雇员信息
、事务处理操作(commit、rollback)
事务一切的相关操作，要么同时成功，要么同时失败。

在Oracle中，每一个连接到数据库上的用户都称为一个SESSION。

每一个SESSION对数据库所做的修改操作，并不会立刻反映到真实的数据之上。

在更新操作中是存在事务的管理的，事务管理中主要依靠以下两个命令
·COMMIT提交事务，事务只有提交之后才能真正的在数据库上执行更新操作
·ROLLBACK回滚事务，如果发现操作有错误，那么可以进行回滚，但是已提交的操作是不能回滚的。

但是在进行事务处理的时候也会有其他的问题，事务处理会造成死锁。

当一个SESSION更新数据库的时候，但是并没有提交，其他的SESSION是无法执行更新操作的，必须等待另外一个SESSION提交之后才可以真正的更新。

在等待另一方的操作完成，就会造成死锁的问题，在以后的数据库更新操作中，一定要不忘记最后存在一个commit语句。

4、表的根本操作
4.1、常见的数据类型
、建立表(create)
掌握了常用的数据类型之后，下面就可以通过以下的语法进行表的建立
表名称不能与已有的表名称重复，或者使用数据库中的关键字，但是这些可以通过数据库中的错误检测系统完成
在进行默认值设置的时候，一定要注意，如果是字符串的话使用“'〞括起来，如果是数字不需要任何的“"〞括。

建立一张person表
此时表创立完成之后，就可以向里面增加所需要的数据
4.2、删除表(drop)
删除表的语法使用DROP语句。

删除person表
在DB2数据库之中所有的表结构都是无法修改的，这样一来，可以保证系统的性能极高，而且在一般的开发中，也不建议进行表结构的修改，所以对于表结构的修改，只需要对语法做出了解即可。

表的修改有两种类型增加新的列、修改已有的列。

、修改表(alter)
增加新列的语法
为person表中，增加一个salary的字段，默认值是1500
如果为一个列设置默认值的话，那么已有的数据将全部更新，如果没有设置默认值，那么所有的内容都将为null。

但是，表可以增加列，却不能删除列。

修改已有列的语法
将person表中的sex字段的默认值设置成“男〞
之后，增加一条新的数据，但是并不设置sex的内容
.。