mysql資料庫總結。

mysql

MySQL語法
MySQL採用結構化查詢語言SQL (Structured Query Language)語言來操作資料庫
SQL語句必須以 ; 結束
SQL語句分類
DDL(數據定義語言): create、drop、alter、truncate
DQL(數據查詢語言): select、show
DML(數據操作語言): insert、update、delete
DCL (數據控制語言): grant、revoke、begin transaction、commit、roolback

MySQL存儲引擎
MySQL數據表類型由存儲引擎來決定,主要有6種類型：InnoDB 、BDB、MyISAM、ISAM、、Heap、Merge
InnoDB
支持事務
支持外鍵
不支持數據壓縮
寫速度較慢
MySQL5.5開始支持全文索引
占用更多的記憶體和磁碟空間來保留數據和索引

MyISAM
不支持事務
不支持外鍵
訪問速度快
支持全文索引

資料庫的備份還原
#方法一
mysqldump -h伺服器 -P埠 -u用戶名 -p密碼 資料庫名稱 > 備份文件路徑
source 備份文件路徑

#方法二
直接備份資料庫文件（註意：這種方法不適用於InnoDB存儲引擎的表，而對於MyISAM存儲引擎的表很方便。同時，還原時MySQL的版本最好相同）

#方法三
使用第三方工具 例如：Navicat for MySQL

命令行鏈接資料庫
mysql -u root -p

資料庫操作
#添加資料庫
create database [if not exists] 庫名 [[default] character set 編碼方式]

#查看資料庫
show databases 查看MySQL下有哪些資料庫
show create database 庫名 查看某個資料庫的詳細信息

#修改資料庫
alter database 庫名 [default] character set 編碼方式

#刪除資料庫
drop database [if exists] 庫名

數據表操作
#打開數據表所在的資料庫
use 庫名

#添加數據表
create table [if not exists] 表名(
欄位名 數據類型 [約束],
欄位名 數據類型 [約束],
......
欄位名 數據類型 [約束]
)[ENGINE=MyISAM DEFAULT CHARSET=utf8 collate utf8_general_ci] ;

#查看數據表
show tables 查看當前數據下有哪此數據表
show create talbe 表名 查看建表信息
desc 表名 查看表結構

#修改數據表
#增加欄位
alter table 表名 add [colum] 欄位名 數據類型 [約束]

#修改欄位名
alter table 表名 change 欄位名 新欄位名 數據類型 [約束]

#刪除欄位
alter table 表名 drop [colum] 欄位名

#更改表名
alter table 表名 rename [to] 新表名

#刪除數據表
drop table 表名

#清空數據表
truncate table 表名

資料庫的增刪改查
#插入記錄
#插入一條新的記錄
INSERT INTO 表名 [(欄位名1,欄位名2,…欄位名n)] VALUES(‘值1’,‘值2’,…,’值n’);
#插入多條記錄
INSERT INTO 表名 [(欄位名1,欄位名2,…欄位名n)]
VALUES(‘值1’,‘值2’，…，‘值n’),
(‘值1’,‘值2’，…，‘值n’),
......
(‘值1’,‘值2’,…,’值n’);

#刪除記錄
#根據條件刪除記錄
delete from 表名 [where 條件子句] [order by 欄位 asc|desc ] [limit 條數]
#在WHERE子句中，條件可以由一個或多個邏輯表達式組成，
#在WHERE子句中，可以通過邏輯操作符和比較操作符指定基本的表達式條件
#刪除全部記錄
truncate table 表名

#更新記錄
update 表名 set 欄位名=新值,欄位名=新值，....... [where 條件子句] [order by 欄位 asc|desc ] [limit 條數]

#查詢記錄
select 欄位1,欄位2,...... from 表名 [where 條件子句] [group by 欄位][haveing 條件子句][order by 欄位] [limit [起始值] 條數]

Alias 別名
#通過使用 SQL，可以為欄位名稱和表名稱指定別名
SELECT 欄位名 AS 別名 FROM 表名
SELECT * FROM 表名 AS 表別名

distinct 去重覆
#關鍵詞 DISTINCT 用於返回唯一不同的值
SELECT DISTINCT 欄位名稱 FROM 表名稱

聚合函數
avg() 平均值
count() 總記錄數，不包括null
max() 最大值
min() 最小值
sum() 求和

時期函數
now()
curdate()
curtime()
unix_timestamp()
from_unixtime()

數學函數
ABS()絕對值
ceil() 向上取整
floor 向下取整
round() 四捨五入
rand() 隨機數
mod() 取餘

字元串函數
concat() 連接字元串
replace() 替換
ucase() 大寫
lcase() 小寫
ltrim() 去左側空格
rtrim() 去右側空格
trim() 去兩邊空格

加密函數
password()
md5()

信息函數
database() 當前資料庫
user() 當前用戶
version 當前mysql版本
last_insert_id() 最後一條插入記錄的主鍵id

