常用经典SQL语句大全完整版(附详细讲解+实例)_MsSqlserver

sql 语言分类

sql（structured query language）是用于管理关系型数据库的标准语言。它主要分为以下几类：

ddl (data definition language) - 数据定义语言
用于定义数据库结构，包括创建、修改和删除数据库对象（如数据库、表、索引、视图等）。
- 常用语句：create，alter，drop，declare
dml (data manipulation language) - 数据操纵语言
用于管理数据库中的数据，包括查询、插入、更新和删除数据。
- 常用语句：select，insert，update，delete
dcl (data control language) - 数据控制语言
用于管理数据库用户的访问权限，以及控制事务的提交与回滚。
- 常用语句：grant，revoke，commit，rollback

1. 数据库管理

1.1 创建和删除数据库

创建数据库
定义一个新的数据库。
```
create database database_name;
```
示例：创建一个名为 companydb 的数据库。
```
create database companydb;
```
删除数据库
永久移除一个数据库及其所有内容。请谨慎操作，此操作不可逆。
```
drop database dbname;
```
示例：删除名为 oldcompanydb 的数据库。
```
drop database oldcompanydb;
```

1.2 数据库备份与恢复 (以 sql server 为例)

数据库备份是保证数据安全的重要环节。

创建备份设备
在sql server中，首先需要指定备份文件的存储位置和名称。

use master;
-- 'disk' 指定备份类型为磁盘文件
-- 'testback' 是备份设备名
-- 'c:\mssql7backup\mynwind_1.dat' 是备份文件的完整路径和文件名
exec sp_addumpdevice 'disk', 'testback', 'c:\mssql7backup\mynwind_1.dat';

执行备份
使用 backup database 命令将数据库备份到指定的设备。
```
-- 'pubs' 是要备份的数据库名
backup database pubs to testback;
```
注意：backup database 语句支持更多选项，如 with differential (差异备份), with compression (压缩备份) 等。

2. 表操作 (ddl)

2.1 创建新表

定义表的结构，包括列名、数据类型、约束等。

create table tabname (
    col1 type1 [constraint constraint_name] primary key [auto_increment/identity(seed, increment)], -- 主键，可自增长
    col2 type2 [not null] [unique], -- 非空，唯一
    col3 type3 [default default_value], -- 默认值
    col4 type4,
    ...
);

示例：创建一个名为 employees 的表。

create table employees (
    employeeid int primary key auto_increment, -- 员工id，主键，自增长
    firstname varchar(50) not null,          -- 名，非空
    lastname varchar(50) not null,           -- 姓，非空
    email varchar(100) unique,               -- 邮箱，唯一
    hiredate date default getdate(),         -- 入职日期，默认为当前日期
    salary decimal(10, 2)                    -- 工资
);

说明：
- int, varchar, date, decimal 是常见的数据类型。
- primary key 指定主键，用于唯一标识行。auto_increment (mysql) 或 identity (sql server) 可实现自增长。
- not null 约束确保列必须有值。
- unique 约束确保列中的所有值都是唯一的。
- default 指定列的默认值。

2.2 根据现有表创建表

有两种方式：复制表结构（不含数据）或复制表结构及数据。

复制表结构 (不含数据)
此方法仅复制表的定义。

-- sql server 语法
select * into new_table from old_table where 1 = 0; -- 1=0 确保不复制任何数据
-- mysql/postgresql 语法
create table new_table like old_table;

复制表结构和数据
将查询结果插入到新表中。

create table tab_new as select col1, col2, ... from tab_old where condition;

示例：复制 employees 表的结构和部分数据到 employeebackup 表。

create table employeebackup as
select employeeid, firstname, lastname, salary
from employees
where hiredate < '2023-01-01';

2.3 删除表

永久删除一个表及其所有数据。

drop table tabname;

示例：删除 employeebackup 表。

drop table employeebackup;

2.4 修改表结构 (alter table)

增加列
向现有表中添加新列。

alter table tabname add column_name type [constraint constraint_name] [default default_value];

示例：为 employees 表添加 department 列。

alter table employees add department varchar(50);

删除列
从表中移除一列。

alter table tabname drop column column_name; -- 标准sql
-- 或 sql server: alter table tabname drop column_name;

示例：移除 employees 表的 email 列。

alter table employees drop column email;

修改列属性
更改列的数据类型、约束等。具体语法因数据库系统而异。
示例 (mysql)：修改 employees 表的 department 列为 varchar(100)。
```
alter table employees modify column department varchar(100);
```
示例 (sql server)：修改 employees 表的 department 列为 varchar(100)。
```
alter table employees alter column department varchar(100);
```

添加和删除主键
为表添加或移除主键约束。

