數據庫

?數據庫（DB）是存放數據的倉庫，只不過這些數據的存在有一定的關聯漓滔，并且按照一定的格式存放在計算機上面编饺，這些數據包含數字，文本响驴，圖像透且，音頻，視頻等多種格式豁鲤。MySQL同Oracel,SQL Server一樣是關系型數據庫秽誊，是開放源碼的關系型數據庫，關聯數據庫將數據保存在不同的表中琳骡，而不是將所有數據放在一個大倉庫內锅论，這樣就增加了速度并提高了靈活性。目前被廣泛應用于Internet上的中小型網站中楣号。

數據庫連接方式

?數據庫的連接方式有多種最易，ODBC，ADO,ADO.NET,JDBC都可以連接數據庫炫狱，這里以JDBC的連接為例藻懒。
?通過JDBC對數據庫的訪問包括4個主要的組件：Java應用程序，JDBC驅動管理器视译，驅動器和數據源嬉荆。
?使用JDBC接口對數據庫的操作有以下的幾個優(yōu)點：
?(1) 、有利于用戶理解酷含。
?(2) 鄙早、使變成人員從復雜的驅動器調用命令和函數中解脫出來，從而致力于應用程序功能的實現第美。
?(3) 蝶锋、JDBC支持不同的關系數據庫陆爽，增強了程序的可移植性什往。
?缺點：訪問數據記錄的速度有一定影響。
e.g.
JDBC連接數據庫,連接的數據庫是postgresql慌闭，如果連接MySQL數據庫别威，把驅動改成MySQL對應的驅動就行了躯舔。不要忘記的一點就是一定要引入數據庫的驅動包。

image.png

package ConnectJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Postgresqljdbc {

    public static void main(String[] args) {
         Connection connection = null;  
            Statement statement = null;  
            try {  
                
                Class.forName("org.postgresql.Driver");  
                connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "123456");  
      
                statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("select * from test_table");
                
                while(resultSet.next()){
                     System.out.println(resultSet.getString(1));
                }
                
                resultSet.close();
                statement.close();  
                connection.close();  
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }  

    }

}

數據庫表的操作

?說明：MySQL數據庫的語句不用區(qū)分大小寫省古，[]括號里面的內容選填
?? MySQL安裝之后粥庄，系統(tǒng)自動的創(chuàng)建了information_schema 和mysql 數據庫, MySQL把有關的數據庫信息存儲在這兩個數據庫中，如果刪除了這些數據庫豺妓，MySQL將不能正常工作惜互。

創(chuàng)建數據庫

CREATE DATABASE [IF NOT EXISTS] base_name;

使用數據庫

USE base_name;

修改數據庫

ALTER DATABASE base_name;
用戶必須對數據庫有修改的權限，才能歲數據庫使用ALTER DATABASE命令

刪除數據庫

DROP DATABASE [IF EXISTS] base_name;
使用該命令的時候一定要小心琳拭，它刪除的是整個數據庫训堆，包括數據庫中的表將會永久被刪除。

創(chuàng)建數據表

CREATE [TEMPORARY] TABLE table_name(column1,column2 , column3,.....);

TEMPORARY:使用TEMPORARY關鍵字表示創(chuàng)建的表為臨時表白嘁，當斷開與該數據庫的連接的時候坑鱼，MySQL會自動刪除它們。

對于表格列column1的書寫格式 col_name type [NOT NULL][...]絮缅，type是指列字段的類型鲁沥，比如int，varchar類型的耕魄，對于[]括號里面有一下的關鍵字來標注列的屬性：

