Java 之 資料庫

標籤：prim   mysql   database   可靠   order by   其他   自訂   拼接   第一個字元   

1.SQL——結構化查詢語言 (SQL)

　　a.分類：①DDL——資料定義語言 (Data Definition Language)

　　　　　　②DQL——資料查詢語言

　　　　　　③DML——資料操作語言

　　　　　　④DCL——資料控制語言

　　b.DDL：包括對資料庫的建立、使用、刪除，對錶的建立、刪除、修改

　　c.DML：包括對錶裡的資料 增、刪、改

　　d.DQL：對錶裡的資料進行各種各樣的查詢

 

 

2.標識符規範：

　　a.第一個字元必須是：字母、底線（_）、at符號（@）、數字記號（#）

　　b.後續字元可以是：字母、數字、貨幣符號（$）、底線（_）、at符號（@）、數字記號（#）

　　c.軟性規定：①表名前加 t_

　　　　　　　　②普通列名前加 f_

　　　　　　　　③主鍵名前加 pk_

　　　　　　　　④外鍵名前加 fk_

 

 

3.資料類型：

　　a.整數資料類型：tinyint（1個位元組）、smallint（2個位元組）、mediumint（3個位元組）、int（4個位元組）、bigint（8個位元組）

　　b.浮點數據類型：float（4位元組）、double（8位元組）、decimal（m，d）（17位元組）、 numeric（M,D）（17位元組）

　　　　註：M為總位元，D為小數位，M必須大於D

　　c.字串資料型別：char（N）0-255、 Varchar（N）0-65535

　　d.位元據類型：tinyblob （255）、 blob （65k）、mediumblob （16M）、longblob （4G）

　　e.邏輯資料類型：boolean （0-1）

　　f.日期資料類型：year（1個位元組）1901-2155、date（3個位元組） 1000-9999、timesamp（4個位元組） 1970-2038、datatime（8個位元組） 1000-9999

 

 

4.對資料庫的操作：

　　a.建立：

create database <庫名>

　　b.使用：

use <庫名>

　　c.刪除：

drop database <庫名>

 

5.對錶的操作：

　　a.建立表：

create table <表名> （<列名> <列的資料類型> [<列的約束>]）;

　　　　註：列與列之間用“，”分隔

　　b.刪除表：

drop table <表名>

　　c.複製表：

create table <表名2> select * from <表名1>

　　d.修改表

alter table <表名> add <列名> <列資料類型> ;        //添加列alter table <表名> change <舊列名> <新列名> <列資料類型> ;        //修改列alter table <表名> drop column <列名>;        //刪除列

 

 

6.表關係：

　　a.一對一關聯性

　　b.一對多關聯性

　　c.多對多關係

 

7.約束：

　　a.作用：保證資料庫的完整性

　　　　資料可靠性 + 資料準確性 = 資料完整性

　　b.分類：實體完整性（行）、值域完整性（列）、參考完整性（外鍵）、使用者自訂完整性（自訂）

　　c.逐漸約束：不可為空，也不能重複

<主鍵名> int primary key;primary key (<主鍵名>);

　　d.唯一約束：UNIQUE

　　e.預設約束：DEFAULT

　　f.非空約束：NO NULL

　　g.檢查約束：CHECK（MySQL不支援）

　　h.外鍵約束：如果要給表添加外鍵，外鍵約束必須為INNODB類型(最後加ENGINE = INNODB)

<外鍵名> int reference <表名> (<主鍵名>);constrant foreign key (<外鍵名>) reference <表名> (<主鍵名>);

 

 

8.DML——資料操作語言

　　a.添加：insert

insert into <表名> (<列名列表>) values (<值列表>);insert into <表名> (<列名列表>) select <select語句>;insert into <表名> values (<值列表1>),(<值列表2>),(<值列表3>);        //僅在MYSQL中使用

　　b.修改：update

update <表名> set <列1> = <新值1>, <列2> = <數值2> where <過濾條件>;update t_student set f_name = 20 where pk_id > 2;

　　c.刪除(可恢複)：delete

delete from <表名> where <過濾條件>;

　　d.刪除(不可恢複)：truncate

truncate table <表名>;

 

 

9.DQL——資料查詢語言

　　a.完全形態：

select [列]from 表名[where 過濾條件][order by 列1, 列2] ;

　　b.執行順序：from——where——投影——select——order by

