数据库开发（二）T-SQL的基本使用

作者 Zhendong Ho 日期 2019-04-14
SQL Server
数据库开发（二）T-SQL的基本使用

SQL语句

SQL全名是结构化查询语言（Structured Query Language），是关系数据库管理系统的标准语言。Sybase与Microsoft对标准SQL做了扩展，称为：T-SQL（Transact-SQL）。

SQL主要分为DDL（数据定义语言）、DML（数据操作语言）和DCL（数据库控制语言）。

Insert语句

基本语法

insert into 表名(列1,列2,列3) values(值1,值2,值3)

插入非自动编号列

自动编号列，默认就会自动增长，所以不需要（默认情况下也不能向自动编号列插入值）。

insert into Departments(DepartmentName) values('研发部')

省略列名插入

如果向表中的所有列（除了自动编号外）都要插入值，那么可以省略列名，同时保证后面的值列表中的顺序必须与表中列的顺序一致。

insert into Employees
values('13031507', 'Zhendong', 'true', '2013-7-1', 24, '广州市', '13800138000', '10120', 'xxx@mail.com')

插入部分列

插入部分列，允许为NULL的列可以不插入。

insert into TblStudent(tSName,tSgender,tsage)
values('Zhendong', '男', 24)

向自动编号列插入值

默认情况下，不允许向自动编号列插入值。但是可以通过启动“手动插入自动编号列”功能向自动编号列插入值。

--启动某个表的“自动编号列”手动插入值的功能
SET IDENTITY_INSERT TbClass ON
insert into TblClass(tClassId,tClassName)
values(500,'A班')
SET IDENTITY_INSERT TbClass OFF

字符串前面带N

在SQL语句中的直接写的字符串中，如果包含中文，一定要在字符串前面加N，否则插入后可能会乱码（与数据库排序规则有关）。

insert into Table_2 values(N'史蒂夫')

Update语句

基本语法

update 表名 set 列1 = 新值1, 列2 = 新值2, ... where 条件

打开和关闭查询结果窗口

打开和关闭查询结果窗口：ctrl + R。

更新语句

update TblStudent set tsAge = tsAge - 1, tsname = tsname + '（男）' where tsgender = '男'

注意：update语句，如果不加where条件，那么表示对表中所有的数据都进行修改，所以一定要加where条件。

删除数据语句

Delete语句

基本语法

delete from 表名 where ...

注意：delete语句，如果不加where条件，表示将表中所有数据删除。加where条件后，会按照where条件进行删除。另外，使用delete语句删除后，自动编号并没有恢复到默认值，仍然继续编号。

Truncate语句

如果确实要删除表中全部数据，那么建议用truncate。

truncate table 表名

truncate特点：

  1. truncate语句不能跟where条件（无法根据条件来删除，只能删除全部数据）。
  2. 自动编号恢复到初始值。
  3. 使用truncate删除表中所有数据要比delete效率高得多。
  4. truncate删除数据，不触发delete触发器。

通过设计器实现约束

常用约束

非空约束。

主键约束（PK）primary key constraint，唯一且不为空。

唯一约束（UQ）unique constraint，唯一，允许为空，但只能出现一次。

默认约束（DF）default constraint，默认值。

检查约束（CK）check constraint，范围以及格式限制。

外键约束（FK）foreign key constraint，表关系。

设计器实现约束

  • 主键约束、非空约束，可以在表设计器中直接修改后保存即可。
  • 唯一约束，在设计器中，右键，索引/键，添加，类型改为唯一键，选择唯一列EmpEmail（ASC），名称改为UQ_Employees_EmpEmail，点击关闭后保存。
  • 检查约束，在设计器中，右键，CHECK约束，添加，名称改为CK_Employees_EmpAge，表达式中填写“EmpAge>=18 and EmpAge<=60”，关闭后保存。
  • 默认约束，在设计器中，选中列EmpGender，常规，默认值或绑定，填写’男’，保存。
  • 外键约束，在外键表设计器中，右键，选择关系，添加，点击表和列规范，选择外键表和主键表以及对应的列，点击关闭后保存。
  • 当主键表中的记录在外检表中有引用的时候，无法删除主键表的记录（也无法删除主键表）。只有在外键表中没有任何引用的时候才能删掉。
  • 如果希望删除主键表中的记录，同时删除外键表引用的所有记录，则需要修改外键表的约束。选择外键表，右键，关系，INSERT和UPDATE规范，删除规则，改成级联，点击保存。

