SQLÓï¾äPART1
Oracle SQL(partI)
Data manipulation language(DML): select, insert, update, delete, merge.
Data definition language(DDL): create, alter, drop, rename, truncate, comment
Data control language(DCL): grant, revoke
Transaction control: commit, rollback, savepoint
Arithmetic Expressions:
+, -, *, /
1) Arithmetic expressions containing a null value evaluate to null
Column Alias
1) requires double quotation marks if it contains spaces or special characters, or if it is case-sensitive
e.g.: select last_name as name, commission_pct comm from employees;
e.g.: select last_name "Name", salary*12 "Annual Salary" from employees
Alternative Quote(q) Operator
e.g.: select depart_name||' Department'||q'['s Manager is ]'||manager_id as "Department and Manager"
results:
Department and Manager
Administrator Department's Manager is Me
...
Using DESCRIBE to display the table structure
e.g.: describe tb1;
Rules of precedence for operators in an expression
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical condition
8 AND logical condition
9 OR logical condition
e.g.: select last_name, job_id, salary from employees where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;
means: select job_id='SA_REP' or (job_id='AD_PRES' and salary>15000)
e.g.: select last_name, job_id, salary from employees where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000
Sorting:
can be sorted by column's numeric position
eg.: select last_name, job_id, department_id, hire_date from employees order by 3;
Substitution Variables
1. temporarily store values with & and &&
2. using in following conditions: where conditions, order by clauses, column expressions, table names, entire select statements.
e.g.: select employee_id, last_name, salary, department_id from employees where e
Ïà¹ØÎĵµ£º
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
&nbs ......
SQL Server 2005 ÈÕ־ɾ³ýºÍÈÕÖ¾ÎļþÏÞÖÆ(ת)
2009Äê08ÔÂ04ÈÕ ÐÇÆÚ¶þ ÏÂÎç 04:40
Çå³ýÈÕÖ¾£º
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE szwzcheck ......
ÒòΪҪ¸ù¾ÝºÜ¸´ÔӵĹæÔò´¦ÀíÓû§Êý¾Ý£¬ËùÒÔÕâÀïÓõ½Êý¾Ý¿âµÄÓαꡣƽʱ²»ÔõôÓÃÕâ¸ö£¬Ð´ÔÚÕâÀï´¿´âΪ×Ô¼º±¸¸öÍü¡£
--½«Ñ§¼®ºÅÖØ¸´µÄ·ÅÈëÁÙʱ±í tmp_zdsoft_unitive_code(³ý¸ßÖÐѧ¶ÎÍâ)
drop table tmp_zdsoft_unitive_code;
select s.id ,sch.school_code,sch.school_name,s.student_name,s.unitive_code,s.identity_car ......
1¡¢¶ÔÏóÀàÐ͹淶
´´½¨¶ÔÏóÀàÐ͹淶µÄÓï·¨ÈçÏÂ
CREATE [OR REPLACE] TYPE [schema.] type_name
[AUTHID {CURRENT_USER|DEFINER}] AS OBJECT (
Attribute1 datatype,
[attribute2 datatype,…]
[method 1]
[method 2]);
/
ÆäÖÐAUTHIDָʾ½«À´Ö´Ðи÷½·¨Ê±£¬ ......
7¡¢¶ÔÏóÀàÐͼ̳Ð
¶ÔÏóÀàÐÍʵÏÖÔÊÐíÎÒÃÇ´´½¨Ò»¸ö»ùÀàÐÍ£¬»ò½Ð¸¸ÀàÐÍ£¬ÕâÖÖÀàÐ͵ÄÊôÐÔ»ò·½·¨¿ÉÒÔ±»ÁíÒ»¸ö¶ÔÏóÀàÐͼ̳С£È»ºó¿ÉÒÔ´´½¨Ò»¸ö×ÓÀàÐÍ£¬»ò½Ðº¢×ÓÀàÐÍ£¬Ö±½ÓʹÓü̳йýÀ´µÄÊôÐÔ»ò·½·¨£¬»òÕßÓÃ×Ô¼ºµÄÊôÐԺͷ½·¨ÖØÐ´¸¸ÀàÐ͵ÄÊôÐÔ»ò·½·¨¡£
INSTANTIABLE¹Ø¼ü×Ö±íʾÎÒÃÇ¿ÉÒÔ´Ó¸ÃÀàÐÍÖÐʵÀý»¯»òÕß´´½¨¶ÔÏ ......