#Day01 2024.05.28

  • 今日学习知识点
    1. 安装SQLServer数据库
    2. 学会增删改查
    3. 各种约束和主键、外键关系

#Day02 2024.05.29

  • 今日知识点
    1. 各种约束的代码表示
    2. SQL语句练习

各种约束的代码表示

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
select * from score
select * from  student
select * from  teacher

insert into teacher(tsex,tage,tname) values('男',190,'老张')
insert into teacher(tsex,tage,tname) values('男',19,'老王')
insert into teacher(tsex,tage,tname) values('男',78,'老赵')
insert into teacher(tsex,tage,tname) values('男',198,'老马')

alter table teacher add constraint DF_teacher_tsex default('男') for tsex			    --默认值约束
alter table teacher add constraint CK_teacher_tsex check(tsex = '男' or tsex = '女')	   --检查约束
alter table teacher add constraint DF_teacher_tage default(30) for tage
alter table teacher add constraint CK_teacher_tage check(tage > 0 and tage < 40)
alter table teacher add constraint UQ_teacher_tname unique(tname)						--唯一约束

alter table score add constraint FK_Score_Student_studentid foreign key(studentid)		--主键外键约束（和下面一行是一个整体）
references student(sid)
																						--级联删除
on delete cascade
																						--级联更新
on update cascade

delete from student where sid = 1

select tname 姓名,tsex 性别 from teacher

SQL语句练习

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
--1、查询所有员工
select [eId], [eName], [eSex], [eAge], [eSalary], [eDepId], [eintime] from employee 

--2、查询工资超过2000快钱的员工
select [eId], [eName], [eSex], [eAge], [eSalary], [eDepId], [eintime] from employee where esalary > 3000

--3、查询最新进来的5个员工
select Top 5 [eId], [eName], [eSex], [eAge], [eSalary], [eDepId], [eintime] from employee Order by eintime desc

--4、查询员工的平均工资，最低工资
select '平均工资' as 工资情况,avg(esalary) as 金额 from employee union all
select '最低工资',min(esalary) from employee

--5、查询总共有多少员工
select count(*) as 员工总数 from employee

--6、查询每个部门有多少员工
select eDepId as 部门,count(*) as 人数 from employee group by eDepId

--7、查询每个部门的平均工资
select eDepId as 部门,avg(eSalary) as 平均工资 from employee group by eDepId

--8、查询每个部门男员工的平均工资
select eDepId as 部门,avg(eSalary) as 金额 from employee where eSex = '男' group by eDepId

--9、查询平均工资超过2000的那些部门
select eDepId as 部门,avg(eSalary) as 金额 from employee group by eDepId having avg(eSalary) > 2000
--

--10、查询员工工资（姓名，工资）的同时，同一结果集显示平均工资和最高工资
select ename as 姓名,esalary as 工资 from employee union all
--select avg(esalary) as 平均工资,max(esalary) as 最高工资 from employee
select '平均工资',avg(esalary) from employee union all
select '最高工资',max(esalary) from employee

--11、查询名字里包含'定,月'的这些员工，并对结果按照年龄排序
select * from employee where ename like '%[定月]%' order by eage desc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select * from CallRecords

--输出所有数据中通话时间最长的5条记录。orderby datediff
select top 5 * from CallRecords order by DATEDIFF(SECOND,StartDateTime,EndDateTime) desc

--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) as 总时长 from CallRecords where TelNum like '0%'

--输出本月通话总时长最多的前三个呼叫员的编号。

select top 3 callernumber as 呼叫员,sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) as 总时长 from CallRecords
where datepart(MONTH,StartDateTime) = 7
group by CallerNumber
order by sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc


--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
select top 3 callernumber as 呼叫员,count(*) as 打电话次数 from CallRecords
where datepart(MONTH,StartDateTime) = 7
group by CallerNumber
order by count(*) desc

#Day03 2024.05.30

  • 今日知识点
    1. C#登录SQLServer数据库的两种方式（关键词顺序记住）
    2. 数据库Connection细节（晚open早close）同时记住using的作用
    3. 字符串连接池（去GPT问清楚）
    4. ADO.NET第一个对象–Command对象
    5. 只创建一个Command对象执行多条SQL语句
    6. ADO.NET第二个对象–DataReader对象
    7. 登录练习
    8. 登录练习进阶版

