# 常用SQL语句示例
#### 数据查询
- 基本查询
  ```
  SELECT * FROM users;
  ```
  
- 条件查询
  ```
  SELECT name, age FROM users WHERE age > 30;
  ```
  
- 排序
  ```
  SELECT * FROM users ORDER BY create_time DESC;
  ```
  
- 分组聚合
  ```
  SELECT age, COUNT(*) FROM users GROUP BY age;
  ```
  
- TOP语法
  ```
  SELECT TOP 10 * FROM users;
  ```
  
 
#### 数据插入与修改
- 插入
  ```
  INSERT INTO users (name, age) VALUES ('Alice',  25);
  ```
  
- OUTPUT 语法
  ```
  INSERT INTO t(name) OUTPUT INSERTED.* VALUES('abc');
  ```
  
- 更新数据
  ```
  UPDATE users SET age = age + 1 WHERE name = 'Alice';
  ```
  
- 删除数据
  ```
  DELETE FROM users WHERE age < 18;
  ```
  
 
#### 表操作
- 创建表
  ```
  CREATE TABLE users (id INT PRIMARY KEY, name NVARCHAR(50), age INT);
  ```
  
- 删除表
  ```
  DROP TABLE users;
  ```
  
- 添加字段
  ```
  ALTER TABLE users ADD email NVARCHAR(100);
  ```
  
- 删除字段
  ```
  ALTER TABLE users DROP COLUMN email;
  ```
  
- 修改字段类型
  ```
  ALTER TABLE users ALTER COLUMN age SMALLINT;
  ```
  
- 全局临时表
  ```
  CREATE TABLE ##tmp(x INT);
  ```
  
- 局部临时表
  ```
  CREATE TABLE #tmp(x INT);
  ```
  
 
#### 数据完整性与索引
- 添加主键
  ```
  ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);
  ```
  
- 创建唯一索引
  ```
  CREATE UNIQUE INDEX idx_users_name ON users(name);
  ```
  
- 添加外键
  ```
  ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);
  ```
  
 
#### 视图与存储过程
- 创建视图
  ```
  CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18;
  ```
  
- 删除视图
  ```
  DROP VIEW adult_users;
  ```
  
- 存储过程
  ```
  CREATE PROCEDURE get_adult_users AS
  SELECT * FROM users WHERE age >= 18;
  ```
  
- 执行存储过程
  ```
  EXEC get_adult_users;
  ```
  
 
#### 数据库操作
- 查询版本
  ```
  SELECT @@version;
  ```
  
- 数据库切换
  ```
  USE testdb;
  ```