007 SG SQL中的注意02

最新推荐文章于 2023-10-16 19:45:00 发布

moveofgod

最新推荐文章于 2023-10-16 19:45:00 发布

阅读量926

点赞数

分类专栏： Oracle 文章标签： sql table timezone insert subquery

本文链接：https://blog.csdn.net/moveofgod/article/details/7216628

版权

Oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

1.subquery

You can place the subquery in a number of SQL clauses , including : The WHERE clause , The FROM clause , the HAVING clause .

SELECT job_id,AVG( salary )
FROM employees
GROUP BY job_id
HAVING AVG( salary ) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id )

A common problem with subquery is that no rows are returned by the inner query .

返回单值子查询比较符号 ( = , > , < , <> 等等 )

返回多值子查询比较符号( in , Any , All 等等 )

Any ( 同意词 some ) : <ANY means less than the maximum , >ANY means more than the minimum =ANY means IN

<ALL means less than the minimum , >ALL means more than the maximum

这里的 ALL, ANY 是很有用的，因为有时候就用到了，比如看一下，前天的计划比今天数量多的情况，那么就可以使用

SELECT * FROM ZHPPEXP05
WHERE WORK_DATE = '20130204'
AND PLANGIQTY1 >ALL (		SELECT PLANGIQTY1 FROM ZHPPEXP05
			   	WHERE WORK_DATE = '20130203'
		)

注意：如果没有这个功能，那么就只能使用之前的方法了( IN, 或者 > )。那就是

SELECT * FROM ZHPPEXP05
WHERE WORK_DATE = '20130204'
AND PLANGIQTY1 >  (		SELECT MAX(PLANGIQTY1) FROM ZHPPEXP05
			   	WHERE WORK_DATE = '20130203'
		)

2.Insert ( NULL value )

a. INSERT INTO departments VALUES( 100, 'Finance' , NULL , NULL )

b. 直接省略,则未被指定的column , 默认为NULL

c. ‘’ 可以用于 String 和 date 类型的 NULL

Copy rows from another tables ( Use subquery )

INSERT INTO sales_raps(id,name,salary)
SELECT id, name, salary FROM employees --> Subquery
WHERE job_id LIKE '%REP%'

3.WITH CHECK OPTION

Specify WITH CHECK OPTION to indicate that , if the subquery is used in place of a table in an INSERT , UPDATE , DELETE statement , no changes

that would produce rows that are not included in the subquery are permitted to that table .

当子查询充当table在 INSERT , UPDATE , DELETE 中出现时, 为什么使用子查询而不直接使用 table , 因为使用子查询时可以使用 WITH CHECK OPTION

进行判断, 例如下边的例子 :

INSERT INTO ( SELECT employee_id,last_name
FROM employees
WHERE department_id = 50 WITH CHECK OPTION )
VALUES( 9998 , 'JOIN')

MESSAGE: ERROR , 因为插入数据并未指定 department_id , 而 WITH CHECK OPTION 要求 department_id 为 50 , 所以错误 .

4. Defalut 可以使用在 INSERT, UPDATE

DEFALUT 可以直接使用在 Insert , update 语句中 , 如果使用DEFAULT的COLUMN没有定义 default , 系统会使用 NULL 代替 .

5.Merge

You can conditionally insert or update rows in a table by using the MERGE statement . (一般是从一个table | view | subquery 复制到另一个table使用)

MERGE INTO copy_emp as 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.department_id)

6.Database Transactions

A database transaction consists of one of the following :

DML statements which constitute one consistent change to the data , DDL , DCL

If you create a second savepoint with the same name as an earlier savepoint , the earlier savepoint is deleted .

自动 commit : DCL , DDL , 正常退出 SQL / PLUS

自动 rollback : 非正常退出 SQL/PLUS. ( exit命令属于正常退出 , 关闭窗口属于非正常退出 )

Statement-Level Rollbacks

当执行一个 transaction , 由于一个 transaction是由多个DML等组合在一起的, 当这些DML中的一个出现错误时,这个错误的之前的DML已经得到执行,

