SqlServer数据库——查询，索引，python连接数据库（第三篇）_sqlserver 查询索引

一，数据修改，删除

1.1数据的修改

1. 在 SQL Server 中，可以使用 UPDATE 语句修改表中的数据。UPDATE 语句的基本语法如下：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; 其中，table_name 是要修改数据的表名，column1、column2 等是要修改的列名，value1、value2 等是要将列修改为的新值，condition 是筛选条件，用于确定要修改哪些行。如果没有指定 condition，则会修改表中的所有行。
2. 例如：

update Student set Name='王力宏',Age=36
where Sto=2
1
2

1.2数据的删除

1. 在 SQL Server 中，可以使用 DELETE 语句删除表中的数据。DELETE 语句的基本语法如下：DELETE FROM table_name WHERE condition; 其中，table_name 是要删除数据的表名，condition 是筛选条件，用于确定要删除哪些行。如果没有指定 condition，则会删除表中的所有行。
2. 例如：
   

delete from Student where Sto=8
1

二，数据库的查询

2.1条件查询语句

1.在SQL Server中，可以使用SELECT语句来查询数据。SELECT语句的一般语法如下：

 SELECT [column1,column2,...columnN] FROM [table_name] WHERE [condition];
1

其中，column1,column2,…columnN是要查询的列名，可以是单个列名，也可以是多个列名用逗号分隔。table_name是要查询的表名，condition是可选的查询条件，它用于限制查询结果。

2.将查询的数据对应的列名起别名：select 列名 as 新列名 from 表名 where condition

搜索表中所有Sto,Name，Age列并将这Sto,Name 列改新名字：

select Sto as 唯一表示,Name as 姓名,Age
from Student 
1
2

,3. 查询某一范围内的数据，对于查询范围要求小值放前面，大值放后面，between 小值 and 大值

select * from Student
where sno between 1000 and 1500
1
2

对于查询的结果以order排序默认升序：

select * from Student
where sno between 1000 and 1500 order by score1
1
2

对于上述数据再取前五十：

select top 50 * from Student
where sno between 1000 and 1500 order by score1 
1
2

2.2高级查询语句

2.2.1case when查询

1. 在SqlServer中，使用CASE WHEN语句可以在查询中根据条件执行不同的操作，下面是一个基本的例子，假设我们有一个名为Employees的表格，其中包含FirstName和LastName两个列：

SELECT FirstName, LastName, 
       CASE WHEN LastName = 'Smith' THEN 'Manager' 
            ELSE 'Employee' 
       END AS JobTitle
FROM Employees;
1
2
3
4
5

这个查询将返回一个名为JobTitle的新列，如果LastName是Smith，则将其设置为Manager，否则设置为Employee。
2. 你也可以使用多个WHEN子句和一个ELSE子句来执行更复杂的条件：

SELECT FirstName, LastName, 
       CASE WHEN LastName = 'Smith' THEN 'Manager' 
            WHEN LastName = 'Jones' THEN 'Supervisor'
            WHEN LastName = 'Doe' THEN 'Coordinator'
            ELSE 'Employee' 
       END AS JobTitle
FROM Employees;
1
2
3
4
5
6
7

这个查询将根据LastName的值返回不同的JobTitle。如果LastName是Smith，则JobTitle为Manager；如果LastName是Jones，则JobTitle为Supervisor；如果LastName是Doe，则JobTitle为Coordinator；否则JobTitle为Employee。
3. 实例：对各分数段进行划分，60-70为及格，70-80为良好，80-100为优秀

select top 50 sno,name,score1 ,
	case when score1>60 and score1<70 then '及格'
		 when score1>70 and score1<80 then '良好'
		 when score1>80 and score1<100 then '优秀'
	end as '评级'
from Student
where sno between 1000 and 1500 order by score1 
1
2
3
4
5
6
7

2.2.2模糊匹配like查询

1. 在 SQL Server 中，可以使用 LIKE 操作符进行模糊查询，LIKE 操作符用于比较字符串的相似度，支持使用通配符进行模糊匹配。
   下面是一些使用 LIKE 操作符进行模糊查询的示例：

（1）匹配以指定字符串开头，结尾的所有记录：

