SQL Server基础

SQL Server基础

创建数据库

create datebase 数据库名字
on	--数据文件
(
--on携带的信息
   name='数据库名字', 	--逻辑名称
   filename='D:\DATA\数据库名字.mdf',		--物理路径和名称
   size=5MB,	--文件的初始大小
   fiegrowth=2MB	--数据库初始大小满了之后自增一次的大小（也可以写百分比）
)
log on	--日志文件
(
--log on携带的信息
   name='数据库名字_log', 	--逻辑名称 
   filename='D:\DATA\数据库名字_log.ldf',		--物理路径和名称
   size=5MB,	--文件的初始大小
   fiegrowth=2MB	--数据库初始大小满了之后自增一次的大小（也可以写百分比）
)

但是如果有数据库直接使用上面的脚本会报错

所以我们需要在上面添加判断

注意这个只限于学习，以防止误删数据库

if exists(select * from sys.databases where name='数据库名字')
	drop database 数据库名字		--如果有此数据库就删除这个数据库再运行下面代码
	
create datebase 数据库名字		--创建数据库
on	--数据文件
(
--on携带的信息
   name='数据库名字', 	--逻辑名称
   filename='D:\DATA\数据库名字.mdf',		--物理路径和名称
   size=5MB,	--文件的初始大小
   fiegrowth=2MB	--数据库初始大小满了之后自增一次的大小（也可以写百分比）
)
log on	--日志文件
(
--log on携带的信息
   name='数据库名字_log', 	--逻辑名称 
   filename='D:\DATA\数据库名字_log.ldf',		--物理路径和名称
   size=5MB,	--文件的初始大小
   fiegrowth=2MB	--数据库初始大小满了之后自增一次的大小（也可以写百分比）
)

---

创建数据表

use 数据库名字		--切换数据库

create table 表名
(
字段名1 数据类型,
字段名2 数据类型
)

比如我们要建表（部门、职级、员工）

部门表

create table 部门表表名
(
    -- 部门编号，设置为int类型并且为主键和自增
	DepartmentId int primary key identity(1,1),
    -- 部门名称，并且设置不为空
    DepartmentName varchar(50) not null,
    -- 部门描述
    DepartmentRemark text
)

防止表冲突，尽肯能在前面加上if判断（和上面一样，同样只建议在学习环境使用，防止误删数据）

-- 判断是否有这张表，并且类型为U（用户创建的）
if exists(select * from sys.objects where name='部门表表名' type='U')

create table 部门表表名
(
    -- 部门编号，设置为int类型并且为主键和自增
	DepartmentId int primary key identity(1,1),
    -- 部门名称，并且设置不为空
    DepartmentName varchar(50) not null,
    -- 部门描述
    DepartmentRemark text
)

职级表

create table 职级表表名
(
    -- 职级编号，设置为int类型并且为主键和自增
	RankId int primary key identity(1,1),
    -- 职级名称，并且设置不为空
    RankName varchar(50) not null,
    -- 职级描述
    RankRemark text
)

员工表

create table 员工表表名
(
    -- 又因为员工有部门和职级，所以我们需要关联上（外键），并且使用references外键所在表（字段名）来检查数据是否存在，不存在的数据是无法存入的
    -- 外键 将其他表的主键作为我的字段
      DepartmentId int references 部门表表名(DepartmentId) not null,
      RankId int references 职级表表名(RankId) not null,
    
    -- 员工编号，设置为int类型并且为主键和自增
	PopleId int primary key identity(1,1),
    -- 员工名称，并且设置不为空
    PopleName varchar(50) not null,
    -- 员工性别 约束只能存入男或者女，默认为男
    PopleSex nvarchar(1)default('男') check(PopleSex='男'orPopleSex='女') not null,
    -- 员工生日 不为空
    PopleBirth datetime not null, 
    -- 员工月薪 不为空,总长度为12，小数点后面两位，约束为u员工工资大于等于1000，小于等于十万
    PopleSalary decimal(12.2) check(PopleSalary>=1000 and PopleSalary<=100000) not null,
    -- 员工电话,unique表示电话数据为不相同的
    PoplePhone Varchar(20) unique not null,
    -- 员工住址
	PopleAddress varchar(300),
    -- 员工添加时间，default(getdate())获取当前时间设置为默认值
    PopleAddTime smalldatetime default(getdate())
 )

---

表结构修改

添加列

alter table 表名 add 新列名 数据类型

-- 给员工表添加一列邮箱
alter table 员工表名 add 邮箱列名 varchar(300)

删除列

alter table 表名 drop columm 列名

-- 删除刚刚添加的邮箱列
alter table 员工表名 drop columm 邮箱列名

修改列

alter table 表名 alter column 被修改列名 数据类型 
-- 重新添加邮箱，并且修改邮箱列的数据类型为varchar(200)
alter table 员工表 alter column 邮箱列 varchar(200) 

维护约束(删除、添加)

删除约束

alter table 表名 drop comstraint 约束名
-- 删除员工表的月薪约束
alter table 员工表名 drop comstraint 约束名