• DEFAULT defalut_value:為列指定默認值画恰，且默認值為一個常數，0吸奴，1等
• NOT NULL：非空約束
• AUTO_INCREMENT :設置自增屬性阐枣，從1開始，如果沒有申明奄抽，則默認值為0蔼两，且每個表都只能有一個AUTO_INCREMENT標記的列，并且他們必須被索引逞度，比如表的ID额划。
• UNIQUE KEY:唯一約束。唯一約束的列值可以為空,并且一張表中可以有多個UNIQUE約束档泽。
• PRIMARY KEY:主鍵約束俊戳，一個表只能有一個主鍵約束，并且主鍵一定要為NOT NULL馆匿，通常用來定義標志列抑胎。
• FOREIGN KEY：外鍵約束,對應的字段只能是主鍵或者唯一約束修飾的字段。有外鍵約束在渐北，插入數據時阿逃，先插入主表中的數據，再插入從表中的數據。刪除數據時恃锉，先刪除從表中的數據搀菩，再刪除主表中的數據。

1破托、主鍵約束
?創(chuàng)建主鍵約束

①肪跋、create table people(
id INTEGER NOT NULL,
num INTEGER NOT NULL,
name VARCHAR,
sex VARCHAR,
phnone_num BIGINT,
PRIMARY KEY(id)
);
②、create table people(
id INTEGER NOT NULL PRIMARY KEY,
num INTEGER NOT NULL,
name VARCHAR,
sex VARCHAR,
phnone_num BIGINT
);

?刪除主鍵約束

alter table people drop primary key;

?添加主鍵約束

alter table people add primary key(id);

2土砂、唯一鍵約束

//創(chuàng)建唯一鍵約束,創(chuàng)建表時創(chuàng)建唯一約束
create table people(
id INTEGER NOT NULL,
num INTEGER NOT NULL,
name VARCHAR,
sex VARCHAR,
phnone_num BIGINT,
PRIMARY KEY(id)州既，
UNIQUE(num)
);
//創(chuàng)建表后創(chuàng)建唯一約束
ALTER TABLE people ADD UNIQUE(num);

修改數據表

ALTER TABLE table_name 表操作

表操作

ADD[COLUM] : 向表中添加新列。例如：ALTER TABLE table_test ADD name char(10) NOT NULL;

RENAME : 修改表名萝映，例如;ALTER TABLE old_test RENAME TO new_test;

CHANGE : 修改列名易桃。例如：ALTER TABLE table_test CHANGE old_name new_name char(10);

MODIFY[COLUMN] : 修改指定列的類型。例如：ALTER TABLE table_test MODIFY b BIGINT NOT NULL;
DROP:刪除列或者約束锌俱。例如：ALTER TABLE new_table DROP new_name;
...

修改表名還可以用該方法:

RENAME TABLE old_table TO new_table,old_table1 TO new_table1,....;晤郑。該命令可以同時修改多張表，中間以逗號隔開贸宏。

復制表造寝，使用LIKE關鍵字，可以創(chuàng)建一個與原表相同的新表吭练，但是內容不會被復制诫龙。

CREATE TABLE new_table LIKE old_table;
如果你想要復制表結構并且連內容一起復制，則：
CREATE TABLE new_table AS (SELECT * FROM old_table);

刪除表

DROP TABLE table_name;

插入表數據

INSERT INTO table_name(column1,column2) VALUES (values1,values2), (values1,values2),(......);
該命令適合同時插入多條數據鲫咽。
插入單條數據：INSERT INTO table_name SET column1 = 'values1', column2 = 'values2';

不僅可以通過INSERT語句插入表數據签赃，還可以通過replace語句插入數據，但是與INSERT不同的是REPLACE語句在插入數據之前會將與新數據沖突的舊數據刪除分尸。比如主鍵值相同锦聊，使用INSERT語句將無法插入新數據。

刪除表數據

DELETE FROM table_name [WHERE ...],用于刪除表數據箩绍，WHERE子句后面跟的是刪除表數據的條件孔庭，如果不寫WHERE子句，將會刪除表中的所有數據材蛛。

TRUNCATE 語句也可以刪除表數據圆到，它相當于不帶WHERE子句的DELETE語句，但是唯一不同的是TRUNCATE語句的刪除速度比DELETE語句快卑吭，并且刪除了表中的數據無法恢復芽淡，因此使用的時候要小心。TRUNCATE TABLE table_name;

修改表數據

UPDATE table_name SET column1 = 'new_values1',column2= 'new_values2' [WHERE ...];

