MySql學(xué)習(xí)--基礎(chǔ)->條件->排序->函數(shù)->分組->表連接

1.查看當(dāng)前所有數(shù)據(jù)庫

show databases;

2.打開指定的庫

use 庫名

3.查看當(dāng)前庫的所有表

show tables;

4.查看其他庫的所有表

show tables from 庫名;

5.創(chuàng)建表

create table 表名(
  列名 列類型,
  列名 列類型,
  ...
);

6.查看表結(jié)構(gòu)

desc 表名;

7.查看服務(wù)器版本
方式一:登錄到mysql服務(wù)端

select version();

方式二:沒有登錄到mysql服務(wù)端

mysql --version
student

1、基礎(chǔ)查詢

8.起別名(as可省略)

select sname as 姓名 from student;
select sname 姓名 from student;

9.查詢字段

select sname as 姓名,classNum as 班級編號 from srudent;

10.查詢所有字段

select * from student

11.查詢常量

select 100 as 常量

12.查詢表達(dá)式

select 100%98 as 表達(dá)式結(jié)果

13.查詢函數(shù)

select version() as 函數(shù)結(jié)果

14.拼接字段
+號的作用:

select 100 + 90; #結(jié)果為190畴蒲,在SQL中+為運(yùn)算符笤闯,在java中,+號既可以做運(yùn)算符,又可以做連接符
select '100' + 90; #結(jié)果為190读恃,試圖將字符型轉(zhuǎn)化為數(shù)值型
select 'like' + 90; #結(jié)果為90愕鼓,字符型轉(zhuǎn)換不成數(shù)值型,便是0
select null + 90; #結(jié)果為0需曾,只要一方為null吗坚,結(jié)果為null

concat函數(shù)的作用:拼接字符串

select concat(sname,ssex) as 姓名性別 from student

15.去重

select distinct classNum as 班級編號 from student

16.ifnull(args1,args2)函數(shù)

select ifnull(sbirthday,0) from student #查看學(xué)生表里的生日,如果生日為null胯舷,那么將用0代替null

2刻蚯、條件查詢

簡單條件運(yùn)算符:>、<桑嘶、=炊汹、!=、<>逃顶、>=讨便、<=
邏輯運(yùn)算符:&&、||以政、!霸褒、and、or盈蛮、not
模糊查詢:like废菱、between...and...、in、is null
簡單查詢:

select sname as 姓名 from student where classNum <> 102; #查詢班級編號不等于102的學(xué)生姓名
select * from student where sno >= 03 and sno <= 07; #查詢學(xué)生編號在03-07之間的學(xué)生信息
select * from student where sno < 03 or sno > 07 or classNum = 102;#查詢學(xué)生編號不是在03-07之間的信息或者班級為102的學(xué)生信息

邏輯查詢
還可以這樣寫:

select * from student where not(sno>=03 and sno <=07) or classNum = 102;

模糊查詢:%表示通配符殊轴,可以表示任意多個(gè)字符衰倦,包括0個(gè),_也表示通配符旁理,不過下劃線只能表示一個(gè)字符

select * from student where sname like '娃%'; #查詢頭部為娃的同學(xué)信息
select * from student where sname like '%娃'; #查詢尾部為娃的同學(xué)信息
select * from student where sname like '%娃%'; #查詢包含娃字符的同學(xué)信息
select * from student where sname like '_娃%'; #查詢第二個(gè)字符為娃的同學(xué)信息

如果模糊查詢條件里有的條件樊零,比如查詢第二個(gè)字符為的數(shù)據(jù),那就要對第二個(gè)_進(jìn)行轉(zhuǎn)義

select * from student where sname like '_\_%';
select * from student where sname like '_$_%' escape '$'; #自定義轉(zhuǎn)義字符
select * from student where sno between 03 and 05; #包含臨界值孽文,不要調(diào)換兩個(gè)臨界值的順序驻襟,完全等價(jià)于>=03 <=05
select * from student where sno in(03,04,05); #in后面加的是個(gè)集合,in列表的值必須兼容或者一致

=和我<>符號不能判斷null值芋哭,要用is null和is not null

select * from student where sbirthday is null; #只能判斷null沉衣,不能判斷其他數(shù)據(jù)類型

安全等于<=>,既可以判斷Null又可以判斷正常數(shù)據(jù)類型

select * from student where sbirthday <=> null;
select * from student where sname <=> '大娃';

3减牺、 排序查詢

