瞭解MySQL數據庫

1.MySQL概述

數據庫：儲存數據的倉庫

數據庫相關概念:
	庫：在一個MySQL伺服器中可以建立多個數據庫
	表：在一個數據庫中可以建立多張表
	表記錄：一張表中可以有多條表記錄

數據庫的分類:層次式數據庫，網路式數據庫，關係型數據庫

關係型數據庫：利用表來儲存數據，用表和表之間的關係儲存數據之間的關係

常見的關係型數據庫：
Sqlserver
MySQL
Oracle

MySQL介紹:MySQL由瑞典MySQL AB公司開發的產品，目前屬於Oracle

MySQL安裝

登錄MySQL：mysql -uroot -proot -h ip
-u：後面是使用者名稱
-p：後面是密碼
-h：後面是ip，如果是本機，可以省略
退出：quit或者exit

常見報錯：
安裝時，如果未響應，解除安裝重灌
登錄時，出現：

出現上圖時:重新設定MySQL的環境變數，設定完成後重新開啓cmd視窗登錄
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

出現上圖時,原因是MySQL的服務沒有開啓
解決：此電腦右鍵->管理->服務和應用程式->服務->MySQL右鍵啓動
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

出現此報錯原因：密碼錯誤
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

2.SQL語言

概述
	SQL指結構化查詢語言，是操作關係型數據庫的通用語言
	SQL是一個標準，各大數據庫廠商都要根據該標準提供實現，實現過程中，數據庫廠商爲了增強數據庫的功能，會新增一些非標準的SQL，稱之爲「方言」

SQL能幹什麼
對庫進行CRUD
對錶進行CRUD
對錶記錄進行CRUD
CRUD：create、read、update、delete

3.庫的CRUD

建立數據庫
		語法：create database [if not exists] db_name [character set utf8];

檢視數據庫
	顯示所有的數據庫:show databases;
	顯示數據庫的建立語句:show create database db_name;

修改數據庫   ==數據庫的名稱無法修改==
		修改字元集:alter database db_name character set utf8;
		刪除數據庫:drop database db_name;

選擇數據庫
		選擇數據庫：use da_name;
		查詢當前使用的數據庫：select database();

4.表的CRUD

4.1.數據型別

字串
char(n)：定長字串，最大長度不超n，n最大值可取255，效率高
通常用來儲存固定長度的字串，例如：身份證號、手機號等

varchar(n)：不定長字串，最大長度不超n，n最大值可取65535，節省空間
通常用來儲存不定長的字串，例如：姓名、暱稱等

數值型別
tinyint：byte
smallint：short
int：int
bigint：long
float：float
double：double

大數據型別
blob：
大二進制型別，如圖片、音訊、視訊等，最大4GB
text：
大文字型別，最大4GB
注意：text爲MySQL的方言，在其他數據庫中爲clob

日期型別
date：日期 2008-8-8
time：時間 20:0:0
datetime：日期時間 2008-8-8 20:0:0
timestamp：時間戳 2008-8-8 20:0:0 存的是1970年1月1日到當前時間的毫秒值

邏輯值
bit：只能取0和1

4.2.欄位約束

主鍵約束：被約束的欄位非空且唯一
field_name data_type primary key
自動增長：field_name data_type primary key auto_increment
非空：not null
唯一：unique

4.3.表的建立

語法：
create table table_name(
field_name1 data_type,
field_name2 data_type,
…
);

4.4.檢視錶

檢視所有的表:show tables;
檢視錶結構:desc table_name;
檢視錶的建立語句:show create table table_name;

4.5.修改表

範例演示:
在employee表上新增一個image列:alter table employee add image blob;

修改job列的數據型別，長度變成50:alter table employee modify job varchar(50);

刪除gender列:alter table employee drop gender;

修改表名爲user:alter table employee rename to user;
                           rename table user to employee;

修改列名name爲username:alter table employee change name username varchar(66);

將image插入到id的後面:alter table employee modify image blob after id;

修改表的字元集:alter table employee character set gbk;

4.6.刪除表

drop table table_name;

5.表記錄的CRUD

5.1.insert

語法：
insert into table_name(field_name1,field_name2,…) values(value1,value2,…);

注意：
插入數據的數據型別一定要和欄位的數據型別一致
數據的長度要在規定範圍內
前面指定欄位名的順序需要和後面value值的順序一致
字串型別和日期型別需要用單引號引起來
如果插入的數據包含了所有的欄位，前面可以不指定欄位名

中文亂碼問題:
原因：cmd視窗使用的編碼爲gbk，而MySQL伺服器使用的編碼爲utf-8，兩端編碼不一致
解決亂碼：
方式一：set names gbk; 只在當前視窗有效，通知伺服器用哪種編碼讀取數據
方式二：修改MySQL的組態檔，修改[mysql]/my.ini的第57行，將utf8改爲gbk，永久生效

5.2.update

語法：
update table_name set field_name=value where…

5.3.delete

語法：
delete from table_name where…

5.4.select

語法：
select [distinct] *|field_name1,field_name2,… from table_name;
distinct：表示去重

5.5.模糊查詢：like

特殊符號：
%：匹配0個或者多個字元
_：匹配1個字元

where salary like ‘200%’ ----->以200開頭的數位
where salary like ‘%200%’---->數位中包含200即可
where salary like ‘00%’----> 數位的第二位和第三位爲0
where salary like '2%_%’----> 數位以2開頭，並至少3位數
where salary like ‘%2’---->以2結尾的數位
where salary like ‘_2%3’----> 第二位爲2，以3結尾的數位
where salary like ‘2___3’---->以2開頭，以3結尾的五位數

