sql server相关学习sql语句
2023-03-28 13:34 由
kk-ss 发表于
#数据库
点击查看代码
if exists(select * from sys.objects where name='Department' and type='U')
drop table Department
create table Department
(
--id identity(x,y) 初始x 自增y,primary key 主键
DepartmentId int primary key identity(1,1),
--名称
DepartmentName nvarchar(50) not null,
--描述
DepartmentRemark text
)
--字符串
--char(x) 占用x个字节 ‘ab’=>x个字节
--varchar(x) 最多占用x个字节 ‘ab’=>2个(x>2)
--text 长文本
--char text varchar 前面加n;存储unicode 对中文友好
--例子
--varchar(100) 100个字母或者50个汉字
--nvarchar(100) 100个字母或者100个汉字
--Rank是关键字了,所以加上[]
create table [Rank]
(
--id identity(x,y) 初始x 自增y,primary key 主键
RankId int primary key identity(1,1),
--名称
RankName nvarchar(50) not null,
--描述
RankRemark text
)
if exists(select * from sys.objects where name='People' and type='U')
drop table People
create table People(
--id identity(x,y) 初始x 自增y,primary key 主键
PeopleId int primary key identity(1,1),
--部门 references 引用外键 引用在部门表的id范围之内
DepartmentId int references Department(DepartmentId)not null ,
--职员
RankId int references [Rank](RankId)not null,
--名称
PeopleName nvarchar(50) not null,
--性别 加上约束(check) 默认
PeopleSex nvarchar(1)default('男') check(PeopleSex='男' or PeopleSex='女')not null,
--老版本date 新版本有time 加上samll就是表示现在时间 不能表示未来
PeopleBirth smalldatetime not null,
--小数的使用 float 可能有误差 decimal(x,y) 长度x,小数y位
PeopleSalary decimal(12,2) check(PeopleSalary>=1000 and PeopleSalary<=100000)not null,
--电话 约束unique 唯一 电话是唯一的
PeoplePhone varchar(20)unique not null,
--地址
PeopleAddress varchar(300),
--添加时间,获取当前时间默认值,有一个函数 getdate()
PeopleAddTime smalldatetime default(getdate()) not null
)
--修改表结构---
--(1)CRUD列
--alter table 表名 add 新列名 数据类型
--添加列 员工表添加邮箱列
alter table People add Mail varchar(200)
--alter table 表名 drop column 列名
--删除列 员工表删除邮箱列
alter table People drop column Mail
--alter table 表名 alter column 列名 数据类型
--修改地址为 200
alter table People alter column PeopleAddress varchar(200)
--维护约束---(删除 添加)
--删除约束
--alter table 表名 drop constraint 约束名
--alter table People drop constraint xxx
--添加约束
--alter table 表名 add constraint 约束名 check(表达式)
--alter table People add constraint psadad check(PeopleSex='男' or PeopleSex='女')
--添加主键 唯一 默认 外键
--alter table 表名 add constraint 约束名 primary key (列名)
insert into Department(DepartmentName,DepartmentRemark)
values('市场部','..........')
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','..........')
insert into Department(DepartmentName,DepartmentRemark)
values('企划部','..........')
insert into Department(DepartmentName,DepartmentRemark)
values('销售部','..........')
--最好一一对应 如果列顺序改变就回值出问题
--一次性插入多行数据
点击查看代码
use Mytest
select *from Department
select * from [Rank]
select * from People
--数据crud
--修改
--update 表名 set 列名='xxx' where 条件
--update 表名 set 列名='xxx', 列名='yyy' where 条件
update Department set DepartmentName='经理部' where DepartmentId=8
update People set PeopleSalary=PeopleSalary+1000
update People set PeopleSalary=15000 where RankId=3 and PeopleSalary<=15000
update People set PeopleSalary=PeopleSalary*2,PeopleAddress='西京' where PeopleName='关羽7'
--delete drop truncate区别
--drop table xxx 删除表对象xxx
--delete from xxx 删除表xxx的数据, 即表对象和结构都存在
--truncate table xxx 删除数据(清空数据)即表对象和结构都存在
--truncate 清空所有数据,不能有数据。 delete 可以删除所有数据也可以带条件删除符合条件的数据
--自动编号 1,2,3,4,5
--truncate 编号为:1,2,3,4,5 (重头开始)
--delete 编号将为6,7,8,9,10(继续)
--查询 as可省略 ,distinct()去重,
select PeopleName 姓名 from People
select distinct(PeopleAddress) from People
select PeopleSalary*1.2 加薪后,PeopleSalary 加薪前 from People
--按照 名字长度的前5个查询
select top 5 PeopleName from People order by len(PeopleName)
--按照 名字长度的前25%查询
select top 25 percent PeopleName from People order by len(PeopleName)
--按照 名字长度的后25%查询
select top 25 percent PeopleName from People order by len(PeopleName) desc
--查询为null的值的单位
select *from People where PeopleAddress is null;
--查询非空的值 和‘ ’区别 null就是没填 ‘ ’insert 有字段但是空白
select *from People where PeopleAddress is not null;
--查询时间的 1988到2000的
select * from People where PeopleBirth>'1988-8-7' and PeopleBirth<'2000-3-1'
select * from People where PeopleBirth between '1988-8-8'and '2000-3-1'
select * from People where year(PeopleBirth) between 1988 and 2000
--查询年纪在多少岁的
select PeopleName,year(GETDATE())-YEAR(PeopleBirth) 年龄 from People
--查询年纪在35岁以下的
select PeopleName,year(GETDATE())-YEAR(PeopleBirth) 年龄 from People where year(GETDATE())-YEAR(PeopleBirth)<35 and PeopleSalary >5000
select* from People where (MONTH(PeopleBirth)=11 and DAY(PeopleBirth)<=22) or (MONTH(PeopleBirth)=12 and DAY(PeopleBirth)<=31)
--子查询
select *from People where PeopleAddress=
(select PeopleAddress from People where PeopleName='关羽')
--龙 8
--鼠 4
--🐖 3
select 2000%12
select *,
case
when YEAR(PeopleBirth)%12=4 then '鼠'
when YEAR(PeopleBirth)%12=5 then '牛'
when YEAR(PeopleBirth)%12=6 then '虎'
when YEAR(PeopleBirth)%12=7 then '兔'
when YEAR(PeopleBirth)%12=8 then '龙'
when YEAR(PeopleBirth)%12=9 then '蛇'
when YEAR(PeopleBirth)%12=10 then '马'
when YEAR(PeopleBirth)%12=11 then '羊'
when YEAR(PeopleBirth)%12=0 then '猴'
when YEAR(PeopleBirth)%12=1 then '鸡'
when YEAR(PeopleBirth)%12=2 then '狗'
when YEAR(PeopleBirth)%12=3 then '猪'
end 生肖
from People
--优化
select *,
case YEAR(PeopleBirth)%12
when 4 then '鼠'
when 5 then '牛'
when 6 then '虎'
when 7 then '兔'
when 8 then '龙'
when 9 then '蛇'
when 10 then '马'
when 11 then '羊'
when 0 then '猴'
when 1 then '鸡'
when 2 then '狗'
when 3 then '猪'
end 生肖
from People
select *from People where PeopleAddress not like '%南%'
--查询名字2个字的 第一个字为刘
select * from People where SUBSTRING(PeopleName,1,1)='刘' and LEN(PeopleName)=2
select * from People where SUBSTRING(PeopleName,2,1)='备' and LEN(PeopleName)=2
select *from People where PeoplePhone like '1516%'
--开头4位是1516 第五位为1或者2 最后一位为9
select *from People where PeoplePhone like '1516[1,2]%9'
--开头4位是1516 第五位为1到8 最后一位为不为4到8
select *from People where PeoplePhone like '1516[1-8]%[^4-8]'
热门相关:超武穿梭 寂静王冠 仗剑高歌 照见星星的她 薄先生,情不由己