sql server相关学习sql语句

if exists(select * from sys.objects where name='Department' and type='U')drop table Departmentcreate 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 Peoplecreate 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 列名 数据类型--修改地址为 200alter 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 Mytestselect *from Departmentselect * from [Rank]select * from People--数据crud--修改--update 表名 set 列名='xxx' where 条件--update 表名 set 列名='xxx'， 列名='yyy' where 条件update Department set DepartmentName='经理部' where DepartmentId=8update People set PeopleSalary=PeopleSalary+1000 update People set PeopleSalary=15000 where RankId=3 and PeopleSalary'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)5000select* 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--? 3select 2000%12select *,casewhen 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)%12when 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 Peopleselect *from People where PeopleAddress not like '%南%' --查询名字2个字的 第一个字为刘select * from People where SUBSTRING(PeopleName,1,1)='刘' and LEN(PeopleName)=2select * from People where SUBSTRING(PeopleName,2,1)='备' and LEN(PeopleName)=2select *from People where PeoplePhone like '1516%'--开头4位是1516 第五位为1或者2 最后一位为9select *from People where PeoplePhone like '1516[1,2]%9'--开头4位是1516 第五位为1到8 最后一位为不为4到8select *from People where PeoplePhone like '1516[1-8]%[^4-8]'