一、在Ubuntu系統(tǒng)下安裝MySQL數(shù)據(jù)庫(kù)

1、更新軟件源，在確保ubuntu系統(tǒng)能正常上網(wǎng)的情況下執(zhí)行以下命令

sudo apt-get update

2、安裝MySQL數(shù)據(jù)庫(kù)及相關(guān)軟件包

# 安裝過程中設(shè)置root用戶的密碼 123456
sudo apt-get install mysql-server 
?
# 安裝訪問數(shù)據(jù)庫(kù)的客戶端
sudo apt-get install mysql-client 
?
# 安裝訪問數(shù)據(jù)庫(kù)的代碼庫(kù)
sudo apt-get install libmysqlclient-dev 

4、配置數(shù)據(jù)庫(kù)的字符集、開啟網(wǎng)絡(luò)連接

1、打開MySQL數(shù)據(jù)庫(kù)的配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
?
2、開啟mysql的網(wǎng)絡(luò)連接，注釋掉以下配置：
# bind-address ? ? ? ?  = 127.0.0.1
?
3、在文件38行后面添加以下配置：
lc-messages-dir = /usr/share/mysql
character_set_server=utf8
skip-external-locking
?
4、保存退出，并重啟數(shù)據(jù)庫(kù)
sudo service mysql restart

5、創(chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建新用戶

1、在終端登錄MySQL的root用戶
mysql -uroot -p123456
?
2、進(jìn)入MySQL的root用戶，創(chuàng)建數(shù)據(jù)庫(kù)
mysql> create database testDB;
?
3、創(chuàng)建test用戶，設(shè)置訪問權(quán)限，設(shè)置用戶密碼為123456
mysql> grant all privileges on testDB.* to 'test'@'%' identified by '123456';
?
4、刷新保存設(shè)置
mysql> flush privileges;
?
5、退出登錄
mysql> exit

6、測(cè)試MySQL數(shù)據(jù)庫(kù)

1、在終端登錄MySQL的test新用戶
mysql -utest -p123456
?
2、選擇要使用的數(shù)據(jù)庫(kù)
mysql> use testDB;
?
3、創(chuàng)建一張Student數(shù)據(jù)表
mysql> create table Student(name char(20),sex char,age int,addr varchar(100));
?
4、向Student數(shù)據(jù)表中插入一條數(shù)據(jù)
mysql> insert into Student values("hehe",'w',18,"杭州指針信息技術(shù)有限公司");
?
5、查詢Student數(shù)據(jù)表中的所有數(shù)據(jù)
mysql> select * from Student;

二、數(shù)據(jù)庫(kù)介紹

1、為什么需要數(shù)據(jù)庫(kù)

1、計(jì)算機(jī)的資源有限，不可能把數(shù)據(jù)全部存儲(chǔ)在內(nèi)存中，且內(nèi)存掉電后數(shù)據(jù)會(huì)丟失，為了能讓程序在關(guān)機(jī)重啟后繼續(xù)使用，必須把數(shù)據(jù)保存到磁盤的文件中。

2、隨著程序的功能越來越復(fù)雜、數(shù)據(jù)量越來越大，從文件中讀寫數(shù)據(jù)需要大量的重復(fù)性操作，從文件中讀取出指定的數(shù)據(jù)需要復(fù)雜的邏輯。

3、不同的程序它的訪問文件的操作不同，就意味著讀寫文件的代碼無法復(fù)用。

4、所以程序員非常需要一個(gè)統(tǒng)一的快速的訪問磁盤數(shù)據(jù)的工具。

5、使用數(shù)據(jù)庫(kù)程序員不需要自己管理數(shù)據(jù)，而是通過數(shù)據(jù)庫(kù)提供的接口進(jìn)行讀寫操作，至于數(shù)據(jù)在文件中是如何保存、查找與程序員無關(guān)。

2、什么是數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)指的管理數(shù)據(jù)的軟件（DBMS），而不是存儲(chǔ)數(shù)據(jù)的倉(cāng)庫(kù)。

3、數(shù)據(jù)庫(kù)的類型

關(guān)系型：

關(guān)系型數(shù)據(jù)庫(kù)，是指采用了關(guān)系模型來組織數(shù)據(jù)的數(shù)據(jù)庫(kù)，其以行和列的形式存儲(chǔ)數(shù)據(jù)，以便于用戶理解，關(guān)系型數(shù)據(jù)庫(kù)這一系列的行和列被稱為表，一組表組成了數(shù)據(jù)庫(kù)。用戶通過查詢來檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)，而查詢是一個(gè)用于限定數(shù)據(jù)庫(kù)中某些區(qū)域的執(zhí)行代碼。關(guān)系模型可以簡(jiǎn)單理解為二維表格模型，而一個(gè)關(guān)系型數(shù)據(jù)庫(kù)就是由二維表及其之間的關(guān)系組成的一個(gè)數(shù)據(jù)組織。

非關(guān)系型（redis）：

非關(guān)系型數(shù)據(jù)庫(kù)嚴(yán)格上不止一種數(shù)據(jù)庫(kù)，應(yīng)該是一種數(shù)據(jù)結(jié)構(gòu)化存儲(chǔ)方法的集合，可以是文檔或者鍵值對(duì)等。

NoSQL，泛指非關(guān)系型的數(shù)據(jù)庫(kù)，NoSQL最常見的解釋是“non-relational”， “Not Only SQL”也被很多人接受。NoSQL僅僅是一個(gè)概念，泛指非關(guān)系型的數(shù)據(jù)庫(kù)，區(qū)別于關(guān)系數(shù)據(jù)庫(kù)。

NoSQL是一項(xiàng)全新的數(shù)據(jù)庫(kù)革命性運(yùn)動(dòng)，其擁護(hù)者們提倡運(yùn)用非關(guān)系型的數(shù)據(jù)存儲(chǔ)，相對(duì)于鋪天蓋地的關(guān)系型數(shù)據(jù)庫(kù)運(yùn)用，這一概念無疑是一種全新的思維的注入。

