精华内容
下载资源
问答
  • MySQL主外键设置
    2021-12-02 17:27:30

    Q:主外键的关系

    pr:ALTER TABLE tb_UserAndRole ADD CONSTRAINT FK__tb_UandR_Role FOREIGN KEY (RoleID) references tb_Role(ID);

    pr:alert外键名,foreign key(外键字段)reference主表(主键名)

    pr:本表作为外键插入到主表的主键上。

    pr:主外键起到限制两表的作用。外键表不能插入没在主键表信息中的信息,不能更新没在表中的信息。对于主键表,更新或删除主键信息时,如果设置了级联删除那会跟着删除外键表,如果设置no action则删除不了有外键信息的主键表信息。

    pr:如果设置了外键,就不能再插入不在主键表里的信息。不能乱插数据了。

    pr:对于数据的稳定性很有帮助,人事系统中几乎所有的表都会和主表A00作关联,所有的数据也都是由主表所来,受到主表的限制。不加外键,这个表的信息不确定性太大了,可以任意插数据。

    pr:设置了主键,主键是唯一的,则插入数据时不用再判断是否有相同的数据。

    pr:设置了级联删除的外键,则删除主键信息时,外键表相关联的数据也会被删除。

    pr:#比$作用大,#可以放置SQL注入。

    更多相关内容
  • mysql主外键约束怎么写?

    千次阅读 2021-01-18 22:01:49
    mysql外键约束的写法:“CREATE TABLE 表名(FOREIGN KEY 字段名 REFERENCES 表名 主键列)”。mysql主键约束主键(PRIMARY KEY)的完整称呼是“主键约束”,是 MySQL 中使用最为频繁的约束。一般情况下,为了便于 ...

    mysql主键约束的写法:“CREATE TABLE 表名(字段名 数据类型 PRIMARY KEY)”;mysql外键约束的写法:“CREATE TABLE 表名(FOREIGN KEY 字段名 REFERENCES 主表名 主键列)”。

    026017d596ddbfd7fe9240c119b8769c.png

    mysql主键约束

    主键(PRIMARY KEY)的完整称呼是“主键约束”,是 MySQL 中使用最为频繁的约束。一般情况下,为了便于 DBMS 更快的查找到表中的记录,都会在表中设置一个主键。

    1、在创建表时设置主键约束

    在 CREATE TABLE 语句中,通过 PRIMARY KEY 关键字来指定主键。

    在定义字段的同时指定主键,语法格式如下: PRIMARY KEY [默认值]

    例 1

    在 test_db 数据库中创建 tb_emp3 数据表,其主键为 id,SQL 语句和运行结果如下。mysql> CREATE TABLE tb_emp3

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT

    -> );

    Query OK, 0 rows affected (0.37 sec)

    mysql> DESC tb_emp3;

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

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

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

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

    | name | varchar(25) | YES | | NULL | |

    | deptId | int(11) | YES | | NULL | |

    | salary | float | YES | | NULL | |

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

    4 rows in set (0.14 sec)

    或者是在定义完所有字段之后指定主键,语法格式如下:[CONSTRAINT ] PRIMARY KEY [字段名]

    例 2

    在 test_db 数据库中创建 tb_emp4 数据表,其主键为 id,SQL 语句和运行结果如下。mysql> CREATE TABLE tb_emp4

    -> (

    -> id INT(11),

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(id)

    -> );

    Query OK, 0 rows affected (0.37 sec)

    mysql> DESC tb_emp4;

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

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

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

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

    | name | varchar(25) | YES | | NULL | |

    | deptId | int(11) | YES | | NULL | |

    | salary | float | YES | | NULL | |

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

    4 rows in set (0.14 sec)

    2、在修改表时添加主键约束

    主键约束不仅可以在创建表的同时创建,也可以在修改表时添加。但是需要注意的是,设置成主键约束的字段中不允许有空值。

    在修改数据表时添加主键约束的语法格式如下:ALTER TABLE ADD PRIMARY KEY();

    查看 tb_emp2 数据表的表结构,SQL 语句和运行结果如下所示。mysql> DESC tb_emp2;

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

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

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

    | id | int(11) | NO | | NULL | |

    | name | varchar(30) | YES | | NULL | |

    | deptId | int(11) | YES | | NULL | |

    | salary | float | YES | | NULL | |

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

    4 rows in set (0.14 sec)

    例 3

    修改数据表 tb_emp2,将字段 id 设置为主键,SQL 语句和运行结果如下。mysql> ALTER TABLE tb_emp2

    -> ADD PRIMARY KEY(id);

    Query OK, 0 rows affected (0.94 sec)

    Records: 0 Duplicates: 0 Warnings: 0

    mysql> DESC tb_emp2;

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

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

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

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

    | name | varchar(30) | YES | | NULL | |

    | deptId | int(11) | YES | | NULL | |

    | salary | float | YES | | NULL | |

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

    4 rows in set (0.12 sec)

    通常情况下,当在修改表时要设置表中某个字段的主键约束时,要确保设置成主键约束的字段中值不能够有重复的,并且要保证是非空的。否则,无法设置主键约束。

    mysql外键约束

    MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。

    外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。比如,一个水果摊,只有苹果、桃子、李子、西瓜等 4 种水果,那么,你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜,其它的水果都是不能购买的。

    定义外键时,需要遵守下列规则:主表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表,这样的表称为自参照表,这种结构称为自参照完整性。

    必须为主表定义主键。

    主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。

    在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

    外键中列的数目必须和主表的主键中列的数目相同。

    外键中列的数据类型必须和主表主键中对应列的数据类型相同。

    1、在创建表时设置外键约束

    在 CREATE TABLE 语句中,通过 FOREIGN KEY 关键字来指定外键,具体的语法格式如下:[CONSTRAINT ] FOREIGN KEY 字段名 [,字段名2,…]

    REFERENCES 主键列1 [,主键列2,…]

    例 4

    为了展现表与表之间的外键关系,本例在 test_db 数据库中创建一个部门表 tb_dept1,表结构如下表所示。字段名称数据类型备注

    idINT(11)部门编号

    nameVARCHAR(22)部门名称

    locationVARCHAR(22)部门位置

    创建 tb_dept1 的 SQL 语句和运行结果如下所示。mysql> CREATE TABLE tb_dept1

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22) NOT NULL,

    -> location VARCHAR(50)

    -> );

    Query OK, 0 rows affected (0.37 sec)

    创建数据表 tb_emp6,并在表 tb_emp6 上创建外键约束,让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id,SQL 语句和运行结果如下所示。mysql> CREATE TABLE tb_emp6

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> CONSTRAINT fk_emp_dept1

    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)

    -> );

    Query OK, 0 rows affected (0.37 sec)

    mysql> DESC tb_emp6;

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

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

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

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

    | name | varchar(25) | YES | | NULL | |

    | deptId | int(11) | YES | MUL | NULL | |

    | salary | float | YES | | NULL | |

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

    4 rows in set (1.33 sec)

    以上语句执行成功之后,在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束,外键名称为 deptId,其依赖于表 tb_dept1 的主键 id。

    注意:从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致。例如,两者都是 INT 类型,或者都是 CHAR 类型。如果不满足这样的要求,在创建从表时,就会出现“ERROR 1005(HY000): Can't create table”错误。

    2、在修改表时添加外键约束

    外键约束也可以在修改表时添加,但是添加外键约束的前提是:从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

    在修改数据表时添加外键约束的语法格式如下:ALTER TABLE ADD CONSTRAINT

    FOREIGN KEY() REFERENCES ();

    例 5

    修改数据表 tb_emp2,将字段 deptId 设置为外键,与数据表 tb_dept1 的主键 id 进行关联,SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp2

    -> ADD CONSTRAINT fk_tb_dept1

    -> FOREIGN KEY(deptId)

    -> REFERENCES tb_dept1(id);

    Query OK, 0 rows affected (1.38 sec)

    Records: 0 Duplicates: 0 Warnings: 0

    mysql> SHOW CREATE TABLE tb_emp2\G

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

    Table: tb_emp2

    Create Table: CREATE TABLE `tb_emp2` (

    `id` int(11) NOT NULL,

    `name` varchar(30) DEFAULT NULL,

    `deptId` int(11) DEFAULT NULL,

    `salary` float DEFAULT NULL,

    PRIMARY KEY (`id`),

    KEY `fk_tb_dept1` (`deptId`),

    CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312

    1 row in set (0.12 sec)

    注意:在为已经创建好的数据表添加外键约束时,要确保添加外键约束的列的值全部来源于主键列,并且外键列不能为空。

    相关推荐:php培训

    展开全文
  • mysql主外键关系

    2021-01-18 18:23:31
    一、外键:1、什么是外键2、外键语法3、外键的条件4、添加外键5、删除外键1、什么是外键:主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性外键:是另一表的主键, 外键可以有重复的, 可以是...

    一、外键:

    1、什么是外键

    2、外键语法

    3、外键的条件

    4、添加外键

    5、删除外键

    1、什么是外键:

    主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性

    外键:是另一表的主键, 外键可以有重复的, 可以是空值,用来和其他表建立联系用的。所以说,如果谈到了外键,一定是至少涉及到两张表。例如下面这两张表:

    8e39d8f03c58e337670ee95e188255d6.png

    上面有两张表:部门表(dept)、员工表(emp)。Id=Dept_id,而Dept_id就是员工表中的外键:因为员工表中的员工需要知道自己属于哪个部门,就可以通过外键Dept_id找到对应的部门,然后才能找到部门表里的各种字段信息,从而让二者相关联。所以说,外键一定是在从表中创建,从而找到与主表之间的联系;从表负责维护二者之间的关系。

    我们先通过如下命令把部门表和职工表创建好,方便后面的举例:

    fc8e6ca3daabc8c2c4f6fcf21d12a09f.gif

    create table department( id int primary key auto_increment, name varchar(20) not null, description varchar(100) ); create table employee( id int primary key auto_increment, name varchar(10) not null, gender varchar(2) not null, salary float(10,2), age int(2), gmr int, dept_id int );

    fc8e6ca3daabc8c2c4f6fcf21d12a09f.gif

    然后把两张表的数据填好,显示效果如下:

    部门表:

    55f705bc3d30e11a21a73eb7ef83b806.png

    员工表:

    efb1a0841f638a2d6c7509d3be414445.png

    2、外键的使用需要满足下列的条件:(这里涉及到了InnoDB的概念)

    1. 两张表必须都是InnoDB表,并且它们没有临时表。

    注:InnoDB是数据库的引擎。MySQL常见引擎有两种:InnoDB和MyISAM,后者不支持外键。

    2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

    3. 建立外键关系的对应列必须建立了索引。

    4. 假如显式的给出了CONSTRAINT symbol,那symbol在数据库中必须是唯一的。假如没有显式的给出,InnoDB会自动的创建。

    面试题:你的数据库用什么存储引擎?区别是?

    答案:常见的有MyISAM和InnoDB。

    MyISAM:不支持外键约束。不支持事务。对数据大批量导入时,它会边插入数据边建索引,所以为了提高执行效率,应该先禁用索引,在完全导入后再开启索引。

    InnoDB:支持外键约束,支持事务。对索引都是单独处理的,无需引用索引。

    3、添加外键的语法:

    有两种方式:

    方式一:在创建表的时候进行添加

    方式二:表已经创建好了,继续修改表的结构来添加外键

    【方式一】在创建表的时候进行添加

    fc8e6ca3daabc8c2c4f6fcf21d12a09f.gif

    [CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1) REFERENCES tbl_name (主表的字段2) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}

    fc8e6ca3daabc8c2c4f6fcf21d12a09f.gif

    上面的代码是同一行,中括号里的内容是可选项。

    解释如下:

    CONSTRAINT symbol:可以给这个外键约束起一个名字,有了名字,以后找到它就很方便了。如果不加此参数的话,系统会自动分配一个名字。

    FOREIGN KEY:将从表中的字段1作为外键的字段。

    REFERENCES:映射到主表的字段2。

    ON DELETE后面的四个参数:代表的是当删除主表的记录时,所做的约定。

    RESTRICT(限制):如果你想删除的那个主表,它的下面有对应从表的记录,此主表将无法删除。

    CASCADE(级联):如果主表的记录删掉,则从表中相关联的记录都将被删掉。

    SET NULL:将外键设置为空。

    NO ACTION:什么都不做。

    注:一般是RESTRICT和CASCADE用的最多。

    【方式二】表已经创建好了,继续修改表的结构来添加外键。

    我们在第一段中内容中已经将表建好了,数据也填充完了,现在来给从表(员工表)添加外键,让它与主表(部门表)相关联。代码举例如下:

    ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id);

    代码解释:

    ALTER TABLE employee:在从表employee中进行操作;

    ADD FOREIGN KEY(dept_id):将从表的字段dept_id添加为外键;

    REFERENCES department(id):映射到主表department当中为id的字段。

    运行上方代码后,我们通过navicat来看一下外键有没有添加成功:

    e502860bafe4df3ce8651bd92f127604.png

    上图中,选中表employee,单击红框部分的“设计表”按钮,界面如下:

    e81f07060d9260a7c987b6261ff49f49.png

    上图中就可以看到我们新建的外键了,而且系统默认给这个外键起了个名字:employee_ibfk_1。默认规则是RESTRICT。紧接着来给外键设置值:

    ebfc4de5b5c37d8ae584ee5fe9769e15.png

    上图中,我们打开员工表,然后给外键设置值,1代表宣传部,2代表秘书部。

    然后我们回到主表(部门表),此时如果想删除id为1的宣传部,会弹出如下提示:(因为外键的默认规则为RESTRICT)

    ace52a0640ef0558f73242640b56b222.png

    4、删除外键:(通过sql语句的方式)

    我们在navicat中可以通过图形界面的方式删除外键,也可以通过sql语句来删除。

    (1)获取外键名:

    如果在命令行中不知道外键的名字,可以通过查看表的定义找出外键的名称:

    show create table emp;

    运行效果如下:

    25d324a1d9e14d6e40140054264b98da.png

    其实我们在表的信息中也可以看到:(注意书写命令的格式)

    1056c3f42256fa3d0cfea7583828b07a.png

    (2)删除外键:

    alter table emp drop foreign key 外键名;

    二、表连接(join)

    我们以下面的两张表举例:作为本段内容的例子

    department部门表:

    d1cfe1146ea2d031fd1648c3ae10c404.png

    employee员工表:

    b0984785073341d814980efe8d1c39e7.png

    其中,外键对应关系为:employee.dept_id=department.id。employee.leader中的数字的含义为:生命壹号的leader是生命二号,生命二号没有leader,生命叁号的leader是生命壹号。

    1、内连接:只列出匹配的记录

    语法:

    SELECT … FROM join_table [INNER] JOIN join_table2 [ON join_condition] WHERE where_definition

    解释:只列出这些连接表中与连接条件相匹配的数据行。INNER可以不写,则默认为内连接。[ON join_condition]里面写的是连接的条件。

    举例:

    select e.name,d.name from employee e inner join department d on e.dept_id=d.id;

    等价于:

    select e.name,d.name from employee e,department d where e.dept_id=d.id;

    运行效果:

    75633900bf01616eb7e086a2d17e6efc.png

    2、外连接:

    外连接分类:

    左外连接(LEFT [OUTER] JOIN)

    右外连接(RIGHT [OUTER] JOIN)

    全外连接(FULL [OUTER] JOIN)  注:MySQL5.1的版本暂不支持

    语法:

    fc8e6ca3daabc8c2c4f6fcf21d12a09f.gif

    SELECT … FROM join_table1 (LEFT | RIGHT | FULL) [OUTER] JOIN join_table2 ON join_condition WHERE where_definition

    fc8e6ca3daabc8c2c4f6fcf21d12a09f.gif

    解释:

    不仅列出与连接条件(on)相匹配的行,还列出左表table1(左外连接)、或右表table2(右外连接)、或两个表(全外连接)中所有符合WHERE过滤条件的数据行。一般都是用左连接或者外连接。

    其中,[OUTER]部分可以不写,(LEFT | RIGHT | FULL)部分要写其中一个。

    2、1左外连接:左表列出全部,右表只列出匹配的记录。

    举例:

    7e567f301634b88e4e924c9cfd8c5a7e.png

    2、2右外连接:右表列出全部,左表只列出匹配的记录。

    举例:

    4b82b6d18c56303db93726a428d21152.png

    3、交叉连接:

    语法:

    SELECT … FROM join_table1 CROSS JOIN join_table2;

    没有ON子句和WHERE子句,它返回的是连接表中所有数据行的笛卡尔积。

    笛卡尔积举例:假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}

    其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

    等价于:(荐)

    SELECT … FROM table1, table2;

    举例:

    a8fc494b74beb32af71994334ead0948.png

    4、自连接:参与连接的表都是同一张表。(通过给表取别名虚拟出两张表)

    注:非常重要,在JavaWeb中的目录树中用的特别多。

    举例:查询出员工姓名和其leader的姓名(类似于求节点及其父节点)

    75165f3619b9501350a451a2e2854fe8.png

    我们来详细解释一下上面的代码。对于同一张employee表,我们把e1作为员工表,e2作为领导表。首先把全部的员工列出来(基于左外连接),然后找到我们所需要的条件:员工的经理id(e1.leader)等于经理表的id(e2.id)。

    举例:查询出所有leader的姓名。

    a4c2fb0467784b9019edfe7d1d024aec.png

    分析的道理同上。

    其实,上面的两个查询结果都是下面这个查询结果的一部分:

    d249d163c6a36e2630f2a6b0b5686f24.png

    三、子查询:

    作用:某些情况下,当进行查询的时候,需要的条件是另外一个select 语句的结果,这个时候,就要用到子查询。

    定义:为了给主查询(外部查询)提供数据而首先执行的查询(内部查询)被叫做子查询。也就是说,先执行子查询,根据子查询的结果,再执行主查询。

    关键字:用于子查询的关键字主要包括 IN、NOT IN、EXIST、NOT EXIST、=、<>等(符号“<>”的意思是:不等于)。

    备注:MySQL从4.1开始才支持SQL的子查询。一般说子查询的效率低于连接查询(因为子查询至少需要查询两次,即至少两个select语句。子查询嵌套也多,性能越低)。表连接都可以用子查询替换,但反过来说却不一定。

    我们一下面的这张员工表举例:

    dfd159e99d392be80e73710a24d56a9f.png

    1、举例:查询月薪最高的员工的名字

    b5adcdd298f185d7cc077a9509bc919e.png

    上面的例子中,我们就是先通过聚合函数查出最高的月薪,然后根据这个值查出对应员工的名字。

    2、举例:查询出每个部门的平均月薪

    d5a3dcc30f94623d5ceac2201a9c9d80.png

    上面的例子中,先将部门进行分类(前提是部门不能为空),然后分别单独求出各类中的薪水平均值。

    注:这里我们没有用到子查询,因为比较麻烦。

    3、举例:查询月薪比平均月薪高的员工的名字(我们知道,整体的平均工资是250)

    fb9b96a4ccf7b527caa8221ee421bfd1.png

    疑问:如果要查询比部门平均工资高的员工,该怎么写呢?下面的这种写法是错误的:

    4f41058a7d134139f224bd8d027b780c.png

    四、索引

    主要内容如下:

    1、索引的概念

    2、普通索引

    3、唯一索引

    4、主键索引

    5、全文索引

    6、删除、禁用索引

    7、设计索引的原则

    关于索引,推荐的学习链接:

    1、索引的概念:

    索引是数据库中用来提高查询性能的最常用工具。

    所有MySQL列类型都可以被索引,对相关列使用索引是提高SELECT操作性能的最佳途径。索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。

    在使用以下操作符时,都会用到相关列上的索引:

    >、=、<=、<>、IN、 BETWEEN

    LIKE 'pattern'(pattern不能以通配符开始,即通配符不能放前面,即使放在了前面,索引也无效)

    注:索引的值因为不断改变,所以是它需要维护的。如果数据量较少,建议不用索引。

    2、normal普通索引(第一种索引)

    方式一:直接创建索引:

    语法:

    CREATE INDEX 索引名 ON 表名(列名[(length)]…);

    举例:

    1914776302890c3235b795143a0a2dc8.png

    然后,我们在表中可以看到新创建的索引:(我们可以在这个navicat的可视化界面中修改索引类型)

    9f8de5278ee13e500164dc9580abfe8d.png

    方式二:修改表时添加索引

    语法:

    ALTER TABLE 表名 ADD INDEX [索引名] (列名[(length)]…);

    方式三:创建表的时候指定索引:

    CREATE TABLE 表名 ( 表名 ( [...], INDEX [索引名] (列名[(length)]…);

    注意:如果要创建索引的列的类型是CHAR、VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length。

    3、unique 唯一索引:(第二种索引)

    这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都必须唯一。例如可以将身份证号作为索引。

    创建方式和上方的普通索引类似。即:将普通索引的“index”改为“unique index”。

    4、主键索引(一种特殊的唯一索引)

    主键是一种特殊的唯一索引,一般在创建表的时候指定。在 MYSQL 中,当你建立主键时,主键索引同时也已经建立起来了,不必重复设置。

    记住:一个表只能有一个主键,也即只有一个主键索引。

    5、FULLTEXT全文索引:(第三种索引)

    MySQL从3.2版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。

    MySQL5.0版本只有MyISAM存储引擎支持FULLTEXT,并且只限于CHAR、VARCHAR和TEXT类型的列上创建。

    注:全文索引维护起来很吃力,所以了解即可。

    创建方式和上方的普通索引类似。即:将普通索引的“index”改为“fulltext index”。

    6、删除、禁用索引:

    一般使用“删除”,不使用“禁用”。

    删除索引:

    语法:

    DROP INDEX 索引名 ON 表名

    对于MyISAM表在做数据大批量导入时,它会边插入数据边建索引。所以为了提高执行效率,应该先禁用索引,在完全导入后,再开启索引。而InnoDB表对索引都是单独处理的,无需禁用索引。

    禁用索引:

    ALTER TABLE 表名 DISABLE KEYS;

    打开索引:

    ALTER TABLE 表名 ENABLE KEYS;

    7、设计索引的原则:

    最适合索引的列是出现在WHERE子句中的列,或连接子句(on语句)中指定的列,而不是出现在SELECT后的列。

    索引列的值中,不相同的数目越多,索引的效果越好。

    使用短索引:对于CHAR和VARCHAR列,只用它的一部分来创建索引,可以节省索引空间,也会使查询更快捷。

    如:CREATE INDEX part_of_name ON employees(name(10));  这个句子中指定的length长度为10,就是使用短索引,也就是说取name的前十个字符。

    利用最左前缀。

    根据搜索的关键字建立多列索引。

    不要过度索引。维护索引需要成本。

    展开全文
  • MySQL外键约束是用来在两个表之间建立链接的,其中一个表发生变化,另外一个表也发生变化。从这个特点来看,它主要是为了保证表数据的一致性和完整性的。对于两个通过外键关联的表,相关联字段中主键所在的表是...

    MySQL的外键约束是用来在两个表之间建立链接的,其中一个表发生变化,另外一个表也发生变化。从这个特点来看,它主要是为了保证表数据的一致性和完整性的。

    对于两个通过外键关联的表,相关联字段中主键所在的表是主表,也称之为父表,外键所在的表是从表,也称之为子表,定义外键的时候需要遵守几个规则:

    1、父表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则父表与子表是同一个表,这样的表称为自参照表,这种结构称为自参照。

    2、必须为父表定义主键。

    3、主键不能包含空值,但允许在外键中出现空值。也就是说,只要外键的每个非空值出现在指定的主键中,这个外键的内容就是正确的。

    4、外键中列的数目必须和父表的主键中列的数目相同。

    5、外键中列的数据类型必须和父表主键中对应列的数据类型相同。说这么多比较笼统,还是看看例子吧。

    mysql:yeyztest ::>>create table fk_test_1(

    -> id int not null primary key auto_increment,

    -> name varchar() default '');

    Query OK, rows affected (0.10 sec)

    mysql:yeyztest ::>>create table fk_test_2(

    -> id int not null primary key auto_increment,

    -> uid int,

    -> foreign key fk_uid(uid) references fk_test_1(id));

    Query OK, rows affected (0.06 sec)

    这里我们创建两个表,一个是fk_test_1,一个是fk_test_2,其中fk_test_2的uid列上设置外键,关联fk_test_1的表的id列,这里很明显,fk_test_1是父表,而fk_test_2是子表,接下来我们进行数据插入实验。

    mysql:yeyztest ::>>insert into fk_test_1 values (,'aaa'),(,'bbb');

    Query OK, rows affected (0.00 sec)

    Records: Duplicates: Warnings:

    mysql:yeyztest ::>>select * from fk_test_1;

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

    | id | name |

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

    | | aaa |

    | | bbb |

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

    rows in set (0.00 sec)

    mysql:yeyztest ::>>insert into fk_test_2 values (,),(,);

    Query OK, rows affected (0.00 sec)

    Records: Duplicates: Warnings:

    mysql:yeyztest ::>>insert into fk_test_2 values (,);

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>insert into fk_test_2 values (,);

    ERROR (): Cannot add or update a child row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

    先在主表上插入两条数据,分别是id=1和id=2的数据,然后再子表插入数据,子表插入uid=1和uid=2的数据都能成功,而要插入uid=3的数据时提示失败,也就是说,默认情况下,子表进行插入时,插入的外键关联字段值必须是父表被关联的列包含的值。注意这里的默认情况,后续会进行说明。

    再来看看删除的情况,

    mysql:yeyztest ::>>select * from fk_test_2 ;

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

    | id | uid |

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

    | | |

    | | |

    | | |

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

    rows in set (0.00 sec)

    mysql:yeyztest ::>>delete from fk_test_2 where id=;

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>select * from fk_test_1 ;

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

    | id | name |

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

    | | aaa |

    | | bbb |

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

    rows in set (0.00 sec)

    mysql:yeyztest ::>>delete from fk_test_1 where id=;

    ERROR (): Cannot delete or update a parent row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

    可以看到,在子表fk_test_2上进行删除,没有出现任何问题,而在父表fk_test_1上删除时,显示无法删除id=1的值,原因是有一个外键约束存在,也就是说,默认情况下,在父表进行删除时,无法直接删除子表中已经存在依赖关联的列值。注意这里的默认情况,下面将会说明。

    既然delete不成功,试试update,

    mysql:yeyztest ::>>update fk_test_1 set id= where id=;

    ERROR (): Cannot delete or update a parent row:

    a foreign key constraint fails (`yeyztest`.`fk_test_2`,

    CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

    mysql:yeyztest ::>>update fk_test_1 set name='ccc' where id=;

    Query OK, row affected (0.00 sec)

    Rows matched: Changed: Warnings:

    可以看到,update父表的主键列还是不能成功执行,但是update其他的列,可以成功执行。

    到这里,我们已经知道,外键的存在是为了保证数据的完整和统一性,但是也带来了一点问题,那就是父表中凡是被子表依赖的列,都没办法删除了,这不是我们想要的,有一些数据确实会过期,我们有删除的需求,那么这个时候应该怎么办?

    在上面的测试中,我们反复提到一个词,就是默认情况,我们没有设置外键的删除和更新规则,这里mysql帮我们使用了最严格的的规则,那就是restrict,其实还有其他一些规则,这里全部列出来:

    delete父表的情况:

    cascade,set null,no action,restrict

    update父表的情况:

    cascade,set null,no action,restrict

    其中

    restrict是默认操作,它表示拒绝父表删除或者修改外键已经被子表所依赖的列,这是最安全的设置;

    cascade表示在父表发生删除的时候直接删除子表的记录,这是最危险的设置;

    set null表示父表删除的时候,对子表进行null值处理;

    no action表示父表删除的时候,子表不进行任何改动。

    设置关联的语法如下:

    alter table 表名 add constraint FK_ID foreign key (外键字段名) references 外表表名 (主键字段名)

    [on delete {cascade | set null | no action| restrict}]

    [on update {cascade | set null | no action| restrict}]

    现在我们测试一下这其他三种情况,首先看cascade的情况:

    mysql:yeyztest ::>>select * from fk_test_1;

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

    | id | name |

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

    | | ccc |

    | | bbb |

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

    rows in set (0.00 sec)

    mysql:yeyztest ::>>select * from fk_test_2;

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

    | id | uid |

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

    | | |

    | | |

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

    rows in set (0.00 sec)

    mysql:yeyztest ::>>show create table fk_test_2\G

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

    Table: fk_test_2

    Create Table: CREATE TABLE `fk_test_2` (

    `id` int() NOT NULL AUTO_INCREMENT,

    `uid` int() DEFAULT NULL,

    PRIMARY KEY (`id`),

    KEY `fk_uid` (`uid`),

    CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`)

    ) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8

    row in set (0.00 sec)

    mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_test_2_ibfk_1;

    Query OK, rows affected (0.02 sec)

    Records: Duplicates: Warnings:

    mysql:yeyztest ::>>alter table fk_test_2 add constraint fk_uid foreign key (uid) references fk_test_1(id) on delete cascade;

    Query OK, rows affected (0.03 sec)

    Records: Duplicates: Warnings:

    #######################################

    ####此处删除父表id=的记录,查看子表的结果###

    #######################################

    mysql:yeyztest ::>>delete from fk_test_1 where id=;

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>select * from fk_test_1 ;

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

    | id | name |

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

    | | ccc |

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

    row in set (0.00 sec)

    mysql:yeyztest ::>>select * from fk_test_2 ;

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

    | id | uid |

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

    | | |

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

    row in set (0.00 sec)

    可以看到,一开始,父表的值包含id=1和id=2的值,子表的值包含uid=2和uid=1的值,当我们删除父表的id=2的值之后,子表中uid=2的值也直接被删除了。这就是cascade的作用,也就是级联删除。

    在看一眼set null的情况:

    mysql:yeyztest ::>>alter table fk_test_2 drop foreign key fk_uid;

    Query OK, row affected (0.02 sec)

    Records: Duplicates: Warnings:

    mysql:yeyztest ::>>alter table fk_test_2 add CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`) ON DELETE set null;

    Query OK, row affected (0.03 sec)

    Records: Duplicates: Warnings:

    mysql:yeyztest ::>>delete from fk_test_1 where id=;

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>select *from fk_test_1 ;

    Empty set (0.00 sec)

    mysql:yeyztest ::>>select *from fk_test_2 ;

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

    | id | uid |

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

    | | NULL |

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

    row in set (0.00 sec)

    可以看到,设置了set null之后,当父表删除id=1的值时,子表的uid的值变成了null,而没有删除记录。

    no action的情况也是类似,只不过是子表的记录没有发生任何改动。

    以上是父表进行delete的操作,当父表进行update的时候,子表可以选择的情况也有以上四种,和delete基本保持一致,这里不再赘述。有兴趣可以自己测试一发。

    最后,说明一点,子表的外键列可以为空值。

    mysql:yeyztest ::>>insert into fk_test_1 values (,);

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>select *from fk_test_2 ;

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

    | id | uid |

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

    | | NULL |

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

    row in set (0.00 sec)

    mysql:yeyztest ::>>insert into fk_test_2 values (,NULL);

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>insert into fk_test_2 values (,NULL);

    Query OK, row affected (0.00 sec)

    mysql:yeyztest ::>>select * from fk_test_2;

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

    | id | uid |

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

    | | NULL |

    | | NULL |

    | | NULL |

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

    rows in set (0.00 sec)

    以上就是MySQL外键约束的实例讲解的详细内容,更多关于MySQL外键约束的资料请关注脚本之家其它相关文章!

    展开全文
  • MySQL解决主外键约束

    2021-06-04 11:58:28
    首先,需要创建两个表,并且设置主外键 设置主外键约束 把info表中的Intype所属类型 FK 连接类型表的主键 运行这句语句,会出现错误: 报错的问题点:无法添加外键约束 我们应该怎么解决这个bug??? 设置外键有...
  • MySQL主外键以及基本查询

    千次阅读 2019-01-10 12:09:01
    将实体与实体的关系,反应到最终数据库表的设计上来:将关系分成三种:一对一,一对多(多对一)和多对多. 一对一 一对一:一张表的一条记录一定只能与另外一张表的一条记录进行对应,反之亦然. 解决方案: 1.将常用...
  • MySQL主键外键设定方法

    千次阅读 2021-03-15 21:53:27
    数据库 mysql 建立外键的前提:本表的列必须与外键类型相同(外键必须是外表主键)。外键作用: 使两张表形成关联,外键只能引用外表中的列的值!指定主键关键字: foreign key(列名)引用外键关键字: references ...
  • 一个用户可有拥有多个订单,一个订单只能属于一个用户,一对多,在tb_order中使用外键user_id关联tb_user的id。当删除、更新tb_user中的主键时,与之关联的tb_order要受到影响,比如#tb_user中的一条记录1chyabcd#tb...
  • 前言为mysql数据表建立主外键需要注意以下几点:需要建立主外键关系的两个表的存储引擎必须是InnoDB。外键列和参照列必须具有相似的数据类型,即可以隐式转换的数据类型。外键列和参照列必须创建索引,如果外键列不...
  • mysqlmysql主外键关系(详细精讲)

    千次阅读 多人点赞 2016-11-25 00:16:02
    mysqlmysql主外键关系(详细精讲) 作者:yjqyyjw 一、外键: 1、什么是外键 2、外键语法 3、外键的条件 4、添加外键 5、删除外键 1、什么是外键: 主键:是唯一标识一条记录,不能有重复...
  • mysql主外键的设置问题 主键:一般情况下,满足第一范式的表都有一个主键Primary key,用于唯一标示数据库中的一个字段。 外键:外键是相对于数据库设计中的参考完整性而言,它与主键之间是彼此依赖的关系。假设现在...
  • mysql创建外键关联

    2021-01-31 05:59:55
    自己的代码总提示“ERROR 1005: Can't create table (errno: 150)”的错误...参照完整性(Referentialintegrity)是数据库设计中一个重要的概念。在系统不同的列表中,当数据库所有参照合法或非合法关联时都会涉及到参...
  • MySQL - 多表&外键&数据库设计1 多表1.1 多表的意义实际开发中,一个项目通常需要很多张表才能完成。例如一个商城项目的数据库,需要有很多张表:用户表、分类表、商品表、订单表....1.2 单表的缺点1.2.1 ...
  • MYSQL外键的使用以及优缺点

    千次阅读 2021-01-18 21:21:47
    转自:http://www.cnblogs.com/fuland/p/4280434.html主键和索引是不可少的,不仅可以优化数据检索速度,开发人员还省不其它的工作,矛盾焦点:数据库设计是否需要外键。这里有两个问题:一个是如何保证数据库数据的...
  • Mysql中使用外键,可以有效地将多个表格的数据“联动”起来,打破独立状态,互相影响。本文简要介绍Mysql外键约束的创建与删除。
  • PS:外键使用条件必须主外键表都为INNODB引擎,外键列必须建立索引(4.1.2版本后在建立外键会自动创建索引),外键关系两表的列必须数据类型相似。 2.4 外键约束 外键约束是用来维护两张表之间的数据一致性的,...
  • 主外键,索引,主键和索引区别于联系
  • mysql如何建立外键

    2021-04-21 00:43:20
    mysql如何建立外键一个表blog博客表,DROP TABLE IF EXISTS `blog`;CREATE TABLE `blog` (`id` int(11) NOT NULL auto_increment,`title` varchar(11) default NULL,`content` varchar(11) default NULL,PRIMARY ...
  • mysql外键

    2021-01-19 17:12:24
    mysql外键一个班级的学生个人信息表:什么是外键设计的时候,就给表1加入一个外键,这个外键就是表2中的学号字段,那么这样表1就是表,表2就是子表。外键用来干什么为了一张表记录的数据不要太过冗余。这和...
  • Mysql基础增删改查,主外键关联

    万次阅读 2021-01-07 16:27:28
    章节导航Mysql基础入门创建新增删除修改查询数据类型主键外键数值计算关联查询 1.登录mysqlmysql -uroot -p 2.使用数据库: use 数据库名; 3.导入sql文件: souse 目录; 4.各个关键字的顺序:select 字段信息 ...
  • mySql外键名称问题

    2021-02-04 21:12:50
    嗨,我在mysql workbench中设计了一个数据库 . 当我去转发工程师时,我得到了(错误:121),因为我在多个表中都有相同的外键,我意识到这是不允许的 . 我有很多联结表,因为大多数是n:m关系 . 我使用复合主键(由2个...
  • mysql视图 外键 索引

    千次阅读 2021-12-27 10:13:45
    mysql之索引 一、什么是索引 ? 索引是一种数据结构 一个索引是存储的表中数据结构; 索引是咋表的字段上创建的 索引包含了一列值,这个值保存在一个数据结构中 二、索引的作用? 1、保证数据记录的唯一性 2...
  • http://my.oschina.net/liting/blog/356150一、外键:1、什么是外键2、外键语法3、外键的条件4、添加外键5、删除外键1、什么是外键:主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性外键:...
  • mysql设置外键的好处阻止执行 从表插入新行,其外键值不是表的键值便阻止插入; 从表修改外键值,新值不是表的键值便阻止修改; 表删除行,其键值在从表里存在便阻止删除(要想删除,必须先删除从表的...
  • 一、什么是主键、外键: 关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键 比如 学生表(学号,姓名,性别,班级) 其中每个学生的学号是唯一...
  • MySQL的主键和外键

    千次阅读 2021-02-04 14:45:16
    MySQL主键设计原则:不使用任何业务相关的字段作为主键永远也不要更新MySQL主键,修改主键会造成一系列的影响。MySQL主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等主键设计的常用方案是自增ID。...
  • mysql语句声明外键

    千次阅读 2018-11-16 11:20:04
    在Navicat 中进行mysql的语句操作 示例 1 创建一张员工表: CREATE TABLE employ(id INT PRIMARY KEY,ename VARCHAR(20),deptId INT); 2 插入员工数据: INSERT INTO employ VALUES(1,'李四','软件开发部'); ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 31,911
精华内容 12,764
关键字:

mysql主外键设计

mysql 订阅