Java EE之旅08 資料庫基礎（2）

小楠總發表於2017-12-21
Java資料庫

###回顧

SQL的分類如下：

  1. DDL（Data Definition Language）：資料定義語言，用來定義資料庫物件：庫、表、列等； CREATE、 ALTER、DROP
  2. DML（Data Manipulation Language）：資料操作語言，用來定義資料庫記錄（資料）； INSERT、 UPDATE、 DELETE
  3. DCL（Data Control Language）：資料控制語言，用來定義訪問許可權和安全級別；
  4. DQL（Data Query Language）：資料查詢語言（主要是SELECT），用來查詢記錄（資料）。

上一篇文章主要介紹了DDL、DML，今天重點介紹DQL。

###DQL-資料查詢語言

資料庫執行DQL語句不會對資料進行改變，而是讓資料庫傳送結果集給客戶端（虛擬，查出來放在記憶體裡面）。 查詢返回的結果集是一張虛擬表。

查詢關鍵字：SELECT

基本語法： 
    select 列名 from表名 [WHERE --> GROUP BY -->HAVING--> ORDER BY]

語法詳解：
    SELECT selection_list /*要查詢的列名稱*/
        FROM table_list /*要查詢的表名稱*/
        WHERE condition /*行條件*/
        GROUP BY grouping_columns /*對結果分組*/
        HAVING condition /*分組後的行條件*/
        ORDER BY sorting_columns /*對結果分組*/
        LIMIT offset_start, row_count /*結果限定*/
複製程式碼

為了更好地說明，我們以3張表為例：

image.png

image.png

image.png

相關的表建立以及資料插入SQL語句如下：

學生表stu：
    create table stu (
        sid     char(6),
        sname   varchar(50),
        age     int,
        gender  varchar(50)
    );
    insert into stu values('s_1001', 'liuyi', 35, 'male');
    insert into stu values('s_1002', 'chener', 15, 'female');
    insert into stu values('s_1003', 'zhangsan', 95, 'male');
    insert into stu values('s_1004', 'lisi', 65, 'female');
    insert into stu values('s_1005', 'wangwu', 55, 'male');
    insert into stu values('s_1006', 'zhaoliu', 75, 'female');
    insert into stu values('s_1007', 'sunqi', 25, 'male');
    insert into stu values('s_1008', 'zhouba', 45, 'female');
    insert into stu values('s_1009', 'wujiu', 85, 'male');
    insert into stu values('s_1010', 'zhengshi', 5, 'female');
    insert into stu values('s_1011', 'xxx', null, null);

員工表emp：
    create table emp(
        empno    int,
        ename    varchar(50),
        job      varchar(50),
        mgr      int,
        hiredate date,
        sal      decimal(7,2),
        comm     decimal(7,2),
        deptno   int
    ); 
    insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
    insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
    insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
    insert into emp values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);
    insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
    insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
    insert into emp values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
    insert into emp values(7788,'scott','analyst',7566,'1987-04-19',3000,null,20);
    insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
    insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
    insert into emp values(7876,'adams','clerk',7788,'1987-05-23',1100,null,20);
    insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
    insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
    insert into emp values(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);
    
部門表dept：
    create table dept(
        deptno    int,
        dname     varchar(14),
        loc       varchar(13)
    );
    insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
    insert into dept values(20, 'RESEARCH', 'DALLAS');
    insert into dept values(30, 'SALES', 'CHICAGO');
    insert into dept values(40, 'OPERATIONS', 'BOSTON');
複製程式碼

###1 基礎查詢

1.1 查詢所有列
select * from stu;

1.2 查詢指定列
select sid, sname, age from stu;
複製程式碼

###2 條件查詢

2.1 條件查詢介紹
條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用如下運算子及關鍵字：
    =、!=、<>、<、<=、>、>=
    BETWEEN…AND
    IN(set)
    IS NULL或者IS NOT NULL
    AND
    OR
    NOT

2.2 查詢性別為女，並且年齡小於50的記錄（and關鍵字，與）
    select * from stu where gender='female' and age<50;

