sudo service mysql status

mysql -u root -p

CREATE DATABASE my_database_name;

DROP DATABASE my_database_name;

USE my_database_name;

CREATE USER 'springuser'@'%' IDENTIFIED BY '123456'; -- Create the user
GRANT ALL ON db_example.* TO 'springuser'@'%'; -- Give all privileges to the new user 
DROP USER 'springuser'@'%';  -- Delete a user; 

SELECT user FROM mysql.user;
DROP user 'springuser'@'%';

CREATE TABLE mytable (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL,
email varchar(255));  -- create a table

CREATE TABLE mynewtable AS 
SELECT column1, column2 
FROM mytable
WHERE column1 = 'W';  -- create a table from an old table

DROP TABLE mytable; -- delete the table
TRUNCATE TABLE mytable;  -- clear the table

ALTER TABLE mytable
ADD COLUMN gender varchar(255) NOT NULL DEFAULT 'male'
AFTER name; -- add a new column

ALTER TABLE mytable
DROP COLUMN gender;  -- drop a column

ALTER TABLE mytable
MODIFY COLUMN gender char(1);  -- change data type of a column

CREATE TABLE mytable (
id int NOT NULL,
name varchar(255),
email varchar(255),
CONSTRAINT UC_mytable UNIQUE (id, name));  -- use constraint when creating a table

ALTER TABLE mytable
ADD CONSTRAINT UC_mytable UNIQUE (id, name);  -- use constraint when altering a table

ALTER TABLE mytable
DROP INDEX UC_mytable;

CREATE TABLE mytable (
id int NOT NULL,
name varchar(255) NOT NULL,
email varchar(255),
CONSTRAINT UC_mytable PRIMARY KEY (id, name));  -- use constraint when creating a table

ALTER TABLE mytable
ADD CONSTRAINT PK_mytable PRIMARY KEY (id, name);  -- use constraint when altering a table

ALTER TABLE mytable
DROP PRIMARY KEY;

CREATE TABLE table1 (
id INT NOT NULL,
name VARCHAR(225) NOT NULL,
PRIMARY KEY (id));

CREATE TABLE table2 (
id INT NOT NULL,
email VARCHAR(255) NOR NULL,
FOREIGN KEY (id) REFERENCES table1(id));  -- foreign key in table2 pointing to primary key in table1.

ALTER TABLE table
ADD CONSTRAINT FK_table2 
FOREIGN KEY (id) REFERENCES table1(id);  -- add a foreign key

ALTER TABLE table2
DROP FOREIGN KEY FK_mytable2;  -- drop a foreign key

CREATE TABLE mytable (
id int NOT NULL,
name VARCHAR(255),
age int,
CHECK (age>=18));  -- define a CHECK CONSTRAINT without name when creating a table

CREATE TABLE mytable (
id int NOT NULL,
name VARCHAR(255),
age int,
CONSTRAINT CHK_mytable CHECK (age>=18 AND id<10));  -- define a CHECK CONSTRAINT with a name when creating a table

ALTER TABLE mytable
ADD CHECK (age>=18);
--or
ALTER TABLE mytable
ADD CONSTRAINT CHK_mytable CHECK (age>=18 AND id<10);

ALTER TABLE mytable
DROP CHECK CHK_mytable;

/*INDEX is a kind of data structure which is stored on disk.
It will facilitate: point query, range query, ordering and join. There is no need to scan the whole table for indexed column.
Shortcomings: modifying an indexed column will be slower*/
CREATE INDEX idx_email ON mytable (email);

CREATE TABLE mytable (
id int NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY (id));

INSERT INTO mytable (name)
VALUES ('ZT');  -- I don't need to INSERT INTO the AUTO_INCREMENT column.

SELECT DISTINCT * FROM mytable; -- select different rows.

-- ORDER BY clause
-- DESC clause means to sort reversely
SELECT column1, column2, column3
FROM myBD
ORDER BY column2 DESC, column3 ASC;

-- WHERE clause
-- Some WHERE clause operators: >, >=, !=, =
-- ORDER BY clause should be placed behind WHERE
SELECT column1, column2, column3
FROM mytable
WHERE column1 BETWEEN 1 AND 10;

SELECT mytable, column2, column3
FROM myBD
WHERE column1 IS NULL AND column2 IS NOT NULL;

-- When don't use "()", AND is prior to OR
SELECT column1, column2, column3
FROM mytable
WHERE column1 IN (1, 0) OR column2 = 0 AND NOT column3 < 100;

SELECT column1, column2, column3
FROM mytable1
WHERE column1 IN (SELECT column1 FROM mytable2);

SELECT * FROM my_table_name
WHERE column1='A'
LIMIT 3;  -- Show the first three records.

SELECT * FROM my_table_name
WHERE column1='A'
LIMIT 3 OFFSET 3;  -- Show the records 4 5 6.

-- % represents a set of character including " " and "".
-- _ represents one character including " ".
-- A returned example: 'Fish bean bay toy'
-- Keep in mind that pattern doesn't split strings. It just match and return the whole content.
SELECT column1, column2, column3
FROM myBD
WHERE column1 LIKE 'F_sh%';

SELECT column1, column2, column3
FROM myBD
WHERE column1 LIKE '%_%_%';  -- Match value that contains at least two characters.

