28_資料庫_第28天（資料庫、表及表數據、SQL語句）_講義

今日內容介紹 1.MySQL資料庫 2.SQL語句 01資料庫概念 A: 什麼是資料庫資料庫就是存儲數據的倉庫，其本質是一個文件系統，數據按照特定的格式將數據存儲起來，用戶可以對資料庫中的數據進行增加，修改，刪除及查詢操作。 B: 什麼是資料庫管理系統資料庫管理系統（DataBase Mana ...

今日內容介紹
1.MySQL資料庫
2.SQL語句

01資料庫概念

A: 什麼是資料庫

  資料庫就是存儲數據的倉庫，其本質是一個文件系統，數據按照特定的格式將數據存儲起來，
  用戶可以對資料庫中的數據進行增加，修改，刪除及查詢操作。

B: 什麼是資料庫管理系統

  資料庫管理系統（DataBase Management System，DBMS）：指一種操作和管理資料庫的大型軟體，用於建立、使用和維護資料庫，
  對資料庫進行統一管理和控制，以保證資料庫的安全性和完整性。用戶通過資料庫管理系統訪問資料庫中表內的數據。

02常見的資料庫

* A: 常見的資料庫
        MYSQL   ：開源免費的資料庫，小型的資料庫.已經被Oracle收購了.MySQL6.x版本也開始收費。
        Oracle  ：收費的大型資料庫，Oracle公司的產品。Oracle收購SUN公司，收購MYSQL。
        DB2     ：IBM公司的資料庫產品,收費的。常應用在銀行系統中.
        SQLServer：MicroSoft 公司收費的中型的資料庫。C#、.net等語言常使用。
        SyBase  ：已經淡出歷史舞臺。提供了一個非常專業數據建模的工具PowerDesigner。
        SQLite  : 嵌入式的小型資料庫，應用在手機端。
        Java相關的資料庫：MYSQL，Oracle．
        這裡使用MySQL資料庫。MySQL中可以有多個資料庫，資料庫是真正存儲數據的地方

03資料庫和管理系統

* A: 資料庫管理系統
        ----資料庫1
            ----數據表1a
            ----數據表1b
        ----資料庫2
            -----數據表2a
            -----數據表2b

04數據表和Java中類的對應關係

* A:資料庫中以表為組織單位存儲數據。
    表類似我們的Java類，每個欄位都有對應的數據類型。
    那麼用我們熟悉的java程式來與關係型數據對比，就會發現以下對應關係。
        類----------表
        類中屬性----------表中欄位
        對象----------記錄

05數據表和Java中類的對應關係用戶表舉例

* A:舉例:
賬務表
id      name        age 
1       lisi        23
2       wang        24

每一條記錄對應一個User的對象
[user1  id = 1 name = lisi  age = 23]
[user2  id = 2 name = wang  age = 24]

06MySQL資料庫安裝

A: 安裝步驟參見 day28_source《MySQL安裝圖解.doc》
B: 安裝後，MySQL會以windows服務的方式為我們提供數據存儲功能。開啟和關閉服務的操作：
    右鍵點擊我的電腦→管理→服務→可以找到MySQL服務開啟或停止。

07資料庫在系統服務

* A：開啟服務和關閉服務
方式1: 我的電腦-----> (右鍵)管理---->服務和應用程式---->服務----找到MySQL服務右鍵啟動或關閉 
方式2: 進入dos視窗 使用命令: net start mysql 開啟MySQL服務;  命令:net stop mysql 關閉MySql服務

08MySQL的登錄

* A: MySQL是一個需要賬戶名密碼登錄的資料庫，登陸後使用，它提供了一個預設的root賬號，使用安裝時設置的密碼即可登錄。
    格式1：cmd>  mysql –u用戶名 –p密碼
    例如：mysql -uroot –proot
    
    格式2：cmd>  mysql --host=ip地址 --user=用戶名 --password=密碼
    例如：mysql --host=127.0.0.1  --user=root --password=root

09SQLYog軟體介紹

