例如:按照 department_id 查询 employees(员工表)和 departments(部门表)的信息。
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`;
有局限性:会自动连接两个表中相同的列(可能有多个:department_id 和manager_id)
SELECT last_name,department_id,department_name
FROM employees
NATURAL JOIN departments
方式三:
SELECT ... JOIN ... USING ...
有局限性:好于方式二,但若多表的连接列列名不同,此法不合适
SELECT last_name,department_id,department_name
FROM employees
JOIN departments
USING(department_id)
方式四:
SELECT ... FROM ... JOIN ... ON ...
常用方式,较方式一,更易实现外联接(左、右、满)
SELECT last_name,e.department_id,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
–等值连接 –不等值连接
–非自连接 –自连接
CRAETE TABLE /TRUNCATE TABLE /ALTER TABLE /REANME…TO/DROP TABLE ..操 作完以后,自动 commit;所以,rollback 对其操作,没有效果
对创建的表的列属性、字段进行的限制。诸如:not null/unique/primary key/foreign key/check