-- 添加约束
alter table 表名 add constraint 约束名 check(约束表达式)
-- 给员工工资列添加工资在1000-100000之间的约束
alter table 员工表名 add constraint 约束名 check(PopleSalary>=1000 and PopleSalary<=100000)

-- 添加check约束
alter table 表名 add constraint 约束名 check(约束表达式)
-- 添加主键约束
alter table 表名 add constraint 约束名 primary key(列名)
-- 添加唯一约束
alter table 表名 add constraint 约束名 unique(列名)
-- 添加默认值约束
alter table 表名 add constraint 约束名 default 默认值 for 列名
-- 添加外键约束	外键就是吧另一张表的主键作为我的字段
-- foreign key(列名)就是外键
-- references 关联表名(列名\主键)	列名一般就是主键名
alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(列名\主键)

插入数据

-- 向部门表插入数据	根据上面建表时候的基础状态去插入
-- 部门编号为自增，不用管，只需要出啊如部门名称和部门描述就行了
-- 部门编号，设置为int类型并且为主键和自增
	-- DepartmentId int primary key identity(1,1),
    -- 部门名称，并且设置不为空
    -- DepartmentName varchar(50) not null,
    -- 部门描述
    -- departmentRemark text
    
install into 部门表名称(DepartmentName,DepartmentRemark) values('市场部','.....')
install into 部门表名称(DepartmentName,DepartmentRemark) values('市场部','.....')
install into 部门表名称(DepartmentName,DepartmentRemark) values('市场部','.....')
-- 也可以简写为 但是尽量还是使用上面的全部写法
install into 部门表名称 values('硬件部','.....')

-- 一次性加入多条数据
install into 部门表名称(DepartmentName,DepartmentRemark)
select '测试部','.....')union
select '实施部','.....')union
select '产品部','.....')		-- 最后一行不用写union

修改数据

update 表名 set 字段1=值1,字段2=值2		-- 这样会把表中字段1 和 字段2 的全部数据修改掉，所以需要添加修改条件
update 表名 set 字段1=值1,字段2=值2 where 条件	

-- 对员工表的所有人的工资加1000元 
update 员工表名 set 工资字段名=工资字段名+1000

-- 给员工表的张三加薪500元（员工编号为7）
update 员工表名 set 工资字段名=工资字段名+500 where 员工编号字段名 =7

-- 软件部门在员工表有外键，编号为1  将人员工资低于一万的调整为一万
-- and 表示两个条件都成立才运行
update 员工表名 set 工资字段名=10000 where 部门id=1 and 工资字段名 <10000

-- 修改人员名为刘备的人的工资为原来两倍 并且把刘备的地址修改为北京
update　员工表名 set 工资字段名=工资字段名*2 , 地址字段='北京' where 用户名称字段='刘备'

删除数据

delete from 表名 where 条件

-- 删除员工表所有记录
delete from 员工表名	-- 危险操作！！！！！

-- 删除市场部（编号为3）中工资大于一万的人
delete from 员工表名 where 部门id=3 and 工资字段名> 10000

-- 删除相关
drop table 员工表名  -- 会把整个表都删除了，表也不存在了
truncate table 员工表名	-- 删除数据，表对象和结构存在
delete table 员工表名 -- 删除数据，表对象和结构存在
-- truncate直接就删除了，是不能带条件判断的		删除带自增的数据时，会从起点开始
-- delete可以带where条件表达式		删除带自增的数据时，会从被删除前最后一位开始

---

SQL基本查询

-- 查询所有列的所有行
select * from 表名 
-- 查询指定列，显示姓名、性别、生日、月薪、电话
select 姓名列名,性别列名,生日列名,月薪列名,电话列名 from 被查询列名

-- 查询指定列，显示姓名、性别、生日、月薪、电话（显示中文列名 设置别名）
select 姓名列名 姓名,性别列名 性别,生日列名 生日,月薪列名 月薪,电话列名 电话 from 被查询列名

-- 查询员工所在城市
select 城市列名 from 员工表名

-- 上面查询员工城市把重复数据也显示出来了，去重显示（在查询的列名前面加上distinct）
select distinct(城市列名) from 员工表名

-- 准备上调工资20%（注意是准备）查询出加工资后的员工数据
select 员工名字列名,员工工资列名,员工工资列名*1.2 加薪后的工资 from 用户表名

---

条件查询

常用运算符

=：等于，比较是否相等及赋值
!=：比较不等于
>：比较大于
<：比较小于
>=：比较大于等于
<=：比较小于等于
IS NULL：比较为空
IS NOT NULL：比较不为空
in：比较是否在其中
like：模糊查询
BETWEEN...AND...：比较是否在两者之间
and：逻辑与（两个条件同时成立表达式成立）
or：逻辑或（两个条件有一个成立表达式成立） 
not：逻辑非（条件成立，表达式则不成立；条件不成立，表达式则成立）

-- 查询性别为女的员工信息
select * from 员工表名 where 性别列名 = '女'

-- 查询工资大于等于一万的员工信息
select * from 员工表名 where 工资列名>=10000