-- 添加主键
alter table tabname add constraint pk_tabname primary key (col1, col2); -- 允许复合主键
-- 删除主键 (sql server/oracle)
alter table tabname drop constraint pk_tabname;
-- 删除主键 (mysql)
alter table tabname drop primary key;

示例：为 orders 表添加主键。

alter table orders add constraint pk_orders primary key (orderid);

2.5 创建和删除索引

索引可以显著提高 select 查询的速度，但会增加 insert, update, delete 操作的开销。

创建索引
为表的一列或多列创建索引。unique 关键字表示该索引列的值必须唯一。
```
create [unique] index idxname on tabname (col1 [asc|desc], col2 [asc|desc], ...);
```
示例：为 employees 表的 lastname 列创建索引。
```
create index idx_lastname on employees (lastname);
```
示例：为 employees 表的 firstname 和 lastname 创建复合唯一索引。
```
create unique index idx_fullname on employees (firstname, lastname);
```

删除索引
移除已有的索引。

drop index idxname on tabname; -- 标准sql (有些数据库系统不支持on tabname)
-- 或 sql server: drop index idxname from tabname;
-- 或 mysql: drop index idxname on tabname;

示例：删除 employees 表的 idx_lastname 索引。

drop index idx_lastname on employees;

3. 视图操作 (ddl/dml)

视图（view）是一个虚拟表，它基于一个 sql 查询语句的结果集。视图不存储实际数据，但可以像表一样被查询。

3.1 创建视图

create view viewname as
select column1, column2, ...
from tablename
where condition;

示例：创建一个视图，显示所有部门为 ‘sales’ 的员工姓名和入职日期。

create view salesemployees as
select firstname, lastname, hiredate
from employees
where department = 'sales';

3.2 删除视图

drop view viewname;

示例：删除 salesemployees 视图。

drop view salesemployees;

4. 基本数据操作 (dml)

4.1 选择数据 (select)

查询数据库中的数据。

选择所有列
使用 * 选择表中的所有列。
```
select * from table1;
```
选择特定列
指定需要显示的列名。
```
select column1, column2, ... from table1;
```
带条件查询 (where)
使用 where 子句过滤满足特定条件的记录。
```
select * from table1 where condition;
```
条件操作符： =, != / <>, >, <, >=, <=, between, like, in, is null, is not null。
逻辑操作符：and, or, not。

示例：查询 employees 表中工资高于 50000 的员工。
```
select employeeid, firstname, lastname, salary
from employees
where salary > 50000;
```
示例：查询 employees 表中姓氏为 ‘smith’ 且入职日期在 2022 年后的员工。
```
select firstname, lastname, hiredate
from employees
where lastname = 'smith' and hiredate > '2022-01-01';
```

排序 (order by)
对查询结果进行升序 (asc) 或降序 (desc) 排序。

select column1, column2 from table1 order by column1 [asc|desc], column2 [asc|desc];

示例：按工资降序排列所有员工信息。

select * from employees order by salary desc;

分组 (group by) 与聚合函数
将数据按某一列或多列分组，并对每组数据进行聚合计算。
常用聚合函数：
- count(*) / count(column): 计数
- sum(column): 求和
- avg(column): 求平均值
- max(column): 求最大值
- min(column): 求最小值
```
select column1, count(*) as total_count, sum(column2) as total_sum
from table1
where condition
group by column1
having count(*) > 10 -- 对分组后的结果进行过滤
order by column1;
```
示例：计算每个部门的员工人数和平均工资。
```
select department, count(*) as employeecount, avg(salary) as averagesalary
from employees
group by department;
```

4.2 插入数据 (insert)

向表中添加新记录。

插入所有列的值
值的顺序必须与表中列的顺序一致。
```
insert into table1 values (value1, value2, ...);
```
插入指定列的值
指定列名，可以不按顺序，或只插入部分列。
```
insert into table1 (field1, field2, field3) values (value1, value2, value3);
```

插入查询结果
将另一个 select 语句的结果插入到表中。

insert into table1 (field1, field2)
select column1, column2 from another_table where condition;

示例：插入一条新员工记录。

insert into employees (firstname, lastname, email, hiredate, salary, department)
values ('alice', 'wonderland', 'alice@example.com', '2023-10-01', 60000.00, 'marketing');

示例：将现有员工的部门信息复制到新员工表中。

insert into employeebackup (employeeid, firstname, lastname, salary)
select employeeid, firstname, lastname, salary
from employees
where department = 'sales' and salary > 70000;

4.3 删除数据 (delete)

移除表中的记录。

delete from table1 where condition;

重要：如果 where 子句被省略，将删除表中的所有记录。

示例：删除 employees 表中工资低于 40000 的所有员工。

delete from employees
where salary < 40000;

示例：删除所有部门为 ‘temporary’ 的员工。

delete from employees
where department = 'temporary';

4.4 更新数据 (update)

