肇庆网站建设cz0758,国外做免费的视频网站,外贸人自己搭建外贸网站wordpress,佛山规划建设局网站头歌 实验一 关系数据库标准语言SQL
制作不易#xff01;点个关注呗#xff01;为大家创造更多的价值#xff01; 目录 头歌 实验一 关系数据库标准语言SQL**制作不易#xff01;点个关注呗#xff01;为大家创造更多的价值#xff01;** 第一关#xff1a;创建数据库第…头歌 实验一 关系数据库标准语言SQL
制作不易点个关注呗为大家创造更多的价值 目录 头歌 实验一 关系数据库标准语言SQL**制作不易点个关注呗为大家创造更多的价值** 第一关创建数据库第二关创建表第三关插入数据第四关数据查询-基础查询第五关数据查询-条件查询第六关数据查询-连接查询第七关数据查询-子查询第八关修改数据第九关创建视图第十关定义索引**制作不易点个关注呗为大家创造更多的价值** 第一关创建数据库
任务描述 本关任务建立数据库 为了完成本关任务你需要掌握 如何创建数据库显示已经建立的数据库 相关知识 创建数据库 创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理。 命令格式
CREATE DATABASE database_name;创建完数据库之后我们可以通过show databases;命令查看MySQL中已存在的所有数据库。
任务要求 建立demo数据库 并显示所有数据库
开始你的任务吧祝你成功 代码如下 #代码开始
CREATE DATABASE demo;#代码结束
show databases;第二关创建表
任务描述 本关任务建立数据表
相关知识 选择数据库 命令格式 use 数据库名; 选择数据库为当前数据库
创建数据表 创建数据表的命令格式
CREATE TABLE 表名
( 列名1 数据类型 字段属性,列名2 数据类型 字段属性,…列名n 数据类型 字段属性
);常用的数据类型如下
枚举型和集合型 在字段类型选择的时候
对于数值类型ZEROFILL 表示前导零填充数值类型值以达到列的显示宽度。 auto_increment表示对于数值型字段自动增加 not null表示不允许该字段值为null 显示数据表结构
describe 数据表名查看数据表的基本结构
任务要求 设有一个demo数据库包括S,P,J,SPJ四个关系模式 S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QTY) 供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成 零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成 工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成 供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成表示某供应商 供应某种零件 给某工程项目的数量为QTY。
demo数据库已经创建好请按下面步骤完成任务。
1.切换到demo数据库 2.分别创建s、p、j和spj数据表 3.查看s、p、j和spj数据表的详细结构 注意表名统一用小写。
数据表结构如下 s表 p表 j表
spj表
开始你的任务吧祝你成功 代码如下
#代码开始#1. 切换到demo数据库use demo;#2. 分别创建s、p、j和spj数据表
/*清除表结构*/
-- DROP TABLE j, p,s,spj;
/**创建s表**/
CREATE TABLE s
(
sno CHAR(2) NULL ,
sname VARCHAR(10) ,
status INT(11) NULL ,
city VARCHAR(10)
);
-- /**创建p表**/
CREATE TABLE p
(
pno CHAR(2) null ,
pname VARCHAR(10),
color CHAR(1),
weight int
);
-- /**创建J表**/
CREATE TABLE j
(jno CHAR(2) null ,jname VARCHAR(10),city VARCHAR(10)
);
-- /*创建spj表*/
CREATE TABLE spj
(
sno CHAR(2) null ,
pno CHAR(2),
jno CHAR(2),
qty INT
);#3. 查看s、p、j和spj数据表的详细结构describe s;describe p;describe j;describe spj;#代码结束第三关插入数据
任务描述 本关任务在s、p、j和spj数据表中插入数据。
相关知识 在数据表中插入一条记录对指定字段赋值
INSERT INTO 表名 (字段1[,字段2…])
VALUES (表达式1[,表达式2…])向指定的数据表插入一条记录并用指定的表达式对各个字段赋值VALUES短语中各个表达式应该与数据表结构的字段顺序一一对应。 例如
INSERT INTO spj(sno,jno,pno,qty) VALUES(S1,J1,P1,200);在数据表中插入一条记录对所有字段赋值
INSERT INTO 表名 VALUES (表达式1[,表达式2…])向指定的数据表插入一条记录并用指定的表达式对所有字段赋值VALUES短语中各个表达式应该与数据表结构的字段顺序一一对应。
例如
INSERT INTO spj VALUES(S1,J1,P1,200);
在数据表中插入多条记录
INSERT INTO 表名 (字段1[,字段2…])
VALUES (表达式11[,表达式12…])(表达式21[,表达式22…])(表达式31[,表达式32…])……例如
INSERT INTO spj VALUES(S1,J1,P1,200),(S1,P1,J3,100),(S1,P1,J4,700);显示数据表的数据 插入数据之后我们可以通过select * from 数据表;命令查看该数据表的所有数据。
例如
查看s表所有数据
select * from s;任务要求 分别在s、p、j和spj数据表中插入数据 分别显示s、p、j和spj数据表的所有数据 s、p、j和spj数据表的原始数据如下所示:
(‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始
#插入s表数据
INSERT INTO s VALUES(S1,精益,20,天津);
INSERT INTO s VALUES(S2,盛锡,10,北京);
INSERT INTO s VALUES(S3,东方红,30,北京);
INSERT INTO s VALUES(S4,丰泰盛,20,天津);
INSERT INTO s VALUES(S5,为民,30,上海);#插入p表数据INSERT INTO p VALUES(P1,螺母,红,12);
INSERT INTO p VALUES(P2,螺栓,绿,17);
INSERT INTO p VALUES(P3,螺丝刀,蓝,14);
INSERT INTO p VALUES(P4,螺丝刀,红,14);
INSERT INTO p VALUES(P5,凸轮,蓝,40);
INSERT INTO p VALUES(P6,齿轮,红,30);#插入j表数据
INSERT INTO j VALUES(J1,三建,北京);
INSERT INTO j VALUES(J2,一汽,长春);
INSERT INTO j VALUES(J3,弹簧厂,天津);
INSERT INTO j VALUES(J4,造船厂,天津);
INSERT INTO j VALUES(J5,机车厂,唐山);
INSERT INTO j VALUES(J6,无线电厂,常州);
INSERT INTO j VALUES(J7,半导体厂,南京);
#插入spj表数据
INSERT INTO spj VALUES(S1,P1,J1,200);
INSERT INTO spj VALUES(S1,P1,J3,100);
INSERT INTO spj VALUES(S1,P1,J4,700);
INSERT INTO spj VALUES(S1,P2,J2,100);
INSERT INTO spj VALUES(S2,P3,J1,400);
INSERT INTO spj VALUES(S2,P3,J2,200);
INSERT INTO spj VALUES(S2,P3,J4,500);
INSERT INTO spj VALUES(S2,P3,J5,400);
INSERT INTO spj VALUES(S2,P5,J1,400);
INSERT INTO spj VALUES(S2,P5,J2,100);
INSERT INTO spj VALUES(S3,P1,J1,200);
INSERT INTO spj VALUES(S3,P3,J1,200);
INSERT INTO spj VALUES(S4,P5,J1,100);
INSERT INTO spj VALUES(S4,P6,J3,300);
INSERT INTO spj VALUES(S4,P6,J4,200);
INSERT INTO spj VALUES(S5,P2,J4,100);
INSERT INTO spj VALUES(S5,P3,J1,200);
INSERT INTO spj VALUES(S5,P6,J2,200);
INSERT INTO spj VALUES(S5,P6,J4,500);
#分别查询s、p、j和spj表的所有数据
select * from s;
select * from p;
select * from j;
select * from spj;#代码结束第四关数据查询-基础查询
相关知识 基础查询 *查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据例如 查询name、age两列
select name,age from stu;查询所有列的数据列名的列表可以使用*替代
select * from stu;上面语句中的不建议大家使用因为在这写不方便我们阅读sql语句。我们写字段列表的话可以提高程序的可读性。 而在上课期间为了简约课程的时间老师很多地方都会写*。 查询地址信息
select address from stu;执行上面语句结果如下
从上面的结果我们可以看到有重复的数据我们也可以使用 distinct 关键字去重重复数据。
*去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;*去除重复记录
select distinct address from stu;*起别名
SELECT 字段列表 AS 别名 FROM 表名;
AS关键字也可以省略查询姓名、数学成绩、英语成绩并通过as给math和english起别名as关键字可以省略
select name,math as 数学成绩,english as 英文成绩 from stu;
select name,math 数学成绩,english 英文成绩 from stu;排序查询 语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;上述语句中的排序方式有两种分别是
ASC 升序排列 默认值 DESC 降序排列 任务要求 请用SQL语句完成下面题目的查询。
查询所有供应商的姓名和所在城市按供应商姓名升序排列。 查询所有零件的名称、颜色、重量按零件名称升序排列。 注意数据大小写要保持一致。
原始数据如下所示
s表 (‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) p表 (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) j表 (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) spj表 (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始#1. 查询所有供应商的姓名和所在城市按供应商姓名升序排列。
select s.sname,s.city from s order by s.sname;#2. 查询所有零件的名称、颜色、重量按零件名称升序排列。
select p.pname,p.color,p.weight from p order by p.pname;#代码结束第五关数据查询-条件查询
任务描述 本关任务按题目要求完成数据表的查询。
相关知识 条件查询 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;条件列表可以使用以下运算符 image-20210722190508272
模糊查询 模糊查询使用like关键字可以使用通配符进行占位:
_ : 代表单个任意字符 % : 代表任意个数字符 例如 查询姓’马’的学员信息
select * from stu where name like 马%;查询第二个字是’花’的学员信息
select * from stu where name like _花%;查询名字中包含 ‘德’ 的学员信息
select * from stu where name like %德%;注意如果有多个排序条件当前边的条件值一样时才会根据第二条件进行排序
任务要求 请用SQL语句完成下面题目的查询。
1、查询供应工程J1零件的供应商号码(去除重复按供应商号码升序排列)。 2、查询供应工程J1零件P1的供应商号码按供应商号码升序排列。 3、查询使用供应商S1所供应零件的工程号码按工程号码升序排列。 注意数据大小写要保持一致。
原始数据如下所示
s表 (‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) p表 (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) j表 (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) spj表 (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始
#1. 查询供应工程J1零件的供应商号码(去除重复按供应商号码升序排列)。SELECT distinct spj.sno FROM spj WHERE jnoJ1 ; #2. 查询供应工程J1零件P1的供应商号码按供应商号码升序排列。SELECT spj.sno FROM spj WHERE spj.jnoJ1 AND spj.pno P1;#3. 查询使用供应商S1所供应零件的工程号码按工程号码升序排列。SELECT spj.jno FROM spj WHERE spj.snoS1 order by spj.jno;#代码结束
第六关数据查询-连接查询
任务描述 本关任务按题目要求完成数据表的查询。
相关知识 多表查询 多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。我们通过具体的示例来进行讲解。
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;# 创建部门表CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20));# 创建员工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键关联部门表(部门表的主键));-- 添加部门数据INSERT INTO dept (dNAME) VALUES (研发部),(市场部),(财务部),(销售部);-- 添加员工数据INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES(孙悟空,男,7200,2013-02-24,1),(猪八戒,男,3600,2010-12-02,2),(唐僧,男,9000,2008-08-08,2),(白骨精,女,5000,2015-10-07,3),(蜘蛛精,女,4500,2011-03-14,1),(小白龙,男,2500,2011-02-14,null); 执行下面的多表查询语句
select * from emp , dept; -- 从emp和dept表中查询所有的字段数据结果如下
从上面的结果我们看到有一些无效的数据如 孙悟空 这个员工属于1号部门但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据
select * from emp , dept where emp.dep_id dept.did;执行后结果如下
上面语句就是连接查询那么多表查询都有哪些呢
连接查询
内连接查询 相当于查询AB交集数据
外连接查询
左外连接查询 相当于查询A表所有数据和交集部门数据 右外连接查询 相当于查询B表所有数据和交集部分数据 子查询
内连接查询 语法 -- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;例如
隐式内连接
SELECT * FROM emp, dept
WHERE emp.dep_id dept.did;执行上述语句结果如下
查询 emp的 name gender和dept表的dname
SELECTemp. NAME,emp.gender,dept.dname
FROMemp,dept
WHEREemp.dep_id dept.did;执行语句结果如下
上面语句中使用表名指定字段所属有点麻烦sql也支持给表指别名上述语句可以改进为
SELECTt1. NAME,t1.gender,t2.dname
FROMemp t1,dept t2
WHEREt1.dep_id t2.did;显式内连接
select * from emp inner join dept on emp.dep_id dept.did;
-- 上面语句中的inner可以省略可以书写为如下语句
select * from emp join dept on emp.dep_id dept.did;执行结果如下
外连接查询 语法
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;– 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;左外连接相当于查询A表所有数据和交集部分数据 右外连接相当于查询B表所有数据和交集部分数据 案例
查询emp表所有数据和对应的部门信息左外连接
select * from emp left join dept on emp.dep_id dept.did;执行语句结果如下
结果显示查询到了左表emp中所有的数据及两张表能关联的数据。
查询dept表所有数据和对应的员工信息右外连接
select * from emp right join dept on emp.dep_id dept.did;执行语句结果如下
结果显示查询到了右表dept中所有的数据及两张表能关联的数据。
要查询出部门表中所有的数据也可以通过左外连接实现只需要将两个表的位置进行互换
select * from dept left join emp on emp.dep_id dept.did;任务要求 请用SQL语句完成下面题目的查询。
查询工程项目J2使用的各种零件的名称及其数量按零件名称升序排列。 查询上海厂商供应的所有零件号码去除重复按零件号码升序排列。 查询使用上海产的零件的工程名称按工程名称升序排列,去除重复数据。 注意数据大小写要保持一致。
原始数据如下所示
s表 (‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) p表 (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) j表 (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) spj表 (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始#1. 查询工程项目J2使用的各种零件的名称及其数量按零件名称升序排列。
select p.pname,spj.qty from p,spj where p.pnospj.pno and spj.jnoJ2 order by p.pname;#2. 查询上海厂商供应的所有零件号码去除重复按零件号码升序排列。
SELECT spj.pno FROM spj WHERE spj.sno IN(SELECT s.SNO FROM s WHERE s.city上海) GROUP BY spj.pno;#3. 查询使用上海产的零件的工程名称按工程名称升序排列,去除重复数据。SELECT j.jname FROM j WHERE j.jno IN(SELECT spj.jno FROM spj WHERE spj.sno IN(SELECT s.sno FROM s WHERE s.city上海)) order by j.jname#代码结束第七关数据查询-子查询
相关知识 子查询 概念
查询中嵌套查询称嵌套查询为子查询。
什么是查询中嵌套查询呢我们通过一个例子来看
需求查询工资高于猪八戒的员工信息。
来实现这个需求我们就可以通过二步实现第一步先查询出来 猪八戒的工资
select salary from emp where name 猪八戒第二步查询工资高于猪八戒的员工信息
select * from emp where salary 3600;第二步中的3600可以通过第一步的sql查询出来所以将3600用第一步的sql语句进行替换
select * from emp where salary (select salary from emp where name 猪八戒);这就是查询语句中嵌套查询语句。
子查询根据查询结果不同作用不同
子查询语句结果是单行单列子查询语句作为条件值使用 ! 等进行条件判断 子查询语句结果是多行单列子查询语句作为条件值使用 in 等关键字进行条件判断 子查询语句结果是多行多列子查询语句作为虚拟表 案例
*查询 财务部 和 市场部 所有的员工信息
-- 查询 财务部 或者 市场部 所有的员工的部门did
select did from dept where dname 财务部 or dname 市场部;
select * from emp where dep_id in (select did from dept where dname 财务部 or dname 市场部);查询入职日期是 2011-11-11 之后的员工信息和部门信息
-- 查询入职日期是 2011-11-11 之后的员工信息
select * from emp where join_date 2011-11-11 ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date 2011-11-11 ) t1, dept where t1.dep_id dept.did;任务要求 请用SQL语句完成下面题目的查询。 查询供应工程J1零件为红色的供应商号码按供应商号码升序排列。 查询没有使用天津供应商生产的红色零件的工程号码去除重复数据按工程号码升序排列。 注意数据大小写要保持一致。
原始数据如下所示
s表 (‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) p表 (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) j表 (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) spj表 (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始#1. 查询供应工程J1零件为红色的供应商号码按供应商号码升序排列。SELECT spj.sno FROM spj WHERE spj.jnoJ1 and spj.pno in (SELECT p.pno from p where p.color红);#2. 查询没有使用天津供应商生产的红色零件的工程号码去除重复数据按工程号码升序排列。
#找到天津生产的零件
SELECT DISTINCT jno FROM spj WHERE jno NOT IN (SELECT DISTINCT jno FROM spj NATURAL JOIN p NATURAL JOIN s WHERE city天津 AND color红) order by jno asc;#代码结束
第八关修改数据
相关知识 更新表中指定的内容 通过前几关的学习我们已经掌握了INSERT语句的操作。同样UPDATE操作也很简单只要记住UPDATE语句三要素就能轻松掌握它们分别是:
**需要更新的表table名 需要更新的字段column名和它的新内容value 决定更新哪一条内容value的过滤条件。 语法规则为
UPDATE 表名 SET 字段名1 内容1, 字段名2 内容2, 字段名3 内容3 WHERE 过滤条件;** 删除表中的指定行 从数据表中删除数据内容需要使用DELETE语句它需要WHERE语句来配合它来指定我们究竟应该删除哪些数据内容。
语法规则为 DELETE FROM 表名 WHERE 条件语句;
我们可以指定删除某一行的数据内容当然我们还可以指定删除很多行的数据内容区别就在于条件语句。
删除表中的所有行 删除表中的所有行就更简单了。如果需要删除表中所有的行只需要省略WHERE语句即可。
语法规则为DELETE FROM 表名;
任务要求 请用SQL语句完成下面题目。
1、把全部红色零件的颜色改成蓝色。 2、由S5供给J4的零件P6改为由S3供应。 3、从供应商关系中删除供应商号是S2的记录并从供应情况关系中删除相应的记录。 4、请将(S2J6P4200)插入供应情况关系表。 数据修改完后请和原始数据进行比对以查看修改结果。
原始数据如下所示
s表 (‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) p表 (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) j表 (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) spj表 (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始
#1. 把全部红色零件的颜色改成蓝色。
UPDATE p SET p.color蓝 WHERE p.color红;#2. 由S5供给J4的零件P6改为由S3供应。
UPDATE spj SET spj.snoS3 WHERE spj.snoS5 AND spj.pnoP6 and spj.jnoJ4;#3. 从供应商关系中删除供应商号是S2的记录并从供应情况关系中删除相应的记录。DELETE FROM s WHERE s.snoS2;
DELETE FROM spj WHERE spj.snoS2;
#4. 请将(S2J6P4200)插入供应情况关系表。
INSERT INTO spj VALUE(S2,P4,J6,200);#代码结束#评测使用不要删除下面代码
select * from s;
select * from p;
select * from spj;
第九关创建视图
相关知识 聚合函数 概念
将一列数据作为一个整体进行纵向计算。
如何理解呢假设有如下表
现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。
聚合函数分类 聚合函数语法
SELECT 聚合函数名(列名) FROM 表;注意null 值不参与所有聚合函数运算
例如
统计班级一共有多少个学生
select count(id) from stu;
select count(english) from stu;上面语句根据某个字段进行统计如果该字段某一行的值为null的话将不会被统计。所以可以在count() 来实现。 表示所有字段数据一行中也不可能所有的数据都为null所以建议使用 count(*)
select count(*) from stu;查询数学成绩的最高分
select max(math) from stu;查询数学成绩的最低分
select min(math) from stu;查询数学成绩的总分
select sum(math) from stu;查询数学成绩的平均分
select avg(math) from stu;查询英语成绩的最低分
select min(english) from stu;分组查询 语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];注意分组之后查询的字段为聚合函数和分组字段查询其他字段无任何意义
例如
查询男同学和女同学各自的数学平均分
select sex, avg(math) from stu group by sex;注意分组之后查询的字段为聚合函数和分组字段查询其他字段无任何意义
select name, sex, avg(math) from stu group by sex; -- 这里查询name字段就没有任何意义查询男同学和女同学各自的数学平均分以及各自人数
select sex, avg(math),count(*) from stu group by sex;查询男同学和女同学各自的数学平均分以及各自人数要求分数低于70分的不参与分组
select sex, avg(math),count(*) from stu where math 70 group by sex;查询男同学和女同学各自的数学平均分以及各自人数要求分数低于70分的不参与分组分组之后人数大于2个的
select sex, avg(math),count(*) from stu where math 70 group by sex having count(*) 2;where 和 having 区别
执行时机不一样where 是分组之前进行限定不满足where条件则不参与分组而having是分组之后对结果进行过滤。
可判断的条件不一样where 不能对聚合函数进行判断having 可以。
定义视图 视图view是一种虚拟存在的表是一个逻辑表本身并不包含数据。通过视图可以展现基表用来创建视图的表的部分数据视图数据来自定义视图的查询表。
我们知道了视图的定义那么为什么要使用它呢
因为视图有如下优点①. 简单使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件对用户来说已经是过滤好的复合条件的结果集。②. 安全使用视图的用户只能访问他们被允许查询的结果集对表的权限管理并不能限制到某个行或列但是通过视图就可以简单的实现。③. 数据独立一旦视图的结构确定了可以屏蔽表结构变化对用户的影响源表增加列队视图没有影响源表修改列名则可以通过修改视图来解决不会造成对访问者的影响。
总而言之使用视图的大部分情况是为了保障数据安全性提高查询效率。
创建视图
CREATE [OR REPLACE] [ALGORITHM {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]参数说明
1、OR REPLACE表示替换已有视图。 ALGORITHM表示视图选择算法默认算法是UNDEFINED(未定义的) MySQL 自动选择要使用的算法 merge合并temptable临时表。 2、column_list可选参数指定视图中各个属性的名词默认情况下与select语句中查询的属性相同。 3、select_statement表示select语句。 4、[WITH [CASCADED | LOCAL] CHECK OPTION]表示视图在更新时保证在视图的权限范围之内cascade是默认值表示更新视图的时候要满足视图和表的相关条件local表示更新视图的时候要满足该视图定义的一个条件即可。
示例一
示例二 以上两个示例可以看出虽然两个视图的字段名不同但是数据是相同的因为两个视图引用的是同一个表中的数据并且as后的创建视图的语句也相同。
在实际开发中用户可以根据自己的需求通过视图的方式获取基本表中自己需要的数据这样既能满足用户的需求也不会破坏基本表原来的结构从而保证了基本表中数据的安全性。
操作视图 视图是逻辑表也就是说视图不是真实的表但操作视图和操作普通表的语法是一样的。
用户可以在视图中无条件地使用select语句查询数据。但使用insert、update和delete操作需要在创建视图时满足以下条件满足以下条件的视图称为可更新视图
1、from子句中只能引用有1个表真实表或可更新视图。 2、不能包含 with、distinct、group by、having、limit等子句。 3、不能使用复合查询即不能使用union、intersect、except等集合操作。 4、select子句的字段列表不能包含聚合、窗口函数、集合返回函数。
我们仍使用之前示例中的数据来操作视图
删除视图 若视图不再被需要我们可以将其删除且视图的删除并不影响源表中的数据。
删除视图的 SQL 如下
DROP VIEW view_name;示例
查看视图 查看视图是指查看数据库中已经存在的视图的定义 查看视图必须要有SHOW VIEW的权限
语法格式
DESCRIBE 视图名;
或者简写
DESC 视图名;任务要求 1、请为三建工程项目建立一个供应情况的视图(v_spj)包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。 2、查看视图(v_spj)信息。 针对视图(v_spj)完成下列查询 (1) 查询三建工程项目使用的各种零件代码及其总数量(total) 提示利用聚合函数sum()和分组查询实现总数量起别名total。 (2) 查询供应商S1的供应情况。
原始数据如下所示
s表 (‘S1’,‘精益’,20,‘天津’) (‘S2’,‘盛锡’,10,‘北京’) (‘S3’,‘东方红’,30,‘北京’) (‘S4’,‘丰泰盛’,20,‘天津’) (‘S5’,‘为民’,30,‘上海’) p表 (‘P1’,‘螺母’,‘红’,12) (‘P2’,‘螺栓’,‘绿’,17) (‘P3’,‘螺丝刀’,‘蓝’,14) (‘P4’,‘螺丝刀’,‘红’,14) (‘P5’,‘凸轮’,‘蓝’,40) (‘P6’,‘齿轮’,‘红’,30) j表 (‘J1’,‘三建’,‘北京’) (‘J2’,‘一汽’,‘长春’) (‘J3’,‘弹簧厂’,‘天津’) (‘J4’,‘造船厂’,‘天津’) (‘J5’,‘机车厂’,‘唐山’) (‘J6’,‘无线电厂’,‘常州’) (‘J7’,‘半导体厂’,‘南京’) spj表 (‘S1’,‘P1’,‘J1’,200) (‘S1’,‘P1’,‘J3’,100) (‘S1’,‘P1’,‘J4’,700) (‘S1’,‘P2’,‘J2’,100) (‘S2’,‘P3’,‘J1’,400) (‘S2’,‘P3’,‘J2’,200) (‘S2’,‘P3’,‘J4’,500) (‘S2’,‘P3’,‘J5’,400) (‘S2’,‘P5’,‘J1’,400) (‘S2’,‘P5’,‘J2’,100) (‘S3’,‘P1’,‘J1’,200) (‘S3’,‘P3’,‘J1’,200) (‘S4’,‘P5’,‘J1’,100) (‘S4’,‘P6’,‘J3’,300) (‘S4’,‘P6’,‘J4’,200) (‘S5’,‘P2’,‘J4’,100) (‘S5’,‘P3’,‘J1’,200) (‘S5’,‘P6’,‘J2’,200) (‘S5’,‘P6’,‘J4’,500) 开始你的任务吧祝你成功 代码如下
use demo;#代码开始
#1. 请为“三建”工程项目建立一个供应情况的视图(v_spj)包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
CREATE VIEW v_spj(sno,pno,qty) AS SELECT sno,pno,qty FROM spj WHERE jno(SELECT jno FROM j WHERE jname三建);#2. 查看视图(v_spj)信息。
desc v_spj;#针对视图(v_spj)完成下列查询
#(1) 查询“三建”工程项目使用的各种零件代码及其总数量(total)
#提示利用聚合函数sum()和分组查询实现总数量起别名total。SELECT pno,SUM(qty) total FROM v_spj GROUP BY pno;#(2) 查询供应商S1的供应情况。sELECT sno,pno,qty FROM v_spj WHERE snoS1;
#代码结束
第十关定义索引
任务描述 为了完成本关任务你需要掌握如下知识点
什么是索引 索引的分类 索引的定义 删除索引 查看索引 相关知识 什么是索引 官方介绍索引是帮助MySQL高效获取数据的数据结构。简单来讲数据库索引就像是书前面的目录能加快数据库的查询速度。
事实上索引是一种数据结构用于帮助我们在大量数据中快速定位到我们想要查找的数据。
但对于海量数据来说它的目录也是很大的不可能全部存储在内存中因此索引往往是存储在磁盘上的文件中可能存储在单独的索引文件中也可能和数据一起存储在数据文件中。
索引的分类 单列索引 一个索引只包含单个列但一个表中可以有多个单列索引。 这里不要搞混淆了
1、普通索引MySQL中基本索引类型没有什么限制允许在定义索引的列中插入重复值和空值纯粹为了查询数据更快一 点。 2、唯一索引索引列中的值必须是唯一的但是允许为空值。 3、主键索引是一种特殊的唯一索引不允许有空值。主键约束就是一个主键索引。 组合索引 指在多个字段上创建的索引在多个字段上创建的普通索引和唯一索引都是组合索引所谓组合实际是字段的组合对于多个字段上的唯一索引要求组合字段必须唯一。
索引的定义 根据创建时机的不同索引主要有两种创建方式
在创建表时同时创建普通索引 语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
(col_name column_definition,INDEX|KEY [index_name] [index_type] (col_name [(length)] [ASC | DESC])
)index_type:USING {BTREE | HASH}其中方括号[]中内容表示可选项竖线|表示两者选一。
col_name列名 column_definition列的具体定义这里省略了 INDEX|KEY表示使用INDEX和KEY都能创建普通索引因为在MySQL中INDEX和KEY是一样的。 [index_name]索引的名字是可选项如果没有写默认使用字段名作为索引的名称一般以idx_字段名作为前缀来命名 [index_type]索引类型表示索引的数据结构有两类BTREE和HASH如果没有指明默认是BTREE col_name [(length)] [ASC | DESC]col_name是要添加索引的列length表示要在类型为字符串的列的前length个字符构成的字符串上添加索引[ASC|DESC]表示升序还是降序方式存储索引默认是升序方式存储 例如
CREATE TABLE my_table
(
id INT(11) not NULL auto_increment,
name VARCHAR(20) NOT NULL,
address VARCHAR(20) NOT NULL,
PRIMARY KEY(id),
INDEX idx_name (name)
);该语句在创建my_table表时同时在字段name上创建了一个名为idx_name的普通索引。
创建表之后创建索引 (1) 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name (col_name [(length)] [ASC | DESC])例如
ALTER TABLE my_table ADD INDEX idx_address(address)该语句在my_table表的address字段上添加了名为idx_address的普通索引。
(2) 直接创建索引
CREATE INDEX index_name ON table(column[(length)] [ASC|DESC])例如
CREATE INDEX idx_name ON my_table(name)删除索引 删除索引的命令格式
DROP INDEX index_name ON table_name查看索引 查看索引的语法格式如下
SHOW INDEX FROM 表名 [ FROM 数据库名]语法说明如下 表名指定需要查看索引的数据表名。 数据库名指定需要查看索引的数据表所在的数据库可省略。比如SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。
任务要求 在spj表定义索引IDX_SPJ包括sno,pno,jno。 查看spj表索引定义。 开始你的任务吧祝你成功 代码如下
use demo;#代码开始# 1.在spj表定义索引IDX_SPJ包括sno,pno,jno。
ALTER TABLE spj ADD INDEX IDX_SPJ(sno,pno,jno);# 2. 查看spj表索引定义。
SHOW INDEX FROM spj;#代码结束
制作不易点个关注呗为大家创造更多的价值
