当前位置：首页 » 《休闲阅读》 » 正文

cgb2108-day04_cgblpx的博客

6 人参与  2022年02月12日 15:24  分类 : 《休闲阅读》  评论

点击全文阅读


文章目录

    • 一,索引
      • --1,概述
      • --2,测试
    • 二,视图
      • --1,概述
      • --2,测试
    • 三,多表联查
      • --1,准备数据
      • --2,三种方式
      • --3,测试
    • 四,作业

一,索引

–1,概述

为了提高查询效率. 索引会单独生成一张表,要合理的使用索引.

分类:
1,单值索引: 一个索引只包含一个字段
2,唯一索引: 一个索引只包含一个字段,但字段的值不能重复
3,复合索引: 一个索引可以包含着多个字段
使用步骤:
1,创建索引(经常按照指定字段查询) + 使用索引

–2,测试

#1.索引:好处是:提高查询效率 坏处是:索引需要单独的一张表
#1.1 查看索引:show index from 表名
SHOW INDEX FROM emp #主键自带索引
#1.2 创建单值索引:一个索引只包含一个字段
CREATE INDEX ename_index ON emp(ename)
#1.3 创建唯一索引:一个索引只包含一个字段,索引列值不能重复
#create unique index uni_index on emp(job)#失败,因为job的值有重复的
CREATE UNIQUE INDEX uni_index ON emp(sal)
#1.4 创建复合索引:一个索引包含着多个字段,遵循最左特性
CREATE INDEX fuhe_index ON emp(job,hiredate,comm)
#1.5 使用索引:背后的故事,按照索引列去查
SELECT * FROM emp WHERE ename='jack' #使用单值索引
SELECT * FROM emp WHERE sal=10000 #使用唯一索引
#使用复合索引,遵循最左特性
EXPLAIN SELECT * FROM emp WHERE job='总监' #生效
EXPLAIN SELECT * FROM emp WHERE job='总监'AND hiredate='2019-1-1'#生效
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1' #失效	
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1'AND comm=100#失效 
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1' AND job='总监'#生效
#1.6 查询SQL的执行计划/性能(看看用没用索引)
EXPLAIN #possible_keys用到的索引
SELECT * FROM emp WHERE ename='jack'
EXPLAIN
SELECT * FROM emp WHERE sal=10000
#1.7 删除索引
ALTER TABLE emp DROP INDEX fuhe_index

二,视图

–1,概述

和索引一样,都是对数据库优化的有效方案.
特点: 1, 可以把视图当做表来使用 2,视图里存的数据是 SQL查询到的结果 3,SQL无法优化,要合理的使用视图

–2,测试

#2.视图:缓存了SQL语句的执行结果,当做表来用--了解
#好处是:
  #1.简化了查询的SQL(相同的SQL需求不必再写SQL了,直接查视图)
  #2.视图可以被共享,视图屏蔽了真实业务表的复杂性
#坏处是:视图一旦创建,SQL无法被优化
#2.1 创建视图
#语法:create view 视图名 as 查询的SQL语句
CREATE VIEW name_view AS 
	SELECT * FROM emp WHERE ename LIKE '%a%'
#2.2 使用视图
SELECT * FROM name_view

三,多表联查

–1,准备数据

CREATE TABLE courses
(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL,
PRIMARY KEY (cno)
)

CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno)
);
CREATE TABLE students(
sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)
);
CREATE TABLE teachers(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
)

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

–2,三种方式

1, 笛卡尔积: 语法 select * from 表名1,表名2,表名3
2, 连接查询
3, 子查询

–3,测试

#多表联查:
#方式1:笛卡尔积,通过逗号连接表名
#练习1:查询部门编号是1的部门信息和员工信息
SELECT * FROM dept,emp
    #表名.字段名=表名.字段名
WHERE dept.deptno=emp.deptno#描述两个表的关系
       AND dept.deptno=1#查deptno=1的数据
#练习2:查询员工姓名叫jack的部门信息      
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno #描述了两张表的关系
   AND emp.ename='jack' #业务条件      
#练习3:查询岗位是总监所在的部门信息    
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
      AND emp.job='总监' #业务条件   
#方式2:连接查询,分为三种:
#内连接inner join:取两张表的交集
#左连接left join:取左表的所有和右表满足条件的
#右连接right join:取右表的所有和左表满足条件的
#练习1:列出java开发部 部门下的所有员工的信息
#笛卡尔积方式
SELECT emp.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
	AND dept.dname='java开发部' #业务条件
