MySQL數(shù)據(jù)庫管理2

te  database   db102; create  table db102.t1(name 

char(6),age  int(3));

insert  into  db102.t1  values("jerry",91024);

create  table db102.t2(id  int(2)  zerofill,name char(6));

insert into  t2  values(3,"tom"),(7,"jim"),(9,"lucy"),

(12,"bob"),(191,"alic");

select  * from t2;

可以使用2位數(shù)字給year類型的字段賦值：

01~69   20XX

70~99   19XX

00          0000

create table   t3 (name char(10),s_year  year);

insert  into   t3  values("bob",01),("jim",69),("lucy",70);

select  * from  t3;

日期時(shí)間類型 datetime  和  timestamp 區(qū)別？

create  table   t4   (

meetting  datetime,

reg_t   timestamp

);

insert into  t4  values

(20170523093900,20170523093900);

insert into  t4(meetting)  values(20170529093058);

insert into  t4(reg_t)  values(20190529094058);

select  * from t4;

+++++++++++++++++++++++++

day02

mysql 索引

1  什么索引？（建在表中的字段上）

相當(dāng)于 "書的目錄"

5000頁

目錄  1~100    正文  101 ~5000

筆畫    9  2000------2010

部首

拼音

刪除  添加  修改

stuinfo                      數(shù)庫目錄 /stuinfo.frm     .ibd

name   age    sex  class

jim

jerry

abob

lili

han×××

select  * from  stuinfo where class="1702";

2 索引的優(yōu)點(diǎn)與缺點(diǎn)？

優(yōu)點(diǎn)：加快查詢的速度

缺點(diǎn)： 占用物理存儲空間，會減慢對表寫操作的速度。

++++++++++++++++++++++++++++++++++

mysql索引類型：

INDEX：普通索引*

UNIQUE：唯一索引

FULLTEXT：全文索引

PRIMARY KEY：主鍵 *

FOREIGN KEY：外鍵*

使用INDEX：普通索引

索引的使用規(guī)則：

一個(gè)表中可以有多個(gè)INDEX字段

字段的值允許有重復(fù)，切可以賦NULL值

經(jīng)常把做查詢條件的字段設(shè)置為INDEX字段

INDEX字段的KEY標(biāo)志是MUL

創(chuàng)建index索引

建表是創(chuàng)建

create  table   t5(

name  char(10),

age  int(2),

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

index(name),

index(sex)

);

把已有的字段設(shè)置為index字段

mysql> create  index  索引名  on  表名(字段名);

查看

desc  表名;   Key

show  index  from 表名; 

Table: t1

Key_name: aaa

Column_name: name

Index_type: BTREE      （B+TREE   HASH）

                     二叉樹

                     1~10

              1-5        6-10

刪除

drop  index   索引名 on 表名；

++++++++++++++++++++++++

使用PRIMARY KEY：主鍵 *

使用規(guī)則？

一個(gè)表中只能有一個(gè)primary  key字段

對應(yīng)的字段值不允許有重復(fù)，且不允許賦NULL值

主鍵字段的KEY標(biāo)志是PRI

如果有多個(gè)字段都作為PRIMARY KEY，稱為復(fù)合主鍵，必須一

起創(chuàng)建。

和auto_increment一起使用讓字段的值自動增長

經(jīng)常把表中能夠唯一標(biāo)識記錄的字段設(shè)置為主鍵字段[記錄編號

字段]

stu_id    name    age 

   1          bob      19

   2         lucy       18

   3         alic        21

   4         jerry      19                                                             

create  table   t9(

stu_id  int(2) primary key  auto_increment,

name  char(10),

age   tinyint(2)  unsigned

);

insert into   t9(name,age)values("bob",21);

insert into   t9(name,age)values("lucy",21);

insert into   t9(name,age)values("lili",21);

select  * from t9;

create  table   t6(

name  char(10),

age  int(2),

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

primary key(name)

);

create  table   t7(

name  char(10)  primary key,

age  int(2),

sex  enum("boy","girl")

);

把表中已有字段設(shè)置為主鍵

alter  table   表名  add  primary key ( 字段名)；

復(fù)合主鍵：

只有做符合主鍵的多個(gè)字段的值不同時(shí)重復(fù)就可以

PRI           PRI

cip            port    status

 1.1.1.1     21        deny

1.1.1.1     25        allow

 2.1.1.1     25        deny

create  table  t8(

cip  varchar(15),

port  smallint(2),

status  enum("allow","deny"),

primary key (cip,port)

);

insert into  t8

values

("1.1.1.1",21,"deny"),

("1.1.1.1",25,"allow"),

("2.2.3.2",25,"deny");

刪除主鍵

alter  table   表名  drop   primary key ;

+++++++++++++++++++++++++++++++

unique 唯一索引：

一個(gè)表中可以有多個(gè)UNIQUE字段

對應(yīng)的字段值不允許有重復(fù)

UNIQUE字段的KEY標(biāo)志是UNI

