oracle mysql 学习_Oracle数据库学习整理【原创】

最新推荐文章于 2021-04-09 01:20:23 发布

weixin_39723655

最新推荐文章于 2021-04-09 01:20:23 发布

阅读量137

点赞数

文章标签： oracle mysql 学习

本文链接：https://blog.csdn.net/weixin_39723655/article/details/114905882

版权

一、select语句

日期和数值型字段可以进行加减乘除

关于NULL

NULL表示不可用，未赋值，不知道，不适用，所以它既不是0，也不是空格。NULL和数值进行四则运算，结果是NULL。

字符串连接符：“||”

去除重复行：“distinct”

二、条件和限制

比较操作符

比较操作符意义

= 等于

> 大于

>= 大于等于

< 小于

<= 小于等于

<> 不等于

BETWEEN ..AND.. 在两个值之间

IN(set) 在一个集合范围内

LIKE 匹配一个字符串样子，可以使用%通配符

使用like进行模糊匹配

可使用% 或者_ 作为通配符：%代表 0个或者多个字符；而 _代表一个单个字符。

如果要是查找有%的记录，使用关键字escape

举例：

select * from t_char where a like '%\%%' escape '\';

逻辑操作符

意义

AND

所有条件都满足，返回TRUE

只要有一个条件满足，返回TRUE

NOT

如果条件是FALSE,返回TRUE

排序：“order by”

asc 升序

desc 降序

三、单行函数

1.大小写转换函数

函数

结果

LOWER('SQL Course')

sql course

UPPER('SQL Course')

SQL COURSE

INITCAP('SQL course')

Sql Course

注意：Oracle数据库中的数据是大小写敏感的

2.字符串操作函数

函数

结果

CONCAT('Hello', 'World')

HelloWorld

SUBSTR('HelloWorld',1,5)

Hello

LENGTH('HelloWorld')

INSTR('HelloWorld', 'W')

LPAD(salary,10,'*')

*****24000

RPAD(salary, 10, '*')

24000*****

TRIM('H' FROM 'HelloWorld')

elloWorld

TRIM(' HelloWorld')

HelloWorld

TRIM('Hello World')

Hello World

3.数字操作函数

函数

结果

ROUND(45.926, 2)

45.93

TRUNC(45.926, 2)

45.92

MOD(1600, 300)

100

4.日期操作函数

函数

结果

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

19.6774194

ADD_MONTHS ('11-JAN-94',6)

11-Jul-94

NEXT_DAY ('01-SEP-95','FRIDAY')

8-Sep-95

NEXT_DAY ('01-SEP-95',1)

3-Sep-95

NEXT_DAY ('1995-09-01',1)

ORA-01861:literal does not match format string

NEXT_DAY (to_date('1995-09-01','YYYY-MM-DD'),1)

3-Sep-95

LAST_DAY('01-FEB-95')

28-Feb-95

ROUND('25-JUL-95','MONTH')

1-Aug-95

ROUND('25-JUL-95' ,'YEAR')

1-Jan-96

TRUNC('25-JUL-95' ,'MONTH')

1-Jul-95

TRUNC('25-JUL-95','YEAR')

1-Jan-95

日期运算操作

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS, sysdate+1 as

tomorrow , hire_date + 8/24

FROM employees

WHERE department_id = 90;

5. Oracle数据类型的隐私转换规则

从

到

VARCHAR2 or CHAR

NUMBER

VARCHAR2 or CHAR

DATE

NUMBER

VARCHAR2

DATE

VARCHAR2

注意：对于表达式比较操作仅可以：

从

到

VARCHAR2 or CHAR

NUMBER

VARCHAR2 or CHAR

DATE

SELECT last_name, TO_CHAR(hire_date, 'fmDD "of" Month YYYY') AS

HIREDATE

FROM employees;

6. 日期到字符串的转换：TO_CHAR(date, 'format_model');

日期格式化元素

意义

YYYY

4位数字表示的年份

YEAR

英文描述的年份

2位数字表示的月份

MONTH

英文描述的月份

