**查詢數(shù)據(jù)記錄操作: **

• 簡單數(shù)據(jù)記錄查詢
• 條件數(shù)據(jù)記錄查詢
• 排序數(shù)據(jù)記錄查詢
• 限制數(shù)據(jù)記錄查詢
• 統(tǒng)計函數(shù)和分組數(shù)據(jù)記錄查詢

測試表

# 創(chuàng)建雇員表
 create table t_employee(
          empno int(11),
          ename varchar(20),
          job varchar(20),
          MGR int(11),
          Miredate date,
          sal double(10,2),
          comm double(10,2),
          deptno int(11)
 );

數(shù)據(jù).png

1. 簡單數(shù)據(jù)記錄查詢
   簡單數(shù)據(jù)記錄查詢: 簡單數(shù)據(jù)查詢、避免重復(fù)數(shù)據(jù)查詢、實現(xiàn)數(shù)據(jù)四則運算數(shù)據(jù)查詢、設(shè)置顯示格式數(shù)據(jù)查詢。
   (1) 簡單數(shù)據(jù)查詢
   簡單數(shù)據(jù)查詢: 查詢所有字段數(shù)據(jù)、查詢指定字段數(shù)據(jù)。

• 查詢所有字段數(shù)據(jù)

select * 
    from t_employee;

• 查詢指定字段數(shù)據(jù)

select empno, ename, sal 
    from t_employee;

(2)避免重復(fù)數(shù)據(jù)查詢(DISTINCT)

select distinct job 
    from t_employee;

(3)實現(xiàn)數(shù)據(jù)四則運算數(shù)據(jù)查詢

select ename, sal*12 
    from t_employee;

修改顯示字段名(AS)

select ename, sal*12 as yearsalary 
     from t_employee;

(4) 設(shè)置顯示格式數(shù)據(jù)查詢

# CONCAT函數(shù)連接字符串
 select concat(ename,'雇員的年薪為: ', sal*12) yearsalary 
    from t_employee;

2. 條件數(shù)據(jù)記錄查詢
   條件語句包含: 帶關(guān)系運算符和邏輯運算符的條件數(shù)據(jù)查詢、帶BETWEEN AND關(guān)鍵字的條件數(shù)據(jù)查詢、帶IS NULL關(guān)鍵字的條件數(shù)據(jù)查詢、帶IN關(guān)鍵字的條件數(shù)據(jù)查詢、帶LIKE關(guān)鍵字的條件數(shù)據(jù)查詢。
   (1)帶關(guān)系運算符和邏輯運算符的條件數(shù)據(jù)查詢

• 單條件數(shù)據(jù)查詢

select ename 
    from t_employee 
    where job='clerk';

• 多條件語句查詢

select ename,sal 
    from t_employee 
    where job='clerk' 
                and sal>800;

(2) 帶BETWEEN AND關(guān)鍵字的條件數(shù)據(jù)查詢

• 符合范圍的數(shù)據(jù)記錄查詢

select ename,sal 
    from t_employee 
    where sal 
                between 1000 and 2000;

• 不符合范圍的數(shù)據(jù)記錄查詢

select ename,sal 
    from t_employee 
    where sal not between 1000 and 2000;

(3)帶IS NULL關(guān)鍵字的條件數(shù)據(jù)查詢

• 空值數(shù)據(jù)記錄查詢

select ename, comm 
    from t_employee 
    where comm  is null;

• 非空值數(shù)據(jù)記錄查詢

select ename, comm 
    from t_employee 
    where not comm is null;

(4)帶IN關(guān)鍵字的條件數(shù)據(jù)查詢
IN--判斷字段的數(shù)值是否在指定的集合中

• 在集合中數(shù)據(jù)記錄查詢

select ename, empno 
    from t_employee 
    where empno in (7521,7780,7566,7788);

• 不在集合中數(shù)據(jù)記錄查詢

select ename, empno 
    from t_employee 
    where empno not in (7521,7780,7566,7788);