SELECT * FROM table_name WHERE column_name LIKE 'search_string%'
SELECT * FROM table_name WHERE column_name LIKE '%search_string'

1
2
3

（2）匹配包含指定字符串的所有记录：

SELECT * FROM table_name WHERE column_name LIKE '%search_string%'

1
2

其中，通配符 % 表示匹配任意字符，可以出现在字符串的任意位置，包括字符串开头和结尾；通配符 _ 表示匹配单个字符，可以用来限定字符串中某个位置的字符。
常见运算符：
（1）条件运算符：>、>=、<、<=、=、<>（不等于）、!=（不等于）、!<（不小于）、!>（不大于）
（2）逻辑运算符：and、or、not
（3）模糊运算符：
(4)like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义,between and,not between and,in,not in,is null,is not null
（3）例如，可以使用以下语句匹配包含 “apple” 的所有记录：

SELECT * FROM table_name WHERE column_name LIKE '%apple%'

1
2

（4）如果要匹配包含 6 个字符的字符串，可以使用以下语句：

SELECT * FROM table_name WHERE column_name LIKE '______'

1
2

这里使用了 6 个下划线 _ 作为通配符，表示匹配任意 6 个字符的字符串。

2.2.3with语句（类似于封装-便于调用）

1. WITH语句是一种在SQL查询中定义临时表的方法。它被称为"公共表表达式"（Common Table Expression，CTE）。WITH语句通常用于简化复杂查询的编写和维护

WITH cte_name (column1, column2, ...) AS (
    SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING ...
)
SELECT ...
FROM cte_name
1
2
3
4
5
6
7
8
9

这里的cte_name是定义的公共表表达式的名称，可以在后续的查询中使用它。括号中是SELECT语句，用于定义公共表。公共表的列名也可以在括号中列出。
在WITH语句中定义的公共表只在当前查询中可用，它们不会持久保存在数据库中。在查询结束后，它们就会被销毁。
WITH语句的主要优点在于，它可以简化复杂查询。通常，复杂的查询可能需要嵌套多个子查询才能得到所需的结果。使用WITH语句可以将这些子查询提取出来，定义为公共表，使查询更加清晰易懂。
2. 实例1：:

with tt as
	(select top 50 sno,name,score1 ,
		case when score1>60 and score1<70 then '及格'
			 when score1>70 and score1<80 then '良好'
			 when score1>80 and score1<100 then '优秀'
		end as '评级'
	from Student
	where sno between 1000 and 1500 order by score1 )
select * from tt
1
2
3
4
5
6
7
8
9

3. 实例二

--WITH 
WITH  select_mesg As (SELECT Top(5) * FROM GradeTable gt 
WHERE gt.成绩 >='80'order BY 成绩 DESC)

SELECT * FROM select_mesg
1
2
3
4
5

刚开始未指定TOP导致出错：

2.2.4去重查询-distinct

1. 例如，假设有一个名为 employees 的表，其中包含员工信息，包括姓名和工资。要查询所有不同的工资数值，可以执行以下 SQL 语句

SELECT DISTINCT salary FROM employees;
1

2. 实例：查询学生StudentTable所有专业类型（要求去重）
   

SELECT DISTINCT 专业 as 专业类型 from StudentTable 
1

2.2.5分组查询，并统计个数-group by

1. 按照学生专业对学生进行分组，查询分组后每个专业的名称以及所对应的学生人数：
   select 专业,COUNT(*) as 学生人数 from StudentTable group by 专业;

2. 查询结果：
   

2.2.6排序查询-order by

1. 语法：

select 【*/字段名,...】 from 【表名/查询结果集】
【where 查询条件】
【group by 分组条件】
【having 过滤条件】
order by 排序字段 asc|desc;

1
2
3
4
5
6

2. 对课程号为**“101”的课程所对应的学生成绩进行降序**排序

select * from GradeTable 
where 课程号='101' ORDER by 成绩 DESC 
1
2

2.2.7过滤查询-having

1. 格式：

select 【*/字段名,...】 from 【表名/查询结果集】
【where 查询条件】
group by 分组条件
having 过滤条件;
1
2
3
4

2. 按照课程号对课程进行分组，查询当前课程下所对应的平均成绩大于75分的课程号信息：