4、目前主流關(guān)系型數(shù)據(jù)庫(kù)：

商用數(shù)據(jù)庫(kù)：OceanBase，Oracle，SQL Server，DB2，GaussDB

開源數(shù)據(jù)庫(kù)：MySQL，MariaDB

桌面數(shù)據(jù)庫(kù)：Access

嵌入式數(shù)據(jù)庫(kù)：SQLite

三、SQL語言介紹

1、什么是SQL

SQL是結(jié)構(gòu)化查詢語言的縮寫，是數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)委員會(huì)，用來訪問和操作數(shù)據(jù)庫(kù)的統(tǒng)一語言，所有的數(shù)據(jù)庫(kù)都支持SQL語言，也就是我們只需要學(xué)習(xí)SQL語言就可以操作所有的關(guān)系型數(shù)據(jù)庫(kù)。

雖然ANSI組織定義了統(tǒng)一的SQL語言標(biāo)準(zhǔn)，但不同的數(shù)據(jù)庫(kù)廠商對(duì)SQL的支持程度不同，有的還添加了新的語法，如果只使用標(biāo)準(zhǔn)的SQL語句理論上可以操作所有的數(shù)據(jù)庫(kù)，然后把每種數(shù)據(jù)庫(kù)特有的SQL語法稱為SQL的方言。

SQL語句不區(qū)分大小寫，但標(biāo)識(shí)符(表名、字段名)是區(qū)別的。

2、SQL語句有功能分類

數(shù)據(jù)控制語句：用于權(quán)限的賦予和回收。

數(shù)據(jù)定義語句：用于建立、修改、刪除數(shù)據(jù)庫(kù)對(duì)象(表、視圖等)。

數(shù)據(jù)操作語句：用于改變表中的數(shù)據(jù)(增、刪、改)。

數(shù)據(jù)查詢語句：根據(jù)不同的條件來查詢數(shù)據(jù)。

事務(wù)控制語句：用于維護(hù)數(shù)據(jù)庫(kù)的一致性。

3、SQL語句中的數(shù)據(jù)類型

MySQL 支持多種類型，大致可以分為三類：數(shù)值、日期/時(shí)間和字符串(字符)類型。

數(shù)值類型：

1、MySQL 支持所有標(biāo)準(zhǔn) SQL 數(shù)值數(shù)據(jù)類型。

2、這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL 和 DOUBLE PRECISION)。

3、關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。

4、BIT數(shù)據(jù)類型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

5、作為 SQL 標(biāo)準(zhǔn)的擴(kuò)展，MySQL 也支持整數(shù)類型 TINYINT、MEDIUMINT 和 BIGINT。下面的表顯示了需要的每個(gè)整數(shù)類型的存儲(chǔ)和范圍。

類型	byte	范圍（有符號(hào)）	無符號(hào)最大值
TINYINT	1	-128,127	255
SMALLINT	2	-32768，32767	65535
MEDIUMINT	3	-8388608，8388607	16777215
INT	4	-2147483648，2147483647	4294967295
BIGINT	8	-9,223372036854775808，9223372036854775807	18446744073709551615
FLOAT	4	(-3.402823466E+38，-1.175494351E-38) (1.175494351E-38，3.402823466351E+38)	1.175494 351E-38，3.402 823 466 E+38
DOUBLE	8	(-1.7976931348623157E+308，-2.225073858507 2014E-308) (2.2250738585072014 E-308，1.797 6931348623157 E+308)	2.225073 858507201 4 E-308，1.797693134 862 3157E+308

---

日期和時(shí)間類型：

1、表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

2、每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值。

3、TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述。

類型	byte	范圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	時(shí)間值或持續(xù)時(shí)間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和時(shí)間值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07'	YYYY-MM-DD hh:mm:ss	混合日期和時(shí)間值，時(shí)間戳

字符串類型：

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。

類型	byte	用途
CHAR	0-255	定長(zhǎng)字符串
VARCHAR	0-65535	變長(zhǎng)字符串
TINYBLOB	0-255	不超過 255 個(gè)字符的二進(jìn)制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65535	二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù)
TEXT	0-65535	長(zhǎng)文本數(shù)據(jù)
MEDIUMTEXT	0-16777215	中等長(zhǎng)度文本數(shù)據(jù)
MEDIUMBLOB	0-1677215	二進(jìn)制形式的中等長(zhǎng)度文本數(shù)據(jù)
LONGBLOB	0-4294967295	二進(jìn)制形式的極大文本數(shù)據(jù)
LONGTEXT	0-4294967295	極大文本數(shù)據(jù)

注意：

1、char(n) 和 varchar(n) 中括號(hào)中 n 代表字符的個(gè)數(shù)，并不代表字節(jié)個(gè)數(shù)，比如 CHAR(30) 就可以存儲(chǔ) 30 個(gè)字符。

2、CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長(zhǎng)度和是否尾部空格被保留等方面也不同。在存儲(chǔ)或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。

3、BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說，它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。

4、BLOB 是一個(gè)二進(jìn)制大對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲(chǔ)范圍不同。

5、有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對(duì)應(yīng)的這 4 種 BLOB 類型，可存儲(chǔ)的最大長(zhǎng)度不同，可根據(jù)實(shí)際情況選擇。

tinyint
smallint
int
bigint
float
double
?
date
timestamp
?
char 
varchar

三、MySQL數(shù)據(jù)庫(kù)的訪問方式

1、本地訪問

# 方便但有泄露密碼的風(fēng)險(xiǎn)
mysql -u用戶名 -p密碼 
?
# 建議這樣登錄
mysql -u用戶名 -p 

2、遠(yuǎn)程訪問

