一晃距离我第一次面试已经一年有余了,其中坎坷也只有经历过才能体会。又到大四求职高峰期,总希望能给后来者一些建议,少走弯路。还记得自己第一次笔试,是一个ERP企业,最后8条写SQL语句的(共40分),当时只会做第一条简单的Select,其余都不会,果断被刷了,机会就这样擦肩而过。最近正好学习数据库知识,结合自己面试以及现在工作的一点经验,给应届生做个参考。
下面是就我个人理解的必须会的SQL语句。表的设计只是为了覆盖语法,有的设计并不合理。
1首当其冲的是单表的增删改查基本功能。
1.1查询所需要的字段
语法:Select 字段 From 表名 Where 条件
例:Select * From dbo.Student Where SAge>20 查找年龄大于20的学生的信息;
Select SID,SName,SAge From dbo.Student 查找所有学生的学号、姓名、年龄信息。
1.2增加数据
语法:添加部分数据:Insert Into 表名(字段1,字段2,字段3……) Values(值1,值2,值3……)
添加全部数据:Insert Into 表名 Values(字段1的值,字段2的值,字段3的值......)
注意:添加数据时可以为空字段可以不添加,自动增长的字段无需添加,别的都必须添加。
1.3修改数据
语法:Update 表名 Set 字段1=值1,字段2=值2……Where 条件
1.4删除数据
语法:Delete From 表名 Where 条件
2创建数据库和表,删除数据库和表,也都应该会吧
2.1创建数据库
语法:Create DataBase 数据库名
2.2创建表
语法:Create Table 表名
(
字段名1 字段1的类型 各种约束,
字段名2 字段2的类型 各种约束,
……
)
例如:Create Table InTable
(
InID int Primary Key,
WorkName nvarchar(4)
)
其中 Primary Key是主键约束,也就是定义中的各种约束。
下面定义了一个复杂的表Test01,包含了常见需求。
--表没有实际意义,只是介绍几种常见的约束 Create Table Test01 ( --primary key主键约束 ID int primary key, --外键约束,创建外键的方法 SID int foreign key references Student (SID), --identity表示自动从12开始增长,每次增长1,插入数据时,这一列无需插入 Tid int identity(12,1), --unique表示唯一约束,不能添加重复的数据 --(针对不是主键的而又要唯一不重复的的) Number int unique, --not null表示不能为空 SName nvarchar(20) not null, --表示最大5位,小数点后最多一位 SSaliay numeric(5,1), --check是在添加记录时自动检查年龄不得大于120,如果大于120则添加不进去 --check中是简单的表达式 SAge int Check(sage <120), )
2.3删除数据库
语法:Drop DataBase 数据库名
2.4删除表
语法:Drop Table 表名
3Group By,Order By,Having的用法
3.1Group By是用来分组的,通常与统计函数一起用
Sum+Group By分组求和
Count+ Group By分组计数
Max+Group By分组求最大值
Min+Group By分组求最小值
Avg+Group By分组求平均值
示例查询所有年龄段学生的总收入,人数,最大收入,最低收入,平均收入
--查询所有年龄段学生的总收入,人数,最大收入,最低收入,平均收入 Select SAge, Sum(SMoney) SumMoney, Count(1) Number, Max(SMoney) MaxMoney, Min(SMoney) MinMoney, Avg(SMoney) AvgMoney From dbo.Student Group By SAge
3.2Order By用来排序记录的
语法: Order By 字段1 排序方式,字段2 排序方式, ……
表示将记录按照字段1,字段2排序,其中先对字段1排序,如果字段1相同再按字段2排序。
3.3Having筛选有统计信息的条件
由于SQL中聚合函数无法跟Where一起用所以引入Having,一般出现个数大于多少(count)、总数小于多少的(sum)、最大值(最小值)大于多少(max/min)、平均数小于多少(avg)时就是用Having的时候。
--求总钱数大于5000的年龄段的人数以及年龄段 Select SAge ,count(1) as '人数',sum(SMoney) '总钱数' From dbo.Student group By SAge having sum(SMoney)>5000;
4一些关键字
top, percent,case,between and、in、 exists以及后三种的否定情况
4.1top,选择前多少条数据时用到
语法:Select Top N * From 表名表示取前N条记录(在分页时会用到)
示例:查询表中第21到30条数据(面试常考,但这种解法不是最优的,更好的用Max实现,这里就不列了,更好的方法涉及到索引优化,对于应届生,到时候说不出所以然还不如就用这种简单的)。
SELECT top 10* FROM Orders WHERE OrderID not in ( SELECT top 20 OrderID FROM Orders ) ORDER BY OrderID ASC
4.2 top N percent ,选择前百分之多少的数据
示例:选择前10%的记录
Select Top 10 percent * From 表名
4.3 between A And B表示范围在【A,B】的闭区间内
示例:查询年龄在【18,22】的学生信息
Select * From Student Where Sage between 18 and 22
4.4 in,表示在集合中
语法:in(A,B,C)
示例:选择年龄为18、19、或者22的人的信息
Select * From Student Where SAge in(18,19,22)
4.5exists是否存在,exists(query)如果有结果返回True,否则返回False
示例:查询是否存在大于20岁且有特长的学生
--查询是否存在大于20岁且有特长的学生 if exists(Select * From Student Where Age>20 And Specialty is not null ) print '有大于20岁且有特长的学生' else print '没有大于20岁且有特长的学生'
4.6 case分支
示例:判断学生的年龄段
--判断学生的年龄段 Select SName, case when SAge>20 then '大于20' when SAge<20 then '小于20' end '年龄段' From dbo.Student
5常用的一些系统自带函数
统计函数:Sum,Count,Min,Max,Avg
字符串函数:Substring,Ltrim,Rtrim,Replace
日期函数:Getdate,Datediff,Dateadd
6多表连接join和union
6.1 join
首先为什么要多表联接,当时学时就是不明白为什么不用from A,B Where A.arg=B.arg。
后来调查了一下发现其实from A,B Where A.arg=B.arg(下面记做方式一)就相当于A inner join B on A.arg=B.arg; (下面记做方式二)。然而有些实际问题是用Where A,B,C方式是不能解决的,比如一个学生表,一个奖学金表,现在要获得所有学生的信息以及奖学金信息(如果有奖学金),用方式一则会丢掉没有获奖学金的学生的数据,但实际不能因为他们没获奖学金就不统计他们的信息。这种情况用方式二就能解决。
join示例:
--查询所有学生信息(如果有奖学金则标出金额) Select SID,SName,SAge,SSex,SMoney,Scholarship.Amount '奖学金金额' From dbo.Student left join dbo.Scholarship on Student.ScholarshipID=Scholarship.ScholarshipID --查询所有获得奖学金的学生的信息以及奖学金金额 Select SID,SName,SAge,SSex,SMoney,Scholarship.Amount '奖学金金额' From dbo.Student inner join dbo.Scholarship on Student.ScholarshipID=Scholarship.ScholarshipID --至于Right Join --跟left思想一样,注意合理的选择主表。 --查询奖学金发放详细情况以及获奖人员信息 Select Amount '奖学金金额',Stu.SID,Stu.SName,Stu.SAge,Stu.SSex,Stu.SMoney From dbo.Scholarship left join dbo.Student as Stu on Stu.ScholarshipID=Scholarship.ScholarshipID
6.2union上下联接表
示例:
--统计人员进出信息 Select InID as IOID,WorkName,'进入' as IOFlag From InTable Union all Select OutID as IOID,WorkName,'出去' as IOFlag From OutTable
其中 进入' as IOFlag 用了'常量表达式
注意:上下连接的表必须字段名和类型都一样,如果不一样可以重命名至一致。
7存储过程
简单示例:
--Procedure是存储过程的关键字(可以简写成Proc),ViewAllStudentInfo是存储过程名 create Procedure ViewAllStudentInfo ( --参数列表 @SID int ) as --要执行的SQL语句 SELECT * FROM Student where SID=@SID; go
后记:涵盖了我所面试的和现在工作用的基本知识,如果过了这个范围,我只能认为是公司对数据库要求比较高吧。但愿能够帮助到求职的应届生吧。
转载请注明出处,原文链接,本文对应的SQL文件下载,可以直接运行的,都有注释的。