MySQL資料庫基礎操作

第一章

1、常用指令

連接配接資料庫：

mysql -uroot -p 密碼
mysql -uroot -p 回車
輸入密碼：******
           

檢視資料庫服務中的所有資料庫：

show databases;
           

選中資料庫

當運作完畢後，出現database changed;

當出現以上資訊時，表示選中了指定的資料庫

檢視指定資料庫的所有資料表

檢視表格中的資料資訊

檢視建立資料表的語句

show create table 表名;
           

檢視表的詳細資訊（字段，字段類型， 屬性等資訊）

2、建立資料庫

3、建立資料表

create table 表名(
	字段名1 字段類型1 屬性 索引 注釋,
    字段名2 字段類型2 屬性 索引 注釋,
    字段名3 字段類型3 屬性 索引 注釋
)
           

注意：

如果字段是以後一個的時候，不能在末尾添加“,”，否則sql語句報錯！

在使用sqlYog時可以使用Tab鍵進行提示！

如果想執行别人給你的sql腳本的話：

1、直接複制+粘貼

2、将sql檔案拖進來，但是并沒有執行結果

3、[email protected] 上右鍵——> 執行sql腳本——>選中需要執行的sql腳本

此時會将sql檔案中的内容進行執行，不單單是sql語句的導入，而是結果的執行！

###3.1、建立myisam類型的表

CREATE TABLE test2(

id

INT(4) PRIMARY KEY,

name

VARCHAR(50) NOT NULL

)ENGINE = MYISAM;

注：

1、myisam類型的表在存儲的時候有以下三個檔案：

​ *.frm ：資料結構檔案

​ *.MYD ：資料檔案

​ *.MYI ： 索引檔案

2、innodb類型：

​ *.frm

​ 在上一級目錄存在一個ibdata1 檔案

###3.2、修改表名 AS 可以省略

3.3、複制表結構as和like的差別

對于MySQL的複制相同表結構方法，有create table as 和create table like 兩種，差別是什麼呢？

create table t2 as select * from t1 where 1=2;
或者 limit 0;
           

as建立出來的t2表（新表）缺少t1表（源表）的索引資訊，隻有表結構相同，沒有索引，會拷貝資料到新表中。

create table t2 like t1 ;
           

like 建立出來的新表包含源表的完整表結構和索引資訊，不會拷貝資料到新表中。

二者的用途：

​ as用來建立相同表結構并複制源表資料。

​ like用來建立完整表結構和全部索引。

​ oracle支援as，也是隻有表結構沒有索引，oracle不支援like。

​ 兩種方式在複制表的時候均不會複制權限對表的設定。比如說原本對表B做了權限設定，複制後，表A不具備類似于表B的權限。

##4、添加字段

5、修改字段 modify（隻修改屬性）

ALTER TABLE test2 MODIFY address CHAR(20) NULL;
DESC test2;
           

##6、修改字段 change（可更改字段名）

ALTER TABLE test2 CHANGE address addr VARCHAR(50) NOT NULL DEFAULT '和平大道';
           

##7、删除字段

ALTER TABLE test2 DROP addr;
           

注：

​ 1、删除表 屬于DDL語句 （資料定義語言）

drop table 表名
           

​ 2、删除字段 （屬于DDL語句）

alter table 表名 drop 字段名
           

​ 3、删除資料，（删除一行資料，或者删除全部資料）

delete 
           

第二章

1、外鍵：

**作用：**進行資料的限制，例如 student 和grade

grade 表是主表，student表是從表

由grade表對student表進行限制，student表中的資料限制于grade表

**即：**假如年級表中隻有1、2、3三個年級，那麼在建立了外鍵之後，學生表中不能随意添加gradeId字段的值。該值必須與grade表中的id（1、2、3）一緻才可以！

2、建立外鍵文法：

方式一：通過外部添加！

通過在建立表的時候就添加主外鍵限制

方式二：通過建立表時規定限制字段

CREATE TABLE student(
	`id` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '學生id',
	`name` VARCHAR(30) NOT NULL COMMENT '學生姓名',
	`address` VARCHAR(50) COMMENT '家庭住址',
	`gradeId` INT(4) NOT NULL,
	CONSTRAINT fk_grade_student FOREIGN KEY(gradeId) REFERENCES `grade`(gradeId)    
)COMMENT = '學生表';
           

3、删除外鍵

執行以上腳本後發現外鍵還存在！需要将外鍵索引删除，才可以徹底删除外鍵