1、先使用 ssh 或 telnet 遠(yuǎn)程登錄MySQL服務(wù)器ssh 用戶名@ip 輸入密碼
?
2、mysql -u用戶名 -p

3、圖形客戶端登錄

sudo apt install mysql-workbench

1、創(chuàng)建連接

2、填寫連接參數(shù)

四、數(shù)據(jù)定義語句

1、創(chuàng)建表

create table 表名(字段名 字段類型,...);
?
-- 創(chuàng)建一個(gè)聊天室的用戶表
create table chat_user(user_name char(20),passwd char(8),email char(32)
);
?
-- 查看表結(jié)構(gòu)
desc 表名;
注意：如何把結(jié)構(gòu)轉(zhuǎn)換成數(shù)據(jù)表

2、修改表

-- 修改表名
rename table 舊表名 to 新表名;
rename table User to chat_user;
?
-- 增加列，如果表已有數(shù)據(jù)，新添加的字段具有非空的要求，則添加失敗
alter table 表名 add(字段名 字段類型);
?
-- 刪除列，如果表中已有數(shù)據(jù)，則該字段的數(shù)據(jù)會(huì)一起刪除
alter table 表名 drop 字段名;
?
-- 修改列，如果表中已有數(shù)據(jù)，新的字段如果能兼容之前的數(shù)據(jù)則修改成功，否則修改失敗
alter table 表名 modify 字段名 新類型;
alter table 表名 change 舊字段名 新字段名 新類型;
?
總結(jié)：1、把表結(jié)構(gòu)設(shè)計(jì)完善后，再添加數(shù)據(jù)。2、盡量不要修改舊表的結(jié)構(gòu)，而設(shè)計(jì)一張新的表，讓他們建立聯(lián)系。

3、刪除表

-- 刪除表數(shù)據(jù)，保留表結(jié)構(gòu)，清空表
truncate 表名;
truncate Student;
?
-- 刪除表，數(shù)據(jù)和結(jié)構(gòu)都刪除
drop table 表名;
drop table Student;

五、數(shù)據(jù)操作語言

1、插入數(shù)據(jù)

-- 按全字段順序插入
insert into 表名 values(數(shù)據(jù));
?
-- 指定字段插入
insert into 表名(字段名,...) values(數(shù)據(jù),...);
?
注意：在設(shè)計(jì)表時(shí)，某些字段為設(shè)置一些約束條件，如果插入的數(shù)據(jù)不滿足這些條件(非空、唯一)，則插入失敗。

2、刪除數(shù)據(jù)

delete from 表名 where 條件;
?
delete from chat_user;
delete from chat_user where nick="二師兄";
delete from chat_user where level < 2;
?
-- 注意，如果不寫where，則整張表全部刪除，所以為了安全MySQL數(shù)據(jù)庫(kù)默認(rèn)不支持該操作
?
?
-- 查看當(dāng)前連接是否開啟數(shù)據(jù)保護(hù)
show variables like 'sql_safe%';
-- 開啟安全保護(hù)，禁止使用非主鍵字段作為刪除條件
set sql_safe_updates=on; ?
-- 關(guān)閉數(shù)據(jù)安全保護(hù)，任何條件都可以作為刪除條件
set sql_safe_updates=off;   

3、修改數(shù)據(jù)

-- 注意：如果不寫where，則整張表的字段數(shù)據(jù)全被修改。
update 表名 set 字段=數(shù)據(jù),... where 條件;

六、數(shù)據(jù)查詢語句

select 字段1,字段2,... from 表名;
-- *在SQL中也是通配符，代表所有字段
?
select * from chat_user;
select nick,level from chat_user;

七、事務(wù)控制語句

1、commit 提交

一個(gè)數(shù)據(jù)庫(kù)會(huì)被若干個(gè)客戶端同時(shí)訪問，數(shù)據(jù)庫(kù)的底層為了保護(hù)數(shù)據(jù)的完整性，修改數(shù)據(jù)時(shí)會(huì)加鎖保護(hù)。

理論上每個(gè)客戶端修改一次數(shù)據(jù)，都要加一次鎖，但頻繁的加鎖會(huì)降低數(shù)據(jù)庫(kù)的運(yùn)行速度，所以數(shù)據(jù)設(shè)計(jì)一種確認(rèn)修改的動(dòng)作。

客戶端對(duì)數(shù)據(jù)庫(kù)進(jìn)行若干次修改了，數(shù)據(jù)庫(kù)不會(huì)立即修改硬盤上的數(shù)據(jù)，而是把修改過的數(shù)據(jù)暫存客戶端，直接客戶端執(zhí)行了確認(rèn)修改的命令，此時(shí)數(shù)據(jù)庫(kù)才會(huì)加鎖，然后把修改后的數(shù)據(jù)更新到硬盤上。

在數(shù)據(jù)庫(kù)，一個(gè)用戶插入一條數(shù)據(jù)時(shí)，只有它自己能查詢到，其它用戶并不能立即看到，只有執(zhí)行了commit語句后，其它用戶才能看到。

2、rollback

當(dāng)用戶對(duì)數(shù)據(jù)進(jìn)行修改后，如果發(fā)現(xiàn)操作錯(cuò)誤，可以使用rollback語句返回到上一次commit;

使用commit的優(yōu)點(diǎn)：

1、讓數(shù)據(jù)庫(kù)批量執(zhí)行寫操作，提高了數(shù)據(jù)庫(kù)的操作效率。

2、降低硬盤的讀寫次數(shù)，提高硬盤的壽命。

3、MySQL中的自動(dòng)提交

-- 查詢當(dāng)前登錄的自動(dòng)提交是否開啟
show variables like 'autocommit';
set session autocommit = 0|1; 關(guān)閉或開啟當(dāng)前登錄的自動(dòng)提交。
set global autocommit = 0|1; 關(guān)閉或開啟所有登錄的自動(dòng)提交，需要root用戶才能執(zhí)行。

