SQLÖеÄDDL,DML,DCLÓïÑÔ
¸Ð¾õ×Ô¼ºÊDZȽϱ¿µÄ£¬Ñ§Ï°¹ýSQLÓïÑÔ£¬µ«ÊÇÈ´·Ö²»Ì«Çå³þDDL¡¢DML¡¢DCLÓïÑÔ£¬ËùÒÔÄØ£¬½ñÌì¾Íºñ×ÅÁ³Æ¤ÎÊÁËÀÏʦ£¬ÏÖÔÚ¾Í×Ô¼ºÄ¿Ç°µÄÁ˽âÀ´×ܽáһϡ£
1¡¢DDL£¨DATA DEFINITION LANGUAGE£©Êý¾ÝÃèÊöÓïÑÔ
DDLÓï¾ä²»»á²úÉú»¹ÔÊý¾Ý£¬ËùÒÔɾ³ýµÄÊý¾ÝÒ²ÊÇÎÞ·¨»Ö¸´µÄ
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
2¡¢DML£¨DATA MANIPULATION LANGUAGE£©Êý¾Ý²Ù×ÝÓãÓïÑÔ
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
3¡¢DCL£¨DATA CONTROL LANGUAGE£©Êý¾Ý¿ØÖÆÓïÑÔ
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like what rollback segment to use
¸Ð¾õÊÇÒªºÃÄÇôһµãÁË£¬ÖÁÉÙ²»ÄÇôºýÍ¿ÁË£¬±Ï¾¹×Ô¼ºÒ²Ñ§ÁËÄÇô¾Ã£¬²»ÄÜʲô¶¼²»ÖªµÀÈý£¬²»ÄÜÖ»ÏþµÃ¾ÍÒ»¸öcreate¡¢update¡¢drop£¬ºÙºÙ£¡£¡£¡
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ÏÖÔÚ·¢ÏÖSQLÓï¾äȷʵÊÇÌ«Ç¿´óÁË£¬ÒÔºóÓöµ½ÎÊÌâÒª¾¡Á¿ÏȺúÃ˼¿¼Ï£¬²»Òª°´ÕÕ×µÄ·½·¨À´×ö£¡
1.´ÓtblFaultDetailTemp±íÖУ¬ÕÒµ½ID=14µÄÏ²¢½«ËùÁгöµÄËĸö×ֶεÄÖµ¿½±´µ½tblFaultDetail±íÖÐ
Insert into tblFaultDetail(Code,FileType,FaultCode,FaultRect) select Code,FileType,FaultCode,FaultRect from tblF ......
sqlÔڶ෽ÃþË÷ºÍÅóÓѵİïÖúÏ£¬ÖÕÓÚ¿ÉÒÔÁ¬½Ó³É¹¦£¬Õâ¶ÔÎÒÀ´ËµÊǸöÀï³Ì±®À´µÄ£¬ÔÚ¹¤×÷µÄ¹ý³ÌÖÐÎÒ×ÜÊÇûÓлú»á¿ÉÒÔÅö´¥µ½SQL£¬µ±ÎÒ¹ÄÆðÓÂÆøÓÂÆøÏëѧϰµÄʱºòÎÒÁ¬ÔõôÓö¼²»»á£¬½øÀ´ÖÕÓÚÁ´½Ó³É¹¦£¬¿ªÐĵغÍÅóÓÑ·ÖÏíÎҵijɹû¡£
1.°²×°sql£¬Ñ§Ï°SQl£¬ÒòΪһ°ãÎÒÊÇÏëÒªÓÃÓÚVS2005ÏîÄ¿µÄ£¬ËùÒÔÒ»°ã×îºó¾ÍÏÈ°²×°VSÈí¼þ£¬ÒÔ±ã ......
---------Êýѧº¯Êý
1.¾ø¶ÔÖµ
S:select abs(-1) value
O:select abs(-1) value from dual
2.È¡Õû(´ó)
S:select ceiling(-1.001) value
O:select ceil(-1.001) value from dual
3.È¡Õû£¨Ð¡£©
S:select floor(-1.001) value
O:select floor(-1.001) value from dual
4.È¡Õû£¨½ØÈ¡£©
S:select cast(-1.002 as int) v ......
--»ù´¡Á˽⣺
1) select distinct name from table --´ò¿ªÖظ´¼Ç¼µÄµ¥¸ö×Ö¶Î
2) select * from table where fid in(Select min(fid) from table group by name)--´ò¿ªÖظ´¼Ç¼µÄËùÓÐ×Ö¶ÎÖµ
3) select * from table where name in(select name from table group by name having count(name)=1)--´ò¿ªÖظ´ÈÎÒâ´ÎÊýµ ......