本文详细介绍了如何使用MySQL进行数据库的启动、停止、连接、查询帮助、查看状态以及创建、删除数据库。此外，还讲解了数据表的创建、查询、更新和删除操作，强调了数据完整性，包括默认值约束、实体完整性和参照完整性，并通过实例展示了如何设置和检查这些约束。最后，涉及了导入外部数据、视图的创建和使用，以及函数的创建与测试。
1 .MySQL的使用
使用语句完成以下操作：
1.启动和停止MySQL服务
net start mysql
net stop mysql
2.登录(连接到)MySQL数据库
mysql -uroot -p123456
mysql -hlocalhost
myaql h127.0.0.1-yroot-p.123456
mysql -u root -p
3.查看MySQL的帮助信息
help
\h
?
\?
4.查看服务器的状态信息
\s
status
5.查看所有数据库
show databases
6.退出MySQL
\q
exit
\q
2.1 数据库的基本操作
-- 使用SQL语句完成：
-- 1. 创建数据库mydb
CREATE DATABASE mydb;
-- 2. 创建数据库shop
CREATE DATABASE shop;
-- 3. 创建数据库library00
CREATE DATABASE library50;
-- 4. 选择数据库mydb
USE mydb;
-- 5. 删除数据库school2
DROP DATABASE school2;
-- 6.采用在data目录下新建数据库文件夹的方式重新创建数据库new_mydb、 new_school
-- 提示位置：C:\ProgramData\MySQL\MySQL Server 5.5\data
#已完成
2.2 数据表的基本操作
-
- 1.在school数据库中创建表student和student_s：
-- 创建school数据库
CREATE DATABASE school;
-- (1)在SQLyog查询编辑器中创建表student
USE school;
CREATE TABLE student
(
id INT NOT NULL COMMENT '学号',
NAME VARCHAR(10) NOT NULL COMMENT '姓名',
age INT NOT NULL COMMENT '年龄',
telnum INT COMMENT '电话号码',
address VARCHAR(50) COMMENT '联系地址'
);
-- (2)在DOS会话窗口中创建表student_s
CREATE TABLE school.student_s
(
id INT NOT NULL COMMENT '学号',
NAME VARCHAR(10) NOT NULL COMMENT '姓名',
age INT NOT NULL COMMENT '年龄',
telnum INT COMMENT '电话号码',
address VARCHAR(50) COMMENT '联系地址'
);
-- 二张表结构相同，如下所示。
-- 2.查看student表的表结构
SHOW TABLES LIKE'%student%';
-- 3.查看student表的定义语句
SHOW CREATE TABLE student;
-- 4.删除student_s表
DROP TABLE school.student_s;
3.1 数据的插入
-
-- 插入数据（简化格式）
INSERT INTO tbl_bookinfo
VALUES ('TP391.4/33','7-81012-690-3','PowerPoint for Windows 95 使用指南','张珑,张均宝编','1996-06-15',NULL,656400,190,'北京航空航天大学出版社','T');
-- 插入数据（简化格式）
INSERT INTO tbl_bookinfo (BookID,ISBN,BookName, Author,PublishDate,BookVersion,WordCount,PageCount,Publisher,ClassId)
VALUES ('TP312BA/52','7-5357-1050-6','计算机基础','甘宪甫编著','1992-08-1',NULL,826800,212,'湖南科学技术出版社','T');
-- 基本格式可以用于交换列名和值
INSERT INTO tbl_bookinfo (BookID,ISBN,BookName, Author,PublishDate,BookVersion,WordCount,PageCount,Publisher,ClassId)
VALUES ('湖南科学技术出版社','TP312BA/52','7-5357-1050-6','计算机基础','甘宪甫编著','1992-08-1',NULL,826800,212,'T');
-- 基本格式可以用于只知道部分列的数据
INSERT INTO TBL_BOOKINFO(bookid,isbn,bookname,author,classid)
VALUES('0241/13','7-313-00072-3','计算方法','钱换延编','O');
-- 练习用其他二种方法实现（简化格式、全部列名的基本格式）
INSERT INTO TBL_BOOKINFO
VALUES('0241/13','7-313-00072-3','计算方法','钱换延编',NULL,NULL,NULL,NULL,NULL,'O');
-- 全部列名
INSERT INTO tbl_bookinfo (BookID,ISBN,BookName, Author,PublishDate,BookVersion,WordCount,PageCount,Publisher,ClassId)
VALUES('0241/13','7-313-00072-3','计算方法','钱换延编',NULL,NULL,NULL,NULL,NULL,'O');
-- 插入多条记录以上三种格式都可以，只是每个值（）内要一致
INSERT INTO tbl_bookinfo
VALUES ('TP391.4/33','7-81012-690-3','PowerPoint for Windows 95 使用指南','张珑,张均宝编','1996-06-15',NULL,656400,190,'北京航空航天大学出版社','T')
,('0241/13','7-313-00072-3','计算方法','钱换延编',NULL,NULL,NULL,NULL,NULL,'O');
-- 修改数据
UPDATE tbl_bookinfo
SET bookname='数值计算'
WHERE author='钱焕延编'
-- 练习
UPDATE tbl_bookinfo
SET author='张三'
WHERE bookname='数值计算'
-- 一个语句改多列
UPDATE tbl_bookinfo
SET wordcount=100000,pagecount=100,bookversion'第二版'
WHERE bookid='0241/13'
-- 统一改所有值
UPDATE tbl_user
SET PASSWORD='123'
-- 删除记录
DELETE FROM tbl_bookinfo WHERE author='钱焕延编'
DELETE FROM tbl_bookinfo WHERE bookid='TP391.4/33'
-- 清空记录
DELETE FROM tbl-bookinfo ;
TRUNCATE TABLE tbl_bookinfo;
3.3导入外部数据
- 我的答案：
4.1 域完整性
-
- 默认值约束
USE test;
CREATE TABLE my_default(
`name` VARCHAR(10),
age INT DEFAULT 18
);
-- 修改表的默认值
ALTER TABLE my_default MODIFY age INT DEFAULT 18;
-- 测试
INSERT INTO my_default VALUES('张三',DEFAULT);
INSERT INTO my_default(NAME) VALUES('李四');
INSERT INTO my_default VALUES ('a',NULL);
INSERT INTO my_default VALUES ('b',20);
-- 实训练习
CREATE DATABASE library50;
USE library50;
-- 1.为借阅表中的IsReturned列加上default约束，值为0；
CREATE TABLE tbl_borrowinfo(
bookid CHAR(15) NOT NULL,
userid CHAR(11) NOT NULL,
borrowdate DATETIME NOT NULL,
returndate DATETIME,
isreturned TINYINT(1) DEFAULT 0
);
-- 方法二
ALTER TABLE tbl_borrowinfo MODIFY isreturned TINYINT(1) DEFAULT 0;
-- 2.在借阅表中，用insert语句插入一条记录，该记录不包含IsReturned值；
-- (测试数据：张三借图书信息表中的第一本书)
INSERT INTO tbl_borrowinfo (bookid,userid,borrowdate) VALUES ('TP391.9/17','20050402001','2023-3-15');
-- 3.查看记录内容。
CREATE TABLE library50.`tbl_borrowinfo`
(ClassID CHAR(1) NOT NULL COMMENT "图书类别编号",
Classname VARCHAR(50) NOT NULL COMMENT "图书类型名称"
);
4.2 实体完整性
-
-- 实训练习一：
-- 1.将图书信息表的bookId、读者信息表的userId设置为主键。
-- 方法一
CREATE TABLE tbl_borrowinfo
ADD PRIMARY KEY('bookid');
ALTER TABLE tal_user
ADD PRIMARY KEY('userid');
)
;
-- 方法二
USE library50;
CREATE TABLE `TBL_BorrowInfo`
(
`BookID` CHAR(15) NOT NULL,
`UserID` CHAR(11) NOT NULL,
`BorrowDate` DATETIME NOT NULL,
`ReturnDate` DATETIME COMMENT,
`Isreturned` TINYINT(1) COMMENT
);
CREATE TABLE `TBL_User`
(
`UserID` CHAR(11) NOT NULL,
`UserName` VARCHAR(40) NOT NULL,
`sex` TINYINT(1) NOT NULL,
`PASSWORD` VARCHAR(20) NOT NULL,
`Email` VARCHAR(40),
`Class` VARCHAR(40) NOT NULL,
`Photo` BLOB
);
-- 2.在图书信息表中，用insert语句插入两条主键值相同的记录，查看出错信息。
INSERT INTO tbl_bookinfo VALUES('TP312BA/52','75357-1050-6','计算机基础','郑邑,王建中编著','1996-06-02',NULL,826800,212,'湖南科学技术出版社','T');
INSERT INTO tbl_bookinfo VALUES('TP312BA/52','75357-1050-6','计算机基础','郑邑,王建中编著','1996-06-02',NULL,826800,212,'湖南科学技术出版社','T');-- --1062代码重复
-- 3.在图书信息表中，用insert语句插入一条主键值为空的记录，查看出错信息。
INSERT INTO tbl_bookinfo VALUES(NULL,'7-5357-1050-6','计算机基础','郑邑,王建中编著','1996-06-02',NULL,826800,212,'湖南科学技术出版社','T');-- 1048不能为空
-- 实训练习一：
-- 1.将图书信息表的bookId、读者信息表的userId设置为主键。
-- 方法一
CREATE TABLE tbl_borrowinfo
ADD PRIMARY KEY('bookid');
ALTER TABLE tal_user
ADD PRIMARY KEY('userid');
)
;
-- 方法二
USE library48;
CREATE TABLE `TBL_BorrowInfo`
(
`BookID` CHAR(15) NOT NULL,
`UserID` CHAR(11) NOT NULL,
`BorrowDate` DATETIME NOT NULL,
`ReturnDate` DATETIME COMMENT,
`Isreturned` TINYINT(1) COMMENT
);
CREATE TABLE `TBL_User`
(
`UserID` CHAR(11) NOT NULL,
`UserName` VARCHAR(40) NOT NULL,
`sex` TINYINT(1) NOT NULL,
`PASSWORD` VARCHAR(20) NOT NULL,
`Email` VARCHAR(40),
`Class` VARCHAR(40) NOT NULL,
`Photo` BLOB
);
-- 2.在图书信息表中，用insert语句插入两条主键值相同的记录，查看出错信息。
INSERT INTO tbl_bookinfo VALUES('TP312BA/52','75357-1050-6','计算机基础','郑邑,王建中编著','1996-06-02',NULL,826800,212,'湖南科学技术出版社','T');
INSERT INTO tbl_bookinfo VALUES('TP312BA/52','75357-1050-6','计算机基础','郑邑,王建中编著','1996-06-02',NULL,826800,212,'湖南科学技术出版社','T');-- --1062代码重复
-- 3.在图书信息表中，用insert语句插入一条主键值为空的记录，查看出错信息
4.3 参照完整性
-
CREATE DATABASE library43;
USE library43;
-- 创建图书类别表
CREATE TABLE tbl_bookclass(
classid CHAR(1) PRIMARY KEY,
classname VARCHAR(50)
);
-- 1.将借阅表（从表）中的BookID列设为外键，引用图书信息表（主表）中的BookID列。 先有主表 后有从表 （设置外键）
-- 2.将借阅表（从表）中的UserID列设为外键，引用读者信息表(主表)中的UserID列。 后有从表 （设置外键）
-- 6.给图书类别表的ClassID设置为主键，建立图书信息表（从表）与图书类别表（主表）的联系。 后有从表（设置外键）
-- 创建图书表
CREATE TABLE tbl_bookinfo(
bookid CHAR(15) NOT NULL PRIMARY KEY,
ISBN CHAR(20) NOT NULL,
BookName VARCHAR(50) NOT NULL,
Author VARCHAR(50) NOT NULL,
PublishDate DATE,
BookVersion VARCHAR(50),
WordCount INT,
PageCount SMALLINT,
Publisher VARCHAR(40),
ClassID CHAR(1) NOT NULL,
FOREIGN KEY(classid) REFERENCES tbl_bookclass(classid)
);
-- 创建读者信息表
CREATE TABLE tbl_user(
userid CHAR(11) NOT NULL PRIMARY KEY,
UserName VARCHAR(40) NOT NULL,
sex TINYINT(1) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL DEFAULT '123456',
Email VARCHAR(50) UNIQUE,
Class VARCHAR(40) NOT NULL,
Photo BLOB
);
-- 创建借阅表
CREATE TABLE tbl_borrowinfo(
borrowid INT PRIMARY KEY AUTO_INCREMENT,
bookid CHAR(15) NOT NULL,
userid CHAR(11) NOT NULL,
borrowdate DATETIME NOT NULL,
returndate DATETIME,
isreturned TINYINT(1) DEFAULT 0,
FOREIGN KEY(bookid) REFERENCES tbl_bookinfo(bookid),
FOREIGN KEY(userid) REFERENCES tbl_user(userid)
);
-- 3.在借阅表中，用insert语句插入一条BookID不存在的记录，查看出错信息。（错误号：？）
INSERT INTO tbl_borrowinfo VALUES (DEFAULT,'TP312BA/253','20220310183','2023-03-01',NULL,0); #1452
-- 4.在借阅表中，用insert语句插入一条UserID不存在的记录，查看出错信息。（错误号：？）
INSERT INTO tbl_borrowinfo VALUES (0,'TP391.9/17','20220622','2023-03-21',NULL,0); #1452
-- 5.在借阅表中，用insert语句插入一条正确的记录。
INSERT INTO tbl_borrowinfo VALUES(NULL,'TP391.9/17','20220310183','2023-03-01',NULL,0);
5.1 基础查询
-
-- SELECT 练习一：
-- 1.查询TBL_Bookinfo 表中所有图书信息。
SELECT * FROM TBL_Bookinfo ;
-- 2.查询TBL_User表中所有读者信息。
SELECT * FROM tbl_user ;
-- 3.查询TBL_Bookinfo表中所有图书的索取号,标准编号,书名。
SELECT bookid,isbn,bookname FROM tbl_bookinfo;
-- 4.查询TBL_User表中读者编号、姓名及班级。
SELECT userid,username,class FROM tbl_user;
-- 5.查询TBL_Bookinfo 表中所有图书的索取号,标准编号,书名，并用中文显示列名。
SELECT bookid 索取号,isbn 标准编号,bookname 书名 FROM tbl_bookinfo
5.2 条件查询
-
-- select练习二：
-- 1.在TBL_BookInfo中找出页数大于100页并且小于200页的书（分别用比较运算符和BETWEEN 两种方法实现）
-- 运算
SELECT * FROM tbl_bookinfo WHERE PageCount>100 AND PageCount<200;-- 20行
-- BETWEEN
SELECT * FROM tbl_bookinfo WHERE PageCount 100 AND 200;-- 20行
-- 2.在TBL_Bookinfo表中查询所有“清华大学出版社”出版的书的书名, 作者及出版日期。
SELECT Author, bookname ,publishdate FROM tbl_bookinfo
WHERE publisher='清华大学出版社';-- 7行
-- 3.从TBL_Bookinfo表中检索出不是电子工业出版社和清华大学出版社出版的图书。
SELECT * FROM tbl_bookinfo
WHERE publisher IN ('电子工业出版社','清华大学出版社');-- 17行
-- 4.在TBL_Bookinfo显示“中国古代教育文选”和“德国近代文学史”两本书的信息。
SELECT * FROM tbl_bookinfo WHERE bookname IN('中国古代教育文选','德国近代文学史');-- 2行
-- 5.在TBL_Bookinfo显示“清华大学出版社”在1980后出版图书的书名，作者，页数。
SELECT bookname ,Author,PageCount FROM tbl_bookinfo
WHERE publisher='清华大学出版社' AND publishdate>='1981-01-01';-- 7行
5.3 高级查询
-
-- select练习四：
-- 1.在TBL_User表中检索所有的班级，去除重复值。
SELECT DISTINCT class FROM tbl_user;-- 6行
-- 2.TBL_Bookinfo显示所有书的书名，出版社、出版日期，并按出版日期升序排列;
SELECT bookname,publisher,PublishDate FROM tbl_bookinfo ORDER BY PublishDate ASC;-- 109行
-- 3.从TBL_Bookinfo表中检索1990至2000年出版的图书信息，并按出版日期降序排列。
SELECT * FROM tbl_bookinfo
WHERE PublishDate BETWEEN'1990-01-01' AND '2000-12-31'
ORDER BY PublishDate ASC;
-- 4.按出版社的降序和出版日期的升序检索TBL_Bookinfo表的记录。
SELECT * FROM tbl_bookinfo ORDER BY publisher DESC,publishdate ASC;-- 109行
-- 5.查询最薄的5本图书。
SELECT * FROM tbl_bookinfo
ORDER BY PageCount LIMIT 0,5;-- 5行
-- 6.查询最新的10本书。
SELECT * FROM tbl_bookinfo
ORDER BY PublishDate DESC LIMIT 0,10;-- 10行
-- 7.查询借阅表中第11-20行记录。
SELECT * FROM tbl_bookinfo LIMIT 10,10;-- 10行
-- 8.在TBL_User表中检索用户名，去掉重复值，取最前面的5个用户名。
SELECT DISTINCT username FROM tbl_user LIMIT 5;
-- 9.从TBL_ User 表中检索出最前面的3个姓’黄’的用户记录的借书证号，姓名，性别。
SELECT userid,username,sex FROM tbl_user
WHERE username LIKE '黄%';
-- 10.显示每本书书名、作者、出版社、出版日期及至今的年限。
SELECT bookname,Author,publisher,PublishDate, YEAR(NOW())-YEAR(PublishDate) AS 书龄 FROM tbl_bookinfo; -- 109行
-- 11.从TBL_Bookinfo 表查询图书的总册数。
SELECT COUNT(*)总册数 FROM tbl_bookinfo;
5.4 分组查询
-
-- select练习五：
-- 1.查询各出版社图书的平均页数、最大页数、最小页数。
SELECT AVG(pagecount)FROM tbl_bookinfo
GROUP BY pagecount ;
SELECT MAX(pagecount)FROM tbl_bookinfo
GROUP BY pagecount;
SELECT MIN(pagrcount)FROM tbl_bookinfo
GROUP BY pagecount;
-- 2.从TBL_Bookinfo 表查询出各图书类别的图书总数在3册及以上的图书类别、图书总数。
SELECT publisher, COUNT(*) AS 图书册数
FROM tbl_bookinfo
GROUP BY classid HAVING COUNT(*)>=3;-- 5行
-- 3.从TBL_ User 表中统计出每班男女人数(列标题依次改为班级、性别和人数)。
SELECT class,sex, COUNT(*)人数
FROM tbl_user
GROUP BY class,sex;-- 12行
-- 4.从TBL_Bookinfo表查询出版社的图书册数最少的前三位的出版社(列标题依次改为出版社、册数)。
SELECT publisher, COUNT(*) AS 图书册数
FROM tbl_bookinfo
GROUP BY publisher
ORDER BY 图书册数 ASC
LIMIT 3;-- 3行
-- 5.查询4月、7月各出版社出版图书的总数。
SELECT publisher, COUNT(*) AS 图书册数
FROM tbl_bookinfo
WHERE MONTH(publishdate)=4 OR MONTH(publishdate)=7
GROUP BY publishdate;-- 15行
6.1 连接查询
-
-- 1.查询出图书的索取号、书名、出版社、类别名称。
SELECT a.`BookID`,a.`BookName`,a.`Publisher`,b.`ClassName`
FROM tbl_bookinfo AS a INNER JOIN tbl_bookclass b ON a.classid=b.`ClassId`;
-- 2.在TBL_Borrowinfo表中用SQL语句插入如下两条记录('G40-092.2/5', '20050402005', '2007-7-6',null,0')，('G633.7/202','20050402005', '2007-7-26',null,0)
INSERT INTO tbl_borrowinfo(BookID,UserID,BorrowDate,ReturnDate,IsReturned)
VALUES('G40-092.2/5', '20220310192', '2007-7-6',NULL,0),
('G633.7/202','20220310192', '2007-7-26',NULL,0);
-- 3. 查询出借阅索取号为“G40-092.2/5”图书的读者姓名、班级。
SELECT a.bookid,b.username,b.class
FROM tbl_borrowinfo AS a INNER JOIN tbl_user b ON a.`UserID`
WHERE bookid='G40-092.2/5';
-- 4.显示没有归还书的书名、读者姓名、读者的班级。
SELECT c.`BookName`,a.`UserName`,c.`Class`
FROM tbl_user a JOIN tbl_borrowinfo b ON c.`UserID`=b.`UserID` JOIN tbl_bookinfo c ON b.`BookID`=c.`BookID`
WHERE b.`IsReturned`=0;
--
SELECT BookName,UserName,`Class`
FROM tbl_user a JOIN tbl_borrowinfo b ON a.`UserID`=b.`UserID` JOIN tbl_bookinfo c ON b.`BookID`=c.`BookID`
WHERE b.`Sex`=0;
-- 5.查询出所有借过书的女生的信息。
SELECT b.*
FROM tbl_borrowinfo AS a INNER JOIN tbl_user b ON a.`UserID`=b.`UserID`
WHERE b.`Sex`=0;
-- 6.查询出所有2007年7月、8月被借出过的书的信息及借出时间。
SELECT b.*,a.`BorrowDate`
FROM tbl_borrowinfo a INNER JOIN tbl_bookinfo b
ON a.`BookID`=b.`BookID`
WHERE a.`BorrowDate` BETWEEN '2007-07-01' AND '2007-08-31';
--
SELECT b.*,a.`BorrowDate`
FROM tbl_borrowinfo a INNER JOIN tbl_bookinfo b
ON a.`BookID`=b.`BookID`
WHERE YEAR(a.`BorrowDate`)=2007 AND (MONTH(a.`BorrowDate`)=7 OR MONTH(a.`BorrowDate`)=8);
-- 7.查询出男、女生各借过多少本书。
SELECT b.sex,COUNT(*)
FROM tbl_borrowinfo AS a INNER JOIN tbl_user b ON a.`UserID`=b.`UserID`
GROUP BY sex;
-- 8.查各出版社在1985年以后出版的图书被借出的数量，并按借出数量的降序显示查询结果。
SELECT `Publisher`,COUNT(*)
FROM tbl_bookinfo a
INNER JOIN tbl_borrowinfo b ON b.`BookID`=a.`BookID`
WHERE YEAR(a.`PublishDate`)>1985
GROUP BY `Publisher`
ORDER BY COUNT(*) DESC;
-- 9.查询出2008年的所有借阅信息（包括：书名、借书时间、读者名）。
SELECT a.`BookName`,b.`BorrowDate`,c.`UserName`
FROM tbl_bookinfo a
INNER JOIN tbl_borrowinfo b ON a.`BookID`=b.`BookID`
INNER JOIN tbl_user c ON b.`UserID`=c.`UserID`
WHERE YEAR(b.`BorrowDate`)=2008;
-- 10.从图书信息表及图书类型表中检索书名、作者、出版社、类别编号、类别名称，所有的类别名称均显示。(分别使用右外连接和左外连接完成)
SELECT a.`BookName`,a.`Author`,a.`Publisher`,b.`ClassId`,b.`ClassName`
FROM tbl_bookinfo a
RIGHT JOIN tbl_bookclass b ON a.`ClassId`=b.`ClassId`;
6.2 子查询
-
-- select练习七：
-- 1.查询与《管理信息系统原理与实践》同一出版社的所有图书的信息。
-- 子查询
SELECT `Publisher` FROM `tbl_bookinfo` WHERE BookName='管理信息系统原理与实践'
-- 主查询
SELECT * FROM `tbl_bookinfo`
WHERE `Publisher` IN(SELECT `Publisher` FROM `tbl_bookinfo` WHERE BookName='管理信息系统原理与实践');-- 10行
-- 2.显示大于平均页数的图书的书名、作者、出版日期、页数.
-- 子查询
SELECT AVG(`PageCount`) FROM `tbl_bookinfo`
-- 主查询
SELECT `BookName`,`Author`,`PageCount`,`PublishDate`
FROM `tbl_bookinfo`
WHERE `PageCount`>(SELECT AVG(`PageCount`) FROM `tbl_bookinfo`);-- 44行
-- 3.查询出最早被借的书的信息。
-- 子查询
SELECT`BookID` FROM `tbl_borrowinfo` ORDER BY `Publisher` ASC
LIMIT 1;
-- 主查询
SELECT *FROM `tbl_bookinfo`
WHERE `BookID`=(SELECT `BookID` FROM `tbl_borrowinfo` ORDER BY`BorrowDate`ASC
LIMIT 1);
-- 4.查询出被借出过的最薄的书。
-- 子查询
SELECT `BookID` FROM `tbl_bookinfo`
ORDER BY`PageCount`ASC
LIMIT 1;
-- 主查询
SELECT * FROM `tbl_bookinfo`
WHERE `BookID` IN (SELECT DISTINCT `BookID` FROM `tbl_borrowinfo`)
ORDER BY `PageCount`ASC
LIMIT 1;
-- 5.查询出版书籍最多的出版社所出版的所有图书的信息。
-- 子查询
SELECT `Publisher` FROM `tbl_bookinfo`
GROUP BY `Publisher`
ORDER BY COUNT(`Publisher`)DESC
LIMIT 1;
-- 主查询
SELECT*FROM `tbl_bookinfo`
WHERE `Publisher`=(SELECT `Publisher` FROM `tbl_bookinfo`
GROUP BY `Publisher`
ORDER BY COUNT(`Publisher`)DESC
LIMIT 1);-- 10行
-- 6.查询同时借了“G40-092.2/5”与“X17/5”这两本书的读者信息。
-- 子查询
SELECT DISTINCT userid FROM `tbl_borrowinfo` WHERE bookid='G40-092.2/5'
SELECT DISTINCT userid FROM `tbl_borrowinfo` WHERE bookid='X17/5'
-- 主查询
SELECT*FROM `tbl_user`
WHERE `UserID`IN(SELECT DISTINCT `UserID` FROM `tbl_borrowinfo`
WHERE `BookID`='G40-092.2/5')
AND `UserID`IN(SELECT DISTINCT `UserID`FROM `tbl_borrowinfo`
WHERE`BookID`='X17/5');
6.4 将查询结果存入表
-
- 1.查询某个用户借阅时间超过3个月(含)未归还的了图书信息和读者信息。
#使用系统日期函数：TIMESTAMPDIFF(类型,开始时间,结束时间)
#参数类型：varchar,datetime,datetime
#返回值：int 两日期差的年差、月差、日差等
#示例：TIMESTAMPDIFF(Month,BorrowDate,curdate()) 返回：月差
SELECT xx.*,u.*
FROM `tbl_bookinfo` xx,`tbl_borrowinfo` j,`tbl_user` u
WHERE xx.`BookID`=j.`BookID` AND j.`UserID`=u.`UserID`
AND TIMESTAMPDIFF(MONTH,j.`BorrowDate`,CURDATE())>3;#27
-- 2.查询人气最旺图书的出版社(借书次数最多)。
#子查询
SELECT `BookID`
FROM `tbl_borrowinfo` j
GROUP BY `BookID`
ORDER BY COUNT(*) DESC
LIMIT 1
#主查询
SELECT `Publisher` FROM `tbl_bookinfo`
WHERE `BookID`=(
SELECT `BookID`
FROM `tbl_borrowinfo` j
GROUP BY `BookID`
ORDER BY COUNT(*) DESC
LIMIT 1
);
-- 3.查询人气最旺的出版社出版的图书。
#子查询
SELECT `Publisher` FROM `tbl_bookinfo`
WHERE `BookID`=(
SELECT `BookID`
FROM `tbl_borrowinfo` j
GROUP BY `BookID`
ORDER BY COUNT(*) DESC
LIMIT 1
);
#主查询
SELECT * FROM `tbl_bookinfo`
WHERE `Publisher`=(
SELECT `Publisher` FROM `tbl_bookinfo`
WHERE `BookID`=(
SELECT `BookID`
FROM `tbl_borrowinfo` j
GROUP BY `BookID`
ORDER BY COUNT(*) DESC
LIMIT 1
)
);#10
-- 4.查询 未归还图书超过3本(含) 的 用户信息。
#子查询
SELECT `UserID` FROM `tbl_borrowinfo`
WHERE `IsReturned`=0
GROUP BY `UserID`
HAVING COUNT(*)>=3
#主查询
SELECT * FROM `tbl_user`
WHERE `UserID` IN (
SELECT `UserID` FROM `tbl_borrowinfo`
WHERE `IsReturned`=0
GROUP BY `UserID`
HAVING COUNT(*)>=3
);
-- 5.查询 某个作者(姓刘)、分类为工业技术、 未被借阅的 图书信息。
SELECT xx.*
FROM `tbl_bookclass` l,`tbl_bookinfo` xx,`tbl_borrowinfo` j
WHERE l.`ClassId`=xx.`ClassId` AND j.`BookID`=xx.`BookID`
AND l.`Classname` LIKE '%工业技术%' AND `Author` LIKE '刘%' AND xx.bookid NOT IN(SELECT `BookID` FROM `tbl_borrowinfo`);
-- 6.各 出版社页数最少 的书名、作者、页数及出版社。
#子查询
SELECT `BookID` FROM `tbl_bookinfo`
GROUP BY `Publisher`
ORDER BY `PageCount` ASC
LIMIT 1;
#主查询
SELECT `BookName`,`Author`,`PageCount`,`Publisher` FROM `tbl_bookinfo`
WHERE `BookID`=(
SELECT `BookID` FROM `tbl_bookinfo`
GROUP BY `Publisher`
ORDER BY `PageCount` ASC
LIMIT 1
); #59行
-- 7.各 图书类别字数最多 的书名、作者、字数及图书类别编号及类型名称。
SELECT a.maxw,b.bookname,author,pageCount,b.`ClassId`,c.`ClassName`
FROM (SELECT MIN(wordcount) maxw FROM `tbl_bookinfo` GROUP BY `ClassId`) AS a
INNER JOIN `tbl_bookinfo` b ON a.`ClassId`=b.`ClassId`
INNER JOIN `tbl_bookclass` c ON b.`ClassId`=c.`ClassId` #12行
-- 8.显示所有借过书的用户名、性别、借书数量。
#子查询
SELECT `UserID`,COUNT(*) COUNT FROM `tbl_borrowinfo` GROUP BY `UserID`
#主查询
SELECT u.`UserName`,u.`Sex`,j.count
FROM (SELECT `UserID`,COUNT(*) AS COUNT FROM `tbl_borrowinfo` GROUP BY `UserID`) j,`tbl_user` AS u
WHERE j.`UserID`=u.`UserID`;
8.1 视图
-
-- 1.创建一个名为view_book1的视图 ，功能是从图书表中查询书名中包含有“工程数学”的所有记录的索取号，标准编号，书名，作者，出版社，出版日期，类别编号。
CREATE VIEW view_book1
AS
SELECT * FROM tbl_bookinfo
WHERE bookname LIKE '%工程数学%';
-- 2.创建一个名为view_book2的视图 ，功能是查询出图书的索取号，书名，出版社，类别名称。
CREATE VIEW view_book2
AS
SELECT a.bookid,a.bookname,a.publisher,b.classname
FROM tbl_bookinfo AS a
INNER JOIN tbl_bookclass AS b -- 显示连接
ON a.classid=b.classid; -- 连接连接
-- 3.创建一个借书情况视图view_borrow，用于从图书借阅表、图书信息表、读者信息表中查询所有读者的借书情况，
-- 其中包括的数据项有：读者姓名、性别、图书名称、出版社、出版日期、借书日期、还书日期。
CREATE VIEW view_borrow
AS
SELECT a.borrowdate,a.ReturnDate,b.bookname,b.publisher,b.publishdate,c.userid,c.sex
FROM `tbl_borrowinfo` AS a
INNER JOIN `tbl_bookinfo` AS b ON a.bookid=b.bookid
INNER JOIN `tbl_user` AS c ON a.userid=c.userid;
-- 4.修改视图view_book2，并按出版社排序。
ALTER VIEW `view_book2`
AS
SELECT a.bookid,a.bookname,a.publisher,b.classname
FROM tbl_bookinfo AS a
INNER JOIN tbl_bookclass AS b ON a.classid=b.classid
GROUP BY publisher;
-- 5.用SQL语句为view_book1视图插入一条数据：
-- ('TB11/6','7-5062-1333-8','高等工程数学方法','胡映电','电子工业出版社','1998-07-15','T')
INSERT INTO view_book1
VALUES('TB11/6','7-5062-1333-8','高等工程数学方法','胡映电','1998-07-15',NULL,NULL,NULL,'电子工业出版社','T');
8.3 绘制E-R图
-
8.5 关系模式的规范化
-
1、不满足一范式，未确保每列的原子性
Employees（员工号，姓名，性别，出生年月，聘任日期，工资，奖金，所在部门，部门主管）
2、满足二范式
每个非主属性完全依赖于主键，满足二范式
3、不满足三范式
｛员工号｝→聘任日期，工资
｛聘任日期，工资｝→奖金
奖金传递依赖于员工号
员工号→部门
部门→部门主管
部门主管传递依赖于员工号
消除传递依赖，分解
-
10.1 函数
-
- 1.创建函数maxnum,传入两个整型变量，返回它们的最大值。
-- -- 提示：使用IF函数或流程控制语句；
DELIMITER $$
CREATE FUNCTION maxnum (m INT,n INT)
RETURNS INT
RETURN(IF(m>n,m,n));
$$
DELIMITER ;
-- 2.测试该函数。
SELECT maxnum(100,50) AS MAX;
-- 3.查看该函数的创建过程。
SHOW CREATE FUNCTION maxnum;
-- 4.删除该函数。
DROP FUNCTION maxnum;
-- 5.定义(创建)函数getusername,传入userid,返回username。
DELIMITER $$
CREATE FUNCTION getusername(v_userid CHAR(15))
RETURNS VARCHAR(50)
BEGIN
RETURN(SELECT username FROM tbl_user WHERE userid=v_userid);
END
$$
DELIMITER ;
-- 6.测试该函数。
SELECT getusername('20020402004');
扫一扫
4168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
