数据库语言 数据库的简易流程(数据库客户端软件和数据库服务软件的执行流程) 主键的概念 如何创建主键 如何创建外键 主外键关系的概念以及使用 数据库的主要类型 数据库的主要数据类型 使用sql语句来创建数据库和表 约束分类 top的使用 distinct的使用(去除
数据库语言数据库的简易流程(数据库客户端软件和数据库服务软件的执行流程)主键的概念如何创建主键如何创建外键主外键关系的概念以及使用数据库的主要类型数据库的主要数据类型使用sql语句来创建数据库和表约束分类top的使用distinct的使用(去除重复数)聚合函数聚合函数注意事项between and 和 in 的使用like, not like 通配符(%,_,[],^)空值处理:null 是什么?排序(order by id asc / desc)(默认是哪一种排序?)分组(group by ),单条件分组,多条件分组(分组时,要注意的事情[位置,列])筛选(having的使用),它和where的区别类型转换(cast,convert)union,union all的使用一次插入多条数据字符串函数ado.net主要类数据库连接字符串sqlconnection类的state属性sqlcommand类的方法:statementcompleted事件的触发获得刚刚插入数据库的自增idsql注入攻击(避免方式?)如何使用迭代生成树形菜单单例模式(单例模式的创建)datatable的使用类sqldataadapter的使用(重点)类sqlcommandbuilder的使用(注意:他必须操作的是有主键的数据库)提取查询语句,封装sqlhelper类(不仅要会,而且要理解思想)sql中的switch语句sql中的子查询sql中的exists用法sql中的anysql中的allsql2008 微软官方推荐分页方式sql中表等值连接(内连接) inner joinsql中表左连接(左外连接) left joinsql中表右连接(右外连接) right joinsql中表交叉连接(两张表的乘积)sql中表全连接 full joinsql中变量sql中的事务创建存储过程存储过程带输出参数调用存储过程触发器定义触发器类型:触发器触发条件:什么是索引索引类型什么是填充因子什么是临时表什么是局部临时表什么是全局临时表什么是三层结构三层结构的目的具体的三层是哪三层三层之间的关系三层结构的优缺点邮件发送方法excel导入导出md5加密解密方法读取数据库后,判断dataset里列的值是否为空项目术语数据库语言dml(数据操作语言)selectinsertdeleteupdateddl(数据定义语言--建表建库等)creatdropalterdcl(数据控制语言)grantrevoke数据库的简易流程(数据库客户端软件和数据库服务软件的执行流程)安装时有一个客户端管理软件和一个服务器。我们平常操作的是客户端软件,发送脚本到服务器dmsm(数据库服务器),服务器分析和解析并展示执行结果。主键的概念唯一的标识一行数据操作可以作为其他表的外键来引用业务主键:有意义(例:身份证号)逻辑主键:唯一的意义就是标识一行如何创建主键
alter table [dbo].[operatetype]
add constraint [pk_operatetype]
primary key clustered ([id] asc);
go 如何创建外键alter table [dbo].[roleaction]
add constraint [fk_roleaction_role]
foreign key ([role_roleid])
references [dbo].[roleinfo]
([roleid])
on delete no action on update no action;
go主外键关系的概念以及使用作用可以优化查询效率减少数据冗余维护方便两张聊存在依赖数据时,就可以使用主外键来解决,其中将依赖列作为主键的就叫作:主键表;另一个就叫作外键表,外键表的外键列数据取自主键表的主键主外键约束为什么为了避免两张表的主外键数据出现不一致的情况,需要建立主外键约束关系作用当两张表数据修改时出现主外键数据不一致,则报错,拒绝修改数据库的主要类型网状数据库、层次数据库和关系数据库数据库的主要数据类型char类型:当储存的数据小于长度时,会自动用空格来补充nchar类型:使用unicode编码,任意字符都占两个字节varchar类型:当储存的数据小于长度时,不会自动用空格来表示datetime类型:储存日期时间数据使用sql语句来创建数据库和表创建数据库create database myschool
on
(
--括号一定是圆括号
name='myschool_data',--数据库名称
filename='d:\myschool_data.mdf',--物理文件名
size=5mb,--初始大小
maxsize=10mb,--最大大小
filegrowth=15% --主文件增长率
)
log on
(
name='myschool_log',--日志文件名
filename='d:\myschool_log.ldf',--日志物理文件名
maxsize=4mb,--最大大小
size=2mb,
filegrowth=1mb
)
go创建表create table student
(--创建学生信息表
sid int identity(1,1) primary key,--自动编号
sclassid int not null, --班级外键
sname nvarchar(50) not null,
sage int not null,
sno numeric(18,0),--身份证号,十八位数字,小数为
ssex char(2) not null,
semail varchar(50)
)约束分类非空约束alter table employees alter column empname varchar(50) not null主键约束alter table score
add constraint pk_score primary key(sid)唯一约束alter table student
add constraint uq_student unique(sno)默认约束alter table student
add constraint df_student default('男') for ssex检测约束alter table student
add constraint ck_student check(sage >=18 and sage 100)外键约束alter table employees
add constraint fk_deptid_depid foreign key(deptid) references department(depid) top的使用select top 2 * from teacher
select top 20 percent * from teacher--按百分比算行数,如果出现小数,则+1(类似天花板函数celling)
distinct的使用(去除重复数)select distinct(age) from teacher --求出不重复的age
select distinct age,salary from teacher --求出不重复的age和salary的组合(相当于将两个列的值加在一起的值不重复)
聚合函数max(最大数)select max(userid),userid from dbo.userinfo group by useridmin(最小数)select min(userid),userid from dbo.userinfo group by useridavg(平均数)select avg(userid),userid from dbo.userinfo group by useridsum(和)select sum(userid),userid from dbo.userinfo group by useridcount(计数)select count(userid),userid from dbo.userinfo group by userid聚合函数注意事项聚合函数对null值不计算聚合函数的结果集是单个的值,没办法与多个值的结果集集合不能整除的情况该列是小数列:如果除出来是小数,则返回带小数的结果该列是整数列:如果除出来是小数,也是直接当整数算,不会自动+1between and 和 in 的使用betwenn and求两个值的区间,推荐使用between andselect * from userinfo where id between 40 and 50in当使用子查询配合in关键字时,子查询的结果集必须只有一个列,而且列的类型必须和条件列类型一直select * from userinfo where id in (40,41)select * from teacher where id in (select id from score where english>98 and math> 98)like, not like 通配符(%,_,[],^)^只有mssqlserver支持,其他dbms用not like,且只能放在[]里用not like 不取like的值_ 代表单个任意字符;% 代表任意长度任意字符select * from student where sname like '张%'[] 代表取值范围内的单个字符^ 取非符号,必须和[]连用空值处理:null 是什么?查询列中null的值用is关键字select *from teacher where name is nullisnull函数如果第一个参数为null,则使用第二个参数作为返回值,否则,返回第一个参数select isnull (email ,'aaa')as email from userinfo排序(order by id asc / desc)(默认是哪一种排序?)order by 子句要放到where子句之后asc--默认是升序select * from userinfo where sort=0 order by id ascdesc--降序select * from userinfo order by phone desc,id desc多条件排序时,如上,当phone中有相等的值,那么这些值按id排序分组(group by ),单条件分组,多条件分组(分组时,要注意的事情[位置,列])根据性别统计男女的人数select count(*) gender from teacher group by gendergroup by 子句必须放到where语句之后,group by 与 order by 都是对筛选后的数据进行处理,而where是用来筛选数据的多条件分组分组条件,是age和gender列的组合,只有当age和gender一样的值时,才分为一组 select count(*),age, gender from teacher group by age,gender分组一般和聚合函数使用的分组的结果是组信息,与表里的当行信息无关一旦出现分组,前面要么是聚合函数,要么是分组的条件筛选(having的使用),它和where的区别having是group by的条件对分组后的数据进行筛选(与where类似,都是筛选,只不过having是用来筛选分组后的组)在where中不能使用聚合函数,必须使用having,having要位于group by之后having的使用几乎是与where一样的,也可以用inselect sclassid,count(sname) from student group by sclassid having count(sname)>3类型转换(cast,convert)castselect cast(salary as int) from teacherconvertselect convert(int,salary) from teacherunion,union all的使用是对两个集合操作的,两个集合必须具有相同的列数,列具有相同的数据类型(至少能隐式转换的),最终输出的集合的列名由第一个集合的列名来确定(可以用来连接多个结果)要union的两个结果集的列数必须一样要连接的两个列的类型鼻血一直(或者类型兼容,比如varchar和nchar)union默认去除了结果集中重复数据而union all 不会删除重复的数据;(因此,union all 的效率要比union的效果高一些)一次插入多条数据insert into score(studentid,english,math)
select 1,80,100 union
select 1,80,100 union
select 3,50,59 union all
select 4,66,89 union
select 5,59,100字符串函数len()select len('字符串的长度')结果是6len返回的是去掉尾部空格后的字符数datalength()select datalength('字符串的长度')结果是12返回的是包含尾部空格的字符串的字节数lower()select lower('jjjjjj')转小写upper()转大写ltrim()去除左边空格rtrim()去除右边空格left()从左边取指定长度字符串right()从右边取指定长度字符串substring(string,start , length) 从指定位置取指定长度字符串ado.net主要类connection主要是开启程序和数据库之间的连接,没有利用连接对象将数据库打开,是无法从数据库中取得数据的close和dispose的区别close以后还可以open,dispose以后则不能再用command主要可以用来对数据库发出一些指令,例如可以对数据库下达查询、新增、修改、删除数据等指令,以及调用存在数据库中的存储过程等,这个对象是架构在connection对象上,也就是command对象是通过连接到数据源dataadapter主要是在数据源以及dataset之间执行数据传输的工作,它可以透过command对象下达命令后,并将取得的数据放入dataset对象中,这个对象是架构在command对象上,并提供了许多配合dataset使用的功能dataset这个对象可以视为一个暂存区(cache),可以把从数据库中查询到的数据保存起来,试着可以将整个数据库显示出来,dataset是放在内存中的,dataset的能力不只是可以存储多个table而已,还可以透过dataadapter对象取得一些例如主键等的数据结构,并可以记录数据表间的关联。dataset对象可以说是ado.net中重量级的对象,这个对象架构在dataadapter对象上,本身不具有和数据源沟通的能力,也就是说我们是将dataadapter对象当作dataset对象及数据源间传输数据的桥梁,dataset包含诺干datatable,datatable包含诺干datarowdatareader当我们只需要循序的读取数据而不需要其它操作时,可以使用 datareader 对象。datareader 对象只是一次一笔向下循序的读取数据源中的数据,这些数据是存在数据库服务器中的,而不是一次性加载到程序的内存中的,只能(通过游标)读取当前行的数据,而且这些数据是只读的,并不允许作其它的操作。因为 datareader 在读取数据的时候限制了每次只读取一笔,而且只能只读,所以使用起来不但节省资源而且效率很好。使用datareader 对象除了效率较好之外,因为不用把数据全部传回,故可以降低网络的负载。 ado.net 使用 connection 对象来连接数据库,使用 command 或 dataadapter 对象来执行sql 语句,并将执行的结果返回给 datareader 或 dataadapter ,然后再使用取得的datareader 或 dataadapter 对象操作数据结果。数据库连接字符串server=.;database='dbname';uid=sa;pwd=123sqlconnection类的state属性是判断数据库连接状态的一组枚举值sqlcommand类的方法:executenonquery()执行非查询语句(增、删、改),返回受影响函数executescalar()返回查询结果集的首行首列,是一个object类型。executereader()返回一个游标指针,然后用read()方法一行一行的读。性能很好。statementcompleted事件的触发每条sql语句执行完后会触发获得刚刚插入数据库的自增id方法一:string strsql=insert into student(name,age,cid) values ('{0}','{1}','{2}'); select @@identity; 方法二:string strsql=insert into student(name,age,cid) output inserted.id values('{0}','{1}','{2}') sql注入攻击(避免方式?)参数化查询,sqlparameter如何使用迭代生成树形菜单void buildtree(treenode node, listcategory> list)
{
//获得父元素的id
int pid = convert.toint32(node.tag);
foreach (category model in list)
{
//找出集合中 父id 和传入参数一致的
if (model.tparentid == pid)
{
//创建子节点
treenode sonnode = new treenode(model.tname);
//将子节点自己的id存入 节点的 tag中
sonnode.tag = model.tid;
//将子节点 添加到父节点中
node.nodes.add(sonnode);
//迭代 调用(看看子节点还有没有子节点)
buildtree(sonnode, list);
}
}
}
单例模式(单例模式的创建)关闭要实现单例模式的类的构造函数(私有化构造函数)在类中添加一个私有的类的静态变量在类中添加一个共有的方法,返回当前的静态变量,在方法中判断静态变量是否为null,如果为null则先new在返回public person getsingleinstance()
{
//在多线程情况下,需要 加锁
//这里:第一,直接锁当前的对象; 第二,定义一个线程 锁的标识volatile,然后锁住标识
lock(_mysingleperson)
{
if(_mysingleperson == null || _mysingleperson.isdisposed)
{
_mysingleperson = new person();
}
}
return _mysingleperson;
}
datatable的使用datatable dt = new datatable(); foreach(datarow row in dt.rows) { string str = row[columnname].tostring(); .... }
类sqldataadapter的使用(重点)fill()方法注意:sqldataadapter的内部还是使用了sqldatarader去读取数据,只不过读取的过程微软帮我们封装了类sqlcommandbuilder的使用(注意:他必须操作的是有主键的数据库)往里边传入一个dataadapter对象,然后可以用相应方法直接执行对表的操作,使用相当简单,功能也很强大,主要是微软为我们做了一系列封装,简化了程序员的操作(但一般我们都不使用这种方式对数据表进行操作)//sql命令生成助手(适配器) sqlcommandbuilder scb=new sqlcommandbuilder(da); //适配器在助手生成的sql语句帮助下,成功的修改类数据库 da.update(ds,temptable); messagebox.show(修改成功)
提取查询语句,封装sqlhelper类(不仅要会,而且要理解思想)
sql中的switch语句直接对列进行值的判断then的值必须是同一种类型(因为列的类型只能有一个)select pid,
case ptypeid
when 1 then 'a'
when 2 then 'b'
else 'c'
end,
pname from phonenum一般用来为列的取值范围case不一定非要和列在一起判断select studentid,成绩=(
case
when english between 90 and 100 then 'a'
when english between 80 and 89 then 'b'
when english between 70 and 79 then 'c'
when english between 60 and 69 then 'd'
when english 60 then 'e'
else '缺考'
end
)
from scoresql中的子查询在结果集的基础上,再次查询,一定要给结果集取别名,否则会报错select * from (select * from bc) as tempsql中的exists用法先执行的是主查询,然后再执行子查询,将匹配行的数据显示出来select * from tbl where exists(select * from phontype where tbl.pid=phontype.pid)判断结果集是否存在,但效率低if exists(select * from tbl where ptid=1)
select 1
else
select 2sql中的anyany相当于条件是很多个orselect * from phone where ptypeid=any(select ptype from b)select * from phone where ptypeid=1 or ptypeid=2sql中的allall相当于条件是很多个andselect * from phone where ptypeid=all(select ptypeid from b)select * from phone where ptypeid=1 and ptypeid=2any、in、all匹配结果集时,结果集只能有一个列sql2008 微软官方推荐分页方式select * from(
select row_number() over(order by ar_id)as num,*from area
)as t where runm between 10 and 14sql中表等值连接(内连接) inner join任何一方都必须满足连接条件,如果有一方不满足连接条件就不显示select * from phone inner join phonetype on n.ptypeid=t.ptidsql中表左连接(左外连接) left join保证左边的数据都有,根据左边数据匹配,显示join左边表的所有记录,右侧表中符合条件的显示,不符合条件的就显示nullselect * from phone n left join phonetype t on n.ptypeid=t.ptidsql中表右连接(右外连接) right join保证右边的数据都有,根据右边数据匹配select * from phonenum n right join phonetype t on n.ptypeid = t.ptidsql中表交叉连接(两张表的乘积)select * from student cross join scoresql中表全连接 full join左右两边的数据都进行匹配,相当于左连接和右连接相加和inner join刚好相反select * from phonenum n full join phonetype t on n.ptypeid = t.ptid
sql中变量声明变量declare @age int变量赋值set @age=3声明并赋值select @age=3如果表数据出现多行,则将最后一行的列赋值给变量select @age=age from student输出变量的值print @agesql中的事务begin transaction 开始事务rollback transaction 回滚事务commit transaction 提交事务银行事务转账例子declare @err int
set @err = 0
begin transaction
begin
print '开始事务'
update bank set balance=balance-1000 where cid='0001'
set @err=@err+@@error
update bank set balance=balance + 1000 where cid='0002'
set @err=@err+@@error
if(@err>0)
begin
rollback transaction
print '回滚事务'
end
else
begin
commit transaction
print '提交事务'
end
end创建存储过程create procedure usp_transfermoney
@intperson varchar(20)
@outperson varchar(20) '123' --可以给默认值,当参数有默认值的时候,执行的时候可以不传该参数
@abcperson varchar(20)
as
select @intperson,@outperson,@abcperson存储过程带输出参数--登陆成功返回用户名不存在返回密码错误返回登陆超过次返回试登陆次数超过次返回
--drop proc login
create proc login
@result int output,
@times int output,
@name varchar(10),
@pwd varchar(10)
as
select @times = utimes from [user] where uname=@name
if(@times>=3)
begin
set @result = 4
return
end
if exists(select * from [user] where uname=@name)
begin
if exists(select * from [user] where uname=@name and upwd=@pwd)
begin
set @result = 1
end
else --密码错误返回
begin
set @result = 3
update [user] set utimes= utimes+1 where uname=@name
select @times = utimes from [user] where uname=@name
end
end
else --用户名不存在返回
begin
set @result = 2
end
insert into [user] values('admin','000000',0)
update [user] set utimes=0
select * from [user]
declare @times int,@r int
exec login @r output,@times output ,'admin111','admin'
print 'times'+cast(@times as varchar)
print 'result'+ cast(@r as varchar)调用存储过程无参数的存储过程调用exec usp_upgrade有参数的存储过程两种调用法exec usp_upgrade2 60,55 ---按次序exec usp_upgrade2 @english=55,@math=60 --参数名参数名参数有默认值时exec usp_upgrade2 --都用默认值
exec usp_upgrade2 1 --第一个用默认值
exec usp_upgrade2 1,5 --不用默认值
触发器定义触发器是一种特殊的存储过程触发器不能传参,通过事件进行触发执行针对tbl_abc表的新增之后的触发器
create trigger triggername on tbl_abc
after
insert
as
begin
select * from inserted --保存了引发新增触发器的新增数据,只能在触发器中访问
end
触发器类型after和for是在执行操作后触发instead of 是在操作之前触发(替换触发器),但不会执行原语句触发器触发条件updateinsertdelete什么是索引就是为某个表,某个列建立一个查找目录,如果没有目录,汉语字典就要一页一页的翻,有了目录直接翻目录,快速定位到查找位置索引类型聚集索引(拼音目录)数据的排列顺序,按照聚集索引排列(控制表的物理顺序)每个表只能建立一个聚集索引非聚集索引(偏旁部首目录)非聚集索引不会改变表的物理顺序每个表可以建立多个非聚集索引什么是填充因子就是为每页索引设置预留空间,在将来加入新索引的时候,就只需要更新当前索引页,而不需要更新索引树如每页索引1m大小,当填充因子设置为60%,在每页只存放60%的数据,剩下40%留给将来要加入的索引项使用什么是临时表是存在缓存中,而不是写在文件中可以在系统数据库→tempdb中找到什么是局部临时表生命周期在当前会话,当前会话结束就销毁临时表相当于c#的局部成员创建时表名前加一个#号create table #tempusers
(
id int identity(1,1),
name varchar(20)
)什么是全局临时表多个用户可以共享这个全局临时表当所有会话都退出的时候,这个全局临时表才会被销毁相当与c#的static 静态成员创建时,表名前面加两个##号create table ##tempusers
(
id