Ò» µ¥´Ê½âÊÍ(2·Ö/¸ö) 34·Ö
Data Êý¾Ý Database Êý¾Ý¿â RDBMS ¹ØϵÊý¾Ý¿â¹ÜÀíϵͳ GRANT ÊÚȨ
REVOKE È¡ÏûȨÏÞ DENY ¾Ü¾øȨÏÞ DECLARE ¶¨Òå±äÁ¿ PROCEDURE´æ´¢¹ý³Ì
ÊÂÎñ Transaction ´¥·¢Æ÷ TRIGGER ¼ÌÐø continue Ψһ unqiue
Ö÷¼ü primary key ±êʶÁÐ identity Íâ¼ü foreign key ¼ì²é check
Ô¼Êø constraint
¶þ ±àдSQLÓï¾ä(5·Ö/Ìâ) 50·Ö £¨°üº¬ ±ÊÊÔÌâÎÊÌâºÍ½â´ð´ð°¸£©
1) ´´½¨Ò»ÕÅѧÉú±í£¬°üº¬ÒÔÏÂÐÅÏ¢£¬Ñ§ºÅ£¬ÐÕÃû£¬ÄêÁ䣬ÐԱ𣬼Òͥסַ£¬ÁªÏµµç»°
Create table stu (ѧºÅ int ,
ÐÕÃû varchar(8),
ÄêÁä int,
ÐÔ±ð varchar(4),
¼ÒÍ¥µØÖ· varchar(50),
ÁªÏµµç»° int
);
2) ÐÞ¸ÄѧÉú±íµÄ½á¹¹£¬Ìí¼ÓÒ»ÁÐÐÅÏ¢£¬Ñ§Àú
Alter table stu add ѧÀú varchar(6);
3) ÐÞ¸ÄѧÉú±íµÄ½á¹¹£¬É¾³ýÒ»ÁÐÐÅÏ¢£¬¼Òͥסַ
Alter table stu drop column ¼ÒÍ¥µØÖ·
4) ÏòѧÉú±íÌí¼ÓÈçÏÂÐÅÏ¢£º
ѧºÅ ÐÕÃûÄêÁäÐÔ±ðÁªÏµµç»°Ñ§Àú
1A22ÄÐ123456Сѧ
2B21ÄÐ119ÖÐѧ
3C23ÄÐ110¸ßÖÐ
4D18Å®114´óѧ
Insert into stu values(1,¡¯A¡¯,22,¡¯ÄС¯,123456,¡¯Ð¡Ñ§¡¯)
Insert into stu values(2,¡¯B¡¯,21,¡¯ÄС¯,119,¡¯ÖÐѧ¡¯)
Insert into stu values(3,¡¯C¡¯,23,¡¯ÄС¯,110,¡¯¸ßÖС¯)
Insert into stu values(4,¡¯D¡¯,18,¡¯Å®¡¯,114,¡¯´óѧ¡¯)
5) ÐÞ¸ÄѧÉú±íµÄÊý¾Ý£¬½«µç»°ºÅÂëÒÔ11¿ªÍ·µÄѧԱµÄѧÀú¸ÄΪ¡°´óר¡±
Update stu set ѧÀú=¡¯´óר¡¯ where ÁªÏµµç»° like ¡®11%¡¯
6) ɾ³ýѧÉú±íµÄÊý¾Ý£¬ÐÕÃûÒÔC¿ªÍ·£¬ÐÔ±ðΪ¡®ÄС¯µÄ¼Ç¼ɾ³ý
Delect from stu where ÐÔ±ð=¡¯ÄС¯ and ÐÕÃû like ¡®c%¡¯
7) ²éѯѧÉú±íµÄÊý¾Ý£¬½«ËùÓÐÄêÁäСÓÚ22ËêµÄ£¬Ñ§ÀúΪ¡°´óר¡±µÄ£¬Ñ§ÉúµÄÐÕÃûºÍѧºÅʾ³öÀ´
Select ÐÕÃû,ѧºÅ from stu where ÄêÁä<22 and ѧÀú=¡¯´óר¡¯
8) ²éѯѧÉú±íµÄÊý¾Ý£¬²éѯËùÓÐÐÅÏ¢£¬ÁгöÇ°25%µÄ¼Ç¼
Select top 25 percent * from stu
9) ²éѯ³öËùÓÐѧÉúµÄÐÕÃû£¬ÐÔ±ð£¬ÄêÁä½µÐòÅÅÁÐ
Select ÐÕÃû,ÐÔ±ð from stu order by ÄêÁä desc
10) °´ÕÕÐÔ±ð·Ö×é²éѯËùÓеÄƽ¾ùÄêÁä
Select avg(ÄêÁä) from stu group by ÐÔ±ð
Èý Ìî¿Õ(3·Ö/Ìâ) 36·Ö £¨°üº¬ ±ÊÊÔÌâÎÊÌâºÍ½â´ð´ð°¸£©
1) Ë÷Òý·ÖΪ__¾Û¼¯Ë÷Òý___ºÍ__·Ç¾Û¼¯Ë÷Òý__ÔÚÒ»ÕűíÉÏ×î¶à¿ÉÒÔ´´½¨1¸ö ¾Û¼¯Ë÷Òý_Ë÷Òý¡£µ«ÊÇ¿ÉÒÔ´´½¨_249¸ö·Ç ¾Û¼¯Ë÷Òý Ë÷Òý¡£
2) ϵͳ´æ´¢¹ý³Ì_sp-helptext__ÊÇÓÃÀ´ÏÔʾ¹æÔò£¬Ä¬ÈÏÖµ£¬Î´¼ÓÃܵĴ洢¹ý³Ì£¬Óû§¶¨Ò庯Êý£¬´¥·¢»òÊÓͼµÄÎı¾
3) ÊÂÎñ¿ªÊ¼£ºbegin Transction
Ìá½»ÊÂÎñ£ºcommit Transction
»Ø¹öÊÂÎñ£ºrollback Transction
ËÄ ÎÊ´ðÌâ(5·Ö/Ìâ) 60·Ö £¨°üº¬ ±ÊÊÔÌâÎÊÌâºÍ½â´ð´ð°¸£©
1) Êý¾Ý¿â°üº¬ÄÄЩÄǼ¸ÖÖºó׺ÃûµÄÎļþ±ØÐ룬ÕâЩÎļþ·Ö±ð´æ·ÅÔÚʲôµÄÐÅÏ¢
Ö÷ÒªÊý¾ÝÎļþ(.mdf) °üº¬Êý¾ÝÓû§ÊÕ¼¯µÄÐÅÏ¢,»¹ÓÐÊý¾Ý¿âÆäËûÏà¹ØµÄÐÅÏ¢,
ÈÕÖ¾Êý¾ÝÎļþ(.ndf) ´æ·ÅÓû§¶ÔÊý¾Ý¿âµÄÔöɾ¸Ä²éµÄÐÅÏ¢,ÓÃÓÚ±¸·Ý»Ö¸´Ê¹ÓÃ
2) TRUNCATE TABLE ÃüÁîÊÇʲôº¬Òå ºÍDelete from ±íÃûÓÐʲôÇø
TRUNCATE TABLE: ÌṩÁËÒ»ÖÖɾ³ý±íÖÐËùÓмǼµÄ¿ìËÙ·½·¨
Delete from ±íÃû:¿ÉÒÔɾ³ý±íµÄÒ»¸ö»ò¶àÌõ¼Ç¼
3) ˵³öÒÔϾۺÏÊýµÄº¬Ò壺avg ,sum ,max ,min , count ,count(*)
AVG:Çóƽ¾ùÖµ
SUM:ÇóºÍ
MAX:Çó×î´óÖµ
MIN:Çó×îСֵ
COUNT(*):·µ»ØËùÓÐÐÐÊý
COUNT·µ»ØÂú×ãÖ¸¶¨Ìõ¼þµÄ¼Ç¼ֵ
4) inner join ÊÇʲôÒâ˼ ×÷ÓÃÊÇʲô д³ö»ù±¾Óï·¨½á¹¹
INNER JOIN ÄÚÁª½Ó,ÓÃÓÚ·µ»ØÁ½¸ö±íÖÐÒª²éѯµÄÁÐÊý¾ÝͨÐÅ
Select * from ±íÃû1 inner join ±íÃû2 on Ìõ¼þ±í´ïʽ
5) ×óÏòÍâÁª½Ó£¬ÓÒÏòÍâÁª½Ó£¬È«Áª½ÓµÄ¹Ø½¡×ÖÈçºÎд
Left outer join ×óÏòÍâÁª½Ó
Right outer join ÓÒÏòÍâÁª½Ó
Full outer join È«Áª½Ó
6) ×Ó²éѯ·ÖΪ¼¸À࣬˵Ã÷Ï໥֮¼äµÄ±ð
Á˲éѯ·ÖÈýÖÖ»ù±¾×Ó²éѯ: 1.ʹÓÃin ²éѯ·µ»ØÒ»Áлò¸ü¶àÖµ
2.±È½ÏÔËËã·û,·µ»Øµ¥¸öÖµÇÚ×öΪÍâ²éѯµÄ²ÎÊý
3.ÓÃexists ²éѯʱÏ൱ÓÚ½øÐÐÒ»´ÎÊý¾Ý²âÊÔ
7) ʵÏÖʵÌåÍêÕûÐÔ£¬ÊµÏÖÓòÍêÕûÐÔ£¬ÊµÏÖ ÍêÕûÐÔ(ÒýÓÃÍêÕûÐÔ)£¬ÊµÏÖ×Ô¶¨ÒåÍêÕûÐÔ·Ö±ðʹÓÃʲôÊÖ¶Î
ʵÏÖʵÌåÍêÕûÐÔ: Ö÷¼üÔ¼Êø ΨһԼÊø ±êʶÁÐ
ʵÏÖÓòÍêÕûÐÔ: ĬÈÏÖµÔ¼Êø ¼ì²éÔ¼Êø ·Ç¿ÕÊôÐÔ
ÒýºÍÍêÕûÐÔ: Íâ¼üÒýÓÃ
8) ÊÓͼ¿ÉÒÔ¸üÐÂÂð »áÓ°Ï쵽ʵ¼Ê±íÂð
ÊÓͼÊÇ¿ÉÒÔ¸üеÄ,ÊÓͼֻÊÇ»ùÓÚ»ù±¾±íÉϵÄÐéÄâ±í,¶ÔÊÓͼµÄ¸üлáÖ±½ÓÓ°Ï쵽ʵ¼Ê±í
9) ̸̸ÕâÑù¼¸¸ö½ÇÉ«£¬ dbo , Sysadmin public
Dbo : ÊÇÊý¾Ý¿âµÄÓµÓÐÕß,¶ÔÊý¾Ý¿âÓµÓÐËùÓвÙ×÷µÄȨÏÞ
Sysadmin : ¿ÉÒÔ¶ÔSQL SERVERÖ´ÐÐÈκλ
Public : ×Ô¶¯´´½¨µÄ,Äܲ¶»ñÊý¾Ý¿âÖÐÓû§µÄËùÓÐĬÈÏȨÏÞ
10) ºÎΪ¶¯Ì¬ÓÎ±ê ºÎΪ¾²Ì¬Óαê
¶¯Ì¬ÓαêÓ뾲̬ÓαêÏà¶Ô,·´Ó³½á¹û¼¯ÖÐËù×öµÄËùÓиü¸Ä,
¾²Ì¬ÓαêµÄ½á¹û¼¯ÔÚÓαê´ò¿ªÊ±,½¨Á¢ÔÚtempdbÖÐ,×Ü°´ÕÕÓαê´ò¿ªÊ±µÄÔÑùÏÔʾ
11) ʲôÊÇ´æ´¢¹ý³Ì Ϊʲô´æ´¢¹ý³ÌÒª±Èµ¥´¿µÄSql Óï¾äÖ´ÐÐÆðÀ´Òª¿ì
´æ´¢¹ý³Ì:ÊÇÒ»×éÔ¤ÏȱàÒëºÃµÄT-SQL´úÂë
ÔÚ´´½¨´æ´¢¹ý³Ìʱ¾¹ýÁËÓï·¨ºÍÐÔÄÜÓÅ»¯,Ö´Ðв»±ØÖظ´µÄ²½Öè,ʹÓô洢¹ý³Ì¿ÉÌá¸ßÔËÐÐЧÂÊ
12)ʲôÊÇInserted ±í ʲôÊÇDeleted ±í
Inserted±íÓÃÓÚ´æ´¢insertedºÍupdateÓï¾äÓ°ÏìµÄ¸±±¾
Deleted ±íÓÃÓÚ´æ´¢delect ºÍ updateÓï¾äÓ°ÏìµÄÐеĸ±±¾