--判断表是否存在,U表示用户定义的表 if exists(select*from sys.objects where name = 'Department' and type = 'U') drop table Department --创建部门表 create table Department ( --部门编号,primary key:主键,不能重复,identity:自增,初始值1,步长为1 DepartmentId int primary key identity(1,1), --部门名称 DepartmentName nvarchar(50) not null, --部门描述 DepartmentRemark text ) -- char:定长,char(10),无论存储字节是否到达10,都要占用10个字节 -- varchar:变长,占用真实个字节 -- text:长文本 --char,varchar,text前面加n:存储unicode字符,对中文友好 varchar(100)存储100个字母或50个汉字 nvarchar(100)存储100个字母或100个汉字 --创建职级表 create table [Rank]--Rank是SQL关键字,所以用方括号括起来 ( RankId int primary key identity(1,1), --职级编号 RankName nvarchar(50) not null, --职级名称 RankRemark text --职级描述 ) --创建员工表 create table People ( PeopleId int primary key identity(1,1),--编号 DepartmentId int referenences Department(DepartmentId), --部门(引用外键),references:代表外键引用,将添加的值与引用列进行对比,如果不存在,则无法添加 RankId int referenences [Rank](RankId),--职级 PeopleName nvarchar(50) not null,--姓名 PeopleSex nvarchar(1) default('男') check(PeopleSex="男"or PeopleSex="女"),--check:建立约定,后续维护必须遵循 PeopleBirth smalldatetime not null, --date年月日,datetime年月日时分秒,smalldatetime最近的日期时间,具体范围1900 年 1 月 1 日至 2079 年 6 月 6 日。为了保证表向下兼容,用datetime。 PeopleSalary decimal(12,2) check(PeopleSalary>=2000 and PeopleSalary<=100000),--decimal(长度,小数位数) Peoplephone varchar(20) unique not null,--unique:唯一约束,不能重复 PeopleAddress varchar(300), PeopleAddTime smalldatetime default(getdate()) --添加时间 )
表结构和约束的维护
修改表结构
1 2 3 4 5 6 7 8 9 10 11 12
(1)添加列 alter table 表名 add 列名 数据类型 --给员工表添加一列邮箱 alter table People add PeopleEmail varchar(200) (2)删除列 alter table 表名 drop column 列名 --删除员工表的邮箱列 alter table People drop column PeopleEmail (3)修改列 alter table 表名 alter column 列名 数据类型 --修改地址列的数据类型(varchar(300)变为varchar(200)) alter table People alter column PeopleAddress varchar(200)
维护约束(删除、添加)
删除约束
1 2 3 4 5
alter table 表名 drop constraint 约束名 --删除月薪的约束 alter table People drop constraint DF__People__PeopleSalary__2E7F3B7B (建议给约束起别名。去sys.object里找) 实际很少用,一般有权限连接到管理工具直接删除
添加约束
1 2 3 4 5 6 7 8 9 10 11 12 13 14
--添加一个check约束 alter table 表名 add constraint 约束名 check(表达式)
eg.添加工资字段约束,工资必须在1000-1000000之间 alter table people add constraint CK_People_PeopleSal1 check(PeopleSalary>=1000 and PeopleSalary<=1000000)
--添加约束(主键) alter table 表名 add constraint 约束名 primary key(列名) --添加约束(唯一) alter table 表名 add constraint 约束名 unique(列名) --添加约束(默认值) alter table 表名 add constraint 约束名 default(默认值) for 列名 --添加约束(外键) alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(列名(通常为主键))
数据的插入、更新、删除
插入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14
--向部门表插入数据 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'人事部','备注'
修改和删除数据
修改
1 2 3 4 5 6 7 8 9
--语法: update 表名 set 字段1=新值1,字段2=新值2... where 条件
eg.1.工资调整 update People set PeopleSalary= PeopleSalary+1000(改每一条数据,没有where条件) 2.给员工编号为7的人加薪 update People set PeopleSalary=PeopleSalary+666 where PeopleId = 7 3.将部门编号为1的部门人员工资低于15000的调整成15000 update People set PeopleSalary=15000 where DepartmentId=1 and PeopleSalary<15000
删除
1 2 3 4 5 6 7 8 9 10 11 12 13 14
--语法: delete from 表名 where 条件
eg.删除市场部(部门编号3)中工资大于1w的人 delete from Peppple where DepartmentId = 3 and PeopleSalary > 10000
--关于删除(drop,truncate,delete) drop table People --删除表对象 truncate table People --删除数据(清空数据) --truncate 和 delete 的区别 1.truncate清空所有数据,不能有条件;delete可以带条件,删除符合条件的数据 2.自动编号;假设表中自动编号为1,2,3,4,5 使用truncate清空再添加数据,编号仍为1,2,3,4,5 使用delete删除数据再添加数据,编号变成了6,7,8,9,10(删除的自动编号将永远不存在)
查询
(运用SQL中常用运算符)
基本查询
查询所有列所有行 select * from Department 查询指定列(姓名,性别,生日,月薪) select PeopleName,PeopleSex,PeopleBirth,PeopleSalary from People 查询指定列(姓名,性别,生日,月薪)(显示中文列名)(不会修改表) select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪 from People 查询员工所在城市(不重复) select distinct(PropleAddress) from People 假设准备加工资(上调20%),查询出加工资前和后的员工数据 select PeopleName,PeopleSex,PeopleSalary,PeopleSalary*1.2 加薪后工资 from People
条件查询
查询性别为女的员工信息 select * from People where PeopleSex = '女' 查询性别为女,工资大于等于100000元的员工信息(多条件) select * from People where PeopleSex = '女'and PeopleSalary>=10000 查询月薪大于等于10000的员工,或者月薪大于等于8000的女员工
1 2 3 4
主要结构: select * from People where 条件1 or 条件2
select * from People where PeopleSalary >= 10000 or (PeopleSex = '女'and PeopleSalary>=8000)
–排序 查询所有员工信息,根据(order by)工资排序,降序(asc:升序(默认),desc:降序) select * from Peopke order by PeopleSalary desc 查询所有员工信息,根据名字长度排序(降序) select * from Peopke order by len(PeopleName) desc 查询出工资最高的5个人的信息 select top 5 * from Peopke order by PeopleSalary desc 查询出工资最高的10%员工的信息 select top 10 precent * from Peopke order by PeopleSalary desc
–null:空值.注意区分空字符串(‘’) 查询出地址没有填写的员工信息 select * from People where PeopleAddress is null 查询出地址已经填写的员工信息 select * from People where PeopleAddress is not null
查询出80后员工信息
1 2 3
select * from People where PeopleBirth between '1980-1-1' and '1989-12-31'
select * from People where year(PeopleBirth) between 1980 and 1989
查询出工资比赵云高的人的信息 select * from People where PeopleSalary > (select PeopleSalary from People where PeopleName='赵云') 查询所有员工信息,添加一列,显示生肖
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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 '猪' else '未知' end 生肖 from People
定义一个函数,根据生日计算生肖
1 2 3 4 5 6 7 8 9
--定义函数 create function GetZodiac(@BirthDate smalldatetime) returns nvarchar(10) as begin declare @Zodiac nvarchar(10) set @Zodiac = ''
select *,dbo.GetZodiac(PeopleBirth) as Zodiac from People