select * from student order by sbirthday desc; #降序排列
select * from student order by sbirthday asc; #升序排列
select * from student order by sbirthday; #默認(rèn)是升序
SELECT CONCAT(last_name,first_name) AS 姓名,department_id AS 部門號,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 
FROM employees 
ORDER BY 年薪 DESC ,ORDER BY 姓名 ASC;
SELECT CONCAT(last_name,first_name) AS 姓名,salary
FROM employees
WHERE salary < 8000 OR salary > 17000 ORDER BY salary DESC;
SELECT * FROM employees
WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC ,ORDER BY partment_id ASC;

4厢蒜、函數(shù)

單行函數(shù):concat、length烹植、ifnull等
分組函數(shù):做統(tǒng)計(jì)使用斑鸦,又稱為統(tǒng)計(jì)函數(shù)、聚合函數(shù)草雕、組函數(shù)

4.1巷屿、常見函數(shù)

length():查看里面的str的字節(jié)個(gè)數(shù),utf8下一個(gè)英文占一個(gè)字節(jié)墩虹,一個(gè)中文占3個(gè)字節(jié)
select length('John');
select length('哈哈哈hahaha');
concat():拼接字符串
select concat(sname,classNum) as 姓名班級 from student;
upper():轉(zhuǎn)大寫 lower():轉(zhuǎn)小寫
select upper('John');
select lower('John');
substr():截取字符串
SQL中嘱巾,索引從1開始,不像java索引從0開始
select substr('楊過和小龍女',4); #小龍女
select substr('楊過和小龍女',3,4); #和小龍女诫钓,3表示從哪里開始旬昭,4表示截取的個(gè)數(shù),不是結(jié)束索引
instr():查詢子字符串第一次出現(xiàn)的時(shí)候的索引
select instr('楊過和小龍女和楊過','楊過'); 
trim():去掉多余的空字符
select trim('    楊過    ');
select trim('a' from 'aaaaaa楊aaaaa過aaaaaaaaa');#去掉有效字符的前后的a字符菌湃,楊過中間的a為有效字符
lpad():左填充 rpad右填充
select lpad('白素貞',10,'*'); #*******白素貞问拘,字符不夠的左邊填充*
replace():替換字符串
select replace('張無忌愛上了周芷若','周芷若','趙敏');

數(shù)學(xué)函數(shù)

round():四舍五入
select round(-1.55);
select round(-1.567,2); #保留兩位
ceil():向上取整
select ceil(1.00); #1
select ceil(1.02); #2
select ceil(-1.02); #-1
floor():向下取整
select floor(1.00); #1
select floor(1.02); #1
select floor(-1.02); #-2
truncate():截?cái)?select truncate(1.566666,1); #從小數(shù)點(diǎn)后一位進(jìn)行截?cái)啵?.5
mod():取模就是去余數(shù) mod(a,b) : a-a/b*b;被除數(shù)為正,結(jié)果為正惧所,被除數(shù)為負(fù)骤坐,結(jié)果為負(fù)
select mod(10,3); #1
select 10%3; #1

日期函數(shù)

select now(); #返回系統(tǒng)當(dāng)前日期+時(shí)間
select curdate(); #返回系統(tǒng)當(dāng)前日期
select curtime(); #返回系統(tǒng)當(dāng)前時(shí)間
select year(now()); #當(dāng)前年份
select datediff('2020-04-06','2020-04-07'); #查看兩個(gè)日期的相差天數(shù)

其他函數(shù)

select version(); #查看當(dāng)前版本
select database(); #查看當(dāng)前數(shù)據(jù)庫
select user(); #查看當(dāng)前用戶

流程控制函數(shù)

if(表達(dá)式,true取值,false取值)
select if(10>5,'大','小');
case 要判斷的字段或表達(dá)式
when 常量1 then 要顯示的值1或語句1;
when 常量2 then 要顯示的值2或語句2;
...
else 要顯示的值n或語句n;
end
案例:查詢員工的工資下愈,要求
部門號=30纽绍,顯示的工資為1.1倍
部門號=40,顯示的工資為1.2倍
部門號=50势似,顯示的工資為1.3倍
其他部門拌夏,顯示的工資為原工資
select salary as 原始工資,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工資
from employees
case
when 表達(dá)式1 then 要顯示的值1或者語句1;
when 表達(dá)式2 then 要顯示的值2或者語句2;
...
else 要顯示的值n或者語句n
end as 等級
from employees
select salary,
case 
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 等級
from employees
SELECT NAME,price,
CASE
WHEN price>5000 THEN '貴'
WHEN price>3000 THEN '一般'
WHEN price>1000 THEN '便宜'
ELSE '小于1000'
END AS 等級
FROM route ORDER BY price desc

