mysql5

• 查詢關鍵字之having過濾
• 查看關鍵字之distinct去重查詢
• 查詢關鍵字之order by排序
• 查詢關鍵字之limit分頁
• 查詢關鍵字之regexp正則
• 多表查詢思路
• 可視化軟件Navicat
• 多表查詢練習題

查詢關鍵字之having過濾

having與where的功能是一模一樣的都是對數據進行篩選where用在分組之前的篩選    having用在分組之后的篩選為了更好的區分 所以將where說成篩選 having說成過濾# 統計每個部門年齡在30歲以上的員工的平均薪資并且保留平均薪資大于10000的部門select post from emp where age>30 group by post having avg(salary)>10000; '''針對聚合函數 如果還需要在其他地方作為條件使用 可以先起別名'''select post,AVG(salary) AS avg_salary FROM emp where age>30 GROUP BY post HAVING avg_salary > 10000;

查看關鍵字之distinct去重查詢

# 去重的前提 數據必須是一模一樣的才可以(如果數據有主鍵肯定無法去重)select distinct age from emp;"""等我們學到django orm之后 數據會被封裝成對象 那個時候主鍵很容易被我們忽略 從而導致去重沒有效果!!!"""

查詢關鍵字之order by排序

# 1.按照薪資高低排序select * from emp order by salary;  # 默認是升序(從小到大)# 2.先按照年齡升序排序 如果年齡相同 則再按照薪資降序排序select * from emp order by age, salary DESC;# 3.統計各部門年齡在10歲以上的員工平均工資 并且保留平均工資大于1000的部門并按照從大到小的順序排序select post,avg(salary) from emp where age>10 group by post HAVING avg(salary) >1000 ORDER BY AVG(salary) DESC;

查詢關鍵字之limit分頁

# 分頁即限制展示條數# 1.限制只展示五條數據select * from emp limit 5;# 2.分頁效果select * from emp limit 5,5;# 3.查詢工資最高的人的詳細信息select * from emp order by salary desc limit 1;"""當數據特別多的時候 經常使用limit來限制展示條數 節省資源 防止系統崩潰"""

查詢關鍵字之regexp正則

select * from emp where name regexp '^j.*(n|y)$';"""補充說明:我們目前所講的是MySQL查詢關鍵字中使用頻率較高的一些其實還有一些關鍵字目前無需講解 并且SQL語句里面同樣還支持流程控制語法如果感興趣的話 自行百度查看"""

多表查詢思路

# 多表查詢的思路總共就兩種1.子查詢  就相當于是我們日常生活中解決問題的方式(一步步解決)    將一條SQL語句的查詢結果加括號當做另外一條SQL語句的查詢條件    eg:以昨天的員工表和部門表為例 查詢jason所在的部門名稱      子查詢的步驟        1.先查jason所在的部門編號          2.根據部門編號去部門表中查找部門名稱            2.連表操作  先將多張表拼接到一起 形成一張大表 然后基于單表查詢獲取數據    eg:以昨天的員工表和部門表為例 查詢jason所在的部門名稱      連表操作        1.先將員工表和部門表按照某個字段拼接到一起          2.基于單表查詢          # 實際演練create table dep(id int primary key auto_increment,    name varchar(32)  );  create table emp(  id int primary key auto_increment,    name varchar(32),    gender enum('male','female','others') default 'male',    age int,    dep_id int  );  insert into dep values(200,'技術'),(201,'人力資源'),(202,'銷售'),(203,'運營'),(205,'安保');  insert into emp(name,age,dep_id) values('jason',18,200),('tony',28,201),('oscar',38,201),('jerry',29,202),('kevin',39,203),('jack',48,204);# 使用子查詢 獲取jason所在的部門名稱# 1.先獲取jason的部門編號select dep_id from emp where name='jason';# 2.將結果加括號作為查詢條件select name from dep where id=(select dep_id from emp where name='jason');  # 使用連表操作  獲取jason所在的部門名稱笛卡爾積(了解知識)select * from emp,dep;  # 會講所有的數據全部對應一遍  select * from emp,dep where emp.dep_id=dep.id;  # 效率低下"""1.一條SQL語句的查詢結果 我們也可以看成是一張虛擬表2.如果一條SQL語句中設計到多張表的字段名稱編寫 建議使用表名前綴做區分"""連表操作有四個關鍵字inner join內連接  select * from emp inner join dep on emp.dep_id=dep.id;    '''只連接兩張表中有對應關系的數據'''  left join左連接  select * from emp left join dep on emp.dep_id=dep.id;    '''以左表為基準 展示所有的數據 沒有對應項則用NULL填充'''  right join右連接  select * from emp right join dep on emp.dep_id=dep.id;    '''以右表為基準 展示所有的數據 沒有對應項則用NULL填充'''  union全連接  select * from emp left join dep on emp.dep_id=dep.idunionselect * from emp right join dep on emp.dep_id=dep.id;    '''左右兩表數據全部展示 沒有對應項則用NULL填充'''答案求解select dep.name from emp   inner join dep on emp.dep_id=dep.id  where emp.name='jason'  ;  """了解我們學會了連表操作之后 其實就可以將N多張表拼接到一起思路:我們可以將兩張表拼接之后的結果起別名當做一張表使用然后再去跟另外一張表拼接select * from emp inner join (select emp.id as epd,emp.name,dep.id from emp inner join dep on emp.dep_id=dep.id) as t1on emp.id=t1.epd;"""

