1、如果想生成带数据的sql脚本 任务------生成脚本------要设置“框架与数据” 如果想兼容低版本 在“高级”那里设置
2、函数名是允许作为字段名 如果非得这么做 建议在t-sql语句中加上[ ]以示区别
3、新增的时候 如果给指定的列 那么其他列要么允许为null’ 要么有默认值 否则会报错
4、如果使用默认值就写default 如果想插入NULL 就写NULL
5、清空表中的数据 Delete form 表名 或者 Delete 表名
6、清空表中的数据并且重置自动增长列truncate table 表名
7、数据库中字符串用单引号引起来
8、*代表多有的字段 一般情况下不建议写* *会造成两次查询(Master 会先到系统数据库中查询指定的表中有哪些列,然后根据这些列到指定的表中查询)
9、工作数据库表示执行的sql语句作用与那个数据库 切换数据库 USE 数据库名称
10、结果集 执行一条查询语句的结果
11、给结果集的列取别名 as 别名 甚至不用as关键(不建议)
12、select 还可以执行与表无关的查询 列出常量的值 执行系统函数 一些运算
13、顺序 ASC 降序 DESC
Top、Distinct
l Top 获取前几条数据,top一般都与order by连用
- 获得年纪最小的5个学生 select top 5 * from 表名
- 获得年纪最大的10%的学生select top 5 percent * from 表名
l Distinct 去除重复数据
- select distinct sName from student
- select distinct sName,sAge from student//多条件去处(根据sName和sAge来判断是否重复)
l Select top 5 sName,sAge from student
l Select top 30 percent sName,sAge from student 非四舍五入,返回最大整数 2.1 返回3
l Distinct 后面不能有任何列名
聚合函数
l SQL聚合函数:
l MAX(最大值)、MIN(最小值)、AVG (平均值)、SUM (和)、COUNT(数量:记录的条数。)是在结果集中查找
l 聚合函数对null值不计算。
l 如果一行的数据都是null,count(*)包含对空值行、重复行的统计。
l 平均成绩select avg(english) from score
l 男学生出生日期的最大值和最小值:select max(sBirthday),min(sBirthday) from student where sSex='男'
l 如果Count函数跟的列名如果每位null 就不统计,count 函数要么跟一个列要么* 不能跟多个列
l Max 、Min如果汉字或者英文单词 根据字母或者汉字的拼音来判断
类型转换:
CAST ( expression AS data_type [ (length ) ])
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
select count(*) from student;
select max(english) from score;
select min(english) from score;
select avg(english) from score
select sum(english) from score;
select count(*) from student
where sSex=‘女’;
COUNT(*) 返回组中的项数。包括 NULL 值和重复项。
COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。
COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。
对于大于 2^31-1 的返回值,COUNT 生成一个错误。这时应使用 COUNT_BIG。
案例:
USE AdventureWorks2008R2; GO SELECT COUNT(DISTINCT JobTitle) FROM HumanResources.Employee; GO
带条件的查询
l Select …from…where …
- 查询没有及格的学生的学号
- 查询年龄在20-30岁之间的男学生
l Between…and …在之间
- 查询年龄在20-30岁之间的男学生
- 查询math成绩在80-90分之间的所有学生
- 建议:优先使用between … and …,而不是“列>=值1 and 列<=值2 ”,between … and …已做过优化处理,效率高。
l 查询班级id为1,2,3的所有学生
- select sName,sAge from student where sClassId=1 or sClassId=2 or sClassId=3
- select sName,sAge from student where sClassId in (1,2,3)
带条件的查询-模糊查询(都是针对字符串操作的)
l 查询所有姓张的同学
- Select * from student where left(sName,1)=‘张‘ 看上去很美,如果改成查询名字中带亮的学生怎么做?
l 换一种做法 like
- Select * from student where sName like ‘张%’ 会吧所有姓张的都查询到,现在我想查询姓张并且名字是一个字的学生?
- Select * from student where sName like ‘%亮%’
l _ 、 % 、 [] 、 ^
不管是什么数据类型 like 后面的都要写字符串可以查询年龄在10 到29的学生
Select * from student where Age like ‘ [1-2][0-9]’
l ^只有MSSQL Server支持,其他DBMS用not like。取反 名字第一个不是以a-z开头的Select * from student where sName like ‘ [^a-z]%’ Select * from student where sName not like ‘ [a-z]%’ 尽量用not like
l 查询姓名字中有梅字的学生信息 ‘ %梅%’
l 通配符 %多字符匹配的通配符,它匹配任意次数(零或多个)出现的任意字符
l 通配符_ 单字符匹配,它匹配单个出现的字符 Select * from student where sName like ‘张_’查询姓张的人并且名字只有两个字的学生信息
l [] 只匹配一个字符 并且这个字符必须是[]范围内的 代表一段范围 [0-9] [a-z] 查询地一个字是a-z中的任意字符 Select * from student where sName like ‘[a-z]%’
l not与like一起使用:not like ….
l 要通配_、%、[、^这些字符怎么办?[_]、[%]、[ [ ]、^(要放到中括号里,因为^只有放到中括号中才认为是通配符)
%
[]
^
[^ab]只有ms sqlserver支持,对于其他数据库,不支持。
要想通配一个_怎么办?
[_]
LIKE 关键字搜索与指定模式匹配的字符串、日期或时间值。有关详细信息,请参阅数据类型 (Transact-SQL)。LIKE 关键字使用常规表达式包含值所要匹配的模式。模式包含要搜索的字符串,字符串中可包含四种通配符的任意组合。
通配符 含义 %包含零个或多个字符的任意字符串。_任何单个字符。[ ]指定范围(例如 [a-f])或集合(例如 [abcdef])内的任何单个字符。[^]不在指定范围(例如 [^a - f])或集合(例如 [^abcdef])内的任何单个字符。请将通配符和字符串用单引号引起来,例如:
此查询将查找 Contact 表中区号为 415 的所有电话号码:
复制代码SELECT Phone FROM AdventureWorks.Person.Contact WHERE Phone LIKE '415%'
可以将 NOT LIKE 与同样的通配符结合使用。若要查找 Contact 表中区号不是 415 的所有电话号码,请使用下列等价查询中的任意一个:
复制代码SELECT Phone FROM AdventureWorks.Person.Contact WHERE Phone NOT LIKE '415%' -- Or SELECT Phone FROM AdventureWorks.Person.Contact WHERE NOT Phone LIKE '415%'
IS NOT NULL 子句可与通配符和 LIKE 子句结合使用。例如,此查询将从 Contact 表中检索以 415 开头且 IS NOT NULL 的所有电话号码:
复制代码USE AdventureWorks; GO SELECT Phone FROM Person.Contact WHERE Phone LIKE '415%' and Phone IS NOT NULL
重要事项: 包含 LIKE 关键字的语句的输出取决于安装过程中选择的排序顺序。有关不同的排序顺序所产生的影响的详细信息,请参阅使用排序规则。
可以在 text 列中使用的唯一 WHERE 条件是 LIKE、IS NULL 或 PATINDEX。
不与 LIKE 一同使用的通配符将解释为常量而非模式,换言之,这些通配符仅代表其本身的值。以下查询试图查找只由四个字符 415% 组成的电话号码。该查询将不会查找以 415 开头的电话号码。有关常量的详细信息,请参阅常量(数据库引擎)。
复制代码SELECT Phone FROM AdventureWorks.Person.Contact WHERE Phone = '415%'
使用通配符时应着重考虑的另一个问题是对性能的影响。如果表达式以通配符开头,就不能使用索引(就如同给定了姓名 "%mith" 而非 "Smith" 时,将无法知道应从电话簿的哪一页开始查找)。表达式中间或结尾处的通配符不妨碍索引的使用(就如同在电话簿中一样,如果姓名为 "Samuel%",则不论 Samuels 和 Samuelson 是否都在电话簿上,都知道该从何处开始查找。)
搜索通配符字符
可以搜索通配符字符。有两种方法可指定平常用作通配符的字符:
如果使用 LIKE 执行字符串比较,模式串中的所有字符(包括每个前导空格和尾随空格)都有意义。如果要求比较返回带有字符串 LIKE 'abc '(abc 后跟一个空格)的所有行,将不会返回列值为 abc(abc 后没有空格)的行。但是反过来,情况并非如此。可以忽略模式所要匹配的表达式中的尾随空格。如果要求比较返回带有字符串 LIKE 'abc'(abc 后没有空格)的所有行,将返回以 abc 开头且具有零个或多个尾随空格的所有行。
空值处理
l 数据库中,一个列如果没有指定值,那么值就为null,数据库中的null表示“不知道”,而不是表示没有。因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”。
l 允许某个字段的值为null代表这个字段的值可以不给 与空的串是两码事
l select * from score where english = null ;
l select * from score where english != null ;都没有任何返回结果,因为数据库也“不知道”。
l SQL中使用is null、is not null来进行空值判断: select * from score where english is null ; select * from score where english is not null ;
l ISNULL ( check_expression , replacement_value )如果check_expression的值为空 那就返回replacement_value的值
数据排序
l ORDER BY子句位于SELECT语句的末尾,它允许指定按照一个列或者多个列进行排序,还可以指定排序方式是升序(从小到大排列,ASC)还是降序(从大到小排列,DESC)。
l 按照年龄升序排序所有学生信息的列表:SELECT * FROM Student ORDER BY sAge ASC
l 按照英语成绩从大到小排序,如果英语成绩相同则按照数学成绩从大到小排序 :SELECT * FROM Score ORDER BY english DESC,math DESC
l ORDER BY子句要放到WHERE子句之后 : SELECT * FROM Score where english>=60 and math>=60 ORDER BY english DESC,math DESC 语句的末尾
l Order by 语句一般要放到所有语句的后面,就是先让其他语句进行筛选,全部筛选完成后,最后排序一下。
l 表中数据是集合,集合是没有顺序的。Order by 返回的数据是有顺序的,故此我们把order by 以后返回的数据集合叫“游标”。
l 默认是升序(物理顺序)
l 如果排序的字段是一样的 那么就会根据物理排序来排序(升序)、
l 多条件排序 先根据年龄来排序 如果年龄一样 再根据ID来排序 Score ORDER BY Age,ID DESC Score ORDER BY Age DESC,ID DESC//要为每个排序字段指定排序规则
数据分组
l 在使用select查询的时候,有时需要对数据进行分组汇总(即:将现有的数据按照某列来汇总统计),这时就需要用到group by语句。select 语句中可以使用group by 子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息。//分组一般都和聚合函数连用。
l 分组之后 只能看到组的信息 看不到个体信息 分组之后在select 语句的选择列里面,只能包含group by 语句中出现的列,或者是聚合函数,不能包括其他列。
多条件分组 几个条件都一样才在1个组 select count(*) as mycount,Age,Cid from Student group by Age,Cid order by mycount desc
l
l 1.请从学生表中查询出每个班的班级Id和班级人数:(见备注1)
l 2 .请从学生表中查询出每个班的班级Id和班级中男同学的人数: (见备注2)
l GROUP BY子句必须放到WHERE语句的之后 ,Group By与Order By都是对筛选后的数据进行处理,而Where是用来筛选数据的。
l 没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)
- 错误: select sClassId,count(sName),sAge from student group by sClassId
- 正确: select sClassId,count(sName),avg(sAge) from student group by sClassId
备注1:
select
tSClassId as班级Id,
count(*)as班级人数
from TblStudent
groupby TSClassId
备注2:
select
tSClassId as班级Id,
count(*)as男同学人数
from TblStudent
where tSGender='男'
groupby TSClassId
可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组,并返回行的汇总信息. 按照group by 子句中指定的表达式的值分组查询结果。
--当在一个查询语句中使用group by语句的时候,
--在select 语句的选择列里面,只能包含group by 语句中出现的列,或者是聚合函数,
--不能包括其他列。
Having语句
l 对表中的数据分组后,会得到一个分组后的结果集,如何对该结果集在进行筛选?→ having
l 查询班级人数超过三个人的班级。(见备注1)
l 注意Having中不能使用未参与分组的列,Having不能替代where。作用不一样,Having是对组进行过滤。
l Having 是Group By的条件对分组后的数据进行筛选(与Where类似,都是筛选,只不过having是用来筛选分组后的组的。)
l 在Where中不能使用聚合函数,必须使用Having,Having要位于Group By之后。
l Having的使用几乎是与where一样的,也可以用in。
- Having count(*) in (5,8,10)
备注1:
select
tSClassId as班级Id,
count(*)as班级人数
from TblStudent
groupby TSClassId
havingcount(*)>3
select sClassId,count(sName) from student where count(sName)>3 group by sClassId
聚合函数不应出现在WHERE 子句中
select sClassId,count(sName) from student group by sClassId having count(sName)>3
Group by 前可以有where,是对筛选过后的数据进行分组
select sClassId,count(sName) from student where sSex='男' group by sClassId
select sClassId,count(sName) from student group by sClassId having sAge>30
//错,having是对分组后信息的过滤,能用的列和select中能用的列是一样。
//having无法代替where。
//having 是用来筛选组信息 where筛选行的
Having只能去筛选组的聚合信息 个体信息无法筛选 可以用用between 、in等
SQL语句的执行顺序(重点)
l 1>…From 表
l 2>…Where 条件(行)
l 3>…Group by 列
l 4>…Having 对组来做筛选条件
l 5>…Select 5-1>选择列,5-2>distinct,5-3>top(才查找)
l 6>…Order by 列
先找到表From 再从表中过滤列 最后排序
技巧
调试:把语句分割成几行语句 再点击错误提示就可以知道那行出错
可以直接在列的字段拖进SQL语句里面