目录
一.导入和导出sql 语句：
导入和导出的目的就是对数据备份。
导入：先建一个数据库，自已起个名字，然后点击图片上面的运行SQL 文件；；；；；
二.联表查询
1. 为什么需要设计多张表。
多表到底哪里好
保证表的单一性，减少冗杂，删改查操作更快捷
缺点：查询操作复杂；；；
表与表之间如何关联（关联一下就行了，然后多表查询）（关联就是个前提）
通过主键和外键关联：
主键和外键
主键：主键是表中一列或多列的组合，其值唯一标识表中的每一行记录。主键列中的每个值必须是唯一的，不能重复。主键列中的值不能为空（NULL）。个表中只能有一个主键，但主键可以由多列组成（复合主键）。
外键：（逻辑外键和物理外键）
2.逻辑外键：
多表之间通过 外键维护关系，但是不强制加外键约束，此时数据的准确性交给程序员通过逻辑进行维护。
3.物理外键：（重要）
多表之间通过外键进行关系维护，并且加外键的束，一旦添加外键约束，此时数据的准确性交给了数据库维护。
删除外键：
4.为什么需要联表查询?
你需要的结果在一张表中无法直接获取，需要在多张表中获取到。
这样查询，但是：
select * from 表名1, 表名2;
：出现笛卡儿积问题！！！！
啥叫 笛卡儿积问题：
是指在数学中，两个集合X和Y的笛卡尔积(Cartesian product)，又称真积，表示为XxY，第一个对象是X的成员而第二个对象,的所有可能有序对的其中一个成员
假设集合A=（a，b}，集合B={0，1，2}，则两个集合的笛卡尔积为{(a，0)，(a，1)，(a，2)，(b，0)，(b，1)。(b，2)}
select*from student,grade
这样的查询数据肯定不对！！！所以需要解决这个问题。
5. 内连接和外连接
用这种方式解决-联表查询： 内连接和外连接！！！（记住这两个才能后面一直复杂的嵌套）！
内连接：
隐士联表查询:
select * from tbl_student,tbl_class where tbl_student.cid=tbl_class.classid;
select * from tbl_student s,tbl_class c where s.cid=c.classid;
显示联表查询
-- on表示联表的条件
select * from tbl_student join tbl_class on tbl_student.cid=tbl_class.classid
select * from tbl_student s join tbl_class c on s.cid=c.classid
外连接：
-- 左连接
select * from tbl_student s left join tbl_class c on s.classid=c.classid-- 右连接
select * from tbl_student s right join tbl_class c on s.classid=c.classid
总结：
6.自连接查询：
（好像在嵌套里面也没用多少，也了解记住）
这个查询市场部门有哪些子部门：
7.子查询： 嵌套查询！！！！！！（重中之重）
- 子查询：在一个SELECT语句中嵌入另一个SELECT语句，被嵌入的SELECT语句称为子查询，外部的SELECT语句称为主查询。子查询可以返回一个值、一组值或（一个表），这些结果可以被外部查询用于计算、过滤或作为条件。
7.1通过一个作业：
books 表格：
readers 表格：
borrow_info 借 书表格
-- 1、检索读者“杨凡”所在单位
--
select dept from readers where name='杨凡';
-- 2、检索所有读者的全部信息
select * from readers;
-- 3、检索图书馆中所有藏书的书名和出版单位
select book_name,publisher from books;
-- 4、检索“人民大学出版社”所有的书名和单价，结果按照单价降序排列
select book_name,price from books where publisher='人民大学出版社' order by price desc;
-- 5、检索价格在10元至15元之间的图书的名称、作者、单价和分类号，结果按分类号和单价升序排列
select book_name,author,price,type_id from books where price BETWEEN 10 and 15 order by type_id,price;
-- 6、检索“人民大学出版社”和“清华大学出版社”的所有图书的名称和作者
select book_name,author from books where publisher='人民大学出版社' or publisher='清华大学出版社'
-- 7、检索书名以“数据库”开头的所有图书的书名和作者
select book_name,author from books where book_name like '数据库%';
-- 8、检索借了总编号为209116和209124两本图书的借书证号
-- 检索借了209124的读者id
select reader_id from borrow_info where book_id=209124 and reader_id in(
select reader_id from borrow_info where book_id=209116
)
select b1.reader_id from borrow_info b1 join borrow_info b2 on b1.reader_id=b2.reader_id
where b1.book_id=209116 and b2.book_id=209124
select reader_id from borrow_info where book_id in(209116,209124)
group by reader_id having count(DISTINCT book_id)=2
-- 9、检索所有借阅了图书的读者姓名和所在单位
-- 查询到借阅图书的读者id 联表
select name,dept from readers where reader_id in( select DISTINCT reader_id from borrow_info
)
-- 10、检索“扬凡”所借的所有图书的书名和借阅日期
select book_name,borrow_time from books b join borrow_info i
on b.book_id=i.book_id join readers r on r.reader_id=i.reader_id
where name='杨凡'
select b.book_name,bi.borrow_time from books b join borrow_info bi on b.book_id=bi.book_id
where bi.reader_id=(select reader_id from readers where name='杨凡')
;
-- 11、检索价格在20元以上且已经借出的图书，结果按单价降序排列
select distinct b.* from books b join borrow_info bi on b.book_id=bi.book_id where price>=20 order by price desc;select * from books where book_id in(select book_id from borrow_info) and price>=20 order by price desc;
-- 12、检索借阅了“C语言程序设计”一书的读者姓名和所在单位
-- 显示联表查询
select name,dept from books b join borrow_info i
on b.book_id=i.book_id join readers r on r.reader_id=i.reader_id
where book_name='C语言程序设计'
-- 隐士联表查询
SELECT name,dept FROM readers,books,borrow_info where readers.reader_id=borrow_info.reader_id and borrow_info.book_id=books.book_id and books.book_name = 'C语言程序设计';
-- 13、检索与“杨凡”在同一天借阅了图书的读者的姓名和所在单位
-- 查询杨凡的读者id
select reader_id from readers where name='杨凡'
-- 根据读者id查询杨凡的借书时间
select borrow_time from borrow_info where reader_id=(select reader_id from readers where name='杨凡')
-- 查询与杨凡同一时间的其他读者id
select reader_id from borrow_info where borrow_time in(
select borrow_time from borrow_info where reader_id=(select reader_id from readers where name='杨凡')
)
-- 其他读者id查询读者的姓名和所在单位
select name,dept from readers where reader_id in(
select reader_id from borrow_info where borrow_time in(
select borrow_time from borrow_info where reader_id=(select reader_id from readers where name='杨凡')
)
) and name!='杨凡'
-- 14、检索藏书中比“高等教育出版社”的所有图书的单价更高的图书
select * from books where price>(
select max(price) from books where publisher='高等教育出版社' )
-- 15、检索藏书中所有与“数据库导论”或“数据库原理”在同一出版社出版的图书
select * from books where publisher in(
select publisher from books where book_name='数据库导论' or book_name='数据库原理')
-- 16、求该图书馆藏书的总册数
select count(*) from books;
-- 17、求“高等教育出版社”的图书中最高的价格、最低的价格以及平均价格
select max(price),min(price),avg(price) from books where publisher='高等教育出版社'
-- 18、求“计算机学院”当前借阅了图书的读者人数
select count(distinct r.reader_id) from readers r join borrow_info b on r.reader_id=b.reader_id
where r.dept='计算机学院'
-- 19、求各个出版社的最高价格、最低价格、平均价格
select max(price),min(price),avg(price) from books group by publisher
-- 20、分别求出各个单位当前借阅图书的读者人数
select count(distinct r.reader_id) from readers r join borrow_info b on r.reader_id=b.reader_id group by dept
-- 21、求各个出版单位的册书、价格总额，并按总价降序排列，如有总价相同者按出版社名称降序排列
select count(book_id),sum(price) from books group by publisher order by sum(price)
desc,publisher desc;
-- 22、检索当前至少借阅了5本图书的读者姓名和所在单位
-- 统计每个读者借书的个数。
select reader_id from borrow_info group by reader_id
having count(distinct book_id)>=5
--
select name,dept from readers where reader_id in(
select reader_id from borrow_info group by reader_id
having count(distinct book_id)>=5
)
-- 23、分别找出借书人数超过10个人的单位和人数
select dept,count(distinct r.reader_id) from borrow_info b join readers r on b.reader_id=r.reader_id
group by dept having count(distinct r.reader_id)>10
-- 24、检索没有借阅任何图书的读者姓名和所在单位
select name,dept from readers where reader_id not in(select reader_id from borrow_info)
三、执行顺序（记住，比较重要）
四、 把查询的结果作为临时表
显示每个班级的人数和班级信息
select c.*,ifnull(rs,0) rs from tbl_class c left join (select classid,count(id) rs from tbl_student group by classid) t on c.classid=t.classid;
五、Mysql 内置函数
内置函数就是mysql自带的函数
1 字符串函数
-- 字符串拼接函数
select concat('aaa',classname) from tbl_class;
-- 把字符串转换为大写
select UPPER(classname) from tbl_class;
-- 左填充
select classname,LPAD(classname,2,'A') from tbl_class;
-- 求字符串的个数。CHAR_LENGTH(str)
select * from tbl_student where CHAR_LENGTH(name)=2
-- 查询所有姓李的员工。不能使用like. select * from tbl_student where LPAD(name,1,'A')='李' select * from tbl_student where SUBSTRING(name,1,1)='李' 查询所有姓名以三结尾的员工。不能使用like.. select * from tbl_student where SUBSTRING(name,CHAR_LENGTH(name),1)='坤'
2 数字函数
关于数字操作的一些函数
3.日期时间函数
select reader_id from borrow_info where year(borrow_time)=2008 and MONTH(borrow_time)=11 select * from borrow_info where DATEDIFF(CURDATE(),borrow_time)=1; select TIMESTAMPDIFF(year,borrow_time,CURDATE()) from borrow_info
4.其他
通过一个作业了解mysql函数:
-- 1. 找出早于12年前受雇的员工
SELECT * FROM emp WHERE TIMESTAMPDIFF(year,HIREDATE, CURDATE()) >12;
SELECT * FROM emp WHERE (NOW()-YEAR(HIREDATE)) >12;
-- 2. 以首字母大写的方式显示所有员工的姓名
SELECT CONCAT (UPPER(SUBSTRING(ENAME,1,1)),LOWER (SUBSTRING(ENAME,2, CHAR_LENGTH(ENAME)-1) ) ) FROM emp ;
SELECT UPPER(ENAME) as ENAME FROM emp ;
-- 3. 显示正好为5个字符的员工的姓名
SELECT ENAME FROM emp WHERE CHAR_LENGTH(ENAME)=5;
-- 4. 显示所有员工姓名的前三个字符
SELECT SUBSTRING(ENAME,1,3) ENAME FROM emp ;
-- 5. 找出各月倒数第3天受雇的所有员工
SELECT * FROM emp WHERE DAY(HIREDATE) = DAY(LAST_DAY(HIREDATE) - INTERVAL 2 DAY);
SELECT * FROM emp WHERE DAY(DATE_ADD(HIREDATE,INTERVAL 3 DAY))=1;-- SELECT DATE_ADD(HIREDATE,INTERVAL 3 DAY) FROM emp ;
-- 6. (任何年份的)2月受聘的所有员工
select * FROM emp WHERE month(HIREDATE)='2';
-- 7. 对于每个员工显示其加入公司的天数
SELECT * ,DATEDIFF(NOW(),HIREDATE) '天数'FROM emp;
-- 8. 以首字母大写的方式显示所有员工的姓名
SELECT CONCAT (UPPER(SUBSTRING(ENAME,1,1)),LOWER (SUBSTRING(ENAME,2, CHAR_LENGTH(ENAME)-1) ) ) FROM emp ;
9. 显示所有员工的姓名，用a替换所有“A”
SELECT REPLACE(ENAME, 'A', 'a') AS Name FROM emp;
扫一扫
1562
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