數據庫查詢
?使用SELECT語句可以實現對表的選擇豆赏，投影以及連接操作挣菲。

SELECT [ALL | DISTICT]
FROM table_name
[WHERE] .... WHERE 子句
[GROUP BY] ... GROUP BY子句 ?對字段進行分組
[HAVING ]... HAVING子句 ?與where子句用法類似富稻，但是having子句可以使用聚合函數
[ORDER BY ]... ORDER BY子句?對查詢數據進行排序
[LIMIT]... LIMIT子句?限制查詢的數據

選擇列以及給列定義別名查詢
?如果不想查詢整張表格的數據，只想查詢固定幾列的數據己单，那么可以將要查詢的列名寫在SELECT 關鍵字后面，并且可以使用AS關鍵字給查詢出來的列命名耙饰。不允許在WHERE子句中使用別名

SELECT column1 AS '序號',column2 AS '姓名' FROM table_name;

將查詢出來的結果進行替換
&emsp纹笼；很多時候我們使用查詢語句，并不是想要的到查詢出來的數據苟跪，而是想獲得查詢出來的數據背后的意義廷痘。就比如在班級考試中，90分以上的成績?yōu)閮?yōu)秀件已，80到90分為一般笋额。當這個時候我們就會用到CASE關鍵字。

CASE
WHEN 條件1 THEN 表達式1
WHEN 條件2 THEN 表達式2
...
ELSE 表達式
END

?此時的表達式表達的就如Java中的if()...else if()..else if ()...else{}語句一樣篷扩。
?查詢student表兄猩，總分在90以上的同學的姓名，學號鉴未，標記為優(yōu)秀,總分在80到90的標記為一般枢冤，其余的為及格。

SELECT num,name,
CASE
WHEN score > 90 THEN '優(yōu)秀'
WHEN score > 80 AND score < 90 THEN '一般'
ELSE '及格'
END AS '等及'
FROM student

數據去重復

SELECT DISTIC name FROM student;

聚合函數
?聚合函數常常用于對一組數值進行計算铜秆，然后返回單一的值淹真，比如COUNT,MAX,SUM等，聚合函數通常和GROUP BY 子句一起使用连茧。

多表連接查詢
?多表連接查詢的連接方式有兩種，全連接和JOIN連接

• 全連接
  ?全連接是指將各個表用逗號分隔啸驯，在FROM子句產生的中間結果是一張表客扎，如果不規(guī)定查詢的列，那么新表中的列就包含了所有表中出現的列罚斗。

SELECT student.num,student.name score.project,score.score
FROM student,score
WHERE score.id = student.ship_id;

?此時查詢出來的是一張新表虐唠，新表的列為student.num,student.name score.project,score.score 。

• JOIN連接
  ?JOIN連接主要分為3種：
  ?(1) . 內連接惰聂，使用INNER關鍵字的連接

SELECT student.num,student.name score.project,score.score
FROM student INNER JOIN score
ON (score.id = student.ship_id);

?其中ON后面跟的條件是用于連接兩張表的條件疆偿，如果不屬于連接兩張表的條件，則可以用WHERE子句搓幌。如果沒有寫明查詢的列杆故，則將會查詢要查詢表的所有列，兩張表的列直接是拼接到列后面溉愁。查詢出來的是兩張表共有的部分处铛。

?(2) . 外連接饲趋，使用OUTTER關鍵字的連接。外連接包括
???-左外鏈接撤蟆，除了查詢出來的共有部分奕塑，還包括了左表中有的行但右表中不匹配的，右表中設置為NULL;

select * from depart a left join student b on a.id=b.deptId;

???-右外連接家肯，除了查詢出來的共有部分龄砰，還包括了右表中有的行但左表中不匹配的，左表中設置為NULL;

select * from depart a right join student b on a.id=b.deptId;

???-自然連接

其中的OUTER關鍵字均可省略

比較運算符
?比較運算符用于比較兩個表達式的值讨衣，MySQL的比較運算符有：>,<,=,<=,>=,<>(不等于)换棚，!=.

