目录
查看索引
MySQL连接分为内连接、外连接、自连接
内连接
INNER JOIN称为内连接
连接查询就是,要查询的数据分布在不同的表中,为了一次将不同表中的数据查询出来,我们需要使用连接查询,查询几张表中有关系的记录
语法及练习:
SELECT 别名1.*/列名,别名2*/列名 FROM 表1 别名1 INNER JOIN 表2 别名2 ON 连接条件(关联的条件) -- 查询EMP表中,员工姓名(ename),职位(job),工资(sal),以及dept表中,部门名称(dname),部门地址(loc) SELECT e.ename,e.job,e.sal,d.dname,d.loc FROM emp as e INNER JOIN dept as d ON e.deptno = d.deptno; -- 查询emp表中,员工姓名不包含字母K,员工的姓名ename,职位job,工资sal,以及dept表中全部列数据 SELECT e.ename,e.job,e.sal,d.* FROM emp e INNER JOIN dept d ON e.deptno = d.deptno AND NOT e.name LIKE '%K%'; -- 查询cou01表中,课程编号(cno),课程名称cname以及sco01表中,学员编号sno,课程分数score,要求课程分数在70-90之间 SELECT c.cno,c.cname,s.sno,s.score FROM cou01 c INNER JOIN sno01 s ON c.cno = S.cno AND s.score BETWEEN 70 AND 90; -- 查询cou01表中,课程编号(cno),课程名称cname以及sco01表中,学员编号sno,课程分数score,要求课程分数在70-90之间,根据课程分数降序排序 SELECT c.cno,c.cname,s.sno,s.score FROM cou01 c INNER JOIN sno01 s ON c.cno = s.cno AND s.score BETWEEN 70 AND 90 ORDER BY s.score DESC; -- 查询emp表中,员工姓名,职位,工资,以及salgrade表中,工资等级grade,最低工资losal,最高工资hisal SELECT e.ename 员工姓名,e.job 职位,e.sal 工资,s.grade 工资等级,s.losal 最低工资,s.hisal 最高工资 FROM EMP e INNER JOIN SALGRADE s ON e.sal BETWEEN s.LOSAL AND s.HISAL; -- 查询stu01表中全部的列,以及cou01表中全部的列以及sco01表中课程分数(score) SELECT t.*,c.*,s.score FROM sco01 s INNER JOIN cou01 c ON s.cno = c.cno INNER JOIN stu01 t ON s.sno = t.sno;
外连接
- 外连接查询就是查询一张表中的全部数据,以及另外一张表中,与之相关的部分数据
- 左外连接查询,以左边表的全部数据查询以及右边表与之有关系的部分数据
- 如果左表的某条记录在右表中没有与之匹配的记录,则右表相关字段显示空值
语法及例题:
SELECT 别名1.*/列名,别名2.*/列名2
FROM 左表 别名1 LEFT JOIN 右表 别名2
ON 连接条件;
-- 查询dept表中,所有部门的信息以及,该部门下 员工的编号,姓名,职位,入职时间(hiredate),根据入职时间升序排列(外连接)
SELECT d.*,e.EMPNO,e.ename,e.job,e.hiredate
FROM dept d LEFT JOIN emp e
ON e.deptno = d.deptno
ORDER BY e.hiredate ASC;
-- 查询emp表中,员工姓名不包含字母k的,所有的员工编号、姓名、职位、工资以及该员工所属的部门名称(dname),部门地址(loc),根据工资降序排列
SELECT e.empno,e.name,e.job,e.sal,d.dname,d.loc
FROM emp e LEFT JOIN dept d
ON e.deptno = d.deptno
WHERE NOT e.ename LIKE '%K%'
ORDER BY e.sal DESC;
右外连接
查询右边表的全部数据,以及左边表的与之有关系的部分数据
语法及例题:
SELECT 别名1.*/列名,别名2.*/列名2
FROM 左表 别名1 RIGHT JOIN 右表 别名2
ON 连接条件;
-- 查询dept表中,所有部门的信息以及,该部门下 员工的编号,姓名,职位,入职时间(hiredate),根据入职时间升序排列(外连接)
SELECT d.*,e.EMPNO,e.ename,e.job,e.hiredate
FROM dept d RIGHT JOIN emp e
ON e.deptno = d.deptno;
-- 查询stu01表中所有学员的,编号(sno),姓名(sname),年龄(age),地址(address)以及该学员的分数(score),根据学院的编号降序排列
-- 右外连接
SELECT t.sno,t.sname,t.age,t.address,s.score
FROM sco01 s RIGHT JOIN stu01 t
ON s.sno = t.sno
ORDER BY t.sno desc;
-- 左外连接
SELECT t.sno,t.sname,t.age,t.address,s.score
FROM stu01 t LEFT JOIN sco01 s
ON s.sno = t.sno
ORDER BY t.sno desc;
自查询
SELECT 别名1.*/列名,别名2.*/列名
FROM 表名 别名1,表1 别名2
子查询
语法及例题:
-- 查询emp表中,工资大于平均工资的,员工的编号(empno),姓名(ename),职位(job),工资(sal)
SELECT empno,ename,job,sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
-- 查询student表中,和唐家三少是同一个班级(sclass)的,学员信息
#查询学员信息
#班级=唐家三少的班级
SELECT *
FROM student
WHERE sclass = (SELECT sclass FROM student WHERE sname='唐家三少');
-- 查询emp表中,和30号部门员工,工资相同的,员工信息
SELECT *
FROM emp
WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno <> 30;
视图
- 视图就是一个虚拟表,可以通过查询一张或者多张表的数据,可以使查询变得简单,提高工作效率
语法及例题:
CREATE VIEW 视图名称 AS 查询语句;
-- 创建一个视图,名称为view_emp,查询emp表中全部列的数据,作为视图的结果
CREATE VIEW view_emp
AS
SELECT * FROM emp;
-- 创建一个视图,名称为view_emp_dept,
-- 查询emp表中全部列数据以及dept表中部门名称(dname),部门地址(loc)
做为视图结果
CREATE view_emp_dept,
AS
SELECT e.*,d.dept,d.dname,d.loc
FROM emp e INNER JOIN dept d
WHERE e.deptno = d.deptno;
-- 创建一个视图,名称为view_stu_cou_sco,查询stu01表中全部列数据以及cou01表中全部列数据,以及sco01表中课程分数(score),做为视图结果
CREATE VIEW view_stu_cou_sco
AS
SELECT s.*,c.*,s.score
FROM sco01 s
INNER JOIN cou01 c ON s.cno = c.cno
INNER JOIN stu01 t ON s.cno = t.sno;
-- 查询视图view_emp,员工姓名,职位,工资,奖金,根据工资降序排列
SELECT ename,job,sal,comm
FROM view_emp
ORDER BY sal DESC;
-- 查询视图view_stu_cou_sco中,学员姓名(sname),地址(address),课程名称(cname),课程分数(score),根据学员姓名升序排列
SELECT sname,address,cname,score
FROM view_stu_cou_sco
ORDER BY sname ASC;
删除视图
DROP VIEW 视图名称;
SHOW TABLES;
-- 检查有无删除
索引
索引是建立在列上的数据库对象
用于提高查询速度,索引是一种提高查询效率的机制
创建索引
CREATE INDEX 索引名称 ON 表名(列名);
查看索引
被主键约束所修饰的列自带索引
被唯一约束所修饰的列自带索引
SHOW INDEX FROM 表名;
删除索引
DROP INDEX 索引名称 ON 表名;
数据备份和恢复
数据备份
mysqldump -u 用户名 -p 仓库名 > 备份路径 -- 对test表所在的数据库进行备份 mysqldump -u root -p test > D;/文件名称/文件名.sql
数据恢复
mysql -u 用户名 -p 仓库名称 < 本地sql文件路径 -- 将d盘中.sql文件恢复到test数据库中 mysql -u root -p test < D:文件名称/文件名.sql