MySQL的小Tips

时间:2021-07-20 06:12:27

交集和差集

MySQL中没有这两个运算,但是有并集运算,所以可以利用这个来间接实现。

差集:

SELECT ID FROM (
SELECT DISTINCT A.AID AS ID FROM TABLE_A A
UNION ALL
SELECT DISTINCT B.BID AS ID FROM TABLE_B B
) as TEMP GROUP BY ID HAVING COUNT(ID) = 1;

并集:

SELECT ID FROM (
SELECT DISTINCT A.AID AS ID FROM TABLE_A A
UNION ALL
SELECT DISTINCT B.BID AS ID FROM TABLE_B B
) as TEMP GROUP BY ID HAVING COUNT(ID) = 2;

外键

MySQL中外键的声明和标准MySQL语言有所不同。

注意:创建外键要保证数据库引擎为INNODB

显示表的索引:

 show indexes from province\G; --\G表示列按行打印

创建表时创建外键:

CREATE TABLE articles (
article_id INT(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
category_id tinyint(3) unsigned NOT NULL,
member_id int(11) unsigned NOT NULL,
INDEX (category_id), --索引
FOREIGN KEY (category_id) REFERENCES categories (category_id) on delete cascade on update cascade , --采用默认的外键名字,on delete ...这部分不是必需的,如果没有就不能删除或者修改外键对应的数据
CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id), --这里显示声明了外键名字
PRIMARY KEY(article_id)
) ENGINE=INNODB; --这里声明为INNODB

其他时候创建外键:

ALTER TABLE `article` ADD CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

删除外键

ALTER TABLE article DROP FOREIGN KEY fk_1

关于count

  • count(*)对行的数目进行计算,包含NULL
  • count(column)对特定的列的值具有的行数进行计算,不包含NULL值
  • 如果表没有主键,那么count(1)比count(*)快
  • 如果有主键,那么count(主键,联合主键)比count(*)快

实用函数

  • curdate() 获取当前日期2017-01-01
  • now() 获取当前时间 2017-01-01 12:12:21
  • datediff(D1,D2) 获取两个日期相减的天数
  • ...
  • 其实这篇博客落枫潇潇