ALTER TABLE student DROP INDEX fk_grade_student
           

通過sqlYog删除外鍵亦是如此！

4、添加完外鍵限制後删除表

如果直接删除grade表

會報以下異常：

Cannot delete or update a parent row: a foreign key constraint fails
           

意思：不能删除主表，原因是有外鍵關聯

是以在添加完外鍵之後，應該先删除從表，再删除主表！

5、添加資料

1）文法：

注：

​ 1、如果不添加字段，那麼要按照表中字段的類型一一對應去添加

​ 2、如果指定字段，那麼要按照指定字段的類型一一對應去添加

​ 3、可以通過一條insert語句添加多條資料，多個值清單之間用“，”隔開即可！

例如：

6、修改資料

1）文法：

注：

​ 1、如果不添加where條件，那麼将會把資料表中所有的記錄資訊全部更新為指定資訊，不允許！

​ 2、如果設定多個條件，可以通過“，”将多個條件隔開！

7、BETWEEN、 AND、 OR、IN

查詢使用者id在5到7之間的使用者資訊

如果條件為連續區間（閉區間即包括5和7），可以使用BETWEEN AND 的文法來代替and

SELECT * FROM student WHERE id BETWEEN 5 AND 7;
           

查詢使用者id為1、3、5、7的使用者資訊

SELECT * FROM student WHERE id = 1 OR id = 3 OR id = 5 OR id =7;
           

如果條件不是連續區間，而是間斷的，那麼可以使用IN(xx,xx,xx,xx)中間用“,”隔開，來代替or

SELECT * FROM student WHERE id IN(1,3,5,7);
           

8、删除資料

1）DELETE文法：

delete from 表名 where 條件
           

**注：**在删除資料的時候，必須添加條件，否則将會把資料表中的全部資料删除，不允許！

2）TRUNCATE文法：

**注：**使用truncate删除資料的時候，資料沒辦法復原，相當于表的截斷

9、對比delete和truncate

相同點：

​ 兩者都可以将表中的資料清空，并且保留表結構！

不同點：

​ 1）使用delete删除資料，如果id為自增的，那麼在添加新資料的時候，id從原來的基礎上進行自增

**即：**自增器不清零！

​ 2）如果使用truncate删除資料，如果id為自增的，那麼在添加新資料的時候，id從1開始

**即：**自增器清零！

​ 3）事務角度：

​ 使用delete删除資料，可以進行資料的復原！也就是有反悔的能力

​ 使用truancate删除資料，資料不能復原，說明truncate對事務沒有影響！

不同存儲引擎：

​ 例如myisam存儲引擎。使用truancate删除後，如果id為自增，那麼新添加的資料也是從1開始，說明不論什麼樣的存儲親情，truncate都會将自增器清零！

第三章

