sql实例

SELECT * FROM Persons
SELECT field(s) FROM tableName
SELECT * FROM tableName  WHERE field ='value'
SELECT DISTINCT field FROM tableName  //列出不同的值
SELECT * FROM tableName  WHERE field1='value1' AND/OR field2='value2'
SELECT field(s) FROM tableName  ORDER BY field1 DESC, field2 ASC
SELECT field(s) FROM tableName  WHERE field1 IS (NOT) NULL
INSERT INTO tableName  VALUES('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
INSERT INTO tableName  (field1, field2) VALUES('Wilson', 'Champs-Elysees')
UPDATE tableName  SET field1= 'Fred' WHERE field2= 'Wilson' 
UPDATE tableName  SET field1= 'Zhongshan', field2= 'Nanjing' WHERE field3= 'Wilson'
DELETE FROM tableName  WHERE field1= 'Wilson' 
DELETE * FROM tableName  /  DELETE FROM tableName 
SELECT * FROM tableName  LIMIT 0,5  / SELECT * FROM tableName  WHERE ROWNUM <=5
SELECT TOP 2 * FROM tableName  //选取头2条记录
SELECT TOP 50 PERCENT * FROM tableName  //选取50%的记录

SELECT * FROM tableName  WHERE field1 (NOT) LIKE '%lon%'
SELECT * FROM tableName  WHERE field1 LIKE '_e_orge_'
SELECT * FROM tableName  WHERE field1 LIKE '[AOF]%'//以A，O，F开头的记录
SELECT * FROM tableName  WHERE field1 LIKE '[!AOF]%'//不以A，O，F开头的记录
SELECT * FROM tableName  WHERE field1 IN ('Adams','Carter')
SELECT * FROM tableName  WHERE field1 (NOT) BETWEEN 'Adams' AND 'Carter'
SELECT t.field1, t.field2 FROM tableName  AS t WHERE t.field1 ='Adams'
SELECT field1 AS f1, field2 AS f2 FROM tableName  

SELECT field(s)  FROM table_name1, table_name2 WHERE table_name1.id = table_name2.id 
SELECT field(s) FROM table_name1 INNER JOIN table_name2  ON table_name1.field=table_name2.field ORDER BY table_name1.field
SELECT field(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.field=table_name2.field
SELECT field(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.field=table_name2.field
SELECT field(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.field=table_name2.field
SELECT field(s) FROM table_name1 UNION SELECT field(s) FROM table_name2
SELECT field(s) FROM table_name1 UNION ALL SELECT field(s) FROM table_name2

SELECT * INTO new_table_name FROM old_table_name
SELECT * INTO table_name IN 'Backup.mdb' FROM table_name
SELECT field(s) INTO new_table_name FROM old_table_name WHERE field='Beijing'
SELECT table_name1.field(s),table_name2.field(s) INTO new_table_name FROM table_name1 INNER JOIN table_name2 ON table_name1.id=table_name1.id

CREATE DATABASE database_name
CREATE TABLE table_name(id int NOT NULL UNIQUE PRIMARY KEY,field1 varchar(255),field2 varchar(255),field3 varchar(255),field4 varchar(255))
CREATE TABLE table_name2(id int NOT NULL AUTO_INCREMENT ,fields varchar(255),Id_P int,PRIMARY KEY(id),FOREIGN KEY (Id_P) REFERENCES table_name1(id))
CREATE TABLE table_name(id int NOT NULL,field1 varchar(255),field2 varchar(255),field3 varchar(255),field4 varchar(255) DEFAULT 'Sandnes'
,CHECK (Id_P>0))

CREATE INDEX index_name ON table_name(column_name)
CREATE UNIQUE INDEX index_name ON table_name(column_name)
CREATE INDEX index_name ON table_name(column_name DESC) 
CREATE INDEX index_name ON table_name(field(s))

DROP TABLE table_name
DROP DATABASE database_name
TRUNCATE TABLE table_name
ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name datatype

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM table_name
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM table_name

SELECT AVG(FIRST,LAST,MAX,MIN,SUM,UCASE,LCASE,LEN)(column_name) FROM table_name
SELECT COUNT(*) FROM table_name
SELECT MID(column_name,start[,length]) FROM table_name
SELECT ROUND(column_name,decimals) FROM table_name
SELECT NOW() FROM table_name
SELECT FORMAT(column_name,format) FROM table_name
SELECT ProductName, UnitPrice, FORMAT(NOW(),'YYYY-MM-DD') as PerDate
FROM Products

SELECT Customer,SUM(OrderPrice) FROM table_name GROUP BY Customer
SELECT Customer,OrderDate,SUM(OrderPrice) FROM table_name GROUP BY Customer,OrderDate
SELECT Customer,SUM(OrderPrice) FROM table_name GROUP BY Customer HAVING SUM(OrderPrice)<2000
SELECT Customer,SUM(OrderPrice) FROM table_name WHERE Customer='Bush' OR Customer='Adams' GROUP BY Customer HAVING SUM(OrderPrice)>1500