SQLserver数据库之基本增删改查操作（2）

--插入单行数据  insert into 表名(列名) values (列值)
insert into Department(DepName) values('');

--直接拿现有表数据创建一个新表并填充  select 新建表列名 into 新建表名 from 原表名
select EmpId,EmpName into student from Employee;

--将现有表数据添加到一个已有表    insert into 已有的新表(列名) select 原表列名 from 原表名
insert into student(EmpId,EmpName) select Uid,UName from Users;

--使用union关键字合并数据进行插入多行    insert 表名(列名) select 列值 union select 列值
insert student(EmpId,EmpName) select '11','tom' union select '12','like';

--删除该表
drop table student;

--注意：删除表数据，但表的结构、列、约束、索引等不会被删除；
--不能用于有外建约束引用的表  truncate table <表名>
truncate table student;

--删除<满足条件的>行 delete from 表名 where 删除条件
delete from Department where DepId=8;

--根据条件修改表数据  update <表名> set <列名=更新值> [where <更新条件>]
update Department set DepName='空姐部' where DepId=5;

--精确查询
select * from Employee where EmpName='李四';

--使用like进行模糊查询  desc降序 asc升序
select * from Employee where EmpName like '%李%' order by Age asc;

--查询null行
select * from Department where DepName is null;

--查询非null的行
select * from Department where DepName is not null;

--使用between在某个范围内进行查询 1-3条数据
select * from Employee where EmpId between 1 and 3;

--in查询  查询表Employee中age为23和15的数据
select * from Employee where Age in('23','15');

--在查询中使用ＡＳ更改列名
select EmpId as ID,EmpName as 姓名 from Employee;

--在查询中使用常量    查询表Employee，显示EmpId列，并添加地址列为ID2，其列值都为'1'
select EmpId,'1' as ID2 from Employee;

--使用group by进行分组查询 在表Employee中查询，按sex字段分组
select sex as 性别,AVG(Age) as 平均年龄 from Employee group by sex;

--使用having子句进行分组筛选 显示分组后count(Age)>1的行，由于where只能在没有分组时使用，
--分组后只能使用having来限制条件。
select sex as 性别,AVG(Age) as 平均年龄 from Employee group by Sex having COUNT(Age)>1;

--查询前3行的所有数据
select top 3 * from Employee;

--查询该表3%的数据，percent为关键字
select top 3 percent * from Employee;

--多表连接查询
select d.DepName,e.EmpName from Department d,Employee e where d.DepId=e.DepId;

--内连接 如果表中有至少一个匹配，则返回行  select * from 表1 inner join 表2 on 表1.id=表2.id
select e.EmpName,d.DepName from Employee e inner join Department d on e.DepId=d.DepId;

--左连接    即使右表中没有匹配，也从左表返回所有的行    left join
select * from Employee e left join Department d on e.DepId=d.DepId;

--右连接    即使左表中没有匹配，也从右表返回所有的行    right join
select * from Employee e right join Department d on e.DepId=d.DepId;

--完全连接 只要其中一个表中存在匹配，则返回行        full join
select * from Employee e full join Department d on e.DepId=d.DepId;

--子查询
select * from Department where DepId in(select DepId from Employee where Sex='男');

--分页 查询根据Age排序后3、4条数据
select top 2 * from Employee where EmpId not in (
	select top 2 EmpId from Employee order by Age asc
)order by EmpId

--查询EmpName字段记录重复1条以上的数据
select EmpName from student group by EmpName having COUNT(EmpName)>1;

--查询EmpName字段记录重复1条以上的数据,并且删除EmpId最大的那个数据
--  (Min删除EmpId最大的数据,Max删除EmpId最小的数据)
delete from student where EmpName in(
	select EmpName from student group by EmpName having COUNT(EmpName)>1
) 
and EmpId not in(
	select Min(EmpId) from student group by EmpName having COUNT(EmpName)>1
)

--内外连接定义
--SQL内链接：将2张表按照on的条件，返回公共部分
--SQL外连接: 包含左链接和右连接

--INNER JOIN：如果表中有至少一个匹配，则返回行
--LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
--RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
--FULL JOIN：只要其中一个表中存在匹配，则返回行

--问题:当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'student' 中的标识列插入显式值。
--解决:其中student为表名。意思是允许将显示值插入到标识列中。
set identity_insert student on;