DDL
創造 create  修改 alter  刪除 dorp
DML
插入 insert  刪除 delete 更新 update
DQL
select from where

MySQL與JDBC

一、用命令行對資料庫的操作

1.  創建一個庫

create database 庫名

create database 庫名 character set 編碼

 建帶有編碼的

查看編碼：

2.  刪除一個庫

drop database 庫名

3.  使用庫

use 庫名

4.查看當前正在操作的庫

二、對資料庫表的操作

1.創建一張表

create table 表名(

    欄位名 類型(長度) [約束],

    欄位名 類型(長度) [約束],

    欄位名 類型(長度) [約束]

);

2.查看資料庫表

創建完成後，我們可以查看資料庫表

show tables;

查看表的結構

desc 表名

3.刪除一張表

drop table 表名

4.修改表

4.1 添加一列

alter table 表名 add 欄位名 類型(長度) [約束]

4.2 修改列的類型(長度、約束)

alter table 表名 modify 要修改的欄位名 類型(長度) [約束]

4.3 修改列的列名

alter table 表名 change 舊列名 新列名 類型(長度) [約束]

4.4 刪除表的列

alter table 表名 drop 列名

4.5 修改表名

rename table 表名 to 新表名

4.6 修改表的字元集

alter table 表名 character set 編碼

查看當前表的編碼

三、對資料庫表記錄進行操作(修改)

1.插入記錄

insert into 表名(列名1,列名2,列名3……) values(值1,值2,值3……)

insert into 表名 values(值1,值2,值3……)

1.1 插入數據中文亂碼問題解決辦法

方式一：【不建議！】

直接修改資料庫安裝目錄裡面的my.ini文件的第57行

方式二：

    set names gbk;

2.修改表記錄

2.1 不帶條件的

update 表名 set 欄位名=值, 欄位名=值, 欄位名=值……

它會將該列的所有記錄都更改

2.2 帶條件的

update 表名 set欄位名=值, 欄位名=值, 欄位名=值…… where 條件

3.刪除表記錄

3.1 帶條件的

delete from 表名 where 條件

註意，刪除後，uid不會重置！

3.2.不帶條件的

先準備數據

insert into tbl_user values(null,’老王’,’666’);

刪除操作

    delete from 表名;

3.3 面試題

說說delete與truncate的區別？

delete刪除的時候是一條一條的刪除記錄，它配合事務，可以將刪除的數據找回。

truncate刪除，它是將整個表摧毀，然後再創建一張一模一樣的表。它刪除的數據無法找回。

Delete操作演示：

Truncate操作演示

註意：delete刪除，uid不會重置！而使用truncate操作，uid會重置

4.查詢操作

語法：

    select [distinct] *| 列名，列名 from 表名 [where條件]

4.1 簡單查詢

1.查詢所有商品

select * from product；

2. 查詢商品名和商品價格

select pname,price from product;

3.查詢所有商品信息使用表別名

select * from product as  p;

4.查詢商品名，使用列別名

select pname as  p from product

5.去掉重覆值(按照價格)

select distinct(price) from product;

先準備數據：

insert into product values (null,'李士雪',38,null);

6.將所有的商品的價格+10進行顯示

select pname,price+10 from product;

4.2 條件查詢

1.查詢商品名稱為"左慈"的商品信息

2.查詢價格>60元的所有商品信息

3.查詢商品名稱含有"士"字的商品信息

4.查詢商品id在(3,6,9)範圍內的所有商品信息

5.查詢商品名稱含有"士"字並且id為6的商品信息

6.查詢id為2或者6的商品信息

4.3 排序

1.查詢所有的商品，按價格進行排序(升序、降序)

2.查詢名稱有"士"的商品信息並且按照價格降序排序

4.4 聚合函數

1.獲得所有商品的價格的總和

2.獲得所有商品的平均價格

3.獲得所有商品的個數

4.5 分組操作

1.添加分類id (alter table product add cid varchar(32);)

2.初始化數據

update product set cid='1';

update product set cid='2' where  pid in (5,6,7);

1.根據cid欄位分組，分組後統計商品的個數。

2.根據cid分組，分組統計每組商品的平均價格，並且平均價格大於20000元。

4.6 查詢總結

