SQL增删改查

最新推荐文章于 2022-02-07 21:21:21 发布

anling_yao

最新推荐文章于 2022-02-07 21:21:21 发布

阅读量396

点赞数

分类专栏：数据库操作

数据库操作专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据检索

l 执行备注中的代码创建测试数据表。

l 简单的数据检索： SELECT * FROM Student

l 只检索需要的列： SELECT sName FROM Student 、 SELECT sName,sAge FROM Student

l 列别名： SELECT sName AS 姓名 , sAge AS 年龄 , sBirthday AS 出生日期 FROM Student

l 使用 where 检索符合条件的数据： SELECT sName FROM Student WHERE sSex =‘ 女 ’ 。

l 还可以检索不与任何表关联的数据： select 1+1;select select getdate ();

Top Distinct

Top 获取前几条数据

•获得年纪最小的5个学生

•获得年纪最大的10%的学生

Distinct 去除重复数据

•select distinct sName from student

•select distinct sName,sAge from student

DISTINCT是对整个结果集进行数据重复处理的，而不是针对某一个列

Select top 5 sName,sAgefrom student

Select top 30 percentsName,sAgefrom student 非四舍五入，返回最大整数2.1返回3

聚合函数

l SQL 聚合函数： MAX （最大值）、 MIN （最小值）、 AVG （平均值）、 SUM （和）、 COUNT （数量）

l 平均成绩 select avg ( english ) from score

l 男学生出生日期的最大值和最小值： SELECT select max( sBirthday ),min( sBirthday ) from student where sSex =' 男 '

selectcount(*) from student;

selectmax(english) fromscore;

selectmin(english) fromscore;

selectavg(english) fromscore

selectsum(english) fromscore;

selectcount(*) from student

wheresSex=‘女’;

带条件的查询

Select …from…where

•查询没有及格的学生的学号

•查询年龄在20-30岁之间的男学生

Between…and …在之间

•查询年龄在20-30岁之间的男学生

•查询成绩在80-90分之间的所有学生

查询班级id为1，2，3的所有学生

•select sName,sAge from student where sClassId=1 or sClassId=2 or sClassId=3

•select sName,sAge from student where sClassId in (1,2,3)

•

select studentIdfrom score whereenglish< 60

select sName,sAge,sSexfrom student wheresAge>=20 andsAge<=30 andsSex='男'

select sName,sAge,sSexfrom student wheresAgebetween 20 and 30 andsSex='男'

带条件的查询 - 模糊查询

查询所有姓张的同学

•Select * from student where left(sName,1)=‘张‘ 看上去很美,如果改成查询名字中带亮的学生怎么做？

换一种做法like

•Select * from student where sName like ‘张%’ 会吧所有姓张的都查询到，现在我想查询姓张并且名字是一个字的学生？

•Select * from student where sName like ‘%亮%’

通配符%多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

通配符_ 单字符匹配，它匹配单个出现的字符

[] 只匹配一个字符并且这个字符必须是[]范围内的 [0-9] [a-z]

[abc]

•

空值处理

数据库中，一个列如果没有指定值，那么值就为null，这个null和C#中的null，数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。

select * from score where english= null ；

select * from score where english!= null ；都没有任何返回结果，因为数据库也“不知道”。

SQL中使用is null、is not null来进行空值判断：select * from score where englishis null ； select* from score where english is not null ；

数据排序

ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。

按照年龄升序排序所有学生信息的列表：SELECT * FROM Student ORDER BY sAgeASC

按照英语成绩从大到小排序，如果英语成绩相同则按照数学成绩从大到小排序：SELECT * FROM Score ORDER BY english DESC,math DESC

ORDER BY子句要放到WHERE子句之后： SELECT * FROM Score where english>=60 and math>=60 ORDER BY englishDESC,mathDESC

数据分组

select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息

按照班级进行分组统计各个班级的人数： select sClassId,count(sName) from student group by sClassId

GROUP BY子句必须放到WHERE语句的之后

没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的（聚合函数中除外）

•错误： select sClassId,count(sName),sAge from student group by sClassId

•正确： select sClassId,count(sName),avg(sAge) from student group by sClassId

可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息. 按照group by 子句中指定的表达式的值分组查询结果。

--group by

--每个班有多少人

select * from student

select sClassId,count(*)from student group bysClassId

use MySchool

--简单查询
select * from student

select sId, sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime from student

select sName from student

--给列改名
select sName as '姓名',sAge as '年龄' from student
select sName '姓名',sAge '年龄' from student
select '姓名'=sName,'年龄'=sAge from student

select sName as '姓名',sAge as '年龄',sSex as '性别'
from student where sSEx='女'

select sName as '姓名',sAge as '年龄',sSex as '性别'
from student where sAge > 20

select 2*3
select getdate()

--Top Distinct
select top 2 sName,sAge,sSex from student
order by sAge

--百分之10个学生如果有小数位数，直接进1
select top 10 percent sName,sAge,sSex
from student

select distinct * from student

select distinct sName from student
select distinct sName,sSex,sAge from student

