SQL的特点

  1. 功能一体化
  2. 高度非过程化
  3. 面向集合的操作方式

1.单表无条件查询

1
2
3
4
5
select * from Reader
select distinct sex from Reader --去重
select book_id ,name ,price*0.9 from Book
select book_id ,name ,price*0.9 列名 from Book
select name,'birthdate',birthdate from reader

2.单表条件查询

1
select * from book where price>30--查询价格大于30的记录

在这里插入图片描述

3.例题

在这里插入图片描述

1
2
3
4
5
select * from reader where sex = '女'
select * from book where price>=30 and price<=50
select * from book where publish='机械工程出版社' or publish='清华大学出版社'
SELECT * from Book WHERE price >=25 AND price <=50
SELECT * from Book WHERE price BETWEEN 25 AND 50

4.like的用法

在这里插入图片描述
【例2.35】查询名字中带有“晓”字的人的信息 

1
Select * from reader where name like ‘王_’

【例2.36】查询所有不姓张的读者的信息

1
2
3
4
5
Select * from reader where name like ‘%晓%’
```  
【例2.34】查询名字是两个字的姓“王”的读者的信息  
```sql
Select * from reader where name not like ‘张%’

空值比较：is null

【例2.37】查询性别为空的读者的信息

1
2
SELECT * FROM Reader 
WHERE sex IS NULL

【例2.38】查询出生日期不为空的读者的姓名 

1
Select name from reader where birthdate is not  null

多重条件查询

【例2.38】查询1977年以后出生的女读者的姓名

1
2
SELECT name FROM Reader 
WHERE birthdate >= ’1977-1-1’ AND sex=‘女’

【例2.38】查询1977年出生的堵着的信息 

1
2
3
4
5
select * from Reader where birthdate>='1977-1-1' and birthdate <='1977-12-31' 
select * from Reader where birthdate like '%1977%' 
select * from Reader where year(birthdate) =1977 
select * from Reader where datename(YY,birthdate) =1977

排序 order by 子句

【例2.39】查询读者的信息按出生日期的升序显示

1
2
SELECT * FROM Reader
ORDER BY birthdate ASC

【例2.40】查询读者的信息按出生日期的“降序”显示 

1
2
SELECT * FROM Reader 
ORDER BY birthdate DESC

语法格式

ORDER BY <列名> [ASC|DESC][,…n]
ASC表示升序排序；DESC表示降序排序
默认情况为升序排序
注：对于空值，若按照升序排序，含空值的元组将最后显示。若按降序排序，空值的元组将最先显示。

【例2.41】查询图书的信息，查询结果按照出版社的名称升序排序，同一出版社的按照价格的降序排序。 

1
2
SELECT * FROM Book 
ORDER BY publish ASC,price DESC

聚集函数

聚集函数（也叫集合函数），方便统计一些数据。
COUNT（*）: 统计表中元组个数；
COUNT（列名）: 统计本列列值个数；
SUM（列名）:计算列值总和（必须是数值型列）；
AVG（列名）:计算列值平均值（必须是数值型列）；
MAX（列名）: 求列值最大值；
MIN（列名）: 求列值最小值。

【例2.42】统计全部图书的平均价格 

1
SELECT AVG(price) AS 平均价格 FROM Book

【例2.43】查询最高的图书价格

1
SELECT MAX(price) FROM Book

【例2.44】统计读者的总人数

1
SELECT COUNT(*) FROM Reader
1
SELECT COUNT(distinct sex) FROM Reader

查询到2行结果

完整内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
create table Book
(
    book_ID char(10) primary key,
    name varchar(30) Not Null,
    author varchar(10),
    publish varchar(20),
    price decimal(6,2) check(price>0),
    classify varchar(20)
)
create table Reader
(
    reader_ID char(10) primary key,
    name varchar(8),
    sex 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)
)

create table Student
(
    sno char(7) PRIMARY key,
    sname char(20) not NULL,
    ssex char(2) check(ssex='男' or ssex='女'),
    sage int check(sage>= 15 and sage <= 45),
    sdept char(20) DEFAULT('计算机系')
)

create table Course
(
    cno char(10) PRIMARY key,
    cname char(20) not NULL,
    ccredit int check(ccredit>0),
    semester int CHECK(semester>0),
    period  int 
)

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)
)

ALTER TABLE Reader ADD profession CHAR(20)
ALTER TABLE Reader ALTER COLUMN profession CHAR(30)

SELECT * FROM reader
SELECT DISTINCT sex from reader

