您好,登錄后才能下訂單哦!
今天小編給大家分享一下MySQL中有哪些數(shù)據(jù)查詢語句的相關(guān)知識(shí)點(diǎn),內(nèi)容詳細(xì),邏輯清晰,相信大部分人都還太了解這方面的知識(shí),所以分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后有所收獲,下面我們一起來了解一下吧。
1、“select * from 表名;”,—可查詢表中全部數(shù)據(jù);
2、“select 字段名 from 表名;”,—可查詢表中指定字段的數(shù)據(jù);
3、“select distinct 字段名 from 表名;”,—可對(duì)表中數(shù)據(jù)進(jìn)行去重查詢。
4、“select 字段名 from 表名 where 查詢條件;”,—可根據(jù)條件查詢表中指定字段的數(shù)據(jù);
1)比較運(yùn)算符:>, <, >=, <=, =, !=, <>
查詢大于18歲的信息
select * from students where age>18; select id, name,gender from students where age>18;查詢小于18歲的信息
select * from students where age<18;查詢年齡為18歲的所有學(xué)生的名字
select * from students where age=18;
2)邏輯運(yùn)算符:and, or, not
–18到28之間的學(xué)生信息
select * from students where age>18_and age<28:–18歲以上的女性
select * from students where age>18 and gender="女"; select * from students where age>18 and gender=2;–18以上或者身高查過180(包含)以上
select * from students where age>18 or height>=180;不在18歲以上的女性這個(gè)范圍內(nèi)的信息
select * from students where not (age>18 and gender=2);年齡不是小于或者等于18并且是女性
select * from students where (not age<=18) and gender=2;
3)模糊查詢:like, rlike
% 替換1個(gè)或者多個(gè)
_ 替換1個(gè)
查詢姓名中 以“小”開始的名字select name from students where name="小"; select name from students where name like"小%";查詢姓名中有“小”所有的名字
select name from students whece name like "%小%";查詢有2個(gè)字的名字
select name from students where name like "__";查詢有3個(gè)字的名字
select name from students where name like "__";查詢至少有2個(gè)字的名字 select name from
students where name like "__%";rlike正則
查詢以周開始的姓名select name from students where name rlike "^周.*";查詢以周開始、倫結(jié)尾的姓名
select name from students where name rlike "^周.*倫$";
4)范圍查詢:in,not in,between…and,not between…and
查詢年齡為18、34的姓名
select name, age from students where age=18 or age=34; select name,age from students where age in (18,34);not in不非連續(xù)的范圍之內(nèi)
年齡不是 18、34歲之間的信息select name,age from students where age not in (18,34);between … and …表示在一個(gè)連續(xù)的范圍內(nèi)
查詢年齡在18到34之間的的信息select name,age from students where age between 18 and 34;not between … and …表示不在一個(gè)連續(xù)的范圍內(nèi)
查詢年齡不在在18到34之間的的信息select * from students where age not between 18 and 34;
空判斷
判空 is null
查詢身高為空的信息select *from students where height is null/NULL/Null;判非空is not null
select * from students where height is not null;
排序:order_by
–查詢年齡在18到34歲之間的男性,按照年齡從小到大排序
select * from students where (age between 18 and 34) and gender=1; select * from students where (age between 18 and 34) and gender=1 order by age; select * from students where (age between 18 and 34) and gender=1 order by age asc;查詢年齡在18到34歲之間的女性,身高從高到矮排序
select * from students where (age between18 and 34) and gender=2 order by height desc;order by多個(gè)字段
查詢年齡在18到34歲之間的女性,身高從高到矮排序,如果身高相同的情況下按照年齡從小到大排序select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc;查詢年齡在18到34歲之間的女性,身高從高到矮排序,如果身高相同的情況下按照年齡從小到大排序,如果年齡也相同那么按照id從大到小排序
select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc, id desc;按照年齡從小到大、身高從高到矮的排序
select * from students order by age asc,height desc;
分組:group_by, group_concat():查詢內(nèi)容, having
where :是對(duì)整個(gè)數(shù)據(jù)表信息的判斷;
having:是對(duì)于分組后的數(shù)據(jù)進(jìn)行判斷
–group by
按照性別分組,查詢所有的性別select gender from students group by gender;–計(jì)算每種性別中的人數(shù)
select gender, count(*) from students group by gender;where是在group by 前面
–計(jì)算男性的人數(shù)select count(*) from students where gender='男';–group_concat(…)
查詢同種性別中的姓名select gender,group_concat(name) from students group by gender;having :having是在group by后面
查詢平均年齡超過30歲的性別,以及姓名select gender ,avg(age) from students group by gender having avg(age) > 30;查詢每種性別中的人數(shù)多于2個(gè)的信息
select gender,count(*) from students group by gender having count(*) > 2;– 查詢每組性別的平均年齡
select gender,avg(age) from students group by gender;
分頁: limit
limit start,count (start:表示從哪─個(gè)開始;count:表示數(shù)量) 即limit(第N頁-1)*每個(gè)的個(gè)數(shù),每頁的個(gè)數(shù); limit在使用的時(shí)候,要放在最后面.
限制查詢出來的數(shù)據(jù)個(gè)數(shù)
select *from students where gender=1 limit 2;查詢前5個(gè)數(shù)據(jù)
select* from students limit 0,5;查詢id6-10(包含)的書序
select * from students limit 5,5;每頁顯示2個(gè),第1個(gè)頁面
select * from students limit 0,2;每頁顯示2個(gè),第2個(gè)頁面
select * from students limit 2,2;每頁顯示2個(gè),第3個(gè)頁面
select * from students limit 4,2;每頁顯示2個(gè),第4個(gè)頁面
select * from students limit 6,2;每頁顯示2個(gè),顯示第6頁的信息,按照年齡從小到大排序
select * from students order by age asc limit 10,2;– 如果重新排序了,那么會(huì)顯示第一頁
select * from students where gender=2 order by height des limit 0,2;
5)聚合函數(shù):count(), max(), min(), sum(), avg(), round()
聚合函數(shù)
-總數(shù)-- count
-查詢男性有多少人,女性有多少人select count(*) from students where gender=1; select count(*) as 男性人數(shù) from students where gender=1; select count(*) as 女性人數(shù) from students where gender=2;-最大值-最小值
– max --min
一查詢最大的年齡select max (age) from students;–查詢女性的最高身高
select max (height) from students where gender=2;-求和
–sum
-計(jì)算所有人的年齡總和select sum ( age) from students;–平均值
– avg
–計(jì)算平均年齡select avg(age) from students;–計(jì)算平均年齡
select sum ( age) / count(* ) from students;–四舍五入round ( 123.23 ,_1)保留1位小數(shù)
–計(jì)算所有人的平均年齡,保留2位小數(shù)select round (sum(age)/count(*),2) from students; select round ( sum(age)/count(*),3) from students ;–計(jì)算男性的平均身高保留2位小數(shù)
select round(avg (height),2) from students where gender=1; select name,round(avg(height),2) from students where gender=1;
6)連接查詢 :inner join, left join, right join
inner join
select … from 表 A inner join表B;select * from students inner join classes;查詢有能夠?qū)?yīng)班級(jí)的學(xué)生以及班級(jí)信息
select * from students inner join classes on students.cls_id=classes.id;按照要求顯示姓名、班級(jí)
select students.*, classes.name from students inner join classes on students.cls_id=classes.id; select students.name,classes.name from students inner join classes on students.cls_id=classes.id;給數(shù)據(jù)表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;查詢有能夠?qū)?yīng)班級(jí)的學(xué)生以及班級(jí)信息,顯示學(xué)生的所有信息,只顯示班級(jí)名稱
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;在以上的查詢中,將班級(jí)姓名顯示在第1列
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;查詢有能夠?qū)?yīng)班級(jí)的學(xué)生以及班級(jí)信息,按照班級(jí)進(jìn)行排序
select c.xxx s.xxx from student as s inner join clssses as c on … order by …;select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;當(dāng)時(shí)同一個(gè)班級(jí)的時(shí)候,按照學(xué)生的id進(jìn)行從小到大排序
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;left join
查詢每位學(xué)生對(duì)應(yīng)的班級(jí)信息select * from students as s left join classes as c on s.cls_id=c.id;查詢沒有對(duì)應(yīng)班級(jí)信息的學(xué)生
– select … from xxx as s left join xxx as c on… where …
– select … from xxx as s left join xxx as c on… . … having …select * from students as s left join classes as c on s.cls_id=c.id having c.id is null; select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;
left join是按照左邊的表為基準(zhǔn)和右邊的表進(jìn)行查詢,查到就顯示,查不到就顯示為null
補(bǔ)充
查詢所有字段:select * from 表名;
查詢指定字段:select 列1,列2,... from 表名;
使用 as 給字段起別名: select 字段 as 名字.... from 表名;
查詢某個(gè)表的某個(gè)字段:select 表名.字段 .... from表名;
可以通過 as 給表起別名: select 別名.字段 .... from 表名 as 別名;
消除重復(fù)行: distinct 字段
注意:WHERE子句中是不能用聚集函數(shù)作為條件表達(dá)式的!
二、總結(jié)
(1)命令:select * from <表名>;
(2)命令:select <要查詢的字段> from <表名>;
命令:select distinct <要查詢的字段> from <表名>
升序:asc
降序:desc
降序排列命令:select <要查詢的字段名> from <表名> order by <要查詢的字段名> desc
不加desc一般默認(rèn)為升序排列
命令:select <按什么分的組>, Sum(score) from <表名> group by <按什么分的組>
假設(shè)現(xiàn)在又有一個(gè)學(xué)生成績表(result)。要求查詢一個(gè)學(xué)生的總成績。我們根據(jù)學(xué)號(hào)將他們分為了不同的組。
命令:
select id, Sum(score) from result group by id;
現(xiàn)在有兩個(gè)表學(xué)生表(stu)和成績表(result)。
當(dāng)連接運(yùn)算符為“=”時(shí),為等值連接查詢。
現(xiàn)在要查詢年齡小于20歲學(xué)生的不及格成績。
select stu.id,score from stu,result where stu.id = result.id and age < 20 and score < 60;
等值查詢效率太低
①語法
select f1,f2,f3,.... from table1 left/right outer join table2 on 條件;
②左外連接查詢,例如
select a.id,score from (select id,age from stu where age < 20) a (過濾左表信息) left join (select id, score from result where score < 60) b (過濾右表信息) on a.id = b.id;
左外連接就是左表過濾的結(jié)果必須全部存在。如果存在左表中過濾出來的數(shù)據(jù),右表沒有匹配上,這樣的話右表就會(huì)出現(xiàn)NULL;
③右外連接查詢,例如
select a.id,score from (select id,age from stu where age < 20) a (過濾左表信息) right join (select id, score from result where score < 60) b (過濾右表信息) on a.id = b.id;
右外連接就是左表過濾的結(jié)果必須全部存在
①語法
select f1,f2,f3,.... from table1 inter join table2 on 條件;
②例如
select a.id,score from (select id,age from stu where age < 20) a (過濾左表信息) inner join (select id, score from result where score < 60) b (過濾右表信息) on a.id = b.id;
在圖書表(t_book)和圖書類別表(t_bookType)中
①.union
使用union關(guān)鍵字是,數(shù)據(jù)庫系統(tǒng)會(huì)將所有的查詢結(jié)果合并到一起,然后去掉相同的記錄;
select id from t_book union select id from t_bookType;
②.union all
使用union all,不會(huì)去除掉重復(fù)的記錄;
select id from t_book union all select id from t_bookType;
以上就是“MySQL中有哪些數(shù)據(jù)查詢語句”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家閱讀完這篇文章都有很大的收獲,小編每天都會(huì)為大家更新不同的知識(shí),如果還想學(xué)習(xí)更多的知識(shí),請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。