一、初識資料庫
1、什麼是資料庫
資料庫叫database ，簡稱DB，是長期存放在計算機内，有組織、可共享的大量資料的集合；資料庫就是存放資料的倉庫，專業的資料庫系統具有較小的資料備援度，較高的資料安全性，易擴充性。
資料庫設計原則：
mysql主要優勢：一個應用對應一個資料庫
2、資料庫分類
| 關系型資料庫（保證資料的一緻性，能建立關系） | 非關系型資料庫(放在不同的伺服器上) |
|---|---|
| MySQL（中小型企業免費） | MongoDB（文檔存儲） |
| Oracle（大型電商網站） | Redis（鍵值存儲） |
| SQL Server（政府網站，大學教育） | Memcached（鍵值存儲） |
| maradb（mysql開源版本的一個分支） | Hbase（列存儲） |
| db2（銀行系統） | Neo4J（圖形） |
| sybase（被淘汰） |
補充：ER關系圖
| 實體用 | 屬性 | 關系 |
|---|---|---|
| 方框 | 橢圓 | 菱形 |
補充：伺服器
伺服器是一台電腦，這台電腦安裝了伺服器軟體，這些軟體會監聽不同的端口号，根據使用者通路的端口号提供不同的服務
3、DBMS、DBS、DBA差別
- DBMS是資料庫管理系統，是一種系統軟體，包括資料庫和用于資料庫通路管理的接口系統，通常将DBMS直接稱為資料庫，嚴格意義上說mysql屬于DBMS
- DBS是一個實際可運作的系統，由軟體、資料庫、資料庫管理者組成
- DBA資料庫管理者
4、MySQL簡介
-
概念
是現行的開源、免費的關系型資料庫 -
特點
- 運作速度快
- 使用成本低
- 容易使用
- 可移植性強
- 适用更多使用者
-
mysql語句分類
DDL DML DQL DCL 資料定義語言 資料操縱語言 資料查詢語言 ：資料控制語言，定義通路權限、取消通路權限，安全設定 create、drop、alter ，insert、update、delete select grant
5、mysql的安裝、解除安裝及啟動mysql服務
-
安裝
- 運作安裝程式、在啟動配置教程以前，一路下一步直到finish
- 第一次finish之後啟動服務配置教程
- 第一個induce mysql bin dinctory to windows path
- 第二個端口号不要去修改，字元集要選擇utf-8，預設端口号：3306
-
解除安裝
- 打開控制控制台删除軟體
- 删除mysql安裝目錄的所有檔案
- 删除mysql資料存放的檔案，C:\ProgramData\MySQL
-
啟動mysql服務方式
- 任務管理器打開
- 指令行輸入：service(檢視本地服務)
- dos視窗輸指令：net start mysql
-
MysqlInsranceConfig:配置向導
- programDate：資料檔案的儲存路徑（預設是隐藏的，在C槽下）
- mysql的安裝目錄下的my.ini檔案：相應的一些配置，可配置編碼格式等
- mysql -uroot -paaaaa123 進入mysql
- show databases顯示所有的資料庫
- select version()顯示mysql版本号
二、DDL語句建立資料庫和資料表
1、連接配接資料庫（在dos視窗下）
- cmd——以管理者身份運作
- 啟動mysql資料庫服務：net start mysql
- 登入mysql：mysql -uroot -paaaaa123（或者：mysql -hlocalhost -uroot -paaaaa123）注意-p以後不能有空格
- exit退出mysql
- show databases;（顯示資料庫，一定要加分号）
- use mysql
- show tables（顯示資料庫中的表）
- sql語句select * from user\G;顯示user使用者清單
- 修改user使用者密碼：update user set password=password'（1234' ） where user=‘root’;,flush privileges;重新整理下
- ？尋求幫助
- 檢視mysql所支援的引擎類型、表類型：SHOW ENGINES;
- 檢視預設引擎：SHOW VARIABLES LIKE 'storage_engine';
- 顯示變量：show variables like'auto%'
2、資料庫操作
-
建立資料庫
CREATE DATABASE [IF NOT EXISTS ]資料庫名;
-
删除資料庫
DROP DATABASE [IF EXISTS] 資料庫名;
-
修改資料庫字元集
alter database 資料庫名 character set 字元集
- 檢視資料庫
- 檢視所有資料庫：show databases
- 檢視資料庫定義：show create databse 資料庫名
- 檢視目前正在使用的資料庫：select database()
-
選中資料庫
use 資料庫名
3、表結構操作
- 建立表
-
create table 表名(
列名 列的類型 列的限制,
列名 列的類型 列的限制
)【表類型】【表字元集】【表注釋】
- 代碼示例：
#建立學生表，并設定表類型、字元集 CREATE TABLE `student` ( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '主鍵、學号', `psd` VARCHAR(20) COLLATE utf8_estonian_ci NOT NULL DEFAULT '123456' COMMENT '密碼', `name` VARCHAR(30) COLLATE utf8_estonian_ci NOT NULL DEFAULT '匿名' COMMENT '學生姓名', `sex` VARCHAR(2) COLLATE utf8_estonian_ci NOT NULL DEFAULT '男' COMMENT '性别', `birsday` DATETIME DEFAULT NULL, `email` VARCHAR(20) COLLATE utf8_estonian_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci
-
- 注釋
字段注釋： comment'注釋的内容' 表注釋：comment='注釋的内容' -
列的限制
primary key 主鍵限制
unique : 唯一限制
not null 非空限制
- 注意事項
- 除了最後一個列不用逗号隔開，每一列都要逗号隔開
- 表名如果變藍了表明和資料庫有沖突，這時候要加上反引号·
- 字元串類型的要用單引号引起來
- 有多個屬性的時候用空格隔開就好
- commend後面加單引号注釋内容
-
代表整個注釋
- 設定嚴格檢查模式
SET sql_mode='strict_trane_tables';
- 列類型
- 數值型
- tinyint：非常小的資料
- int：标準整數
- double：雙精度浮點數
- decimal【（M,D）】：字元串形式浮點數，M表示總位數，D表示保留小數位數
- 字元串型
- char：固定長度
- vachar：可變長度，括号内代表的是字元的個數
- text：文本串
- 日期和時間
- datetime：日期和時間，預設是null
- timestamp：常用于顯示目前時間，預設是目前日期和時間
- Null值
- 了解為沒有值或未知值
- 不要用null進行算術運算
- 數值型
-
表的類型
常見的MyISAM，InnoDB
名稱 MyISAM InnoDB 事務處理 no yes 外鍵限制 資料行鎖定 全文索引 表空間大小 較小 較大 -
資料表的存儲位置
都位于C:\ProgramData\MySQL目錄下
隻有一個frm檔案 frm、wyd、myi三個檔案 - 資料字段屬性
- unsigned：無符号，聲明該資料不允許為負數
- zerofill：0填充，不足位數用0填充，如int（3）5則為005
- auto_increment：自動增長，常用于設定主鍵，且為整數，定義起始值和步長：
- 定義起始值：auto_increment=100(建表時在右括号右邊寫，影響的是目前表，對整個資料庫的其他表沒有影響
- 定義自增步長：set auto_increment_ncrement=5，會對所有資料庫受影響
- null和not null ：預設為null，若設定為not null則必須有值
- default：設定預設值
-
删除表
drop table 表名
- 修改表
#修改表名 ALTER TABLE 表名 RENAME AS test1; #修改表的字元集 alter table 表名 character set 字元集 #添加列 ALTER TABLE 表名 ADD 列名 列類型 屬性 ; #修改列 alter table 表名 modify 列名 列類型 屬性 ; #修改列名 alter table 表名 change 舊列名 新列名 列類型 屬性 ; #删除列 alter table 表名 dop 列名 - 檢視表
檢視目前資料庫所有的表名 show tables 檢視表的定義結構/建立語句 show create table 表名 檢視表的結構 desc 表名
4、儲存sql檔案以及打開sql檔案
儲存Ctrl+s
建立查詢編輯器——在同一标簽中打開檔案（在不同标簽中打開）
對于圖像聲音視訊用text、blob存儲資料
一般沒有必要直接存儲圖像，而存儲的是路徑用字元串
三、DML語句對表中資料CRUD操作
1、主鍵&外鍵
- 主鍵咋一個表裡面可有也可以沒有，
- 一個表裡面的外鍵既可以有一個也可以有多個，
- 建立主外鍵關系的有主鍵的表為主表，有外鍵的表成為子表或者從表
- 能夠保證資料的完整性和 正确性
- 對于有主外鍵關系的表，資料插入操作要有一定順序，先放主鍵資料，再放外鍵資料 ，先删除子表，再删除主表
- 主鍵和外鍵，列名可以不同，但是資料類型一定要相同
- 主鍵和外鍵建立聯系第一種方法在建表的時候就聯系，關鍵詞是constraint，外鍵名一般以FK開頭 foreign key代表外鍵，引用主鍵用關鍵詞reference
- 主鍵&外鍵相關的SQL語句
建立主鍵&外鍵 1、建立此表的同時就建立外鍵 主鍵：PRIMARY KEY可寫在列後面，也可以字段都寫完了在最後一行寫明PRIMARY KEY（字段名 ） 外鍵：CONSTRAINT FR_gradeId FOREIGN KEY (要添加主鍵的字段) REFERENCES 引用表(引用表中的哪個字段)（FR_gradeId字段名） 2、建立字表完畢後，修改子表增加外鍵 ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY (要添加主鍵的字段) REFERENCES 引用表(引用表中的哪個字段); 删除外鍵 ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名; （删除了之是以還能看到外鍵在表中，是因為外鍵會默默的建立了一個索引，要想徹底删除還要加上一句話删除索引 ALTER TABLE 表名 DROP INDEX 外鍵名; 删除外鍵要有兩句話才能徹底删除）
2、插入資料
- insert into 表名（列名1，列名2） values（值1，值2）
- insert into 表名 values（值1，值2）
-
insert into 表名（列名1，列名2） values（值1，值2），（值1，值2）
批量插入資料
- 注意點：
- 批量插入比單條插入效率更高，但是更容易出現問題
- 插入全部列名可以省略，插入部分列的話就不能省略表名後面的列名。
- 有預設值的時候在values裡面可以寫預設值也可以寫default
- 如果不寫列名的情況下即使有些列可以為空，但是不能不寫要寫null，最好都寫上列名，如果外鍵值超過了主表的主鍵數目，會報錯
3、删除資料
- delete from 表名 【where條件】
- truncate table 表名
- delete 和 truncate 的差別
- delete 是DML語句 一條一條删除表中資料
- truncate是DDL 先删除表再重建表
- 關于哪條執行效率高，具體要看表中的資料量，如果資料比較少，delete高效，資料多，則truncate效率高
- 當使用不帶where條件的delete from删除資料時，自增目前數值依然從原來基礎上進行，會記錄日志，不删除表結構，但使用truncate table删除表中全部資料的時候，自增目前數值不從原來基礎上進行，從原來自增初始值開始。如下例子：不會記錄日志
- 兩種不同村粗引擎，在使用delete from删除全部資料後，自增列初始值表現的不同：同樣使用delete from清空資料，重新開機資料庫服務後，對于INNODB的表，自增列從初始值從新開始（比如開始預設初始值是1，那就從1再開始），而對于MYISAM型的表，自增序列從上一個自增資料基礎上開始，原因是：INNODB的資料是儲存在内容中的，重新開機資料可服務後，之前的也就都沒有了，而MYISAM是儲存在檔案中，就算重新開機，資料依然存在。
4、修改資料
- update 表名 set 列名=值【where條件】
四、DQL查詢語句
1、AS為字段取别名
AS可省略
2、Dinstinct去重複項
select distinct 字段名 from 表名
3、通用格式
select [distinct] [*] [列名1,列名2] from 表名 where 條件
group by...
having
order by 4、基礎查詢
#查詢資料用DQL語句
#1、核心查詢（*代表查詢所有列，效率比較低）
SELECT * FROM student;
#2、查詢指定列，查詢student的學号和姓名,在mysql中列名不區分大小寫
SELECT studentno,studentname FROM student;
#3、被查出的列取别名（給列取别名as可以省略）
SELECT studentno AS 學号,studentname AS 姓名 FROM student;
SELECT studentno 學号,studentname 姓名 FROM student;
#6、給表取取别名（給表取别名as可以省略），别名不會該表原表的列名，隻是為了看的
SELECT studentno 學号,studentname 姓名 FROM student AS 學生表;
SELECT studentno 學号,studentname 姓名 FROM student 學生表;
#7、as為查詢結構（如函數）取一個新的名字
SELECT CONCAT('姓名：',studentName)AS 新姓名 FROM student;
#8、檢視那些同學參加了考試（學号）,去除重複項，用關鍵字distinct，預設為all代表查詢所有行
SELECT DISTINCT studentno FROM result;
注意：distinct一定寫前面
SELECT DISTINCT NAME NAME,english FROM exam;
#9、select中可以出現表達式
SELECT @@auto_increment_increment;
SELECT VERSION();
SELECT 100*3-1 AS 計算結果
SELECT studentno,studentresult+1 AS 提分後 FROM result;
#10、滿足條件的查詢(where) 考試成績95-100
SELECT studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100; 5、模糊查詢
#13、精确查詢
SELECT studentno,StudentResult
FROM result
WHERE studentno=1000;
#15、查詢除了1000号這個學生，其他學生的考試成績！=
SELECT studentno,StudentResult
FROM result
WHERE studentno !=1000;
#16、或者用not和！=一樣
SELECT studentno,StudentResult
FROM result
WHERE NOT studentno =1000;
#17、模糊查詢between and/ like/in/null
#查詢姓李的同學的學号和姓名
#like及結合使用的通配符，%（0到任意一個字元） _(一個字元)
#查詢所有姓李的同學
SELECT studentno,StudentName
FROM student
WHERE StudentName LIKE '李%';
#查詢姓李的，名字是三個字的
SELECT studentno,StudentName
FROM student
WHERE StudentName LIKE '李__';
#查詢所有帶有“文字的”（不管開頭和結尾）常用
SELECT studentno,StudentName
FROM student
WHERE StudentName LIKE '%文%'; 轉義字元%——\%
_——\_
如何不想用\作為轉義字元，可以自定義轉義字元，使用自己定義的轉衣服：escape ‘你定義的轉義符’但是%和_是不可以自行設計的
#模糊查詢之 in
SELECT studentName,studentNo FROM student
WHERE studentNo IN(1000,1001,1002,1003);
SELECT studentName,studentNo FROM student
WHERE Address IN('北京','南京');
#模糊查詢之 null
注意：隻有是顯示null的才為空，對于空白什麼都沒有的并不是空null而是字元串為空，如： #查詢家庭住址沒有寫的同學
#1、錯誤用法
SELECT studentname FROM student
WHERE Address IS NULL;
#2、用空字元串
SELECT studentname FROM student
WHERE Address='';
#3、一般這種類型的查詢用這種寫法
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL; #查詢出生日期沒有填寫的同學，=null是錯誤的，和null比較要用iis null
SELECT studentno FROM student
WHERE BornDate IS NULL;
#對于不是空的要用is not null
SELECT studentname FROM student
WHERE BornDate IS NOT NULL; 6、分組查詢
#不同課程的平均分、最高分、最低分
SELECT subjectname, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.subjectno
#分組後再篩選執行的sql語句篩選
SELECT subjectname, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
GROUP BY r.subjectno
HAVING 平均分>80
ORDER BY 平均分 DESC
LIMIT 0,4 7、連接配接查詢
| 内連接配接 | 左連接配接 | 右連接配接 | 自連接配接 | 等值連接配接 | 非等值連接配接 |
|---|---|---|---|---|---|
| nner join | left join | right join | \ | ||
| 查詢兩個表結果集中的交集 | 以左表為基準，右邊表來一一比對，比對不上的傳回左表記錄，右表以null填充 | 以右表為基準，右邊表來一一比對，比對不上的傳回左表記錄，左表以null填充 | 自連接配接的表一定要取别名（AS）子欄目父欄目問題 | 和内連一樣，隻是寫法不同 | 差別于等值連接配接，不加where |
等值連接配接：
SELECT s.StudentNo,StudentName,subjectno,StudentResult
FROM student s,result r
WHERE s.StudentNo = r.StudentNo; #自連接配接代碼
CREATE TABLE IF NOT EXISTS category(
categoryId INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '目前欄目',
pId INT(10) NOT NULL COMMENT '目前欄目的父欄目',
categoryName VARCHAR(50) NOT NULL,
PRIMARY KEY (categoryId)
)
INSERT INTO category
VALUES (2,1,'美術設計'),
(3,1,'軟體開發'),
(4,3,'資料庫基礎'),
(5,2,'ps基礎'),
(7,2,'色彩搭配'),
(8,3,'PHP基礎'),
(9,3,'java基礎');
SELECT * FROM category;
SELECT categoryName '知足上進' FROM category;
#将各個欄目的父子欄目顯示出來（父欄目名稱 子欄目名稱）
SELECT a.categoryName AS '主欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b;
WHERE a.categoryId=b.pId; - 一定要先寫連接配接查詢再寫where
-
多重嵌套連接配接：
注意：inner join ,和from以及on後面都不能用,隔開，否則會報1064錯誤
#參加了參加了考試的同學（學号、學生姓名、科目名、分數） SELECT s.StudentNo,StudentName,subjectname,StudentResult FROM student AS s INNER JOIN result AS r ON s.StudentNo = r.StudentNo INNER JOIN `subject` AS sub ON r.SubjectNo=sub.SubjectNo;
8、排序
group by having
#查詢《資料庫結構—1》的所有考試的同學（學号、學生姓名、科目名、分數）
SELECT s.StudentNo,StudentName,subjectname,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` AS sub
ON r.SubjectNo=sub.SubjectNo
WHERE SubjectName='資料庫結構-1'
ORDER BY StudentResult DESC,studentno DESC #1、預設升序asc 2、desc為降序
#常見錯誤：ORDER BY StudentResult ,studentno DESC 這時候StudentResult升序，studentno降序，desc是就近原則 9、分頁查詢
- limit 索引，頁容量
- 目前頁碼-1）*頁容量，頁容量
#分頁查詢
##查詢《資料庫結構—1》的所有考試的同學（學号、學生姓名、科目名、分數）
#每頁顯示五條資料出來
SELECT s.StudentNo,StudentName,subjectname,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` AS sub
ON r.SubjectNo=sub.SubjectNo
WHERE SubjectName='資料庫結構-1'
ORDER BY StudentResult DESC,studentno DESC
#limit 0,5 #從哪條記錄開始起始行0，要顯示幾行
LIMIT 5,5 #第二頁
LIMIT 10,5 #第三頁 （等于linit 5 offset 0) 10、子查詢
- 定義：子查詢就是在查詢語句中的where條件子句中，又嵌套了另外一個select查詢語句
#子查詢
#分部寫簡單sql語句，然後去嵌套
SELECT studentno ,studentname FROM student WHERE studentno IN()
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=()#1\2\3\4
SELECT subjectno FROM `subject` WHERE SubjectName='高等數學-2' #2
SELECT studentno, studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE SubjectName='高等數學-2' )) 五、mysql函數
1、數學函數
| ABS（) | CEILING() | FLOOR() | RAND() | |
|---|---|---|---|---|
| 絕對值 | 大于等于我的最小的整數 | 小于等于我的最大的整數 | 傳回0-1之間的随機數 | 每次生成固定的0-1之間的随機數(以某個數作為種子) |
| SELECT ABS(-8) '-8的絕對值' | SELECT CEILING(9.8)=10 | SELECT FLOOR(9.8)=9 |
2、字元串函數
#傳回字元串中包含的字元數
SELECT CHAR_LENGTH ( '好好學習資料庫')
#合并字元串
SELECT CONCAT('我','愛')
#替換字元串，從某個位置開始，替換某個長度，替換的内容
SELECT INSERT('我愛你婺源',1,3,'很愛')
SELECT INSERT('我愛你婺源',1,3,'很愛') #如果起始位置超過字元串長度，則傳回原長度
#變小寫
SELECT LOWER('I LOVE YOU')
#變大寫
SELECT UPPER('i love you')
#從左邊截取指定長度的字元串
SELECT LEFT('我愛你中國',3)
##從右邊截取指定長度的字元串
SELECT RIGHT('我愛你中國',3)
#替換字元串（要替換的字元串，要替換的文字，替換的内容）
SELECT REPLACE('中國歡迎你，你好','你','你們')
#截取(從哪個位置開始截取，截取多長）
SELECT SUBSTRING('中國歡迎你，你好',1,2)
#反轉
SELECT REVERSE('中國歡迎你') 3、日期和時間函數
#獲得目前日期
SELECT CURRENT_DATE()
SELECT CURDATE()
#獲得目前日期和時間
SELECT NOW()
SELECT LOCALTIME()
SELECT SYSDATE()
#分别擷取日期中的某個部分
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW()) 4、系統資訊的函數
SELECT VERSION()
SELECT USER() 5、聚合函數
#聚合函數：傳回的是一個值，不要出現多個值
#count（字段名） 是非空值的計數
count(*)傳回所有列的統計
SELECT COUNT(StudentName) FROM student
SELECT COUNT(1) FROM student
#sum()求總和
SELECT SUM(StudentResult) AS 總分 FROM result
SELECT AVG(StudentResult) AS 平均分 FROM result
SELECT MAX(StudentResult) AS 最高分 FROM【】 result
SELECT MIN(StudentResult) AS 最低分 FROM result count（*)盡量少用
六、MySql事務
1、事務定義
事務就是将一組sql語句放在同一批次内去執行，如果一個sql語句錯誤，則該批次的所有sql語句都将取消執行，最能了解的就是銀行轉賬
注意： mysql事務隻支出innoDB和BDB資料表類型
2、事務的ACID原則
-
原子性（A）
組sql語句是當做一個整體執行的，不能單獨執行其中的某一條，要麼全部成功，要麼全部失敗
-
一緻性（C）
要麼都是事務送出前的狀态，要麼都是事務送出以後的狀态，不可能存在事務在中間的什麼狀态
-
隔離性（I）
每一個事務處理之間互不影響，彼此獨立和透明，事務間不能交叉
-
持久性（D）
事務一旦送出成功，對事務的影響是永久 的
3、mysql事務實作方法
- set autocomment=0關閉mysql的自動送出
-
start transaction
開啟一個事務
- 執行的sql語句
-
commit/rollback
送出或者復原
-
set autocomment=1
還原mysql預設的自動送出
代碼示例：
#使用事務模拟實作轉賬
CREATE TABLE IF NOT EXISTS account(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
cash DECIMAL(9,2) NOT NULL
);
INSERT INTO account (`name`,cash)
VALUES ('A',2000),('B',10000);
SELECT * FROM account;
DELETE FROM account;
#沒有異常情況時候的事務
SET autocommit=0;
START TRANSACTION;
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT;
SET autocommit=1;
#有問題時候的事務用rollback撤銷，回到事務開始最初的狀态
SET autocommit=0;
START TRANSACTION;
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
ROLLBACK；
SET autocommit=1; 七、MySql索引和視圖
1、索引分類
| 主鍵索引 | 唯一索引 | 正常索引 | |
|---|---|---|---|
| primary key | unique | index | fulltext |
| 避免同一個表中某資料列的值重複 | 不宜添加過多正常索引 | 隻能英語myisam，并且隻能是vachar、char、test類型 |
唯一索引和主鍵索引差別：
- 主鍵索引隻有一個、唯一索引可以有多個
- 主鍵索引非空，唯一索引可以null
- 一個列上有很多索引，資料庫會去選一個效率高的索引執行
2、添加索引
#添加索引
#方式一：在建立表申明列的時候添加上
CREATE TABLE text1(
id INT(3) PRIMARY KEY,
testno VARCHAR(10) UNIQUE,
c VARCHAR(50),
d VARCHAR(20),
e TEXT,
INDEX `index_c`(c,d),
FULLTEXT(e)
)ENGINE MYISAM
***添加正常索引的時候，可以添加多個列為正常索引，寫在前面的列有優先權，索引名字用``隔開，而不是逗号。
#方式二，将所有列都申明完畢後再添加索引，統一添加索引
CREATE TABLE text2(
id INT(3) ,
testno VARCHAR(10) ,
c VARCHAR(50),
d VARCHAR(20),
e TEXT,
PRIMARY KEY(id),
UNIQUE KEY(testno),
INDEX `index_c`(c,d),
FULLTEXT(e)
)ENGINE MYISAM
#方式三：建立表完畢後修改表的時候去添加索引
CREATE TABLE text3(
id INT(3) ,
testno VARCHAR(10) ,
c VARCHAR(50),
d VARCHAR(20),
e TEXT
)ENGINE MYISAM
ALTER TABLE text3 ADD PRIMARY KEY(id);
ALTER TABLE text3 ADD UNIQUE KEY(testno);
ALTER TABLE text3 ADD INDEX(c,d);
ALTER TABLE text3 ADD FULLTEXT(e); - 注意：EXPLAIN SELECT * FROM student WHERE studentno='1000'，explain代表的是查詢查詢的具體明細，包括如下資訊：
- 全文索引，你要設定全文索引的列，查詢的條件不能超過全文資料的50%，否則全文索引就沒用了（隻能用在字元串類型varchar和text上，隻能用于MYISAM）
- 設定全文索引
ALTER TABLE student ENGINE=MYISAM; EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('李%');
3、顯示索引資訊
- 在目錄index下可以看到
- 利用sql語句顯示索引資訊：SHOW INDEX FROM student;
4、删除索引
1、DROP INDEX 索引名 ON 表名;
DROP INDEX testno ON text3;
2、ALTER TABLE 表名 DROP INDEX e索引名
ALTER TABLE text3 DROP INDEX e;
3、#删除主鍵索引
ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE text3 DROP PRIMARY KEY; 5、複合索引
A-B-C先把A用到
6、索引準則
- 選擇建立索引的列
- 頻繁搜尋的列
- 經常用作查詢的列
- 經常排序、分組的列
- 經常用作連接配接的列（主鍵、外鍵）
- 不介意使用索引的列
- 僅包含幾個不同值的列
- 小型表
7、視圖
- 視圖是儲存在資料庫中的select查詢，是一種虛拟表，使用視圖原因，一個是出于安全考慮，使用者不必看到整個資料庫的結構，另一個是複合使用者日常業務邏輯
- 建立視圖
-
create view 視圖名 AS 查詢語句 -
删除視圖
drop view 視圖名
-
檢視視圖
select 字段1，字段2 from 視圖名
-
- 每個視圖中可以使用多個表
- 一個視圖可以嵌套另一個視圖，單最好不要超過三層
- 對視圖進行增加、修改、删除操作會直接影響表中資料
- 當視圖來自多個表時，不允許添加和删除資料
- 選中系統資料庫information_schnma，然後 select * from vieww;可檢視所有視圖
八、Mysql資料的備份與恢複
1、使用指令mysqldump備份
mysqldump是和mysql同級的一個指令！
備份整個資料庫
mysqldump -uroot -p school>E:/java/beifen/school.sql
執行備份：
*密碼可以先不P輸出，最後以密碼文的形式
*>千萬别丢了，如果丢了，會報mysqldump couldnot find。。。。
備份特定的表
mysqldump -uroot -p school grade student >E:/java/beifen/school1.sql
mysqldump設定之指定哪些語句不顯示
mysqldump -uroot -p --skip-add-drop-table school grade student >E:/java/beifen/school2.sql
mysqldump設定之顯示insert into加上列名
加上一個-c即可
mysqldump -uroot -p --skip-add-drop-table -c school grade student >E:/java/beifen/school2.sql
備份檔案解析：
1、/*和*/包裹的資料代表是可以被mysql執行的注釋，但是不能被其他資料庫執行
2、--包圍的是注釋
3、 !40101是版本号
**mysql --help|more會以一屏顯示，一行一行的看 2、使用mysql底下的腳本source
1、進入mysql
mysql -urrot -p
2、選擇要備份到哪裡
use test
3、選擇要備份的資料庫
source E:/java/beifen/school.sql 3、使用sqlyong工具備份
注意點：1、打勾的時候一定要注意，不要勾選use database和carat database
4、用sql指令進行備份資料
#将school庫中的student表中的學号和姓名這兩列備份出去
USE school;
#注意：備份出去的檔案不可以提前存在
SELECT studentno,StudentName INTO OUTFILE 'E:/java/beifen/student.sql' FROM student;
#将備份出去的資料導入到test庫中的stable表裡來
USE test;
CREATE TABLE stable(
id INT(4),
sname VARCHAR(20)
)
LOAD DATA INFILE 'E:/java/beifen/student.sql' INTO TABLE stable(id,sname);
SELECT * FROM stable; 九、資料庫設計
1、設計目的
- 效率高
- 節省資料的儲存空間
- 便于進一步擴充
- 使應用程式的開發變得更容易
2、設計資料庫的步驟
- 收集資訊
- 辨別實體
- 辨別每個實體之間的關系
3、繪制E-R圖
矩形表示實體、橢圓表示屬性、菱形表示關系
4、繪制資料庫模型圖
用Visio2007版本及以下版本繪制更好
5、三大範式
-
第一範式（1NF）
目标是確定每列 原子性，如果每列或者每個屬性值都是不可再分的最小資料單元，則滿足第一範式
- 第二範式（2NF）
- 一個表隻描述一件事情，目标是確定表中的每列都和主鍵相關
- 如果一個關系滿足第一範式，并且除了主鍵以外的其他鍵全部依賴于主鍵，則滿足第二範式
-
第三範式（3NF）
目标是確定每列值都和主鍵直接相關，而不是間接相關，如果一個關系滿足第二範式，并且除了主鍵以外的其他列都隻能依賴于主鍵，列于列之間不存在互相依賴關系
十、JDBC連接配接資料庫
1、JDBC概念
JDBC是實作java程式對各種資料庫的通路，是一組類和接口，位于java.sql與javax.sql包
2、通過JDBC連接配接資料庫
//1、加載驅動
Class.forName("com.mysql.jdbc.Driver");
//2、建立連接配接
String url="jdbc:mysql://localhost:3306/myschool";
String user ="root" ;
String password = "aaaaa123";
con= DriverManager.getConnection(url, user, password);
//建立sql語句
String sql ="SELECT studentno,studentName FROM student";
//建立對象
pstm =con.prepareStatement(sql);
//執行sql語句
rs=pstm.executeQuery();
//處理結果
System.out.println("編号：\t姓名：");
while(rs.next()) {
System.out.println(rs.getInt("studentno")+"\t"+rs.getString("studentName"));
}
//關閉資源
rs.close();
pstm.close();
con.close(); Class.forName() 後加載 反射，事先不知道加載哪個類，運作時進行加載（橋接） 而Student stu =new Student() 先加載類
3、使用JDBC操作資料庫（增删改查）
//增加資料
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
public class Test3 {
public static void main(String[] args) {
ResultSet rs =null;
PreparedStatement pstm=null;
Connection con =null;
//1、加載驅動
try {
Class.forName("com.mysql.jdbc.Driver");
//2、建立連接配接
String url="jdbc:mysql://localhost:3306/kgcnews";
String user ="root" ;
String password = "aaaaa123";
con= DriverManager.getConnection(url, user, password);
//建立sql語句
String sql ="INSERT INTO news_category (NAME,createDate) VALUES (?,?)";
//建立對象
pstm =con.prepareStatement(sql);
//給占位符指派
pstm.setString(1, "哈哈");
pstm.setTimestamp(2, new Timestamp(10));
//執行sql語句
int i=pstm.executeUpdate();
//處理結果
if(i>=1) {
System.out.println("增加成功");
}else {
System.out.println("未增加");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(null !=pstm) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(null !=con) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
增删改操作類似，隻是替換sql語句即可
十一、思維導圖
1、更改
2、全景
