查找作业善用搜索功能：Ctrl+F

《数据库原理与技术》末考考了99分

9.3

SQL

Structured Query Language

SQL的组成部分

数据定义语言(Data Definition Language):CREATE DROP ALTER

数据操纵语言(Data Manipulation Language):INSERT DELETE UPDATE SELECT

数据控制语言(Data Control Language):GRANT REVOKE

SQL的特点

• 功能一体化

• 高度非过程化

• 面向集合的操作方式

• 两种使用方式

  • 命令行
  • 嵌入到其他宿主语言

• 简洁易学

创建表之前需要掌握的知识

1)SQL中的数据类型

2)表中的约束条件

SQL中的数据类型

• 数值型
• 字符串型
• 日期型
• 货币型

---

数值型

• int
• numeric(p,q):比如(0,1000.00)，表示为numeric(5,2)

字符串型

• char
• varchar

日期型

Datetime

数据完整性约束

• 主码约束:PRIMARY KEY
• 非空约束:NOT NULL
• 检查约束:CHECK
• 唯一值约束:UNIQUE
• 默认值约束:DEFAULT
• 外码约束:[FOREIGN KEY(外码列)] REFERENCES表名(主码列)

9.7

创建表

CREATE TABLE class(
	bjh char(10) primary key,
	bjm varchar(30) not null,
	bzr char(10)
);
CREATE TABLE student(
	xh	char(11)  primary key,
	xm	char(10)  not null,
	xb	char(2) check(xb = '男' or xb = '女'),--列级约束
	nl	int check(nl >= 10 and nl <= 60),
	xi	varchar(26) default'软件工程',
	rxrq datetime,
	id char(18)	unique,
	bjh char(10) references class(bjh)
--表级约束	foreign key (bjh) references Class(bjh)
); 
---------------------------------------------------------
--删除表 DROP TABLE 表名
drop table student;
drop table Class;

select * from Class;
select * from student;

insert into class values('19005', '软件19005','刑');
insert into class values('19006', '软件19006','刑');

insert into student(xh, xm, xb, nl, bjh) 
			values('19001020502', '邱谦','男',20,'19005');
-----------------------------------------------------------
--修改表结构(非标准SQL)
--添加列 ALTER TABLE 表名 add 新列名 数据类型;
--修改列的数据类型 ALTER TABLE 表名 ALTER COLUMN 列名 新的数据类型;
--删除列 ALTER TABLE 表名 DROP COLUMN 列名;
---------------------------------------------------------
--1给学生表添加新列 家庭住址jtzz，varchar(50)
ALTER TABLE student add jtzz varchar(50);
--2修改表student，将姓名列xm数据类型更新为char(20)
ALTER TABLE student ALTER COLUMN xm char(20);
--3修改表class，添加班级人数列bjrs， int
ALTER TABLE class add bjrs int;
--4修改表class，删除班级人数列bjrs
ALTER TABLE class DROP COLUMN bjrs;

---

作业1 Library

---

之前用了我的代码的同学，导入excel数据会失败，是因为在book表中，我的约束写错地方了，本来应该在author里面约束非空，写成了Name里面约束非空，dbq是我的锅。。。。现已更正，放心使用么么哒^ ^

---

CREATE TABLE Book(
	book_ID char(10) primary key,
	Name varchar(30) not null,
	author varchar(10),
	publish varchar(30),
	Price decimal(6,2) check(Price > 0),
	classify varchar(20)
);
CREATE TABLE Reader(
	reader_ID char(10) primary key,
	Name varchar(8),
	gender char(2),
	birthdate datetime
);
CREATE TABLE Borrow(
	book_ID char(10),
	reader_ID char(10),
	borrowdate datetime,
	returndate datetime,

	primary key(book_ID, reader_ID),
	foreign key(book_ID) references Book(book_ID),
	foreign key(reader_ID) references Reader(reader_ID)
);
SELECT * FROM Borrow;

作业2 Student

CREATE TABLE Student(
	sno varchar(7) primary key,
	sname varchar(10) not null,
	ssex char(2) check(ssex = '男' or ssex = '女'),
	sage int check(sage >=15 and sage <= 45),
	sdept varchar(20) default'计算机系'
);
CREATE TABLE Course(
	cno varchar(10) primary key,
	cname varchar(20) not null,
	ccredit int check(ccredit > 0),
	semester int check(semester > 0),
	period int
);
CREATE TABLE  Sc(
	sno varchar(7),
	cno varchar(10),
	grade int check(grade >= 0 and grade <= 100),
	primary key(sno, cno),
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno)
);

SELECT * FROM Sc;

---

导入不成功，执行下列脚本文件

Library

use master;
Go
drop database Library;
Go

create database Library;

Go

USE Library;

GO

IF OBJECT_ID ('book', 'U') IS NOT NULL
   DROP table borrow,book;
GO
CREATE TABLE Book (
book_ID   CHAR(10)          PRIMARY KEY,   
                                   --book_ID为主码
name      VARCHAR(30)     NOT NULL,  
                                   --非空约束
author     VARCHAR(10) ,
publish     VARCHAR(20),
price       DECIMAL(6,2)     CHECK(price>0) ,   
                                    --CHECK约束，price大于0
classify   varchar(20)   --图书分类                                    
) 
go
insert into book values('A32DT00001','计算机文化基础','周文波','清华大学出版社','28.0','计算机类')
insert into book values('A32DT00002','数据库原理','岳海健','电子工业出版社','25.0','计算机类')
insert into book values('B32DT00001','高等数学','李丹','同济大学出版社','42.0','基础类')
insert into book values('B32DT00002','离散数学',null,'高等教育出版社','31.0','基础类')
insert into book values('C32DT00001','毛泽东思想','刘琳','机械工业出版社','18.0','社科类')
insert into book values('D32DT00001','大学语文','赵阳','机械工业出版社','22.0','社科类')
insert into book values('A32DT00003','操作系统',null,'清华大学出版社','24.0','计算机类')
insert into book values('A32DT00004','C语言','谭浩强','清华大学出版社','20.0','计算机类')
insert into book values('B32DT00003','线形代数','李俐','高等教育出版社','12.0','基础类')
insert into book values('B32DT00004','概率论与数理统计',null,'机械工业出版社','22.0','基础类')




GO
IF OBJECT_ID ('reader', 'U') IS NOT NULL
   DROP table borrow,reader;
GO

CREATE TABLE Reader (
Reader_ID    CHAR(10)      PRIMARY KEY,
name        VARCHAR(8) ,
gender          VARCHAR(2),
birthdate     DATETIME
)

insert into reader values('021B310003','于海颖','男','1977-01-26')
insert into reader values('021B310004','胡晓丽','女','1977-01-26')
insert into reader values('021B310005','宋玮','女',null)
insert into reader values('021B310006','施秋乐',null,'1976-09-20')
insert into reader values('021B310007','张巍',null,null)
insert into reader values('021B310008','王金娟',null,'1977-07-13')
insert into reader values('021B310009','王旭','女','1977-07-13')

GO
IF OBJECT_ID ('borrow', 'U') IS NOT NULL
   DROP table borrow;
GO

CREATE TABLE Borrow(
book_ID     CHAR(10),
Reader_ID    CHAR(10),
Borrowdate   DATETIME,
returndate datetime,
PRIMARY KEY(book_ID,Reader_ID), 
FOREIGN KEY(book_ID) REFERENCES Book(book_ID),  
FOREIGN KEY(Reader_ID) REFERENCES Reader(Reader_ID) 
)

insert into borrow values('A32DT00002','021B310003','2012-01-20','2012-02-18')
insert into borrow values('A32DT00001','021B310006','2012-01-20','2012-02-18')
insert into borrow values('B32DT00001','021B310004','2012-02-01','2012-03-20')
insert into borrow values('B32DT00002','021B310004','2012-02-01','2012-03-20')
insert into borrow values('C32DT00001','021B310006','2012-02-03','2012-05-09')
insert into borrow values('A32DT00004','021B310009','2012-06-07','2012-07-25')
insert into borrow values('B32DT00003','021B310009','2012-06-07','2012-08-05')
insert into borrow values('B32DT00004','021B310009','2012-06-07','2012-10-17')

select * from Book
select * from Reader
select * from borrow

Student

drop database Students
Go

create database Students
go

use students
GO
IF OBJECT_ID ('Student', 'U') IS NOT NULL
   DROP table sc,Student;
GO
create table Student(
  sno char(7) primary key,
  sname varchar(10) not null,
  ssex char(2) check(ssex='男' or ssex='女'),
  sage int check(sage>=15 and sage<=45),
  sdept varchar(20)  default '计算机系',
  instructor  varchar(20) --辅导员
  );
