.NET学习之数据库总结

古城微笑少年丶 2022-08-07 13:38 158阅读 0赞

1、数据库

1)数据类型

\->数字类型：int 、bigint、money、numeric、bit

\->时间类型：dateime(从1753年1月1日到9999年12月31日)

\->字节类型：image(可变长度的二进制数据，最多2G)、bit(允许0、1、null)

\->字符串类型

\->ASCII：char(n)固定长度的字符串，最多8000个字符

varchar(n)可变长度的字符串，最多8000个字符

\->Unicode：nchar(n),固定长度的Unicode数据，最多4000个字符

nvarchar(n)可变长度的Unicode数据，最多4000个字符

2)创建数据库

-- create database 数据库名;
    create database 数据库的名字
    on	-- 数据文件的参数
    (
    	name='数据库的文件的逻辑名字'
    	, filename = '数据库文件路径'
    	, size=10mb -- 数据库文件的初始大小，单位可以是KB，MB，GB，TB
    	, maxsize=1024mb	-- 文件的最大限制
    	, filegrowth=10%	-- 文件的增长率
    )
    log on -- 日志文件的参数
    (
    	name='数据库的文件的逻辑名字_log'
    	, filename = '数据库文件路径_log'
    	, size=10mb -- 数据库文件的初始大小，单位可以是KB，MB，GB，TB
    	, maxsize=1024mb	-- 文件的最大限制
    	, filegrowth=10%	-- 文件的增长率
    ); -- 用分号表示SQL语句的结束
    go

2、数据表

1)创建表

create table 表名
    		 (
    		列名1	类型1	NULL|NOT NULL
    	      , 列名2	类型2	NULL|NOT NULL
    	      , 列名3	类型3	NULL|NOT NULL
    	        ...
    		 );

2)增删改查

insert into  表名（列1，列2） values('a','d');

select distinct(字段\[去掉重复值\])  列1,count(\*) from 数据表 group by...having 判断语句（限定）; //count(\*) 不忽略空值，计算全部的

select \* from test\_1 where name1 in ('aaa','bbb','ccc','ddd','eee');

delete from 表名 where 条件;

update 表名 set 列名1=值1 where 条件;

alter table Stu drop colum Ssex; //删除某个字段

\------select \*,avg(score) over() from Table, //\*和avg数量可以匹配了，，开窗函数

\-----case when stuSex='m' then '男' else '女' end as ‘性别’

4、完整的SQL

select top | distinct
    		字段列表
    		, 常量
    		, 函数
    		, 表达式
    		, 标量子查询
    		, ...
    	from 
    		结果集(结果集进行集合运算的结果)
    	where
    		逻辑判断
    		空值处理
    		范围处理
    		模糊处理
    	group by
    		分组字段(可以是多个字段，但是需要注意select只允许有
    			其中出现的字段、聚合函数与常量等)
    	having
    		与where类似，筛选分组后的结果
    	order by
    		排序的字段或表达式;

from->where->group by->having->select->order by <------顺序----->

id int identity(1,1) not null constraint PK\_TbLogin\_id primary key;

4、约束类别

◆主键约束(primary key constraint)：要求主键列数据唯一，并且不允许为空。  
◆唯一约束(unique constraint)：要求该列唯一，允许为空，但只能出现一个空值。  
◆检查约束(check constraint)：某列取值范围、格式限制等，如有关年龄的约束。  
◆默认约束(default constraint)：某列的默认值，如我们的男生学员较多，性别默认为男。  
◆外键约束(foregin key constraint)：用于两表间建立关系，需要指定引用主表的那列，哪个表是外键表，就修改哪个表。

--主键约束
 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 EndDateTimealter table student
 --唯一约束 <pre name="code" class="html"> alter table student

add constraint UQ\_stuName unique(stuName)

5、datepart(标记，时间)

\->select datepart(year,current\_timestamp)  
dateadd(标记，增量，时间)  
\->select current\_timestamp，dateadd(day,10,current\_timestamp)  
datediff(标记，开始时间，结束时间)

\->select datediff(day,‘’,‘’)

\-- cast（数据 as 类型）  
\--convert（类型，数据）

6、like用法

