mysql(四)

最新推荐文章于 2023-02-27 14:40:49 发布

原创最新推荐文章于 2023-02-27 14:40:49 发布 · 235 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

14 篇文章

订阅专栏

外键约束 foreign key

在MySQL中，InnoDB引擎类型的表支持了外键约束，MyISAM类型暂时不支持外键。
建立外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；此外，联合主键在MSSQL中建立外键会出现一些情况，不建议使用。
外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。
外键的定义语法：
[sql] view plain copy
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。指定约束名称可以方便维护，删除约束可以用
[sql] view plain copy
ALTER TABLE tbl_name DROP FOREIGN KEY symbol
语句快速删掉。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动）
CASCADE（跟随外键改动）
SET NULL（设空值）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）

http://blog.youkuaiyun.com/myslq/article/details/48133123

=================================================================

INNODB支持的ON语句
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update
--外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对
-- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的
-- on update/on delete子句
-----------------innodb支持的四种方式---------------------------------------
-----cascade方式在父表上update/delete记录时，同步update/delete掉子表的匹配记录
-----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除--------

FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
ON DELETE CASCADE

------set null方式在父表上update/delete记录时，将子表上匹配记录的列设为null
-- 要注意子表的外键列不能为not null

FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
ON DELETE SET NULL

------Restrict方式 :拒绝对父表进行删除更新操作(了解)

------No action方式在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
-- 进行update/delete操作

=================================================================

多表查询

1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如：
由于其返回的结果为被连接的两个数据表的乘积，因此当有WHERE, ON或USING条件的时候一般不建议使用，因为当数据表项目太多的时候，会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN
2. 内连接INNER JOIN 在MySQL中把INNER JOIN叫做等值连接，即需要指定等值连接条件在MySQL中CROSS和INNER JOIN被划分在一起。 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

SELECT * FROM table1 CROSS JOIN table2  
SELECT * FROM table1 JOIN table2  
SELECT * FROM table1,table2

3. MySQL中的外连接，分为左外连接和右连接，即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果，相对应的使用NULL对应。

a. LEFT [OUTER] JOIN：
除了返回符合连接条件的结果之外，还需要显示左表中不符合连接条件的数据列，相对应使用NULL对应
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
b. RIGHT [OUTER] JOIN：
RIGHT与LEFT JOIN相似不同的仅仅是除了显示符合连接条件的结果之外，还需要显示右表中不符合连接条件的数据列，相应使用NULL对应
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
Tips:
1. on a.c1 = b.c1 等同于 using(c1)
2. INNER JOIN 和 , (逗号) 在语义上是等同的
3. 当 MySQL 在从一个表中检索信息时，你可以提示它选择了哪一个索引。
如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引，这个特性将是很有用的。
通过指定 USE INDEX (key_list)，你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。

可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。如：

mysql> SELECT * FROM table1 USE INDEX (key1,key2) 
-> WHERE key1=1 AND key2=2 AND key3=3; 
mysql> SELECT * FROM table1 IGNORE INDEX (key3) 
-> WHERE key1=1 AND key2=2 AND key3=3;

=================================================================

多表查询之子查询

-- 子查询是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
-- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
-- 还可以包含比较运算符：= 、 !=、> 、<等

-- 1. 带IN关键字的子查询

SELECT * FROM table1 WHERE key IN (SELECT key FROM table2)

-- 2. 带比较运算符的子查询
-- =、!=、>、>=、<、<=、<>

SELECT * FROM table1 WHERE key = (SELECT key FROM table2)

-- 3. 带EXISTS关键字的子查询

-- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
-- 而是返回一个真假值。Ture或False
-- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

 select * from table1
              WHERE EXISTS
              (SELECT key from table2 where key1=203);