UNIQUE字段的值允許為NULL，當(dāng)將其修改為不允許為NULL

，則此字段限制與主鍵相同

create  table   t10(

name  char(10),

shf_id   varchar(18),

ks_num   char(8),

age  int(2),

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

index(name),

unique(shf_id),

unique(ks_num)

);

create unique index  索引名 on  表 (字段名);

drop  index    索引名 on  表;

mysql> drop index  shf_id on t10;

+++++++++++++++++++++++++++++

FOREIGN KEY：外鍵

功能：給當(dāng)前表的字段賦值時(shí)，字段的值，只能在另一個(gè)表的字

段值里選擇。

使用規(guī)則？

表的存儲引擎必須是innodb

字段類型必須匹配

被參考字段的必須是索引的一種（通常是primary key ）

財(cái)務(wù)表cwb

create table cwb(

cwb_id  int(2) primary  key  auto_increment,

name    varchar(15),

pay   float(7,2)

)engine=innodb;

create  table  bjb(

bjb_id int(2),

name varchar(15),

age  tinyint(2),

foreign key(bjb_id)  references  cwb(cwb_id)  on update  

cascade  on delete cascade

)engine=innodb;

desc  bjb;

show create table bjb;

 insert into  cwb(name,pay) values("bob",20000),

("lucy",20000);

insert into  bjb   values(1,"alic",23);

update  cwb  set  cwb_id=8  where  cwb_id=2;

delete from cwb where cwb_id=3;

刪除外鍵

show create  table 表名；#查看建表命令

alter  table  表 drop    foreign  key   外鍵名；

在已有表里添加外鍵。

alter  table  表 add  foreign key(字段名)  references  表名(

字段名)  on update  cascade   on delete cascade；

alter  table   bjb  add   foreign  key(bjb_id)  references  

cwb(cwb_id)  on update  cascade   on delete cascade;

++++++++++++++++++++++++++++++++

二、mysql存儲引擎

mysql服務(wù)的工作過程：

連接池

sql接口

分析器

優(yōu)化器    select   insert  update  delete

查詢緩存

存儲引擎

文件系統(tǒng)： 硬盤 （/var/lib/mysql）

管理工具:安裝服務(wù)軟件包時(shí)，自帶的命令

1 什么存儲引擎？

是mysql數(shù)據(jù)庫服務(wù)軟件自帶程序 ，是表的處理器，不同的處理

器有不同的功能和數(shù)據(jù)存儲方式。

2 查看存儲引擎？

查看表使用的存儲引擎   show  create  table  表名；

查看數(shù)據(jù)庫服務(wù)默認(rèn)使用的存儲引擎show engines;

 InnoDB              DEFAULT 

3設(shè)置存儲引擎

設(shè)置表使用的存儲引擎

create  table  表名(字段名列表)engine=存儲引擎;

設(shè)置數(shù)據(jù)庫服務(wù)默認(rèn)使用的存儲引擎

vim /etc/my.cnf

[mysqld]

validate_password_policy=0

validate_password_length=6

default-storage-engine=myisam

:wq

# systemctl stop mysqld

# systemctl start mysqld

#mysql  -uroot  -p123456

mysql> show engines;

4  常用存儲引擎的特點(diǎn)

myisam

表級鎖

不支持事務(wù)  和 事務(wù)回滾  

外鍵

表.frm  表結(jié)構(gòu) 

表.MYD  表記錄 select  * from  表；

表.MYI   表索引  

innodb

行級鎖

支持事務(wù)  和 事務(wù)回滾

支持外鍵

表.frm  表結(jié)構(gòu)

表.ibd  表記錄+表索引

什么事務(wù)？對數(shù)據(jù)庫做訪問時(shí)，從開始連接到斷開連接 過程稱作

事務(wù)。

插卡  提示輸入密碼  -->登錄成功

轉(zhuǎn)賬    接收卡號   

           金額         5w

                                確認(rèn)  

                               轉(zhuǎn)賬中......     成功

退卡

事務(wù)回滾？ 事務(wù)執(zhí)行過程，任意一步執(zhí)行失敗，還原之前所有的

操作。

事務(wù)日志文件

/var/lib/mysql/

ib_logfile0

ib_logfile1  

ibdata1

鎖： 解決并發(fā)訪問沖突問題

讀鎖                   myisam

select    *  from  t1  where  id>=10;

                          innodb

寫鎖

update

insert

delete

                    innodb

pc1   update  t1  set  name="tom"  where  name="jim";

pc2   update  t1  set  name="lucy"  where  name="jim";

行級鎖： 客戶端訪問數(shù)據(jù)庫時(shí)，只給sql命令操作的行加鎖。

表級鎖：客戶端訪問數(shù)據(jù)庫時(shí)，給sql命令操作的表加鎖

+++++++++++++++++++

5 工作中建表時(shí)，如何決定表使用的存儲引擎

寫操作多的表適合使用innodb存儲引擎

查詢操作多的表適合使用myisam存儲引擎