天天看點

「JavaWeb學習記錄02」操作資料庫

#頭條創作挑戰賽#

上一篇記錄:記錄01 資料庫、MySQL、SQL簡介

1.4 DDL:操作資料庫

DDL操作資料庫,主要是對資料庫的增删查操作。

1.4.1 查詢

查詢所有的資料庫

SHOW DATABASES;           

運作上面語句效果如下:

「JavaWeb學習記錄02」操作資料庫

上述查詢到的是的這些資料庫是mysql安裝好自帶的資料庫,以後不要操作這些資料庫。

1.4.2 建立資料庫

  • 建立資料庫:
CREATE DATABASE 資料庫名稱;           

運作語句效果如下:

「JavaWeb學習記錄02」操作資料庫

而在建立資料庫的時候,我并不知道db1資料庫有沒有建立,直接再次建立名為db1的資料庫就會出現錯誤。

「JavaWeb學習記錄02」操作資料庫

為了避免上面的錯誤,在建立資料庫的時候先做判斷,如果不存在再建立。

  • 建立資料庫(判斷,如果不存在則建立)
CREATE DATABASE IF NOT EXISTS 資料庫名稱;           

運作語句效果如下:

「JavaWeb學習記錄02」操作資料庫

從上面的效果可以看到雖然db1資料庫已經存在,再建立db1也沒有報錯,而建立db2資料庫則建立成功。

1. 4.3 删除資料庫

  • 删除資料庫
DROP DATABASE 資料庫名稱;           
  • 删除資料庫(判斷,如果存在則删除)
DROP DATABASE IF EXISTS 資料庫名稱;           

運作語句效果如下:

「JavaWeb學習記錄02」操作資料庫

1.4.4 使用資料庫

資料庫建立好了,要在資料庫中建立表,得先明确在哪兒個資料庫中操作,此時就需要使用資料庫。

  • 使用資料庫
USE 資料庫名稱;           
  • 檢視目前使用的資料庫
SELECT DATABASE();           

運作語句效果如下:

「JavaWeb學習記錄02」操作資料庫

1.5 DDL:操作表

操作表也就是對表進行增(Create)删(Retrieve)改(Update)查(Delete)。

1. 5.1 查詢表

  • 查詢目前資料庫下所有表名稱
SHOW TABLES;           

我們建立的資料庫中沒有任何表,是以我們進入mysql自帶的mysql資料庫,執行上述語句檢視

「JavaWeb學習記錄02」操作資料庫
  • 查詢表結構
DESC 表名稱;           

檢視mysql資料庫中func表的結構,運作語句如下:

「JavaWeb學習記錄02」操作資料庫

1.5.2 建立表

  • 建立表
CREATE TABLE 表名 (
     字段名1  資料類型1,
     字段名2  資料類型2,
     …
     字段名n  資料類型n
 );
            
注意:最後一行末尾,不能加逗号

知道了建立表的語句,那麼我們建立建立如下結構的表

「JavaWeb學習記錄02」操作資料庫
create table tb_user (
     id int,
     username varchar(20),
     password varchar(32)
 );           

運作語句如下:

「JavaWeb學習記錄02」操作資料庫

1.5.3 資料類型

MySQL 支援多種類型,可以分為三類:

  • 數值
  • tinyint : 小整數型,占一個位元組

    int : 大整數類型,占四個位元組

    eg : age int

    double : 浮點類型

    使用格式: 字段名 double(總長度,小數點後保留的位數)

    eg : score double(5,2)

  • 日期
  • date : 日期值。隻包含年月日

    eg :birthday date :

    datetime : 混合日期和時間值。包含年月日時分秒

  • 字元串
  • char : 定長字元串。

    優點:存儲性能高

    缺點:浪費空間

    eg : name char(10) 如果存儲的資料字元個數不足10個,也會占10個的空間

    varchar : 變長字元串。

    優點:節約空間

    缺點:存儲性能底

    eg : name varchar(10) 如果存儲的資料字元個數不足10個,那就資料字元個數是幾就占幾個的空間

注意:其他類型參考資料中的《MySQL資料類型].xlsx》

案例:

需求:設計一張學生表,請注重資料類型、長度的合理性
     1. 編号
     2. 姓名,姓名最長不超過10個漢字
     3. 性别,因為取值隻有兩種可能,是以最多一個漢字
     4. 生日,取值為年月日
     5. 入學成績,小數點後保留兩位
     6. 郵件位址,最大長度不超過 64
     7. 家庭聯系電話,不一定是手機号碼,可能會出現 - 等字元
     8. 學生狀态(用數字表示,正常、休學、畢業...)           

語句設計如下:

create table student (
     id int,
     name varchar(10),
     gender char(1),
     birthday date,
     score double(5,2),
     email varchar(15),
     tel varchar(15),
     status tinyint
 );           