select 课程号,AVG(成绩) from GradeTable  group by 课程号 having AVG(成绩) > 75;

1
2

2.2.8多表查询

1. 格式：

select t1.*,t2.*,... from 表1 t1, 表2 t2, ...
where 连接条件
【and 查询条件】
【group by 分组条件】
【having 过滤条件】
【order by 排序字段 asc|desc】;
1
2
3
4
5
6

2. 案例演示1：查询每一位学生选修课程的名称以及成绩，要求输出学生学号、姓名、课程名、成绩

SELECT st.学号,st.姓名,ct.课程名,gt.成绩 from StudentTable st ,CourseTable ct ,GradeTable gt 
where  st.学号 =gt.学号 and gt.课程号 =ct.课程号 
1
2

由于要查询的列位于多张表，所以要先将多张表根据链接条件链接，链接条件也就是两张表互相都拥有的公共部分：

3. --查询学号为“081101”学生选修 课程的名称以及 成绩，要求输出学生学号、姓名、课程名、成绩（从三张表查询）

SELECT st2.学号 ,st2 .姓名 ,ct2.课程号 ,gt2.成绩 from CourseTable ct2,GradeTable gt2,StudentTable st2 
where ct2.课程号 =gt2.课程号 and st2.学号 =gt2.学号 and  gt2.学号 ='081101'

1
2
3

2.2.9子查询

1. 格式：

SELECT column1, column2, column3
FROM table1
WHERE column1 exists (
    SELECT column1
    FROM table2
    WHERE column4 = 'value'
);
1
2
3
4
5
6
7

在这个例子中，子查询 SELECT column1 FROM table2 WHERE column4 = ‘value’ 返回一个包含满足条件的行的列表，然后外部查询使用 WHERE column1 IN 子句从 table1 中选择所有满足该列表中条件的行。

2. 查找选修了离散数学的学生学号

SELECT gt3.学号  from GradeTable gt3 
WHERE 课程号=(SELECT 课程号 from CourseTable ct3 WHERE 课程名='离散数学')
1
2

3. 查找比所有计算机系的学生年龄都小的学生

SELECT * from StudentTable 
WHERE 出生时间>(select MAX(出生时间) from StudentTable where 专业='计算机')
1
2

2.2.10多表查询与子查询区别

• 在SQL Server中，多表查询和子查询都是用于从多个表中检索数据的方法，但它们有一些区别。
  （1）多表查询是指在一个SQL语句中同时使用多个表，并使用连接条件将它们关联起来。多表查询的主要优点是可以一次性获取多个表的数据，避免了多次查询带来的性能损失。多表查询通常使用JOIN操作符来连接两个或多个表，可以通过INNER JOIN，LEFT JOIN，RIGHT JOIN和FULL OUTER JOIN来指定不同的连接类型。
  （2）子查询是指在一个查询中嵌套另一个查询，通常用于在内部查询中执行一些复杂的操作，然后将结果传递给外部查询。子查询可以嵌套多层，但需要注意，子查询可能会影响整体查询的性能。
  （3）多表查询和子查询的主要区别在于它们的使用方式和效率。多表查询更适合用于需要检索多个表中的相关数据的情况，而子查询则更适合用于需要在内部执行一些复杂操作的情况。在性能方面，多表查询通常比子查询更快，因为它们可以一次性获取多个表的数据。但是，当需要使用子查询时，可以通过优化查询语句和创建索引来提高性能。

2.3分页查询

1. 之所以要分页，是因为查询到的数据太多，可能一次不能展示完，为了能够更好的展示数据，分成许多页，控制每一页显示的数据条数。
2. 在SQL Server中实现分页查询可以使用OFFSET和FETCH语句。OFFSET子句指定从查询结果中的第几行开始返回记录，FETCH子句指定要返回的记录数。以下是一个示例查询，返回从第11行开始的10条记录：

SELECT column1, column2, column3
FROM table_name
ORDER BY column1
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
1
2
3
4
5