1、查詢的文法：

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
    [ left|out|inner  join  table_name2 ]    #聯合查詢
	[ WHERE  … ]   	#指定結果需滿足的條件
	[ GROUP BY …]	#指定結果按照哪幾個字段來分組
	[ HAVING …]	#過濾分組的記錄必須滿足的次要條件
	[ ORDER BY… ]	#指定查詢記錄按一個或者多個條件排序
	[ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  #指定查詢的記錄從哪條至哪條
           

注：

​ 1、[] 括号代表可選的；

​ 2、{} 括号代表必須的；

​ 3、# MySQL語句中的注釋符，也可以用 /該處為注釋/

案例1：查詢所有參加考試的學生資訊，（使用等值連接配接）

SELECT 
  student.studentNo,
  studentName,
  studentResult 
FROM
  student,
  result 
WHERE student.`studentNo` = result.`StudentNo` ;

           

注：

1、如果在使用等值連接配接的時候，并沒有通用字段的相等，那麼結果傳回的是笛卡爾乘積

隻有當使用where 通用字段的相等 最後的結果才正确！

​ 2、當聯表查詢的時候，如果兩張表中都有相同的字段，那麼一定要在相同字段的前面加上資料表的名稱

案例2：查詢所有參加考試的學生資訊，（使用内連接配接）

SELECT
student.studentNo,studentName,studentResult
FROM
student INNER JOIN result 
ON student.`studentNo`= result.`StudentNo`;
           

注：

​ 1、當需要表示通用字段的相等的時候，不能使用where，需要使用“ON ” 關鍵字！

​ 優化：使用AS關鍵字 給表和字段加别名，進而簡化sql語句

SELECT
s.studentNo AS '學生編号',studentName '學生姓名',studentResult '學生成績'
FROM
student AS s INNER JOIN result r
ON s.`studentNo`= r.`StudentNo`;
           

案例3：去重distinct

**注：**1、隻有全部的字段值都相同的時候，才能去重。否則不能去重

例如：

select distinct sex from student
           

結果隻有 男 和 女

案例4：查詢所有郵箱為空（null）的使用者資訊

案例5：查詢分數在80-90之間的學生資訊

SELECT 
s.studentNo '學生編号',studentName '學生姓名',StudentResult '學生成績'
FROM 
student s,result r
WHERE 
r.`StudentResult` BETWEEN 80 AND 90 
AND s.`studentNo` = r.`StudentNo`
           

案例6：模糊查詢

#查詢姓李的資訊
SELECT * FROM student WHERE studentName LIKE CONCAT('李','%');
           

#查詢名字中包含“秋”字的學生資訊
SELECT * FROM student WHERE studentName LIKE CONCAT('%','秋','%');
           

#查詢姓“張”，名字為一個字的學生資訊
SELECT * FROM student WHERE studentName LIKE CONCAT('張','_');
           

使用自定義的轉義符進行模糊查詢

#查詢名字中包含%的學生資訊
SELECT * FROM student WHERE studentname LIKE '%\%%' ESCAPE '\\';
           

案例7：使用左外連接配接查詢

SELECT
s.studentNo,studentName,StudentResult
FROM result r
LEFT JOIN 
student s
ON s.`studentNo` = r.`StudentNo`
           

注：

​ 如果student是主表，那麼将會輸出主表中的所有資訊，從表中如果沒有對應的資訊，以null填充

案例7：使用右外連接配接查詢

SELECT
s.studentNo,studentName,StudentResult
FROM student s
RIGHT JOIN 
result r
ON s.`studentNo` = r.`StudentNo`
           

**注：**如果成績表為主表，成績表中的資訊會全部輸出，如果student表中沒有對應的資料，将不會顯示！

案例8：自連接配接

建立表的sql：

CREATE TABLE IF NOT EXISTS category(
     categoryId INT(10)  AUTO_INCREMENT  PRIMARY KEY,
     categoryName   VARCHAR(32) NOT NULL ,
     pid  INT(10)
);

           

添加資料：

INSERT INTO category VALUES
			(2,1,"美術設計"),
			(3,1,"軟體開發"),
			(4,3,"資料庫基礎"),
			(5,2,"Photoshop基礎"),
			(6,2,"色彩搭配學"),
			(7,3,"PHP基礎"),
			(8,3,"一起學JAVA");
           

查詢：

SELECT 
	c1.`categoryName` "1級",c2.`categoryName`
	FROM category c1 INNER JOIN category c2
	ON c2.`pid` = c1.`categoryId`;
           

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-GttqbMgT-1584173615669)(C:/Users/LucyBoy/Desktop/建立檔案夾/image/自連接配接效果圖.png)]

#第四章

1、order by排序

#查詢學生成績，并按照由高到低進行排序
SELECT 
s.studentNo '學生編号',studentName '學生姓名',studentResult '學生成績'
FROM student s 
INNER JOIN 
result r
ON s.`studentNo` = r.`StudentNo`
ORDER BY StudentResult DESC
           

注：

​ 1、預設是升序排序，即ASC，即使不寫也是升序

​ 2、DESC是降序排序

2、limit 語句

#查詢學生成績，并按照由高到低進行排序
SELECT 
s.studentNo '學生編号',studentName '學生姓名',studentResult '學生成績'
FROM student s 
INNER JOIN 
result r
ON s.`studentNo` = r.`StudentNo`
ORDER BY StudentResult DESC
LIMIT 6,3;
           

limit 語句的文法：

​ limit 偏移量 , 頁容量

公式：偏移量 = （目前頁碼數 - 1）* 頁容量
           

3、子查詢

在查詢語句中的WHERE條件子句中，又嵌套了另外一個查詢語句

3.1、普通的子查詢

案例1：

#查詢出生日期比“李斯文”同學大的學生資訊

#1、查詢李斯文的出生日期
SELECT bornDate FROM student WHERE studentName = '李斯文';

#2、查詢出生日期比1993-07-23大的學生資訊
SELECT * FROM student 
WHERE bornDate < '1993-07-23';

