Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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


Ïà¹ØÎĵµ£º

PL/SQL³ÌÐòÉè¼Æ£¨ÓαêµÄʹÓã©


 ÎªÁË´¦Àí SQL Óï¾ä£¬ORACLE ±ØÐë·ÖÅäһƬ½ÐÉÏÏÂÎÄ( context area )µÄÇøÓòÀ´´¦ÀíËù±ØÐèµÄÐÅÏ¢£¬ÆäÖаüÀ¨Òª´¦ÀíµÄÐеÄÊýÄ¿£¬Ò»¸öÖ¸ÏòÓï¾ä±»·ÖÎöÒÔºóµÄ±íʾÐÎʽµÄÖ¸ÕëÒÔ¼°²éѯµÄ»î¶¯¼¯(active set)¡£
 ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú( handle)»òÖ¸Õ롣ͨ¹ýÓα꣬PL/SQL¿ÉÒÔ¿ØÖÆÉÏÏÂÎÄÇøºÍ´¦ÀíÓï¾äʱÉÏÏÂÎÄÇø»á·¢ÉúÐ ......

sqlÓαê

ÒòΪҪ¸ù¾ÝºÜ¸´ÔӵĹæÔò´¦ÀíÓû§Êý¾Ý£¬ËùÒÔÕâÀïÓõ½Êý¾Ý¿âµÄÓαꡣƽʱ²»ÔõôÓÃÕâ¸ö£¬Ð´ÔÚÕâÀï´¿´âΪ×Ô¼º±¸¸öÍü¡£
--½«Ñ§¼®ºÅÖظ´µÄ·ÅÈëÁÙʱ±í 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 ......

£¨×ª£©SQL¾­µäÃæÊÔÌ⼯£¨Ò»£©


µÚÒ»Ì⣺
Ϊ¹ÜÀíÒµÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í£º
S(S#,SN,SD,SA)S#,SN,SD,SA·Ö±ð´ú±íѧºÅ£¬Ñ§Ô±ÐÕÃû£¬ËùÊôµ¥Î»£¬Ñ§Ô±ÄêÁä
C(C#,CN)C#,CN·Ö±ð´ú±í¿Î³Ì±àºÅ£¬¿Î³ÌÃû³Æ
SC(S#,C#,G) S#,C#,G·Ö±ð´ú±íѧºÅ£¬ËùÑ¡µÄ¿Î³Ì±àºÅ£¬Ñ§Ï°³É¼¨
(1)ʹÓñê×¼SQLǶÌ×Óï¾ä²éѯѡÐ޿γÌÃû³ÆΪ’Ë°ÊÕ»ù´¡’µÄѧԱѧºÅºÍÐÕÃû?
(2) ......

ORACLE PL/SQL ¶ÔÏó(object)ѧϰ±Ê¼Ç(Ò»)

 1¡¢¶ÔÏóÀàÐ͹淶
 
´´½¨¶ÔÏóÀàÐ͹淶µÄÓï·¨ÈçÏÂ
 
CREATE [OR REPLACE] TYPE [schema.] type_name
[AUTHID {CURRENT_USER|DEFINER}] AS OBJECT (
Attribute1 datatype,
[attribute2 datatype,…]
[method 1]
[method 2]);
/

 
 
 
ÆäÖÐAUTHIDָʾ½«À´Ö´Ðи÷½·¨Ê±£¬ ......

sql server ÄÚ´æ±í

--1¼ÓÄÚ´æ±í
EXEC sp_tableoption '±íÃû','pintable', 'true' 
--2жÔØÄÚ´æ±í
EXEC sp_tableoption '±íÃû','pintable', 'false' 
--2²éѯÊÇ·ñÓÐÄÚ´æ±íפÁô
SELECT * from INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
          AND OBJECTPROP ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