4、設(shè)置保存點(diǎn)

使用rollback取消操作時(shí)，會(huì)取消所有的操作，直接回到上次commit的時(shí)刻，但這樣可以會(huì)浪費(fèi)一部分有意義的操作，可以在一個(gè)的階段設(shè)置在保存點(diǎn)，讓rollback返回到指定的位置。

insert into chat_user values("二師兄","123123",13388666688,1);
savepoint s1;
insert into chat_user values("二師兄","123123",13388666687,2);
savepoint s2;
insert into chat_user values("二師兄","123123",13388666686,3);
savepoint s3;
insert into chat_user values("二師兄","123123",13388666685,4);
savepoint s4;
insert into chat_user values("二師兄","123123",13388666684,5);
?
select * from chat_user;
rollback to savepoint s3;
select * from chat_user;

八、使用C連接并操作數(shù)據(jù)庫(kù)

所有MySQL數(shù)據(jù)庫(kù)的C語言接口就聲明在mysql/mysql.h頭文件中，但前提是安裝libmysqlclient-dev庫(kù)。

1、初始化MYSQL對(duì)象

MYSQL *mysql_init(MYSQL *mysql) 
功能：分配或初始化與mysql_real_connect()相適應(yīng)的MYSQL對(duì)象。
mysql：1、參數(shù)是NULL指針，該函數(shù)將分配、初始化、并返回新對(duì)象2、參數(shù)是MYSQL對(duì)象地址，將初始化對(duì)象，并返回對(duì)象的地址。
返回值：成功：初始化的MYSQL*句柄。如果無足夠內(nèi)存以分配新的對(duì)象，返回NULL。錯(cuò)誤：在內(nèi)存不足的情況下，返回NULL。
?
注意：如果mysql_init分配了新的對(duì)象，當(dāng)調(diào)用mysql_close來關(guān)閉連接時(shí)，將釋放該對(duì)象。 

2、連接數(shù)據(jù)庫(kù)

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag);
mysql：已有MYSQL結(jié)構(gòu)的地址調(diào)用mysql_real_connect()之前，必須調(diào)用mysql_init()來初始化MYSQL結(jié)構(gòu)。
host：主機(jī)名或IP地址。如果“host”是NULL或字符串"localhost"，連接將被視為與本地主機(jī)的連接。如果操作系統(tǒng)支持套接字（Unix）或命名管道（Windows），將使用它們而不是TCP/IP連接到服務(wù)器。
user：用戶名
passwd：用戶的密碼
db：是數(shù)據(jù)庫(kù)名稱
port：如果不是0，其值將用作TCP/IP連接的端口號(hào)，host參數(shù)決定了連接的類型。
unix_socket：該字符串描述了應(yīng)使用的套接字或命名管道，注意，“host”參數(shù)決定了連接的類型。
client_flag：值通常為0，但是，也能將其設(shè)置為下述標(biāo)志的組合。
?
返回值：如果連接成功，返回MYSQL*連接句柄,與第1個(gè)參數(shù)的值相同。如果連接失敗，返回NULL。

2、設(shè)置字符集、獲得當(dāng)前連接的字符集

int mysql_set_character_set(MYSQL *mysql, char *csname);
如果查詢成功，返回0。如果出現(xiàn)錯(cuò)誤，返回非0值。 
const char *mysql_character_set_name(MYSQL *mysql);

3、發(fā)送SQL指令

int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length) 
如果查詢成功，返回0。如果出現(xiàn)錯(cuò)誤，返回非0值。 

4、獲取查詢結(jié)果

MYSQL_RES *mysql_store_result(MYSQL *mysql)
功能：將查詢的全部結(jié)果讀取到客戶端，分配1個(gè)MYSQL_RES結(jié)構(gòu)，并將結(jié)果置于該結(jié)構(gòu)中。
返回值結(jié)果為NULL：語句執(zhí)行出現(xiàn)錯(cuò)誤，或執(zhí)行的是沒有結(jié)果的SQL語句，如INSERT語句。通過檢查mysql_error()是否返回非空字符串，mysql_errno()是否返回非0值，或mysql_field_count()是否返回0，可以檢查是否出現(xiàn)了錯(cuò)誤。
?
返回值結(jié)果不為NULL則需要調(diào)用以下函數(shù)： ? ?
my_ulonglong mysql_num_rows(MYSQL_RES *result) 
功能：返回結(jié)果集中的行數(shù)。unsigned int mysql_num_fields(MYSQL_RES *result)
功能：返回結(jié)果集中的列數(shù)MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
功能：從結(jié)果集中讀取一行數(shù)據(jù)void mysql_free_result(MYSQL_RES *result)
功能：釋放結(jié)果集

5、受影響或檢索的記錄行數(shù)

unsigned long long mysql_affected_rows(MYSQL *mysql);
功能：返回上次UPDATE更改的行數(shù)，上次DELETE刪除的行數(shù)，或上次INSERT語句插入的行數(shù)。對(duì)于UPDATE、DELETE或INSERT語句，可在mysql_query()后立刻調(diào)用。對(duì)于SELECT語句，mysql_affected_rows()的工作方式與mysql_num_rows()類似。
?
返回值：大于0的整數(shù)表明受影響或檢索的行數(shù)。0表示UPDATE語句未更新記錄，在查詢中沒有與WHERE匹配的行，或未執(zhí)行查詢。-1表示查詢返回錯(cuò)誤，或者，對(duì)于SELECT查詢，在調(diào)用mysql_store_result()之前調(diào)用了mysql_affected_rows。由于mysql_affected_rows返回?zé)o符號(hào)值，通過比較返回值和(my_ulonglong)-1或等效的(my_ulonglong)~0，檢查是否為“-1”。

6、commit提交