#使用子查詢優化以上案例
SELECT * FROM student 
WHERE bornDate < (
SELECT 
  bornDate 
FROM
  student 
WHERE studentName = '李斯文'
);
           

3.2、使用IN子查詢

#2、查詢考java的學生資訊
#1）先查詢java的課程編号
SELECT SubJectId FROM `subject`
WHERE subjectName = 'Java'
#2）查詢考java的學生編号資訊
SELECT studentNo FROM result WHERE
subjectId = (
	SELECT SubJectId FROM `subject`
	WHERE subjectName = 'Java'
)
#3）根據查詢出的學生編号去學生表中查詢學生資訊
SELECT 
	student.studentNo '學生編号',
	studentName '學生姓名',
	phone '聯系方式'
FROM student 
WHERE 
studentNo IN (
	SELECT studentNo FROM result WHERE
	subjectId = (
		SELECT SubJectId FROM `subject`
		WHERE subjectName = 'Java'
	)
)
           

注：

​ 1、當子查詢中查詢的結果是一個的時候，可以使用 “=”（算數運算符）

​ 2、當子查詢中查詢的結果不止一個的時候，隻能使用IN子查詢，表示一個範圍。

3.3、EXISTS子查詢

SELECT * FROM student WHERE EXISTS(
SELECT student.studentNo 
FROM
  student 
  INNER JOIN result 
    ON student.`studentNo` = result.`StudentNo` 
)
           

注：

​ 1、exists子查詢中的結果對父查詢中的輸出列沒有影響

​ 2、當子查詢中有結果傳回時，将傳回true，此時進行父查詢

​ 3、當子查詢中沒有結果傳回，将傳回false，此時不進行父查詢

補充案例：

​ 1、查詢沒有參加考試的學生資訊

#查詢沒有參數考試的學生資訊
SELECT s.studentNo '學生資訊',studentName '學生姓名'
FROM student s WHERE s.studentNo NOT IN(
	SELECT student.studentNo FROM student 
	INNER JOIN result ON student.`studentNo` = result.`StudentNo`
)
           

​ 2、使用了四層子查詢 查詢最近一次參加考試的學生資訊

#3、查詢最近一次考試java的學生資訊
#1）查詢考java的課程編号
SELECT SubJectId FROM `subject`
WHERE subjectName = 'Java'

#2）查詢考java的學生編号資訊
SELECT studentNo FROM result WHERE
subjectId = (
	SELECT SubJectId FROM `subject`
	WHERE subjectName = 'Java'
)

#3）查詢考試java這門課的考試日期
SELECT ExamDate FROM result 
WHERE subjectId = (
	SELECT subjectId FROM `subject`
	WHERE subjectName = 'Java'
)

#4）查詢最近一次考java的學生的資訊
SELECT 
	student.studentNo '學生編号',
	studentName '學生姓名',
	phone '聯系方式'
FROM student 
WHERE studentNo IN(
	SELECT studentNo FROM result 
	WHERE SubJectId = (
		SELECT subjectId FROM `subject`
		WHERE subjectName = 'Java'
	)AND ExamDate = (
		SELECT MAX(ExamDate) FROM result 
		WHERE subjectId = (
		SELECT subjectId FROM `subject`
		WHERE subjectName = 'Java'
		)
	)
)
           

4、聚合函數

4.1、常用函數

#1、查詢學生表中的總條數
SELECT COUNT(1) '總人數' FROM student;

#2、查詢考試的最高分的學生資訊
SELECT MAX(StudentResult) '最高分'
FROM student s 
INNER JOIN 
result r 
WHERE s.`studentNo` = r.`StudentNo`

#3、查詢考試的平均分
SELECT AVG(StudentResult) FROM result;

#4、查詢所有成績之和
SELECT SUM(StudentResult) FROM result;
           

4.2、日期函數

