USE carash;

# 表示使用名为carash的数据库

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM 表名;

SELECT 属性名 FROM 表名;

# 会返回不重复的值
SELECT DISTINCT 属性名 FROM 表名;

# 返回值不能多于五行
SELECT 属性名 FROM 表名 LIMIT 5;

# 返回值按属性1升序排序，属性1相同的再按属性2升序排序
SELECT 属性1,属性2 FROM 表名 ORDER BY 属性1,属性2;

# 返回值不多于五行、按降序排序
SELECT 属性1,属性2 FROM 表名 ORDER BY 属性1,属性2 DESC LIMIT 5;

# 第二大的属性1，OFFSET 1 表示忽略掉第一个符合的记录
# LIMIT 1 OFFSET 1可以写成 LIMIT 1,1
# 注意LIMIT后面不能有运算，加减也不行
SELECT DISTINCT 属性1 FROM 表名 ORDER BY 属性1 DESC LIMIT 1 OFFSET 1

# 返回属性2==xx的属性1，并按属性3升序排序
SELECT 属性1 FORM 表名 WHERE 属性2 = xx ORDER BY 属性3;

# 返回属性2在[XX,XX]之间的属性1，并按属性3升序排序
SELECT 属性1 FORM 表名 WHERE 属性2 BETWEEN xx AND XX ORDER BY 属性3;

# 返回属性2等于xx1或xx2的属性1
SELECT 属性1 FORM 表名 WHERE 属性2 IN (xx1,xx2);

# 返回属性2为空值的属性1
SELECT 属性1 FORM 表名 WHERE 属性2 IS NULL;

# 返回属性2不为空值的属性1
SELECT 属性1 FORM 表名 WHERE 属性2 IS NOT NULL;

# 返回属性2符合通配符表达式的属性1，%表示任意个任意字符，_表示一个任意字符
SELECT 属性1 FORM 表名 WHERE 属性2 LIKE '通配符表达式';

# 返回属性2符合正则表达式的属性1，.表示一个任意字符，[123]和[1-3]代表含义相同表示'1'或'2'或'3'其中一个，^、$表示串开始处的定位符结束符，若用'^...$'的形式则与LIKE效果等同
# LIKE要整个串符合才匹配成功，REGEXP只要整个串的部分子串符合计算匹配成功
SELECT 属性1 FORM 表名 WHERE 属性2 REGEXP '正则表达式';

# 表示返回一个chs前有一个长度为1到5的字符串的属性1
SELECT 属性1 FORM表名 WHERE 属性2 REGEXP '[1-5]chs'

# 将满足条件并经过函数计算的属性1赋值给新属性并返回，不加WHERE的话会计算所有属性1有值的行
# AVG(),COUNT(),MAX(),MIN(),SUM()
SELECT 函数(属性1) AS 新属性名 FROM 表名 WHERE 条件;

# 会返回以属性1为分组依据，返回每个不同属性1出现的行数的表
# 在GROUP BY后面加HAVING 条件，会只返回符合条件的分组
SELECT COUNT(属性1) AS 新属性名 FORM 表名 GROUP BY 属性1

# 表示SELECT DISTINCT ... OFFSET 1有符合要求记录时就返回符合记录，没有符合的就返回NULL
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary

# 在BEGIN下线DECLARE xx 数据类型， 再另起一行SET
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE m int;
    SET m = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET m), NULL)
  );
END

# 遇到相同的会写成1,1,3形式
SELECT -, rank() OVER(ORDER BY number ) AS rank FROM num
# 遇到相同的会写成1,1,2形式
SELECT -, dense_rank() OVER(ORDER BY number ) AS dense_rank FROM num
# 遇到相同的会写成1,2,3形式
SELECT -, row_number() OVER(ORDER BY number ) AS row_num FROM num

select distinct l1.Num as ConsecutiveNums 
from Logs l1, Logs l2, Logs l3 
where l1.Id = l2.Id+1 and l2.Id = l3.Id+1 and l1.Num = l2.Num and l1.Num = l3.Num;

# 以上语句用于找出在Logs表中Id连续的Num相同的最少出现三次的项

select cust_name, cust_contact
from custiomers
where cust_id in (select cust_id from orders where order_num = 1001);