my_bool mysql_autocommit(MYSQL *mysql, my_bool mode) 
mode:為真，啟用autocommit模式，為假，禁止autocommit模式。
返回值：如果成功，返回0，如果出現(xiàn)錯(cuò)誤，返回非0值。
?
my_bool mysql_commit(MYSQL *mysql) 
功能：提交當(dāng)前事務(wù)。
返回值：如果成功，返回0，如果出現(xiàn)錯(cuò)誤，返回非0值。

7、錯(cuò)誤編號(hào)、原因

unsigned int mysql_errno(MYSQL *mysql)
功能：獲取錯(cuò)誤編號(hào)，如果沒有錯(cuò)誤則返回0const char *mysql_error(MYSQL *mysql)
功能：獲取錯(cuò)誤原因，如果沒有錯(cuò)誤則返回NULL

8、編譯時(shí)需要添加參數(shù)

# 用的庫(kù)是libmysqlclient-dev，所以編譯時(shí)需要添加參數(shù)
gcc xxx.c -lmysqlclient

任務(wù)1：使用以上API實(shí)現(xiàn)一個(gè)mysql命令。

任務(wù)2：使用數(shù)據(jù)庫(kù)實(shí)現(xiàn)通訊錄項(xiàng)目。

增、刪、改、查、列出，直接針對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作。

九、高級(jí)查詢

1、排重 distinct

-- 在數(shù)據(jù)表中，一個(gè)字段的值可能有多個(gè)重復(fù)的，distinct 可以排除重復(fù)數(shù)據(jù)，如果多個(gè)字段查詢，那么所有查詢的字段值都一樣，才算重復(fù)
select distinct 字段,... from 表名;
?
select distinct nick from chat_user;

2、算術(shù)運(yùn)算符

-- select 語句查詢時(shí)，可以對(duì)表中的數(shù)值字段直接進(jìn)行算術(shù)運(yùn)算，如果想改變運(yùn)算的優(yōu)先級(jí)可以使用小括號(hào)。
select 字段 + - * / % from 表名;
?
select level/3 from chat_user;

3、where子句

-- 在where字句中可以使用關(guān)系運(yùn)算符和邏輯運(yùn)算符，只有條件為真的數(shù)據(jù)才會(huì)顯示
select 字段 from 表名 where 條件;
?
關(guān)系運(yùn)算符：> < >= <= = !=
邏輯運(yùn)算符：and or not&& || ! 可以繼續(xù)使用特殊條件：between a and b <=> 判斷一個(gè)范圍，使用判斷運(yùn)算符加邏輯運(yùn)算符也能達(dá)到同樣的效果[a,b]。is null、is not null 判斷字段值是不是為空，在數(shù)據(jù)表中空值是一種狀態(tài)，而不是一個(gè)特定的值。select * from chat_user where phone is null;in (a,b,c,...) 當(dāng)字段的值出現(xiàn)在in的范圍列表中，則為真。select * from chat_user where level in (1,3,5,7,9);like "%str_" 模糊查詢（Linux系統(tǒng)命令行中使用的通配符），字符型字段使用才合適select * from chat_user where phone like "183%";% 匹配任意多個(gè)字符_ 匹配一個(gè)字符

注意：判斷字段值是不為空，在數(shù)據(jù)表中空值是一種狀態(tài)，而不是一個(gè)特定的值。

4、排序

select 字段 from 表名 order by 字段 [asc|desc];asc 升序，從小到大，默認(rèn)desc 降低，從大到小    select * from chat_user order by phone;
-- 注意：MySQL數(shù)據(jù)庫(kù)在排序時(shí)把空值當(dāng)作最小值，Oracle數(shù)據(jù)庫(kù)把空值當(dāng)作了最大值。
?
-- 排序與where子句配合：
select 字段 from 表名 where 條件 order by 字段 [asc|desc];
select * from chat_user ?where phone is not null order by phone desc;

5、分頁查詢

使用SELECT查詢時(shí)，如果結(jié)果集數(shù)據(jù)量很大，比如幾萬行數(shù)據(jù)，放在一個(gè)頁面顯示的話數(shù)據(jù)量太大，不如分頁顯示，每次顯示100條。

要實(shí)現(xiàn)分頁功能，實(shí)際上就是從結(jié)果集中顯示第1~100條記錄作為第1頁，顯示第101~200條記錄作為第2頁，以此類推。因此，分頁實(shí)際上就是從結(jié)果集中“截取”出第M~N條記錄。

select 字段 from 表名 limit <一頁的記錄數(shù)> offset <要跳過的記錄數(shù)>;
select * from chat_user limit 10 offset 0;
?
假設(shè)一頁m條數(shù)據(jù)
select * from chat_user limit m offset (頁數(shù)-1)*m;

6、連接查詢(多表查詢)

當(dāng)需要的數(shù)據(jù)分布在不同的表中，就需要多張表連接查詢，無條件的連接會(huì)產(chǎn)生"笛卡樂積"有海量的無效數(shù)據(jù)，需要配合where子句進(jìn)行連接。

-- 班級(jí)表
create table class(class_id int,class_name char(20),teacher_id int,room_id int
);
?
-- 教師表
create table teacher(work_id int,name char(20),sex char(1),phone char(11)
);
?
-- 學(xué)生表
create table student(id int,name char(20), sex char(1),class_id int
);
?
?

select 字段 from 表1,表2 where 表1.key = 表2.key;
?
-- 表中的字段可能會(huì)重名，需要 表名.字段進(jìn)行區(qū)分;
select 表名.字段 from 表1,表2 where 表1.key = 表2.key;

根據(jù)where條件和連接結(jié)果，連接查詢分為以下幾種：