上面的查询中，column1, column2, column3是要返回的列名，table_name是要查询的表名，ORDER BY指定了排序方式。OFFSET 10 ROWS表示从结果集中的第11行开始返回记录，FETCH NEXT 10 ROWS ONLY表示返回10条记录。您可以将OFFSET和FETCH子句中的数字更改为适合您的需求的数字。
3. 如果您需要在查询中使用变量来实现分页，则可以使用以下查询：

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10

SELECT column1, column2, column3
FROM table_name
ORDER BY column1
OFFSET (@PageNumber - 1) * @RowspPage ROWS
FETCH NEXT @RowspPage ROWS ONLY;

1
2
3
4
5
6
7
8
9
10

在上面的查询中，@PageNumber变量指定要返回的页数，@RowspPage变量指定每页要返回的记录数。OFFSET子句中的计算式(@PageNumber - 1) * @RowspPage将计算出要跳过的行数。FETCH子句将返回指定数量的记录。您可以更改@PageNumber和@RowspPage变量的值来返回不同的页数和记录数。

2.4连接查询

2.4.1左连接查询

1. 在 SQL Server 中，使用左连接可以在两个或多个表之间建立关联，以便返回满足指定条件的结果集。左连接将返回左表中所有行，无论是否有匹配的行在右表中，如果在右表中没有匹配到，则会返回 NULL 值。
   
2. 左连接语法格式：

SELECT [column1], [column2], ...
FROM [table1]
LEFT JOIN [table2]
ON [table1].[column_name] = [table2].[column_name];
1
2
3
4

在上面的语法中，table1 是左表，table2 是右表，column_name 是两个表之间用来进行匹配的列名，column1，column2 等是需要返回的列名。

3. 例如，我们有两个表，TableA 和 TableB，其中 TableA 包含 ID、Column1 和 Column2，TableB 包含 ID、Column3 和 Column4，我们想要从这两个表中返回 Column1 和 Column3 的值，同时按照 TableA 的 ID 列与 TableB 的 ID 列进行匹配，则可以使用以下 SQL 语句：

SELECT TableA.Column1, TableB.Column3
FROM TableA
LEFT JOIN TableB
ON TableA.ID = TableB.ID;
1
2
3
4

这个查询将会返回 TableA 表中的所有行，并且对于那些在 TableB 表中没有对应行的记录也会返回空值。如果要查询所有列，可以使用通配符 *

4. 实例：
   （1）建表：

CREATE TABLE TableA (
  ID INT PRIMARY KEY,
  Column1 VARCHAR(50),
  Column2 INT
);
CREATE TABLE TableB (
  ID INT PRIMARY KEY,
  Column3 VARCHAR(50),
  Column4 INT
);
1
2
3
4
5
6
7
8
9
10

（2）插入数据：

INSERT INTO TableA (ID, Column1, Column2)
VALUES (1, 'Value 1', 10),
       (2, 'Value 2', 20),
       (3, 'Value 3', 30);
INSERT INTO TableB (ID, Column3, Column4)
VALUES (1, 'Value A', 100),
       (3, 'Value C', 300),
       (4, 'Value D', 400);
1
2
3
4
5
6
7
8

（3）左连接查询：

SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.ID = TableB.ID
1
2
3
4

TableA:

TableB:
左连接查询：

2.4.2右连接查询

1. SQL Server中的右连接（right join）是一种用于连接两个表的SQL查询语句，它返回两个表中所有右表（第二个表）的记录以及匹配的左表（第一个表）中的记录。
2. 右连接语法格式：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
1
2
3
4

其中，table1是左表，table2是右表，column_name是两个表共有的列名，ON关键字指定了连接的条件。

3. 右连接的查询结果包括以下内容：
   （1）包括右表中的所有记录，左表中没有匹配的记录用 NULL 填充
   （2）仅包括左表中与右表匹配的记录

4. 右连接通常用于在左表中找到与右表匹配的记录。如果需要返回左表中所有记录以及匹配的右表记录，可以使用左连接（left join）。

2.4.3内连接查询

5. SQL Server中的内连接（inner join）是一种用于连接两个表的SQL查询语句，它返回两个表中都有匹配的记录。（公共部分）
6. 内连接语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
1
2
3
4

其中，table1和table2是要连接的两个表，column_name是这两个表共有的列名，ON关键字指定了连接的条件