* A: 具體參見 《SQLYog配置.doc》

10SQL語句介紹和分類

A:SQL介紹
- 前面學習了介面的代碼體現，現在來學習介面的思想，接下里從生活中的例子進行說明。
- 舉例：我們都知道電腦上留有很多個插口，而這些插口可以插入相應的設備，這些設備為什麼能插在上面呢？
- 主要原因是這些設備在生產的時候符合了這個插口的使用規則，否則將無法插入介面中，更無法使用。發現這個插口的出現讓我們使用更多的設備。
B: SQL分類
- 數據定義語言：簡稱DDL(Data Definition Language)，用來定義資料庫對象：資料庫，表，列等。關鍵字：create，alter，drop等
- 數據操作語言：簡稱DML(Data Manipulation Language)，用來對資料庫中表的記錄進行更新。關鍵字：insert，delete，update等
- 數據控制語言：簡稱DCL(Data Control Language)，用來定義資料庫的訪問許可權和安全級別，及創建用戶。
- 數據查詢語言：簡稱DQL(Data Query Language)，用來查詢資料庫中表的記錄。關鍵字：select，from，where等

C: SQL通用語法

  SQL語句可以單行或多行書寫，以分號結尾
  可使用空格和縮進來增強語句的可讀性
  MySQL資料庫的SQL語句不區分大小寫，建議使用大寫，例如：SELECT * FROM user。
  同樣可以使用/**/的方式完成註釋

11數據表中的數據類型

* A:MySQL中的我們常使用的數據類型如下  
    詳細的數據類型如下(不建議詳細閱讀！)
    分類  類型名稱    說明 
    整數類型    tinyInt 很小的整數
        smallint    小的整數
        mediumint   中等大小的整數
        int(integer)    普通大小的整數
    小數類型    float   單精度浮點數
        double  雙精度浮點數
        decimal（m,d）    壓縮嚴格的定點數
    日期類型    year    YYYY  1901~2155
        time    HH:MM:SS  -838:59:59~838:59:59
        date    YYYY-MM-DD 1000-01-01~9999-12-3
        datetime    YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
        timestamp   YYYY-MM-DD HH:MM:SS  1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC
    文本、二進位類型    CHAR(M)         M為0~255之間的整數
        VARCHAR(M)      M為0~65535之間的整數
        TINYBLOB    允許長度0~255位元組
        BLOB        允許長度0~65535位元組
        MEDIUMBLOB  允許長度0~167772150位元組
        LONGBLOB    允許長度0~4294967295位元組
        TINYTEXT    允許長度0~255位元組
        TEXT        允許長度0~65535位元組
        MEDIUMTEXT  允許長度0~167772150位元組
        LONGTEXT    允許長度0~4294967295位元組
        VARBINARY(M)允許長度0~M個位元組的變長位元組字元串
        BINARY(M)   允許長度0~M個位元組的定長位元組字元串

12創建資料庫操作

* A: 創建資料庫
    格式:
        * create database 資料庫名;
        * create database 資料庫名 character set 字元集;
    例如：
    #創建資料庫 資料庫中數據的編碼採用的是安裝資料庫時指定的預設編碼 utf8
    CREATE DATABASE day21_1; 
    #創建資料庫 並指定資料庫中數據的編碼
    CREATE DATABASE day21_2 CHARACTER SET utf8;
    
* B: 查看資料庫
    查看資料庫MySQL伺服器中的所有的資料庫:
    show databases;
    查看某個資料庫的定義的信息:
    show create database 資料庫名;
    例如：
    show create database day21_1;

* C: 刪除資料庫
    drop database 資料庫名稱;
    例如：
    drop database day21_2;
    
* D: 其他的資料庫操作命令
    切換資料庫：
    use 資料庫名;
    例如：
    use day21_1;
    
* E: 查看正在使用的資料庫:
    select database();

13創建數據表格式

* A:格式：
    create table 表名(
       欄位名 類型(長度) 約束,
       欄位名 類型(長度) 約束
    );
    例如：
    ###創建分類表
    CREATE TABLE sort (
      sid INT, #分類ID 
      sname VARCHAR(100) #分類名稱
    );

