SQL Server查询进阶

常用的SQL查询语句

--选择school数据库

use school;
select * from student;
select * from grade;


--使用内连接查询所有学生的基本信息和班级
select
name as '姓名' , sex as '性别', age as '年龄', gradeid as '班别', address as '地址', phone '联系方式', gradename as '年级' 
from student inner join grade on student.GradeID = Grade.ID;


--左连接查询，先查左边的表，再查右边的表
select
name as '姓名', sex as '性别', age as '年龄', gradeid as '班别', address as '地址', phone as '联系方式', gradename as '年级'
from student left join grade on student.GradeID = grade.ID ;


--右连接查询
select
name as '姓名', sex as '性别', age as '年龄', gradeid as '班别', address as '地址', phone as '联系方式', gradename as '年龄'
from student right join grade on student.GradeID = grade.ID;


--在grade表中插入新的一行
insert into grade values('四年级');


--修改student表中学生的年龄
select * from student;
update student set Age = 20 where Name = '王芳';
update student set Age = 22 where Name = '李梅';
update student set Age = 24 where Name = '张凯';
update student set Age = 25 where Name = '李四';
update student set Age = 29 where Name = '王五';


--插入姓名为傻瓜的学生数据
insert into student(Name,Sex,Age,GradeID,Address,Phone) values('傻瓜', '男', 50, 10, '月球', '1234567890')


--删除姓名为傻瓜的学生数据
delete from student where Name = '傻瓜';


--查询张凯所在的年级
select 
(select gradename from grade where student.GradeID = grade.id) as '张凯所在年级'
from student where Name = '张凯';


--查询王五的年级
select gradename as '王五所在的年级' from grade where ID = (select gradeid from student where Name = '王五');


-------------------聚合函数查询-------------------
--1:查询student表和grade表中的学生总人数和年级总数. (COUNT)
select COUNT(*) as '学生总数' from student;
select count(*) as '年级总数' from grade;


--2:查询student表中最大的年龄 (MAX)
select MAX(student.Age) as '最大年龄' from student;


--3:查询student表中最小的年龄 (MIN)
select MIN(student.Age) as '最小年龄' from student;


--4:查询student表中的平均年龄 (AVG)
select AVG(student.Age) as '平均年龄' from student;