mysql查询

SELECT*FROM reader-- 查询读者表

 

 

 

 

SELECT*FROM book-- 查询书表

 

 

 

 

-- 查询多个表

SELECT*FROM book,reader,borrow

 

 

 

 

-- 查询全部图书的图书号、作者、出版社和单价

SELECT book.bno,book.author,book.publish,book.price FROM book

 

 

 

 

-- 查询全体图书的信息，其中单价打8折，并设置该列的别名为“打折价”

SELECT *,price*0.8 as 打折价 FROM book

 

 

 

 

 

 

 

 

 

-- 显示所有借过书的借阅者的读者号、并去掉重复行

SELECT rno FROM reader WHERE sex>=1;

 

 

 

 

-- 查询所有单价在20-30元之间的信息

SELECT*FROM book WHERE price>=20 and price<=30;

 

 

 

-- 查询所有单价不在20-30之间的信息

SELECT*FROM book WHERE price<20 or price>30;

 

 

 

--  查询机械工业出版社、科学出版社、人民邮电出版社的图书信息

SELECT*FROM book WHERE publish='机械工业出版社'or publish='科学出版社'or publish='人民邮电出版社';

 

 

 

-- 查询既不是机械工业出版社也不是科学出版社出版的图书信息

SELECT*FROM book WHERE publish!='机械工业出版社'AND publish!='科学出版社'

 

 

 

--  查询姓名的第二个字符是‘建’并且只有两个字符的读者的读者号及姓名

SELECT*FROM reader WHERE  rname LIKE '_建';

 

 

 

-- 查找姓名以王、张、或李开头的所有读者的读者号及姓名

SELECT*FROM reader WHERE rname LIKE '王%'or rname LIKE'张%'or rname LIKE'李%';

 

 

 

-- 查询无归还日期的借阅信息

SELECT*FROM borrow WHERE rdate is NULL;

 

 

 

-- 查询有归还日期的借阅信息

SELECT*FROM borrow WHERE rdate is NOT NULL; publish='机械工业出版社'AND

 

 

 

-- 查询单价在50元以上、60元以下的机械工业出版社出版的图书名及单价

SELECT publish,price FROM book WHERE publish='机械工业出版社'AND  price>50 and price<60;

 

 

 

 

 

-- 查询机械工业出版社或科学出版社出版的图书名、出版社及单价

SELECT bname,publish,price FROM book WHERE publish='机械工业出版社'or publish='科学出版社';

 

 

 

-- 查询读者的总⼈数

SELECT count(*) FROM reader;

 

 

 

-- 查询借阅了图书的读者的总人数

SELECT count(*) FROM reader WHERE sex!=0;

SELECT count(*) FROM reader WHERE rno in(SELECT rno FROM borrow);

 

 

 

 

 

 

-- 查询机械工业出版社图书的平均价格、最⾼价、最低价

SELECT AVG(price)as 平均价格,MAX(price) 最高价格,min(price)as 最低价格 FROM book WHERE publish='机械工业出版社';

 

 

 

-- 查询借阅图书数超过2本的读者号、总本书，并按照借阅本数从大到小排序

SELECT rno, count(1) FROM borrow GROUP BY rno HAVING count(1)>2 ORDER BY count(1) desc;

 

 

 

-- 查询读者的基本信息及其借阅情况（借阅情况指历史借阅数量，在借数量

SELECT r.rno as 读者号,r.rname as 姓名, count(1) as 历史阅读量,count(1)-count(b.rdate) as 在借数量 FROM borrow as b inner join

reader as r on r.rno=b.rno GROUP BY b.rno;

 

 

 

-- 查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期，并按照读者号作升序排序

SELECT r.rno as 读者号, r.rname as 姓名, bk.bname,b.bdate,b.rdate FROM borrow as b

inner join reader as r on r.rno=b.rno inner join book as bk on bk.bno=b.bno ORDER BY b.rno;

 

 

 

-- 查询借阅了机械工业出版社，并且书名包含‘数据库’三个字的图书的读者，并显示读者号、姓名、书名、出版社、借出日期及归还日期；

SELECT reader.rno,reader.rname,book.bname,book.publish,borrow.bdate,borrow.rdate FROM borrow inner join reader on reader.rno=borrow.rno

inner join book on book.bno=borrow.bno WHERE book.publish='机械工业出版社' and  book.bname LIKE '%数据库%';

 

 

 

--  查询至少借阅过1本机械工业出版社的图书的读者的读者号、姓名、借阅本数，并按借阅本数多少排序

SELECT reader.rno,reader.rname, count(1) FROM borrow inner join reader on reader.rno=borrow.rno

inner join book on book.bno=borrow.bno WHERE book.publish='机械工业出版社' GROUP BY borrow.rno having count(1)>=1;

 

 

 

-- 查询与‘王小平’的办公电话相同的读者的姓名（王小平本人不再列出）

SELECT rname FROM reader WHERE rname!='王小平' and reader.tel='88320701';-- 知道电话号的查询

SELECT rname FROM reader WHERE rname!='王小平' and tel in(SELECT tel FROM reader WHERE rname='王小平')-- 不知道电话号的查询

 

 

 

-- 查询办公电话为‘88320701’的所有读者的借阅情况，要求包含借阅了图书的读者和没有借阅的读者，显示他们的读者号、姓名、书名及借阅日期

SELECT reader.rno,reader.rname,book.bname,borrow.bdate FROM borrow right join reader using(rno) left join book using(bno) WHERE reader.tel='88320701';

 

 

 

-- 查询所有单价小于平均单价的图书的书号、书名及出版社

SELECT bno,bname,publish FROM book WHERE price<(SELECT avg(price) FROM book)

 

 

 