登录SQLServer数据库的两种方式

  1. 本机身份验证一般是本机练习使用
  2. SQL身份验证登录一般是真正生产使用
  • 特别注意使用SQLConnectionStringBuilder时的顺序是DIUP
  • Integrated Security = true它用于启用 Windows 身份验证
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
static void Main(string[] args)
{
    //1、声明连接字符串，描述连接信息

    //1.1.1、Windows本机身份登录
    //string conStr = "Data Source = .;Initial Catalog = MySchool;Integrated Security = true";
    //或者1.1.2
    //SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
    //scsb.DataSource = ".";
    //scsb.InitialCatalog = "MySchool";
    //scsb.IntegratedSecurity = true;

    //2、SQL身份验证登录
    //2.1.1
    string conStr = "Data Source = 10.134.67.57;Initial Catalog = MySchool;uid = sa;pwd = admin";
    //2.1.2
    SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
    scsb.DataSource = "10.134.67.57";
    scsb.InitialCatalog = "MySchool";
    scsb.UserID = "sa";
    scsb.Password = "admin";
    scsb.PersistSecurityInfo = true;//TRUE代表可以被访问，FALSE代表不可以被访问

    SqlConnection con = new SqlConnection(scsb.ToString());
    con.Open();//sqlconnection对象con连接到服务器
    Console.WriteLine("连接成功");
    Console.WriteLine(con.ConnectionString);//展示连接信息
    //1.1.1 Data Source = .;Initial Catalog = MySchool;Integrated Security = true
    //1.1.2 Data Source=.;Initial Catalog=MySchool;Integrated Security=True
    //2.1.1 Data Source = 10.134.67.57;Initial Catalog = MySchool;uid = sa;
    //2.1.1 中的pwd字段自动隐藏，因为PersistSecurityInfo字段自动设置为False，因此打印出来自动隐藏pwd
    //2.1.2 Data Source=10.134.67.57;Initial Catalog=MySchool;Persist Security Info=True;User ID=sa;Password=admin

    Console.ReadKey();
 ///////////////////////////////////////////////////////////////////////////
}

数据库Connection细节

  1. using的作用
    1. 实现了自动资源管理
    2. 更加简洁（减少了close()和dispose()的使用）
  2. SQL连接很耗费资源，因此要尽可能的实现晚Open早Close
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
static void Main(string[] args)
{
    string conStr = "Data Source = .;Initial Catalog = MySchool;Integrated Security = true";

    using (SqlConnection con = new SqlConnection(conStr))
    {
        //注册事件
        con.StateChange += Con_StateChange;

        con.Open();//该代码触发了事件

    }
    Console.ReadKey ();
}

private static void Con_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
    Console.WriteLine(e.CurrentState);//当前的状态
    Console.WriteLine(e.OriginalState);//之前的状态
}

字符串连接池

  • 记住只要不去管它就默认使用字符串连接池节省资源

ADO.NET第一个对象–Command对象

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
42
43
44
45
static void Main(string[] args)
{
    string conStr = "Data Source = .;initial catalog = MySchool;integrated security = true";

    //创建sqlConnection对象
    using (SqlConnection  con = new SqlConnection(conStr))
    {
        string sql = "insert into Score(studentid,english,math) values(200,200,200)";
        //创建sqlCommand对象
        //但是新创建的SQLCommand对象创建的时候就绑定了一条SQL语句
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            int r = 0;
            try
            {
                //首先创建了SQLCommand以后肯定就要执行SQL命令
                //但是在任何操作之前必须先连接上数据库
                con.Open();
                //由于我执行的是插入命令，因此使用ExecuteNonQuery方法
                r = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally//无论执不执行都要进入finally
            {
                //无论是SQL语句执行完成与否，SQLConnection都已经没有作用了
                //因此在这里早Close节省资源
                con.Close();
                con.Dispose();
            }
            if(r > 0)
            {
                Console.WriteLine($"成功插入了{r}条数据");
            }
            else
            {
                Console.WriteLine("插入失败"); ;
            }
        }

    }
    Console.ReadKey(); 
}

