MYSQL 入門全套

MySQL 簡介

1 、什麼是資料庫 ？

資料庫（Database）是按照資料結構來組織、儲存和管理資料的倉庫，它產生於距今六十多年前，隨著資訊科技和市場的發展，特別是二十世紀九十年代以後，資料管理不再僅僅是儲存和管理資料，而轉變成使用者所需要的各種資料管理的方式。資料庫有很多種型別，從最簡單的儲存有各種資料的表格到能夠進行海量資料儲存的大型資料庫系統都在各個方面得到了廣泛的應用。

主流的資料庫有：sqlserver，mysql，Oracle、SQLite、Access、MS SQL Server等，本文主要講述的是mysql

2 、資料庫管理是幹什麼用的？

·       a. 將資料儲存到檔案或記憶體

·       b. 接收特定的命令，然後對檔案進行相應的操作

PS ：如果有了以上管理系統，無須自己再去建立檔案和資料夾，而是直接傳遞 命令 給上述軟體，讓其來進行檔案操作，他們統稱為資料庫管理系統（DBMS，Database Management System）

MySQL 安裝

MySQL 是一種開放原始碼的關係型資料庫管理系統（RDBMS），MySQL資料庫系統使用最常用的資料庫管理語言–結構化查詢語言（SQL）進行資料庫管理。在 WEB 應用方面MySQL是最好的 RDBMS (Relational Database Management System，關聯式資料庫管理系統) 應用軟體之一。

使用mysql必須具備一下條件

 

·       a. 安裝MySQL服務端

 

·       b. 安裝MySQL客戶端

 

·       c. 【客戶端】連線【服務端】

 

·       d. 【客戶端】傳送命令給【服務端MySQL】服務的接受命令並執行相應操作(增刪改查等)

MySQL 操作

一、連線資料庫

mysql  -u user -p                   


 

例：mysql -u root -p

常見錯誤如下：

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

 

退出連線：

QUIT 或者 Ctrl+D

二、檢視資料庫，建立資料庫，使用資料庫檢視資料庫: 

show databases;

預設資料庫：

mysql

 - 

使用者許可權相關資料


 

test

 - 

用於使用者測試資料


 

information_schema

 - MySQL

本身架構相關資料

建立資料庫:    

create
 

database

 db1 

DEFAULT
 

CHARSET

 utf8 

COLLATE

 utf8_general_ci; 

# utf8

編碼


 

 

create
 

database

 db1 

DEFAULT
 

CHARACTER
 

SET

 gbk 

COLLATE

 gbk_chinese_ci; 

# gbk

編碼

使用資料庫:

 use db1;

顯示當前使用的資料庫中所有表：

SHOW TABLES;

三、使用者管理

建立使用者

create user '使用者名稱'@'IP地址' identified by '密碼';

刪除使用者

drop user '使用者名稱'@'IP地址';

修改使用者

rename user '使用者名稱'@'IP地址'; to '新使用者名稱'@'IP地址';

修改密碼

set password for '使用者名稱'@'IP地址' = Password('新密碼');

注：使用者許可權相關資料儲存在mysql資料庫的user表中，所以也可以直接對其進行操作（不建議）

四、許可權管理

mysql 對於許可權這塊有以下限制：

all privileges

：

除grant外的所有許可權


 

select

：

僅查許可權


 

select,insert

：

查和插入許可權


 

...


 
usage

：

無訪問許可權


 

alter

：

使用alter table


 

alter routine

：

使用alter procedure和drop procedure


 

create

：

使用create table


 

create routine

：

使用create procedure


 

create temporary tables

：

使用create temporary tables


 

create user

：

使用create user、drop user、rename user和revoke  all privileges


 

create view

：

使用create view


 

delete

：

使用delete


 

drop

：

使用drop table


 

execute

：

使用call和儲存過程


 

file

：

使用select into outfile 和 load data infile


 

grant option

：

使用grant 和 revoke


 

index

：

使用index


 

insert

：

使用insert


 

lock tables

：

使用lock table


 

process

：

使用show full processlist


 

select

：

使用select


 

show databases

：

使用show databases


 

show view

：

使用show view


 

update

：

使用update


 

reload

：

使用flush


 

shutdown

：

使用mysqladmin shutdown(關閉MySQL)


 

super

：

使用change master、kill、logs、purge、master和set global。還允許mysqladmin除錯登陸


 

replication client

：

伺服器位置的訪問


 

replication slave

：

由複製從屬使用

對於資料庫及內部其他許可權如下：

資料庫名.*  

          

資料庫中的所有


 

資料庫名.表 

          

指定資料庫中的某張表


 

資料庫名.儲存過程

      

指定資料庫中的儲存過程


 

*.*    

               

所有資料庫

對於使用者和IP的許可權如下：

使用者名稱@IP地址   

     

使用者只能在改IP下才能訪問


 

使用者名稱@192.168.1.% 

  

使用者只能在改IP段下才能訪問(萬用字元%表示任意)


 

使用者名稱@% 

            

使用者可以再任意IP下訪問(預設IP地址為%)

1 、檢視許可權：