1.5.4 删除表

  • 删除表
DROP TABLE 表名;           
  • 删除表時判斷表是否存在
DROP TABLE IF EXISTS 表名;           

運作語句效果如下:

「JavaWeb學習記錄02」操作資料庫

1.5.5 修改表

  • 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
 
 -- 将表名student修改為stu
 alter table student rename to stu;           
  • 添加一列
ALTER TABLE 表名 ADD 列名 資料類型;
 
 -- 給stu表添加一列address,該字段類型是varchar(50)
 alter table stu add address varchar(50);           
  • 修改資料類型
ALTER TABLE 表名 MODIFY 列名 新資料類型;
 
 -- 将stu表中的address字段的類型改為 char(50)
 alter table stu modify address char(50);           
  • 修改列名和資料類型
ALTER TABLE 表名 CHANGE 列名 新列名 新資料類型;
 
 -- 将stu表中的address字段名改為 addr,類型改為varchar(50)
 alter table stu change address addr varchar(50);           
  • 删除列
ALTER TABLE 表名 DROP 列名;
 
 -- 将stu表中的addr字段 删除
 alter table stu drop addr;           

1. 6 navicat使用

通過上面的學習,我們發現在指令行中寫sql語句特别不友善,尤其是編寫建立表的語句,我們隻能在記事本上寫好後直接複制到指令行進行執行。那麼有沒有剛好的工具提供給我們進行使用呢? 有。

1.6.1 navicat概述

  • Navicat for MySQL 是管理和開發 MySQL 或 MariaDB 的理想解決方案。
  • 這套全面的前端工具為資料庫管理、開發和維護提供了一款直覺而強大的圖形界面。
  • 官網: http://www.navicat.com.cn

1.6.2 navicat安裝

參考 : 資料\navicat安裝包\navicat_mysql_x86\navicat安裝步驟.md

1.6.3 navicat使用

1. 建立和mysql服務的連接配接

第一步: 點選連接配接,選擇MySQL

「JavaWeb學習記錄02」操作資料庫

第二步:填寫連接配接資料庫必要的資訊

「JavaWeb學習記錄02」操作資料庫

以上操作沒有問題就會出現如下圖所示界面:

「JavaWeb學習記錄02」操作資料庫

2. 操作

連接配接成功後就能看到如下圖界面:

「JavaWeb學習記錄02」操作資料庫
  • 修改表結構

通過下圖操作修改表結構:

點選了設計表後即出現如下圖所示界面,在圖中紅框中直接修改字段名,類型等資訊:

「JavaWeb學習記錄02」操作資料庫
「JavaWeb學習記錄02」操作資料庫
  • 編寫SQL語句并執行

按照如下圖所示進行操作即可書寫SQL語句并執行sql語句。

「JavaWeb學習記錄02」操作資料庫

1.7 DML

DML主要是對資料進行增(insert)删(delete)改(update)操作。

1.7.1 添加資料

  • 給指定列添加資料
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);           
  • 給全部列添加資料
INSERT INTO 表名 VALUES(值1,值2,…);           
  • 批量添加資料
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
 INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;           
  • 練習

為了示範以下的增删改操作是否操作成功,故先将查詢所有資料的語句介紹給大家:

select * from stu;           
-- 給指定列添加資料
 INSERT INTO stu (id, NAME) VALUES (1, '張三');
 -- 給所有列添加資料,列名的清單可以省略的
 INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);
 
 INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);
 
 -- 批量添加資料
 INSERT INTO stu VALUES 
     (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1),
     (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1),
     (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);           

1.7.2 修改資料

  • 修改表資料
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 條件] ;           

注意:

修改語句中如果不加條件,則将所有資料都修改!

像上面的語句中的中括号,表示在寫sql語句中可以省略這部分

  • 練習
    • 将張三的性别改為女
    • update stu set sex = '女' where name = '張三';
    • 将張三的生日改為 1999-12-12 分數改為99.99
    • update stu set birthday = '1999-12-12', score = 99.99 where name = '張三';
    • 注意:如果update語句沒有加where條件,則會将表中所有資料全部修改!
    • update stu set sex = '女';
    • 上面語句的執行完後查詢到的結果是:
「JavaWeb學習記錄02」操作資料庫

1.7.3 删除資料

  • 删除資料
DELETE FROM 表名 [WHERE 條件] ;           
  • 練習
-- 删除張三記錄
 delete from stu where name = '張三';
 
 -- 删除stu表中所有的資料
 delete from stu;           

1.8 DQL

下面是展示試題庫資料的頁面

「JavaWeb學習記錄02」操作資料庫