14約束

* A: 約束的作用:
    create table 表名(
           列名 類型(長度) 約束,
           列名 類型(長度) 約束
        );
        限制每一列能寫什麼數據,不能寫什麼數據。
    
* B: 哪些約束:
        主鍵約束
        非空約束
        唯一約束
        外鍵約束

15SQL代碼的保存

* A: 當sql語句執行了，就已經對資料庫進行操作了，一般不用保存操作
    在SQLyog 中Ctrl + S 保存的是寫sql語句。

16創建用戶表

* A: 創建用戶表:
    需求:創建用戶表,用戶編號,姓名,用戶的地址
    
    * B: SQL語句
    CREAT TABLE users (
        uid INT,
        uname VARCHAR(20),
        uaddress VARCHAR(200)
    );

17主鍵約束

* A: 主鍵是用於標識當前記錄的欄位。它的特點是非空，唯一。
    在開發中一般情況下主鍵是不具備任何含義，只是用於標識當前記錄。
* B: 格式：
    1.在創建表時創建主鍵，在欄位後面加上  primary key.
    create table tablename( 
    id int primary key,
    .......
    )
    
    2. 在創建表時創建主鍵，在表創建的最後來指定主鍵   
    create table tablename(                     
    id int，
    .......，
    primary key(id)
    )
    
    3.刪除主鍵：alter table 表名 drop primary key;
    alter table sort drop primary key;
    
    4.主鍵自動增長：一般主鍵是自增長的欄位，不需要指定。
    實現添加自增長語句,主鍵欄位後加auto_increment(只適用MySQL)

18常見表的操作

* A: 查看資料庫中的所有表：
    格式：show tables;
       查看表結構：
    格式：desc 表名;
    例如：desc sort;

* B: 格式：drop table 表名;
    例如：drop table sort;

19修改表結構

 * A: 修改表添加列
    alter table 表名 add 列名 類型(長度) 約束;    
    例如：
    #1，為分類表添加一個新的欄位為 分類描述 varchar(20)
    ALTER TABLE sort ADD sdesc VARCHAR(20);

* B: 修改表修改列的類型長度及約束
    alter table 表名 modify 列名 類型(長度) 約束; 
    例如：
    #2, 為分類表的分類名稱欄位進行修改，類型varchar(50) 添加約束 not null
    ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL;

* C: 修改表修改列名
    alter table 表名 change 舊列名 新列名 類型(長度) 約束; 
    例如：
    #3, 為分類表的分類名稱欄位進行更換 更換為 snamesname varchar(30)
    ALTER TABLE sort CHANGE sname snamename VARCHAR(30);

* D: 修改表刪除列
    alter table 表名 drop 列名; 
    例如：
    #4, 刪除分類表中snamename這列
    ALTER TABLE sort DROP snamename;

* E: 修改表名
    rename table 表名 to 新表名; 
    例如：
    #5, 為分類表sort 改名成 category
    RENAME TABLE sort TO category;

* F: 修改表的字元集
    salter table 表名 character set 字元集;
    例如：
    #6, 為分類表 category 的編碼表進行修改，修改成 gbk
    ALTER TABLE category CHARACTER SET gbk;

20數據表添加數據_1

 * A: 語法：
    insert into 表 (列名1,列名2,列名3..) values  (值1,值2,值3..); -- 向表中插入某些列

 * 舉例:
    INSERT INTO product (id,pname,price) VALUES (1,'筆記本',5555.99);
    INSERT INTO product (id,pname,price) VALUES (2,'智能手機',9999);
 * 註意:
    列表,表名問題
    對應問題,個數,數據類型

