您的位置:68399皇家赌场 > 虚拟主机 > 深切mysql外键关联难点的详解

深切mysql外键关联难点的详解

发布时间:2019-06-29 14:58编辑:虚拟主机浏览(174)

    自定义主键步长方法 CREATE TABLE IF NOT EXISTS `useradmin` ( `id` int(4) NOT NULL auto_increment, `username` varchar(20) default NULL, `userpass` varchar(40) de...

          mysql> alter table student2 drop index index_name;
          Query OK, 0 rows affected (2.08 sec)
          Records: 0 Duplicates: 0 Warnings: 0

          mysql> show create table student2G;
          *************************** 1. row ***************************
          Table: student2
          Create Table: CREATE TABLE `student2` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` char(20) NOT NULL,
          `age` tinyint(2) NOT NULL DEFAULT '0',
          `dept` varchar(16) DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `index_name` (`name`)
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8
          1 row in set (0.00 sec)

    在豪门(老师和网民)的拉拉扯扯下终于化解了,做法先drop掉表里的外键,然后在add。呵呵……

    SET @auto_increment_increment=10;
    o                       Query OK, 0 rows affected (0.00 sec)
    o                     
    o                       mysql> SHOW VARIABLES LIKE 'auto_inc%';
    o                       -------------------------- -------
    o                       | Variable_name            | Value |
    o                       -------------------------- -------
    o                       | auto_increment_increment | 10    |
    o                       | auto_increment_澳门皇家赌场55533网址,offset    | 1     |
    o                       -------------------------- -------
    o                       2 rows in set (0.01 sec)
    o                     
    o                       mysql> INSERT INTO useradmin VALUES ('www.bKjia.c0m'), (NULL), (NULL), (NULL);
    o                       Query OK, 4 rows affected (0.00 sec)
    o                       Records: 4  Duplicates: 0  Warnings: 0
    o                     
    o                       mysql> SELECT col FROM autoinc1;
    o                       -----
    o                       | col |
    o                       -----
    o                       |   1 |
    o                       |  11 |
    o                       |  21 |
    o                       |  31 |
    o                       -----
    o                       4 rows in set (0.00 sec)

          mysql> alter table student2 add index index_name(name);
          Query OK, 0 rows affected (0.01 sec)
          Records: 0 Duplicates: 0 Warnings: 0

        删除后:

    复制代码 代码如下:

    mysql教程 自定义主键步长方法
    CREATE TABLE IF NOT EXISTS `useradmin` (
      `id` int(4) NOT NULL auto_increment,
      `username` varchar(20) default NULL,
      `userpass` varchar(40) default NULL,
      `logins` int(4) NOT NULL default '0' COMMENT '登录次数',
      `logintime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `mid` char(1) NOT NULL default '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

        修改后:    

      数据库索引就象书的目录相同,倘使在字段上确立了目录,那么以索引列为查询条件时得以加速查询数据的快慢。查询数据库,按主键查询是最快的,每一种表只可以有二个主键列,不过足以有多个普通索引列,主键列需要列的享有内容必须唯一,而普通索引列不须要内容必须唯一。主键就临近大家在母校读书时的学好一样,班级里是独占鳌头的,整个表的每一条记下的主键值在表内都以唯一的,用来唯一标志一条记下

    复制代码 代码如下:

          mysql> show create table student2G;
          *************************** 1. row ***************************
          Table: student2
          Create Table: CREATE TABLE `student2` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` char(20) NOT NULL,
          `age` tinyint(2) NOT NULL DEFAULT '0',
          `dept` varchar(16) DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `index_name` (`name`)
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8
          1 row in set (0.00 sec)

          KEY index_name(name) <-name字段普通索引

    mysql> create table icity(id int not null, city varchar(20), country_id smallint unsigned not null , primary key(id), foreign key(country_id) references country(country_id) on update cascade )engine=innodb;
    Query OK, 0 rows affected (0.11 sec)
    mysql> show create table icityG
    *************************** 1. row ***************************
           Table: icity
    Create Table: CREATE TABLE `icity` (
      `id` int(11) NOT NULL,
      `city` varchar(20) DEFAULT NULL,
      `country_id` smallint(5) unsigned NOT NULL,
      PRIMARY KEY (`id`),
      KEY `country_id` (`country_id`),
      CONSTRAINT `icity_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.02 sec)

          primary key(id)  <-主键

        

    mysql> alter table city drop FOREIGN KEY `city_ibfk_1`;
    Query OK, 0 rows affected (0.24 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> alter table city add FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE;Query OK, 0 rows affected (0.16 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql> show create table cityG
    *************************** 1. row ***************************
           Table: city
    Create Table: CREATE TABLE `city` (
      `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `city` varchar(50) NOT NULL,
      `country_id` smallint(5) unsigned NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`city_id`),
      KEY `country_id` (`country_id`),
      KEY `idx_fk_country_id` (`country_id`),
      CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

          ERROR:
          No query specified   

          唯有int类型且为primary key才足以动用auto_increment

    mysql> show create table countryG
    *************************** 1. row ***************************
           Table: country
    Create Table: CREATE TABLE `country` (
      `country_id` smallint(5) unsigned NOT NULL auto_increment,
      `country` varchar(50) NOT NULL,
      `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY  (`country_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.01 sec)
    mysql> show create table cityG
    *************************** 1. row ***************************
           Table: city
    Create Table: CREATE TABLE `city` (
      `city_id` smallint(5) unsigned NOT NULL auto_increment,
      `city` varchar(50) NOT NULL,
      `country_id` smallint(5) unsigned NOT NULL,
      `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY  (`city_id`),
      KEY `country_id` (`country_id`),
      CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    mysql> select * from city;
    --------- ---------- ------------ ---------------------
    | city_id | city     | country_id | last_update         |
    --------- ---------- ------------ ---------------------
    |       1 | hancheng |          1 | 2012-01-09 09:18:33 |
    --------- ---------- ------------ ---------------------
    1 row in set (0.01 sec)
    mysql> select * from country;
    ------------ --------- ---------------------
    | country_id | country | last_update         |
    ------------ --------- ---------------------
    |          1 | chen    | 2012-01-09 09:16:38 |
    ------------ --------- ---------------------

        mysql> show create table studentG;
        *************************** 1. row ***************************
        Table: student
        Create Table: CREATE TABLE `student` (
        `id` int(4) NOT NULL AUTO_INCREMENT,
        `name` char(20) NOT NULL,
        `age` tinyint(2) NOT NULL DEFAULT '0',
        `dept` varchar(16) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `index_name` (`name`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)

          mysql> desc student2;
           ------- ------------- ------ ----- --------- ----------------
          | Field | Type | Null | Key | Default | Extra |
           ------- ------------- ------ ----- --------- ----------------
          | id | int(11) | NO | PRI | NULL | auto_increment |
          | name | char(20) | NO | | NULL | |
          | age | tinyint(2) | NO | | 0 | |
          | dept | varchar(16) | YES | | NULL | |
           ------- ------------- ------ ----- --------- ----------------
          4 rows in set (0.00 sec)

    | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      | ADD [CONSTRAINT [symbol]]
            PRIMARY KEY [index_type] (index_col_name,...)
      | ADD [CONSTRAINT [symbol]]
            UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

          唯有int类型且为primary key才得以应用auto_increment

      修改student2表的id列为自增主键列:例子  

    ...

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:深切mysql外键关联难点的详解

    关键词: 68399皇家赌场 MySQL数据库