唯一、去重 DISTINCT

SELECT DISTINCT country FROM Websites;

where 条件判断

Select * from emp where empno=7900;
Select * from emp where ename='SMITH';
Select * from emp where sal > 2000 and sal < 3000; //优先级 () not and or
Select * from Websites where alexa > 15 and (country='CN' or country='USA');
select * from emp where not sal > 1500;
Select * from emp where comm is null;
Select * from emp where sal between 1500 and 3000;
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H'; // 字符串比较下between
Select * from emp where sal in (5000,3000,1500);
Select * from emp where ename like 'M%';        // %替代0个或多个字符, _替代一个字符
Select * from emp where ename like '_M%';   
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开头， not regexp 也可表示

IF IFNULL 判断流程控制

IF(expr1,expr2,expr3) // 如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
update Salary set sex=if(sex="f","m","f"); // 把 sex调换
select employee_id,salary*if(employee_id%2!=0 and name not like "M%", 1, 0) as bonus
from Employees

IFNULL(expr1,expr2) //假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

一些函数，类似于存储过程，在数据库服务器上内定的一些功能函数

concat(s1, s2)
group_concat(select_result_list separator ',' )
upper(s)
lower(s) 
left(s, 1) // 字符串首字符
substring(name, 2) // 第二个字符起的子字符串

排序

SELECT * FROM Websites ORDER BY alexa; // 默认 ASC
SELECT * FROM Websites ORDER BY alexa DESC;
SELECT * FROM Websites ORDER BY country ASC, alexa DESC;

插入 删除 更新

INSERT INTO Websites VALUES ('百度','https://www.baidu.com/','4','CN');
INSERT INTO Websites (name, url, alexa, country) VALUES ('百度','https://www.baidu.com/','4','CN');
INSERT INTO Websites (name, url, country) VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');
UPDATE Websites  SET alexa='5000', country='USA'  WHERE name='菜鸟教程';
UPDATE Websites SET alexa='5000', country='USA'  // update 没有(a,b,c)value(1,2,3)的搞法，要一个个赋值
DELETE FROM Websites WHERE name='Facebook' AND country='USA';

前N条 N%

select * from Websites LIMIT 2;
select top 50 percent * from Websites;
select top 5 * from table order by id desc；

别名

SELECT w.name, w.url, a.count, a.date 
FROM Websites AS w, access_log AS a 
WHERE a.site_id=w.id and w.name="菜鸟教程";

SELECT Websites.name, Websites.url, access_log.count, access_log.date
FROM Websites, access_log
WHERE Websites.id=access_log.site_id and Websites.name="菜鸟教程";

JOIN, 细分了几种多表联合查询的相连方式
联合查询两张表时，基于某一个共同意义的字段，将两表的数据行合并。使用最多的、默认情况下的是查询交集inner join。
要解决的问题是，多对应，无对应情况下，保留在查询结果中的是哪一部分，也就是集合交集、并集、差集

sql-join.png

SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a INNER JOIN mount_info b ON a.role_id = b.role_id;
SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a, mount_info b WHERE a.role_id = b.role_id; // 两句的意思一样，查询两个表中role_id相同的项。
inner join下，满足同一交叉条件下的数量在表A中，role_id=1 的有2条，表B中role_id=1的有3条，那么结果中笛卡尔积，就有6条role_id=1的项。
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
+---------+------------+----------+------------+


SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a LEFT JOIN mount_info b ON a.role_id = b.role_id; // left join下保留了A表的全部项，A中role_id在B中对应找到的，列出笛卡尔积结果；A中role_id未在B中找到对应的项，也列出在结果中但属于B的字段部分是NULL
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
|       2 | paladin    | alliance | NULL       |
|       3 | rogue      | Horde    | NULL       |
+---------+------------+----------+------------+

SELECT a.role_id, a.occupation, a.camp, b.mount_name FROM roles a RIGHT JOIN mount_info b ON a.role_id = b.role_id; // right join 保留了B表的全部项
+---------+------------+----------+------------+
| role_id | occupation | camp     | mount_name |
+---------+------------+----------+------------+
|       1 | warrior    | alliance | horse      |
|       1 | warrior    | alliance | sheep      |
|    NULL | NULL       | NULL     | sheep      |
+---------+------------+----------+------------+

group by , having, exists

SELECT column_name, aggregate_function(column_name) FROM table_name
WHERE column_name operator value GROUP BY column_name;
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log 
GROUP BY site_id; // 按照site_id聚类结果下，统计每一类的总和 统计函数常用的有max min avg sum mid count first等

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200; // 聚类中总和超过200的（where无法对聚类函数使用）

SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

max min avg sum mid count

SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);
SELECT COUNT(count) AS nums FROM access_log WHERE site_id=3;
SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;
SELECT MAX(alexa) AS max_alexa FROM Websites;
SELECT SUM(count) AS nums FROM access_log;

表操作

DROP TABLE table_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 MODIFY COLUMN column_name datatype

综合类型，嵌套查询，深层嵌套

SELECT *
   FROM CUSTOMERS
   WHERE SALARY > (SELECT SALARY
         FROM CUSTOMERS
         WHERE NAME='Komal'); // 查询工资高于Komal的人

SQL> SELECT *
   FROM CUSTOMERS
   WHERE ID IN (SELECT ID
         FROM CUSTOMERS
         WHERE SALARY > 4500)
   AND AGE >= 25; // SALARY>4500且age>=25的人