select * from student
where age <18;

通配符匹配符
?LIKE,用于將字符串與指定的字符串想匹配，返回TURE或FALSE反镇。當使用LIKE進行匹配時固蚤，會用到“_”和"%"這兩個特殊符號進行模糊查詢。

select * from student
where name like "%王"; ?查詢student表中歹茶，姓王的學生夕玩，%表示匹配0個或多個字符，即表示姓名為“王”惊豺，“王X”,"王XX"等的數據都將會被查詢出來风秤。

select * from student
where name like "%琪_"; ?查詢student表中，姓名的倒數第二個字為“琪”的數據扮叨，
_ 表示匹配一個字符缤弦，即表示姓名為“琪X”，“X琪X”,"XX琪X"等的數據都將會被查詢出來彻磁。

正則表達式匹配符
?REGEXP是正則表達式的的縮寫碍沐。REGEXP的特殊字符以及含義表。

select * from student
where num REGEXP '[010]'; ?匹配學號中出現010的數據衷蜓。

子查詢
?在查詢條件中可以用用另一條查詢結果做為查詢的一個條件，這時候可以用IN關鍵字來作為連接的橋梁磁浇。

select name from student
where score IN (
??select score from score
??where score>80
);???該段語句表示的是查詢成績在80分以上的學生的姓名斋陪。

IN子查詢自能返回一列數據，對于比較復雜的查詢可以使用嵌套查詢

視圖

?視圖是數據庫的視圖置吓，與數據庫中的表不同的是无虚，視圖是一個虛擬表，表的內容由查詢的結果而定義的衍锚，創(chuàng)建時圖的主要原因是為了在復雜的查詢中提高查詢效率友题。通常情況下，當一段SQL的查詢結果使用的很平凡需要用來當做子查詢的時候以及當一張數據結構表很復雜戴质，而我們只需要其中的一些數據的時候會創(chuàng)建視圖度宦。

視圖與表的關系
(1) . 視圖是表的查詢結果踢匣，如果表的數據變了，會影響視圖的結果戈抄。
(2) . 視圖的增刪改也會影響表,但視圖并不總是能增刪改的离唬；
(3) . 視圖的數據與表的數據一一對應時可以修改；
對于視圖的insert還應注意：視圖必須包含表中沒有默認值的列划鸽。

e.g.

create view v_student as select num,name,sex from student with check option;
select * from v_student;
select phnone_num from student where name in (select name from v_student where name='琪琪')崭捍；
INSERT INTO v_student(num,name,sex) VALUES(0001,'柴油','男');

?以上的例子可以看出，視圖一旦創(chuàng)建完畢啰脚，就可以像一個普通表那樣使用殷蛇，主要用來查詢。
? 如果要自定義視圖列名橄浓，請注意視圖名后面的列的數量必須匹配select子句中的列的數量粒梦。如果不定義視圖列名，則默認和select表的列名一致荸实。
?在創(chuàng)建有條件限制的視圖的時候匀们，語句后加上with check option; 語句可以保證數據的安全性。即當你想要更新視圖的時候准给，如果更新的內容和創(chuàng)建視圖的條件沖突就不會更新成功，保證數據安全性。

?在更新視圖的時候應該注意有下列任一結構宴偿，視圖不能更新：
?(1) . 聚合函數含长。
?(2) . distinct關鍵字
?(3) . group by子句
?(4) . order by子句
?(5) . union 、union all等集合運算符
?(6) . from子句中包含多個表
?(7) . where子句中包含相關子查詢
?(8) . 如果視圖中有計算列畔规，則不能更新
?(9) . 如果基表中有某個具有非空約束的列未出現在視圖定義中局扶，則不能做insert操作

--創(chuàng)建表
create table student(
    num INTEGER,
    name VARCHAR,
    sex VARCHAR,
    phnone_num BIGINT
);

-- 刪除表
drop TABLE student;