-- I can also use regular expression through clause REGEXP
SELECT column1, column2, column3
FROM myBD
WHERE column1 REGEXP '^[FK]';

SELECT t1.column1, t2.column2
FROM table1 AS t1, table2 AS t2;

SELECT MIN(column1)  -- Return the minimum value of column1.
FROM my_table_name
WHERE column3 = 'W';

SELECT MAX(column1) AS maximal_column1
FROM my_table_name
WHERE column3 = 'W';

SELECT AVG(column1), SUM(column1)
FROM my_table_name
WHERE column3 = 'W';

SELECT COUNT(column1)
FROM my_table_name
WHERE column3 = 'W';

DROP FUNCTION IF EXISTS getNthHighestSalary;

DELIMITER $$
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
DETERMINISTIC  -- 明确指定函数为确定性函数
BEGIN
    DECLARE offset_val INT;
    SET offset_val = N - 1;
    RETURN (
        SELECT (
            SELECT DISTINCT Chinese 
            FROM student
            ORDER BY Chinese DESC
            LIMIT 1 OFFSET offset_val
        )
    );
END $$
DELIMITER ;

SELECT * 
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.column1=t2.column1;

SELECT * 
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.column1=t2.column1;

SELECT * 
FROM table1 AS t1 RIGHT JOIN table2 AS t2 ON t1.column1=t2.column1;

SELECT * 
FROM table1 CROSS JOIN table2;

SELECT column1 FROM table1
UNION
SELECT column2 FROM table2;  -- union distinct values

SELECT column1 FROM table1
UNION ALL
SELECT column2 FROM table2;  -- union all values

SELECT ProductName 
FROM Products
WHERE EXISTS (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

SELECT ProductName 
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
-- For each ProductID, if it equals to one of the ProductID from OrderDetails.

SELECT ProductName 
FROM Products
WHERE ProductID > ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
-- For each ProductID, if it is larger than all of the ProductID from OrderDetails.

SELECT  *
FROM    mytable
LATERAL VIEW EXPLODE(SPLIT(hobbies, ','));

CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (column1, column2, ...);
-- 查看索引（可在下面的SQL代码后加上"\G"以纵向展示结果）
-- 默认使用FULLTEXT为索引。
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX index_name ON table_name;

SHOW GLOBAL STATUS LIKE 'Com_______';  -- 七个下划线

-- 业务场景下更推荐使用>=（我目前还不理解这是为什么）
SELECT * FROM student WHERE age>30 AND name='Jack';

-- 查看MySQL慢查询日志是否打开
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看数据库是否支持profiles
SELECT @@have_profiling;
-- 开启profiles
SELECT @@profiling;  -- 为0则表示未开启
SET @@profiling=1;

SHOW profiles;  -- 查询当前会话所有SQL语句的执行耗时情况
SHOW profile FOR QUERY query_id;  -- 单独查看某条SQL语句的具体耗时情况
SHOW profile CPU FOR QUERY query_id;  -- 单独查看某条SQL语句的具体耗时情况（CPU）

EXPLAIN SELECT * FROM student WHERE id=(SELECT id FROM course ORDER BY id LIMIT 1);

-- 假设对age,phone创建了一个联合索引（默认以升序构建索引）
SELECT id, age, phone ... ORDER BY age;  -- index
SELECT id, age, phone ... ORDER BY age, phone;  -- index
SELECT id, age, phone ... ORDER BY age DESC, phone, DESC;  -- index (backward scan)
SELECT id, age, phone ... ORDER BY age ASC, phone DESC; -- index & filesort：对于age使用index，对于phone使用filesort。
SELECT id, age, phone ... ORDER BY phone, age; -- filesort & index：对于phone使用filesort，对于age使用index

WITH myWindowFunc AS (
    SELECT id, num, LAG(num, 1) OVER (ORDER BY id) as prev_num1, LAG(num, 2) OVER (ORDER BY id) as prev_num2 FROM Logs
)
SELECT DISTINCT num as ConsecutiveNums
FROM myWindowFunc
WHERE num=prev_num1 AND num=prev_num2;

SELECT score, DENSE_RANK() OVER(ORDER BY score DESC) AS `rank`
FROM Scores
ORDER BY score DESC;

SELECT *, SUM(salary) OVER(PARTITION BY department) AS maxSalaryPerDepartment
FROM mytable;

ROW_NUMBER() OVER(PARTITION BY gender ORDER BY grade) AS rm

CREATE TABLE dwd_trd_alipay_order_dd (
    order_id STRING COMMENT '订单id',
    buyer_id STRING COMMENT '买家id',
    seller_id STRING COMMENT '卖家id',
    gmt_pay STRING COMMENT '付款时间'
) COMMENT '订单表'
PARTITIONED BY (dt STRING COMMENT '日期分区yyyymmdd')
CLUSTERED BY (order_id) SORTED BY (order_id) INTO 64 BUCKETS,
LIFECYCLE 365;

-- 获取logid
SHOW CHANGELOGS FOR TABLE mytable PARTITION (dt=20250801);

-- 恢复指定的logid
UNDO TABLE mytable PARTITION (dt=20250801) TO <logid>;