sql-server笔记-sql
时间：2021-07-01 10:21:17
帮助过：16人阅读
快捷键：关闭显示结果：ctrl+R
--一、创建表，删除表
/*--创建表
--格式：
create table 表名
(
字段名 数据类型 [约束条件(identity(1,1);primary key)],
字段名2 数据类型 [约束条件]
)
*/
/*
--删除数据库和表
drop 数据库名
drop table 表名
*/
/*
--创建table学生表
create table TblStudent
(
tSId int identity(1,1) primary key,
tSName nvarchar(50) not null,
tSGender nchar(1),
tSAge int,
tSBirthday datetime,
tSCardId VARCHAR(18),
tSClassId INT
)
*/
/*
--创建table教室表
create table TblClass
(
tClassId int identity(1,1) primary key,
tClassName nvarchar(50)
)
*/
/*
--创建table成绩表
create table TblScore
(
tScoreId int identity(1,1) primary key,
tSid int not null,
tEnglish float,
tMath float
)
*/
use HeiMal3
--创建table教师表
create table Tblteacher
(
tTId int identity(
1,
1)
primary key,
tTName nvarchar(
50)
not null,
tTGender nchar(
1),
tTAge int,
tTSalary money,
tTBirthday datetime,
tTJionDate datetime
)
/*
--创建部门表
create table Departments
(
DepID int identity(1,1) primary key,
DepName nvarchar(50) null
)
*/
/*
--创建员工表
--<员工表>:员工号id,身份证号,姓名,性别,入职日期,年龄,地址,电话.所属部门,email
create table Employees
(
EmpID int identity(1,1) primary key,
EmpIDCard varchar(18) not null,
EmpName nvarchar(50) null,
EmpGender bit not null,
EmpJionDate datetime,
EmpAge int,
EmpAddress nvarchar(300),
EmpPhone varchar(100),
Empmaill varchar(100),
DeptID int not null,
)
*/
--创建table人表
create table TblPerson
(
autoId int identity(
1,
1)
primary key,
uName nvarchar(
10),
age int,
height int,
gendder bit
)
--二、插入表
--格式：
--insert into 表名(列1，列2，列3) values（值1，值2，值3）
--连续插入多行
--insert into 表名(列1，列2，列3) values(值1，值2，值3),(值1，值2，值3),(值1，值2，值3)
--向班级表插入一条记录
--自动编号列，默认就会自动增长，所以不需要（默认情况下也不能向自动编号列插入值）
/*
insert into TblClass(tClassName) values (‘.net黑马一期‘)
--查询显示出来
select * from TblClass
*/
/*
--向学生表插入一条记录
insert into
TblStudent(tSName,tSGender,tSAge,tSBirthday,tSCardId,tSClassId)
values(‘熊丽‘,‘女‘,‘北京市海定区‘,16,‘1998-5-5‘,‘123456789654123698‘,1)
*/
/*
insert into
TblStudent(tSName,tSGender,tSAddress,tSAge,tSBirthday,tSCardId,tSClassId)
values(‘刘天龙‘,‘男‘,‘北京海钉区‘,17,‘1997-5-5‘,‘12345784663135‘,1)
insert into
TblStudent
values(‘刘天龙12‘,‘男‘,‘北京海钉区‘,17,‘1997-5-5‘,‘12345784663135‘,1)
select * from TblStudent
insert into TblStudent(tSName,tsgender,tSAge)
values(‘石荣‘,‘女‘,15)
*/
/*
--向自动编号插入值（插入不了）
insert into TblClass(tClassId,tClassName)
values(500,‘.net黑马二期‘)
--方法
--向自动编号列插入值
--启动某个表的“自动编号列”手动插入值的功能
set identity_insert TblClass on
insert into TblClass(tClassId,tClassName)
values(500,‘.net黑马二期‘)
set identity_insert TblClass off
select * from TblClass
*/
insert into TblClass(tClassName)
values(
‘.net黑马三期‘)
--字符串后面有中文，则前面加N
insert into TblClass(tClassName)
values(N
‘.net黑马四期‘)
--三、更新列表
--格式:
--update 表名 set 列=新值，列2=新值2，......where 条件
use HeiMal3
select * from TblStudent
--如果不加where 条件，那么表示对表中所有的数据都进行修改，所以一定要加where条件
update TblStudent
set tSAge
=tSAge
-1,tSName
=tSName
+‘(女)‘ where tSGender
=‘女‘
--update 表名 set age=18 where name=‘王灿‘or age<25
--update 表名 set age=30 where (age>20and age<30) or age=50
--四、删除数据语句：
--delete from 表名 where ......
--delete 语句如果不加where 条件，表示将表中所有的数据都删除，加了where条件后，只会按照where条件进行删除
select * from TblStudent
--删除table学生表，不需要加在delete 后面 加 * 无条件进行删除,只有查询的时候带* 号
delete from TblStudent
insert into
TblStudent
values(
‘刘天龙12‘,
‘男‘,
‘北京海钉区‘,
17,
‘1997-5-5‘,
‘12345784663135‘,
1)
select * from TblStudent
--删除所有性别为‘女‘，同事年龄小于20岁的
delete from TblStudent
where tSGender
=‘女‘ and tSAge
<20
--delete只是删除数据,表还在,和 drop不同
--删除表中的全部数据：
--1.delete from 表
--2.truncate table 表
--如果确定要删除表中全部数据，那么建议使用truncate
--truncate特点：
--1>truncate 语句不能跟where条件（无法根据条件进行删除，只能全部删除数据）
--2>同时自动编号恢复到初始值。delete 不能恢复到初始值
--3>使用truncate 删除表中所有数据比delete的效率高
--4>truncate 删除数据，不触发delete触发器。
select * from Tblteacher
/*
--向Tblteacher插入数据
insert into Tblteacher(tTName,tTGender,tTAge,tTSalary,tTBirthday,tTJionDate)
values(‘苏坤‘,‘男‘,18,10060,‘1990-09-09‘,‘2010-12-15‘)
insert into Tblteacher(tTName,tTGender,tTAge,tTSalary,tTBirthday,tTJionDate)
values(‘王二‘,‘男‘,18,10060,‘1990-09-09‘,‘2010-12-15‘),
(‘李四‘,‘男‘,18,10060,‘1990-09-09‘,‘2010-12-15‘),
(‘苏折坤‘,‘男‘,18,10060,‘1990-09-09‘,‘2010-12-15‘),
(‘苏大坤‘,‘男‘,18,10060,‘1990-09-09‘,‘2010-12-15‘),
(‘李梅‘,‘女‘,19,30060,‘1995-09-09‘,‘2015-12-15‘)
*/
--1.使用insert into 向TblTeacher表插入2条数据
select * from Tblteacher
insert into Tblteacher
values(
‘刘琦‘,
‘男‘,
30,
150000,
‘1983-10-10‘,
‘2012-5-8‘)
--2.向TblPerson表中插入2条数据.
select * from TblPerson
insert into TblPerson
values(
‘百川儿‘,
21,
175,
1),(
‘小李‘,
22,
170,
0)
--3.给TblScore中的studentId是1的英语成绩加10分
select * from TblScore
insert into TblScore
values(
1,
96,
85),(
2,
93,
88),(
1,
98,
88)
update TblScore
set tEnglish
=tEnglish
+10 where tSid
=1
--3.1给TblScore中的studentId是1的英语成绩加10分,分数不能超过100.
--分数超过100的,都设置为100
--3.2给分数没有超过100的,每人加10分
select * from TblScore
update TblScore
set tEnglish
=100 where(tEnglish
+10>100 or tEnglish
+10=100)
update TblScore
set tEnglish
=tEnglish
+10 where tEnglish
+10<100
--4.所有男童鞋的年龄减1岁
select * from TblStudent
update TblStudent
set tSAge
=tSAge
-1 where tSGender
=‘男‘
--5.删除工资大于2000的老师
select * from Tblteacher
delete from Tblteacher
where tTSalary
>20000
--6.删除表,把自动增长列的值还原成种子(还原成默认值，最开始的状态，从1开始，每增加一次加1)
truncate table TblTeacher
--四、建立约束
--创建部门表
create table Department
(
Depid int identity(
1,
1),
DepName varchar(
50)
)
--创建员工表
create table Employees
(
EmpId int identity(
1,
1),
EmpName varchar(
50),
EmpGender char(
2),
EmpAge int,
Empmail varchar(
100),
EmpAddress varchar(
500),
)
select * from Employees
select * from Department
--4.1非空约束：不能为空
--4.2主键约束(PK) primary key constraint 唯一且不能空
--4.3唯一约束(UQ) unique constraint 唯一，允许为空，但只能出现一次
--4.4默认约束(DF) default constraint 默认值
--4.5检查约束(Ck)check constraint 范围以及格式限制
--4.6外键约束(Fk) foreign key constraint表关系
--备注：当主键表中一个值被外键表被引用，则删除不了主键中的值，除非外键表不引用主键表
/*
--初始化表
truncate table TblScore
truncate table TblStudent
*/
--五、通过sql进行设置约束
/*--删除表，重新新建表
drop table Department
drop table Employees
*/
--5.1修改表结构。删除其中一列
--结构：
--alter table 表名 drop column EmpAddress
alter table Employees
drop column EmpAddress
select * from Employees
--5.2增加一列EmpAddr nvarchar(1000)
--表中增加默认是列，所以不用写column
alter table Employees
add EmpAddr
nvarchar(
1000)
--5.3修改表中，EmpEmaill的数据类型为varchar(200)
alter table Employees
alter column Empmail
varchar(
200)
--5.4为EmpId增加一个主键约束
alter table Employees
add constraint PK_Employees_EmpId
primary key(EmpId)
--5.5为EmpName增加一个非空约束(修改列，由null修改为not null)
alter table Employees
alter column EmpName
varchar(
50)
not null
--5.6为EmpName增加唯一约束
alter table Employees
add constraint UQ_Employees_EmpName
unique(EmpName)
--5.7为性别增加一个默认约束，默认为‘男‘
alter table Employees
add constraint DF_Employees_EmpGender
default(
‘男‘)
for EmpGender
--5.8为性别增加一个检查约束，要求性别只能是：‘男‘ or ‘女‘
alter table Employees
add constraint CK_Employees_EmpGender
check(EmpGender
=‘男‘ or EmpGender
=‘女‘)
--为年龄增加一个检查约束：年龄必须在0--120岁之间。
alter table Employees
add constraint CK_Employees_EmpAge
check(EmpAge
>=0 and EmpAge
<=120)
select * from Department
select * from Employees
--5.9为部门表Department表设置主键，主键列是：DepId
alter table Department
add constraint PK_Department_DepId
primary key(DepId)
alter table Employees
alter column
--5.10为员工表中加入外键，增加一个DepId
alter table Employees
drop column DepId
alter table Employees
add EmpDepId
int not null
--5.11添加外键约束
alter table Employees
add constraint FK_Employees_Department
foreign key(EmpDepId)
references Department(Depid)
--5.12删除约束
--格式：
--alter table Employees drop constraint 外键1,外键2,外键3,
alter table Employees
drop constraint FKEmployees_Department,PK_Department_DepId
--5.13通过一条代码增加多个约束
--格式：
--
alter table Employees
add
constraint FK_Employees_Department
foreign key(EmpDepId)
references Department(Depid),
constraint PK_Department_DepId
primary key(DepId),
constraint CK_Employees_EmpAge
check(EmpAge
>=0 and EmpAge
<=120)
---备注：在创建表的时候吧约束就加上
drop table Department
drop table Employees
create table Department
(
DepId int identity(
1,
1)
primary key,
DepName varchar(
50)
not null unique
)
--创建部门表
create table Department
(
Depid int identity(
1,
1)
primary key,
DepName varchar(
50)
not null unique
)
--创建员工表
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),
Empmail varchar(
100)
unique,
EmpAddress varchar(
500)
not null,
EmpDepId int foreign key references Department(DepId)
on delete cascade
--EmpDepId int foreign key references Department(DepId) on delete cascade //可以实现级联删除
)
--六、数据查询（****）
--显示所有行，所有列
--* 表示所有列
--查询语句中没有where条件，表示查询所有行
--先执行from语句，再执行select 语句
select *
from Tblteacher
--只查询部分列
select tTid,tTname,tTGender
from Tblteacher
--根据条件，只查询部分行
select * from Tblteacher
where tTId
=5
--给查询结果的列起别名，也可以把 as 省略掉
select tTid
as 编号,tTname
as 姓名,tTGender
as 性别
from Tblteacher
--格式变换，方便查看
select
tTid as ‘(编号)‘,
tTname as 姓名,
tTGender as 性别
from Tblteacher
--备注：并不是select 必须和from 一起使用
--获取当前的时间
select GETDATE() 当前系统时间
--七、去掉重复
--distinct 是针对已经查询出的结果后去除重复
select * from Tblteacher
select distinct * from Tblteacher
select distinct tTGender
from Tblteacher
--八、Top
--Top一般都与order by一起使用
------------排序
--order by 列名
--8.1按照年龄，降序排序
update Tblteacher
set tTAge
=16 where tTId
=1
select * from Tblteacher
order by tTAge
desc
--8.2按照年龄，升序排序,
--备注：默认就是升序排序
select * from Tblteacher
order by tTAge
--排序后，进行筛选前多少列
--显示前2条 top后面都有 * 号
select top 2 * from Tblteacher
order by tTAge
--显示前20% 条数据,向上取整
select top 20 percent * from Tblteacher
order by tTAge
desc
--九、聚合函数--把多条聚合在一起，必须先进行分类。先分组再统计
--9.1统计出所有人的年龄的总和----sum()
select * from Tblteacher
select SUM(tTAge)
as 年龄总和
from Tblteacher
--9.2统计表中有多少条记录----count()
select COUNT(
*)
from Tblteacher
--9.3计算平均年龄---avg()
select AVG(tTAge)
as 平均年龄
from Tblteacher
select AVG(tTAge
*1.0)
as 平均年龄
from Tblteacher
select ((
select SUM(tTAge)
as 年龄总和
from Tblteacher)
*1.0/(
select COUNT(
*)
as 总数
from Tblteacher))
as 平均年龄
--9.4计算年龄最大--max()
select max(tTAge)
as 最大年龄
from Tblteacher
--9.4计算年龄最小--min()
select min(tTAge)
as 最小年龄
from Tblteacher
------聚合函数的一些其他问题
--聚合函数不统计空值--count(),avg() 不统计空值，sum（）把空值默认为0进行计算
--select count(tTAge) from Tblteacher
--如果使用聚合函数的时候，没有手动用group by 分组，那么聚合函数会把整个表中的数据作为一组来统计
--十、条件查询——————————————————————————————
--格式：
--select 列
--from 表
--where 条件
select * from TblScore
insert into TblScore(tSid,tEnglish,tMath)
values(
1,
85,
42),
(2,
85,
80),
(3,
35,
90)
insert into TblScore
values(
1,
45,
56)
--查询没有及格的学习
select * from TblScore
where tEnglish
<60 or tMath
<60
--c查询年龄包含在20--30岁之间男同学（包含20和30）
select * from TblStudent
where tSAge
>=20 and tSAge
<=30 and tSGender
=‘男‘
select * from TblStudent
where tSAge
between 20 and 30 and tSGender
=‘男‘
--查询出所有班级ID为3，4,5的那些学生
select * from TblStudent
where tSClassId
=3 or tSClassId
=4 or tSClassId
=5
--简化版
select * from TblStudent
where tSClassId
in(
3,
4,
5)
--如果后面的值是连续的，则简化成下面的代码（区间的话，执行速度高效）
select * from TblStudent
where tSClassId
>=3 and tSClassId
<=5
--备注：对于in或者or查询，如果查询中的条件是连续的几个数字，最好使用>= <= 或者 between and
--不要使用or 或者 in。提高效率
--十一、模糊查询（主要针对字符串）
--通配符：_ (下划线) 、 % 、 [] 、^
--：_(下划线)表示任意的单个字符
--查姓张，三个字（两个下划线）
select * from Tblteacher
where tTName
like ‘苏__‘
--无论姓名的字数，只要是苏开头就可以
select * from Tblteacher
where tTName
like ‘苏%‘
select * from Tblteacher
where tTName
like ‘苏%‘ and LEN(tTName)
=2
--:[](中括号)表示范围
--tTName:张a雨（女）（女） 把（女去掉）
update Tblteacher
set tTName
=REPLACE(tTName,
‘(女)‘,
‘‘)
--查询张开头，妹结尾，中间是数字
select * from Tblteacher
where tTName
like ‘张[0-9]妹‘
select * from Tblteacher
where tTName
like ‘张[a-z]妹‘
select * from Tblteacher
where tTName
like ‘张_妹‘
--张什么妹都可以，就是不是数字
select * from Tblteacher
where tTName
like ‘张[^0-9]妹‘
--数据中有名字中包含%。怎么查询(要进行转义)[放到中括号里]
select * from Tblteacher
where tTName
like ‘%\%%‘ --错误
select * from Tblteacher
where tTName
like ‘%[%]%‘--错误
--where columnA like ‘%5/%%‘ escape ‘/‘
select * from Tblteacher
where tTName
like ‘%/%%‘ escape ‘/‘
select * from Tblteacher
where tTName
like ‘%/]%‘ escape ‘/‘
select * from Tblteacher
where tTName
like ‘%/[%‘ escape ‘/‘
select * from Tblteacher
where tTName
like ‘%/[%/]%‘ escape ‘/‘
--十二、空值处理
select * from Tblteacher
--查询年龄中为空值的老师表
select * from Tblteacher
where tTAge
=null --查询不出来
--查询年龄中不是空值的老师表
select * from Tblteacher
where tTAge
<>null--查询不出来
select * from Tblteacher
where tTAge
=18 --查询出来
select * from Tblteacher
where tTAge
<>18 --查询出来
--备注：空值是空值(unknown)，无法使用=或者<>进行比较
--判断null值必须使用is null 或者 is not null
select * from Tblteacher
where tTAge
is null
select * from Tblteacher
where tTAge
is not null
--任何值和null计算都是null
---十三、order by排序
--1.降序order by 列名 desc
--2.升序order by 列名 asc 或者order by 列名（默认是升序）
--3.order by 语句必须一定要放在整个sql语句的最后
--select * from 表名
--where ...
--gourp by...
--haing ...
--
