DAY02

一、修改表结构

修改表结构会影响表中已有记录，表结构的修改受表中已有记录的限制。因此要在插入表记录之前修改表结构，若已有记录，在修改前先备份。

默认只有数据库管理员root从数据库服务器本机登录时，才有修改表结构的权限 

1、alter table  数据库名.表名  处理动作;   //修改表结构

处理动作：

1.1添加新字段： add

格式：alter  table  数据库名.表名 add 字段名…

例：

mysql> alter table  数据库名.表名

mysql> add 字段名  字段类型(宽度)  约束条件 after 字段名,

mysql> add 字段名  字段类型(宽度)  约束条件 first，

mysql> add 字段名  字段类型(宽度)  约束条件;

first   //把新添加的字段添加在所有字段的上方

after字段名  //把新字段添加在指定字段名的下方

        默认新添加的字段追加在已有字段末尾，first把新添加的字段添加在所有字段的上方，’after字段名’ 把新字段添加在指定字段名的下方

     1.2删除字段: drop   

格式：alter  table  数据库名.表名 drop  字段名；

例：

mysql> alter  table  数据库名.表名

mysql>drop字段名,

mysql>drop字段名；

      1.3修改字段类型:  modify   

格式：alter  table  数据库名.表名  modify  字段名;

例：

mysql> alter  table  数据库名.表名

mysql> modify  字段名  新字段类型(宽度)  约束条件,

mysql>modify 字段名  新字段类型(宽度)  约束条件,

mysql>modify 字段名  新字段类型(宽度)  约束条件;

修改时，修改部分写上新设置，不变的原样写上。

1.4修改字段名: change  （既能该名字，又能该类型）

格式：alter  table  数据库名.表名 change 原字段名 新字段名

例：

mysql> alter  table  数据库名.表名

mysql>change原字段名 新字段名字段类型(宽度) 约束条件,

mysql> change 原字段名 新字段名 字段类型(宽度) 约束条件,

mysql> change 原字段名 新字段名 字段类型(宽度) 约束条件；

change 即能改字段名 也能改约束条件 ，如果只改字段名，后面字段类型，约束条件原样落下。

2、alter  table  原表名  rename  to  新表名;   //修改表名

3、create  table  新表名  SQL查询语句;    //复制表  

复制表时，原表中的Key的属性不会复制给新表

例：create table newuserselect * from mysql.user;

4、create table 新表名 没有查询结果的SQL语句; //复制原表的表结构

   SQL查询语句结果为空时，复制表结构

   例：create table newuser1select * from mysql.user where host=’12’;

delete  from  表名; //删除所有表记录

drop table 表名;  //删除表

update 表名 set 字段名= where 条件  //更新表记录

例：update yg_tab set yg_id=8 where yg_id=2;

mysql>update mysql.user set host=‘192.168.1.1‘ wherehost=‘192.168.1.10‘;

二、mysql索引类型

1、什么是索引

相当于书的目录，表结构中的Key字段

不同的索引有不同作用，功能，使用规则也不同。

2、索引的优缺点

    优点：加快查询表记录速度

缺点：减慢对表记录的写速度（insertupdate delete）、占物理磁盘空间。

默认情况下用.MYI文件存储表索引信息 .MYD存储表记录 .frm存储表结构

3、怎么查看表里是否有索引字段

   查看表结构，desc 表名,再看 key字段是否有值，不同的值代表不同索引类型。

4、怎么看表里的索引信息

    show index from 数据库名.表名   //查看表的索引字段详细信息

# Show create table t9 //查看t9表的建表过程

索引名key_name

索引字段名Column_name

索 引 算法Index_type  (BTREE 、B+TREE、hash)

默认使用的算法是BTREE算法

BTREE算法：二叉树算法

5、mysql支持哪些索引 ，使用说明

      不同的索引类型有不同的功能和约束方式

      使用索引时要满足索引的使用规则

什么是索引

索引相当于一本书的目录

优点：加快查询速度

缺点 占用空间，减慢更新速度

●普通索引index :一个表中可以把多个字段设置为index字段，

