常用SQL整理

有同事询问一些很基本的sql，刚好很久没有做过sql方面的开发，所以便整理了一些以前常用的和众总所周知的sql语法。有不对的地方请指摘哦。O(∩_∩)O~

以下，斜体字代表可更改的代入值，如表名列名等，根据实际需求决定。“[]”代表可以有可以没有根据业务需求决定。

数据库操作：

1）创建数据库：CREATE DATABASE db_name;

2）删除数据库：DROP DATABASE db_name;

表操作：

1）创建表：CREATE TABLE table_name(column_name column_type [NOTNULL] [PRIMARY KEY] [DEFAULTdefault_value][COMMENTcomment_value], ...);

2）根据既存表创建新表：CREATE TABLE table_name LIKE old_table_name;

3）删除表：DROP TABLE table_name;

4）修改表：

 ①追加列：ALTER TABLE table_name ADD COLUMN column_name column_type;

 ②追加主键：ALTER TABLE table_name ADD PRIMARY KEY(column_name);/*前提不能有既存主键*/

 ③删除主键：ALTER TABLE table_name DROP PRIMARY KEY(column_name);

索引操作：

1）创建索引：CREATE [UNIQUE] INDEX index_name ON table_name(column_name,...);

2）删除索引：DROP INDEX index_name;/*索引不可更改，如需更改，先删除再重建*/

视图操作：

1）创建视图：CREATE VIEW view_name AS SELECT statement;

2）删除视图：DROP VIEW view_name;

数据操作：

1）查询语句：SELECT result FROM table_name [WHERE condition] [GROUP BY group_column] [HAVING having_value] [UNION union_content] [ORDER BY order_column];

2）插入语句：INSERT INTO table_name(field1_name, field2_name, ...) VALUES(field1_value, field2_value2, ...);

删除语句：DELETE FROM table_name [WHERE condition]; /*没有WHERE条件时代表删除表内全部数据，注意，DELETE后面没有“*”哦。*/

3）更新语句：UPDATE table_name SET field1_name = field1_value [WHEREcondition];

查询时常用语法：

> LIKE：用于查找或模糊查询。例：SELECT * FROM table_name WHERE field_name LIKE'%value%'; /*%代表多个字符，只要field_name字段包含value即符合条件，另外还有‘%value’（以value结尾），‘value%’（以value开头），‘value’（和value相同，同IS）*/

> IN：后面跟的是一个范围，你可以认为是一个子查询临时表，也可以认为是一个临时数组。例：SELECT * FROM table1 WHERE table1.column1 IN (SELECT table2.column2FROMtable2); 或 SELECT* FROMtable1WHEREcolumn1IN (1,2,3);

> EXISTS：同IN。

> NOT：和代码中的非用法类似，譬如放在IN前，NOT IN就代表不在这个范围的。

> AND：同代码，相当于且运算符&&。

> OR：同代码，相当于或运算符||。

> BETWEEN AND：范围，限制查询数据范围，包括边界值。NOT BETWEEN AND不包括，完全相反。

> ASC/DESC：用于排序，和ORDER BY同用，ASC是升序，DESC是降序，默认是ASC，每一个只决定一列。例：SELECT * FROM table_name ORDER BY field1_name,field2_nameDESC;/*优先按照field1_name升序排列，当field1_name相同时，按照field2_name降序排列，可理解为“ORDER BY field1_name ASC, field2_name DESC”*/

> COUNT：查询数量。例：SELECT COUNT(*) FROM table_name;/*有时也用COUNT(1),COUNT(field_name)*/

> AS：起别名。例：SELECT field_name AS name FROM table_name;/*可以理解为取出一个临时表，只有一列，名为name，值和table_name表的field_name列的值一样，类型也一样*/

> SUM：求和。例：SELECT SUM(field_name) AS sum_value FROM table_name;

> AVG：平均值，用法同SUM。

> MAX：最大值，用法同SUM。

> MIN：最小值，用法同SUM。

> GROUP BY：分组。一旦分组后，查询结果只能得到组相关的信息。如count，sum，max，min，avg。

> DISTINCT：去重，涉及大量数据移动，不适合大容量的数据操作。

高级运算符：

1）UNION：组合两个结果集并消去重复行，从而派生出一个结果集。如果是UNION ALL，则不消除重复行。可以理解为A+B或A||B(ALL决定是否去重)

2）EXCEPT：包括所有在结果集1中，但不在结果集2中的行，并消除所有重复行从而派生出一个结果集。如果是EXCEPTALL，则不消除重复行。可以理解为A-B（ALL决定是否去重）

3）INTERSECT：结果集1和结果集2都有的行，并消除所有重复行从而派生出一个结果集。如果是INTERSECTALL，则不消除重复行。可以理解为A交B或A&&B（ALL决定是否去重）

注：使用上面运算符的结果集必须是一致的。（字段相同）

内连接：

INNER JOIN ... ON：内连接，其实就是平时使用的join。结果集包括两个连接表的匹配行。

外连接：

1）LEFT (OUTER) JOIN ... ON：左外连接（左连接），这个比较常用，结果集包括连接表的匹配行，也包括左连接表的所有行。例：SELECTA.column1, A.column2,B.column3,B.column4 FROM A LEFT JOIN B ON A.column1 = B.column1;/*结果集为A表的全部数据和B中column1和A的column1相同的数据*/

2）RIGHT (OUTER) JOIN ... ON：右外连接（右连接），和左连接相反，结果集包括连接表的匹配行和右连接表的所有行。

3）FULL/CROSS (OUTER) JOIN ... ON：全外连接，可以理解为两个结果集的并集。

一些判断条件：

1）1=1、1=2：条件中使用1=1表示全部，1=2表示全部不选，有时会作为判断。

2）CASE WEHN THEN ELSE END：一般用于结果集，我一般使用的方式是判断null，如果为null赋默认值，避免代码中使用出错。例：SELECT m.column1,  CASE WHEN d.column2 IS NULL THEN ''  ELSEd.column2 END ASd_column2 FROM table1 m LEFT JOIN table2 d ON m.column1 = d.column2;

一些常用语句：

SYSDATE() DB所在服务器的时间

md5(value)  MD5加密

AES_ENCRYPT(original_string,key) sql自带加密；

AES_DECRYPT(encrypt_string,key) sql自带解密，key要和加密时使用的key一致；

DATE_SUB(SYSDATE(), INTERVAL 3 MONTH) 三个月前的日期 ，如果现在时间“2016-10-01 18:56:23” 那么得到的就是“2016-07-01 18:56:23”;

group_concat方法和group by共用，可以实现分组排序内容以逗号隔开，再使用substring_index可以选取分组排序的前几个，如下是取分组排序后第一个：

select a, substring_index(group_concat(b order by c DESC,d DESC separator ','),',',1) AS b,substring_index(group_concat(c order by c DESC,d DESC separator ','),',',1) AS c,substring_index(group_concat(d order by c DESC,d DESC separator ','),',',1) AS d fromtable_namegroup by a