--插入表數據
insert into student (num,name,sex,phnone_num) values(01020180001,'琪琪','女',15884498989),
                                                  (01020180002,'岳岳','男',5884498989),
                                                  (01020180003,'琴琴','女',154444444),
                                                    (01020180004,'康康','男',8989),
                                                    (01020180005,'鳳鳳','女',7666776),
                                                    (01020180006,'丫妹兒','女',443333);

--查詢表
select * from student;

--刪除視圖
drop view v_student;

--創(chuàng)建視圖
create view v_student as select num,name,sex from student with check option;

--查詢視圖
select * from v_student;

--查詢視圖
select phnone_num from student where name in (select name from v_student where name='琪琪');

--為視圖插入數據
INSERT INTO v_student(num,name,sex) VALUES(0001,'柴油','男');

--刪除視圖數據
delete from v_student where num = 1;

索引

?MySQL中有多種方法進行訪問表中的行，其中最常用的是順序訪問和索引訪問叁扫。其中順序訪問非常浪費時間且效率很低三妈。但是索引訪問能使訪問時不必掃描整個數據庫就能迅速查到鎖需要的數據。
?索引是根據表中一列或者多列按照一定的順序建立的列值與記錄行之間的對應關系表莫绣。

建立索引的優(yōu)點：

• 快速讀取數據沈跨。
• 保證數據記錄的唯一性。
• 實現表與表之間參照的完整性兔综。
• 在使用GROUP BY,ORDER BY 子句進行數據檢索時饿凛，利用索引可以減少排序和分組的時間狞玛。
  注意：
• 當表類型為MyISAM,InnoDB或BDB時，才可以向右NULL,BLOB,TEXT列中添加索引涧窒。
• 一個表最多有16個索引心肪，最大索引長度為256個字節(jié)。
• 對于CHAR和VARCHAR列纠吴，可以索引列的前綴硬鞍。
• MySQL能在多個表上面創(chuàng)建索引

索引缺點
　　①索引是以文件的形式存儲的戴已，會占用磁盤空間固该。
　　②增糖儡，刪伐坏，改索引列上的數據的時候，對索引也要進行更新握联，表中的索引越多桦沉，更新表的時間越長。

索引的分類

(1) . 普通索引：最基本的索引類型金闽，沒有唯一性之類的限制纯露，普通索引的關鍵字為INDEX。
(2) . 唯一索引：索引列的所有值只能出現一次代芜，即必須是唯一的埠褪，其關鍵字為UNIQUE。
(3) . 主鍵：主鍵是一種唯一索引挤庇，他必須自定為PRIMARY KEY,每個表只有一個主鍵钞速。
(4) . 全文索引 ：全文索引的類型為FULLTEXT，全文索引只能在VARCHAR或者TEXT類型的列上面創(chuàng)建罚随，并且只能在MyISAM表上面創(chuàng)建玉工。但是對于大規(guī)模的數據，通過ALTER TABLE（或者CREATE INDEX）命令創(chuàng)建全文索引要比把記錄插入帶有全文索引的空表更快淘菩。

創(chuàng)建索引

?表創(chuàng)建完成之后創(chuàng)建索引

1遵班、CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_column_name(length)); // CREATE INDEX 不能創(chuàng)建主鍵索引
2、ALTER TABLE table_name ADD [UNIQUE | FULLTEXT] INDEX index_name(index_column_name(length));
??①潮改、ALTER TABLE table_name ADD PRIMARY KEY (index_column_name(length))狭郑；//創(chuàng)建主鍵索引，索引值必須唯一且不能為null汇在。
index_column_name:創(chuàng)建索引的列名翰萨。
length:表示使用列的前l(fā)ength個字符創(chuàng)建索引。

?在創(chuàng)建表的時候創(chuàng)建索引

CREATE TABLE [IF NOT EXISTS] table_name (
column1,column2 , column3,.....
PRIMARY KEY (index_column_name), //主鍵索引
[UNIQUE | FULLTEXT] INDEX [index_name] index_column_name; //唯一糕殉，全文亩鬼，一般索引
)

e.g.

//創(chuàng)建表之后創(chuàng)建索引
--創(chuàng)建索引
CREATE INDEX index_name ON student(num);

