Microsoft SQL 数据库基础知识汇集

SQL： 数据库操作

数据库操作

简单查询

Select语句处理过程顺序如下：

From

Where

Group by

Having

Select

Order by

检索所有的行

数据库内容见附录，实际数据表的内容（行）有添加

检索所有的列和所有的行

SELECT user_id,username,email,age

  FROM users;

SELECT *

  FROM users;

从所有的行中检索指定的列

SELECT user_id,age

  FROM users;

使用distinct

SELECT distinct email

  FROM users;

计算字段

SELECT user_id,age*1.0/12 as cage

  FROM users;

列选择

五个条件的基本运算

比较：

比较两个表达式的值

范围：

测试表达式的值是否在指定的范围中

成员关系

测试表达式是否在某一值集合内

模式匹配

测试字符串是否与指定模式相匹配

空

测试列是否为空值

比较运算符作为查找条件

SQL 语句中可用的比较运算符如下：

= 等于

<>不等于 （!= 不等于，部分方言这样用）

<小于

>大于

>= 大于等于

<=小于等于

SELECT username,age

  FROM users where age > 30;

复合比较运算作为查找条件

复杂的谓词可由逻辑运算符and,or和not产生，必要或期望时可用括号表示计算的顺序。计值表达式的规则如下：

计值顺序由左到右

首先计算括号中子表达式的值

Not优先于and和or

And 优先于or

可运用括号消除歧义

SELECT user_id,username,age

  FROM users where age > 30 or user_id=1;

范围作为查找条件

可以使用not在between之前

SELECT user_id,username,age

  FROM users where age between 20 and 40;

SELECT user_id,username,age

  FROM users where (age between 20 and 40) or user_id = 5;

集合成员测试作为查找条件

可以使用not在in之前

SELECT user_id,username,age

  FROM users where age in (30,100);

In测试不能增强SQL的表达功能，可以使用多个or 或者and来代替

模式匹配作为查找条件

% 百分号表示零或多个字符序列（通配符）

_ 下划线表示任意单个字符

转义

SELECT user_id,username,age

  FROM users where email like '$%__' escape '$';

这里使用$ 作为转义字符

可以使用not在like之前

空查找条件

NULL值不可以使用（无法使用）相等或者不相等来查找到。

Is null， is not null

SELECT user_id,username,age

  FROM users where age is null;

查询结果排序

升序Asc  ascending

降序 desc descending

Order by 子句都只能是select语句的最后一个子句

出现在select列表中（某些实现版本）

列可以不出现在查询结果中

单列排序

select user_id,username

from users order by age;

SELECT user_id,username,age

  FROM users order by username;

注意，还可以这样表达order by子句， order by 2 asc

和上述查询结果一致（为人诟病）

多列排序

SELECT user_id,username,age

  FROM users order by age,user_id desc;

Order by 子句可能包含多个元素，主排序关键字决定查询结果总体的排序。可以增加一个排序关键字来控制主排序关键字相同的那些行的顺序。Order by子句中的第二个元素也成为次排序关键字。

使用SQL聚集函数

ISO标准定义了五个聚集函数

Count 返回指定列中数据的个数

Sum返回指定列中数据的总和

Avg返回指定列中数据的平均值

Min返回指定列中数据的最小值

Max返回指定列中数据的最大值

这些函数只对表中的单个列进行操作，返回一个值。Count，min，和max可以用于数值和非数值字段，而sum和avg只能用于数值字段，除了count（*）外，每一个函数首先要去掉空值，然后计算其非空值。Count(*)是count的特殊用法，计算表中的所有行的数目，而不管是否有空值或重复出现。

Count(*)的使用

查询user_id = 1的订单的数目

select count(*) from orders where user_id = 1;

查询下订单的人的数目

select count(distinct user_id) as [user] from orders;

Count和sum的使用

select count(user_id) as 'people',sum(total_amount) as 'sum' from orders;

Min，Max，和Avg的使用

select max(total_amount),min(total_amount),avg(total_amount)

   from orders;

查询结果分组（group by）

在使用GROUP BY子句时，SELECT子句中的列应该要么包含在GROUP BY子句中，要么通过聚合函数进行汇总。

当使用group by 时，select列表中的项必须每组都有单一值。

Group by 的使用

select max(order_id) as max,user_id from orders group by user_id;

分组约束

Having子句的设计意图是与group by子句一起使用，来限定哪些分组将出现在最终的查询结果中。不是必须的。Having子句使用的列名必须出现在group by的子句列表中，或包含在聚集函数中。

