SQLserver基础语句大全[通俗易懂]

2022-08-22 10:49:27 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

SQL 基础

结构化查询语言(Structured Query Language)简称 SQL，是一种特殊目的的编程语言， 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统; 同时也是数据库脚本文件的扩展名。

SQL DML 和 DDL

可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入 和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分: ➢ SELECT – 从数据库表中获取数据 ➢ UPDATE – 更新数据库表中的数据 ➢ DELETE – 从数据库表中删除数据

➢ INSERT INTO – 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引 (键)，规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句: ➢ CREATE DATABASE – 创建新数据库 ➢ ALTER DATABASE – 修改数据库 ➢ CREATE TABLE – 创建新表 ➢ ALTER TABLE – 变更(改变)数据库表 ➢ DROP TABLE – 删除表 ➢ CREATE INDEX – 创建索引(搜索键) ➢ DROP INDEX – 删除索引

重要事项

一定要记住，SQL 对大小写不敏感!

SQL SELECT 语句

Sql 语法:

代码语言：javascript复制
SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,...]]} FROM tableexpression[,...][IN externaldatabase]
[WHERE...]
[GROUP BY...]
[HAVING...]
[ORDER BY...]

执行步骤: SQL Select 语句完整的执行顺序【从 DBMS 使用者角度】:

1、from 子句组装来自不同数据源的数据; 2、where 子句基于指定的条件对记录行进行筛选; 3、group by 子句将数据划分为多个分组; 4、使用聚集函数进行计算; 5、使用 having 子句筛选分组; 6、计算所有的表达式; 7、使用 order by 对结果集进行排序。

SQL Select 语句的执行步骤【从 DBMS 实现者角度，这个对 我们用户意义不大】:

1)语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。 2) 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 3)视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。 4)表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。 5)选择优化器，不同的优化器一般产生不同的“执行计划” 6)选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当

的连接方式。 7)选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪

个表做为源数据表。 8)选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表

搜索还是利用索引或是其他的方式。 9)运行“执行计划”。

运算符

字符匹配符

脚本示例:

代码语言：javascript复制
with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons

with Orders as (
select 'IBM' as company ,'3532' as ordernumber union all select 'W3School' as company ,'2356' as ordernumber union all select 'Apple' as company ,'4698' as ordernumber union all select 'W3School' as company ,'6953' as ordernumber)
select * from Orders

SQL distinct

关键词 DISTINCT 用于返回唯一不同的值。

代码语言：javascript复制
with Orders as (
select 'IBM' as company ,'3532' as ordernumber union all select 'W3School' as company ,'2356' as ordernumber union all select 'Apple' as company ,'4698' as ordernumber union all select 'W3School' as company ,'6953' as ordernumber)
select distinct company from Orders

SQL where

WHERE 子句用于规定选择的标准。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

AND 和 OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

代码语言：javascript复制
with persons as (
select 'Adams' as lastname,'John' as firstname ,'Oxford Street' as address,'London' as city,'1970' as year union all
select 'Bush'as lastname,'George' as firstname ,'Fifth Avenue' as address,'New York'

as city,'1975' as year union all
select 'Carter' as lastname,'Thomas' as firstname ,'Changan Street' as address,'Beijing' as city,'1980' as year union all
select 'Gates' as lastname,'Bill' as firstname ,'Xuanwumen 10' address,'Beijing' as city,'1985' as year )
select * from persons WHERE FirstName='Thomas'

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤。

SQL as

代码语言：javascript复制
with persons as (
select 'Adams' as lastname ,'John' as FirstName,'Oxford Street'
address ,'London' as city union all
select 'Bush'as lastname ,'George' as FirstName,'Fifth Avenue' as address ,'New York' as city union all
select 'Carter' as lastname ,'Thomas' as FirstName,'Changan Street' as
address ,'Beijing' as city union all
select 'Carter' as lastname ,'William'as FirstName,'Xuanwumen 10' as
address ,'Beijing' as city)
select * from persons WHERE FirstName='Thomas' AND LastName='Carter'

SQL order by

ORDER BY 语句用于对结果集进行排序。

