mysql資料庫基礎知識

什麼是資料庫

資料庫顧名思義，就是用來存儲數據的工具，用一個比例形象的例子來比喻，就是Excel，一個Excel文件就可以看成是一個資料庫。

關係型資料庫

就是以行與列構成的二維數據表的形式，用來存儲數據的，並且將多張數據表存儲在一個單元中的存儲形式，就稱為關係型資料庫。

數據表

由行與列構成的二維結構的存儲數據的表格。

記錄

就是數據表中行，一橫行數據我們就稱為一條數據記錄。

通常而言，每條數據記錄都有一個ID號，我們可以把這個id理解成是excel中的行號，用來對每條記錄進行區分與標記。

主鍵

很多時候我們將id稱為主鍵，主鍵這是指這張表的排列順序的依據。

欄位

數據欄位，就是數據表中的列。

每一個欄位都需要指定一個名稱，用來說明該列數據的作用，就叫作欄位名。

常用的資料庫的類型

Mysql 性能強悍，可以免費使用。

MSSQL server 微軟公司的資料庫軟體，通常用於搭配微軟體系的編程語言。

Access是office的辦公套件之一。

Oracel 性能非強悍，也非常昂貴。

安裝與使用mysql

安裝mysql的方式很多，我們使用phpstudy這個軟體來安裝php + mysql的運行環境。安裝之後，我們就擁有了mysql的服務。而且還有兩款mysql客戶端。

Mysql分為客戶端與服務端。服務端僅僅用於對軟體程式提供數據通信，普通用戶無法直接看到其中的數據內容。

客戶端用來將服務端的數據，以用戶可以理解的方式展示在其界面上。

phpMyAdmin

phpMyAdmin是最常用的mysql客戶端，它是基於php語言的。

在桌面右下角的phpstudy圖標上右擊彈出菜單中選擇phpMyAdmin就可以啟動進入了。啟動之後，需要輸入用戶與密碼進行登陸，預設的用戶名密碼都是root。

新建資料庫

1、 要指定資料庫的名稱。

2、 通常選擇utf8_unicode_ci字元集，在只有中文的情況下，可以選擇gbk_chinese_ic或gb2312_chinese_ci 。

新建數據表

1、 選中資料庫之後，點擊右側的“新建數據表”，填寫“數據表名”。

2、 指定數據表的欄位，也就是列。在指定欄位的過程中我們要對欄位添加欄位名、數據類型、數據長度、是否是主鍵、是否自增。（多數情況下，主鍵是名為id的整數類型，而且是自增的。）

3、 為欄位指定這一列的數據類型。

自增auto_increment

自增欄位中通常存放的是整數類型的數據，用於表示資料庫中的記錄的序號。

自增欄位的值不需要手動輸入，其中的編號是自動產生的，每當向這表裡面添加一條新記錄的時候，自增欄位會自動取出上一行欄位值，然後加一，作為新記錄的主鍵值。（主鍵絕對不會發生重覆，即使上一條記錄被刪除了）。

如何設置一個欄位為自增

1、在創建表或在修改欄位結構的時候，設置欄位的數據類型為int，然後選中auto_increment上的勾。

2、當插入一條數據時，不要手動填寫主鍵欄位。

3、主鍵生成的數字絕對不會重覆，即使記錄被刪除。

如何設置主鍵

1、在創建表或在修改欄位結構的時候，在主鍵欄位的”索引”下拉菜單中選擇“primary”。（一張表只允許設置一個主鍵，通常它是int自增的。）

小練習：

1、 新建一個產品product資料庫

2、 在資料庫中添加產品product表，並建立欄位pId （主鍵）、pName（產品名稱）、pModel（產品型號）、pPrice（產品價格）

3、 添加5條產品信息

4、 在資料庫中再添加一個產品入庫表（saveInLib）,並建立欄位：sid（主鍵）、pName（產品名稱）、saveInTime（入庫時間）、saveInCount（入庫數量）。

5、 添加5條產品入庫記錄。

mySQL中的數據類型

在mySQL中每個欄位，都必須明確它存放的數據的類型，一旦指定了類型，該欄位（列）中的數據都必須符合這個類型的範圍，否則就會引起錯誤。

並且字元等類型需要指定內容的最大長度。

int整數類型

只能存放整數

varchar字元類型

可以存儲任一字元，包括符號、數字、字母，但是他們都會被當成字元為處理。

Date日期

用於存儲日期和時間

數值類型

TinyInt 非常小的整數存儲格式，1位元組（8位的二進位數），它的取值範圍是：不帶符號時0 ~ 255，帶正負號時-128 ~ 127

SmallInt 小整數，2位元組，不帶符號時0~65535，帶正負號時-32768~32767

