创建数据库
if exists(select*from sys.databases where name = 'DBTEST')
drop database DBTEST --删除数据库
--创建数据库
create database DBTEST
on --数据文件
(
name = 'DBTEST', --逻辑名称
filename = 'D:\DATA1\DBTEAT.mdf', --物理路径和名称
size = 5MB, --数据文件的初始大小
filegrowth = 2MB --文件的增长方式可以写大小,也可以写百分比
)
log on --日志文件
(
name = 'DBTEST_log', --逻辑名称
filename = 'D:\DATA1\DBTEAT_log.ldf', --物理路径和名称
size = 5MB, --数据文件的初始大小
filegrowth = 2MB --文件的增长方式可以写大小,也可以写百分比
)
--创建数据库简写
create database DBTEST1
创建表
--切换数据库
use DBTEST
--创建的表的基本语法
--create table 表名
--(
-- 字段名1 数据类型,
-- 字段名2 数据类型
--)
--判断表是否存在,type='U'代表用户添加的
if exists(select * from sys.objects where name='Department' and type='U')
--建表(部门,职级,员工)
drop table Department
create table Department
(
--部门编号,primary key:主键,唯一标识一行 identity(1,1):自动增长,初始值1,每次增长1
DepartmentId int primary key identity(1,1),
--部门名称
DepartmentName nvarchar(50) not null,
--部门描述
DepartmentRemark text
)
--char:定长,char(10),无论存储的数据是否真的到了10个字节,都要占用10个字节
--char(10)存储'ab',仍然占用10个字节
--varchar:变长,varchar(10),最多占用10个字节
--varchar(10)存储'ab',占用两个字节
--text:长文本
--char,varchar,text前面加n:存储unicode字符,对中文友好
--varchar(100):存储100个字母或50个汉字
--nvarchar(100):存储100字母或100个汉字
if exists(select * from sys.objects where name='Rank' and type='U')
--建表(部门,职级,员工)
drop table Rank
create table [Rank]
(
--职级编号,primary key:主键,唯一标识一行 identity(1,1):自动增长,初始值1,每次增长1
RankmentId int primary key identity(1,1),
--职级名称
RankmentName nvarchar(50) not null,
--职级描述
RankmentRemark text
)
if exists(select * from sys.objects where name='People' and type='U')
--建表(部门,职级,员工)
drop table People
--员工
create table People
(
PeopleId int primary key identity(1,1),primary key:主键,唯一标识一行 identity(1,1):自动增长,初始值1,每次增长1
DepartmentId int references Department(DepartmentId) not null,--部门(引用外键)
RankId int references [Rank](RankmentId) not null,--职级(引用外键)
PeopleName nvarchar(50) not null,--姓名
PeopleSex nvarchar(1) default('男') check(PeopleSex='男'or PeopleSex='女') not null,--性别default如果没有填默认男
PeopleBirth smalldatetime not null,--small保存的时间范围小 --生日
PeopleSalary decimal(12,2) check(PeopleSalary>=1000 or PeopleSalary<=1000000) not null,--12总长度,2小数点后几位 --月薪
PeoplePhone varchar(20) unique not null,--unique表示唯一 --电话
PeopleAddress varchar(300), --地址
PeopleAddTime smalldatetime default(getdate()) --添加时间
)
--修改表结构
--(1)添加列
-- alter table 表名 add 新列明 数据类型
--给员工表添加一列邮箱
alter table People add PeopleMail varchar(200)
--(2)删除列
-- alter table 表名 drop column 新列明 数据类型
--删除邮箱这一列
alter table People drop column PeopleMail
--(3)修改列
--alter table 表名 alter column 列名 数据类型
--修改地址varchar(300)为varchar(200)
alter table People alter column PeopleAddress varchar(200)
--维护约束(删除、添加)
--删除约束
--alter table 表名 drop constraint 约束名
--删除月薪约束
alter table People drop constraint CK__People__PeopleSa__5165187F
--添加约束(check约束)
--alter table 表名 add constraint 约束名 check(表达式)
--添加工资字段的约束,工资必须在1000-1000000之间
alter table People add constraint CK__People__PeopleSa1
check(PeopleSalary>=1000 or PeopleSalary<=1000000)
--添加约束(主键约束)
alter table 表名 add constraint 约束名 primary key(列名)
--添加约束(唯一约束)
alter table 表名 add constraint 约束名 primary unique(列名)
--添加约束(主键约束)
alter table 表名 add constraint 约束名 default 默认值 for 列名
--添加约束(外键约束)
alter table 表名 add constraint 约束名 forign key(列名)references 关联的表名(列名(主键))
在表里插入数据
--向部门表里插入数据(稳妥的方法)
insert into Department(DepartmentName,DepartmentRemark)
values('市场部','.......')
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','.......')
insert into Department(DepartmentName,DepartmentRemark)
values('企化部','.......')
--简写
insert into Department values('硬件部','.......')
--一次性插入多行数据
insert into Department(DepartmentName,DepartmentRemark)
select '测试部','.......' union
select '实施部','.......' union
select '产品部','.......'
--向职级表插入信息
insert into [Rank](RankmentName,RankmentRemark)
values('初级','.....')
insert into [Rank](RankmentName,RankmentRemark)
values('中级','.....')
insert into [Rank](RankmentName,RankmentRemark)
values('高级','.....')
--向员工表插入数据
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(9,1,'刘备','男','1988-8-8',5000,'13888888888','中国',getdate())
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(9,1,'张三','男','1988-8-8',5000,'13888888889','中国',getdate())
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(9,1,'李四','男','1988-8-8',5000,'13888888899','中国',getdate())
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(9,1,'王五','男','1988-8-8',5000,'13888888998','中国',getdate())
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(9,1,'六六','男','1988-8-8',5000,'13888889988','中国',getdate())
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddress,PeopleAddTime)
values(9,1,'七七','男','1988-8-8',5000,'13889888888','中国',getdate())
删除表里数据
--删除数据
--语法:
--delete from 表名 where 条件
--删除员工表所有数据
delete from People
--删除市场部(部门编号1)工资大于两万的人
delete from People where DepartmentId=1 and PeopleSalary > 20000
--关于删除(drop,truncate,delete)
drop table People --删除表对象
truncate table People --删除数据(清空数据),表对象及表结构依然存在
delete from People --删除所有数据,表对象及表结构依然存在
--truncate和delete区别
--truncate清空所有数据,不能有条件,delete可以删除所有数据,也可以带条件,删除符合条件的数据
--自动编号:
--假设表中自动编号为1,2,3,4,5
--使用truncate清空数据之后再添加数据,编号仍然是1,2,3,4,5
--使用delete删除数据,删除的自动编号将永远不存在了
--如果使用delete删除了数据,再添加数据,编号就变成了,6,7,8,9,10
修改表里的数据
--修改
--语法:
--update 表名 set 字段1=值1,字段2=值二,where 条件
--工资普调,每个人加薪1000元
update People set PeopleSalary = PeopleSalary+1000
--将员工编号为7的人加薪500
update People set PeopleSalary = PeopleSalary+500 where PeopleId = 7
--将软件部(部门编号1)人员工资低于15000的调成15000
update People set PeopleSalary = 15000 where
DepartmentId = 1 and PeopleSalary<15000
--将刘备的工资修改为以前的两倍,并且把地址修改成北京
update People set PeopleSalary = PeopleSalary*2,PeopleAddress='北京'
where PeopleName='刘备'
查询表里的数据
--查询所有列所有行
select * from Department
select * from [Rank]
select * from People
--查询指定列(姓名,性别,生日,月薪,电话)
select PeopleName ,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone from People
--查询指定列(姓名,性别,生日,月薪,电话)(显示中文列名)
select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话 from People
--查询出员工所在的城市(过滤掉重复的城市)
select distinct(PeopleAddress) from People
--假设要准备加工资(上调20%),查询出加工资前后的数据对比
select PeopleName,PeopleSex,PeopleSalary 加薪前的工资,PeopleSalary*1.2 加薪后的工资 from People
select * from People
--查询性别为女的员工信息
select * from People where PeopleSex = '女'
--查询工资大于等于10000元的员工信息
select * from People where PeopleSalary >= 10000
update People set PeopleSalary = 0.5* PeopleSalary where PeopleId = 6
--查询出性别为男,工资大于等于10000元的员工信息(多条件)
select * from People where PeopleSex = '男' and PeopleSalary >=10000
--查询月薪大于等于10000员工,或者月薪大于等于8000的女员工
select * from People where PeopleSalary>= 10000 or (PeopleSex = '女' and PeopleSalary >=100)
--查询出生日在1980-1-1之后,或者月薪大于等于8000的女员工
select * from People where PeopleBirth >= '1980-08-07' or (PeopleSex = '女' and PeopleSalary >=100)
--查询地址在中国或上海的男员工
select * from People where PeopleSex = '男' and (PeopleAddress='中国' or PeopleAddress = '上海')
update People set PeopleSalary = 10000 where PeopleId = 2
--查询地址在中国或工资为10000为的男员工
select * from People where PeopleSex = '男' and (PeopleAddress='中国' or PeopleSalary = 10000)
--查询月薪在10000-20000之间的员工信息(多条件)
select * from People where PeopleSalary >= 10000 and PeopleSalary <= 20000
select * from People where PeopleSalary between 10000 and 20000
--查询出地址在武汉或者北京的员工信息
select * from People where PeopleAddress = '北京' or PeopleAddress = '中国'
select * from People where PeopleAddress in('南京','北京','上海')
select * from People where PeopleName in('张三','李四','王五')
update People set PeopleName = '王小花' where PeopleAddress = '南京'
--排序
--查询所有的员工信息,根据工资排序,降序
--desc降序(默认值,可不写) asc升序
select * from People order by PeopleSalary asc
--查询所有的员工信息,根据名字的长度排序(降序)
select * from People order by len(PeopleName) desc
--查询出工资最高的2个人的信息
select top 2 * from People order by PeopleSalary
--查询出工资最低的50%的员工信息
select top 50 percent * from People order by PeopleSalary asc
--null:空值
insert into people(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,
PeoplePhone,PeopleAddTime)
values(1,1,'马云','男','1988-8-8',5000,'13888877888',getdate())
--查询出没有填地址的员工
select * from People where PeopleAddress is null
--查询出填地址的员工
select * from People where PeopleAddress not is null