数据库操作语法

#创建数据库
CREATE DATABASE database_name;

#删除数据库
DROP DATABASE database_name;

#选择数据库
USE database_name;

#显示所有数据库
SHOW DATABASES;

#修改数据库，修改数据库的选项（如字符集）使用 ALTER DATABASE 语句
ALTER DATABASE database_name CHARACTER SET charset_name COLLATE collation_name;
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

数据库表操作语法

#显示数据库表的结构
SHOW CREATE TABLE table_name;

#查看数据库表的详细信息
SHOW TABLE STATUS;

#创建表
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

#查看表结构
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

#修改表结构
#添加列
ALTER TABLE table_name ADD column_name datatype constraints;
ALTER TABLE users ADD age INT;
#删除列
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE users DROP COLUMN age;
#修改列
ALTER TABLE table_name MODIFY COLUMN column_name datatype constraints;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
#重命名列
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype constraints;
ALTER TABLE users CHANGE username user_name VARCHAR(100);
#重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
ALTER TABLE users RENAME TO customers;

#删除列
DROP TABLE table_name;
DROP TABLE users;

#清空表
TRUNCATE TABLE table_name;

数据库对表数据操作语法

#插入数据
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
#查询数据
SELECT column1, column2, ... FROM table_name WHERE conditions;
SELECT username, email FROM users WHERE id = 1;
#更新数据
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE conditions;
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
#删除数据
DELETE FROM table_name WHERE conditions;
DELETE FROM users WHERE id = 1;

总体语法

SELECT d.department_name,
       AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 60000
GROUP BY d.department_name
HAVING AVG(e.salary) > 60000
ORDER BY average_salary DESC
LIMIT 2;

# SELECT: 指定要查询的字段，这里是部门名称和平均工资
# FROM: 指定要查询的表，使用别名 e 表示 employees 表，d 表示 departments 表
# JOIN: 将 employees 表和 departments 表通过 department_id 连接，确保我们可以访问部门名称
# WHERE: 过滤条件，选择工资大于 60000 的员工
# GROUP BY: 按部门名称分组，以便计算每个部门的平均工资
# HAVING: 在分组后过滤，只保留平均工资大于 60000 的部门
# ORDER BY: 按平均工资降序排列结果
# LIMIT: 限制结果为前 2 条