<table style="border:1px solid silver; border-collapse:collapse; word-break:break-word; color:rgb(0,0,0); font-family:Verdana,'Lucida Grande',Arial,Helvetica,sans-serif; font-size:12px; line-height:18px; widows:auto; width:552px"> 
 <tbody> 
 <tr> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> 通配符 </td> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> % </td> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> 替代一个或多个字符 </td> 
 </tr> 
 <tr> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> _ </td> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> 仅替代一个字符 </td> 
 </tr> 
 <tr> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> [charlist] </td> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> 字符列中的任何单一字符 </td> 
 </tr> 
 <tr> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> [^charlist] 或者 [!charlist] </td> 
 <td style="border:1px solid silver; border-collapse:collapse; padding:3px"> 不在字符列中的任何单一字符 </td> 
 </tr> 
 </tbody> 
</table>

引用：

--学生信息表
    IF OBJECT_ID (N'Students', N'U') IS NOT NULL
        DROP TABLE Students;
    GO
    CREATE TABLE Students(
        ID int primary key not null,
        Name nvarchar(50),
        Age int,
        City nvarchar(50),
        MajorID int
    )
    
    
    --专业信息表
    IF OBJECT_ID (N'Majors', N'U') IS NOT NULL
        DROP TABLE Majors;
    GO
    CREATE TABLE Majors(
        ID int primary key not null,
        Name nvarchar(50)
    )
    
    --课程表
    IF OBJECT_ID (N'Courses', N'U') IS NOT NULL
        DROP TABLE Courses;
    GO
    CREATE TABLE Courses(
        ID int primary key not null,
        Name nvarchar(50) not null
    )
    
    IF OBJECT_ID (N'SC', N'U') IS NOT NULL
        DROP TABLE SC;
    GO
    --选课表
    CREATE TABLE SC(
        StudentID int not null,
        CourseID int not null,
        Score int    
    )

7、ADO.NET

![Center][]

1)System.Data;描述数据的命名空间 System.Data.SqlClient; SQL Server的命名空间

2)四大类：Connction、Command、DataReader、DataAdapter

3)四大模型：ExecuteNonQuery模型  
如果执行的是非增删改操作返回-1  
\->准备连接字符串，sql语句  
\->创建连接对象  
\->创建执行对象  
\->打开连接  
\->执行操作，关闭

public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddRange(ps);
                        conn.Open();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }

ExecuteScalar模型：返回查询的第一行第一列的结果，一般与聚合函数联用，用来统计数量

public static  object ExecuteScaler (string sql, params SqlParameter[] ps)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddRange(ps);
                        conn.Open();
                        return cmd.ExecuteScalar();
                    }
                }
            }

ExecuteReader()模型

public static  SqlDataReader ExecuteReader(string sql, params SqlParameter[] ps)
            {
               
                    SqlConnection conn = new SqlConnection(connStr);
                   try
                   {  
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddRange(ps);
                        conn.Open();
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                }
                catch(Exception ex)
                {
                    conn.Dispose();
                    throw ex;
                }
            }

SqlDataAdapter 模型：sda.Fill(DataSet对象)，DataGridView DataSource，DataAdapter提供连接DataSet对象和数据源的桥梁。DataAdapter使用Command对象在数据源中执行SQL命令，以便将数据加载到DataSet中，并使DataSet中数据的更改与数据源保持一致。DataAdapter就像一根输水管，通过发动机，把水从水源输送到水库里进行保存。

public static DataSet GetDataSet(string sql, params SqlParameter[] ps)
            {
                DataSet ds = new DataSet();
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, connStr))
                {
                    sda.SelectCommand.Parameters.AddRange(ps);
                    sda.Fill(ds);
                }
                return ds;
            }

8、参数化查询

\->将原来需要字符串拼接的值，用一个@引导的变量名替代  
\->使用SQLParameter类型将参数变量与值绑定在一起  
\->将SQLParameter对象交给cmd对象的Parameters集合

\------------导出数据 StreamWriter（多次读取！）------------------File（单次读取）

9、SQL高级篇

1)case表达式

\-->select stuName case when stuSex='m' as '男' else '女' end as stuSex from Stu;  
\-->case when 条件 then 结果 when 条件 then 结果。。else 结果 end

2)交叉连接

\-->语法：select \* from 表1 cross join 表2

3)内连接：先做交叉再做删选

\--> 语法：from 表1 left \[inner\] join 表2 on  表1.字段 = 表2.字段

4)外链接：（左外、右外）

\-->语法：from 表1 left join 表2 on 条件

5)表表达式

\-->语法：

