黑马程序员：MSSQLServer和ADO.net

---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------

数据库直白点说就是通过一定的组织关系来有序地存数据的，比较常见的是关系型数据库，比较符合我们的认识观。数据的存储首要的是数据的完整性，完整性主要依靠约束来实现，其次要控制冗余数据，减少成本，建立表间关系（外键约束）是减少冗余的一个重要手段。

MSSQLServer

MSSQLServer常用的数据类型如下：

 

系统数据库

（1）master数据库，是主要的控制数据库，记录了SQL Server实例所需的所有信息。

（2）model数据库，为新建的数据库提供模版。

（3）msdb数据库，由SQL Server Agent使用，用于调度任务和发出警报。

（4）tempdb数据库，保存临时表和其他数据对象，由SQL Server自动创建,也可以自定义创建，在每次启动实例时会重新创建，其中对象在SQLServer关闭时消失。

 

T-SQL是关系型数据库的通用语言

创建数据库：

if DB_ID('mydatabase') is not null
drop database mydatabase;
create database mydatabase
on
(
	name='mydatabase',--数据库逻辑名
	filename='文件名.mdf',--主文件名，一个数据库可以有多个文件，只能有一个主文件
	size=5,--单位可以是M(默认)
	filegrowth=5M --单位为M或%
)
log on
(
	name='mydatabase_log',
	filename='文件名.ldf',
	size=5,
	filegrowth=5 %
)

删除数据库的时候不能自己删除自己。

创建表：

if(OBJECT_ID('UseLogin','U'))is 
not null drop table UserLogin;
create table UseLogin
(
	Id int identity(1,1) primary key,
	useName nvarchar(20),
	Pwd varchar(20),
	loginTime datetime default(getdate()),
	errorTimes int default(0)	
);

为表添加约束：

alter table uselogin--添加约束
add constraint
UQ_uselogin_usename
unique(usename);--唯一约束
alter table uselogin
add constraint 
DF_uselogin_pwd
default('123456')for pwd;--默认约束

插入数据：

insert into UseLogin (useName)
values ('she'),('admin');

查询数据:

select *from UseLogin ;
select DATEDIFF(MINUTE,loginTime,GETDATE())
as dif from UseLogin;--求时间差

 

修改数据语法：

updata 表名 set 字段1=值1,字段2=值2where条件

Where中可以使用的他逻辑运算符：or、and、not、<、>、>=、<=、 <>（或!=）等
，优先级not > and > or

删除数据语法：

delete from 表名 [where]--不加where就是全部删除

 

关于常用的聚合函数MAX、MIN、AVG、子查询：

select (select MAX(scdatabase)from Scor)as 最高分,
(select Min(scdatabase)from Scor)as 最低分,
(select Avg(scdatabase)from Scor)as 平均分;

 

关于表的连接，分为交叉连接、内连接、左连接、右连接，举一个查询学生信息的例子：

--交叉连接与内连接外连接（左、右外连接）
select stuId ,stuName,DATEDIFF(YEAR,stuBirthday,GETDATE())as age,
case stuGender when 'm' then '男' 
			   when 'f' then'女' end as stuGender
,stuPhone,stuEmail,stuAddress,stuSchool, cName, cDescription
from dbo.Stu		--内连接在交叉链接的基础上用on进行筛选
	inner join		--如果有为null的数据将删除这一行数据
	dbo.Course 
	on(stu.cId=course.cId)
where stuisdel=0;

--查询学生分数
select stu.stuId,stuName,scId, scBase, scDataBase, scHTML_JavaScript, scPro, scProj
from
	Stu left join Scor
on stu.stuId=scor.stuId

关于case用法例子：

