Mysql专场

Mysql事务

use school;  # 库
create table db_school(id int(5)) engine=innodb;  #创建数据表
select * from db_school;  # id :0
begin  #开始第一次事务
insert into db_school value(6);
insert into db_school value(8);
commit  #提交事务
select * from db_school;  # id :5,6

begin;  #开始第二次事务
insert into db_school value(7);
rollback;  #回滚
select * from db_school;  # id ：5,6 因为回滚所以数据没有插入

 

mysql索引

普通索引
    显示索引信息
        show index from table_name;\G
    创建索引
        create index indexname on mytable(username(length));
        
    给表添加索引
        alter table tablename add index indexname(columName)
        
    创建表时直接指定索引
        create table mytable(
        id int not null,
        username varchar(16) not null,
        index [indexname] (username(length))
        )
    删除索引
        drop index [indexname] on mytable;
   
 唯一索引
    创建索引
        create union index indexname on mytable(username(length)
        
    给表加索引
        alter table mytable add union [indexname] (username(length))

    创建表时直接指定索引
        create table mytable(
        id int not null,
        username varchar(16) not null,
        union [indexname] (username(length))
        );

mysql 复制表

show create table tb1 \G;  # 显示数据表的sql创建语句
# 结果
Table: tbl
Create Table: CREATE TABLE `tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY  (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB 

create table `clone_tb1`(   #修改sql语句的数据表名，执行sql
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY  (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB 

# 表结构拷贝完成，如果你想拷贝数据，执行以下
insert into clone_tb1(
runoob_id,
runoob_author,
submission_date)
select runoob_id,runoob_author,submission_date
from tb1;

# 执行完上边的所有，你就可以完全复制了一个表，包含数据