Mysql join table sql collation

This paper sorts out some sql usage of mysql join table:

TABLE statement

/*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;

Linked table query

• 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 does not support external connection, which can be replaced by union]

• Multiple table checking

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`

Linked list updating

• Writing 1

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

• Writing 2

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

Linked list deletion

Single table record deletion
- Writing 1

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

• Writing 2

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

Delete employee table and department record
- Writing 1

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

• Writing 2

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

view

The updatability of a view is related to the definition of a query in the view. The following types of views are not updatable.
-sql statements with the following keywords: aggregate function, distinct, group by, having, union, or union all
-Constant view
-Sub query in Select
-join
-from a view that cannot be updated
-Subqueries in the where clause refer to tables in the from clause