Sql server笔记

/ 0评 / 1

常用数据类型

 

 

 

本地连接

如果实例是默认名字即可用英文符号 . 或者 (local) 又或者 localhost 进行连接

如果是自定义名称,那么就需要用 IP\实例名本机名\实例名

 

约束

主键约束:检查当前列是否有重复值,如果有则不保存当前数据进入数据库

创建方法:表 - 设计 - 右键列名前面 - 设为主键/删除主键

SQL语句添加 - primary key

-- 新建表时添加 primary key
create table Student(
  Sno int identity primary key not null,
)

唯一约束unique:同理主键约束,主键约束也是唯一约束

创建方法:设计界面右键空白处 - 索引/键 - 添加 - 自定义标识名称(可忽略) - 更改常规的类型为唯一键

SQL语句添加

-- 新建列时添加
create table Student(
  Sno char(8) primary key,
  Sname char(8) unique,
)
-- 已经创建了列的时候添加
alter table Student add unique(Sname)
-- 添加多个对象也是可以的
alter table Student add unique(Sname, Sage)

检查约束:检查当前列中设定的条件是否为真,为假则提示错误

创建方法:设计界面右键空白处 - Check约束 - 添加 - 自定义标识名称(可忽略) - 常规表达式 - 配置表达式

如:xxx = '男' or xxx = '女' ,xxx为列表名

SQL语句添加

-- 创建列时添加
create table T_Employee(
  Age int not null check(age >= 18 and age <= 50),
);
-- 已有列后添加
alter table T_Employee add check(age >= 18 and age <= 50)
-- 当然也可以同时添加多个check约束
alter table T_Employee add check(age >= 18 and age <= 50), check(EmpName != 'test')

默认约束:默认当前列的数据都是指定数据

设置方法:列属性 - 默认值绑定 - 设置默认值

SQL语句添加

-- 创建列时添加
create table T_Employee(
  Sex nchar(8) default '保密'
);
-- 已有列后添加
alter table T_Employee add constraint sex default '保密' for sex

 

主键约束与唯一约束的区别

主键约束唯一约束
整张表只有一个主键约束整张表可有多个唯一约束
不允许存在NULL允许NULL存在

主键约束也是与唯一性相同的,但是他只能有一个且不能为空,而且整个表只存在一个主键,而唯一性约束就是主键“管不到”的列上它来负责数据的唯一性,所以可以拥有多个唯一性约束

 

主键和外键关系

主键就类似于一个核心,外键的数据需要去主键确定是否存在才能存储进入外键数据表

举个例子,假如给学号列表定位主键,因为每个人的学号是独一无二的,我们把整个班级的学号都存储到了主键上,当另一张表想要存储进入一条学号数据时,外键就会先来看看主键是否存在这个学号,如果存在则存储进入自己的表中,否则就提示错误。

建立外键关系也相当与特定情况下为了数据安全增加了一道保障

如何建立外键呢?首先得满足以下条件

如何区分谁是外键谁是主键?A现在需要去找B确定是否存在这个数据,A就是外键,B就是主键

可视化创建方法:

演示一下,这里创建一张名为test1的表,作为外键表,一列为ID列,一列为Name列,他们对应int类型与nvarchar类型

 

 

然后再新建一个名为test2的表,作为主键表

请注意!因为我要Name这个列创建外键关系,所以在这个主键表需要把Name设为主键

 

 

两张表的Name列的属性、数据类型、Null是否为空全部一致的情况下,我们回到test1表

右键空白处,点击关系

 

 

在新的窗口中点击添加,然后在右侧表和列规范点击最右边的按钮

 

 

然后把主键表改为test2,下面的选项分边选择两张表的Name列

 

 

然后点击确定,关闭,Ctrl + S保存,此时会提示一个提示更改了表什么什么的,确定即可

这时候我们去编辑test2表,在Name输入两个参数,小明和小红

 

 

然后我们去编辑test1外键表,尝试在Name里面输入其他的数据

 

 

此时就会提示约束冲突,然后我们输入主键表中的数据

 

 

此时就会发现没问题啦

 

语句创建方法

创建表时添加:constraint 自定义外键名 foreign key (外键列名) references 主键表名(主键列名)

创建表后添加:alter table 外键表名 constraint 自定义外键名 foreign key (外键列名) references 主键表名(主键列名)

当前表的列名为外键,表名(列名)为主键

-- 建立表1-Customer里面的CustomerID列与表2-Orders里面的CustomerID列为主外键关系
-- 表2的CustomerID将设置为外键
create table Orders(
  OrderID int primary key,
  CustomerID int constraint FK_Orders_Customer foreign key (CustomerID) references Customer(CustomerID)
)

