综合实训（一）
1、在数据库【gxhx教务数据库】上，创建【会员】表
操作：
USE gxhx教务数据库
CREATE TABLE 会员 (
会员ID int PRIMARY KEY,
登录名 varchar(20) NULL DEFAULT NULL,
用户名 varchar(30) NULL DEFAULT NULL,
密码 varchar(50) NULL DEFAULT NULL,
性别 char(2) NULL DEFAULT NULL,
出生日期 date NULL DEFAULT NULL,
所在城市 varchar(50) NULL DEFAULT NULL,
邮箱 varchar(50) NULL DEFAULT NULL,
积分 int NULL DEFAULT NULL,
注册时间 date NULL DEFAULT NULL
)
--插入测试数据
INSERT INTO 会员 VALUES (1, '17598632598', '谢小明', '0x4c0c5763662fea4cfc25fd5160f4a3bc', '男', '2005-12-28', '广州', '214896335@qq.com', 72, '2021-09-10');
INSERT INTO 会员 VALUES (2, '14786593245', '蔡静', '0x7a66743ad810f7dd1b19254a722dfcb8', '女', '1998-10-28', '深圳', '258269775@qq.com', 139, '2021-09-10');
INSERT INTO 会员 VALUES (3, '18974521635', '段湘林', '0x81d0b79f9be07ea27c03432bc0fc205d', '男', '2000-03-01', '长沙', '127582934@qq.com', 0, '2021-09-10');
INSERT INTO 会员 VALUES (4, '13598742685', '盛伟刚', '0x605f44e0ae84888b6c56bbc551026ec3', '男', '1994-04-20', '北京', '24596325@qq.com', 58, '2021-09-10');
INSERT INTO 会员 VALUES (5, '14752369842', '李小莉', '0x6075de3c82f97982335e48778524ef03', '女', '1989-09-03', '广州', NULL, 8, '2021-09-10');
INSERT INTO 会员 VALUES (6, '16247536915', '罗湘萍', '202cb962ac59075b964b07152d234b70', '女', '1985-09-24', '长沙', '2157596@qq.com', 541, '2021-09-10');
INSERT INTO 会员 VALUES (7, '18245739214', '柴宗文', '202cb962ac59075b964b07152d234b70', '男', '2000-02-19', '北京', '225489365@qq.com', 104, '2021-09-10');
INSERT INTO 会员 VALUES (8, '17632954782', '冯玲珍', '202cb962ac59075b964b07152d234b70', '女', '1994-01-24', '广州', '', 349, '2021-09-10');
INSERT INTO 会员 VALUES (9, '15874269513', '陈郭', '202cb962ac59075b964b07152d234b70', '女', '2001-07-02', '北京', '2159635874@qq.com', 65, '2021-09-10');
INSERT INTO 会员 VALUES (10, '17654289375', '韩明', '202cb962ac59075b964b07152d234b70', '男', '2002-12-23', '长沙', '2459632@qq.com', 0, '2021-09-10');
INSERT INTO 会员 VALUES (11, '19875236942', '罗松', '202cb962ac59075b964b07152d234b70', '女', '2002-09-12', '广州', '25578963@qq.com', 237, '2021-09-10');
INSERT INTO 会员 VALUES (12, '17652149635', '李全', '202cb962ac59075b964b07152d234b70', '男', '2001-04-29', '长沙', '2225478@qq.com', 62, '2021-09-10');
INSERT INTO 会员 VALUES (101, '20231001', '谢伟明', 'e69bdaa8c073a9bf4388e1b4a23a6511', '男', '2005-11-28', '广州', '214896335@qq.com', 72, '2023-09-10');
INSERT INTO 会员 VALUES (102, '20231002', '谢伟芳', '97da1d8cf844333ce468d7478355837e', '女', '2005-10-28', '广州', '214896336@qq.com', 72, '2023-09-10');
INSERT INTO 会员 VALUES (103, '20231003', '林小明', 'e69bdaa8c073a9bf4388e1b4a23a6511', '男', '2005-09-28', '广州', '214896335@qq.com', 72, '2023-09-10');
INSERT INTO 会员 VALUES (104, '20231004', '林小芳', '97da1d8cf844333ce468d7478355837e', '女', '2005-05-28', '广州', '214896336@qq.com', 72, '2023-09-10');
用密码无加密存储：
INSERT INTO 会员 VALUES (108, '20231018', '李小芳', '20238899', '女', '2005-05-28', '广州', '214896336@qq.com', 72, '2023-09-10');
将 会员ID<13的记录的密码修改为 登录名，并用MD5加密算法加密存储。
UPDATE 会员 set 密码=sys.fn_varbintohexstr(HASHBYTES('MD5',登录名)) WHERE 会员ID<13
用MD5加密存储密码：
INSERT INTO 会员 VALUES (109, '20232023', '刘小宝',sys.fn_varbintohexstr(HASHBYTES('MD5','99999999')), '男', '2003-10-18', '广州', '214896339@qq.com', 88, '2023-09-12');
SELECT * FROM [会员]
查询：SELECT * FROM 会员 ORDER BY 所在城市,积分,会员ID
创建过程，对会员表查询 根据所在城市及姓名
CREATE PROCEDURE seleCitysex @City char(20),@Sex nchar(1)
AS
SELECT * From 会员
WHERE 所在城市=@City AND 性别=@Sex
调用运行过程： EXECUTE seleCitysex @City='广州',@sex='女'
或：EXEC seleCitysex @City='广州',@sex='男'
或直接在Navicat 上 运行过程函数并输入参数。
#修改无加密的密码的存储过程，
CREATE PROCEDURE uppassword @Uname char(20) , @upass varchar(20) , @newpass varchar(20)
AS
UPDATE 会员 set 密码=@newpass
WHERE 登录名=@uname AND 密码=@upass
调用运行过程示例（注意观察是否修改成功）：
SELECT * FROM [dbo].[会员]
EXECUTE uppassword @Uname='20232023' , @upass='88888888' , @newpass='66666666'
SELECT * FROM [dbo].[会员]
EXECUTE uppassword @Uname='20232023' , @upass='99999999' , @newpass='66669999'
SELECT * FROM [dbo].[会员]
或直接在Navicat 上 运行过程函数并输入参数。
#修改用md5算法加密过的密码的存储过程
CREATE PROCEDURE upmd5password @Uname char(20) , @upass varchar(20) , @newpass varchar(20)
AS UPDATE 会员 set 密码=sys.fn_varbintohexstr(HASHBYTES('MD5',@newpass))
WHERE 登录名=@uname AND
密码=sys.fn_varbintohexstr(HASHBYTES('MD5',@upass))
调用运行过程 upmd5password 示例（注意观察是否修改成功）：
SELECT * FROM [dbo].[会员]
EXECUTE upmd5password @Uname='20232023' , @upass='88888888' , @newpass='66666666'
SELECT * FROM [dbo].[会员]
EXECUTE upmd5password @Uname='20232023' , @upass='99999999' , @newpass='66669999'
SELECT * FROM [dbo].[会员]
练习：
创建过程，对会员表查询 根据登录名及密码合法才准查阅自己的信息
(下面程序若存在错误，请调试修正）
CREATE PROCEDURE sename @uname varchar(20) , @upass varchar(20)
AS
SELECT * From 会员
WHERE 登录名=@uname AND
( 密码=@upass OR 密码=sys.fn_varbintohexstr(HASHBYTES('MD5',@upass)) )
调用运行过程示例（注意观察是否成功）：
EXECUTE seleuname @Uname='20232023' , @upass='66669999'