只创建一个Command对象执行多条SQL语句

  • 不与具体的SQL语句绑定
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
static void Main(string[] args)
{
    string conStr = "Data Source = .;Initial Catalog = myschool;integrated security = true";

    using (SqlConnection  con = new SqlConnection(conStr))
    {
        using (SqlCommand cmd = con.CreateCommand())
        {
            con.Open();
            //string sql = "insert into score(studentid,english,math) values(200,300,300)";
            //cmd.CommandText = sql;
            //int r = cmd.ExecuteNonQuery();
            //Console.WriteLine($"查入了{r}条数据");

            //string sql1 = "insert into score(studentid,english,math) values(300,400,400)";
            //string sql1 = "update score set english = 114514 where studentid = 4";
            //cmd.CommandText = sql1;
            //int r1 = cmd.ExecuteNonQuery();
            //Console.WriteLine(r1);

            string sql = "select count(*) from student";
            cmd.CommandText = sql;
            int r2 = Convert.ToInt32(cmd.ExecuteScalar());
            Console.WriteLine(r2);
        }
    }
    Console.ReadKey();
}

ADO.NET第二个对象–DataReader对象

  • 由图可知，DataReader对象是由Command对象创建的。具体方法为cmd.ExecuteReader(返回一个SqlDataReader对象)

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
static void Main(string[] args)
{
    string conStr = "Data Source = 192.168.215.193;Initial catalog = myschool;uid = sa;pwd = admin";

    //or

    SqlConnectionStringBuilder conSTR = new SqlConnectionStringBuilder();
    conSTR.DataSource = "192.168.215.193";
    conSTR.InitialCatalog = "myschool";
    conSTR.UserID = "sa";
    conSTR.Password = "admin";
    conSTR.PersistSecurityInfo = true;

    using (SqlConnection con = new SqlConnection(conStr))
    {
        string sql = "select sid, sname, sgender, sage as '年龄',sbirthday from student";
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            //读取表中所有数据
            //sqlDataReader数据库读取对象
            SqlDataReader sr = cmd.ExecuteReader();
            //服务器端已经产生了数据
            if (sr.HasRows)
            {
                //1、读取数据
                //2、让指针向下移动
                while (sr.Read())
                {
                    int idIndex = sr.GetOrdinal("sid");//GetOrdinal 根据列名获取列的索引
                    int id = Convert.ToInt32(sr[idIndex]);
                    Console.WriteLine(id);

                    string name = sr[1].ToString();
                    Console.WriteLine(name);

                    string gender = sr[2].ToString();
                    Console.WriteLine(gender);

                    int age = Convert.ToInt32(sr["年龄"]);//如果用as给列起了别名，则采用别名进行数据读取
                    Console.WriteLine(age);

                    //SQLServer中的NULL值表示不知道
                    //在C#中使用DBNULL表示

                    if (sr["sbirthday"] != DBNull.Value)
                    {
                        DateTime dt = Convert.ToDateTime(sr["sbirthday"]);
                        Console.WriteLine(dt.ToString());
                    }
                    Console.WriteLine("==========================================================");
                }
            }
        }
    }
    Console.ReadKey();
}