select * from Reader
select distinct sex from Reader
select book_id ,name ,price*0.9 from Book
select book_id ,name ,price*0.9 列名 from Book
select name,'birthdate',birthdate from reader
select * from book where price>30
select * from reader where sex = '女'
select * from book where price>=30 and price<=50
select * from book where publish='机械工程出版社' or publish='清华大学出版社'
SELECT * from Book WHERE price >=25 AND price <=50
SELECT * from Book WHERE price BETWEEN 25 AND 50
SELECT * FROM book where PUBLIsh IN ('机械工业出版社','清华大学出版社','高等教育出版社')
SELECT * FROM book where PUBLIsh not IN ('机械工业出版社','清华大学出版社','高等教育出版社')
SELECT * from Book WHERE price >=25 AND price <=50
SELECT * from Book WHERE price BETWEEN 25 AND 50
Select * from reader where name like '%晓%'
Select * from reader where name like '王_'
Select * from reader where name not like '张%'
SELECT * FROM Reader 
WHERE sex IS NULL
Select name from reader where birthdate is not  null
select * from Reader where birthdate>='1977-1-1' and birthdate <='1977-12-31' 
select * from Reader where birthdate like '%1977%' 
select * from Reader where year(birthdate) =1977 
select * from Reader where datename(YY,birthdate) =1977
SELECT * FROM Reader 
   ORDER BY birthdate DESC
SELECT * FROM Reader
   ORDER BY birthdate ASC
SELECT COUNT(*) FROM Reader
SELECT MAX(price) FROM Book
SELECT AVG(price) AS 平均价格 FROM Book
SELECT COUNT(sex) FROM Reader
SELECT COUNT(distinct sex) FROM Reader

分组 (group by 语句)

【例2.41】统计每个出版社的出版图书的数目。

1
SELECT COUNT(*),publish FROM Book GROUP BY publish

分组筛选条件使用having子句
【例2.45】查询出版图书多余3本的出版社名称和出版图书数目

1
2
3
4
SELECT publish,COUNT(*) FROM Book 
GROUP BY publish
HAVING COUNT(*)>3

【例2.42】统计每个人所借图书的数目，并筛选出借书超过5本的读者。

1
2
3
SELECT COUNT(book_ID),Reader_id
FROM Borrow 
GROUP BY Reader_id

【例2.43】统计每个出版社出版图书的平均价格，并显示每个出版社的名称

1
2
SELECT publish, AVG(price) AS 平均价格 FROM Book
GROUP BY publish

思考：查询出版图书平均价格高于30元的出版社名称？ 

1
select publish from book group by publish having AVG(price)>30

注意：

嵌套子查询

思考：查询和王旭同一天出生的读者的信息
分析：查询王旭的生日，

1
2
3
4
5
6
SELECT birthdate FROM Reader WHERE name=’王旭’。
```  
那么可不可以使用这个查询结果呢？  
当然可以！  
```sql
SELECT * FROM Reader WHERE birthdate=(SELECT birthdate FROM Reader WHERE name=’王旭’)

【例2.54】查询与“C语言”在同一出版社的图书信息

1
SELECT * FROM Book WHERE publish IN (SELECT publish FROM Book WHERE name=’C语言’)

【例2.55】查询王旭所借图书的图书编号

1
2
3
4
SELECT book_ID FROM Borrow WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE   
name=‘王旭’)
--- 查书名
select name from book where book_ID in(SELECT reader_ID FROM Borrow WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE name='王旭'))

some（子查询)

表示子查询的结果集合中某一个元素
【例2.57】查询除最低价格外的所有图书

1
2
SELECT * FROM Book WHERE price>SOME(SELECT price FROM Book)
Select * from book where price>(select min(price )  From book )

【例2.58】查询价格最低的图书信息

1
2
SELECT * FROM Book WHERE NOT( price>SOME(SELECT price FROM Book))
select * from book where price = (select min(price) from book)

ALL(子查询)

表示子查询的全部结果
【例2.59】查询书价最高的图书的信息 

1
2
SELECT * FROM Book WHERE price >=ALL(SELECT price FROM Book)
Select  * from book where price=(select Max(price) from book)