go
insert into Student values('9512101','李勇','男',19,'计算机系','刘莉')
insert into Student values('9512102','刘晨','男',20,'计算机系','刘莉')
insert into Student values('9512103','王敏','女',20,'计算机系','刘莉')
insert into Student values('9521101','张力','男',22,'信管系','王斌')
insert into Student values('9521102','吴宾','女',21,'信管系','王斌')
insert into Student values('9521103','张海','男',20,'信管系','许颖')
insert into Student values('9531101','钱小平','女',18,'数学系','陈义')
insert into Student values('9531102','王大力','男',19,'数学系','陈义')
  
 
GO
IF OBJECT_ID ('Course', 'U') IS NOT NULL
   DROP table sc,Course;
GO 
create table Course(
  cno char(10) primary key,
  cname varchar(20) not null,
  ccredit int check(ccredit>0),
  semester int check(semester>0),
  period int
  )

insert into Course values('c01','计算机文化学',3,1,4)
insert into Course values('c02','VB',2,3,2)
insert into Course values('c03','计算机网络',4,7,4)
insert into Course values('c04','数据库基础',6,6,4)
insert into Course values('c05','高等数学',8,2,4)
insert into Course values('c06','数据结构',5,4,4)


GO
IF OBJECT_ID ('sc', 'U') IS NOT NULL
   DROP table sc;
GO  
create table SC(
  sno char(7),
  cno char(10),
  grade int check(grade>=0 and grade<=100),
  primary key(sno,cno),
  foreign key(sno) references Student(sno),
  foreign key(cno) references Course(cno)
  )

insert into SC values('9512101','c01',90)
insert into SC values('9512101','c02',86)
insert into SC values('9512101','c06',null)
insert into SC values('9512102','c02',78)
insert into SC values('9512102','c04',66)
insert into SC values('9521102','c01',82)
insert into SC values('9521102','c02',75)
insert into SC values('9521102','c04',92)
insert into SC values('9521102','c05',50)
insert into SC values('9521103','c02',68)
insert into SC values('9521103','c06',null)
insert into SC values('9531101','c01',80)
insert into SC values('9531101','c05',95)
insert into SC values('9531102','c05',50)


select * from Student
select * from Course
select * from SC

9.10

简单查询

--单表无条件查询
--select [distinct] 列名列表 from 表名
--[order by 列名 ASC|DESC]
--[distinct]用来消除列名列表显示的重复行
--给列起别名: 列名 [as] 别名(不是常量，是标识符，不用添加单引号)
--------------------------------------
SELECT name AS 书名, book_id AS 书号, price AS 价格 FROM book;
SELECT * FROM book; --* 代表全部属性列 列的顺序与创建表的时候一致
SELECT name, book_id, price, author, publish, classify FROM book;
--查询所有的图书出版社
SELECT publish FROM book;
--查询所有图书出版社(消除所有重复行)
SELECT DISTINCT publish FROM book;
--给列起别名(编码的时候使用英文，显示的时候打印中文)

--排序
SELECT name AS 书名, book_id AS 书号, price AS 价格 FROM book ORDER BY price DESC;
SELECT * FROM book;--显示的时候按照book_id排序，如果是中文，按照拼音顺序排序
SELECT * FROM book ORDER BY publish;
SELECT * FROM book ORDER BY author;--如果有空值，排序在最前
--查询所有图书，先按出版社排序，同一出版社的书再按照价格降序排列
SELECT * FROM book ORDER BY publish, price DESC;--多列排序

作业1 Library

--练习：
--1．	把表Book中的所有数据都显示出来。
SELECT * FROM Book;
--2．	把Reader表的所有记录显示出来，并且列名用汉字表示。
SELECT reader_ID AS 借阅者ID, Name AS 姓名, gender AS 性别, birthdate AS 出生日期 FROM Reader; 
--3．	显示所有读者（Reader）的姓名和年龄。
SELECT name, DATEDIFF(YEAR, birthdate, GETDATE()) AS 年龄 FROM reader;
--4．	查询borrow表中所有读者借阅图书的信息。
SELECT * FROM Borrow;
--5．	查询所有借书的读者编号。
SELECT DISTINCT reader_ID FROM Borrow;
--6．	查询所有图书信息，结果按照图书价格降序排列。
SELECT * FROM book ORDER BY Price DESC;
--7．	查询所有图书信息，结果先按出版社升序排列，同一出版社的再按书号升序排列。
SELECT * FROM book ORDER BY publish, book_ID;

作业2 Student

--一、列名列表
--1．查询全体学生的学号与姓名
SELECT sno, sname FROM Student;
--2．查询全体学生的姓名、学号、所在系
SELECT sname, sno, sdept FROM Student;
--3．查询全体学生的详细记录
SELECT * FROM Student;
--4．查询全体学生的姓名及出生年份
SELECT sname, YEAR( getdate() ) - sage AS birthdate FROM Student;
--5．查询全体学生的姓名、出生年份、所在系，且要求用小写字母表示所有系名（自己查找资料实现！）
SELECT sname, YEAR( getdate() ) - sage AS birthdate, lower(sdept) FROM Student;
--6. 查询选修了课程的学生学号，要求消除重复行
SELECT DISTINCT sno FROM Sc;
--7．查询全体学生的学号、姓名、年龄、所在系
SELECT sno, sname, sage, sdept FROMStudent;
--8．查询全体学生的信息，并用中文显示列名
SELECT sno AS 学号, sname AS 姓名, ssex AS 性别, sage AS 年龄, sdept AS 系别 FROM Student;
--二、排序子句 
--1. 查询选修了C03号课程的学生的学号及成绩，查询结果按成绩降序排列。 
SELECT sno, grade FROM Sc WHERE cno = 'C03' ORDER BY grade DESC;
--2．查询全体学生信息，查询结果按所在系升序排列，同一系的学生按年龄降序排列。 
SELECT * FROM Student ORDER BY sdept, sage DESC;

9.14

--条件查询
--SELECT 列名列表 FROM 表名 [WHERE 条件] [ORDER BY 列名 asc [desc]]
--------------------------------------------------------
--1.比较大小 WHERE 列名 比较运算符 值
SELECT * FROM book WHERE Price > 30;
--查询计算机类的图书
SELECT * FROM book WHERE classify = '计算机类';
--查询清华大学出版社的所有图书书号和书名，并用中文显示列名
SELECT book_ID as 书号, Name as 书名 FROM book WHERE publish = '清华大学出版社';
------------------------------------------------------
--2.范围比较 WHERE 列名 BETWEEN 值1 AND 值2(包含边界值)  [值1,值2]
--			WHERE 列名 NOT BETWEEN 值1 AND 值2(不包含边界值)
--查询图书价格在20-30之间的图书信息
SELECT * FROM book WHERE Price BETWEEN 20 AND 30;
SELECT * FROM book WHERE Price >= 20 AND Price <= 30;
--查询图书价格不在20-30之间的图书信息
SELECT * FROM book WHERE Price NOT BETWEEN 20 AND 30;
SELECT * FROM book WHERE Price < 20 OR Price > 30;
SELECT * FROM book WHERE NOT (Price >= 20 AND Price <= 30);
-----------------------------------------------------------------------------------------
--3.集合查询 WHERE 列名 [NOT] IN (列表值)
--查询价格是20、30、40的图书
SELECT * FROM book WHERE Price IN (20, 30, 40);
SELECT * FROM book WHERE Price = 20 OR Price = 30 OR Price = 40;
--查询清华大学出版社和同济大学出版社的图书
SELECT * FROM book WHERE publish IN ('清华大学出版社', '同济大学出版社');
SELECT * FROM book WHERE publish = '清华大学出版社' OR publish = '同济大学出版社';
--查询清华大学和同济大学以外的出版社出版的图书
SELECT * FROM book WHERE publish NOT IN ('清华大学出版社', '同济大学出版社');
SELECT * FROM book WHERE NOT (publish = '清华大学出版社' OR publish = '同济大学出版社');
SELECT * FROM book WHERE publish <> '清华大学出版社' AND publish <> '同济大学出版社';
-------------------------------------------------------------------------------------
--4.空值查询 WHERE 列名 IS [NOT] NULL
--查询作者为空的图书
SELECT * FROM book WHERE author IS NULL;
--查询出生日期不为空的读者
SELECT * FROM Reader WHERE birthdate IS NOT NULL;
----------------------------------------------------
--5.字符串模糊查询 WHERE 列名 [NOT] LIKE '带通配符的字符串'
--通配符：
-- %:任意多个字符，包括0个
-- _:任意一个字符
--查询C语言相关的图书
SELECT * FROM book WHERE Name LIKE '%C语言%';
--查询姓王的读者
SELECT Name from Reader WHERE Name LIKE '王%';
--查询姓王且姓名是两个字的读者
SELECT Name from Reader WHERE Name LIKE '王_';
--查询姓名第二个字是晓的读者
SELECT Name from Reader WHERE Name LIKE '_晓%';
-------------------
--6.复合条件 AND OR连接以上各条件
--查询清华大学出版社的C相关的图书并且价格不高于50元
SELECT * FROM book WHERE publish = '清华大学出版社' AND Name LIKE '%C%' AND Price <= 50;
--查询在2012年2月份借阅图书的读者
SELECT * FROM Borrow WHERE borrowdate BETWEEN '2012-02-01' AND '2012-02-29';
SELECT * FROM Borrow WHERE year(borrowdate) = 2012 and month(borrowdate) = 02;