分组的使用

select max(order_id) as "max",user_id from orders

    group by user_id having max(order_id) > 102;

实际上是先查询分组再过滤吗？ 确实也是如此，这样实际上只是起了过滤的作用。

在Microsoft SQL Server Manager中，order by ，和group by 可以使用select中没有选择的表中的列，但是最好添加上。

子查询

内部select语句（子查询）的结果用在外部语句中以决定最后的查询结果。子查询可以被使用在外部select语句的where和having子句中，成为子查询或者嵌套查询。

子查询也可以出现在insert，update和delete的语句中。

子查询有3种类型：

标量子查询返回单个列和单个行，即单个值。原则上，标量子查询可用于任何需要单个值的地方。

行子查询返回多个列，但只有单个行。行子查询可用于任何需要行值构造器的时候，如在谓词中。

表子查询返回多个行，每行有一个或多个列。表子查询用于需要一个表的情况。例如： 作为谓词In的操作数。

用于相等判断的子查询

select * from orders where user_id =

    (select user_id from users where age = 25);

在这个SQL查询中，如果子查询返回空（即没有与年龄等于25的用户匹配的行），那么子查询的结果将是一个空结果集，而不是NULL。这意味着子查询不会返回任何行，而不是返回一个包含NULL值的行。

然后，主查询中的WHERE子句将与一个空结果集进行比较，这将导致主查询也返回一个空结果集，而不是NULL。在SQL中，当条件不成立时，查询的结果通常是一个空结果集，而不是NULL。因此，如果子查询返回空，主查询也将返回一个空结果集。

用于聚集函数的子查询

查询列 用户id，高于平均年龄的部分

查询范围：

年龄大于最低年龄或者为null的行

select user_id,age -(select avg(age) from users) as "upper" from users

where age > (select Min(age) from users) or age is null order by "upper";

嵌套子查询-in的使用

查询下过单的用户

SELECT u.user_id, u.username, u.email, u.age

FROM users u

INNER JOIN orders o ON u.user_id = o.user_id;

使用子查询

SELECT user_id, username, email, age

FROM users

WHERE user_id IN (SELECT DISTINCT user_id FROM orders);

查询下单次数超过一次的用户

SELECT user_id, username, email, age

FROM users

WHERE user_id IN (

    SELECT user_id

    FROM orders

    GROUP BY user_id

    HAVING COUNT(*) > 1

);

Any和All

关键字any和some用于产生单个列的子查询。若子查询前缀关键字ALL，那么仅当子查询产生的所有值都满足条件时，条件才为真，若子查询前缀关键字any，那么子查询产生的任何一个值（一个或多个）满足条件时，条件就为真。如果子查询是空值，all条件返回真值，any条件返回加值。可以使用some代替any。    

Any的使用

查询用户中年龄大于下过单的用户的最低年龄的所有用户

select * from users where age >

    any(select age from users where user_id in

         (select user_id from orders)

);

SELECT u.*

FROM users u

WHERE EXISTS (

    SELECT 1

    FROM orders o

    WHERE o.user_id = u.user_id

) AND u.age > ANY (

    SELECT age

    FROM users

    WHERE user_id IN (SELECT user_id FROM orders)

);

我知道了，但是现在我想了解EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.user_id )是如何判断一个用户是否下过单的

ChatGPT

EXISTS子查询用于判断是否存在符合特定条件的记录，如果子查询返回至少一行结果，那么条件为真。在您的情况下，子查询判断是否存在与用户相关的订单记录，如果存在至少一行这样的记录，就表示用户下过单。

具体来说，子查询 (SELECT 1 FROM orders o WHERE o.user_id = u.user_id) 在内部会检查是否有满足条件 o.user_id = u.user_id 的订单记录存在。如果存在这样的订单记录，那么 EXISTS子查询返回1，表示条件为真。如果没有满足条件的记录，EXISTS子查询返回空，表示条件为假。

所以，EXISTS子查询用于判断是否至少存在一个与用户相关的订单记录，如果存在，那么用户就被认为下过单。

select 1 from orders where order_id > 101;

All的使用

不在赘述

多表查询

很多情况下，一个表不够，要把来自多个表的列组合到结果表时，就需要用到连接操作。SQL连接操作通过配对相关行来合并两个表中的信息。而构成连接表的配对行是指这两行在这两个表的匹配列上具有相同的值。