排序查詢

語法：select * from table_name order by field_name asc|desc;

注意：order by通常放在sql的末尾--------asc：（預設）升序 /desc：降序

聚合函數

語法
求記錄數:select count(*) from table_name where…;

求指定列的和:select sum(field_name) from table_name where…;

求指定列的平均值:select avg(field_name) from table_name where…;

求指定列的最大值:select max(field_name) from table_name where…;

求指定列的最小值:select min(field_name) from table_name where…;

分組查詢

語法：select * from table_name group by field_name having …;

having：分組後過濾
where：分組前過濾
在分組的列上我們可以使用 COUNT，SUM，AVG，MAX，MIN等聚合(多行)函數。
多行函數和是否分組有關，如果查詢結果中的數據沒有經過分組，預設整個查詢結果是一個組,產生的結果只有一個。
如果查詢結果中的數據經過分組（分的組不止一個），多行函數會根據分的組進行統計，有多少個組，就會統計出多少個結果。
where和having子句的區別:
(1)相同點: where和having都可以對記錄進行篩選過濾。
(2)區別:where是在分組之前,對記錄進行篩選過濾,並且where子句中不能使用多行函數以及列別名(但是可以使用表別名)
(3)區別:
having是在分組之後,對記錄進行篩選過濾,並且having子句中可以使用多行函數以及列別名、表別名。

分頁查詢

在mysql中，通過limit進行分頁查詢：
*limit (頁碼-1)每頁顯示記錄數，每頁顯示記錄數

//查詢emp表中的所有記錄，分頁顯示：每頁顯示3條記錄，返回第 1 頁:
select * from emp limit  0,3;

SQL語句的書寫順序:

select * | 列名 -- 確定要查詢的列有哪些
from 表名 -- 確定查詢哪張表
where 條件 -- 通過篩選過濾，剔除不符合條件的記錄
group by 分組的列 -- 指定根據哪一列進行分組
having 條件 -- 通過條件對分組後的數據進行篩選過濾
order by 排序的列 -- 指定根據哪一列進行排序
limit (countPage-1)*rowCount, rowCount -- 指定返回第幾頁記錄以及每頁顯示多少條

SQL語句的執行順序:

from 表名 -- 確定查詢哪張表
where 條件 -- 通過篩選過濾，剔除不符合條件的記錄
select * | 列名 列別名 -- 確定要查詢的列有哪些，
group by 分組的列 -- 指定根據哪一列進行分組
having 條件 -- 通過條件對分組後的數據進行篩選過濾
order by 排序的列 -- 指定根據哪一列進行排序
limit (countPage-1)*rowCount, rowCount

外來鍵

外來鍵：唯一標識其他表中的一條記錄，用來維護兩張表之間的關係
外來鍵約束：確保數據庫數據的完整性和一致性
新增外來鍵約束：foreign key(c_id) references clas(id)

案例：
create table clas(
id int primary key auto_increment,
name varchar(20)
);

insert into clas values(null,'big2004');
insert into clas values(null,'big2005');
insert into clas values(null,'big2006');
insert into clas values(null,'big2007');

create table stu(
id int primary key auto_increment,
name varchar(20),
c_id int,
foreign key (c_id) references clas(id)
);

insert into stu values(null,'a',1);
insert into stu values(null,'b',2);
insert into stu values(null,'c',3);
insert into stu values(null,'d',4);
insert into stu values(null,'e',4);

外來鍵約束會導致執行效率降低，而且操作數據不靈活，所以一般開發時很少使用，多表的關係可以使用程式碼維護

多表設計

（1）一對多：在多的一方儲存一的一方的外來鍵
（2）一對一：任意一方儲存另一方的外來鍵
（3）多對多：在第三張表中分別儲存兩張表的外來鍵
多表查詢
案例：
create table dept(
id int primary key auto_increment,
name varchar(20)
);

insert into dept values(null,'財務部');
insert into dept values(null,'人事部');
insert into dept values(null,'銷售部');
insert into dept values(null,'產品部');

create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int
);

insert into emp values(null,'張三',1);
insert into emp values(null,'李四',2);
insert into emp values(null,'王五',3);
insert into emp values(null,'趙六',5);

笛卡爾積：
兩張表相乘得出的結果，如果左邊有m條數據，右邊有n條數據，查詢出來的結果時m×n條數據。這種查詢結果中包含了大量的錯誤數據，一般不會直接使用笛卡爾積查詢
內連線查詢：inner join … on …(查詢出左邊有，右邊也有的數據)
左外連線查詢：left join … on …(在內連線的基礎上，查詢出左邊有，右邊沒有的數據)
右外連線查詢：right join … on …(在內連線的基礎上，查詢出右邊有，左邊沒有的數據)
全外連線查詢：full join … on …(相當於查詢出所有的數據)
注意：MySQL不支援全外
可以通過union代替

練習：
查詢出部門資訊和對應的員工資訊
select * from emp,dept;  //笛卡爾積
select * from emp,dept where emp.dept_id=dept.id;
select * from emp inner join dept on emp.dept_id=dept.id;

查詢出部門資訊和對應的員工資訊，並列出沒有部門的員工
select * from emp left join dept on emp.dept_id=dept.id;

查詢出部門資訊和對應的員工資訊，並列出沒有員工的部門
//可以對錶起別名
select * from emp e right join dept d on e.dept_id=d.id;

查詢出部門資訊和對應的員工資訊，並列出沒有員工的部門和沒有部門的員工
select * from emp e full join dept d on e.dept_id=d.id;//不支援
select * from emp left join dept on emp.dept_id=dept.id
union 
select * from emp e right join dept d on e.dept_id=d.id;