数据查询
- 基本查询
SELECT column1, column2 FROM table_name;
- 带条件的查询
SELECT * FROM table_name WHERE condition;
- 排序查询结果
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
- 分组并聚合
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
- HAVING子句(与GROUP BY一起使用)
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 10;
- 连接两个表
SELECT * FROM table1
JOIN table2 ON table1.column_name = table2.column_name;
- 子查询
SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);
数据操作
- 插入数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- 更新数据
UPDATE table_name SET column1 = value1 WHERE condition;
- 删除数据
DELETE FROM table_name WHERE condition;
数据定义
- 创建表
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
- 添加列
ALTER TABLE table_name ADD column_name datatype;
- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
- 修改列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
- 删除表
DROP TABLE table_name;
数据约束
- 设置主键
CREATE TABLE table_name (
column_name datatype PRIMARY KEY,
...
);
- 添加外键
ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES another_table(another_column);
- 设置唯一约束
CREATE TABLE table_name (
column_name datatype UNIQUE,
...
);
- 设置非空约束
CREATE TABLE table_name (
column_name datatype NOT NULL,
...
);
- 设置默认值
CREATE TABLE table_name (
column_name datatype DEFAULT default_value,
...
);
请注意,上述语法可能需要根据具体的数据库管理系统(如MySQL、PostgreSQL、SQL Server等)进行微调。此外,SQL是一种非常灵活和强大的语言,这里只列出了其常用的一部分语法。