--case语法
--显示分数等级A(100-90)B(89-80)C(79-70)D(69-60)E
select scid,stuId,
	case when scBase between 100 and 90 then 'A'
		 when scBase between 80 and 89  then 'B' 
		 when scBase between 70 and 79  then 'C'
		 when scBase between 60 and 69  then 'D'
		 else 'E'
		 end as scBase,
		 case when scDataBase between 100 and 90 then 'A'
		 when scDataBase between 80 and 89  then 'B' 
		 when scDataBase between 70 and 79  then 'C'
		 when scDataBase between 60 and 69  then 'D'
		 else 'E'
		 end as scDataBase,
		 case when scHTML_JavaScript between 100 and 90 then 'A'
		 when scHTML_JavaScript between 80 and 89  then 'B' 
		 when scHTML_JavaScript between 70 and 79  then 'C'
		 when scHTML_JavaScript between 60 and 69  then 'D'
		 else 'E'
		 end as scHTML_JavaScript,
		 case when scPro between 100 and 90 then 'A'
		 when scPro between 80 and 89  then 'B' 
		 when scPro between 70 and 79  then 'C'
		 when scPro between 60 and 69  then 'D'
		 else 'E'
		 end as scPro,
		 scProj
	from Scor;

 进行一个查询，得到一个单值数据，将这个单值作为另一个查询的一部分(条件或结果)，就称这个查询为“单值子查询”：

select	scId, stuId, scBase, scDataBase, scHTML_JavaScript, scPro, scProj
from Scor	where stuid=(select stuId from Stu where stuName='张三')  -- 查询结果当作条件来用的

select *
from scor where stuId in(select stuId from Stu where stuGender='m') --多值子查询一般外查询用in筛选

 

表表达式，关于派生表就是将一个select查询语句查出的表值结果集作为数据源进行第二次查询，注意作为数据源的查询语句，绝对不能包含order by语句(除非有top语句)，作为数据源的查询语句，一定得包含列名。一般派生表嵌套查询，比较混乱，所以将表定义在外边，其后直接引用。

--with 表名 as
--(
--	查询语句
--)
--使用该表名的查询语句
with t as
(
	select stuId, stuName, DATEDIFF(YEAR,stuBirthday,GETDATE())as age,
	case stuGender when 'm'then '男'when 'f'then '女'end as 性别,
	stuPhone, stuEmail, stuAddress, stuSchool, stuInputTime,cName, cDescription
	from Stu left join Course on(stu.cId=course.cId)
	where stuIsDel=0
)
select *from t left join Scor on(t.stuId=scor.stuId) where stuName='张三';

 

关于事务处理，事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。

--事务
create table TestBank
(	Cid int primary key,
	[money] decimal check (money>=0)
)
insert into TestBank (Cid,money)values
(1,1000),(2,10)
alter table testbank
alter column [money] money
select *from TestBank
--把一批语句打包，要么都执行commit，一个有错误就全部回滚rollback
begin try
	begin transaction
	update TestBank set [money]=[money]-1000 where Cid=1
	update TestBank set [money]=[money]+1000 where Cid=2
	commit transaction
end try
begin catch
	rollback transaction 
end catch

关于存储过程可以理解为把一系列语句封装成一个方法，其实就是能完成一定操作的一组SQL语句。

--存储过程
--create proc usp_存储过程名字
--as
--begin
--	T-SQL语句
--end
--=====
create Proc usp_转帐   @money [money], @cid1 int ,@cid2 int as 
--带有返回值则在变量类型后加output
begin
	begin transaction
	begin try
		update TestBank set money=money-@money where Cid=@cid1
		update TestBank set money=money+@money where Cid=@cid2
		commit transaction
	end try
	begin catch
		rollback transaction
	end catch
end

ADO.NET

 ADO.NET其实就是一组包含操作数据库的类的类库。操作不同的数据库有不同的类，命名很规范，看名字就一目了然了。

SqlConnection用于连接数据库，有两个属性，ConnectionString表示连接字符串，一般为“datasource=计算机名\实例名;initialcatalog=数据库名;uid=用户名;pwd=密码”,在本机的话还可以用integrated security=true ， State属性表示连接状态，是枚举类型。还有2个重要方法Open和close。不过一般创建SqlConnection实例（非托管代码）时采用using语句自动释放。

