Mysql中的查詢加強和多表查詢詳解
查詢加強
1)日期直接比較;
2)模糊查詢增強;
3)表結構查詢;
4)order by 多列條件排序;
測試代碼
SELECT * FROM emp; SELECT * FROM dept; SELECT * FROM slagrade; -- 日期比較 -- 查找1992.1.1后入職的工作 SELECT * FROM emp WHERE hiredate > '1992-01-01'; -- 比較時,日期格式要一致1-1與01-01 SELECT * FROM emp; -- 模糊查詢 -- %:表示0到多個字符 _:表示單個字符 -- 顯示首字符為S的員工姓名和工資 SELECT ename,sal FROM emp WHERE ename LIKE 'S%'; -- 顯示第三個字符為大寫O的所有員工的姓名和工資 SELECT ename,sal FROM emp WHERE ename LIKE '__O%'; -- 顯示沒有上級的雇員的情況 SELECT * FROM emp WHERE mgr IS NULL; -- 查詢表結構 selectinc.sql DESC emp; -- order by -- 按照工資從低到高的順序顯示雇員信息 SELECT * FROM emp ORDER BY sal ASC; -- 默認ASC 從低到高升序 -- 按照部門號升序雇員的工資降序排列顯示雇員信息 SELECT * FROM emp ORDER BY deptno ASC, sal DESC; -- desc從高到低降序
分頁查詢
# 基本語法 SELECT ... LIMIT start, rows #表示從start1行開始取,取出rows行,start 從0開始計算
測試代碼
SELECT * FROM emp; SELECT * FROM dept; SELECT * FROM slagrade; -- 日期比較 -- 查找1992.1.1后入職的工作 SELECT * FROM emp WHERE hiredate > '1992-01-01'; -- 比較時,日期格式要一致1-1與01-01 SELECT * FROM emp; -- 模糊查詢 -- %:表示0到多個字符 _:表示單個字符 -- 顯示首字符為S的員工姓名和工資 SELECT ename,sal FROM emp WHERE ename LIKE 'S%'; -- 顯示第三個字符為大寫O的所有員工的姓名和工資 SELECT ename,sal FROM emp WHERE ename LIKE '__O%'; -- 顯示沒有上級的雇員的情況 SELECT * FROM emp WHERE mgr IS NULL; -- 查詢表結構 selectinc.sql DESC emp; -- order by -- 按照工資從低到高的順序顯示雇員信息 SELECT * FROM emp ORDER BY sal ASC; -- 默認ASC 從低到高升序 -- 按照部門號升序雇員的工資降序排列顯示雇員信息 SELECT * FROM emp ORDER BY deptno ASC, sal DESC; -- desc從高到低降序 -- 按雇員的id號升序取出,每頁顯示三條記錄,分別顯示第1頁,第1頁,第3頁; -- 第一頁 SELECT * FROM emp ORDER BY empno LIMIT 0 , 3; -- 從第0+1頁開始取 -- 第二頁 SELECT * FROM emp ORDER BY empno LIMIT 3 ,3; -- 第三頁 SELECT * FROM emp ORDER BY empno LIMIT 6 ,3; -- 第四頁 SELECT * FROM emp ORDER BY empno LIMIT 9 ,3; -- 公式 -- select * from emp -- order by empno -- limit 每頁行數(shù)*(第幾頁-1),每頁行數(shù)
分組函數(shù)和分組子句加強
測試代碼
-- 顯示每種崗位的雇員總數(shù)、平均工資 SELECT job, FORMAT(AVG(sal),2) ,COUNT(*) FROM emp GROUP BY job; -- 顯示雇員的總數(shù)和有補助的雇員數(shù) SELECT COUNT(*) AS '雇員總數(shù)',COUNT(comm) AS '獲得補助的雇員數(shù)' FROM emp; -- count(n)如果n為null則不會統(tǒng)計數(shù)量 -- 統(tǒng)計沒有獲得補助的雇員數(shù) SELECT COUNT(IF(comm IS NULL,1,NULL)) AS '沒有獲得補助的雇員數(shù)' FROM emp; -- 顯示管理者的總人數(shù) SELECT COUNT(DISTINCT mgr) AS '管理者總人數(shù)' FROM emp; -- 顯示雇員工資的最大差額 SELECT (MAX(sal) - MIN(sal)) AS '工資最大差額' FROM emp; SELECT * FROM emp;
多子句查詢
多子句查詢語句順序
SELECT column1, column2, column3...from table_name group by column having conditon order by column limit start, rows;
測試代碼
-- 統(tǒng)計各個部門(group by)的平均工資(avg) -- 并且要求平均工資要大于1000(having),按照平均工資從高到低排序(order by) -- 取出前兩行記錄(limit) SELECT deptno, FORMAT (AVG(sal),2) AS '平均工資' FROM emp GROUP BY deptno HAVING AVG(sal) >1000 ORDER BY AVG(sal) DESC LIMIT 0 , 2;
多表查詢
基本介紹
多表查詢是指基于兩個和兩個以上的表查詢,在實際應用中查詢單個表可能不能滿足需求,需要使用到多個表進行數(shù)據(jù)查詢。
測試代碼
多表查詢的條件不能少于表的個數(shù)-1,否則會出現(xiàn)笛卡爾集!
#多表查詢 -- 顯示雇員名,雇員工資及所有在部門的名字【笛卡爾集】 -- 兩表一起查詢: SELECT * FROM emp, dept; -- 1)從第一張表中取出每一行與第二張表的每一行組合并返回結果; -- 2)一共返回的記錄數(shù)為第一張表的行數(shù)*第二張表的行數(shù) -- 3)多表查詢默認處理方式的結果成為笛卡爾積 -- 4)★進行過濾 SELECT ename, sal, dname FROM emp, dept WHERE emp.`deptno` = dept.`deptno`; -- 過濾條件 -- 顯示部門為10的部門名、員工名和工資 SELECT emp.`deptno`,dname,ename,sal FROM emp,dept WHERE emp.`deptno` = dept.`deptno` AND emp.`deptno` = 10; -- 顯示各個員工的姓名、工資、及工資級別 SELECT ename, sal, grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal; -- 顯示雇員名,雇員工資及所在部門的名字,并按部門排序(降序排序) SELECT ename,sal,dname,emp.deptno FROM emp,dept WHERE emp.`deptno` = dept.`deptno` ORDER BY emp.deptno DESC;
自連接
自連接是指在同一張表的連接查詢【將同一張表看做兩張表】
1)把同一張表當作兩張表使用;
2)需要給表取別名,表名 表的別名;
3)如果列明不明確可以指定列的別名。
# 多表查詢的自連接 -- 顯示公司員工姓名和上級名字 SELECT worker.`ename` AS '職工',boss.`ename` AS '領導' FROM emp worker,emp boss WHERE worker.`mgr` = boss.`empno`;
多行子查詢
子查詢是指嵌入在其他sql語句中的select語句,也叫嵌套查詢;單行子查詢,是指只返回一行數(shù)據(jù)的子查詢語句;多行子查詢,指返回多行數(shù)據(jù)的子查詢,使用關鍵字in;
# 子查詢 -- 顯示SMITH的部門編號 SELECT deptno FROM emp WHERE ename = 'SMITH'; -- 顯示與SMITH同一部門的所有員工 SELECT * FROM emp WHERE deptno= ( SELECT deptno FROM emp WHERE ename = 'SMITH'); -- 多行子查詢 -- 查詢和部門10的工作相同的雇員的 -- 名字、崗位、工資、部門號,但是不含10自己的雇員 -- 10號部門的工作崗位(當作子查詢結果使用) SELECT DISTINCT job FROM emp WHERE deptno = 10; -- 和部門10的工作相同的雇員的名字、崗位、工資、部門號 SELECT ename, job, sal,deptno FROM emp WHERE job IN ( SELECT DISTINCT job FROM emp WHERE deptno = 10) AND deptno != 10; -- 不含10自己的雇員
子查詢當作臨時表使用
# 查看ecs_goods表的有關信息 SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods; # 查看ecs_goods表中的商品價格最高的價格,并根據(jù)類別進行分組類別id(臨時表) SELECT cat_id, MAX(shop_price) FROM ecs_goods GROUP BY cat_id; SELECT goods_id, temp.cat_id, goods_name, shop_price FROM (SELECT cat_id, MAX(shop_price) AS max_price FROM ecs_goods GROUP BY cat_id) temp ,ecs_goods -- 臨時表別名temp WHERE temp.cat_id = ecs_goods.cat_id AND temp.max_price = ecs_goods.shop_price;
在多行子查詢中使用all、any操作符
-- 顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號 SELECT ename, sal,deptno FROM emp WHERE sal > ALL( -- 比所有高,比最高的高 SELECT sal FROM emp WHERE deptno = 30); -- 顯示工資比部門30 的其中一個員工的工資高的員工的姓名、工資和部門號 SELECT ename, sal,deptno FROM emp WHERE sal > ANY( -- 比其中一個高,比最低的高 SELECT sal FROM emp WHERE deptno = 30);
多列子查詢
多列子查詢是指查詢返回多個列數(shù)據(jù)的子查詢語句。
-- 多列子查詢 -- 查詢與SMITH的部門和崗位完全相同的所有雇員(并且不包含smith本人) -- 1.查詢smith的部門和崗位 SELECT deptno, job FROM emp WHERE ename = 'ALLEN'; -- 2.把1步驟當作子查詢并且使用多列子查詢的語法進行匹配 SELECT * FROM emp WHERE (deptno, job) = ( SELECT deptno, job FROM emp WHERE ename = 'ALLEN') AND ename != 'ALLEN'; -- 查詢與tom語文、數(shù)學、英語成績相同的學生 SELECT * FROM student WHERE (chinese, english, math) = ( SELECT chinese, english, math FROM student WHERE `name` = 'tom') AND `name` != 'tom';
練習代碼
-- 查詢每個部門(group by)工資高于(>)本部門平均工資(avg)的人的資料 -- 1.查詢部門平均值 SELECT deptno, FORMAT(AVG(sal)) FROM emp GROUP BY deptno; -- 2.查詢高于本部門的每個人 SELECT ename, sal, temp.avg_sal, emp.deptno FROM emp , ( SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) temp WHERE emp.`deptno` = temp.deptno AND emp.`sal` > temp.avg_sal; -- 每個部門最高工資的雇員信息 -- 查詢每個部門的最高工資 SELECT deptno, MAX(sal) FROM emp GROUP BY deptno; SELECT ename, sal, temp.max_sal, emp.deptno FROM emp, ( SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno) temp WHERE emp.`deptno` = temp.deptno AND emp.`sal` = temp.max_sal; -- 查詢每個部門(group by)的部門名(dept表)、編號(emp和dept)、地址(dept)和人員數(shù)量(emp) -- 1.每個部門的人員數(shù)量 SELECT deptno, COUNT(ename) AS people FROM emp GROUP BY deptno; -- 2.把1作為臨時表 SELECT dname, dept.deptno, dept.`loc`, people FROM dept, ( SELECT deptno, COUNT(ename) AS people FROM emp GROUP BY deptno) temp WHERE dept.`deptno` = temp.deptno; -- 第二種寫法, 表.* 表示把表中的所有信息都打出來 -- 在多表查詢中,當多個表的列不重復時,才可以直接寫出列明 SELECT temp.*, dname,loc FROM dept, ( SELECT deptno, COUNT(ename) AS people FROM emp GROUP BY deptno) temp WHERE dept.`deptno` = temp.deptno;
到此這篇關于Mysql中的查詢加強和多表查詢詳解的文章就介紹到這了,更多相關Mysql查詢加強和多表查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL刪除數(shù)據(jù)后自增主鍵ID不連貫問題及解決
這篇文章主要介紹了MySQL刪除數(shù)據(jù)后自增主鍵ID不連貫問題及解決,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-09-09MySQL中實現(xiàn)插入或更新操作(類似Oracle的merge語句)
這篇文章主要介紹了在MySQL中實現(xiàn)插入或更新操作(類似Oracle的merge語句)的相關資料,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2016-09-09linux mysql忘記密碼的多種解決或Access denied for user ''root''@''local
linux mysql忘記密碼的多種解決方法。2009-07-07SQL中count(1)、count(*)?與?count(列名)的區(qū)別詳細解釋
count(1)和count(*)是SQL中用于統(tǒng)計行數(shù)的兩種常見方式,它們的區(qū)別在于統(tǒng)計的對象不同,下面這篇文章主要給大家介紹了關于SQL中count(1)、count(*)?與?count(列名)區(qū)別的相關資料,需要的朋友可以參考下2024-08-08