高级查询

文章目录

一、高级查询是什么？

二、高级查询

1.统计查询

2.联表查询

总结

       实验1 数据库操作

1．创建数据库：

2．查看数据库属性：

3．删除数据库：

实验2 表操作

1．创建表：

2．修改表结构：

3．删除表

4．向表中输入数据记录

实验3 数据完整性

1．空值约束( NULL )

2．默认值约束( DEFAULT )

3．默认值对象

4．检查约束( CHECK )

5．规则约束对象

6．主键

7．唯一性约束( UNIQUE )

8．标识列

9．外键（ FOREIGN KEY ）

---

一、高级查询是什么？

--高级查询
1.统计查询 √
2.联表查询 √
3.子查询 

--基础查询 
--select 你要查询的列名(*) from 表名 where 条件
/*
like 模糊查询
between..and 在...之间
in/not in:在/不在
is null/is not null:为空/不为空
distinct:去除重复
top:前...
50 percent:50%
order by 列名 desc/asc:降序/升序
取别名的三种方式：sname as 姓名 sname 姓名 姓名=sname
*/

二、高级查询

1.统计查询

5个聚合函数：会自动忽略为null的列
sum()求和 max()最大值 min()最小值 avg()平均值 count()计数
分组:group by
分组前过滤：where
分组后过滤：having

注意事项：
聚合函数要么单独用,要么跟group by搭配使用
group by后面的列名>=select后面的列名

--1.求班级总人数
select COUNT(*) as 人数 from tb_stu
--2.求班级年龄总和
select SUM(sage) from tb_stu
--3.求班级最大年龄
select MAX(sage) from tb_stu
--4.求班级最小年龄
select MIN(sage) from tb_stu
--5.求班级平均年龄 409/10=40 409/9
select AVG(sage) from tb_stu

--6.班上男女生各多少人
select ssex 性别,COUNT(*) 人数 from tb_stu group by ssex
--7.班上各个地方各多少人
select saddress,COUNT(*) from tb_stu group by saddress
--8.统计各个姓氏各多少人
select LEFT(sname,1),COUNT(*) from tb_stu group by LEFT(sname,1)
--9.统计班上各个地方男女生各多少人
select saddress,ssex,COUNT(*) from tb_stu group by ssex,saddress
--10.统计住在夜总会的男女生各多少人
select ssex,COUNT(*) from tb_stu where saddress='夜总会'
group by ssex
--11.统计姓'小'的男女生各多少人
select ssex,COUNT(*) from tb_stu where sname like '小%'
group by ssex
--12.统计人数在1人以上的地方
select saddress from tb_stu group by saddress
having COUNT(*)>2
--13.收官之作：统计男生人数在2人以上的地方
select saddress from tb_stu where ssex='男'
group by saddress having COUNT(*)>2

2.联表查询

内联：inner join 取表的共同部分 ******
左联：left join  以左表为基准，不管右表有没有，左表的数据全部显示
右联：right join 以右表为基准，不管左表有没有，右表的数据全部显示
全联：full join  两个表的所有部分都显示

左表：1234
右表：3456

内联：34
左联：1234
右联：3456
全联：123456

tb_stu:123456789 10 11 12
tb_score:1234567

--内联三种方式
--查询学生的学号、姓名、课程名、分数
select a.SCode,a.SName,b.Course,b.Score from tb_stu as a
inner join tb_score as b
on a.SCode=b.StudentID

select a.SCode,a.SName,b.Course,b.Score from tb_stu as a
join tb_score as b
on a.SCode=b.StudentID

select a.SCode,a.SName,b.Course,b.Score from tb_stu as a,tb_score as b
where a.SCode=b.StudentID

--查询所有人的考试情况 不管有没有参考
--isnull(a,b):如果a为null，就用b来替代
select a.SCode,a.SName,b.Course,isnull(b.Score,0) from tb_stu as a
full join tb_score as b
on a.SCode=b.StudentID

