mysql> desc t5;

+-------+---------+------+-----+---------+----------------+

| Field | Type    | Null | Key | Default | Extra          |

+-------+---------+------+-----+---------+----------------+

| nid   | int(11) | NO   | PRI | NULL    | auto_increment |

| pid   | int(11) | NO   | PRI | NULL    |                |

| num   | int(11) | YES  |     | NULL    |                |

+-------+---------+------+-----+---------+----------------+

mysql> alter table t6 AUTO_INCREMENT=2;

mysql> show create table t6 \G;

*************************** 1. row *********************

       Table: t6

Create Table: CREATE TABLE `t6` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `num` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

mysql> select * from t6;

+----+------+

| id | num  |

+----+------+

| 20 |   15 |

+----+------+

对于自增：

        desc t6;

        show create table t6 \G;

         alter table t6 AUTO_INCREMENT=2;

Mysql :自增步长

    基于会话级别：

查看全局变量

mysql> show session variables like 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 1     |

| auto_increment_offset    | 1     |

+--------------------------+-------+

设置会话步长

mysql> set session auto_increment_increment=2;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t6(num) values(66);

Query OK, 1 row affected (0.44 sec)

mysql> select * from t6;

+----+------+

| id | num  |

+----+------+

| 20 |   15 |

| 21 |   88 |

| 23 |   66 |

+----+------+

起始值

mysql> set session auto_increment_offset=2;

基于全局级别步长：

mysql> set global auto_increment_increment=200;

mysql> show global variables like 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 200   |

| auto_increment_offset    | 1     |

+--------------------------+-------+

mysql> show session variables like 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 200   |

| auto_increment_offset    | 1     |

+--------------------------+-------+

起始值

mysql> set global auto_increment_offset=22;

一对一

create table userinfo1(

        id int auto_increment primary key,

        name char(10),

        gender char(10),

        email varchar(64)

)engine=innodb default charset=utf8;

create table admin(

    id int not null auto_increment primary key,

    username varchar(64) not null,

    password VARCHAR(64) not null,

    user_id int not null,

    unique uq_u1 (user_id),

    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)

)engine=innodb default charset=utf8;

多对多

create table userinfo2(

        id int auto_increment primary key,

        name char(10),

        gender char(10),

        email varchar(64)

)engine=innodb default charset=utf8;

create table host(

    id int auto_increment primary key,

    hostname char(64)

)engine=innodb default charset=utf8;

create table user2host(

    id int auto_increment primary key,

    userid int not null,

    hostid int not null,

    unique uq_user_host (userid,hostid),

    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),

    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)

)engine=innodb default charset=utf8;

MySQL语句数据行操作的补充

增：

    insert into tb11(name,age) values('alex',12);

    insert into tb11(name,age) values('tim',12),('root',11);

    insert into tb12(name,age) select name,age from tb11;

create table tb12(

            id int auto_increment primary key,

            name varchar(32),

            age int

        )engine=innodb default charset=utf8;

删：

    delete from tb12;

    delete from tb12 where id=2;

    delete from tb12 where id=2 and name ='tim',

改：

    update tb12 set name='tim' where id >12 and name = 'xx'

查：

  select * from tb12;

  select id,name from tb12;

  select id,name from tb12 where id >10 or name ='xxx';

  select name,age  as cname from tb12;

  select name,age,111 from tb12;

  select * from tb12 where id not in (1,4,5);

  select * from tb12 where id between 1 and 201;

  select * from tb12 where id in (select id from tb11)

通配符：

    select * from tb12 where name like '%a'

    select * from tb12 where name like 'a_'

分页:

    select * from tb12 limit 2;

    select * from tb12 limit 1,3;

    select * from tb12 limit 2 offset 20;

排序：

    select * from tb12 order by id desc;  大到小

    select * from tb12 order by id asc;     小到大

    select * from tb12 order by id asc limit 2;

    select * from tb12 order by age desc,id desc;

# select mac(id),part_id from uerinfo5 group by part_id;

# count

# max

# min

# sum

# avg

# 对于聚合函数的结果进行二次筛选，必须用having

# select count(id),part_id from userinfor5  group by part_id where haveing count(id) >1;

# select count(id),part_id from userinfo5 where id >0 group by part_id haveing count(id) >1;

连表

select * from userinfo5 where userinfo5.part_id = department5.id;

select * from userinfo5 left join department5 on userinfo.part_id = department5.id;

userinfo5 左边全部显示

select * from userinfo5 right join department5 on userinfo.part_id = department5.id;

department5右边全部显示

select * from userinfo5 innder join department5 on userinfo.part_id = department5.id;

将出现null时一行隐藏