(5)帶LIKE關(guān)鍵字的條件數(shù)據(jù)查詢
LIKE--模糊查詢，支持的通配符如下：
-“_”通配符，該通配符值能匹配單個字符。
-“%”通配符，該通配符值可以匹配任意長度的字符串，既可以是0個字符，也可以是1個字符，也可以是很多個字符。在后邊表示向后模糊，在前面表示向前模糊，前后可以同時模糊。

• 帶有“%”通配符的查詢

select ename 
    from t_employee 
    where ename like 'a%';

• 帶有“_”通配符的查詢

# 匹配出第二個字母是a的
select ename 
    from t_employee 
    where ename like '_a%';

3. 排序數(shù)據(jù)記錄查詢
   查詢時默認為升序。
   排序數(shù)據(jù)查詢結(jié)果: 單字段排序、多字段排序。
   (1) 按照單字段排序

• 升序排序

select * 
    from t_employee 
    order by sal asc;

• 降序排序

select * 
    from t_employee 
    order by mgr desc;

(2) 按照多字段排序

select *
         from t_employee
         order by sal asc,
                 Miredate desc;

4. 限制數(shù)據(jù)記錄查詢數(shù)量
   限制數(shù)據(jù)查詢結(jié)果數(shù)量語句: 不指定初始位置方式、指定初始位置方式。
   (1) 不指定初始位置

• 顯示記錄數(shù)小于查詢結(jié)果

select *
         from t_employee
         where comm is null limit 2;

• 顯示記錄數(shù)大于查詢結(jié)果

 select *
          from t_employee
          where comm is null limit 11;

(2) 指定初始位置

select *
         from t_employee
         where comm is null
         order by miredate limit 0,5;

5. 統(tǒng)計函數(shù)和分組數(shù)據(jù)記錄查詢
   統(tǒng)計函數(shù):

• COUNT()函數(shù): 該統(tǒng)計函數(shù)實現(xiàn)統(tǒng)計表中記錄的條數(shù)。
• AVG()函數(shù): 該統(tǒng)計函數(shù)實現(xiàn)計算字段值的平均值。
• SUM()函數(shù): 該統(tǒng)計函數(shù)實現(xiàn)計算字段值的總和。
• MAX()函數(shù): 該統(tǒng)計函數(shù)實現(xiàn)查詢字段值的最大值。
• MIN()函數(shù): 該統(tǒng)計函數(shù)實現(xiàn)查詢字段值的最小值。
  (1) 統(tǒng)計函數(shù)
• 統(tǒng)計數(shù)據(jù)記錄條數(shù)

# 為count(*)設(shè)置別名為number
select count(*) number from t_employee;

(2) 統(tǒng)計計算平均值

select avg(comm) average from t_employee;

(3) 統(tǒng)計計算求和

select sum(sal) sumvalue from t_employee;

(4) 統(tǒng)計計算最大值和最小值

select max(sal) maxval, min(sal) minval from t_employee;

(5) 關(guān)于統(tǒng)計函數(shù)注意點
如果操作的表中沒有任何數(shù)據(jù)記錄，則COUNT()函數(shù)返回數(shù)據(jù)0， 而其他函數(shù)則返回NULL。
(6) 分組數(shù)據(jù)查詢---簡單分組查詢

select *
         from t_employee
         group by deptno;

(7) 分組數(shù)據(jù)查詢---實現(xiàn)統(tǒng)計功能分組查詢(GROUP_CONCAT)
GROUP_CONCAT函數(shù)實現(xiàn)顯示每個分組中的字段。

select deptno, group_concat(ename) enames
         from t_employee
         group by deptno;

(8) 分組數(shù)據(jù)查詢---實現(xiàn)多個字段分組查詢

 select deptno, miredate, group_concat(ename) enames, count(ename)
          from t_employee
          group by deptno, miredate;

(9) 分組數(shù)據(jù)查詢---實現(xiàn)HAVING子句限定分組查詢

select deptno, avg(sal) average,group_concat(ename) enames, count(ename) number
          from t_employee
          group by deptno
          having avg(sal)>2000;