use kefang
select * from tb_kefang
select * from tb_type
--查询房号、类型编号、类型名称、房价
select a.fid,a.tid,b.tname,a.fprice from tb_kefang as a
inner join tb_type as b
on a.tid=b.tid

select a.fid,a.tid,b.tname,a.fprice from tb_kefang as a
join tb_type as b
on a.tid=b.tid

select a.fid,b.tid,b.tname,a.fprice from tb_kefang as a,tb_type as b
where a.tid=b.tid

--多表联查
/*
tb_stu(学生)：sid(学号) sname ssex......
tb_course(课程)：cid(课程号) cname(课程名称)
tb_score(分数): sid(学号) cid(课程号) score(成绩)
*/

--查询学号、姓名、课程名、成绩
select a.sid,a.sname,c.cname,b.score from tb_stu as a
inner join tb_score as b
on a.sid=b.sid 
inner join tb_course as c
on b.cid=c.cid

select a.sid,a.sname,c.cname,b.score from tb_stu as a,tb_score as b,tb_course as c
where a.sid=b.sid and b.cid=c.cid

---

总结

1.统计查询：max min avg sum count group by having
2.联表查询：
select 要查询的列名 from 表1 as a
inner join 表2 as b
on 表1和表2的关系

select 要查询的列名 from 表1 as a,表2 as b
where 表1和表2的关系

                                        实验1 数据库操作

1．创建数据库：

操作1.1：创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。

参考答案：

create database Test

ON primary

(

name = test_data,

filename = 'd:\test\test_data.mdf',

size = 5MB,

maxsize = unlimited,

filegrowth = 1MB

)

LOG ON

(

name = test_log,

filename = 'd:\test\test_log.ldf',

size = 1MB,

maxsize = 5MB,

filegrowth = 10%

)

GO

2．查看数据库属性：

操作1.2：使用T-SQL语句查看数据库test属性

参考答案：

EXEC  sp_helpdb  test

3．删除数据库：

操作1.3：使用T-SQL语句删除数据库test

参考答案：

drop database Test

实验2 表操作

1．创建表：

操作2.1：创建学生表：

表名：student			说明：学生基本信息表
属性列	数据类型	长度	空值	列约束	说明
st_id	nVarChar	9	Not Null	PK	学生学号
st_nm	nVarChar	8	Not Null		学生姓名
st_sex	nVarChar	2	Null		学生性别
st_birth	datetime		Null		出生日期
st_score	int		Null		入学成绩
st_date	datetime		Null		入学日期
st_from	nChar	20	Null		学生来源
st_dpid	nVarChar	2	Null		所在系编号
st_mnt	tinyint		Null		学生职务

参考答案：

USE test

GO

CREATE TABLE student

(

st_id nVarChar(9) primary key NOT NULL ,

st_nm nVarChar(8) NOT NULL ,

st_sex nVarChar(2) NULL ,

st_birth datetime NULL ,

st_score int NULL ,

st_date  datetime NULL ,

st_ from nVarChar(20) NULL ,

st_dpid  nVarChar(2) NULL ,

st_ mnt tinyint NULL

)

GO

操作2.2：创建课程信息表：

表名：couse			说明：课程信息表
属性列	数据类型	长度	空值	列约束	说明
cs_id	nVarChar	4	Not Null	PK	课程编号
cs_nm	nVarChar	20	Not Null		课程名称
cs_tm	int		Null		课程学时
cs_sc	int		Null		课程学分

参考答案：

USE test

GO

CREATE TABLE couse

(

cs_id nVarChar(4) primary key NOT NULL ,

cs_nm nVarChar(20) NOT NULL ,

cs_tm int NULL ,

cs_sc int NULL

)

GO

操作2.3：创建选课表：

表名：slt_couse			说明：选课表
属性列	数据类型	长度	空值	列约束	说明
cs_id	nVarChar	4	Not Null	FK	课程编号
st_id	nVarChar	9	Not Null	FK	学生编号
score	int		Null		课程成绩
sltdate	datetime		Null		选课日期

