数据库学习实操

内容有点多，还没细化整理

--查询工资最高的三个人

SELECT * FROM emp ORDER BY sal LIMIT 0,3;

 

--非空约束 NOT NULL

--默认值约束

CREATE TABLE student (

id INT PRIMARY KEY,

NAME VARCHAR(50) NOT NULL,

sex VARCHAR(10) DEFAULT '女'

);

INSERT INTO student VALUES(3,'ccc','女');

INSERT INTO student VALUES(4,'ddd',DEFAULT);

INSERT INTO student(id,NAME) VALUES(5,'eee');

 

--外键约束--参照作用

CREATE TABLE stu (

id INT PRIMARY KEY,

sname VARCHAR(50) NOT NULL,

sex VARCHAR(10) DEFAULT '男'

);

 

--第1种方式

CREATE TABLE score (

id INT PRIMARY KEY 1,

sid INT,

coursename VARCHAR(20),

score INT,

CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES stu(sid)

);

 

 

--第2种方式

 

ALTER TABLE stu ADD CONSTRAINT fk_score_sid FOREIGN KEY

(sid) REFERENCES stu(id);

 

--第3种方式

--手动设置外键

 

--连接查询

--无重复

SELECT * FROM t1 UNION SELECT * FROM t2;

--有重复

SELECT * FROM t1 UNION ALL SELECT * FROM t2;

 

--笛卡尔乘积

--t1表与t2表联合查询，会产生集合运算中的笛卡尔乘积

SELECT * FROM t1,t2

 

--查询员工所在部门名称

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

 

 

--只输出员工姓名，部门编号，部门名称

SELECT enname,e.deptno,dname  FROM emp e,dept d WHERE e.deptno = d.deptno;

 

--查询10,20号部门的员工姓名，部门编号，部门名称

SELECT ename ,e.depyno,dname  FROM emp e, dept d WHERE e.deptno = d.deptno AND e.deptno IN(10,20);

 

--查询学生的学号，课程号，成绩

SELECT studentno,courseno,score FROM scores

 

--查询学生的的1学号，学生姓名，课程号，成绩

SELECT sc.studengtno,studentname,courseno,score FROM scores sc , course co

WHERE sc.studentno = st.studentno

 

--查询学生的学号，课程号，课程名称，成绩

SELECT studentno,sc.courseno,coursename,score  FROM scores sc,course co

WHERE sc.courseno = co.courseno

 

 

--查询学生的学号，学生姓名，课程号，课程名称，成绩

SELECT scores.studentno,studentname,scores.courseno,coursename,score  FROM scores ,studentinfo,course

WHERE scores.studentno = studentinfo.studentno AND scores.courseno = course.courseno

 

 

--内连接

--只输出员工姓名，部门编号，部门名称

SELECT ename,e.deptno,dname  FROM emp e, dept dwhere e.deptno = d.deptno

--上述内连接不是标准的内连接语言，是属于方言

SELECT ename,e.deptno,dname  FROM emp JOIN dept ON dept.deptno = emp.deptno

 

 

 

 

--外连接 OUTER JOIN

--右外连接LEFT OUTER JOIN

--左外连接RIGHT OUTER JOIN

 

--查询所有部门，员工信息，包含所有部门

SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno

--左外连接是在内连接的基础上加上左表中与右表中不匹配的记录

 

SELECT * FROM emp RIGHT OUTER JOIN dept ON dept.deptno = emp.deptno

--右外连接是在内连接的基础上加上右表中与左表中不匹配的记录

 

--子查询

--查询工资高于JONES的员工。

--1.先查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES'

 

--2.查询工资高于上一步查出来的结果(2975)的员工

SELECT * FROM emp WHERE sal>2975;

 

--合并

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES')

 

--查询与SCOTT同一个部门的员工。

--1.查询SCOTT所在的部门

SELECT deptno FROM emp WHERE ename='SCOTT'

 

--2.查询部门为上一步的结果(20)的员工

 

SELECT * FROM emp WHERE deptno=20

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

 

--工资高于30号部门所有人的员工信息

--1.查询30号部门的最高工资

SELECT MAX(sal) FROM emp WHERE deptno=30

--2.查询工资高于上一步结果的员工

SELECT * FROM emp WHERE sal>2850;

SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

--第二种方式

所有ALL(表示高于MAX),任意一个ANY(高于MIN)

 

--工资高于30号部门任意其中一个人的员工信息

SELECT * FROM emp WHERE sal>(SELECT MIN(sal) FROM emp WHERE deptno=30);

SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30);

 

--查询工作和工资与MARTIN（马丁）完全相同的员工信息

--1.查询MARTIN的工作

SELECT job FROM emp WHERE ename='MARTIN'

--2.查询MARTIN的工资

SELECT sal FROM emp WHERE ename='MARTIN'

--3.查询与上述结果相同的员工

SELECT * FROM emp WHERE job='SALESMAN' AND sal=1250.00

 

SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='MARTIN') AND sal=(SELECT sal FROM emp WHERE ename='MARTIN')

 

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN');

 

--有2个以上直接下属的员工信息

 

--1.分组查询mgr出现的次数

SELECT mgr,COUNT(mgr) FROM emp GROUP BY mgr;

--2.筛选出上述结果中大于2的

SELECT mgr,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr)>2;

 

--3.只显示上述结果中的第一列

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2;

 

--4.查询上述结果中的员工编号的员工信息

SELECT * FROM emp WHERE empno IN (7698,7839);

SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2);

 

--查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

--SELECT ename,sal,dname,loc FROM emp,dept WHERE emp.deptno=dept.deptno AND empno='7788'

 

--自连接

--求7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno,e1.ename,e1.mgr,e2.ename FROM emp e1,emp e2 WHERE e1.empno='7369'

AND e1.mgr=e2.empno;

--使用子查询

SELECT e1.empno,e1.ename,e1.mgr,(SELECT ename FROM emp WHERE empno=e1.mgr)

AS 经理姓名 FROM emp e1 WHERE e1.empno='7369'