2.3 查詢學號為S_1001，或者姓名為liSi的記錄（or關鍵字，或）
    select * from stu where sid ='s_1001' or sname='liSi';

2.4 查詢學號為S_1001，S_1002，S_1003的記錄（in關鍵字，包含）
    select * from stu where sid in ('S_1001','S_1002','S_1003');

2.5 查詢學號不是S_1001，S_1002，S_1003的記錄（not in關鍵字，不包含）
    select * from tab_student where s_number not in ('S_1001','S_1002','S_1003');

2.6 查詢年齡為null的記錄（is關鍵字，是；not null不為空）
    select * from stu where age is null;

2.7 查詢年齡在20到40之間的學生記錄（大於（等於）小於（等於）；between區間）
    select * from stu where age>=20 and age<=40;
    或者
    select * from stu where age between 20 and 40;

2.8 查詢性別非男的學生記錄（不等於）
    select * from stu where gender!='male';
    或者
    select * from stu where gender<>'male';
    或者
    select * from stu where not gender='male';

2.9 查詢姓名不為null的學生記錄
    select * from stu where sname is not null;
    或者
    select * from stu where not sname is null;
複製程式碼

###3 模糊查詢

當想模糊匹配的時候就需要使用模糊查詢了。模糊查詢需要使用關鍵字LIKE。


模糊查詢需要用到的萬用字元: 
    _ 任意一個字元
  	%：任意0~n個字元
例子：
    '%張%'  '張_'

3.1 查詢姓名由5個字母構成的學生記錄
    select * from stu where sname like '_____';
    注意：
        模糊查詢必須使用LIKE關鍵字。其中 “_”匹配任意一個字母，5個“_”表示5個任意字母。

3.2 查詢姓名由5個字母構成，並且第5個字母為“i”的學生記錄
    select * from stu where sname like '____i';

3.3 查詢姓名以“z”開頭的學生記錄
    select * from stu where sname like 'z%';
    注意：
        其中“%”匹配0~n個任何字母。

3.4 查詢姓名中第2個字母為“i”的學生記錄
    select * from stu where sname like '_i%';

3.5 查詢姓名中包含“a”字母的學生記錄
    select * from stu where sname like '%a%';

複製程式碼

###4 欄位控制查詢


4.1 去除重複記錄
    去除重複記錄（兩行或兩行以上記錄中系列的上的資料都相同），例如emp表中sal欄位就存在相同的記錄。當只查詢emp表的sal欄位時，那麼會出現重複記錄，那麼想去除重複記錄，需要使用DISTINCT：
    select distinct sal from emp;

4.2 檢視僱員的月薪與佣金之和
    因為sal和comm兩列的型別都是數值型別，所以可以做加運算。如果sal或comm中有一個欄位不是數值型別，那麼會出錯。
    select *,sal+comm from emp;
    注意：
        comm列有很多記錄的值為NULL，因為任何東西與NULL相加結果還是NULL，所以結算結果可能會出現NULL。下面使用了把NULL轉換成數值0的函式IFNULL：
        select *,sal+ifnull(comm,0) from emp;

4.3 給列名新增別名
    在上面查詢中出現列名為sal+IFNULL(comm,0)，這很不美觀，現在我們給這一列給出一個別名，為total：
    select *, sal+ifnull(comm,0) as total from emp;
    給列起別名時，是可以省略AS關鍵字的：
    select *,sal+ifnull(comm,0) total from emp;

複製程式碼

###5 排序order by 列名 asc(預設) desc

5.1 查詢所有學生記錄，按年齡升序排序
    select * from stu order by sage asc;
    或者
    select * from stu order by sage;

5.2 查詢所有學生記錄，按年齡降序排序
    select * from stu order by age desc;

5.3 查詢所有僱員，按月薪降序排序，如果月薪相同時，按編號升序排序
    select * from emp order by sal desc,empno asc;
複製程式碼

###6 聚合函式sum avg max min count