#日期函數
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT WEEK(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
SELECT DATEDIFF('2020-8-8',NOW());
SELECT ADDDATE(NOW(),10);
           

4.3、數學函數

#數學函數
SELECT CEIL(2.1);  3
SELECT FLOOR(2.9999);  2
SELECT RAND();傳回0-1之間的随機數
SELECT ROUND(3.15);四舍五入
           

5、group by 分組查詢

#按照不同的課程，分别算出其平均分、最高分和最低分，對于低于60分平均分的不予顯示

SELECT 
s.SubjectName AS "課程名",
MAX(StudentResult) AS "最高分" ,
MIN(StudentResult) AS "最低分" ,
AVG(StudentResult) AS 平均分
FROM 
result  AS r 
LEFT JOIN 
`subject` AS s 
ON s.subjectId = r.subjectId
GROUP BY  r.subjectId	
HAVING AVG(StudentResult) >= 60 ;
           

注：

​ having 是對group by分組的資料再進行篩選

​ group by是對 where 篩選出的輸出列中的字段進行分組

​ 還可以根據多個字段進行分組，分組的順序依次進行

SELECT * FROM student
GROUP BY sex,gradeId
           

​ 先按照性别進行分組，再按照年級編号進行分組。

第五章

1、事務：

​ 事務就是将一組SQL語句放在同一批次内去執行，如果一個SQL語句出錯，則該批次内的所有SQL都将被取消執行

2、事務的四大特性

​ 1）原子性 Atomic

​ 2）一緻性 Consist

​ 3）隔離性 Isolated

​ 4）持久性 Durable

3、事務控制

1）關閉自動送出

​ 由于在mysql中，每一條sql語句都進行自動送出，是以可以将自動送出關閉，進而實作對事務的控制

文法：

注:

​ 1、0 表示關閉自動送出

​ 2、1表示開啟自動送出

​ 3、在關閉了自動送出之後，可以手動的進行commit或者rollback

2）開啟事務

​ 照樣打開事務的自動送出，如果想進行事務的控制，可以開啟一個事務。（使用DML語句也可以開啟事務）

文法：

注：

​ 開啟了事務之後，就可以進行手動的commit或rollback了

4、索引

1、作用：

​ 1）提高查詢速度

​ 2）確定資料的唯一性

​ 3）可以加速表和表之間的連接配接，實作表與表之間的參照完整性

​ 4）使用分組和排序子句進行資料檢索時，可以顯著減少分組和排序的時間

​ 5）全文檢索字段進行搜尋優化

2、分類

​ 1）主鍵索引（PRIMARY KEY）

​ 2）唯一索引（UNIQUE）

​ 3）正常索引（INDEX）

​ 4）全文索引（FULLTEXT）（引擎要用Myisam）（查詢時用WHERE MATCH(

studentName

) AGAINST(‘李斯文’)）

​ 5）複合索引：最左字首規格

3、建立文法：

方式一: 在聲明表添加字段的時候建立

方式二: 外部的方式建立索引

#基于student表建立一個附表
CREATE TABLE student_copy AS(
	SELECT * FROM student
)
           

#給student_copy表添加索引
ALTER TABLE student_copy ADD PRIMARY KEY(`studentNo`);

ALTER TABLE student_copy ADD UNIQUE KEY(`identityCard`);

ALTER TABLE student_copy ADD INDEX(`GradeId`);

ALTER TABLE student_copy ENGINE = MYISAM
#添加全文索引
ALTER TABLE student_copy ADD FULLTEXT(`address`);

           

可以使用EXPLAIN來解釋 在使用索引查詢的效率

EXPLAIN SELECT * FROM student_copy WHERE studentNo = '10000'

EXPLAIN SELECT * FROM student_copy 
WHERE address LIKE CONCAT('%','天','%');

EXPLAIN SELECT * FROM student_copy 
WHERE MATCH(`studentName`) AGAINST('李斯文')

ALTER TABLE student_copy ADD FULLTEXT(`studentName`);

EXPLAIN SELECT * FROM student_copy 
WHERE MATCH(`address`) AGAINST('天津市河西區');

           

**注意：**全文索引不能直接用 = 或者 like

應該使用MATCH AGAINST文法

4、删除索引

#删除字段 -- 迫使索引删除
ALTER TABLE student_copy DROP address;
#直接删除索引
DROP INDEX address ON student_copy;
           

5、索引的添加規則

1）index和key關鍵字都可設定正常索引

2）應加在查找條件的字段

3）不宜添加太多正常索引，影響資料的插入、删除和修改操作

索引不是越多越好

不要對經常變動的資料加索引

小資料量的表建議不要加索引

索引一般應加在查找條件的字段

6、檢視表中的所有索引資訊

SHOW KEYS FROM student_copy 
	或者
SHOW INDEX FROM student_copy 
           

5、視圖

1、視圖的作用：

1）使用視圖，可以定制使用者資料，聚焦特定的資料（不同的使用者檢視不同的資料）

2）使用視圖，可以簡化資料操作。

3）使用視圖，基表中的資料就有了一定的安全性 （虛拟表）

4）可以合并分離的資料，建立分區視圖 （可以跨庫建立視圖）

2、建立的文法：

2.1、基于單張表建立視圖：

CREATE VIEW view_result_copy2 AS(
	SELECT * FROM student_copy
)
           

注：

​ 基于一張表建立的視圖，可以通過修改視圖的資訊（insert、update、delete）來達到對原資料表資料的更新

2.2、基于多張表建立視圖：

CREATE VIEW view_result_copy_student_copy
AS(
SELECT s.studentNo '學生編号',s.studentName '學生姓名',r.studentResult '學生成績' FROM student_copy s 
INNER JOIN result_copy r
ON s.studentNo = r.studentNo
)

           

注：

​ 基于多張表建立的視圖，隻能通過update修改視圖的資訊，來對原資料表的資料進行修改，不能實作添加（insert）和删除（delete）操作！

原資料表中的限制對視圖也是有效的！

2.3、基于不同的資料庫建立視圖

#根據多個資料庫建立視圖
CREATE VIEW V_TEST
AS
SELECT A.studentNo,A.studentName,B.*
FROM `school1`.`student` A,school.person B

SELECT * FROM V_TEST;
           

注：

​ 前提是：兩張表中不能存在重複的字段。

​ 如果兩張表中都存在id這個字段，是建立不成功的。

2.4、删除視圖

DROP VIEW [IF EXISTS]視圖名
           

6、資料庫的備份和恢複

1、備份資料庫文法

mysqldump -uroot -p666666 -c school1 student_copy > d:/backup/2018_10_10student.sql
備份多張表用空格隔開
           

2、資料庫的恢複（DOS操作）

​ 方法1：使用source指令

第一步：先登入資料庫mysql：mysql -u root -p123456
第二步：選擇需要恢複至哪個資料庫：use db_name
第三步：source d:/backup/2018_10_10student.sql（已備份檔案的路徑）
           

​ 方法2：使用mysql指令

注：

​ 1、在進行資料庫的備份的時候，直接在dos視窗執行mysqldump指令。不需要登入資料庫。

​ 2、在恢複資料庫資訊的時候，如果是使用source指令，首先需要先登入mysql資料庫，選中一個需要恢複資料的資料庫，然後再執行source指令

​ 3、如果使用mysql指令。就直接在控制台執行即可。

7、資料的導出和導入

7.1、資料的導出

#資料的導出
SELECT * FROM student
WHERE gradeId = 1 INTO OUTFILE
'C:/ProgramData/MySQL/MySQL Server 5.5/Uploads/greadeOne.txt';
           

注：

由于在my.ini 檔案中有安全檔案的權限問題，導緻我在導出的時候隻能導出在指定的路徑下！

7.2、資料的導入

#導入檔案
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.5/Uploads/greadeOne.txt' INTO TABLE student;
           

注：

​ 如果要将外部的資料導入到資料庫中，那麼一定要保證在資料庫中有響應的表結構來接收導入的資料！

MySQL 的存儲過程

​ MySQL 存儲過程是從MySQL5.0 開始增加的新功能。存儲過程的優點有一籮筐。不過最主要的還是執行效率和SQL 代碼封裝。特别是SQL 代碼封裝功能，如果沒有存儲過程，在外部程式通路資料庫時，要組織很多SQL 語句。特别是業務邏輯複雜的時候，一大堆的SQL 和條件夾雜在代碼中，讓人不寒而栗。現在有了MySQL 存儲過程，業務邏輯可以封裝存儲過程中，這樣不僅容易維護，而且執行效率也高。

1、建立MySQL 存儲過程

​ 下面代碼建立了一個叫pr_add 的MySQL 存儲過程，這個MySQL 存儲過程有兩個int 類型的輸入參數“a”、

“b”，傳回這兩個參數的和。

1）drop procedure if exists pr_add; （備注：如果存在pr_add 的存儲過程，則先删掉）

2）計算兩個數之和（備注：實作計算兩個整數之和的功能）

create procedure pr_add ( a int, b int ) begin declare c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set c = a + b;
select c as sum;
           

2、調用MySQL 存儲過程

call pr_add(10, 20);
           

3、存儲過程與SQL 的對比

3.1、優勢:

1、提高性能

SQL 語句在建立過程時進行分析和編譯。存儲過程是預編譯的，在首次運作一個存儲過程時，查詢優化器對其進行分析、優化，并給出最終被存在系統表中的存儲計劃，這樣，在執行過程時便可節省此開銷。

2、降低網絡開銷

存儲過程調用時隻需用提供存儲過程名和必要的參數資訊，進而可降低網絡的流量。

3、便于進行代碼移植

資料庫專業人員可以随時對存儲過程進行修改，但對應用程式源代碼卻毫無影響，進而極大的提高了程式的可移植性。

4、更強的安全性

1）系統管理者可以對執行的某一個存儲過程進行權限限制，避免非授權使用者對資料的通路

2）在通過網絡調用過程時，隻有對執行過程的調用是可見的。是以，惡意使用者無法看到表和資料庫對象名稱、嵌入自己的Transact-SQL 語句或搜尋關鍵資料。

3）使用過程參數有助于避免SQL 注入攻擊。因為參數輸入被視作文字值而非可執行代碼，是以，攻擊者将指令插入過程内的Transact-SQL 語句并損害安全性将更為困難。

4）可以對過程進行加密，這有助于對源代碼進行模糊處理。

3.2、劣勢：

​ 1)、存儲過程需要專門的資料庫開發人員進行維護，但實際情況是，往往由程式開發員人員兼職