--聚合函数max min avg sum count
select max(english) from score
select min(english) from score
--null值不参加avg的运算
select avg(english) from score
select sum(english)/count(*) from score

select sum(english) from score
select count(sId) from student

select count(*) from student where sSex='女'

select max(english) as '最大值',min(english) as '最小值',avg(english) as '平均值'
from score

select max(sBirthday),min(sBirthday) from student

select * from student
select * from score

--带条件查询
select studentId from score where english >= 60

select sName,sAge,sSex from student
where sAge >=20 and sAge<=30 and sSex='男'

--between....and ....
select sName,sAge,sSex from student
where sAge between 20 and 30 and sSex='男'

select * from student
where sBirthday between '1988-1-1' and '1989-12-31'

select * from student
select * from score where english between 80 and 90
--in (1,2,3)
select * from student
where sClassId = 1 or sClassId=4 or sClassId = 3

select * from student
where sClassId in (1,2,4)

--模糊查询

select left('123',1)
select * from student where left(sName,1)='张'

select * from student where sName like '张%'
select * from student where sName like '%亮%'

select * from student where sName like '张__'

select * from student where sName like '张[飞亮]%'

alter table student
add constraint CK_Student_sPhone check (sPhone like '[0-9][0-9][0-9][0-9][0-9][0-9]')

insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday,sPhone) values (4,'诸葛亮',20,'男',22315678801234561,'1989-8-8','123456')

--null
select null + 123
select * from student where sPhone is null
select * from score where english is null
select * from score where english is not null

--order by
select top 2 * from student
order by sAge desc

select * from student
order by sName desc

select * from score
order by english asc,math desc

select * from student
where sSex='男'
order by sAge desc

--group by sClassId
--分组就是把sClassId相同值的那些行，合并为一行
--当有“每个”的含义时候使用分组

--每个班有多少个学生

--第一个问题使用group by后，select之后的列必须出现在group by子句中或者聚合函数中
select count(*),sClassId from student
group by sClassId

select count(sId) as '个数',sClassId from student
where sSex='男'
group by sClassId

select count(sId),sClassId,avg(sAge)
from student
group by sClassId

--聚合函数不能出现在where子句中
--错误
select count(sId),sClassId,avg(sAge)
from student
where avg(sAge) > 20
group by sClassId

--求平均年龄超过20岁的那些班
--having 对分组过后的数据进行筛选（和where的作用不用）
select sClassId,avg(sAge)
from student
group by sClassId
having avg(sAge) > 20

--求人数超过5个的那些班
select sClassId,count(sId)
from student
group by sClassId
having count(sId) > 5

--分组练习
--求男生和女生分别有多少人
select count(sId),sSex from student
group by sSex
--求每个班有多少男生
select count(sId),sClassId from student
where sSex='男'
group by sClassid
--每个班中的男同学的平均年龄
select count(sId),sClassId,avg(sAge) from student
where sSex='男'
group by sClassid

--求平均年龄小于22的那些班
select count(sId),sClassId,avg(sAge) from student
group by sClassid
having avg(sAge) < 22

--union
--列的个数一致，类型一致
--排序去除重复数据
select tName,tSex,-1 from teacher union
select sName,sSex,sClassId from student

--union all
--直接连接两个结果集
select tName,tSex from teacher union all
select sName,sSex from student

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select '最高成绩',max(english) from score union all
select '最低成绩',min(english) from score union all
select '平均成绩',avg(english) from score

--查询每位老师的信息，包括姓名、工资，并且在最后加上平均工资和最高工资
select tName,tSalary from teacher union all
select '平均工资',avg(tSalary) from teacher union all
select '最高工资',max(tSalary) from teacher

--一次插入多条数据
insert into Score(studentId,english,math)
select 1,80,100 union
select 2,60,80 union
select 3,50,59 union
select 4,66,89 union
select 5,59,100

select * from score

--把现有表中的数据插入到新表中(newStudent表不能存在)
--去除表中重复数据
select * into newStudent from student
select * from newStudent

--把现有表的数据复制到一个已存在的表
truncate table newStudent
insert into newStudent select sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime, sPhone from student

use MySchool

--简单查询
select * from student

select sId, sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime from student

select sName from student

--给列改名
select sName as '姓名',sAge as '年龄' from student
select sName '姓名',sAge '年龄' from student
select '姓名'=sName,'年龄'=sAge from student

select sName as '姓名',sAge as '年龄',sSex as '性别'
from student where sSEx='女'

select sName as '姓名',sAge as '年龄',sSex as '性别'
from student where sAge > 20

select 2*3
select getdate()

--Top Distinct
select top 2 sName,sAge,sSex from student
order by sAge

--百分之10个学生如果有小数位数，直接进1
select top 10 percent sName,sAge,sSex
from student

select distinct * from student

select distinct sName from student
select distinct sName,sSex,sAge from student

--聚合函数max min avg sum count
select max(english) from score
select min(english) from score
--null值不参加avg的运算
select avg(english) from score
select sum(english)/count(*) from score

select sum(english) from score
select count(sId) from student