当前用户( 其他用户不可以 ) 可以查看到之前的DML改变database的结果 , 此时可以用户可以 commit 或者 rollback . 但是当用户执行的 transaction中含有

DDL时,由于DDL是自动commit的,所以这个错误之前的DDL也已经得到执行,并且已经commit .

读取数据一致性

一个用户改变数据不会影响其他用户改变数据. ( 同一个数据 , 谁先 commit 谁说的算 )

The purpose of read consistency is to ensure that each user sees data as it existed at the last commit , before a DML operation started .

数据状态是 , 硬盘database data block保存一份 , 还有 undo segment也保存一份 , 当一个用户改变数据时 , 他可以读data block中的一份,

在他没 commit 之前 , 只有他本人可以看到这个数据 , 其他用户查看的是 undo segment中的数据 , 当他 commit 之后 , 其他用户才可以看到

data block中的内容, database 并将 undo segment 清空 , 而当用户 rollback 时 , The original , older version , of the data in the undo segment is

written back to the table . All user can see this existed before the transaction began .

DDL 也要加 LOCK , 因为有时对 table 等 object 操作时 , 会增加 LOCK , 防止此 object 被同时另外修改 .

7.schema

schema is collection of objects . schema is the same as the owner's name . Schema objects include tables , views , synonyms , sequences ,

stored procedures , indexes , clusters , and database links .

for example SELECT * FROM user.employees ( user is the schema name )

逻辑结构 : datebase( sid ) -> schema -> object ( 例如 table 等等)

8.data dictionary

All data dictionary tables are owned by the SYS user .

There are four categories of data dictionary views

Prefix	Description
USER_	These views contain information about objects owned by the user .
ALL_	These views contain information about all of the tables ( objec tables and relational tables ) accessible to the user .
DBA_	These views are restricted views , which can be accessed only by people who have been assigned the DBA role .
V$	These views are dynamic performance views , database server performance , memory , and locking .

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
时间类型的关键性总结

1. date: 只是存储普通日期和时间

2. timestamp: 在 date类型的基础上更加精细到纳秒级别 + 6位 ( UTC 标准 )

3. timestamp with timezone: 存储与 UTC 标准的差异，例如: 05-JUN-04 07.03.46.926 PM -07:00

4. timestamp with local time zone: 返回的时间是访问数据库服务器的客户端的本地时间，例如，

如果我在丹佛，美国东时区 17:00时，访问了纽约的一个数据库服务器，那么返回的值是 15:00, 这与我们客户机上设置的时区相匹配。

个人理解为：其实数据库timezone只是一个计算的标尺，TIMESTAMP WITH LOCAL TIME ZONE数据类型从客户端传入数据库后，转为数据库时区存入数据库。在需要进行相关计算的时候，Oracle先把时间转换为标准时间(UTC)，完成计算后再把结果转换为数据库时区的时间保存到数据库。所以这种情况，最好的办法就是将数据库的TIMEZONG设置成为 UTC。

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

9.TIMESTAMP类型

Timestamp类型是指在传统的 date类型下 (最小单位是秒) , 更加精确 , 可以指定精确秒的位数 , 例如 TIMESTAMP(6) 精确到秒后6位 .

Timestamp WITH TIME ZONE For example : '1999-04-15 8:00:00-8:00' = '1999-04-15 11:00:00-5:00'

That is , 8:00 am Pacific Standard Time is the same as 11:00 am Eastern Standard Time .

Timestamp WITH LOCAL TIME ZONE

Timestamp主要是应对国际化的要求

1.Oracle中的时区主要分为两大类 : 数据库时区和 SESSION时区

SELECT DBTIMEZONE FROM DUAL -->数据库时区

SELECT SESSIONTIMEZONE FROM DUAL -->SESSION时区

数据库时区和 SESSION 时区可以修改 .

Note:Database TimeZone只和TIMESTAMP WITH LOCAL TIMEZONE数据类型相关！其实数据库timezone只是一个计算的标尺，TIMESTAMPWITH LOCAL TIMEZONE数据类型从客户端传入数据库后，转为数据库时区存入数据库。在需要进行相关计算的时候，Oracle先把时间转换为标准时间(UTC)，完成计算后再把结果转换为数据库时区的时间保存到数据库。

