服务器相关（4）：MySQL常见表操作

一、首先创建三张表

-- 创建部门表
CREATE TABLE dept(
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 部门编号
	dname VARCHAR(20)NOT NULL DEFAULT"",-- 部门名称
	loc VARCHAR(13)NOT NULL DEFAULT"" -- 地址
);

INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
SELECT * FROM dept;

-- 创建员工表
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, -- 员工编号
ename VARCHAR(20) NOT NULL DEFAULT"", -- 员工名称
job VARCHAR(9) NOT NULL DEFAULT"",-- 员工工作
mgr MEDIUMINT UNSIGNED,-- 上级
hiredate DATE NOT NULL, -- 入职时间
sal DECIMAL(7,2) NOT NULL, -- 工资
comm DECIMAL(7,2), -- 奖金
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 -- 部门编号
);
-- DROP TABLE emp;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
    (7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
    (7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),
    (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
    (7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),
    (7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
    (7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
    (7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,NULL,20),
    (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
    (7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
    (7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
    (7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
    (7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
SELECT * FROM emp;

-- 创建工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

SELECT * FROM salgrade;

二、 多表查询的简单测试

显示员工名、薪水、部门名、部门编号

SELECT ename,sal,dname,emp.deptno
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	

-- 只显示部门编号为10 的部门名 员工名、工资
SELECT ename,sal,dname,emp.deptno
	FROM emp,dept
	WHERE emp.deptno=dept.deptno AND dept.deptno = 10

-- 显示各个员工的工资和工资级别
SELECT ename,sal,grade
	FROM emp,salgrade
	WHERE sal BETWEEN losal AND hisal;

三、 多表查询的自连接

	-- 显示员工名和他的上级名称
	
SELECT worker.ename AS '职员名', boss.ename AS '上级名'
	FROM emp worker,emp boss
	WHERE worker.mgr = boss.empno;

四、 mysql表的子查询

-- 单行子查询
	
	-- 显示与SIMTH同一部门的所有员工
	SELECT deptno
		FROM emp
		WHERE ename = 'SMITH'

SELECT *
	FROM emp
	WHERE deptno =(
	SELECT deptno
	FROM emp
	WHERE ename = 'SMITH'
	)
	

-- 多行子查询
	
	查询其他部门10号部门工作相同的雇员的	工作、工资 、部门编号
	先查询10号部门有哪些工作
	SELECT DISTINCT job
		FROM emp
		WHERE deptno=10 

完整语句
SELECT ename,job,sal,deptno
	FROM emp
	WHERE job IN (
		SELECT DISTINCT job
		FROM emp
		WHERE deptno=10 
	)AND deptno!= 10

-- all any使用

-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal,deptno
	FROM emp
	WHERE sal >ALL(
		SELECT sal
			FROM emp
			WHERE deptno = 30
	)

-- 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal,deptno
	FROM emp
	WHERE sal >ANY(
		SELECT sal
			FROM emp
			WHERE deptno = 30
	)

五、自我复制

一、先创建表再复制数据
CREATE TABLE my_tab01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT
);
DESC my_tab01;
SELECT * FROM my_tab01;
-- 将emp表中的数据复制到my_tab01
INSERT INTO my_tab01
	(id,`name`,sal, job,deptno)
	SELECT empno,ename,sal,job,deptno FROM emp;
-- 自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;
-- 查看有多少数据
SELECT COUNT(*) FROM my_tab01;

二、先复制表的格式再复制数据
-- 令my_tab02具有与emp相同的格式
CREATE TABLE my_tab02 LIKE emp;
DESC my_tab02;
-- 复制emp表到my_tab02
INSERT INTO my_tab02
	SELECT * FROM emp;
SELECT * FROM my_tab02;

六、合并查询

SELECT ename,sal,job FROM emp WHERE sal>2500

SELECT ename,sal,job FROM emp WHERE job='MANGER'

-- union all 就是将两个查询结果合并，不会去重

SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANGER'

七、表的外连接

首先创建两张表
-- 创建学生表
CREATE TABLE stu(
id INT,
`name`VARCHAR(32)
);
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');

SELECT * FROM stu;
-- 创建成绩表
CREATE TABLE exam(
id INT,
grade INT
);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;

-- 使用左连接（显示所有人的成绩，如果没有策划脑瓜子，显示id）
SELECT `name`, stu.id ,grade
	FROM stu, exam
	WHERE stu.id = exam.id;

-- 改成左外连接
SELECT `name`, stu.id ,grade
	FROM stu LEFT JOIN exam
	ON stu.id = exam.id;
	

-- 改成右外连接（显示所有成绩，如果没有名字，则显示空）
SELECT `name`, stu.id ,grade
	FROM stu RIGHT JOIN exam
	ON stu.id = exam.id;