作业1

Library

--实践：
--1．把表Book中的出版社为“清华大学出版社”的书选出来。
SELECT * FROM book WHERE publish = '清华大学出版社';
--2．把表Book中出版社为“清华大学出版社”，并且定价不超过25元的书选出来。
SELECT * FROM book WHERE publish = '清华大学出版社' AND Price <= 25;
--3．把表Book中出版社为“清华大学出版社”，或者定价不超过25元的书选出来。
SELECT * FROM book WHERE publish = '清华大学出版社' OR Price <= 25;
--4．显示定价在20到25元之间（包含20元和25元）的图书信息。
SELECT * FROM book WHERE Price BETWEEN 20 AND 25;
--10．显示定价不在20到25元之间（不包含20元和25元）的图书信息。
SELECT * FROM book WHERE Price NOT BETWEEN 20 AND 25;
--11．显示出版社为“清华大学出版社”、“同济大学出版社”或者“高等教育出版社”的图书信息。
SELECT * FROM book WHERE publish = '清华大学出版社' OR publish = '同济大学出版社';
--12．显示出版社为“清华大学出版社”、“同济大学出版社”或者“高等教育出版社”以外的其它出版社出版的图书信息。 
SELECT * FROM book WHERE NOT (publish = '清华大学出版社' OR publish = '同济大学出版社' OR publish = '高等教育出版社');
--13．显示书名包含“数学”的图书信息。
SELECT * FROM book WHERE Name LIKE '%数学%';
--14．显示姓名为三个字的读者信息。
SELECT * FROM Reader WHERE Name LIKE '___';
--15．显示由姓李的作者写的书的信息。
SELECT * FROM book WHERE author LIKE '李%';

Student

--练习与作业
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Sc;
--1．查询全体学生的信息，并用中文显示列名
SELECT sno as 学号, sname as 姓名, ssex as 性别, sage as 年龄, sdept as 系别 FROM Student;
--2．查询所有年龄在20岁以下的学生姓名及其年龄
SELECT sname, sage FROM Student WHERE sage < 20;
--3．查询考试成绩不及格的学生学号
SELECT sno FROM Sc WHERE grade < 60 OR grade IS NULL;
--4. 查询年龄在20－23岁之间的学生的姓名、年龄和系别
SELECT sname, sage, sdept FROM Student WHERE sage BETWEEN 20 AND 23;
--5．查询信息系、数学系和计算机系的学生的姓名和性别
SELECT sname, ssex FROM Student WHERE sdept IN ('信息系', '数学系', '计算机系');
--6．查询既不是信息系、数学系，也不是计算机系的学生的姓名和性别
SELECT sname, ssex FROM Student WHERE sdept NOT IN ('信息系', '数学系', '计算机系');
--7．查询学号为95001的学生的详细情况
SELECT * FROM Student WHERE sno = '95001';
--8．查询姓李且全名为三个汉字的学生的姓名
SELECT sname FROM Student WHERE sname LIKE '李__';
--9．查询名字中第二个字为阳字的学生的姓名和学号
SELECT sname, sno FROM Student WHERE sname LIKE '_阳%';
--10．查询所有不姓刘的学生姓名
SELECT sname FROM Student WHERE NOT (sname LIKE '刘%');
--11．查询数据库课程的课程号和学分
SELECT cno, ccredit FROM Course WHERE cname LIKE '%数据库%';
--12. 查询计算机系年龄20岁以下的学生姓名
SELECT sname FROM Student WHERE sdept = '计算机系' AND sage < 20;
--13. 查询缺少成绩的学生的学号和相应的课程号
SELECT sno, cno FROM Sc WHERE grade IS NULL;
--14．查询所有有成绩的学生的学号和课程号
SELECT sno, cno FROM Sc WHERE grade IS NOT NULL;

9.17

课上练习

--查询清华大学出版社出版的姓谭的作者写的C语言相关的图书
SELECT * FROM book WHERE publish = '清华大学出版社' AND author LIKE '谭%' AND Name LIKE '%C语言%';
--查询1990年出生的性别不为空的女读者信息
SELECT * FROM Reader WHERE  DATEDIFF(year,birthdate,GETDATE())='30' AND gender IS NOT NULL AND gender = '女';
SELECT * FROM Reader WHERE  year(birthdate) = '1990' AND gender IS NOT NULL AND gender = '女';
--查询计算机类、数学类的价格不高于50元的图书信息，结果按照价格降序排列
SELECT * FROM Book WHERE classify in ('计算机类', '数学类') AND Price <= 50 ORDER BY Price DESC;

分组查询

--聚集函数(集合函数)，只返回一个值
--max(列名)：求某列的最大值
--min(列名)：求某列的最小值
--sum(列名)：求某列值的和，要求该列必须是数值类型
--avg(列名)：求某列值的平均值，要求该列必须是数值类型
--count([distinct]列名)：统计该列(去重后)值不是null的，记录行数
--count(*)：统计全部记录行数(不是null)
--------------------------------------------------------
--统计图书表中共有多少本图书
SELECT count(*) as 图书表中共有多少本图书 
FROM book;
--查询所有图书的最高价和最低价格
SELECT max(Price) as 所有图书的最高价, min(Price) as 所有图书的最低价 
FROM book;
--查询计算机类图书的平均价格，价格之和
SELECT avg(Price) as 计算机类图书的平均价格, sum(Price) as 计算机类图书的价格之和 
FROM book 
WHERE classify = '计算机类';
--查询有多少名读者借阅过图书
SELECT count(distinct reader_ID) as 有多少名读者借阅过图书 
FROM Borrow;
--查询出生日期不是空的读者数量
SELECT count(birthdate) as 出生日期不是空的读者数量 
FROM Reader;

--分组统计 SELECT * FROM Book;

--SELECT 列名列表 
--FROM 表名 
--[WHERE 条件](筛选原表中的记录) 
--[group by 列名1[, 列名2......]](按照列的值进行分组，值相等的记录分为一组)
--[having 条件](筛选分组后的记录)
--[order by 列名 ASC|DESC](排序)
--查询清华大学出版社的图书平均价格
SELECT avg(Price) AS 清华大学出版社的图书平均价格 FROM Book WHERE publish = '清华大学出版社';
--查询各个出版社的图书平均价格
SELECT avg(Price) AS 平均价格, publish AS 出版社 FROM book GROUP BY publish;

作业

P59 7~24题答案

--7
SELECT sno, sname FROM Student;
--8
SELECT sname FROM Student WHERE sdept = '计算机系';
--9
SELECT sname, sage FROM Student WHERE sage < 20;
--10
SELECT sname, sdept, sage FROM Student WHERE sage BETWEEN 20 AND 23;
--11
SELECT sname, ssex FROM Student WHERE sdept IN ('信息系', '数学系', '计算机系');
--12
SELECT * FROM Student WHERE sname LIKE '张%';
--13
SELECT * FROM Student WHERE sname LIKE '张%' OR sname LIKE '李%' OR sname LIKE '刘%';
--14
SELECT sname, sno FROM Student WHERE sname LIKE '_小%' OR sname LIKE '_大%';
--15
SELECT sno, cno FROM Sc WHERE grade IS NOT NULL;
--16
SELECT sname FROM Student WHERE sdept = '计算机系' AND sage < 20;
--17
SELECT sno, grade FROM Sc WHERE cno = 'c02' ORDER BY grade DESC;
--18
SELECT * FROM Student ORDER BY sdept, sage DESC;
--19
SELECT COUNT(DISTINCT sno) FROM Student;
--20
SELECT SUM(grade) AS '9512101号同学总成绩' FROM Sc WHERE sno = '9512101';
--21
SELECT AVG(grade) AS 'c01平均成绩' FROM Sc WHERE cno = 'c01';
--22
SELECT MAX(grade) AS c01最高分, MIN(grade) AS c01最低分 FROM Sc WHERE cno = 'c01';
--23
SELECT cno, COUNT(DISTINCT sno) AS 选课人数 FROM Sc GROUP BY cno;
--24
SELECT sno, COUNT(cno) AS 选课门数, AVG(grade) AS 平均成绩 FROM Sc GROUP BY sno;