注意：

  1. 创建约束时，如果表中已经存在“非法数据”（与约束不一致的数据），那么此时是无法成功创建约束的，需要先将表中的数据做更改，然后才能创建约束。
  2. 主键约束和唯一约束的区别，主键约束不允许重复并且不允许为空，唯一约束不允许重复，但是允许有一个空值。

通过T-SQL实现约束

手动删除一列

删除EmpAddress列。

alter table Employees drop column EmpAddress

手动增加一列

增加一列EmpAddr nvarchar(1000)。

alter table Employees add EmpAddr nvarchar(1000)

修改列的数据类型

修改一下EmpEmail的数据类型varchar(200)。

alter table Employees alter column EmpEmail varchar(200)

增加主键约束

为EmpId增加一个主键约束。

alter table Employees add constraint PK_Employees_EmpId primary key(EmpId)

增加非空约束

为EmpName增加一个非空约束，修改列的方式。

alter table Employees alter column EmpName varchar(50) not null

增加唯一约束

为EmpName增加一个唯一约束。

alter table Employees add constraint UQ_Employees_EmpName unique(EmpName)

增加默认约束

为EmpGender增加一个默认约束，默认为“男”。

alter table Employees add constraint DF_Employees_EmpGender default('男') for EmpGender

增加检查约束

为EmpGender增加一个检查约束，要求性别只能是“男”或“女”。

alter table Employees add constraint CK_Employees_EmpGender check(EmpGender='男' or EmpGender='女')

为EmpAge增加一个检查约束，要求年龄必须在0-120岁之间。

alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)

增加外键约束

为员工表增加外键约束（部门表为主键表，DepId）。

alter table Employees add constraint FK_Employees_Department foreign key(EmpDepId) references Department(DepId)

删除约束

一次性删除多个（一个）约束。

alter table Employees drop constraint FK_Employees_Department, CK_Employees_EmpAge, CK_Employees_EmpGender, DF_Employees_EmpGender, UQ_Employees_EmpName

通过一条代码增加多个约束

alter table Employees add
constraint FK_Employees_Department foreign key(EmpDepId) references Department(DepId),
constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120),
constraint CK_Employees_EmpGender check(EmpGender='男' or EmpGender='女')

创建表的同时就为表增加约束

create table Employees
(
    EmpId int identity(1, 1) primary key,
    EmpName varchar(50) not null unique check(len(EmpName)>2),
    EmpGender char(2) default('男'),
    EmpAge int check(EmpAge>0 and EmpAge<120),
    EmpEmail varchar(100) unique,
    EmpAddress varchar(500) not null,
    EmpDepId int foreign key references Department(EmpId) on delete cascade--允许级联删除
)

create table Department
(
    DepId int identity(1, 1) primary key,
    DepName varchar(50) not null unique
)

数据检索

查询所有行所有列

*表示显示所有列，查询语句没有加where条件表示查询所有行。

select * from TblStudent

只查询表中的部分列

select tsid, tsname, tsgender from TblStudent

根据条件，只查询部分行

select * from TblStudent where tsclassId = 5

为查询结果中的列起列名

select
    tsid as 学生编号,
    tsname as 学生姓名,
    tsgender as 性别
from TblStudent

/*省略as的方式，或用引号括起来*/
select
    tsid '(学生编号)',
    tsname 学生姓名,
    tsgender 性别
from TblStudent

/*列名在前面*/
select
    学生编号=tsid,
    学生姓名=tsname,
    性别=tsgender
from TblStudent

单独使用select

并不是说select必须配合from一起来使用，可以单独使用select。

select
    当前系统时间 = getdate()

distinct关键字

distinct关键字，针对已经查询出的结果，然后去除重复。

select distinct * from TblStudent
--看不出效果，因为查询出的结果没有重复数据（ID不重复）

select distinct tsname, tsgender, tsaddress from TblStudent
--去重后的数据，重复记录必须是所有列都完全一样

distinct是对查询出的整个结果集进行重复数据处理，而不是针对某一个列。

order by排序

按照年龄，降序排序。

select * from TblStudent order by tsage desc --降序排序

按照年龄，升序排序。

