SQL经典50题答案

CREATE DATABASE my_database;

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

INSERT INTO my_table (id, name, age) VALUES (1, 'John', 30);

SELECT * FROM my_table;

UPDATE my_table SET name = 'John Doe' WHERE id = 1;

DELETE FROM my_table WHERE id = 1;

SELECT * FROM my_table ORDER BY name;

SELECT * FROM my_table WHERE age > 30;

SELECT age, COUNT(*) AS count FROM my_table GROUP BY age;

SELECT SUM(age) FROM my_table;

SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id;

SELECT * FROM my_table WHERE id IN (SELECT id FROM my_second_table);

SELECT * FROM my_table WHERE EXISTS (SELECT 1 FROM my_second_table WHERE id = my_table.id);

SELECT * FROM my_table WHERE NOT EXISTS (SELECT 1 FROM my_second_table WHERE id = my_table.id);

SELECT * FROM my_table UNION SELECT * FROM my_second_table;

SELECT * FROM my_table INTERSECT SELECT * FROM my_second_table;

SELECT * FROM my_table EXCEPT SELECT * FROM my_second_table;

SELECT * FROM my_table WHERE name LIKE '%John%';

SELECT * FROM my_table WHERE age BETWEEN 20 AND 30;

SELECT * FROM my_table WHERE age IS NULL;

ALTER TABLE my_table ADD FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE my_table ADD UNIQUE INDEX (name);

ALTER TABLE my_table ADD PRIMARY KEY (id);

BEGIN TRANSACTION;
-- 执行一系列查询
COMMIT;

CREATE PROCEDURE my_procedure (IN param1 INT) AS
BEGIN
    -- 存储过程代码
END;

CREATE FUNCTION my_function (param1 INT) RETURNS INT AS
BEGIN
    -- 函数代码
END;

CREATE VIEW my_view AS
SELECT * FROM my_table;

DECLARE cursor_name CURSOR FOR
SELECT * FROM my_table;

CREATE TRIGGER my_trigger ON my_table FOR DELETE AS
BEGIN
    -- 触发器代码
END;

INT (整型)
TEXT (文本)
FLOAT (浮点数)
DATE (日期)
TIME (时间)
DATETIME (日期时间)

+ (加法)
- (减法)
* (乘法)
/ (除法)
% (模运算)

SUM() (求和)
AVG() (求平均值)
MIN() (求最小值)
MAX() (求最大值)

SELECT (选择)
FROM (从)
WHERE (条件)
ORDER BY (排序)
LIMIT (限制)

INSERT (插入)
UPDATE (更新)
DELETE (删除)

CREATE (创建)
DROP (删除)
ALTER (修改)

GRANT (授予)
REVOKE (撤销)

INNER JOIN (内部连接)
LEFT JOIN (左连接)
RIGHT JOIN (右连接)
FULL JOIN (全连接)

使用索引
避免嵌套查询
使用 LIMIT 子句

使用强密码
启用身份验证
使用加密

MySQL Workbench
phpMyAdmin
Navicat

MySQL 文档
Stack Overflow
W3Schools

MySQL 8.0
MySQL 5.7
MariaDB

语法错误
表不存在
列不存在
权限不足

使用 EXPLAIN 命令
检查错误日志
使用调试器

使用规范化的模式
编写高效的查询
备份你的数据库
保持你的数据库更新

云数据库
NoSQL 数据库
机器学习

SELECT ... FOR UPDATE;