9.21

SELECT 列名列表

FROM 表名

WHERE 条件 –筛选表中行

GROUP BY 列名 –分组

HAVING 条件 –分组

ORDER BY 列名

GROUP BY

--GROUP BY后面的列名叫分组依据列
--分组依据列值相等的会分为一组，然后统计(聚集函数)
--注意： 1.分组依据列通常出现在SELECT后面，只有分组依据列才能直接出现在SELECT后面
--      2.大多数情况下，每/各 字眼后面的列名是分组依据列
--查询各个出版社的图书平均价格
SELECT avg(Price) AS 平均价格, publish AS 出版社 
FROM book 
GROUP BY publish;
------------------------------------------------------------------------------------
--查询每类图书的数量，价格总和与平均价格
SELECT classify AS 类别, COUNT(*) AS 图书数量, SUM(Price) AS 价格总和, AVG(Price) AS 平均价格 
FROM Book 
GROUP BY classify;
--查询每位读者借阅的图书数量和最早借阅日期
SELECT reader_ID, COUNT(*) AS 借阅图书数量, MIN(borrowdate) AS 最早借阅日期
FROM Borrow 
GROUP BY reader_ID;
--查询每个出版社的最高价格和最低价格
SELECT publish, MAX(Price) AS 最高价格, MIN(Price) AS 最低价格 
FROM book 
GROUP BY publish;
--查询价格在20-50之间的图书在各类图书中的分配数量
SELECT classify AS 类别, COUNT(*) AS '价格在20-50的数量'
FROM Book 
WHERE Price BETWEEN 20 AND 50 
GROUP BY classify;

HAVING

--HAVING条件，用来筛选分组后的统计结果的记录
--注意： 1.HAVING前面必须有GROUP BY
--      2.HAVING和WHERE条件的区别
--			(1)WHERE筛选原表中的记录行，HAVING筛选分组后的记录行
--			(2)WHERE条件不能有聚集函数，HAVING条件一般都会有聚集函数
----------------------------------------------------------------------
--查询出版图书数量超过2本的出版社名称
SELECT publish, COUNT(*)
FROM Book 
GROUP BY publish
HAVING COUNT(*) > 2;
--查询平均价格超过30元的出版社名
SELECT publish 
FROM Book 
GROUP BY publish 
HAVING AVG(Price) > 30;
--查询价格超过30元的图书信息
SELECT * FROM Book WHERE Price > 30;

课上练习

--1、查询图书平均价格不低于25元的图书类别和平均价格
SELECT classify, AVG(Price)
FROM Book
GROUP BY classify
HAVING AVG(Price) >= 25;
--2、查询图书数量多于2本的出版社名称、出版数量和平均价格
SELECT publish, COUNT(*) AS 出版数量, AVG(Price) AS 平均价格
FROM Book
GROUP BY publish
HAVING COUNT(*) > 2;
--3、查询借阅图书数量不多于3本的读者编号和借阅数量
SELECT reader_ID, COUNT(*) AS 借阅数量
FROM Borrow
GROUP BY reader_ID
HAVING COUNT(*) <= 3;
--4、查询2012年2月份各读者借阅图书的数量，并按照借阅图书数量降序排列
SELECT reader_ID, COUNT(*) AS 借阅数量
FROM Borrow
--WHERE Borrowdate BETWEEN '2012-02-01' AND '2012-02-29'
WHERE year(Borrowdate) = 2012 AND month(borrowdate) = 02
GROUP BY reader_ID
ORDER BY COUNT(*) DESC;
--5、查询清华大学出版社和高等教育出版社中图书最高价格高于30元的图书类别
SELECT classify
FROM Book
WHERE publish IN ('清华大学出版社', '高等教育出版社')
GROUP BY classify
HAVING MAX(Price) > 30;

作业

--一、集合函数 
--3．查询学生的总人数 
SELECT COUNT(*) FROM Student;
--4．查询选修了课程的学生人数 
SELECT COUNT(DISTINCT sno) FROM Sc;
--5．计算C01号课程的学生平均成绩 
SELECT AVG(grade) FROM Sc WHERE cno = 'C01';
--6．查询选修C01号课程的学生最高分数和最低分数。 
SELECT MAX(grade) AS C01最高分, MIN(grade) AS C01最低分 FROM Sc WHERE cno = 'C01';
--二、group by子句
--1．求各个课程号及相应的选课人数。 
SELECT cno, COUNT(*) AS 选课人数 FROM SC GROUP BY cno;
--2．查询每名学生的选课门数和平均成绩。 
SELECT sno, COUNT(cno) AS 选课门数, AVG(grade) FROM Sc GROUP BY sno;
--3. 统计每个系的学生人数。 
SELECT sdept, COUNT(*) FROM Student GROUP BY sdept;
--四、having子句
--1．查询借书数量不低于2本的读者编号。 
SELECT reader_ID, COUNT(*) FROM Borrow GROUP BY reader_ID HAVING COUNT(*) > 2 ;
--2．查询学生选修课程的平均成绩高于75分的课程号。 
SELECT cno FROM Sc GROUP BY cno HAVING AVG(grade) > 75;
--3．查询人数超过2人的系。 
SELECT sdept AS  人数超过2人的系 FROM Student GROUP BY sdept HAVING COUNT(*) > 2 ;
--4．查询选修了3门及3门以上课程的学生学号。 
SELECT sno AS 选修了3门及3门以上课程的学生学号 FROM Sc GROUP BY sno HAVING COUNT(*) >= 3;
--5．查询选课门数等于或大于4门的学生的平均成绩和选课门数 
SELECT sno AS 选课门数等于或大于4门的学生, AVG(grade) AS 平均成绩, COUNT(*) AS 选课门数 
FROM Sc GROUP BY sno HAVING COUNT(*) >= 4;

9.24

多表查询

--多表查询
--多表连接查询ANSI连接方式
--SELECT [DISTINCT] 列名列表
--FROM 表1 JOIN 表2 ON 连接条件 [JOIN 表3 ON 连接条件2]
-------------------------------
--多表连接查询Theta连接方式
--SELECT [DISTINCT] 列名列表
--FROM 表1, 表2 [,表3...]
--WHERE 条件 AND 条件
--------------------------------
--连接条件：表1.列名1 = 表2.列名2
--列名1和列名2是两张表之间的关联列
------------------------------------------------------------------------------
SELECT * 
FROM reader JOIN Borrow ON Reader.reader_ID  = Borrow.reader_ID;
------------------------------------------------------------------------------
SELECT * 
FROM Book JOIN Borrow ON Book.book_ID = Borrow.book_ID 
			JOIN Reader ON Reader.reader_ID = Borrow.reader_ID;
------------------------------------------------------------------------------
SELECT * FROM Book;
SELECT * FROM Borrow
SELECT * FROM Reader;
--查询借阅了数据库原理一书的读者编号和借阅日期
SELECT reader_ID, borrowdate
FROM Book JOIN Borrow 
	ON Book.book_ID = Borrow.book_ID
WHERE Name = '数据库原理';
--查询借阅了数据库原理一书的读者编号、姓名和借阅日期
SELECT Borrow.reader_ID, Book.Name, borrowdate
FROM Book JOIN Borrow
	ON  Book.book_ID = Borrow.book_ID
WHERE book.Name = '数据库原理';

课上实践

--1、查询2012-2-1借阅图书的读者编号和姓名
SELECT reader.reader_ID, reader.Name
FROM Borrow JOIN Reader
	ON Borrow.reader_ID = Reader.reader_ID
WHERE borrowdate = '2012-02-01';
--2、查询读者王旭借阅过的图书编号和借阅日期
SELECT Borrow.book_ID, Borrow.borrowdate
FROM Borrow JOIN Reader
	ON Borrow.reader_ID = Reader.reader_ID
WHERE Reader.Name = '王旭';
--3、查询胡晓丽借阅的图书编号、书名和价格，结果按照价格升序排列
SELECT Borrow.book_ID, Book.Name, Book.Price
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
			JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WHERE reader.Name = '胡晓丽'
ORDER BY Book.Price;
--4、查询查询2012年2月份借阅图书的读者编号、姓名，借阅的图书编号和书名信息
SELECT Borrow.reader_ID, Reader.Name, Borrow.book_ID, Book.Name
FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
			JOIN Book ON Borrow.book_ID = Book.book_ID
WHERE Borrow.borrowdate BETWEEN '2012-02-01' AND '2012-02-29';
--5、统计每个读者借阅图书数量和平均价格，并显示读者号和读者名
SELECT COUNT(*) AS 借阅图书数量, AVG(Price) AS 平均价格, Borrow.reader_ID, Reader.Name
FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
			JOIN Book ON Borrow.book_ID = Book.book_ID
