SQL语句

1、增删改查

/*插入语句*/
insert into table_name（column_name1,column_name2,column_name3,...）
values (value1,value2,value3,...);

/*删除语句*/
DELETE FROM table_name WHERE id = 2;

/*更新语句*/
UPDATE table_name SET column1=value1,column2=value2,...WHERE some_column=some_value;
/*查询语句*/
select * from table_name where column_name = "condition"；
/*在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。DISTINCT 关键词用于返回唯一不同的值*/
SELECT DISTINCT column_name,column_name FROM table_name;
/*
where 条件
"=":等于
"<>":不等于
">":大于
"<":小于
">=":大于等于
"<=":小于等于
"between":在某个范围内
"in":指定针对某个列的多个可能值
"like":搜索某种模式
*/
select column_name1,column_name2,....from table_name where column_name = "condition"；
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
/*
and 与 or条件组合
*/
SELECT * FROM table_name WHERE age > 15 AND (city='hk' OR city='USA');
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');
/*
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字
*/
select column_name1,column_name2 from table_name order by column_name3 asc|desc

2、limit

/*返回前number条数据*/
SELECT column_name(s) FROM table_name LIMIT number;
/*返回number1到number2条数据*/
select column_name(s) from table_name limit number1,number2;
/* 按照column_name降序后的数据返回number1到number2条数据*/
select column_name(s) from table_name order by column_name limit number1,number2;

3、like

/*'G%'、'%k'、'%mm%'
以G开头
以k结尾
包含mm
*/
select column_name(s) from table_name where column_name like 'G%' limit 2;
select column_name(s) from table_name where column_name like '%k' limit 2;
select column_name(s) from table_name where column_name like '%mm%' limit 2;
select column_name(s) from table_name where column_name not like '%mm%' limit 2;

4、别名

SELECT column_name AS a FROM table_name as t;
/*多表*/
SELECT t1.name, t2.url, t2.count, t2.date FROM table1 AS t1, table2 AS t2 WHERE t2.table1_id=t1.id and t1.name="weqwesadd";

5、连接

/*SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段*/
SELECT t1.id, t1.name, t2.count, t2.date FROM table1 as t1 INNER JOIN table2 as t2  ON t1.id=t2.table1_id;
/*INNER JOIN 关键字在表中存在至少一个匹配时返回行。*/
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
/*LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL*/
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
/*RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL*/
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;
/*参考菜鸟教程*/

6、SQL函数

/*AVG() 函数返回数值列的平均值。*/
SELECT AVG(column_name) FROM table_name
/**/
SELECT COUNT(column_name) FROM table_name;
/**/
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
/*在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。*/
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;
/*参考菜鸟教程*/

《!—未完待续—》