-- 选择要操作的数据库
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 条件;
高级SQL语句整理(mysql5.7下通过运行)
最新推荐文章于 2024-08-29 17:59:52 发布