GROUP BY Borrow.reader_ID, Reader.Name;
--6、查询借阅图书数量多于2本的读者编号和读者姓名
SELECT Borrow.reader_ID, Reader.Name
FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
GROUP BY Borrow.reader_ID, Reader.Name
HAVING COUNT(*) > 2;

作业

Library

--Library库多表连接查询实践练习题：
--1.查询读者王旭借阅图书的书号和借书日期
SELECT Borrow.book_ID, Borrow.borrowdate
FROM Borrow JOIN Reader
	ON Borrow.reader_ID = Reader.reader_ID
WHERE Reader.Name = '王旭';
--2.查询借阅了数据库原理一书的读者编号和借阅日期
SELECT reader_ID, borrowdate
FROM Book JOIN Borrow 
	ON Book.book_ID = Borrow.book_ID
WHERE Name = '数据库原理';
--3.查询王旭借阅的图书书名
SELECT Book.Name
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
			JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WHERE Reader.Name = '王旭';
--4.查询借阅了数据库原理一书的读者姓名
SELECT Reader.Name
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
			JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WHERE Book.Name = '数据库原理';
--5.查询借阅图书价格在20-40之间的读者信息
SELECT Reader.*
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
			JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WhERE Book.Price BETWEEN 20 AND 40;
--6.查询2012-1-20借阅图书的读者姓名
SELECT Reader.Name
FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WHERE Borrow.borrowdate = '2012-01-20';
--7.查询借阅图书的平均价格超过25元的读者姓名
SELECT Reader.Name
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
			JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
GROUP BY Reader.Name
HAVING AVG(Book.Price) > 25;
--8.查询所有读者借阅图书的情况，包括没借阅过图书的读者，显示读者号，读者名，借阅书号和借阅日期
--SQL LEFT JOIN 关键字
--LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
SELECT Reader.reader_ID, Reader.Name, Borrow.book_ID, Borrow.borrowdate
FROM Reader LEFT JOIN Borrow ON Borrow.reader_ID = Reader.reader_ID
ORDER BY Reader.reader_ID;

Student

--Students库多表连接查询实践练习题目：
--1.查询选修了“数据库”的相关课程的学生学号、课程名和成绩
SELECT Sc.sno, Course.cname, Sc.grade
FROM Sc JOIN Course ON Sc.cno = Course.cno
WHERE Course.cname LIKE '%数据库%';
--2.查询选修了“数据库”的相关课程的学生学号、姓名、课程名和成绩
SELECT Student.sno, Student.sname, Course.cname, Sc.grade
FROM Sc JOIN Course ON Sc.cno = Course.cno
		JOIN Student ON Sc.sno = Student.sno
WHERE Course.cname LIKE '%数据库%';
--3.查询刘晨选修的所有课程的总成绩
SELECT SUM(Sc.grade) AS 刘晨选修的所有课程的总成绩
FROM Sc JOIN Student ON Sc.sno = Student.sno
WHERE Student.sname = '刘晨';
--4.查询每个学生的选课信息，显示学号，姓名，选修课号，课名和成绩
SELECT Student.sno, Student.sname, Course.cno, Course.cname, Sc.grade
FROM Sc JOIN Course ON Sc.cno = Course.cno
		JOIN Student ON Sc.sno = Student.sno
GROUP BY Student.sno, Student.sname, Course.cno, Course.cname, Sc.grade;
--5.查询学生的信息以及修课情况，包括选修了课程的学生和没有修课的学生。
--SQL LEFT JOIN 关键字
--LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
SELECT Student.*, Sc.cno AS 选修课程号, Sc.grade
FROM Student LEFT JOIN Sc ON Student.sno = Sc.sno;

9.28

外连接

------------------------
--内连接：只有满足连接条件的记录才显示到结果集中
--外连接：满足和不满足连接条件的记录也会显示到结果集中
--	外连接分类：左外连接、右外连接、全外连接
--			左外连接：指左边表记录全部显示，不满足连接条件的记录也显示，对应右边表的列会显示空值(NULL)
--			右外连接：类似左外连接
--			全外连接：指两边表的记录全部显示。
-------------------------------------------------------------------
SELECT * 
FROM Book LEFT JOIN Borrow ON Book.book_ID = Borrow.book_ID;
--Book表记录全部显示
----------------------------------------------
SELECT * 
FROM Borrow RIGHT JOIN Reader ON Borrow.reader_ID = Reader.reader_ID;
--Reader表记录全部显示
----------------------------------------------------------------------
SELECT *
FROM Book FULL JOIN Borrow ON Book.book_ID = Borrow.book_ID 
			FULL JOIN Reader ON Reader.reader_ID = Borrow.reader_ID;
--三张表全部显示
-----------------------------------------------------------------------------------------------
--查询所有读者借阅图书的信息，包括没借阅过图书的读者信息，显示书号，书名，价格和借阅者姓名和借阅日期
--右外连接
SELECT Book.book_ID, Book.Name, Book.Price, Reader.Name, Borrow.borrowdate
FROM Book JOIN Borrow ON Book.book_ID = Borrow.book_ID 
		RIGHT JOIN Reader ON Reader.reader_ID = Borrow.reader_ID;
--左外连接 左外连接的时候每次都要LEFT JOIN
SELECT Book.book_ID, Book.Name, Book.Price, Reader.Name, Borrow.borrowdate
FROM Reader LEFT JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
			LEFT	JOIN Book ON Borrow.book_ID = Book.book_ID;

嵌套子查询

--嵌套子查询
--一个SELECT...FROM...查询语句嵌套在另一个SQL语句中
--总结：
--		1.大部分子查询用在WHERE条件和HAVING条件中
--		2.IN可以替换=
--		3.执行顺序：先执行内层的子查询，后执行外层的主查询(不相关子查询)
--------------------------------------------------------------------
--查询所有图书的最高价格
SELECT MAX(price) FROM Book;
--查询价格最高的图书书名
SELECT Name FROM Book WHERE Price = (SELECT MAX(price) FROM Book);
--查询价格超过 所有图书平均价格 的图书信息
SELECT *
FROM Book
WHERE Price > (SELECT AVG(Price) FROM Book);
--查询 平均价格 比 清华大学出版社图书平均价格 高的出版社名
SELECT publish
FROM Book
GROUP BY publish
HAVING AVG(Price) > (SELECT AVG(Price) FROM BOOK WHERE publish = '清华大学出版社');
-----------------------------------------------------------------------------------------
--查询与 C语言在同一出版社的 图书信息
SELECT * FROM Book
WHERE publish = (SELECT publish FROM Book WHERE Name = 'C语言');
--查询 王旭借阅的 图书书号和借阅日期
SELECT Book_ID, borrowdate
FROM Borrow
WHERE Reader_ID = (SELECT reader_ID FROM Reader WHERE Name = '王旭');
--查询 借阅了数据库原理一书 的 读者编号和借阅日期
SELECT Reader_id, borrowdate
FROM Borrow
WHERE book_ID = (SELECT book_ID FROM Book WHERE Name = '数据库原理');
--查询 2012-02-01借阅了图书的读者编号、姓名
SELECT reader_ID, Name
FROM Reader
--和多个值比较，要用IN
--用IN替换等号
WHERE reader_ID IN (SELECT reader_ID FROM Borrow WHERE borrowdate = '2012-02-01');
---------------------------------------------------------------------------------------
--查询读者王旭借阅的图书书号，书名
SELECT Borrow.book_ID, Book.Name
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
WHERE Borrow.reader_ID IN (SELECT reader_ID FROM Reader WHERE Reader.Name = '王旭');

SELECT book_ID, Name FROM Book WHERE book_ID IN (
	SELECT book_ID FROM Borrow WHERE reader_ID IN (
    	SELECT reader_ID FROM Reader WHERE Name = '王旭'
    )
);
--查询借阅了数据库原理一书的读者编号和姓名
SELECT Borrow.reader_ID, Reader.Name
FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WHERE Borrow.book_ID IN (SELECT book_ID FROM Book WHERE Name = '数据库原理');

SELECT reader_ID, Name FROM Reader WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow WHERE book_ID IN (
    	SELECT book_ID FROM Book WHERE Name = '数据库原理'
    )
);

10.5

嵌套子查询2

--子查询返回多个值的时候：
--	相等判断用 IN 或 NOT IN 
--大于小于判断用 > < >= <=,后面加 some 或 all 关键字
--some表示：其中(子查询返回的多个值)任意一个
--all表示：所有值
--惯常用法： >= all (子查询)：求子查询返回的最大值
--			<= all (子查询)：求子查询返回的最小值

