高级SQL语句整理(mysql5.7下通过运行)

-- 选择要操作的数据库
USE db1;
-- 创建表
CREATE TABLE `dept` (
  `deptno` INT (3),
  `dname` VARCHAR (14),
  `loc` VARCHAR (13),
  CONSTRAINT dept_pk PRIMARY KEY (deptno)
);
DROP TABLE `dept`;
-- 插入数据进行准备工作
INSERT  INTO `dept`(`deptno`,`dname`,`loc`) VALUES (10,'ACCOUNTING','NEW YORK');
INSERT  INTO `dept`(`deptno`,`dname`,`loc`) VALUES (20,'RESEARCH','DALLAS');
INSERT  INTO `dept`(`deptno`,`dname`,`loc`) VALUES (30,'SALES','CHICAGO');
INSERT  INTO `dept`(`deptno`,`dname`,`loc`) VALUES (40,'OPERATIONS','BOSTON');
-- 删除多个表
DROP TABLE student,dept;
-- 创建表，设置外键
CREATE TABLE `emp` (
  `empno` INT (5),
  `ename` VARCHAR (10),
  `job` VARCHAR (9),
  `mgr` INT (5),
  `hiredate` DATE,
  `sal` DOUBLE (7, 2),
  `comm` DOUBLE (7, 2),
  `deptno` INT (3),
  CONSTRAINT emp_pk PRIMARY KEY (empno),
  FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
-- 内联结
SELECT 列 FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键;
-- 左外联
SELECT 列 FROM  表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键
-- 右外联
SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键;
-- 查询一级分类
SELECT * FROM t_category WHERE pid=0;
-- 查询一级分类下的二级分类和三级分类
SELECT * FROM t_category t1 LEFT JOIN t_category t2 ON t1.id = t2.pid LEFT JOIN t_category t3 ON t2.id = t3.pid WHERE t1.NAME = '电脑/办公';
-- 子查询
SELECT 列 FROM 表 WHERE 条件（子查询）;
SELECT 列 FROM (子查询结果集)  WHERE 条件;