-- 创建数据库
CREATE DATABASE db_1;
CREATE SCHEMA 'db_1';

-- 删除
DROP DATABASE db_1;

-- 选择数据库
use db_1;

-- 创建
create table if not exists 表单名(
    列1名 数据类型 [修饰1],
    列2名 数据类型 [修饰2]
);

-- 删除
drop table if exists 表单名;

-- 描述：输出各列的定义信息（包括列名、数据类型、是否允许空值、默认值等）
describe 表单名;

create table if not exists T1(
    primary key (id),
    id int(10) not NULL auto_increment,

    n1 int,
    n2 int(10) zerofill,
    n3 int unsigned comment 'unsigned int',
    n4 decimal(5,2),
    n5 float,

    c1 char(4),
    c2 varchar(4) default null,

    color enum('white', 'red', 'green'),
    func set('eat', 'sleep', 'fly') default 'eat,sleep'
)

insert into table1 (color) values ('red'); -- 不可插入多个
insert into table1 (color) values (1);

insert into table1 (func) values ('eat,sleep,fly'); -- 逗号前后不要留空格

select 'A' as N, 0 as id
union
select 'B' as N, 1 as id

CREATE TABLE Persons (
    ID INT NOT NULL,
    Name VARCHAR(25),
    CONSTRAINT PK_Person PRIMARY KEY (ID)
)

CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderNumber INT NOT NULL,
    PersonID INT,
    CONSTRAINT PK_Order PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(ID)
)

ALTER TABLE Persons
DROP CONSTRAINT PK_Person

ALTER TABLE Orders
ADD

alter table table1 rename to table2;

insert into table1
values
(r11, r12, r13),
(r21, r22, r23);

-- 添加到第一列
alter table table1 add column col_new int(10) not NULL default 0 first; 
-- 添加到特定列之后
alter table table1 add column col_new int(10) not NULL default 0 after col2;
-- 添加到最后
alter table table1 add column col_new int(10) not NULL default 0;

-- 添加一列数据，其数值为已知列的运算结果
-- virtual 表示值不是真实存储的，每次读取动作的时候会重新计算
-- stored 表示值是实际存储的，不会随着其他列值的更新而更新
alter table orders
add column sum_price decimal(9,2) as (unit_price * quantity) [virtual|stored]
after quantity;

-- 整体修改
alter table table1 change column col1 col_new int(10) not NULL default 0;
-- 属性修改（名称不改）
alter table table1 modify column col1 varchar(100);
-- 修改列为主键
alter table table1 add primary key(col1);

alter table table1 drop column col1;

-- 完全使用原表单的部分列构建新表单
create table table2 as (select t1c1, t1c3 from table1);

-- 部分使用原表单的列构建新表单
create table table2(
    t2c1 int(10),
    t2c2 varchar(100)
) select distinct t1c3 as t2c2 from table1; -- t1c3 是 table1 的

select * from Customers
unpivot (
    -- 创建新列: Phone_num
    Phone_num
    -- 创建新列: Phone_type
    -- 删除旧列: Phone1, Phone2，并将它们的列名作为 Phone_type 的值
    -- 遍历这些列名，结合ID，查找旧表中的电话数据填入 Phone_num
    for Phone_type in (Phone1, Phone2)
) as up;

select p.ID, 
    p.Phone1 as Phone1_, 
    p.Phone2 as Phone2_ 
from Customers
pivot (
    -- 删除旧列: Phone_nume
    -- 这里必须是一个聚合函数 Aggregation，例如 sum() avg()
    -- 因此这要求电话号码的格式必须为数字，不能是字符串
    sum(Phone_num)
    -- 创建新列: Phone1, Phone2
    -- 删除旧列: Phone_type
    -- 遍历这些列名，结合ID，查找旧表中的电话数据填入新表
    for Phone_type in (Phone1, Phone2)
) as p;

select a1 from T as t1
union all
select a2 from T as t2

select * from SKU_layersDailyPicking 
into outfile '/tmp/SKU_layersDailyPicking.csv' -- 绝对路径
fields terminated by ','; -- 指定分隔符(默认为 Tab)

-- 插入一条有两列的record
insert into table1 values (column1_value1, column2_value1);

-- 插入多条数据
insert into table1 values
(a11, a12),
(a21, a22);

-- 插入一条仅为指定列赋值的record
insert into table1(col1, col2) values (a31, a32);
-- values中的顺序不需要符合从左到右
insert into table1(col2, col1) values (a42, a41);

delete from table1; -- 删除全部数据记录
delete from table1 where id = 6; -- 将对应id的数据行删除

delete from Drinks
where name in (
    -- MySQL不支持对一个表同时进行查找和删除
    -- 因此需要再套一层中间表
    select t.name from(
        select d1.name 
        from Drinks d1, Drinks d2
        where d1.name = d2.name and d1.manf <> d2.manf
    ) as t
);