# 以上语句表示从customers表中返回cust_id等于在orders表中的order_num = 1001的cust_id的cust_name、cust_contact

select l2.Name as Department, l1.Name as Employee, l1.Salary  
from Employee l1, Department l2
where l1.DepartmentId = l2.Id and (l1.DepartmentId , l1.Salary) in 
(select DepartmentId, max(Salary) from Employee group by DepartmentId);

# 这种where后面不只一个属性的子查询方式，要像(l1.DepartmentId , l1.Salary)一样把两个属性用括号括起来

create procedure-创建存储过程
exec procedure-执行存储过程
alter procedure-修改存储过程
drop procedure-删除存储过程

# datediff(l1.recordDate, l2.recordDate) = 1表示l2的日期要比l1的日期少一天，diff等于l1-l2
select l1.id from Weather l1, Weather l2 where l1.Temperature > l2.Temperature and datediff(l1.recordDate, l2.recordDate) = 1;

1.select
2.from
3.where
4.group by
5.order by
6.limit

select class 
from 
(select class, count(distinct student) as num from courses group by class) as temple 
where num >= 5; 

# 构建了一个有着class属性和num属性作为临时表temple
# num变量是按class相同的分一组，每一组分别计算学生数量

update 表名 set email = "southern.emperor@outlook.com" where name = "Southern Emperor";

where bonus is null;

select - from Orders group by customer_id  order by count(order_id) DESC limit 1;

select x, y, z, case when x+y>z and x+z>y and y+z>x then 'YES' else 'NO' from Triangle

SELECT -
WHERE LENGTH(xx) = 2; 

WITH old_table AS (
    SELECT  stat_time_day,
            event_type,
            ad_id,
            advertiser_id,
            stat_time_hour,
            event_source_type,
            apple_app_skan_event_cnt,
            mmp_retargeting_attribute_event_cnt,
            mmp_repetitive_non_retargeting_attribute_event_cnt,
            mmp_repetitive_retargeting_attribute_event_cnt,
            mmp_total_event_cnt,
            mmp_non_retargeting_attribute_event_cnt,
            event_source_id,
            CASE WHEN event_source_type = 1 THEN CAST(event_source_id AS BIGINT)
                 ELSE NULL
            END AS tiktok_app_id
    FROM    ad_app.dwa_event_manager_measurement_metric_daily
    WHERE   p_date = '${date}'
    AND     p_date < '20220228'
)
INSERT OVERWRITE TABLE addrd_test.dwa_event_manager_measurement_metric_daily_v2_test PARTITION (p_date = '${date}')
SELECT  `stat_time_day`,
        `event_type`,
        `ad_id`,
        `advertiser_id`,
        `stat_time_hour`,
        `event_source_type`,
        SUM(COALESCE(`apple_app_skan_event_cnt`, 0)) AS apple_app_skan_event_cnt,
        SUM(COALESCE(`mmp_retargeting_attribute_event_cnt`, 0)) AS mmp_retargeting_attribute_event_cnt,
        SUM(COALESCE(`mmp_repetitive_non_retargeting_attribute_event_cnt`, 0)) AS mmp_repetitive_non_retargeting_attribute_event_cnt,
        SUM(COALESCE(`mmp_repetitive_retargeting_attribute_event_cnt`, 0)) AS mmp_repetitive_retargeting_attribute_event_cnt,
        SUM(COALESCE(`mmp_total_event_cnt`, 0)) AS mmp_total_event_cnt,
        SUM(COALESCE(`mmp_non_retargeting_attribute_event_cnt`, 0)) AS mmp_non_retargeting_attribute_event_cnt,
        `event_source_id`,
        SUM(COALESCE(`sdkapi_non_retargeting_attribute_event_cnt`, 0)) AS sdkapi_non_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_repetitive_non_retargeting_attribute_event_cnt`, 0)) AS sdkapi_repetitive_non_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_retargeting_attribute_event_cnt`, 0)) AS sdkapi_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_repetitive_retargeting_attribute_event_cnt`, 0)) AS sdkapi_repetitive_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_total_event_cnt`, 0)) AS sdkapi_total_event_cnt,
        SUM(COALESCE(`web_adv_browser_attribute_event_cnt`, 0)) AS web_adv_browser_attribute_event_cnt,
        SUM(COALESCE(`web_adv_server_attribute_event_cnt`, 0)) AS web_adv_server_attribute_event_cnt,
        SUM(COALESCE(`web_adv_duplicated_attribute_event_cnt`, 0)) AS web_adv_duplicated_attribute_event_cnt,
        SUM(COALESCE(`web_adv_browser_total_event_cnt`, 0)) AS web_adv_browser_total_event_cnt,
        SUM(COALESCE(`web_adv_server_total_event_cnt`, 0)) AS web_adv_server_total_event_cnt,
        SUM(COALESCE(`web_adv_duplicated_total_event_cnt`, 0)) AS web_adv_duplicated_total_event_cnt,
        event_source_id_split,
        UNIX_TIMESTAMP(stat_time_hour) AS last_record_time,
        SUM(COALESCE(offline_upload_event_cnt, 0)) AS offline_upload_event_cnt,
        SUM(COALESCE(offline_matched_event_cnt, 0)) AS offline_matched_event_cnt,
        SUM(COALESCE(offline_total_event_cnt, 0)) AS offline_total_event_cnt