　　c.表首碼：<表名>.<列名>

t_team.f_teamName

　　e.列別名：<列名> AS <自定標識符>

select f_name as N, f_age as A from t_student as S;

　　　　　作用：多在多表同名是使用，易於區別

　　f.計算資料行

　　　　①自增：

select f_teamNum + 5 from t_team;

　　　　②字串拼接：

select concat (姓名, ‘_‘, 年齡) from t_student;

　　g.排除重複資料——distinct關鍵字

select distinct 列1 from 表名;

　　h.返回限定行數的查詢——limit關鍵字（僅在MYSQL中使用）

select 列1, 列2 from 表名 limit 開始序號, 返回行數;

　　i.選擇操作——組合where條件

　　　　①and：並且，相當於&&

　　　　②or：或者，相當於||

　　　　③between—and：兩界限資料範圍之間

　　　　④in(,)/ not in(,)：多選

　　　　⑤like/ not like：模糊查詢

　　　　　　萬用字元：‘_‘：單個字元

　　　　　　　　　　‘%‘：任意多個字元

　　　　⑥is null/ is not null：判斷是否非空

　　j.排序操作

　　　　①關鍵字：order by

　　　　②單列排序：預設升序。降序：desc；升序：asc

select * from 表名 order by 列名 desc;

　　　　③多列排序：每列之間加逗號（，）根據後面列名的順序確定優先順序

select * from 表名 order by 列1, 列2, 列3;

 

 

10.彙總函式 與 分組

　　a.彙總函式的種類：

　　　　①count：統計行數目

　　　　②sum：擷取單個列的合計值

　　　　③avg：計算某列的平均值

　　　　④max：計算資料行的最大值

　　　　⑤min：計算資料行的最小值

　　b.count:

select count (<計數規範>) from <表名>;

　　　　　　技術規範：①*：所有行（包括null）

　　　　　　　　　　　②all 列：統計指定列非空值行（all 可以省略）

　　　　　　　　　　　③distinct 列：統計指定列唯一非空值行

　　c.sum:

select sum (<計數規範>) from <表名>;

　　　　　　技術規範：①all 列　　②distinct 列

　　d.avg：

select avg (<平均數規範>) from <表名>;

　　　　　　技術規範：①all 列　　②distinct 列

　　　　　　注意：預設小數點後面4位小數

　　e.max/min：

select avg (<列名>) from <表名>;

　　　　　　沒有 *，all，distinct 

 

　　f.資料分組

　　　　①格式：

select 列A, 彙總函式（彙總函式規範） from 表名where 過濾條件group by 列A, 列Bhaving 過濾條件

　　　　②執行順序：from——where——group by——select——having——order by

　　　　③where與having：where在分組前運行，過濾的是from之後的語句，having在分組後運行，過濾的是select後的列表；

　　　　④注意：分組後的投影，只能顯示彙總函式或者分組列，顯示其他列是沒有意義的

 

 

11.子查詢

　　a.定義：嵌套在主查詢中的查詢

select 列A, （子查詢）as 列B from 表名;

　　b.嵌套位置：select/from/where/group by/having/order by

　　c.特點：①select巢狀查詢的值只能是單行單列

　　　　　　②from巢狀查詢的值只能是多行多列

　　　　　　③where巢狀查詢的值只能是單行單列；但 in 之後為單列，可以為單行或者多行

　　d.運算子：

　　　　①all：逐一比較，全部滿足才為真

　　　　②any：逐一比較，只要一條記錄滿足就為真

　　　　③exists/not exists：存在資料為真，不存在資料為假，not exists則相反

　　e.子查詢分類：

　　　　①相互關聯的子查詢：制定依賴於外部查詢的資料

　　　　②非相互關聯的子查詢：獨立於外部查詢的子查詢

 

 

12.表串連

　　a.定義：將多個表聯合在一起實現查詢效果

　　b.關鍵字：join — on

select 列1, 列2 from 表1 join 表2 on 表1.列 = 表2.列;

　　c.與union比較：union 為縱向連結；表聯結 為橫向連結（笛卡爾乘積）

　　d.分類：①內聯結：[inner] join

　　　　　　②外聯結：left join （左外聯結）

　　　　　　　　　　　right join （右外聯結）

　　　　　　　　　　　full join （全外聯結，MYSQL不支援）

　　　　　　③自我聯結：本表與本表聯結

 

Java 之 資料庫