MON

三个字母的英文描述月份简称

2位数字表示的日期

DAY

英文描述的星期几

三个字母的英文描述的星期几简称

HH24:MI:SS AM

时分秒的格式化

DDspth

英文描述的月中第几天

格式化关键字，可选

7. 数字到字符串的转换TO_CHAR(number, 'format_model');

数字格式化元素

意义

表示一个数字

强制显示0

放一个美元占位符

使用浮点本地币种符号

显示一个小数点占位符

显示一个千分位占位符

SELECT TO_CHAR(salary, 'L99,999.00') SALARY FROM employees

WHERE last_name = 'Ernst';

8. 字符串到数字和日期的转换

TO_NUMBER(char[, 'format_model']);

TO_DATE(char[, 'format_model']);

9. 其它单行函数

函数

用途

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

SELECT last_name, salary, NVL(commission_pct, 0),

(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL

FROM employees;

10. 条件表达式

CASE语句：

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;

DECODE语句：

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;

四、多表关联查询

不同的数据库厂商对链接类型有不同的定义，但国际上有个凌驾于各厂商的工业标准定义(SQL 1999), 我们先来看Oracle定义的链接类型：

1、等于链接

2、不等链接

3、外连接(可细分为左外连接、右外连接)

4、自链接

1.等于连接

语法：

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

举例：

SELECT employees.employee_id, employees.last_name,

employees.department_id,

departments.department_id,departments.location_id

FROM employees, departments

WHERE employees.department_id = departments.department_id;

2. 不等于连接

“不等链接” 语法：使用不等链接符，包括> , < , !=, between

语法：

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 > table2.column2;

举例：

SELECT e.last_name, e.salary, j.grade_level

FROM employees e, job_grades j

WHERE e.salary

BETWEEN j.lowest_sal AND j.highest_sal;

3.外连接

外连接包括左外连接和右外连接

举例：

SELECT e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id;

记忆方式：

哪边没加好，就是那种外连接。比如：加号在右面，就叫左外连接，此时右面对应的空数据也会查询出来

4.自连接

“自链接” ：其实是一种概念，某个table和自己本身链接，比如：table1给另一个“自己”起别名为table2

举例：

SELECT worker.last_name || ' works for ' || manager.last_name

FROM employees worker, employees manager

WHERE worker.manager_id = manager.employee_id ;

五、分组计算函数和“GROUP BY”字句

常用的分组计算函数包括：

作用

关键字

求和

(SUM)

求平均值

(AVG)

计数

(COUNT)

求标准差

(STDDEV)

求方差

(VARIANCE)

求最大值

(MAX)

求最小值

(MIN)

count()的用法

函数用法

意义

COUNT(*)

返回满足选择条件的所有行的行数，包括值为空的行和重复的行

COUNT(expr)

返回满足选择条件的且表达式不为空行数

COUNT(DISTINCT expr)

返回满足选择条件的且表达式不为空，且不重复的行数

注意：

SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By子句中，否则不合法。

不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。

分组计算函数可以嵌套使用

六、子查询

语法：

SELECT select_list

FROM table

WHERE expr operator

(SELECT select_list

FROM table);

注意点：

单行比较必须对应单行子查询(返回单一结果值的查询)；比如= ， >

多行比较必须对应多行子查询(返回一个数据集合的查询)；比如 IN , > ANY, > ALL 等

七、DML语句

insert语句

形式一：

不允许为空的列，必须写出来。

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

形式二：仅写出列名，这种形式必须显式的给出所有列的数据。

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

形式三：从另一个表中copy

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, department_id

FROM employees

WHERE department_id = 50)

VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'),

'ST_CLERK', 5000, 50);

update语句

UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];

注意：存在约束条件的时候，可能会更新失败

delete语句

DELETE [FROM] table

[WHERE condition];

删除所有：delete删除语句没有where条件时，意为删除所有数据，但是表依然存在，删除表实体使用drop。

注意：当存在约束时，有可能会删除失败。

meger比较整合语句

举例：

MERGE INTO copy_emp c