​ 2)、設計邏輯變更，修改存儲過程沒有SQL 靈活

MySQL 觸發器

​ MySQL 包含對觸發器的支援。觸發器是一種與表操作有關的資料庫對象，當觸發器所在表上出現指定事件時，将調用該對象，即表的操作事件觸發表上的觸發器的執行。

在MySQL 中，建立觸發器文法如下：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
           

其中：

trigger_name：辨別觸發器名稱，使用者自行指定；

trigger_time：辨別觸發時機，取值為BEFORE 或AFTER；

trigger_event：辨別觸發事件，取值為INSERT、UPDATE 或DELETE；

tbl_name：辨別建立觸發器的表名，即在哪張表上建立觸發器；

trigger_stmt：觸發器程式體，可以是一句SQL 語句，或者用BEGIN 和END 包含的多條語句。

​ 由此可見，可以建立6 種觸發器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

​ 另外有一個限制是不能同時在一個表上建立2 個相同類型的觸發器，是以在一個表上最多建立6 個觸發器。

1、示例：

假設系統中有兩個表：

1)班級表class(班級号classID, 班内學生數stuCount)

2)學生表student(學号stuID, 所屬班級号classID)

要建立觸發器來使班級表中的班内學生數随着學生的添加自動更新，代碼如下：

