任务描述
本关任务:建立数据库 为了完成本关任务,你需要掌握:
如何创建数据库,显示已经建立的数据库
相关知识
创建数据库
创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理。 命令格式: 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数据库已经创建好,请按下面步骤完成任务。
- 切换到
demo
数据库 - 分别创建
s
、p
、j
和spj
数据表 - 查看
s
、p
、j
和spj
数据表的详细结构
注意:表名统一用小写。
数据表结构如下: s表
字段名称 | 字段类型 | 宽度 |
---|---|---|
sno | char | 2 |
sname | varchar | 10 |
status | int | |
city | varchar | 10 |
p表
字段名称 | 字段类型 | 宽度 |
---|---|---|
pno | char | 2 |
pname | varchar | 10 |
color | char | 1 |
weight | int |
j表
字段名称 | 字段类型 | 宽度 |
---|---|---|
jno | char | 2 |
jname | varchar | 10 |
city | varchar | 10 |
spj表
字段名称 | 字段类型 | 宽度 |
---|---|---|
sno | char | 2 |
pno | char | 2 |
jno | char | 2 |
qty | int |
#代码开始
#1. 切换到demo数据库
use demo;
#2. 分别创建s、p、j和spj数据表
create table s
(
sno char(2),
sname varchar(10),
status int,
city varchar(10)
);
create table p
(
pno char(2),
pname varchar(10),
color char(1),
weight int
);
create table j
(
jno char(2),
jname varchar(10),
city varchar(10)
);
create table spj
(
sno char(2),
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(sno,sname,status,city)values
('S1','精益',20,'天津'),
('S2','盛锡',10,'北京'),
('S3','东方红',30,'北京'),
('S4','丰泰盛',20,'天津'),
('S5','为民',30,'上海');
#插入p表数据
insert into p(pno,pname,color,weight)values
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);
#插入j表数据
insert into j(jno,jname,city)values
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');
#插入spj表数据
insert into spj(sno,pno,jno,qty)values
('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);
#分别查询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 sname,city from s order by sname;
#2. 查询所有零件的名称、颜色、重量(按零件名称升序排列)。
select pname,color,weight from p order by pname;
#代码结束
任务描述
本关任务:按题目要求完成数据表的查询。
相关知识
条件查询
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件列表可以使用以下运算符
模糊查询
模糊查询使用like关键字,可以使用通配符进行占位:
- _ : 代表单个任意字符
- % : 代表任意个数字符
例如:
-
查询姓'马'的学员信息
select * from stu where name like '马%';
-
查询第二个字是'花'的学员信息
select * from stu where name like '_花%';
-
查询名字中包含 '德' 的学员信息
select * from stu where name like '%德%';
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
任务要求
请用SQL语句完成下面题目的查询。
- 查询供应工程J1零件的供应商号码(去除重复,按供应商号码升序排列)。
- 查询供应工程J1零件P1的供应商号码(按供应商号码升序排列)。
- 查询使用供应商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 sno from s order by sno;
#2. 查询供应工程J1零件P1的供应商号码(按供应商号码升序排列)。
select sno from spj where pno='p1' and jno='j1' order by sno;
#3. 查询使用供应商S1所供应零件的工程号码(按工程号码升序排列)。
select jno from spj where sno='s1' order by 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
SELECT
emp. NAME,
emp.gender,
dept.dname
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;
执行语句结果如下:
上面语句中使用表名指定字段所属有点麻烦,sql也支持给表指别名,上述语句可以改进为SELECT
t1. NAME,
t1.gender,
t2.dname
FROM
emp t1,
dept t2
WHERE
t1.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 pname,qty from spj,p where spj.pno = p.pno and jno ='j2'order by pname;
#2. 查询上海厂商供应的所有零件号码(去除重复,按零件号码升序排列)。
select distinct pno from spj,s where spj.sno = s.sno and city='上海'order by pno;
#3. 查询使用上海产的零件的工程名称(按工程名称升序排列,去除重复数据)。
select distinct jname from spj,s,j where spj.sno=s.sno and spj.jno=j.jno and s.city='上海' order by 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 s.sno from spj,s,p where spj.sno=s.sno and spj.pno=p.pno and spj.jno='j1'
and p.color='红'order by s.sno;
#2. 查询没有使用天津供应商生产的红色零件的工程号码(去除重复数据,按工程号码升序排列)。
select distinct jno from spj where jno not in(select distinct spj.jno from spj,s,p where spj.sno=s.sno and spj.pno=p.pno and s.city='天津'and p.color ='红'order by spj.jno);
#代码结束
的行,只需要省略WHERE
语句即可。
语法规则为:DELETE FROM 表名;
任务要求
请用SQL语句完成下面题目。
- 把全部红色零件的颜色改成蓝色。
- 由S5供给J4的零件P6改为由S3供应。
- 从供应商关系中删除供应商号是S2的记录,并从供应情况关系中删除相应的记录。
- 请将(S2,J6,P4,200)插入供应情况关系表。
数据修改完后请和原始数据进行比对以查看修改结果。
原始数据如下所示:
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 color='蓝'where color='红';
#2. 由S5供给J4的零件P6改为由S3供应。
update spj set sno='S3' where sno ='s5'and jno='j4'and pno='p6';
#3. 从供应商关系中删除供应商号是S2的记录,并从供应情况关系中删除相应的记录。
delete from spj where sno='s2';
delete from s where sno='s2';
#4. 请将(S2,J6,P4,200)插入供应情况关系表。
insert into spj(sno,jno,pno,qty) values ('S2','J6','P4',200);
#代码结束
#评测使用,不要删除下面代码
select * from s;
select * from p;
select * from spj;
相关知识
聚合函数
概念
==将一列数据作为一个整体,进行纵向计算。==
如何理解呢?假设有如下表
现有一需求让我们求表中所有数据的数学成绩的总和。这就是对math字段进行纵向求和。
聚合函数分类
函数名 | 功能 |
---|---|
count(列名) | 统计数量(一般选用不为null的列) |
max(列名) | 最大值 |
min(列名) | 最小值 |
sum(列名) | 求和 |
avg(列名) | 平均值 |
聚合函数语法
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]
参数说明:
-
OR REPLACE
:表示替换已有视图。 -
ALGORITHM
:表示视图选择算法,默认算法是UNDEFINED
(未定义的): MySQL 自动选择要使用的算法 ;merge
合并;temptable
临时表。 -
column_list
:可选参数,指定视图中各个属性的名词,默认情况下与select
语句中查询的属性相同。 -
select_statement
:表示select
语句。 -
[WITH [CASCADED | LOCAL] CHECK OPTION]
:表示视图在更新时保证在视图的权限范围之内;cascade
是默认值,表示更新视图的时候,要满足视图和表的相关条件;local
表示更新视图的时候,要满足该视图定义的一个条件即可。
示例一:
示例二:
以上两个示例可以看出,虽然两个视图的字段名不同,但是,数据是相同的,因为两个视图引用的是同一个表中的数据,并且,as
后的创建视图的语句也相同。
在实际开发中,用户可以根据自己的需求,通过视图的方式,获取基本表中自己需要的数据,这样既能满足用户的需求,也不会破坏基本表原来的结构,从而保证了基本表中数据的安全性。
操作视图
视图是逻辑表,也就是说视图不是真实的表,但操作视图和操作普通表的语法是一样的。
用户可以在视图中无条件地使用select
语句查询数据。但使用insert
、update
和delete
操作需要在创建视图时满足以下条件(满足以下条件的视图称为可更新视图):
-
from
子句中只能引用有1
个表(真实表或可更新视图)。 -
不能包含
with
、distinct
、group by
、having
、limit
等子句。 -
不能使用复合查询,即不能使用
union
、intersect
、except
等集合操作。 -
select
子句的字段列表不能包含聚合、窗口函数、集合返回函数。
我们仍使用之前示例中的数据来操作视图:
删除视图
若视图不再被需要,我们可以将其删除,且视图的删除并不影响源表中的数据。
删除视图的 SQL 如下:
DROP VIEW view_name;
示例:
查看视图
查看视图是指查看数据库中,已经存在的视图的定义 查看视图,必须要有SHOW VIEW的权限
语法格式
DESCRIBE 视图名;
或者简写
DESC 视图名;
任务要求
- 请为
三建
工程项目建立一个供应情况的视图(v_spj
),包括供应商代码(SNO
)、零件代码(PNO
)、供应数量(QTY
)。 - 查看视图(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 as select sno,pno,qty from spj,j where
spj.jno=j.jno and jname='三建';
#2. 查看视图(v_spj)信息。
desc v_spj;
#针对视图(v_spj)完成下列查询:
#(1) 查询“三建”工程项目使用的各种零件代码及其总数量(total)
#提示:利用聚合函数sum()和分组查询实现,总数量起别名total。
select pno,sum(qty) as total from v_spj group by pno;
#(2) 查询供应商S1的供应情况。
select * from v_spj where sno='s1';
#代码结束
任务描述
为了完成本关任务,你需要掌握如下知识点:
- 什么是索引
- 索引的分类
- 索引的定义
- 删除索引
- 查看索引
相关知识
什么是索引
官方介绍索引是帮助MySQL高效获取数据的数据结构。简单来讲,数据库索引就像是书前面的目录,能加快数据库的查询速度。
事实上,索引是一种数据结构,用于帮助我们在大量数据中快速定位到我们想要查找的数据。
但对于海量数据来说,它的目录也是很大的,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。
索引的分类
单列索引
一个索引只包含单个列,但一个表中可以有多个单列索引。 这里不要搞混淆了
- 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一 点。
- 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
- 主键索引:是一种特殊的唯一索引,不允许有空值。(主键约束,就是一个主键索引)。
组合索引
指在多个字段上创建的索引,在多个字段上创建的普通索引和唯一索引都是组合索引,所谓组合实际是字段的组合,对于多个字段上的唯一索引,要求组合字段必须唯一。
索引的定义
根据创建时机的不同,索引主要有两种创建方式:
- 在创建表时同时创建普通索引
语法:
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
表索引定义。