.NET学习之数据库总结

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类型1NULL|NOT NULL      , 列名2类型2NULL|NOT NULL      , 列名3类型3NULL|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用法

通配符

描述

%

替代一个或多个字符

_

仅替代一个字符

[charlist]

字符列中的任何单一字符

[^charlist]

或者

[!charlist]

不在字符列中的任何单一字符

引用：

--学生信息表IF OBJECT_ID (N'Students', N'U') IS NOT NULL    DROP TABLE Students;GOCREATE 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;GOCREATE TABLE Majors(    ID int primary key not null,    Name nvarchar(50))--课程表IF OBJECT_ID (N'Courses', N'U') IS NOT NULL    DROP TABLE Courses;GOCREATE 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

                

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)：可持久的派生表，无法存储数据

GOCREATE VIEW vw_FenYeableAS SELECT ROW_NUMBER() OVER (ORDER BY 学号) AS num    ----自动增长,课程,成绩FROM  ScoreGO 

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

-->语法：
/* create function fn_函数名

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

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

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

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

go/*begin tryend trybegin catchend catch*/create proc usp_test4@from as char(4), @to as char(4), @money as money, @isSuccess int output -- 与C#一模一样，在存储过程内部赋值即可asbeginbegin transactionbegin tryupdate bank set balance=balance - @money where cid=@fromupdate bank set balance=balance + @money where cid=@tocommitset @isSuccess = 1;end trybegin catchrollbackset @isSuccess = 0;end catchendgo     

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

gocreate proc usp_FenYe1@pageIndex int = 1-- 第几页,@pageCount int = 10-- 每页条数asbeginselect * from fn_FenYe(@pageCount, @pageIndex);endgoexec 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 Scoregroup 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)触发器

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