SQL server 数据库 SQL语句高级用法

1、表的高级查询

use student

select * from stuinfo1

-- 使用 in 的子查询

select * from stuinfo where stu_age in ( select stu_age from stuinfo where

cla_id = '12345' )

select * from stuinfo where stu_age in ( 19 , 20 , 21 , 25 , 23 , 16 , 31 , 11 , 19 )

-- 使用 = 号的子查询

select * from stuinfo where stu_age = ( select stu_age from stuinfo where

stu_name = ' 张三 ' )

select * from stuinfo where stu_age = 20

-- 设置字段名称：给字段命名的时候，一般用的是英文，很理解，查询时，可以给字段进行重命名，让其便于

理解

/*

给字段设置别名格式：

1 、原字段名 as 新名称

2 、新名称 = 原字段名

3 、原字段名 新名称

*/

select stu_name from stuinfo

select stu_name as 姓名 from stuinfo

select 姓名 = stu_name from stuinfo

select stu_name 姓名 from stuinfo

/* 字段拼接：指的将多个字段合并一个新的字段，使用的是 "+" 号作为连接符。

注意：所连接的字段数据类型应该是相同类型，而且不能是数值类型；

*/

select stu_name,stu_add from stuinfo

select stu_name + stu_add from stuinfo

-- 去重 distinct ，去掉重复值

select stu_add from stuinfo

select distinct stu_add from stuinfo

-- top: 显示指定前多少条数据或前百分比的数据，后面要跟上一个数据或百分比

select * from stuinfo

select top 3 * from stuinfo -- 查询前 3 条记录

select top 10 percent * from stuinfo -- 查询前百分之 10 的记录

-- 范围查询： between and 是在一定的范围内，包含界值

-- 案例：查询年龄在 18 到 23 之间的学生信息，包含 18 和 23

select * from stuinfo where stu_age between 18 and 23

-- not between and 不在一个范围之内，不包含界值

-- 案例：查询年龄不在 18 到 23 之间的学生信息，结果不包含 18 和 23

select * from stuinfo where stu_age not between 18 and 23

-- not 否定后面的任何内容

-- 查询姓名叫张三学生

select * from stuinfo where stu_name = ' 张三 '

-- 查询姓名不叫张三的学生信息

select * from stuinfo where not stu_name = ' 张三 '

select * from stuinfo where stu_name <> ' 张三 '

select * from stuinfo where stu_name != ' 张三 '

/* 模糊查询：

关键字： like

通配符： %:0 个或多个任意字符

_ ：单个字符

[] ：表示在这个范围之内

[^] ：表示不在这个范围之内

*/

-- 查询学生信息表中姓李学生的信息

select * from stuinfo where stu_name like ' 李 %'

-- 查询学生信息表中姓李且名字只有一个单字的学生的信息

select * from stuinfo where stu_name like ' 李 _'

-- 查询学生信息表中姓李或姓张学生的信息

select * from stuinfo where stu_name like ' 李 %' or stu_name like ' 张 %'

select * from stuinfo where stu_name like '[ 李张 ]%'

-- 查询学生信息表中即不是姓李的学生，也不是姓张的学生信息。

select * from stuinfo where not stu_name like ' 李 %' and not stu_name like ' 张 %'

select * from stuinfo where stu_name like '[^ 李张 ]%'

-- 排序： order by

-- 语法格式： select * from < 表名 > order by 字段名 ASC /DESC

-- 注意：如果按升序排序，可以省略 asc ，默认就是以升序进行排序；如果是降序，不可以省略 desc

-- 注意：如果有多个字段参与排序，优先级按从左至右进行排序

-- 案例：查询学生的信息，并按照年龄的升序进行排序

select * from stuinfo order by stu_age asc

select * from stuinfo order by stu_age

-- 案例：查询学生的信息，并按照年龄的降序进行排序

select * from stuinfo order by stu_age desc

-- 案例：查询学生的信息，并按照年龄降序进行排序，按照学号的升序进行排序

select * from stuinfo order by stu_age desc ,stu_id asc

-- 案例：查询学生的信息，并按照学号降序进行排序，按照年龄的升序进行排序

select * from stuinfo order by stu_id desc ,stu_age asc

-- 案例：排序与 top 结合使用

select top 2 * from stuinfo order by stu_age desc ,stu_id asc