select count(*) from student where sSex='女'

select max(english) as '最大值',min(english) as '最小值',avg(english) as '平均值'
from score

select max(sBirthday),min(sBirthday) from student

select * from student
select * from score

--带条件查询
select studentId from score where english >= 60

select sName,sAge,sSex from student
where sAge >=20 and sAge<=30 and sSex='男'

--between....and ....
select sName,sAge,sSex from student
where sAge between 20 and 30 and sSex='男'

select * from student
where sBirthday between '1988-1-1' and '1989-12-31'

select * from student
select * from score where english between 80 and 90
--in (1,2,3)
select * from student
where sClassId = 1 or sClassId=4 or sClassId = 3

select * from student
where sClassId in (1,2,4)

--模糊查询

select left('123',1)
select * from student where left(sName,1)='张'

select * from student where sName like '张%'
select * from student where sName like '%亮%'

select * from student where sName like '张__'

select * from student where sName like '张[飞亮]%'

alter table student
add constraint CK_Student_sPhone check (sPhone like '[0-9][0-9][0-9][0-9][0-9][0-9]')

insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday,sPhone) values (4,'诸葛亮',20,'男',22315678801234561,'1989-8-8','123456')

--null
select null + 123
select * from student where sPhone is null
select * from score where english is null
select * from score where english is not null

--order by
select top 2 * from student
order by sAge desc

select * from student
order by sName desc

select * from score
order by english asc,math desc

select * from student
where sSex='男'
order by sAge desc

--group by sClassId
--分组就是把sClassId相同值的那些行，合并为一行
--当有“每个”的含义时候使用分组

--每个班有多少个学生

--第一个问题使用group by后，select之后的列必须出现在group by子句中或者聚合函数中
select count(*),sClassId from student
group by sClassId

select count(sId) as '个数',sClassId from student
where sSex='男'
group by sClassId

select count(sId),sClassId,avg(sAge)
from student
group by sClassId

--聚合函数不能出现在where子句中
--错误
select count(sId),sClassId,avg(sAge)
from student
where avg(sAge) > 20
group by sClassId

--求平均年龄超过20岁的那些班
--having 对分组过后的数据进行筛选（和where的作用不用）
select sClassId,avg(sAge) as'平均年龄'
from student
group by sClassId
having avg(sAge) > 20

--求人数超过5个的那些班
select sClassId,count(sId)
from student
group by sClassId
having count(sId) > 5

--分组练习
--求男生和女生分别有多少人
select count(sId),sSex from student
group by sSex
--求每个班有多少男生
select count(sId),sClassId from student
where sSex='男'
group by sClassid
--每个班中的男同学的平均年龄
select count(sId),sClassId,avg(sAge) from student
where sSex='男'
group by sClassid

--求平均年龄小于22的那些班
select count(sId),sClassId,avg(sAge) from student
group by sClassid
having avg(sAge) < 22

--union
--列的个数一致，类型一致
--排序去除重复数据
select tName,tSex,-1 from teacher union
select sName,sSex,sClassId from student

--union all
--直接连接两个结果集
select tName,tSex from teacher union all
select sName,sSex from student

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select '最高成绩',max(english) from score union all
select '最低成绩',min(english) from score union all
select '平均成绩',avg(english) from score

--查询每位老师的信息，包括姓名、工资，并且在最后加上平均工资和最高工资
select tName,tSalary from teacher union all
select '平均工资',avg(tSalary) from teacher union all
select '最高工资',max(tSalary) from teacher

--一次插入多条数据
insert into Score(studentId,english,math)
select 1,80,100 union
select 2,60,80 union
select 3,50,59 union
select 4,66,89 union
select 5,59,100

select * from score

--把现有表中的数据插入到新表中(newStudent表不能存在)
--去除表中重复数据
select * into newStudent from student
select * from newStudent

--把现有表的数据复制到一个已存在的表
truncate table newStudent
insert into newStudent select sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime, sPhone from student

truncate table score
truncate table student
truncate table class

create database Company
go
use company
go
create table department
(
dId int identity(1,1) primary key,
dName nvarchar(50)
)
create table employee
(
eId int identity(1,1) primary key,
eName nvarchar(50) not null,
eSex nchar(1),
eAge int,
eSalary money,
eDepId int not null
)

alter table employee
add constraint FK_employee foreign key (eDepId) references department(dId)
--级联删除
--on delete cascade

insert into department values('财务部')
insert into department values('开发部')
insert into department values('后勤部')

insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('淡定哥','男',18,3000,2)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('小月月','女',20,4000,1)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('马户','男',18,3500,1)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('凤姐','女',21,2500,2)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('凤,姐','女',21,2500,2)

--7、查询每个部门的平均工资
select avg(eSalary),eDepId from employee
group by eDepId
--8、查询每个部门男员工的平均工资
select avg(eSalary),eDepId from employee
where eSex = '男'
group by eDepId
--9、查询平均工资超过2000的那些部门
select avg(eSalary),eDepId from employee
group by eDepId
having avg(eSalary) > 2000