1、SQL语言概述
SQL:Structured Query Language,结构化查询语言。
1.1、SQL语言特点
SQL不是Oracle的专利技术,它是关系数据库管理系统的标准语言,是应用系统与数据库进行交互操作的接口。
SQL语言特点如下:
- SQL语言采用集合操作方式,对数据的处理是成组进行的,而不是一条一条处理。通过使用集合操作方式,可以加快数据的处理速度。
- 执行SQL语句时,每次只能发送并处理一条语句。如果要降低语句发送和处理次数,可以使用PL/SQL。
- 执行SQL语句时,用户只需要知道其逻辑含义,而不需要关心SQL语句的具体执行步骤。
- 使用SQL语句时,即可以采用交互方式执行(例如SQL*Plus),也可以将SQL语句嵌入到高级语言中执行。
1.2、SQL语言分类
SQL语言可以分为5类:查询语句、数据操纵语言、数据定义语言、事务控制和数据控制语言。
- 查询语句:用于检索数据库表中存储的行。
- 数据库操纵语言(DML):用于修改表的内容。
- INSERT 向表中添加行。
- UPDATE 修改行的内容。
- DELETE 删除行。
- MERGE 合并(插入或修改)。
- 数据库定义语言(DDL):用于定义构成数据库的数据结构。
- CREATE 创建数据库结构。
- ALTER 修改数据库结构。
- DROP 删除数据库结构。
- RENAME 更改表名。
- TRUNCATE 删除表的全部内容。
- 事务控制(TC):用于将对行所做的修改永久性地存储到表中,或者取消这些修改操作。
- COMMIT 永久性地保存对行所做的修改。
- ROLLBACK 取消对行所做的修改。
- SAVEPOINT 设置一个“保存点”,可以将对行的修改回滚到此处。
- 数据控制语言(DCL):用于修改数据库结构的操作权限。
- GRANT 授予其他用户对数据库结构的访问权限。
- REVOKE 收回用户访问数据库结构的权限。
1.3、SQL语言的编写规则
- SQL关键字不区分大小写,既可以使用大写格式,也可以使用小写格式,或者混用大小写格式。
- 对象名和列名不区分大小写,它们既可以使用大写格式,也可以使用小写格式,也可以使用小写格式,或者混用大小写格式。
- 字符值和日期值区分大小写。(当在SQL语句中引用字符值和日期值时,必须要给出正确的大小写数据,否则不能返回正确信息)。
- 在应用程序中编写SQL语句时,如果SQL语句文本很短,可以将语句文本放在一行上;如果SQL语句文本很长,可以将语句文本分布到多行上,并且可以通过使用跳格和缩进提高可读性。另外,在SQL*Plus中SQL语句要以分号结束。
2、使用SELECT检索数据
2.1、使用FROM子句指定表
SELECT * FROM table_name;
- SELECT 用于指定要检索的列。
- * 表示检索所有项。
- FROM 用于指定要检索的表。
- table_name 指定要检索的表名。
2.2、使用SELECT(必要元素)指定列
在SELECT 关键字后指定列名,检索多个列的数据,在列之间使用逗号(,)隔开。
2.3、使用WHERE子句指定行
SELECT <column1,column2,...> FROM table_name
WHERE expression;
- expression 表示条件语句。
- WHERE 用于指定条件子句,如果条件子句返回值为TRUE,则会检索对应的数据,如果条件为FALSE,则不会检索该行数据。
比较操作符 | 含义 | 比较操作符 | 含义 |
= | 等于 | < | 小于 |
<>,!= | 不等于 | BETWEEN...AND... | 在两值之间 |
>= | 大于等于 | IN | 匹配于列表值 |
<= | 小于等于 | LIKE | 匹配于字符样式 |
> | 大于 | ISNULL | 测试NULL |
- 在WHERE条件中使用数字值:当在WHERE条件中使用数字值时,既可以使用单引号引住数字值,也可以直接引用数字值。
- 在WHERE条件中使用字符值:当在WHERE条件中使用字符值,必须用单引号引住。(因为字符值区分大小写,所以在引用字符值时必须要指定正确的大小写格式,否则不能正确显示输出信息)。
- 在WHERE条件中使用日期值:当在WHERE条件中使用日期值时,必须要用单引号引住,并且日期值必须要符合日期显示格式。如果日期值不符合默认日期显示格式,那么必须使用TO_DATE()函数进行转换。如下:
select column1,column2,... from table_name where hiredate > to_date('01-1月-02','dd-mon-yy');
- 在WHERE条件中使用BETWEEN...AND..操作符:BETWEEN...AND..操作符用于指定特定范围条件,在BETWEEN操作符后指定较小的一个值,在AND操作符后指定较大的一个值。
select column1,column2,...from table_name where column3 between 1000 and 2000;
- 在WHERE条件中使用LIKE操作符:LIKE操作符用于执行模糊查询。(当执行查询操作时,如果不能完全确定某些信息的查询条件,但是这些信息又具有某些特征,那么可以使用模糊查询。)当执行模糊查询时,需要使用通配符“%”和“_”,其中“%”用于表示0个或多个字符,而“_”则用于表示单个字符。如下:
select column1,column2,.. from table_name where column3 like 'S%';
select column1,column2,... from table_name where column3 like '_A%';
- 在WHERE条件中使用IN操作符:IN操作符用于执行列表匹配操作。如下:
select column1,column2,... from table_name where column3 in (100,200);
- 在WHERE子句中使用逻辑操作符:当执行查询操作时,许多情况下需要指定多个查询条件。当使用多个查询条件时,必须要使用逻辑操作符AND、OR或NOT,
逻辑操作符及其含义 逻辑操作符 含义 AND 如果条件都是TRUE,则返回TRUE,否则返回FALSE OR 如果人一个条件时TRUE,则返回TRUE,否则返回FALSE NOT 如果条件是FALSE,则返回TRUE;如果条件是TRUE,则返回FALSE (逻辑操作符AND、OR、NOT的优先级低于任一种比较操作符,在这3个操作符中,NOT优先级最高,AND其次,OR最低。如果要改变优先级,则需要使用括号)。如下:
select column1,column2,... from table_name where column3 = 20 and column4 = 'TOM';
select column1,column2,... from table_name where column3 = 20 or column4 = 'TOM';
select column1,column2,... from table_name where column3 not in (20,30);
2.4、使用ORDER BY 子句对行进行排序
在执行查询操作时,默认情况下会按照数据插入的先后顺序来显示行数据。但是在实际应用中经常需要对数据进行排序,以显示更直观的数据,数据排序是使用ORDER BY 子句来完成,其语法如下:
SELECT <column1,column2,...> FROM table_name WHERE expression ORDER BY column1[,column2,...][ASC | DESC];
- ORDER BY COLUMN 表示按列名进行排序。
- ASC 指定按照升序排列,这是默认的排列顺序。
- DESC 指定按照降序排列。
- 升序排序:默认情况下,但是用ORDER BY 执行排序操作时,数据以升序方式排序,也可以使用ASC关键字。如下:
select column1,column2,... from table_name where column3 = 30 order by column4;
- 降序排序:降序排列使用关键字DESC。如下:
select coulumn1,column2,...from table_name where column3 = 30 order by column4 desc;
- 使用多列排序:但是用ORDER BY 子句执行排序操作时,不仅可以基于单个列或单个表达式进行排序,也可以基于多个列或多个表达式进行排序。当以多个列或多个表达式进行排序时,首先按照第一个或表达式进行排序,当第一个列或表达式存在相同数据时,然后以第二个列或表达式进行排序。如下:
select column1,column2,... from table_name where column3 = 30 order by column4,column5 desc;
(如果在SELECT语句中同时包含有ORDER BY 、HAVING 以及 GROUP GY子句,则必须将ORDER BY子句放在最后。)
2.5、使用GROUP BY 子句进行分组
GROUP BY 子句用于对查询结果进行分组统计。GROUP BY 语法如下:
SELECT <column1,column2,...> FROM table_name GROUP BY column3[,column4,...];
- 使用GROUP BY 进行单列分组:单列分组是指在GROUP BY 子句中使用单个列生成分组统计结果。当进行单列分组时,会基于列的每个不同值生成一个数据统计结果。如下:
select column1,column2,... from table_name group by column1;
- 使用GROUP BY 进行多列分组:多列分组是指在GROUP BY 子句中使用两个或两个以上的列生成分组统计结果。当进行多列分组时,会基于多个列的不同值生成数据统计结果。如下:
select column1,column2,... from table_name group by column1,column2 order by column1;
2.6、使用HAVING子句过滤行组
HAVING子句用于限制分组统计结果,并且HAVING子句必须跟在GROUP BY子句后面。如下:
select column1,column2,column3,... from table_name group by column1 having column2 > 30 ;
2.7、组合使用WHERE、GROUP BY 和HAVING子句
WHERE、GROUP BY 和HAVING子句可以在同一个查询中一起使用。这样使用时,WHERE子句首先对返回行进行过滤,然后GROUP BY 子句对保留的行进行分组,最后HAVING子句对行进行过滤。如下:
select column1,column2,column3,... from table_name where column1 > 10 group by column2 having column3 >20;
2.8、使用DISTINCT来检索唯一的表列值
DISTINCT关键字可以取消完全重复的显示结果。(默认使用ALL关键字)
select distinct column from table_name ;
2.9、使用算术运算符
当执行查询操作时,可以在数字列上使用算术表达式(+、-、*、/),其中乘、除的优先级要高于加、减。如果要改变优先级,可以使用括号。如下:
select column1,column2*(1+0.2) from table_name where column3 > 10;
3、使用DML语句
3.1、使用INSERT语句插入表数据
INSERT语句一般有两种:
INSERT INTO table_name [column1,column2,...] VALUES (value1,vslue2,...);
INSERT INTO table_name1 [column1,column2,...] SELECT (value1,vslue2,...) FROM table_name2;
- INSERT INTO 指明要插入的表以及表中的字段。
- VALUES 指明要插入相应字段值。
(第一条INSERT 语句用于向table_name表中插入单条记录,第二条INSERT语句用于把从table_name2表中查询出来的数据插入到表table_name1中,实现多条记录的插入。)
注:无论是插入单条记录,还是插入多条记录,都应该注意要插入的值与要插入的字段相互对应。
3.2、使用UPDATE语句更新表数据
UPDATE语句的语法:
UPDATE table_name SET column1=value1 [,column2=value2,column3=value3,...] WHERE espression;
- table_name 指定要更新的表。
- SET 指定要更新的字段及其对应的值。
- expression 表示更新条件。
- WHERE 指定更新条件,如果没有指定更新条件,则对表中所有数据记录进行更新。
3.3、使用DELETE语句删除表数据
DELETE语句的语法:
DELETE FROM table_name [WHERE expression];
- FROM 指定要删除数据的表。
- WHERE 指定要删除数据的条件。如果没有WHERE子句,则删除表中的所有记录。
(使用DELETE语句删除表中数据时,并不能释放被占用的数据块空间,它只是把那些被删除的数据块标记为Unused,将来还可以使用回退(Rollback)操作。)
3.4、使用MERGE语句修改表数据
MERGE常使用场景为,先查找是否存在旧数据,如果存在,则用UPDATE替换,否则用INSERT语句插入。MERGE语句的语法如下:
MERGE INTO table1 USING table2 ON expression
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHEN THEN INSERT ...;
- UPDATE或INSERT 子句是可选的,UPDATE和INSERT子句可以加WHERE子句。
- 在ON条件中使用常量过滤谓词将所有的行插入到目标表中,不需要连接源表和目标表。
- UPDATE子句后面可以跟DELETE子句来删除一些不需要的行。
4、表的连接查询
连接查询是指基于两个或两个以上表或试图的查询。
4.1、简单连接
- 自然连接:是指使用相等比较符(=)指定连接条件的连接查询,这个连接查询主要用于检索主从表之间的相关数据。
SELECT table1.column1,table2.column2 from table1,table2 WHERE table1.column3 = table2.column4;
- 表别名:就是为每一个表定义一个标识,容易识别。
SELECT t1.column1,t2.column2 from table1 t1,table2 t2 WHERE t1.column3 = 2.column4;
- 列别名:当在SQL*Plus中执行查询操作时,首先会显示列标题,然后才会显示数据。默认情况下,列标题是大写格式的列名或表达式。通过使用列别名,可以改变列标题的显示样式。
SELECT table1.column1 as "工号",table2.column2 as "姓名" from table1,table2 WHERE table1.column3 = table2.column4;
4.2、内连接
内连接用于返回满足连接条件的记录。如下:
SELECT table1.column1,table2.column2 from table1 [INNER] JOIN table2 ON table1.column3 = table2.column4;
- INNER JOIN 表示内连接。
- ON 用于指定连接条件。
(默认情况下,在执行连接查询时如果没有指定任何连接操作符,那么这些连接查询都属于内连接。)
4.3、外连接
外链接是内连接的扩展,它不仅会返回满足连接条件的所有记录,而且还会返回不满足条件的记录。语法如下:
SELECT table1.column1,table2.column2 from table1 [LEFT | RIGHT] OUTER JOIN table2 ON table1.column3 = table2.column4;
(在连接条件中可以使用外链接操作符来执行一个外连接;Oracle特有的外连接操作符是:(+)。)
- 左连接:左连接不仅会返回连接表中满足连接条件的所有记录,而且还会返回不满足连接条件的连接操作符左边表的其他行。
SELECT table1.column1,table2.column2 from table1,table2 WHERE table1.column3 = table2.column4(+);
- 右连接:右连接不仅会返回连接表中满足连接条件的所有记录,而且还会返回不满足连接条件的连接操作符右边表的其他行。
SELECT table1.column1,table2.column2 from table1,table2 WHERE table1.column3(+) = table2.column4;
4.4、自连接
自连接是指在同一张表之间的连接查询,主要用于显示上下级关系或者层次关系。如下:
select column1,column2,... from table_name;
5、数据库事务
事务是由一系列相关的SQL语句组成的最小逻辑工作单元。Oracle系统以事务为单位来处理数据,用以保证数据的一致性。对于事务中的每一个操作要么全部完成,要么全部不执行。
5.1、事务的提交和回滚
要永久性地记录事务中SQL语句的结果,需要执行COMMIT语句,从而提交事务。要取消SQL语句的结果,需要执行ROLLBACK语句,从而回滚事务,将所有行重新设置为原始状态。
5.2、事务的开始于结束
事务开始:
- 连接到数据库上,并执行一条DML语句(INSERT、UPDATE或DELETE)。
- 前一个事务结束后,又输入另外一条DML语句。
事务结束:
- 执行COMMIT或ROLLBACK语句。
- 执行一条DDL语句,如CREATE TABLE语句。在这种情况下,会自动执行COMMIT语句。
- 断开与数据库的连接,在退出SQL*Plus时,通常会输入EXIT命令,此时会自动执行COMMIT语句。(如果SQL*Plus被意外终止了,例如运行SQL*Plus的计算机崩溃了,那么就会自动执行ROLLBACK语句。)
- 执行了一条DML语句,该语句却失败了。在中情况下,会为这个无效的DML语句执行ROLLBACK语句。
(事务可以是一组SQL命令,也可以是一条SQL语句,这些SQL语句只能是DML语句,而其他SQL语句,例如DDL语句和DCL语句等,一旦执行就立即提交给数据库,不能回滚。)
5.3、事务的ACID特性
- 原子性(Atomic) 事务是原子的,这就是说一个事务中包含的所有SQL语句都是一个不可分割的工作单元。
- 一致性(Consist) 事务必须确保数据库的状态保持一致,这就是说事务开始时,数据库的状态是一致的;在事务结束时,数据库的状态也必须是一致的。
- 隔离性(Isolated) 多个事务可以独立运行,而不会彼此产生影响。
- 持久性(Durable) 一旦事务被提交之后,数据库的变化就会被永久保留下来,即使运行数据库软件的机器后来崩溃也是如此。
5.4、并发事务
数据库软件支持多个用户同时与数据库进行交互,每个用户都可以同时运行自己的事务,这种事务就称为并发事务。
如果用户同时运行多个事务,而这些事务都对同一个表产生影响,那么这些事务的影响都是独立的,直到执行一条COMMIT语句时才会彼此产生影响。