练习

  1. 查询平均书价最高的出版社的名称
    1
    select publish from book group by publish having AVG(price)>= all(select AVG(price) from book group by publish)
  2. 借阅图书最多的读者的信息
    1
    2
    select * from reader where reader_id =
    (select reader_id from borrow group by reader_id having count(*)>= all(select count(*) from borrow group by reader_id))
  3. “数据库原理”借阅最早的读者的信息
    1
    2
    3
    4
    5
    SELECT * from reader where reader_id in 
    ((select reader_ID from borrow where borrowdate =
    (select min(borrowdate) from Borrow where book_ID =
    (select book_id from book where name='数据库原理')) 
    and book_ID =(select book_id from book where name= '数据库原理')))

    多表连接查询

    【例2.63】查询2005年1月20日借书的读者姓名和所借图书的编号
    1
    2
    3
    SELECT  Name, book_ID 
    FROM Reader JOIN Borrow ON Reader.reader_ID =Borrow.reader_ID
    WHERE borrowdate= '2005-1-20'
    【例2.64】查询王旭所借图书的书号和借阅时间
    1
    2
    3
    4
    5
    6
    SELECT  book_ID, borrowdate
    FROM Reader JOIN Borrow ON Reader.Reader_id =Borrow.Reader_id
    WHERE name=’王旭’

    select book_id,borrowdate from borrow
    where reader_id = (select reader_id from reader where name='王旭')
    【例2.65】查询2005年6月7日借阅图书的读者姓名和所借图书名称
    1
    SELECT Reader.name, Book.name FROM Book JOIN Borrow ON Book.book_ID=Borrow.Book_ID JOIN Reader ON Reader.reader_ID=Borrow.reader_ID WHERE borrowdate='2005-6-7'
    【例2.66】查询每个读者所借的图书的信息，要求显示读者姓名，图书名称，借出日期
    1
    SELECT Reader.name AS 姓名,Book.name AS 图书名称,Borrow.Borrowdate AS 借出日期 FROM Reader JOIN Borrow ON Reader.Reader_id = Borrow.Reader_id  JOIN Book ON Borrow.book_ID=Book.book_ID
    注意：多表的同名列前面必须加表名前缀。
    即：表名.列名

    练习

  4. 查询’王旭’所借图书的书名
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    select name from book where book_ID in(
    select book_id from Borrow where Reader_ID in(
    select reader_id from Reader where name='王旭'))

    select book.name from book join borrow on 
    book.book_id=borrow.book_id join reader 
    on reader.reader_id=borrow.reader_id 
    where reader.name='王旭'
    ```   
    2. 查询借书价格在20-40之间的读者的姓名
    ```sql
    select reader.name 
    FROM Reader JOIN Borrow 
    ON Reader.Reader_id = Borrow.Reader_id  
    JOIN Book ON Borrow.book_ID=Book.book_ID 
    where price between 20 and 40

    select 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))
  5. 查询所借图书的平均价格多余30元的读者的姓名
    1
    2
    3
    4
    5
    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
  6. 查询‘胡晓丽’借阅‘高等数学’的日期
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select Borrowdate
    FROM Reader JOIN Borrow 
    ON Reader.Reader_id = Borrow.Reader_id  
    JOIN Book ON Borrow.book_ID=Book.book_ID 
    where reader.name='胡晓丽' and book.name='高等数学'

    select Borrowdate from borrow where 
    reader_id=(select reader_id from reader where name='胡晓丽' ) and book_id=(select book_id from book where 
    name='高等数学')

    插入数据

    INSERT语句的基本语法：
    INSERT INTO 表名（列名列表）
    VALUES（值列表）

【例2.85】Reader表中插入下面的数据：
读者编号：’021B310002’
读者姓名：’牟晓光’
读者性别：’女’

1
2
3
4
5
 INSERT INTO Reader(ID,name,sex)
  VALUES(‘021B310002’,’牟晓光’,’女’)
或者：
 INSERT INTO Reader
  VALUES(‘021B310002’,’牟晓光’,’女’,NULL)

修改数据

语法：UPDATE <表名> SET <列名=值|表达式>[，…] [WHERE <更新条件>]

【例2.87】所有图书的价格打8折

1
UPDATE Book SET price=price*0.8

【例2.88】将王旭所借图书的日期更改为2005-5-1
子查询方式： 

1
UPDATE Borrow SET Borrowdate= ‘2005-5-1’ WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE name= '王旭')

多表连接方式：

1
UPDATE Borrow SET Borrowdate= ‘2005-5-1’ FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID WHERE name= '王旭'

练习

更改’胡晓丽’借阅‘高等数学’的借阅日期为‘2005-6-8’

1
UPDATE Borrow SET Borrowdate= ‘2005-6-8’ FROM Borrow JOIN Reader ON Borrow.reader_ID = Reader.reader_ID join book on borrow.book_id = book.book_id WHERE reader.name= '胡晓丽'and book.name= '高等数学'
1
2
Update borrow set borrowdate='2005-6-8' where reader_id in(select reader_id from reader where name= '胡晓丽') and 
Book_id in(select book_id from book where Name= '高等数学')

删除数据

【例2.89】删除Book表中的全部数据

1
DELETE FROM Book

【例2.90】删除价格大于100元的图书

1
DELETE FROM Book WHERE price>100

练习：删除’2005-6-7’的借阅记录

1
Delete from borrow where borrowdate= ’2005-6-7’

【例2.91】删除王旭的借阅纪录
子查询方式实现

1
DELETE FROM Borrow WHERE Reader_id IN (SELECT Reader_id FROM Reader WHERE name=’王旭’)

多表连接的方式实现

1
2
3
DELETE FROM Borrow 
FROM Borrow JOIN Reader ON 
borrow.Reader_id=Reader.Reader_id WHERE name=’王旭’