index字段值可以重复，把经常做查询条件的字段设置为index字段，index字段的KEY标志是MUL。

创建index字段

1. 1.   把已有表中的某个字段设置为index字段

Create            index         name                 on                       stu （name）；

                             索引名称（通常和字段重名）         表名（字段名）；         

Show index from 表名：   \G 换行显示

查看一个表的索引详细信息，默认使用BTREE算法

Drop index 索引名 on 表名

删除索引字段index 属性

      BTREE算法又称二叉树算法

Update 表名   set 字段名=值；

Update tea3 settea_id=1；

更新表中对应字段名称为值

1. 2.   建表时指定索引字段

Create table  表名（字段名 字段类型（宽度） 条件，index（索引字段，索引字段）

默认第一个作为索引名。

mysql> create table tea3(

    -> name char(3) notnull,

    -> age int(3),

    -> index(name),

    -> index(age));

二叉树深度= 分开后的高度

●主    键primary key：一个表中只可以有一个主键字段且主键字段的值不可以重复。标志为PRI

当把一个表中的多个字段设置为primary key字段时，叫复合主键，复合主键要在建表之初一起创建。 向表中的复合主键"字段"插值时，只要复合主键"字段的值"不同时重复就可以。

primarykey 通常与auto_increment（自动增长） 连用，作用是让字段值自动增长。每添加一条记录，字段值自动加 +1， 是从当前记录最大值开始

通常把表中查询时能够唯一定位一条记录的字段设置为primary key 字段。

加auto_increment属性字段类型要是整型数值类型 只有primary key 才可以使用auto_increment属性。

如果不是主键不能设置auto_increment。(auto_increment 自动增长，字段类型是整数型)

通常把编号字段作为主键，自动增长的初始值是零

让字段的值自动增长要满足如下条件”

1. 1.  字段类型必须是整形数值类型

2. 2.  必须是主键字段  (仅主键可设置自动增长,主键可不设置自动增长

3. 3.  Createtable tea5(

Id int(3)primary key,

Namechar(3),

Age int(3)

);

复合主键  复合主键要在建表之初一起创建

●外    键foreign key：外键就是表与表之间做关联，让当前表里字段的值受另一表里字段值限制

外键的使用规则？建外键条件：（1）表的存储引擎要是innodb存储引擎

（2）外键字段的类型要一致

（3）被参考字段要有明确的索引(index，primary key)7，

也就是必须要建索引。

具有外键属性的字段不能直接删除，表可以删除。子表中一个字段可以参考不同父表中的字段（插入

的数据必须在两个父表中都存在）。子表中的不同字段也可以参考不同父表中的字段（插入的数据必

须在两个父表中分别存在）。若要删除父表，需先删除子表的外键约束。

建表：

Createtable tab_name(字段)engine=innodfb;   // 建表设置存储引擎

创建外键： 在下方

      全文索引 fulltext：

唯一索引unique：

使用规则：

一个表中可以有多个unique字段，unique字段值不可以重复，unique字段key标志是UNI，unique字段值允许为NULL，当修改为不允许为NULL时，则字段限制与主键相同。

           设置唯一索引

create  uniqueindex 索引名 on 表名(索引字段)

create  unique index tea_id on tea3(tea_id)

建表时建立

Create table tea4（

Tea_id int（3）not null,

Name char（3）not null,

Age   int（3） not null,

Sex   enum（“m“,”w”）,

Index (name),

Unique(tea_id));

6、如何在表里设置索引字段

（1）建表时创建索引字段

1）设置index字段、unique字段

create table stu(

stu_id  varchar(10),

name varchar(10) not null ,

age  tinyint(3)unsigned,

sex  enum(“boy”,”girl”),

index (name,sex), //默认把第一个作为索引名，这是需要用show index from 数据库名.表名  显示详细索引信息。

unique(stu_id)

);

         2）设置主键

或

创建复合主键  （只能在建表之初创建）

create table t2 (

ip varchar(15),

port varchar(5),

sername varchar(10),

status enum(‘deny’,’allow’) default ‘deny’,

primary key(ip,port)

);

