[SQLServer随笔-1] -基础知识

1、创建数据库

 1 if exists(select * from sys.databases where name='DBTEST')
 2     drop database DBTEST
 3 
 4 
 5 --创建数据库
 6 create database DBTEST
 7 on  --数据文件
 8 (
 9     name = 'DBTEST',    --逻辑名称，一般与数据库名称保持一直
10     filename='D:\DBTEST\DBTEST.mdf',    --物理路径和名称
11     size = 5MB,    --文件的初始大小
12     filegrowth = 2MB    --文件增长方式可以写大小和百分比
13 )
14 
15 log on --日志文件
16 (
17     name = 'DBTEST_log',    --逻辑名称，一般与数据库名称保持一直
18     filename='D:\DBTEST\DBTEST_log.ldf',    --物理路径和名称
19     size = 5MB,    --文件的初始大小
20     filegrowth = 2MB    --文件增长方式可以写大小和百分比
21 )

2、创建表格

--判断表是否存在
--type='U' 用户创建表
if exists(select * from sys.objects where name='Departmemt' and type='U')
drop table Departmemt

--切换数据库
use DBTEST

--创建表 员工 职位   部门
create table Departmemt
(
    --部门编号,primary key设置主键
    --identity(1,1):自动增长，初始值为1 增长步长1
    DepartmentId int primary key identity(1,1),

    --部门名称
    DepartmentName nvarchar(50) not null,

    --部门描述
    --未知长度的字符串类型可使用text类型
    DepartmentRemark text
)

--char 定长字符串，无论存储数据达到10个字节，都占用10个字节
--varchar：变长，varchar最多占用10字节
--text:长文本
--char  nvarchr text:存储unicode，对中文友好(一个汉字2个字节)

create table [Rank]
(
    --职级编号
    RankId int primary key identity(1,1),

    --部门名称
    RankName nvarchar(50) not null,

    --部门描述
    --未知长度的字符串类型可使用text类型
    RankRemark text
)


create table EUser
(
    UserId int primary key identity(1,1),

    --references:引用外键，将其他表的主键作为该表的字段
    --部门
    DepartmentId int references Departmemt(DepartmentId) not null,    --保证员工的部门ID存在与'部门(DepartmentId)'表当中
    --职级
    RankId int references [Rank](RankId) not null,

    UserName nvarchar(50) not null,
    UserSex nvarchar(1) default('男') check(UserSex='男' or UserSex ='女'),    --check约束，若不满足条件无法保存
    UserBirth datetime not null,    --Date  年月日   Datetime  可到秒
    UserSalary decimal(12,2) check(UserSalary>1000 or UserSalary <10000000),
    UserPhone varchar(20) unique not null,--唯一
    UserAddress varchar(300),
    UserAddTime smalldatetime default(getdate()) --添加时间,获取当前时间作为添加时间

)


---------修改表结构----------------
--1.添加列
alter table EUser add Email varchar(200)

--2、删除列
alter table EUser drop column Email

--3、修改列
alter table EUser alter column UserAddress varchar(200)


-----------维护约束----------------
---删除约束，可以在对应表单当中进行维护CHECK约束    
alter table EUser drop constraint CK__EUser__UserSalar__1DE57479--约束名CK__EUser__UserSalar__1DE57479

---添加约束(check约束)
alter table EUser add constraint CK__EUser__UserSalar__1DE57479 
check(UserSalary>1000 and UserSalary <100000)

--添加约束（主键约束）
--alter table 表名 add constraint 约束名 primary key(列名)

--添加约束（唯一）
--alter table 表名 add constraint 约束名 unique(列名)

--添加约束（默认值）
--alter table 表名 add constraint 约束名 default 默认值 for 列名

--添加约束（外键）
--alter table 表名 add constraint 约束名 foregn key(列名) references 关联表名(列名(主键))

 

3、修改和删除表格

select * from Departmemt
select * from [Rank]
select * from EUser

---修改
--update 表名  set 字段1= 值1,字段2 = 值2 where 条件

--每人加1000元
update EUser set UserSalary = UserSalary +1000

--将员工ID为3的员工加薪500
update EUser set UserSalary = UserSalary+500 where EUser.UserId =4

--将企划部id = 3低于10000的调整为10000
update EUser set UserSalary = 10000 
where DepartmentId =3 and UserSalary <10000

update EUser set UserSalary = UserSalary*2,UserAddress = 'X省X市X区X县11'
where UserName = '张三' 


---删除--------------------
--drop删除表对象    drop table 表名
--truncate清空数据   truncate table 表名
--delete from 表名  where 条件

--删除全部
--delete from EUser

--删除工资大于10000
delete from EUser where UserSalary > 11000

----truncate delete区别
--truncate清空数据，不能有数据，delete则不然
--truncate删除数据后，可沿用之前的编号，而delete则不然

 

4、基本查询

---查询所有列所有行
select * from Departmemt
select * from [Rank]
Select    * from EUser

---查询指定列（姓名  生日   月薪  电话）、
select UserName,UserBirth,UserSalary,UserPhone from EUser

---查询指定列（姓名  生日   月薪  电话）显示中文列名
select UserName 姓名,UserBirth 生日,UserSalary 月薪,UserPhone 电话 from EUser

--查询员工所在城市,不需要重复数据显示
select distinct(UserAddress) from EUser