set sql_safe_updates = false;

update Drinks set name = 'Cola_2022' where manf = 'M1';

select col1
from table1
(where) condition
(group by) col2
(having) condition
(order by) sorting
(limit)

-- 从表单中选取所有数据
select * from table1;
-- 根据选取的内容创建一张新表单
select * into table2 from table1;

-- 选取用户id和姓
select user_id, last_name from users;
-- 输出用户的全名
select concat(first_name,' ',last_name) as full_name
from users;

-- 统计每个位置有多少用户
select location, count(loaction) from users
group by location;

-- 查看该列有哪些不一样的数值（非重复值）
select distinct col1 from table1;

select O.order_id, U.user_name
from orders O, users U
where O.user_id = U.user_id;

select col from table1 where (限制条件);

Id=9 OR Id=10 AND Price>15
-- 等同于
Id=9 OR (Id=10 AND Price>15)

select
    prod_status, count(*), avg(prod_price)
from products
group by prod_status

-- 修改: 同时能够显示出各组内数据的商品名
select
    GROUP_CONCAT(distinct prod_name), -- distinct 去重
    count(*), avg(prod_price)
from products
group by prod_status

select prod_status, sum(v1) as v1_sum, sum(v2) as v2_sum
from table1 
where v3 >= 0 
group by prod_status
having v1_sum + v2_sum >= 100;

-- 升序排列（默认）
select * from table1 order by col1 (asc);
-- 降序排列，并取前三个最大的
select * from table1 order by col1 desc limit 3;
-- 符合排列，先根据col1升序，在其排序结果内再根据col2降序
select * from table1 order by col1 asc, col2 desc;

-- 取出table1中，生产日期大于2014年的物品中，价格前三贵的物品的名字
select col_name from table1 
where 
    col_date > '2015-1-1' 
order by col_price desc limit 3;

-- 取前n条数据
select col1 from table1 limit n;

-- 取第n+1-n+m+1条数据
select col1 from table1 limit n, m;

-- 查看名字以“A”开头且电话号码以“5”结尾的顾客的信息
select * from (
    select * from Customer 
    where name like 'A%'
) as tem_C
where temp_C.phone like '%5';

-- 查看状态为新上架的商品订单
select * from orders
where product_id in (
    select product_id from products
    where sale_status = 'New'
);

-- 查看售价高于5元的drink信息
select * from Drinks D
where exists (
    select * from Sells
    where drink = D.name and price > 5
);

select Customer_name from Customers
where CustID in (
    select Customer_ID from Orders
    where Order_number in (
        select order_num from OrderItems
        where prod_name = "Toothbrush"
    )
)

( subquery ) UNION ( subquery )
( subquery ) UNION ALL ( subquery ) -- 允许重复元素
( subquery ) INTERSECT ( subquery )
( subquery ) EXCEPT ( subquery )

(select * from Likes) 
INTERSECT(
    select customer, drink from Sells S, Frequents F
    where S.cafe = F.cafe
);

select
    col_name,
    price,
    (case
        when col_price < 20 then '0-20'
        when col_price >= 20 and col_price < 40 then '20-40'
        else 'too expensive'
    end) as price_group
from
    table1;

POW(4, 2);       -- 幂方(平方): 4^2=16
SQRT(4);         -- 开方
MOD(4, 2);       -- 取余: 4%2=0
DIV(4, 2);       -- 整除: 4//2=0
ABS(-1);         -- 绝对值
FORMAT(1.123, 2) -- 保留两位小数，以字符串格式返回
ROUND(1.123, 2)  -- 保留两位小数，以浮点型格式返回

select sum(c1) as sum1, sum(c2) as sum2
from table1;

-- 统计 col1,col2 值相等的数据的数量
select sum(col1 = col2) from table1;

select count(*) from table1;     -- 数据总个数
select count(col1) from table1;  -- 特定列的非空数据个数
select count(distinct col1) from table1;     -- 特定列去除重复值后的数据个数
select count(col1 > 0 or null) from table1;  -- 特定列数值大于0的数据个数

-- 查看5号商品有几种不同的售价
select count(distinct product_price) from products
where product_id = "005"; 

-- 查看每个买家购买的商品种类（根据字符排序）
select user_id, group_concat(distinct product_name order by product_name) 
from orders
group by user_id

DATE_ADD(dt, inerval 1 day); -- 增加一天
LEFT(dt, n);                 -- 通过调整n实现获取年份、月份
YEAR(dt);                    -- 获取年份

DATEDIFF(dt1, dt2);          -- 两个日期之间的天数

DATE_FORMAT(datetime, format); -- 将datetime转化成指定format的string
STR_TO_DATE(str, format);      -- 按照指定format将string转化为datetime
    str_to_date('Thursday, February 17, 2022', '%W, %M %d, %Y')

