• MySQL开发技巧——查询、索引和完整性


     目录

    第1关 基本查询的学习

    查询语句SELECT

    头歌实验

    查询语句SELECT

    第2关 深入学习查询语句

    MySQL聚集函数

    头歌实验

    MySQL聚集函数

    第3关 视图的创建和使用

    查看数据库中表的情况:

    头歌实验

    第4关 索引和完整性

    索引

    头歌实验

    索引

    数据完整性

    域完整性

    实体完整性

    参照完整性

    第1关 基本查询的学习

    查询语句SELECT

    以下是MySQL数据库中查询数据通用的SELECT语法:

    1. SELECT column_name1,column_name2 FROM table_name [WHERE Clause] [OFFSET M ] [LIMIT N]
    • 查询语句中可以指定一个或者多个表,表之间使用逗号,分割,并使用WHERE语句来设定查询条件
    • SELECT命令可以读取一条或者多条记录
    • 可以使用星号*来代替字段,返回表所有字段的数据
    • 可以使用LIMIT属性来设定返回的记录数。

    头歌实验

    本关的应用场景是企业员工数据库的查询。本关你将自己动手完成对一个真实的员工数据库YGGL的查询:

    • 查找财务部年龄不低于所有研发部雇员年龄的雇员姓名、编号和性别。
    • 查找财务部收入在5200元以上的雇员姓名及其薪水收入支出情况。

    上述查询功能需要你掌握SELECT语句的基本语法、掌握子查询、连接查询的表示方法。下面就是对这些内容的详细教程,请先仔细阅读。

    相关知识

    查询语句SELECT

    以下是MySQL数据库中查询数据通用的SELECT语法:

    1. SELECT column_name1,column_name2 FROM table_name [WHERE Clause] [OFFSET M ] [LIMIT N]
    • 查询语句中可以指定一个或者多个表,表之间使用逗号,分割,并使用WHERE语句来设定查询条件
    • SELECT命令可以读取一条或者多条记录
    • 可以使用星号*来代替字段,返回表所有字段的数据
    • 可以使用LIMIT属性来设定返回的记录数。

    命令行的操作

    首先我们来通过命令行操作复习上一阶段所学内容吧!

    下面几个表来自于我们用于实验的员工管理系统的数据库YGGL,通过命令行新建数据库数据表并插入数据吧!

    1. CREATE DATABASE YGGL;

    使用数据库:

    1. use YGGL;

    员工信息表emp表结构:

    列名数据类型长度是否允许为空值说明
    eidchar6员工编号,主键
    enamechar10姓名
    birthdate生日
    sexint1性别
    addrchar20地址
    zipchar6邮编
    telchar12电话号码
    didchar3部门编号,外键
    1. create table emp
    2. (
    3. eid char(6) NOT NULL PRIMARY KEY,
    4. ename char(10) NOT NULL,
    5. birth date NOT NULL,
    6. sex int(1) NOT NULL,
    7. addr char(20) NULL,
    8. zip char(6) NULL,
    9. tel char(12) NULL,
    10. did char(3) NOT NULL
    11. );

    数据样本:

    eidenamebirthsexaddrzipteldid
    001wl1971-01-231zsl210003123456682
    008wrh1981-03-281bjdl210001123456211
    010wxr1987-12-091spl210006123456611
    018ll1965-07-300zsdl210002123456011
    201lm1977-10-181hjl210013123456085
    208zj1970-09-281plx210004123456175
    991zm1984-08-100zsl210003123456223
    006zsb1979-10-011jfl210010123456185
    678lt1982-04-021zsbl210008123456363
    566lym1973-09-201rhl210001123456914
    759yf1983-11-181bjxl210002123456014
    209cll1974-09-030hzl210018123456584
    1. insert into emp values
    2. ('001','wl','1971-01-23',1,'zsl','210003','12345668','2'),
    3. ('008','wrh','1981-03-28',1,'bjdl','210001','12345621','1'),
    4. ('010','wxr','1987-12-09',1,'spl','210006','12345661','1'),
    5. ('018','ll','1965-07-30',0,'zsdl','210002','12345601','1'),
    6. ('201','lm','1977-10-18',1,'hjl','210013','12345608','5'),
    7. ('208','zj','1970-09-28',1,'plx','210004','12345617','5'),
    8. ('991','zm','1984-08-10',0,'zsl','210003','12345622','3'),
    9. ('006','zsb','1979-10-01',1,'jfl','210010','12345618','5'),
    10. ('678','lt','1982-04-02',1,'zsbl','210008','12345636','3'),
    11. ('566','lym','1973-09-20',1,'rhl','210001','12345691','4'),
    12. ('759','yf','1983-11-18',1,'bjxl','210002','12345601','4'),
    13. ('209','cll','1974-09-03',0,'hzl','210018','12345658','4');

    部门信息表dept表结构:

    列名数据类型长度是否允许为空值说明
    didchar3部门编号,主键
    dnamechar20部门名
    notevarchar100备注
    1. create table dept
    2. (
    3. did char(3) NOT NULL PRIMARY KEY,
    4. dname char(20) NOT NULL,
    5. note varchar(100) NULL
    6. );

    数据样本:

    diddnamenote说明
    1cwbNULL财务部
    2rlzybNULL人力资源部
    3jlbgsNULL经理办公室
    4yfbNULL研发部
    5scbNULL市场部
    1. insert into dept values
    2. ('1','cwb',NULL),
    3. ('2','rlzyb',NULL),
    4. ('3','jlbgs',NULL),
    5. ('4','yfb',NULL),
    6. ('5','scb',NULL);

    工资表sal表结构:

    列名数据类型长度是否允许为空值说明
    eidchar6员工编号,主键
    incomeint8收入
    outcomeint8支出
    1. create table sal
    2. (
    3. eid char(6) NOT NULL PRIMARY KEY,
    4. income int(8) NOT NULL,
    5. outcome int(8) NOT NULL
    6. );

    数据样本:

    eidincomeoutcome
    00151001123
    00845821088
    20155691185
    00649871079
    20950661108
    56659801210
    99162591281
    01058601198
    01853471180
    75955311199
    67852401121
    20849801100
    1. insert into sal values
    2. ('001',5100,1123),
    3. ('008',4582,1088),
    4. ('201',5569,1185),
    5. ('006',4987,1079),
    6. ('209',5066,1108),
    7. ('566',5980,1210),
    8. ('991',6259,1281),
    9. ('010',5860,1198),
    10. ('018',5347,1180),
    11. ('759',5531,1199),
    12. ('678',5240,1121),
    13. ('208',4980,1100);

    查询每个雇员的所有数据:

    1. select * from emp;

    查询每个雇员的地址和电话:

    1. select ename,addr,tel from emp;

    查询eid001的雇员地址和电话:

    1. select ename,addr,tel from emp where eid='001';

    查询emp中所有女雇员的地址和电话,使用as子句将结果中各列的标题分别指定为地址和电话:

    1. select ename as fname,addr as faddr,tel as ftel from emp where sex=0;

    计算每个雇员的实际收入:

    1. select did,income-outcome as money from sal;

    找出所有姓名是w开头的雇员的部门号:

    1. select did from emp where name like 'w%';
    • %表示任意符号

    找出所有收入在5000-6000之间的雇员号码:

    1. select eid from sal where income between 5000 and 6000;
    • BETWEEN运算符用于WHERE表达式中,选取介于两个值之间的数据范围。BETWEENAND一起搭配使用。通常value1应该小于 value2。当 BETWEEN 前面加上NOT运算符时,表示与BETWEEN相反的意思,即选取这个范围之外的值。

    子查询的使用

    所谓子查询,即在查询语句中内嵌其他查询语句。下面,我们仍继续在命令行的操作中学习。

    查找在cwb工作的雇员情况:

    1. select * from emp where did=(select did from dept where dname='cwb');

    连接查询的使用

    比如查询每个雇员的薪水情况,但是我们要返回雇员的姓名,而在sal表中没有雇员姓名,这个时候就需要我们通过员工编号连接emp和sal两张表。

    1. select emp.ename,sal.income from emp,sal where emp.eid=sal.eid;

    一些关键字

    • any关键字:

    假设any内部的查询语句返回的结果个数是三个,那么:

    1. select ...from ... where a > any(...)

    等价于

    1. select ...from ... where a > result1 or a > result2 or a > result3

    ALL关键字与any关键字类似,但其含义不同,相当于上面的or改成andsome关键字和any关键字是一样的功能。

    • IN运算符用于WHERE表达式中,以列表项的形式支持多个选择
    1. WHERE column IN (value1,value2,...)
    2. WHERE column NOT IN (value1,value2,...)

    IN前面加上NOT运算符时,表示与IN相反的意思,即不在这些列表项内选择。

    • UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。
       
        
      1. SELECT expression_1,expression_2,...,expression_n FROM tables [WHERE conditions]
      2. UNION [ALL | DISTINCT]
      3. SELECT expression_1,expression_2,...,expression_n FROM tables[WHERE conditions];
      参数expression_1expression_2, ... expression_n是要检索的列,tables是要检索的数据表,WHERE conditions是检索条件,DISTINCT是删除结果集中重复的数据。默认情况下 UNION操作符已经删除了重复数据,所以DISTINCT修饰符对结果没啥影响。而ALL可以返回所有结果集,包含重复数据。

    编程要求

    编写查询语句,实现对数据库YGGL(包括表empdeptsal)的相关查询:

    查询一:使用子查询的方法,查找财务部cwb年龄不低于所有研发部yfb雇员年龄的雇员姓名ename、编号eid和性别sex

    查询二:使用连接查询的方式,查找财务部cwb收入income在5200元以上的雇员姓名ename及其薪水收入income支出outcome情况。

    1. //请在下面补齐查询一的MySQL语句
    2. /*********begin*********/
    3. select ename,eid,sex from emp
    4. where did in
    5. (select did from dept
    6. where dname='cwb'
    7. )
    8. /*********end*********/
    9. and
    10. birth<=all
    11. (select birth from emp
    12. where did in
    13. (select did from dept
    14. where dname='yfb'
    15. )
    16. );
    17. //请在下面输入查询二的MySQL语句
    18. /*********begin*********/
    19. select ename,income,outcome
    20. from emp,sal,dept
    21. where emp.eid=sal.eid and
    22. emp.did=dept.did and
    23. dname='cwb' and income>5200;
    24. /*********end*********/

    第2关 深入学习查询语句

    MySQL聚集函数

    函数说明
    COUNT()返回某列的行数
    MAX()返回某列最大值
    MIN()返回某列最小值
    AVG()返回某列平均值
    SUM()返回某列值之和

    头歌实验

    本关的应用场景是企业员工数据库的查询。上一关中简单的查询满足不了需求,比如:

    • 求财务部雇员的总人数;
    • 求各部门的雇员数;
    • 将各雇员的姓名按收入由低到高排列。

    上述查询功能需要你掌握数据汇总、掌握GROUP BY和ORDER BY子句的作用和使用方法。下面就是对这些内容的详细教程,请先仔细阅读。

    相关知识

    首先我们还是使用命令行操作登录数据库系统,创建数据库YGGL、创建数据表empdeptsal并插入数据。

    MySQL聚集函数

    函数说明
    COUNT()返回某列的行数
    MAX()返回某列最大值
    MIN()返回某列最小值
    AVG()返回某列平均值
    SUM()返回某列值之和

    求财务部雇员的平均收入:

    1. select avg(income) as avgincome
    2. from sal
    3. where eid in
    4. (select eid
    5. from emp
    6. where did=
    7. (select did
    8. from dept
    9. where dname='cwb'));

    GROUP BY 和 ORDER BY 子句的使用

    GROUP BY语句根据一个或多个列对结果集进行分组。

    1. SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

    如果我们需要对读取的数据进行排序,我们就可以使用MySQL的ORDER BY子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。

    1. SELECT field1, field2,...,fieldN table_name1,table_name2..., ORDER BY field1,[field2...] [ASC [DESC]]
    • 你可以设定多个字段来排序。
    • 你可以使用 ASC (升序)或 DESC(降序) 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
    • 你可以添加 WHERE...LIKE 子句来设置条件。

    编程要求

    在右侧代码窗口区域的指定位置编写查询语句,实现对数据库YGGL(包括表empdeptsal)的相关查询:
    查询一:求财务部雇员的总人数;
    查询二:求各部门的雇员数;
    查询三:将各雇员的姓名按收入由低到高排列(提示:使用连接查询)。

    1. //请在下面输入查询一的MySQL语句
    2. /*********begin*********/
    3. select count(eid)
    4. from emp
    5. where did=
    6. (select did
    7. from dept
    8. where dname='cwb');
    9. /*********end*********/
    10. //请在下面输入查询二的MySQL语句
    11. /*********begin*********/
    12. select count(eid)
    13. from emp
    14. group by did;
    15. /*********end*********/
    16. //请在下面输入查询三的MySQL语句
    17. /*********begin*********/
    18. select emp.ename
    19. from emp,sal
    20. where emp.eid=sal.eid
    21. order by income;
    22. /*********end*********/

    第3关 视图的创建和使用

    查看数据库中表的情况:

    1. show tables;

    头歌实验

    本关的应用场景是企业员工数据库的查询。视图也是为了实现多样地查看表中的数据,比如限制财务部的经理只能看到财务部的信息。本关你创建cx_sal视图并使用该视图查看财务部雇员薪水情况。你需要掌握视图的使用方法~

    相关知识

    首先我们还是使用命令行操作登录数据库系统,创建数据库YGGL、创建数据表empdeptsal并插入数据。

    现在,你输入以下代码查看数据库中表的情况:

    1. show tables;

    限制查看雇员的某些情况:

    1. create or replace view cx_emp
    2. as
    3. select eid,ename,birth,sex,did
    4. from emp;

    创建该视图后,我们可以在数据库中查看所有的表,是不是变成了下面这样?

    接下来我们输入下面的命令来查看该视图:

    1. select * from cx_emp;

    为了了解视图的特性,我们向视图cx_emp中插入一条记录:

    eidenamebirthsexdid
    888zhj1983-09-2513

    现在,我们查看一下emp表,你是不是已经成功把这条记录插入到emp表中了呢?这就是视图,原表和视图之间是同步的。

    接下来请你尝试以下操作。

    zhj从经理办公室(部门编号3)转到市场部(部门编号为5):

    1. update cx_emp set did='5'
    2. where ename='zhj';

    请你再次查看emp表核查是否修改成功。

    编程要求

    请你思考,我们想限制各部门的经理只能查找本部雇员的薪水情况该怎么操作呢?比如财务部,只让财务部的经理查看本部门雇员姓名和收入、支出情况。

    请你创建cx_sal视图并使用该视图查看财务部雇员薪水情况enameincomeoutcome。 

    1. //请在下面输入创建cx_sal的视图的MySQL语句
    2. /*********begin*********/
    3. create or replace view cx_sal
    4. as
    5. select ename,income,outcome
    6. from emp,sal,dept
    7. where emp.eid=sal.eid and
    8. emp.did=dept.did and
    9. dname='cwb';
    10. /*********end*********/
    11. //请在下面输入查询财务部雇员薪水情况视图的MySQL语句
    12. /*********begin*********/
    13. select * from cx_sal;
    14. /*********end*********/

    第4关 索引和完整性

    索引

    索引是根据表中一列或若干列按照一定的顺序建立的列值与记录行之间的对应关系表。在列上创建了索引之后,查找数据是可以直接根据该列上的索引找到对应行的位置,从而快速找到数据。

    索引类型分成下列几个:

    • 普通索引(INDEX):基本索引类型
    • 唯一性索引(UNIQUE):该列的所有值没有重复
    • 主键(PRIMARY KEY):一种唯一性索引,一个表只能有一个主键
    • 全文索引(FLLTEXT):只能在varchar或text类型上创建

    头歌实验

    任务描述

    经理觉得查找数据的速度偏慢、精度较低,这个时候,你需要创建索引来使查询的速度更快,使用完整性来提高查询精度。

    上述查询功能需要你掌握索引的使用方法、理解数据完整性的概念及分类、掌握各种数据完整性的实现方法。下面就是对这些内容的详细教程,请先仔细阅读。

    背景知识

    索引

    索引是根据表中一列或若干列按照一定的顺序建立的列值与记录行之间的对应关系表。在列上创建了索引之后,查找数据是可以直接根据该列上的索引找到对应行的位置,从而快速找到数据。

    索引类型分成下列几个:

    • 普通索引(INDEX):基本索引类型
    • 唯一性索引(UNIQUE):该列的所有值没有重复
    • 主键(PRIMARY KEY):一种唯一性索引,一个表只能有一个主键
    • 全文索引(FLLTEXT):只能在varchar或text类型上创建

    语法格式:

    1. //创建
    2. CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名{字段名称[(长度)] [ASC|DESC]}
    3. //修改
    4. ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX索引名称(字段名称[(长度)][ASC|DESC]);

    下面我们用命令行操作试一试。

    在创建好整个数据库的基础上,对YGGL数据库的emp表的ename列建立索引:

    1. create index emp_name_idx
    2. on emp(ename);

    查看是否创建成功:

    1. show index from emp;

    重命名索引:

    1. alter index emp_name_idx
    2. rename to emp_idx;

    删除索引:

    1. drop index emp_idex;

    数据完整性

    为了防止不合规定的数据进入基表中,我们定义完整性规则。分为:域完整性、实体完整性和参照完整性。

    域完整性

    域完整性又叫列完整性,主要是对一列的数据进行约束。比如emp中限定sex的值只能为12中的一个,可以在创建表时将sex做以下定义:

    1. sex int(1) check(sex='1' or sex='2') NOT NULL,

    或者在所有字段定义完成后加一句:

    1. constraint ch_sex check(sex='1' or sex='2')

    下面,我们在命令行中通过修改表的方式创建约束:

    1. alter table emp
    2. add(constraint ch_sex check(sex='1' or sex='2'));

    删除约束:

    1. alter table emp
    2. drop constraint ch_sex;

    实体完整性

    又叫行完整性,要求每一行都有一个唯一的标识符。比如emp中的员工eid是唯一的,才能唯一确定某一个人。通过unique约束和primary key约束可以实现实体完整性。

    同样的,在创建表时对tel创建约束应该将tel定义为:

    1. tel char(12) NULL constraint un_tel unique,

    下面我们在命令行中通过修改表的方式创建约束:

    1. alter table emp
    2. add constraint un_tel unique(tel);

    参照完整性

    又叫引用完整性,它保证主表和从表中的数据一致性,实现方式是定义外键与主键。例如empsal表,eidemp中是外键,而在saleid定义为主键。

    • 从表不能引用主表不存在的键值
    • 主表中的值更改了,则从表中所有引用都也要修改
    • 若要删除主表中的记录,应先删除从表中匹配的记录

    如果在创建sal表时想创建参照完整性使emp表中所有eid都要出现在sal中,可以在定义empeid时这样定义:

    1. eid char(6) NOT NULL references sal(eid),

    下面我们在命令行中通过修改表的方式定义:

    1. alter table emp
    2. add constraint sal_id foreign key(eid)
    3. references sal(eid);

    编程要求

    在已经创建好整个YGGL数据库的基础上进行以下操作:

    建立索引pk_xs_bak:对empeid建立索引;
    实现域完整性ch_tel:为emptel建立check约束,其值只能为0-9的数字;
    实现实体完整性un_dept:为deptdname创建唯一性索引;
    实现参照完整性fk_emp:将emp中的did列为外键。

    1. //请在下面输入创建索引的MySQL语句
    2. /*********begin*********/
    3. create index pk_xs_bak
    4. on emp(eid);
    5. /*********end*********/
    6. //请在下面输入实现域完整性的MySQL语句
    7. /*********begin*********/
    8. alter table emp
    9. add(constraint ch_tel check(tel between 0 and 9));
    10. /*********end*********/
    11. //请在下面输入实现实体完整性的MySQL语句
    12. /*********begin*********/
    13. alter table dept
    14. add constraint un_dept unique(dname);
    15. /*********end*********/
    16. //请在下面输入实现参照完整性的MySQL语句
    17. /*********begin*********/
    18. alter table emp
    19. add constraint sal_id foreign key(eid)
    20. references sal(eid);
    21. /*********end*********/
  • 相关阅读:
    Java 中的面向数据编程
    信息安全实验三 :PGP邮件加密软件的使用
    音频转文字怎么操作?快来看看这几个方法吧
    【性能测试】Jenkins+Ant+Jmeter自动化框架的搭建思路
    Science子刊 | 将CAR-T细胞疗法与造血干细胞移植相结合 或许 能治疗所有血液癌症...
    手机检测 打电话识别
    SAP B1 Web Client & MS Teams App集成连载一:先决条件/Prerequisites
    Curl 命令方式对elasticsearch备份和恢复—— 筑梦之路
    云计算是什么?学习云计算能做什么工作?
    Docker 使用 IDEA 内置插件构建上传镜像 与 SSH、FTP 功能使用
  • 原文地址:https://blog.csdn.net/weixin_51970555/article/details/126822177