7. 内连接的查询结果包括以下内容：
   （1）仅包括两个表中都有匹配的记录
   （2）内连接通常用于从两个表中获取相关的数据。如果需要返回一个表中的

三，用Python链接SqlServer数据库

3.1安装第三方库pymssql

1. 在命令行窗口输入：pip install pymssql
   

3.2链接数据库

import pymssql
connect=pymssql.connect(host='.',port = '1234',user='sa',password='123456',database='DataTest3')
#服务器名称：'localhost',port = '1234':用的是TCP动态端口，在SqlServer管理器中设置查看。动态用户名：'sa',密码：'123456',数据库名：'DataTest3'
if connect:
    print("链接成功！")
1
2
3
4
5

在Python中连接MySQL数据库时，本地IP地址需要使用“127.0.0.1”或“localhost”来表示。这是因为MySQL默认情况下不支持使用“.”代替IP地址中的数字，因此在连接MySQL数据库时必须使用正确的IP地址。

相比之下，在SqlServer中，使用“.”代替IP地址是一个常见的惯例，因为SqlServer支持将“.”解释为本地IP地址。这个功能被称为“本地实例名称”，它允许您使用类似于“.\SQLEXPRESS”这样的名称来连接本地SQL Server实例，而无需知道实际的IP地址。

总的来说，Python连接MySQL和SqlServer数据库时，在输入本地IP地址时需要遵循不同的规则，这是因为两个数据库管理系统的实现方式不同。

3.2.1链接报错

1. 刚开始port我填写的是1433，且我的TCP动态端口并未设置，导致链接出错：pymssql._pymssql.OperationalError: (20009, b’DB-Lib error message 20009, sev…
2. 解决：
   （1）进入SqlServer配置管理器：
   
   （2）点击如图协议并启用TCP/IP,进入内部：

（3）TCP动态端口如果有数值，则在python语句中的port参数填入对应数值，如果没有，可自定义一个数值，并传给port参数。


（4）成功链接

3.3通过python向数据库添加50万条数据

3.3.1SqlServer建表，定义各列名称

create table Student(
sto int,
name varchar,
socre1 int,
score2 int,
score3 int)
1
2
3
4
5
6

3.3.2插入数据

import pymssql
import random
import time
#01SqlServer数据库链接
cnxn=pymssql.connect(
    host='.',
    port = '1234',
    user='sa',
    password='123456',
    database='DataTest3')
#服务器名称：'localhost',port = '1234':用的是TCP动态端口，在SqlServer管理器中设置查看。动态用户名：'sa',密码：'123456',数据库名：'DataTest3'
if cnxn:
    print("链接成功！")

# 插入50万条学生信息
start_time = time.time()
for i in range(1, 500001):
    sno = f'{i:06}'  # 它的意思是将变量 i 格式化为一个六位数的字符串,: 后面的 06 表示格式化字符串的宽度为 6，如果原字符串的长度小于 6，则在左侧用 0 填充
    name = 'Student ' + str(i)
    score1 = random.randint(60, 100)
    score2 = random.randint(60, 100)
    score3 = random.randint(60, 100)
    sql = "INSERT INTO Student (sno, name, score1, score2, score3) VALUES (%d, %s, %d, %d, %d)"
    cursor1 = cnxn.cursor()  # 获取游标
    # 执行插入操作
    params = (sno, name, score1, score2, score3)
    cursor1.execute(sql,params)
    cnxn.commit()
    #每一万行提示一次
    if i % 10000 == 0:
        print(f'{i} records inserted.')


end_time = time.time()

# 输出插入时间
print(f'Total time taken: {end_time - start_time:.2f} seconds')

# 关闭连接
cnxn.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

（1）python代码运行结果：

（2）数据库查询结果：

3.3.3小BUG

1. 在Python中编写插入数据代码时，最终插入数据后需要调用commit（）进行提交到数据库，我是用的是游标对象进行调用，最终报错。
2. 错误类型： 通过python向SqlServer数据库批量添加数据报错AttributeError: 'pymssql._pymssql.Cursor' object has no attribute 'commit'
3. 问题查找：对于 pymssql 库中的 cursor 对象，没有 commit() 方法，可以使用 connection.commit() 方法提交事务。也就是获取的连接对象调用commit（）方法。