--查询价格最高的图书书名
SELECT Name FROM Book WHERE Price >= all(SELECT Price FROM Book);
--ORDER BY + TOP 求最值的方法(非标准SQL)
SELECT TOP 1 Name FROM Book ORDER BY Price DESC;
--SOME
SELECT Name FROM Book WHERE NOT ( Price < SOME(SELECT Price FROM Book) );
-------------------------------------------------------------------------------
--查询胡晓丽借阅高等数学的借阅日期
SELECT borrowdate FROM Borrow
WHERE reader_ID IN (SELECT Reader.reader_ID FROM Reader WHERE Name = '胡晓丽')
AND book_ID IN (SELECT book_ID FROM Book WHERE Name = '高等数学');
--查询借阅图书数量多于2本的读者编号和姓名
SELECT reader_ID, Name FROM Reader 
WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow 
	GROUP BY reader_ID 
	HAVING COUNT(*) > 2
);
--查询借阅图书价格高于30元的读者编号和姓名
SELECT Reader_ID, Name FROM Reader WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow WHERE book_ID IN (
		SELECT book_ID FROM Book WHERE Price > 30
	)
);
----------------------------------
--查询出版社图书数量最多的出版社名
SELECT publish FROM Book 
GROUP BY publish
HAVING COUNT(*) >= all( 
	SELECT COUNT(*) FROM Book GROUP BY publish
);

--查询借阅图书最早的读者姓名
SELECT Reader.Name FROM Reader
JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
WHERE borrowdate <= ALL( SELECT borrowdate FROM Borrow );

SELECT Name FROM Reader WHERE reader_ID IN (
	SELECT reader_id FROM Borrow WHERE borrowdate <= ALL (
		SELECT borrowdate FROM Borrow
	)
);
--查询平均价格最高的图书类别
SELECT classify FROM Book
GROUP BY classify
HAVING AVG(Price) >= ALL(
	SELECT AVG(Price)Price 
	FROM Book
	GROUP BY classify
);
--查询借阅图书数量最多的读者编号和姓名
SELECT reader_ID, Name FROM Reader
WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow 
	GROUP BY reader_ID
	HAVING COUNT(*) >= ALL (
		SELECT COUNT(*) 
		FROM Borrow 
		GROUP BY reader_ID
	)
);

作业

Library库子查询练习题

--Library库子查询练习题：
--1.查询价格最高的图书的书名
SELECT Name FROM Book WHERE Price >= ALL(SELECT Price FROM BOOK);
--2.查询价格高于所有图书平均价格的图书信息
SELECT * FROM Book WHERE Price > (SELECT AVG(Price) FROM Book);
--3.查询平均价格超过清华大学出版社的图书平均价格的出版社名
SELECT publish FROM Book GROUP BY publish 
HAVING AVG(Price) > (SELECT AVG(Price) FROM BOOK WHERE publish = '清华大学出版社');
--4.查询读者王旭借阅的图书书号
SELECT Book_ID FROM Borrow WHERE Reader_ID = (SELECT reader_ID FROM Reader WHERE Name = '王旭');
--5.查询借阅过C语言一书的读者编号
SELECT reader_ID FROM Borrow WHERE book_ID IN (
	SELECT book_ID FROM Book WHERE Name = 'C语言'
);
--6.查询2012-1-20借阅图书的读者信息
SELECT * FROM Reader WHERE reader_ID IN ( 
	SELECT reader_ID FROM Borrow WHERE borrowdate = '2012-01-20'
);
--7.查询王旭借阅的图书书名
SELECT Book.Name FROM Book WHERE book_ID IN (
	SELECT book_ID FROM Borrow WHERE reader_ID IN (
		SELECT reader_ID FROM Reader WHERE Name = '王旭'
	)
);
--8.查询借阅了C语言一书的读者姓名
SELECT Reader.Name FROM Reader WHERE reader_ID IN(
	SELECT reader_ID FROM Borrow WHERE book_ID IN (
		SELECT book_ID FROM Book WHERE Name = 'C语言'
	)
);
--9.查询借阅图书的价格在20-40之间的读者姓名
SELECT Reader.Name FROM Reader WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow WHERE book_ID IN (
		SELECT book_ID FROM Book WHERE Price BETWEEN 20 AND 40
	)
);
--10.查询与C语言在同一出版社的图书信息
SELECT * FROM Book
WHERE publish = (SELECT publish FROM Book WHERE Name = 'C语言');
--11.查询最早借阅图书的读者编号
SELECT reader_ID FROM Reader WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow WHERE borrowdate <= ALL(SELECT borrowdate FROM Borrow)
);
--12.查询最早借阅图书的读者信息
SELECT * FROM Reader WHERE reader_ID IN (
	SELECT reader_ID FROM Borrow WHERE borrowdate <= ALL(SELECT borrowdate FROM Borrow)
);
--13.查询最早借阅图书的读者姓名和借阅日期
SELECT Reader.Name, Borrow.borrowdate FROM Reader JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
WHERE Borrow.reader_ID IN (
	SELECT Borrow.reader_ID WHERE Borrow.borrowdate <= ALL(SELECT Borrow.borrowdate FROM Borrow)
);
--14.查询最早借阅图书的读者信息和所借图书信息，以及借阅日期
SELECT Book.*, Borrow.borrowdate 
FROM Book JOIN Borrow ON Book.book_ID = Borrow.book_ID 
WHERE Borrow.book_ID IN ( 
	SELECT Borrow.book_ID FROM Borrow WHERE borrowdate <= ALL(SELECT borrowdate FROM Borrow)
);
--15.查询出版图书数量最多的出版社名称。
SELECT publish FROM Book 
GROUP BY publish
HAVING COUNT(*) >= all( 
	SELECT COUNT(*) FROM Book GROUP BY publish
);

Students库子查询练习题

--Students库子查询练习题：
--1.查询计算机系学生的修课情况，要求列出学生的名字、所修课程的课程号和成绩。
SELECT Student.sname, Sc.cno, Sc.grade FROM Student JOIN Sc ON Student.sno = Sc.sno WHERE Student.sdept IN ('计算机系');
--2.查询选修VB课程的学生的修课成绩，要求列出学生学号和成绩。
SELECT Sno, grade FROM Sc JOIN Course ON Sc.cno = Course.cno WHERE cname = 'VB';
--3.查询与“刘晨”在同一个系的学生的姓名和所在系。
SELECT sname, sdept FROM Student WHERE sdept IN (
	SELECT sdept FROM Student WHERE sname = '刘晨'
);
--4.查询平均成绩高于75分的学生学号、姓名、系别。
SELECT sno, sname, sdept FROM Student WHERE sno IN (
	SELECT sno
	FROM Sc 
	GROUP BY sno
	HAVING AVG(grade) > 75
);
--5.查询选修了C02课程且成绩超过C02平均成绩的学生学号。
SELECT sno FROM Sc WHERE ( (cno = 'C02') AND ( grade > (SELECT AVG(grade) FROM Sc WHERE cno = 'C02') ) );
--6.查询选修了“数据库基础”课程的学生学号、姓名。
SELECT sno, sname FROM Student WHERE sno IN (
	SELECT sno FROM Sc WHERE cno IN (
		SELECT cno FROM Course WHERE cname = '数据库基础'
	)
);
--7.查询哪些课程没有人选，列出课程名和课程号。
SELECT cno, cname FROM Course WHERE cno NOT IN (
	SELECT cno FROM SC WHERE cno IN (
		SELECT cno FROM Course
	)
);

10.8

练习

--查询平均书价最高的出版社的信息
SELECT publish FROM Book 
WHERE publish IS NOT NULL
GROUP BY publish 
HAVING AVG(Price) >= ALL(
	SELECT AVG(Price) 
	FROM Book 
	WHERE publish IS NOT NULL 
	GROUP BY publish
); 
--2.借阅图书最多的读者的编号，姓名和借阅书号，书名和借阅日期
SELECT Reader.reader_ID, Reader.Name, Book.book_ID, Book.Name, Borrow.borrowdate
FROM Reader JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
			JOIN Book ON Borrow.book_ID = Book.book_ID
WHERE Borrow.reader_ID IN (
			SELECT reader_ID FROM Borrow GROUP BY reader_ID
			HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM Borrow GROUP BY reader_ID)
);
--3.查询所借图书的平均价格多于30元的读者的姓名
SELECT Name FROM Reader WHERE reader_ID IN(
	SELECT reader_ID FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
	GROUP BY reader_ID
	HAVING AVG(Price) > 30
);

SELECT Reader.Name
FROM Reader JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
			JOIN Book ON Borrow.book_ID = Book.book_ID
