Oracle11g Database SQL 语言基础

Oracle11g Database SQL语言基础 2011.8 By Kevinlin.林少杰 kevinlin.ora@gmail.com

SQL语言简介 • SQL语言(Structured Query Language) 是访问关系型数据库的标准语言。 • SQL语言可以分为五种类型： • 查询(QUERY)-SELECT • 数据处理(DML)- INSERT, UPDATE, DELETE • 数据定义(DDL)- CREATE, ALTER, DROP, RENAME, TRUNCATE • 事务控制(TC) - COMMIT, ROLLBACK, SAVEPOINT • 数据控制(DCL)– GRANT, REVOKE

SQL语句可以被嵌入其他语句编写的程序里进行运行，比如C++程序，也可以使用JDBC将SQL语句写到Java程序里。SQL语句可以被嵌入其他语句编写的程序里进行运行，比如C++程序，也可以使用JDBC将SQL语句写到Java程序里。 • SQL语句也可以使用一些工具运行，与数据库交互，比如Oracle SQL*Plus，Oracle SQL Developer以及其他第三方工具。

查询数据

使用SELECT查询数据 查询表中的若干行数据查询表中的若干列联合不同表中的数据表2 表1

基本SELECT语句 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; • SELECT子句指定要查询的数据列。 • FROM 子句指定对象表。

查询所有列 SELECT * FROM departments;

查询指定列 SELECT department_id, location_id FROM departments;

SELECT语句编写 • 大小写不敏感。 • 可以写成一行或多行。 • 关键字不能缩写或跨行。 • 语句中的子句(clause)通常分行输入。 • 缩进增加可读性。 • 在一般的工具中，SQL语句一般以分号“;”结尾。

SELECT语句中的四则运算 SELECT last_name, salary, 12*(salary+100) FROM employees;

Null值 • Null值表示相应的值未知、不可用、未被分配。 • Null值不同于数字0或空格。 • 所有与Null值相关的四则运行，结果为Null。

定义列别名(Column Alias) • 重命名列名 • 在有四则运算的情况下，可以使列名更具有意义 • 列别名在定义时，紧跟列名，以空格或“as”分隔 • 当列别名的大小写敏感，或者含有空格、特殊字符，需要使用双引号。 SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees; …

连接符操作 • 将列、字符串连接在一起，作为一个新列。 • 连接符为两个竖线：“||” • 最终形成的列为字符表达式。 SELECT last_name||job_id AS "Employees" FROM employees;

原义字符串(Literal Character Strings) • 原义字符指字符、数字，或者日期。 • 日期和字符需要使用单引号封装。 • 每行数据显示一次。 SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees; …

Quote (q) 操作 • 可以选择分隔符。 • 可选择任意方便的分隔符，单字节或多字节，或者下列符号: [ ], { }, ( ), < >。 • 增加可读性与实用性。 SELECT department_name || ' Department' || q'['s Manager Id: ]' || manager_id AS "Department and Manager" FROM departments;

消除重复行 SELECT DISTINCT department_id FROM employees; … • 可以在多个列之前使用DISTINCT修饰词，影响指定的所有列，其结果是这些列组合后的不同值。 SELECT DISTINCT department_id , job_id FROM employees;

DESCRIBE命令 DESCRIBE employees

限制返回结果 • 使用WHERE子句限制返回结果: • WHERE 子句在 FROM子句之后。 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)]; SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

比较运算符(Comparison Operators) 运算符意义 = 等于 > 大于 >= 大于或等于 < 小于 <= 小于或等于 <> 不等于 BETWEEN...AND... 两个值之间(包括限值) IN(set) 符合列表内的值 LIKE 符合字符样式 IS NULL 是null值

使用比较运算符 SELECT last_name, salary FROM employees WHERE salary <= 3000 ;

使用比较运算符 • 使用BETWEEN 操作显示符合范围的值: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ; 下限上限

使用比较运算符 • 使用IN 操作符显示符合列表内值的数据: SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ;

使用比较运算符 • LIKE 操作符筛先符合查找字符串的数据行。 • 查找条件可以包括字符或数字： • “%”表示数字0或者多个字符。 • “_”表示一个字符。 SELECT first_name FROM employees WHERE first_name LIKE 'S%' ;

使用比较运算符 • 可以同时使用 (%, _) 匹配符: • 使用 ESCAPE “\”标识符查找值中含有“%”和“_”字符的数据。 SELECT last_name FROM employees WHERE last_name LIKE '_o%' ; SELECT last_name FROM employees WHERE last_name LIKE '%SA\_%' ESCAPE '\';

使用比较运算符 • 使用IS NULL 操作检验是否有Null值 SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;

逻辑运算符 Operator Meaning AND 如果前后两个条件都为真，则返回TRUE OR 如果前后两个条件有一个为真，则返回TRUE NOT 如果条件为假，则返回TRUE

