SQL 基本查询语句

--使用数据库
use date
go

--创建表班级表
create table classInfo
(
classNo int primary key identity(1,1),--主键约束使用primary key identity
className varchar(10) not null--非空约束
)
go
--创建学员表
create table stuInfo         --标识列约束使用identity
(
stuNo int primary key identity(1,1),--主键约束要求主键数据唯一并且不允许为空
stuname varchar(10)not null unique,--唯一约束使用unique关键字允许为空，但只能出现一个空值
age int check(age>10 and age<100),--检查约束使用check设置年龄
sex nchar(1)default'男',--默认约束
class int foreign key references classInfo(classNo)--外键约束用于两表之间建立关系，需要指定引用主表的那一列

)
go
--插入数据
insert into classInfo values('S2S147'),('S2S148')
insert into stuInfo values
('田七',18,'女',null),
('张三',38,'女',null),
('王五',20,'女',null),
('赵六',30,'男',null)


--修改表中的数据
update  stuInfo  set class=1
--删除表中的数据
delete stuInfo where  stuNo=1

--修改朱茂深的年龄和性别
update stuInfo set age=18,sex='男'where stuname='张三'

--查询表中的数据
select * from stuInfo
select * from classInfo

--查询性别为女的数据
select * from stuInfo where sex='女'

--模糊查询使用like --查询'朱'字开头的名字
select * from stuInfo where stuname like '张%'

--查询年龄18到20之间的数据
select * from stuInfo where age=18 or age=20
--使用in的效果一样
select  * from stuInfo where age in(18,20)
select  * from stuInfo  where age between 18 and 20
--查询姓名年龄
select '姓名'=stuname,'年龄'=age from stuInfo where   age between 18 and 20

--查询男生的人数（使用聚合函数）
select count(*) as 人数 from stuInfo where sex='男' 

--sum(求和)、avg(平均值)、max(最大值)、min(最小值)、count(计算总数)

--查询男生女生分别多少人（分组）(group by 统计函数联合使用)
 select 性别=sex, 人数=count(*) from stuInfo group by sex
 --查询年龄18到20之间的个数大于等于2的数
 select 性别=sex, 人数=count(*) from stuInfo 
 where age in (18,20) group by sex
 having COUNT(*)>=2
 
 --order by(作用是升降序 (ASC升从小到大排列)（Desc降）从大到小排列)
 select * from stuInfo order by  age asc
 select * from stuInfo order by  age desc
 
 --使用top查询前三条数据  
 --查询降序后的三条数据
 select top 3 * from stuInfo order by age desc
 
--查询出男生的 姓名 年龄 性别
select 姓名=stuname,年龄=age,性别=sex
 from stuInfo,classInfo 
where stuInfo.class=classInfo.classNo and sex='男'

--内链接查询
--查询 姓名 年龄 性别 班级
select 姓名=stuname,年龄=age,性别=sex,班级=className
from stuInfo inner join classInfo 
on stuInfo.class=classInfo.classNo  
where sex='男'

--左外连接查询使用(left join)
select stuname,age,sex,className 
from stuInfo left join classInfo 
on stuInfo.class=classInfo.classNo

--右外连接查询 使用(right join)如果不满足条件的则返回null(空值)
select stuname,age,sex,classname 
from stuInfo right join classInfo
 on stuInfo.class=classInfo.classNo


--全外连接查询使用(full outer join)outer可以不写
select stuname,age,sex,classname 
from stuInfo full join  classInfo 
on stuInfo.class=classInfo.classNo


--交叉链接查询（笛卡尔积)就是查询所指定要查询的值
select stuname,age,sex,classname 
from stuInfo cross join classInfo

--自连接查询查询张三所在班级的所有学员信息
select 
姓名=s1.stuname ,
年龄=s1.age,
性别=s1.sex 
from stuInfo s1,stuInfo s2
where s1.class=s2.class
and s2.stuname='张三'

select 
姓名=s1.stuname,
年龄=s1.age,
性别=s1.sex,
班级=c3 .className
from stuInfo s1, stuInfo s2 ,classInfo c3
where s1.class=s2.class 
and s2.stuname='张三'
and s1.class=c3.classNo


--子查询
select 
姓名=stuname,
年龄=age,
性别=sex,
班级=class
from stuInfo
where class=(select class  from stuInfo where stuname='张三')


select
姓名=stuname,
年龄=age,
性别=sex,
班级=(select classname from classInfo where classNo=class)
from stuInfo 
where class=
(select class from stuInfo where stuname='张三')