sql登录篇(1):登录界面与SQL数据库连接
登录界面与SQL数据库连接
2016-01-06 13:35
1610人阅读
评论(0)
收藏
举报
登录界面与SQL数据库连接
“做任何一个项目，思路是最重要的，因为思路才是引导你慢慢走向成功的关键”这是我在这次学做登录界面感受到的其中重要一点。另外一点就是“做项目时可以尝试将可视化的一步步操作流程用代码表示出来，因为一步步的可视化操作流程正是你的制作项目的思路”。
我这次做的登录界面比较简单，主要板块就是“登录的界面”和“SQL server数据库中的表格”两块，而连接它们的桥梁就是“ADO.NET的应用”，ADO.NET的使用也正是这个小项目的重中之重。以下我就侧重这三个重要的应用中来讲述我做这个项目的整个过程。
第一个板块就是登陆界面的制作，这个板块比较好做，说白了就是拖控件。只要你有时间，有精力的话，你可以把你的界面制作的非常漂亮。在这里我尽量从简，我主要是想说说如何实现基本功能。在你新建的一个项目中会弹出一个窗口，而在这个窗口中你只需要五个控件即可，分别是：两个lable控件，两个textBox控件，一个Button控件。分别将两个lable控件文本设置为“用户名”和“密码”；两个textBox控件文本设置为空，作为前面两个lable的对应输入框；最后一个Button控件文本设置为“登录”。这样一个简单的登录界面就完成了。
第二个板块就是用SQL语言在SQL server数据库中建立一个表格，也就是你要登录连接的对象。在这里我就先简单的建立一个数据库中的表格。首先新建一个数据库命名为stu，再在stu中新建一个表格命名为connection,这些命名是可以随意的，但是它们也是比较重要的，因为在写代码寻找这个表格时用的上。现在用代码新建一个临时使用的表connection,如下：
create table connection
(
ID int primary key,
Username Nvarchar(20) Not null,
Password Nvarchar(20) Not null,
)
这就是建立了一个三个列分别为ID、Username、Password的表格。
接下来向其中插入数据，如下：
insert into connection values("145211","小琪","111111")
select * from connection
insert into connection values("145212","小明","222222")
select * from connection
insert into connection values("145213","小玉","333333")
select * from connection
其中括号内的为插入的数据，这样一个简单的数据表格也就完成了。
前面两个板块已经完成，接下来就是最后一个重要的应用----ADO.NET,用它来实现界面与SQL server的连接。做这个连接之前，我们要有一个清晰地思路，而这个思路的来源就是我们自己平时操作这些过程的流程。比如，你已经做好了一个登陆界面，你要与SQL server数据库进行连接，你会怎么做？1、你要先从界面的输入框中取得数据；2、你会先打开SQL
server数据库；3、你要进行身份验证，这样你才能进入SQL server数据库；4、你会打开你新建的那个数据库stu；5、你要打开stu数据库中的新建的表格connection；6、然后将表中你要的数据提取出来；7、最后将你在界面里输入的数据与从表格中提取的数据进行验证是否正确。你要做的就是这些可视化的操作流程，而电脑其实要做的也就是这些过程，我们要做的就是将这些可视化操作流程用代码写出来，这是一个重要的学习方法。
有了整体的思路，然后我们来实现Button控件发生的功能。首先，在代码中加入using system.Data.SqlClient;接下来我们要做的就是将上面的一步步操作流程代码化就行了。
1、从界面中的两个输入框中取得两个数据“用户名”和“密码”：
string Username = this.textBox1.Text;
string Password=this.textBox2.Text;
2、打开SQL server数据库并进行身份验证：
string num = ("Server=(local);Integrated Security=SSPI;database=stu")
3、找到数据库stu并且找到其中的表格connection，用一条记录作为连接桥梁：
SqlConnection connect = new SqlConnection(num); //建立一个数据库连接对象connect
string sql = "select * from connection where Username=""+ Username + "" and Password=""+Password + """;
4、SQL server数据库打开并连接：
SqlCommand command = new SqlCommand(sql,connect); //建立一个新的SQL命令
connect.Open();
5、SQL server数据库连接关闭：
connect.Close();
6、将你从界面输入框中取得的两个数据与数据库中数据进行验证过程：
int num1 = Convert.ToInt32(command.ExecuteScalar())
if(num1>0)
{
MessageBox.Show("登入成功！");
}
else
{
MessageBox.Show("对不起，你输入的账号或者密码错误！");
}
这些就是实现Button控件连接功能的所有代码，接下来，你就可以直接去进行调试应用了。事实上这些代码在步骤顺序上与我们的实际可视化操作并不是完全一致，但也只是稍微的改变而已，所以在做许多项目时，还是可以尝试用这种方法去实现。到这里这个登陆界面已经基本完成了。
学习过程中的知识小结：
1、断点调试法：在你编写代码时，时常会出现代码的错误导致无法正常运行，所以有的时候你可以用断点调试法，对重要的一条语句进行断点（方法：将鼠标的光标置于你选中的那条语句左边界后单击，之后这条语句会变成红色），--->然后对该语句进行调试，--->在弹出的登陆界面窗口中输入“用户名”和“密码”后，--->连接.之后你便可以将鼠标光标分别置于对界面两个输入框进行取数的那两个语句上，若是语句正确的话，那么分别会显示出你刚刚所输入的数据；若是语句错误的话，那么光标置于该处位置无法显示出刚刚你所输入的数据。这样你就可以有针对性的去查找错误语句。
2、在你拖拉lable和textBox控件时，其实代码中会帮你自动生成几条对应的空间代码，这些代码你可以忽略掉，不用再其中添加任何东西。因为，每个控件对应的代码指的是这个控件发生的变化事件，需要填写代码去激活，然而这些控件并未发生什么事件，所以也不用填写代码去激活。并且你可以将其删除，当你删除它们的时候，系统会自动报错并生成一个代码窗口，此时你只要双击对应的错误提示，将光标所停留的位置那条语句删除即可。
sql登录篇(2):sql
use mastergo
/*查询数据库系统表 如果该数据库存在 就删除该数据库*/if exists(select * from sysdatabases where name="stuDB") drop database stuDBcreate database stuDBON PRIMARY( filename="d:\project\stuDB.mdf", name="stuDB", size=1mb, filegrowth=15%)log on( name="stuDB_Log", filename="d:\project\stuDB.log", size=1mb, filegrowth=15%)GOuse StuDBgo--判断当前新建表是否已经存在if exists(select * from sysobjects where [name]="stuInfo" and type="u") drop table stuInfocreate table stuInfo( stuName varchar(20) not null, stuNo varchar(6) check(stuNo like "s253__") primary key, stuSex varchar(8) check(stuSex="男" or stuSex="女") default "男" not null, stuAge int check(stuAge between 15 and 50) not null, stuSeat int identity(1,1) check(stuSeat<=30) not null, stuAddress text default "地址不详",)
--检查当前新建表是否存在if exists(select * from sysobjects where [name]="stuMarks" and type="u") drop table stuMarkscreate table stuMarks( examNo varchar(11) check(examNo like "E200507____") primary key, stuNo varchar(6) foreign key references stuInfo(stuNo) not null, writtenExam decimal(8,2) check(writtenExam between 0 and 100) default 0, labExam decimal(8,2) check(labExam between 0 and 100) default 0,)go
insert into stuInfo values("张秋丽","s25301","男",18,"北京海淀")insert into stuInfo values("李斯文","s25303","女",18,"河南洛阳")insert into stuInfo values("李文才","s25302","男",18,default)insert into stuInfo values("欧阳俊雄","s25304","男",28,"新疆威武哈")
select * from stuInfo
insert into stuMarks values("E2005070001","s25301",80,58)
insert into stuMarks values("E2005070002","s25302",50,default)
insert into stuMarks values("E2005070003","s25303",97,82)select * from stuMarks
select * from stuInfo where stuSex="男"
select stuNo as 学号 ,writtenExam as 笔试成绩 from stuMarks where writtenExam between 75 and 100
select stuInfo.stuName as 学员姓名,stuMarks.writtenExam as 笔试成绩,stuMarks.labExam as 机试成绩from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
select avg(writtenExam) as 笔试平均分 from stuMarksselect avg(labExam) as 机试平均分 from stuMarks
select count(*) as 参加考试的人数 from stuInfo inner join stuMarkson stuInfo.stuNo=stuMarks.stuNo
select count(*) as 不及格人数 from stuMarks inner join stuInfoon stuInfo.stuNo=stuMarks.stuNo and (writtenExam<60 or labExam<60)
select stuNo as 学号,(writtenExam+labExam)/2 as 平均分,writtenExam as 笔试成绩,labExam as 机试成绩from stuMarks
update stuMarks set writtenExam=writtenExam+5 where writtenExam<95update stuMarks set writtenExam=100 where writtenExam>=95
---创建SQL登陆账户 第一个参数是登录名 第二个参数是密码exec sp_addlogin "banzhuren","accp"
---为登陆帐户赋予数据库访问权限 第一个是用户名 第二个是角色exec sp_grantdbaccess "banzhuren","accp"
select * from sysUsersgrant select,update,insert ,delete on stuInfo to accp
grant select on stuMarks to accp
---创建SQL登陆账户 第一个参数是登录名 第二个参数是密码exec sp_addlogin "jiaoyuan","accp"
---为登陆帐户赋予数据库访问权限 第一个是用户名 第二个是角色exec sp_grantdbaccess "jiaoyuan","jiaoyuan"
select * from sysUsersgrant select,update,insert ,delete on stuMarks to jiaoyuan
grant select on stuInfo to jiaoyuan
//===================================================
use Demo
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values("2005-05-09","胜")insert into #tmp values("2005-05-09","胜")insert into #tmp values("2005-05-09","负")insert into #tmp values("2005-05-09","负")insert into #tmp values("2005-05-10","胜")insert into #tmp values("2005-05-10","负")insert into #tmp values("2005-05-10","负")select * from #tmpselect rq, sum(case when shengfu="胜" then 1 else 0 end)"胜",sum(case when shengfu="负" then 1 else 0 end)"负" from #tmp group by rqselect rq,sum(case when shengfu="胜" then 1 else 0 end)"胜",sum(case when shengfu="负" then 1 else 0 end)"负" from #tmp group by rq
select N.rq,N.勝,M.負 from (select rq,勝=count(*) from #tmp where shengfu="胜"group by rq)N inner join(select rq,負=count(*) from #tmp where shengfu="负"group by rq)M on N.rq=M.rq
select a.col001,a.a1 胜,b.b1 负 from(select col001,count(col001) a1 from temp1 where col002="胜" group by col001) a,(select col001,count(col001) b1 from temp1 where col002="负" group by col001) bwhere a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。------------------------------------------select (case when a>b then a else b end ),(case when b>c then b esle c end)from table_name
3.面试题：一个日期判断的sql语句？请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)------------------------------------------select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）： 大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。 显示格式： 语文 数学 英语 及格 优秀 不及格 ------------------------------------------select(case when 语文>=80 then "优秀" when 语文>=60 then "及格"else "不及格") as 语文,(case when 数学>=80 then "优秀" when 数学>=60 then "及格"else "不及格") as 数学,(case when 英语>=80 then "优秀" when 英语>=60 then "及格"else "不及格") as 英语,from table
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?------------------------------------------用户临时表:create table #xx(ID int, IDValues int)系统临时表:create table ##xx(ID int, IDValues int)
区别:用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
6.sqlserver2000是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。------------------------------------------它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
7.请用一个sql语句得出结果从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。如使用存储过程也可以。
table1
月份mon 部门dep 业绩yj-------------------------------一月份 01 10一月份 02 10一月份 03 5二月份 02 8二月份 04 9三月份 03 8
table2
部门dep 部门名称dname-------------------------------- 01 国内业务一部 02 国内业务二部 03 国内业务三部 04 国际业务部
table3 （result）
部门dep 一月份 二月份 三月份-------------------------------------- 01 10 null null 02 10 8 null 03 null 5 8 04 null null 9
------------------------------------------1)select a.部门名称dname,b.业绩yj as "一月份",c.业绩yj as "二月份",d.业绩yj as "三月份"from table1 a,table2 b,table2 c,table2 dwhere a.部门dep = b.部门dep and b.月份mon = "一月份" anda.部门dep = c.部门dep and c.月份mon = "二月份" anda.部门dep = d.部门dep and d.月份mon = "三月份" and2)select a.dep,sum(case when b.mon=1 then b.yj else 0 end) as "一月份",sum(case when b.mon=2 then b.yj else 0 end) as "二月份",sum(case when b.mon=3 then b.yj else 0 end) as "三月份",sum(case when b.mon=4 then b.yj else 0 end) as "四月份",sum(case when b.mon=5 then b.yj else 0 end) as "五月份",sum(case when b.mon=6 then b.yj else 0 end) as "六月份",sum(case when b.mon=7 then b.yj else 0 end) as "七月份",sum(case when b.mon=8 then b.yj else 0 end) as "八月份",sum(case when b.mon=9 then b.yj else 0 end) as "九月份",sum(case when b.mon=10 then b.yj else 0 end) as "十月份",sum(case when b.mon=11 then b.yj else 0 end) as "十一月份",sum(case when b.mon=12 then b.yj else 0 end) as "十二月份",from table2 a left join table1 b on a.dep=b.dep
http://221.199.150.103/jsj/Html/page/book/js/HtmlDom.htm?F=14,L=1
sql登录篇(3):SQL注入登陆
mysql> select id from users where username = "" or 1=1 and password = "123";
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select id from users where username = "" or 1=1 and password = "123" at line 1
mysql> select id from users;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select id from users" at line 1
mysql> select id from user;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select id from user" at line 1
mysql> select * from user;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select * from user" at line 1
mysql> select * from user;
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
| ff8081814e62293e014e627e8c050000 | 402880944e28a74f014e2970c1640056 | r | 2015-07-06T00:00:00 | [email protected] | 2015-07-06T00:00:00 | 马玉德 | 211 | 123 | 13381086775 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
2 rows in set
mysql> select * from user;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select * from user" at line 1
mysql> select * from user;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select * from user" at line 1
mysql> select * from user;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select * from user" at line 1
mysql> select * from user;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "select * from user" at line 1
mysql> select * from user;
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
| ff8081814e62293e014e627e8c050000 | 402880944e28a74f014e2970c1640056 | r | 2015-07-06T00:00:00 | [email protected] | 2015-07-06T00:00:00 | 马玉德 | 211 | 123 | 13381086775 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
2 rows in set
mysql> select * from user where user_name="魏正钦";
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
1 row in set
mysql> select * from user where user_name=魏正钦;
1054 - Unknown column "魏正钦" in "where clause"
mysql> select * from user where user_name="" or 1=1 "";
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near """" at line 1
mysql> select * from user where user_name="" or user_password="11";
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
1 row in set
mysql> select * from user where user_name="" or user_password="11" and user_password="";
Empty set
mysql> select * from user where user_name="魏正钦" or user_password="11" and user_password="";
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
1 row in set
mysql> select * from user where user_name="魏正钦" or user_password="" and user_password="";
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
1 row in set
mysql> select * from user where user_name="魏正钦" or 1=1"" and user_password="";
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near """ and user_password=""" at line 1
mysql> select * from user where user_name="魏正钦" or 1==1"" and user_password="";
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "==1"" and user_password=""" at line 1
mysql> select * from user where user_name="魏正钦" or user_password="" and user_password="";
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| user_pk | fk_roles_pk | fk_station_pk | user_birthday | user_email | user_intime | user_name | user_number | user_password | user_phone | user_sex |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
| 402880304e33e46e014e34087e440000 | ff8081814e6601be014e660a8df70000 | q | 2015-06-27T00:00:00 | [email protected] | 2015-06-27T00:00:00 | 魏正钦 | HX20105718 | 11 | 13612084736 | 男 |
+----------------------------------+----------------------------------+---------------+---------------------+---------------+---------------------+-----------+-------------+---------------+-------------+----------+
1 row in set
https://m.shanpow.com/mrmy/441055/
推荐访问:sql语句
