-- table_1中有id,age; table_2中有id，sex。想取出id,age,sex 三列信息，-- 将table_1,table_2 根据主键id连接起来
select a.id,a.age,b.sex from (select id,age from table_1) a join (select id, sex from table_2) b on a.id =b.id
========删除重复的行
DELETE FROM Products P1
WHERE id < ( SELECT MAX(P2.id)
FROM Products P2
WHERE P1.name = P2.name
AND P1.price = P2.price );
=========
SELECT col_1, col_2, col_3,
COUNT(*)
FROM tbl_A
WHERE col_1 = 'a'
AND col_2 = ( SELECT MAX(col_2)
FROM tbl_B
WHERE col_3 = 100 )
GROUP BY col_1, col_2, col_3
============ EXISTS的用法
SELECT *
FROM Class_A A
WHERE EXISTS
(SELECT *
FROM Class_B B
WHERE A.id = B.id);
=== in 用 join 替换
SELECT * FROM Class_A WHERE id IN (SELECT id FROM CLASS_B);
替换上面的语句：
SELECT A.id, A.name FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;
======使用 EXISTS 代表 DISTINCT
如何找出有销售记录的商品，使用如下 DISTINCT 可以：
SELECT DISTINCT I.item_no FROM Items I INNER JOIN SalesHistory SH ON I. item_no = SH. item_no;
用EXIST实现:
SELECT item_no FROM Items I WHERE EXISTS (SELECT * FROM SalesHistory SH WHERE I.item_no = SH.item_no);==========
SELECT * FROM Addresses1 A1 WHERE id || state || city IN (SELECT id || state|| city FROM Addresses2 A2);
==========
对当前工资为 1 万以上的员工，降薪 10%。对当前工资低于 1 万的员工，加薪 20%
UPDATE Salaries
SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
WHEN salary < 10000 THEN salary * 1.2
ELSE salary END;
================想知道有多少去重的用户数
select count(*) from(select distinct id from table_1) tb
================将数值型的变量转化为分类型的变量？—— case when 条件函数
-- 收入区间分组
select id,(case when CAST(salary as float)<50000 Then '0-5万'when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10万'when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20万'when CAST(salary as float)>200000 then '20万以上'else NULL end from table_1;
==============concat( A, B...)返回将A和B按顺序连接在一起的字符串
select concat('www','.iteblog','.com') fromiteblog;--得到 www.iteblog.com
==============substr（str,0,len) 截取字符串从0位开始的长度为len个字符。
select substr('abcde',3,2) fromiteblog;-- 得到cd
==============