MediumInt 中等整數，3位元組，不帶符號時0~16777215，帶正負號時-8388608 ~ 8388607

Int 標準整數，4位元組，不帶符號時0 ~ 4294967295 ，帶正負號時-2147483648 ~ 2147483647

BigInt 大整數，8位元組，不帶符號時0~18446744073709551615

Float 單精度浮點數，4位元組，最小值，正負1.175494351E-58（1.175494351*10^-58）最大值，正負3.102823466E+38(3.102823466E*10³⁸)

Double 雙精度浮點數，8位元組，最小值，正負2.22507385072014E-308，最大值：正負1.17976931348623157E+308。

Decimal 以字元串形式存儲浮點數。它的取值範圍不是固定的，取決於用戶指定的長度。

數值類型的欄位可以設置的參數：

unsigned屬性

“整理”菜單中的unsigned選項，代表無符號，代表這一欄位中的數值不能為負數，因為無符號就是代表沒有正負號。如果“整理”菜單留空，什麼都不選的話，就是代表有符號，可以為正數也可以為負數。

unsigned zerofill屬性

當數據的寬度小於類型的最大寬度時，則在數據前面自動填充0

auto_increment

自增

預設à定義

這個選項代表，填充數據時如果該欄位為空值時，所使用的預設值。

字元串數據類型

字元類型可以存儲任何值，甚至是二進位形式的圖像、聲音、視頻。

CHAR[M] 代表M位元組的字元。

varChar 常用的字元存儲格式，使用時需要指定最大內容長度。

存儲大容量的文本

TinyBlob ，blob，TinyText，text

前兩個是代表二進位字元串，後兩個是代表非二進位字元串，都可以存儲大量的文本信息。

MediumBlob，MediumText

LongBlob，LongText

枚舉類型

ENUM / SET 類型

設置其預設值為(‘值1’, ‘值2’, ‘值3’,…)，由用戶指定多個可選值，欄位中的值必須是其中之一，最多只能有65535個可選值。

日期時間類型

Date 1000-01-01 ~ 9999-12-31

TIME -838:59:59~838:59:59

DateTime 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

TimeStmp 1970-01-01 00:00:00 到2037年的之間的某一個時刻

Year 存儲1901~2155年的一個年份。

小練習：

1、 創建一張員工數據表，employee，包含欄位：eId(標準整數，主鍵、自增)、姓名eName(varchar,50)、年齡eAge（tinyInt，無符號）、工號eNum（smallInt，不足的位數用0填充）、學歷（枚舉：初中~研究生）、性別（枚舉：男，女）、出生日期（DateTime）、基本工資（Float，預設:1300）、自我介紹text。然後輸入五名員工的信息。

T-SQL語言基礎之增刪改查

SQL語句

是一門專門用於資料庫操作的語言，SQL語句的語法不僅僅適用於mysql資料庫，同時也適用於幾乎所有的主流資料庫。當然不同公司出口的資料庫在語法細節上還是有些差異。

select語句

查詢語句，專門用於在數據表中按照用戶指定的條件進行查詢。

簡要語法描述：

SELECT <欄位1,欄位2，欄位3…..> FROM <數據表名> [WHERE 篩選條件]

其中欄位部分代表你想要從這張數據表中查詢哪些欄位（列），如果要查詢所有欄位，可以 * 號表示。

示例：

SELECT pName FROM product

//從產品表中查詢所有產品名稱

SELECT pName,pPrice FROM product

//從產品表中查詢所有產品名稱和價格

SELECT * FROM product

//從產品表中查詢中所有產品的欄位

SELECT pName,pPrice-30 FROM product

//在查詢時給所有產品減30元

SELECT pName as '產品名',pPrice as '價格' FROM product

//在查詢時修改結果集中的欄位名

SELECT 3.1415926 *12 *12

//用select語句來進行數學運算

 

where子句

定義一個查詢條件，然後在查詢過程中用這個條件來篩選符合條件的記錄。

SELECT * FROM product WHERE pPrice >=1000

查詢價格大於等於1000的產品

SELECT * FROM product WHERE pPrice <>3000

SELECT * FROM product WHERE pPrice !=3000

查詢價格不等於3000的產品

SELECT * FROM product WHERE pName = '中興z954'

查詢產品名稱是中興z954的產品信息（在mysql中雙引號與單引號都表示字元串，但是推薦使用單引號，因為我們將來要學習的mssql server 中字元串用單引號表示）

邏輯與and

SELECT * FROM product
WHERE pPrice <800 AND pId >6 AND pName ='Nokia v998'

邏輯或or

SELECT * FROM product

WHERE pPrice <800 OR pId >6

邏輯非not

SELECT * FROM product

WHERE not pName = '中興z954'

insert插入數據