SqlCommand用于执行SQL命令，包含了连接信息和需要执行的命令，执行的常用方式有三种，ExecuteNonQuery（增、删、改影响的行数）、ExecuteReader（返回DataReader对象类型）ExecuteScalar（返回结果集的第一行第一列值）。

            string sqlConn
                = @"server=.\yangfan;database=my_database;integrated security=true";
            string cmdText = "insert into UsersLogin(name,pwd)values ('admin','123456');";
            //链接数据库首先要有一个连接对象SqlConnection
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                
                using (SqlCommand cmd = new SqlCommand(cmdText,conn))
                {
                    if(conn.State==ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    int res = cmd.ExecuteNonQuery();
                    Console.WriteLine("{0}行受影响",res);
                    Console.ReadKey();
                }
            }

SqlDataReader实现读取数据（不过需要保持连接），不能直接实例化，需要通过执行SqlCommand实例的ExecuteReader方法来创建，一个重要方法Reader，读取一条记录并且将读取位置向下推进（一般需要判断是否还有下一条数据）。

SqlDataAdapter数据适配器，相当于封装了SqlConnection、SlCmmand和SqlDatareader，可以通过构造函数传入SqlCommand也可以通过为其属性赋值传入。一个重要方法FIll，这个方法可以在不打开数据库连接的情况进行数据操作，这样可以大大节约消耗，我们在需要时打开连接，把需要操作的数据全部保存在DataSet中，操作完后再连接数据库进行更新。

DataSet相当于内存中的数据库，保存离线数据，可以通过数据适配器的Fill方法来获得，当然也可以手动添加。一个DataSet可以包含多个DataTable，DataTable又可以包含多个DataColumn对象和DataRow对象。

手动添加DataSet：

        static void Main(string[] args)
        {
            DataSet ds = new DataSet("MyData1");
            DataTable dt= ds.Tables.Add("MyTable1");
            DataColumn dc = dt.Columns.Add("id",typeof(int));
            DataColumn dc1 = dt.Columns.Add("name", typeof(string));
            dc.AutoIncrement = true;
            dc.AutoIncrementSeed = 1;
            dc.AutoIncrementStep = 1;
            DataRow dr= dt.NewRow();
            dr["name"] = "sheig";
            DataRow dr1 = dt.NewRow();
            dr1["name"] = "afigagg";
            dt.Rows.Add(dr);
            dt.Rows.Add(dr1);
            dt.Rows.Add(new object[] { 3, "afgag3" });
            foreach (DataRow dr2 in dt.Rows)
            {
                Console.WriteLine("{0}\t{1}",dr2["id"],dr2["name"]);
            }

            ds.Tables.Add("MyTable2");
            ds.Tables.Add("MyTable3");
            ds.Tables.Add("MyTable4");
            ds.Tables.Add("MyTable5");
            ds.WriteXml("2.xml", XmlWriteMode.WriteSchema);

        }

通过数据适配器生成：

        static void Main(string[] args)
        {
            SqlDataAdapter sda = new SqlDataAdapter(
                @"select top 10 id,name,gender,age from tblname order by id desc",
                ConfigurationManager.ConnectionStrings["sql"].ConnectionString//通过配置文件读取连接字符串
                );
            DataSet ds = new DataSet();
            sda.Fill(ds);
            ds.WriteXml("3.xml");
        }

一般来说，对数据库的操作代码重用非常高，且繁琐，通过会把需要的操作封装在一个SqlHelper类，具体需要怎么封住看需要什么：

    public class SqlHelper
    {
        static string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        public static int ExecuteNonQuery(string commandText, params SqlParameter[] para)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                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.ExecuteNonQuery();

                }
            }
        }

        public static object ExecuteScalar(string commandText, params SqlParameter[] para)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    if (para.Length != 0 && para != null)
                    {
                        cmd.Parameters.AddRange(para);
                    }
                    if (conn.State == System.Data.ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] para)
        {
            SqlConnection conn = new SqlConnection(connStr);
            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(CommandBehavior.CloseConnection);
            }
        }

        public static DataSet DataAdapterFill(string commandText, params SqlParameter[] para)
        {
            DataSet ds = new DataSet();
            using (SqlDataAdapter sda = new SqlDataAdapter(commandText, connStr))
            {
                if (para != null && para.Length != 0)
                {
                    sda.SelectCommand.Parameters.AddRange(para);
                }
                sda.Fill(ds);
                return ds;
            }
        }
    }

---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------