My SQL的基本查詢

一、SQL的分類(lèi)

數(shù)據(jù)查詢語(yǔ)言(DQL Data Query Language):select
數(shù)據(jù)操縱語(yǔ)言(DML Data Manipulation Language):insert,delete,update(針對(duì)表中的數(shù)據(jù))
數(shù)據(jù)定義語(yǔ)言(DDL Data Definition Language):create,drop,alter(針對(duì)表的結(jié)構(gòu))
事務(wù)控制語(yǔ)言(TCL Transactional Control Language):commit,rollback
數(shù)據(jù)控制語(yǔ)言(DCL Data Control Language):grant,revoke 

二除师、常用命令

查看當(dāng)前數(shù)據(jù)庫(kù)管理系統(tǒng)中得所有數(shù)據(jù)庫(kù):show databases;
創(chuàng)建數(shù)據(jù)庫(kù)：create database 數(shù)據(jù)庫(kù)名;
選擇數(shù)據(jù)庫(kù)：use 數(shù)據(jù)庫(kù)名;
導(dǎo)入數(shù)據(jù)：source 路徑;
刪除數(shù)據(jù)庫(kù)：drop database 數(shù)據(jù)庫(kù)名;
查詢當(dāng)前使用的數(shù)據(jù)庫(kù):select database();
查看當(dāng)前數(shù)據(jù)庫(kù)中的表：show tables;
查看表結(jié)構(gòu):desc 表名;
退出數(shù)據(jù)庫(kù):exit/ctrl+c/quit
查看其它庫(kù)中的表:show tables from 數(shù)據(jù)庫(kù)名;
查看表的創(chuàng)建語(yǔ)句:show create table 表名;

三凝化、DQL

查詢字段:

select 字段名 from 表名;

一個(gè)：select ename from emp;
多個(gè)：select empno,ename from emp;
所有：select * from emp;

別名的使用:

select empno,ename,sal*12 as yearsal from emp;
as關(guān)鍵字可以省略
別名為中文，需要加上''
select empno,ename,sal*12 '年薪' from emp;

注意：SQL語(yǔ)句中帶有select官辽，不會(huì)修改底層表中的數(shù)據(jù)蛹磺，
只是將表中的記錄檢索出來(lái)；

1同仆、條件查詢：

運(yùn)算符.PNG

My SQL的基本查詢

使用where關(guān)鍵字：

查詢工資等于5000的員工信息:
select empno,ename,sal from emp where sal=5000;

工資為double類(lèi)型萤捆，也可以帶上'';但最好不要
select empno,ename,sal from emp where sal>'3000';

工資大于等于3000并且小于等于5000：
select empno,ename,sal from emp where sal>=3000 and sal<=5000;
或：select empno,ename,sal from emp where sal between 3000 and 5000;

注意：between...and...必須前面是小的，后面是大的

between...and..也可用在字符上：前閉后開(kāi)：
select ename from emp where ename between 'A' and 'K';
結(jié)果中包含A但不包含K

null：不是一個(gè)值俗批，表示什么也不是俗或，空

找出補(bǔ)助是空的所有員工：
select ename,comm from emp where comm is null;

查詢補(bǔ)助不是空的員工：
select ename,comm from emp where comm is not null;

and：并且 or:或者 （and優(yōu)先級(jí)高于or）

找出從事salesman和manage的員工
select ename,job from emp where job='salesman' or job='manager';

找出工資大于1800的員工，要求部門(mén)編號(hào)是20或者30的
select empno,ename,sal,deptno 
from emp 
where sal>1800 and (deptno=20 or deptno=30);

in：在某個(gè)范圍中搜索

找出工作崗位是manager和工作崗位是salesman的員工:
select ename,job
from emp
where job='manager' or job='salesman';

使用in：
select ename,job 
from emp 
where job in('manager','salesman');

找出工資為1500和5000的員工：
select ename,sal
from emp
where sal in(1500,5000);

找出工資不是1500和5000的員工：
select ename,sal
from emp
where sal not in(1500,5000);

like:模糊查詢

%:0~N個(gè)任意字符
_:任意一個(gè)字符

找出名字里含有O的員工:
select ename from emp where ename like '%O%';

找出第一個(gè)字母為S的員工：
select ename from emp where ename like'S%';

找出最后一個(gè)字母為T(mén)的員工：
select ename from emp where ename like '%T';

找出第二個(gè)字母為A的員工：
select ename from emp where ename like '_A%';

找出第三個(gè)字母為A的員工：
select ename from emp where ename like '__A%';

找出倒數(shù)第二個(gè)字母為N的員工：
select ename from emp where ename like '%N_';

2岁忘、排序：

排序采用order by子句辛慰，order by后面跟上排序字段，排序字段可以放多個(gè)干像，多個(gè)采用逗號(hào)間隔帅腌，order by默認(rèn)采用升序(asc)，如果存在where子句麻汰，那么order by必須放到where語(yǔ)句的后面

通過(guò)薪水排序：
select ename,sal from emp order by sal;

通過(guò)薪水排序(降序)：
select ename,sal from emp order by sal desc;