頁面上展示的資料肯定是在資料庫中的試題庫表中進行存儲,而我們需要将資料庫中的資料查詢出來并展示在頁面給使用者看。上圖中的是最基本的查詢效果,那麼資料庫其實是很多的,不可能在将所有的資料在一頁進行全部展示,而頁面上會有分頁展示的效果,如下:

「JavaWeb學習記錄02」操作資料庫

當然上圖中的難度字段當我們點選也可以實作排序查詢操作。從這個例子我們就可以看出,對于資料庫的查詢時靈活多變的,需要根據具體的需求來實作,而資料庫查詢操作也是最重要的操作,是以此部分需要大家重點掌握。

接下來我們先介紹查詢的完整文法:

SELECT 
     字段清單
 FROM 
     表名清單 
 WHERE 
     條件清單
 GROUP BY
     分組字段
 HAVING
     分組後條件
 ORDER BY
     排序字段
 LIMIT
     分頁限定           

為了給大家示範查詢的語句,我們需要先準備表及一些資料:

-- 删除stu表
 drop table if exists stu;
 
 
 -- 建立stu表
 CREATE TABLE stu (
  id int, -- 編号
  name varchar(20), -- 姓名
  age int, -- 年齡
  sex varchar(5), -- 性别
  address varchar(100), -- 位址
  math double(5,2), -- 數學成績
  english double(5,2), -- 英語成績
  hire_date date -- 入學時間
 );
 
 -- 添加資料
 INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) 
 VALUES 
 (1,'馬運',55,'男','杭州',66,78,'1995-09-01'),
 (2,'馬花疼',45,'女','深圳',98,87,'1998-09-01'),
 (3,'馬斯克',55,'男','香港',56,77,'1999-09-02'),
 (4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
 (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
 (6,'劉德花',57,'男','香港',99,99,'1998-09-01'),
 (7,'張學右',22,'女','香港',99,99,'1998-09-01'),
 (8,'德瑪西亞',18,'男','南京',56,65,'1994-09-02');           

接下來咱們從最基本的查詢語句開始學起。

1.8.1 基礎查詢

1. 文法

  • 查詢多個字段
SELECT 字段清單 FROM 表名;
 SELECT * FROM 表名; -- 查詢所有資料           
  • 去除重複記錄
SELECT DISTINCT 字段清單 FROM 表名;           
  • 起别名
AS: AS 也可以省略           

2. 練習

  • 查詢name、age兩列
  • select name,age from stu;
  • 查詢所有列的資料,列名的清單可以使用*替代
  • select * from stu;
  • 上面語句中的*不建議大家使用,因為在這寫*不友善我們閱讀sql語句。我們寫字段清單的話,可以添加注釋對每一個字段進行說明
  • 而在上課期間為了簡約課程的時間,老師很多地方都會寫*。
  • 查詢位址資訊
  • select address from stu;
  • 執行上面語句結果如下:
  • 從上面的結果我們可以看到有重複的資料,我們也可以使用 distinct 關鍵字去重重複資料。
  • 去除重複記錄
  • select distinct address from stu;
  • 查詢姓名、數學成績、英語成績。并通過as給math和english起别名(as關鍵字可以省略)
  • select name,math as 數學成績,english as 英文成績 from stu;

    select name,math 數學成績,english 英文成績 from stu;

1.8.2 條件查詢

1. 文法

SELECT 字段清單 FROM 表名 WHERE 條件清單;           
  • 條件

條件清單可以使用以下運算符

2. 條件查詢練習

  • 查詢年齡大于20歲的學員資訊
  • select * from stu where age > 20;
  • 查詢年齡大于等于20歲的學員資訊
  • select * from stu where age >= 20;
  • 查詢年齡大于等于20歲 并且 年齡 小于等于 30歲 的學員資訊
  • select * from stu where age >= 20 && age <= 30;

    select * from stu where age >= 20 and age <= 30;

  • 上面語句中 && 和 and 都表示并且的意思。建議使用 and 。
  • 也可以使用 between ... and 來實作上面需求
  • select * from stu where age BETWEEN 20 and 30;
  • 查詢入學日期在'1998-09-01' 到 '1999-09-01' 之間的學員資訊
  • select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';
  • 查詢年齡等于18歲的學員資訊
  • select * from stu where age = 18;
  • 查詢年齡不等于18歲的學員資訊
  • select * from stu where age != 18;

    select * from stu where age <> 18;

  • 查詢年齡等于18歲 或者 年齡等于20歲 或者 年齡等于22歲的學員資訊
  • select * from stu where age = 18 or age = 20 or age = 22;

    select * from stu where age in (18,20 ,22);

  • 查詢英語成績為 null的學員資訊
  • null值的比較不能使用 = 或者 != 。需要使用 is 或者 is not
  • select * from stu where english = null; -- 這個語句是不行的

    select * from stu where english is null;

    select * from stu where english is not null;

3. 模糊查詢練習

模糊查詢使用like關鍵字,可以使用通配符進行占位:

(1)_ : 代表單個任意字元

(2)% : 代表任意個數字元

  • 查詢姓'馬'的學員資訊
  • select * from stu where name like '馬%';
  • 查詢第二個字是'花'的學員資訊
  • select * from stu where name like '_花%';
  • 查詢名字中包含 '德' 的學員資訊
  • select * from stu where name like '%德%';

1.8.3 排序查詢

1. 文法

SELECT 字段清單 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;           

上述語句中的排序方式有兩種,分别是:

  • ASC : 升序排列 (預設值)
  • DESC : 降序排列
注意:如果有多個排序條件,目前邊的條件值一樣時,才會根據第二條件進行排序

2. 練習

  • 查詢學生資訊,按照年齡升序排列
  • select * from stu order by age ;
  • 查詢學生資訊,按照數學成績降序排列
  • select * from stu order by math desc ;
  • 查詢學生資訊,按照數學成績降序排列,如果數學成績一樣,再按照英語成績升序排列
  • select * from stu order by math desc , english asc ;

1.8.4 聚合函數

1. 概念

将一列資料作為一個整體,進行縱向計算。

如何了解呢?假設有如下表

「JavaWeb學習記錄02」操作資料庫

現有一需求讓我們求表中所有資料的數學成績的總和。這就是對math字段進行縱向求和。

2. 聚合函數分類

函數名 功能
count(列名) 統計數量(一般選用不為null的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值

3. 聚合函數文法

SELECT 聚合函數名(列名) FROM 表;           
注意:null 值不參與所有聚合函數運算

4. 練習

  • 統計班級一共有多少個學生
  • select count(id) from stu;

    select count(english) from stu;

  • 上面語句根據某個字段進行統計,如果該字段某一行的值為null的話,将不會被統計。是以可以在count(*) 來實作。* 表示所有字段資料,一行中也不可能所有的資料都為null,是以建議使用 count(*)
  • select count(*) from stu;
  • 查詢數學成績的最高分
  • select max(math) from stu;
  • 查詢數學成績的最低分
  • select min(math) from stu;
  • 查詢數學成績的總分
  • select sum(math) from stu;
  • 查詢數學成績的平均分
  • select avg(math) from stu;
  • 查詢英語成績的最低分
  • select min(english) from stu;

1.8.5 分組查詢

1. 文法

SELECT 字段清單 FROM 表名 [WHERE 分組前條件限定] GROUP BY 分組字段名 [HAVING 分組後條件過濾];           
注意:分組之後,查詢的字段為聚合函數和分組字段,查詢其他字段無任何意義

2. 練習

  • 查詢男同學和女同學各自的數學平均分
  • select sex, avg(math) from stu group by sex;
  • 注意:分組之後,查詢的字段為聚合函數和分組字段,查詢其他字段無任何意義
  • select name, sex, avg(math) from stu group by sex; -- 這裡查詢name字段就沒有任何意義
  • 查詢男同學和女同學各自的數學平均分,以及各自人數
  • select sex, avg(math),count(*) from stu group by sex;
  • 查詢男同學和女同學各自的數學平均分,以及各自人數,要求:分數低于70分的不參與分組
  • select sex, avg(math),count(*) from stu where math > 70 group by sex;
  • 查詢男同學和女同學各自的數學平均分,以及各自人數,要求:分數低于70分的不參與分組,分組之後人數大于2個的
  • select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;

where 和 having 差別:

  • 執行時機不一樣:where 是分組之前進行限定,不滿足where條件,則不參與分組,而having是分組之後對結果進行過濾。
  • 可判斷的條件不一樣:where 不能對聚合函數進行判斷,having 可以。

1.8.6 分頁查詢

如下圖所示,很多網站都見過類似的效果,如京東、百度、淘寶等。分頁查詢是将資料一頁一頁的展示給使用者看,使用者也可以通過點選檢視下一頁的資料。

「JavaWeb學習記錄02」操作資料庫

1. 文法

SELECT 字段清單 FROM 表名 LIMIT  起始索引 , 查詢條目數;           
注意: 上述語句中的起始索引是從0開始

2. 練習

  • 從0開始查詢,查詢3條資料
  • select * from stu limit 0 , 3;
  • 每頁顯示3條資料,查詢第1頁資料
  • select * from stu limit 0 , 3;
  • 每頁顯示3條資料,查詢第2頁資料
  • select * from stu limit 3 , 3;
  • 每頁顯示3條資料,查詢第3頁資料
  • select * from stu limit 6 , 3;

從上面的練習推導出起始索引計算公式:

起始索引 = (目前頁碼 - 1) * 每頁顯示的條數           

繼續閱讀