mysql> create table ser (

    -> c_ip char(15),

    -> ser_nvarchar(15),

    -> portchar(5),

    -> statusenum("allow","deny") default "deny",

    -> primarykey(c_ip,port)

    -> );

3）创建外键

Createtable tab_name(字段)engine=innodfb;   // 建表设置存储引擎

foreign key (当前表里字段名) references 表名(表名里的字段名)

on updatecascade   //同步更新  on delete cascade    //同步删除  这两个之间用空格隔开。

Create  tablegz_tab(

Gz_id int(3) not null ,

Show create  table  表名

删除外键

Alter table  表名drop foreign key 外键名

delete from 表名； // 删除表中所有记录

      where 字段名=值；

在已有表中添加索引字段

1）添加index字段

create index 索引名 on 表名(字段名列表);  //通常索引名与字段名同名

例：create index name on stu(name);

    create index sexon stu(sex);

2）添加unique字段   //若表记录中unique字段值不唯一，则不成功

createunique index 索引名 on 表名(字段名列表);

例：create unique index stu_id on stu(stu_id);

3）添加主键  //若表记录中主键字段值有NULL，则不成功

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

例：delete from t1 where id is null;  //删除t1表中id值为空的记录

alter table t1 addprimary key(id);

alter table t1 modify id int(2) not null auto_increment;//主键通常与auto_increment 连用

         4）添加外键

          alter table 子表名 add foreign key(字段名) references 父表名(字段名);//当子表字段下有值并不在父表中时，添加不成功

7、如何删除表中字段的索引属性

    1）删除index索引、unique索引

drop index 索引名 on 表名;

例：drop  index  name  on stu;

drop  index  stu_id on  stu;

     2）删除主键  若有auto_increment,需先删除自动增长

     alter table t1 modify id int(2) notnull;   修改id字段的约束条件

     alter table t1 drop primary key;   //删除主键 

     3）删除外键

     alter table t1 drop foreign key  外键名;  //通过show create table表名查看外键名

     例：altertable t1 drop foreign key  t_ibfk_1;

三、mysql存储引擎

     1） MyISAM特点：支持表级锁定，不支持行级锁定，不支持事务，独享表空间,多用在查询、插入较多的表。

         相关的表： XXX.frm 存放的表结构  XXX.MYI  索引信息   XXX.MYD表中的数据 记录

2）innodb支持事务和事务回滚， 支持表级/行级锁定 共享表表空间

事务= 一次sql操作从开始到结束的过程

xxx.frm 存储表结构

xxx.ibd 存储数据

索引和记录用同一个文件存储/var/lib/mysql/ibdata1,ibdata1在服务初始化时生成，10M，用完后，服务器自动增大）

事务：一次SQL操作从开始执行到正确执行结束的过程

         事务回滚：ib_logfile0  ib_logfile1

         set autocommit=0;  // 关闭自动提交功能

start transaction; //开启事务

create table 30(id int) engine=innodb;

insert into t30 values(200);

select * from t30;

在另一终端查看（事务未提交，查看不到）

rollback;   //回滚

insert into t30 values(100);

commit;//提交，事务结束，永久生效

      3）myisam支持表锁，innodb支持行锁

         锁机制：解决用户的并发访问冲突问题

锁粒度：表锁-并发访问量低 系统开销小

                 行锁-并发访问量高  系统开销大

                 页级锁：对整个页面（MySQL管理数据的基本存储单位）进行加锁                               

         锁类型：写锁（delete update insert）互斥锁、排它锁特点：独占锁，上锁期间其他线程不能读表或写表

                 读锁（select desc show）共享锁特点：支持并发读，写操作被锁

幻读（两次读出的数据不同）与脏读（从内存读出的数据）

                 死锁：锁超时

          写锁优先级高于读锁*

获取写锁时，其他操作均需等待写锁释放后执行。

查看当前的锁状态

  查看table_lock开头的变量，% 作通配符

show status like ‘ table_lock% ’;

表结构-索引