-- 內(nèi)連接
查詢出每班級(jí)的教師叫什么名字？
select class_name,name from class,teacher where work_id = teacher_id;
?
-- 左內(nèi)連接
查詢出每班級(jí)的教師叫什么名字,沒有分配教師的班級(jí)也顯示？
select class_name,name from class left join teacher on work_id = teacher_id;
?
-- 右內(nèi)連接
查詢出每班級(jí)的教師叫什么名字,沒有教學(xué)任務(wù)的教師也顯示？
select class_name,name from class right join teacher on work_id = teacher_id;
?
-- 左外連接
查詢出沒有分配教師的班級(jí)？
select class_name,name from class left join teacher on work_id = teacher_id where teacher_id is null;
?
?
-- 右外連接
查詢出沒有分配教學(xué)任務(wù)的教師？
select class_name,name from class right join teacher on work_id = teacher_id where class_name is null;
?
-- 全連接
select class_name,name from class full outer join teacher on work_id = teacher_id;
select class_name,name from class left join teacher on work_id = teacher_id union
select class_name,name from class right join teacher on work_id = teacher_id where class_name is null;
?
-- 外連接
select class_name,name from class left join teacher on work_id = teacher_id where teacher_id is null union 
select class_name,name from class right join teacher on work_id = teacher_id where class_name is null;
?
?
全連接、外連接MySQL不支持該語法，可以使用union關(guān)鍵字，把左外連接、右外連接聯(lián)合在一起實(shí)現(xiàn)外連接、全連接。左外連接 union 右外連接 = 外連接;左連接 union 右連接 = 全連接;

7、取別名

在多表查詢時(shí)，由于表名，在訪問重名字段時(shí)語句過長(zhǎng)，可以給表名取一個(gè)簡(jiǎn)單的別名，也可以解決一張表自連接的查詢。

select 別名.字段 from 表名 as 別名;
?
問題：查詢出每個(gè)學(xué)生的上課時(shí)的教室。
select name,room_id from class as c,student as s where c.class_id = s.class_id;
select name,room_id from class as c,student as s where class.class_id = student.class_id;

8、子查詢

把一個(gè)查詢結(jié)果作為另一個(gè)查詢語句的基礎(chǔ)，這種查詢叫子查詢或嵌套查詢。

-- 在SQL中可以把select的查詢結(jié)果當(dāng)作一張內(nèi)存表。
?
查詢出沒有分配教學(xué)任務(wù)的教師，并顯示它的ID？
select work_id from class right join teacher on teacher_id = work_id where teacher_id is null;
查詢出未分配教學(xué)任務(wù)的教師姓名、性別、手機(jī)號(hào)？
select name,sex,phone from teacher where work_id in (select work_id from class right join teacher on teacher_id = work_id where teacher_id is null);

9、MySQL中的函數(shù)

-- 普通函數(shù)一條記錄就會(huì)產(chǎn)生一個(gè)結(jié)果，而組函數(shù)一次查詢只產(chǎn)生一個(gè)結(jié)果，這兩種函數(shù)不能混用
count 計(jì)數(shù)
max 求最大
min 求最小
sum 求和
avg 求平均

MySQL 函數(shù) | 菜鳥教程

注意：MySQL中有豐富的數(shù)據(jù)處理函數(shù)，但程序員也可以先把查詢到的結(jié)果轉(zhuǎn)換成相關(guān)的數(shù)據(jù)類型，再使用編程語言中的數(shù)據(jù)處理函數(shù)。

10、分組查詢

把表中的數(shù)據(jù)按照標(biāo)準(zhǔn)分為不同的組。

select 分組標(biāo)準(zhǔn)或組函數(shù)處理過的數(shù)據(jù) from 表 group by 字段;
?
查詢出每個(gè)班級(jí)的學(xué)生數(shù)量？
select class_id,count(id),max(id),min(id) from student group by class_id;
?
查詢出每個(gè)班級(jí)的班級(jí)名，授課教師名，學(xué)生數(shù)量？
select class_name,teacher.name,count(id) from 
class,teacher,student where work_id=teacher_id && student.class_id=class.class_id 
group by class_name,teacher.name;
?
查詢出每個(gè)班級(jí)的班級(jí)ID、班級(jí)名，授課教師名，學(xué)生數(shù)量？
select class.class_id,min(class_name),min(teacher.name),count(student.id) from class,teacher,student where work_id=teacher_id && student.class_id=class.class_id group by class.class_id;
?
select class_name,teacher.name,count(id) from 
class 
left join teacher 
on work_id = teacher_id 
left join student on student.class_id=class.class_id 
group by class_name,teacher.name;
?

過慮分組后的數(shù)據(jù):

select 分組標(biāo)準(zhǔn)或組函數(shù)處理過的數(shù)據(jù) from 表 group by 字段 having 條件;
注意：having的條件字段必須分組標(biāo)準(zhǔn)或組函數(shù)處理過的字段。
?
查詢出每個(gè)1004班級(jí)的班級(jí)名，授課教師名，學(xué)生數(shù)量？
select class.class_id,class_name,teacher.name,count(id) from class left join teacher on work_id = teacher_id left join student on student.class_id=class.class_id group by class_name,teacher.name,class.class_id having class.class_id = 1004;

復(fù)雜語句的執(zhí)行順序：

select 組函數(shù)(字段)|或分組標(biāo)準(zhǔn)from 表名where 條件 | 連接標(biāo)準(zhǔn)group by 分組標(biāo)準(zhǔn)having 過慮條件order by  排序標(biāo)準(zhǔn);     查詢出班級(jí)人數(shù)在3人以上的班級(jí)，顯示班級(jí)名，班級(jí)ID，班級(jí)人數(shù)，并且對(duì)按班級(jí)人數(shù)進(jìn)行排序。
select student.class_id,max(class_name),count(name) from student,class where student.class_id=class.class_idgroup by student.class_idhaving count(name) > 3order by count(name);
?

