SqlServer数据库语句大全

### SqlServer数据库语句大全知识点解析 #### 一、数据库（Database） **1.1 数据库的建立与删除（Create/Drop Database）** ##### 1.1.1 建立数据库 **语法：** ``` CREATE DATABASE <数据库名> [其他参数] ``` **示例：** ``` -- 创建名为 hr 的数据库 CREATE DATABASE hr; ``` **说明：** - `CREATE DATABASE` 语句用于创建一个新的数据库。 - `<数据库名>` 是要创建的新数据库的名称。 - 可以添加其他参数来指定数据库的各种属性，如初始大小、最大大小等。 ##### 1.1.2 删除数据库 **语法：** ``` DROP DATABASE <数据库名> ``` **示例：** ``` -- 删除名为 hr 的数据库 DROP DATABASE hr; ``` **说明：** - `DROP DATABASE` 语句用于删除一个现有的数据库。 - `<数据库名>` 是要删除的数据库的名称。 - 删除数据库是一个不可逆的操作，请谨慎使用。 **条件删除示例：** ```sql -- 如果 hr 数据库存在，则删除它 IF DB_ID('hr') IS NOT NULL BEGIN DROP DATABASE hr; END ``` **说明：** - 使用 `DB_ID` 函数检查数据库是否存在。 - 如果数据库存在，则执行删除操作。 #### 二、数据查询（Data Query Language） **2.1 选择查询（Select Query）** **语法：** ``` SELECT column_name(s) FROM table_name WHERE condition; ``` **示例：** ```sql -- 查询 employees 表中的所有员工姓名 SELECT first_name, last_name FROM employees; ``` **说明：** - `SELECT` 用于从表中选择数据。 - `column_name(s)` 指定要返回的列名。 - `FROM` 指定要从中选择数据的表。 - `WHERE` 条件可以用来过滤结果集。 **2.2 聚集查询（Aggregate Query）** **语法：** ``` SELECT aggregate_function(column_name) FROM table_name WHERE condition; ``` **示例：** ```sql -- 计算 employees 表中的平均工资 SELECT AVG(salary) FROM employees; ``` **说明：** - `aggregate_function` 包括 COUNT、SUM、AVG、MAX 和 MIN 等聚合函数。 - `column_name` 指定进行计算的列。 **2.3 子查询（Subquery）** **语法：** ``` SELECT column_name(s) FROM table_name WHERE condition (subquery); ``` **示例：** ```sql -- 查询比部门编号为 10 的所有员工薪资高的员工 SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 10); ``` **说明：** - 子查询可以放在 `SELECT`、`FROM` 或 `WHERE` 子句中。 - 子查询的结果通常作为外层查询的一部分被使用。 **2.4 连接查询（Table Joins）** **语法：** ``` SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name = table2.column_name; ``` **示例：** ```sql -- 查询 employees 表与 departments 表的连接结果 SELECT e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; ``` **说明：** - `JOIN` 用于将两个或多个表的数据组合在一起。 - `ON` 子句指定了连接条件。 **2.5 汇总查询（Group Query）** **语法：** ``` SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition; ``` **示例：** ```sql -- 分部门统计员工数量 SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id HAVING COUNT(*) > 10; ``` **说明：** - `GROUP BY` 用于按一个或多个列分组数据。 - `HAVING` 子句用于过滤结果集，类似于 `WHERE` 子句，但作用于分组后的数据。 #### 三、数据修改（Data Modify Language） **3.1 插入数据（Insert）** **语法：** ``` INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...); ``` **示例：** ```sql -- 向 employees 表中插入新记录 INSERT INTO employees (first_name, last_name, email, hire_date, job_id, salary, department_id) VALUES ('John', 'Doe', 'JDOE', '2008-01-13', 'IT_PROG', 4200, 60); ``` **说明：** - `INSERT INTO` 用于向表中插入新记录。 - `(column1, column2,...)` 指定要插入值的列。 - `(value1, value2,...)` 是要插入的具体值。 **3.2 修改数据（Update）** **语法：** ``` UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; ``` **示例：** ```sql -- 更新员工 John Doe 的职位 UPDATE employees SET job_id = 'IT_MANAGER' WHERE first_name = 'John' AND last_name = 'Doe'; ``` **说明：** - `UPDATE` 用于更新现有记录。 - `SET` 子句用于指定要修改的列及其新值。 - `WHERE` 子句用于指定哪些记录将被更新。 **3.3 删除数据（Delete）** **语法：** ``` DELETE FROM table_name WHERE condition; ``` **示例：** ```sql -- 删除员工 John Doe 的记录 DELETE FROM employees WHERE first_name = 'John' AND last_name = 'Doe'; ``` **说明：** - `DELETE FROM` 用于从表中删除记录。 - `WHERE` 子句用于指定哪些记录将被删除。 #### 四、数据定义（Data Define Language） **4.1 表（Table）** - **创建表**： - **语法**： ``` CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ... ); ``` - **示例**： ```sql -- 创建 employees 表 CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(25), email VARCHAR(25), hire_date DATE, job_id VARCHAR(10), salary DECIMAL(8, 2), commission_pct DECIMAL(2, 2), manager_id INT, department_id INT ); ``` - **修改表**： - **语法**： ``` ALTER TABLE table_name ADD | DROP | MODIFY column_name datatype [constraints]; ``` - **示例**： ```sql -- 在 employees 表中增加 phone_number 列 ALTER TABLE employees ADD phone_number VARCHAR(20); ``` - **删除表**： - **语法**： ``` DROP TABLE table_name; ``` - **示例**： ```sql -- 删除 employees 表 DROP TABLE employees; ``` **4.2 列（Column）** - 每个表由多列组成，每一列都有自己的数据类型和约束条件。 - 示例： - `employee_id INT PRIMARY KEY`：指定主键列 `employee_id`，数据类型为整型。 - `first_name VARCHAR(20)`：指定列 `first_name`，数据类型为字符串，长度限制为 20。 **4.3 序列（Identity）** - **语法**： ``` IDENTITY (start_value, increment_value) ``` - **示例**： ```sql -- 创建带有自动增长字段的表 CREATE TABLE products ( product_id INT IDENTITY (1, 1) PRIMARY KEY, product_name VARCHAR(50), price DECIMAL(10, 2) ); ``` **4.4 约束（Constraints）** - **主要类型**： - **PRIMARY KEY**：指定主键，确保表中每一行的唯一性。 - **FOREIGN KEY**：定义外键，确保引用完整性。 - **UNIQUE**：确保列中的值是唯一的。 - **CHECK**：指定列的有效值范围。 - **DEFAULT**：为列提供默认值。 - **NOT NULL**：确保列中的值不能为空。 **4.5 索引（Index）** - **创建索引**： - **语法**： ``` CREATE INDEX index_name ON table_name (column_name); ``` - **示例**： ```sql -- 创建基于 first_name 列的索引 CREATE INDEX idx_first_name ON employees (first_name); ``` - **删除索引**： - **语法**： ``` DROP INDEX index_name; ``` - **示例**： ```sql -- 删除 idx_first_name 索引 DROP INDEX idx_first_name; ``` **4.6 视图（View）** - **创建视图**： - **语法**： ``` CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition; ``` - **示例**： ```sql -- 创建一个显示所有员工和他们的部门名称的视图 CREATE VIEW employee_department AS SELECT e.employee_id, e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; ``` - **修改视图**： - **语法**： ``` ALTER VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition; ``` - **示例**： ```sql -- 修改视图以包括员工的电子邮件地址 ALTER VIEW employee_department AS SELECT e.employee_id, e.first_name, e.email, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; ``` - **删除视图**： - **语法**： ``` DROP VIEW view_name; ``` - **示例**： ```sql -- 删除 employee_department 视图 DROP VIEW employee_department; ``` **4.7 权限（Privilege）** - **授予权限**： - **语法**： ``` GRANT privilege ON object TO user; ``` - **示例**： ```sql -- 授予用户 user1 对 employees 表的 SELECT 权限 GRANT SELECT ON employees TO user1; ``` - **撤销权限**： - **语法**： ``` REVOKE privilege ON object FROM user; ``` - **示例**： ```sql -- 撤销用户 user1 对 employees 表的 SELECT 权限 REVOKE SELECT ON employees FROM user1; ``` #### 五、数据库函数（Functions） **5.1 转换函数（Data Convert Functions）** - **主要类型**： - **CAST**：将一种数据类型转换为另一种数据类型。 - **CONVERT**：将一种数据类型转换为另一种数据类型，并提供更多的格式化选项。 - **ISNUMERIC**：检查输入是否为数字。 **5.2 聚集函数（Aggregate Functions）** - **主要类型**： - **COUNT**：计算行的数量。 - **SUM**：计算数值列的总和。 - **AVG**：计算数值列的平均值。 - **MAX**：找出一列的最大值。 - **MIN**：找出一列的最小值。 **5.3 字符函数（Character Functions）** - **主要类型**： - **CHAR_LENGTH**：返回字符串的长度。 - **UPPER**：将字符串转换为大写。 - **LOWER**：将字符串转换为小写。 - **TRIM**：删除字符串两端的空格。 **5.4 日期函数（Date Functions）** - **主要类型**： - **GETDATE**：返回当前日期和时间。 - **DATEDIFF**：计算两个日期之间的差值。 - **DATEADD**：在日期上加减指定的时间间隔。 **5.5 数学函数（Math Functions）** - **主要类型**： - **ABS**：返回数值的绝对值。 - **CEILING**：返回小于或等于指定数值的最小整数。 - **FLOOR**：返回大于或等于指定数值的最大整数。 - **POWER**：返回指定数值的指数运算结果。 **5.6 分析函数（Analytical Functions）** - **主要类型**： - **RANK**：为结果集中每一行分配一个唯一的排名。 - **DENSE_RANK**：为结果集中每一行分配一个连续的排名。 - **ROW_NUMBER**：为结果集中的每一行分配一个唯一的数字。 #### 六、数据库脚本（Script） **6.1 数据类型（Data Types）** - **主要类型**： - **INT**：整数类型。 - **VARCHAR**：可变长度的字符串。 - **DATE**：日期类型。 - **DECIMAL**：固定精度的小数。 **6.2 脚本语法（Statements）** - **主要类型**： - **IF...ELSE**：根据条件执行不同的代码块。 - **WHILE**：循环执行代码块，直到条件不满足为止。 - **BEGIN...END**：将多个 SQL 语句组合成一个逻辑单元。 **6.3 脚本游标（Cursor）** - **主要类型**： - **DECLARE CURSOR**：声明一个游标。 - **OPEN CURSOR**：打开游标。 - **FETCH NEXT**：获取下一行数据。 - **CLOSE CURSOR**：关闭游标。 - **DEALLOCATE CURSOR**：释放游标资源。 **6.4 存储过程（Procedure）** - **创建存储过程**： - **语法**： ``` CREATE PROCEDURE procedure_name @param1 datatype = default_value1, @param2 datatype = default_value2, ... AS BEGIN SQL_statements; END ``` - **示例**： ```sql -- 创建名为 GetEmployeeSalary 的存储过程 CREATE PROCEDURE GetEmployeeSalary @employee_id INT AS BEGIN SELECT salary FROM employees WHERE employee_id = @employee_id; END ``` - **调用存储过程**： - **语法**： ``` EXEC procedure_name @param1 = value1, @param2 = value2, ... ``` - **示例**： ```sql -- 调用 GetEmployeeSalary 存储过程 EXEC GetEmployeeSalary @employee_id = 100; ``` **6.5 存储函数（Function）** - **创建存储函数**： - **语法**： ``` CREATE FUNCTION function_name (@param1 datatype, @param2 datatype, ...) RETURNS return_type AS BEGIN RETURN (function_body); END ``` - **示例**： ```sql -- 创建一个计算年薪的函数 CREATE FUNCTION CalculateAnnualSalary (@monthly_salary DECIMAL(8, 2)) RETURNS DECIMAL(10, 2) AS BEGIN RETURN (@monthly_salary * 12); END ``` - **调用存储函数**： - **语法**： ``` SELECT dbo.function_name(@param1, @param2, ...); ``` - **示例**： ```sql -- 调用 CalculateAnnualSalary 函数 SELECT dbo.CalculateAnnualSalary(4200); ``` **6.6 触发器（Trigger）** - **创建触发器**： - **语法**： ``` CREATE TRIGGER trigger_name ON table_name FOR | AFTER insert, update, delete AS BEGIN SQL_statements; END ``` - **示例**： ```sql -- 创建一个在 employees 表上触发的 INSERT 触发器 CREATE TRIGGER trg_insert_employee ON employees AFTER INSERT AS BEGIN PRINT 'New employee has been inserted.'; END ``` - **删除触发器**： - **语法**： ``` DROP TRIGGER trigger_name; ``` - **示例**： ```sql -- 删除 trg_insert_employee 触发器 DROP TRIGGER trg_insert_employee; ``` **6.7 事务（Transaction）** - **事务管理**： - **BEGIN TRANSACTION**：开始一个事务。 - **COMMIT**：提交事务中的所有更改。 - **ROLLBACK**：取消事务中的所有更改。 **6.8 其它（Other）** - **变量**： - **DECLARE**：声明变量。 - **SET** 或 **SELECT**：赋值给变量。 - **PRINT**：打印变量的值。 #### 总结 以上总结了 SqlServer 数据库中的常见操作和概念，涵盖了从基本的数据操作到更高级的功能，如函数、存储过程、触发器等。对于初学者来说，这是一个很好的起点，而对于有一定经验的人来说，也是一个不错的复习和巩固的参考资料。通过掌握这些知识，你可以更好地管理和操作数据库，提高工作效率。