要从多个表中得出查询结果，可用子查询，也可用连接操作。如果最终结果表中包括了许多个表中的列，则必须用连接操作。

简单链接

select s.*,o.* from users s join orders o on s.user_id = o.user_id;

排序连接结果

三表连接

使用where

按多个列分组

Cross Join

CROSS JOIN 是 SQL 中用于获取两个表的笛卡尔积的操作。它返回两个表中所有行的组合，生成一个新的结果集。

语法上，CROSS JOIN 没有指定连接条件，它简单地将左侧表的每一行与右侧表的每一行进行组合。结果集的行数等于左侧表的行数乘以右侧表的行数。

下面是 CROSS JOIN 的基本语法：

SELECT *

FROM table1

CROSS JOIN table2;

在这个语法中，table1 和 table2 是要进行组合的表。* 表示选择所有列，您也可以指定特定的列。

让我们通过一个示例来说明 CROSS JOIN 的使用。假设我们有两个表：Customers 和 Products。

Table: Customers

+----+----------+

| ID | Customer |

+----+----------+

| 1  | Alice    |

| 2  | Bob      |

+----+----------+

Table: Products

+----+----------+

| ID | Product  |

+----+----------+

| 1  | Laptop   |

| 2  | Printer  |

| 3  | Monitor  |

+----+----------+

如果我们执行以下 CROSS JOIN 查询：

SELECT *

FROM Customers

CROSS JOIN Products;

结果将是两个表的笛卡尔积：

+----+----------+----+---------+

| ID | Customer | ID | Product |

+----+----------+----+---------+

| 1  | Alice    | 1  | Laptop  |

| 1  | Alice    | 2  | Printer |

| 1  | Alice    | 3  | Monitor |

| 2  | Bob      | 1  | Laptop  |

| 2  | Bob      | 2  | Printer |

| 2  | Bob      | 3  | Monitor |

+----+----------+----+---------+

注意到结果集中的每一行都是 Customers 表中的一行与 Products 表中的一行的组合。

需要注意的是，CROSS JOIN 可能会产生非常大的结果集，特别是当两个表的行数较大时。因此，在使用 CROSS JOIN 时要谨慎，确保结果集的大小是可接受的。

总结一下，CROSS JOIN 提供了一种获取两个表的笛卡尔积的方法，它将两个表的所有行进行组合，生成一个新的结果集。

外连接

左外连接

Left join

以左为主

右外连接

Right join

以右为主

全外连接

Full join

左外连接+右外连接

Exists 和 not Exists

关键字exists 和 not exists 仅用于子查询中，返回结果为真/假。 Exists 为真当且仅当子查询返回的结果表示至少存在一行。

特殊格式

Mod

在SQL中，MOD()和%操作符都用于执行取模运算，它们的作用是相同的，即计算两个数相除后的余数。MOD()是SQL标准函数，而%是一种常见的SQL操作符，通常用于执行取模运算。

日期

相隔一天

MySQL:

SELECT *

FROM your_table

WHERE DATEDIFF(end_date, start_date) = 1;

PostgreSQL:

sql

SELECT *

FROM your_table

WHERE end_date - start_date = INTERVAL '1 day';

Microsoft SQL Server:

sql

SELECT *

FROM your_table

WHERE DATEDIFF(day, start_date, end_date) = 1;

这些示例假设你有一个表（your_table）包含两个日期列（start_date和end_date）

字符串

长度

Ms

SELECT *

FROM your_table

WHERE LEN(content) > 10;

PostgreSQL 或 MySQL：

SELECT *

FROM your_table

WHERE LENGTH(content) > 10;

Leetcode

197. 上升的温度

select id as Id from Weather w1 where w1.Temperature >

  (select Temperature from Weather w2 where

    datediff(day,w1.recordDate,w2.recordDate)=-1

  );

1581. 进店却未进行过交易的顾客

SELECT v.customer_id,count(*) as count_no_trans

  FROM Visits v

       left JOIN Transactions t

       ON v.visit_id = t.visit_id

     where t.transaction_id is null group by v.customer_id;

1661. 每台机器的进程平均运行时间

SELECT

    machine_id,

    ROUND(

        AVG(timestamp_end - timestamp_start),

        3

    ) AS processing_time

FROM (

    SELECT

        machine_id,

        process_id,

        MAX(CASE WHEN activity_type = 'start' THEN timestamp END) AS timestamp_start,

        MAX(CASE WHEN activity_type = 'end' THEN timestamp END) AS timestamp_end

    FROM Activity

    GROUP BY machine_id, process_id

) AS process_times