-- 查询‘科学出版社’的图书的单价比‘机械工业出版社’最高单价还高的图书书名及单价

SELECT bname,price FROM book WHERE publish='科学出版社'and price>(SELECT max(price) FROM book WHERE publish='机械工业出版社')

 

 

 

-- 查询已经被借阅过并已经归还的图书信息

SELECT*FROM book WHERE bno in(SELECT bno FROM borrow WHERE bdate is not NULL and rdate is not NULL)

 

 

 

-- 查询从未被借阅过的图书信息

SELECT*FROM book WHERE bno not in (SELECT bno FROM borrow);

 

 

 

-- 查询正在被借阅的图书信息

SELECT*FROM book WHERE bno in(SELECT bno FROM borrow WHERE rdate is NULL)

 

 

 

-- 查询工商系借了书还没有还的读者号、姓名和书名

SELECT DISTINCT r.rno as 读者号, r.rname as 姓名,bk.bname as 书名 FROM borrow as b inner join reader as r on r.rno=b.rno inner join book as bk on bk.bno=b.bno WHERE b.rdate is null and department='工商';

 

 

 

-- 查询借阅图书总数最多的宿舍楼

SELECT reader.address FROM reader left join borrow on reader.rno=borrow.rno GROUP BY reader.address

ORDER BY count(borrow.bno) desc LIMIT 1 ;

 

 

 

 

总结在数据查询中出现的关键字及其意思：

去除重复记录：SELECT  DISTINCT 字段列表  FROM 表名；

起别名as，也可省略不写用空格隔开；

条件查询语法：

SELECT 字段列表 FROM 表名 WHERE  条件列表；

条件 ：符号： 功能：

           >         大于

           <         小于

           >=       大于等于

           <=        小于等于

           =          等于

          <>或!=  不等于

BETWEEN....AND....      在某个范围之内(都包含）；

           IN(....)      多选一

        LIKE 占位符      模糊查询 _单个任意字符 %多个任意字符；

        IS NULL      是NULL

        IS NOT NULL    不是NULL

        AND或&&        并且

        OR或ll               或者

        NOT或！           非，不是；

      

模糊查询，查询第一个字可以只写第一个字是多少

然后加上%，'马%',

查询第二个子是多少需要在前面加上一个_, 后面加%号  '_德%';

 查询第名字中包含某个字的，可以在前后两边都加上%号 '%让%';

示例：

 

-- 名字中包含爱的数据

SELECT * from test where name like '%爱%';

-- 名字以爱开头的数据

SELECT * from test where name like '爱%';

-- 名字等于爱的数据

SELECT * from test where name like '爱';

-- 名字第二个字符是爱的数据:比如我爱java

SELECT * from test where name like '_爱%';

ORDER BY：用于排序查询

列：SElECT 字段列表 FROM 表名 ORDER BY  排序字段名1 排序方式，排序字段名2 排序方式2；

两种排序方法：ASC：升序排列(默认值）

DESC：降序排列

 

聚合函数分类:

函数名：count(列名）功能：统计数量（一般选用不为null的列）；

max（列名）功能：最大值；

min（列名）功能：最小值；

sun（列名）功能：求和；

avg（列名）功能：平均值；

聚合函数语法：

SELECT 聚合函数名（列名）FROM 表；

注意点：null值不参与所有聚合函数的运算；

 

where 和 having 区别；

执行时机不一样：where是分组之前进行限定，不能满足where条件，则不参与分组，而having是分组之后对结果进行过滤；

可判断的条件不一样：where不能对聚合函数进行判断，having可以。

执行顺序：where>聚合函数>having;

 

 分页查询：

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数；

起始索引：从0开始

 

计算机公式：起始索引=（当前页码-1)*每页显示的条数；

--显示内连接：

SELECT 字段列表 FROM 表1[INNER] JOIN 表2 ON 条件；

列：select*from emp inner join dept on emp.dep_id=dept.did;

外连接：

 

左外连接：相当于查询A表所有数据和交集部分数据

select  字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件；

 

列：select*from emp left join dept on emp.dep_id=dept.did;

左外连接还可以 更换表的位置进行右查询：

列：select*from dept left join emp on emp.dep_id=dept.did;

右外连接：相当于查询B表所有数据和交集部分数据

select 字段列表 FORM 表1 RIGHT [OUTER] JOIN 表2 ON 条件；

 

列：select*from emp right join dept on emp.dep_id=dept.did;

 

Using：等价于join操作中的on，例如a和b根据id字段关联，那么以下等价

using(id)

on a.id=b.id

其目的是查询两个表中存在的相同的数据；

 

约束：

约束是在表中列上规则，用于限制加入表的数据

约束的存在保证了数据库中数据的正确性，有效性和完整性；

 

非空约束：保证列中的所有数据不能有null值：NOT NULL

唯一约束: 保证列中所有的数据各不相同：       UNIQUE

主键约束： 主键是一行数据的唯一标识要求非空且唯- PRIMARY KEY

检查约束： 保证列中的值满足某一条件    CHECK；

默认约束： 保存数据时，未指定值采用默认值 DEFAULT；

外键约束：  外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性  ；

FOREIGN KEY

 

auto increment：主键且自增长；

 

子查询：

1，子查询根据查询结构不同，作用不同：

单行单列：作为条件值，使用=!=><等进行条件判断；

 

SELECT  字段列表  FROM 表  WHERE 字段名=(子查询）

 

多行单列：作为条件值，使用in等关键字进行条件判断

 

SELECT  字段列表  FROM 表  WHERE 字段名 in (子查询）

 

多行多列：作为虚拟表：

SELECT  字段列表  FROM 表 (子查询）WHERE 条件；

 price>(SELECT max(price) FROM book WHERE publish='机械工业出版社')