/\*  
with 别名  
as  
(  
结果集  
)  
紧跟查询  
\*/

WITH B AS 
    (
        SELECT * FROM xxx WHERE Id > 5
    )
    SELECT * FROM B

6)派生表

7)分页(重要)

select \* from Stu where stuId between M\*(N-1)+1 and M\*N

8)视图(View)：可持久的派生表，无法存储数据

GO
    CREATE VIEW vw_FenYeable
    AS 
    	SELECT ROW_NUMBER() OVER (ORDER BY 学号) AS num    ----自动增长
    			,课程
    			,成绩
    			FROM  Score
    GO

内连表值函数(带有参数的"视图")

\-->语法：  
/\*create function fn\_函数名

(@参数名 as 类型, @参数名 as 类型, ...)  
returns table  
as  
return  
结果集 \*/

go
    create function fn_FenYe
    (@pageCount as int, @pageIndex as int)
    returns table
    as
    return 
    	select 
    		num
    		, stuId
    		, stuName
    		, stuSex
    		, stuBirthdate
    		, stuStudydate
    		, stuAddress
    		, stuEmail
    		, stuPhone
    		, stuIsDel
    		, stuInputtime
    		, classId
    	from vw_FenYeable 
    	where 
    		vw_FenYeable.num 
    		between (@pageIndex - 1) * @pageCount + 1 and @pageCount * @pageIndex;
    go
    select * from fn_FenYe(20, 9);

9)事务：如果一个操作满足原子性(要么全部成功那么全部失败)，持久性，隔离性（锁<共享锁（读），排它锁（增删改）>）与一致性，那么即事务。

\->begin transaction ....  
\->commit transaction 生效  
\->rollback transaction 回滚  
\-->@@ERROR 检测一个特定错误  
\-->@@ROWCOUNT 设置为受影响或被读取的行的数目

go
    /*
    	begin try
    	end try
    	begin catch
    	end catch
    */
    create proc usp_test4
    @from as char(4)
    , @to as char(4)
    , @money as money
    , @isSuccess int output -- 与C#一模一样，在存储过程内部赋值即可
    as
    begin
    	begin transaction
    		begin try
    			update bank set balance=balance - @money where cid=@from
    			update bank set balance=balance + @money where cid=@to
    			commit
    			set @isSuccess = 1;
    		end try
    		begin catch
    			rollback
    			set @isSuccess = 0;
    		end catch
    end
    go

declare @res int;
    exec usp_test4 '0001', '0002', 900, @res output;
    select @res;

go
    create proc usp_FenYe1
    @pageIndex int = 1	-- 第几页
    ,@pageCount int = 10		-- 每页条数
    as
    begin
    	select * from fn_FenYe(@pageCount, @pageIndex);
    end
    go
    
    exec usp_FenYe 10, 5;

10)存储过程：将一个执行的过程，封装成一个名字，然后使用这个名字就可以执行这个过程

/\*procedure  
create proc\[edurre\] 存储过程名字  
参数 as 类型 \[默认值|output\]  
as  
begin 代码 end  
\*/ 执行 。。。。。。。。。。。exec 存储过程名字

11)子查询

\-->select \* from Score where stuId in (select stuId from Stu where stuName='\*\*');

12)表的透视变换

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 Score
    group by 学号;

13)逻辑处理与流程控制

\-- 选择结构  
/\*  
if表达式  
begin end  
else if表达式  
begin end  
else  
begin end  
\*/  
\-- 循环结构  
/\*  
while 表达式  
begin end  
\*/  
  
\-- 定义变量  
declare @num int;  
\-- 为变量赋值  
set @num = 123;  
\-- 使用  
select @num;

\-- 系统变量常常使用@@来引导  
select @@VERSION  
select @@ERROR;

14)触发器

go
    create trigger tr_insert_监视 on TblLogin
    after
    insert
    as	
    	insert into Test(uid, pwd, isdel, inputtime, username, type)
    	select 
    		uid, pwd, isdel, inputtime, SUSER_NAME(), 'insert' 
    	from inserted;
    go
    
    select * from Test;
    select * from TblLogin;
    insert into TblLogin(uid, pwd) values(N'牛亮亮', 'niuniujiushiniu');
    
    insert into TblLogin(uid, pwd) values(N'马伦', 'supperhouse');
    
    select HOST_NAME();

[Center]: /images/20220805/2ab934a4b5b1493795ecef6f18dd598e.png