T-SQL编程
标识符
即Transact—SQL

标识符分为两种：

常规表示符
界定标识符

常规标识符要满足下面三点：

开头为字母_@#这四类，序列为字母数字_@$这五类
不能与保留字相同
长度小于128

若不是常规标识符则加""或者[]界定

注释：

单行注释 –

多行注释 /*     */

数据类型

整数数据，字符数据，货币数据，日期和时间数据，二进制字符串等

变量：

局部变量：用户定义且必须以@开头，在程序内声明，而且只能在该程序内使用。
-- 定义
declare @变量名 数据类型
declare @var int
-- 赋值
set @变量名=表达式 -- 一条语句只能设置一个
select @变量名=表达式 -- 可以一次赋值多个，逗号分隔即可
-- 打印输出
print @变量名 -- 只能一次输出一个
select @变量名 -- 可以一次输出多个

全局变量：以@@开头

版本号：@@version

服务器名称：@@servername

服务名：@@servicename

循环语句：
if else语句：
if bool表达式 -- exists返回bool类型
begin
	SQL语句
end
else
begin
	SQL语句
end



case语句：相当于switch
case 表达式
when 表达式的值1 then 返回表达式1
when 表达式的值2 then 返回表达式2
。。。。
else 返回表达式n
end -- 这有个end，注意



ps:搜索式case，将when后面表达式的值改为逻辑表达式即可



循环结构
while 逻辑表达式
begin
	T-SQL语句组
	[break] -- 可选，终止循环
	[continue] -- 可选，停止当前次的循环
end



convert函数用于类型转换

等待语句
waitfor delay 'time' -- 引号的time是具体时间
waitfor time 'time' -- 要写在执行的sql语句前


return语句

return 整数表达式


函数






游标
游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制

游标的使用分五步：

声明游标
打开游标
读取游标中的数据
关闭游标
释放游标

-- 声明
declare 游标名
cursor
for select语句
for read only -- 只读，可选
-- 打开
open 游标名
-- 读取数据
fetch next 游标名
into 变量1，变量2.....
-- 关闭
close 游标名
-- 释放
deallocate 游标名

ps：执行fetch语句之后，可以通过全局变量@@fetch_status返回游标当前的状态





ps：通过while循环改变步长，即可遍历游标的所有数据

实验八 数据库编程
一、实验目的
1.掌握数据库PL/SQL编程语言,以及数据库存储过程的设计和使用方法。

2.掌握数据库自定义函数的设计和使用方法。

3.掌握PL/SQL游标的设计、定义和使用方法。
二、实验内容和要求
1.存储过程定义,存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。，

2.自定义函数定义，自定义函数运行，自定义函数更名，自定义函数删除,自定义函数的参数传递。

3.游标定义、游标使用。

按要求写出相应的SQL语句。
三、实验过程
（1）对学生-选课（s_t）数据库，编写存储过程grade_info，统计某门课程的成绩分布情况，即分别统计出100分，90—99分，80—89分，70—79分，60—69分，小于60分的学生人数。存储过程grade_info以课程名作为输入参数。
delimiter //
create procedure grade_info(in course_name char(40))
begin
		declare p100, p90,p80,p70,p60,pbujige,grade int default 0;
		declare done int default false;
		declare cur cursor for
					select grade 
					from sc 
					where cno=( select cno
							  from course
							  where cname=course_name);
		declare continue handler for not found set done=TRUE;
		open cur;
		read_loop: loop
				fetch cur into grade;
				if done then
					leave read_loop;
				end if;
				if(grade=100)then
					SET p100= p100+1;
				elseif(grade>=90)then
					SET p90= p90+1;
				elseif(grade>=80)then
					SET p80=p80+1;
				elseif(grade>=70)  then
					SET p70=p70+1;
				elseif(grade>=60)  then
					set p60=p60+1;
				else set pbujige=pbujige+1;
				end if;
		end loop;
		close cur;
end;//
delimiter ;

（2）编写函数course_avg，统计任意一门课程的平均分。函数course_avg以课程名为输入参数，平均分为返回值。
delimiter //
create function course_avg(course_name char(40))
returns double
deterministic
begin
declare c_avg double;
select AVG(grade)
into c_avg
from sc
where sno in (select sno from course where cname=course_name);
return c_avg;
end;//
delimiter ;

