高级查询

一、高级查询是什么？

--高级查询

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：创建学生表：

参考答案：

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：创建课程信息表：

参考答案：

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：创建选课表：

参考答案：

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：创建院系信息表：

参考答案：

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)