修改表中现有记录的字段值。

update table1 set field1 = value1, field2 = value2, ... where condition;

重要：如果 where 子句被省略，将更新表中所有记录。

示例：将员工id为 101 的员工的工资提高 10%。

update employees
set salary = salary * 1.10
where employeeid = 101;

示例：将所有部门为 ‘it’ 的员工的部门改为 ‘technology’。

update employees
set department = 'technology'
where department = 'it';

5. 高级查询与连接

5.1 集合运算符

用于合并多个 select 语句的结果集。

union：合并结果集，并自动去除重复的行。
union all：合并结果集，保留所有行（包括重复行）。
except (或 minus in oracle)：返回第一个 select 语句的结果集，但不包含第二个 select 语句结果集中的行。
intersect：返回两个 select 语句结果集中都存在的行。

要求：参与集合运算的 select 语句必须具有相同数量的列，且对应列的数据类型兼容。

示例：获取所有男性员工的姓名和所有女性员工的姓名（合并且去重）。

select firstname, lastname from employees where gender = 'male'
union
select firstname, lastname from employees where gender = 'female';

5.2 连接 (join)

将来自两个或多个表的记录组合起来，基于它们之间的关联列。

inner join (内连接)：
返回两个表中连接列的值匹配的行。这是默认的连接类型（如果只写 join）。
```
select t1.col1, t2.col2
from table1 t1
inner join table2 t2 on t1.common_column = t2.common_column;
```
left join (左外连接)：
返回左表（table1）的所有行，以及右表（table2）中匹配的行。如果右表中没有匹配项，则右表列显示为 null。
```
select t1.col1, t2.col2
from table1 t1
left join table2 t2 on t1.common_column = t2.common_column;
```
right join (右外连接)：
返回右表（table2）的所有行，以及左表（table1）中匹配的行。如果左表中没有匹配项，则左表列显示为 null。
```
select t1.col1, t2.col2
from table1 t1
right join table2 t2 on t1.common_column = t2.common_column;
```
full outer join (全外连接)：
返回左表和右表中所有不匹配的行，以及匹配的行。如果某侧没有匹配，则该侧列显示为 null。
```
select t1.col1, t2.col2
from table1 t1
full outer join table2 t2 on t1.common_column = t2.common_column;
```
注意：full outer join 在 mysql 中不受支持，可使用 left join 和 right join 的 union 来模拟。

示例：查询所有员工及其所在的部门名称。假设 employees 表有 departmentid 列，departments 表有 departmentid 和 departmentname 列。

select
    e.firstname,
    e.lastname,
    d.departmentname
from employees e
left join departments d on e.departmentid = d.departmentid;

示例：查询没有分配部门的员工。

select e.firstname, e.lastname
from employees e
left join departments d on e.departmentid = d.departmentid
where d.departmentid is null; -- 或者 where d.departmentname is null

5.3 统计与聚合查询

如前文 group by 部分所述，聚合函数用于对一组行进行计算。

总数

select count(*) as total_records from your_table;

示例：统计 employees 表的总记录数。

select count(*) as totalemployees from employees;

求和

select sum(numeric_column) as total_sum from your_table;

示例：计算 employees 表的总工资。

select sum(salary) as totalpayroll from employees;

平均值

select avg(numeric_column) as average_value from your_table;

示例：计算 employees 表的平均工资。

select avg(salary) as averagesalary from employees;

最大值

select max(column) as max_value from your_table;

示例：查找 employees 表中最高工资。

select max(salary) as highestsalary from employees;

最小值

select min(column) as min_value from your_table;

示例：查找 employees 表中最低工资。

select min(salary) as lowestsalary from employees;

6. 子查询与表复制

6.1 子查询 (subquery)

子查询是在 where, from, select 子句中嵌套的查询语句，用于返回数据供外部查询使用。

在 where 子句中使用子查询
通常与 in, not in, =, !=, >, <, any, all 等操作符结合使用。

-- 查找工资高于平均工资的员工
select firstname, lastname, salary
from employees
where salary > (select avg(salary) from employees);
-- 查找在'sales'部门工作的员工（假设departmentid是sales的id）
select firstname, lastname
from employees
where departmentid in (select departmentid from departments where departmentname = 'sales');

在 from 子句中使用子查询 (派生表)
子查询的结果集可以作为一个临时表（派生表）被外部查询使用。
```
select *
from (select employeeid, firstname, lastname from employees where salary > 50000) as highsalaryemployees;
```

在 select 子句中使用子查询 (标量子查询)
子查询返回单个值，用于为外部查询的每一行提供一个计算值。

select
    e.firstname,
    e.lastname,
    e.salary,
    (select avg(salary) from employees) as globalaveragesalary -- 显示全局平均工资
from employees e;

6.2 复制表结构与数据

