2017/2/13

查询：

SELECT * FROM c2_company;

唯一值：

SELECT DISTINCT company_type FROM c2_company;

更精确的查找：

SELECT * FROM c2_company c WHERE c.`COMPANY_NAME`='刘欢的de';

AND:

SELECT * FROM c2_company c WHERE c.`COMPANY_NAME`='刘欢的de' AND c.`COMPANY_TYPE`='T';

OR:

SELECT * FROM c2_company c WHERE c.`COMPANY_NAME`='刘欢的de' OR C.`COMPANY_TYPE`='R';

ORDER BY 升序：

SELECT * FROM c2_company ORDER BY parent_id;

DESC 降序：

SELECT * FROM c2_company ORDER BY parent_id DESC;

UPDATE 修改：

UPDATE c2_company SET company_name ='欢欢波波' WHERE company_name ='刘欢的de';

DELETE 删除：

DELETE FROM c2_company WHERE company_name='bobo';

INSERT INTO 插入：

INSERT INTO c2_company(id,company_name) VALUES (6,huanhuan);

LIKE 模糊查询：

SELECT * FROM c2_company WHERE company_name LIKE '欢%';

SELECT * FROM c2_company WHERE company_name LIKE '%商';

SELECT * FROM c2_company WHERE company_name LIKE '%小玩意%';

SELECT * FROM c2_company WHERE company_name NOT LIKE '%小玩意%';

TOP 摘取：

SELECT top 5 * FROM c2_company;

SELECT top 50 percent * FROM c2_comapny;

MYSQL中语法 LIMIT ：

SELECT * FROM c2_company LIMIT 5;

ORACLE中语法 ：

SELECT * FROM c2_company WHERE rownum <='6';

IN 在where子句子中规定多个值：

SELECT * FROM c2_company WHERE company_type IN ('T','R');

BETWEEN ^ AND ^  选取这个范围内的值：

SELECT * FROM c2_company WHERE id BETWEEN 2 AND 40;

AS

alias 别名：列

SELECT id AS '排序',company_name AS'公司名称' FROM c2_company;

JOIN 通过两个或多个表相同的部分，把表结合起来：

通过两个表相同的部分进行结合：

SELECT c2_company.`COMPANY_CODE`,c2_store.`BACK_NUMBER`FROM c2_company,c2_store

WHERE c2_company.`AREA_ID_FK`=c2_store.`AREA_ID_FK`;

INNER JOIN ^ ON 表中至少有一个匹配：

SELECT c2_company.`COMPANY_CODE`,c2_store.`COUNTER_NUMBER` FROM c2_company INNER JOIN c2_store ON c2_company.`AREA_ID_FK`=c2_store.`AREA_ID_FK`;

LEFT JOIN ^ ON 从左表有符合条件的行，即使右表没有符合条件的行。

SELECT c2_company.`COMPANY_CODE`,c2_store.`COUNTER_NUMBER` FROM c2_company LEFT JOIN c2_store ON c2_company.`AREA_ID_FK`=c2_store.`AREA_ID_FK`;

RIGHT JOIN ^ ON  从右表有符合条件的行，即使左表没有符合条件的行。

SELECT c2_company.`COMPANY_CODE`,c2_store.`COUNTER_NUMBER` FROM c2_company RIGHT JOIN c2_store ON c2_company.`AREA_ID_FK`=c2_store.`AREA_ID_FK`;

FULL JOIN ^ON  只要某个表存在匹配

SELECT c2_company.`COMPANY_CODE`,c2_store.`COUNTER_NUMBER` FROM c2_company FULL JOIN c2_store ON c2_company.`AREA_ID_FK`=c2_store.`AREA_ID_FK`;

UNION 合并两个或多个 SELECT 语句的结果集

UNION 合并不重复：

SELECT E_Name FROM Employees_China

UNION

SELECT E_Name FROM Employees_USA;

UNION ALL 合并所有，会重复：

SELECT E_Name FROM Employees_China

UNION ALL

SELECT E_Name FROM Employees_USA;