USING employees e

ON (c.employee_id = e.employee_id)

WHEN MATCHED THEN

UPDATE SET

c.first_name = e.first_name,

c.last_name = e.last_name,

...

c.department_id = e.department_id

WHEN NOT MATCHED THEN

INSERT VALUES(e.employee_id, e.first_name, e.last_name,

e.email, e.phone_number, e.hire_date, e.job_id,

e.salary, e.commission_pct, e.manager_id,

e.department_id);

七、事务控制

隐式的事务提交或者回滚：

Commit, rollback 是显式的提交和回滚语句，还有一些隐式的提交和回滚是大家需要知道并引起注意的：

当如下事件发生是，会隐式的执行Commit动作：

1、数据定义语句被执行的时候，比如新建一张表：Create Table …

2、数据控制语句被执行的时候，比如赋权 GRANT …( 或者 DENY)

3、正常退出 iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行 COMMIT 或者 ROLLBACK 语句。

当如下事件发生时，会隐式执行Rollback 动作：

九、数据库对象-表

表的命名要求和表中列的命名要求：

1、必须以字母开头

2、长度不能超过30个字符

3、只能包含 A–Z, a–z, 0–9, _, $, and #

4、不能与数据库中的已有对象重名

5、不能使用Oracle 数据库的保留字

语法：

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][, ...]);

中间部分省略

十四、GROUP BY 增强

GROUP BY 中使用Rollup产生常规分组汇总行以及分组小计

SELECT department_id, job_id, SUM(salary)

FROM employees

WHERE department_id < 60

GROUP BY ROLLUP(department_id, job_id);

这样会产生三维的统计数据：department_id, job_id为一个维度，department_id为一个维度，job_id为一个维度。

GROUP BY 中使用cube产生Rollup结果集+多维度交叉表数据来源

SELECT department_id, job_id, SUM(salary)

FROM employees

WHERE department_id < 60

GROUP BY CUBE (department_id, job_id) ;

这样就会有四个维度，相当于再反向的rollup一次

GROUPING函数

关键字： grouping(column) rename

举例：

SELECT department_id DEPTID, job_id JOB,

SUM(salary),

GROUPING(department_id) GRP_DEPT,--用了哪个维度，哪个维度就显示为0，否则为1

GROUPING(job_id) GRP_JOB

FROM employees

WHERE department_id < 50

GROUP BY ROLLUP(department_id, job_id);

GROUPING STES 来代替多次的union

举例：

SELECT department_id, job_id,

manager_id,avg(salary)

FROM employees

GROUP BY GROUPING SETS

((department_id,job_id), (job_id,manager_id));

此时，就会按照(department_id,job_id)和 (job_id,manager_id)两个组合来进行分组汇总，最后再union。

十五、子查询进阶

非相关子查询，即把子查询的结果当做一张表来使用

举例：

SELECT a.last_name, a.salary,

a.department_id, b.salavg

FROM employees a, (SELECT department_id,

AVG(salary) salavg

FROM employees

GROUP BY department_id) b

WHERE a.department_id = b.department_id

AND a.salary > b.salavg;

相关子查询，即子查询中参考了外部主查询的中的表

举例：

SELECT last_name, salary, department_id

FROM employees outer

WHERE salary > (SELECT AVG(salary)

FROM employees

WHERE department_id =

outer.department_id) ;

使用Exists、not exists操作

使用in、not in操作

注意：Not In 里面只要有一个NULL ，就不成立了，这是很容易出错的地方；正确的方法请在后面的子查询中加上where department_id is not null;

weixin_39723655

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle mysql 学习_Oracle数据库学习整理【原创】

一、select语句日期和数值型字段可以进行加减乘除关于NULLNULL表示不可用，未赋值，不知道，不适用，所以它既不是0，也不是空格。NULL和数值进行四则运算，结果是NULL。字符串连接符：“||”去除重复行：“distinct”二、条件和限制比较操作符比较操作符意义= 等于> 大于>= 大于等于< 小于...
复制链接

扫一扫