ORDER BY 语句 ORDER BY 语句用于根据指定的列对结果集进行排序。 ORDER BY 语句默认按照升序对记录进行排序。 如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

代码语言：javascript复制
with Orders as (
select 'IBM' as company ,'3532' as ordernumber union all select 'W3School' as company ,'2356' as ordernumber union all select 'Apple' as company ,'4698' as ordernumber union all select 'W3School' as company ,'6953' as ordernumber)
select distinct company from Orders

SQL top

TOP 子句

TOP 子句用于规定要返回的记录的数目。 对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。 注释:并非所有的数据库系统都支持 TOP 子句。

代码语言：javascript复制
with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as

as

address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select top 2 * from Persons

SQL like

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

LIKE 操作符 LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

代码语言：javascript复制
with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City LIKE 'N%'

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。 SQL 通配符必须与 LIKE 运算符一起使用。

代码语言：javascript复制
with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City LIKE '%lond%'

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street'

as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where FirstName LIKE '_eorge'
代码语言：javascript复制
with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all

select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where LastName LIKE 'C_r_er'

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City LIKE '[ALN]%';

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where City not LIKE '[ALN]%';

SQL in

IN 操作符 IN 操作符允许我们在 WHERE 子句中规定多个值。

代码语言：javascript复制
with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where LastName IN ('Adams','Carter')

with Persons as (
select '1' as id, 'Adams' as lastname, 'John' as firstname, 'Oxford Street' as address, 'London' as city union all
select '2' as id, 'Bush' as lastname, 'George' as firstname, 'Fifth Avenue' as

address, 'New York' as city union all
select '3' as id, 'Carter' as lastname, 'Thomas' as firstname, 'Changan Street' as address, 'Beijing' as city )
select * from Persons where LastName not IN ('Adams','Carter')

SQL between

BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围。BETWEEN 操作符 操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或 者日期。

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John'
address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10'
as address ,'Beijing' as city )
select * from Persons where LastName BETWEEN 'Adams' AND 'Carter'

with Persons as (
select '1' as id,'Adams' as lastname,'John' as fistname ,'Oxford Street' as address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George' as fistname ,'Fifth Avenue'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10'
as address ,'Beijing' as city )
select * from Persons where not LastName BETWEEN 'Adams' AND 'Carter'

SQL Alias

通过使用 SQL，可以为列名称和表名称指定别名(Alias)。

SQL join

SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

Join 和 Key 有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。

as fistname ,’Oxford Street’ as as fistname ,’Fifth Avenue’

数据库中的表可通过键将彼此联系起来。主键(Primary Key)是一个列，在这个列中的每一 行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中 的所有数据的情况下，把表间的数据交叉捆绑在一起。

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John' as fistname ,'Oxford Street' as address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George' as fistname ,'Fifth Avenue'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10'
as address ,'Beijing' as city )
, orders as (
SELECT '1' AS ORDERID,'77895' AS ORDERS,'3' AS ID UNION ALL
SELECT '2' AS ORDERID,'44678' AS ORDERS,'3' AS ID UNION ALL
SELECT '3' AS ORDERID,'22456' AS ORDERS,'1' AS ID UNION ALL
SELECT '4' AS ORDERID,'24562' AS ORDERS,'1' AS ID UNION ALL
SELECT '5' AS ORDERID,'34764' AS ORDERS,'65' AS ID )
select * from Persons a join orders b on a.id=b.id

SQL INNER JOIN

在表中存在至少一个匹配时，INNER JOIN 关键字返回行注释:INNER JOIN 与 JOIN 是相同的。

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John' address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George' as address ,'New York' as city union all

as fistname ,'Oxford Street' as as fistname ,'Fifth Avenue'

select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10' as address ,'Beijing' as city )

, orders as (
SELECT '1' AS ORDERID,'77895' AS ORDERS,'3' AS ID UNION ALL SELECT '2' AS ORDERID,'44678' AS ORDERS,'3' AS ID UNION ALL SELECT '3' AS ORDERID,'22456' AS ORDERS,'1' AS ID UNION ALL SELECT '4' AS ORDERID,'24562' AS ORDERS,'1' AS ID UNION ALL SELECT '5' AS ORDERID,'34764' AS ORDERS,'65' AS ID )
select * from Persons a inner join orders b on a.id=b.id