--假设准备加薪，查询出加薪后的员工数据
select UserName,UserSex,UserSalary*1.3   加薪后工资 from EUser 

--假设准备加薪，查询出加薪后的员工数据并对比加之前的数据
select UserName,UserSex,UserSalary,UserSalary*1.3   加薪后工资 from EUser 

 

5、表格插入数据

---向部门表插入数据
use DBTEST
insert into Departmemt(DepartmentName,DepartmentRemark)
values('市场部','.............')

insert into Departmemt(DepartmentName,DepartmentRemark)
values('软件部','.............')


insert into Departmemt(DepartmentName,DepartmentRemark)
values('企划部','.............')

--简写
insert into Departmemt values('硬件','.............')


--一次性插入多行数据
insert into Departmemt(DepartmentName,DepartmentRemark)
select '测试部','........'  union
select '实施部','........'  union
select '产品部','........' 


-----向职级插入数据--------------------
insert into [Rank](RankName,RankRemark)
select '初级','........'  union
select '中级','........'  union
select '高级','........' 


-----向员工表 插入数据
insert into EUser(DepartmentId,RankId,UserName,UserSex,UserBirth,UserSalary,UserPhone,UserAddress,UserAddTime)
values(2,1,'张三','男','1999-11-1',5000,'111222','X省X市X区X县',getdate())

insert into EUser(DepartmentId,RankId,UserName,UserSex,UserBirth,UserSalary,UserPhone,UserAddress,UserAddTime)
values(1,2,'李四','男','1999-11-2',3000,'1112244','X省X市X区X1县',getdate())

insert into EUser(DepartmentId,RankId,UserName,UserSex,UserBirth,UserSalary,UserPhone,UserAddress,UserAddTime)
values(1,3,'王五','男','1999-11-3',5030,'111224','X省X市X区X3县',getdate())

insert into EUser(DepartmentId,RankId,UserName,UserSex,UserBirth,UserSalary,UserPhone,UserAddress,UserAddTime)
values(3,3,'马六','男','1999-11-4',3200,'111225','X省X市X区X4县',getdate())

insert into EUser(DepartmentId,RankId,UserName,UserSex,UserBirth,UserSalary,UserPhone,UserAddress,UserAddTime)
values(3,3,'柳如烟','女','1999-11-5',3200,'111226','X省X市X区X5县',getdate())

 

6、条件查询

Select    * from EUser

--查询性别为女的员工
select * from EUser where UserSex = '女'

--查询薪资大于10000的员工
select * from EUser where UserSalary >=10000

--查询薪资大于3000的员工性别为女
select * from EUser where UserSalary >=3000 and UserSex ='女'

--查询薪资小于10000的员工，或者薪资大于3000的女员工
select * from EUser where UserSalary <10000 or (UserSex ='女' and UserSalary >3000)

--查询工资位于5000-10000的员工
select * from EUser where UserSalary>5000 and UserSalary <=10000
select * from EUser where UserSalary between 5000 and 10000

--查询地址在X省X市X区X4县或X省X市X区X1县
select * from EUser where UserAddress='X省X市X区X4县' or UserAddress='X省X市X区X1县'
select * from EUser where UserAddress in('X省X市X区X4县' ,'X省X市X区X1县')

--排序
--根据工资大小进行排序
select * from EUser order by UserSalary asc --升序，默认
select * from EUser order by UserSalary desc --降序

--根据名字长度排序，仅显示前三个
select top 3 * from EUser order by  LEN(UserName) desc

--查询工资最高的10%的员工
select top 30 percent * from EUser ORDER BY UserSalary desc

--查询地址未填写的信息
select * from EUser where UserAddress is null

insert into EUser(DepartmentId,RankId,UserName,UserSex,UserBirth,UserSalary,UserPhone,UserAddTime)
values(3,3,'柳如烟','女','1999-11-5',3200,'111277',getdate())

--查询90后员工
select * from EUser where year(UserBirth) between 1990 and 1999

--查询员工的年龄
select UserName,UserSex,year(getdate())-year(UserBirth) 年龄 from EUser

--查询员工在25-36之间的员工
select UserName,year(getdate())-year(UserBirth) 年龄  from EUser where year(getdate())-year(UserBirth) Between 25 and 36


--查询星座为巨蟹座的员工
select * from EUser where 
(MONTH(UserBirth) =6 and day(UserBirth) >22) or (MONTH(UserBirth) =7 and day(UserBirth) <22)

--查询工资大于王五高的人的信息
select * from EUser where UserSalary>
(select UserSalary from EUser where UserName ='王五')

--查询员工信息，添加一列显示生肖
select *,
    case 
    when year(UserBirth)%12 = 4 then '鼠'
    when year(UserBirth)%12 = 5 then '牛'
    when year(UserBirth)%12 = 6 then '虎'
    when year(UserBirth)%12 = 7 then '兔'
    when year(UserBirth)%12 = 8 then '龙'
    when year(UserBirth)%12 = 9 then '蛇'
    when year(UserBirth)%12 = 10 then '马'
    when year(UserBirth)%12 = 11 then '羊'
    when year(UserBirth)%12 = 0 then '猴'
    when year(UserBirth)%12 = 1 then '鸡'
    when year(UserBirth)%12 = 2 then '狗'
    when year(UserBirth)%12 = 3 then '猪'
    else '' 
end 生肖
from EUser

select *,
    case year(UserBirth)%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 '猪'
    else '' 
end 生肖
from EUser