-- 案例：按学生年龄的降序排序，并输出姓李的学生信息

select * from stuinfo where stu_name like ' 李 %' order by stu_age desc

2、 聚合函数及 group by 分组用法

-- 求和： sum 函数，格式： sum ( 字段 ) ，注意：字段的类型只能为数值型；

-- 案例：计算学生信息表中所有学后的年龄之和

select sum ( stu_age ) 年龄之和 from stuinfo

-- 求平均值： avg 函数，格式： avg ( 字段 ) 注意：字段的类型只能为数值型

-- 案例：计算学生的平均年龄

select avg ( stu_age ) 平均年龄 from stuinfo

-- 求最大值： max 函数 格式： max ( 字段 )

-- 列出学生信息表中年龄最大的学生信息

select max ( stu_age ) 最大年龄 from stuinfo

-- 列出学生信息表中最大学号的学生信息

select max ( stu_id ) from stuinfo

-- 求最小值 ： min 函数 格式： min ( 字段 )

-- 列出学生信息表中年龄最小的学生信息

select min ( stu_age ) 最小年龄 from stuinfo

-- 列出学生信息表中最小学号的学生信息

select min ( stu_id ) 最小学号 from stuinfo

-- 计数 ： count 函数 格式： count ( 字段 )

-- 计算出学生信息表中学生的数量

select * from stuinfo

select count ( stu_name ) from stuinfo

select count ( stu_age ) from stuinfo

-- 计算出班级编号为 12345 的学生的人数

select count ( stu_id ) from stuinfo where cla_id = '12345'

select count ( * ) from stuinfo where cla_id = '12345'

-- 创建一个科目表

create table km (

id int identity ( 1 , 1 ) , -- 序号

km_id int primary key not null , -- 课程号

km_name varchar ( 10 ) -- 课程名称

)

insert into km values ( 1 , ' 语文 ' ) , ( 2 , ' 数学 ' ) , ( 3 , ' 英语 ' ) , ( 4 , ' 计算机 ' )

-- 创建一个成绩表

create table cj (

id int identity ( 1 , 1 ) ,

stu_id varchar ( 20 ) references stuinfo ( stu_id ) ,

km_id int references km ( km_id ) ,

cj int check ( cj >= 0 and cj <= 100 ) not null

)

insert into cj values ( '10001' , 1 , 80 ) , ( '10001' , 2 , 75 ) , ( '10001' , 3 , 90 ) ,

( '10001' , 4 , 95 ) , ( '10002' , 1 , 88 ) , ( '10002' , 2 , 98 ) , ( '10002' , 3 , 70 ) ,

( '10002' , 4 , 95 ) , ( '10003' , 1 , 30 ) , ( '10003' , 2 , 55 ) , ( '10003' , 3 , 91 ) , ( '10003' , 4 , 92 ) ,

( '10004' , 1 , 83 ) , ( '10004' , 2 , 85 ) , ( '10004' , 3 , 70 ) , ( '10004' , 4 , 65 )

select * from km

select * from cj

-- 查询学生为 10001 学生的平均成绩

select avg ( cj ) 平均成绩 from cj where stu_id = '10001'

-- 查询学生为 10001 学生的总成绩

select sum ( cj ) 总绩 from cj where stu_id = '10001'

-- 查询每个学生的总成绩

select stu_id 学号 , sum ( cj ) 总成绩 from cj group by stu_id

-- 查询每门课程平均成绩

select km_id 课程号 , avg ( cj ) 平均成绩 from cj group by km_id

-- 查询每门课程最高成绩

select km_id 课程号 , max ( cj ) 平均成绩 from cj group by km_id

-- 列出学生的总成绩大于 350 分的学生的信息

-- 使用 group by 进行分组时，如果需要用到条件语句，后面只能使用 having 做条件表达式关键字，不能使用

where

select stu_id 学号 , sum ( cj ) 总成绩 from cj group by stu_id having sum ( cj ) > 350

-- 列出每门课程的平均成绩

select km_id 课程号 , avg ( cj ) 平均成绩 from cj group by km_id

select * from class

select * from stuinfo

-- 将学生信息表中 id 编号为 7 到 9 的学生的班级修改为 1234

update stuinfo set cla_id = '1234' where id between 7 and 9