设为首页收藏本站我的广告

运维网

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
运维网 首页 数据库运维 SQL Server 查看内容

浅谈SQL基本概念及其用法

2012-4-27 13:37| 发布者: yunweiw.com| 查看: 902| 评论: 0|原作者: 运维网|来自: 网络

  这两天学习了SQL与ADO.NET,简单总结下……

  一、数据库概述:1、DBMS(DataBaseManagementSystem,数据库管理系统)和数据库。平时谈到“数据库”可能有两种含义:MSSQLServer、Oracle等某种DBMS;存放一堆数据表的一个分类(Catalog)

  2、数据库的构成-管理软件/服务/数据文件(表,视图……)

  3、不同品牌的DBMS有自己的不同的特点:MYSQL、MSSQLServer、DB2、Oracle、Access、Sybase等。对于开发人员来讲,大同小异4、SQL的全称是:Structured Query Language(结构化查询语言)SQL<>SQLServer<>MSSQLServer.最常见的错误。

  5、除了Access、SQLServerCE等文件型数据库之外,大部分数据库都需要数据库服务器才能运行。学习\开发时是连接本机的数据库,上线运行时是数据库运行在单独的服务器。

  二、数据库开中的概念:1、Catalog(分类)(又叫数据库DataBase,表空间TableSpace),不同类的数据应该放到不同的数据库中1)便于对各个Catalog进行个性化管理2)避免命名冲突3安全性更高2、Table(表):书都放到书架上,碗都放到橱柜中,不同类型的资料放到不同的“格子”中,将这种区域叫做“表”(Table)。不同的表根据放的数据不同进行空间的优化,找起来也方便。

  3、列(Column)、字段(Field)要概念4、Primary(主键):1)主键就是一个表中每个数据行的唯一标识。不会有重复值的列才能当主键。一个表可以没有主键,但是会非常难以处理,因此没有特殊理由表都要设定主键2)主键有两种选用策略:业务主键和逻辑主键。业务主键是使用有业务意义的字段做主键,比如身份证号、银行账号等;逻辑主键是使用没有任何业务意义的字段做主键,完全给程序看的,业务人员不会看的数据。因为很难保证业务主键不会重复(身份证号重复)、不会变化(帐号升位),因此推荐用逻辑主键。

  三、数据库的管理:1、需要安装SQLServer2005或者SQLServer2008,若要使用SQLServer管理工具进行开发还要安装SQL Server Management Studio,还可以使用VisualStudio进行管理2、使用免费的SQLServerExpress版本,Express版本的服务器名称。 \SQLEXPRESS,对于开发人员来讲和其他版本没有区别。

  3、SQLServer的两种验证方式:用户名验证和Windows验证,开发时用Windows验证就行。

  4、开发人员关注点在开发上,而不是配置/备份等之上,那是DBA做的事情。

  创建数据库,创建表,设置主键5、SQLServer2008中:编辑200行;SQLServer2005中:打开表。

  6、常用字段类型:bit(可选值0、1)、datetime、int、varchar、nvarchar(可能含有中文用nvarchar)

  7、Nvarchar(50)、Nvarchar(MAX)

  8、varchar、nvarchar 和char(n)的区别: char(n)不足长度n的部分用空格填充。Var:Variable,可变的。

  四、SQL语句入门:1、SQL语句是和DBMS“交谈”专用的语句,不同DBMS都认SQL语法2、SQL语句中字符串用单引号。

  3、SQL语句是大小写不敏感的,不敏感指的是SQL关键字,字符串值还是大小写敏感的4、创建表、删除表不仅可以手工完成,还可以执行SQL语句完成,在自动化部署、数据导入中用的很多,CREATE TABLE T_Person(Id int NOT NULL,Name nvarchar(50),Age int NULL)、Drop table T_Person1 5、简单的Insert语句。INSERT INTO T_Person(Id,Name,Age) VALUES(1,'Jim',20)

  6、(*) SQL主要分DDL(数据定义语言)和DML(数据操作语言)两类。Create Table、Drop Table、Alter Table等属于DDL, Select、Insert、Update、Delete等属于DML. 7、一个SQL查询例子:(条件查询)

  表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。

  select(case when A>B then A else B end),(case when B>C then B else C end ) from T

  五、主键的选择:(Globally Unique Identifier(叫全球唯一标识符))

  1、SQLServer中两种常用的主键数据类型:int(或bigint)+标识列(又称自动增长字段);uniqueidentifier(又称Guid、UUID)

  2、用标识列实现字段自增可以避免并发等问题,不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值。将字段的“是标识列”设置为“是”,一个表只能有一个标识列。

  3、Guid算法是一种可以产生唯一标识的高效算法,它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的,这样保证每次生成的GUID永远不会重复,无论是同一个计算机上还是不同的计算机。在公元3400年以前产生的GUID与任何其他产生过的GUID都不相同。SQLServer中生成GUID的函数newid(),。Net中生成Guid的方法:Guid.NewGuid(),返回是Guid类型。

  4、(*)Int自增字段的优点:占用空间小、无需开发人员干预、易读;缺点:效率低;数据导入导出的时候很痛苦。

  5、(*)Guid的优点:效率高、数据导入导出方便;缺点占用空间大、不易读。

  6、业界主流倾向于使用Guid.六、新增(插入)数据:Inert into Table(col,col2……)values(data1,data2……)

  1、Insert语句可以省略表名后的列名,但是不推荐2、如果插入的行中有些字段的值不确定,那么Insert的时候不指定那些列即可。

  3、给可以给字段默认值,如果Guid类型主键的默认值设定为newid()就会自动生成,很少这么干4、主键:insert into Person3(Name,Age) values('lily',38);insert into Person4(Id,Name,Age) values(newid(),'tom',30);例如:分析以下几句,看懂了你就会用insert into了。

  1)INSERT INTO Book (b_title,b_cid,b_Content,b_author,b_isdel,b_addtime)

  VALUES ('传智播客',5, '严谨负责', '家宝',0,getdate())

  2)INSERT INTO Book VALUES (1, '传智播客', '严谨负责', '家宝',0,getdate())

  3)INSERT INTO Book (b_title,b_Content,b_author,b_isdel,b_addtime)

  VALUES ('传智播客', '严谨负责')

  4)INSERT INTO Book (b_cid,b_title,b_Content,b_author,b_isdel,b_addtime)

  VALUES (2,'传智播客', '严谨负责', '家宝', '删除了',getdate())

  5)INSERT INTO Book (b_cid,b_title,b_Content,b_author,b_isdel,b_addtime)

  VALUES (1,'传智播客', '严谨负责', '家宝',0,getdate())

  6)INSERT INTO Book (b_cid,b_title,b_isdel,b_addtime)

  VALUES (1,'传智播客',0,getdate())

  7)INSERT INTO Book (b_cid,b_title,b_Content,b_author,b_isdel,b_addtime)

  VALUES (1, '有美女', '严谨负责', '家宝',0,getdate())

  8)INSERT INTO Book (b_cid,b_title,b_Content,b_author,b_isdel,b_addtime)

  VALUES (1,'传智播客', '严谨负责', '家宝', DEFAULT,getdate())

  七、更新数据: update Table set col=value,col2=value2 where ……

  1、更新一个列:UPDATE T_Person Set Age=30 2、更新多个列:UPDATE T_Person Set Age=30,Name=‘tom’3、更新一部分数据: UPDATE T_Person Set Age=30 where Name=‘tom’,用where语句表示只更新Name是‘tom’的行,注意SQL中等于判断用单个=,而不是== 4、Where中还可以使用复杂的逻辑判断UPDATE T_Person Set Age=30 where Name=‘tom’ or Age<25,or相当于C#中的||(或者)

  5、update Person1 set NickName=N'二十岁' 6、where (Age>20 and Age<30) or(Age=80)

  7、Where中可以使用的其他逻辑运算符:or、and、not、<、>、>=、<=、!=(或<>)等

  八、删除数据:delete [from] Table where col=data 1、删除表中全部数据:DELETE FROM T_Person. 2、Delete只是删除数据,表还在,和Drop Table不同。

  3、Delete 也可以带where子句来删除一部分数据:DELETE FROM T_Person WHERE FAge > 20

  九、数据检索: select col,col2[,*] from Table where ……

  1、执行备注中的代码创建测试数据表。

  2、简单的数据检索 :SELECT * FROM T_Employee 3、只检索需要的列 :SELECT FNumber FROM T_Employee 、SELECT FName,FAge FROM T_Employee 4、列别名:SELECT FNumber AS 编号,FName AS 姓名,FAge AS Age111 FROM T_Employee 5、使用where检索符合条件的数据:SELECT FName FROM T_Employee WHERE FSalary<5000. 6、还可以检索不与任何表关联的数据:select 1+1;select newid();select getdate();

  十、数据汇总(聚合函数)

  1、SQL聚合函数:MAX(最大值)、MIN(最小值)、AVG (平均值)、SUM (和)、COUNT(数量)

  2、大于25岁的员工的最高工资 :SELECT MAX(FSalary) FROM T_Employee WHERE FAge>25 3、最低工资和最高工资:SELECT MIN(FSalary),MAX(FSalary) FROM T_Employee十一、数据排序:1、ORDER BY子句位于SELECT语句的末尾,它允许指定按照一个列或者多个列进行排序,还可以指定排序方式是升序(从小到大排列,ASC)还是降序(从大到小排列,DESC)。

  2、按照年龄升序排序所有员工信息的列表:SELECT * FROM T_Employee ORDER BY FAge ASC 3、按照年龄从大到小排序,如果年龄相同则按照工资从大到小排序 :SELECT * FROM T_Employee ORDER BY FAge DESC,FSalary DESC(多个排序条件)

  4、ORDER BY子句要放到WHERE子句之后 :SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC十二、通配符过滤:1、通配符过滤关键字使用LIKE . 2、单字符匹配的通配符为半角下划线“_”,它匹配单个出现的字符。

  eg:以任意字符开头,剩余部分为“erry”

  SELECT * FROM T_Employee WHERE FName LIKE '_erry' 3、多字符匹配的通配符为半角百分号“%”,它匹配任意次数(零或多个)出现的任意字符。 “k%”匹配以“k”开头、任意长度的字符串eg:检索姓名中包含字母“n”的员工信息SELECT * FROM T_Employee WHERE FName LIKE '%n%'.

  十三、空值处理:1、数据库中,一个列如果没有指定值,那么值就为null,这个null和C#中的null,数据库中的null表示“不知道”,而不是表示没有。

  2、因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”。

  3、SELECT * FROM T_Employee WHERE FNAME=null ; SELECT * FROM T_Employee WHERE FNAME!=null ;都没有任何返回结果,因为数据库也“不知道”。

  4、SQL中使用is null、is not null来进行空值判断:5、SELECT * FROM T_Employee WHERE FNAME is null ; SELECT * FROM T_Employee WHERE FNAME is not null ;十四、数据分组:1、按照年龄进行分组统计各个年龄段的人数:SELECT FAge,Count(*) FROM T_Employee GROUP BY Fage 2、GROUP BY子句必须放到WHERE语句的之后3、没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)

  错误:SELECT FAge,FSalary FROM T_Employee GROUP BY FAge正确:SELECT FAge,AVG(FSalary) FROM T_Employee GROUP BY FAge十五、 Having语句(分组后查询条件)

  1、在Where中不能使用聚合函数,必须使用Having,Having要位于Group By之后:SELECT FAge,COUNT(*) AS 人数 FROM T_Employee GROUP BY FAge HAVING COUNT(*)>1 2、注意Having中不能使用未参与分组的列,Having不能替代where.作用不一样,Having是对组进行过滤。

  3、顺序:先where,后group by,再having.十六、限制结果集行数1、SELECT top 5 * FROM T_Employee order by FSalary Desc 2、(*)检索按照工资从高到低排序检索从第六名开始一共三个人的信息 :SELECT top 3 * FROM T_Employee WHERE FNumber NOT IN (SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)

  ORDER BY FSalary DESC 3、SQLServer2005后增加了Row_Number函数简化实现,后面会讲。

  十七、DISTINCT:去掉数据重复1、执行备注中的SQL语句,Alter和Insert单独执行SELECT FDepartment FROM T_Employee SELECT DISTINCT FDepartment FROM T_Employee

  2、DISTINCT是对整个结果集进行数据重复处理的,而不是针对每一个列,因此下面的语句并不会只保留Fdepartment进行重复值处理:SELECT DISTINCT FDepartment,FSubCompany FROM T_Employee十八、UNION:联合结果集1、执行备注中的代码2、简单的结果集联合:3、SELECT FNumber,FName,FAge FROM T_Employee UNION SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee 4、基本的原则:每个结果集必须有相同的列数;每个结果集的列必须类型相容。

  SELECT FNumber,FName,FAge,FDepartment FROM T_Employee UNION SELECT FIdCardNumber,FName,FAge,‘临时工,无部门' FROM T_TempEmployee十九、UNION ALL SELECT FName FROM T_Employee UNION SELECT FName FROM T_TempEmployee 1.UNION合并两个查询结果集,并且将其中完全重复的数据行合并为一条SELECT FName FROM T_Employee UNION ALL SELECT FName FROM T_TempEmployee 2.Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNION ALL案例:1)要求查询员工的最低年龄和最高年龄,临时工和正式员工要分别查询SELECT '正式员工最高年龄',MAX(FAge) FROM T_Employee UNION ALL SELECT '正式员工最低年龄',MIN(FAge) FROM T_Employee UNION ALL SELECT '临时工最高年龄',MAX(FAge) FROM T_TempEmployee UNION ALL SELECT '临时工最低年龄',MIN(FAge) FROM T_TempEmployee UNION ALL 2)查询每位正式员工的信息,包括工号、工资,并且在最后一行加上所有员工工资额合计。

  SELECT FNumber,FSalary FROM T_Employee UNION ALL SELECT '工资合计',SUM(FSalary) FROM T_Employee二十、数字函数:执行备注中的代码:1、ABS() :求绝对值。

  2、CEILING():舍入到最大整数 .3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3. Ceiling→天花板3、FLOOR():舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4. Floor→地板。

  4、ROUND():四舍五入。舍入到“离我半径最近的数” .Round→“半径”。Round(3.1425,2)。

  二十一、字符串函数:1、LEN() :计算字符串长度2、LOWER() 、UPPER () :转小写、大写3、LTRIM():字符串左侧的空格去掉4、RTRIM () :字符串右侧的空格去掉LTRIM(RTRIM(' bb '))

  5、SUBSTRING(string,start_position,length)

  参数string为主字符串,start_position为子字符串在主字符串中的起始位置,length为子字符串的最大长度。

  SELECT SUBSTRING('abcdef111',2,3)

  二十二、日期函数:1、GETDATE() :取得当前日期时间2、DATEADD (datepart , number, date ),计算增加以后的日期。参数date为待计算的日期;参数number为增量;参数datepart为计量单位,可选值见备注。DATEADD(DAY, 3,date)为计算日期date3天后的日期,而DATEADD(MONTH ,-8,date)为计算日期date8个月之前的日期3、DATEDIFF ( datepart , startdate , enddate ) :计算两个日期之间的差额。 datepart 为计量单位,可取值参考DateAdd.统计不同工龄的员工的个数:select DateDiff(year,FInDate,getdate()),count(*) from T_Employee group by DateDiff(year,FInDate,getdate())

  4、DATEPART (datepart,date):返回一个日期的特定部分统计员工的入职年份个数:select DatePart(year,FInDate),count(*) from T_Employee group by DatePart(year,FInDate)

  二十三、类型转换函数:1、CAST ( expression AS data_type)

  2、CONVERT ( data_type, expression)

  3、SELECT FIdNumber,RIGHT(FIdNumber,3) as 后三位,CAST(RIGHT(FIdNumber,3) AS INTEGER) as 后三位的整数形式,CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 as 后三位加1,CONVERT(INTEGER,RIGHT(FIdNumber,3))/2 as 后三位除以2 FROM T_Person二十四:空值处理函数:1、执行备注中的代码2、ISNULL(expression,value) :如果expression不为空则返回expression,否则返回value SELECT ISNULL(FName,'佚名') as 姓名 FROM T_Employee二十五:CASE函数用法:1、单值判断,相当于switch case CASE expression WHEN value1 THEN returnvalue1 WHEN value2 THEN returnvalue2 WHEN value3 THEN returnvalue3 ELSE defaultreturnvalue END 2、例子:SELECT FName,(CASE FLevel WHEN 1 THEN 'VIP客户' WHEN 2 THEN '高级客户' WHEN 3 THEN '普通客户' ELSE '客户类型错误' END) as FLevelName FROM T_Customer 3、测试数据在备注中CASE WHEN condition1 THEN returnvalue1 WHEN condition 2 THEN returnvalue2 WHEN condition 3 THEN returnvalue3 ELSE defaultreturnvalue END相当于if…else…else…。

  SELECT FName, FWeight,(CASE WHEN FWeight<40 THEN ‘瘦瘦' WHEN FWeight>50 THEN ’肥肥' ELSE 'ok' END) as isnormal FROM T_Person二十六、索引Index:1、全表扫描:对数据进行检索(select)效率最差的是全表扫描,就是一条条的找。

  2、如果没有目录,查汉语字典就要一页页的翻,而有了目录只要查询目录即可。为了提高检索的速度,可以为经常进行检索的列添加索引,相当于创建目录。

  3、创建索引的方式,在表设计器中点击右键,选择“索引/键”→添加→在列中选择索引包含的列。

  4、使用索引能提高查询效率,但是索引也是占据空间的,而且添加、更新、删除数据的时候也需要同步更新索引,因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。

  5、(*)即使创建了索引,仍然有可能全表扫描,比如like、函数、类型转换等。

  二十七、表连接Join:1、有客户表(T_Customers)和订单表(T_Orders)两个表,客户表字段为:Id、Name、Age,订单表字段为:Id、BillNo、CustomerId,订单表通过CustomerId关联客户表。测试数据见备注。

  2、SELECT o.BillNo,c.Name,c.Age from T_Orders as o 3、JOIN T_Customers as c on o.CustomerId=c.Id 4、join是和哪个表连接,on后是连接的关系是什么。(多表)

  5、要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄。

  6、要求显示年龄大于平均年龄的顾客购买的订单7、Inner Join、Left Join、Right Join二十八:子查询:1、将一个查询语句做为一个结果集供其他SQL语句使用,就像使用普通的表一样,被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。SELECT * FROM(SELECT * FROM T2 where FAge<30)

  2、执行备注中的SQL. 3、单值做为子查询:SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished) FROM T_Book) AS f4 4、只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。下面的是错误的:SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book)

  5、SELECT * FROM T_ReaderFavorite WHERE FCategoryId=(SELECT FId FROM T_Category WHERE FName='Story')

  实例:子查询的两种特殊情况:如果子查询是多行单列的子查询,这样的子查询的结果集其实是一个集合。

  SELECT * FROM T_Reader WHERE FYear Of Join IN(select FYearPublished FROM T_Book)

  限制结果集。返回第3行到第5行的数据(ROW_NUMBER 不能用在where子句中,所以将带行号的执行结果作为子查询,就可以将结果当成表一样用了):SELECT * FROM(SELECT ROW_NUMBER()OVER(ORDER BY FSalary DESC) AS rownum,FNumber,FName,FSalary,FAge FROM T_Employee) AS a WHERE a.rownum>=3 AND a.rownum<=5

下一篇:SQL Server:查询时将空字段排在下面

上一篇:SQL Server数据库占用过多内存


鲜花

握手

雷人

路过

鸡蛋
加入阿里云推荐返利15%

最新评论

QQ|申请友链|sitemap|手机版|小黑屋|Archiver|运维网 ( 京ICP备16008201号  

GMT+8, 2017-6-29 20:31 , Processed in 0.087421 second(s), 32 queries , Xcache On.

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

返回顶部