select  一般在的後面的內容都是要查詢的欄位

from  要查詢到表

where

group by

having  分組後帶有條件只能使用having

order by 它必須放到最後面

二、回顧JDBC，完成查詢

1 什麼是JDBC

  JDBC（Java DataBase Connectivity）就是Java資料庫連接，說白了就是用Java語言來操作資料庫。原來我們操作資料庫是在控制台使用SQL語句來操作資料庫，JDBC是用Java語言向資料庫發送SQL語句。

2 JDBC原理

早期SUN公司的天才們想編寫一套可以連接天下所有資料庫的API，但是當他們剛剛開始時就發現這是不可完成的任務，因為各個廠商的資料庫伺服器差異太大了。後來SUN開始與資料庫廠商們討論，最終得出的結論是，由SUN提供一套訪問資料庫的規範（就是一組介面），並提供連接資料庫的協議標準，然後各個資料庫廠商會遵循SUN的規範提供一套訪問自己公司的資料庫伺服器的API出現。SUN提供的規範命名為JDBC，而各個廠商提供的，遵循了JDBC規範的，可以訪問自己資料庫的API被稱之為驅動！

JDBC是介面，而JDBC驅動才是介面的實現，沒有驅動無法完成資料庫連接！每個資料庫廠商都有自己的驅動，用來連接自己公司的資料庫。

當然還有第三方公司專門為某一資料庫提供驅動，這樣的驅動往往不是開源免費的！

3 JDBC核心類（介面）介紹

JDBC中的核心類有：DriverManager、Connection、Statement，和ResultSet！

DriverManger（驅動管理器）的作用有兩個：

l  註冊驅動：這可以讓JDBC知道要使用的是哪個驅動；

l  獲取Connection：如果可以獲取到Connection，那麼說明已經與資料庫連接上了。

Connection對象表示連接，與資料庫的通訊都是通過這個對象展開的：

l  Connection最為重要的一個方法就是用來獲取Statement對象；

l  Statement是用來向資料庫發送SQL語句的，這樣資料庫就會執行發送過來的SQL語句

l  void executeUpdate(String sql)：執行更新操作（insert、update、delete等）；

l  ResultSet executeQuery(String sql)：執行查詢操作，資料庫在執行查詢後會把查詢結果，查詢結果就是ResultSet；

ResultSet對象表示查詢結果集，只有在執行查詢操作後才會有結果集的產生。結果集是一個二維的表格，有行有列。操作結果集要學習移動ResultSet內部的“行游標”，以及獲取當前行上的每一列上的數據：

l  boolean next()：使“行游標”移動到下一行，並返回移動後的行是否存在；

l  XXX getXXX(int col)：獲取當前行指定列上的值，參數就是列數，列數從1開始，而不是0。

4 Hello JDBC

 下麵開始編寫第一個JDBC程式

介紹eclipse的相關知識

單元測試junit

4.1 導入mysql資料庫的驅動jar包：

mysql-connector-java-5.1.39-bin.jar；

4.2 註冊驅動

看清楚了，註冊驅動就只有一句話：Class.forName(“com.mysql.jdbc.Driver”)，下麵的內容都是對這句代碼的解釋。今後我們的代碼中，與註冊驅動相關的代碼只有這一句。

DriverManager類的registerDriver()方法的參數是java.sql.Driver，但java.sql.Driver是一個介面，實現類由mysql驅動來提供，mysql驅動中的java.sql.Driver介面的實現類為com.mysql.jdbc.Driver！那麼註冊驅動的代碼如下：

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

上面代碼雖然可以註冊驅動，但是出現硬編碼（代碼依賴mysql驅動jar包），如果將來想連接Oracle資料庫，那麼必須要修改代碼的。並且其實這種註冊驅動的方式是註冊了兩次驅動！

JDBC中規定，驅動類在被載入時，需要自己“主動”把自己註冊到DriverManger中，下麵我們來看看com.mysql.jdbc.Driver類的源代碼：

com.mysql.jdbc.Driver.java

com.mysql.jdbc.Driver類中的static塊會創建本類對象，並註冊到DriverManager中。這說明只要去載入com.mysql.jdbc.Driver類，那麼就會執行這個static塊，從而也就會把com.mysql.jdbc.Driver註冊到DriverManager中，所以可以把註冊驅動類的代碼修改為載入驅動類。

