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 |
插入部分列
插入部分列,允许为NULL的列可以不插入。
insert into TblStudent(tSName,tSgender,tsage) |
向自动编号列插入值
默认情况下,不允许向自动编号列插入值。但是可以通过启动“手动插入自动编号列”功能向自动编号列插入值。
--启动某个表的“自动编号列”手动插入值的功能 |
字符串前面带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特点:
- truncate语句不能跟where条件(无法根据条件来删除,只能删除全部数据)。
- 自动编号恢复到初始值。
- 使用truncate删除表中所有数据要比delete效率高得多。
- 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规范,删除规则,改成级联,点击保存。
注意:
- 创建约束时,如果表中已经存在“非法数据”(与约束不一致的数据),那么此时是无法成功创建约束的,需要先将表中的数据做更改,然后才能创建约束。
- 主键约束和唯一约束的区别,主键约束不允许重复并且不允许为空,唯一约束不允许重复,但是允许有一个空值。
通过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 |
创建表的同时就为表增加约束
create table Employees |
数据检索
查询所有行所有列
*表示显示所有列,查询语句没有加where条件表示查询所有行。
select * from TblStudent |
只查询表中的部分列
select tsid, tsname, tsgender from TblStudent |
根据条件,只查询部分行
select * from TblStudent where tsclassId = 5 |
为查询结果中的列起列名
select |
单独使用select
并不是说select必须配合from一起来使用,可以单独使用select。
select |
distinct关键字
distinct关键字,针对已经查询出的结果,然后去除重复。
select distinct * from TblStudent |
distinct是对查询出的整个结果集进行重复数据处理,而不是针对某一个列。
order by排序
按照年龄,降序排序。
select * from TblStudent order by tsage desc --降序排序 |
按照年龄,升序排序。
select * from TblStudent order by tsage asc --升序排序 |
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 |
MAX求最大值
计算年龄最大的。
select max(age) from NewPerson |
MIN求最小值
计算年龄最小的。
select min(age) from NewPerson |
AVG求平均值
计算平均值avg
select avg(age*1.0) from NewPerson |
聚合函数的一些问题
- 聚合函数不统计空值。
- 如果使用聚合函数的时候,没有手动group by分组,那么聚合函数会把整个表中的数据作为一组来统计。
select * from TblStudent |
条件查询
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只是写法不同 |
对于in或者or查询,如果查询中的条件是连续的几个数字,最好用>=、<=或者between…and,不要使用or或者in。可以提高效率。
select * from TblStudent where tsClassId>=3 and tsClassId<=5 |
模糊查询
like表示模糊查询。
通配符
_:表示任意的单个字符
--姓张,两个字 |
%:匹配任意多个任意字符
--姓张,无论姓名字数 |
[ ]:表示筛选,范围
--中间是数字的 |
转义:通配符放到[]中就转义了,就不会认为是通配符了。
--查询出姓名包含%的人 |
另外,还可以自己制定转义符。
--自己指定一个转义符/ |
注意:like 'a%' 与 like '%a' 的区别,前者效率比后者效率高,因为使用了索引。
空值处理
null值无法使用=和<>来进行比较。判断null值必须使用is null或者is not null。
select * from TblStudent where tsage is not null |
任何值与null进行计算,得到的结果还是null。
select 2000 + null |