常用sql语句

//查询
"SELECT viewNumber from bloginfo WHERE uid = "+ uid +" and blogId = "+ blogId;

//查询（忽略重复项）
"SELECT DISTINCT country FROM Websites";

//更改
"UPDATE bloginfo SET viewNumber = " + a +" WHERE uid = "+ uid +" and blogId = "+ blogId; 

//删除
DELETE FROM Websites WHERE name='百度' AND country='CN';

//添加
INSERT INTO Websites (name, url, country) VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');

//排序
SELECT * FROM Websites ORDER BY alexa DESC;（降序，不写DESC则默认升序）
SELECT * FROM Websites ORDER BY country,alexa;（多列排序）

//查询前几个
SELECT TOP number|percent column_name(s) FROM table_name;（SQL Server / MS Access）
SELECT column_name(s) FROM table_name LIMIT number;（MySQL）
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;（Oracle）

//模糊查询
SELECT * FROM Websites WHERE name LIKE 'G%';（以G开头）
SELECT * FROM Websites WHERE name LIKE '%G';（以G结尾）
SELECT * FROM Websites WHERE name LIKE '%G%';（包含G）
SELECT * FROM Websites WHERE name NOT LIKE '%G';（不包含G）
SELECT * FROM Websites WHERE name LIKE '_a;(两位且结尾是a）
SELECT * FROM Websites WHERE name LIKE 'a_';（两位且开头是a）
SELECT * FROM Websites WHERE name LIKE '_a_';（三位且中间是a）
SELECT * FROM Websites WHERE name REGEXP '^[GFs]';（以 "G"、"F" 或 "s" 开始）
SELECT * FROM Websites WHERE name REGEXP '^[A-H]';（以 A 到 H 字母开头）
SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';（不以 A 到 H 字母开头）

//in
SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');
SELECT * FROM Websites WHERE not name IN ('Google','菜鸟教程');（不等于）

//给定范围查询（可以是数值、文本或者日期）
SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;（介于 1 和 20 之间）
SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;（不在1 和 20 之间）
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';（以介于 'A' 和 'H' 之间字母开始）

//别名
SELECT column_name(s) FROM table_name AS alias_name;
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;（把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名）

//平均数
SELECT AVG(count) AS CountAverage FROM access_log;

//计数
SELECT COUNT(DISTINCT column_name) FROM table_name;（返回指定列的不同值的数目）
SELECT COUNT(column_name) FROM table_name;（返回指定列的值的数目（NULL 不计入））

//时间
SELECT name, url, Now() AS date FROM Websites;
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date FROM Websites;（日期格式化为 YYYY-MM-DD）

//