复制表结构 (不含数据)
创建与源表结构相同但为空的新表。

-- sql server 语法
select * into new_table from old_table where 1=0;
-- mysql/postgresql 语法
create table new_table like old_table;

复制数据 (插入)
将源表中选定的数据插入到目标表中（目标表需已存在或与源表结构一致）。

insert into b (col1, col2, col3)
select d, e, f from a where condition;

示例：将2023年入职的员工数据复制到 employeearchive 表。

insert into employeearchive (employeeid, firstname, lastname, hiredate)
select employeeid, firstname, lastname, hiredate
from employees
where year(hiredate) = 2023;

7. 随机数据处理与重复数据处理

7.1 随机选择记录

从表中随机抽取一定数量的记录。不同数据库系统语法略有差异。

sql server
使用 order by newid()。

select top 10 * from tablename order by newid();

mysql
使用 order by rand()。

select * from tablename order by rand() limit 10;

postgresql
使用 order by random()。

select * from tablename order by random() limit 10;

7.2 删除重复记录

识别并保留每组重复记录中的一条（通常是id最大或最小的），删除其余的。

通用方法 (使用row_number或max/min id)
假设 id 是自增主键，col1, col2 是用于判断重复的列。

-- 方法一：使用 row_number() (sql server, postgresql, oracle)
with cte as (
    select
        id,
        col1,
        col2,
        row_number() over(partition by col1, col2 order by id desc) as rn
    from tablename
)
delete from tablename
where id in (select id from cte where rn > 1);
-- 方法二：使用 max/min id (兼容性更广)
-- 删除除最大id之外的所有重复记录
delete from tablename
where id not in (select max(id) from tablename group by col1, col2, ...);

示例：删除 products 表中 productname 和 category 重复的记录，保留 productid 最大的那条。

delete from products
where productid not in (
    select max(productid)
    from products
    group by productname, category
);

8. 数据库和表信息查询

用于查看数据库的元数据（关于数据的数据）。

列出数据库中的所有用户表
不同数据库系统语法不同。

-- sql server
select name from sysobjects where type = 'u'; -- u 代表用户表
-- mysql
show tables;
-- postgresql
select tablename from pg_tables where schemaname = 'public'; -- 'public' 是默认模式

列出指定表的列信息
查看表的列名、数据类型、是否可空等。

-- sql server
select name from syscolumns where id = object_id('tablename');
-- mysql
show columns from tablename;
-- 或
describe tablename;
-- postgresql
select column_name, data_type, is_nullable
from information_schema.columns
where table_name = 'tablename';

示例：查看 employees 表的列信息 (mysql 语法)。

describe employees;

9. 事务管理 (dcl)

事务是一组数据库操作的逻辑单元。事务要么全部成功执行，要么全部不执行，以保证数据的一致性和完整性。

9.1 事务控制语句

begin transaction / start transaction
标记事务的开始。
```
begin transaction;
-- 或 start transaction;
```
rollback
回滚事务，撤销自 begin transaction 以来的所有修改。
```
rollback;
```
commit
提交事务，将所有修改永久保存到数据库。
```
commit;
```

9.2 事务管理示例

一个典型的转账场景：从账户a扣款，给账户b加款。

begin transaction;
-- 1. 从账户 a 扣除 100 元
update accounts set balance = balance - 100 where account_id = 1;
-- 2. 检查扣款操作是否成功 (sql server 的 @@error, 其他数据库可能有类似机制或异常处理)
if @@error <> 0
begin
    -- 如果发生错误，回滚事务
    rollback transaction;
    print 'error: failed to debit account 1. transaction rolled back.';
end
else
begin
    -- 3. 给账户 b 增加 100 元
    update accounts set balance = balance + 100 where account_id = 2;
    -- 4. 检查加款操作是否成功
    if @@error <> 0
    begin
        -- 如果发生错误，回滚事务
        rollback transaction;
        print 'error: failed to credit account 2. transaction rolled back.';
    end
    else
    begin
        -- 5. 如果所有操作都成功，提交事务
        commit transaction;
        print 'transaction completed successfully. balances updated.';
    end
end

说明：

begin transaction; 开启一个事务。
update 语句执行实际的数据修改。
if @@error <> 0 (sql server 语法) 用于检测上一条sql语句是否执行出错。在其他数据库中，您可能需要使用 try...catch 块或检查事务的状态。
rollback; 用于撤销所有未提交的更改。
commit; 用于使所有更改永久生效。

通过掌握这些sql语句，您将能够高效、准确地进行数据库管理和数据操作，确保数据操作的完整性和安全性。

总结

到此这篇关于常用经典sql语句大全完整版的文章就介绍到这了,更多相关sql语句常用大全内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

常用经典SQL语句大全完整版(附详细讲解+实例)