经典 MYSQL语句

CREATE DATABASE database-name

DROP DATABASE dbname

USE master
 
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

BACKUP DATABASE pubs TO testBack

CREATE TABLE tabname(col1 type1 [NOT NULL] [PRIMARY KEY],col2 type2 [NOT NULL],..)

CREATE TABLE tab_new LIKE tab_old #(使用旧表创建新表)

CREATE TABLE tab_new AS SELECT col1,col2… FROM tab_old definition ONLY

DROP TABLE tabname

ALTER TABLE tabname ADD COLUMN col TYPE

ALTER TABLE tabname ADD PRIMARY KEY(col)

 ALTER TABLE tabname DROP PRIMARY KEY(col)

CREATE [UNIQUE] INDEX idxname ON tabname(col….)

DROP INDEX idxname

CREATE VIEW viewname AS SELECT statement

DROP VIEW viewname

选择：SELECT * FROM table1 WHERE 范围
 
插入：INSERT INTO table1(field1,field2) VALUES(value1,value2)
 
删除：DELETE FROM table1 WHERE 范围
 
更新：UPDATE table1 SET field1=value1 WHERE 范围
 
查找：SELECT * FROM table1 WHERE field1 LIKE ’%value1%’ ---like的语法很精妙，查资料!
 
排序：SELECT * FROM table1 ORDER BY field1,field2 [DESC]
 
总数：SELECT COUNT * AS totalcount FROM table1
 
求和：SELECT SUM(field1) AS sumvalue FROM table1
 
平均：SELECT avg(field1) AS avgvalue FROM table1
 
最大：SELECT MAX(field1) AS maxvalue FROM table1
 
最小：SELECT MIN(field1) AS minvalue FROM table1

SQL: SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT OUT JOIN b ON a.a = b.c

SELECT * INTO b FROM a WHERE 1<>1

SELECT top 0 * INTO b FROM a

INSERT INTO b(a, b, c) SELECT d,e,f FROM b;

INSERT INTO b(a, b, c) SELECT d,e,f FROM b IN ‘具体数据库’ WHERE 条件

SELECT a,b,c FROM a WHERE a IN (SELECT d FROM b ) 或者: SELECT a,b,c FROM a WHERE 
a IN (1,2,3)

SELECT a.title,a.username,b.adddate FROM TABLE a,(SELECT MAX(adddate) adddate FROM TABLE WHERE TABLE.title=a.title) b

SELECT a.a, a.b, a.c, b.c, b.d, b.f FROM a LEFT OUT JOIN b ON a.a = b.c

SELECT * FROM (SELECT a,b,c FROM a) T WHERE t.a >1;

SELECT * FROM table1 WHERE TIME BETWEEN time1 AND time2

SELECT * FROM table1 WHERE a [NOT] IN (‘值1’,’值2’,’值4’,’值6’)

DELETE FROM table1 WHERE NOT EXISTS ( SELECT * FROM table2 WHERE table1.field1=table2.field1 )

SELECT * FROM a LEFT INNER JOIN b ON a.a=b.b RIGHT INNER JOIN c ON a.a=c.c INNER JOIN d ON a.a=d.d WHERE .....

SELECT * FROM 曰程安排 WHERE datediff('minute',f开始时间,getdate())>5

SELECT top 10 b.* FROM (SELECT top 20 主键字段,排序字段 FROM 表名 ORDER BY 排序字段 DESC) a,表名 b WHERE b.主键字段 = a.主键字段 ORDER BY a.排序字段

SELECT top 10 * form table1 WHERE 范围

SELECT a,b,c FROM tablename ta WHERE a=(SELECT MAX(a) FROM tablename tb WHERE tb.b=ta.b)

(SELECT a FROM tableA ) EXCEPT (SELECT a FROM tableB) EXCEPT (SELECT a FROM tableC)

SELECT top 10 * FROM tablename ORDER BY newid()

SELECT newid()

DELETE FROM tablename WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY col1,col2,...)

SELECT name FROM sysobjects WHERE TYPE='U'

SELECT name FROM syscolumns WHERE id=object_id('TableName')

SELECT TYPE,SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END),SUM(CASE vender WHEN 'C' THEN pcs ELSE 0 END),SUM(CASE vender WHEN 'B' THEN pcs ELSE 0 END) FROM tablename GROUP BY TYPE

TRUNCATE TABLE table1

SELECT top 5 * FROM (SELECT top 15 * FROM TABLE ORDER BY id ASC) table_别名 ORDER BY id DESC

Randomize 
 
RNumber = INT(Rnd*499) +1 
 
While NOT objRec.EOF 
 
IF objRec('ID') = RNumber THEN 
 
#... 这里是执行脚本 ... 
 
END IF 
 
objRec.MoveNext 
 
Wend

Randomize
RNumber = INT(Rnd*499) + 1
　
SQL = "Select * FROM Customers Where ID = " & RNumber
　
SET objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")

SQL = "Select * FROM Customers Where ID = " & RNumber & " or ID = " & RNumber2 & " or ID = " & RNumber3

SQL = "Select * FROM Customers Where ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"

SELECT top 10 * FROM 表名 ORDER BY Rnd(id)

SELECT top n * FROM 表名 ORDER BY newid()

SELECT * FROM 表名 ORDER BY rand() LIMIT n

SELECT table1.fd1,table1,fd2,table2.fd2 FROM table1 LEFT JOIN table2 ON table1.fd1,table2.fd1 WHERE ...

SELECT CASE WHEN len(FIELD)>10 THEN LEFT(FIELD,10)+'...' ELSE FIELD END AS news_name,news_id FROM tablename

SELECT iif(len(FIELD)>2,LEFT(FIELD,2)+'...',FIELD) FROM tablename;

SET 对象变量名=连接对象.Execute("SQL 查询语言")

连接对象.Execute "SQL 操作性语句" [, RecordAffected][, OPTION]