Centos Mysql
创建部门表.CREATE TABLE `department` ( `id` int(11) NOT NULL AUTO_INCREMENT, `department` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `department` VALUES ('1', 'tech');INSERT INTO `department` VALUES ('2', 'product');INSERT INTO `department` VALUES ('3', 'market');
创建员工表,同时创建外键(FOREIGN KEY)一对多.CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `department_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), FOREIGN KEY (department_id) references department(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `staff` VALUES ('1', 'galen', '30', '1');INSERT INTO `staff` VALUES ('2', 'annie', '20', '1');INSERT INTO `staff` VALUES ('3', 'timor', '10', '3');
两张表合成一张表.select * from 员工表,部门表;
查询,员工表department_id字段和部门表id字段相同的记录.select * from 员工表,部门表 where 员工表.department_id=部门表.id;
查询,galen是哪个部门的.select 部门字段 from 员工表,部门表 where 员工表.department_id=部门表.id and 员工表.name='galen';
左外连接left join,已左边的表为基准.(速度快,用的最多)
右外连接right join,已右边的表为基准.
子查询:把内层查询结果,提供给外层查询.子查询可以在使用表达式的任何地方使用(IN、NOT IN、EXISTS、NOT EXISTS、ANY、ALL、=、!=、<、>).查询,哪个部门里不存在员工.select * from 部门表 where id not in (select department_id from 员工表);