MySQL语法笔记（温习版）

数据库操作

创建数据库

CREATE DATABASE database_name;

删除数据库

DROP DATABASE database_name;

选择数据库

USE database_name;

表操作

创建表

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

删除表

DROP TABLE table_name;

修改表

添加列

ALTER TABLE table_name
ADD column_name datatype constraints;

修改列

ALTER TABLE table_name
MODIFY column_name new_datatype new_constraints;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

重命名表

ALTER TABLE table_name
RENAME TO new_table_name;

插入数据

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

更新数据

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

删除数据

DELETE FROM table_name
WHERE condition;

查询数据

SELECT语句

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number;

连接表

内连接

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

左连接

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

右连接

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

聚合函数

COUNT

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

SUM

SELECT SUM(column_name)
FROM table_name
WHERE condition;

AVG

SELECT AVG(column_name)
FROM table_name
WHERE condition;

MAX

SELECT MAX(column_name)
FROM table_name
WHERE condition;

MIN

SELECT MIN(column_name)
FROM table_name
WHERE condition;

数据类型与运算符

数值类型

• INT: 整数类型，可指定长度，例如：INT(10)
• FLOAT: 单精度浮点数
• DOUBLE: 双精度浮点数
• DECIMAL: 高精度小数

字符串类型

• CHAR: 定长字符串，指定长度，例如：CHAR(50)
• VARCHAR: 变长字符串，最大长度，例如：VARCHAR(255)
• TEXT: 长文本类型

日期与时间类型

• DATE: 日期类型，格式为’YYYY-MM-DD’
• TIME: 时间类型，格式为’HH:MM:SS’
• DATETIME: 日期与时间类型，格式为’YYYY-MM-DD HH:MM:SS’
• TIMESTAMP: 时间戳类型，记录时间的整数值

布尔类型

• BOOLEAN: 布尔值，取值为TRUE或FALSE

运算符

• 算术运算符: +, -, *, /, %
• 比较运算符: =, <, <=, >, >=, <>
• 逻辑运算符: AND, OR, NOT
• 字符串连接运算符: CONCAT()
• 空值判断运算符: IS NULL, IS NOT NULL

视图

创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

删除视图

DROP VIEW view_name;

使用视图

SELECT * FROM view_name;

索引

创建索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

删除索引

DROP INDEX index_name
ON table_name;

用户管理

创建用户

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

授予权限

GRANT permission1, permission2, ...
ON database_name.table_name
TO 'username'@'localhost';

撤销权限

REVOKE permission1, permission2, ...
ON database_name.table_name
FROM 'username'@'localhost';

删除用户

DROP USER 'username'@'localhost';