4.2僧著、分組函數(shù)

特點(diǎn):
1、sum障簿、avg一般處理數(shù)值型
max霹抛、min、count可以處理任意類型
2卷谈、0+Null都等于Null,以上分組函數(shù)都忽略null值
3霞篡、可以和distinct配合使用
4世蔗、和分組行數(shù)一同查詢的字段有限制,因?yàn)榉纸M行數(shù)查詢出來的是一行數(shù)據(jù)朗兵,字段卻有很多行數(shù)據(jù)污淋,最終只會(huì)顯示一行,和分組函數(shù)一同查詢的字段要求是group by 后的字段余掖,group by的意思是分組寸爆,可以將下過單的用戶分組,分別查詢每個(gè)用戶下單的平均值盐欺,不然為啥叫分組行數(shù)呢赁豆,哈哈哈

訂單表
sum():計(jì)算和
select sum(totalPrice) from orders
avg():計(jì)算平均值
select avg(totalPrice) from orders
max():計(jì)算最大值
select max(totalPrice) from orders
min():計(jì)算最小值
select min(totalPrice) from orders
count():計(jì)算該字段的非空個(gè)數(shù)
select count(totalPrice) from orders

可以合并:
SELECT SUM(totalPrice) 總價(jià),ROUND(AVG(totalPrice)) 平均值,MAX(totalPrice) 最大值,MIN(totalPrice) 最小值,COUNT(totalPrice) 總個(gè)數(shù) FROM orders
也可以查時(shí)間的最早和最晚
SELECT MAX(orderTime) 最晚下單時(shí)間,MIN(orderTime) 最早下單時(shí)間 FROM orders;
SELECT SUM(DISTINCT totalPrice) 去重后,SUM(totalPrice) 去重前 FROM orders;
image.png

查看有多少人下過單:去重、求和

SELECT COUNT(DISTINCT userId) FROM orders;
image.png

查看每個(gè)用戶下單的錢的總和

SELECT SUM(totalPrice),userId FROM orders GROUP BY userId
image.png

查看最晚下單和最早下單相隔的天數(shù)

SELECT DATEDIFF(MAX(orderTime),MIN(orderTime)) FROM orders;
image.png

查看自己活了多少天

SELECT DATEDIFF(NOW(),'1998-07-24') AS 活了多少天
image.png

補(bǔ)充:

select count(*) from orders;
select count(1) from orders; 

都表示查詢所有行數(shù)的個(gè)數(shù)冗美、
MYISAM存儲(chǔ)引擎下魔种,count()的效率高,因?yàn)樵撘嫦掠杏?jì)數(shù)器粉洼,可以直接返回行數(shù)
INNODB存儲(chǔ)引擎下节预,count(
)和count(1)的效率差不多,比count(字段)要高一些

5属韧、分組查詢

基礎(chǔ)格式

select 分組函數(shù),分組字段 from 表名 [where 查詢條件] group by 分組字段

簡單分組查詢:將一列字段進(jìn)行分組安拟,同樣的放在一塊

SELECT userId FROM orders GROUP BY userId
image.png

一般將分組函數(shù)和分組查詢一塊使用
比如查詢每人的平均消費(fèi)值

SELECT AVG(totalPrice) AS 平均消費(fèi),userId FROM orders GROUP BY userId
image.png

復(fù)雜一點(diǎn)的分組查詢:
查詢下單超過兩單的用戶
分析:第一、先按照用戶分組宵喂,第二再去判斷下單數(shù)是不是大于2
where后面指定查詢條件指的是原表的糠赦,這里是在一個(gè)分組查詢后的新表做判斷,所用條件用having來連接锅棕,放在分組后
第一步

SELECT COUNT(*),userId FROM orders GROUP BY userId;
image.png

第二步

SELECT COUNT(*),userId FROM orders GROUP BY userId HAVING COUNT(*) > 2;
image.png

涉及到分組查詢的條件時(shí)愉棱,如果條件在原表中有可以用where(在group by前面),如果在分組后的結(jié)果集中就用having(在group by后面)連接查詢條件哲戚。


按每個(gè)字段分組
按用戶id和旅游路線id進(jìn)行分組奔滑,并查詢每一組的平均值

SELECT AVG(totalPrice),userId,routeId FROM orders GROUP BY userId,routeId
image.png

添加排序

SELECT AVG(totalPrice),userId,routeId FROM orders GROUP BY userId,routeId ORDER BY AVG(totalPrice) desc
image.png