使用逻辑运算符 • AND 需要前后两个条件为TRUE: SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%MAN%' ;

使用逻辑运算符 • OR 需要前后两个条件有一个为TRUE: SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ;

使用逻辑运算符 • NOT 操作符可以与IN, BETWEEN, LIKE, and NULL配合使用: • ... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP') • ... WHERE salary NOT BETWEEN 10000 AND 15000 • ... WHERE last_name NOT LIKE '%A%‘ • ... WHERE commission_pct IS NOT NULL

运算符的优先级规则 优先级运算符 1 四则运算 2 连接运算符 3 比较运算符 4 IS[NOT]NULL, LIKE, [NOT]IN 5 [NOT]BETWEEN 6 Not equal to 7 NOT • 8 AND • 9 OR 可以使用括号改变默认的优先级别

排序ORDER BY • 排序使用ORDER BY 子句: • ASC: 升序(默认行为) • DESC: 倒序 • 如果不使用ORDERBY ，相同的两次查询返回的结果顺序可以不一样。 • 使用NULLFIRST和NULLLAST指定NULL值在排序中的位置。 • ORDER BY 子句中的每个列都可以单独指定排序顺序。

排序ORDER BY • 默认的排序为升序： • 数字是最小的值在前 (1 to 999). • 日期的值最早的在前(01-JAN-11在01-JAN-12之前) • 字符按字母表顺序(“A” 最前，“Z” 最后). • Null 值在升序时显示在最后面，在降序时显示在最前面 • 可以使用一个不在SELECT列表中的列来排序

替换变量Substitution Variables • 替换变量临时保存数据，使用“&”和“&&”符号 • 替换变量可用于： • WHERE 条件子句 • ORDER BY 子句 • 列表达式 • 表名 • 整个SELECT语句 SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ;

SQL函数SQL functions 输入输出函数参数 1 函数进行运算参数2 结果参数n

SQL函数的两种类型 函数多行函数单行函数每行返回一个结果多行返回一个结果

单行函数Single-Row Functions • 用于处理数据对象 • 接收参数，然后输出一个结果 • 每返回一行数据就要进行运算 • 每行返回一个结果 • 可以修改数据类型 • 可以被嵌套 • 参数可以是一个列或一个表达式 function_name [(arg1, arg2,...)] 字符函数转换函数日期函数数字函数一般函数

字符函数Character Functions 字符函数字符处理函数大小写转换函数 LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE

大小写转换函数Case-Conversion 函数结果 LOWER('SQL Course') sql course UPPER('SQL Course') SQL COURSE INITCAP('SQL Course') Sql Course

字符处理函数 函数结果 CONCAT('Hello', 'World') HelloWorld SUBSTR('HelloWorld',1,5) Hello LENGTH('HelloWorld') 10 INSTR('HelloWorld', 'W') 6 LPAD(salary,10,'*') *****24000 RPAD(salary, 10, '*') 24000***** REPLACE('JACK and JUE','J','BL') BLACK and BLUE TRIM('H' FROM 'HelloWorld') elloWorld

数字函数 函数结果 ROUND(45.926, 2) 45.93 TRUNC(45.926, 2) 45.92 MOD(1600, 300) 100

日期函数 Function Result MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 ADD_MONTHS (‘31-JAN-96',1) ‘29-FEB-96' NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95' LAST_DAY ('01-FEB-95') '28-FEB-95' ROUND(SYSDATE,'MONTH') 01-AUG-03 ROUND(SYSDATE ,'YEAR') 01-JAN-04 TRUNC(SYSDATE ,'MONTH') 01-JUL-03 TRUNC(SYSDATE ,'YEAR') 01-JAN-03

转换函数 数据类型转换隐式转换显式转换

数据类型的隐式转换 … where hire_date > '01-JAN-90' From To VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE … where name_varchar > 2345 From To NUMBER VARCHAR2 or CHAR DATE VARCHAR2 or CHAR 不建议使用隐式转换，可能导致SQL的性能下降

数据类型的显式转换 TO_NUMBER() TO_DATE() 数字类型字符类型日期类型 TO_CHAR() TO_CHAR()

日期转换为字符 SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees; …

数字转换为字符 SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';

字符转换为日期、数字 SQL> select TO_number('6,000.00','99,999.00') from dual; TO_NUMBER('6,000.00','99,999.00') --------------------------------- 6000 SQL> select TO_date('20110818','YYYYMMDD') from dual; TO_DATE('201 ------------ 18-AUG-11

函数嵌套 • 单行函数可以嵌套到任意层。 • 函数的运算顺序是从内到外。 F3(F2(F1(col,arg1),arg2),arg3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3

一般函数 • 下面的函数与NULL值的使用有关:

条件表达式Conditional Expressions • IF-THEN-ELSE的逻辑判断 • 有两种方法： • CASE • DECODE CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])

Oracle11g Database SQL 语言基础

Télécharger la présentation

Plus de contenu associé