（3）调用grade_info输出“数据库”课程的成绩分布情况。
call grade_info('数据库');

（4）调用course_avg输出“操作系统”课程的平均分。
select course_avg('操作系统');

（5）删除grade_info。
 drop procedure grade_info;

（6）删除course_avg。
drop function course_avg;

四、实验总结

在做第一小题时，按照课本上的变量赋值语句写的，一直报错检查了好久，最后一句句的定位错误发现赋值语句有问题，网上查找关于mysql变量赋值的资料，发现mysql中的变量赋值不能用变量名:=表达式，在mysql中会报错，在mysql中用Set 变量名=表达式。

使用call或select 加存储过程/函数名都可用来调用存储过程/函数

使用drop procedur/function 存储过程名/函数名删除存储过程/函数

数据库编程
一、实验目的
掌握数据库PL/SQL编程语言，以及数据库存储过程的设计和使用方法。
二、实验内容
存储过程定义，存储过程运行，存储过程的参数传递。掌握PL/SQL编程语言和编程规范，规范设计存储过程。
三、实验数据
已知学生管理系统数据库xsgl,有5个数据表xsgl_user,xsgl_major,xsgl_student,xsgl_course和xsgl_score,分别存储用户、专业、学生、课程和成绩数据，如下图所示。



用SQL语言实现以下：

1、定义存储过程proCourseAvg，该存储过程的功能是根据输入的课程名称，统计该课程的选课人数和平均分，并返回统计结果。

①定义存储过程

--定义存储过程：
create procedure proCourseAvg(@course_name Varchar(30),@cnt int output,@avg_score int output)
as
select @cnt=Count(stud_no),@avg_score=Avg(xsgl_score.score_score) 
from xsgl_score,xsgl_course
where  xsgl_score.course_no= xsgl_course.course_no and xsgl_course.course_name=@course_name
group by(xsgl_score.course_no)


②执行结果
--执行结果：
Ps：以C语言为例
declare @course_name Varchar(30),@cnt int ,@avg_score int 
exec proCourseAvg 'C语言',@cnt output,@avg_score output
print @cnt
print @avg_score


2、定义存储过程proStudentAvg，该存储过程的功能是根据输入的同学学号，统计该同学的平均分，并返回该同学的姓名和平均分。

以上每个存储过程定义后，给出执行结果。

①定义存储过程
--定义存储过程
create Procedure proStudentAvg(@sid varchar(20),@sname varchar(20) output,@avg_score int output)
as
 select @sname=xsgl_student.stud_name,@avg_score=avg(score_score)
from xsgl_score,xsgl_student
where xsgl_student.stud_no=xsgl_score.stud_no and xsgl_student.stud_no=@sid
group by(stud_name)


②执行结果
--以学号202的学生为例
declare @sname varchar(20),@avg_score int 
exec proStudentAvg '202',@sname output,@avg_score output
print @sname
print @avg_score

数据库编程就是针对数据库的操作，通过编写程序的方式，让程序做为数据库的客户端进行数据库操作。

通过使用程序代码的方式去连接数据库服务器，通过和服务器进行交互完成对数据库的增删改查的方式，就称为数据库编程。



从图中可以看出，在 Python 中操作数据库，要经过五个步骤，分别是连接数据库，获取游标，数据库操作，关闭游标，关闭数据库连接。

在 Pyhton 中，使用 Pymysql 模块来对数据库进行编程。

1.1pymysql 操作



1. 导入模块

from pymysql import connect



2. Connection 对象

目的：用于建立代码与数据库的连接



3. 创建连接对象 conn=connect(参数列表)


  

  
参数host：连接的mysql主机，如果本机是‘localhost’
  
参数port：连接的mysql主机的端口，默认是3306
  
参数database：数据库的名称
  
参数user：连接的用户名
  
参数password：连接的密码
  
参数charset：通信采用的编码方式，推荐使用utf8
  




4. 关闭连接 conn.close()



5. 提交数据 conn.commit()



6. 撤销数据 conn.rollback()



7. 通过连接获取游标 cur = conn.cursor()返回Cursor对象

用于执行sql语句并获得结果



8. Cursor游标对象 目的: 执行sql语句



