MySQL數據庫管理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位數字給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;

日期時間類型 datetime  和  timestamp 區別？

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  什么索引？（建在表中的字段上）

相當于 "書的目錄"

5000頁

目錄  1~100    正文  101 ~5000

筆畫    9  2000------2010

部首

拼音

刪除  添加  修改

stuinfo                      數庫目錄 /stuinfo.frm     .ibd

name   age    sex  class

jim

jerry

abob

lili

han×××

select  * from  stuinfo where class="1702";

2 索引的優點與缺點？

優點：加快查詢的速度

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

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

mysql索引類型：

INDEX：普通索引*

UNIQUE：唯一索引

FULLTEXT：全文索引

PRIMARY KEY：主鍵 *

FOREIGN KEY：外鍵*

使用INDEX：普通索引

索引的使用規則：

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

字段的值允許有重復，切可以賦NULL值

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

INDEX字段的KEY標志是MUL

創建index索引

建表是創建

create  table   t5(

name  char(10),

age  int(2),

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

index(name),

index(sex)

);

把已有的字段設置為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：主鍵 *

使用規則？

一個表中只能有一個primary  key字段

對應的字段值不允許有重復，且不允許賦NULL值

主鍵字段的KEY標志是PRI

如果有多個字段都作為PRIMARY KEY，稱為復合主鍵，必須一

起創建。

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

經常把表中能夠唯一標識記錄的字段設置為主鍵字段[記錄編號

字段]

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

);

把表中已有字段設置為主鍵

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

復合主鍵：

只有做符合主鍵的多個字段的值不同時重復就可以

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 唯一索引：

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

對應的字段值不允許有重復

UNIQUE字段的KEY標志是UNI

UNIQUE字段的值允許為NULL，當將其修改為不允許為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：外鍵

功能：給當前表的字段賦值時，字段的值，只能在另一個表的字

段值里選擇。

使用規則？

表的存儲引擎必須是innodb

字段類型必須匹配

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

財務表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服務的工作過程：

連接池

sql接口

分析器

優化器    select   insert  update  delete

查詢緩存

存儲引擎

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

管理工具:安裝服務軟件包時，自帶的命令

1 什么存儲引擎？

是mysql數據庫服務軟件自帶程序 ，是表的處理器，不同的處理

器有不同的功能和數據存儲方式。

2 查看存儲引擎？

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

查看數據庫服務默認使用的存儲引擎show engines;

 InnoDB              DEFAULT 

3設置存儲引擎

設置表使用的存儲引擎

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

設置數據庫服務默認使用的存儲引擎

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  常用存儲引擎的特點

myisam

表級鎖

不支持事務  和 事務回滾  

外鍵

表.frm  表結構 

表.MYD  表記錄 select  * from  表；

表.MYI   表索引  

innodb

行級鎖

支持事務  和 事務回滾

支持外鍵

表.frm  表結構

表.ibd  表記錄+表索引

什么事務？對數據庫做訪問時，從開始連接到斷開連接 過程稱作

事務。

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

轉賬    接收卡號   

           金額         5w

                                確認  

                               轉賬中......     成功

退卡

事務回滾？ 事務執行過程，任意一步執行失敗，還原之前所有的

操作。

事務日志文件

/var/lib/mysql/

ib_logfile0

ib_logfile1  

ibdata1

鎖： 解決并發訪問沖突問題

讀鎖                   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";

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

表級鎖：客戶端訪問數據庫時，給sql命令操作的表加鎖

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

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

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

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