1.T-SQL练习 （1） --> 创建一个School数据库 ---> 创建一个班级表： --班级id、班级名称、班级简介 ---> 创建一个学生表： --学生id、姓名、性别、年龄、电话、 --家庭住址、出生日期、身份证号、班级id (
if(DB_ID('School'))is not null drop database School; --查看有没有此数据库,有则删除掉 create database School on ( name='School', filename='F:\DBA\mytest\School.mdf' ) log on ( name='School_log', filename='F:\DBA\mytest\School.ldf' ) --创建School数据库 --go use School; --go create table T_class--创建class表 ( ClassID int, ClassName nvarchar(10), ClassDesciption nvarchar(50) ) create table T_Student --创建student表 ( StuID varchar(10), StuName nvarchar(10), StuGender bit, StuAge int, StuPhone varchar(11), StuDress nvarchar(50), StuBorthday datetime, StuCardID varchar(18), ClassID int ) --go insert into dbo.T_Class(ClassID,ClassName,ClassDesciption) --添加数据 values(1,'DOTNET基础班','DOTNET基础班学习基础'); insert into dbo.T_Class(ClassID,ClassName,ClassDesciption) values(2,'JAVA基础班','JAVA基础班学习基础'); insert into dbo.T_Class(ClassID,ClassName,ClassDesciption) values(3,'JAVA基础班','JAVA基础班学习基础'); insert into dbo.T_Class(ClassID,ClassName,ClassDesciption) values(3,'JAVA基础班','JAVA基础班学习基础');
select * from dbo.T_Class; --查询表 select ClassName from dbo.T_Class; --查询指定字段 select * from dbo.T_Class where ClassID=1; --查询语句
update dbo.T_Class set ClassName='JAVA就业班' , ClassDesciption='JAVA就业班毕业就能就业' where ClassID=3; select * from dbo.T_Class; --更改表的信息
update dbo.T_Class set ClassID=4 where ClassID=3 and ClassName='JAVA基础班';
Delete from dbo.T_Class where ClassID=4; --删除信息
) （2） --创建学生成绩表Score --scoreId,studentId,english,math --创建老师表Teacher --tId,tName,tSex,tAge,tSalary,tBirthday --学生表Student (
studentId name age
if(DB_ID('Score')) is not null drop database Score;
create database Score on ( name ='Score', filename ='F:\DBA\Score\Score.mdf' ) log on ( name='Score_log', filename='F:\DBA\Score\Score.1df' )
use Score; create table T_Score ( ScoreID int, StudentID int, english int, Math int )
create table T_Teacher ( TId int, TName nvarchar(10), TSex bit, TAge int, TSalary decimal, TBirthday datetime )
create table T_Student ( StudentID int, name nvarchar(10), age int )
insert into T_Score(ScoreID,StudentID,english,Math) values (1,1,79,85); insert into T_Score(ScoreID,StudentID,english,Math) values (2,2,73,80); insert into T_Score(ScoreID,StudentID,english,Math) values (3,3,58,59);
update T_Score set english=98,Math=99 where ScoreID=3;
delete from T_Score where ScoreID=2; select * from dbo.T_Score;
) 3. --创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。 --要求：输出所有数据中通话时间最长的5条记录。orderby datediff --输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum --输出本月通话总时长最多的前三个呼叫员的编号。 --输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
(
CREATE TABLE [CallRecords] ( [Id] [int] NOT NULL identity(1,1), [CallerNumber] [nvarchar](50), --三位数字，呼叫中心员工编号（工号） [TelNum] [varchar](50), [StartDateTime] [datetime] NULL, [EndDateTime] [datetime] NULL --结束时间要大于开始时间,默认当前时间 )
--主键约束 alter table [CallRecords] add constraint PK_CallRecords primary key(id)
--检查约束 alter table [CallRecords] add constraint CK_CallRecords check(CallerNumber like '[0-9][0-9][0-9]')
alter table [CallRecords] add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime)
--默认约束 alter table [CallRecords] add constraint DF_CallRecords default(getdate()) for EndDateTime
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime)); INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
use MyDataBase select * from dbo.CallRecords --要求：输出所有数据中通话时间最长的5条记录。orderby datediff select TOP 5 * from dbo.CallRecords order by datediff(SECOND,StartDateTime,EndDateTime) desc;
--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select SUM(datediff(SECOND,StartDateTime,EndDateTime)) as 长途总时长 from dbo.CallRecords where TelNum like '0%'
--输出本月通话总时长最多的前三个呼叫员的编号。 select TOP 3 CallerNumber as 呼叫员, SUM(datediff(SECOND,StartDateTime,EndDateTime)) as 通话总时间 from dbo.CallRecords group by CallerNumber order by SUM(datediff(SECOND,StartDateTime,EndDateTime)) desc ;
--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*) select TOP 3 CallerNumber as 呼叫员, COUNT(CallerNumber) as 通话次数 from dbo.CallRecords group by CallerNumber order by COUNT(CallerNumber) desc;
) 4.产生随机姓名 namespace 产生随机姓名 ( { class Program { static void Main(string[] args) { List<string> list = new List<string>(); string familyname = File.ReadAllText(@"百家姓.txt", Encoding.Default); MatchCollection ms = Regex.Matches(familyname, @"〔(\w+)〕"); foreach (Match m in ms) { list.Add(m.Groups[1].Value); //Console.WriteLine(m.Groups[1].Value); } List<string> list1 = new List<string>(); string girlname = File.ReadAllText(@"好听的女孩的名字.txt", Encoding.Default); string[] strs = girlname.Split(new char[] { '、', '。' }, StringSplitOptions.RemoveEmptyEntries); list1.AddRange(strs);
List<string> list2 = new List<string>(); string boyname = File.ReadAllText(@"好听的男孩名字.txt", Encoding.UTF8); string[] strboy = boyname.Split(new char[] { '\r', '\n', '。' }, StringSplitOptions.RemoveEmptyEntries); list2.AddRange(strboy);
Random r = new Random(); StringBuilder sb = new StringBuilder(); List<string> boylist = new List<string>(); List<string> girllist = new List<string>(); for (int i = 0; i < 100; i++) { string s1 = list[r.Next(1, list.Count)]; string s2 = list1[r.Next(1, list1.Count)]; string s3 = list2[r.Next(1, list2.Count)]; if (r.Next(1, 100) % 2 == 0) { sb.AppendFormat("{0}{1}", s1, s3); boylist.Add(sb.ToString()); sb.Clear(); } else { sb.AppendFormat("{0}{1}", s1, s2); girllist.Add(sb.ToString()); sb.Clear(); } }
using (SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=randomname;Integrated Security=True")) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { foreach (var item in boylist) { int age = r.Next(18, 35); cmd.CommandText = @"insert into T_Boy(BoyName,BoyAge) values('" + item + "','" + age + "')"; cmd.ExecuteNonQuery(); } foreach (var item in girllist) { int age = r.Next(18, 35); cmd.CommandText = @"insert into T_Girl(GirlName,GirlAge) values('" + item + "','" + age + "')"; cmd.ExecuteNonQuery(); } } }
//Console.ReadKey(); } } } ) 5.创建表同时添加约束 (
create database D_0710 on ( name='D_0710', filename='F:\DBA\0710\D_0710.mdf' ) log on ( name='D_0710_log', filename='F:\DBA\0710\D_0710.ldf' ) use D_0710; create table T_Classroom ( RoomID int primary key, RoomName nvarchar(10) )
create table T_Student ( StuID int primary key, StuNum varchar(10) unique, Stuname nvarchar(10), Stuphone varchar(11) check(Len(Stuphone)=11), StuEnterdate datetime default(getdate()), RoomID int foreign key references T_Classroom(RoomID) )
create table T_Teacher ( TeaID int primary key, TeaNum varchar(10) unique, TeaName nvarchar(10), TeaPhone varchar(11) check(Len(TeaPhone)=11), TeaEnterDate datetime default(getdate()), RoomID int foreign key references T_Classroom(RoomID) )
insert into T_Classroom(RoomID,RoomName) values(1,'.NET基础班'); insert into T_Classroom(RoomID,RoomName) values(2,'.NET就业班'); insert into T_Classroom(RoomID,RoomName) values(3,'JAVA基础班'); insert into T_Classroom(RoomID,RoomName) values(4,'JAVA就业班'); select * from dbo.T_Classroom; insert into T_Student(StuID,StuNum,Stuname,Stuphone,StuEnterdate,RoomID) values(1,'061901','董超','12345678900','2012-06-19',1); insert into T_Student(StuID,StuNum,Stuname,Stuphone,RoomID) values(2,'061902','庞永帅','12345678901',2); insert into T_Student(StuID,StuNum,Stuname,Stuphone,RoomID) values(3,'061903','翟冲','12345678902',4); insert into T_Student(StuID,StuNum,Stuname,Stuphone,RoomID) values(4,'061904','赵希平','12345678903',4); select * from dbo.T_Student; insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,TeaEnterDate,RoomID) values(1,'001','蒋坤','12345678900','2012-01-01',3); insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,RoomID) values(2,'002','苏坤','12345678901',1); insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,RoomID) values(3,'003','赵小虎','12345678902',4); insert into dbo.T_Teacher(TeaID,TeaNum,TeaName,TeaPhone,RoomID) values(4,'004','牛亮亮','12345678903',1); select * from dbo.T_Teacher; )
6SQLHelper ( using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Text.RegularExpressions; using System.Data;
namespace SQLHelper { public class Class1 { static string strcoon = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString; -- #region 封装ExcuteNonQuery public static int ExcuteNonQurey(string commandText, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(strcoon)) { using (SqlCommand cmd = new SqlCommand(commandText, conn)) { if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } if (para != null && para.Length != 0) { cmd.Parameters.AddRange(para); } return cmd.ExecuteNonQuery(); } } } #endregion
-- #region 二次封装ExcuteNonQuery public static int ExcuteNonQurey(string commandText, params object[] objs) { MatchCollection ms = Regex.Matches(commandText, @"@\w+"); List<SqlParameter> list = new List<SqlParameter>(); if (ms.Count != objs.Length) { throw new Exception(); } for (int i = 0; i < ms.Count; i++) { list.Add(new SqlParameter(ms[i].Value, objs[i])); } return ExcuteNonQurey(commandText, list.ToArray()); } #endregion
-- #region 封装ExcuteScalar public static object ExcuteScalar(string commandText, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(strcoon)) { using (SqlCommand cmd = new SqlCommand(commandText, conn)) { if (para != null && para.Length != 0) { cmd.Parameters.AddRange(para); } if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } return cmd.ExecuteScalar(); } } } #endregion
-- #region 二次封装ExcuteScalar public static object ExcuteScalar(string commandText, params object[] objs) { MatchCollection ms = Regex.Matches(commandText, @"@\w+"); List<SqlParameter> list = new List<SqlParameter>(); if (ms.Count != objs.Length) { throw new Exception(); } for (int i = 0; i < ms.Count; i++) { list.Add(new SqlParameter(ms[i].Value, objs[i])); } return ExcuteScalar(commandText, list.ToArray()); } #endregion
-- #region 封装ExcuteReader public static SqlDataReader ExcuteReader(string commandText, params SqlParameter[] para) { SqlConnection conn = new SqlConnection(strcoon); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { if (para != null && para.Length != 0) { cmd.Parameters.AddRange(para); } if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } } #endregion
-- #region 二次封装ExcuteReader public static SqlDataReader ExcuteReader(string commandText, params object[] objs) { MatchCollection ms = Regex.Matches(commandText, @"@\w+"); List<SqlParameter> list = new List<SqlParameter>(); if (ms.Count != objs.Length) { throw new Exception(); } for (int i = 0; i < ms.Count; i++) { list.Add(new SqlParameter(ms[i].Value, objs[i])); } return ExcuteReader(commandText, list.ToArray()); } #endregion
-- #region 封装DataAdapter public static DataSet DataAdapter(string commandText, params SqlParameter[] para) { DataSet ds = new DataSet(); using (SqlDataAdapter sda = new SqlDataAdapter(commandText, strcoon)) { if (para != null && para.Length != 0) { sda.SelectCommand.Parameters.AddRange(para); } sda.Fill(ds); return ds; } } #endregion
-- #region 二次封装DataAdapter public static DataSet DataAdapter(string commandText, params object[] objs) { MatchCollection ms = Regex.Matches(commandText, @"@\w+"); List<SqlParameter> list = new List<SqlParameter>(); if (ms.Count != objs.Length) { throw new Exception(); } for (int i = 0; i < ms.Count; i++) { list.Add(new SqlParameter(ms[i].Value, objs[i])); } return DataAdapter(commandText, list.ToArray()); } #endregion
} } ) 7.省市联动 --首先添加SQL类型，写应用程序配置文件，并分别引用 ( --Model类 namespace 省市联动专属Helper版 { class model { public int ID { get; set; } public string Name { get; set; } -- //重写ToString public override string ToString() { return Name; } } }
--专属Helper static class PCHelper { public static List<model> PCExcute(string commandText, params object[] objs) { List<model> list = new List<model>(); using (SqlDataReader reader = SQLHelper.Class1.ExcuteReader(commandText, objs)) { if (reader.HasRows) { while (reader.Read()) { list.Add( new model { ID=Convert.ToInt32(reader["AreaId"]), Name=reader["AreaName"].ToString() } ); } } } return list; } } --窗口 namespace 省市联动专属Helper版 { public partial class Form1 : Form { public Form1() { InitializeComponent(); }
private void Form1_Load(object sender, EventArgs e) { string str = @"select AreaId,AreaName from TblArea where AreaPId=@p"; cmdpro.Items.AddRange(PCHelper.PCExcute(str,0).ToArray()); }
private void cmdpro_SelectedIndexChanged(object sender, EventArgs e) { cmbcity.Items.Clear(); cmbcity.Text = string.Empty; string str = @"select AreaId,AreaName from TblArea where AreaPId=@pid"; int id = ((model)(cmdpro.SelectedItem)).ID; cmbcity.Items.AddRange(PCHelper.PCExcute(str, id).ToArray()); } } } )
8.登录注册 ---首先添加SQL类型，写应用程序配置文件，并分别引用 --主窗口 ( using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.Configuration;
namespace 用户登录 { public partial class MainForm : Form { public static string strLogName = string.Empty; public MainForm() { InitializeComponent(); } private void btnlogin_Click(object sender, EventArgs e) { Form1 f1 = new Form1(); this.Hide(); f1.Show(); } private void btnenter_Click(object sender, EventArgs e) { //判断文本框不为空 if (txtid.Text == "" || txtpwd.Text == "") { MessageBox.Show("用户名或者密码不能为空"); return; } string myname = txtid.Text.Trim(); string sqlstr = "select count(userName) from T_userinformation where userName=@name"; int res = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(sqlstr, myname)); //查询用户名是否存在 if (res > 0) { Found: string str2 = "select time from T_userinformation where username=@name"; bool res2 =Convert.IsDBNull(SQLHelper.Class1.ExcuteScalar(str2, myname)); //查询数据库设定的time是否为空 if (res2) { string str = "select userPWD from T_userinformation where username=@name"; string pwd = Convert.ToString(SQLHelper.Class1.ExcuteScalar(str, myname)); //查询输入的密码是否正确 if (pwd == txtpwd.Text.Trim()) { MessageBox.Show("登录成功"); strLogName = txtid.Text.Trim(); messageform mform = new messageform(); this.Hide(); mform.Show(); } else { string str1 = "select seed from T_userinformation where username=@name"; int myseed = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str1, myname)); //设定种子，小于3则给与提示，并自增1 if (myseed < 3) { myseed++; string str4 = "update T_userinformation set seed='" + myseed + "' where username=@name"; SQLHelper.Class1.ExcuteNonQurey(str4, myname); MessageBox.Show(string.Format("密码错误，你还有{0}次登录机会", 4 - myseed)); } //大于3则锁定账户，往数据库插入当前时间 else { btnenter.Enabled = false; string str3 = "update T_userinformation set time=GETDATE() where username=@name"; SQLHelper.Class1.ExcuteNonQurey(str3, myname); MessageBox.Show("你的用户名已被锁定，15分钟后可以登录"); } } } else { //用当前时间减去数据库里的时间，大于15分钟则设数据库里的时间为NULL并返回，小于15分钟 //则给予提示 string str4 = "select time from T_userinformation where username=@name"; DateTime savetime =Convert.ToDateTime(SQLHelper.Class1.ExcuteScalar(str4, myname)); string str1 = @"select datediff(SECOND,'"+savetime+"',getdate())"; int time =Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str1,null)); if (time < 900) { MessageBox.Show(string.Format("还有{0}分钟可以登录", time / 60 + 1)); } else { string str5 = "update T_userinformation set seed=1,time=null where username=@name"; SQLHelper.Class1.ExcuteNonQurey(str5, myname); goto Found; } } } else { MessageBox.Show("用户名不存在"); } } private void btnalter_Click(object sender, EventArgs e) { alterpwd ap = new alterpwd(); this.Hide(); ap.Show(); } } }
--修改密码窗口 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Text.RegularExpressions;
namespace 用户登录 { public partial class alterpwd : Form { public alterpwd() { InitializeComponent(); } bool b1, b2, b3; private void textBox1_Leave(object sender, EventArgs e) { b1 = false; if (string.IsNullOrEmpty(txtuid.Text.Trim())) { return; } string sqlstr1 = "select count(userName) from T_userinformation where userName=@name"; int res = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(sqlstr1, txtuid.Text.Trim())); if (res > 0) { lbuid.ForeColor = Color.Green; lbuid.Text = "用户名正确"; b1 = true; } else { lbuid.ForeColor = Color.Red; lbuid.Text = "用户名不存在"; } } private void txtnewpwd_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtnewpwd.Text.Trim())) { return; } b2 = Regex.IsMatch(txtnewpwd.Text.Trim(), @"^\d{6}$"); if (b2) { lbpwd.ForeColor = Color.Green; lbpwd.Text = "恭喜，密码输入正确！"; } else { lbpwd.ForeColor = Color.Red; lbpwd.Text = "输入错误，请检查密码长度和格式"; } }
private void txtnewpwd2_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtnewpwd2.Text.Trim())) { return; } b3 = txtnewpwd2.Text.Trim() == txtnewpwd.Text.Trim(); if (b3) { lbpwd2.ForeColor = Color.Green; lbpwd2.Text = "恭喜，密码验证正确！"; } else { lbpwd2.ForeColor = Color.Red; lbpwd2.Text = "密码不一致"; } }
private void btnok_Click(object sender, EventArgs e) { string str = "select userPWD from T_userinformation where username=@name"; string pwd = Convert.ToString(SQLHelper.Class1.ExcuteScalar(str, txtuid.Text.Trim())); if (b1 && b2 && b3) { if (pwd == txtoldpwd.Text.Trim()) { string str2 = "update T_userinformation set userPWD=@pwd where username=@name"; SQLHelper.Class1.ExcuteNonQurey(str2, txtnewpwd2.Text.Trim(), txtuid.Text.Trim()); MessageBox.Show("修改成功"); } else { MessageBox.Show("原始密码错误"); } } else { MessageBox.Show("请填写正确的信息"); } }
private void button1_Click(object sender, EventArgs e) { MainForm mf = new MainForm(); this.Hide(); mf.Show(); } } }
--注册窗口 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Text.RegularExpressions; using System.Data.SqlClient;
namespace 用户登录 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } bool b1, b2, b3, b4, b5, b6; private void button1_Click(object sender, EventArgs e) { try { if (b1 && b2 && b3 && b4 && b5 && b6) { string str = @"insert into T_userinformation (userName, userPWD, userPhone, userEmial, userBirthday) values (@t1,@t2,@t3,@t4,@t5)"; SQLHelper.Class1.ExcuteNonQurey(str,txtuid.Text,txtpwd.Text,txtphone.Text,txtmail.Text,Convert.ToDateTime(txtbirth.Text)); MessageBox.Show("注册成功"); MainForm mf = new MainForm(); this.Hide(); mf.Show(); } else { MessageBox.Show("请完善信息"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void textBox1_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtuid.Text.Trim())) { return; } b1 = Regex.IsMatch(txtuid.Text.Trim(), @"^\w{0,20}$"); string str = "select count(userName) from T_userinformation where userName=@n"; int res = Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str, txtuid.Text.Trim())); if (b1) { if (res == 0) { labelUid.ForeColor = Color.Green; labelUid.Text = "恭喜，用户名可以使用！"; } else { labelUid.ForeColor = Color.Red; labelUid.Text = "用户名已存在"; } } else { labelUid.ForeColor = Color.Red; labelUid.Text = "用户名长度超过20"; } }
private void textBox3_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtpwd.Text.Trim())) { return; } b2 = Regex.IsMatch(txtpwd.Text.Trim(), @"^\d{6}$"); if (b2) { labelpas.ForeColor = Color.Green; labelpas.Text = "恭喜，密码输入正确！"; } else { labelpas.ForeColor = Color.Red; labelpas.Text = "输入错误，请检查密码长度和格式"; } }
private void textBox2_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtpwd2.Text.Trim())) { return; } b3 = txtpwd2.Text.Trim() == txtpwd.Text.Trim(); if (b3) { labelpas2.ForeColor = Color.Green; labelpas2.Text = "恭喜，密码验证正确！"; } else { labelpas2.ForeColor = Color.Red; labelpas2.Text = "输入错误，请检查密码"; } }
private void textBox4_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtmail.Text.Trim())) { return; } b4 = Regex.IsMatch(txtmail.Text.Trim(), @"^[0-9a-zA-Z\.-_]+@[0-9a-zA-Z\-_]+(\.[0-9a-zA-Z\-_]+)+$"); if (b4) { labelmail.ForeColor = Color.Green; labelmail.Text = "恭喜，邮箱输入正确！"; } else { labelmail.ForeColor = Color.Red; labelmail.Text = "输入错误，请检查邮箱格式"; } }
private void textBox5_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtphone.Text.Trim())) { return; } b5 = Regex.IsMatch(txtphone.Text.Trim(), @"^\d{11}$"); if (b5) { labelphone.ForeColor = Color.Green; labelphone.Text = "恭喜，手机输入正确"; } else { labelphone.ForeColor = Color.Red; labelphone.Text = "输入错误，请检查手机格式"; } }
private void textBox6_Leave(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtbirth.Text.Trim())) { return; } b6 = Regex.IsMatch(txtbirth.Text.Trim(), @"^\d{4}\-((0[1-9])|(1[0-2]))-((0[1-9])|(1[0-9])|(2[0-9])|(3[0-1]))$"); if (b6) { labelbirth.ForeColor = Color.Green; labelbirth.Text = "恭喜，出生日期输入正确"; } else { labelbirth.ForeColor = Color.Red; labelbirth.Text = "输入错误，请检查出生日期格式"; } }
private void Form1_Load(object sender, EventArgs e) {
}
private void btnreturn_Click(object sender, EventArgs e) { MainForm mf = new MainForm(); this.Hide(); mf.Show(); } } }
--信息显示窗口 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient;
namespace 用户登录 { public partial class messageform : Form { public messageform() { InitializeComponent(); }
private void messageform_Load(object sender, EventArgs e) { string uid = MainForm.strLogName; string str = "select * from T_userinformation where username=@name"; SqlDataReader reader = SQLHelper.Class1.ExcuteReader(str, uid); if (reader.HasRows) { while (reader.Read()) { label5.Text = reader["userName"].ToString(); label6.Text = reader["userPhone"].ToString(); label7.Text = reader["userEmial"].ToString(); label8.Text = reader["userBirthday"].ToString(); } } } } } )
9.case函数练习题 ( <1>--显示分数等级A(100-90)B(89-80)C(79-70)D(69-60)E select top 50 * from Score select top 50 scId, case when scBaseDotNet>=90 then 'A' when scBaseDotNet>=80 then 'B' when scBaseDotNet>=70 then 'C' when scBaseDotNet>=60 then 'D' else 'E' end AS Net基础, scDataBase, case when scDataBase>=90 then 'A' when scDataBase>=80 then 'B' when scDataBase>=70 then 'C' when scDataBase>=60 then 'D' else 'E' end AS 数据库, scJavaScript from Score <2>----面试题 单号 金额 Rk1 10 Rk2 20 Rk3 -30 Rk4 -10 将上面的表输出为如下的格式： 单号 收入 支出 Rk1 10 0 Rk2 20 0 Rk3 0 30 Rk4 0 10
create table test ( number varchar(10), amount int ) insert into test(number,amount) values('RK1',10) insert into test(number,amount) values('RK2',20) insert into test(number,amount) values('RK3',-30) insert into test(number,amount) values('RK4',-10)
select * from test
select number as 单号, case when amount>0 then amount else 0 end as 收入, case when amount<0 then -amount else 0 end as 支出 from test <3>--有如下一张表，使用SQL实现如表2的数据集 学号 课程 成绩 1 0001 语文 87 2 0001 数学 79 3 0001 英语 95 4 0002 语文 69 5 0002 数学 84 表2 学号 数学 语文 英语 1 0001 79 87 95 2 0002 84 69 0 --create table TScore ( 学号 nvarchar(10), 课程 nvarchar(10), 成绩 int )
insert into TScore values('0001','语文',87); insert into TScore values('0001','数学',79); insert into TScore values('0001','英语',95); insert into TScore values('0002','语文',69); insert into TScore values('0002','数学',84);
select * from TScore
--要使用学生进行分组 select 学号 from TScore group by 学号
--前面不能显示各个科目 --可以考虑使用聚合函数 select 学号, SUM(case when 课程='数学' then 成绩 else 0 end) as 数学, SUM(case when 课程='语文' then 成绩 else 0 end) as 语文, SUM(case when 课程='英语' then 成绩 else 0 end) as 英语 from TScore group by 学号
--知道，这种将行变列的用法叫做透视pivot
) 10.实现日记和分页 ( --视图 --表的内连接 SELECT t2.UserName, t1.Title, t1.CreateTime,t2.ID FROM dbo.DiaryInfo AS t1 INNER JOIN dbo.Users AS t2 ON t1.UserID = t2.ID --存储过程 create proc usp_Getpaged --时间分页 @pageindex int, @pagesize int, @pagecount int output as begin declare @num int select @num=COUNT(*) from dbo.vw_myvw set @pagecount=CEILING(@num*1.0/@pagesize) select * from (select ROW_NUMBER() over(order by ID) as num,* from dbo.vw_myvw)as t where num between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize order by t.ID end --查询 declare @n int exec dbo.usp_Getpaged 1,3,@n output print @n
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; ---登录主页面 namespace 日记 { public partial class LoginForm : Form { public LoginForm() { InitializeComponent(); }
private void btnenter_Click(object sender, EventArgs e) { if(string.IsNullOrEmpty(txtuid.Text.Trim())||string.IsNullOrEmpty(txtpwd.Text.Trim())) { return; } string str = @"select count(*) from Users where UserName=@n"; int res =Convert.ToInt32(SQLHelper.Class1.ExcuteScalar(str, txtuid.Text.Trim())); if (res > 0) { string str2 = @"select Pwd from Users where UserName=@n"; string mypwd = Convert.ToString(SQLHelper.Class1.ExcuteScalar(str2, txtuid.Text.Trim())); if (mypwd == txtpwd.Text.Trim()) { ShowtxtForm sf = new ShowtxtForm(); sf.Show(); this.Hide(); } else { MessageBox.Show("密码错误"); return; } } else { MessageBox.Show("用户名不存在"); return; } }
private void btnlogin_Click(object sender, EventArgs e) { try { string str = @"insert into Users(UserName,Pwd) values(@n,@p)"; SQLHelper.Class1.ExcuteNonQurey(str, txtuid.Text.Trim(), txtpwd.Text.Trim()); MessageBox.Show("注册成功"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } } --显示日记的页面 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient;
namespace 日记 { public partial class ShowtxtForm : Form { public ShowtxtForm() { InitializeComponent(); } int pageIndex = 1; int pageSize = 3; int pageCount; private void button1_Click(object sender, EventArgs e) { AddtxtForm af = new AddtxtForm(); this.Close(); af.Show(); }
private void ShowtxtForm_Load(object sender, EventArgs e) { BindDGV(); } void BindDGV() { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(@"Data Source=(local);Initial Catalog=DiaryManager;Integrated Security=True")) { using (SqlCommand cmd = new SqlCommand("usp_Getpaged", conn)) //存储过程名字 { cmd.CommandType = CommandType.StoredProcedure; //设置类型为存储过程类型，默认是Text
SqlParameter[] para = { new SqlParameter("@pageindex",SqlDbType.Int), new SqlParameter("@pagesize",SqlDbType.Int), new SqlParameter("@pagecount",SqlDbType.Int) //不忙给赋值，先定义类型 }; //输入参数 para[0].Value = pageIndex; para[1].Value = pageSize; //输出参数 para[2].Direction = ParameterDirection.Output; //添加参数 cmd.Parameters.AddRange(para); //执行存储过程 SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(dt); //必须调用完存储过程 才能获取输出参数的值 pageCount = Convert.ToInt32(para[2].Value); //设置dgv不自动生成列 dataGridView1.AutoGenerateColumns = false; //设置dgv的数据源 dataGridView1.DataSource = dt; } } btnfirst.Enabled = true; btnpre.Enabled = true; btnnext.Enabled = true; btnlast.Enabled = true; if (pageIndex == 1) { btnfirst.Enabled = false; btnpre.Enabled = false; } if (pageIndex == pageCount) { btnnext.Enabled = false; btnlast.Enabled = false; } } //首页 private void btnfirst_Click(object sender, EventArgs e) { pageIndex = 1; BindDGV(); } //上一页 private void btnpre_Click(object sender, EventArgs e) { if (pageIndex > 1) { pageIndex--; BindDGV(); } } //下一页 private void btnnext_Click(object sender, EventArgs e) { if (pageIndex < pageCount) { pageIndex++; BindDGV(); } } //末页 private void btnlast_Click(object sender, EventArgs e) { pageIndex = pageCount; BindDGV(); } } } --添加日记的页面 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient;
namespace 日记 { public partial class AddtxtForm : Form { public AddtxtForm() { InitializeComponent(); }
private void AddtxtForm_Load(object sender, EventArgs e) { string str = @"select ID,UserName from Users"; List<model> list = new List<model>(); SqlDataReader reader = SQLHelper.Class1.ExcuteReader(str, null); using (reader) { if (reader.HasRows) { while (reader.Read()) { list.Add( new model { ID=Convert.ToInt32(reader["ID"]), Name=(reader["UserName"]).ToString() } ); } } } cmbauther.Items.AddRange(list.ToArray()); }
private void btnok_Click(object sender, EventArgs e) { try { string str1 = @"insert into DiaryInfo(Title,Content,CreateTime,UserID) values(@t,@c,getdate(),@id)"; SQLHelper.Class1.ExcuteNonQurey(str1, txttitle.Text.Trim(), txtboby.Text.Trim(), ((model)cmbauther.SelectedItem).ID); MessageBox.Show("添加成功"); ShowtxtForm sf = new ShowtxtForm(); this.Close(); sf.Show(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } class model { public int ID { get; set; } public string Name { get; set; } public override string ToString() { return Name; } } } ) 11.
1202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