登录练习

  • 为防止SQL注入，在判断用户名和密码的时候使用变量而非直接插入。
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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void button1_Click(object sender, EventArgs e)
    {
        //1、判断用户在两个文本框中都输入了具体内容
        if(!IsInput())
        {
            //2、判断用户名和密码是否正确  查询数据库
            bool b = IsLogin(txtUserName.Text.Trim(),txtUserPwd.Text.Trim());
            if (b)
            {
                MessageBox.Show("登录成功");
            }
            else
            {
                MessageBox.Show("登录失败");
            }
        }
    }

    bool IsInput()
    {
        if(txtUserName.Text.Trim() == " " || txtUserPwd.Text.Trim() == " ") 
        {
            return true;
        }
        return false;
    }

    bool IsLogin(string name,string pwd)
    {
        string conStr = "Data Source = 192.168.215.193;INitial Catalog = MySchool;Uid = sa;pwd=admin";
        using (SqlConnection con = new SqlConnection(conStr))
        {
            string sql = "select * from [user] where uname = @name and upassword = @pwd";//防止SQL注入
            using (SqlCommand conCmd = new SqlCommand(sql, con))
            {
                //conCmd.CommandText = sql;
                con.Open();
                //创建两个SQL对象n
                //SqlParameter sqlParameter1 = new SqlParameter("@name", name);//前面是变量名，后面是要给这个变量赋值的值
                //SqlParameter sqlParameter2 = new SqlParameter("@pwd", pwd);
                ////把变量添加到cmd对象中
                //conCmd.Parameters.Add(sqlParameter1);
                //conCmd.Parameters.Add(sqlParameter2);

                conCmd.Parameters.AddWithValue("@name", name);
                conCmd.Parameters.AddWithValue("@pwd", pwd);

                SqlDataReader sr = conCmd.ExecuteReader();
                if (sr.HasRows)//如果在数据库中能找到数据则说明用户名和密码是正确的
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}

#Day04 2024.06.01

  • 今日知识点
    1. 从数据库中读取数据（业务训练）
    2. 从数据库中写入数据（业务训练）
    3. 将连接服务器的string语句写入配置文件
    4. 熟练度究极练习！！！！！！！

省市联动选择器

首先要了解如何将连接服务器的string语句写入配置文件

首先点击这里

然后在程序集里搜索configuration添加引用

最后要using 一下

XML代码如下所示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>

	<!--可以给当前程序，添加多个连接字符串-->
	<connectionStrings>
		<add  name="conStr" connectionString="Data Source=.;Initial Catalog = DBPromary;Integrated Security = True"/>
		<!--<add/>-->
	</connectionStrings>
</configuration>

-- string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;//C#使用该string语句

#Day05 2024.06.02(07补)

省市联动选择器（使用SQLHelper）

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
public Form1()
{
    InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
    //1、先加载所有的省份

    string sql = "select * from promary";
    DataTable dt = SqlHelper.ExecuteDataTable(sql);

    //proid  proname
    //告诉下拉框要显示的值
    //DisplayMember 给用户看的
    cboProvince.DisplayMember = "proName";
    //绑定给程序员使用的ID Tag = 程序员  Text = 用户 Name = 程序员  控件对象的名称
    cboProvince.ValueMember = "proid";

    cboProvince.DataSource = dt;
}

private void cboProvince_SelectedIndexChanged(object sender, EventArgs e)
{
    //DisplayMember和ValueMember是数据绑定用的，只能用来数据的绑定，不能用来数据的获取

    int proid = Convert.ToInt32(cboProvince.SelectedValue);

    //省份下拉框选项发生变化的时候，加载市区的数据
    string sql = "select * from city where proid =" + proid;

    DataTable dt = SqlHelper.ExecuteDataTable(sql);

    //给城市的下拉框进行数据绑定
    cboCity.DisplayMember = "cityName";
    cboCity.ValueMember = "cityID";
    cboCity.DataSource = dt;
}

电话本程序

  1. 绑定数据源到控件：

    通过将数据源（例如 DataTable）绑定到控件（例如 DataGridView），可以将数据库中的数据加载到控件中进行显示。

    1
    dgvPhone.DataSource = dt;

  2. 控件属性绑定：

    通过 DataBindings.Add 方法，将控件的某个属性（例如 Text）绑定到数据源的某个字段上，使控件的值随数据源的变化而自动更新。

    1
    2
    3
    4
    txtpName.DataBindings.Add("Text", dgvPhone.DataSource, "pname");
    txtHomePhone.DataBindings.Add("Text", dgvPhone.DataSource, "phomenum");
    txtCellPhone.DataBindings.Add("Text", dgvPhone.DataSource, "pnum");
    cboType1.DataBindings.Add("SelectedValue", dgvPhone.DataSource, "ptypeid");

  3. 清除绑定：

    在重新绑定之前，使用 DataBindings.Clear 方法清除现有的所有绑定，以避免重复绑定导致的问题。

    1
    2
    3
    4
    txtpName1.DataBindings.Clear();
    txtHomePhone.DataBindings.Clear();
    txtCellPhone.DataBindings.Clear();
    cboType1.DataBindings.Clear();

  4. 绑定 DataGridView 选择模式：

    设置 DataGridView 的选择模式和行头可见性。

    1
    2
    dgvPhone.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
    dgvPhone.RowHeadersVisible = false;

  5. **绑定数据到 ComboBox**：

    设置 ComboBox 的 DisplayMember 和 ValueMember 属性，并绑定数据源以实现下拉列表的显示和选择功能。

    1
    2
    3
    4
    cboType.DisplayMember = "ptname";
    cboType.ValueMember = "ptid";
    cboType.DataSource = dt;

  6. 在 DataGridView 行点击事件中处理绑定：

    在 DataGridView 的行点击事件中，通过选中行的单元格值更新其他控件的值。

    1
    2
    3
    4
    txtpName1.Text = dgvPhone.Rows[e.RowIndex].Cells[4].Value.ToString();
    txtHomePhone.Text = dgvPhone.SelectedRows[0].Cells[2].Value.ToString();
    txtCellPhone.Text = dgvPhone.SelectedRows[0].Cells[1].Value.ToString();
    cboType1.SelectedValue = dgvPhone.SelectedRows[0].Cells[3].Value.ToString();

    SqlHelper默写顺序

    1. 创建SQL语句。private static string sql = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    2. 创建查询方法。Datatable ExecuteDatatable
    3. 创建增删改查方法。int ExecuteNonQuery
    4. 创建聚合函数方法。object ExecuteScalar

#Day06 2024.06.05(07补)

  • SQL高级语法

case then

  • then后面的值的数据类型必须一样！！！

  • when后面可以不一致

  • 多行多列转换

  • 对于每个学生，CASE 语句会根据科目来选择相应的 result。

    然后，SUM 函数对每个科目进行分组求和。

索引 dic<k,v>

  1. 聚集索引：值不能重复（例如主键），一个表只能有一个聚集索引
  2. 非聚集索引：值可以重复

嵌套子查询

  • 把一条查询语句的结果作为另一条查询语句的数据集使用

相关子查询和分页查询

  • 嵌套子查询先执行的是子查询
  • 相关子查询先执行的是主查询（子查询执行N遍），一般得起名

表连接

cross join外连接

inner join内连接

left join以左表为基础，结果左表的数据全都存在

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--案例1：查询所有学生的姓名、年龄及所在班级
select sname as '姓名',sage as '年龄',cname as '班级'
from student as a inner join class as b on a.sclassid = b.cid
--案例2：查询年龄超过20岁的学生的姓名、年龄及所在班级
select sname as '姓名',sage as '年龄',cname as '班级'
from student as a inner join class as b on a.sclassid = b.cid
where a.sage < 20
--案例3：查询学生姓名、年龄、班级及成绩(三表联合查询)
select a.sname as '姓名',a.sage as '年龄',b.cname as '班级',c.english as '英语成绩'
from student as a inner join class as b on a.sclassid = b.cid inner join score as c on a.sid = c.studentid
--案例4：查询所有学生(参加及未参加考试的都算)及成绩
select a.sname as '学生姓名',c.english as '英语成绩',
case c.math 
	when c.math then c.math
	else 0
end as '数学成绩'
from student as a inner join class as b on a.sclassid = b.cid inner join score as c on a.sid = c.studentid

视图

  • 视图是对【查询】SQL语句的封装（不用在C#端拼写复杂的SQL语句）
  • 提升程序运行速度
  • 防止未经许可的用户访问敏感数据（只暴露你想暴露的数据）
  • 缺点：不容易维护

SQL变量

  • set没法一次性给多个变量赋值，赋值有一般都使用select
  • @@identity–获取最近一次插入数据的ID

SQL语法

事务

  • 事务用四个字形容–同生共死

#Day07 2024.06.06(27补)

存储过程

KilgourNote:

先说结论

再说论据

最后再总结

  • 优点
    1. 已经被编译好了，重复执行，不需要重复编译
    2. 减少了跟数据库通信的次数
    3. 减少了数据的传输量
  • 缺点
    1. 难以维护
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
--创建有参数的存储过程
select * from score

--让不及格的人数不到总人数的一般
if exists(select * from sys.objects where name = 'usp_UpdateScore')
	drop proc usp_updateScore
go
create proc usp_UpdateScore
	--参数
	--不需要接declare
	@englishpass int,
	@mathpass int
as
	--声明一个变量，存储不及格的人数
	declare @failedCount int
	--给不及格的人数赋值
	select @failedCount = count(*) from score where english < @englishpass and math < @mathpass
	declare @totalCount int
	--给总人数赋值
	select @totalCount = count(*) from score
	while(1=1)
		begin
			if(@failedCount > @totalCount / 2)
				begin
					update score set english = english + 2,math = math + 2 
					select @failedCount = count(*) from score where english < @englishpass and math < @mathpass
				end
			else 
				begin
					break
				end
		end

--执行该存储过程
exec usp_UpdateScore 60,60