ÕÒЩ²»´íµÄsqlÃæÊÔÌâ(2)
ÎÊÌâÃèÊö£º
±¾ÌâÓõ½ÏÂÃæÈý¸ö¹Øϵ±í£º
CARD ½èÊ鿨¡£ CNO ¿¨ºÅ£¬NAME ÐÕÃû£¬CLASS °à¼¶
BOOKS ͼÊé¡£ BNO ÊéºÅ£¬BNAME ÊéÃû,AUTHOR ×÷Õߣ¬PRICE µ¥¼Û£¬QUANTITY ¿â´æ²áÊý
BORROW ½èÊé¼Ç¼¡£ CNO ½èÊ鿨ºÅ£¬BNO ÊéºÅ£¬RDATE »¹ÊéÈÕÆÚ
±¸×¢£ºÏÞ¶¨Ã¿ÈËÿÖÖÊéÖ»ÄܽèÒ»±¾£»¿â´æ²áÊýËæ½èÊé¡¢»¹Êé¶ø¸Ä±ä¡£
ÒªÇóʵÏÖÈçÏÂ15¸ö´¦Àí£º
1£® д³ö½¨Á¢BORROW±íµÄSQLÓï¾ä£¬ÒªÇó¶¨ÒåÖ÷ÂëÍêÕûÐÔÔ¼ÊøºÍÒýÓÃÍêÕûÐÔÔ¼Êø¡£
2£® ÕÒ³ö½èÊ鳬¹ý5±¾µÄ¶ÁÕß,Êä³ö½èÊ鿨ºÅ¼°Ëù½èͼÊé²áÊý¡£
3£® ²éѯ½èÔÄÁË"Ë®ä°"Ò»ÊéµÄ¶ÁÕߣ¬Êä³öÐÕÃû¼°°à¼¶¡£
4£® ²éѯ¹ýÆÚδ»¹Í¼Ê飬Êä³ö½èÔÄÕߣ¨¿¨ºÅ£©¡¢ÊéºÅ¼°»¹ÊéÈÕÆÚ¡£
5£® ²éѯÊéÃû°üÀ¨"ÍøÂç"¹Ø¼ü´ÊµÄͼÊ飬Êä³öÊéºÅ¡¢ÊéÃû¡¢×÷Õß¡£
6£® ²éѯÏÖÓÐͼÊéÖм۸ñ×î¸ßµÄͼÊ飬Êä³öÊéÃû¼°×÷Õß¡£
7£® ²éѯµ±Ç°½èÁË"¼ÆËã·½·¨"µ«Ã»Óнè"¼ÆËã·½·¨Ï°Ì⼯"µÄ¶ÁÕߣ¬Êä³öÆä½èÊ鿨ºÅ£¬²¢°´¿¨ºÅ½µÐòÅÅÐòÊä³ö¡£
8£® ½«"C01"°àͬѧËù½èͼÊéµÄ»¹ÆÚ¶¼ÑÓ³¤Ò»ÖÜ¡£
9£® ´ÓBOOKS±íÖÐɾ³ýµ±Ç°ÎÞÈ˽èÔĵÄͼÊé¼Ç¼¡£
10£®Èç¹û¾³£°´ÊéÃû²éѯͼÊéÐÅÏ¢£¬Ç뽨Á¢ºÏÊʵÄË÷Òý¡£
11£®ÔÚBORROW±íÉϽ¨Á¢Ò»¸ö´¥·¢Æ÷£¬Íê³ÉÈçϹ¦ÄÜ£ºÈç¹û¶ÁÕß½èÔĵÄÊéÃûÊÇ"Êý¾Ý¿â¼¼Êõ¼°Ó¦ÓÃ"£¬¾Í½«¸Ã¶ÁÕߵĽèÔļǼ±£´æÔÚBORROW_SAVE±íÖУ¨×¢ORROW_SAVE±í½á¹¹Í¬BORROW±í£©¡£
12£®½¨Á¢Ò»¸öÊÓͼ£¬ÏÔʾ"Á¦01"°àѧÉúµÄ½èÊéÐÅÏ¢£¨Ö»ÒªÇóÏÔʾÐÕÃûºÍÊéÃû£©¡£
13£®²éѯµ±Ç°Í¬Ê±½èÓÐ"¼ÆËã·½·¨"ºÍ"×éºÏÊýѧ"Á½±¾ÊéµÄ¶ÁÕߣ¬Êä³öÆä½èÊ鿨ºÅ£¬²¢°´¿¨ºÅÉýÐòÅÅÐòÊä³ö¡£
14£®¼Ù¶¨ÔÚ½¨BOOKS±íʱûÓж¨ÒåÖ÷Â룬д³öΪBOOKS±í×·¼Ó¶¨ÒåÖ÷ÂëµÄÓï¾ä¡£
15£®¶ÔCARD±í×öÈçÏÂÐ޸ģº
a. ½«NAME×î´óÁпíÔö¼Óµ½10¸ö×Ö·û£¨¼Ù¶¨ÔΪ6¸ö×Ö·û£©¡£
b. Ϊ¸Ã±íÔö¼Ó1ÁÐNAME£¨ÏµÃû£©£¬¿É±ä³¤£¬×î´ó20¸ö×Ö·û¡£
1. д³ö½¨Á¢BORROW±íµÄSQLÓï¾ä£¬ÒªÇó¶¨ÒåÖ÷ÂëÍêÕûÐÔÔ¼ÊøºÍÒýÓÃÍêÕûÐÔÔ¼Êø
--ʵÏÖ´úÂ룺
CREATE TABLE BORROW(
CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
RDATE datetime,
PRIMARY KEY(CNO,BNO))
2. ÕÒ³ö½èÊ鳬¹ý5±
Ïà¹ØÎĵµ£º
¾ßÓй̶¨ÓÐЧλÊýºÍСÊýλÊýµÄÊýÖµ×ÊÁÏÀàÐÍ¡£
decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
¹Ì¶¨ÓÐЧλÊýºÍСÊýλÊýµÄÊý×Ö¡£µ±Ê¹ÓÃ×î´óÓÐЧλÊýʱ£¬ÓÐЧֵÊÇ´Ó - 10^38 +1 µ½ 10^38 - 1¡£decimal µÄ SQL-92 ͬÒå×ÖÊÇ dec ºÍ dec(p, s)¡£numeric µÄ¹¦ÄÜÏàµ±ì¶ decimal¡£
p (ÓÐЧλÊý)
¿É´¢´æµÄ×î´óÊ®½øλÊý×ÜÊý£¬Ð¡ ......
1¡¢Ê¹ÓÃË÷ÒýÀ´¸ü¿ìµØ±éÀú±í¡£
ȱʡÇé¿öϽ¨Á¢µÄË÷ÒýÊÇ·ÇȺ¼¯Ë÷Òý£¬µ«ÓÐʱËü²¢²»ÊÇ×î¼ÑµÄ¡£ÔÚ·ÇȺ¼¯Ë÷ÒýÏ£¬Êý¾ÝÔÚÎïÀíÉÏËæ»ú´æ·ÅÔÚÊý¾ÝÒ³ÉÏ¡£ºÏÀíµÄË÷ÒýÉè¼ÆÒª½¨Á¢ÔÚ¶Ô¸÷ÖÖ²éѯµÄ·ÖÎöºÍÔ¤²âÉÏ¡£
Ò»°ãÀ´Ëµ£º
a.ÓдóÁ¿Öظ´Öµ¡¢ÇÒ¾³£Óз¶Î§²éѯ£¨ > ,< £¬> =,< =£©ºÍorder by¡¢group by·¢ÉúµÄÁУ¬¿É¿¼
Âǽ ......
Ò»¡¢SQL ServerÖÐÊý¾ÝÐеĴ洢·½Ê½
ÔÚSQL ServerÖдæ·ÅÊý¾ÝµÄÎļþ»áÒÔ8KBµÄ´óС·ÖÒ³¡£Ã¿Ò»Ò³¿ÉÒÔÊÇÊý¾Ý¡¢Ë÷ÒýÒÔ¼°ÆäËûSQL ServerÊý¾Ý¿âÐèҪΪÆäά»¤Êý¾ÝÎļþµÄÊý¾ÝÀàÐÍ¡£´ó¶àÊýÒ³ÊÇÊý¾ÝÒ³»òË÷ÒýÒ³¡£Ò³ÊÇSQL Server¶Á¡¢Ð´Êý¾ÝÎļþµÄµ¥Ôª¡£Ã¿Ò»Ò³Ö»°üÀ¨Ò»¸ö¶ÔÏóµÄÊý¾Ý»òË÷ÒýÐÅÏ¢£¬ËùÒÔÔÚÿһ¸öÊý¾Ý ......
¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
¡¡¡¡2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
fr ......