MySQL安裝與講解

一：數據庫講解

1  5.6之前的源碼安裝

 源碼格式   ./configure    選項

             make    make   install

2  yum安裝

  6  yum   -y   install   mysql-server    mysql

    service  mysqld start  ； chkconfig  mysqld on

  7  yum  -y groupinstall mariadb  mariadb-client

     Systemctl  enable/restart   mariadb

3  基礎

端口號   3306

進程名   mysqld

進程所有者/組    mysql/mysql

數據傳輸協議     tcp

主配置文件      /etc/my.cnf

數據庫目錄     /var/lib/mysql/

錯誤日志文件        /var/log/mysqld.log

進程pid號文件 /var/run/mysqld/mysqld.pid

每條sql命令必須以 ; 結尾

sql命令不區分字母大小寫

\c   結束sql命令

查看已有的庫    show  databases;

information_schema   虛擬庫

              存儲當前數據庫服務器上已有庫和表的信息/數據存儲在物理內存里

mysql              授權庫   

              存儲用戶登錄信息和權限信息/占用物理存儲空間

performance_schema  

存儲當前數據庫服務運行的參數信息

占用物理存儲空間

mysql體系結構 （8）

連接池

sql接口

解析器

優化器

緩存區  service  mysql  start   /etc/my.cnf 8M

存儲引擎

文件系統（硬盤  /var/lib/mysql）  

管理工具                              

4  數據庫連接

mysql   -h數據庫服務器Ip地址   -u用戶名    -p密碼    [庫名]

mysql -uroot -p123456 -e "show databases;"        非交互

5  設置密碼

 1 mysqladmin -hlocalhost -uroot   password "abc123"

 2 mysql> set  password  for  root@"localhost"=password("999");

修改數據庫管理員密碼

1  [root@www ~]# mysqladmin  -hlocalhost -uroot -p  password "999"

Enter password:   舊密碼

2  恢復數據庫管理員密碼

#service  mysql  stop

#service  mysql  start   --skip-grant-table

#mysql

mysql> update mysql.user

    -> set

    -> password=password("123")

    -> where

    -> host="localhost";

mysql> flush privileges;

#service  mysql  stop

#service  mysql  start

6  升級

安裝下載高版本的軟件包提供mysql數據庫服務

service  mysqld  stop

rpm  -e  --nodeps   mysql-server    mysql

rm  -rf  /etc/my.cnf