CHAR_LENGTH(str);              -- 字符串长度
LEFT(str, n);                  -- 取左侧n个字符（可用于datetime）
CONCAT(str1, str2);            -- 合并/连接字符串
REGEXP_LIKE(str, '正则表达式');  -- 判断字符串是否符合正则表达式

IFNULL(col, 0); -- 如果对应列的值为 NULL 则返回0，否则返回该值

select (select ...) as num;

func(col_name) OVER(
    partition by ...
    order by ...
    {rows|range} ... -- window frame
) as new_col_name

SELECT prod_name, price
    rank() OVER(ORDER BY price DESC) as price_rk 
FROM Products

SELECT prod_name, price
    row_number() OVER(ORDER BY price DESC) as price_rk 
FROM Products

SELECT prod_name, class,
    count(*) OVER(PARTITION BY class) as count_num
FROM Products

SELECT prod_name, class, price
    first_value(prod_name) over(
        partition by class
        order by price desc
    ) as high_price_prod
FROM Products

rows between lower_bound and upper_bound

-- window1: previous n rows + current row + m rows following
rows between n preceding and m following 
-- window2: from 1st row to current row
rows between unbounded preceding and current row

select month_num, prod_class, avg(income) over(
    partition by prod_class,
    order by month_num asc,
    rows between 1 preceding and 1 following
) as avg_income
from Sell_Table

select month_num, sum(income) over(
    rows between unbounded preceding and current row
)
from Sell_Table
order by month_num asc

range between lower_bound and upper_bound

CREATE TABLE Drinks (
    name CHAR(20) PRIMARY KEY,
    manf CHAR(20) 
);
CREATE TABLE Sells (
    cafe CHAR(20),
    drink CHAR(20),
    price REAL,
    FOREIGN KEY drink REFERENCES Drinks(name)
        [on delete reference_option]
        [on update reference_option]
);

alter table T1
add constraint constraint_name foreign key (c1)
    references T2 (c2)
    [on delete reference_option]
    [on update reference_option];

RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

CREATE TABLE Sells (
    cafe CHAR(20),
    drink CHAR(20) CHECK (drink IN (SELECT name FROM Drinks)),
    price REAL CHECK (price <= 5.00)
);

select
    user_name, product_name, unit_price, quantity
from 
    orders inner join (users, products)
on
    orders.user_id = users.user_id and orders.product_id = products.product_id;

-- or
select user_name, product_name, unit_price, quantity
from orders 
inner join users on orders.user_id = users.user_id
inner join products on orders.product_id = products.product_id;

select * from orders natural join users;

select * from products left join orders
on products.product_id = orders.product_id;

-- or
select * from products left join orders
using (product_id);

select * from df1 full outer join df2
on df1.col1 = df2.col1

select City, Country from Customers
where Country = "Germany"
UNION
select City, Country from Suppliers
where Country = "Germany"

WITH T1 AS (select distinct c1, c2 from Table_1),
     T2 AS (select distinct c1, c2 from Table_2)
SELECT count(*) FROM T1 INNER JOIN T2
ON T1.c1 = T2.c1 AND T1.c2 = T2.c2;

WITH T1 AS (select distinct c1, c2 from Table_1)
SELECT count(*) FROM T1;

WITH T2 AS (select distinct c1, c2 from Table_2)
SELECT count(*) FROM T2;

IFNULL(col, 0); -- 如果对应列的值为 NULL 则返回0，否则返回该值

select (select ...) as num;

select * from persons where
(age<25) AND (height > 6 OR weight < 150)

select(
    select price from Products
    order by price desc
    limit 1, 1
) as SecondHighestPrice;

CREATE VIEW CanDrink AS
SELECT customer, drink
FROM Frequents, Sells
WHERE Frequents.cafe = Sells.cafe;

create materialized view view_table as ...

delimiter //
create procedure proc_name
    (proc_parameter, ...)
begin
    -- 函数内部的代码块
end//
delimiter ;

DELIMITER //
CREATE PROCEDURE GetTypeNumber (IN M VARCHAR(30))
BEGIN
    DECLARE typeNumber INT DEFAULT 0;

    SELECT COUNT(*) 
    INTO typeNumber
    FROM Drinks
    WHERE manf = M;

    SELECT typeNumber;
END //
DELIMITER ;

--查看生产商M1所生产的饮料的种类数
CALL GetTypeNumber('M1');

DELIMITER //
CREATE PROCEDURE GetTypeNumber (
    IN M VARCHAR(30), 
    OUT typeNumber INT)
BEGIN
    SELECT COUNT(*) 
    INTO typeNumber
    FROM Drinks
    WHERE manf = M;
END //
DELIMITER ;

-- 查看生产商M1所生产的饮料的种类数
CALL GetTypeNumber('M1', @typeNumber);
-- 还可以随时查看结果
SELECT @typeNumber;

SELECT 
    product_name, price
FROM
    products
WHERE
    price = (SELECT MIN(price) FROM products);