数据库操作语句涵盖了SQL(Structured Query Language)的多种功能,包括但不限于创建、查询、更新和删除数据等。以下是一些基本且常用的SQL数据库操作语句概览:
1. **创建数据库或表**:
- 创建数据库:
```sql
CREATE DATABASE database_name;
```
- 创建表:
```sql
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
PRIMARY KEY (column),
FOREIGN KEY (column) REFERENCES other_table(column)
);
```
2. **插入数据**:
```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
```
3. **查询数据**:
- 简单查询:
```sql
SELECT column1, column2 FROM table_name;
```
- 条件查询:
```sql
SELECT * FROM table_name WHERE condition;
```
- 联合查询与JOIN:
```sql
SELECT a.column1, b.column2
FROM table_a AS a
JOIN table_b AS b ON a.key = b.foreign_key;
```
4. **更新数据**:
```sql
UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;
```
5. **删除数据**:
- 删除行:
```sql
DELETE FROM table_name WHERE condition;
```
- 删除表中的所有数据(保留表结构):
```sql
TRUNCATE TABLE table_name;
```
- 删除表:
```sql
DROP TABLE table_name;
```
6. **事务处理**:
```sql
BEGIN TRANSACTION;
-- 一系列操作语句...
COMMIT; -- 提交事务,使更改永久生效
ROLLBACK; -- 在出错时撤销事务中的所有更改
```
7. **索引操作**:
- 创建索引:
```sql
CREATE INDEX idx_column_name ON table_name (column_name);
```
- 删除索引:
```sql
DROP INDEX idx_column_name ON table_name;
```
8. **视图操作**:
- 创建视图:
```sql
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
```
- 查看视图:
```sql
SELECT * FROM view_name;
```
- 删除视图:
```sql
DROP VIEW view_name;
```
9. **其他常用操作**:
- 统计查询:
```sql
SELECT COUNT(*) FROM table_name;
```
- 分组和聚合函数:
```sql
SELECT column1, AVG(column2), MAX(column3) FROM table_name GROUP BY column1;
```
- 排序:
```sql
SELECT * FROM table_name ORDER BY column1 ASC/DESC;
```