SQL LEFT JOIN

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John'
address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10' as address ,'Beijing' as city )
, orders as (
SELECT '1' AS ORDERID,'77895' AS ORDERS,'3' AS ID UNION ALL
SELECT '2' AS ORDERID,'44678' AS ORDERS,'3' AS ID UNION ALL
SELECT '3' AS ORDERID,'22456' AS ORDERS,'1' AS ID UNION ALL
SELECT '4' AS ORDERID,'24562' AS ORDERS,'1' AS ID UNION ALL
SELECT '5' AS ORDERID,'34764' AS ORDERS,'65' AS ID )
select * from Persons a left join orders b on a.id=b.id

SQL RIGHT JOIN

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John' address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George' as address ,'New York' as city union all

as fistname ,'Oxford Street' as as fistname ,'Fifth Avenue'

as fistname ,'Oxford Street' as as fistname ,'Fifth Avenue'

select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10' as address ,'Beijing' as city )

, orders as (
SELECT '1' AS ORDERID,'77895' AS ORDERS,'3' AS ID UNION ALL SELECT '2' AS ORDERID,'44678' AS ORDERS,'3' AS ID UNION ALL SELECT '3' AS ORDERID,'22456' AS ORDERS,'1' AS ID UNION ALL SELECT '4' AS ORDERID,'24562' AS ORDERS,'1' AS ID UNION ALL SELECT '5' AS ORDERID,'34764' AS ORDERS,'65' AS ID )
select * from Persons a right join orders b on a.id=b.id

SQL FULL JOIN

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John' as fistname ,'Oxford Street' as address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George' as fistname ,'Fifth Avenue'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as fistname ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as fistname ,'Xuanwumen 10'
as address ,'Beijing' as city )
, orders as (
SELECT '1' AS ORDERID,'77895' AS ORDERS,'3' AS ID UNION ALL
SELECT '2' AS ORDERID,'44678' AS ORDERS,'3' AS ID UNION ALL
SELECT '3' AS ORDERID,'22456' AS ORDERS,'1' AS ID UNION ALL
SELECT '4' AS ORDERID,'24562' AS ORDERS,'1' AS ID UNION ALL
SELECT '5' AS ORDERID,'34764' AS ORDERS,'65' AS ID )
select * from Persons a full join orders b on a.id=b.id

SQL UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类 型。同时，每条 SELECT 语句中的列的顺序必须相同。

代码语言：javascript复制
with Employees_China as (
select '01' as e_id,'Zhang, Hua' as e_name union all
select '02' as e_id,'Wang, Wei' as e_name union all
select '03' as e_id,'Carter, Thomas' as e_name union all
select '04' as e_id,'Yang, Ming' as e_name )

, Employees_USA as (
select '01' as e_id,'Adams, John' as e_name union all
select '02' as e_id,'Bush, George' as e_name union all
select '03' as e_id,'Carter, Thomas' as e_name union all
select '04' as e_id,'Gates, Bill' as e_name)
select e_name from Employees_China
union
select e_name from Employees_USA
UNION ALL
UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。 with Employees_China as (
select '01' as e_id,'Zhang, Hua'
select '02' as e_id,'Wang, Wei'
select '03' as e_id,'Carter, Thomas'
select '04' as e_id,'Yang, Ming'
, Employees_USA as (
select '01' as e_id,'Adams, John'
select '02' as e_id,'Bush, George'
select '03' as e_id,'Carter, Thomas'
select '04' as e_id,'Gates, Bill'
select e_name from Employees_China
union all
select e_name from Employees_USA

SQL SELECT INTO

SQL SELECT INTO 语句可用于创建表的备份复件。 SELECT INTO 语句 SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。 SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

代码语言：javascript复制
with Employees_China as (
select '01' as e_id,'Zhang, Hua'
select '02' as e_id,'Wang, Wei'
select '03' as e_id,'Carter, Thomas'
select '04' as e_id,'Yang, Ming'
, Employees_USA as (
select '01' as e_id,'Adams, John'
select '02' as e_id,'Bush, George'
select '03' as e_id,'Carter, Thomas'
select '04' as e_id,'Gates, Bill'
,employess as (select e_name from Employees_China union all
select e_name from Employees_USA)

as e_name  union all
as e_name  union all
as e_name  union all
as e_name    )
as e_name union all
as e_name union all
as e_name union all
as e_name)
as e_name  union all
as e_name  union all
as e_name  union all
as e_name    )
as e_name union all
as e_name union all
as e_name union all
as e_name)
select * into employess_backup from employess

