文章目錄

• • • 1.基礎(chǔ)查詢
    • 2.條件查詢
    • 3.排序查詢
    • 4.常見函數(shù)
    • • 4.1 單行函數(shù)
      • • 4.1.1 字符函數(shù)
        • 4.1.2 數(shù)學(xué)函數(shù)
        • 4.1.3 日期函數(shù)
        • 4.1.4 流程控制函數(shù)
        • 4.1.5 其他函數(shù)
      • 4.2 分組函數(shù)/統(tǒng)計(jì)函數(shù)/聚合函數(shù)
    • 5.分組查詢

1.基礎(chǔ)查詢

語法： SELECT 要查詢的東西 【FROM 表名】; #查詢employees表中所有字段 select * from employees;#查詢employees表中的first_name字段 select first_name from employees;#查詢employees表中的多個(gè)字段 select first_name,salary FROM employees;#查詢常量值 select 100+123; select "zhaoxr"#查詢表達(dá)式 select 100*98;#查詢函數(shù) select version();#起別名 select 98*98 as result; select last_name as "姓",first_name as "名" from employees; select last_name "姓",first_name "名" from employees;#去重 select distinct department_id from employees;# +的作用 select 100+99; select "123"+99; select "zhaoxr"+"linux"; select null+16;# concat函數(shù) select concat(last_name," ",first_name) "姓名" from employees; 類似于Java中 :System.out.println(要打印的東西); 特點(diǎn)： ① 通過select查詢完的結(jié)果 ，是一個(gè)虛擬的表格，不是真實(shí)存在 ② 要查詢的東西 可以是常量值、可以是表達(dá)式、可以是字段、可以是函數(shù)

2.條件查詢

條件查詢：根據(jù)條件過濾原始表的數(shù)據(jù)，查詢到想要的數(shù)據(jù) 語法： select 要查詢的字段|表達(dá)式|常量值|函數(shù) from 表 where 條件 ;分類： 一、條件表達(dá)式示例：salary>10000條件運(yùn)算符：> < >= <= = != <>二、邏輯表達(dá)式 示例：salary>10000 && salary<20000邏輯運(yùn)算符：and（&&）:兩個(gè)條件如果同時(shí)成立，結(jié)果為true，否則為falseor(||)：兩個(gè)條件只要有一個(gè)成立，結(jié)果為true，否則為falsenot(!)：如果條件成立，則not后為false，否則為true三、模糊查詢 like between and in is null | is not null通配符： %：任意多個(gè)字符，包含0個(gè)字符 _：單個(gè)字符 \：轉(zhuǎn)義字符，"\_"代表字符_示例：last_name like 'a%' # 查詢工資大于等于12000的員工ID,姓名和薪水 select employee_id,CONCAT(last_name," ",first_name) as "姓名",salary from employees where salary>=12000;# 查詢部分編號(hào)不等于90的員工姓名和部門編號(hào) select CONCAT(last_name," ",first_name) as "姓名",department_id from employees where department_id!=90;#查詢工資在10000到20000之間的員工ID，姓名和工資 select employee_id,concat(last_name," ",first_name) as "姓名",salary from employees where salary>=10000 and salary<=20000;#查詢員工姓中含有a的員工信息 select * from employees where last_name like "%a%";#查詢員工姓第三個(gè)字母是l,第五個(gè)是e的員工信息 select * from employees where last_name like "__l_e%"; #查詢員工姓的第二個(gè)字符是_的員工信息 select * from employees where last_name like "_\_%";#查詢員工ID在100到200之間的員工信息,包含100和200 select * from employees where employee_id between 100 and 200;#查詢工種編號(hào)是IT_PROG,PU_CLERK,ST_CLERK的員工信息 select * from employees where job_id in("IT_PROG","PU_CLERK","ST_CLERK");#查詢獎(jiǎng)金率為null的員工信息 select * from employees where commission_pct is null;

3.排序查詢

語法： select要查詢的東西 from表 where 條件order by 排序的字段|表達(dá)式|函數(shù)|別名 【asc|desc】 #查詢員工信息，工資由低到高排列 select * from employees order by salary asc;#查詢員工信息，部門編號(hào)大于等于90，并且按照入職先后排列 select * from employees where department_id>=90 order by hiredate asc;#查詢員工信息和年薪，并按照年薪從低到高排列 select *,salary*12*(1+ifnull(commission_pct,0)) as "年薪" from employees order by salary*12*(1+ifnull(commission_pct,0)) asc;#查詢員工的姓名和工資，按照姓名的長(zhǎng)度從小到大排列 select CONCAT(last_name," ",first_name) as "姓名",length(concat(last_name," ",first_name)) as "姓名長(zhǎng)度",salary from employees order by length(concat(last_name," ",first_name)) asc;#查詢員工信息，先按工資從低到高排名，再按員工編號(hào)從低到高排序 select * from employees order by salary asc,employee_id asc;