注意：在MySQL客戶端里，使用Ctrl+B 可以格式化SQL語句。

十、設(shè)計(jì)表

1、數(shù)據(jù)庫(kù)設(shè)計(jì)的三 大范式

在進(jìn)行數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，制定的一些規(guī)則，稱為數(shù)據(jù)庫(kù)設(shè)計(jì)范式 ，遵守這些規(guī)則將創(chuàng)建出良好的數(shù)據(jù)庫(kù)，如經(jīng)常聽到的數(shù)據(jù)庫(kù)三大范式。

第一范式（Normal From,1NF）：確保每列的原子性

如果每列都是不可再分的最小數(shù)據(jù)單元，則滿足第一范式。

例如：顧客表(姓名、編號(hào)、地址、……)，其中"地址"列還可以細(xì)分為國(guó)家、省、市、區(qū)等，所以按照第一次范圍，地址更改為地區(qū)編號(hào)(例如身份證號(hào)的前6位)。

第二范式（Normal From,2NF）：在第一范式的基礎(chǔ)上更進(jìn)一層，目標(biāo)是確保表中的每列都和主鍵相關(guān)

如果一個(gè)關(guān)系滿足第一范式，并且除了主鍵以外的其它列，都依賴于該主鍵，則滿足第二范式.

例如：訂單表(訂單編號(hào)、產(chǎn)品編號(hào)、產(chǎn)品數(shù)量、定購(gòu)日期、產(chǎn)品價(jià)格、……)，"訂單編號(hào)"為主鍵，"產(chǎn)品價(jià)格"和主鍵列沒有直接的關(guān)系，即"產(chǎn)品價(jià)格"列不依賴于主鍵列，應(yīng)刪除，然后連接產(chǎn)品表，根據(jù)產(chǎn)品編號(hào)獲得該項(xiàng)數(shù)據(jù)。

第三范式（Normal From,3NF）：在第二范式的基礎(chǔ)上更進(jìn)一層，目標(biāo)是確保每列都和主鍵列直接相關(guān)，而不是間接相關(guān)

如果一個(gè)關(guān)系滿足第二范式，并且除了主鍵以外的其他列都只能依賴主鍵列，列與列之間不存在相互依賴關(guān)系，則滿足第三范式。

例如：訂單表(訂單編號(hào)，定購(gòu)日期，顧客編號(hào)，顧客姓名，……)，初看該表沒有問題，滿足第二范式，每列都和主鍵列"訂單編號(hào)"相關(guān)，再細(xì)看你會(huì)發(fā)現(xiàn)"顧客姓名"和"顧客編號(hào)"相關(guān)，為了滿足第三范式，應(yīng)去掉"顧客姓名"列，放入客戶表中。

總結(jié)：字段不可再分，字段跟主鍵都有關(guān)系，字段與主鍵有強(qiáng)直接關(guān)系，遵循這三范式能讓數(shù)據(jù)庫(kù)中的表更靈活、強(qiáng)大、節(jié)約存儲(chǔ)空間，但并不能保證查詢速度最快，所以在實(shí)際開發(fā)過程中會(huì)突破三范式，犧牲存儲(chǔ)空間以達(dá)到速度最優(yōu)。

2、約束

約束是對(duì)數(shù)據(jù)和表的限制，可以提高表中數(shù)據(jù)的準(zhǔn)確性和可靠性，一般在創(chuàng)建表、修改表時(shí)使用（在已有數(shù)據(jù)的情況下，修改表的約束不一定能成功）。

約束的分類：

not null ? 非空，字段的數(shù)據(jù)不能為空
unique ?  唯一，字段的數(shù)據(jù)不能重復(fù)
primary key 主鍵(非空且唯一)
foreign key 外鍵依賴，B表某個(gè)字段值必須在A表中的某個(gè)字段出現(xiàn)過
default ? 默認(rèn)值，給字段設(shè)置完默認(rèn)值后，當(dāng)插入數(shù)據(jù)不提供該字段的數(shù)據(jù)值，數(shù)據(jù)庫(kù)自動(dòng)填充默認(rèn)值
check ?  檢查，設(shè)置一個(gè)條件判斷，當(dāng)數(shù)據(jù)不滿足條件時(shí)插入失敗，但MySQL數(shù)據(jù)庫(kù)不支持
?
?
drop table teacher;
create table teacher(work_id int primary key,name char(20) not null,sex char NULL default 'w',enter_time timestamp NULL default CURRENT_TIMESTAMP
);
?
drop table class;
create table class(class_id int primary key,class_name char(20) not null,enter_time timestamp NULL default CURRENT_TIMESTAMPteacher_id int,room_id int unique not null
);
?
-- 一般在創(chuàng)建表的時(shí)候設(shè)置外鍵，先建父表，再建子表：
create table 父表(字段名 類型 primary key, 
);
?
create table 子表(...foreign key(字段) REFERENCES 父表(字段)
);
?
drop table student;

約束的設(shè)置方式：

-- 創(chuàng)建表時(shí)設(shè)置：
create table 表名(字段名 類型 約束,
);
?
-- 修改表時(shí)設(shè)置：
alter table 表名 modify 字段 類型 約束;
?

外鍵約束：

一張表（子表）的值引用自另一張表（父表），被引用的字段必須具備唯一性，子表中的外鍵字段的值必須來自父表或者是null值。

-- 一般在創(chuàng)建表的時(shí)候設(shè)置外鍵，先建父表，再建子表：
create table 父表(字段名 類型 primary key, 
);
?
create table 子表(...foreign key(字段) REFERENCES 父表(字段)
);
?
-- 也可以后期添加（一般不建議使用）：
alter table 子表 add foreign key(子表字段) REFERENCES 父表(父表字段);
?
-- 班級(jí)表 父表
drop table class;
create table class(class_id int primary key,name char(20) unique not null);-- 學(xué)生表 子表
drop table student;
create table student(student_id int primary key,name char(20) unique not null,class_id int, ? ?foreign key(class_id) REFERENCES class(class_id))

