数据库基础操作命令总结

以下是数据库基础操作命令的总结，涵盖 创建、查询、修改、删除 等核心操作（以通用 SQL 语法为基础，部分语法需根据具体数据库如 MySQL/PostgreSQL/SQLite 调整）：

---

---

一、创建数据库

-- 创建数据库
CREATE DATABASE db_name 
[CHARACTER SET charset]   -- 可选，指定字符集（如 utf8mb4）
[COLLATE collation];      -- 可选，指定排序规则

---

二、创建表

-- 创建表
CREATE TABLE table_name (
    column1_name data_type [NOT NULL | UNIQUE | PRIMARY KEY],
    column2_name data_type [NOT NULL | UNIQUE],
    ...
    [CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES other_table(column)]
);

示例：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- MySQL 自增
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT
);

---

三、插入数据

-- 插入单行数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- 插入多行数据
INSERT INTO table_name (column1, column2)
VALUES 
    (value1, value2),
    (value3, value4);

---

四、查询数据

1. 基础查询

SELECT * FROM table_name;           -- 查询所有列
SELECT column1, column2 FROM table_name;  -- 指定列
SELECT DISTINCT column_name FROM table_name;  -- 去重

2. 条件查询

SELECT * FROM table_name 
WHERE condition;  -- WHERE 条件
-- 示例：年龄 > 18 的用户
SELECT * FROM users WHERE age > 18;

3. 排序与限制

SELECT * FROM table_name 
ORDER BY column_name ASC/DESC;  -- ASC 升序，DESC 降序
LIMIT N;  -- 限制返回 N 条记录
-- 示例：取前 10 名用户
SELECT * FROM users ORDER BY age DESC LIMIT 10;

4. 聚合函数

SELECT COUNT(*) AS total FROM table_name;       -- 统计总数
SELECT AVG(age) AS avg_age FROM users;        -- 平均值
SELECT MAX(age) FROM users;                   -- 最大值

---

五、更新数据

UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;  -- 必须指定 WHERE，否则全表更新！
-- 示例：将用户年龄+1
UPDATE users SET age = age + 1 WHERE name = 'Alice';

---

六、删除数据

-- 删除符合条件的行
DELETE FROM table_name WHERE condition;

-- 删除整个表
DROP TABLE table_name;

-- 清空表数据（保留表结构）
TRUNCATE TABLE table_name;

---

七 、管理索引

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);

-- 删除索引
DROP INDEX index_name ON table_name;

---

八、数据库连接与权限

-- 登录数据库（如 MySQL）
mysql -u username -p dbname

-- 授予权限（示例：允许用户 'user'@'localhost' 访问 db_name）
GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

---

九、不同数据库语法差异

操作	MySQL	PostgreSQL	SQLite
自增主键	AUTO_INCREMENT	SERIAL	AUTOINCREMENT
当前时间戳	CURRENT_TIMESTAMP	NOW()	CURRENT_TIMESTAMP
字符串拼接	CONCAT(str1, str2)	`
删除表并重建	DROP TABLE IF EXISTS	DROP TABLE IF EXISTS	DROP TABLE IF EXISTS

---

十、Python 连接数据库示例（以 MySQL 和 SQLite 为例）

1. MySQL（使用 mysql-connector-python）

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="db_name"
)

cursor = conn.cursor()

# 执行查询
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)

# 关闭连接
cursor.close()
conn.close()

2. SQLite（内置模块）

import sqlite3

# 连接数据库（文件）
conn = sqlite3.connect("example.db")
cursor = conn.cursor()

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL
    )
""")

# 插入数据
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
conn.commit()  # 提交事务

# 查询数据
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

# 关闭连接
conn.close()

---