select * from employess_backup SQL create

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL 约束

SQL NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值。 NOTNULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录 或者更新记录。 下面的 SQL 语句强制 “Id_P” 列和 “LastName” 列不接受 NULL 值:

代码语言：javascript复制
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

代码语言：javascript复制
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。 主键必须包含唯一的值。 主键列不能包含 NULL 值。 每个表都应该有一个主键，并且每个表只能有一个主键

代码语言：javascript复制
CREATE TABLE Persons(
 Id_P int NOT NULL PRIMARY KEY, 
 LastName varchar(255) NOT NULL, 
 FirstName varchar(255), 
 Address varchar(255), City varchar(255) )

SQL FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。 下面的 SQL 在 “Orders” 表创建时为 “Id_P” 列创建 FOREIGN KEY:

代码语言：javascript复制
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

如果需要命名 FOREIGNKEY 约束，以及为多个列定义 FOREIGNKEY 约束，请使用下面的 SQL 语法:

代码语言：javascript复制
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),

CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

SQL CHECK 约束

CHECK 约束用于限制列中的值的范围。 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。 如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

代码语言：javascript复制
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

SQL DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

代码语言：javascript复制
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

CREATE INDEX

CREATE INDEX 语句用于在表中创建索引。 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。 索引 您可以在表中创建索引，以便更加快速高效地查询数据。 用户无法看到索引，它们只能被用来加速搜索/查询。 注释:更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身 也需要更新。因此，理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。

SQL CREATE VIEW

SQL CREATE VIEW 语句 什么是视图? 在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的 表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交 数据，就像这些来自于某个单一的表。 注释:数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

代码语言：javascript复制
create view employees as
with Employees_China as (
select '01' as e_id,'Zhang, Hua' as e_name union all
select '02' as e_id,'Wang, Wei' as e_name union all
select '03' as e_id,'Carter, Thomas' as e_name union all

select '04' as e_id,'Yang, Ming'
, Employees_USA as (
select '01' as e_id,'Adams, John'
select '02' as e_id,'Bush, George'
select '03' as e_id,'Carter, Thomas' as e_name union all select '04' as e_id,'Gates, Bill' as e_name) ,employess as (select e_name from Employees_China

union all
select e_name from  Employees_USA)
select *  from  employess
select * from employees
as e_name )

as e_name union all
as e_name union all

SQL DATE

SQL 日期

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式 相匹配。 只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点 复杂了。

在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。

函数

描述

GETDATE()

返回当前日期和时间

DATEPART()

返回日期/时间的单独部分

DATEADD()

在日期中添加或减去指定的时间间隔

DATEDIFF()

返回两个日期之间的时间

CONVERT()

用不同的格式显示日期/时间

代码语言：javascript复制
with orders as (
select '1' as orderid,'computer'
all
select '2' as orderid,'printer'
all
select '3' as orderid,'electrograph'
all
select '4' as orderid,'telephone'
select * from orders where OrderDate='2008-12-26'

as productname,'2008-12-26' as orderdate union
as productname,'2008-12-26' as orderdate union
as productname,'2008-11-12' as orderdate union
as productname,'2008-10-19' as orderdate)

SQL NULL 函数

代码语言：javascript复制
with Products as (
select '1' as p_id,'computer' as productname,CAST('699' AS INT) as unitprice,CAST('25' AS INT) as unitsinstock,CAST('15' AS INT) as unitsonorder union all
select '2' as p_id,'prCASTer' as productname,CAST('365' AS INT) as unitprice,CAST('36' AS INT) as unitsinstock,CAST('' AS INT) as unitsonorder union all
select '3' as p_id,'telephone' as productname,CAST('280' AS INT) as unitprice,CAST('159' AS INT) as unitsinstock,CAST('57' AS INT) as unitsonorder ) select ProductName,UnitPrice*(UnitsInStock ISNULL(UnitsOnOrder,0)) from Products