可視化軟件Navicat

Navicat可以充當很多數據庫軟件的客戶端 提供了圖形化界面能夠讓我們更加快速的操作數據庫# 下載navicat有很多版本 并且默認都是收費使用  正版可以免費體驗14天   針對這種圖形化軟件 版本越新越好(不同版本圖標顏色不一樣 但是主題功能是一樣的)# 使用內部封裝了SQL語句 用戶只需要鼠標點點點就可以快速操作  連接數據庫  創建庫和表 錄入數據 操作數據  外鍵 SQL文件 逆向數據庫到模型 查詢(自己寫SQL語句)  # 使用navicat編寫SQL 如果自動補全語句 那么關鍵字都會變大寫  SQL語句注釋語法(快捷鍵與pycharm中的一致 ctrl+?)  #     --# 運行SQL文件

多表查詢練習題

1、查詢所有的課程的名稱以及對應的任課老師姓名4、查詢平均成績大于八十分的同學的姓名和平均成績7、查詢沒有報李平老師課的學生姓名8、查詢沒有同時選修物理課程和體育課程的學生姓名9、查詢掛科超過兩門(包括兩門)的學生姓名和班級-- 1、查詢所有的課程的名稱以及對應的任課老師姓名# 1.先明確需要幾張表 course表 teacher表# 2.大致查找一些表中的數據情況# 3.既然是多表查詢 那么查詢思路 子查詢 連表操作(復雜的SQL需要兩者配合使用)# 4.編寫完成后 使用美化功能 將SQL語句規范化-- select-- course.cname,-- teacher.tname -- FROM-- course-- INNER JOIN teacher ON course.teacher_id = teacher.tid;-- 4、查詢平均成績大于八十分的同學的姓名和平均成績# 1.先明確需要用到幾張表 student score# 2.大致查看一下兩張表里面的數據# 3.先獲取平均成績大于80分的學生信息(按照student_id分組)-- select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;# 4.結果需要從兩個表里面的獲取  student  SQL語句執行之后的虛擬表-- select-- student.sname,-- t1.avg_num -- FROM-- student-- INNER JOIN ( select student_id, avg( num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg_num > 80 ) AS t1 ON student.sid = t1.student_id;-- 7、查詢沒有報李平老師課的學生姓名# 此題有兩種思路 第一種是正向查詢 第二種是反向查詢(先查所有報了李平老師課程的學生id 之后取反即可)# 1.先明確需要用到幾張表  四張表# 2.先查詢李平老師的編號-- select tid from teacher where tname='李平老師'# 3.再查李平老師教授的課程編號-- select cid from course where teacher_id=(select tid from teacher where tname='李平老師')# 4.根據課程編號 去score表中篩選出所有選了課程的學生編號-- select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老師'));# 5.根據學生編號去學生表中反向篩選出沒有報李平老師課程的學生姓名-- select-- sname -- FROM-- student -- WHERE-- sid NOT IN ( select DISTINCT student_id FROM score WHERE course_id IN ( select cid FROM course WHERE teacher_id = ( select tid FROM teacher WHERE tname = '李平老師' ) ) )-- 8、查詢沒有同時選修物理課程和體育課程的學生姓名(兩門都選了和一門都沒選的 都不要 只要選了一門)# 1.先明確需要用到幾張表 三張# 2.先獲取物理課程和體育課程的編號-- select cid from course where cname in ('物理','體育');# 3.再去分數表中篩選出選了物理和體育的數據(包含了選了一門和兩門 沒有選的就已經被排除了)-- select * from score where course_id in (select cid from course where cname in ('物理','體育'))# 4.如何剔除選了兩門的數據(按照學生id分組 然后對課程計數即可)-- select student_id from score where course_id in (select cid from course where cname in ('物理','體育'))-- group by student_id HAVING count(course_id) = 1;# 5.根據上述學生id號篩選出學生姓名-- select-- sname -- FROM-- student -- WHERE-- sid IN (-- select-- student_id -- FROM-- score -- WHERE-- course_id IN ( select cid FROM course WHERE cname IN ( '物理', '體育' ) ) -- GROUP BY-- student_id -- HAVING-- count( course_id ) = 1 -- )-- 9、查詢掛科超過兩門(包括兩門)的學生姓名和班級# 1.先明確需要幾張表  三張表# 2.先去score表中篩選出所有不及格的數據-- select * from score where num < 60;# 3.如何篩選每個學生掛科的門數(按照學生id分組 對學科計數即可)-- select student_id from score where num < 60 group by student_id -- HAVING count(course_id) >= 2;# 4.由于最終的結果需要取自兩張表 所以應該拼接-- select student.sname,class.caption from class inner join student on class.cid=student.class_id;# 5.使用步驟3獲取到的學生編號 對步驟4的表結果篩選數據SELECTstudent.sname,class.caption FROMclassINNER JOIN student ON class.cid = student.class_id WHEREstudent.sid IN ( select student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );    """重點掌握上述五道題目即可 如果還想擴展 可以考慮下面的題目https://www.cnblogs.com/Dominic-Ji/p/10875493.html只需要完成三分之一及以上即可!!!"""