SQL Server 学习笔记.docx
- 文档编号:10005899
- 上传时间:2023-02-07
- 格式:DOCX
- 页数:39
- 大小:30.56KB
SQL Server 学习笔记.docx
《SQL Server 学习笔记.docx》由会员分享,可在线阅读,更多相关《SQL Server 学习笔记.docx(39页珍藏版)》请在冰豆网上搜索。
SQLServer学习笔记
SQLServer2005
新建数据库
数据库文件有两个:
一个是.mdf数据文件,一个是.LDF日志文件。
删除数据库
附加和分离数据库
分离:
右击数据库-任务-分离-
scott.mdfscott.ldf
附加:
右击-附加
概念:
字段、属性、列、元组、记录、表、主键、外键、表
列是字段的另一种称谓;元组是记录的另一种称谓。
一、通过SQL命令建表
创建表:
——先建主键表,再建外键表
createtabledept
(
dept_idintprimarykey,
dept_namenvarchar(100)notnull,
dept_addressnvarchar(100)
)
createtableemp
(
emp_idintconstraintpk_id_hahaprimarykey,
emp_namenvarchar(20)notnull,
emp_sexnchar
(1),
dept_idintconstraintfk_id_heheforeignkeyreferencesondept(dept_id)
)
注:
注释用“--“
对数据库表操作的三个环节:
①创建表:
createtablestudent
(
namevarchar(20)null,
ageint
)
②插入数据:
insertintostudentvalues(‘张三’,38);
insertintostudentvalues(‘Tom’,46);
insertintostudentvalues(‘a_b’,22);
insertintostudentvalues(‘c%d’,45);
insertintostudentvalues(‘abc_fe’,56);
insertintostudentvalues(‘haobin’,25);
insertintostudentvalues(‘HaoBin’,58);
insertintostudentvalues(‘c%’,33);
insertintostudentvalues(‘long”s’,29);
③查询:
select*fromstudent;
对表的操作大致就这三个环节吧。
nvarchar——n表示国际化编码,支持汉字;var是变量,char是字符;括号中的数字是字符的长度。
constraint----约束、限制的意思
定义主键:
primarykey;
定义外键:
foreignkey。
最后一个命令后可以要逗号,也可以不写,但为了和Oracle兼容,最好不写。
constraintpk_id_haha意思是指定主键的名字,可以指定主键的名字,如果不指定,则系统自动指定。
外键也是一样:
constraintfk_id_hehe
二、约束:
主键约束、外键约束
check约束【保证事物属性的取值在合法的范围之内】
createtablestudent
(
stu_idintprimarykey,
stu_salintcheck(stu_sal>=1000andstu_sal<=8000)
)
insertintostudent(stu_id,stu_sal)values(1,1000)
insertintostudentvalues(2,6000,‘女’)
(插入数据:
insert)
default约束【设置默认值】
stu_sexnchar
(1)default(‘男’)--此语句放在上面表中
--括号可以省略,字符串必须用单引号括起来。
【此语句属于student表中的字段】
--单引号——字符串
--双引号——数据的名字
--语句后用分号,而不是逗号
唯一约束【保证属性值不重复,但允许其中一个为空值】
stu_namenvarchar(200)unique【属于student表中的字段】
保证了事物属性的取值不允许重复,但允许其中一列为空(只允许一列为空)
SQLServer2005只允许一个unique列为空,Oracle11G允许多个unique列为空。
主键与唯一键的区别。
什么是notnull约束以及notnull约束与default约束的异同
notnull
——要求用户必须得为该属性赋一个值,否则语法出错
——可当做约束来处理
如果一个字段不写null,与不写notnull,则默认是null,即默认允许为空,用户可以不给该字段赋值。
notnull与default的区别:
相同点:
都允许用户不赋值
不同点:
null修饰的字段如果用户孙赋值则默认是null;default如果用户不赋值,则默认是default指定的值。
三、查询(重要)
1、计算列
select*formemp;
--*星号表示所有的
--formemp表示从emp表查询
selectempno,enamefromemp;--查询emp表中的empno和ename列
selectename,sal*12as“年薪”fromemp;--以逗号相隔
--as“年薪”是给新的计算列命名。
--as可以省略,名字年薪一定要用双引号,不可省略也不可写成单引号。
注意:
在Oracle中字段的别名不允许用单引号括起来,但SQLServer2005中却允许,因此,为了兼容性,最好字段别名用双引号括起来。
2、distinct【不允许重复的】独特的
selectdeptnofromemp;--输出所有的记录
selectdistinctdeptnofromemp;
--distinctdeptno会过滤掉重复的deptno值。
同时distinct也可以过滤掉重复的null值,即如果有多个null则只输出一个。
如果过滤多个字段,则过滤它们的组合。
3、between【在某个范围内】
如:
查找工资在1500到3000之间(包括1500和3000)的所有的员工信息
select*fromemp
wheresal>=1500andsal<=3000
等价于:
select*formemp
wheresalbetween1500and3000
再如:
查找工资小于1500大于3000的所有的员工信息
select*formemp
wheresal<1500orsal>3000
等价于:
select*formemp
wheresalnotbetween1500and3000
4、in【属于若干个孤立的值】
select*fromempwheresalin(1500,3000,5000)
等价于——语句可以写在一行上也可分两行写
select*formemp
wheresal=1500orsal=3000orsal=5000
另如:
select*fromempwheresalnotin(1500,3000,5000)
--把sal中除1500、3000、5000之外的项输出
等价于
select*formemp
wheresal<>1500andsal<>3000andsal<>5000
--数据库中不等于有两种表示:
!
=和<>。
推荐使用第二种
--对“或”取反是“并且”,对“并且”取反是“或”。
5、top【最前面的若干个记录】
如:
selecttop5*fromemp;
--top2是输出最前2个记录,*表示输出这两个记录的信息全部输出
selecttop2fromemp;--error
selecttop15percent*fromemp;
--15percent表示把最前的15%的记录输出,如果百分比结果不是整数,则进一取值。
分页查询后面会讲
把工资在1500到3000(包括1500和3000)的员工中工资最高的前4个人的信息输出:
selecttop4*
formemp;
wheresalbeteen1500and3000
orderbysaldesc
--orderby是排序,desc是降序的意思,不写则默认是升序。
排序默认是按升序排序
6、null【空值/没有值】
--输出资金为非空的员工的信息:
select*fromempwherecomm.isnull;--输出奖金为空的员工的信息。
select*fromempwherecomm.isnotnull;--输出奖金不为空的员工的信息。
以下是错误的写法:
select*fromempwherecomm.<>null;---输出为空error
select*fromempwherecomm.!
=null;---输出为空error
select*fromempwherecomm.=null;---输出为空error
总结:
null不能够参与如下运算:
<>、!
=、=,可以参与is、isont的运算。
0和null是不一样的,null表示空值,即没有值,而0代表一个确定的值。
任何类型的数据都允许为空,如:
createtablet1(namenvarchar(20),cntint,riqidatetime);
insertintot1values(null,null,null)
select*fromt1;
--输出每个员工的姓名、年薪(包含了奖金),comm假设是一年的奖金:
selectempno,ename,sal*12+comm.“年薪”fromemp;--错误
--此名证明:
null不能参与任何数据运算,否则结果永远为空,
任何数字与null进行运算的结果永远是null
以下句子可以输出包含奖金在内的年薪(假设字段comm为奖金项,奖金有的为空即null):
selectename,sal*12+isnull(comm,0)“年薪”fromemp;
--isnull(comm,0)如果comm是null,就返回一个值0,否则返回comm的实际值。
函数isnull(comm.,0)的含义:
如果comm为null,则返回值0,如果comm不为null,则返回其实际值。
7、orderby【以某个字段排序】
以下按工资升序排序:
select*fromemporderbysal;
--默认是按照升序排序。
select*fromemporderbydeptno,sal
--先按照deptno升序排序,deptno相同的,再按照sal排序。
注:
asc是升序的意思,默认可以不写,desc是降序。
select*fromemporderbydeptnodesc,sal;
--desc只影响前面的字段deptno,不会对后面的sal产生影响,即在上句中,deptno按desc排序,而sal按默认方式asc排序。
select*fromemporderbydeptno,saldesc;
--deptno按默认方式却升序排序,deptno相同的,再按saldesc降序排序。
综合:
orderbya,b--a和b都是升序
orderbya,bdesc--a升序,b降序
orderbyadesc,b--a降序,b升序
orderbyadesc,bdesc--a和b都是降序
总结:
如果不指定排序的标准,则默认是升序,升序用asc表示,默认可以不写;为一个字段指定的排序标准不会影响另一个字段的排序。
强烈建议为每一个字段都指定排序的标准。
8、模糊查询【搜索时经常使用】
格式:
select字段的集合from表名where某个字段的名字like匹配的条件
匹配的条件通常通常含有通配符
通配符:
%【代表任意0个或多个字符】
select*
fromemp
whereenamelike‘%A%’--ename只要含有字母A就输出
select*fromempwhereenamelike‘A%’--ename首字母是A就输出
select*fromempwhereenamelike‘%A’--ename尾字母是A就输出
_【任意单个字符】是下划线,不是减号或其它
select*fromempwhereenamelike‘_A%’--只要第二个字母是A的就输出
[a-f]【表示a到f中的任意一个字符】
select*fromempwhereenamelike‘_[A-F]%’
--把ename中第二个字符是A到F当中的任意一个的记录输出。
[a,f]【A或者F】
select*fromempwhereenamelike‘_[A,F]%’
[^a-f]【不是A到F中的任意一个的单个字符】
select*fromempwhereenamelike‘_[^A-F]%’
注意:
匹配的条件必须用单引号括起来,不能省略也不能改用双引号。
通配符作为普通字符使用:
select*fromstudentwherenamelike‘%\%%’escape‘\’
select*fromstudentwherenamelike‘%\_%’escape‘\’
--表示把所有记录中name列包含下划线“_”字符的记录输出。
escape‘\’——意思是把escape后面的字符\当作转义字符的标志,而“\”后的字符就会被作为普通字符处理了。
在SQL中可以把任意的字符当做转义字符的标志,具体是把哪个字符当做转义字符,这是由excape‘’来决定的。
9、聚合函数【多行记录返回一个值,通常用于统计分组的信息】
函数分类:
单行函数:
对每一行记录都起作用,对每一行记录都能返回一个结果。
例
selectename,lower(ename)fromemp
--lower(ename)会对每一个记录的ename字段都返回一个结果
多行函数:
对一组记录返回一个结果,即多行记录返回一个值,
聚合函数就是多行函数,例
selectmax(sal)fromemp
例:
selectlower(ename)fromemp;--最终返回的是14行,lower()是单行函数
selectmax(sal)fromemp;--返回1行,max()是多行函数
函数lower()是把大写的字符转换成小写的,而函数upper()是转换成大写的。
聚合函数的分类:
max()最大值
min()最小值
avg()平均值
count()求个数
聚合函数count()的使用
selectcount(*)fromemp;--返回emp表所有记录的个数
selectcount(deptno)fromemp;--返回emp表中字段deptno所有记录的个数,返回值是14,这说明deptno重复的记录也被当做有效的记录。
selectcount(distinctdeptno)fromemp;--返回值是3,统计deptno不重复的记录的个数。
selectcount(comm)fromemp;--返回值是4,这说明comm中为空的记录不会被当做有效的记录
总结:
count(*)返回表中所有的记录的个数
count(字段名)返回字段值非空的记录的个数,重复的记录也会被当做有效的记录
count(distinct字段名)返回字段中非空且不重复的记录的个数。
注意的问题:
selectmax(sal),min(sal),count(*)fromemp;--正确
selectmax(sal)“最高工资”,min(sal)“最低工资”,count(*)“员工人数”fromemp;
selectmax(sal),lower(ename)fromemp;--error,单行函数和多行函数不能混用。
从一个数据库切换到另一个数据库除了用鼠标点外也可以用命令:
usescott;
10、groupby【分组】
格式:
groupby字段的集合
功能:
把表中的记录按照字段分成不同的组
例子:
①selectdeptno,avg(sal)as“部门平均工资”
fromemp
groupbydeptno
--以上,输出每个部门的编号和该部门的平均工资
以下语句有错误:
②selectdeptno,avg(sal)as“部门平均工资”,ename
fromemp
groupbydeptno
总结:
使用了groupby之后,select中只能出现分组后的整体信息,不能出现组内的详细信息。
.
groupbya,b的用法:
③select*
fromemp
groupbydeptno,job--error
④selectdeptno,job,sal
fromemp
groupbydeptno,job--error
⑤selectdeptno,job,avg(sal)
fromemp
groupbydeptno,job--OK
⑥selectdeptno,job,avg(sal)
fromemp
groupbydeptno,job
orderbydeptno--OK,加入了排序的参数
查询不同部门的平均工资:
⑦selectdeptno,job,avg(sal)“平均工资”,count(*)“部门人数”,sum(sal)“部门总工资”,min(sal)“部门最低工资”
fromemp
groupbydeptno,job
orderbydeptno--OK,聚合函数都可以在一起使用
注意:
理解“groupbya,b,c“的用法:
先按a分组,a中相同的按b分组,b中相同的再按c分组。
最终统计的是最小分组的信息。
一定要弄明白上面②③④语句为什么错误。
11、having【对分组之后的信息进行过滤】
如:
输出部门平均工资大于2000的部门的部门编号、部门的平均工资
①selectdeptno,avg(sal)
fromemp
groupbydeptno
havingavg(sal)>2000
②selectdeptno,avg(sal)as“平均工资”
fromemp
groupbydeptno
havingavg(sal)>2000
③selectdeptno,avg(sal)as“平均工资”
fromemp
groupbydeptno
havingdeptno>10
④selectdeptno,avg(sal)as“平均工资”
fromemp
groupbydeptno
havingenamelike‘%A%’--ERROR
⑤selectdeptno“部门编号”,avg(sal)as“平均工资”
fromemp
groupbydeptno
having“部门编号”>1--ERROR
把工资大于2000且,部门平均工资大于2000的部门的部门编号、部门的平均工资,统计输出:
selectdeptno,avg(sal)“平均工资”,count(*)“部门人数”,max(sal)“部门最高工资”
fromemp
wheresal>2000--where是对原始的记录过滤
groupbydeptno
havingavg(sal)>3000--having是对分组之后的记录过滤
所有select参数的位置是固定的,顺序不允许变化的,否则编译时出错
总结:
⑴having是用来对分组之后的数据进行过滤,因此使用having时,通常都会先使用groupby。
⑵如果没使用groupby,但使用了having,则意味着having把所有的记录当做一组来进行过滤了。
如:
selectcount(*)
fromemp
havingavg(sal)>1000
⑶having子句出现的字段必须得是分组之后的组的整体信息,having子句不允许出现组内的详细信息。
⑷尽管select字段中可以出现别名,但是having子句中不能出现字段的别名,只能使用字段最原始的名字,原因不得而知。
⑸having和where的异同:
相同点:
都是对数据的过滤,只保留有效的数据。
二者都不允许出现字段的别名,只允许出现最原始的字段的名字。
比如前面例子⑤。
不同点:
where是对原始的记录过滤,having是对分组之后的记录过滤。
两者放在一起使用时,where必须写在having的前面,顺序不可颠倒,否则运行出错。
例子见前面。
================延伸:
select顺序:
selectdeptno,avg(sal)
fromemp--指明从哪张表去查询
wheresal>2000--对单条记录进行过滤
groupbydeptno--对数据进行分组
havingavg(sal)>1500--对分组后的数据进行过滤
orderbyavg(sal)desc;--对最后的结果进行排序
注:
不允许在where子句中使用聚合函数:
selectdeptno,avg(sal)formemp
whereavg(sal)>2000--运行时出错
groupbydeptno;
出错的原因:
聚合不应出现在WHERE子句中,不允许在WHERE子句中使用聚合函数。
先执行where,后执行groupby,所以执行where时对emp表还没有进行分组,因此编译时会报错。
SELECT语句的基本结构:
selectselect_list
[intonew_table_name]
fromtable_list
[wheresearch_conditions]
[groupbygroup_by_list]
[havingsearch_conditions]
[orderbyorder_list[
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server 学习笔记 学习 笔记