多表插入
#將查詢到的記錄插入到已存在的表中
insert 表名(欄位列表) select 語句
insert brand(brand_name) select brand_name from goods group by brand_name;

#創建新表並將查詢到的記錄插入到新表中
create 表名[(
欄位名 數據類型 [約束]，
............
)] select 語句 ;

create table category(
cid smallint unsigned key auto_increment
)
select cate_name from goods group by cate_name;

多表查詢
select 欄位列表 from 表1 [[as] 別名] 連接類型 表2 [[as] 別名] on 連接條件 where ......

連接類型
內連接 inner join
外聯接
左外連接 left [outer] join
右外連接 right [outer] join

#內連接：顯示左表及右表中符合連接條件的記錄
#左外連接：顯示左表全部記錄及右表中符合連接條件的記錄
#右外連接：顯示右表全部記錄及左表中符合連接條件的記錄

類似於內連接的多表查詢
select 欄位列表 from 表1,表2,表3...... where 連接條件1 and 連接條件2 .......

select id,goods_name,cate_name,brand_name,price from goods as g,brand as b,category as c where g.brand_id=b.bid and g.cate_id=c.cid;

比較操作符

like模糊查詢通配符
% 通配0個或多個字元 %李%
_ 通配一個字元
[abcdef] 通配字元列中的任何單一字元
[^abcdef] 通配不在字元列中的任何單一字元

邏輯操作符

MySQL數據類型
整型

int(10) unsigned zerofill //最大10位數字的無符號整數

浮點型

float[(總位數,小數點後位數)] 最大精確到小數點後7位
double[(總位數,小數點後位數)] 最大精確到小數點後15位
註：超過限定長度將進行四捨五入計算

字元型

布爾型
TRUE FALSE
日期型

實際開發當中，存儲日期時，我們一般使用整型來存儲時間戳，這樣做便於我們進行日期的計算

註意事項
CHAR的長度範圍在0~255之間
VARCHAR類型的長度範圍0~65535
在使用CHAR和VARCHAR類型時，當我們傳入的實際的值的長度大於指定的長度，字元串會被截取至指定長度
在使用CHAR類型時，如果我們傳入的值的長度小於指定長度，實際長度會使用空格補至指定長度
在使用VARCHAR類型時，如果我們傳入的值的長度小於指定長度，實際長度即為傳入字元串的長度，不會使用空格填補
CHAR要比VARCHAR效率更高，但占用空間較大
BLOB和TEXT類型是可以存放任意大數據的數據類型
BLOB區分大小寫，TEXT不區分大小寫
ENUM和SET類型是特殊的的串類型，其列值必須從固定的串集中選擇
ENUM只能選擇其中一個值，SET可以選擇多個值

約束
約束保證數據的完整性和一致性
約束分為表級約束和列級約束
約束類型包括
非空約束 not null
主鍵約束 primary key
唯一約束 unique key
預設約束 default
外鍵約束 foreign key

非空約束
空約束，欄位值允許為null
非空約束，欄位值不允許為null，必須賦值
#增加非空約束
create table mebmer(
username varchar(30) not null
);

自增長約束
必須與主鍵組合使用
預設情況下，起始值為1，增量為1
更改AUTO_INCREMENT初始值:
ALTER TABLE 表名稱 AUTO_INCREMENT=1

create table mebmer(
id int unsigned primary key auto_increment
);

主鍵約束
[primary] key
主鍵約束
每張表只能存在一個主鍵
主鍵必須保證記錄的唯一性
主鍵自動為not null
主鍵不必與自增長約束同時存在

create table mebmer(
id int unsigned auto_increment,
primary key (id)
);

預設約束
default
預設約束，當插入記錄時，如果沒有明確為欄位賦值，則自動賦予預設值
create table mebmer(
sex enum('male','female') default 'male'
);

外鍵約束
foreign key
外鍵約束
數據表的存儲引擎只能為InnoDB
外鍵列和參照列必須具有相似的數據類型
參照列必須存在索引
create table group(
id tinyint unsigned primary key auto_increment,
group varchar(20) unique not null
);
create table mebmer(
gid tinyint unsigned not null,
constraint member_group_key foreign key (gid) references group (id)
);

增加約束
#除外鍵約束外的其它約束
alter table 表名 change 欄位名 新欄位名 數據類型 約束
ALTER TABLE 表名 ADD UNIQUE/PRIMARY KEY(欄位名稱)
#外鍵約束
alter table 表名 add [constraint [約束名]] foreign key (外鍵欄位名) references 被參照表 (被參照欄位名)

刪除約束
#刪除唯一約束
ALTER TABLE 表名 DROP INDEX 欄位名
#刪除主鍵約束
ALTER TABLE 表名 DROP PRIMARY KEY
如果主鍵列存在自增長約束，刪除主鍵約束前必須先刪自增長約束
#刪除外鍵約束
ALTER TABLE 表名 DROP FOREIGN KEY 約束名
#刪除預設值
ALTER TABLE 表名 ALTER 欄位名 DROP DEFAULT；

done。