MySQL基礎(chǔ)
一、MySQL概述
1、什么是數(shù)據(jù)庫 ？
答：數(shù)據(jù)的倉庫，如：在ATM的示例中我們創(chuàng)建了一個 db 目錄，稱其為數(shù)據(jù)庫
2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ？
答：他們均是一個軟件，都有兩個主要的功能：
- a. 將數(shù)據(jù)保存到文件或內(nèi)存
- b. 接收特定的命令，然后對文件進行相應(yīng)的操作
3、什么是SQL ？
答：MySQL等軟件可以接受命令，并做出相應(yīng)的操作，由于命令中可以包含刪除文件、獲取文件內(nèi)容等眾多操作，對于編寫的命令就是是SQL語句。
二、MySQL安裝
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下公司。MySQL 最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，在 WEB 應(yīng)用方面MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫管理系統(tǒng)) 應(yīng)用軟件之一。
想要使用MySQL來存儲并操作數(shù)據(jù)，則需要做幾件事情：
a. 安裝MySQL服務(wù)端
b. 安裝MySQL客戶端
b. 【客戶端】連接【服務(wù)端】
c. 【客戶端】發(fā)送命令給【服務(wù)端MySQL】服務(wù)的接受命令并執(zhí)行相應(yīng)操作(增刪改查等)
下載
http://dev.mysql.com/downloads/mysql/
安裝
windows：
http://jingyan.baidu.com/article/f3ad7d0ffc061a09c3345bf0.html
linux：
yum install mysql-server
mac：
一直點下一步
客戶端連接
連接：
1、mysql管理人默認為root，沒有設(shè)置密碼則直接登錄
mysql -h host -u root -p 不用輸入密碼按回車自動進入
2、如果想設(shè)置mysql密碼
mysqladmin -u root password 123456
3、如果你的root現(xiàn)在有密碼了（123456），那么修改密碼為abcdef的命令是：
mysqladmin -u root -p password abcdef
退出：
QUIT 或者 Control+D
三、數(shù)據(jù)庫基礎(chǔ)
分為兩大部分：
1、數(shù)據(jù)庫和表的創(chuàng)建;
2、數(shù)據(jù)庫和表內(nèi)容的操作
數(shù)據(jù)庫操作-思路圖
1、數(shù)據(jù)庫和表的創(chuàng)建
（一）數(shù)據(jù)庫的創(chuàng)建
1.1、顯示數(shù)據(jù)庫
1 SHOW DATABASES;
默認數(shù)據(jù)庫：
mysql - 用戶權(quán)限相關(guān)數(shù)據(jù)
test - 用于用戶測試數(shù)據(jù)
information_schema - MySQL本身架構(gòu)相關(guān)數(shù)據(jù)
1.2、創(chuàng)建數(shù)據(jù)庫
# utf-8 CREATE DATABASE 數(shù)據(jù)庫名稱 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # gbk CREATE DATABASE 數(shù)據(jù)庫名稱 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
1.3、打開數(shù)據(jù)庫
USE db_name;
注：每次使用數(shù)據(jù)庫必須打開相應(yīng)數(shù)據(jù)庫
顯示當前使用的數(shù)據(jù)庫中所有表：SHOW TABLES;
1.4、用戶管理
用戶設(shè)置:
創(chuàng)建用戶
create user '用戶名'@'IP地址' identified by '密碼';
刪除用戶
drop user '用戶名'@'IP地址';
修改用戶
rename user '用戶名'@'IP地址'; to '新用戶名'@'IP地址';;
修改密碼
set password for '用戶名'@'IP地址' = Password('新密碼')
PS：用戶權(quán)限相關(guān)數(shù)據(jù)保存在mysql數(shù)據(jù)庫的user表中，所以也可以直接對其進行操作（不建議）
用戶權(quán)限設(shè)置:
show grants for '用戶'@'IP地址' -- 查看權(quán)限 grant 權(quán)限 on 數(shù)據(jù)庫.表 to '用戶'@'IP地址' -- 授權(quán) revoke 權(quán)限 on 數(shù)據(jù)庫.表 from '用戶'@'IP地址' -- 取消權(quán)限
對于權(quán)限設(shè)置 對于數(shù)據(jù)庫名的解釋 對于ip地址的訪問 實際例子1.4、備份庫和恢復庫
備份庫：
MySQL備份和還原,都是利用mysqldump、mysql和source命令來完成。
1.在Windows下MySQL的備份與還原
備份 1、開始菜單 | 運行 | cmd |利用“cd /Program Files/MySQL/MySQL Server 5.0/bin”命令進入bin文件夾 2、利用“mysqldump -u 用戶名 -p databasename >exportfilename”導出數(shù)據(jù)庫到文件，如mysqldump -u root -p voice>voice.sql，然后輸入密碼即可開始導出。 還原 1、進入MySQL Command Line Client，輸入密碼，進入到“mysql>”。 2、輸入命令"show databases；"，回車，看看有些什么數(shù)據(jù)庫；建立你要還原的數(shù)據(jù)庫，輸入"create database voice；"，回車。 3、切換到剛建立的數(shù)據(jù)庫，輸入"use voice；"，回車；導入數(shù)據(jù)，輸入"source voice.sql；"，回車，開始導入，再次出現(xiàn)"mysql>"并且沒有提示錯誤即還原成功。
2、在linux下MySQL的備份與還原
2.1 備份(利用命令mysqldump進行備份) [root@localhost mysql]# mysqldump -u root -p voice>voice.sql，輸入密碼即可。 2.2 還原 方法一： [root@localhost ~]# mysql -u root -p 回車，輸入密碼，進入MySQL的控制臺"mysql>"，同1.2還原。 方法二： [root@localhost mysql]# mysql -u root -p voice<voice.sql，輸入密碼即可。
3、更多備份及還原命令
更多備份更多備份知識：
http://www.jb51.net/article/41570.htm
（二）數(shù)據(jù)表的創(chuàng)建
1.1、顯示數(shù)據(jù)表
show tables;
1.2、創(chuàng)建數(shù)據(jù)表
create table 表名(
列名 類型 是否可以為空，
列名 類型 是否可以為空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
設(shè)置是否為空 設(shè)置默認值 設(shè)置自增 設(shè)置主鍵 設(shè)置外鍵主鍵與外鍵關(guān)系(非常重要)
http://www.rzrgm.cn/programmer-tlh/p/5782451.html
1.3刪除表
drop table 表名
1.4、清空表
delete from 表名 truncate table 表名
1.5、基本數(shù)據(jù)類型
MySQL的數(shù)據(jù)類型大致分為：數(shù)值、時間和字符串
bit[(M)]
二進制位（101001），m表示二進制位的長度（1-64），默認m＝1
tinyint[(m)] [unsigned] [zerofill]
小整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號：
-128 ～ 127.
無符號：
0 ～ 255
特別的： MySQL中無布爾值，使用tinyint(1)構(gòu)造。
int[(m)][unsigned][zerofill]
整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號：
-2147483648 ～ 2147483647
無符號：
0 ～ 4294967295
特別的：整數(shù)類型中的m僅用于顯示，對存儲范圍無限制。例如： int(5),當插入數(shù)據(jù)2時，select 時數(shù)據(jù)顯示為： 00002
bigint[(m)][unsigned][zerofill]
大整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號：
-9223372036854775808 ～ 9223372036854775807
無符號：
0 ～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
準確的小數(shù)值，m是數(shù)字總個數(shù)（負號不算），d是小數(shù)點后個數(shù)。 m最大值為65，d最大值為30。
特別的：對于精確數(shù)值計算時需要用此類型
decaimal能夠存儲精確值的原因在于其內(nèi)部按照字符串存儲。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
單精度浮點數(shù)（非準確小數(shù)值），m是數(shù)字總個數(shù)，d是小數(shù)點后個數(shù)。
無符號：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符號：
0
1.175494351E-38 to 3.402823466E+38
**** 數(shù)值越大，越不準確 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
雙精度浮點數(shù)（非準確小數(shù)值），m是數(shù)字總個數(shù)，d是小數(shù)點后個數(shù)。
無符號：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符號：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 數(shù)值越大，越不準確 ****
char (m)
char數(shù)據(jù)類型用于表示固定長度的字符串，可以包含最多達255個字符。其中m代表字符串的長度。
PS: 即使數(shù)據(jù)小于m長度，也會占用m長度
varchar(m)
varchars數(shù)據(jù)類型用于變長的字符串，可以包含最多達255個字符。其中m代表該數(shù)據(jù)類型所允許保存的字符串的最大長度，只要長度小于該最大值的字符串都可以被保存在該數(shù)據(jù)類型中。
注：雖然varchar使用起來較為靈活，但是從整個系統(tǒng)的性能角度來說，char數(shù)據(jù)類型的處理速度更快，有時甚至可以超出varchar處理速度的50%。因此，用戶在設(shè)計數(shù)據(jù)庫時應(yīng)當綜合考慮各方面的因素，以求達到最佳的平衡
text
text數(shù)據(jù)類型用于保存變長的大字符串，可以組多到65535 (2**16 ? 1)個字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.
enum
枚舉類型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合類型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）
1.6、修改表(alter)
修改表更多參考：
- http://www.runoob.com/mysql/mysql-data-types.html
1.7、數(shù)據(jù)表關(guān)系
關(guān)聯(lián)映射：一對多/多對一
約束是一種限制，它通過對表的行或列的數(shù)據(jù)做出限制，來確保表的數(shù)據(jù)的完整性、唯一性。
MYSQL中，常用的幾種約束：
===================================================
主鍵(PRIMARY KEY)是用于約束表中的一行，作為這一行的標識符，在一張表中通過主鍵就能準確定位到一行，因此主鍵十分重要。主鍵要求這一行的數(shù)據(jù)不能有重復且不能為空。
還有一種特殊的主鍵——復合主鍵。主鍵不僅可以是表中的一列，也可以由表中的兩列或多列來共同標識
===================================================
默認值約束(DEFAULT)規(guī)定，當有DEFAULT約束的列，插入數(shù)據(jù)為空時該怎么辦。
DEFAULT約束只會在使用INSERT語句（上一實驗介紹過）時體現(xiàn)出來，INSERT語句中，如果被DEFAULT約束的位置沒有值，那么這個位置將會被DEFAULT的值填充
===================================================
唯一約束(UNIQUE)比較簡單，它規(guī)定一張表中指定的一列的值必須不能有重復值，即這一列每個值都是唯一的。
當INSERT語句新插入的數(shù)據(jù)和已有數(shù)據(jù)重復的時候，如果有UNIQUE約束，則INSERT失敗.
===================================================
外鍵(FOREIGN KEY)既能確保數(shù)據(jù)完整性，也能表現(xiàn)表之間的關(guān)系。
一個表可以有多個外鍵，每個外鍵必須REFERENCES(參考)另一個表的主鍵，被外鍵約束的列，取值必須在它參考的列中有對應(yīng)值。
在INSERT時，如果被外鍵約束的值沒有在參考列中有對應(yīng)，比如以下命令，參考列(department表的dpt_name)中沒有dpt3，則INSERT失敗
===================================================
非空約束(NOT NULL),聽名字就能理解，被非空約束的列，在插入值時必須非空。
在MySQL中違反非空約束，不會報錯，只會有警告.
例子2、數(shù)據(jù)庫和表內(nèi)容的操作(增、刪、改、查)
1、增
insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表
2、刪
delete from 表 delete from 表 where id＝1 and name＝'alex'
3、改
update 表 set name ＝ 'alex' where id>1
4、查
4.1、普通查詢
select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1
更多選項查詢4.2、數(shù)據(jù)排序(查詢)
排序
select * from 表 order by 列 asc - 根據(jù) “列” 從小到大排列
select * from 表 order by 列 desc - 根據(jù) “列” 從大到小排列
select * from 表 order by 列1 desc,列2 asc - 根據(jù) “列1” 從大到小排列，如果相同則按列2從小到大排序
4.3、模糊查詢
通配符(模糊查詢)
select * from 表 where name like 'ale%' - ale開頭的所有（多個字符串）
select * from 表 where name like 'ale_' - ale開頭的所有（一個字符）
4.4、聚集函數(shù)查詢
聚集函數(shù) 4.5、分組查詢
分組
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
select num from 表 group by num having max(id) > 10
特別的：group by 必須在where之后，order by之前
4.6多表查詢
a、連表
無對應(yīng)關(guān)系則不顯示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid
無對應(yīng)關(guān)系則不顯示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid
A表所有顯示，如果B中無對應(yīng)關(guān)系，則值為null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid
B表所有顯示，如果B中無對應(yīng)關(guān)系，則值為null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid
b、組合
組合，自動處理重合
select nickname
from A
union
select name
from B
組合，不處理重合
select nickname
from A
union all
select name
from B
浙公網(wǎng)安備 33010602011771號