SQL 语句速查表

数据库连接与选择

-- 连接到数据库
mysql -u 用户名 -p 密码 -h 主机名 数据库名;

-- 使用USE语句选择数据库
USE database_name;

数据库和表操作

创建数据库

CREATE DATABASE database_name;

删除数据库

DROP DATABASE database_name;

查看所有数据库

SHOW DATABASES;

创建表

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

删除表

DROP TABLE table_name;

查看表结构

DESCRIBE table_name;

修改表结构

ALTER TABLE table_name
ADD column_name datatype; -- 添加列
ALTER TABLE table_name
MODIFY column_name datatype; -- 修改列的数据类型
ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype; -- 修改列名
ALTER TABLE table_name
DROP column_name; -- 删除列

数据查询

基本的SELECT语句

SELECT column1, column2, ...
FROM table_name;

选择所有列

SELECT * FROM table_name;

WHERE子句

SELECT column1, column2, ...
FROM table_name
WHERE condition;

比较运算符

// www.javascriptcn.com code example
SELECT * FROM table_name
WHERE column_name = 'value'; -- 等于
SELECT * FROM table_name
WHERE column_name != 'value'; -- 不等于
SELECT * FROM table_name
WHERE column_name > 'value'; -- 大于
SELECT * FROM table_name
WHERE column_name < 'value'; -- 小于
SELECT * FROM table_name
WHERE column_name >= 'value'; -- 大于等于
SELECT * FROM table_name
WHERE column_name <= 'value'; -- 小于等于

逻辑运算符

SELECT * FROM table_name
WHERE column_name1 = 'value' AND column_name2 = 'value'; -- 与
SELECT * FROM table_name
WHERE column_name1 = 'value' OR column_name2 = 'value'; -- 或
SELECT * FROM table_name
WHERE NOT (column_name1 = 'value'); -- 非

IN运算符

SELECT * FROM table_name
WHERE column_name IN ('value1', 'value2', 'value3');

BETWEEN运算符

SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;

LIKE运算符

SELECT * FROM table_name
WHERE column_name LIKE pattern;

通配符

% 代表任意数量的字符
_ 代表一个字符

ORDER BY子句

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name ASC|DESC;

GROUP BY子句

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;

HAVING子句

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 5;

JOIN子句

// www.javascriptcn.com code example
-- 内连接
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;

-- 全连接
SELECT column1, column2
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

子查询

SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

更新数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

删除数据

DELETE FROM table_name
WHERE condition;

以上是SQL的基本语句速查表。希望对你有所帮助！