21數據表添加數據_2

 * A: 添加數據格式,不考慮主鍵
    insert into 表名 (列名) values (值)
 * 舉例:
    INSERT INTO product (pname,price) VALUE('洗衣機',800);
    
    
 * B: 添加數據格式,所有值全給出
    格式
    insert into 表名 values (值1,值2,值3..); --向表中插入所有列
    INSERT INOT product VALUES (4,'微波爐',300.25)
 * C: 添加數據格式,批量寫入
    格式:
    insert into 表名 (列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3)
 舉例:
    INSERT INTO product (pname,price) VALUES
    ('智能機器人',25999.22),
    ('彩色電視',1250.36),
    ('沙發',58899.02)

22更新數據

* A: 用來修改指定條件的數據，將滿足條件的記錄指定列修改為指定值
    語法：
    update 表名 set 欄位名=值,欄位名=值;
    update 表名 set 欄位名=值,欄位名=值 where 條件;
* B: 註意：
        列名的類型與修改的值要一致.
        修改值得時候不能超過最大長度.
        值如果是字元串或者日期需要加’’.

* C: 例如：
    #1，將指定的sname欄位中的值 修改成 日用品
    UPDATE sort SET sname='日用品';
    #2, 將sid為s002的記錄中的sname改成 日用品
    UPDATE sort SET sname='日用品' WHERE sid='s002';
    UPDATE sort SET sname='日用品' WHERE sid='s003';

23刪除數據

* A: 語法：
    delete from 表名 [where 條件];
    或者
    truncate table 表名;

 * B: 面試題：
    刪除表中所有記錄使用delete from 表名; 還是用truncate table 表名;
    刪除方式：delete 一條一條刪除，不清空auto_increment記錄數。
    truncate 直接將表刪除，重新建表，auto_increment將置為零，從新開始。

 * C: 例如：
    DELETE FROM sort WHERE sname='日用品';
    #表數據清空
    DELETE FROM sort;

24命令行亂碼問題

A: 問題
    我們在dos命令行操作中文時，會報錯
    insert into user(username,password) values(‘張三’,’123’);     
    ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'username' at row 1
B: 原因:因為mysql的客戶端編碼的問題我們的是utf8,而系統的cmd視窗編碼是gbk
    解決方案（臨時解決方案）:修改mysql客戶端編碼。
    show variables like 'character%'; 查看所有mysql的編碼
    client connetion result 和客戶端相關
    database server system 和伺服器端相關 
    將客戶端編碼修改為gbk.
    set character_set_results=gbk; / set names gbk;
    以上操作，只針對當前視窗有效果，如果關閉了伺服器便失效。如果想要永久修改，通過以下方式:
    在mysql安裝目錄下有my.ini文件
    default-character-set=gbk 客戶端編碼設置                       
    character-set-server=utf8 伺服器端編碼設置
    註意:修改完成配置文件，重啟服務

25數據表和測試數據準備

* A: 查詢語句，在開發中使用的次數最多，此處使用“zhangwu” 賬務表。
    創建賬務表：
    CREATE TABLE zhangwu (
      id INT PRIMARY KEY AUTO_INCREMENT, -- 賬務ID
      name VARCHAR(200), -- 賬務名稱
      money DOUBLE, -- 金額
    );
* B: 插入表記錄：
    INSERT  INTO zhangwu(id,name,money) VALUES (1,'吃飯支出',247);
    INSERT  INTO zhangwu(id,name,money) VALUES (2,'工資收入',12345);
    INSERT  INTO zhangwu(id,name,money) VALUES (3,'服裝支出',1000);
    INSERT  INTO zhangwu(id,name,money) VALUES (4,'吃飯支出',325);
    INSERT  INTO zhangwu(id,name,money) VALUES (5,'股票收入',8000);
    INSERT  INTO zhangwu(id,name,money) VALUES (6,'打麻將支出',8000);
    INSERT  INTO zhangwu(id,name,money) VALUES (7,null,5000);

