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存储引擎