¡¡¡¡SQL»ù±¾Óï¾ä
Ò». SQLµÄËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾äΪInsert£¬Select£¬UpdateºÍDelete¡£
¶þ.Ê×ÏÈÎÒÃÇʹÓÃCREATE TABLEÓï¾äÀ´´´½¨Ò»¸ö±í¡£DDLÓï¾ä¶ÔÊý¾Ý¿â¶ÔÏóÈç±í¡¢ÁкÍÊÓ½øÐж¨Òå¡£ËüÃDz¢²»¶Ô±íÖеÄÐнøÐд¦Àí£¬ÕâÊÇÒòΪDDLÓï¾ä²¢²»´¦ÀíÊý¾Ý¿âÖÐʵ¼ÊµÄÊý¾Ý¡£ÕâЩ¹¤×÷ÓÉÁíÒ»ÀàSQLÓï¾ä—Êý¾Ý²Ù×÷ÓïÑÔ£¨DML£©Óï¾ä½øÐд¦Àí¡£
SQLÖÐÓÐËÄÖÖ»ù±¾µÄDML²Ù×÷£ºINSERT£¬SELECT£¬UPDATEºÍDELETE¡£ÓÉÓÚÕâÊÇ´ó¶àÊýSQLÓû§¾³£Óõ½µÄ£¬ÎÒÃÇÓбØÒªÔڴ˶ÔËüÃǽøÐÐһһ˵Ã÷¡£
(1)INSERTÓï¾ä
Óû§¿ÉÒÔÓÃINSERTÓï¾ä½«Ò»ÐмǼ²åÈëµ½Ö¸¶¨µÄÒ»¸ö±íÖС£ÀýÈ磬Ҫ½«¹ÍÔ±John SmithµÄ¼Ç¼²åÈëµ½±¾ÀýµÄ±íÖУ¬¿ÉÒÔʹÓÃÈçÏÂÓï¾ä£º
¡¡¡¡INSERT INTO EMPLOYEES VALUES
¡¡¡¡ ('Smith','John','1980-06-10',
¡¡¡¡ 'Los Angles',16,45000);
ÎÒÃÇ˵¹ýϵͳ»á“ÊÔ×Å”½«ÖµÌîÈ룬³ýÁËÖ´ÐйæÔòÖ®ÍâËü»¹Òª½øÐÐÀàÐͼì²é¡£Èç¹ûÀàÐͲ»·û£¨È罫һ¸ö×Ö·û´®ÌîÈëµ½ÀàÐÍΪÊý×ÖµÄÁÐÖУ©£¬ÏµÍ³½«¾Ü¾øÕâÒ»´Î²Ù×÷²¢·µ»ØÒ»¸ö´íÎóÐÅÏ¢¡£
Èç¹ûSQL¾Ü¾øÁËÄãËùÌîÈëµÄÒ»ÁÐÖµ£¬Óï¾äÖÐÆäËû¸÷ÁеÄÖµÒ²²»»áÌîÈë¡£ÕâÊÇÒòΪSQLÌṩ¶ÔÊÂÎñµÄÖ§³Ö¡£Ò»´ÎÊÂÎñ½«Êý¾Ý¿â´ÓÒ»ÖÖÒ ......
1.´´½¨Êý¾Ý¿â
--exec xp_cmdshell 'mkdir d:\project'--µ÷ÓÃDOSÃüÁî´´½¨Îļþ¼Ð£¬Ê¹Óô˾äÐèÒªÆô¶¯SQLµÄÍâΧ¹¤¾ß
if exists(select * from sysdatabases where name='Êý¾Ý¿âÃû')
drop database Êý¾Ý¿âÃû
set nocount on --È¡ÏûÊÜÓ°ÏìÐÐÊý
create database Êý¾Ý¿âÃû
on
(
name='',
filename='',-- .mdf .ldf
size=10mb,
maxsize=
filename= --Êý¾Ý¿â´óСÔö³¤Á¿
)
log on
(
)
go
use Êý¾Ý¿âÃû
--´´½¨µÇ¼Óû§
exec sp_addlogin 'Óû§Ãû','ÃÜÂë'
--È¥µôÊÜÓ°ÏìÐÐÊý
set nocount on
exec sp_grantdbaccess 'adminMaster','adminMaster'
--ÉèÖÃÓû§¶Ô±íµÄȨÏÞ
grant select,delete,update on bbstopic to ̞ ......
Õª×Ôhttp://blog.sina.com.cn/zhm85
SQLÓï¾ä½Øȡʱ¼ä£¬Ö»ÏÔʾÄêÔÂÈÕ£¨2004-09-12£©
select CONVERT(varchar, getdate(), 120 )
‘getdate£¨£©’¸ÄΪʱ¼ä×Ö¶ÎÃû‘createtime’
ÔÙÖØÃüÃûмÓÁУ¨Select Name AS UName from Users£©
ÀýÈç select convert(varchar(11),createtime,120) as Ndate from UserS
ÈÕÆÚ½ØÈ¡ÏÔʾ¸ñʽ
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar ......
ת×Ô£ºhttp://hi.baidu.com/arslong/blog/item/b23307e76252342cb8382001.html
Item01
Á¬½Ó×Ö·û´®Öг£ÓõÄÉùÃ÷ÓУº
·þÎñÆ÷ÉùÃ÷£ºData Source
¡¢Server
ºÍAddr
µÈ¡£
Êý¾Ý¿âÉùÃ÷£ºInitial Catalog
ºÍDataBase
µÈ¡£
¼¯³ÉWindows
Õ˺ŵݲȫÐÔÉùÃ÷£ºIntegrated
Security
ºÍTrusted_Connection
µÈ¡£
ʹÓÃÊý¾Ý¿âÕ˺ŵݲȫÐÔÉùÃ÷£ºUser ID
ºÍPassWord
µÈ¡£
1
£©¶ÔÓÚ·ÃÎÊÊý¾Ý¿âµÄÕ˺ÅÀ´Ëµ£¬Í¨³£ÎÒÃÇÔÚһЩ²Î¿¼×ÊÁÏÉÏ¿´µ½ADO.NET
µÄ×Ö·û´®Á¬½ÓÍùÍùÓÐÈçÏÂд·¨£º
string ConnStr = "server = localhost; user id = sa; password = xxx;
database = northwind";
2
£©¶ÔÓÚ¼¯³ÉWindows
°²È«ÐÔµÄÕ˺ÅÀ´Ëµ£¬ÆäÁ¬½Ó×Ö·û´®Ð´·¨Ò»°ãÈçÏ£º
string ConnStr = "server = localhost; integrated security = sspi; database
= northwind";
»òstring ConnStr = "server = localhost;
trusted_connection = yes; database = northwind";
ʹÓÃWindows
¼¯³ÉµÄ°²È«ÐÔÑéÖ¤ÔÚ·ÃÎÊÊý¾Ý¿âʱ¾ßÓкܶàÓÅÊÆ£º°²È«ÐÔ¸ü¸ß¡¢·ÃÎÊËٶȸü¿ì¡¢¼õÉÙÖØÐÂÉè¼Æ°²È«¼Ü¹¹µÄ¹¤×÷¡¢¿ÉÒÔÓ²±àÂëÁ¬½Ó×Ö·û´®µÈ£¬»¹ÊǺÜÖµµÃʹÓõġ£
1.
Item02 ......
use AdventureWorks
GO
SELECT c.LastName from Person.Contact c;
SELECT * from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID; n
SELECT ProductID,Name,ProductNumber,ReorderPoint
from Production.Product
where ProductID in( select ProductID from Production.Product where ProductID in (1,2,3) )
if EXISTS( select ProductID from Production.Product where ProductID in (1,2,3) )
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
SELECT e.EmployeeID,ce.FirstName,ce.LastName
from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID
INNER JOIN Person.Contact ce
ON e.ContactID = ce.ContactID
INNER JOIN Person.Contact cm
ON m.ContactID = cm.ContactID
WHERE cm.FirstName = 'Jo' and cm.LastName = 'Brown';
Select SalesOrderID,SUM(OrderQty) as sum
from Sales.SalesOrderDetail
where SalesOrderID between 43684 and 43686
group by SalesOrderID
having SUM(OrderQty) > 5
c ......
--SQL¸ß¼¶³ÌÐòÉè¼Æ£º×Ó²éѯ
use AdventureWorks
GO
SELECT DISTINCT EmployeeID from HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL;
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN (SELECT DISTINCT EmployeeID from HumanResources.JobCandidate
WHERE EmployeeID IS NOT NULL)
SELECT DISTINCT EmployeeID from HumanResources.JobCandidate WHERE EmployeeID IS NOT NULL
SELECT e.EmployeeID,FirstName,LastName
from HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID NOT IN (
SELECT DISTINCT EmployeeID
from HumanResources.JobCandidate
WHERE EmployeeID IS NOT NULL
)
SELECT CustomerID,MIN((OrderDate)) AS OrderDate
......