26數據的基本查詢

    * A: 查詢指定欄位信息
        select 欄位1,欄位2,...from 表名;
        例如：
        select id,name from zhangwu;

    * B: 查詢表中所有欄位
        select * from 表名; 
        例如：
        select * from zhangwu; 
        註意:使用"*"在練習、學習過程中可以使用，在實際開發中，不推薦使用。原因，
        要查詢的欄位信息不明確，若欄位數量很多，會導致查詢速度很慢。

    * C: distinct用於去除重覆記錄
        select distinct 欄位 from 表名;         
        例如：
        select distinct money from zhangwu;

    * D: 別名查詢，使用的as關鍵字，as可以省略的.
        別名可以給表中的欄位，表設置別名。 當查詢語句複雜時，使用別名可以極大的簡便操作。
        表別名格式: 
        select * from 表名 as 別名;
        或
        select * from 表名 別名;
        列別名格式：
        select 欄位名 as 別名 from 表名;
        或
        select 欄位名 別名 from 表名;
        例如
        表別名：
            select * from zhangwu as zw;
        列別名：
            select money as m from zhangwu;
            或
            select money m from zhangwu;

        我們在sql語句的操作中，可以直接對列進行運算。
        例如：將所有賬務的金額+10000元進行顯示.
        select pname,price+10000 from product;

27數據的條件查詢_1

 * A:條件查詢
        where語句表條件過濾。滿足條件操作，不滿足不操作，多用於數據的查詢與修改。
    
 * B : 格式 :
        select 欄位  from 表名  where 條件;   
 
 * C: while條件的種類如下：
    比較運算符   
        >  <  <=   >=   =  <>   ---------- 大於、小於、大於(小於)等於、不等於
        BETWEEN  ...AND...      ----------- 顯示在某一區間的值(含頭含尾)
        IN(set)                 -----------顯示在in列表中的值，例：in(100,200)
        LIKE 通配符                -----------模糊查詢，Like語句中有兩個通配符：
                                            % 用來匹配多個字元；例first_name like ‘a%’;
                                            _ 用來匹配一個字元。例first_name like ‘a_’;
        IS NULL     判斷是否為空
                                ------------is null; 判斷為空
                                            is not null; 判斷不為空
 * D 邏輯運算符  
        and                     ------------ 多個條件同時成立
        or                      ------------ 多個條件任一成立
        not                     ------------ 不成立，例：where not(salary>100);

 * E: 例如：
    查詢所有吃飯支出記錄
    SELECT * FROM zhangwu WHERE name = '吃飯支出';

    查詢出金額大於1000的信息
    SELECT * FROM zhangwu WHERE money >1000;

    查詢出金額在2000-5000之間的賬務信息
    SELECT * FROM zhangwu WHERE money >=2000 AND money <=5000;
    或
    SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 5000;

    查詢出金額是1000或5000或3500的商品信息
    SELECT * FROM zhangwu WHERE money =1000 OR money =5000 OR money =3500;
    或
    SELECT * FROM zhangwu WHERE money IN(1000,5000,3500);

28數據的條件查詢_2

 * A 模糊查詢
    查詢出賬務名稱包含”支出”的賬務信息。
    SELECT * FROM zhangwu WHERE name LIKE "%支出%";

 * B 查詢出賬務名稱中是五個字的賬務信息
    SELECT * FROM gjp_ledger WHERE ldesc LIKE "_____"; -- 五個下劃線_

* C 查詢出賬務名稱不為null賬務信息
    SELECT * FROM zhangwu WHERE name IS NOT NULL;
    SELECT * FROM zhangwu WHERE NOT (name IS NULL);

29排序查詢

* A: 排序查詢
     使用格式
        * 通過order by語句，可以將查詢出的結果進行排序。放置在select語句的最後。
        * SELECT * FROM 表名 ORDER BY 欄位ASC;
            * ASC 升序 (預設)
            * DESC 降序
        
* B: 案例代碼
        /*
          查詢,對結果集進行排序
          升序,降序,對指定列排序
          order by 列名 [desc][asc]
          desc 降序
          asc  升序排列,可以不寫
        */
        -- 查詢賬務表,價格進行升序
        SELECT * FROM zhangwu ORDER BY zmoney ASC

        -- 查詢賬務表,價格進行降序
        SELECT * FROM zhangwu ORDER BY zmoney DESC

        -- 查詢賬務表,查詢所有的支出,對金額降序排列
        -- 先過濾條件 where 查詢的結果再排序
        SELECT * FROM zhangwu WHERE zname LIKE'%支出%' ORDER BY zmoney DESC