ALTER TABLE student ADD INDEX index_name(num);

--查看創(chuàng)建索引的情況
SHOW INDEX FROM student;

//創(chuàng)建表的時候創(chuàng)建索引
create table people(
    id INTEGER NOT NULL,
    num INTEGER NOT NULL,
    name VARCHAR,
    sex VARCHAR,
    phnone_num BIGINT,
    PRIMARY KEY(id),
    INDEX index_name(num)
);

刪除索引

①殖告、使用DROP INDEX 刪除索引
DROP INDEX index_name ON table_name;
②、使用ALTER TABLE 刪除索引
ALTER TABLE table_name
DROP PRIMARY KEY, //刪除主鍵
DROP UNIQUE INDEX index_name, //刪除唯一索引
DROP INDEX index_name; //刪除索引
DROP FOREIGN KEY fk_symbol; //刪除外鍵
使用DROP 子句可以刪除個只能怪各樣的索引
e.g.

DROP INDEX index_name ON student;
ALTER TABLE student DROP INDEX index_name;

顯示索引信息

SHOW INDEX FROM table_name; \G
//使用\G雳锋，是用來格式化輸出信息的黄绩。

存儲過程

?存儲過程是在大型數據庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集玷过，經編譯后存儲在數據庫中爽丹，用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執(zhí)行它。
?存儲過程的優(yōu)點：
?(1)辛蚊、在服務端運行粤蝎，執(zhí)行速度快。
?(2)袋马、確保數據庫的安全初澎。
?(3)、減少了數據在數據庫和服務器之間的傳輸飞蛹。即當存儲過程執(zhí)行過一次之后谤狡，在以后的操作中只需要在高速緩沖存儲器中調用已經編譯好的代碼執(zhí)行就可以了灸眼，這樣就提高了系統(tǒng)的性能卧檐。
創(chuàng)建存儲過程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
sp_name:存儲過程的名稱，默認在當前數據庫中創(chuàng)建焰宣，如果需要在特定數據庫中創(chuàng)建霉囚，，則要在名稱前面加上數據哭的名稱匕积。database_name.sp_name盈罐。
proc_parameter:存儲過程的參數，其格式為：[IN|OUT|INOUT] param_name type
其中IN 表示輸入參數闪唆，OUT表示輸出參數盅粪，INOUT表示輸入輸出參數。

[IN|OUT|INOUT] param_name type

數據庫優(yōu)化

?盡量避免null,通常情況下最好指定列為NOT NULL,可為null的列會使用更多的存儲空間悄蕾，為NULL的列使得索引票顾、索引統(tǒng)計和值都比較復雜，當可為NULL的列被索引時帆调，每個索引記錄需要一個額外的字節(jié)奠骄，在MYISAM里可能導致固定大小的索引變成可變大小的索引。通常把 可為NULL的列改為NOT NULL帶來的性能提升比較小番刊。除非確實會導致問題才去改含鳞。
1、使用join連接來代替子查詢芹务，因為MySQL不需要在內存中創(chuàng)建臨時表來完成子查詢蝉绷。
2鸭廷、使用事物，事物以BEGIN關鍵字開始熔吗，COMMIT關鍵字結束靴姿。在這之間的一條SQL操作失敗，那么磁滚，ROLLBACK命令就可以把數據庫恢復到BEGIN開始之前的狀態(tài)佛吓。事物有4種特性：
?原子性：只要有一條SQL沒有執(zhí)行成功，事物將會回滾垂攘。比如銀行取錢不可能出現銀行這邊的記錄已經扣錢了维雇，取錢人那邊還沒有扣錢記錄。
?原子性：
?一致性：
?隔離性：
?持久性：
3晒他、避免使用select * 來做查詢吱型，必須指明其要查詢的字段
4、避免在where子句中對字段進行null值判斷
5陨仅、不建議使用%前綴模糊查詢
?例如LIKE“%name”或者LIKE“%name%”津滞，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE “name%”灼伤。如果LIKE“%name%”這種情況則可以使用全文索引來完成触徐。