4.常見函數(shù)

4.1 單行函數(shù)

4.1.1 字符函數(shù)

concat拼接substr截取子串upper轉(zhuǎn)換成大寫lower轉(zhuǎn)換成小寫trim去前后指定的空格和字符ltrim去左邊空格rtrim去右邊空格replace替換lpad左填充rpad右填充instr返回子串第一次出現(xiàn)的索引length 獲取字節(jié)個(gè)數(shù) #length函數(shù)|查詢員工的姓，以及姓的長(zhǎng)度 select last_name,length(last_name) from employees;#concat函數(shù)|查詢員工的姓名 select concat(last_name," ",first_name) as "姓名" from employees;#upper函數(shù)和lower函數(shù)|查詢員工的姓名，姓大寫，名小寫 select concat(upper(last_name)," ",lower(first_name)) as "姓名" from employees;#substr截取函數(shù)|注意：截取函數(shù)從1開始 #返回"i love china"中的"china" select substr("i love china",8);#返回"我愛你中國(guó)"中"中國(guó)" select substr("我愛你中國(guó)",4);#返回"i love china"中的"love" select substr("i love china",3,4);#instr返回子串第一次出現(xiàn)的索引|返回"i love china"中的'c'在第幾個(gè)位置 select instr("i love china","c");#trim去空格|刪除" love "左右的空格 select trim(" love ");#trim去除指定的字符|刪除"aaaaaloveaaaa"左右的'a' select trim('a' from "aaaaaaaloveaaaaa");#lpad填充|使用'0'填充字符"我愛你",總字符數(shù)10個(gè)，填充結(jié)果為"0000000我愛你" select lpad("我愛你",10,'0');#replace替換|將"我愛china",替換為"我愛中國(guó)" select replace("我愛china","china","中國(guó)");

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

round 四舍五入rand 隨機(jī)數(shù)floor向下取整ceil向上取整mod取余truncate截?cái)? #數(shù)學(xué)函數(shù) #round四舍五入|2.55四舍五入保留整數(shù)部分 select round(2.55);#ceil向上取整，返回>=該數(shù)的最小整數(shù)|取整2.0001,2.0000 select ceil(2.0001),ceil(2.0000);#floor向下取整，返回<=該數(shù)的最大整數(shù)|取整-2.0001,2.0000 select floor(-2.0001),floor(2.0000);#truncate截?cái)?#xff0c;保留小數(shù)幾位|1.99999保留兩位小數(shù) select truncate(1.99999,2);#mod取余|10/3的余數(shù) select mod(10,3);

4.1.3 日期函數(shù)

now當(dāng)前系統(tǒng)日期+時(shí)間curdate當(dāng)前系統(tǒng)日期curtime當(dāng)前系統(tǒng)時(shí)間str_to_date 將字符轉(zhuǎn)換成日期date_format將日期轉(zhuǎn)換成字符

#時(shí)間函數(shù) #查詢當(dāng)前時(shí)間 select now();#獲取指定的年月日，獲取當(dāng)前年份,月份,日 select year(now()) as "年",month(now()) as "月",day(now()) as "日";#獲取員工姓名和入職年份 select concat(last_name," ",first_name) as "姓名",year(hiredate) as "入職年份" from employees;#str_to_date將日期格式的字符轉(zhuǎn)換為規(guī)定的日期格式，規(guī)定的日期格式"2021-04-29" select str_to_date("2021年4月29日","%Y年%m月%d日"),str_to_date("04/29/2021","%m/%d/%Y");#date_format將日期轉(zhuǎn)換為自己想要的日期字符|查詢員工的姓名和入職時(shí)間，入職時(shí)間顯示為"2021年4月29日"的格式 select concat(last_name," ",first_name) as "姓名",date_format(hiredate,"%Y年%m月%d日") as "日期" from employees;

4.1.4 流程控制函數(shù)

