SQL Server 学习笔记二Word下载.docx

SQL Server 学习笔记二Word下载.docx

《SQL Server 学习笔记二Word下载.docx》由会员分享，可在线阅读，更多相关《SQL Server 学习笔记二Word下载.docx（21页珍藏版）》请在冰豆网上搜索。

--使用sql语句来查询数据,这是最简单的查询

select*fromhero

--①查询工资低于的heros

select*fromherowheresalary<

16000

--②把工资低于的heros的工资提高%（update）

--语法：

update表名set字段名=？

，字段名=？

where条件

updateherosetsalary=salary*1.1wheresalary<

--③请删除性别为女的hero

deletefromherowheregender='

女'

--表名规范

--表名以字母和_开头

--长度不能超过个字符

--不要使用sqlserver关键字和保留字

--只能使用A-Z，a-b，-9,$,#,_等

--支持的数据类型

/*字符型

<

纯中文时：

char和varchar>

①char：

定长，最大个字符（非unicode编码）

②char（10）'

小韩'

：

前个字符放‘小韩’，后面添个空格补全

③varchar:

变长，最大字符（非unicode编码）

④varchar（10）'

sqlserver分配个字符，这样可以节省空间

如果一个字段的长度是确定的，则使用char，因为char的速度更快

只有在不知道字段长度的情况下用varchar的

①ntext变长unicode数据，最大长度为的次方-1（，，，）个字符

②text变长非unicode数据，最大长度为的次方-1（，，，）个字符

区别：

text是字节格式存储英文的，也可以存放中文但有时候会显示乱码

ntext是多字节格式存储unicode的，也就是存储各种字母用的

可以含中文：

nchar和nvarchar>

①nchar：

定长，最大个字符（unicode编码）

②nchar（10）'

③nvarchar:

变长，最大字符（unicode编码）

④nvarchar（10）'

unicode编码：

用两个字节表示一个字符（可以是英文字母，也可以是汉字）-->

>

对汉字支持比较OK

非unicode编码：

用一个字节表示一个字母，用两个字节表示一个汉字

big5码：

支持繁体

iso-8859-1编码：

支持欧文

gb2312编码：

国标码针对中国汉字

gbk编码：

可以支持更多汉字

/*数字型

①bit：

范围到

②int：

范围-2的次到+2的次方-1

③bigint：

范围-2的次方到+2的次方-1

④float：

存放小数，不推荐使用

⑤numeric：

小数（更加精确，好控制）

强烈建议如果要去存放小数，最好使用numeric

/*日期类型

①datetime（表示日期，可以精确到毫秒）

②timestamp（时间戳）

一般情况下用datetime表示日期

sqlserver为我们提供了一个专门的函数

--getdate（）

/*图片

image：

保存图片，但是很少用，一般用路径保存图片，在软件公司

往往使用图片服务器和图床技术

视频

binary：

字段可以存放，但是我们往往将视频文件保存在文件服务器

上，sqlserver中保留文件路劲，存取效率高

sqlserver建议表的设计者，最好给表定义一个主键，用来标示唯一

的一条记录。

主键（primarykey）

①不能重复出现

②而且必须给值，换句话说，主键不允许为空

createdatabaseHongloumeng

useHongloumeng

go

createtableclerk

cleidintprimarykey,--主键必须给值

cleNamenvarchar（50）,

ageint

insertintoclerkvalues（1,'

贾宝玉'

18）

insertintoclerkvalues（2,'

贾政'

insertintoclerkvalues（5,'

贾母'

70）

insertintoclerkvalues（6,'

焦大'

75）

--插入部分字段，则需要在表名后，指定字段列表

--insertinto表名（字段列表）values（值列表）

insertintoclerk（cleid,cleName）values（3,'

林黛玉'

--把贾政的age修改为

updateclerksetage=38wherecleName='

--把号人，名字改为薛蟠，age该为

updateclerksetcleName='

薛蟠'

age=40wherecleid=2

--主键能修改吗?

2->

4

--可以修该，但修该后不能重复

updateclerksetcleid=4wherecleName='

--修该空值的数据

--空值不能用"

="

匹配,而要用"

is"

--把age为null的人的名字改为'

薛宝钗'

whereageisnull

--删除全部数据

--语法:

--deletefrom表名;

--删除指定数据

--deletefrom表名where字段名='

值'

and字段名='

--删除age>

=75的人，而且<

=75，或者age=null

deletefromclerkwhere（age>

=70andage<

=75）orageisnull

select*fromclerk

---------案例：

建表（公司管理系统）

--职员表

createdatabaseCompany

useCompany

--创建部门表

createtabledept

depNointprimarykey,--部门编号

depNamenvarchar（30）,--部门名称

depAddressnvarchar（30）--部门地址

select*fromdept

--创建员工表

createtableemp

empNointprimarykey,--员工编号

empNamenvarchar（30）,--姓名

jobnvarchar（30）,--岗位

mgrint,--上级

hireDatedatetime,--入职时间

salarynumeric（8,2）,--工资

commnumeric（8,2）,--奖金

depNointforeignkeyreferencesdept（depNo）--因为depNo我们根据需要要做成外键

--针对外键，请注意：

①外键只能指向主键

②外键和主键的数据类型要一致

select*fromemp

--键入数据

--首先键入部门表数据

insertintodeptvalues（10,'

Accounting'

NewYork'

insertintodeptvalues（20,'

Research'

Dallas'

insertintodeptvalues（30,'

Sales'

Chicago'

insertintodeptvalues（40,'

Aperations'

Boston'

--再键入员工数据

insertintoemp（empNo,empName,job,mgr,hireDate,salary,depNo）values

（7369,'

Smith'

clerk'

7902,'

1980-12-17'

800.00,20）

insertintoempvalues（7409,'

Allen'

salesman'

7698,'

1981-2-20'

1600.00,300.00,30）

insertintoempvalues（7521,'

Ward'

1981-2-22'

1250.00,500.00,30）

（7566,'

Jones'

manager'

7839,'

1981-4-2'

2975.00,20）

insertintoempvalues（7654,'

Martin'

1981-9-28'

1250.00,1400.00,30）

（7698,'

Blake'

1981-5-1'

2850.00,30）

（7782,'

Clark'

1981-6-9'

2450.00,10）

（7788,'

Scott'

analyst'

7566,'

1987-4-19'

3000.00,20）

insertintoemp（empNo,empName,job,hireDate,salary,depNo）values

（7839,'

King'

president'

1981-11-17'

5000.00,10）

insertintoempvalues（7844,'

Turner'

1981-9-8'

1500.00,0.00,30）

（7876,'

Adams'

7788,'

1987-5-23'

1100.00,20）

（7900,'

James'

1981-12-3'

950.00,30）

（7902,'

Ford'

（7934,'

Miller'

7782,'

1982-1-23'

1300.00,10）

insertintoempvalues（9999,'

Yangwei'

1988-5-5'

2345.00,80.00,10）

查询所有列

select*fromemp（表名）where条件

查询指定列

select字段,字段from表名where条件

如何取消重复行

selectdistinct字段from表名where条件

distinct只能消除完全一样的行，保留一行

--例：

查询Smith的薪水，工作，所在部门编号

selectsalary,job,depNofromempwhereempName='

--'

'

中不区分大小写

统计共有都少个部门编号

selectdistinctdepNofromemp

使用算数表达式

--显示每个员工的年工资

--年工资是别名或小名

selectempName,salary*13年工资fromemp

selectempName,salary*13+isnull（comm,0）*13年工资fromemp

使用where字句

--显示工资高于的员工

selectempNamefromempwheresalary>

3000

--查找-1-1后入职的员工

selectempNamefromempwherehireDate>

1982-1-1'

--显示工资在到的员工情况

select*fromempwheresalary>

=2000andsalary<

=2500

select*fromempwheresalarybetween2000and2500--betweenand是左右闭区间

使用like操作符（模糊查询）

--显示首字母为S的员工姓名和工资

selectempName,salaryfromempwhereempNamelike'

S%'

--显示第三个字母为O的员工姓名和工资

__O%'

在where条件中使用in

--显示empNo为,345,7788...的员工的情况

select*fromempwhereempNo=123orempNo=345orempNo=7788

--一般我们使用in这个关键字，效率更高

select*fromempwhereempNoin（123,456,7788）

使用isnull的操作符

--显示没有上级的员工情况

select*fromempwheremgrisnull

使用逻辑操作符号

--查询工资高于或岗位为manager的员工，同时满足他们的姓名首字母为j

select*fromempwhere（salary>

1000orjob='

）andempNamelike'

j%'

使用orderby字句

--orderby默认是升序

--按照工资从低到高循序显示员工信息

select*fromemporderbysalary

select*fromemporderbysalaryasc

--按照工资从高到低循序显示员工信息

select*fromemporderbysalarydesc

--按照入职的先后顺序排列

select*fromemporderbyhireDateasc

--按照部门号升序而员工工资降序排序

--orderby可以根据不同的字段排序

select*fromemporderbydepNo,salarydesc

使用列的别名排序

--统计每个人的年薪，并按照从高到低的循序排列

selectempName,（salary+isnull（null,0））*13fromemp

selectempName,（salary+isnull（null,0））*13年薪fromemporderby年薪

表的复杂查询

在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据

--数据分组函数：

max、min、avg、sum、count

显示所有员工中最高工资和最高工资

selectmin（salary）fromemp

--显示最低工资和员工的名字

selectempName,salaryfromemp

wheresalary=（selectmin（salary）fromemp）

--显示所有员工的平均工资和总工资

selectavg（salary）平均工资,sum（salary）总工资fromemp

selectempName,salaryfromemp

wheresalary>

（selectavg（salary）fromemp）

--并显示平均工资,以下这种方法效率不太高

selectempName,salary,（selectavg（salary）fromemp）fromemp

--计算总共有多少员工

selectcount（*）fromemp

--groupby和having子句

--groupby用于对查询的结果分组统计

--having子句用于限制分组显示结果

显示每个部门的平均工资和最高工资

selectavg（salary）平均工资,depNo部门编号,max（salary）最高工资fromempgroupbydepNo

--并显示部门名称（多表查询）

--显示每个部门的每个职位的平均工资和最低工资

selectavg（salary）平均工资,min（salary）最低工资,depNo,jobfromempgroupbydepNo,joborderbydepNo

--显示平均工资高于的部门编号和它的平均工资

--having往往和groupby结合使用，可以对分组查询后的结果进行筛选

selectavg（salary）,depNofromempgroupbydepNohavingavg（salary）>

2000

--并按从低到高排序

orderbyavg（salary）asc

对数据分组的总结

1.分组函数只能出现在选择列表、having、orderby子句中

2.如果在select语句中同时包含groupby，having，orderby那么他们的顺序是groupby，having，orderby

3.在选择列中，如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在groupby子句中，否则就会出错

（作业一）

学校环境如下：

一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。

现在要求建立关于系、学生、

班级的数据库，关系模式为

班CLASS（班号claNo,专业名subject,系名depName,入学年份enrollDate,人数num）

学生STUDENT（学号stuNo,姓名stuName,年龄age,班号claNo）

系DEPARTMENT（系号depNo,系名depName）

createdatabaseSchool

useSchool

createtableDEPARTMENT

depNointprimarykey,

depNamenvarchar（30）

select*fromDEPARTMENT

createtableCLASS

claNointprimarykey,

subjectnvarchar（30）,

enrolltimedatetime,

numint,

depNointforeignkeyreferencesDEPARTMENT（depNo）

select*fromCLASS

createtableSTUDENT

stuNointprimarykey,

stuNamenvarchar（30）,

ageint,

claNointforeignkeyreferencesCLASS（claNo）

select*fromSTUDENT

（作业二）

现在有一个商店的数据库，记录顾客及购物情况，请设计几张表，每张表的字段是什么

多表查询：

是指基于两个和俩个以上的表或是视图的查询，在实际应用中，查询单个表可能不能

满足你的需求，（如显示sales部门位置和其员工的姓名），这种情况下需要使用到（dept表和emp表）

显示员工姓名、员工工资及所在部门名称【笛卡尔集】

selectempName,depAddressfromemp,deptwheredept.depName='

sales'

andemp.depNo=dept.depNo

selectempName,salary,depNamefromemp,deptwhereemp.depNo=dept.depNo

--并且显示部门号

--如果两张表都有相同名字的字段，则需要带表名（别名）

selectempName,salary,depName,e.depNofromempe,deptdwheree.depNo=d.depNo

--显示部门号为的部门名、员工名和工资

selectdepNa