SQL Sever入门一、建库建表1. 创建数据库如果需要创建数据库可能会出现数据库名字重名的现象我们可以使用如下代码查询数据库名是否存在存在则删除此数据库。删除有风险请注意if exist(select *from sys.databases where name Mydatabase) drop database Mydatabase2. 创建数据库create database Mydatabase on--数据文件 ( name Mydatabase,--逻辑名称 filename D:\Database\Mydatabase.mdf, --存放路径以及逻辑名称 size 8MB,--文件初始大小 filegrowth 10% --增长率也可以使用MB ) log on--日志文件 ( name Mydatabase_log,--逻辑名称 filename D:\Database\Mydatabase_log.ldf, --存放路径以及逻辑名称 size 8MB,--文件初始大小 filegrowth 10% )还可以使用以下方法创建数据库create database Mydatabase --数据文件和日志文件的信息全部采用默认值3. 建表在建表之前我们需要指定对应的数据库同时不允许存在同名的数据库所以需要进行判断然后删除原先的表仅限学习阶段。use Mydatabase --指定我们要建表的数据库 if exits(select * from sys.objects where name MySchool and type U) drop table Department(1) 创建语法create table 表名 ( 字段名1 数据类型(长度), 字段名2 数据类型(长度) ) --示例 create table Department ( DepartmentID int primary key identity(1,1), --创建部门编号int代表整型primary key代表主键identity(1,1)代表从1开始以1为步长自动增长 DepartmentName varchar(50) not null, --创建部门名称varchar(50)表示长度为50的字符串not null表示不能为空 DepartmentRemark text --创建部门的表述text表示长文本 )(2) 常用字符串类型。char定长例如char(5)无论存储的数据是否达到了5个字节都要占用5个字节的空间。 varchar可变长度例如varchar(5)表示最多占用5个字节。限长8000也可以使用varchar(max)表示最大长度。 text长文本最大长度为2^31-1个字符 nchar,nvarchar,ntext前缀为n表示Unicode数据类型的字符区别于varchar(100)可以存储100个英文字符或者50个中文汉字nvarchar(100)可以存储100个英文字符或者100个中文汉字。(3) 创建表create table [Rank] ( RankID int primary key identity(1,1) RankName varchar(50) not null, RankRemark text ) --创建职级表其中rank为关键字用于对结果集中的行进行排名遇到相同值时排名会跳跃所以我们添加[]表示自定义名字 create table Teacher ( TeacherID int primary key identity(1,1), DepartmentID int references Department(DepartmentID) not null,--references代表外键引用 RankID int references [Rank](RankID) not null, TeacherName varchar(50) not null, TeacherSex varchar(2) default(男) check(TeacherSex 男 or TeacherSex 女) not null --default代表默认字段,check可以规定字段值的约束条件 TeacherBirth datetime not null, TeacherSalary decimal(12,2) check(TeacherSalary 1000 and TeacherSalary50000) not null, TeacherPhone varchar(20) unique not null,--unique表示唯一约束,不能重复 TeacherAddress varchar(100), TeacherAddTime smalldatetime default(getdate()) --datetime和smalldatetime都可以表示时间类型getdate()用于获取系统当前的时间 )--创建老师信息表4. 修改表结构(1) 在表中添加列--语法 alter table 表名 add 列名 数据类型 --示例为教师添加邮箱 alter table Teacher add TeacherMail varchar(100)(2) 在表中删除列--语法 alter table 表名 drop column 列名 --示例删除邮箱 alter table Teacher drop column TeacherMail(3) 改变表中列的数据类型--语法 alter table 表名 alter column 列名 数据类型 --示例 改变邮箱列的数据类型为nvarchar(100) alter table Teacher alter column TeacherMail nvarchar(100)5. 添加删除约束(1) 添加约束--添加主键约束 alter table 表名 add constraint 约束名称 primary key(列名) --添加check约束 alter table 表名 add constraint 约束名称 check(约束表达式) --添加unique约束 alter table 表名 add constraint 约束名称 unique(列名) --添加default约束 alter table 表名 add constraint 约束名称 default 默认值 列名 --添加外键约束 alter table 表名 add constraint 约束名称 foreign key(列名) references 关联表名(关联列表名)(2) 删除约束if exists(select * from sysobjects where name约束名) alter table 表名 drop constraint 约束名; go二、插入数据1. 向部门表中插入数据--标准语法 insert into Department(DepartmentName,DepartmentRamark) values(教育部,......) insert into Department(DepartmentName,DepartmentRamark) values(纪律部,......) --简写语法省略字段名称 insert into Department values(卫生部,学校主管卫生工作的部门) --该写法在给字段赋值时必须保证顺序和数据表结构中字段顺序完全一致 --一次插入多行数据 insert into Department(DepartmentName,DepartmentRemark) select 教育部,负责学生教育工作 union select 纪律部,负责管理学生纪律 union select 宿管部,负责学生住宿管理2. 向职级表插入数据insert into [Rank](RankName,RankRemark) values(初级,能够完成基本工作) insert into [Rank](RankName,RankRemark) values(中级,可以担任管理人员) insert into [Rank](RankName,RankRemark) values(高级,这是领导)3.向教师表插入数据INSERT INTO Teacher (DepartmentID, RankID, TeacherName, TeacherSex, TeacherBirth, TeacherSalary, TeacherPhone, TeacherAddress) VALUES -- 斗破苍穹3人 (1, 1, 萧炎, 男, 1995-06-06, 3800.00, 13800000001, 浙江省乌坦城萧家旧宅), (1, 2, 药老, 男, 1989-11-23, 6500.00, 13800000002, 浙江省乌坦城郊外山洞), (1, 3, 海波东, 男, 1983-03-11, 12000.00, 13800000003, 加玛帝国米特尔家族总部), -- 斗罗大陆4人 (1, 1, 唐三, 男, 1995-11-28, 3600.00, 13800000004, 四川省唐门旧址), (1, 1, 小舞, 女, 1996-03-21, 3700.00, 13800000005, 四川省星斗大森林边缘), (1, 2, 玉小刚, 男, 1988-05-17, 6400.00, 13800000006, 四川省蓝电霸王龙家族), (1, 3, 比比东, 女, 1984-02-27, 12500.00, 13800000007, 四川省武魂殿总部), -- 凡人修仙传3人 (1, 1, 韩立, 男, 1996-07-14, 3900.00, 13800000008, 山东省青牛镇韩家村), (1, 2, 南宫婉, 女, 1990-12-15, 6300.00, 13800000009, 掩月宗大殿), (1, 3, 令狐老祖, 男, 1982-09-08, 12800.00, 13800000010, 黄枫谷后山禁地), -- 纪律部DepartmentID 2—— 10人 -- 斗破苍穹3人 (2, 1, 萧薰儿, 女, 1996-07-15, 3800.00, 13800000011, 浙江省乌坦城萧家后院), (2, 2, 云韵, 女, 1989-12-08, 6600.00, 13800000012, 云南省加玛帝国云岚宗), (2, 3, 美杜莎女王, 女, 1984-05-18, 13500.00, 13800000013, 塔戈尔大沙漠蛇人族神殿), -- 斗罗大陆4人 (2, 1, 戴沐白, 男, 1994-07-09, 3500.00, 13800000014, 天津市星罗帝国旧址), (2, 1, 宁荣荣, 女, 1997-12-05, 3800.00, 13800000015, 浙江省宁波市七宝琉璃宗), (2, 2, 柳二龙, 女, 1990-03-09, 6200.00, 13800000016, 四川省黄金铁三角驻地), (2, 3, 千仞雪, 女, 1985-07-16, 11800.00, 13800000017, 四川省天使神殿), -- 凡人修仙传3人 (2, 1, 墨彩环, 女, 1996-04-22, 3700.00, 13800000018, 越国七玄门旧址), (2, 2, 元瑶, 女, 1990-08-09, 6400.00, 13800000019, 乱星海妙音门), (2, 3, 向之礼, 男, 1983-11-03, 13000.00, 13800000020, 天南修仙界传送阵), -- 宿管部DepartmentID 3—— 10人 -- 斗破苍穹4人 (3, 1, 纳兰嫣然, 女, 1995-10-20, 3700.00, 13800000021, 云南省加玛帝国纳兰家), (3, 1, 小医仙, 女, 1996-02-14, 3600.00, 13800000022, 魔兽山脉山谷小屋), (3, 2, 萧战, 男, 1989-03-17, 6100.00, 13800000023, 浙江省乌坦城萧家大厅), (3, 3, 魂天帝, 男, 1982-12-25, 14500.00, 13800000024, 中州魂殿总部), -- 斗罗大陆3人 (3, 1, 奥斯卡, 男, 1996-10-31, 3650.00, 13800000025, 四川省史莱克学院), (3, 2, 弗兰德, 男, 1987-09-21, 6200.00, 13800000026, 四川省史莱克学院院长室), (3, 3, 唐晨, 男, 1982-09-28, 13200.00, 13800000027, 四川省昊天宗旧址), -- 凡人修仙传3人 (3, 1, 厉飞雨, 男, 1995-05-08, 3800.00, 13800000028, 越国七玄门演武场), (3, 2, 紫灵, 女, 1990-10-11, 6300.00, 13800000029, 乱星海星宫), (3, 3, 大衍神君, 男, 1982-06-19, 13800.00, 13800000030, 大晋国天机阁旧址);4. 查询数据是否插入成功select * from Department select * from [Rank] select * from Teacher三、修改和删除数据1. 修改数据示例--涨工资为每个老师500元工资 update Teacher set TeacherSalary TeacherSalary 500 --指定修改将教师工号为8的工资1000元 update Teacher set TeacherSalary TeacherSalary 1000 where TeacherID 8 --将教育部部门编号已知1所有教师工资低于1万的全部调成一万 update Teacher set TeacherSalary TeacherSalary 10000 where DepartmentID 1 adn TeacherSalary 10000 --将药老地址改为浙江省乌坦城萧家旧宅 update Teacher set TeacherAddress 浙江省乌坦城萧家旧宅 where TeacherName药老 --将韩立工资改为以前的两倍并修改其地址为天道盟落云宗青竹峰 update Teacher set TeacherAddress 天道盟落云宗青竹峰 where TeacherName 韩立2. 删除数据示例--删除教师表中所有数据 delect from Teacher --删除宿管部已知编号为3中工资大于15000的所有老师 delect form Teacher where DepartmentID 3 and TeacherSalary 150003. droptruncatedelete的区别drop table删除表对象其中表数据表结构表对象都进行了删除delete 和 truncate table删除表数据但是表对象以及表结构仍然存在delete和truncate table具体区别delete 1、可以删除表所有数据也可以根据条件删除数据 2、如有自动编号泽删除后继续编号例如delete删除表所有数据之后之前数据的编号是123那么之后新增的数据编号从4开始 truncate 1、只能清空整个表数据不能根据条件删除数据 2、如果有自动编号清空表数据后重新编号例如truncate删除表所有数据之后之前数据的编号是123那么之后新增的数据编号仍然从1开始四、基础查询1. 查询所有行所有列--查询所有部门 select * from Department --查询所有职级 select * from [Rank] --查询所有教师信息 select * from Teacher2. 指定列查询select TeacherName,TeacherSex,TeacherSalary,TeacherPhone from Teacher3. 指定列查询并自定义中文列名select TeacherName 姓名,TeacherSex 性别,TeacherSalary 工资,TeacherPhone 电话 from Teacher4. 查询学校老师所在的地点不需要重复数据select distinct TeacherAddress from Teacher --关键字 distinct 用于返回唯一不同的值去重5. 假设工资涨10%查询原始工资和调整后的工资显示姓名性别月薪和加薪后的月薪select TeacherName 姓名,TeacherSex 性别,TeacherSalary 月薪,TeacherSalary*1.1 加薪后月薪 from Teacher五、条件查询1. SQL中常用的运算符运算符作用用于比较是否相等以及赋值用于比较是否不相等用于比较是否不相等用于比较是否大于用于比较是否小于用于比较是否大于等于用于比较是否小于等于is null用于判断是否为空is not null用于判断是否不为空in用于判断是否在其中like模糊查询between…and…比较是否在两者之间and逻辑与两个条件同时成立则表达式成立or逻辑或两个条件有一个成立则表达式成立not逻辑非前面成立则后面不成立前面不成立则后面成立2. 查询示例--(1)根据指定列姓名性别工资电话查询性别为女的教师信息并自定义中文列名 select TeacherName 姓名,TeacherSex 性别,TeacherSalary 工资,TeacherPhone 电话 from Teacher where TeacherSex 女 --(2)查询月薪大于等于10000的教师信息 select * from Teacher where TeacherSalary 10000 --(3)查询月薪大于等于10000的女教师信息 select * from Teacher where TeacherSalary 10000 and TeacherSex 女 --(4)查询出生年月在1990-1-1之后并且月薪大于等于10000的女教师信息 select * from Teacher where TeacherBirth1990-1-1 and TeacherSalary 女 --(5)查询工资大于15000的教师或者工资大于8000的女教师信息 select * from Teacher where TeacherSalary 15000 or(TeacherSalary8000 and TeacherSex女) --(6)查询月薪在10000到20000之间的教师信息 select * from Teacher where TeacherSalary 10000 and TeacherSalary 20000 select * from Teacher where TeacherSalary between 10000 and 20000 --(7)查询出地址在落云宗或者掩月宗大殿的教师信息 select * from Teacher where TeacherAddress掩月宗大殿 or TeacherAddress天道盟落云宗青竹峰 select * from Teacher where TeacherAddress in(掩月宗大殿,天道盟落云宗青竹峰) --(8)查询所有教师信息并按工资降序排列 --order by 排序asc 正序desc 倒序 select * from Teacher order by TeacherSalary desc --(9)显示所有教师信息按照名字长度进行倒叙排序 select * from Teacher order by len(TeacherName) desc --(10)查询工资最高的10个人的信息 select top 10 * from Teacher order by TeacherSalary desc --(11)查询工资前百分之十的教师信息 select top 10 percent * from Teacher order by TeacherSalary desc --(12)查询没填地址的教师信息 select * from Teacher where TeacherAddress is null --(13)查询地址已经填写的教师信息 select * from Teacher where TeacherAddress is not null --(14)查询所有90后教师信息 select * from Teacher where TeacherBirth 1990-1-1 and TeacherBirth 1999-12-31 select * from Teacher where TeacherBirth between 1990-1-1 and 1999-12-31 select * from Teacher where year(TeacherBirth) 1990 and year(TeacherBirth) 1999 --(15)查询年龄在30-40 之间并且工资在15000-30000 之间的教师信息 select * from Teacher where (year(getdate())-year(TeacherBirth) 30 and year(getdate())-year(TeacherBirth) 40) and (TeacherSalary 15000 and TeacherSalary 30000) select * from Teacher where (year(getdate())-year(TeacherBirth) between 30 and 40) and TeacherSalary between 15000 and 30000 --(16)查询工资比萧炎高的人 select * from Teacher where TeacherSalary(select TeacherSalary from Teacher where TeacherName萧炎) --(17)查询出星座是天蝎座的人的信息10月24日至11月22日 select * from Teacher where (month(TeacherBirth) 10 and DAY(TeacherBirth) 24) or (month(TeacherBirth) 11 and DAY(TeacherBirth) 22) --(18)查询和萧炎地址一样的人 select * from Teacher where TeacherAddress (select TeacherAddress from Teacher where TeacherName 萧炎) --(19)查询出生生肖为羊的人 select * from Teacher where year(TeacherBirth)%1211 --(20)查询所有教师的信息添加一列显示生肖 select TeacherName 姓名,TeacherSex 性别,TeacherSalary 工资,TeacherPhone 电话,TeacherBirth 生日, case when year(TeacherBirth) % 12 4 then 鼠 when year(TeacherBirth) % 12 5 then 牛 when year(TeacherBirth) % 12 6 then 虎 when year(TeacherBirth) % 12 7 then 兔 when year(TeacherBirth) % 12 8 then 龙 when year(TeacherBirth) % 12 9 then 蛇 when year(TeacherBirth) % 12 10 then 马 when year(TeacherBirth) % 12 11 then 羊 when year(TeacherBirth) % 12 0 then 猴 when year(TeacherBirth) % 12 1 then 鸡 when year(TeacherBirth) % 12 2 then 狗 when year(TeacherBirth) % 12 3 then 猪 ELSE end 生肖 from Teacher六、模糊查询模糊查询使用like关键字和通配符结合实现常见通配符如下通配符作用%代表匹配0个字符、1个字符或多个字符。_代表匹配有且只有1个字符。[]代表匹配范围内[^]代表匹配不在范围内--(1)查询姓唐的教师信息 select * from Teacher Where TeacherName like唐% --(2)查询名字中含有宫的教师信息 select * from Teacher where TeacherName like %宫% --(3)查询名字中含有宫或者荣的教师信息 select * from Teacher where TeacherName like %荣% or TeacherName like %宫% --4查询姓唐名字是两个字的人 select * from Teacher where TeacherName like 唐_ select * from Teacher where SUBSTRING(TeacherName,1,1)唐 and LEN(TeacherName)2 --(5)查询最后一个字是儿名字共有三个字的人的信息 select * from Teacher where TeacherName like__儿 select * from Teacher where SUBSTRING(TeacherName,3,1) 儿and len(TeacherName)3 --(6)查询出电话是以138开头的教师信息 select * from Teacher where TeacherPhone like138% --(7)查询电话是138开头第四位可能是5可能是9最后一位是2的人的信息 select * from Teacher where TeacherPhone like 138[5,9]%2 --(8)查询电话是138开头第四位是3-8之间最后一个不是1和2的人的信息 select * from Teacher where TeacherPhone like 138[3,4,5,6,7,8]%[^1,2] select * from Teacher where TeacherPhone like 138[3-8]%[^1-2]七、聚合函数SQL SERVER中聚合函数主要有count求数量 max求最大值 min求最小值 sum求总和 avg求平均值1. 聚合函数举例应用1求教师总人数select COUNT(*) 数量 from Teacher2求最大值求最高工资select MAX(TeacherSalary) 最高工资 from Teacher3求最小时求最小工资select MIN(TeacherSalary) 最低工资 from Teacher4求和求所有教师的工资总和select SUM(TeacherSalary) 工资总和 from Teacher5求平均值求所有教师的平均工资--方案一 select AVG(TeacherSalary) 平均工资 from Teacher --方案二精确到2位小数 select ROUND(AVG(TeacherSalary),2) 平均工资 from Teacher --方案三精确到2位小数 select Convert(decimal(12,2),AVG(TeacherSalary)) 平均工资 from TeacherROUND函数用法round(num,len,[type]) 其中: num表示需要处理的数字len表示需要保留的长度type处理类型(0是默认值代表四舍五入非0代表直接截取) select ROUND(123.45454,3) --123.45500 select ROUND(123.45454,3,1) --123.454006求数量最大值最小值总和平均值在一行显示select COUNT(*) 数量,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资,SUM(TeacherSalary) 工资总和,AVG(TeacherSalary) 平均工资 from Teacher7查询出四川省的教师人数总工资最高工资最低工资和平均工资select 四川省 地区,COUNT(*) 数量,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 ,SUM(TeacherSalary) 工资总和,AVG(TeacherSalary) 平均工资 from Teacher WHERE TeacherAddress like 四川省%8求出工资比平均工资高的人员信息select * from Teacher where TeacherSalary (select AVG(TeacherSalary) 平均工资 from Teacher)9求数量年龄最大值年龄最小值年龄总和年龄平均值在一行显示--方案一 select COUNT(*) 数量, MAX(year(getdate())-year(TeacherBirth)) 最高年龄, MIN(year(getdate())-year(TeacherBirth)) 最低年龄, SUM(year(getdate())-year(TeacherBirth)) 年龄总和, AVG(year(getdate())-year(TeacherBirth)) 平均年龄 from Teacher --方案二 select COUNT(*) 数量, MAX(DATEDIFF(year, TeacherBirth, getDate())) 最高年龄, MIN(DATEDIFF(year, TeacherBirth, getDate())) 最低年龄, SUM(DATEDIFF(year, TeacherBirth, getDate())) 年龄总和, AVG(DATEDIFF(year, TeacherBirth, getDate())) 平均年龄 from Teacher10计算出月薪在10000 以上的男性教师的最大年龄最小年龄和平均年龄--方案一 select 男 性别,COUNT(*) 数量, MAX(year(getdate())-year(TeacherBirth)) 最高年龄, MIN(year(getdate())-year(TeacherBirth)) 最低年龄, SUM(year(getdate())-year(TeacherBirth)) 年龄总和, AVG(year(getdate())-year(TeacherBirth)) 平均年龄 from Teacher where TeacherSex 男 and TeacherSalary 10000 --方案二 select 男 性别,COUNT(*) 数量, MAX(DATEDIFF(year, TeacherBirth, getDate())) 最高年龄, MIN(DATEDIFF(year, TeacherBirth, getDate())) 最低年龄, SUM(DATEDIFF(year, TeacherBirth, getDate())) 年龄总和, AVG(DATEDIFF(year, TeacherBirth, getDate())) 平均年龄 from Teacher where TeacherSex 男 and TeacherSalary 1000011统计出所在地在“四川省地区或浙江省地区”的所有女教师数量以及最大年龄最小年龄和平均年龄--方案一 select 四川省地区或浙江省地区 地区,女 性别,COUNT(*) 数量, MAX(year(getdate())-year(TeacherBirth)) 最高年龄, MIN(year(getdate())-year(TeacherBirth)) 最低年龄, SUM(year(getdate())-year(TeacherBirth)) 年龄总和, AVG(year(getdate())-year(TeacherBirth)) 平均年龄 from Teacher where TeacherSex 女 and (TeacherAddress LIKE 四川省% OR TeacherAddress LIKE 浙江省%) --方案二 select 四川省地区或浙江省地区 地区,女 性别,COUNT(*) 数量, MAX(DATEDIFF(year, TeacherBirth, getDate())) 最高年龄, MIN(DATEDIFF(year, TeacherBirth, getDate())) 最低年龄, SUM(DATEDIFF(year, TeacherBirth, getDate())) 年龄总和, AVG(DATEDIFF(year, TeacherBirth, getDate())) 平均年龄 from Teacher where TeacherSex 女 and (TeacherAddress LIKE 四川省% OR TeacherAddress LIKE 浙江省%)12求出年龄比平均年龄高的人员信息--方案一 select * from Teacher where year(getdate())-year(TeacherBirth) (select AVG(year(getdate())-year(TeacherBirth)) from Teacher) --方案二 select * from Teacher where DATEDIFF(year, TeacherBirth, getDate()) (select AVG(DATEDIFF(year, TeacherBirth, getDate())) from Teacher)2.SQL中常用时间处理函数GETDATE() 返回当前的日期和时间DATEPART() 返回日期/时间的单独部分DATEADD() 返回日期中添加或减去指定的时间间隔DATEDIFF() 返回两个日期直接的时间DATENAME() 返回指定日期的指定日期部分的整数CONVERT() 返回不同格式的时间示例select DATEDIFF(day, 2019-08-20, getDate()); --获取指定时间单位的差值 SELECT DATEADD(MINUTE,-5,GETDATE()) --加减时间,此处为获取五分钟前的时间,MINUTE 表示分钟可为 YEAR,MONTH,DAY,HOUR select DATENAME(month, getDate()); --当前月份 select DATENAME(WEEKDAY, getDate()); --当前星期几 select DATEPART(month, getDate()); --当前月份 select DAY(getDate()); --返回当前日期天数 select MONTH(getDate()); --返回当前日期月数 select YEAR(getDate()); --返回当前日期年数 SELECT CONVERT(VARCHAR(22),GETDATE(),20) --2020-01-09 14:46:46 SELECT CONVERT(VARCHAR(24),GETDATE(),21) --2020-01-09 14:46:55.91 SELECT CONVERT(VARCHAR(22),GETDATE(),23) --2020-01-09 SELECT CONVERT(VARCHAR(22),GETDATE(),24) --15:04:07 Select CONVERT(varchar(20),GETDATE(),14) --15:05:49:330时间格式控制字符串名称日期单位缩写年yearyyyy 或yy季度quarterqq,q月monthmm,m一年中第几天dayofyeardy,y日daydd,d一年中第几周weekwk,ww星期weekdaydw小时Hourhh分钟minutemi,n秒secondss,s毫秒millisecondms八、分组查询--1根据教师所在地区分组统计教师数量教师工资总和平均工资最高工资和最低工资 select left(TeacherAddress,3) 地区,count(*) 人数,sum(TeacherSalary) 工资总和,avg(TeacherSalary) 平均工资,max(TeacherSalary) 最高工资,min(TeacherSalary) 最低工资 from Teacher group by left(TeacherAddress,3) --2根据教师所在地区分组统计教师人数教师工资总和平均工资最高工资和最低工资1985 年及以后出身的教师不参与统计。 select left(TeacherAddress,3) 地区,count(*) 人数,sum(TeacherSalary) 工资总和,avg(TeacherSalary) 平均工资,max(TeacherSalary) 最高工资,min(TeacherSalary) 最低工资 from Teacher where year(TeacherBirth)1985 group by left(TeacherAddress,3) --3根据教师所在地区分组统计教师人数教师工资总和平均工资最高工资和最低工资要求筛选出教师人数至少在2人及以上的记录并且1985年及以后出身的教师不参与统计。 select left(TeacherAddress,3) 地区,count(*) 人数,sum(TeacherSalary) 工资总和,avg(TeacherSalary) 平均工资,max(TeacherSalary) 最高工资,min(TeacherSalary) 最低工资 from Teacher where year(TeacherBirth)1985 group by left(TeacherAddress,3) having COUNT(*) 2九、多表查询1. 笛卡尔乘积select * from Teacher,Department该查询会将Teacher表中所有数据和Department表中的所有数据进行一次排列组合然后形成新的记录。例如Teacher中有30条记录department中有3条则会形成90条记录2. 简单多表查询该查询方式不会查询不符合主外键关系的数据查询教师信息同时显示部门名称select * from Teacher,Department where Teacher.DepartmentIDDepartment.DepartmentID查询教师信息同时显示职级名称select * from Teacher,Rank where Teacher.RankIDRank.RankID查询教师信息同时显示部门名称和职位名称select * from Teacher,Department,Rank where Teacher.DepartmentIDDepartment.DepartmentID and Teacher.RankIDRank.RankID3. 内连接该查询方式不会查询不符合主外键关系的数据查询教师信息同时显示部门名称select * from Teacher inner join Department on Teacher.DepartmentId Department.DepartmentId查询教师信息同时显示职级名称select * from Teacher inner join Rank on Teacher.RankId Rank.RankId查询教师信息同时显示部门名称职位名称select * from Teacher inner join Department on Teacher.DepartmentId Department.DepartmentId inner join Rank on Teacher.RankId Rank.RankId4. 外连接外连接分为三类左外连接以左表为主显示全部数据主外键关系找不到数据的地方用null取代--查询教师信息同时显示部门名称 select * from Teacher left join Department on Teacher.DepartmentID Department.DepartmentID --查询教师信息同时显示职级名称 select * from Teacher left join Rank on Teacher.RankID Rank.RankID --查询教师信息同时显示部门名称职位名称 select * from Teacher left join Department on Teacher.DepartmentID Department.DepartmentID left join Rank on Teacher.RankID Rank.RankID右外连接以右表为主显示全部数据主外键关系找不到数据的地方用null取代—A left join B B right join A--查询教师信息同时显示部门名称 SELECT * FROM Teacher RIGHT JOIN Department ON Teacher.DepartmentID Department.DepartmentID; --查询教师信息同时显示职级名称 SELECT * FROM Teacher RIGHT JOIN Rank ON Teacher.RankID Rank.RankID; --查询教师信息同时显示部门名称职位名称 SELECT Teacher.*, Department.DepartmentName, Rank.RankName FROM Rank RIGHT JOIN ( Teacher RIGHT JOIN Department ON Teacher.DepartmentID Department.DepartmentID ) ON Teacher.RankID Rank.RankID;全连接它会返回左右两张表的所有记录匹配上的行两边的数据都显示左表有但右表没有的右表字段显示 NULL右表有但左表没有的左表字段显示 NULL--查询教师信息同时显示部门名称 SELECT * FROM Teacher FULL JOIN Department ON Teacher.DepartmentID Department.DepartmentID; --查询教师信息同时显示职级名称 SELECT * FROM Teacher FULL JOIN Rank ON Teacher.RankID Rank.RankID; --查询教师信息同时显示部门名称职位名称 SELECT Teacher.*, Department.DepartmentName, Rank.RankName FROM Teacher FULL JOIN Department ON Teacher.DepartmentID Department.DepartmentID FULL JOIN Rank ON Teacher.RankID Rank.RankID;5. 多表查询示例--1查询出浙江地区所有的员工信息要求显示部门名称以及员工的详细资料 select TeacherName 姓名,Teacher.DepartmentId 部门编号 ,DepartmentName 部门名称, TeacherSex 性别,TeacherBirth 生日, TeacherSalary 月薪,TeacherPhone 电话,TeacherAddress 地区 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId where TeacherAddress like 浙江% --2查询出浙江地区所有的员工信息要求显示部门名称职级名称以及员工的详细资料 select TeacherName 姓名,DepartmentName 部门名称,RankName 职位名称, TeacherSex 性别,TeacherBirth 生日, TeacherSalary 月薪,TeacherPhone 电话,TeacherAddress 地区 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId left join [Rank] on [Rank].RankId Teacher.RankId where TeacherAddress like 浙江% --3根据部门分组统计员工人数员工工资总和平均工资最高工资和最低工资。 --提示在进行分组统计查询的时候添加二表联合查询。 select DepartmentName 部门名称,COUNT(*) 人数,SUM(TeacherSalary) 工资总和, AVG(TeacherSalary) 平均工资,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId group by Department.DepartmentId,DepartmentName --4根据部门分组统计员工人数员工工资总和平均工资最高工资和最低工资平均工资在10000 以下的不参与统计并且根据平均工资降序排列。 select DepartmentName 部门名称,COUNT(*) 人数,SUM(TeacherSalary) 工资总和, AVG(TeacherSalary) 平均工资,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId group by Department.DepartmentId,DepartmentName having AVG(TeacherSalary) 10000 order by AVG(TeacherSalary) desc --5根据部门名称然后根据职位名称分组统计员工人数员工工资总和平均工资最高工资和最低工资 select DepartmentName 部门名称,RANKNAME 职级名称,COUNT(*) 人数,SUM(TeacherSalary) 工资总和, AVG(TeacherSalary) 平均工资,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 from Teacher LEFT JOIN DEPARTMENT on Department.DepartmentId Teacher.DepartmentId LEFT JOIN [Rank] on [Rank].RANKID Teacher.RANKID group by Department.DepartmentId,DepartmentName,[Rank].RANKID,RANKNAME6. 自连接自己连接自己示例 create table Dept ( DeptId int primary key, --部门编号 DeptName varchar(50) not null, --部门名称 ParentId int not null, --上级部门编号 ) insert into Dept(DeptId,DeptName,ParentId) values(1,软件部,0) insert into Dept(DeptId,DeptName,ParentId) values(2,硬件部,0) insert into Dept(DeptId,DeptName,ParentId) values(3,软件研发部,1) insert into Dept(DeptId,DeptName,ParentId) values(4,软件测试部,1) insert into Dept(DeptId,DeptName,ParentId) values(5,软件实施部,1) insert into Dept(DeptId,DeptName,ParentId) values(6,硬件研发部,2) insert into Dept(DeptId,DeptName,ParentId) values(7,硬件测试部,2) insert into Dept(DeptId,DeptName,ParentId) values(8,硬件实施部,2) 如果要查询出所有部门信息并且查询出自己的上级部门查询结果如下 --部门编号 部门名称 上级部门 -- 3 软件研发部 软件部 -- 4 软件测试部 软件部 -- 5 软件实施部 软件部 -- 6 硬件研发部 硬件部 -- 7 硬件测试部 硬件部 -- 8 硬件实施部 硬件部 select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级名称 from Dept A inner join Dept B on A.ParentId B.DeptId
SQL Sever入门一、建库建表1. 创建数据库如果需要创建数据库可能会出现数据库名字重名的现象我们可以使用如下代码查询数据库名是否存在存在则删除此数据库。删除有风险请注意if exist(select *from sys.databases where name Mydatabase) drop database Mydatabase2. 创建数据库create database Mydatabase on--数据文件 ( name Mydatabase,--逻辑名称 filename D:\Database\Mydatabase.mdf, --存放路径以及逻辑名称 size 8MB,--文件初始大小 filegrowth 10% --增长率也可以使用MB ) log on--日志文件 ( name Mydatabase_log,--逻辑名称 filename D:\Database\Mydatabase_log.ldf, --存放路径以及逻辑名称 size 8MB,--文件初始大小 filegrowth 10% )还可以使用以下方法创建数据库create database Mydatabase --数据文件和日志文件的信息全部采用默认值3. 建表在建表之前我们需要指定对应的数据库同时不允许存在同名的数据库所以需要进行判断然后删除原先的表仅限学习阶段。use Mydatabase --指定我们要建表的数据库 if exits(select * from sys.objects where name MySchool and type U) drop table Department(1) 创建语法create table 表名 ( 字段名1 数据类型(长度), 字段名2 数据类型(长度) ) --示例 create table Department ( DepartmentID int primary key identity(1,1), --创建部门编号int代表整型primary key代表主键identity(1,1)代表从1开始以1为步长自动增长 DepartmentName varchar(50) not null, --创建部门名称varchar(50)表示长度为50的字符串not null表示不能为空 DepartmentRemark text --创建部门的表述text表示长文本 )(2) 常用字符串类型。char定长例如char(5)无论存储的数据是否达到了5个字节都要占用5个字节的空间。 varchar可变长度例如varchar(5)表示最多占用5个字节。限长8000也可以使用varchar(max)表示最大长度。 text长文本最大长度为2^31-1个字符 nchar,nvarchar,ntext前缀为n表示Unicode数据类型的字符区别于varchar(100)可以存储100个英文字符或者50个中文汉字nvarchar(100)可以存储100个英文字符或者100个中文汉字。(3) 创建表create table [Rank] ( RankID int primary key identity(1,1) RankName varchar(50) not null, RankRemark text ) --创建职级表其中rank为关键字用于对结果集中的行进行排名遇到相同值时排名会跳跃所以我们添加[]表示自定义名字 create table Teacher ( TeacherID int primary key identity(1,1), DepartmentID int references Department(DepartmentID) not null,--references代表外键引用 RankID int references [Rank](RankID) not null, TeacherName varchar(50) not null, TeacherSex varchar(2) default(男) check(TeacherSex 男 or TeacherSex 女) not null --default代表默认字段,check可以规定字段值的约束条件 TeacherBirth datetime not null, TeacherSalary decimal(12,2) check(TeacherSalary 1000 and TeacherSalary50000) not null, TeacherPhone varchar(20) unique not null,--unique表示唯一约束,不能重复 TeacherAddress varchar(100), TeacherAddTime smalldatetime default(getdate()) --datetime和smalldatetime都可以表示时间类型getdate()用于获取系统当前的时间 )--创建老师信息表4. 修改表结构(1) 在表中添加列--语法 alter table 表名 add 列名 数据类型 --示例为教师添加邮箱 alter table Teacher add TeacherMail varchar(100)(2) 在表中删除列--语法 alter table 表名 drop column 列名 --示例删除邮箱 alter table Teacher drop column TeacherMail(3) 改变表中列的数据类型--语法 alter table 表名 alter column 列名 数据类型 --示例 改变邮箱列的数据类型为nvarchar(100) alter table Teacher alter column TeacherMail nvarchar(100)5. 添加删除约束(1) 添加约束--添加主键约束 alter table 表名 add constraint 约束名称 primary key(列名) --添加check约束 alter table 表名 add constraint 约束名称 check(约束表达式) --添加unique约束 alter table 表名 add constraint 约束名称 unique(列名) --添加default约束 alter table 表名 add constraint 约束名称 default 默认值 列名 --添加外键约束 alter table 表名 add constraint 约束名称 foreign key(列名) references 关联表名(关联列表名)(2) 删除约束if exists(select * from sysobjects where name约束名) alter table 表名 drop constraint 约束名; go二、插入数据1. 向部门表中插入数据--标准语法 insert into Department(DepartmentName,DepartmentRamark) values(教育部,......) insert into Department(DepartmentName,DepartmentRamark) values(纪律部,......) --简写语法省略字段名称 insert into Department values(卫生部,学校主管卫生工作的部门) --该写法在给字段赋值时必须保证顺序和数据表结构中字段顺序完全一致 --一次插入多行数据 insert into Department(DepartmentName,DepartmentRemark) select 教育部,负责学生教育工作 union select 纪律部,负责管理学生纪律 union select 宿管部,负责学生住宿管理2. 向职级表插入数据insert into [Rank](RankName,RankRemark) values(初级,能够完成基本工作) insert into [Rank](RankName,RankRemark) values(中级,可以担任管理人员) insert into [Rank](RankName,RankRemark) values(高级,这是领导)3.向教师表插入数据INSERT INTO Teacher (DepartmentID, RankID, TeacherName, TeacherSex, TeacherBirth, TeacherSalary, TeacherPhone, TeacherAddress) VALUES -- 斗破苍穹3人 (1, 1, 萧炎, 男, 1995-06-06, 3800.00, 13800000001, 浙江省乌坦城萧家旧宅), (1, 2, 药老, 男, 1989-11-23, 6500.00, 13800000002, 浙江省乌坦城郊外山洞), (1, 3, 海波东, 男, 1983-03-11, 12000.00, 13800000003, 加玛帝国米特尔家族总部), -- 斗罗大陆4人 (1, 1, 唐三, 男, 1995-11-28, 3600.00, 13800000004, 四川省唐门旧址), (1, 1, 小舞, 女, 1996-03-21, 3700.00, 13800000005, 四川省星斗大森林边缘), (1, 2, 玉小刚, 男, 1988-05-17, 6400.00, 13800000006, 四川省蓝电霸王龙家族), (1, 3, 比比东, 女, 1984-02-27, 12500.00, 13800000007, 四川省武魂殿总部), -- 凡人修仙传3人 (1, 1, 韩立, 男, 1996-07-14, 3900.00, 13800000008, 山东省青牛镇韩家村), (1, 2, 南宫婉, 女, 1990-12-15, 6300.00, 13800000009, 掩月宗大殿), (1, 3, 令狐老祖, 男, 1982-09-08, 12800.00, 13800000010, 黄枫谷后山禁地), -- 纪律部DepartmentID 2—— 10人 -- 斗破苍穹3人 (2, 1, 萧薰儿, 女, 1996-07-15, 3800.00, 13800000011, 浙江省乌坦城萧家后院), (2, 2, 云韵, 女, 1989-12-08, 6600.00, 13800000012, 云南省加玛帝国云岚宗), (2, 3, 美杜莎女王, 女, 1984-05-18, 13500.00, 13800000013, 塔戈尔大沙漠蛇人族神殿), -- 斗罗大陆4人 (2, 1, 戴沐白, 男, 1994-07-09, 3500.00, 13800000014, 天津市星罗帝国旧址), (2, 1, 宁荣荣, 女, 1997-12-05, 3800.00, 13800000015, 浙江省宁波市七宝琉璃宗), (2, 2, 柳二龙, 女, 1990-03-09, 6200.00, 13800000016, 四川省黄金铁三角驻地), (2, 3, 千仞雪, 女, 1985-07-16, 11800.00, 13800000017, 四川省天使神殿), -- 凡人修仙传3人 (2, 1, 墨彩环, 女, 1996-04-22, 3700.00, 13800000018, 越国七玄门旧址), (2, 2, 元瑶, 女, 1990-08-09, 6400.00, 13800000019, 乱星海妙音门), (2, 3, 向之礼, 男, 1983-11-03, 13000.00, 13800000020, 天南修仙界传送阵), -- 宿管部DepartmentID 3—— 10人 -- 斗破苍穹4人 (3, 1, 纳兰嫣然, 女, 1995-10-20, 3700.00, 13800000021, 云南省加玛帝国纳兰家), (3, 1, 小医仙, 女, 1996-02-14, 3600.00, 13800000022, 魔兽山脉山谷小屋), (3, 2, 萧战, 男, 1989-03-17, 6100.00, 13800000023, 浙江省乌坦城萧家大厅), (3, 3, 魂天帝, 男, 1982-12-25, 14500.00, 13800000024, 中州魂殿总部), -- 斗罗大陆3人 (3, 1, 奥斯卡, 男, 1996-10-31, 3650.00, 13800000025, 四川省史莱克学院), (3, 2, 弗兰德, 男, 1987-09-21, 6200.00, 13800000026, 四川省史莱克学院院长室), (3, 3, 唐晨, 男, 1982-09-28, 13200.00, 13800000027, 四川省昊天宗旧址), -- 凡人修仙传3人 (3, 1, 厉飞雨, 男, 1995-05-08, 3800.00, 13800000028, 越国七玄门演武场), (3, 2, 紫灵, 女, 1990-10-11, 6300.00, 13800000029, 乱星海星宫), (3, 3, 大衍神君, 男, 1982-06-19, 13800.00, 13800000030, 大晋国天机阁旧址);4. 查询数据是否插入成功select * from Department select * from [Rank] select * from Teacher三、修改和删除数据1. 修改数据示例--涨工资为每个老师500元工资 update Teacher set TeacherSalary TeacherSalary 500 --指定修改将教师工号为8的工资1000元 update Teacher set TeacherSalary TeacherSalary 1000 where TeacherID 8 --将教育部部门编号已知1所有教师工资低于1万的全部调成一万 update Teacher set TeacherSalary TeacherSalary 10000 where DepartmentID 1 adn TeacherSalary 10000 --将药老地址改为浙江省乌坦城萧家旧宅 update Teacher set TeacherAddress 浙江省乌坦城萧家旧宅 where TeacherName药老 --将韩立工资改为以前的两倍并修改其地址为天道盟落云宗青竹峰 update Teacher set TeacherAddress 天道盟落云宗青竹峰 where TeacherName 韩立2. 删除数据示例--删除教师表中所有数据 delect from Teacher --删除宿管部已知编号为3中工资大于15000的所有老师 delect form Teacher where DepartmentID 3 and TeacherSalary 150003. droptruncatedelete的区别drop table删除表对象其中表数据表结构表对象都进行了删除delete 和 truncate table删除表数据但是表对象以及表结构仍然存在delete和truncate table具体区别delete 1、可以删除表所有数据也可以根据条件删除数据 2、如有自动编号泽删除后继续编号例如delete删除表所有数据之后之前数据的编号是123那么之后新增的数据编号从4开始 truncate 1、只能清空整个表数据不能根据条件删除数据 2、如果有自动编号清空表数据后重新编号例如truncate删除表所有数据之后之前数据的编号是123那么之后新增的数据编号仍然从1开始四、基础查询1. 查询所有行所有列--查询所有部门 select * from Department --查询所有职级 select * from [Rank] --查询所有教师信息 select * from Teacher2. 指定列查询select TeacherName,TeacherSex,TeacherSalary,TeacherPhone from Teacher3. 指定列查询并自定义中文列名select TeacherName 姓名,TeacherSex 性别,TeacherSalary 工资,TeacherPhone 电话 from Teacher4. 查询学校老师所在的地点不需要重复数据select distinct TeacherAddress from Teacher --关键字 distinct 用于返回唯一不同的值去重5. 假设工资涨10%查询原始工资和调整后的工资显示姓名性别月薪和加薪后的月薪select TeacherName 姓名,TeacherSex 性别,TeacherSalary 月薪,TeacherSalary*1.1 加薪后月薪 from Teacher五、条件查询1. SQL中常用的运算符运算符作用用于比较是否相等以及赋值用于比较是否不相等用于比较是否不相等用于比较是否大于用于比较是否小于用于比较是否大于等于用于比较是否小于等于is null用于判断是否为空is not null用于判断是否不为空in用于判断是否在其中like模糊查询between…and…比较是否在两者之间and逻辑与两个条件同时成立则表达式成立or逻辑或两个条件有一个成立则表达式成立not逻辑非前面成立则后面不成立前面不成立则后面成立2. 查询示例--(1)根据指定列姓名性别工资电话查询性别为女的教师信息并自定义中文列名 select TeacherName 姓名,TeacherSex 性别,TeacherSalary 工资,TeacherPhone 电话 from Teacher where TeacherSex 女 --(2)查询月薪大于等于10000的教师信息 select * from Teacher where TeacherSalary 10000 --(3)查询月薪大于等于10000的女教师信息 select * from Teacher where TeacherSalary 10000 and TeacherSex 女 --(4)查询出生年月在1990-1-1之后并且月薪大于等于10000的女教师信息 select * from Teacher where TeacherBirth1990-1-1 and TeacherSalary 女 --(5)查询工资大于15000的教师或者工资大于8000的女教师信息 select * from Teacher where TeacherSalary 15000 or(TeacherSalary8000 and TeacherSex女) --(6)查询月薪在10000到20000之间的教师信息 select * from Teacher where TeacherSalary 10000 and TeacherSalary 20000 select * from Teacher where TeacherSalary between 10000 and 20000 --(7)查询出地址在落云宗或者掩月宗大殿的教师信息 select * from Teacher where TeacherAddress掩月宗大殿 or TeacherAddress天道盟落云宗青竹峰 select * from Teacher where TeacherAddress in(掩月宗大殿,天道盟落云宗青竹峰) --(8)查询所有教师信息并按工资降序排列 --order by 排序asc 正序desc 倒序 select * from Teacher order by TeacherSalary desc --(9)显示所有教师信息按照名字长度进行倒叙排序 select * from Teacher order by len(TeacherName) desc --(10)查询工资最高的10个人的信息 select top 10 * from Teacher order by TeacherSalary desc --(11)查询工资前百分之十的教师信息 select top 10 percent * from Teacher order by TeacherSalary desc --(12)查询没填地址的教师信息 select * from Teacher where TeacherAddress is null --(13)查询地址已经填写的教师信息 select * from Teacher where TeacherAddress is not null --(14)查询所有90后教师信息 select * from Teacher where TeacherBirth 1990-1-1 and TeacherBirth 1999-12-31 select * from Teacher where TeacherBirth between 1990-1-1 and 1999-12-31 select * from Teacher where year(TeacherBirth) 1990 and year(TeacherBirth) 1999 --(15)查询年龄在30-40 之间并且工资在15000-30000 之间的教师信息 select * from Teacher where (year(getdate())-year(TeacherBirth) 30 and year(getdate())-year(TeacherBirth) 40) and (TeacherSalary 15000 and TeacherSalary 30000) select * from Teacher where (year(getdate())-year(TeacherBirth) between 30 and 40) and TeacherSalary between 15000 and 30000 --(16)查询工资比萧炎高的人 select * from Teacher where TeacherSalary(select TeacherSalary from Teacher where TeacherName萧炎) --(17)查询出星座是天蝎座的人的信息10月24日至11月22日 select * from Teacher where (month(TeacherBirth) 10 and DAY(TeacherBirth) 24) or (month(TeacherBirth) 11 and DAY(TeacherBirth) 22) --(18)查询和萧炎地址一样的人 select * from Teacher where TeacherAddress (select TeacherAddress from Teacher where TeacherName 萧炎) --(19)查询出生生肖为羊的人 select * from Teacher where year(TeacherBirth)%1211 --(20)查询所有教师的信息添加一列显示生肖 select TeacherName 姓名,TeacherSex 性别,TeacherSalary 工资,TeacherPhone 电话,TeacherBirth 生日, case when year(TeacherBirth) % 12 4 then 鼠 when year(TeacherBirth) % 12 5 then 牛 when year(TeacherBirth) % 12 6 then 虎 when year(TeacherBirth) % 12 7 then 兔 when year(TeacherBirth) % 12 8 then 龙 when year(TeacherBirth) % 12 9 then 蛇 when year(TeacherBirth) % 12 10 then 马 when year(TeacherBirth) % 12 11 then 羊 when year(TeacherBirth) % 12 0 then 猴 when year(TeacherBirth) % 12 1 then 鸡 when year(TeacherBirth) % 12 2 then 狗 when year(TeacherBirth) % 12 3 then 猪 ELSE end 生肖 from Teacher六、模糊查询模糊查询使用like关键字和通配符结合实现常见通配符如下通配符作用%代表匹配0个字符、1个字符或多个字符。_代表匹配有且只有1个字符。[]代表匹配范围内[^]代表匹配不在范围内--(1)查询姓唐的教师信息 select * from Teacher Where TeacherName like唐% --(2)查询名字中含有宫的教师信息 select * from Teacher where TeacherName like %宫% --(3)查询名字中含有宫或者荣的教师信息 select * from Teacher where TeacherName like %荣% or TeacherName like %宫% --4查询姓唐名字是两个字的人 select * from Teacher where TeacherName like 唐_ select * from Teacher where SUBSTRING(TeacherName,1,1)唐 and LEN(TeacherName)2 --(5)查询最后一个字是儿名字共有三个字的人的信息 select * from Teacher where TeacherName like__儿 select * from Teacher where SUBSTRING(TeacherName,3,1) 儿and len(TeacherName)3 --(6)查询出电话是以138开头的教师信息 select * from Teacher where TeacherPhone like138% --(7)查询电话是138开头第四位可能是5可能是9最后一位是2的人的信息 select * from Teacher where TeacherPhone like 138[5,9]%2 --(8)查询电话是138开头第四位是3-8之间最后一个不是1和2的人的信息 select * from Teacher where TeacherPhone like 138[3,4,5,6,7,8]%[^1,2] select * from Teacher where TeacherPhone like 138[3-8]%[^1-2]七、聚合函数SQL SERVER中聚合函数主要有count求数量 max求最大值 min求最小值 sum求总和 avg求平均值1. 聚合函数举例应用1求教师总人数select COUNT(*) 数量 from Teacher2求最大值求最高工资select MAX(TeacherSalary) 最高工资 from Teacher3求最小时求最小工资select MIN(TeacherSalary) 最低工资 from Teacher4求和求所有教师的工资总和select SUM(TeacherSalary) 工资总和 from Teacher5求平均值求所有教师的平均工资--方案一 select AVG(TeacherSalary) 平均工资 from Teacher --方案二精确到2位小数 select ROUND(AVG(TeacherSalary),2) 平均工资 from Teacher --方案三精确到2位小数 select Convert(decimal(12,2),AVG(TeacherSalary)) 平均工资 from TeacherROUND函数用法round(num,len,[type]) 其中: num表示需要处理的数字len表示需要保留的长度type处理类型(0是默认值代表四舍五入非0代表直接截取) select ROUND(123.45454,3) --123.45500 select ROUND(123.45454,3,1) --123.454006求数量最大值最小值总和平均值在一行显示select COUNT(*) 数量,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资,SUM(TeacherSalary) 工资总和,AVG(TeacherSalary) 平均工资 from Teacher7查询出四川省的教师人数总工资最高工资最低工资和平均工资select 四川省 地区,COUNT(*) 数量,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 ,SUM(TeacherSalary) 工资总和,AVG(TeacherSalary) 平均工资 from Teacher WHERE TeacherAddress like 四川省%8求出工资比平均工资高的人员信息select * from Teacher where TeacherSalary (select AVG(TeacherSalary) 平均工资 from Teacher)9求数量年龄最大值年龄最小值年龄总和年龄平均值在一行显示--方案一 select COUNT(*) 数量, MAX(year(getdate())-year(TeacherBirth)) 最高年龄, MIN(year(getdate())-year(TeacherBirth)) 最低年龄, SUM(year(getdate())-year(TeacherBirth)) 年龄总和, AVG(year(getdate())-year(TeacherBirth)) 平均年龄 from Teacher --方案二 select COUNT(*) 数量, MAX(DATEDIFF(year, TeacherBirth, getDate())) 最高年龄, MIN(DATEDIFF(year, TeacherBirth, getDate())) 最低年龄, SUM(DATEDIFF(year, TeacherBirth, getDate())) 年龄总和, AVG(DATEDIFF(year, TeacherBirth, getDate())) 平均年龄 from Teacher10计算出月薪在10000 以上的男性教师的最大年龄最小年龄和平均年龄--方案一 select 男 性别,COUNT(*) 数量, MAX(year(getdate())-year(TeacherBirth)) 最高年龄, MIN(year(getdate())-year(TeacherBirth)) 最低年龄, SUM(year(getdate())-year(TeacherBirth)) 年龄总和, AVG(year(getdate())-year(TeacherBirth)) 平均年龄 from Teacher where TeacherSex 男 and TeacherSalary 10000 --方案二 select 男 性别,COUNT(*) 数量, MAX(DATEDIFF(year, TeacherBirth, getDate())) 最高年龄, MIN(DATEDIFF(year, TeacherBirth, getDate())) 最低年龄, SUM(DATEDIFF(year, TeacherBirth, getDate())) 年龄总和, AVG(DATEDIFF(year, TeacherBirth, getDate())) 平均年龄 from Teacher where TeacherSex 男 and TeacherSalary 1000011统计出所在地在“四川省地区或浙江省地区”的所有女教师数量以及最大年龄最小年龄和平均年龄--方案一 select 四川省地区或浙江省地区 地区,女 性别,COUNT(*) 数量, MAX(year(getdate())-year(TeacherBirth)) 最高年龄, MIN(year(getdate())-year(TeacherBirth)) 最低年龄, SUM(year(getdate())-year(TeacherBirth)) 年龄总和, AVG(year(getdate())-year(TeacherBirth)) 平均年龄 from Teacher where TeacherSex 女 and (TeacherAddress LIKE 四川省% OR TeacherAddress LIKE 浙江省%) --方案二 select 四川省地区或浙江省地区 地区,女 性别,COUNT(*) 数量, MAX(DATEDIFF(year, TeacherBirth, getDate())) 最高年龄, MIN(DATEDIFF(year, TeacherBirth, getDate())) 最低年龄, SUM(DATEDIFF(year, TeacherBirth, getDate())) 年龄总和, AVG(DATEDIFF(year, TeacherBirth, getDate())) 平均年龄 from Teacher where TeacherSex 女 and (TeacherAddress LIKE 四川省% OR TeacherAddress LIKE 浙江省%)12求出年龄比平均年龄高的人员信息--方案一 select * from Teacher where year(getdate())-year(TeacherBirth) (select AVG(year(getdate())-year(TeacherBirth)) from Teacher) --方案二 select * from Teacher where DATEDIFF(year, TeacherBirth, getDate()) (select AVG(DATEDIFF(year, TeacherBirth, getDate())) from Teacher)2.SQL中常用时间处理函数GETDATE() 返回当前的日期和时间DATEPART() 返回日期/时间的单独部分DATEADD() 返回日期中添加或减去指定的时间间隔DATEDIFF() 返回两个日期直接的时间DATENAME() 返回指定日期的指定日期部分的整数CONVERT() 返回不同格式的时间示例select DATEDIFF(day, 2019-08-20, getDate()); --获取指定时间单位的差值 SELECT DATEADD(MINUTE,-5,GETDATE()) --加减时间,此处为获取五分钟前的时间,MINUTE 表示分钟可为 YEAR,MONTH,DAY,HOUR select DATENAME(month, getDate()); --当前月份 select DATENAME(WEEKDAY, getDate()); --当前星期几 select DATEPART(month, getDate()); --当前月份 select DAY(getDate()); --返回当前日期天数 select MONTH(getDate()); --返回当前日期月数 select YEAR(getDate()); --返回当前日期年数 SELECT CONVERT(VARCHAR(22),GETDATE(),20) --2020-01-09 14:46:46 SELECT CONVERT(VARCHAR(24),GETDATE(),21) --2020-01-09 14:46:55.91 SELECT CONVERT(VARCHAR(22),GETDATE(),23) --2020-01-09 SELECT CONVERT(VARCHAR(22),GETDATE(),24) --15:04:07 Select CONVERT(varchar(20),GETDATE(),14) --15:05:49:330时间格式控制字符串名称日期单位缩写年yearyyyy 或yy季度quarterqq,q月monthmm,m一年中第几天dayofyeardy,y日daydd,d一年中第几周weekwk,ww星期weekdaydw小时Hourhh分钟minutemi,n秒secondss,s毫秒millisecondms八、分组查询--1根据教师所在地区分组统计教师数量教师工资总和平均工资最高工资和最低工资 select left(TeacherAddress,3) 地区,count(*) 人数,sum(TeacherSalary) 工资总和,avg(TeacherSalary) 平均工资,max(TeacherSalary) 最高工资,min(TeacherSalary) 最低工资 from Teacher group by left(TeacherAddress,3) --2根据教师所在地区分组统计教师人数教师工资总和平均工资最高工资和最低工资1985 年及以后出身的教师不参与统计。 select left(TeacherAddress,3) 地区,count(*) 人数,sum(TeacherSalary) 工资总和,avg(TeacherSalary) 平均工资,max(TeacherSalary) 最高工资,min(TeacherSalary) 最低工资 from Teacher where year(TeacherBirth)1985 group by left(TeacherAddress,3) --3根据教师所在地区分组统计教师人数教师工资总和平均工资最高工资和最低工资要求筛选出教师人数至少在2人及以上的记录并且1985年及以后出身的教师不参与统计。 select left(TeacherAddress,3) 地区,count(*) 人数,sum(TeacherSalary) 工资总和,avg(TeacherSalary) 平均工资,max(TeacherSalary) 最高工资,min(TeacherSalary) 最低工资 from Teacher where year(TeacherBirth)1985 group by left(TeacherAddress,3) having COUNT(*) 2九、多表查询1. 笛卡尔乘积select * from Teacher,Department该查询会将Teacher表中所有数据和Department表中的所有数据进行一次排列组合然后形成新的记录。例如Teacher中有30条记录department中有3条则会形成90条记录2. 简单多表查询该查询方式不会查询不符合主外键关系的数据查询教师信息同时显示部门名称select * from Teacher,Department where Teacher.DepartmentIDDepartment.DepartmentID查询教师信息同时显示职级名称select * from Teacher,Rank where Teacher.RankIDRank.RankID查询教师信息同时显示部门名称和职位名称select * from Teacher,Department,Rank where Teacher.DepartmentIDDepartment.DepartmentID and Teacher.RankIDRank.RankID3. 内连接该查询方式不会查询不符合主外键关系的数据查询教师信息同时显示部门名称select * from Teacher inner join Department on Teacher.DepartmentId Department.DepartmentId查询教师信息同时显示职级名称select * from Teacher inner join Rank on Teacher.RankId Rank.RankId查询教师信息同时显示部门名称职位名称select * from Teacher inner join Department on Teacher.DepartmentId Department.DepartmentId inner join Rank on Teacher.RankId Rank.RankId4. 外连接外连接分为三类左外连接以左表为主显示全部数据主外键关系找不到数据的地方用null取代--查询教师信息同时显示部门名称 select * from Teacher left join Department on Teacher.DepartmentID Department.DepartmentID --查询教师信息同时显示职级名称 select * from Teacher left join Rank on Teacher.RankID Rank.RankID --查询教师信息同时显示部门名称职位名称 select * from Teacher left join Department on Teacher.DepartmentID Department.DepartmentID left join Rank on Teacher.RankID Rank.RankID右外连接以右表为主显示全部数据主外键关系找不到数据的地方用null取代—A left join B B right join A--查询教师信息同时显示部门名称 SELECT * FROM Teacher RIGHT JOIN Department ON Teacher.DepartmentID Department.DepartmentID; --查询教师信息同时显示职级名称 SELECT * FROM Teacher RIGHT JOIN Rank ON Teacher.RankID Rank.RankID; --查询教师信息同时显示部门名称职位名称 SELECT Teacher.*, Department.DepartmentName, Rank.RankName FROM Rank RIGHT JOIN ( Teacher RIGHT JOIN Department ON Teacher.DepartmentID Department.DepartmentID ) ON Teacher.RankID Rank.RankID;全连接它会返回左右两张表的所有记录匹配上的行两边的数据都显示左表有但右表没有的右表字段显示 NULL右表有但左表没有的左表字段显示 NULL--查询教师信息同时显示部门名称 SELECT * FROM Teacher FULL JOIN Department ON Teacher.DepartmentID Department.DepartmentID; --查询教师信息同时显示职级名称 SELECT * FROM Teacher FULL JOIN Rank ON Teacher.RankID Rank.RankID; --查询教师信息同时显示部门名称职位名称 SELECT Teacher.*, Department.DepartmentName, Rank.RankName FROM Teacher FULL JOIN Department ON Teacher.DepartmentID Department.DepartmentID FULL JOIN Rank ON Teacher.RankID Rank.RankID;5. 多表查询示例--1查询出浙江地区所有的员工信息要求显示部门名称以及员工的详细资料 select TeacherName 姓名,Teacher.DepartmentId 部门编号 ,DepartmentName 部门名称, TeacherSex 性别,TeacherBirth 生日, TeacherSalary 月薪,TeacherPhone 电话,TeacherAddress 地区 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId where TeacherAddress like 浙江% --2查询出浙江地区所有的员工信息要求显示部门名称职级名称以及员工的详细资料 select TeacherName 姓名,DepartmentName 部门名称,RankName 职位名称, TeacherSex 性别,TeacherBirth 生日, TeacherSalary 月薪,TeacherPhone 电话,TeacherAddress 地区 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId left join [Rank] on [Rank].RankId Teacher.RankId where TeacherAddress like 浙江% --3根据部门分组统计员工人数员工工资总和平均工资最高工资和最低工资。 --提示在进行分组统计查询的时候添加二表联合查询。 select DepartmentName 部门名称,COUNT(*) 人数,SUM(TeacherSalary) 工资总和, AVG(TeacherSalary) 平均工资,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId group by Department.DepartmentId,DepartmentName --4根据部门分组统计员工人数员工工资总和平均工资最高工资和最低工资平均工资在10000 以下的不参与统计并且根据平均工资降序排列。 select DepartmentName 部门名称,COUNT(*) 人数,SUM(TeacherSalary) 工资总和, AVG(TeacherSalary) 平均工资,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 from Teacher left join DEPARTMENT on Department.DepartmentId Teacher.DepartmentId group by Department.DepartmentId,DepartmentName having AVG(TeacherSalary) 10000 order by AVG(TeacherSalary) desc --5根据部门名称然后根据职位名称分组统计员工人数员工工资总和平均工资最高工资和最低工资 select DepartmentName 部门名称,RANKNAME 职级名称,COUNT(*) 人数,SUM(TeacherSalary) 工资总和, AVG(TeacherSalary) 平均工资,MAX(TeacherSalary) 最高工资,MIN(TeacherSalary) 最低工资 from Teacher LEFT JOIN DEPARTMENT on Department.DepartmentId Teacher.DepartmentId LEFT JOIN [Rank] on [Rank].RANKID Teacher.RANKID group by Department.DepartmentId,DepartmentName,[Rank].RANKID,RANKNAME6. 自连接自己连接自己示例 create table Dept ( DeptId int primary key, --部门编号 DeptName varchar(50) not null, --部门名称 ParentId int not null, --上级部门编号 ) insert into Dept(DeptId,DeptName,ParentId) values(1,软件部,0) insert into Dept(DeptId,DeptName,ParentId) values(2,硬件部,0) insert into Dept(DeptId,DeptName,ParentId) values(3,软件研发部,1) insert into Dept(DeptId,DeptName,ParentId) values(4,软件测试部,1) insert into Dept(DeptId,DeptName,ParentId) values(5,软件实施部,1) insert into Dept(DeptId,DeptName,ParentId) values(6,硬件研发部,2) insert into Dept(DeptId,DeptName,ParentId) values(7,硬件测试部,2) insert into Dept(DeptId,DeptName,ParentId) values(8,硬件实施部,2) 如果要查询出所有部门信息并且查询出自己的上级部门查询结果如下 --部门编号 部门名称 上级部门 -- 3 软件研发部 软件部 -- 4 软件测试部 软件部 -- 5 软件实施部 软件部 -- 6 硬件研发部 硬件部 -- 7 硬件测试部 硬件部 -- 8 硬件实施部 硬件部 select A.DeptId 部门编号,A.DeptName 部门名称,B.DeptName 上级名称 from Dept A inner join Dept B on A.ParentId B.DeptId