Class.forName(“com.mysql.jdbc.Driver”);

4.3 獲取連接

獲取連接需要兩步，一是使用DriverManager來註冊驅動，二是使用DriverManager來獲取Connection對象。

獲取連接的也只有一句代碼：

DriverManager.getConnection(url,username,password)，

其中username和password是登錄資料庫的用戶名和密碼，如果我沒說錯的話，你的mysql資料庫的用戶名和密碼分別是：root、123。

url查對複雜一點，它是用來找到要連接資料庫“網址”，就好比你要瀏覽器中查找百度時，也需要提供一個url。下麵是mysql的url：

jdbc:mysql://localhost:3306/mydb1

JDBC規定url的格式由三部分組成，每個部分中間使用冒號分隔。

l  第一部分是jdbc，這是固定的；

l  第二部分是資料庫名稱，那麼連接mysql資料庫，第二部分當然是mysql了；

l  第三部分是由資料庫廠商規定的，我們需要瞭解每個資料庫廠商的要求，mysql的第三部分分別由資料庫伺服器的IP地址（localhost）、埠號（3306），以及DATABASE名稱(mydb1)組成。

下麵是獲取連接的語句：

Connection con = DriverManager.getConnection(“jdbc:mysql://localhost:3306/web08”,”root”,”root”);

還可以在url中提供參數：

jdbc:mysql://localhost:3306/web08?useUnicode=true&characterEncoding=UTF8

useUnicode參數指定這個連接資料庫的過程中，使用的位元組集是Unicode位元組集；

characherEncoding參數指定穿上連接資料庫的過程中，使用的位元組集編碼為UTF-8編碼。請註意，mysql中指定UTF-8編碼是給出的是UTF8，而不是UTF-8。要小心了！

4.4 獲取Statement

在得到Connectoin之後，說明已經與資料庫連接上了，下麵是通過Connection獲取Statement對象的代碼：

Statement stmt = con.createStatement();

Statement是用來向資料庫發送要執行的SQL語句的！

4.5 發送SQL查詢語句

String sql = “select * from user”;

ResultSet rs = stmt.executeQuery(sql);

請註意，執行查詢使用的不是executeUpdate()方法，而是executeQuery()方法。executeQuery()方法返回的是ResultSet，ResultSet封裝了查詢結果，我們稱之為結果集。

4.6 讀取結果集中的數據

ResultSet就是一張二維的表格，它內部有一個“行游標”，游標預設的位置在“第一行上方”，我們可以調用rs對象的next()方法把“行游標”向下移動一行，當第一次調用next()方法時，“行游標”就到了第一行記錄的位置，這時就可以使用ResultSet提供的getXXX(int col)方法來獲取指定列的數據了：

rs.next();//游標移動到第一行

rs.getInt(1);//獲取第一行第一列的數據

當你使用rs.getInt(1)方法時，你必須可以肯定第1列的數據類型就是int類型，如果你不能肯定，那麼最好使用rs.getObject(1)。在ResultSet類中提供了一系列的getXXX()方法，比較常用的方法有：

Object getObject(int col)

String getString(int col)

int getInt(int col)

double getDouble(int col)

4.7 關閉

與IO流一樣，使用後的東西都需要關閉！關閉的順序是先得到的後關閉，後得到的先關閉。

rs.close();

stmt.close();

con.close();

4.8 完成查詢操作代碼

4.9 規範化代碼

所謂規範化代碼就是無論是否出現異常，都要關閉ResultSet、Statement，以及Connection，如果你還記得IO流的規範化代碼，那麼下麵的代碼你就明白什麼意思了。

JDBC對象介紹

1 JDBC中的主要類（介面）

在JDBC中常用的類有：

l  DriverManager；

l  Connection；

l  Statement；

l  ResultSet。

2 DriverManager

其實我們今後只需要會用DriverManager的getConnection()方法即可：

1. Class.forName(“com.mysql.jdbc.Driver”);//註冊驅動
2. String url = “jdbc:mysql://localhost:3306/web08”;
3. String username = “root”;
4. String password = “root”;
5. Connection con = DriverManager.getConnection(url, username, password);

註意，上面代碼可能出現的兩種異常：

1. ClassNotFoundException：這個異常是在第1句上出現的，出現這個異常有兩個可能：

l  你沒有給出mysql的jar包；

l  你把類名稱打錯了，查看類名是不是com.mysql.jdbc.Driver。

1. SQLException：這個異常出現在第5句，出現這個異常就是三個參數的問題，往往username和password一般不是出錯，所以需要認真查看url是否打錯。

對於DriverManager.registerDriver()方法瞭解即可，因為我們今後註冊驅動只會Class.forName()，而不會使用這個方法。

3 Connection

Connection最為重要的方法就是獲取Statement：

l  Statement stmt = con.createStatement();

後面在學習ResultSet方法時，還要學習一下下麵的方法：

l  Statement stmt = con.createStatement(int,int);

4 Statement

Statement最為重要的方法是：

l  int executeUpdate(String sql)：執行更新操作，即執行insert、update、delete語句，其實這個方法也可以執行create table、alter table，以及drop table等語句，但我們很少會使用JDBC來執行這些語句；

l  ResultSet executeQuery(String sql)：執行查詢操作，執行查詢操作會返回ResultSet，即結果集。

l   boolean execute()

Statement還有一個boolean execute()方法，這個方法可以用來執行增、刪、改、查所有SQL語句。該方法返回的是boolean類型，表示SQL語句是否執行成功。

如果使用execute()方法執行的是更新語句，那麼還要調用int getUpdateCount()來獲取insert、update、delete語句所影響的行數。

如果使用execute()方法執行的是查詢語句，那麼還要調用ResultSet getResultSet()來獲取select語句的查詢結果。

5 ResultSet之滾動結果集（瞭解）

ResultSet表示結果集，它是一個二維的表格！ResultSet內部維護一個行游標（游標），ResultSet提供了一系列的方法來移動游標：

l  void beforeFirst()：把游標放到第一行的前面，這也是游標預設的位置；

l  void afterLast()：把游標放到最後一行的後面；

l  boolean first()：把游標放到第一行的位置上，返回值表示調控游標是否成功；

l  boolean last()：把游標放到最後一行的位置上；

l  boolean isBeforeFirst()：當前游標位置是否在第一行前面；

l  boolean isAfterLast()：當前游標位置是否在最後一行的後面；

l  boolean isFirst()：當前游標位置是否在第一行上；

l  boolean isLast()：當前游標位置是否在最後一行上；

l  boolean previous()：把游標向上挪一行；

l  boolean next()：把游標向下挪一行；

l  boolean relative(int row)：相對位移，當row為正數時，表示向下移動row行，為負數時表示向上移動row行；

l  boolean absolute(int row)：絕對位移，把游標移動到指定的行上；

l  int getRow()：返回當前游標所有行。

上面方法分為兩類，一類用來判斷游標位置的，另一類是用來移動游標的。如果結果集是不可滾動的，那麼只能使用next()方法來移動游標，而beforeFirst()、afterLast()、first()、last()、previous()、relative()方法都不能使用！！！

結果集是否支持滾動，要從Connection類的createStatement()方法說起。也就是說創建的Statement決定了使用Statement創建的ResultSet是否支持滾動。

Statement createStatement(int resultSetType, int resultSetConcurrency)

resultSetType的可選值：

l  ResultSet.TYPE_FORWARD_ONLY：不滾動結果集；

l  ResultSet.TYPE_SCROLL_INSENSITIVE：滾動結果集，但結果集數據不會再跟隨資料庫而變化；

l  ResultSet.TYPE_SCROLL_SENSITIVE：滾動結果集，但結果集數據不會再跟隨資料庫而變化；

可以看出，如果想使用滾動的結果集，我們應該選擇TYPE_SCROLL_INSENSITIVE！其實很少有資料庫驅動會支持TYPE_SCROLL_SENSITIVE的特性！通常我們也不需要查詢到的結果集再受到資料庫變化的影響。

resultSetConcurrency的可選值：

l  CONCUR_READ_ONLY：結果集是只讀的，不能通過修改結果集而反向影響資料庫；

l  CONCUR_UPDATABLE：結果集是可更新的，對結果集的更新可以反向影響資料庫。

通常可更新結果集這一“高級特性”我們也是不需要的！

獲取滾動結果集的代碼如下：

Connection con = …

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY);