if 處理雙分支if(判斷條件,真就執(zhí)行此處，假就執(zhí)行此處)case 要判斷的字段或者表達(dá)式when 常量1 then 要顯示的值1或者語句1when 常量2 then 要顯示的值2或者語句2。。。。。else 要顯示的值n或者語句nendcase when 條件1 then 要顯示的值1或者語句1when 條件2 then 要顯示的值2或者語句2。。。。。else 要顯示的值n或者語句nend #流程控制函數(shù) #if函數(shù)|查詢員工的姓，獎(jiǎng)金率，如果有獎(jiǎng)金，返回有獎(jiǎng)金，沒有獎(jiǎng)金返回沒獎(jiǎng)金 select last_name,commission_pct,if(commission_pct is null,"沒獎(jiǎng)金","有獎(jiǎng)金") as "獎(jiǎng)金" from employees;#case|查詢員工的姓名，工資，部門ID#如果部門=90，salary*1.1;如果部門=100，salary*1.2;如果部門=110，salary*1.3;其它salary不變;并顯示 select concat(last_name," ",first_name),department_id,salary as "原始工資",case department_idwhen 90 then salary*1.1when 100 then salary*1.2when 110 then salary*1.3else salaryend as "當(dāng)前工資" from employees;#case|查詢員工的姓名，工資#如果工資小于10000，顯示低工資；#如果工資大于等于10000，小于20000，顯示中等工資；#如果工資大于等于20000，顯示高工資 select concat(last_name," ",first_name),salary,case when salary<10000 then "低工資"when salary>=10000 and salary<20000 then "中等工資"when salary>20000 then "高工資"end as "工資區(qū)間" from employees;

4.1.5 其他函數(shù)

version版本database當(dāng)前庫user當(dāng)前連接用戶 #其它函數(shù) #version查看版本號(hào) select version();#database查看當(dāng)前數(shù)據(jù)庫 select database();#user查看當(dāng)前用戶 select user();

4.2 分組函數(shù)/統(tǒng)計(jì)函數(shù)/聚合函數(shù)

sum 求和max 最大值min 最小值avg 平均值count 計(jì)數(shù) #分組函數(shù) #簡(jiǎn)單使用 select sum(salary) as "總工資",max(salary) as "最高工資",min(salary) as "最低工資",avg(salary) as "平均工資",count(salary) as "工資個(gè)數(shù)" from employees;#查詢有多少中工資 select count(distinct salary) from employees;#查詢employees表有多少行 select count(*) as "行數(shù)",count(1) as "行數(shù)" from employees; 特點(diǎn)：1、以上五個(gè)分組函數(shù)都忽略null值，除了count(*)2、sum和avg一般用于處理數(shù)值型max、min、count可以處理任何數(shù)據(jù)類型3、都可以搭配distinct使用，用于統(tǒng)計(jì)去重后的結(jié)果4、count的參數(shù)可以支持：字段、*、常量值，一般放1建議使用 count(*)

5.分組查詢

語法： select 查詢的字段，分組函數(shù) from 表 group by 分組的字段特點(diǎn)： 1、可以按單個(gè)字段分組 2、和分組函數(shù)一同查詢的字段最好是分組后的字段 3、分組篩選針對(duì)的表 位置 關(guān)鍵字 分組前篩選： 原始表 group by的前面 where 分組后篩選： 分組后的結(jié)果集 group by的后面 having4、可以按多個(gè)字段分組，字段之間用逗號(hào)隔開 5、可以支持排序 6、having后可以支持別名 #查詢每個(gè)部門的平均工資 select avg(salary),department_id from employees group by department_id;#查詢每個(gè)工種的最高工資 select max(salary),job_id from employees group by job_id;#查詢工資大于10000的每個(gè)工種的最高工資 select max(salary),job_id from employees where salary>10000 group by job_id;#查詢哪個(gè)部門員工的個(gè)數(shù)大于2 select count(*),department_id from employees group by department_id HAVING count(*)>2;#按員工的姓名長(zhǎng)度分組，查詢每一組員工的個(gè)數(shù)，篩選員工個(gè)數(shù)大于5的有哪些 select count(*),length(last_name) as "len_name" from employees group by length(last_name) having count(*)>5;#查詢每個(gè)部門每個(gè)工種的平均工資 select avg(salary),department_id,job_id from employees group by department_id,job_id;#查詢每個(gè)部門每個(gè)工種的平均工資,并按照降序排列 select avg(salary),department_id,job_id from employees group by department_id,job_id order by avg(salary) desc;

總結(jié)

以上是生活随笔為你收集整理的数据库基础知识——DQL语言（一）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。