SQL基础


select语句
基础部分
  • 字符串连接操作符: “||”
SELECT last_name||job_id AS "Employees" FROM employees; SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;
  • DISTINCT 去除重复行:
SELECT DISTINCT department_id FROM employees; //使用DISTINCT

  • 比较操作符:

  • 排序
ASC :升序 (默认)
DESC :倒序
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ;
单行函数
  • 字符串操作函数:
SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';
  • 数字操作函数
  • 日期操作函数:
  • Oracle 数据类型的 显式转换函数:
  • TO_CHAR() 函数:日期到字符串的转换
TO_CHAR(date, 'format_model') ;

例如:
SELECT last_name, TO_CHAR(hire_date, 'fmDD "of" Month YYYY') AS HIREDATE FROM employees;
结果:
  • NVL函数 NVL(string1, string2)

NVL (expr1, expr2)
如果expr1为空,则返回expr2
NVL2 (expr1, expr2, expr3)
如果expr1为空,则返回expr3(第2个结果)否则返回expr2
NULLIF (expr1, expr2)
如果expr1和expr2相等,则返回空
COALESCE (expr1, expr2, ..., exprn)
如果expr1不为空,则返回expr1,结束;否则计算expr2,直到找到一个不为NULL的值 或者如果全部为NULL,也只能返回NULL了
  • 条件表达式:
实现方法: CASE 语句 或者DECODE 函数,两者均可实现 IF-THEN-ELSE 的逻辑,相比较
而言,DECODE 更加简洁。
举例:
SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1.10*salary WHEN 'ST_CLERK' THEN 1.15*salary WHEN 'SA_REP' THEN 1.20*salary ELSE salary END "REVISED_SALARY" FROM employees;
SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees;
多表关联
由于在实际开发中很少使用,笔记跳过
分组函数和GROUP BY
  • 分组计算 函数( 常用) :包括
1 、求和 (SUM)
2 、求平均值(AVG)
3 、计数(COUNT )
4 、求标准差(STDDEV)
5 、求方差(VARIANCE)
6 、求最大值(MAX)
7 、求最小值(MIN)
  • 分组计算函数使用语法:
SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column]; //order by 一定是放在最后!!
count()用法说明
函数用法
意义
COUNT(*)
返回满足选择条件的所有行的行数,包括值为空的行和重复的行
COUNT(expr)
返回满足选择条件的且表达式不为空行数。
COUNT(DISTINCT expr)
返回满足选择条件的且表达式不为空,且不重复的行数。
avg()的用法
问题
当分组计算函数遇到NULL:
Employees 表中存在多条记录,其commission_pct 的值为NULL, 那么下面的Sql 语句等价于A 还是B ?
SELECT AVG(commission_pct) FROM employees;
A: select (select sum(commission_pct) from employees)/(select count(*) from employees ) from dual B: select (select sum(commission_pct) from employees)/(select count(commission_pct) from employees ) from dual
答案选B
GROUP BY 语句
注意 SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ,其他字段必须出现在Group By 子句中,否则不合法。
错误:
SELECT department_id, COUNT(last_name) FROM employees;
正确:
SELECT department_id, count(last_name) FROM employees GROUP BY department_id;
注意 不能在Where 条件中使用分组计算函数表达式,当出现这样的需求的时候,使用Having 子句。
例如:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;
  • 分组计算函数也可嵌套使用。
比如下面的例子可获取最高的部门平均薪水:
SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;
子查询
  • 单行比较必须对应单行子查询(返回单一结果值的查询); 比如= , >
  • 多行比较必须对应多行子查询(返回一个数据集合的查询);比如 IN , > ANY, > ALL
例如:
SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); //正确写法,将此行删去
ERROR at line 4:
ORA-01427: single-row subquery returns more than
one row
DML语句
  • DML: Data Manipulation Language , 数据操纵语言;简单的说就是SQL 中的增、删、改 等语句。
  1. insert语句
  • 方式一:写出表名+ 列名
注意:在“列”中,对于不允许为NULL 的列,必须写出来;对于允许为NULL 的列,可以不写出来在Value 中,对于列中未写出来的列,默认赋予NULL
例如:
INSERT INTO departments (department_id, department_name ) VALUES (30, 'Purchasing' );
  • 方式二:仅写出表名(不推荐使用,因为表结构一旦修改,SQL就会报错
注意:在Value 中必须对应写出每个列的值,即是是允许NULL 的字段,也必须显式的给出 NULL
  • 方式三:从另一个表中 Copy 一行
语法:INSERT INTO table [ column (, column) ] subquery;
注意:在这种方式下,不要使用VALUES 关键字
例如:
INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';
  • 方式四:使用子查询作为插入目
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION) VALUES (99998, 'Smith', 'JSMITH', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
注意:WITH CHECK OPTION 可以检查要插入的内容,是否符合目标子查询的Where 条件
  1. update语句
UPDATE table SET column = value [, column = value, ...] [WHERE condition];
注意:当存在约束的时候,某些更新可能会失败
  1. delete语句
DELETE [FROM] table [WHERE condition];
  • truncate 一次性清空表中数据,保留表结构
如果遇到这种需求,也可以使用TRUNCATE 语句, ,TRUNCATE TABLE copy_emp ,
但要注意,TRUNCATE 语句无法回滚,因此除非是单独执行,并非常确认,否则慎用。

锁有既有隐式的,也有显式的; 但某用户对某一批数据进行更改,而未提交之前,Oracle 会隐式的进行加锁;
当然用户也可以显式的加锁,比如: Select … from TableA Where … For UPDATE NoWait
  • For UPDATE NoWait:若有锁,直接报错,不再等待

由于锁的知识比较复杂,后续学习完在进行补充。[嘘]

曹永达

发表评论 取消回复