GROUP BY machine_id;

SELECT

    machine_id,

    ROUND(

        (SUM(CASE WHEN activity_type = 'end' THEN timestamp END) -

         SUM(CASE WHEN activity_type = 'start' THEN timestamp END)) / COUNT(DISTINCT process_id)

        , 3

    ) AS processing_time

FROM Activity

GROUP BY machine_id;

1280. 学生们参加各科测试的次数

select s.student_id,s.student_name,b.subject_name,count(e.subject_name) as attended_exams

    from Students s cross join Subjects b left join Examinations e on s.student_id = e.student_id and b.subject_name = e.subject_name

     group by s.student_id,s.student_name,b.subject_name order by s.student_id,b.subject_name

    ;

Cross join

附录

创建数据表和数据

Users和orders表

CREATE TABLE users (

    user_id INT PRIMARY KEY,

    username VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL,

    age INT

);

-- 插入示例数据到用户表

INSERT INTO users (user_id, username, email, age)

VALUES

    (1, 'JohnDoe', 'john@example.com', 30),

    (2, 'JaneSmith', 'jane@example.com', 25),

    (3, 'BobJohnson', 'bob@example.com', 35);

-- 创建订单表

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    user_id INT,

    order_date DATE,

    total_amount DECIMAL(10, 2)

);

-- 插入示例数据到订单表

INSERT INTO orders (order_id, user_id, order_date, total_amount)

VALUES

    (101, 1, '2023-01-15', 150.00),

    (102, 2, '2023-01-20', 200.00),

(103, 1, '2023-02-10', 75.50);

Visits，Transactions表

CREATE TABLE Visits (

    visit_id INT PRIMARY KEY,

    customer_id INT

);

CREATE TABLE Transactions (

    transaction_id INT PRIMARY KEY,

    visit_id INT,

    amount INT

);

INSERT INTO Visits (visit_id, customer_id) VALUES

(1, 23),

(2, 9),

(4, 30),

(5, 54),

(6, 96),

(7, 54),

(8, 54);

INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES

(2, 5, 310),

(3, 5, 300),

(9, 5, 200),

(12, 1, 910),

(13, 2, 970);

Weather表

-- 创建 Weather 表

CREATE TABLE Weather (

    id INT PRIMARY KEY,

    recordDate DATE,

    Temperature INT

);

-- 插入数据

INSERT INTO Weather (id, recordDate, Temperature) VALUES

(1, '2015-01-01', 10),

(2, '2015-01-02', 25),

(3, '2015-01-03', 20),

(4, '2015-01-04', 30);

Activity表

-- 创建 Activity 表

CREATE TABLE Activity (

    machine_id INT,

    process_id INT,

    activity_type NVARCHAR(10),

    timestamp FLOAT,

    PRIMARY KEY (machine_id, process_id, activity_type)

);

-- 插入数据

INSERT INTO Activity (machine_id, process_id, activity_type, timestamp) VALUES

(0, 0, 'start', 0.712),

(0, 0, 'end', 1.520),

(0, 1, 'start', 3.140),

(0, 1, 'end', 4.120),

(1, 0, 'start', 0.550),

(1, 0, 'end', 1.550),

(1, 1, 'start', 0.430),

(1, 1, 'end', 1.420),

(2, 0, 'start', 4.100),

(2, 0, 'end', 4.512),

(2, 1, 'start', 2.500),

(2, 1, 'end', 5.000);

Students,Subjects,Examinations 表

CREATE TABLE Students (

  student_id INT PRIMARY KEY,

  student_name VARCHAR(50)

);

INSERT INTO Students (student_id, student_name)

VALUES

  (1, 'Alice'),

  (2, 'Bob'),

  (13, 'John'),

  (6, 'Alex');

CREATE TABLE Subjects (

  subject_name VARCHAR(50) PRIMARY KEY

);

INSERT INTO Subjects (subject_name)

VALUES

  ('Math'),

  ('Physics'),

  ('Programming');

CREATE TABLE Examinations (

  student_id INT,

  subject_name VARCHAR(50)

);

INSERT INTO Examinations (student_id, subject_name)

VALUES

  (1, 'Math'),

  (1, 'Physics'),

  (1, 'Programming'),

  (2, 'Programming'),

  (1, 'Physics'),

  (1, 'Math'),

  (13, 'Math'),

  (13, 'Programming'),

  (13, 'Physics'),

  (2, 'Math'),

  (1, 'Math');