T-SQL

最新推荐文章于 2024-09-09 09:47:53 发布

CharlesBlog

最新推荐文章于 2024-09-09 09:47:53 发布

阅读量321

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/JinDaoer/article/details/78887402

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

CharlesBlog

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
T-SQL

1. T-SQL插入篇T-SQL创建数据库语法： CREATE DATABASE 数据库名 ON [PRIMARY] ( <数据文件参数> [，…n] [<文件组参数>] ) [LOG ON] ( <日志文件参数> [，…n] ) 实例： --使用系统默认的参数创建数据库 create database stuDb go --使用自定义参数创建
复制链接

扫一扫

专栏目录