GROUP BY Reader.reader_ID, Reader.Name
HAVING AVG(price) > 30;

复杂查询

--复杂查询
--自连接：一张表和自身进行连接。必须给表起别名。
--起别名后语句中所有用到该表的地方都只能用别名
--查询与C语言在同一个出版社的其他图书信息
SELECT * FROM Book WHERE publish IN (
	SELECT publish FROM Book WHERE Name = 'C语言'
) AND Name != 'C语言';
--自连接实现
SELECT otherBook.*
FROM Book AS CBook JOIN Book AS otherBook ON CBook.publish = otherBook.publish
WHERE CBook.Name = 'C语言' AND otherBook.Name != 'C语言';
--派生关系：把一个查询结果当作一张临时的表，从中进行查询
SELECT MAX(avg_p) FROM 
(SELECT publish, AVG(Price) FROM Book GROUP BY publish) AS T(pub, avg_p);
--并集查询：UNION把两个查询结果求并集(UNION会去除重复记录)
SELECT * FROM Book WHERE Name = 'C语言'
UNION ALL
SELECT * FROM Book WHERE publish = '同济大学出版社';

数据更新

--添加记录：INSERT INTO 表名(列名列表) VALUES(值列表), (值列表)...
--表中的约束被限定添加的数据是否满足要求，不满足约束的要求无法插入
INSERT INTO Reader(reader_ID, Name, gender, birthdate)
			VALUES('021B310001', '张冬', '男', '1976-11-26');

INSERT INTO Book(book_ID, Name, Price)
			VALUES('A32DT00005', 'JAVA语言', 55);
-------------------------------------------------------
--更新数据：UPDATE 表名 SET 列名 = 值, 列名1 = 值1, .....
--			[WHERE 条件]
UPDATE Reader SET birthdate = '2000-11-26' WHERE Name LIKE '张%';

UPDATE Book SET author = '张三', publish = 'Neusoft' WHERE publish IS NULL;

--修改胡晓丽借阅图书的日期
UPDATE Borrow SET borrowdate = '2020-09-01' 
	WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE Name = '胡晓丽');
--	多表连接方式
UPDATE Borrow SET borrowdate = '2020-09-01' 
FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID
WHERE Reader.Name = '胡晓丽';

作业

Student

--作业：
--1、向student表中插入记录（学号：04101，姓名：张三，性别：男，年龄：20，系别：计算机系）
INSERT INTO Student(sno, sname, ssex, sage, sdept)
			VALUES('04101', '张三', '男', 20, '计算机系');
--2、向student表中插入记录（学号：04102，姓名：李四）
INSERT INTO Student(sno, sname) 
			VALUES('04102', '李四');
--3、向student表中插入记录（学号：04103，姓名：王五，系别：信管系）
INSERT INTO Student(sno, sname, sdept)
			VALUES('04103', '王五', '信管系');
--4、向student表中插入记录（学号：04104，姓名：赵六，性别：女，年龄：null，系别：null）
INSERT INTO Student(sno, sname, ssex, sage, sdept)
			VALUES('04104', '赵六', '女', NULL, NULL);
--5、修改student表中学号为04103的学生性别为女
UPDATE Student SET ssex = '女' WHERE sno = '04103';
--6、修改student表中学号为04104的学生出生日期为1984-5-1，系别为计算机系
--		没有出生日期列...
--7、修改出生日期为null的学生出生日期为1984－1－01
--		没有出生日期列...
--8、将计算机系所有学生的出生日期设为null
--		没有出生日期列...
--9、删除student表中学号为04104的学生
DELETE Student WHERE sno = '04104';
--10、删除student表中计算机系的所有男生
DELETE Student WHERE ssex = '男';
--11、删除信息系学生的选课记录
DELETE Sc WHERE sno IN (SELECT sno FROM Student WHERE sdept = '信息系');
--12、删除刘晨同学的选课记录
DELETE Sc WHERE sno IN (SELECT sno FROM Student WHERE sname = '刘晨');

Library

--练习：
--1、向book表中插入记录（ID：B32DT00001，Name：高等数学，Author：赵丹，publish：同济大学出版社，price：42）
INSERT INTO Book(book_ID, Name, author, publish, Price)
			VALUES('B32DT00001', '高等数学', '赵丹', '同济大学出版社', 42);
--2、向book表中插入记录（ID：B32DT00002，Name：离散数学）
INSERT INTO Book(book_ID, Name)
			VALUES('B32DT00002', '离散数学');
--3、向book表中插入记录（ID：B32DT00003，Name：线性代数，Publish：高等教育出版社）
INSERT INTO Book(book_ID, Name, publish)
			VALUES('B32DT00003', '线性代数', '高等教育出版社');
--4、向book表中插入记录（ID：B32DT00004，Name：概率论，Publish：NULL，Price：22）
INSERT INTO Book(book_ID, Name, publish, Price)
			VALUES('B32DT00004', '概率论', NULL, 22);
--5、修改book表中ID为B32DT00002的书Author为王旭，publish为高等教育出版社，价格为31）
UPDATE Book SET author = '王旭', publish = '高等教育出版社' WHERE book_ID = 'B32DT00002';
--6、修改book表中publish为NULL的书publish为机械工业出版社
UPDATE Book SET publish = '机械工业出版社' WHERE publish IS NULL;
--7、修改book表中价格高于20元的书publish为NULL
UPDATE Book SET publish = NULL WHERE Price > 20;
--8、修改王旭的借阅时间为2013-10-28
UPDATE Borrow SET borrowdate = '2013-10-28' WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE Name = '王旭');
--9、删除book表中ID为B32DT00003的书
DELETE FROM Book WHERE book_ID = 'B32DT00003';
--10、删除book表中price为NULL的书
DELETE FROM Book WHERE Price IS NULL;
--11、删除book表中高等教育出版社出版的价格高于40元钱的书
DELETE FROM Book WHERE publish = '高等教育出版社' AND Price > 40;
--12、删除王旭借阅记录
DELETE FROM Borrow WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE Name = '王旭');

10.12

删除数据

--删除数据： DELETE FROM 表名 [WHERE 条件]
--如果没有WHERE条件， 则删除表中全部记录
--删除表： DROP TABLE 和 DELETE FROM 表名 的区别
--	DROP TABLE删除表的定义，数据一并删除
--	DELETE FROM 表名 删除表中全部记录， 表的结构(列)仍存在，变为空表
---------------------------------------------------------------------
--谨慎执行！！！
--删除Borrow表全部记录
DELETE FROM Borrow;
--删除借阅数据库原理一书的借阅记录
--	子查询：
DELETE FROM Borrow WHERE book_ID IN (
	SELECT book_ID FROM Book WHERE Name = '数据库原理'
);
--	多表连接：
DELETE FROM Borrow 
FROM Borrow JOIN Book ON Borrow.book_ID = Book.book_ID
WHERE Name = '数据库原理';

视图

--视图与基本表区别：
--基本表是用 CREATE TABLE 语句创建的表，是真实存储数据的表
--视图是从基本表中查询出来的数据构成的虚表，不存储数据，数据库中只存储定义
----------------------------------------------
--创建视图
--	CREATE VIEW 视图名[ (列名列表) ] 
--	AS 
--	SELECT 查询语句
--	[WITH CHECK OPTION]
--	要点： 如果创建视图时有 WITH CHECK OPTION 子句， 则对视图的更新会CHECK更新数是否满足SELECT子查询中的WHERE条件，不满足则不允许更新
--			换种说法，就是保证对视图的更新仍然能通过视图看到
----------------------------------------------

--在Book表上创建清华大学出版社的图书视图QH_BookView
CREATE VIEW QH_BookView
AS SELECT * FROM Book WHERE publish = '清华大学出版社';

SELECT * FROM QH_BookView;
SELECT * FROM Book WHERE publish = '清华大学出版社';

INSERT INTO QH_BookView(book_ID, Name, Price)
			VALUES('1001', 'C++', 50);

--创建清华大学出版社的图书视图QH_BookView1， 要求对视图的更新仍然能够通过视图看到
CREATE VIEW QH_BookView1
AS SELECT * FROM Book WHERE publish = '清华大学出版社'
WITH CHECK OPTION;

INSERT INTO QH_BookView1(book_ID, Name, publish, Price)
			VALUES('1002', 'JAVA', '东软电子出版社', 35);--失败
INSERT INTO QH_BookView1(book_ID, Name, publish, Price)
			VALUES('1002', 'JAVA', '清华大学出版社', 35);--成功

SELECT * FROM Book;

--在Borrow表上创建2012年的借阅视图2012_BorrowView
CREATE VIEW BorrowView_2012
AS SELECT * FROM Borrow WHERE YEAR(borrowdate) = 2012;