通過(guò)薪水降序速客，如果薪水一樣的，再按名字升序排
select ename,sal from emp order by sal desc,ename asc;

按所選的第二個(gè)字段排(ename,sal中的sal)
數(shù)字表示查詢結(jié)果中的字段的編號(hào)什乙，從1開(kāi)始
select ename,sal from emp order by 2;

3挽封、數(shù)據(jù)處理函數(shù)/單行處理函數(shù)：

單行處理函數(shù).PNG

單行處理函數(shù)：只針對(duì)當(dāng)前行進(jìn)行處理

轉(zhuǎn)小寫(xiě)：(底層數(shù)據(jù)庫(kù)沒(méi)改，只是將查詢結(jié)果顯示成小寫(xiě))
select lower(ename) from emp;

轉(zhuǎn)大寫(xiě):
select upper(ename) as ename from emp;

substr:
select substr(ename,1,1) from emp;

找出員工名字第二個(gè)字母為A的員工：
select ename from emp where substr(ename,2,1)='A';

length:
select length(ename) from emp;

trim:
select ename from emp where ename =trim('  smith   ');

保留兩位小數(shù)：
select round(123.456,2);

不保留小數(shù)：(0可以不寫(xiě))
select round(123.456,0);

保留到個(gè)位，十位辅愿，百位：-1智亮，-2，-3
select round(123.456,-1)   120
select round(123.456,-2)   100
select round(153.456,-2)   200

生成隨機(jī)數(shù)：
select rand();  

ifnull:
補(bǔ)助是空的顯示為0：
select ename,ifnull(comm,0) from emp;

注意：null參與運(yùn)算点待，結(jié)果還是null

補(bǔ)助為空的阔蛉，每月發(fā)100：
select sal + ifnull(comm,100) from emp;

計(jì)算年薪：
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

關(guān)于數(shù)據(jù)庫(kù)的日期處理

每一個(gè)數(shù)據(jù)庫(kù)處理日期的時(shí)候，采用的機(jī)制都是不同的癞埠，日期處理都有自己的一套機(jī)制状原，所以在實(shí)際的開(kāi)發(fā)中，表中的字段定義為date類(lèi)型的情況很少苗踪。因?yàn)橐坏┦褂萌掌陬?lèi)型颠区，那么java程序?qū)⒉荒軌蛲ㄓ谩Ｔ趯?shí)際開(kāi)發(fā)中通铲，一般會(huì)使用"日期字符串"來(lái)表示日期毕莱。

mysql數(shù)據(jù)庫(kù)管理系統(tǒng)中對(duì)日期的處理提供了兩個(gè)重要的函數(shù)：

str_to_date
date_format

java中的日期格式：

YYYY 年
MM   月
dd   日
HH   時(shí)
mm   分
ss   秒
SSS  毫秒

java中

將字符串轉(zhuǎn)換成日期類(lèi)型：
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse("1970-10-10");

日期格式化：
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date nowTime = new Date();
String strTime = sdf.format(nowTime);

mysql的日期格式：

%Y  年
%m  月
%d  日
%H  時(shí)
%i  分
%s  秒

str_to_date函數(shù)：

作用：將“日期字符串”轉(zhuǎn)換成“日期類(lèi)型”數(shù)據(jù)。(varchar--->date)
執(zhí)行結(jié)果：為date類(lèi)型
使用格式：str_to_date('日期字符串','日期格式')

例：

查詢出1980-12-17入職的員工(hiredate為date類(lèi)型):
select ename,hiredate from emp where hiredate='1980-12-17';
能查出結(jié)果：mysql默認(rèn)的日期格式：%Y-%m-%d,以上的日期字符串'1980-12-17'正好和默認(rèn)的日期格式一樣颅夺，
存在了自動(dòng)類(lèi)型轉(zhuǎn)換朋截，自動(dòng)將日期字符串轉(zhuǎn)換成了日期類(lèi)型，所以以上查詢可以查詢出結(jié)果吧黄。

select ename,hiredate from emp where hiredate='12-17-1980';
發(fā)生錯(cuò)誤：
'12-17-1980'日期字符串和mysql默認(rèn)的日期格式不同部服，
hiredate是date類(lèi)型，'12-17-1980'是一個(gè)字符串varchar類(lèi)型拗慨，
類(lèi)型不匹配廓八，無(wú)法查詢出結(jié)果，發(fā)生錯(cuò)誤胆描。

糾正：
select ename,hiredate from emp where hiredate=str_to_date('12-17-1980','%m-%d-%Y');

str_to_date函數(shù)通常使用在插入操作中瘫想。字段是date類(lèi)型，不接收字符串varchar類(lèi)型昌讲，需要先通過(guò)該函數(shù)將varchar變成date再插入數(shù)據(jù)国夜。才能成功。

date_format函數(shù)：

作用：將日期類(lèi)型date轉(zhuǎn)換成具有特定格式的日期字符串varchar（date--->varchar）
運(yùn)算結(jié)果：varchar類(lèi)型（具備特定格式的）
語(yǔ)法：date_format(日期類(lèi)型數(shù)據(jù)短绸，'日期格式')

