Mysql联表sql整理

Mysql联表sql整理

本文整理了一些mysql联表方面的sql使用：

---

建表语句

/*Table structure for table `departments` */

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) CHARACTER SET gb2312 DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=utf8;

/*Table structure for table `employees` */

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) CHARACTER SET gb2312 DEFAULT NULL,
  `last_name` varchar(25) CHARACTER SET gb2312 DEFAULT NULL,
  `email` varchar(25) CHARACTER SET gb2312 DEFAULT NULL,
  `phone_number` varchar(20) CHARACTER SET gb2312 DEFAULT NULL,
  `job_id` varchar(10) CHARACTER SET gb2312 DEFAULT NULL,
  `salary` double(10,2) DEFAULT NULL,
  `commission_pct` double(4,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id_fk` (`department_id`),
  KEY `job_id_fk` (`job_id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=utf8;

/*Table structure for table `jobs` */

CREATE TABLE `jobs` (
  `job_id` varchar(10) CHARACTER SET gb2312 NOT NULL,
  `job_title` varchar(35) CHARACTER SET gb2312 DEFAULT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `locations` */

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(40) CHARACTER SET gb2312 DEFAULT NULL,
  `postal_code` varchar(12) CHARACTER SET gb2312 DEFAULT NULL,
  `city` varchar(30) CHARACTER SET gb2312 DEFAULT NULL,
  `state_province` varchar(25) CHARACTER SET gb2312 DEFAULT NULL,
  `country_id` varchar(2) CHARACTER SET gb2312 DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=utf8;

联表查询

• left join(1)
  

SELECT * FROM employees LEFT JOIN departments 
ON employees.`department_id` = departments.`department_id`

• left join(2)
  

SELECT * FROM employees LEFT JOIN departments 
ON employees.`department_id` = departments.`department_id`
where departments.`department_id` IS NULL

• inner join
  

SELECT * FROM employees INNER JOIN departments 
ON employees.`department_id` = departments.`department_id`

• right join(1)
  

SELECT * FROM employees RIGHT JOIN departments 
ON employees.`department_id` = departments.`department_id`

• right join(2)
  

SELECT * FROM employees RIGHT JOIN departments 
ON employees.`department_id` = departments.`department_id`
WHERE employees.`department_id` IS NULL

[PS:mysql不支持外连接，可以用union方式代替]

• 多表联查

SELECT * FROM employees 
INNER JOIN departments 
ON employees.`department_id` = departments.`department_id`
INNER JOIN jobs
ON employees.`job_id` = jobs.`job_id`
INNER JOIN locations
ON departments.`location_id` = locations.`location_id`

联表更新

• 写法1

    UPDATE employees,departments
    SET employees.`commission_pct` =0
    WHERE employees.`department_id` = departments.`department_id`
    AND employees.`first_name` ='Jennifer'

• 写法2

    UPDATE employees 
    INNER JOIN departments 
    ON employees.`department_id` = departments.`department_id`
    SET employees.`commission_pct` =1
    WHERE employees.`first_name` ='Jennifer'

联表删除

单表记录删除
- 写法1

    DELETE employees FROM employees,departments 
    WHERE employees.`department_id` = departments.`department_id`
    AND employees.`first_name` ='Jennifer'

• 写法2

    DELETE employees FROM employees
    INNER JOIN departments 
    on employees.`department_id` = departments.`department_id`
    where employees.`first_name` ='Jennifer'

删除员工表同时删除department记录
- 写法1

    DELETE employees,departments FROM employees
    INNER JOIN departments 
    ON employees.`department_id` = departments.`department_id`
    WHERE employees.`first_name` ='11111'

• 写法2

    DELETE employees,departments FROM employees,departments 
    where employees.`department_id` = departments.`department_id`
    and employees.`first_name` ='11111'

视图

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。
-包含以下关键字的sql语句：聚合函数、distinct、group by、having、union或者union all
-常量视图
-Select中包含子查询
-join
-from一个不能更新的视图
-where子句的子查询引用了from子句中的表