10习题讲义
重要习题讲义
要什么
要就是书 SELECT * FROM
要的是书名和作者 SELECT bname,bauthor FROM
要的多少个 最大 最贵 SELECT 聚合函数 FROM
是否重复 多少个 个数>1
SELECT bname,COUNT(id) AS zs FROM books GROUP BY bname
SELECT bname,COUNT(id) zs FROM books GROUP BY bname HAVING zs>1
最贵的书是谁? 用性能去换功能
SELECT * FROM books WHERE bprice = (SELECT MAX(bprice) FROM books)
1.已知：员工信息表，表名为：employee
NAME Sex Age Address
张三 女 19 北京
李四 男 20 上海
王五 女 25 广州
薛六 女 20 北京
王五 男 22 北京
赵七 男 28 上海
张四 女 23 北京
（1）. 写出sql语句，查询所有年龄大于20岁的员工（2分）
SELECT * FROM employee WHERE age > 20
（2）. 写出sql语句，查询所有年龄小于25岁的女性员工（3分）
SELECT * FROM employee WHERE age < 25 AND sex = '女'
（3）. 写出sql语句，统计男女员工各有多少名（3分）
SELECT sex,COUNT(NAME) FROM employee GROUP BY sex
（4）. 写出sql语句，按照年龄倒序获取员工信息（3分）
SELECT * FROM employee ORDER BY age DESC
（5）. 写出sql语句，获取员工中哪个姓名具有重名现象（3分）
SELECT NAME,COUNT(NAME) FROM employee GROUP BY NAME HAVING COUNT(NAME)>1
（6）. 写出sql语句，查询所有姓张的员工（3分）
SELECT * FROM employee WHERE NAME LIKE '张%'
（7）. 写出sql语句，查询住址为北京的前3条记录（3分）
SELECT * FROM employee WHERE address = '北京' LIMIT 0,3
（8）. 写出sql语句，查询员工总数（3分）
SELECT COUNT(NAME) FROM employee
（9）. 写出sql语句，向表中插入一条记录（2分）
INSERT INTO employee (NAME,age,sex,address) VALUES ('',20,'','')
（10）.写出sql语句，修改员工张四的住址为南京（2分）
UPDATE employee SET address = '南京' WHERE NAME = '张四'
（11）.写出sql语句，删除年龄大于24岁的女员工（2分）
DELETE FROM employee WHERE age > 24 AND sex = '女'
SELECT * FROM (SELECT bname,COUNT(id) AS zs FROM books GROUP BY bname) AS aa WHERE zs > 1
CREATE TABLE book (b_code VARCHAR(10),b_name VARCHAR(100),b_author VARCHAR(50),b_publish VARCHAR(100),b_pubdate DATE,b_price DOUBLE(8,2));
CREATE TABLE reader (r_code VARCHAR(10),r_name VARCHAR(100),r_sex CHAR(1),r_dep VARCHAR(100));
CREATE TABLE check_out(b_code VARCHAR(10),r_code VARCHAR(10),borrow_date DATE,return_date DATE);
book 多
b_code bname
1 幻城
2 小时代
3 大话西游
reader 多 加外键
r_code rname
1 张三
2 李四
3 王五
check_out
b_code r_code
1 1
1 2
2 1
2 2
book.b_code = check_out.b_code AND check_out.r_code= reader.r_code
1.特点 三张表 实际上是 书和读者的多对多关系
2.特点 别没有进行联合查询
3.虽然三张表 但是始终都是单表查询
-- 插入图书信息
INSERT INTO book VALUES('b_001','《幻城》','郭敬明','人民教育出版社','2004-5-21',19.8);
INSERT INTO book VALUES('b_002','《三重门》','韩寒','电子工业出版社','2001-7-22',22.5);
INSERT INTO book VALUES('b_003','《幻城》','郭敬明','中国人民大学出版社','2004-5-21',20);
INSERT INTO book VALUES('b_004','《围城》','钱钟书','人民教育出版社','2010-5-15',21);
INSERT INTO book VALUES('b_005','《幻城》','郭敬明','清华大学出版社','2011-5-21',22);
INSERT INTO book VALUES('b_006','《水浒传》','施耐庵','电子工业出版社','2008-3-21',48);
INSERT INTO book VALUES('b_007','《福尔摩斯》','柯南道尔','人民教育出版社','2004-5-21',33);
INSERT INTO book VALUES('b_008','《伟大的博弈》','张化龙','电子工业出版社','2009-2-13',28);
INSERT INTO book VALUES('b_009','《活法》','稻盛和夫','人民教育出版社','2000-1-2',15);
INSERT INTO book VALUES('b_010','《人性的弱点》','戴尔卡耐基','人民教育出版社','2007-7-31',19.8);
-- 插入读者数据
INSERT INTO reader VALUES('r_001','丁涛','男','人事部');
INSERT INTO reader VALUES('r_002','王壮','男','人事部');
INSERT INTO reader VALUES('r_003','王影影','女','财务部');
INSERT INTO reader VALUES('r_004','吴俊铖','男','培训部');
INSERT INTO reader VALUES('r_005','张佳','女','财务部');
INSERT INTO reader VALUES('r_006','刁钱龙','男','计算机系');
INSERT INTO reader VALUES('r_007','李佳成','男','财务部');
-- 插入借阅记录信息
INSERT INTO check_out VALUES('b_003','r_001','2008-4-9','2008-4-22');
INSERT INTO check_out VALUES('b_003','r_006','2009-4-21','2009-5-10');
INSERT INTO check_out VALUES('b_004','r_005','2009-10-2','2009-11-1');
INSERT INTO check_out VALUES('b_001','r_003','2010-3-12','2010-4-1');
INSERT INTO check_out VALUES('b_009','r_002','2010-3-22','2010-4-5');
INSERT INTO check_out VALUES('b_008','r_001','2010-8-11','2010-9-3');
INSERT INTO check_out VALUES('b_009','r_007','2010-12-3','2010-12-31');
INSERT INTO check_out VALUES('b_003','r_004','2011-4-8','2011-5-7');
INSERT INTO check_out VALUES('b_001','r_003','2012-4-9',NULL);
查询出以下结果
1.查询所有图书记录、查询所有借阅记录、查询所有读者信息，采用字段选择和通配符选择！
SELECT * FROM book
SELECT * FROM reader
SELECT * FROM check_out
2.查询图书馆中的书籍都有哪些出版社？
SELECT DISTINCT b_publish FROM book
3.查询出版日期在2008年至2010年之间的所有图书！
SELECT * FROM book WHERE b_pubdate BETWEEN '2008' AND '2010'
4.查询部门是财务部、人事部、培训部的读者信息！
SELECT * FROM reader WHERE r_dep IN('财务部','人事部','培训部')
5.查询所有姓张的读者详细信息!
SELECT * FROM reader WHERE r_name LIKE '张%'
6.查询所有未归还图书的借阅记录详情！
SELECT * FROM check_out WHERE return_date IS NULL
7.查询读者中人事部的读者有多少个、查询作者姓李的图书共有多少本！
SELECT r_dep,COUNT(r_code) FROM reader WHERE r_dep = '人事部' GROUP BY r_dep
SELECT b_author,COUNT(b_code) FROM book WHERE b_author LIKE '李%' GROUP BY b_author
8.查询《幻城》这本书总计价格是多少？（建议 数据录入的时候有不少于三本《幻城》）
SELECT SUM(b_price) FROM book WHERE b_name = '《幻城》'
9.查询《幻城》这本书平均价格是多少？
SELECT AVG(b_price) FROM book WHERE b_name = '《幻城》'
10.查找最贵的一本《幻城》的信息 ！
SELECT MAX(b_price) FROM book WHERE b_name = '《幻城》'
11.查询借阅日期在2011年10月的前3条记录
SELECT * FROM check_out WHERE borrow_date LIKE '%2011-10%'
12.查询所有图书并按出版日期排序、查询2011年的全年借阅记录，并按借阅日期倒序排序
SELECT * FROM book ORDER BY b_pubdate DESC
13.统计读者群体中各个部门的人数
SELECT r_dep,COUNT(r_code) FROM reader GROUP BY r_dep
14.统计2010年的借阅记录，查看每个图书编号都被借出去过多少次，并按照这个次数从大到小排序，以便了解书籍的畅销程度!
做热榜
SELECT b_code,COUNT(b_code) FROM check_out WHERE borrow_date
LIKE '%2010%' GROUP BY b_code ORDER BY COUNT(b_code) DESC
15.统计读者群体中各个部门的女性群体数量
SELECT r_sex,r_dep,COUNT(r_code) FROM reader GROUP BY r_sex,r_dep
16.查询那本书被哪个读者在什么时间借过在什么时间还的
SELECT * FROM book,reader,check_out
WHERE book.b_code = check_out.b_code AND check_out.r_code= reader.r_code
SELECT b.b_name,r.r_name,co.borrow_date,co.return_date
FROM book b,reader r,check_out co
WHERE b.b_code = co.b_code AND co.r_code= r.r_code AND co.return_date IS NULL
前 后 分组 排序 分页==范围
查询难度分为四种的
1.单表查询 条件单一
2.单表查询 条件复杂多
3.多表联合查询 条件单一
4.多表联合查询 条件复杂多
回顾一对多
联合查询 一对多 和 多对多
1.普通连接 WHERE 一方的主键=多方的外键
SELECT * FROM class c,student s WHERE c.id = s.c_id
2.内连接 INNER JOIN ON 一方的主键=多方的外键
SELECT * FROM class c INNER JOIN student s ON c.id = s.c_id
3.自连接 表自己连接自己 A表自己是一方 同时 也是多方 后面再讲
4.外连接 OUTER JOIN ON 一方的主键=多方的外键
左外 LEFT JOIN ON
表分成 左表和右表 单纯的左右
效果 查询出左表的全部数据 和右表关系列相等的数据
SELECT * FROM class c LEFT JOIN student s ON c.id = s.c_id
右外 RIGHT JOIN ON
表分成 左表和右表 单纯的左右
效果 查询出右表的全部数据 和左表关系列相等的数据
SELECT * FROM class c RIGHT JOIN student s ON c.id = s.c_id
数据中 有 没有班级的学生 有 没有学生的班级
多对多
如何建立关系
1.给两个多方表 建立一个共同的关系表 关系表中是两个多方表的外键
qqnum
id num
1 100828833
2 100910101
3 101000011
qqrole
id rname
1 蓝钻
2 绿钻
3 黄钻
关系表
num_role
num_id role_id
1 1
1 2
2 1
2 2
CREATE TABLE qqnum(id INT(2) PRIMARY KEY AUTO_INCREMENT,num VARCHAR(10));
CREATE TABLE qqrole(id INT(2) PRIMARY KEY AUTO_INCREMENT,rname VARCHAR(10));
CREATE TABLE num_role(n_id INT(2),r_id INT(2));
关系列 n.id = nr.n_id AND nr.r_id = r.id
SELECT * FROM qqnum n,qqrole r,num_role nr
WHERE n.id = nr.n_id AND nr.r_id = r.id
知道 1008的会员身份有哪些？
SELECT * FROM qqnum n,qqrole r,num_role nr
WHERE n.id = nr.n_id AND nr.r_id = r.id AND n.num = '1008'
添加两个外键约束
ALTER TABLE num_role
ADD CONSTRAINT a1 FOREIGN KEY (n_id) REFERENCES qqnum(id);
ALTER TABLE num_role
ADD CONSTRAINT a2 FOREIGN KEY (r_id) REFERENCES qqrole(id);
浙公网安备 33010602011771号