例：

查詢員工的入職日期车吹，以'10-12-1980'的格式顯示
select ename,date_format(hiredate,'%m-%d-%Y') as hiredate from emp;

查詢員工的入職日期，以'10/12/1980'的格式顯示
select ename,date_format(hiredate,'%m/%d/%Y') as hiredate from emp;

以下兩個(gè)DQL語(yǔ)句的執(zhí)行結(jié)果相同：
select ename,hiredate from emp;(hiredate自動(dòng)轉(zhuǎn)換成varchar類(lèi)型醋闭，采用'%Y-%m-%d'格式)
select ename,date_format(hiredate,'%Y-%m-%d') as hiredate from emp;

結(jié)論：date_format函數(shù)主要使用在查詢操作中窄驹。客戶需要日期以特定格式展示的時(shí)候证逻，需要使用該函數(shù)乐埠。

4、分組函數(shù)/聚合函數(shù)/多行處理函數(shù)：

count sum max min avg:（自動(dòng)忽略null）

求和：select sum(sal) from emp;
求平均值：select avg(sal) from emp;
最高薪水：select max(sal) from emp;
最低薪水：select min(sal) from emp;
統(tǒng)計(jì)：select count(ename) from emp;

注意：count

查詢?cè)撟侄沃胁粸閚ull的元素總數(shù)：select count(comm) from emp;
查詢滿足條件的所有記錄總數(shù)：select count(*) from emp where comm is null;

分組函數(shù)不能直接使用在where子句中：

select ename,sal from emp where sal>avg(sal);報(bào)錯(cuò)！

distinct:去除重復(fù)記錄,只能出現(xiàn)在所有字段的最前方

select distinct job from emp;
統(tǒng)計(jì)公司一共有多少崗位：
select count(distinct job) from emp;

分組查詢：

主要涉及兩個(gè)子句：group by和having

group by：通過(guò)某個(gè)或者某些字段進(jìn)行分組

having：和where相同丈咐，都是為了完成數(shù)據(jù)的過(guò)濾瑞眼，
        where和having后面都是添加條件
        where在group by之前完成過(guò)濾
        having在group by之后完成過(guò)濾

例：

找出每個(gè)工作崗位的最高薪水:
先按照工作崗位分組，使用max函數(shù)求每一組的最高薪水：
select max(sal) from emp group by job;

工作崗位也可以寫(xiě)到select后面棵逊，因?yàn)檎霉ぷ鲘徫灰彩?個(gè)不同的值
select job,max(sal) from emp group by job;

ename不可以寫(xiě)到select后面伤疙，因?yàn)椴粚儆诜纸M字段
select ename,job,max(sal) from emp group by job;
--mysql中可以，語(yǔ)法在這方面比較松散辆影，不嚴(yán)格徒像，但是查詢結(jié)果沒(méi)有意義
--oracle中不可以，語(yǔ)法嚴(yán)格蛙讥，執(zhí)行的時(shí)候報(bào)錯(cuò)锯蛀。

總結(jié)：如果語(yǔ)句中有g(shù)roup by子句,select后面只能跟參與分組的字段和分組函數(shù)。

例：

計(jì)算每個(gè)部門(mén)的平均薪水（按照部門(mén)編號(hào)分組次慢，對(duì)每一組求平均薪水）：
select deptno,avg(sal) as avgsal from emp group by deptno;

計(jì)算出不同部門(mén)中的不同工作崗位的最高薪水：
select deptno,job,max(sal) from emp group by deptno,job;
//deptno,job兩個(gè)字段聯(lián)合起來(lái)分組谬墙。

找出每個(gè)工作崗位的最高薪水，除manager之外：
select job,max(sal) from emp where job<>'manager' group by job;

找出每個(gè)工作崗位的平均薪水经备，要求顯示平均薪水大于1500：
select job,avg(sal) from emp where avg(sal)>1500 group by job;
報(bào)錯(cuò)！where關(guān)鍵字后不能直接使用分組函數(shù)
分組函數(shù)必須在分組完成后執(zhí)行部默，而分組需要group by侵蒙，而group by在where后面執(zhí)行
使用having：
select job,avg(sal) from emp group by job having avg(sal)>1500;

注意：盡量在where中過(guò)濾，無(wú)法過(guò)濾的數(shù)據(jù)傅蹂，通常都是需要先分組之后再過(guò)濾的纷闺，這個(gè)時(shí)候可以選擇使用having。（效率問(wèn)題）

總結(jié)：一個(gè)完整的DQL語(yǔ)句的總結(jié)：

select ...
from ...
where ...
group by ..
having ...
order by...

第一：以上的關(guān)鍵字順序不能變份蝴，嚴(yán)格遵守
第二：執(zhí)行順序：
1.from      從某張表中檢索數(shù)據(jù)
2.where     經(jīng)過(guò)某條件進(jìn)行過(guò)濾
3.group by  然后分組
4.having    分組之后不滿意再過(guò)濾
5.select    查詢出來(lái)
6.order by  排序輸出