Java程式設計基礎31——MySql資料庫

一.關係型資料庫概述

1.資料庫定義和特點

• 關係型資料庫：是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的資料。
• RDBMS即關聯式資料庫管理系統(Relational Database Management System)的特點：
  1.資料以表格的形式出現
  2.每行為各種記錄名稱
  3.每列為記錄名稱所對應的資料域
  4.許多的行和列組成一張表單
  5.若干的表單組成database

2.RDBMS 術語

• 資料庫: 資料庫是一些關聯表的集合。
• 資料表: 表是資料的矩陣。在一個資料庫中的表看起來像一個簡單的電子表格。
• 列: 一列(資料元素) 包含了相同的資料, 例如郵政編碼的資料。
• 行：一行（=元組，或記錄）是一組相關的資料，例如一條使用者訂閱的資料。
• 冗餘：儲存兩倍資料，冗餘降低了效能，但提高了資料的安全性。
• 主鍵：主鍵是唯一的。一個資料表中只能包含一個主鍵。你可以使用主鍵來查詢資料。
• 外來鍵：外來鍵用於關聯兩個表。
• 複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
• 索引：使用索引可快速訪問資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
• 參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證資料的一致性。

3.MySQL資料庫

概念

• MySQL 是一個關係型資料庫管理系統，由瑞典 MySQL AB 公司開發，目前屬於 Oracle 公司。MySQL 是一種關聯資料庫管理系統，關聯資料庫將資料儲存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

特點

• MySQL 是開源的，所以你不需要支付額外的費用。
• MySQL 支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。
• MySQL 使用標準的SQL資料語言形式。
• MySQL 可以執行於多個系統上，並且支援多種語言。這些程式語言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
• MySQL 對PHP有很好的支援，PHP是目前最流行的Web開發語言。
• MySQL 支援大型資料庫，支援5000萬條記錄的資料倉儲，32位系統表檔案最大可支援4GB，64位系統支援最大的表檔案為8TB。
• MySQL 是可以定製的，採用了GPL協議，你可以修改原始碼來開發自己的 MySQL 系統。

安裝

• a.解壓後在該資料夾下建立 my.ini 配置檔案，編輯 my.ini 配置以下基本資訊：

[mysql]
# 設定mysql客戶端預設字符集
default-character-set=utf8
 
[mysqld]
# 設定3307埠
port = 3307
# 設定mysql的安裝目錄
basedir=E:MySQLmysql-8.0.12

# 設定mysql資料庫的資料的存放目錄
datadir=E:MySQLsqldata
# 允許最大連線數
max_connections=20
# 服務端使用的字符集預設為8位元編碼的latin1字符集
character-set-server=utf8
# 建立新表時將使用的預設儲存引擎
default-storage-engine=INNODB
# 預設使用“mysql_native_password”外掛認證
default_authentication_plugin=mysql_native_password
[mysql]
# 設定mysql客戶端預設字符集
default-character-set=utf8
[client]
# 設定mysql客戶端連線服務端時預設使用的埠
port=3307
default-character-set=utf8

• b：初始化資料庫：使用管理員開啟cmd，在 bin 目錄下執行命令：mysqld –initialize –console
• c：安裝服務：mysqld –install [服務名]
• d：啟動服務：net start mysql，net stop mysql停止服務，通過命令sc delete MySQL/mysqld -remove解除安裝 MySQL 服務
• e：登陸資料庫：mysql -u root -p 輸入上面初始化生成的密碼。
• f：更改密碼：ALTER USER `root`@`localhost` IDENTIFIED WITH mysql_native_password BY `新密碼`;

二.MYSQL的SQL語言

• SQL：Structure Query Language結構化查詢語言
• DDL:資料定義語言，定義資料庫、資料表它們的結構：create(建立) drop(刪除) alter(修改)
• DML:資料操作語言，主要是用來運算元據insert(插入) update(修改) delete(刪除)
• DCL:資料控制語言，定義訪問許可權，取消訪問許可權，安全設定grant
• DQL:資料查詢語言，select(查詢) from字句 where字句

三.資料庫CRUD的操作

 --1.首先要登陸資料庫伺服器
    mysql -uroot -p123456
--2.資料庫的建立 : 
    create database 資料庫的名 character set 字符集  collate 校對規則
    create database demodb character set utf8 collate utf8_bin;
--3.資料庫的查詢:  
    show databases;
    select database();    //正在使用的
    show create database 資料庫的名字;
--4.資料庫的修改: 
    alter database 資料庫 character set 字符集(utf8);
--5.資料庫的刪除: 
    drop database 資料庫名
--6.切換資料庫 :
    use 資料庫的名字;

四.表的操作

1.建立表

create table 表名（
    列名 列的型別(長度) 約束，
    sid int primary key auto_increment,  
    sname varchar(30),
    sex int,
    age int
）;

--列的型別：
    int
    char（固定長度）/varchar（可變長度），長度代表的是字元的個數
    double
    date:YYYY-MM-DD
    time:hh:mm:ss    預設值是null
    timestamp:YYYY-MM-DD hh:mm:ss預設使用當前時間   
    text:主要是存放文字
    blob:存放的是二進位制
                
--列的約束：
    主鍵約束：primary key
    唯一約束：unique
    非空約束：not null  
    自動增長: auto_increment

2.檢視錶

--檢視所有的表
    show tables;
--檢視錶的定義
    show create table 表名;
--檢視錶結構
    desc 表名;

3.修改表