-- 建表后添加
alter table Orders add constraint FK_Orders_Customer foreign key (CustomerID) references Customer(CustomerID)

 

增删改查

create新增数据库

-- 创建一个名为T_Employee的表
create table T_Employee(
        -- 设置为自增、主键
  EmpId int identity primary key,
  EmpName nvarchar(32) not null,
  Age int not null,
  Sex nchar(8) not null,
  Salary money not null,
  Birthday date not null
);

 

insert新增数据

insert into 表名 values(对应列内容1 , 对应列内容2 , 对应列内容3... ...)

-- 在test1表中插入一行数据,数据由逗号隔开且顺序对应
insert into test1 values(1, '小明', 18, '男')

如果需要增加多行就使用union即可

insert into 表名 values(对应列名1 , 对应列名2 , 对应列名3... ...)

select 对应列内容1 , 对应列内容2 , 对应列内容3 union

select 对应列内容1 , 对应列内容2 , 对应列内容3 union

select 对应列内容1 , 对应列内容2 , 对应列内容3

-- 在test1表中插入多行数据,数据由逗号隔开且顺序对应
insert into test1
select 2, '茄子', 18, '男' union
select 3, '毛子', 20, '男' union
select 4, '小波', 21, '女' union
select 5, '馒老六', 22, '男'

以上方法适用于正行数据新增,如果只想增加某一项则需要在表名后面列出想要新增的列

insert into 表名(对应列名1 , 对应列名2 , 对应列名3... ...) values(对应列内容1 , 对应列内容2 , 对应列内容3... ...)

-- 在test1表中插入一行数据到指定列,数据由逗号隔开且顺序对应指定列
insert into test1(ID, Age, Gender) values(6, 18, '男')

如果忽略某内容时,值为NULL

 

delete删除数据

delete from 表单名 where 条件

-- 删除test1表中ID=4的一行数据
delete from test1 where ID = 4

或者删除整个表格的数据,但保留列

delete from 表名

-- 删除了test1表中全部数据,保留了列的属性
delete from test1

如果不想要这张表单,那么也可以全部移除

drop from 表名

运行完刷新表,就会发现test1表已经不见了,彻底删除

-- 彻底删除整张表test1
drop table test1

 

update修改数据

update 表名 set 列名= ...

-- 修改test1表的Gender列全部为“女”
update test1 set Gender = '女'

当然也可以修改数值,增加条件等

update 表名 set 列名 = 'xxx' , 列名 = 'xxx' , 列名 = 'xxx'

-- 修改test1表中Gender列全部改为“骚”;Age列的数值全部+3;ID列数值全部+1
update test1 set Gender = '骚', Age += 3, ID += 1

当然还可以指定某一行然后修改,比如我们指定ID列列等于5的行,修改他的名字为茄子

update 表名 set 列名 = 'xxx' where 列名 = 'xxx'

where后面就是搜索条件

-- 修改test1表中ID=2的一行其中的Name数据为“馒老六”
update test1 set Name = '馒老六' where ID = 2

where后面条件满足时才会进行索引,否则跳过

 

select:查询数据

在test1表中查找name列与Age列的全部数据,如果查找单列就输入一个即可

select 列名1,列名2 from 表名

select Name, Age from test1

在test1表中获取大于18岁的数据(Age > 18)

运算符有>、<、>=、<=、=

在这里也可以用到或关系和与关系,或也就是两个条件只需要满足一个即可,而与需要同时满足才行

select * from 表名 where 列名 = xx or 列名 = xx

select * from test1 where Age = 18 or Name = '小黄'

在这里只要等于18岁的满足条件1,所以搜索出来了;小黄的名字满足条件2,也被搜索出来了

select * from test1 where Age = 18 and Name = '小明'

这里使用了and,也就是两个必须满足才能搜索到,所以只输出了小明这一行的数据,因为条件1与2全部满足

like的意思就是模糊查找;

"%"代表的是任意的字符串,它的值可以是空的也可以不是空

"_"代表的的是一个占位符,可以理解为它是存在数据的只是不知道数据是什么,它只代表一个数值!并且不能为空!

like演示:找出test1表中Age列全部是18的数据

select * from test1 where Age like '18'

like就是精确搜索,它同等于where Age = '18'

"%"演示:找出test1表中Phone列中前面符合123456数据

因为"%"他的值可空也可以不空,可以看见小明的Phone数值只有123456但是也被搜索了,因为它后面没有值,满足条件;123456后面还存在数据的也被搜索了,因为后面有数值,也算满足条件,这就是"%"的使用方法

select * from test1 where Phone like '123456%'

既然能忽略后面的数值,那么同理也可以忽略前面

select * from test1 where Phone like '%5678'

前面和后面也可以同时忽略,只提取中间

