交集和差集
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) 获取两个日期相减的天数
- ...
- 其实这篇博客落枫潇潇