OralceÊý¾Ý¿â
Ò»¡¢Ñ¡ÔñÌâ
1¡¢ÔÚSQLµÄ²éѯÓï¾äÖУ¬ÓÃÓÚ·Ö×é²éѯµÄÓï¾äÊÇ£¨ C £©¡£
A£©ORDER BY B£©WHERE C£©GROUP BY D£©HAVING
2¡¢ÔÚ¡°Ñ§ÉúÇé¿ö.DBF¡±±íÖУ¬²éѯ¼ÆËã»úרҵ¡¢Öúѧ½ð´óÓÚ40ÔªµÄѧÉúµÄÐÕÃû£¬ÕýÈ·µÄÓï¾äÊÇ£¨ C £©¡£
A£©SELECT ÐÕÃû FROM ѧÉúÇé¿ö WHERE ¼ÆËã»ú .AND.Öúѧ½ð<40
B£©SELECT ÐÕÃû FROM ѧÉúÇé¿ö WHERE רҵ=¡°¼ÆËã»ú¡±.OR.Öúѧ½ð>40
C£©SELECT ÐÕÃû FROM ѧÉúÇé¿ö WHERE רҵ=¡°¼ÆËã»ú¡±.AND.Öúѧ½ð>40
D£©SELECT ÐÕÃû FROM ѧÉúÇé¿ö WHERE רҵ=¡°¼ÆËã»ú¡±.AND.Öúѧ½ð<40
3¡¢ÏÂÁÐSQLÓï¾äÖУ¬Ð޸ıí½á¹¹µÄÊÇ£¨ A £©¡£
A£©ALTER B£©CREATE C£©DESC D£©RENAME
4¡¢ÒÑÖªÖ°¹¤±íEMPÓй¤ºÅE_NOºÍ¹¤×ÊE_SALÁ½¸ö×ֶΡ£´ÓÖ°¹¤¹ØϵÖмìË÷ËùÓй¤×ÊÖµ,ÒªÇóÔÚÊä³ö½á¹ûÖÐûÓÐÖظ´µÄ¹¤×ÊÖµ,ÔòSQLµÄÃüÁîÓï¾äÄÜʵÏÖÉÏÊö¹¦ÄܵÄÊÇ£¨ B £©¡£
A£©SELECT ALL E_SAL FROM EMP
B£©SELECT DISTINCT E_SAL FROM EMP
C£©SELECT E_SAL FROM EMP
D£©SELECT E_SAL WHERE EMP
5¡¢ÇëÑ¡³öÊôÓÚDMLµÄÑ¡Ï C £©
A) TRUNCATE B£©CREAT C£©DELETE D£©DROP
6¡¢ÊÓͼ´æ·ÅÔÚÄÄÀB£©
A£®Êý¾Ý¿âµÄ±íÖС¡¡¡¡¡ B.Êý¾Ý×ÖµäµÄselectÓï¾äÖÐ
C£®fromÁбíµÄµÚÒ»¸ö±í¸ñµÄselectÓï¾äÖÐ D.ÁбíµÄµÚ¶þ¸ö±í¸ñµÄselectÓï¾äÖÐ
7¡¢µ±fetchÖ´ÐÐʧ°Üʱ£¬ÄÄÖÖÓαêµÄÊôÐÔֵΪtrue£¨C£©
A£®%isopen B.%found C.%notfound D.%rowcount
8¡¢ÏÂÁÐÄÇЩ˵·¨ÊÇÕýÈ·µÄ£¨B £©
A) Ò»¸öÊÂÎñÖеÄijÌõSQLÃüÁîÌύʱ£¬ÆäËüSQLÃüÁî¿ÉÒÔ²»Ìá½»¡£
B) ÔÚÒ»¸ö¹Øϵ±íÖУ¬Ö÷¼ü¿ÉΨһ±êʶһÐмǼ¡£
C) Ò»¸ö¹Øϵ±íÖеÄÍâ¼ü±Ø¶¨ÊÇÁíÒ»±íÖеÄÖ÷¼ü¡£
D) »Ø¹ö¿ÉÒÔÔÚÊÂÎñʧ°Üʱ£¬»Øµ½²Ù×÷ÊÂÎñÇ°µÄÊý¾Ý״̬¡£
9¡¢¼ÙÈçÒ»¸ö±äÁ¿µÄֵΪnuLL£¬ÓÐÒ»ÌõifÓï¾ä£¬
if±äÁ¿=NULL
·ÖÖ§1
else
·ÖÖ§2
ÇëÎÊ×îÖÕÖ´ÐеĽá¹ûÊÇ£¨B£©
A) ·ÖÖ§1 B£©·ÖÖ§2 C£©ÏµÍ³´íÎóÌáʾ D£©¶¼Ã»ÓÐÖ´ÐÐ
10¡¢¹ØÓÚÖ÷¼ü(primary key)ºÍΨһË÷Òý(unique index)µÄ˵·¨ÄÄЩÊÇ´íÎóµÄ £¨A£©
A¡¢Î¨Ò»Ë÷ÒýµÄÁÐÔÊÐíΪNULLÖµ
B¡¢ Ö÷¼üµÄÁв»ÔÊÐíΪNULLÖµ
C¡¢ Ò»¸ö±íÖ»ÄÜÓÐÒ»¸öΨһË÷Òý
11¡¢½ÌʦÐÅÏ¢±íteacher°üº¬ÒÔÏÂ×ֶΣº
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
ÏÂÃæÄĸöÓï¾ä¿ÉÒÔÏÔʾÎÞEMAILµØÖ·µÄ½ÌʦµÄÐÕÃû£¨ C £©
SELECT last_name£¬fist_name from teacher where email=null
SELECT last_name£¬first_name from teacher where email<>null
SELECT last_name£¬first_name from teacher where email IS null
SELECT last_name£¬first_name from teacher where email IS NOT NULL
12¡¢ÔÚÖ´ÐÐÒ»¸ö²éѯʱÄĸöÊÇÕýÈ·µÄ˳Ðò £¨C£©
A¡¢½âÎö¡¢Ö´ÐÐ B¡¢Ö´ÐС¢½âÎö¡¢»ñÈ¡
C¡¢½âÎö¡¢Ö´ÐС¢»ñÈ¡ D¡¢½âÎö¡¢»ñÈ¡
13¡¢ÈçÏÂÓï¾ä
if V_num>5 then
v_example£º=1£º
elSif V_num>10 then
v_example£º=2£º
elSif v_hum<20 then
v_example£º=3£º
elsif v_num<39 then
v_example£º=4£º
else v_example£º=5£º
Èç¹ûv num=37£¬Ôòv¡ªexampleµÄÖµÊǶàÉÙ£¨£©
A¡¢1 B¡¢2 C¡¢3 D¡¢4 E¡¢5
¶þ¡¢Åж϶Դí
1¡¢Ò»¸ö±í¿Õ¼ä¿ÉÒÔº¬Óжà¸öÊý¾ÝÎļþ£¬Ò»¸öÊý¾ÝÎļþÒ²¿ÉÒÔ¿ç¶à¸ö±í¿Õ¼ä¡£Ò»¸ö±í²»¿ÉÒÔ¿ç±í¿Õ¼ä£¨ ´íÎó £©
2¡¢Ò»¸ö±ítest ÓÐ×ֶΠid,name,age,address,email,salaryɾ³ýage ´óÓÚƽ¾ùÄêÁäµÄ¼Ç¼
ÕâÑùд¿ÉÒÔÂð delete from test where age > avg(age); £¨ ´íÎó £©
3¡¢BETWEEN ANDÔËËã·ûÊÇ·ñ¼È¿ÉÒÔÓÃÓÚÊýÖµ±í´ïʽ£¬ÓÖ¿ÉÒÔÓÃÓÚ×Ö·û±í´ïʽ£¿ £¨ ÕýÈ· £©
¶þ¡¢¼ò´ðÌ⣺
1¡¢ÓÃSQLÓï¾äд³öÒÔϲÙ×÷
a¡¢ ½¨Ò»ÕÅÊý¾Ý±íEMP£¨Ö°¹¤ÐÅÏ¢±í£©£¬°üº¬¹¤ºÅ¡¢ÐÕÃû¡¢ÐԱ𡢲¿ÃÅ¡¢ÄêÁä¡¢¹¤×ʵÈ×ֶΡ£
Create table EMP
(
ID NUMBER,
NAME VARCHAR2(20),
SEX CHAR(4),
DEPARTMENT VARCHAR2(20),
AGE NUMBER,
SALARY NUMBER(6,2)
);
b¡¢ Íù¸ÃÊý¾Ý±íÖÐÌí¼ÓÒ»Ìõ¼Ç¼¡£
INSERT INTO EMP VALUES(1,¡¯XIAOZHANG¡¯,¡¯ÄС¯,¡¯ÏúÊÛ²¿¡¯,23,1200.0);
c¡¢ ¼ÙÉè¸Ã±íÖдæÔÚ3000ÌõÔ±¹¤µÄÏà¹ØÐÅÏ¢£¬ÒªÇóÏÔʾËùÓÐÄêÁäÔÚ20µ½30ËêÖ®¼äµÄÈËÔ±¼Ç¼
SELECT * FROM EMP WHERE AGE BETWEEN 20 AND 30;
d¡¢ ¼ÙÉèÓÐÒ»¸öÏúÊÛ²¿£¬Çë¸ø³öÏúÊÛ²¿ÃÅËùÓÐŮְԱµÄ¹¤×Ê×ÜÊý
SELECT SUM(SALARY) FROM EMP WHERE DEPARTMENT=¡¯ÏúÊÛ²¿¡¯ AND SEX=¡¯Å®¡¯;
e¡¢ ΪÁËÌá¸ß²Ù×÷¸Ã±íµÄÐÔÄÜ£¬¸ø±í°´ÄêÁä·ÖÇø£¬20ÒÔÏ·ÖΪ youth,20µ½30Ϊmid1£¬ 30µ½40 Ϊmid2£¬40µ½60 Ϊold
2¡¢ ʲôÊÇDDL¡¢Ê²Ã´ÊÇDML£¬Çë·Ö±ðÁоÙһЩ¡£
DDLÊÇÊý¾Ý¶¨ÒåÓïÑÔ£¬½¨Á¢¡¢É¾³ýºÍÐÞ¸ÄÊý¾Ý¶ÔÏó¡£
ÀýÈ磺CREATE,DROP,ALTER.
DMLÊÇÊý¾Ý²Ù×ÝÓïÑÔ£¬Íê³ÉÊý¾Ý²Ù×÷µÄÃüÁ°üÀ¨²éѯ¡£
ÀýÈ磺SELECT£¬INSERT£¬UPDATE£¬DELETE
3¡¢ ÊÓͼռ¿Õ¼äÂð£¿ÐÞ¸ÄÊÓͼÊÇ·ñ¸Ä±ä»ù±í£¬ÎªÊ²Ã´
¶¨Ò壺ÊÓͼÊDz鿴Êý¾Ý±íÖеÄÒ»ÖÖ·½·¨¡££¨µ±Ò»Ð©Óû§ÐèÒª¾³£·ÃÎʺͲéѯÊý¾Ý±íÖеÄijЩ×ֶι¹³ÉµÄÊý¾Ý£¬µ«¹ÜÀíÔ±´Ó°²È«½Ç¶È¿¼ÂÇÓÖ²»Ï£ÍûËûÃÇÖ±½Ó½Ó´¥Êý¾Ý±íʱ£¬¿ÉÒÔÀûÓÃOracleÊý¾Ý¿âÌṩµÄÊÓͼÕâÒ»Êý¾Ý¶ÔÏ󣩡££¨ÊÓͼ²»ÊÇÊý¾Ý±í£¬Ëü½öÊÇһЩSQL²éѯÓï¾äµÄ¼¯ºÏ£¬×÷ÓÃÊÇ°´ÕÕ²»Í¬µÄÒªÇó´ÓÊý¾Ý±íÖÐÌáÈ¡²»Í¬µÄÊý¾Ý¡£
4¡¢ ÓαêÊÇʲô£¬×÷ÓÃÊÇʲô£¬Ê¹ÓÃÓαêµÄ²½ÖèÊÇʲôдһ¸öʹÓÃÓαêµÄÀý×Ó
¶¨Ò壺ÓαêÊÇΪ´¦ÀíSQLµÄselectÓï¾ä¶ø·ÖÅäµÄÒ»´ó¿éÄÚ´æ¡£
×÷Óãº
²½Ö裺
£¨1£© ÉùÃ÷Óαê
£¨2£© ´ò¿ªÓαê
£¨3£© ÌáÈ¡Êý¾Ý
£¨4£© ¹Ø±ÕÓαê
Àý×Ó£º
declare
cursor student_id is
select student_id from studentinfo order by studentno;
studenta studentinfo.student_id%type
for each row
begin
open student_list;
loop
fetch student_list into studenta