create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
           

2、檢視觸發器：

​ 和檢視資料庫（show databases;）檢視表格（show tables;）一樣，檢視觸發器的文法如下：

SHOW TRIGGERS [FROM schema_name];
           

​ 其中，schema_name 即Schema 的名稱，在MySQL 中Schema 和Database 是一樣的，也就是說，可以

指定資料庫名，這樣就不必先“USE database_name;”了。

3、删除觸發器：

​ 和删除資料庫、删除表格一樣，删除觸發器的文法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
           

4、觸發器的作用

1）觸發器可通過資料庫中的相關表實作級聯更改；通過級聯引用完整性限制可以更有效地執行這些更改。

2）觸發器可以強制比用CHECK 限制定義的限制更為複雜的限制。與CHECK 限制不同，觸發器可以引用其它

表中的列。例如，觸發器可以使用另一個表中的SELECT 比較插入或更新的資料，以及執行其它操作，如修改資料或顯示使用者定義錯誤資訊。

3）觸發器還可以強制執行業務規則

4）觸發器也可以評估資料修改前後的表狀态，并根據其差異采取對策。

提高千萬級的資料庫查詢效率

1)資料庫設計方面

​ a. 對查詢進行優化，應盡量避免全表掃描，首先應考慮在where 及order by 涉及的列上建立索引。

​ b. 應盡量避免在where 子句中對字段進行null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描，

如： select id from t where num is null 可以在num 上設定預設值0，確定表中num 列沒有null 值，然後這樣查詢： select id from t where num=0

​ c. 并不是所有索引對查詢都有效，SQL 是根據表中資料來進行查詢優化的，當索引列有大量資料重複時,查詢可能不會去利用索引，如一表中有字段sex，male、female 幾乎各一半，那麼即使在sex 上建了索引也對查詢效率起不了作用。