-- 查询性别女且工资大于一万的员工信息
select * from 员工表名 where 性别列名='女' and 工资列名>=10000

-- 查询月薪大于等于一万的员工，或者月薪大于等于八千的女员工
select * from 员工表名 where 工资列名>=10000 or (工资列名>=8000 and 性别列名='女')

-- 查询月薪在10000-20000之间的员工信息
select * from 员工表 where 工资列名>=10000 <= 10000
-- 第二种写法 两者之间
select * from 员工表 where 工资列名 between 10000 and 10000

-- 查询地址在武汉或者北京的员工信息
select * from where 员工地址列 ='武汉' or 员工地址列 ='北京'
-- 或者使用in关键字进行查询
select * from 员工地址列 in('武汉','北京')


-- 查询所有员工信息，根据工资排序，降序排序（asc升序，升序为默认值，可以不写 desc降序）
select * from 员工列名 order by 工资列名 desc
-- 查询所有员工信息，根据名字长度排序（降序）
-- len()是sqlsever提供的长度函数
select * from 员工表名 order by len(员工姓名字段) desc

-- 查询工资最高的五个人
select top 5 * from 员工列名 order by 工资列名 desc
-- 查询工资最高的10%的员工信息
select top 10 percent * from 员工列名 order by 工资列名 desc

-- 查询地址为null的员工信息	必须使用is关键字，直接查询=null是无效的
select * from 员工表名 where 地址列名 is null

-- 查询地址不为null的员工信息	必须使用is not关键字，直接查询!=null是无效的
select * from 员工表名 where 地址列名 is not null

-- 查询所有的80后员工信息
select * from 员工表 where 生日列名 >= '1980-1-1' and 生日列名 <= '1989-12-31'
-- 或者可以使用between and
select * from 员工表 where 生日列名 between '1980-1-1' and '1989-12-31'
-- sqlsever还提供了year()函数来取出字段中的年份，可以通过这个函数来查询所有80后的信息
select * from 员工表 where year(生日列名) between 1980 and 1989


-- 查询年龄在30-40 之间，并且工资在15000-30000 之间的员工信息
select * from 员工表 where
(year(getdate())-year(生日列名) >= 30 and year(getdate())-year(生日列名) <= 40) and
(工资列名 >= 15000 and 工资列名 <= 30000)
-- 同理也可以使用between and
select * from 员工表 where
(year(getdate())-year(生日列名) between 30 and 40) and
(工资列名 between 15000 and 30000)


-- 查询出巨蟹座的员工信息6.22-7.22
-- mouth()	提取月份
-- day()	提取日期
select * from 员工表 where 
(month(生日列名) = 6 and DAY(生日列名) >= 22) or
(month(生日列名) = 7 and DAY(生日列名) <= 22)

-- 查询工资比张三高的人（嵌套查询）
select * from 员工表 where 工资列名 > 
(select 工资列名 from 员工表 where 员工姓名列 = '张三')

-- 查询和张三在一个城市的人
select * from 员工表 where 员工住址列 > 
(select 员工住址列 from 员工表 where 员工姓名列 = '张三')


-- 查询出属羊的人员信息
-- 鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
-- 4、 5、 6、 7、8、 9、10、11、0、 1、2、3
select * from 员工表 where year(生日列名)%12=11 -- 年份除以12取余，等于11的就是属羊的

-- 查询全部的员工信息，并且显示其对应的生肖
select *,
-- 这里使用if判断结构 when是条件，当。。。时候
case
	when 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 '猪'
    else '生肖错误'
end
from 员工表名

-- 也可以吧判断条件写到case上，去简化sql
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 员工表名

---

模糊查询

模糊查询通配符含义

%：代表匹配0个字符、1个字符或多个字符。
_：代表匹配有且只有1个字符。
[]：代表匹配范围内
[^]：代表匹配不在范围内

-- 查询姓刘的员工信息
select * from 员工表名  where 员工姓名列 like '刘%'

-- 查询名字中带刘的员工信息
select * from 员工表名  where 员工姓名列 like '%刘%'

-- 查询名字中带刘或者张的员工
select * from 员工表名  where 员工姓名列 like '%刘%' or 员工姓名列 like '%张%'

-- 查询两个字名字带刘的员工
select * from 员工表名  where 员工姓名列 like '刘_'

-- 使用SUBSTRING()来截取数据 如果是要查找姓名三个字且最后一个字是香的员工
-- SUBSTRING(数据,截取从哪开始,长度)
select * from 员工表名 where SUBSTRING(姓名列,3,1) = '香' and LEN(姓名列) = 3


-- 查询出电话号码开头138的员工信息,第4位可能是7，可能8 ，最后一个号码是5
select * from 用户表名 where 电话列名 like '138[7,8]%5'

-- 电话号码开头133的员工信息,第4位是2-5之间的数字 ，最后一个号码不是2和3
select * from 用户表名 where 电话列名 like '133[2,3,4,5]%[^2,3]'
-- 或者使用[2-5]%[^2-3]
select * from 用户表名 where 电话列名 like '133[2-5]%[^2-3]'