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';