向指定的數據表的指定欄位插入一條記錄。

語法：

INSERT INTO 表名(欄位1,欄位2，欄位3，……) values(值1，值2，值3，….)

其中，欄位的位置與值的位置必須一一對應。

示例：

INSERT INTO userinfo(uName,uPhone,uIDCard)

VALUES('江小白','010-89562321','5001234567814541X')

向數據表中插入一條用戶信息

INSERT INTO userinfo(uName) VALUES('張小強')

插入一條用戶信息，但是只有姓名，其他欄位自動留空或使用預設值（如果其中某個欄位設置為不允許為null，而沒有預設值的話，就必須要給這個欄位賦值了）。

INSERT INTO userinfo VALUES

(3,'王小虎','010-89562321','5001234567814541X')

可以省略欄位名，但是所有的欄位都必須按照順序來依次賦值。包含主鍵ID（要按照順序來填寫主鍵值）

一次性插入多條語句

insert into student(sName,sAge,sSex,sPhone)

values('小黑',20,'女','010-89562314'),

('小強',20,'男','020-89562314'),

('小紅',20,'女','021-89562314'),

('小黃',20,'男','019-89562314'),

('小李',20,'女','022-89562314')

Update修改數據

根據指定的條件確定需要修改的行，然後修改指定欄位的數據。

語法：

update 數據表名 set 欄位1=值1，欄位2=值2，……..WHERE 條件

雖然where是一個可選參數，但是通常情況下都需要寫where條件，如果不寫就是修改整張表的所有行。

示例：

UPDATE userinfo SET uName = '江老怪' WHERE uid = 1

修改第1條記錄的姓名為江老怪

UPDATE userinfo SET uName = '江老怪'

修改所有行的用戶名為江老怪

 

UPDATE userinfo

SET uName = '小二黑',uPhone='111111',uIDCard='11111111' WHERE uid = 3

delete刪除

根據條件刪除指定的記錄

語法：

delete from 表名 where 條件

雖然where是一個可選參數，但是通常情況下都需要寫where條件，如果不寫就是刪除整張表的所有行。

示例：

DELETE FROM userinfo WHERE uid = 2

小練習：

1、 記憶背誦增刪改查指定的語法。

2、 手動新建一個資料庫product，在其中新建數據表product，這個表中的欄位：pid（主鍵）、pName（產品名）、pModel（型號）、pIntro（簡介）、pFrom(產地)、pPrice（價格） 。

3、 用insert語句添加10條不同的產品數據

4、 查詢

a) 查詢所有產品數據

b) 查詢所有產品的產品名和價格

c) 查詢所有價格在2000以上的產品

d) 查詢所有產品為重慶並且價格大於3000的產品

e) 查詢所有產地不是重慶的產品

5、 將第5條記錄的產品號修改為T1000

6、 刪除所有價格小於5元的產品。

將第三題之後的sql語句複製到一個word文檔中，然後組長檢查組員，班長檢查組長。

3、5、6每題20分。

第4題中的每個小題8分共40分

命令行與高級查詢

Mysql命令行

1、 在phpstudy的右鍵菜單中，mysql工具 à mysql命令行

2、 彈出的命令框中提示”enter password”，輸入預設密碼root

3、 如果看到”you mysql connection”就說明你登陸成功了。

4、 在命令行中，每一條指令都必須以分號;結束，否則系統會認為你這條指令還沒有完成，始終要求你繼續輸入。

查看資料庫列表

show Databases

選擇資料庫use

格式：

use 資料庫名

選定指定資料庫為當前預設被操作的資料庫。

在使用操作數據表中的數據之前，必須要選定一個當前資料庫。

羅列出當前資料庫中的數據表

show tables

羅列出數據表中的欄位

格式：show columns from 表名

或

Describe 數據表名

資料庫與數據操作

創建一個新的資料庫

命令格式：create database 資料庫名

在當前資料庫中創建數據表

註意，在創建之前必須選擇當前資料庫。

格式：create table 表名(欄位1 數據類型,欄位2 數據類型，……)

示例：

create table stdInfo(

sName varchar(20),

sAge int,

sSex varchar(5)

);

創建包含自增主鍵的數據表

示例：創建客戶表：

create table customers(

 id int not null auto_increment,

 name varchar(20) not null,

 age int not null,

 address varchar(100) not null default 'empty',

 primary key(id)

);

說明：

not null 代表該欄位不允許出現空值，就是說當你使用insert語句插入數據記錄的時候，必須向這個欄位賦值，否則數據操作將發生錯誤。

auto_increment 自增

Default 預設值

Primary key 指定數據表的主鍵

刪除數據表

格式：drop table 數據表名

刪除資料庫

格式：drop database 資料庫名

更改表結構：增加欄位

添加一個欄位