--修改表名和字符集
    rename table 舊錶名 to 新表名;
    alter table 表名 character set gbk;
--修改列
    新增列: alter table 表名 add 列名 列的型別 列的約束(not null);
    刪除列: alter table 表名 drop chengji;
    改型別: alter table 表名 modify 列名 新的型別 新的約束(not null);
    改列名: alter table 表名 change 舊列名 新列名 varchar(2);

4.刪除表

drop table allidea;

五.表中資料的CRUD的操作

1.增刪改資料

--1.插入資料
    insert into 表名(列名1,列名2,列名3) values(值1,值2,值3),(值1,值2,值3); //全插入時列名可以省略
    --中文亂碼問題
        暫停mysql服務,在mysql安裝路徑中找到my.ini配置檔案，將57行改成gbk，重啟mysql.
--2.刪除記錄
    delete from 表名 where 條件;
    delete from demodb where sid = 5;
    --delete和truncate 刪除資料有什麼差別
        delete: DML一條一條刪除表中的資料，適合資料少，高效
        truncate:DDL 先刪除表再重建表，適合資料比較多
--檢視錶中的資料
    select * from allidea;
--3.更新表記錄
    update 表名 set 列名1=列的值1，列名2=列的值2 where 條件;
    update student set sname=`李四` where sid=5;

4.查詢記錄

• select:選擇顯示哪些列的內容

--商品分類（欄目）
1.分類的id  
2.分類名稱  
3.分類描述    

create table category(      
    cid int primary key auto_increment,      
    cname varchar(12),      
    cdesc varchar(31)
);    
insert into category values(null,`手機數碼`,`中國製造`);  
insert into category values(null,`皮鞋箱包`,`義大利`);  
insert into category values(null,`服裝內衣`,`中國製造`);  
insert into category values(null,`菸酒食品`,`中國製造`); 

--所有商品（貨品）
1.商品ID
2.商品名稱
3.商品價格
4.生產日期
5.商品分類ID

--商品和商品分類：所屬關係
create table product(
    pid int primary key auto_increment,
    pname varchar(10),
    price double,
    pdate timestamp,
    cno int
);

insert into product values(null,`小米手機`,998,null,1);
insert into product values(null,`華為手機`,2888,null,1);
insert into product values(null,`貴人鳥`,399,null,2);
insert into product values(null,`傑克瓊斯`,500,null,3);
insert into product values(null,`海瀾之家`,500,null,3);
insert into product values(null,`茅臺酒`,388,null,4);
insert into product values(null,`西鳳酒`,128,null,4);
insert into product values(null,`餅乾`,5,null,4);

--查詢所有的商品
    select * from product;
    
--查詢商品名稱和商品價格
    select pname,price from product;
    
--別名查詢. as的關鍵字，as 關鍵字是可以省略
    --表別名(主要是用在多表查詢);
        select p.pname,p.price from product as p;
    --列別名;
        select pname 名稱,price 價格 from product;
        
--去掉重複的值 distinct
    select distinct price from product;
    
--select運算查詢(僅僅在查詢結果上做了運算)
    select *,price*0.5 折後價格 from product;
    
--where關鍵字
    --關係運算子:> >= < <= != <>(標準sql語法)
    --判斷某一列是否為空: is null,is not null
    --邏輯運算: and , or , not
    --between ... and ...在某一區間
    --like:模糊查詢
        _ :代表的是一個字元
        % :代表的是多個字元
    --in 在某個範圍中獲得值     
    --查詢商品價格>60的所有商品資訊
        select * from product where price > 60;
    
    --查詢商品價格在10到100之間
        select * from product where price > 10 and price < 100;
        between...add...
        select * from product where price between 10 and 100;
    --查詢商品價格小於 100 或者商品價格大於 999
        select * from product where price < 100 or price > 999;
        
    --查詢出名字中帶有 米 的商品
        select * from product where pname like `%米%`;
    
    --查詢第二個名字是熊的所有商品
        select * from product where pname like `_便%`;
    
    --查詢商品分類ID在1,4,5裡面所有的商品
        select * from product where cno in (1,4);
        
--排序查詢: order by 關鍵字
    asc : ascend 升序(預設的排序方式)
    desc : descend 降序
    --1.查詢所有商品,按照價格進行排序
        select * from product order by price;
    --2.查詢所有商品,按照價格進行降序
        select * from product order by price desc;
    --3.查詢名稱有 小 的商品,按照價格降序排序
        select * from product where pname like `%面%` order by price desc;
        
--聚合函式: select 函式(列名) from 表名;
    sum() : 求和
    avg() : 求平均值
    count() : 統計數量
    max() : 最大值
    min() : 最小值
    --1.獲得所有商品價格的總和
        select sum(price) from product;
        
    --2.獲得所有商品的個數
        select count(*) from product;
        
    --注意:where條件後面不能接聚合函式
    --查出商品價格 大於 平均價格 的所有商品
        select * from product where price > (select avg(price) from product);
--分組: group by
    --having 分組之後條件過濾關鍵字,可以接聚合函式,出現在分組之後
    --1.根據cno欄位分組,分組後統計商品的個數
        select cno,count(*)
        from product group by cno;
    --2.根據con分組,分組統計每組商品的平均價格,並且商品平均價格 > 60
        select cno,avg(price)
        from product group by cno
        having avg(price) > 60;
        
--編寫順序
    select .. from .. where .. group by .. having .. order by
    
--執行順序
    from .. where .. group by .. having .. select .. order by