数据库一

字符串类型的区别：

 

--char:定长，char(10),无论存储数据是否真的到了10个字节，都要占用10个字节。
--比如：char(10)存储“ab”,仍然占用10字节。

--varchar:变长，varchar(10)，有多少字节它就占用多少，超过10的存不进去。
--比如：varchar(10)，存储“ab”,占用2个字节。

--text：长文本

--char,varchar,text前面加n：存储的是Unicode字符，对中文友好。
--varchar(100)：存储100个字母或者50个汉字。
--nvarchar(100)：存储100个字母或者100个汉字。

 

1、创建数据库：

create database DBTEST

2、创建数据表：

--切换数据库
use DBTEST

--建表（部门、职级、员工）
create table Department --部门表
(
    --部门编号
    --primary key：设置主键
    -- identity(1,1):自动增长，初始值1，增长步长1
    DeparementId int primary key identity(1,1),
    --部门名称
    DeparementName nvarchar(50) not null,
    --部门描述
    DeparementRemark text
)

create table [Rank] --职级表
(
    --职级编号
    --primary key：设置主键
    -- identity(1,1):自动增长，初始值1，增长步长1
    RankId int primary key identity(1,1),
    --职级名称
    RankName nvarchar(50) not null,
    --职级描述
    RankRemark text
)

create table People --员工表
(
    PeopleId int primary key identity(1,1),--员工编号
    DeparementId int references Department(DeparementId) not null,--引用部门表的主键作为我的外键
    RankId int references [Rank](RankId) not null,--引用职级表的主键作为我的外键
    PeopleName nvarchar(50) not null,--姓名
    PeopleSex nvarchar(1) default('男') check(PeopleSex='男' or PeopleSex='女'),--性别
    PeopleBirth smalldatetime not null,--生日
    PeopleSalary decimal(12,2) check(PeopleSalary>=1000 and PeopleSalary<=100000),--月薪，12位数，小数点后2位。
    PeoplePhone varchar(20) unique not null,--电话，unique:唯一约束
    PeopleAddress varchar(300),--地址
    PeopleAddTime smalldatetime default(getdate())--datetime和smalldatetime都可以表示时间类型，getdate（）获取系统当前时间。
)

 3、修改表结构

--修改表结构

--（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)

4、维护约束

--删除约束
--alter table 表名 drop constraint 约束名，约束名在“表名-设计-列名属性 -check约束”
--删除月薪的约束
alter table People drop constraint CK__People__PeopleSe__4D94879B
--添加check约束
--alter table 表名 add constraint 约束名 check(表达式)，添加工资字段的约束，工资范围在1000-1000000之间
alter table People add constraint CK__6 check(PeopleSalary>=1000 and PeopleSalary<=100000)
--添加主键约束
alter table 表名 add constraint 约束名 primary key(列名)
--添加唯一约束
alter table 表名 add constraint 约束名 unique(列名)
--添加默认值约束
alter table 表名 add constraint 约束名 default 默认值 for 列名
--添加外键约束
alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(主键)

5、插入数据

--向部门表插入数据
insert into Department([DeparementName],[DeparementRemark])values('市场部','......')
insert into Department([DeparementName],[DeparementRemark])values('软件部','......')
insert into Department([DeparementName],[DeparementRemark])values('企划部','......')
--一次性插入多行数据
insert into Department([DeparementName],[DeparementRemark])
select '测试部','这是测试部' union
select '实施部','这是实施部' union
select '产品部','这是产品部' 

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

--向员工表插入数据-----------------------