alter table student add sScore float null;

其中：

add 代表增加一列

Null 代表這個欄位允許空值。

student 是數據表名

sScore 是新增的欄位名，後面是數據類型。

更改表結構：刪除欄位

alter table student drop column sScore;

其中：

drop column 代表刪除欄位操作

sScore 要被刪除的欄位名

student 是表名

重命名數據表

rename table student to sInfo;

註意：to前面是修改前的原表名，to後面是修改後的新表名。

更改表結構：添加自增主鍵

alter table product

 add pid int

 not null

 primary key

 auto_increment

 first;

其中

primary key 設置新欄位為主鍵

first 將新欄位放在其他欄位的前面，處於第一位。

小練習：

請同學把所有SQL都寫到一個SQL文件中，按“班級-姓名(可以是拼音).SQL”的方式保存

某學校的學生管理資料庫中有學生表(T_STUDENT)、班級表(T_CLASS)，表結構及存儲的數據如下表所示：

學生表(T_STUDENT)：

班級表(T_CLASS)：

用 SQL 語言完成以下功能

1. 建庫、建表，要求添加主鍵
2. 插入指定的數據
3. 找出所有年齡小於19歲的學生學號、姓名、年齡。
4. 學生張三轉到化學系 111 班，請更新相關的表。
5. 刪除班級表的主鍵這一列。
6. 將學生表改名為T_STD
7. 為班級表添加欄位CLS_COUNT人數欄位
8. 為班級表添加主鍵。
9. 刪除班級表

mysql_4_聚合函數

mysql資料庫的備份與恢復

備份

在phpmyAdmin中使用“導出”功能將數據表的結構與數據保存為一個.sql文件（保存格式選擇SQL）

恢復

在phpmyAdmin中點擊“導入”，選擇.sql文件的路徑（格式選擇SQL），點擊執行。

mysql中的聚合函數

就是對指定欄位中的一列數據進行統計和運算的函數。

GROUP BY分組

在指定欄位中將數據內容重覆的記錄，聚合為一組。剔除重覆的值。

示例：

SELECT * FROM student GROUP BY sSex

SELECT sName FROM student GROUP BY sAge

COUNT 統計個數

用於統計（按條件）查詢出聚合後的記錄或查詢的結果一共有多少條。示例：

SELECT COUNT(*) FROM student //統計表中一共有多少條記錄

SELECT COUNT(1) FROM student //同上，性能更強。

SELECT COUNT(1) FROM student WHERE sAge>17 //統計符合條件的記錄總數

SELECT COUNT(1),sSex FROM student GROUP BY sSex

//將GROUP BY與COUNT兩個函數結合起來使用，按性別進行分組統計。

AVG求平均值

格式：AVG(欄位名)

對指定的欄位中(一列中) 的數據值進行求平均值的運算。

SELECT AVG(sAge) FROM student

SELECT AVG(sScore) FROM student WHERE sAge<=18

SELECT AVG(sScore),sSex FROM student GROUP BY sSex

GROUP_CONCAT分組連接

將一張表中的多行記錄中的指定的欄位值，連接成一個字元串。每個值之間以逗號進行分隔。通常用於得到聚合後的每個分組中包穀的成員。

SELECT GROUP_CONCAT( sName ) FROM student

//連接所有學生的姓名

SELECT GROUP_CONCAT( sName ) FROM student WHERE sAge>18

//連接所有18歲以上的學生姓名

SELECT GROUP_CONCAT(sName),sSex FROM student GROUP BY sSex

//得到所有男生和女生的名單

ORDER BY排序

按照指定的欄位的值的大小的次序，來排列查詢的結果。

SELECT * FROM student ORDER BY sScore

//通過成績來排列學生。預設為升序（從小到大）

SELECT * FROM student ORDER BY sScore DESC

//通過成績來排列學生。添加DESC就是降序（從大到小）

最大值最小值MAX()、MIN()

在查詢結果中的指定欄位中找到最大的值或最小的值。

SELECT MAX(sScore) FROM student

//得到成績最高分數

SELECT MIN(sScore),sSex FROM student GROUP BY sSex

//分別得到男生和女生最低分

求和sum()

在查詢結果中對指定欄位的值求和。

SELECT SUM(sSCore) FROM student

//全班總分

SELECT SUM(sSCore),sSex FROM student GROUP BY sSex

//查看男生總分和女生總分

小練習：

1、用Create指令創建一個產品銷售記錄數據表，包含欄位：產品名、銷售地區、銷售數量、銷售金額。(10分)

2、假設本公司出售的產品只有三種，銷售地區也只有三個。用insert語句插入十條產品銷售記錄。（產品與銷售地區肯定有重覆值。）(15分)

3、統計每種產品的銷售總額(10<