6、表連接

按功能分類
內(nèi)連接:等值連接顺少、非等值連接朋其、自連接
外連接:左外連接王浴、右外連接、全外連接
交叉連接

6.1梅猿、Sql92語法

6.1.1等值連接

查看訂單編號(在訂單表中)和下單賬號名(在用戶表中)氓辣,并按用戶名排序

SELECT orderNum,username FROM orders,USER WHERE orders.userId = user.id ORDER BY username;
image.png

查看訂單編號和下單產(chǎn)品
有時(shí)候表名比較長,又多處會(huì)用到袱蚓,就可以為表名取別名钞啸,跟為字段取別名一樣,用as或者空格喇潘,同樣可以區(qū)分不同表的同名字段体斩,不過如果為表起了別名,就不能再使用原表名.字段名

SELECT o.orderNum,r.name FROM orders o,route r WHERE o.routeId = r.id;
image.png

實(shí)現(xiàn)三表連接

SELECT o.orderNum,u.username,r.name FROM orders o,USER u,route r WHERE o.userId = u.id AND o.routeId = r.id ORDER BY username;
image.png

6.1.2非等值連接

就是where后面的查詢條件不再是等值判斷颖低,變成了區(qū)間判斷比如:> < >= <= <> between and等等
查詢訂單表里下單的用戶名以及價(jià)格大于10000的訂單價(jià)格

SELECT username,totalPrice FROM USER u,orders o WHERE u.id = o.userId AND o.totalPrice > 10000;
image.png

6.1.3自連接:自己表連接自己表

把自己表當(dāng)成多份表來使用
sql92語法練習(xí)

SELECT job_id,location_id FROM employees e,department d WHERE e.department_id = d.department_id AND e.department_id = 90;
SELECT e.last_name,d.department_name,d.location_id,l.city FROM employees e,department d,locations l 
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commosion_pct IS NOT NULL;
SELECT e.last_name,e.job_id,e.department_id,d.department_name FROM employees e,department d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.city = 'Toronto';
SELECT department_name,job_title,MIN(salary) FROM employees e,jobs j,department d 
WHERE e.department_id = d.department_id AND e.job_id = j.job_id GROUP BY department_name,job_title;
SELECT COUNT(*),country_id FROM department d,locations l 
WHERE d.location_id = l.location_id GROUP BY country_id HAVING COUNT(*) > 2;
SELECT e.last_name AS employees,e.employee_id AS "Emp#",m.last_name AS manager,m.employee_id AS "Mgr#"
FROM employees e,employees m WHERE e.manager_id = m.employee_id;

6.2絮吵、Sql99語法

select 查詢列表
from 表1 別名 【連接條件】
join 表2 別名
on 【表連接條件】
【where 篩選條件】
【group by 分組】
【having 篩選條件】
【order by 排序條件】
【limit 選取個(gè)數(shù)】

表連接條件
內(nèi)連接:inner
外連接:左外連接(left 【outer】)、右外連接(right【outer】)忱屑、全外連接(full 【outer】)
交叉連接:cross

6.2.1內(nèi)連接(inner可以省略 )

select 查詢列表
from 表1 別名 inner
join 表2 別名
on 【表連接條件】
【where 篩選條件】
【group by 分組】
【having 篩選條件】
【order by 排序條件】
【limit 選取個(gè)數(shù)】
SELECT last_name,department_name 
FROM employees e INNER JOIN department d ON e.department_id = d.department_id

SELECT last_name,job_title
FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id
WHERE last_name LIKE '%e%';

SELECT COUNT(*),city
FROM department d INNER JOIN locations l ON d.location.id = l.location_id
GROUP BY city HAVING COUNT(*) > 3

SELECT department_name,COUNT(*) AS 員工個(gè)數(shù)
FROM department d INNER JOIN employees e ON e.department_id = d.department_id
GROUP BY department_name HAVING COUNT(*) > 3
ORDER BY 員工個(gè)數(shù) DESC;

SELECT e.last_name,d.department_name,j.job_title
FROM employees e 
INNER JOIN department d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

6.2.3外連接

特點(diǎn):
1.外連接的查詢結(jié)果是主表中的所有記錄
2.如果從表中有和它匹配的蹬敲,則顯示匹配的值,如果從表中沒有和它匹配的莺戒,則顯示為null
3.外連接查詢結(jié)果=內(nèi)連接結(jié)果+主表中有而從表中沒有的記錄
4.左外連接:left outer join:left左邊的是主表伴嗡;right outer join :right join 右邊的是主表
左外連接左邊的是主表,右外連接右邊的是主表
內(nèi)連接查的是兩個(gè)表都有的數(shù)據(jù)
外連接可以查出一個(gè)表有另一個(gè)表沒有的數(shù)據(jù)从铲,沒有的就用Null來填充