2. 时区相关的数据类型

和时区相关的数据类型主要有：DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE和TIMESTAMPWITH LOCAL TIME ZONE。粗略介绍如下：

DATE：存储日期和时间信息，精确到秒。

SQL> alter session setnls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select to_date('2009-01-1213:24:33','YYYY-MM-DD HH24:MI:SS') from dual;

TO_DATE('2009-01-12
-------------------
2009-01-12 13:24:33

TIMESTAMP：DATE类型的扩展，保留小数级别的秒，默认为小数点后６位。不保存时区和地区信息。

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07.21.37.984000 PM

TIMESTAMP WITH TIMEZONE：存储带时区信息的TIMESTAMP(以和UTC时间差或者地区信息的形式保存)。形式大致为：

TIMESTAMP '2009-01-12 8:00:00 +8:00'

TIMESTAMP WITH LOCAL TIMEZONE：另一种不同类型的TIMESTAMP，和TIMESTAMP WITH TIMEZONE类型的区别在于：数据库不保存时区相关信息，而是把客户端输入的时间转换为基于databasetimezone的时间后存入数据库(这也就是database timezone设置的意义所在，作为TIMESTAMP WITHLOCAL TIMEZONE类型的计算标尺)。当用户请求此类型信息时，Oracle把数据转换为用户session的时区时间返回给用户。所以Oracle建议把databasetimezone设置为标准时间UTC，这样可以节省每次转换所需要的开销，提高性能。

下面是针对以上几种类型所做的实验：

操作DATE类型数据：

SQL> INSERT INTO table_dt VALUES(1,DATE'2009-01-01');

1 row created.

SQL> INSERT INTO table_dt VALUES(2,TIMESTAMP'2009-01-01 00:00:00 Asia/Hong_Kong');

1 row created.

SQL> INSERT INTO table_dtVALUES(3,TO_DATE('01-JAN-2009','DD-MON-YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_dt;

     C_ID C_DT
---------- -------------------
        1 2009-01-01 00:00:00
        2 2009-01-01 00:00:00
        3 2009-01-01 00:00:00

操作TIMESTAMP数据类型：

SQL> ALTER SESSION SETNLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

Session altered.

SQL> CREATE TABLE table_ts(c_id NUMBER, c_tsTIMESTAMP);

Table created.

SQL> INSERT INTO table_ts VALUES(1, '01-JAN-20092:00:00');

1 row created.

SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP'2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP'2009-01-01 2:00:00 -08:00');

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 120
SQL> select * from table_ts;

     C_ID C_TS
-------------------------------------------------------------------------------------
        1 01-JAN-09 02:00:00.000000
        2 01-JAN-09 02:00:00.000000
        3 01-JAN-09 02:00:00.000000

Note:第三条数据的时区信息丢失！

操作TIMESTAMP WITH TIME ZONE数据类型：

SQL> ALTER SESSION SETNLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

Session altered.

SQL> ALTER SESSION SET TIME_ZONE='-7:00';

Session altered.

SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstzTIMESTAMP WITH TIME ZONE);

Table created.

SQL> INSERT INTO table_tstz VALUES(1,'01-JAN-2009 2:00:00 AM -07:00');

1 row created.

SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP'2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP'2009-01-01 2:00:00 -8:00');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tstz;

     C_ID C_TSTZ
-------------------------------------------------------------------------------------
        1 01-JAN-09 02:00:00.000000 AM -07:00
        2 01-JAN-09 02:00:00.000000 AM -07:00
        3 01-JAN-09 02:00:00.000000 AM -08:00
Note: 第三条数据保存了时区信息！可以和上一个例子TIMESTAMP类型做一个对比。

操作TIMESTAMP WITH LOCAL TIME ZONE数据类型：

SQL> ALTER SESSION SET TIME_ZONE='-07:00';

Session altered.

SQL> CREATE TABLE table_tsltz (c_id NUMBER,c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO table_tsltz VALUES(1,'01-JAN-2009 2:00:00');

1 row created.

SQL> INSERT INTO table_tsltzVALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP'2009-01-01 2:00:00 -08:00');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tsltz;

     C_ID C_TSLTZ
-------------------------------------------------------------------------------------
        1 01-JAN-09 02:00:00.000000
        2 01-JAN-09 02:00:00.000000
        3 01-JAN-09 03:00:00.000000

Note:插入的第三条数据指定为UTC-8时区的时间，然后存入数据库后按照databasetimezone的时间保存，最后在客户端请求的时候，转换为客户端时区的时间(UTC-7)返回！可以参考以下简单实验：

SQL> ALTER SESSION SET TIME_ZONE='-05:00';

Session altered.

SQL> select * from table_tsltz;

     C_ID C_TSLTZ
-------------------------------------------------------------------------------------
        1 01-JAN-09 04:00:00.000000
        2 01-JAN-09 04:00:00.000000
        3 01-JAN-09 05:00:00.000000

可以看出，当客户端时区改为UTC-5的时候，TIMESTAMP WITH LOCAL TIMEZONE数据类型的返回信息是会相应改变的。

在了解了相关数据类型后，那么我们该如何在它们之间做出选择呢？

当你不需要保存时区/地区信息的时候，选择使用TIMESTAMP数据类型，因为它一般需要7-11bytes的存储空间，可以节省空间。

当你需要保存时区/地区信息的时候，请选择使用TIMESTAMP WITH TIMEZONE数据类型。比如一个跨国银行业务应用系统，需要精确纪录每一笔交易的时间和地点(时区)，在这种情况下就需要纪录时区相关信息。因为需要纪录时区相关信息，所以需要多一些的存储空间，一般需要13bytes。

当你并不关心操作发生的具体地点，而只是关心操作是在你当前时区的几点发生的时候，选择使用TIMESTAMP WITH LOCALTIME ZONE。比如一个全球统一的change controlsystem。用户可能只关心某某操作是在我的时间几点发生的(比如中国用户看到的是北京时间8:00am，而伦敦的用户看到的是0:00am)。记住，此类行不保存时区/地区信息，因此如果需要保存相关信息的要慎重！

3. 时区相关的几个函数

DBTIMEZONE -- Returns the value of the database time zone. Thevalue is a time zone offset or a time zone region name.

SESSIONTIMEZONE -- Returns the value of the current session'stime zone.

CURRENT_DATE -- Returns the current date in the session timezone in a value in the Gregorian calendar, of the DATEdatatype.

CURRENT_TIMESTAMP -- Returns the current date and time in thesession time zone as a TIMESTAMP WITH TIME ZONE value.

SYSDATE -- Returns the date and time of the operating system onwhich the database resides, taking into account the time zone ofthe database server's operating system that was in effect when thedatabase was started.

SYSTIMESTAMP -- Returns the system date, including fractionalseconds and time zone of the system on which the databaseresides.

Note:SYSDATE和SYSTIMESTAMP的返回信息是数据库所在操作系统的信息，和当前session的时区无关！

例：

数据库时区为+08:00，当前session时区为-05:00时：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2009-01-12 06:18:24

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 06:18:36.625000 AM -05:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2009-01-12 19:18:42

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07:18:52.921000 PM +08:00

SQL>

把当前session时区改为+09:00以后：

SQL> alter session set time_zone='+09:00';

Session altered.

SQL>
SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+09:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2009-01-12 20:19:54

SQL>
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 08:20:07.218000 PM +09:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2009-01-12 19:20:24

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07:20:30.921000 PM +08:00

SQL>

从以上例子可以看出，SYSDATE和SYSTIMESTAMP的返回结果是不随SESSION时区的改变而改变的，其实从函数的命名就能看出(一组是system的，一组是current的~)

4.存储时间间隔 interval year to month , interval day to second 类型

oracle 提供了存储时间间隔的类型 , 例如相隔3年零2个月 , 或者2天零4个小时 , 不要混淆时间间隔与时间值或时间戳的概念。时间间隔记录的是一段时间长度(例如1年零3个

月)，而时间值或时间戳记录的是一个特定的日期和时间(例如2006年10月28日下午7点32分16秒)。

类型	说明
interval year [ ( years_precision )]to month	存储一个时间间隔，其单位为年和月；可以通过指定可选的years_precision参数来指定年的精度，该参数是一个0～9的整数,默认的精度为2，意思是可以在时间间隔中为年数存储两位数字,时间间隔既可以存储正数，也可以存储负数
interval day [ ( days_precision)]to second[(seconds_presion)]	存储一个时间间隔，其单位为天和秒；可以通过指定可选的days_precision参数来指定天的精度，该参数是一个0~9的整数，默认值为2。另外，还可以通过指定可选的seconds_precision参数来指定秒的小数部分的精度，该参数是一个0~9的整数，默认值为6。时间间隔既可以存储正数，也可以存储负数

注意顺序不能颠倒 .

10. Create a table by Using a subquery

CREATE TABLE dept80

SELECT employee_id , name,

salary * 12 month_salary

FROM employees

WHERE department_id = 80

select 出来的数据会插入到新表中 .

create中的 column必须和 select 中的column相同.

列的约束不会传递到新表 , 只有列的定义, NAME , default值会传递过去 .

如果有表达式等 , 例如上例中的 month_salary , 必须给别名 , 否则会出错 .

11.Alter Table

alter table : add | modify | drop

alter table dep80

add ( job_id varchar(8) )

You can't specify where the column is to appear . The new column becomes the last column .

alter table dep80

modify( job_id varchar(30))

Column modification can include changes to a column's data type , size , default value

You can increase the width or precision of a numeric column .

You can increase the width of numeric or charactor column .

You can decreass the width of a column only if the column contains only null value or if the table has no rows .

You can change the data type only if the column contains null values .

You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains

null values or if you do not change the size .

A change to the default value of a column affects only subsequent insertions to the table .

alter table dep80

DROP column job_id 或者 drop( job_id )

Only one column can be dropped at a time .

The table must have at least one column remaining in it after it is altered .

Once a column is dropped , it can not be recovered .

alter table dep80

set UNUSED( job_id )

UNUSED 由于并非真正DROP, 所以操作比drop快 , 等到系统不忙时 , 再使用 drop将该column删除 , 另外被设置成UNUSED的column无法查看 , 就好象真的删除了一样 ,

只有通过数据字典中的 USER_UNUSED_COL_TABS中可以查看到 .

alter table dept80

DROP UNUSED COLUMNS ( 不需要指定列，全部 unused 列被删除 )

DROP TABLE dept80

所有的此 table 相关的数据和结构将被删除包括 index . ( views , synonyms remain but are invalid ) .

RENAME dept TO detail_dept --> 这是一个 DDL 操作

To change the name of a table , view , sequence , or synonym , execute the RENAME statement .

You must be the owner of the object that you rename .

TRUNCATE TABLE detail_dept -->这是一个DDL操作 , 删除动作很快，因为不用做 undo

TRUNCATE TABLE : removes all rows from a table , releases the storage space used by that table . ( You can not rollback )

The delete statement can also remove all rows from a table , but it does not release storage space . The TRUNCATE command is faster . ( reason as below : )

The TRUNCATE statement is a data definition language ( DDL ) statement and generates no rollback information .

Truncating a table does not fire the delete triggers of the table.

If the table is the parent of a referential integrity constraint , you can not truncate the table , Disable the constraint before issuing the TRUNCATE statement .

Adding Comments to a table

COMMENT ON TABLE | column employees.job_id IS 'Employee Information' .

可以在数据字典中查看COMMENT情况 , ALL_COL_COMMENTS , USER_COL_COMMENTS , ALL_TAB_COMMENTS , USER_TAB_COMMENTS

You can add a comment of upto 2000 bytes about a column , table , view , or snapshot by using the COMMENT statement .

moveofgod

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
007 SG SQL中的注意02

1.subquery You can place the subquery in a number of SQL clauses , including : The WHERE clause , The FROM clause , the HAVING clause . SELECT job_id,AVG( salary ) FROM employees GROUP
复制链接

扫一扫