9. 获取Cursor对象 cur = conn.cursor()



10.    使用游标执行SQL语句 cur.execute(operation , [parameters])



11.    执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete语句



12.    获取结果集中的一条 cur.fetchone() 返回一个元组



13.    获取结果集中的一条 cur.fetchmany(2) 返回一个元组 ，元组中包含两条数据



14.    获取结果集中的所有 cur.fetchall() 执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回.



15.    关闭游标 cur.close()



1.2 示例代码


  
在这里默认已经拥有了一个名为python_db的数据库，数据库包含students,classes两个数据表，students字段包括id, name, age, height, gender, cls_id, is_delect， classes字段包括id, name. 




1.2.1  查询数据



# 导入pymysql模块
from pymysql import *

# 与数据库建立连接
conn = connect(host = 'localhost', port = 3306, database = 'python_db', user = 'root', password = '', charset = 'utf8')

# 通过连接获取游标
cur = conn.cursor()

# 编辑mysql命令串
sql_str = '''select * from students;'''

# 通过游标向数据库服务器发送命令
cur.execute(sql_str)

# 接收数据库服务器返回的数据
result = cur.fetchall()

# 循环输出数据信息
for t in result:
    print(t)

# 关闭游标
cur.close()

# 关闭数据库连接
conn.close()



1.2.2   增删改操作



# 导入pymysql模块
from pymysql import *

# 建立连接
conn = connect(host = 'localhost', port = 3306, database = 'students', user = 'root', password = '', charset = 'utf8')

# 通过连接获取游标
cur = conn.cursor()

# 增加命令串：
sql_str = '''insert into students values (0, '新来的', 20, 180, '男', 1, 1);'''

# 修改命令串：
#sql_str = '''update students set name = '小明' where name = '新来的';'''

# 删除命令串：
#sql_str = '''delete from students where name = '小明';'''

# 通过游标向数据库服务器发送命令
row_count = cur.execute(sql_str)

# 在执行增删改数据库操作时需要对操作进行提交，否则不成功
conn.commit()

# 关闭游标
cur.close()

# 关闭连接
conn.close()



1.2.3  回滚（取消操作）



# 导入pymysql模块
from pymysql import *

# 与mysql数据库建立连接
conn = connect(host = 'localhost', port = 3306, database = 'python_db', user = 'root', password = '', charset = 'utf8')

# 通过连接获取游标
cur = conn.cursor()

# 创建sql命令串
sql_str = '''insert into students values (0, '小华',  20, 180, '男', 1, 1);'''

# 插入10条相同的数据
for i in range(10):
    # 执行sql语句
    row_count = cur.execute(sql_str)

# 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
conn.rollback()

# 关闭游标
cur.close()

# 关闭数据库连接
cur.close()



1.3 参数化列表防止SQL注入

SQL注入:

产生原因: 后台对用户提交的带有恶意的数据和 SQL 进行字符串方式的拼接，得到了脱离原意的 SQL 语句，从而影响了 SQL 语句的语义，最终产生数据泄露的现象。

如何防止: SQL 语句的参数化, 将 SQL 语句的所有数据参数存在一个列表\元组\字典中传递给 execute 函数的第二个参数。

注意:


此处不同于python的字符串格式化，必须全部使用%s占位.




# 导入模块
from pymysql import *

# 与mysql数据库建立连接
conn = connect(host = 'localhost', port = 3306, database = 'python_db', user = 'root', password = '', charset = 'utf8')

# 获取游标
cur = conn.cursor()

# 接收外部传入的查询指令条件
find_name = input('输入要查询的姓名：')

# 创建sql命令
sql_str = '''select * from student from name = %s'''% find_name

# 执行select语句，execute方法在内部实现了防SQL注入的功能，但具体如何实现并不清楚，隐藏了细节
# 这里使用了元组的方式将find_name 传入sql_str中
row_count = cur.execute(sql_str, (find_name, ))

# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么(sql_str, (数值1, 数值2....))，此时sql语句中有多个%s即可
# 打印受影响的行数
print(row_count)

# 获取查询的结果
result = cur.fetchall()

# 打印查询的结果
print(result)

# 关闭游标
cur.close()

# 关闭数据库连接
conn.close()