insert into People(DeparementId,RankId,PeopleName,PeopleSex,PeopleBirth,
PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(6,1,'刘备','男','1995-8-8',5000,'13127978688','中国',getdate())

6、数据的修改

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

--修改工资，每个人都加薪1000元
update People set PeopleSalary= PeopleSalary+1000

--为员工编号为3的人加薪2500
update People set PeopleSalary= PeopleSalary+2500 where PeopleId=3

--将软件部（部门编号为3）人员工资低于10000的调整成15000
update People set PeopleSalary= 15000
where DeparementId =6 and PeopleSalary<10000

--修改刘备的工资是以前的两倍，并且把刘备的地址修改为北京
update People set PeopleSalary= PeopleSalary*2,PeopleAddress='北京'
where PeopleName='刘备'  

7、数据的删除

--删除数据
--delete from 表名 where 条件

--删除员工表所有记录
delete from People

--删除市场部（部门编号为1）人员工资大于1万的员工
delete from People where DeparementId=1 and PeopleSalary>10000

--关于删除的几种语法
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删除数据再添加数据，删除的自动编号将永远不存在了，编号变成了6,7,8,9,10

 8、基本查询

--查询所有列所有行
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

9、条件查询一

select * from People
--查询性别为男的员工信息
select * from People where PeopleSex='男'

--查询工资大于等于20000的员工信息
select * from People where PeopleSalary>=20000

--查询出性别为女，工资大于等于10000的员工信息
select * from People where PeopleSex='女' and PeopleSalary>=10000

--查询出生年月在1995-1-1之后，而且月薪大于等于8000的女员工
select * from People where PeopleBirth>='1980-1-1' and (PeopleSalary>=8000 and PeopleSex='女')

--查询月薪大于等于10000的员工，或者月薪大于等于8000的女员工。
select * from People where PeopleSalary>=10000 or (PeopleSalary>=8000 and PeopleSex='女')

--查询月薪在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('武汉','北京')

--排序
--查询所有的员工信息，根据工资排序,降序
--asc：升序（默认升序，可以不写）         desc：降序
select * from People order by PeopleSalary desc

--查询所有员工信息，根据名字长度排序（降序排序）
select * from People order by len(PeopleName)desc

--查询出工资最高的5个人的信息
select top 5 * from People order by PeopleSalary desc

--查询出工资最高的10%的员工信息
select top 10 percent * from People order by PeopleSalary desc

--null：空值
--查询出地址没有填写的员工信息
select * from People where PeopleAddress is null

--查询地址已经填写的员工信息
select * from People where PeopleAddress is not null

10、条件查询二

--查询80后的员工信息
select * from People where PeopleBirth>='1980-1-1' and PeopleBirth>='1989-12-31'--第一种写法
select * from People where PeopleBirth between '1980-1-1' and '1989-12-31'  --第二种写法
select * from People where year(PeopleBirth) between 1980 and 1989  --第三种写法

--查询30-40岁之间，并且工资在15000-30000之间的员工信息
--假设 年龄=当前年份 - 生日年份
select * from People where 
(year(getdate())-year(PeopleBirth) >=30 and year(getdate())-year(PeopleBirth)<=40) 
and (PeopleSalary>=15000 and PeopleSalary<=30000) -- 第一种写法

select * from People where 
(year(getdate())-year(PeopleBirth) between 30 and 40)
and (PeopleSalary>=15000 and PeopleSalary<=30000) -- 第二种写法

--查询出星座是巨蟹座的员工信息（6.22-7.22）
select * from People where
(month(PeopleBirth)=6 and day(PeopleBirth)>=22)
or
(month(PeopleBirth)=7 and day(PeopleBirth)<=22)

--查询工资比春哥高的信息
select * from People where PeopleSalary>
(select PeopleSalary from People where PeopleName='春哥')

--查询和春哥在一个城市的人的信息
select * from People where PeopleAddress=(select PeopleAddress from People where PeopleName='春哥')

--查询生肖是猪的人员信息
--鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
--4    5   6   7   8   9  10  11   0   1   2   3
select * from People where year(PeopleBirth)%12 =3

--查询所有的员工信息，添加一列，显示生肖
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 

11、模糊查询

--查询姓刘的员工信息
select * from People where PeopleName like '刘%'

--查询出名字中含有“云”的员工信息
select * from People where PeopleName like '%云%'

--查询出名字中含有“云”或者“备”的员工信息
select * from People where PeopleName like '%云%' or  PeopleName like '%备%'

--查询姓刘的员工信息，名字是2个字的
select * from People where PeopleName like'刘_'

--查询名字第三个字为“红”，名字一共是三个字的员工信息
select * from People where PeopleName like '__红'

--查询出电话号码开头为131的员工信息
select * from People where PeoplePhone like '131%'

--查询电话号码开头是131的，第四位好像是7或者8，最后一个号码是5
select * from People where PeoplePhone like'131[7,8]%5'

--查询电话号码开头是131，第四位好像是2-5之间，最后一个号码不是2和3
select * from People where PeoplePhone like '131[2,3,4,5]%[^2,3]'--第一种写法
select * from People where PeoplePhone like '131[2-5]%[^2-3]'--第二种写法