select * from TblStudent order by tsage asc --升序排序
select * from TblStudent order by tsage --默认就是升序排序

top关键字

top获取前几条数据，top一般都与order by连用。

查询数学成绩最高的前五名。

select top 5 * from TblScore order by tMath desc

如果top后跟的不是数字，而是一个表达式，一定要用()把表达式括起来。

select top (2*2) * from TblScore order by tMath desc

选择前百分之三十五条，会查询出4条数据，向上取整。

select top 35 percent * from TblScore order by tMath desc

聚合函数

MAX(最大值)、MIN(最小值)、AVG(平均值)、SUM(和)、COUNT(数量：记录的条数)。

SUM求总和

统计出所有人的年龄的总和。

select sum(age) as 年龄总和 from NewPerson

COUNT求记录条数

统计出当前表中一共有多少条记录。

select count(*) from NewPerson

计算平均年龄。

select
	平均年龄=(select sum(age) as 年龄总和 from NewPerson)*1.0/(select count(*) from NewPerson)

MAX求最大值

计算年龄最大的。

select max(age) from NewPerson

MIN求最小值

计算年龄最小的。

select min(age) from NewPerson

AVG求平均值

计算平均值avg

select avg(age*1.0) from NewPerson

聚合函数的一些问题

  1. 聚合函数不统计空值。
  2. 如果使用聚合函数的时候，没有手动group by分组，那么聚合函数会把整个表中的数据作为一组来统计。
select * from TblStudent
select count(tsid) from TblStudent
select avg(tsage) from TblStudent --avg()也是不统计空值的
select sum(tsage) from TblStudent --sum()对于null值，认为是0

条件查询

select…from…where…

select 列 from 表 where 条件。

查询没有及格的学生的学号。

select tsid from TblScore where tEnglish<60 or tMath<60

查询年龄在20-30岁之间的男同学，包含20和30。

select * from MyStudent where fage>=20 and fage<=30 and fgender='男'

between…and…

在…之间，闭区间，包含两个端点值。

查询math成绩在80-90分之间的所有学生。

select * from TblScore where tMath between 80 and 90

in和or

查询班级id为1、2、3的所有学生。

select * from TblStudent where tsClassId=3 or tsClassId=4 tsClassId=5 --or与in只是写法不同
select * from TblStudent where tsClassId in (3,4,5) --in比or看起来更加简便

对于in或者or查询，如果查询中的条件是连续的几个数字，最好用>=、<=或者between…and，不要使用or或者in。可以提高效率。

select * from TblStudent where tsClassId>=3 and tsClassId<=5

模糊查询

like表示模糊查询。

通配符

_：表示任意的单个字符

--姓张，两个字
select * from MyStudent where fname like '张_'
--姓张，三个字
select * from MyStudent where fname like '张__'

%：匹配任意多个任意字符

--姓张，无论姓名字数
select * from MyStudent where fname like '张%'
--姓张，两个字（与_实现效果一样）
select * from MyStudent where fname like '张%' and len(fname)=2

[ ]：表示筛选，范围

--中间是数字的
select * from TblStudent where tsname like '张[0-9]妹'
--中间是字母的
select * from TblStudent where tsname like '张[a-z]妹'
--中间是数字或者字母的
select * from TblStudent where tsname like '张[a-z0-9]妹'
--中间不是数字的任意字符
select * from TblStudent where tsname like '张[^0-9]妹'

转义：通配符放到[]中就转义了，就不会认为是通配符了。

--查询出姓名包含%的人
select * from TblStudent where tsname like '%[%]%'

另外，还可以自己制定转义符。

--自己指定一个转义符/
--查询出名字中包含]的人
select * from TblStudent where tsname like '%/]%' ESCAPE '/'
--查询出名字中包含[的人
select * from TblStudent where tsname like '%/[%' ESCAPE '/'
--查询出名字中包含[...]的人
select * from TblStudent where tsname like '%/[%/]%' ESCAPE '/'

注意：like 'a%' 与 like '%a' 的区别，前者效率比后者效率高，因为使用了索引。

空值处理

null值无法使用=和<>来进行比较。判断null值必须使用is null或者is not null。

select * from TblStudent where tsage is not null
select * from TblStudnet where tsage is null

任何值与null进行计算，得到的结果还是null。

select 2000 + null
FEATURED TAGS
SQL Server
FRIENDS