SQL Server 数据类型

Character 字符串:

数据类型

描述

存储

char(n)

固定长度的字符串。最多 8,000 个字符。

n

varchar(n)

可变长度的字符串。最多 8,000 个字符。

varchar(max)

可变长度的字符串。最多 1,073,741,824 个 字符。

text

可变长度的字符串。最多 2GB 字符数据。

Unicode 字符串:

数据类型

描述

存储

nchar(n)

固定长度的 Unicode 数据。最多 4,000 个字符。

nvarchar(n)

可变长度的 Unicode 数据。最多 4,000 个字符。

nvarchar(max)

可变长度的 Unicode 数据。最多 536,870,912 个 字符。

ntext

可变长度的 Unicode 数据。最多 2GB 字符数据。

Binary 类型:

数据类型

描述

存储

bit

允许 0、1 或 NULL

binary(n)

固定长度的二进制数据。最多 8,000 字节。

varbinary(n)

可变长度的二进制数据。最多 8,000 字节。

varbinary(max)

可变长度的二进制数据。最多 2GB 字节。

image

可变长度的二进制数据。最多 2GB。

Number 类型:

数据类型

描述

存储

tinyint

允许从 0 到 255 的所有数字。

1 字节

smallint

允许从 -32,768 到 32,767 的所有数字。

2 字节

int

允许从 -2,147,483,648 到 2,147,483,647 的所有 数字。

4 字节

bigint

允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。

8 字节

decimal(p,s)

固定精度和比例的数字。允许从 -10^38 1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数(小数点左侧和右 侧)。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。

5-17 字 节

numeric(p,s)

固定精度和比例的数字。允许从 -10^38 1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数(小数点左侧和右 侧)。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。

5-17 字 节

smallmoney

介于 -214,748.3648 和 214,748.3647 之间的货币 数据。

4 字节

money

介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。

8 字节

float(n)

从 -1.79E 308 到 1.79E 308 的浮动精度数字 数据。 参数 n 指示该字段保存 4 字节还是 8 字节。 float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的 默认值是 53。

4或8 字节

real

从 -3.40E 38 到 3.40E 38 的浮动精度数字数 据。

4 字节

Date 类型:

数据类型

描述

存储

datetime

从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。

8 bytes

datetime2

从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。

6-8 bytes

smalldatetime

从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精 度为 1 分钟。

4 bytes

date

仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。

3 bytes

time

仅存储时间。精度为 100 纳秒。

3-5 bytes

datetimeoffset

与 datetime2 相同，外加时区偏移。

8-10 bytes

timestamp

存储唯一的数字，每当创建或修改某行时，该数字会更 新。timestamp 基于内部时钟，不对应真实时间。每个表 只能有一个 timestamp 变量。

其他数据类型:

数据类型

描述

sql_variant

存储最多 8,000 字节不同数据类型的数据，除了 text、 ntext 以及 timestamp。

uniqueidentifier

存储全局标识符 (GUID)。

xml

存储 XML 格式化数据。最多 2GB。

cursor

存储对用于数据库操作的指针的引用。

table

存储结果集，供稍后处理。

SQL Server 中的合计函数

函数

AVG(column)

BINARY_CHECKSUM

CHECKSUM

CHECKSUM_AGG

COUNT(column)

COUNT(*)

COUNT(DISTINCT column)

FIRST(column) LAST(column)

MAX(column)

MIN(column)

STDEV(column)

STDEVP(column)

SUM(column)

VAR(column)

VARP(column)

希望计算 “OrderPrice” 字段的平均值。

代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)
as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'

描述

返回某列的平均值

返回某列的行数(不包括 NULL 值) 返回被选行数 返回相异结果的数目

返回在指定的域中第一个记录的值 (SQLServer2000 不支持)

返回在指定的域中最后一个记录的值 (SQLServer2000 不支持)