聚合函式是用來做縱向運算的函式： COUNT()：統計指定列不為NULL的記錄行數； MAX()：計算指定列的最大值，如果指定列是字串型別，那麼使用字串排序運算； MIN()：計算指定列的最小值，如果指定列是字串型別，那麼使用字串排序運算； SUM()：計算指定列的數值和，如果指定列型別不是數值型別，那麼計算結果為0； AVG()：計算指定列的平均值，如果指定列型別不是數值型別，那麼計算結果為0；

6.1 COUNT：當需要縱向統計時可以使用COUNT()。

    查詢emp表中記錄數：
        select count(*) as cnt from emp;
    
    查詢emp表中有佣金的人數：
        select count(comm) cnt from emp;
    注意：
        因為count()函式中給出的是comm列，那麼只統計comm列非NULL的行數。
    
    查詢emp表中月薪大於2500的人數：
        select count(*) from emp where sal > 2500;
    
    統計月薪與佣金之和大於2500元的人數：
        select count(*) as cnt from emp where sal+ifnull(comm,0) > 2500;
    
    查詢有佣金的人數，有領導的人數：
        select count(comm), count(mgr) from emp;

6.2 SUM和AVG：當需要縱向求和時使用sum()函式。

    查詢所有僱員月薪和：
        select sum(sal) from emp;
    
    查詢所有僱員月薪和，以及所有僱員佣金和：
        select sum(sal), sum(comm) from emp;
    
    查詢所有僱員月薪+佣金和：
        select sum(sal+ifnull(comm,0)) from emp;
    
    統計所有員工平均工資：
        select avg(sal) from emp;

6.3 MAX和MIN

    查詢最高工資和最低工資：
        select max(sal), min(sal) from emp;

複製程式碼

###7 分組查詢

當需要分組查詢時需要使用GROUP BY子句，例如查詢每個部門的工資和，這說明要使用部門來分組。 注：凡和聚合函式同時出現的列名，一定要寫在group by 之後

查詢每個部門的部門編號和每個部門的工資和：
    select deptno, sum(sal) from emp group by deptno;

查詢每個部門的部門編號以及每個部門的人數：
    select deptno,count(*) from emp group by deptno;

查詢每個部門的部門編號以及每個部門工資大於1500的人數：
    select deptno,count(*) from emp where sal>1500 group by deptno;

HAVING子句
    查詢工資總和大於9000的部門編號以及工資和：
    select deptno, sum(sal) from emp group by deptno having sum(sal) > 9000;
複製程式碼

having與where的區別:

  1. having是在分組後對資料進行過濾。where是在分組前對資料進行過濾
  2. having後面可以使用聚合函式(統計函式)。where後面不可以使用聚合函式。
  3. WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那麼這行記錄不會參加分組；而HAVING是對分組後資料的約束。

###8 LIMIT

LIMIT用來限定查詢結果的起始行，以及總行數。涉及到分頁技術。

MySQL中有，Oracle中沒有這個LIMIT關鍵字。

8.1 查詢5行記錄，起始行從0開始
    select * from emp limit 0, 5;
    第一個引數：從第幾行查詢，第二個引數：查幾行
    注意，起始行從0開始，即第一行開始！（這與程式碼中一樣）

8.2　查詢10行記錄，起始行從3開始
    select * from emp limit 3, 10;

8.3　分頁查詢

    如果一頁記錄為10條，希望檢視第3頁記錄應該怎麼查呢？
    
    1. 第一頁記錄起始行為0，一共查詢10行；
    2. 第二頁記錄起始行為10，一共查詢10行；
    3. 第三頁記錄起始行為20，一共查詢10行；
    
    例子：
        select * from emp;
        int currentpage = 3;-- 當前頁
        int pagesize = 3; -- 每頁顯示的條數
        select * from emp limit （currentpage-1）*pagesize,pagesize;
複製程式碼

###查詢程式碼的書寫順序和執行順序

我們用一個圖來理解查詢的過程（可以看到查詢結果不斷減少、分組化、有序化）：

image.png

查詢語句書寫順序：select –> from -> where -> group by -> having -> order by -> limit
查詢語句執行順序：from -> where -> group by -> having -> select -> order by -> limit	
複製程式碼

注意：排序和篩選的順序不能夠反過來（執行就會報錯），因為篩選之後資料的順序可能就亂了。

相關文章