rm  -rf  /var/lib/mysql/*

tar -xvf MySQL-5.6.rpm.tar

rpm -Uvh   MySQL-*.rpm

rpm  -qa   | grep  -i mysql

service   mysql   start  ;chkconfig    mysql    on

cat  /root/.mysql_secret

mysql -hlocalhost -uroot -pNlUDn9Wn

mysql>

mysql> set  password  for  root@"localhost"=password("999");

mysql>quit

mysql -hlocalhost -uroot -p999

mysql> show  databases;

二  數據庫操作

1  部分SQL命令（庫）

查看當前登錄的用戶信息

select user();

查看當前所在庫的名字

select   database();

創建庫

create  database  庫名；

刪除庫

drop    database  庫名

切換庫

use   庫名；

數據庫名的命名規則？

可以使用數字/字母/下劃線，但不能純數字

區分大小寫，具有唯一性

不可使用指令關鍵字、特殊字符。

2  部分SQL命令（表）

查看里已有的表

show    tables；

查看表結構

desc   表名;

查看表記錄

select  *  from  表名;

select  字段名1，字段名2，字段名N from  表名；

select   字段名列表   from  庫名.表名 where  條件；

向表中添加記錄

insert    into   stuinfo(name)values("j"),("t");

表的使用（表必須存儲在庫里）

建表

create   table  表名(   

字段名1  字段類型(寬度)  字段約束，

字段名2  字段類型(寬度)  字段約束，

字段名3  字段類型(寬度)  字段約束，

.......

 );

復制表

create  table   表名   SQL查詢命令;

create  table  user2  select  * from user;

create  table  user3  select  id,name,uid from user limit 3;

create  table  user4  select  * from user where 1 = 2;

刪除表的所有記錄

delete  from 表名；

insert  into  bjb values(1,"zzz");

insert  into  bjb values(4,"bob");

update   表名   set   字段名=值  where  條件；

update   jfb   set   jfb_id=8 where  jfb_id=2;

delete  from 表名  where   條件；

delete   from  jfb  where  jfb_id=3;

delete from user where name regexp '[0-9]';

3  修改表結構  (受表中已有記錄的限制)

alter     table    表名  動作；  

add 添加新字段

add  字段名   類型（寬度）  約束條件；

alter  table  t26  

add   mail   varchar(30)  not null   default "plj@tarena.com",

add  qq   varchar(11);

alter  table  t26  add   stu_id   int(2)  first;

alter table  t26  add   age  tinyint(2) unsigned  not null default "21"  after    name;

drop  刪除已有字段

drop   字段名；

drop  字段名,drop 字段名;

4  modify  修改已有字段的類型

modify  字段名   類型（寬度） 約束條件；

alter table   t26  modify   sex  enum("boy","girl")  default "girl";

change  修改字段名

change   源字段名  新字段名  類型（寬度） 約束條件；

alter  table   t26  change   email  mail  varchar(10);

修改表名

alter  table   源表名　renam  [to]  新表名；

三  數據庫字段類型（表）

1 字符型    

  char     （255） 定長

          varchar  （65532）    變長

  blob

              text

create  table   stuinfo(name  char（10）);

create  table   stuinfo2(name  varchar(3)  );

2 數值型

   微小整型  小整型   中整型  大整型  極大整型

有符號           無符號

create  table   t9(name  char(5),age  tinyint  unsigned);

create  table   t10(name  char(5),age  int  unsigned);

create  table   t11(name  char(5),age  tinyint  unsigned，pay  int(2));

浮點型   float      單精度   4字節    

 double  雙精度   8字節

 float(N,M)

double(N,M)

N  數值的總位數

M  小數位位數

create  table   t11(

name  char(5),

age  tinyint  unsigned,

pay    float(7,2)

3枚舉型

set(值1，值2，值N)    選擇一個或多個

enum(值1，值2，值N）只能選擇一個

create  table  studb.t25(

name   varchar(10),

sex   enum("boy","girl"),

love  set("game","film","music","girl"),

class  set("network","system","server","shell")

);

4日期類型

年   year（YYYY）   

兩位自動補規則

      01~69   2001~2069

          70~99   1970~1999

create  table  t23(

name  char(10),

s_year   year

);

insert  into   t23   values("jim",01);

insert  into   t23   values("bob",80);

insert  into   t23   values("jerry",00);

日期  date（YYYYMMDD）

時間  time (HHMMSS)

日期時間

datetime

               1000-01-01 00:00:00.000000

               9999-12-31  23:59:59.999999

timestamp

               1970-01-01  00:00:00.000000

               2038-01-19  03:14:07.999999

create  table  t22(

time1   datetime,

time2   timestamp

);

insert  into   t22  values(20151211094418,20151211094418);

insert  into   t22(time2)values(20160214000000);

insert  into   t22(time1)values(20170214200000);

與日期相關函數

now()

year()

month()

day()

time()

select time(now());

select day(now());

select  now();

四：  字段約束條件的設置(限制如何給字段賦值)

1 是否允許給字段賦空null/NULL值   默認允許為NULL值

not   null

字段默認值  向表中添加新記錄時，不給字段賦值時，使用默認值給字段賦值，若沒有  

設置過默認值的值， 默認值的值是null值

2 default  值

create  table  t26(

name   varchar(10)  not  null,

sex   enum("boy","girl")  not  null  default   "boy",

love  set("game","film","music","girl")  default  "game,film",

class  set("network","system","server","shell")  default "shell"

);

insert  into   t26(name)values("jerry");

insert  into   t26    values("lucy","girl","game","network");

insert  into   t26    values(null,null,null,null)

3 非零zerofill 

五：索引

1 索引好處

索引優點  ： 加快查詢的速度

索引缺點   ： 占用物理存儲空間

                     減慢 update  insert   delete

2 索引查看

show  index  from   表名;

3 index索引

一個表中可以有多個INDEX字段

對應的字段值允許有重復

可以賦null 值

把經常做查詢條件的字段設置為INDEX字段

INDEX字段的KEY標志是MUL

方式一

create    index  name  on  tt26(name);

create    index  sex  on  tt26(sex);

方式二

create  table  t27(

name   varchar(10)  not  null,

sex   enum("boy","girl")  not  null  default   "boy",

love  set("game","film","music","girl")  default  "game,film",

class  set("network","system","server","shell")  default "shell",

index(name),

index(sex)

);

                     索引名           表名

drop  index    name   on   tt26;

4 unique索引

一個表中可以有多個UNIQUE字段

對應的字段值不允許有重復,但可以賦NULL值；

UNIQUE字段的值允許為NULL,將其修改為不允許為NULL，則此字段限制與主鍵相同

UNIQUE字段的KEY標志是UNI

方式一

create  unique  index    stu_id    on  tt26(stu_id);

方式二

create   table   t28(

stu_id  char(4) ,

iphone   char(11),

name  varchar(10),

unique(stu_id),

unique(iphone)

);

drop   index    stu_id    on  tt26;

5 主鍵（primary  key）

一個表中只能有一個PRIMARY字段

對應的字段值不允許重復且不允許為null

主鍵字段的KEY標志是PRI

如果有多個字段都作為PRIMARY KEY，稱為復合主鍵，必須在建表時一起創建.

通常與 AUTO_INCREMENT 連用

            讓字段的值自動增長   ++

            數值類型

            字段必須是primay key 字段                        

把表中能夠唯一定位一條記錄的字段設置為主鍵字段

create  table  t211(

name  varchar(10)   primary key ,

age  tinyint(2)

);

alter  table  t211  drop  primary key;

alter  table  t211  add  primary key(age);

create  table  t222(

id  int(2)  primary key   auto_increment,

name  varchar(10) not null,

age  tinyint(2) unsigned,

index(name)

);

insert into  t222 (name,age)   values("bob",23);

alter  table  t222  modify   id   int(2) not  null;

--------------------------------------------------------------------

alter  table   t222   

add

id   int(2)   primary  key   auto_increment  first ;

create  table   test3(

id1  int(3) zerofill,

level   int   zerofill  

);

數值類型的寬度是 顯示寬度 ，不能夠控制給字段賦值的大小。字段值的大小由字段類型決定。

insert  into  test3 values(9,9);

insert  into  test3 values(27,27);

------------------------------------------------------------------

create  table   sertab(

cip   varchar(15) ,

sername  varchar(20),

serport     smallint(2) ,

status   enum("deny","allow")   not null   default  "deny",

primary key (cip,serport)

);

6 外鍵

1 表的存儲引擎必須是innodb

2 字段的類型要一致

3 被參考字段必須是一個key(主鍵)

create  table  表名(字段名列表)engine=存儲引擎名   DEFAULT CHARSET=字符集；

創建外鍵

foreign  key(字段名)  references   表名（字段名）   on  delete cascade     on update  cascade

繳費表

create  table   jfb(

jfb_id   tinyint(2)   primary key  auto_increment,

class   char(7)  not null default "nsd1509",

name  varchar(15),

xf  float(7,2)  default "17800"

)engine=innodb;

insert into jfb(name)values("zzz");

insert into jfb(name)values("mda");

insert into jfb(name)values("jack");

班級表

create   table   bjb(

bjb_id  tinyint(2),

name  varchar(15),

foreign  key(bjb_id)  references   jfb(jfb_id)   on  delete cascade     on update  cascade

)engine=innodb;

刪除表字段的外鍵屬性

mysql> alter table  bjb drop foreign key   bjb_ibfk_1;

六：存儲引擎

1 查看當前數據庫服務器支持哪些存儲引擎

show  engines;

2 存儲引擎MyISAM與InnoDB

 MyISAM

不支持事務、事務回滾 、 外鍵

支持表級鎖

獨享表空間  bt.frm    表結構

                    bt.MYD  表記錄

                    bt.MYI    表索引

表級鎖

 InnoDB

 Supports transactions, row-level locking, and foreign keys

事務 ？  一次SQL操作從開始到結束的過程

事務回滾 ： 當操作沒有正確完成時，把數據恢復到操作之前的                    狀態。

事務日志文件

ib_logfile0

ib_logfile1

ibdata1

共享表空間    at.frm   表結構

                      at.ibd    表記錄+表索引

行級鎖

讀鎖 （ 共享鎖）

寫鎖  （互斥鎖 排它鎖

3 設置事務狀態

mysql> show  variables like "%commit%";

mysql> set autocommit=off；                  事務自動提交關閉

start  transaction;                          開啟事務

4 存儲引擎選擇

編輯操作多的表  使用行級鎖  (innodb)

查詢操作多的表  使用表級鎖  (myisam)

5 存儲引擎設置

修改表的存儲引擎

alter  table   表名  engine=存儲引擎；

修改數據庫服務默認使用的存儲引擎

service  mysql   stop

vim  /etc/my.cnf

[mysqld]

default-storage-engine=myisam

:wq

service  mysql  start

七：文件與數據庫的轉換（導入與導出）

1 文件到數據庫（導入），，，先有表結構

mysql>  LOAD   DATA    INFILE   "系統文件名"   

INTO   TABLE  表名     

FIELDS   TERMINATED   BY  "分隔符"  

 LINES TERMINATED BY  "\n";

例子

把當前系統用戶信息保存到數據庫 studb庫的user表里。

           /etc/passwd                       studb.user

create  table  studb.user(

name    varchar(25),

password  char(1),

uid  int(2),

gid  int(2),

comments   varchar(50),

homedir  varchar(50),

shell   varchar(20),

index(name)

);

load   data   infile  "/etc/passwd"  into   table   user  fields  terminated by ":"   lines   terminated by  "\n";

alter  table   user   add    id   int(2) primary key  auto_increment  first;

2 數據庫到文件（導出）

l庫下user表的所有記錄保存到系統/mydata目錄下user.txt文件里。

mkdir  /mydata

chown  mysql  /mydata

select  * from  mysql.user  into outfile  "/mydata/user.txt";

SELECT查詢命令   

INTO    OUTFILE   “文件名”  fields  terminated by "#"   lines   terminated by  "!!!"；

例子

select   name,uid,gid  from user  into  outfile  "user4.txt"  fields  terminated                                          by "#"  lines   terminated by  "!!!" ;

導出的內容由SQL查詢語句決定

若不指定路徑，默認會放在執行導出命令時所在庫對應的數據庫目錄下。

應確保mysql用戶對目標文件夾有寫權限。

八： 查詢條件

查詢表記錄時條件的表示方式

1、字符比較

相等   =

不相等 !=

select  id,name  from user where  name!="zzz";

select  name  from  user  where  shell="/sbin/nologin";

2、數值比較

相等  不相等  大于   大于等于    小于    小于等于

  =       !=      >          >=          <          <=

select  uid  from  user where  uid=500;

3  范圍內查找

字段名  in  (值列表)  在....里

字段名  not   in  (值列表)  不在....里

字段名   between  值1  and  值2          在...之間

select  name,uid  from user  where  name in  ("sync","plj","bob");

select  name,uid  from user  where  uid  in  (500,2000,65535);

select  name,uid  from user  where  uid  not  in  (500,2000,65535);

select  *  from   user  where   uid  between 500 and 510;

4  匹配空   

    is null

select  id,name  from user  where  name is null;

5  匹配非空    

    is not  null

select  id,name  from user  where  name is not null;

6 邏輯比較  （多個查詢條件時）

邏輯與   and    多個條件同時成立

邏輯或   or      多個條件有一個條件成立就可以

邏輯非   !         取反

select * from user  where  name in ("root","bin");

select  * from user where  name="root" or  uid=10 or  uid=20;

select  name,uid  from user where  name in ("root","bin") or   uid<=5;

select  name,uid  from user where  uid=2000 and  name="zzz";

7 DISTINCT  不顯示字段的重復的值

select   distinct   shell   from user;

8   查詢時可以做四則運算   +  -  *  /   %

     字段的類型必須是數值類型

alter  table user   add   age  tinyint(2)  unsigned  not null default  23  after name;

alter  table user   add   services  int(2)  not  null  default  "60",add  mysql   int(2) not null  default  60;

select name,2015-age  as  s_year  from user where name="root"

mysql> select name,mysql,services,mysql+services as zcj from user   where name="root";

select name,mysql,services,mysql+services as zcj , (mysql+services)/2  as  pjcj  from user   where name="root";

9  常用的統計函數avg()：字段的平均值

sum()：字段求和

min()：字段的最小值

max()：字段的最大值

count()：字段記錄的個數

select  avg(mysql) from user;

select  count(id) from user;

select  count(id),count(name) from user;

10  like  模糊查詢

where  字段名　　like   '表達式';

_  匹配任意一個字符  

% 匹配任意零個或多個字符

insert into  user(name)values("a");

insert into  user(name)values("");

select  id,name from user where name="";

select name  from  user  where name like  '___';

select name  from  user  where name like  '%';

select name  from  user  where name like  'a%';

having 條件  在查詢結果里過數據

select  * from user where uid<=10 having name="sync";

11  使用正則表達式做查詢條件

where   字段名   regexp  '正則表達式'；

^  $   .  [ ]

把名字包含數字用戶名全顯示出來

insert into user(name)values("jim3");

insert into user(name)values("ji7m");

insert into user(name)values("j0im");

insert into user(name)values("9jim");

insert into user(name)values("jim");

select  name from user where  name  regexp  '[0-9]';

select  name from user where  name like   '%jim%';

select  name from user where  name  regexp  '^a.*m$';

select  name from user where  name  regexp   '^a'   and name  regexp   'm$';

12 分組查詢

group  by    字段名;

select   sex   from  user   group  by    sex;

select  shell  from user   where  uid>=500   group by  shell;

13 給查詢結果排序      asc/   desc（倒序）

order by   字段名   排序的方式

select  name,uid from user  order by  uid;

select  name,uid from user where uid>=500 order by  uid desc;

14 limit 限制顯示查詢記錄的條目數  

limit  N,M

N  從第幾條記錄開始顯示

     第一條記錄的編號是零

M  共顯示幾條記錄

select  * from user  limit  0,1 ;

select * from user order by uid desc limit 1；

select * from user order by uid desc limit  2,5；

15 嵌套查詢

把內層查詢結果作為外層查詢的查詢條件

select  字段名列表  from 表名 where   條件   (sql查詢)；

select name,system  from user where system > (select avg(system)  from user);  

select  name  from user where  name = (select name from user2  where  uid =3);

select  name  from user where  name in (select name from user2  where  uid >=3);

16 多表查詢

select 字段名列表 from  表1，表2，表N  where 條件;

*

表1.字段名

表2.字段名

select  * from  atab,btab;  迪卡爾集

select  atab.name,btab.shell  from atab,btab where 條件;

select  atab.name from atab,btab where  atab.name = btab.name;

18 左連接查詢（以左表紀錄為主 顯示）

select 字段名列表 from   表a  LEFT JOIN  表b  ON  條件表達式；

右連接查詢（以右表紀錄為主 顯示）select 字段名列表 from   表a  RIGHT JOIN  表b  ON  條件表達式；

mysql> select atab.name,btab.shell from   atab right join btab on  atab.uid =btab.uid;

select * from   atab left join btab on  atab.uid!=btab.uid;

select name from atab where uid not in (select uid from btab);

九: 數據庫授權與撤銷

1 查詢授權

show grants;  連接數據庫服務器的用戶查看自己的權限信息

查看數據庫服務器上有哪些授權用戶？

select user,host  from mysq.user

2 權限列表

命令        權限

all    所有權限

usage      無權限

SELECT     查詢表記錄

INSERT     插入表記錄

UPDATE     更新表記錄

DELETE     刪除表記錄

CREATE     創建庫、表

DROP       刪除庫、表

RELOAD     有重新載入授權 必須擁有reload權限，才可以執行flush [tables | logs | privileges]

SHUTDOWN   允許關閉mysql服務 使用mysqladmin shutdown 來關閉mysql

PROCESS    允許查看用戶登錄數據庫服務器的進程 （  show  processlist;  ）

FILE       導入、導出數據

REFERENCES 創建外鍵

INDEX      創建索引

ALTER      修改表結構

SHOW DATABASES     查看庫

SUPER     關閉屬于任何用戶的線程

CREATE TEMPORARY TABLES       允許在create  table 語句中使用  TEMPORARY關鍵字

LOCK   TABLES                 允許使用  LOCK   TABLES  語句

EXECUTE  執行存在的Functions,Procedures

REPLICATION SLAVE    從主服務器讀取二進制日志

REPLICATION CLIENT   允許在主/從數據庫服務器上使用 show  status命令

CREATE VIEW  創建視圖

SHOW VIEW    查看視圖

CREATE ROUTINE 創建存儲過程

ALTER ROUTINE  修改存儲過程

CREATE USER    創建用戶

EVENT          有操作事件的權限

TRIGGER,  有操作觸發器的權限

CREATE TABLESPACE  有創建表空間的權限

3 客戶端地址的表示方式？

172.40.7.213    固定Ip地址

192.168.1.%      網段

%                         所有地址

pc10.tarena.com   主機名

%.tarena.com         區域

4 授權的信息存儲與庫和表

user   存儲授權用戶權限信息    *.*

db      庫的權限信息           庫名.*

tables_priv   表的權限信息    庫名.表名

columns_priv   字段的權限信息               update(name,sex)  

5用戶授權

用戶有授權權限要具備2個條件

1 有授權權限  with grant option

2  對授權庫要有寫入權限

3  給其他用戶授權時，權限不能大過自己本身的權限

grant   權限列表  on  數據庫名   to  用戶名@"客戶端地址"  identified by  "密碼"；

grant   權限列表  on  數據庫名   to  用戶名@"客戶端地址"  identified by  "密碼"  with grant option;   

with  grant  option            設置授權用戶有授權權限

管理員可重設授權用戶的密碼

SET PASSWORD

FOR 用戶名@'客戶端地址'=PASSWORD('新密碼');

6 權限撤銷

* 有過授權才可以撤銷權限。

* revoke 撤銷的是權限

revoke  權限列表  on 數據庫名  from 用戶名@"客戶端地址"                    

grant  all   on  *.*   to   root@"172.40.7.42"  identified  by   "123456"  with grant option;

revoke  grant  option on  *.* from  root@"172.40.7.42";

revoke  delete  on  *.*  from  root@"172.40.7.42";

revoke all  on  studb.*  from  root@"172.40.7.42";