1

CREATE DATABASE company;

1

SHOW DATABASES;

1

USE company;

1

DROP DATABASE company;

1

CREATE DATABASE IF NOT EXISTS company;

1
2
3
4
5
6
7
8

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    position VARCHAR(50),
    salary DECIMAL(10,2) DEFAULT 0,
    hire_date DATE,
    email VARCHAR(100) UNIQUE
);

1
2
3
4
5

DESC employess;

-- 或者

SHOW CREATE TABLE employess;

1
2
3
4
5

ALTER TABLE employess
ADD phone VARCHAR(50);

ALTER TABLE employess
ADD address VARCHAR(100) AFTER name; -- 添加到某字段后

1
2

ALTER TABLE employess
MODIFY salary DECIMAL(12, 2);

1
2

ALTER TABLE employees
CHANGE phone mobile VARCHAR(30);

1
2

ALTER TABLE employees
DROP COLUMN address;

1

RENAME TABLE employees TO staff;

1
2
3
4
5
6

ALTER TABLE employees
ADD UNIQUE (email);

ALTER TABLE employees
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

1
2
3

DROP TABLE employees;

DROP TABLE IF EXISTS employees;

1
2
3
4
5
6

INSERT INTO employees (name, position, salary)
VALUES ('Alice', 'Engineer', 12000);

-- 也可不指定字段
INSERT INTO employees
VALUES (1, 'Bob', 'Manager', 18000, '2023-01-10');

1
2
3
4
5

INSERT INTO employees (name, position, salary)
VALUES 
('Tom', 'HR', 9000),
('Lucy', 'Engineer', 13000),
('Ken', 'Designer', 11000);

1
2

INSERT INTO employees (name, position)
VALUES ('Jerry', DEFAULT);

1
2
3
4

INSERT INTO developer (name, salary)
SELECT name, salary
FROM employees
WHERE position = 'Engineer';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

UPDATE employees
SET salary = 15000
WHERE name = 'Alice';

-- 更新多字段
UPDATE employees
SET position = 'Senior Engineer',
    salary = salary + 2000
WHERE id = 1;

-- 若不使用 WHERE 即为全表更新
UPDATE employees
SET salary = salary + 1000;

1
2
3
4
5
6
7
8
9

DELETE FROM employees
WHERE id = 3;  -- 删除单条

DELETE FROM employees
WHERE salary < 5000; -- 删除多条

DELETE FROM employees; -- 危险，全表删除

TRUNCATE TABLE employees; -- 清空表（不记录逐行删除日志）

1

SELECT * FROM employees;

1

SELECT name, salary FROM employees;

1
2

SELECT name, salary * 12 annual_salary
FROM employees;

1

SELECT DISTINCT position FROM employees;

1
2
3

SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE age >= 30;
SELECT * FROM employees WHERE name <> 'Alice';

1
2
3
4
5
6
7
8

SELECT * FROM employees
WHERE position = 'Engineer' AND salary > 12000;

SELECT * FROM employees
WHERE position = 'Engineer' OR position = 'Designer';

SELECT * FROM employees
WHERE NOT (salary > 20000);

1
2

SELECT * FROM employees
WHERE salary BETWEEN 10000 AND 20000;

1
2

SELECT * FROM employees
WHERE position IN ('Engineer', 'Manager', 'Designer');

1
2
3

SELECT * FROM employees WHERE name LIKE 'A%';   -- A开头
SELECT * FROM employees WHERE name LIKE '%son'; -- 以 son 结尾
SELECT * FROM employees WHERE name LIKE '%a%';  -- 含 a

1
2

SELECT * FROM employees WHERE email IS NULL;
SELECT * FROM employees WHERE email IS NOT NULL;

1
2
3

SELECT position, AVG(salary) AS avg_salary
FROM employees
GROUP BY position;

1
2
3
4

SELECT position, AVG(salary) AS avg_salary
FROM employees
GROUP BY position
HAVING avg_salary > 15000;

1
2

SELECT * FROM employees
ORDER BY position ASC, salary DESC;

1
2
3

LIMIT offset, size;

LIMIT size;

1

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

1
2
3

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- localhost 为本机，% 为任意 IP  

1

SELECT user, host FROM mysql.user;

1

ALTER USER 'dev'@'%' IDENTIFIED BY 'newpassword';

1

DROP USER 'dev'@'%';

1
2
3
4
5
6
7

-- 对 company 的所有表，赋予所有权限
GRANT ALL PRIVILEGES ON company.* TO 'dev'@'%';

-- 授予部分权限  
GRANT SELECT, INSERT, UPDATE
ON company.employees
TO 'dev'@'%';

1
2
3
4
5
6
7

-- 部分撤销
REVOKE INSERT, UPDATE
ON company.employees
FROM 'dev'@'%';

-- 全部撤销
REVOKE ALL PRIVILEGES ON company.* FROM 'dev'@'%';

