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
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;
查看有多少人下過單:去重、求和
SELECT COUNT(DISTINCT userId) FROM orders;
查看每個(gè)用戶下單的錢的總和
SELECT SUM(totalPrice),userId FROM orders GROUP BY userId
查看最晚下單和最早下單相隔的天數(shù)
SELECT DATEDIFF(MAX(orderTime),MIN(orderTime)) FROM orders;
查看自己活了多少天
SELECT DATEDIFF(NOW(),'1998-07-24') AS 活了多少天
補(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
一般將分組函數(shù)和分組查詢一塊使用
比如查詢每人的平均消費(fèi)值
SELECT AVG(totalPrice) AS 平均消費(fèi),userId FROM orders GROUP BY userId
復(fù)雜一點(diǎn)的分組查詢:
查詢下單超過兩單的用戶
分析:第一、先按照用戶分組宵喂,第二再去判斷下單數(shù)是不是大于2
where后面指定查詢條件指的是原表的糠赦,這里是在一個(gè)分組查詢后的新表做判斷,所用條件用having來連接锅棕,放在分組后
第一步
SELECT COUNT(*),userId FROM orders GROUP BY userId;
第二步
SELECT COUNT(*),userId FROM orders GROUP BY userId HAVING COUNT(*) > 2;
涉及到分組查詢的條件時(shí)愉棱,如果條件在原表中有可以用where(在group by前面),如果在分組后的結(jié)果集中就用having(在group by后面)連接查詢條件哲戚。
按每個(gè)字段分組
按用戶id和旅游路線id進(jìn)行分組奔滑,并查詢每一組的平均值
SELECT AVG(totalPrice),userId,routeId FROM orders GROUP BY userId,routeId
添加排序
SELECT AVG(totalPrice),userId,routeId FROM orders GROUP BY userId,routeId ORDER BY AVG(totalPrice) desc
6、表連接
按功能分類
內(nèi)連接:等值連接顺少、非等值連接朋其、自連接
外連接:左外連接王浴、右外連接、全外連接
交叉連接
6.1梅猿、Sql92語法
6.1.1等值連接
查看訂單編號(在訂單表中)和下單賬號名(在用戶表中)氓辣,并按用戶名排序
SELECT orderNum,username FROM orders,USER WHERE orders.userId = user.id ORDER BY username;
查看訂單編號和下單產(chǎn)品
有時(shí)候表名比較長,又多處會(huì)用到袱蚓,就可以為表名取別名钞啸,跟為字段取別名一樣,用as或者空格喇潘,同樣可以區(qū)分不同表的同名字段体斩,不過如果為表起了別名,就不能再使用原表名.字段名
SELECT o.orderNum,r.name FROM orders o,route r WHERE o.routeId = r.id;
實(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;
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;
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;
6.2.4交叉連接
查的就是笛卡爾積
SELECT u.*,r.*
FROM USER u
CROSS JOIN role r;
練習(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';