左外連接
SELECT s.name,s.destId,d.name
FROM scenery s
LEFT OUTER JOIN destination d ON s.destId = d.id;

SELECT d.id,d.name,COUNT(*) AS 景點(diǎn)個(gè)數(shù)
FROM destination d
LEFT OUTER JOIN scenery s ON d.id = s.destId
WHERE s.name IS NOT NULL
GROUP BY d.name;
右外連接
SELECT s.name,s.destId,d.name
FROM scenery s
RIGHT OUTER JOIN destination d ON s.destId = d.id;
image.png

6.2.4交叉連接

查的就是笛卡爾積

SELECT u.*,r.*
FROM USER u
CROSS JOIN role r;
image.png

練習(xí)

SELECT g.name,g.boyId,b.* 
FROM girl g
LEFT OUTER JOIN boy b ON g.boyId = b.id
WHERE g.id > 3;

SELECT city,d.*
FROM locations l
LEFT OUTER department d ON d.location_id = l.locationId
WHERE d.department_id IS NULL;

SELECT e.*,department_name
FROM employees e
LEFT OUT JOIN department d ON e.department_id =d.department_id
WHERE d.department_name = 'SAL' OR d.department_name = 'IT';
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末闹究,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子食店,更是在濱河造成了極大的恐慌渣淤,老刑警劉巖,帶你破解...
    沈念sama閱讀 223,002評論 6 519
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件吉嫩,死亡現(xiàn)場離奇詭異价认,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)自娩,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,357評論 3 400
  • 文/潘曉璐 我一進(jìn)店門用踩,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人忙迁,你說我怎么就攤上這事脐彩。” “怎么了姊扔?”我有些...
    開封第一講書人閱讀 169,787評論 0 365
  • 文/不壞的土叔 我叫張陵惠奸,是天一觀的道長。 經(jīng)常有香客問我恰梢,道長佛南,這世上最難降的妖魔是什么梗掰? 我笑而不...
    開封第一講書人閱讀 60,237評論 1 300
  • 正文 為了忘掉前任,我火速辦了婚禮嗅回,結(jié)果婚禮上及穗,老公的妹妹穿的比我還像新娘。我一直安慰自己绵载,他們只是感情好埂陆,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,237評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著娃豹,像睡著了一般焚虱。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上培愁,一...
    開封第一講書人閱讀 52,821評論 1 314
  • 那天,我揣著相機(jī)與錄音缓窜,去河邊找鬼定续。 笑死,一個(gè)胖子當(dāng)著我的面吹牛禾锤,可吹牛的內(nèi)容都是我干的私股。 我是一名探鬼主播,決...
    沈念sama閱讀 41,236評論 3 424
  • 文/蒼蘭香墨 我猛地睜開眼恩掷,長吁一口氣:“原來是場噩夢啊……” “哼倡鲸!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起黄娘,我...
    開封第一講書人閱讀 40,196評論 0 277
  • 序言:老撾萬榮一對情侶失蹤峭状,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后逼争,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體优床,經(jīng)...
    沈念sama閱讀 46,716評論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,794評論 3 343
  • 正文 我和宋清朗相戀三年誓焦,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了胆敞。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,928評論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡杂伟,死狀恐怖移层,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情赫粥,我是刑警寧澤观话,帶...
    沈念sama閱讀 36,583評論 5 351
  • 正文 年R本政府宣布,位于F島的核電站越平,受9級特大地震影響匪燕,放射性物質(zhì)發(fā)生泄漏蕾羊。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,264評論 3 336
  • 文/蒙蒙 一帽驯、第九天 我趴在偏房一處隱蔽的房頂上張望龟再。 院中可真熱鬧,春花似錦尼变、人聲如沸利凑。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,755評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽哀澈。三九已至,卻和暖如春度气,著一層夾襖步出監(jiān)牢的瞬間割按,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,869評論 1 274
  • 我被黑心中介騙來泰國打工磷籍, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留适荣,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 49,378評論 3 379
  • 正文 我出身青樓院领,卻偏偏與公主長得像弛矛,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個(gè)殘疾皇子比然,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,937評論 2 361

推薦閱讀更多精彩內(nèi)容