sql server(二)

1. T-SQL插入数据

1.1 单条数据插入

use testDB
go
--1)  insert (into) 表名(列名,列名....) values (值,值....)
insert into ProductType (TypeName)
values ('工具类')
--2) insert into 表名(列名,列名....) select 值,值....
insert ProductType(TypeName) 
select '鞋类'

1.2 多条插入

--一次性插入多条  批量插入操作
--1)
insert into ProductType (TypeName)
values ('工具类1'),('工具类2'),('工具类3'),('工具类4')

--2) union 去重   union all  允许重复  union all效率比union高
insert TestTable(id,Name,Age) 
select 5,'ddddd',23   union
select 6,'dd',25 union
select 7,'bbdddbb',20 union

insert TestTable(Name,Age)
select 'fff',21 union 
select 'fff',21

1.3 克隆表数据
将一张表的数所复制到另一张表

--1) 目标表在数据库已经存在
insert into Test(Name) ---目标表
select TypeName from ProductType  --源表

--2)目标表之前数据库中不存在,执行操作时自动创建的
select TypeName into Test2 --目标表
from ProductType

2. T-SQL修改数据

更新数据

--主键不可以修改
--如果不加where条件,会把整张表的数据都修改了
use testDB
go

update Test set MName='ssss',Age=30
where Id=5
--where Id=5 (and | or) 条件 

3. T-SQL删除数据

删除数据

--数据删除 : 只是删除数据,表还在;  连同表一起删除
--1)删除数据  delete from Table 不加条件,会删除整个表数据,几乎都要加where条件  
--标识列  值还是接着删除前的值而自增,而不是从初始值开始
--delete语句会造成标识列的值不连续
delete from Test where Id = 20

--如果我们想删除数据,让标识列的值恢复到初始值
truncate table Test --表数据清空,恢复到初始化,标识列也恢复
--truncate效率比delete from Test高.
--delete每删除一条数据,都会在日志里记录.truncate不会记录日志,不激活触发器,drop truncate 是即时操作,不能rollback,delete update insert 事务中,可以恢复.
--慎用truncate 一旦删除,不能恢复.

4. T-SQL查询数据

4.1 单表查询

1.查询所有数据
select * from UserInfos
  
2.查询表的部分数据
--select UserId,UserName,Age from UserInfos where 条件
select UserId,UserName,Age from UserInfos 
where UserId=1001

3.列命别名  
--给列命别名 
--列名 as 别名     列名 别名     别名=列名
--select UserId as 用户编号,UserName 用户名,年龄= Age
--from UserInfos
select UserId as 用户编号,UserName 用户名,年龄= Age
from UserInfos

4.排序
--主键,默认就有排序功能  从小到大 --升序 asc  
--降序:从大到小  desc
--不管是否有条件,还是分组,order by 永远放在最后
select UserId,UserName,Age from UserInfos
order by Age,UserId

4.2 模糊查询

模糊查询：
--select UserName,Age from 表名 
--where UserName like ....

--like  SQL 4种匹配模式：
1. % 0个或多个
--1) like '%2%' 包含于 2
select * from UserInfos where UserId like '%2%'
--2) like '%u' 以u结尾
select * from UserInfos where UserName like '%u'
--3)like '1%' 以1开头
select * from UserInfos where UserName like 'l%'

2._  匹配单个字符
--5个字符长度
select * from UserInfos where UserName like '_____'

3.[] 范围匹配  括号中所有字符中的一个
select * from UserInfos where UserName like 'ad[mnd]in'
select * from UserInfos where UserName like 'ad[m-p]in'

4.[^] 不在括号中所有字符之内的单个字符
select * from UserInfos where UserName like 'ad[^abc]in'

4.3 范围查询

1.范围查询
--select  from  where 子句 条件 -- 给定范围
  --1) 比较运算符 > < >= <= <> 
  	select * from UserInfos
 	where Age <30 and DeptId>1
  --2)in (2,3,4)    not in (2,3,4) 不在这个范围之内
    select * from UserInfos
    where Age not in (30,35,24)
  --3) between  and  推荐  等价于 >= and <=
   select * from UserInfos
   where Age between 20 and 33

