寒假生活指导 07

一、数据库操作

创建数据库
CREATE DATABASE database_name;

删除数据库
DROP DATABASE database_name;
USE database_name;

查看所有数据库
SHOW DATABASES;

二、表操作

创建表
CREATE TABLE table_name (
column1 datatype [约束],
column2 datatype [约束],
...
);

删除表
DROP TABLE table_name;

修改表结构

添加列：
ALTER TABLE table_name ADD column_name datatype;

删除列：
ALTER TABLE table_name DROP COLUMN column_name;

修改列类型：
ALTER TABLE table_name MODIFY column_name new_datatype;

查看表结构
DESCRIBE table_name; -- MySQL

三、数据操作

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

查询数据
SELECT column1, column2 FROM table_name WHERE condition;
SELECT * FROM table_name; -- 查询所有数据

更新数据
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

删除数据
DELETE FROM table_name WHERE condition;

四、查询进阶

条件筛选
WHERE, AND, OR, NOT, IN, BETWEEN, LIKE

排序
SELECT * FROM table_name ORDER BY column1 ASC|DESC;

分组与聚合
SELECT column, COUNT(*), AVG(column)
FROM table_name
GROUP BY column
HAVING condition;

连接查询
-- 内连接
SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.foreign_id;

-- 左连接
SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.foreign_id;

五、索引操作

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

删除索引
ALTER TABLE table_name DROP INDEX index_name;

六、权限管理

创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授权
GRANT SELECT, INSERT ON database_name.* TO 'username'@'host';

撤销权限
REVOKE INSERT ON database_name.* FROM 'username'@'host';

七、事务管理

开启事务
BEGIN TRANSACTION; -- 或 START TRANSACTION;

提交事务
COMMIT;

回滚事务
ROLLBACK;