show grants for '使用者'@'IP地址'

2 、授權

grant  許可權 on 資料庫.表 to   '使用者'@'IP地址'

3 、取消授權

revoke 許可權 on 資料庫.表 from '使用者名稱'@'IP地址'

授權例項如下：

grant
 

all
 

privileges
 

on

 db1.tb1 

TO
 

'

使用者名稱'

@

'IP'


 

grant
 

select
 

on

 db1.* 

TO
 

'

使用者名稱'

@

'IP'


 

grant
 

select

,

insert
 

on

 *.* 

TO
 

'

使用者名稱'

@

'IP'


 

revoke
 

select
 

on

 db1.tb1 

from
 

'

使用者名稱'

@

'IP'

MySQL 表操作

1 、檢視錶

show
 

tables

; 

# 

檢視資料庫全部表


 

select

 * 

from
 

表名; 

# 

檢視錶所有內容

2 、建立表

create
 

table
 

表名(


 
    

列名  型別  是否可以為空，


 
    

列名  型別  是否可以為空


 
)

ENGINE

=

InnoDB
 

DEFAULT
 

CHARSET

=utf8

來一個例項好詳解

CREATE
 

TABLE
 

`tab1`

 (


 
  

`nid`
 

int

(

11

) 

NOT
 

NULL

 auto_increment,


 
  

`name`
 

varchar

(

255

) 

DEFAULT

 zhangyanlin,


 
  

`email`
 

varchar

(

255

),


 
  

PRIMARY
 

KEY

 (

`nid`

) 


 
) 

ENGINE

=

InnoDB
 

DEFAULT
 

CHARSET

=utf8;

注：

·       預設值，建立列時可以指定預設值，當插入資料時如果未主動設定，則自動新增預設值

·       自增，如果為某列設定自增列，插入資料時無需設定此列，預設將自增（表中只能有 一個自增列）注意：1、對於自增列，必須是索引（含主鍵）2、對於自增可以設定步長和起始值

·       主鍵，一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。

3 、刪除表

drop table 表名

4 、清空表內容

delete
 

from
 

表名


 

truncate
 

table
 

表名

5 、修改表

新增列：

   


 

alter
 

table
 

表名 

add
 

列名 型別


 

刪除列：  
 


 

alter
 

table
 

表名 

drop
 

column
 

列名


 

修改列：


 
          

 

alter
 

table
 

表名 modify 

column
 

列名 型別;  

-- 

型別


 

alter
 

table
 

表名 

change
 

原列名 新列名 型別; 

-- 

列名，型別


 

新增主鍵：


 
          

 

alter
 

table
 

表名 

add
 

primary
 

key

(

列名);


 

刪除主鍵：


 
          

 

alter
 

table
 

表名 

drop
 

primary
 

key

;


 

alter
 

table
 

表名  modify  列名 

int

, 

drop
 

primary
 

key

;


 

新增外來鍵： 


 

alter
 

table
 

從表 

add
 

constraint
 

外來鍵名稱（形如：FK_從表_主表） 

foreign
 

key
 

從表(外來鍵欄位) 

references
 

主表(主鍵欄位);


 

刪除外來鍵：
 


 

alter
 

table
 

表名 

drop
 

foreign
 

key
 

外來鍵名稱


 

修改預設值：


 

ALTER
 

TABLE

 testalter_tbl 

ALTER

 i 

SET
 

DEFAULT
 

1000

;


 

刪除預設值：


 

ALTER
 

TABLE

 testalter_tbl 

ALTER

 i 

DROP
 

DEFAULT

;

對於上述這些操作是不是看起來很麻煩，很浪費時間，別慌！有專門的軟體能提供這些功能，操作起來非常簡單，這個軟體名字叫Navicat Premium ，大家自行在網上下載，練練手，但是下面的即將講到表內容操作還是建議自己寫命令來進行

6 、基本資料型別

MySQL 的資料型別大致分為：數值、時間和字串

bit[(M)]


 
            

二進位制位（101001），m表示二進位制位的長度（1-64），預設m＝1


 

tinyint[(m)] [unsigned] [zerofill]


 
            

小整數，資料型別用於儲存一些範圍的整數數值範圍：


 
            

有符號：


 
                -128 

～ 127.


 
            

無符號：


 
                0 

～ 255


 
            

特別的： MySQL中無布林值，使用tinyint(1)構造。


 

int[(m)][unsigned][zerofill]


 
            

整數，資料型別用於儲存一些範圍的整數數值範圍：


 
                

有符號：


 
                    -2147483648 

～ 2147483647


 
                

無符號：


 
                    0 

～ 4294967295


 
            

特別的：整數型別中的m僅用於顯示，對儲存範圍無限制。例如： int(5),當插入資料2時，select 時資料顯示為：00002


 

bigint[(m)][unsigned][zerofill]


 
            

大整數，資料型別用於儲存一些範圍的整數數值範圍：


 
                

有符號：


 
                    -9223372036854775808 

～ 9223372036854775807


 
                

無符號：


 
                    0  

～  18446744073709551615


 

 

decimal[(m[,d])] [unsigned] [zerofill]


 


 
            