select * from test1 where Phone like '%56%'

"_"演示:找出test1表中Age列中以8结尾的数据

select * from test1 where Age like '_8'

 

修改表名列名与列类型

-- 修改表名 
exec sp_rename '表名', '新表名'
-- 修改列名
exec sp_rename '表名.[列名]', '新列名' , 'column'
--修改表类型
alter table 表名 alter column 列名 数据类型 not null;

 

函数

修改显示内容:replace

把test1表中的Name列的所有“小明”替换成“小红”并不修改数据内容,只显示在结果

replace(列名, 转换前, 转换后)

select replace (Name, '小明', '小红') from test1

反向显示:reverse

把test1表中的Name列所有数据全部反向显示,如ABC反向显示为CBA

reverse(列名)

select reverse(Name) from test1

字符串转换为数字:str

将字符串转换成数字并返回

str(数字字符串,  最大长度, 小数后几位)

如果数字字符串没有小数,可以忽略第三项的小数后几位设置;最大长度为整个数字的长度,包括小数

select str('123456', 6)
-- 结果为123456
select str('123.456', 6, 3)
-- 结果为123.456

提取指定字符:substring

提取“我起了一枪秒了有什么好说的”其中的“一枪秒了”字段

这个并不是从0开始计算,而是从1开始,“我起了”分别是123,“一”处于第四个,所以从4开始截取,然后向后取四个字符

substring('字符串数据', 起始位置, 取值范围)

select substring('我起了一枪秒了有什么好说的', 4, 4)

返回字符串位置:charindex

返回“我起了一枪秒了有什么好说的”其中的“一枪秒了”在这句话的多少位置

原理同上

charindex('想要查询的字符串', '整个字符串')

select charindex('一枪秒了', '我起了一枪秒了有什么好说的')
-- 返回了4

获取指定字符串长度:len

len('指定字符串')

select len('我起了一枪秒了有什么好说的')

获取左/右指定数据:left/right

从左/右获取指定的数据

left/right('指定的字符串', 获取多少位)

select left('我起了一枪秒了有什么好说的', 5)
select right('我起了一枪秒了有什么好说的', 5)

获取当前年月日:getdate

获取当前时间

getdate()

select getdate() 当前时间
select day(getdate()) 当前天数
select month(getdate()) 当前月数
select year(getdate()) 当年年数

时间戳差距计算:datediff

计算给出的两个时间段相差多少年/月/日

datediff(年/月/日, '时间1', '时间2')

-- 依次对应查询年月日
select datediff(year, '2000-1-1', '2002-5-5')
select datediff(month, '2000-1-1', '2002-5-5')
select datediff(day, '2000-1-1', '2002-5-5')

如果第一个数值比第二个数值要小,将返回负数结果

增加时间:dateadd

给指定时间增加年/月/日

dateadd(年/月/日, 增加大小, 指定时间)

-- 获取当前时间并增加8天
select dateadd(day, 8, getdate())

随机取数:rand

在0 - 1中间随机取值

rand()

select rand()

随机取整数可以用*来解决

floor是取0至N-1之间的;ceiling是取0至N之间

select floor(rand()*100)
select ceiling(rand()*100)

指定舍去数:round

round的意思就是指定一个位置进行四舍五入,比如我在下方案例里面的数值填上12345.567,把舍去的数值改为2,那么他就会向右边推进两位,然后开始运算四舍五入,也就是保留两位小数变成12345.570

如果我填写-1,那么他就会向左边推进一位,然后开始计算四舍五入,结果为12350.000

同理,如果我填写-2,他就会向左边推进2位进行四舍五入运算,结果为12300.000

round('数值', 舍去的数值)

select round(12345.567, -2) --12300.000
select round(12345.567, -1) -- 12350.000
select round(12345.567, 0) -- 12346.000
select round(12345.567, 1) -- 12345.600
select round(12345.567, 2) -- 12345.570

转换函数:cast , convert

将数据转换成其他格式
cast(数据 as 类型)
convert(类型, 数据)

-- cast转换
select '当前时间为:' + cast(getdate() as nvarchar(max))
-- convert转换
select '当前时间为:' + convert(nvarchar(max), getdate())

cast与convert的区别就是convert可以格式化时间和数值

 

不允许重复

test1表中存在重复的name值,就可以用distinct去过滤掉重复数据

distinct 列名

-- 去掉重复的name
select distinct name from test1

 

分组查询group by

group by的主要用处就是对一堆数据进行分组查询,先分组,再查询

group by 分组列名

-- 从test表中先把sex分组,然后从分组中查询每个分组最大的salary
select max(salary) from test group by sex

发表评论

电子邮件地址不会被公开。 必填项已用*标注