String sql = …//查詢語句

ResultSet rs = stmt.executeQuery(sql);//這個結果集是可滾動的

6 ResultSet之獲取列數據

可以通過next()方法使ResultSet的游標向下移動，當游標移動到你需要的行時，就需要來獲取該行的數據了，ResultSet提供了一系列的獲取列數據的方法：

l  String getString(int columnIndex)：獲取指定列的String類型數據；

l  int getInt(int columnIndex)：獲取指定列的int類型數據；

l  double getDouble(int columnIndex)：獲取指定列的double類型數據；

l  boolean getBoolean(int columnIndex)：獲取指定列的boolean類型數據；

l  Object getObject(int columnIndex)：獲取指定列的Object類型的數據。

上面方法中，參數columnIndex表示列的索引，列索引從1開始，而不是0，這第一點與數組不同。如果你清楚當前列的數據類型，那麼可以使用getInt()之類的方法來獲取，如果你不清楚列的類型，那麼你應該使用getObject()方法來獲取。

ResultSet還提供了一套通過列名稱來獲取列數據的方法：

l  String getString(String columnName)：獲取名稱為columnName的列的String數據；

l  int getInt(String columnName)：獲取名稱為columnName的列的int數據；

l  double getDouble(String columnName)：獲取名稱為columnName的列的double數據；

