数据库
-
Linq to sql :实现左连接、右连接
可以用 into 语法,配合DefaultIfEmpty() 方法加载右侧表中的空值;
左连接:
DataClassesDataContext db=new DataClassesDataContext();
var leftJoinSql=from s in db.Student
join b in db.Book on s.ID equals b.StudentID into temp
from tt in temp.DefaultIfEmpty()
select new
{
Sname=s.Name,
Bname=tt==null?””:tt.Name//这里是第二个集合有可能为空,需要判读; }右连接:把两个表的顺序颠倒一下,一些为空判断改一下。
-
update set from 语法
update A set 列名 = B.列名 from (select 编码,count(*)as 列名1,SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) AS 列名 from c表 group by 编码) as B where A.编号 = B.编号 -
ROW_NUMBER() OVER (PARTITION BY 列名 ORDER BY 列名)
删掉重复的数据
WITH cteUser AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY PrjGuid, UserCode order by PrjGuid) AS No,*
FROM PT_PrjInfo_ZTB_User
)
DELETE FROM cteUser
WHERE No > 1 -
列转字符串
DECLARE @user AS nvarchar(4000)
SELECT @user= ISNULL(@user + ‘,’ ,’,’) + UserCode
FROM (
SELECT DISTINCT UserCode FROM PT_PrjInfo_ZTB_User
WHERE PrjGuid = ‘1F9D636B-0B28-4BF7-8717-BF6544DCD244’
)AS T
SELECT @user -
行转列
DECLARE @itemName AS nvarchar(4000)
SET @itemName = ‘’
SELECT @itemName= ItemName +’],[’ + @itemName
FROM (
SELECT DISTINCT ItemName FROM Basic_CodeList
WHERE TypeCode = ‘ProjectState’
)AS T
SET @itemName = ‘[’ + @itemName
SET @itemName = SUBSTRING(@itemName,1,LEN(@itemName) - 2)
EXEC(’
SELECT * FROM
(
SELECT PrjCode,PrjGuid, PrjState, B.ItemName
FROM PT_PrjInfo_ZTB AS P
INNER JOIN Basic_CodeList AS B ON B.ItemCode = P.PrjState
WHERE B.TypeCode = ‘‘ProjectState’’
) AS A
PIVOT
(
MAX(PrjState) FOR ItemName in (’ + @itemName +’)
) AS pvt
') -
递归
WITH cteResType AS
(
SELECT ResourceTypeId, ParentId, ResourceTypeCode, ResourceTypeName
FROM Res_ResourceType
WHERE ParentId = ‘6A1A7050-1F92-4291-B932-43E1DFCE6E91’
UNION ALL
SELECT RT.ResourceTypeId, RT.ParentId, RT.ResourceTypeCode, RT.ResourceTypeName
FROM Res_ResourceType AS RT
INNER JOIN cteResType ON RT.ParentId = cteResType.ResourceTypeID
)
SELECT * FROM cteResType
–递归查询资源类型 Bery 2011-08-01 11:35
IF OBJECT_ID(‘ufn_GetResourceType’, ‘IF’) IS NOT NULL
DROP FUNCTION ufn_GetResourceType
GO
CREATE FUNCTION ufn_GetResourceType(@ResourceTypeId nvarchar(200))
RETURNS TABLE
AS
RETURN
(
WITH cteResourceType AS
(
SELECT ResourceTypeId, ParentId FROM Res_ResourceType
WHERE ResourceTypeId = @ResourceTypeId
UNION ALL
SELECT Res_ResourceType.ResourceTypeId, Res_ResourceType.ParentId FROM Res_ResourceType
INNER JOIN cteResourceType ON Res_ResourceType.ParentId = cteResourceType.ResourceTypeId
)
SELECT cteResourceType.ResourceTypeId FROM cteResourceType
)
GO
-
sql语句注意事项
alter table EPM_Con_ContractMain alter column PayMode varchar(400)
ALTER TABLE Sm_Resource_PriceType ADD IsDefault int default 0
ALTER TABLE Sm_Set add CONSTRAINT pk1 primary key(paraname)
ALTER TABLE Bud_Task DROP CONSTRAINT FK__Bud_Task__TaskTy__04115F34
ALTER TABLE Bud_Task DROP COLUMN TaskType
ALTER TABLE PT_d_bm add adss CHAR(100),yb CHAR(10),fx CHAR(10)
StartDate DATETIME DEFAULT(GETDATE()) -
sql索引
mysql的索引是B+Tree实现的,本质是数据结构
B+Tree的特性
(1)由图能看出,单节点能存储更多数据,使得磁盘IO次数更少。
(2)叶子节点形成有序链表,便于执行范围操作。
(3)聚集索引中,叶子节点的data直接包含数据;非聚集索引中,叶子节点存储数据地址的指针。
选择B+树而不是其他数据结构的原因主要是因为数据是保存在硬盘上而不是内存中,所以减少磁盘IO次数才是提升效率的关键。
原理:B+树索引通过找到被查数据所在的页,然后将页读入内存,再在内存中进行查找,最后得到查找的数据。
二叉搜索树:二叉树,每个结点只存储一个关键字,等于则命中,小于走左结点,大于
走右结点;
B(B-)树:多路搜索树,每个结点存储M/2到M个关键字,非叶子结点存储指向关键
字范围的子结点;
所有关键字在整颗树中出现,且只出现一次,非叶子结点可以命中;
B+树:在B-树基础上,为叶子结点增加链表指针,所有关键字都在叶子结点
中出现,非叶子结点作为叶子结点的索引;B+树总是到叶子结点才命中;
B*树:在B+树基础上,为非叶子结点也增加链表指针,将结点的最低利用率
从1/2提高到2/3;
分为聚集索引和非聚集索引
聚集索引:数据表的物理顺序和索引顺序一致(物理排序)
非聚集索引:数据表的物理顺序与索引顺序不一致(逻辑排序)
每个表只能有一个聚集索引,因为目录只能按照一种方式进行排序
一个表最多可以创建249个非聚集索引
使用聚集索引的最大的好处是能够根据查询条件迅速缩小查询范围,避免全表扫描
优点:
1、 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性;
2、 可以大大加快数据的检索速度
3、 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义
4、 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能
缺点:
1、 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加;
2、 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚集索引,那么需要的空间就会更大。
3、 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度
建立索引的原则
创建索引虽然可以提高查询速度,但是它是牺牲一定的系统性能。因此,在创建时,哪些列适合创建索引,哪些列不适合创建索引,需要进行判断,具体以下原则:
<1>.有主键的数据列要建立索引。因为主键可以加速定位到表中的某一行。
<2>.有外键的数据列要建立索引。外键列通常用于表与表之间的连接,在其上创建索引可以加快表间的连接。
<3>.对于经常查询的数据列最好建立索引。
A.对于需要在指定范围内快速或频繁查询的数据列,因为索引已经排序,其指定的范围是连续的,查询可以利用索引的排序,加快查询的时间。
B.经常用在WHERE子句中的数据列,将索引建立在WHERE子句的集合过程中,对于需要加速或频繁检索的数据列,可以让这些经常参与查询的数据列按照索引的排序进行查询,加快查询的时间。
<4>.对于那些查询中很少涉及的列、重复值比较多的列不要建立索引。
例如,在查询中很少使用的列,有无索引并不能提高查询的速度,相反增加了系统维护时间和消耗了系统空间。
<5>.对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大、要么很小,不利于使用索引。 -
存储过程
<1>.存储过程优点
A.执行速度快。
存储过程只在创造时进行编译,已经通过语法检查和性能优化,以后每次执行存储过程都不需再重新编译,而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
B.允许组件式编程。
经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。只需创建存储过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次。在代码上看,SQL语句和程序代码语句的分离,可以提高程序代码的可读性。
存储过程可以设置参数,可以根据传入参数的不同重复使用同一个存储过程,从而高效的提高代码的优化率和可读性。
C.减少网络流量。
一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现,而不需要在网络中发送数百行代码。
对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。
D.提高系统安全性。
可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限,建立特定的存储过程供用户使用,避免非授权用户对数据的访问,保证数据的安全。
<2>.存储过程缺点:
A.移植性差。依赖于数据库厂商,难以移植(当一个小系统发展到大系统时,对数据库的要求也会发生改变);
B.难以调试、维护。业务逻辑大的时候,封装性不够,难调试难以维护;
C.服务器不能负载均衡。复杂的应用用存储过程来实现,就把业务处理的负担压在数据库服务器上了。没有办法通过中间层来灵活分担负载和压力.均衡负载等。
优点:数据库已经编译过了,一次性编译
执行速度快
可以重复使用
安全性高
存储过程一旦创建完成并存在于数据中,可以反复调用
更快的执行速度
有效降低网络流量
较好的安全机制(可设定只有某此用户才具有对指定存储过程的使用权)
缺点:
1. 如果更改范围大到需要对输入存储过程的参数进行更改,或者要更改由其返回的数据,则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用,等等,这时候估计比较繁琐了。
-
可移植性差
由于存储过程将应用程序绑定到 SQL Server,因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要,则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。 -
大量采用存储过程进行业务逻辑的开发致命的缺点是很多存储过程不支持面向对象的设计,无法采用面向对象的方式将业务逻辑进行封装,从而无法形成通用的可支持复用的业务逻辑框架。
-
代码可读性差,相当难维护.
-
通配符
%:0个多个字符
-:仅替换一个字符
[charlist]:字符串中的任何单一字符,例如[ASN]表示以A或S或N开头
[!charlist]:不在任何字符串中的任何单一字符 -
视图的优缺点
优点:数据安全性
查询简单化
逻辑数据独立性
缺点:
A.性能。
SQL Server必须把视图的查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,即使是视图的一个简单查询,SQL Server也把它变成一个复杂的结合体,需要花费一定的时间。
B.修改限制。
当用户试图修改视图的某些行时,SQL Server必须把它转化为对基本表的某些行的修改。事实上,当从视图中插入或者删除时,情况也是这样。对于简单视图来说,这是很方便的,但是,对于比较复杂的视图,可能是不可修改的,这些视图有如下特征:
a.有UNIQUE等集合操作符的视图。
b.有GROUP BY子句的视图。
c.有诸如AVG\SUM\MAX等聚合函数的视图。
d.使用DISTINCT关键字的视图。
e.连接表的视图(其中有些例外) -
约束
有五种约束类型,分别是primary key(主键) 约束、foreign key(外键) 约束、unique约束、default约束、check约束
一个表可以定义多个unique约束 -
数据库优化
- 把数据、日志、索引放到不同的I/O设备上,增加读取速度,以前可以将Tempdb应放在RAID0上,SQL2000不在支持。数据量(尺寸)越大,提高I/O越重要。
- 纵向、横向分割表,减少表的尺寸(sp_spaceuse)。
- 升级硬件
- 根据查询条件,建立索引,优化索引,优化数据库的访问方式,限制结果集的数据量。
- 提高网速
- 扩大服务器内存
- 增加服务器CPU个数
- 在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据,浪费了服务器的I/O资源,加重了网络的负担降低性能。如果表很大,在表扫描的期间将表锁住,禁止其他的联接访问表,后果严重。
- 如果要插入大的二进制值到image列,使用存储过程,千万不能用内嵌的insert来插入。10. Between在某些时候比In速度更快,Between能够更快地根据索引找到范围。
- 尽量少用视图,因为他的效率低
- 在IN后面值的列表中,将出现最频繁的值放在最前面,出现最少的放在最后面,减少判读的次数
- 数据库运维方面的优化:启用数据库缓存。对于一些比较常用的查询可以采用数据库缓存的机制,部署的时候需要注意设置好缓存依赖项,防止“过期”数据的产生。
- 数据库索引方面的优化:比如常用的字段建索引,联合查询考虑联合索引。(PS:如果你有基础,可以敞开谈谈聚集索引和非聚集索引的使用场景和区别)
- 数据库查询方面的优化:避免select * 的写法、尽量不用in和not in 这种耗性能的用法等等。
- 数据库算法方面的优化:尽量避免大事务操作、减少循环算法,对于大数据量的操作,避免使用游标的用法等等。
- 锁的种类
分为独占锁、共享锁、更新锁
引入锁的原因:当多个用户同时对数据库的并发操作时会带来以下数据不一致的问题
丢失更新
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统
◆脏读
A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致
◆不可重复读
A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致
并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致
共享锁:用于不更改或不更新数据的操作(只读操作),如 SELECT 语句。
更新 锁用于更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
独占锁用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待
可以使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据,并且直到其它进程释放自己的锁时,每个进程才能释放自己的锁,即发生死锁情况。
什么是死锁?
死锁(Deadlock):是指两个或两个以上的进程在运行过程中,因争夺资源而造成的一种互相等待(谁也无法再继续推进)的现象,若无外力作用,它们都将无法推进下去。
死锁的四个必要条件:
互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用;
请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源;
非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺;
循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源;
处理死锁的策略:
1、忽略该问题。例如鸵鸟算法,该算法可以应用在极少发生死锁的的情况下。为什么叫鸵鸟算法呢,因为传说中鸵鸟看到危险就把头埋在地底下,可能鸵鸟觉得看不到危险也就没危险了吧,跟掩耳盗铃有点像;
2、检测死锁并且恢复;
3、仔细地对资源进行动态分配,以避免死锁;
4、通过破除死锁四个必要条件之一,来防止死锁产生; - SQL语句优化
1) 使用临时表或表变量存放中间结果(使用临时表加快查询)
2) 不要有超过5个以上的表连接(JOIN)
3) 少用子查询
4) 视图嵌套不要过深,一般视图嵌套不要超过两个为宜
5) 限制结果集(要尽量减少返回的结果行)
6) 索引优化(对出现在where子句中的字段加索引)
7) 使用存储过程
8) Where子句中的连接顺序
9) 用EXISTS 代替IN,NOT EXISTS 代替NOT IN,WHERE 查询语句优化,避免全表扫描
in/not in,or会引起全表扫描
10)采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回,UNION先取出两个表的结果,再用排序空间进行排序删除重复的记录,
如何处理几十万条并发数据?
用存储过程或事务。取得最大标识的时候同时更新。注意主键不是自增量方式这种方法并发的时候是不会有重复主键的,取得最大标识要有一个存储过程来获取; - Where 与having
Where子句用来筛选 from子句中指定的操作所产生的行
Group by 子句用来分组where子句的输出
Having子句用来分组where子句的输出
Select deparment,avg(salary) as average from salary_info
Group by deparment having average>3000 - NoSql
NoSQL=not only sql,泛指非关系型数据库
分为四大类:
键值(key-value)存储数据库Dynamo
列存储数据库
文档型数据库mogodb
图形数据库
NoSql比较适用以下几种情况:1)数据模型比较简单2)需要灵活性更强的IT系统3)对数据库性能要求较高4)不需要高度的数据一致性5)对于指定的key,比较容易映射复杂值的环境
分布式领域CAP理论
Consistency(一致性), 数据一致更新,所有数据变动都是同步的
Availability(可用性), 好的响应性能
Partition tolerance(分区容忍性) 可靠性 - 事务的特征(ACID)
原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
原子性是指事务包含的所有操作要么全部成功,要么全部失败
一致性是指一个事务执行之前和执行之后都必须处于一致性状态(拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。)
隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。
事务的隔离级别
脏读Read uncommitted 在一个事务处理过程里读取了另一个未提交的事务中的数据
不可重复读 Read committed指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了
幻读Repeatable read 事务开启,不允许其他事务的UPDATE修改操作,但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作,而不是UPDATE操作。
序列化 Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待
① Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
② Repeatable read (可重复读):可避免脏读、不可重复读的发生。
③ Read committed (读已提交):可避免脏读的发生。
④ Read uncommitted (读未提交):最低级别,任何情况都无法保证。
持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。 - Char,varchar,nvarchar的区别
如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar
char是定长的,输入字符小于你制定的数目时,后面补空值。大于制定数目,会截断
Varchar(n)
长度为n个字节的可变长度且非Unicode的字符数据,n必须是一个介于1和8000之间的数值。存储大小为输入数据的字节的实际长度,而不是n个字节
Nvarchar(n)
包含n个字符的可变长度Unicode字符数据。N必须介于1到4000之间。字符的存储大小是所输入字符个数的两倍。
我和coffee,varchar字段是22+6=10个字节的存储空间
Nvarchar是82=16个字节的存储空间 - 触发器的作用?
触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如,某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发。 - 高并发的时候,发现数据库有个存储过程执行时间过长,这个时候怎么办?
先定位问题,看下执行计划,到底哪句慢。然后针对性的解决:是不是索引没用到?sql 写法有问题?甚至可以重新设计表结构增加查询速度