2.前面多少条、百比比
--   top n / n percent
select top 10 * from UserInfos
select top 100 percent * from UserInfos

 --子查询
 select * from UserInfos 
 where DeptId in
 (
   select DeptId from DeptInfos where DeptId>1
 )

4.4 聚合函数

聚合函数文章

聚合函数：对一组值执行计算并返回单一的值。
   经常与select语句中group by结合使用。
五种聚合函数：count  记录个数  sum 求和   avg 求平均  max 最大值  min 最小值

--select count(1) 伪造列  
select 1 from UserInfos 
select count(1) Record from UserInfos  --一般统计一个表的记录数 

--sum 求和 相加
select sum(Age) from UserInfos

--agv 求平均
select avg(Age) from UserInfos

--max 求最大
select max(Age) from UserInfos

--min 求最小
select min(Age) from UserInfos

4.5 分组函数

--select ....
--where .....
--group by 列名,列名   结合聚合函数，根据一列或多个列对结果集进行分组。

--统计各部门有多少个用户
--select 出现的列名,必须出现在group by之后或包含在聚合函数中
Select DeptId,count(1) 用户数 from UserInfos
--where Age>26
group by DeptId
having DeptId>1  --分组后的筛选条件
order by DeptId desc

4.6 内连查询

连接查询 根据两个或多个表之间的关系，从这些表中查询数据。
实现多表查询。
内连接：inner join  使用比较运算符 = > < >= <= <> 进行表间的比较，查询与条件相匹配的数据。

等值连接 
结果：相匹配的数据查询出来，显示匹配出来的结果，如果没有匹配上，就没有结果。
显示连接    表 inner join  表  on 连接条件  where 
隐式连接  select .... from  表,表 where 关联条件

--inner join  on 关联条件
select UserId,UserName,Age,u.DeptId,DeptName
from UserInfos u
inner join DeptInfos d on d.DeptId=u.DeptId 
--and ....
where Age>25

--隐式写法
select UserId,UserName,Age,u.DeptId,DeptName
from UserInfos u, DeptInfos d 
where d.DeptId=u.DeptId and Age>40

4.7 外连查询

外连接
外连接分类：左外连接、右外连接 全外连接  简称为：左连接  右连接  全连接

左连接 left (outer) join   on   返回左表的所有行，右表中没有匹配上，对应的列就显示NULL
结果：左表：所有行   右表：行数与左表相同，没有匹配上，显示NULL

右连接 right (outer) join  on  与左连接相反，返回的右表的所有行，左表进行匹配，左表中没有匹配上的，对应的列显示NULL
结果：右表：所有行  左表：行数与右表相同，没有匹配上的，显示NULL

--左连接
select * from UserInfos u  --左表
left outer join DeptInfos d  -- 右表  outer 可以省略
on u.DeptId=d.DeptId
--结果：左表：所有行   右表：行数与左表相同，没有匹配上，显示NULL

--右连接 右外连接
select * from UserInfos u  --左表
right outer join DeptInfos d  -- 右表  outer 可以省略
on u.DeptId=d.DeptId
--结果：右表：所有行  左表：行数与右表相同，没有匹配上的，显示NULL

4.8 全连查询

连接：内连接、外连接（左连接、右连接、全连接）、交叉连接

全连接：full (outer) join  全外连接  返回左表和右表中所有行，当某一行在另一个表中没有匹配，另一个表中的列返回NULL

交叉连接 cross join   迪卡尔积
如果不带where子句时，返回被连接的两个表的迪卡尔积，返回的行数是两个表行数的乘积。
带where子句，等价于inner join 返回的是匹配的数据。

--全连接 
select * from UserInfos u
full  join DeptInfos d  --outer 可以省略
on u.DeptId=d.DeptId
--结果:左表和右表 所有数据都会出来,每一行的数据,如果在另一个表里匹配不上,就是对应显示NULL

--交叉连接 cross join   等价于 inner join  
select * from UserInfos u
cross  join DeptInfos d  --outer 可以省略
where u.DeptId=d.DeptId --只显示匹配出来的行数
--4*4=16
--返回的行数是两个表行数的乘积。