插入數(shù)據(jù)：

先插入父表數(shù)據(jù)，再插入子表數(shù)據(jù)。

更新或刪除數(shù)據(jù)：

默認(rèn)情況下：

刪除父表數(shù)據(jù)時(shí)：先刪除子表數(shù)據(jù)，再刪除父表數(shù)據(jù)。

更新父表數(shù)據(jù)時(shí)：父表插入新數(shù)據(jù)，修改子表數(shù)據(jù)，再刪除父表中數(shù)據(jù)。

設(shè)置級(jí)聯(lián)刪除，級(jí)聯(lián)更新：

on delete cascade 級(jí)聯(lián)刪除，刪除父表時(shí)，子表中的數(shù)據(jù)會(huì)一起刪除

on update cascade 級(jí)聯(lián)更新，更新父表時(shí)，子表中的數(shù)據(jù)會(huì)一起刪除

注意：級(jí)聯(lián)刪除、更新必須在創(chuàng)建子表時(shí)設(shè)置才有效。

create table student(student_id int primary key,name char(20) unique not null,class_id int, ? ?foreign key(class_id) REFERENCES class(class_id) on delete cascade on update cascade 
);

3、自動(dòng)增長(zhǎng)的字段

這種字段可以不用手動(dòng)插入值，由系統(tǒng)提供默認(rèn)的序列值，但必須滿足以下要求：

1、只有主鍵才能設(shè)置

2、必須是數(shù)值型字段

3、一張表最多只能設(shè)置一個(gè)

設(shè)置方法：

--  創(chuàng)建表時(shí)設(shè)置
create table 表名(字段 類型 primary key auto_increment,
);
?
-- 修改表時(shí)設(shè)置
alter table 表名 modify 字段 類型 primary key auto_increment;
alter table student modify id int primary key auto_increment;

初始值和步長(zhǎng)：

alter table 表名 auto_increment = 初始值;
set auto_increment_increment = value 設(shè)置步長(zhǎng);
-- 也可以通過第一次手動(dòng)插入數(shù)據(jù)設(shè)置初始值

4、索引

索引是一種提高查詢速度的技術(shù)，如果把數(shù)據(jù)庫(kù)看作字典，那么索引就是字典的目錄。

創(chuàng)建索引：

create table 表名(...index [索引名] (字段,)
);
-- 注意：創(chuàng)建的索引字段是經(jīng)常在SQL語句的where字句條件上

添加索引：

alter table 表名 add index [索引名] (字段名);

查看索引：

show index from 表名;
-- 注意：MySQL數(shù)據(jù)庫(kù)會(huì)自動(dòng)對(duì)表進(jìn)行優(yōu)化，主鍵、非空且唯一的字段會(huì)自動(dòng)優(yōu)化成索引。

刪除索引：

drop index 索引名 on 表名; 

索引的優(yōu)點(diǎn)和缺點(diǎn)：

1、能大大提高數(shù)據(jù)庫(kù)的查詢速度。

2、但索引的本質(zhì)其實(shí)也是硬盤地址的表，里面存儲(chǔ)著字段數(shù)據(jù)所在的硬盤位置，創(chuàng)建索引需要額外的存儲(chǔ)空間，是典型用空間換取時(shí)間。

3、而且使用索引雖然提高了查詢速度，但會(huì)降低插入、更新、刪除數(shù)據(jù)的速度。

4、MySQL數(shù)據(jù)庫(kù)會(huì)自動(dòng)為主鍵創(chuàng)建索引，所有在MySQL數(shù)據(jù)庫(kù)中不建議主動(dòng)創(chuàng)建索引。

5、視圖

視圖是一張?zhí)摂M的表，它本身并不包含數(shù)據(jù)，而是作為一個(gè)select語句保存在數(shù)據(jù)庫(kù)中。

如果設(shè)計(jì)表時(shí)遵循了三大范式，我們的數(shù)據(jù)庫(kù)中會(huì)有很多張表(零散)，查詢數(shù)據(jù)時(shí)會(huì)有很多連接查詢，SQL語句就需要寫的非常長(zhǎng)，非常麻煩。

視圖就是把常用的連接查詢語句存儲(chǔ)到數(shù)據(jù)庫(kù)中。

創(chuàng)建視圖：

create view 視圖名 [字段名] as <select語句>;
?
create view class_view as select * from class,student where class.class_id = student.class_id;

查看視圖：

desc 視圖名;

刪除視圖：

drop view 視圖名;

使用視圖的優(yōu)、缺點(diǎn)：

1、可以只展現(xiàn)基本表的部分?jǐn)?shù)據(jù)，不用關(guān)心基本表的結(jié)構(gòu);

2、使用視圖的用戶只能訪問被允許訪問的數(shù)據(jù)，對(duì)數(shù)據(jù)庫(kù)權(quán)限的管理只能精細(xì)到某張表，但使用視圖可以管理某些列的某些行，可以大大提高數(shù)據(jù)的安全性。

3、視圖創(chuàng)建完成后，可以刪除、添加列，而視圖不受影響。

4、速度慢，無法修改視圖中的數(shù)據(jù)，只能讀。

十、擴(kuò)展

1、導(dǎo)出數(shù)據(jù)(備份)

mysqldump -uroot -p123456 -hlocalhost testDB > testDB.sql

2、導(dǎo)入數(shù)據(jù)(恢復(fù))

-- 登錄數(shù)據(jù)庫(kù)
mysql -utest -p
mysql> use testDB;
-- 把備份的數(shù)據(jù)導(dǎo)入到當(dāng)前數(shù)據(jù)庫(kù)
mysql> source /home/sunll/testDB.sql

3、SQL 注入

4、存儲(chǔ)引擎