代码语言：javascript复制
    返回某列的最高值
    返回某列的最低值

返回某列的总和

代码语言：javascript复制
as orderprice,'Bush'
as
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100'
as customer)
select AVG(OrderPrice) AS OrderAverage from Orders
代码语言：javascript复制
希望找到 OrderPrice 值高于 OrderPrice 平均值的客户
代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)
as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100' as int)as orderprice,'Carter' as customer)
select * from Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
代码语言：javascript复制
COUNT() 函数
返回匹配指定条件的行数
代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)
as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100'
as customer)
select COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'
as int)as orderprice,'Carter'
         as orderprice,'Bush'
         as
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int) as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)

as orderprice,'Bush'
as
         as orderprice,'Bush'
         as
as int)as orderprice,'Carter'
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100' as int)as orderprice,'Carter' as customer)
select COUNT(*) AS NumberOfOrders FROM Orders

MAX() 函数

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)
as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100' as int)as orderprice,'Carter' as customer)
select MAX(OrderPrice) AS LargestOrderPrice FROM Orders

MIN() 函数

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)
as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100' as int)as orderprice,'Carter' as customer)
select MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

SUM() 函数

SUM 函数返回数值列的总数(总额)。

代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)
as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int)
orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100'
as customer)
select SUM(OrderPrice) AS OrderTotal FROM Orders

UPPER()函数

用于大小写转换

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John'
address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as FirstName ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as FirstName ,'Xuanwumen 10'
as address ,'Beijing' as city )
select upper(LastName) as LastName,lower(FirstName) FROM Persons

LEN()函数

用于求字符串长度

代码语言：javascript复制
with Persons as (
select '1' as id,'Adams' as lastname,'John'
address ,'London' as city union all
select '2' as id,'Bush' as lastname,'George'
as address ,'New York' as city union all
select '3' as id,'Carter' as lastname,'Thomas' as FirstName ,'Changan Street' as address ,'Beijing' as city union all
select '4' as id,'Gates' as lastname,'Bill' as FirstName ,'Xuanwumen 10'
as address ,'Beijing' as city )
select LEN(City) as LengthOfCity FROM Persons

GROUP BY函数

合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。

代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int)

as orderprice,'Bush'
         as orderprice,'Bush'
         as
as int)as orderprice,'Carter'
as FirstName ,'Oxford Street' as as FirstName ,'Fifth Avenue'

as FirstName ,'Oxford Street' as as FirstName ,'Fifth Avenue'

as customer union all
select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int) as orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100'
as customer)
select Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

代码语言：javascript复制
with Orders as (
select '1' as o_id,'2008/12/29' as orderdate,cast('1000' as int) as orderprice,'Bush' as customer union all

select '2' as o_id,'2008/11/23' as orderdate,cast('1600' as int) as orderprice,'Carter' as customer union all
select '3' as o_id,'2008/10/05' as orderdate,cast('700' as int)as orderprice,'Bush'
as customer union all
select '4' as o_id,'2008/09/28' as orderdate,cast('300' as int)as orderprice,'Bush'
as customer union all
select '5' as o_id,'2008/08/06' as orderdate,cast('2000'as int) as orderprice,'Adams' as customer union all
select '6' as o_id,'2008/07/21' as orderdate,cast('100'
as customer)
select Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

ROUND() 函数ROUND 函数用于把数值字段舍入为指定的小数位数。

代码语言：javascript复制
with Products as (
select '1' as prod_id,'gold'as productname,'1000 g' as unit, '32.35' as unitprice union all
select '2' as prod_id,'silver' as productname,'1000 g' as unit,'11.56' as unitprice union all
select '3' as prod_id,'copper' as productname,'1000 g' as unit,'6.85' as unitprice )

as int)as orderprice,'Carter'
as int)as orderprice,'Carter'
select ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

待补充…..

注：此文为本人参照w3cshcool教程（http://www.w3school.com.cn/sql/index.asp）所学后写出的文章，部分语句使用了w3cSQL

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/136894.html原文链接：https://javaforall.cn

sqlserver sql 数据库 云推荐引擎 python

0 人点赞