基础知识
- 作者仓库星标 0
- 作者仓库 skills-registry
- 领域
- 通用
- 作者声明的智能体
- 未找到明确声明;不据此推断已兼容或已测试
- 静态检查
- 88 / 100 · 启发式扫描,不代表运行安全
- 运行环境证据
- 未执行;当前没有隔离运行测试证据
- 作者 / 版本 / 许可证
- @tomevault-io · 未声明许可证
- 上下文用量评级
- 低消耗
- 接入复杂程度
- 需简单配置
- 是否需要外部接口密钥
- 不需要
- 兼容的系统
- 未声明(默认跨平台)
- 运行环境要求
- 未检出明确要求
- 检测到的文件与系统行为
-
- 只读
- 允许写入 / 修改
- 命令执行
- 网络行为
- 仅限本地
- 安装命令数
- 无(仅作为资料)
需要注意: 未声明工具权限范围;实际权限由运行环境决定。
# SQL Fundamentals Core Knowledge
> **Deep Knowledge**: Use `mcp__documentation__fetch_docs` with technology: `sql` for comprehensive documentation.
## SQL Statement Categories
| Category | Statements | Purpose |
|----------|------------|---------|
| **DML** | SELECT, INSERT, UPDATE, DELETE, MERGE | Data manipulation |
| **DDL** | CREATE, ALTER, DROP, TRUNCATE | Schema definition |
| **DCL** | GRANT, REVOKE | Access control |
| **TCL** | BEGIN, COMMIT, ROLLBACK, SAVEPOINT | Transaction control |
## SELECT Statement
```sql
SELECT [DISTINCT] columns
FROM table
[JOIN other_table ON condition]
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns [ASC|DESC]]
[LIMIT n OFFSET m];
```
### Execution Order
1. FROM (and JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET
## INSERT Patterns
```sql
-- Single row
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Multiple rows
INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com');
-- Insert from SELECT
INSERT INTO users_backup (name, email)
SELECT name, email FROM users WHERE created_at < '2024-01-01';
-- Insert with RETURNING (PostgreSQL)
INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
RETURNING id, created_at;
```
## UPDATE Patterns
```sql
-- Simple update
UPDATE users SET name = 'John Doe' WHERE id = 1;
-- Multiple columns
UPDATE users SET name = 'John', status = 'active' WHERE id = 1;
-- Update with subquery
UPDATE orders SET status = 'shipped'
WHERE user_id IN (SELECT id FROM users WHERE is_premium = true);
-- Update with JOIN (varies by database)
-- PostgreSQL
UPDATE orders o SET status = 'vip'
FROM users u WHERE o.user_id = u.id AND u.is_premium = true;
```
## DELETE Patterns
```sql
-- Delete with condition
DELETE FROM users WHERE id = 1;
-- Delete with subquery
DELETE FROM orders WHERE user_id IN (
SELECT id FROM users WHERE status = 'deleted'
);
-- Soft delete pattern (prefer this)
UPDATE users SET deleted_at = NOW() WHERE id = 1;
```
## JOIN Types
| Join Type | Returns |
|-----------|---------|
| `INNER JOIN` | Only matching rows from both tables |
| `LEFT JOIN` | All left + matching right (NULL if no match) |
| `RIGHT JOIN` | All right + matching left (NULL if no match) |
| `FULL OUTER JOIN` | All rows from both tables |
| `CROSS JOIN` | Cartesian product (all combinations) |
```sql
-- INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- LEFT JOIN (include users without orders)
SELECT u.name, COALESCE(o.total, 0) as total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Self JOIN (hierarchical data)
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
```
## Aggregations
```sql
-- Basic aggregates
SELECT
COUNT(*) as total,
COUNT(DISTINCT user_id) as unique_users,
SUM(amount) as total_amount,
AVG(amount) as avg_amount,
MIN(amount) as min_amount,
MAX(amount) as max_amount
FROM orders;
-- GROUP BY
SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
FROM orders
GROUP BY user_id;
-- HAVING (filter after GROUP BY)
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
```
## DDL - Table Definition
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- PostgreSQL auto-increment
-- id INT AUTO_INCREMENT PRIMARY KEY -- MySQL
-- id INT IDENTITY(1,1) PRIMARY KEY -- SQL Server
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'deleted'))
);
-- Foreign key
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
```
## ALTER TABLE
```sql
-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Drop column
ALTER TABLE users DROP COLUMN phone;
-- Modify column
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
-- Add constraint
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE (phone);
-- Drop constraint
ALTER TABLE users DROP CONSTRAINT uq_phone;
-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Rename table
ALTER TABLE users RENAME TO customers;
```
## Indexes
```sql
-- Create index
CREATE INDEX idx_users_email ON users(email);
-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- Drop index
DROP INDEX idx_users_email;
```
## Transactions
```sql
-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- With savepoint
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Something went wrong with the credit
ROLLBACK TO after_debit;
-- Try different approach
UPDATE accounts SET balance = balance + 100 WHERE id = 3;
COMMIT;
-- Rollback on error
BEGIN;
-- operations...
ROLLBACK; -- cancel all changes
```
### Isolation Levels
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|-------|------------|---------------------|--------------|
| READ UNCOMMITTED | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes |
| REPEATABLE READ | No | No | Yes |
| SERIALIZABLE | No | No | No |
```sql
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- operations
COMMIT;
```
## NULL Handling
```sql
-- Check for NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;
-- COALESCE (first non-null)
SELECT COALESCE(phone, 'N/A') as phone FROM users;
-- NULLIF (return NULL if equal)
SELECT NULLIF(status, 'unknown') FROM users;
-- NULL in aggregates (ignored except COUNT(*))
SELECT AVG(score) FROM tests; -- NULLs ignored
SELECT COUNT(*) FROM tests; -- counts all rows
SELECT COUNT(score) FROM tests; -- counts non-NULL only
```
## Subqueries
```sql
-- Scalar subquery
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;
-- IN subquery
SELECT * FROM users WHERE id IN (
SELECT DISTINCT user_id FROM orders WHERE total > 100
);
-- EXISTS subquery (often faster than IN)
SELECT * FROM users u WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100
);
-- Correlated subquery
SELECT * FROM orders o1 WHERE total > (
SELECT AVG(total) FROM orders o2 WHERE o2.user_id = o1.user_id
);
```
## Best Practices
### DO
- Use parameterized queries (prevent SQL injection)
- Add indexes on WHERE/JOIN columns
- Use appropriate data types
- Define foreign keys for data integrity
- Use transactions for multiple related operations
- Use EXPLAIN to analyze query performance
### DON'T
- Use SELECT * in production
- UPDATE/DELETE without WHERE clause
- Store comma-separated values in columns
- Use reserved words as identifiers
- Ignore NULL handling
## When NOT to Use This Skill
- **Advanced SQL** (CTEs, window functions, recursive queries) - Use `sql-advanced` skill
- **PostgreSQL specifics** (arrays, JSONB, extensions) - Use `postgresql` skill
- **MySQL specifics** (engine selection, stored procedures) - Use `mysql` skill
- **Document databases** - Use `mongodb` for document-oriented data
- **Caching** - Use `redis` for caching needs
## Anti-Patterns
| Anti-Pattern | Problem | Solution |
|--------------|---------|----------|
| SELECT * in production | Transfers unnecessary data | Specify only needed columns |
| No WHERE on UPDATE/DELETE | Unintended changes to all rows | Always add WHERE clause |
| Missing indexes on JOIN columns | Slow queries, full table scans | Add indexes on foreign keys |
| String concatenation in SQL | SQL injection vulnerability | Use parameterized queries |
| Implicit data type conversions | Performance loss, unexpected results | Use explicit CAST |
| Storing CSV in columns | Violates 1NF, hard to query | Normalize into separate table |
| Using reserved words as identifiers | Syntax errors, portability issues | Choose different names |
## Quick Troubleshooting
| Problem | Diagnostic | Fix |
|---------|------------|-----|
| Syntax errors | Check SQL dialect | Use correct syntax for your database |
| Slow queries | `EXPLAIN` or `EXPLAIN ANALYZE` | Add indexes, rewrite query |
| Deadlocks | Check transaction logs | Reduce transaction scope, consistent ordering |
| Foreign key violation | Check referenced table data | Insert parent record first |
| Duplicate key error | Check UNIQUE constraints | Use UPSERT or handle conflict |
| NULL comparison fails | Remember NULL != NULL | Use IS NULL, IS NOT NULL |
## Reference Documentation
- [DML Patterns](quick-ref/dml.md)
- [DDL Patterns](quick-ref/ddl.md)
- [JOIN Patterns](quick-ref/joins.md)
- [Aggregations & Window Functions](quick-ref/aggregations.md)
- [Transactions](quick-ref/transactions.md)
---
> Source: [claude-dev-suite/claude-dev-suite](https://github.com/claude-dev-suite/claude-dev-suite) — distributed by [TomeVault](https://tomevault.io).
<!-- tomevault:4.0:skill_md:2026-05-22 -->
章节与文件索引
按说明文件中的顺序排列。
SQL语句类别
执行指令
连接类型
隔离级别
溶解氧
文件/命令 -> mcp__documentation__fetch_docs · sql · INNER JOIN · LEFT JOIN · RIGHT JOIN · FULL OUTER JOIN · CROSS JOIN · sql-advanced
内容校验值 -> 1b0bf6946943
当前原文的自动摘译,尚未人工审校。
SQL语句类别
类别 · 语句 · 目的 DML · SELECT、INSERT、UPDATE、DELETE、MERGE · 数据操作 DDL · CREATE、ALTER、DROP、TRUNCATE · 模式定义 DCL · GRANT、REVOKE · 访问控制 TCL · BEGIN、COMMIT、ROLLBACK、SAVEPOINT · 事务控制
执行指令
从(和连接)哪里通过选择进行分组
连接类型
连接类型 · 返回
INNER JOIN· 仅两个表中匹配的行LEFT JOIN· 所有左 + 匹配右(如果不匹配则为 NULL)RIGHT JOIN· 所有右 + 匹配左(如果不匹配则为 NULL)FULL OUTER JOIN· 来自两个表的所有行隔离级别
级别 · 脏读 · 不可重复读 · 幻读 未提交读 · 是 · 是 · 是 读已提交 · 否 · 是 · 是 可重复读 · 否 · 否 · 是 可串行化 · 否 · 否 · 否
溶解氧
使用参数化查询(防止 SQL 注入) 在 WHERE/JOIN 列上添加索引 使用适当的数据类型 定义外键以确保数据完整性 使用事务进行多个相关操作