参考答案：

USE test

GO

CREATE TABLE couse

(

cs_id nVarChar(4) NOT NULL ,

st_id nVarChar(9) NOT NULL ,

score int NULL ,

sltdate  datetime NULL

)

GO

操作2.4：创建院系信息表：

表名：dept			说明：院系信息表
属性列	数据类型	长度	空值	列约束	说明
dp_id	nVarChar	2	Not Null		系编号
dp_nm	nVarChar	20	Not Null		院系名称
dp_drt	nVarChar	8	Null		院系主任
dt_tel	nVarChar	12	Null		联系电话

参考答案：

USE test

GO

CREATE TABLE dept

(

dp_id nVarChar(2) NOT NULL ,

dp_nm nVarChar(20) NOT NULL ,

dp_drt nVarChar(8) NULL ,

dp_tel nVarChar(12) NULL

)

GO

2．修改表结构：

（1）向表中添加列：

操作2.5：为“dept”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）

参考答案：

ALTER TABLE dept ADD dp_count nvarchar(3) NULL

（2）修改列数据类型：

操作2.6：修改“dept”表的“dp_count”列数据类型为int

参考答案：

ALTER TABLE dept ALTER COLUMN dp_count int NULL

（3）删除表中指定列：

操作2.7：删除“dept”表的“dp_count”列

参考答案：

ALTER TABLE dept DROP COLUMN dp_count

3．删除表

操作2.8：删除“dept”表

参考答案：

DROP TABLE student

4．向表中输入数据记录

操作2.9：分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录

实验3 数据完整性

1．空值约束( NULL )

操作3.1：将student表中的st_sex列属性更改为NOT NULL

参考答案：

ALTER TABLE student ALTER COLUME st_nm nVarChar(8) NOT NULL

2．默认值约束( DEFAULT )

操作3.2：将student表中的st_from列默认值设置为“陕西省”

参考答案：

ALTER TABLE student ADD DEFAULT '陕西省' FOR st_from

3．默认值对象

操作3.3：创建默认值对象df_today为当前日期，并将其绑定到slt_couse表中的sltdate列，然后取消绑定，最后删除默认值对象df_today。

参考答案：

CREATE DEFAULT df_today AS Getdate( )

GO

EXEC sp_bindefault df_today, 'slt_couse.sltdate'

GO

EXEC sp_unbindefault 'slt_couse.sltdate'

GO

DROP DEFAULT df_today

GO

4．检查约束( CHECK )

操作3.4：将slt_couse表中的score列的检查约束设置为>=0且<=100

参考答案：

ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100)

5．规则约束对象

操作3.5：创建规则约束对象rl_sex，用于检查性别的取值仅限于“男”和“女”，并将其绑定到student表中的st_sex列，然后取消绑定，最后删除规则约束对象rl_sex。

参考答案：

CREATE RULE rl_sex AS @chksex ’男’ OR @chksex=’女’

或

CREATE RULE rl_sex AS @chksex IN (’男’, ’女’)

GO

EXEC sp_bindrule rl_sex, 'student.st_sex'

GO

EXEC sp_unbindrule 'student.st_sex'

GO

DROP RULE rl_sex

GO

6．主键

操作3.6：将dept表中的dp_id列设置为主键

参考答案：

ALTER TABLE dept ADD PRIMARY KEY (dp_id)

7．唯一性约束( UNIQUE )

操作3.7：将dept表中的dp_nm列设置为唯一性约束

参考答案：

ALTER TABLE dept ADD UNIQUE (dp_nm)

8．标识列

操作3.8：向slt_couse表中添加标识列id，第1行默认值为1，相邻两个标识列间的增量为1

参考答案：

ALTER TABLE slt_couse  ADD id INT IDENTITY(1,1) NOT NULL

9．外键（ FOREIGN KEY ）

操作3.9：被参照表为dept，参照表为student

参考答案：

ALTER TABLE student

ADD FOREIGN KEY (st_dpid) REFERENCES dept(dp_id)