FROM    ad_app.dwd_event_manager_measurement_metric_detail_etl_hourly_v2
WHERE   (p_date = '${date}' OR (p_date = '${date+1}' AND hour < '8'))
AND     stat_time_day = '${DATE} 00:00:00'
AND     p_date >= '20220228'
GROUP BY
        `stat_time_day`,
        `event_type`,
        `ad_id`,
        `advertiser_id`,
        `stat_time_hour`,
        `event_source_type`,
        `event_source_id`,
        `event_source_id_split`,
        `last_record_time`
UNION ALL
SELECT  stat_time_day,
        event_type,
        ad_id,
        advertiser_id,
        stat_time_hour,
        event_source_type,
        apple_app_skan_event_cnt,
        mmp_retargeting_attribute_event_cnt,
        mmp_repetitive_non_retargeting_attribute_event_cnt,
        mmp_repetitive_retargeting_attribute_event_cnt,
        mmp_total_event_cnt,
        mmp_non_retargeting_attribute_event_cnt,
        event_source_id,
        0 AS sdkapi_non_retargeting_attribute_event_cnt,
        0 AS sdkapi_repetitive_non_retargeting_attribute_event_cnt,
        0 AS sdkapi_retargeting_attribute_event_cnt,
        0 AS sdkapi_repetitive_retargeting_attribute_event_cnt,
        0 AS sdkapi_total_event_cnt,
        0 AS web_adv_browser_attribute_event_cnt,
        0 AS web_adv_server_attribute_event_cnt,
        0 AS web_adv_duplicated_attribute_event_cnt,
        0 AS web_adv_browser_total_event_cnt,
        0 AS web_adv_server_total_event_cnt,
        0 AS web_adv_duplicated_total_event_cnt,
        '' AS event_source_id_split,
        UNIX_TIMESTAMP(stat_time_hour) AS last_record_time,
        0 AS offline_upload_event_cnt,
        0 AS offline_matched_event_cnt,
        0 AS offline_total_event_cnt
FROM    old_table

CREATE VIEW v_teachers AS
SELECT - FROM teachers 
WHERE age < 30
WITH CHECK OPTION;

# 意为从teachers表选取所有age>30的记录创建一个视图
# WITH CHECK OPTION表示视图可更新，即原表teachers新插入age<30的记录后，视图中也会相应更新

### 以下语句得到的是ad_id相等的

WITH third_party AS (
    SELECT  SUM(value1) AS form_third_party, ad_id
    FROM    ad_dm.ad_stats_hourly
    WHERE   date = '${date}'
    AND hour = '${hour}'
    group by ad_id
)
SELECT 
    t1.advertiser_id,
    t1.campaign_id,
    t2.form_third_party
FROM ad_dwd.dwd_overseas_overture_event_log_hourly AS t1
LEFT JOIN
third_party AS t2
ON t1.ad_id = t2.ad_id

delete from Person where Id not in (select min(Id) as Id from Person group by Email);

delete l1 from Person l1, Person l2 where l1.Email = l2.Email and l1.Id > l2.Id;