30聚合函數

    * A: 聚合函數       
    * B: 函數介紹
        * 之前我們做的查詢都是橫向查詢，它們都是根據條件一行一行的進行判斷，而使用聚合函數查詢是縱向查詢，
            它是對一列的值進行計算，然後返回一個單一的值；另外聚合函數會忽略空值。
        * count：統計指定列不為NULL的記錄行數；
        * sum：計算指定列的數值和，如果指定列；
        * max：計算指定列的最大值，如果指定列是字元串類型，那麼使用字元串類型不是數值類型，那麼計算結果為0排0序運算；
        * min：計算指定列的最小值，如果指定列是字元串類型，那麼使用字元串排序運算；
        * avg：計算指定列的平均值，如果指定列類型不是數值類型，那麼計算結果為0；

    * C: 案例代碼
        /*
           使用聚合函數查詢計算
        */

        -- count 求和,對錶中的數據的個數求和  count(列名)
        -- 查詢統計賬務表中,一共有多少條數據
        SELECT COUNT(*)AS'count' FROM zhangwu

        -- sum求和,對一列中數據進行求和計算 sum(列名)
        -- 對賬務表查詢,對所有的金額求和計算
        SELECT SUM(zmoney) FROM zhangwu
        -- 求和,統計所有支出的總金額
        SELECT SUM(zname) FROM zhangwu WHERE zname LIKE'%收入%'

        INSERT INTO zhangwu (zname) VALUES ('彩票收入')

        -- max 函數,對某列數據,獲取最大值
        SELECT MAX(zmoney) FROM zhangwu

        -- avg 函數,計算一個列所有數據的平均數
        SELECT AVG(zmoney)FROM zhangwu

31分組查詢

A: 分組查詢
- a: 使用格式
  - 分組查詢是指使用group by字句對查詢信息進行分組,例如：我們要統計出zhanguw表中所有分類賬務的總數量,這時就需要使用group by 來對zhangwu表中的賬務信息根據parent進行分組操作。
  - SELECT 欄位1,欄位2… FROM 表名 GROUP BY 欄位 HAVING 條件;
  - 分組操作中的having子語句，是用於在分組後對數據進行過濾的，作用類似於where條件。
- b: having與where的區別
  - having是在分組後對數據進行過濾.
  - where是在分組前對數據進行過濾
  - having後面可以使用分組函數(統計函數)
  - where後面不可以使用分組函數。
B: 案例代碼

    /*
        查詢所有的數據
        吃飯支出 共計多少
        工資收入 共計多少
        服裝支出 共計多少
        股票收入 共計多少
        打麻將支出 共計多少錢

        分組查詢:  group by 被分組的列名
        必須跟隨聚合函數
        select 查詢的時候,被分組的列,要出現在select 選擇列的後面
    */
      SELECT SUM(zmoney),zname FROM zhangwu GROUP BY zname

    -- 對zname內容進行分組查詢求和,但是只要支出
    SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'%支出%'
    GROUP BY zname
    ORDER BY getsum DESC

    -- 對zname內容進行分組查詢求和,但是只要支出, 顯示金額大於5000
    -- 結果集是分組查詢後,再次進行篩選,不能使用where, 分組後再次過濾,關鍵字
     having
    SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'%支出%'
    GROUP BY zname HAVING getsum>5000

作業測試

一.創建一個資料庫,day28

二.創建一個Test表,並使用

三.測試修改表的結構

四.按一下要求,完成以下操作

1.創建一個學生表 id 主鍵 name age score classroom(班級)
2.給學生表中添加10條數據,逐漸自動增長
3.刪除一條數據
4.修改一條數據
5.按照程式將序查詢
6.統計所有的總成績和平均成績
7.統計不同班級的總成績和平均成績