​ d. 索引并不是越多越好，索引固然可以提高相應的select 的效率，但同時也降低了insert 及update 的效率，因為insert 或update 時有可能會重建索引，是以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6 個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

​ e. 應盡可能的避免更新索引資料列，因為索引資料列的順序就是表記錄的實體存儲順序，一旦該列值改變将導緻整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新索引資料列，那麼需要考慮是否應将該索引建為索引。

​ f. 盡量使用數字型字段，若隻含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接配接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接配接時會逐個比較字元串中每一個字元，而對于數字型而言隻需要比較一次就夠了。

​ g. 盡可能的使用varchar/nvarchar 代替char/nchar ，因為首先變長字段存儲空間小，可以節省存儲空間，

其次對于查詢來說，在一個相對較小的字段内搜尋效率顯然要高些。

​ h. 盡量使用表變量來代替臨時表。如果表變量包含大量資料，請注意索引非常有限（隻有主鍵索引）。

​ i. 避免頻繁建立和删除臨時表，以減少系統表資源的消耗。

​ j. 臨時表并不是不可使用，适當地使用它們可以使某些例程更有效，例如，當需要重複引用大型表或常用表

中的某個資料集時。但是，對于一次性事件，最好使用導出表。

​ k. 在建立臨時表時，如果一次性插入資料量很大，那麼可以使用select into 代替create table，避免造成大量log，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然後insert。

​ l. 如果使用到了臨時表，在存儲過程的最後務必将所有的臨時表顯式删除，先truncate table ，然後drop

table ，這樣可以避免系統表的較長時間鎖定。

2)SQL 語句方面

​ a. 應盡量避免在where 子句中使用!=或<>操作符，否則将引擎放棄使用索引而進行全表掃描。

​ b. 應盡量避免在where 子句中使用or 來連接配接條件，否則将導緻引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20 可以這樣查詢： select id from t where num=10 union all

select id from t where num=20

​ c. in 和not in 也要慎用，否則會導緻全表掃描，如： select id from t where num in(1,2,3) 對于連續的

數值，能用between 就不要用in 了： select id from t where num between 1 and 3

​ d. 下面的查詢也将導緻全表掃描： select id from t where name like ‘%abc%’

​ e. 如果在where 子句中使用參數，也會導緻全表掃描。因為SQL 隻有在運作時才會解析局部變量，但優化

程式不能将通路計劃的選擇推遲到運作時；它必須在編譯時進行選擇。然而，如果在編譯時建立通路計劃，變量的

值還是未知的，因而無法作為索引選擇的輸入項。如下面語句将進行全表掃描： select id from t where

[email protected] 可以改為強制查詢使用索引： select id from t with(index(索引名)) where [email protected]

​ f. 應盡量避免在where 子句中對字段進行表達式操作，這将導緻引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100 應改為: select id from t where num=1002

​ g. 應盡量避免在where子句中對字段進行函數操作，這将導緻引擎放棄使用索引而進行全表掃描。如：select

id from t where substring(name,1,3)= ‘ abc ’ – name 以abc 開頭的id select id from t where

datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id 應改為: select id from t where name

like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

​ h. 不要在where 子句中的“=”左邊進行函數、算術運算或其他表達式運算，否則系統将可能無法正确使用

索引。

​ i. 不要寫一些沒有意義的查詢，如需要生成一個空表結構： select col1,col2 into #t from t where 1=0 這

類代碼不會傳回任何結果集，但是會消耗系統資源的，應改成這樣： create table #t(…)

​ j. 很多時候用exists 代替in 是一個好的選擇： select num from a where num in(select num from b)

用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)

​ k. 任何地方都不要使用select * from t ，用具體的字段清單代替“”，不要傳回用不到的任何字段。

​ l. 盡量避免使用遊标，因為遊标的效率較差，如果遊标操作的資料超過1 萬行，那麼就應該考慮改寫。

​ m. 盡量避免向用戶端傳回大資料量，若資料量過大，應該考慮相應需求是否合理。

​ n. 盡量避免大事務操作，提高系統并發能力。

3)java 方面：重點内容

​ a.盡可能的少造對象。

​ b.合理擺正系統設計的位置。大量資料操作，和少量資料操作一定是分開的。大量的資料操作，肯定不是ORM架構搞定的；

​ c.使用jDBC 連結資料庫操作資料；

​ d.控制好記憶體，讓資料流起來，而不是全部讀到記憶體再處理，而是邊讀取邊處理；

​ e.合理利用記憶體，有的資料要緩存。