1. T-SQL插入篇
- T-SQL创建数据库
- 语法:
CREATE DATABASE 数据库名
ON [PRIMARY]
(
<数据文件参数> [,…n] [<文件组参数>]
)
[LOG ON]
(
<日志文件参数> [,…n]
)
- 实例:
--使用系统默认的参数创建数据库
create database stuDb
go
--使用自定义参数创建数据库
create database students
on primary --主文件组
(
name='students_data', ---主文件逻辑名
filename='d:\data2\students_data.mdf',---主文件物理名称
size=5mb, --主文件初始大小
maxsize=10mb,--主文件增长最大值
filegrowth=10% --主文件增长率
)
log on
(
name='students_log', --日志文件逻辑名
filename='d:\data2\students_log.ldf', --日志文件的物理名称
size=1mb, --日志文件初始大小
filegrowth=1mb --日志文件增长率
)
go
- 语法:
T-SQL创建表
语法:
create table 表名 ( 列名1 数据类型 约束, 列名2 数据类型 约束, 列名3 数据类型 约束 )
实例
--不使用约束创建表 use students create table stuInfo ( stuId int not null, --学号 不能为空 stuName varchar(50) not null,--姓名 不能为空 stuAge int not null, --年龄 不能为空 stuSex char(2) , ---性别 可以为空,"null"可以省略 stuPhone varchar(11), --电话 stuBrith datetime ---出生日期 ) --使用约束时,表的创建 use students create table stuInfo ( stuId int not null primary key identity(1000,1), ---学号,主键约束(primary key),标识列(identity(1,1)) stuName varchar(50) not null unique, ---姓名, 唯一约束(unique) stuAge int not null check(stuage>=18 and stuage<=35), ---年龄,检查约束(大于等于18,小于等于35) stuSex char(2) default('男') check(stuSex='男' or stuSex='女'), ---性别, 默认约束(default) 检查约束 stuCreate datetime default(getdate()) ---日期, 默认约束 getdate()获取系统当前时间 )
(需要注意的是,在新创建的数据库中创建表时,需要先使用use打开数据库,再执行创建表的操作,否则则会创建到默认的数据库(master)中)
插入记录
语法:
insert [into] 表名[(列名1,列名2..)] values (值1,值2...);
实例:
--插入数据,不具有标识列,标识列不需要插入数据 insert stuinfo values(1,'张三',18,'男') ---值与表中列的顺序相同 insert into stuinfo values(2,'李四',19,'女') --标准插入 insert into stuInfo values(5,'赵六',null,null) --给列值插入null insert into stuInfo(stuId,stuName) values(3,'王五');--选取部分列插入 insert into stuinfo(stuName,stusex,stuId) values('妲己','女',4) ---值与部分列名形成映射 --插入数据,具有标识列,标识列不需要插入数据 insert stuinfo values('张三',18,'男') --插入数据,具有标识列、默认值 insert into stuInfo values('jack',18,'女','2017-12-14') insert into stuInfo values('rose',18,'男',2017-12-14) insert into stuInfo values('rose',18,default,default) --默认值 insert into stuInfo values('rose',default,default,default) --默认值
插入多行数据
语法:
--INSERT INTO <表名>(列名) --SELECT <列名> UNION --SELECT <列名>
实例:
insert into stuInfo(stuName,stuAge) select '张三',18 union select '李四',19 union select '王五',17 union select '赵六',18
数据备份—备份表已经存在
- 语法:
INSERT INTO <目标表名>(列名) SELECT <列名> FROM <源表名>;
实例:
insert into stuInfobak(stuName,stuAge,stuSex,stutime) select stuName,stuAge,stuSex,stutime from stuInfo insert into stuInfobak select stuName,stuAge,stuSex,stutime from stuinfo
数据备份—备份表不存在,创建备份表
– 语法:SELECT (列名) INTO <新目标表名> FROM <源表名>;
实例:
select * into stuInfobak from stuInfo select stuId,stuName,stuAge,stuSex,stutime into stuInfobak from stuInfo
2. T-SQL删除篇
- 删除数据库
- 语法:
drop database [数据库名];
- 实例:
drop database students
- 语法:
- 删除表
- 语法:
drop table [表名];
- 实例:
drop table stuInfo;
- 语法:
- 删除表中的约束
- 语法:
alter table 表名 drop constraint [约束名];
- 实例:
alter table stuMarks drop constraint UQ_stuMarks_stuId;
- 语法:
删除表中的记录
语法:
delete [from] 表名 [where 删除条件];
实例:
delete from stumarks ---删除表中所有记录 ---指定删除某一条记录 delete from stuInfo where stuid=105 delete from stuInfo where stuId=101 or stuid=102 delete from stuInfo where stuname='小溪'; delete from stuMarks ---删除表中数据后,标识列不会重新开始,继续增长
语法:
truncate table 表名 == delete from 表名;
实例:
truncate table stuMarks ---删除表中的数据后,标识列会重新开始,不能对用于外键表
3. T-SQL修改篇
- 添加约束
- 语法:
alter table 表名 add constraint 约束名 约束类型 具体说明
- 实例:
--添加主键约束(primary key)
alter table stuInfo add constraint PK_stuInfo_stuId primary key(stuId)
--添加唯一约束(unique)
alter table stuInfo add constraint UQ_stuInfo_stuName unique(stuName)
--添加默认约束(default)
alter table stuInfo add constraint DF_stuInfo_stuSex default('男') for stuSex
--添加检查约束(check)
alter table stuInfo add constraint CK_stuInfo_stuAge check(stuAge>=18 and stuAge<=35)
alter table stuInfo add constraint CK_stuInfo_stuSex check(stuSex='男' or stuSex='女')
- 语法:
修改记录(记录信息)
语法:
update 表名 set 列名=修改值 [where 条件]
实例:
--修改表中stuage列所有记录 update stuInfo set stuAge=20; ---使所有年龄增加1岁 update stuinfo set stuage=stuAge+1 ---编号 101,年龄要小1岁 update stuInfo set stuAge=stuAge-1 where stuId=101 update stuInfo set stuName='小新' where stuId=101 ---修改编号102,姓名和性别 update stuInfo set stuName='小名',stuSex='女' where stuId=102 ---修改编号103和105的时间 update stuinfo set stutime=default where stuid=103 update stuinfo set stutime=getdate() where stuid=105 update stuInfo set stutime='2017-12-15' where stuid=103 or stuid=105 --修改编号103和105的年龄和性别 update stuinfo set stuage=stuage-1,stuSex='男' where stuId=103 or stuid=105
4. T-SQL查询篇
查询表中记录
语法:
select <列名> from 表名 [where 查询条件] [group by 列名 [having 查询条件] ] [order by 列名 <asc 或 desc>];
查询表中所有记录
- 语法:
select <列名> from 表名;
- 实例:
select * from stuInfo; -- * 表示所有列
- 查询表中部分字段的记录
select stuName,stuClass,stuId from stuInfo;
- 根据条件查询记录(查询班级是hopu1702)
- 语法:
select <列名> from 表名 where 查询条件;
实例:
select * from stuInfo where stuClass='hopu1702'; select stuName,stuAddress from stuInfo where stuclass='hopu1702'; select * from stuInfo where stuSex='男'; --查询1701班所有女生 select * from stuinfo where stuClass='hopu1701' and stuSex='女';
取别名
--方式1:as select stuId as '学号',stuName as '姓名',stuAge as '年龄',stuSex as '性别',stuAddress as '地址',stuClass as '班级' from stuinfo --方式2: = select '学号'=stuId,'姓名'=stuName,'年龄'=stuAge,'性别'=stuSex,'地址'=stuAddress,'班级'=stuClass from stuInfo --方式3: select stuId 学号,stuName 姓名,stuAge 年龄,stuSex 性别,stuAddress 地址,stuClass 班级 from stuinfo --混合使用 select stuId as 学号,stuName 姓名,年龄=stuAge from stuinfo
合并列
select stuName+stusex as '姓名和性别' from stuInfo select stuId+stuAge as '学号与年龄求和' from stuInfo --select stuName+stuAge from stuInfo ---数据类型不兼容 加号(+) 1.字符串拼接 2. 数字则是求和
- 查询空行(null)
select * from stuInfo where stuAddress is null --查询地址为空
select * from stuInfo where stuAddress is not null --查询地址不为空
select * from stuInfo where stuAddress is null and stuAge>17
查询前N条记录
- 查询表中前N列(top)
语法:
select top n * from 表名
实例:
select top 3 * from stuInfo --查询表中前3条记录 select top 3 stuName,stuAge from stuInfo --查询表中前3条记录 ---查询表中前30%的记录 (percent) select top 30 percent * from stuInfo
排序
语法:
select * from 表名 [where 查询条件] order by 列名 [asc 或 desc] asc是升序(order by默认为升序)/desc是降序
实例:
升序: select * from stuInfo order by stuAge asc ---asc可以省略,默认排序为升序 降序: select * from stuInfo order by stuAge desc ---desc 不可以省略 --对hopu1702班学生根据年龄进行升序排序 select * from stuInfo where stuClass='hopu1702' order by stuAge --根据多个条件进行排序(首先根据年龄排升序,学号排降序) select * from stuInfo where stuClass='hopu1702' order by stuAge asc,stuId desc
模糊查询
语法:
select * from 表名 where 列名 like 匹配字符条件 select * from stuInfo where stuName like '张' --等同于 select * from stuInfo where stuname='张'
实例:
---查询表中姓‘张’学生信息 (% 任意长度的字符串) select * from stuInfo where stuName like '张%' --查询表中姓名包含"三"的学生信息 select * from stuInfo where stuName like '%三%' --查询表中姓名以"三"结尾的学生信息 select * from stuInfo where stuName like '%三' --查询表中姓"张"的学生信息,姓名一共两个字 (_表示一个字符) select * from stuInfo where stuName like '张_' --查询表中姓名只有两个字信息 select * from stuInfo where stuName like '__' --查询姓'张'的信息,名字是'三'和'无'的学生信息([] 括号中所指定范围内的一个字符) select * from stuInfo where stuName like '张三' or stuname like '张无' select * from stuInfo where stuName like '张[三无忌]' --张三 张无 张忌 --查询班级's170'开头 [0-9] 表示从0到9 [a-z] 表示字母a-z select * from stuInfo where stuGrade like 's170[0123456789]' select * from stuInfo where stuGrade like 's170[0-9]' --查询班级不包含's1701'和‘1702’ ([^] 不在括号中所指定范围内的一个字符) select * from stuInfo where stuGrade like 's170[^12]' ---查询年龄在20到30之间(between..and.. 在某个范围之间) select * from stuInfo where stuAge>=20 and stuAge<=30 select * from stuInfo where stuAge between 20 and 30 --查询某一列中内容与所列出的内容列表匹配的记录(in) select * from stuInfo where stuGrade in ('s1701','s1703','s1705') select * from stuInfo where stuGrade not in ('s1701','s1703','s1705')
- 过滤重复记录
- 语法:
select distinct 列名 from 表名
- 实例:
--查询有多少科目
select distinct ExamName from stuMarks
- 语法:
聚合函数
常用聚合函数:
SUM() 求和; MAX() 统计最大值; MIN() 统计最小值; COUNT() 统计人数;
实例:
--计算所有学生总分,求和(sum(列名)) select sum(score) as '总分' from stuMarks --计算出学号为100的学生总成绩 select sum(score) from stuMarks where stuid=100 --计算所有成绩中最高分(max(列名)),最低分(min(列名)) select max(score) as '最高分',min(score) as '最低分' from stuMarks --计算出所有成绩平均分(avg(列名)) select avg(score) as '平均分' from stuMarks --统计一共有多少名学生 (count(*)) select count(*) '统计人数' from stuInfo --统计及格人数 select count(*) '及格人数' from stuMarks where score>=60
分组查询
语法:
select 聚合函数/分组列名 from 表名 [where 条件筛选] group by 列名 [having 条件筛选] [order by 列名] where和having对比: 在分组之前用where ,分组之后使用having (where---group by---having)
实例:
----统计各个班级的人数 --select count(*) as 's1701人数' from stuInfo where stuGrade='s1701' --select count(*) as 's1702人数' from stuInfo where stuGrade='s1702' --select count(*) as 's1703人数' from stuInfo where stuGrade='s1703' --select count(*) as 's1704人数' from stuInfo where stuGrade='s1704' --select count(*) as 's1711人数' from stuInfo where stuGrade='s1711' select count(*) '统计人数',stuGrade from stuInfo group by stuGrade --统计性别分别有多少 select count(*),stuSex from stuInfo group by stuSex ----统计各个班级的人数,只显示人数至少2人班级 select count(*),stugrade from stuInfo group by stuGrade having count(*)>=2 ---统计各个班级女生人数,只显示人数至少2人班级 select count(*) 人数,stuGrade from stuInfo where stuSex='女' group by stuGrade having count(*)>=2 ---统计各个班级女生人数,只显示女生人数至少2人班级,按降序进行排列 select count(*),stuGrade from stuInfo where stuSex='女' group by stuGrade having count(*)>=2 order by count(*) desc
内联查询
- 方法1:
- 语法:
select <列名> from 表1 inner join 表2 on 联接条件
实例:
--查询显示: 姓名、性别、年龄、班级名称 select stuName,stuSex,stuAge,gName from stuInfo inner join stuGrade on stuInfo.gid=stuGrade.gId ----查询显示: 姓名、性别、年龄、班级编号、班级名称 --select stuname,stuSex,stuAge,gId,gname from stuInfo inner join stuGrade on stuGrade.gId=stuInfo.gId ---如果有结果集(虚拟表)中有重复列名,则需要明确指明属于哪个一个表 select stuName,stuSex,stuAge,gname,stuGrade.gId from stuInfo inner join stuGrade on stuGrade.gId=stuInfo.gId --可以给每个列名指定表名 select stuInfo.stuName,stuInfo.stuSex,stuInfo.stuAge,stuGrade.gname,stuGrade.gId from stuInfo inner join stuGrade on stuGrade.gId=stuInfo.gId ---简写: 使用别名 select s1.stuName,s1.stuSex,s1.stuAge,s2.gId,s2.gName from stuInfo s1 inner join stuGrade s2 on s1.gId=s2.gId
- 方法2:
- 语法:
select <列名> from 表1,表2 where 联接条件
- 实例:
----查询显示: 姓名、性别、年龄、班级编号、班级名称
select * from stuInfo,stuGrade where stuInfo.gId=stuGrade.gId
select stuName,stuSex,stuAge,stuInfo.gId,gName from stuInfo,stuGrade where stuInfo.gId=stuGrade.gId
- 复杂联接查询
- 语法一:
select 列名 from 表1,表2,表3 where 联接条件1 and 联接条件2
select stuName,subName,score from stuInfo,stuScore,stuSubject where stuInfo.stuId=stuScore.stuId and stuSubject.subId=stuScore.subId
- 语法二:
select 列名 from 表1 inner join 表2 on 条件表达式1 inner join 表3 on 条件表达式2
select stuName,subName,score from stuInfo inner join stuScore on stuInfo.stuId=stuScore.stuId inner join stuSubject on stuSubject.subId=stuScore.subId
- 实例:
---查询1701班每名学生,各个科目,考试分数,按照降序排列
select gName,stuName,subName,score from stuGrade,stuInfo,stuScore,stuSubject
where stuGrade.gId=stuInfo.gId
and stuInfo.stuId=stuScore.stuId
and stuSubject.subId=stuScore.subId
and gName='hopu1701'
order by score desc
- 语法一:
外部联接查询
- 左联接查询
注意: 左联接查询以左表为主表,需要显示主表中所有信息,右表为匹配表(次表)
- 语法:
select 列名 from 表1 left join 表2 on 联接条件
- 实例:
--查询所有学生考试信息,显示姓名,分数,如果没有成绩则显示null
select stuName,subId,score from stuinfo left join stuScore on stuInfo.stuId=stuScore.stuId;
- 右联接查询
注意: 左联接查询以右表为主表,左表为匹配表(次表)
- 语法:
select 列名 from 表1 right join 表2 on 联接条件
- 实例:
select stuName,subId,score from stuScore right join stuinfo on stuInfo.stuId=stuScore.stuId
- 交叉联接(cross join)
- 实例:
select * from stuInfo cross join stuGrade
集合运算(UNION)
过滤重复记录
--过滤重复记录 select * from stuScore where score>80 union select * from stuScore where score>70 --不会过滤重复记录 select * from stuScore where score>80 union all select * from stuScore where score>70
交集运算(intersect)
--显示重复记录 select * from stuScore where score>80 intersect select * from stuScore where score>70
减集运算(except)
--比较两个结果集,除去交集部分,在显示第一个结果集的数据 select * from stuScore where score>80 except select * from stuScore where score>70
- 左联接查询
此文章由博主个人整理,包含错误的地方请多多指点,也可与博主联系,探讨相关技术问题。
博主Email:charles@secchina.net
备用Email:2638385556@qq.com
博客地址:blog.csdn.net/jindaoer