l  boolean getBoolean(String columnName)：獲取名稱為columnName的列的boolean數據；

l  Object getObject(String columnName)：獲取名稱為columnName的列的Object數據；

PreparedStatement

1 什麼是SQL攻擊

在需要用戶輸入的地方，用戶輸入的是SQL語句的片段，最終用戶輸入的SQL片段與我們DAO中寫的SQL語句合成一個完整的SQL語句！例如用戶在登錄時輸入的用戶名和密碼都是為SQL語句的片段！

2 演示SQL攻擊

首先我們需要創建一張用戶表，用來存儲用戶的信息。

現在用戶表中只有一行記錄，就是zs。

下麵我們寫一個login()方法！

下麵是調用這個方法的代碼：

這行當前會使我們登錄成功！因為是輸入的用戶名和密碼是SQL語句片段，最終與我們的login()方法中的SQL語句組合在一起！我們來看看組合在一起的SQL語句：

3 防止SQL攻擊

l  過濾用戶輸入的數據中是否包含非法字元；

l  分步校驗！先使用用戶名來查詢用戶，如果查找到了，再比較密碼；

l  使用PreparedStatement。

4 PreparedStatement是什麼？

PreparedStatement叫預編譯聲明！

PreparedStatement是Statement的子介面，你可以使用PreparedStatement來替換Statement。

PreparedStatement的好處：

l  防止SQL攻擊；

l  提高代碼的可讀性，以可維護性；

l  提高效率。

5 PreparedStatement的使用

l  使用Connection的prepareStatement(String sql)：即創建它時就讓它與一條SQL模板綁定；

l  調用PreparedStatement的setXXX()系列方法為問號設置值

l  調用executeUpdate()或executeQuery()方法，但要註意，調用沒有參數的方法；

在使用Connection創建PreparedStatement對象時需要給出一個SQL模板，所謂SQL模板就是有“?”的SQL語句，其中“?”就是參數。

在得到PreparedStatement對象後，調用它的setXXX()方法為“?”賦值，這樣就可以得到把模板變成一條完整的SQL語句，然後再調用PreparedStatement對象的executeQuery()方法獲取ResultSet對象。

註意PreparedStatement對象獨有的executeQuery()方法是沒有參數的，而Statement的executeQuery()是需要參數（SQL語句）的。因為在創建PreparedStatement對象時已經讓它與一條SQL模板綁定在一起了，所以在調用它的executeQuery()和executeUpdate()方法時就不再需要參數了。

PreparedStatement最大的好處就是在於重覆使用同一模板，給予其不同的參數來重覆的使用它。這才是真正提高效率的原因。

所以，建議大家在今後的開發中，無論什麼情況，都去需要PreparedStatement，而不是使用Statement。