1
2

SELECT LENGTH('Hello');       -- 字节长度
SELECT CHAR_LENGTH('Hello');  -- 字符长度

1
2

SELECT CONCAT(first_name, ' ', last_name) AS fullname
FROM users;

1
2
3

SELECT SUBSTRING('HelloWorld', 1, 5); -- Hello
SELECT LEFT('HelloWorld', 5);         -- Hello
SELECT RIGHT('HelloWorld', 5);        -- World

1

SELECT REPLACE('2024-01-01', '-', '/')  -- 2024/01/01

1
2

SELECT UPPER('hello');  -- HELLO
SELECT LOWER('Hello');  -- hello

1
2
3

SELECT TRIM('   hi   ');   -- 'hi'
SELECT LTRIM('   hi');     -- 'hi'
SELECT RTRIM('hi   ');     -- 'hi'

1

SELECT ABS(-5);  -- 5

1
2
3

SELECT ROUND(3.14159, 2);  -- 3.14
SELECT CEIL(3.1);          -- 4
SELECT FLOOR(3.9);         -- 3

1
2

SELECT RAND();     -- 0~1 随机小数
SELECT RAND()*100; -- 0~100

1

SELECT MOD(10, 3);  -- 1

1
2
3

SELECT NOW();      -- 当前日期 + 时间
SELECT CURDATE();  -- 当前日期
SELECT CURTIME();  -- 当前时间

1

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

1

SELECT DATEDIFF('2024-02-01', '2024-01-01');  -- 31

1

SELECT TIMESTAMPDIFF(unit, datetime1, datetime2)

1
2

SELECT DATE_ADD('2024-01-01', INTERVAL 30 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

1
2
3

SELECT name,
       IF(salary > 10000, 'High', 'Low') AS salary_level
FROM employees;

1
2
3
4
5
6
7

SELECT name,
       CASE
            WHEN salary >= 20000 THEN 'A'
            WHEN salary >= 15000 THEN 'B'
            ELSE 'C'
       END AS grade
FROM employees;

1
2
3
4

SELECT JSON_EXTRACT(info, '$.age') FROM users;

-- 简写
SELECT info->'$.age' FROM users;

1

SELECT JSON_SET(info, '$.age', 30) FROM users;

1

SELECT JSON_VALID(info) FROM users;

1

SELECT COALESCE(nickname, username, 'unknown') AS display_name FROM users;

1
2
3
4

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 通常搭配自增使用
    name varchar(50)
);

1
2
3
4
5
6
7
8

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE
);

-- 可多个字段联合唯一  

UNIQUE (user_id, role_id)

1

name VARCHAR(50) NOT NULL

1

status VARCHAR(20) DEFAULT 'active'

1
2
3
4
5

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id);
);

1
2
3
4
5
6
7

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) UNIQUE NOT NULL,
    age INT DEFAULT 18,
    created_ad DATETIME DEFAULT CURRENT_TIMESTAMP
);

1
2
3

ALTER TABLE users ADD UNIQUE (email); -- 添加

ALTER TABLE users DROP INDEX email; -- 删除

1
2
3
4
5

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id); -- 添加

ALTER TABLE orders DROP FOREIGN KEY fk_user; -- 删除

1

users.id <- orders.user_id;

1

student_course(student_id, course_id)

1
2

users(id, name)
orders(id, user_id. amount)

1
2
3
4

SELECT u.name, o.amount 
FROM users AS u
INNER JOIN orders AS o
    ON u.id = o.user_id;

1
2
3
4

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id;

1

employees(id, name, manager_id)

1
2
3
4

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.id;

1
2
3

SELECT id FROM t1
UNION
SELECT id FROM t2;

1
2
3

SELECT id FROM t1
UNION ALL
SELECT id FROM t2;

1
2
3
4
5

SELECT *
FROM orders
WHERE amount = (
    SELECT MAX(amount) FROM orders
);

1
2
3
4
5

SELECT *
FROM users
WHERE id IN {
    SELECT user_id FROM orders
};

1
2
3
4
5

SELECT *
FROM users
WHERE (id, name) = (
    SELECT id, name FROM users WHERE id = 1
);

1
2
3
4
5
6
7

SELECT t.user_id, COUNT(*) AS cnt
FROM (
    SELECT user_id, amount
    FROM orders
    WHERE amount > 100
) AS t
GROUP BY t.user_id;

1
2
3
4

SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o 
    ON u.id = o.user_id;

1
2
3
4

SELECT u.name, o.amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.amount = (SELECT MAX(amount) FROM orders);

1
2
3
4

SELECT u.name, SUM(o.amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

1
2
3
4
5
6

START TRANSACTION;  -- 也可以使用 BEGIN

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

1

ROLLBACK;

1

SELECT @@transaction_isolation;

1

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

1

SHOW VARIABLES LIKE 'autocommit';

1

SET autocommit = 0;