----------------------------------------------------------
--创建R_B_Book视图，查询所有读者借阅图书的读者编号、姓名、图书编号、书名、出版社、价格、借阅日期信息。
CREATE VIEW R_B_Book(读者编号, 姓名, 图书编号, 书名, 出版社, 价格, 借阅日期)
AS SELECT Reader.reader_ID, Reader.Name, Book.book_ID, Book.Name, publish, Price, borrowdate 
FROM Reader JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
			JOIN Book ON Borrow.book_ID = Book.book_ID;

SELECT * FROM R_B_Book;

--基于上例中的视图R_B_Book，创建”王旭”的读者借阅的图书书名和出版社信息和借阅日期的视图WXBorrow
--注意：从视图查询应该用视图定义时的列名，而不是原表的列名
CREATE VIEW WXBorrow
AS SELECT 书名, 出版社, 借阅日期 
FROM R_B_Book --基于视图的视图
WHERE 姓名 = '王旭';

SELECT * FROM WXBorrow;

--创建读者信息的视图ReaderInfo,包括读者编号、姓名和年龄，在视图中的列名分别为ID，Name和Age
CREATE VIEW ReaderInfo(ID, Name, Age)
AS SELECT Reader.reader_ID, Reader.Name, YEAR(GETDATE())-YEAR(birthdate) 
FROM Reader;

SELECT * FROM ReaderInfo;

--创建每个出版社出版图书的平均价格的视图PerPublish_AVG
CREATE VIEW PerPublish_AVG(出版社, 平均价格)
AS SELECT publish, AVG(Price) FROM Book GROUP BY publish;

SELECT * FROM PerPublish_AVG;

10.15

--修改视图
--	ALTER VIEW 视图名[(列名1[,...n])]
--	AS SELECT 查询语句
--删除视图
--	DROP VIEW 视图名
--删除视图时注意：按照参照的逆序删除

视图的作用

• 简化用户的操作
• 使用户以多种角度看待同一数据
• 对重构数据库提供了一定程度的逻辑独立性
• 对机密数据提供安全保护
• 适当地利用视图可以更清晰、更简洁地表达查询

作业

--第三章作业题：
--1．什么是基本表？什么是视图？两者的区别和联系是什么？
--基本表是用 CREATE TABLE 语句创建的表，是真实存储数据的表
--视图是从基本表中查询出来的数据构成的虚表，不存储数据，数据库中只存储定义
--视图是在基本表之上建立的表，它的结构和内容都来自基本表，它依据基本表存在而存在。
--一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
--2. 视图的作用有哪些（至少列举三个）。
--对重构数据库提供了一定程度的逻辑独立性
--对机密数据提供安全保护
--适当地利用视图可以更清晰、更简洁地表达查询
--3．创建一个读者借书的视图R _ B_View ( ReaderName, BookName, BookAuthor, BookPublish, BookPrice, BorrowDate) 
CREATE VIEW R_B_View(ReaderName, BookName, BookAuthor, BookPublish, BookPrice, BorrowDate)
AS SELECT Reader.Name, Book.Name, Book.author, Book.publish, Book.Price, Borrow.borrowdate 
FROM Reader JOIN Borrow ON Reader.reader_ID = Borrow.reader_ID
			JOIN Book ON Borrow.book_ID = Book.book_ID;
--4．从视图R_B_View中查找2005-5-22日借书的读者姓名和书名
SELECT ReaderName, BookName FROM R_B_View WHERE BorrowDate IN (2005-05-22);
--5. 创建一个学生选课信息视图SView，包含学生的学号Sno，所修学分总和Sum_Credit，和总成绩Sum_Grade。
CREATE VIEW SView(学号, 所修学分总和, 总成绩)
AS SELECT Student.sno, SUM(Course.ccredit), SUM(Sc.grade)
FROM Student JOIN Sc ON Student.sno = Sc.sno
			JOIN Course ON Course.cno = Sc.cno
GROUP BY Student.sno;
--6. 从视图SView中查询总成绩最高的学生学号。
SELECT 学号 FROM SView WHERE 总成绩 >= (SELECT MAX(总成绩) FROM SView);
--7. 创建一个计算机系学生信息的视图JXView，包含学生的学号、姓名、年龄和性别，并且要求对该视图的所有修改要满足计算机系的条件。
CREATE VIEW JXView(学号, 姓名, 年龄, 性别)
AS SELECT sno, sname, sage, ssex FROM Student
WITH CHECK OPTION;
--8. 创建一个学生选修课程的视图SCView（Sno, Sname, Sdept, Cno, Cname, Credit, Grade）
CREATE VIEW SCView(Sno, Sname, Sdept, Cno, Cname, Credit, Grade)
AS SELECT Student.sno, sname, sdept, Course.cno, cname, Course.ccredit, grade
FROM Student JOIN Sc ON Student.sno = Sc.sno
			JOIN Course ON Course.cno = Sc.cno;
--9．从视图SCView中查询每个学生的选课门数、总学分和总成绩。
SELECT Sno, COUNT(Cno) AS 选课门数, SUM(Credit) AS 总学分, SUM(Grade) AS 总成绩 FROM SCView
GROUP BY Sno;
--10. 简述索引的概念和作用。
--概念：索引实际上是一个单独的、物理的数据库结构，它是表中的一个或多个列(成为搜索关键字)
--的值的集合和相应的指向表中物理表示这些值的数据页的逻辑指针清单
--11. 简述索引的优缺点。
--优点：1可以大大加快数据的检索速度
--		2通过创建唯一性索引，可以确保表中每一行数据的唯一性
--		3可以加速表于表之间的连接，特别有利于实现数据的参照完整性
--		4在使用分组子句和排序子句进行数据检索时，可以显著提高查询中分组和排序的效率
--缺点：1创建索引要耗费时间
--		2索引要占据数据库的物理空间
--		3维护索引要花费很多的时间
--12. 设经常执行如下查询语句：
--Select book_id, name from book where price<50 and name like '%数据库%';
--创建一个索引提升此查询的性能。
CREATE INDEX bookIndex ON Book(Price, Name);

10.19

索引

--创建索引
--CREATE [UNIQUE] [CLUSTERED] INDEX 索引名
--ON 表名|视图名(列名 [ASC|DESC] [,...]);

--为读者表[读者姓名]列创建索引IDX_NAME
CREATE INDEX IDX_NAME ON Reader(Name);
--为图书信息表中的图书编号一列创建聚集索引IDX_ID，并且强制唯一性
CREATE UNIQUE CLUSTERED INDEX IDX_ID ON Book(book_ID);
--在图书表上创建复合索引IDX_Unite首先在publish上创建降序索引，若publish列值相同的图书再按照book_ID列创建升序索引
CREATE INDEX IDX_Unite ON Book(publish DESC, book_ID ASC);

--删除索引
--DROP INDEX 表名.索引名|视图.索引名 [,...];
DROP INDEX Reader.IDX_NAME;

关系模型

关系模型三要素

• 关系数据结构

  • 行：元组/记录
    • 行的顺序无所谓
    • 任意两行不能完全相同(主码约束)
  • 列：属性字段
    • 列的顺序无所谓
    • 同一列中的数据是相同性质的
    • 每一列要有唯一的列名
    • 每一列中的值都是原子的，不可再分的
  • 候选码(候选键)：如果关系中的某一个或者一组属性的值能够唯一的确定一个元组，则该属性或属性组成为候选码
  • 主码(主键)：如果一个关系包含多个候选码，可以任选其中一个作为主码。因为一个关系只能设定一个主码
  • 外码(外键)：用以建立列之间的参照关系，使得一个列的取值受另一个列的约束(可以为空值)
  • 关系模式
    • 一般来说，关系就是一张二维表，二维表的表头哪一行成为关系模式，又称表的框架或记录类型。关系模式是对关系的抽象描述
    • 关系模式可表示为 关系名(属性名1, 属性名2, …, 属性名n)

• 关系操作

• 关系完整性约束

  • 指数据的正确性和相容性，即数据是正确的、有意义的

  • 实体完整性约束(主键)

    • 每张表有且只有一个主码
    • 主码非空且唯一

  • 参照完整性约束(外键)

    • 外码要么取空值，要么取对应主码范围内的值

  • 用户自定义完整性约束

    • 检查(取值范围)
    • 非空
    • 唯一
    • 缺省值

数据库的系统结构

三级模式

概念模式

外模式

内模式

两级映像

模式/内模式的映像

外模式/模式的映像

数据的独立性

物理独立性

逻辑独立性

11.12

安全性

一个用户的最终权限 = 所属角色的权限 + 管理员或其他用户授予的权限

一些 绝密 图片，自己感悟

上传已经过作者同意