#连接查询方式
SELECT emp.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了两张表的关系
WHERE dept.dname='java开发部' #业务条件
#练习2:查询岗位是总监所在的部门信息  
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了两张表的关系
WHERE emp.job='总监' #业务条件
#练习3:查询员工姓名叫jack的部门信息  
SELECT * FROM emp JOIN dept #只写join是inner join的简写形式
ON emp.deptno=dept.deptno#描述了两张表的关系
WHERE emp.ename='jack' #业务条件
#三种连接查询:inner join/left join/right join
SELECT * FROM dept INNER JOIN emp #可以简写成join
#取两张表都满足条件后的交集
ON emp.deptno=dept.deptno

SELECT * FROM dept LEFT JOIN emp
#取左表的所有,右边满足条件的取到不满足条件的用null填充 
ON dept.deptno=emp.deptno

SELECT * FROM emp RIGHT JOIN dept
#取右表的所有,左边满足条件的取到不满足条件的用null填充 
ON dept.deptno=emp.deptno

#综合练习1:查询陈冰老师能讲解的课程的名称(teachers/courses)
#笛卡尔积方式
SELECT courses.cname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述两张表的关系
      AND teachers.tname='陈冰' #业务条件
#连接查询方式
SELECT courses.cname FROM teachers INNER JOIN courses
ON teachers.tno=courses.tno#描述两张表的关系
      WHERE teachers.tname='陈冰' #业务条件
#综合练习2:查询学员李军的总得分(students/scores)
#笛卡尔积方式
SELECT SUM(degree) FROM students s1,scores s2
WHERE s1.sno=s2.sno #描述两张表的关系
     AND s1.sname='李军' #业务条件
#连接查询方式
SELECT SUM(degree) FROM students s1 INNER JOIN scores s2
ON s1.sno=s2.sno #描述两张表的关系
WHERE s1.sname='李军' #业务条件

#方式3:子查询/嵌套查询:把上次的查询结果作为这次的查询条件
#练习1:查询学员李军的总得分(students/scores)
#先查询学生表,根据学员名字查编号
SELECT sno FROM students WHERE sname='李军'#101
#再根据编号查得分表里的分数
SELECT SUM(degree) FROM scores WHERE sno=101
#子查询
SELECT SUM(degree) FROM scores WHERE sno=(
  SELECT sno FROM students WHERE sname='李军'
)
#练习2:查询陈冰老师能讲解的课程的名称(teachers/courses)
#先查老师表,根据名字查编号
SELECT tno FROM teachers WHERE tname='陈冰' #831
#再根据编号查课程名称
SELECT cname FROM courses WHERE tno=831
#子查询
SELECT cname FROM courses WHERE tno<(
  SELECT tno FROM teachers WHERE tname='陈冰'
)

四,作业

#SQL的练习:
#练习1:查询部门编号是1的员工姓名
#练习2:查询员工姓名叫jack的部门信息
#练习3:查询岗位是总监所在的部门信息
#练习4:列出java开发部 部门下的所有员工的信息
#练习5:查询部门地址在北京和广州的员工信息


点击全文阅读


本文链接：http://zhangshiyu.com/post/34610.html

索引  查询  视图  
<< 上一篇 下一篇 >>

  • 评论（0）
  • 赞助本站

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

最新文章

  • 完整版《我，SSR级歌手，开嗓唱哭所有人》免费阅读
  • 《保护妹妹的我，在学院成了天才》小说大结局免费试读 钟元罗晓媛讲解员小说
  • 习惯宠你主角苏晚季律师林妍小说完整版全文在线阅读
  • 已完结小说《欧皇入禁地，队友白月魁》全章节在线阅读
  • 男女主人公叫胡三的小说免费资源
  • 总在爹地总凶妈咪主角江小白陆梦颜顾承衍小说完整版全文在线阅读
  • 《兵王之战神归来》全集免费在线阅读（慕晴雪秦天）
  • 仙尊修真在校园最新章节，仙尊修真在校园免费阅读
  • 穿越七零：开局被卖给全村最富大佬小说阅读，穿越七零：开局被卖给全村最富大佬完整版
  • 楚帝楚南天邢贵妃小说《被流放后，全国都在求我回都当皇帝》在线阅读
  • 《亮剑：为救新一团，我以一敌百》全集免费在线阅读（陆风士兵支那人）
  • 已完结小说《被流放后，全国都在求我回都当皇帝》全章节在线阅读

    关于我们 | 我要投稿 | 免责申明

    Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1