準確的小數值，m是數字總個數（負號不算），d是小數點後個數。 m最大值為65，d最大值為30。


 

 
            

特別的：對於精確數值計算時需要用此型別


 
                   decaimal

能夠儲存精確值的原因在於其內部按照字串儲存。


 

 

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]


 
            

 
            

單精度浮點數（非準確小數值），m是數字總個數，d是小數點後個數。


 
                

無符號：


 
               

     -3.402823466E+38 to -1.175494351E-38,


 
                    0

 
                    1.175494351E-38 to 3.402823466E+38

 
                

有符號：


 
                    0

 
                    1.175494351E-38 to 3.402823466E+38


 

 
            **** 

數值越大，越不準確 ****


 

 

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]


 


 
           

 

雙精度浮點數（非準確小數值），m是數字總個數，d是小數點後個數。


 

 
                

無符號：


 
                    -1.7976931348623157E+308 to -2.2250738585072014E-308

 
                    0

 
                    2.2250738585072014E-308 to 1.7976931348623157E+308

 
                

有符號：


 
                    0

 
                    2.2250738585072014E-308 to 1.7976931348623157E+308

 
            **** 

數值越大，越不準確 ****


 

char (m)


 
         

   char

資料型別用於表示固定長度的字串，可以包含最多達255個字元。其中m代表字串的長度。


 
            PS: 

即使資料小於m長度，也會佔用m長度


 
            

 

varchar(m)


 
           

 varchars

資料型別用於變長的字串，可以包含最多達255個字元。其中m代表該資料型別所允許儲存的字串的最大長度，只要長度小於該最大值的字串都可以被儲存在該資料型別中。


 
            

注：雖然varchar使用起來較為靈活，但是從整個系統的效能角度來說，char資料型別的處理速度更快，有時甚至可以超出varchar處理速度的50%。因此，使用者在設計資料庫時應當綜合考慮各方面的因素，以求達到最佳的平衡


 

text


 
        

    text

資料型別用於儲存變長的大字串，可以組多到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 年某時）

MySQL 表內容操作

表內容操作無非就是增刪改查，當然用的最多的還是查，而且查這一塊東西最多，用起來最難，當然對於大神來說那就是so easy了，對於我這種小白還是非常難以靈活運用的，下面我們來一一操作一下

1 、增

insert
 

into
 

表 (列名,列名...) 

values

 (

值,值,...)


 

insert
 

into
 

表 (列名,列名...) 

values

 (

值,值,...),(值,值,值...)


 

insert
 

into
 

表 (列名,列名...) 

select

 (

列名,列名...) 

from
 

表


 

例：


 
    

insert
 

into

 tab1(name,email) 

values

(

'zhangyanlin'

,

'zhangyanlin8851@163.com'

)

2 、刪

delete
 

from
 

表   

# 

刪除表裡全部資料


 

delete
 

from
 

表 

where

 id

＝

1
 

and

 name

＝

'zhangyanlin'
 

# 

刪除ID =1 和name='zhangyanlin' 那一行資料

3 、改

update 表 set name ＝ 'zhangyanlin' where id>1

4 、查

select

 * 

from
 

表


 

select

 * 

from
 

表 

where

 id > 

1


 

select

 nid,name,gender 

as

 gg 

from
 

表 

where

 id > 

1

查這塊的條件太多太多我給列舉出來至於組合還得看大家的理解程度哈

 

a 、條件判斷where

select

 * 

from
 

表 

where

 id > 

1
 

and

 name != 

'aylin'
 

and

 num = 

12

;


 

select

 * 

from
 

表 

where

 id 

between
 

5
 

and
 

16

;


 

select

 * 

from
 

表 

where

 id 

in

 (

11

,

22

,

33

)


 

select

 * 

from
 

表 

where

 id 

not
 

in

 (

11

,

22

,

33

)


 

select

 * 

from
 

表 

where

 id 

in

 (

select

 nid 

from
 

表)

b 、萬用字元like

select

 * 

from
 

表 

where

 name 

like
 

'zhang%'

  

# zhang

開頭的所有（多個字串）


 

select

 * 

from
 

表 

where

 name 

like
 

'zhang_'
 

 # zhang

開頭的所有（一個字元）

c 、限制limit

select

 * 

from
 

表 

limit
 

5

;            

- 

前5行


 

select

 * 

from
 

表 

limit
 

4

,

5

;          

- 

從第4行開始的5行


 

select

 * 

from
 

表 

limit
 

5

 offset 

4

    

- 

從第4行開始的5行

d 、排序asc，desc

select

 * 

from
 

表 

order
 

by
 

列 

asc

              

- 

根據 “列” 從小到大排列


 

select

 * 

from
 

表 

order
 

by
 

列 

desc

             

- 

根據 “列” 從大到小排列


 

select

 * 

from
 

表 

order
 

by
 

列

1
 

desc

,

列

2
 

asc

    

- 

根據 “列1” 從大到小排列，如果相同則按列2從小到大排序

e 、分組group by

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) > 

1


 

特別的：

group
 

by
 

必須在

where

之後，

order
 

by

之前