MySQL高级查询和编程基础

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL高级查询和编程基础，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9816字，纯文字阅读大概需要15分钟。

内容图文

一、数据需求分析：

  数据需求分析是为后续概念设计和逻辑结构设计做准备。

结构：（1）对现实世界要处理的对象进行详细的调查。

     （2）收集基础数、据。

     （3）对所收集的数据进行处理。

     （4）确定新的功能。

二、概念结构设计：

 主要的五项概念：实体、属性、域、码、实体间联系。

  实体之间的联系：（1）1：1 在任意一方建立另外一方的外键。

               （2）1：m 在多的一方建立一的外键。

               （3）m：n 建立第三张表，双方的主键在第三张表中作为外键。

三、使用E-R模型进行概念结构设计：

    E-R图概述：

  （1）真实、充分的反映现实世界中事物和事物之间的联系。

  （2）简明易懂。

  （3）易于修改。

  （4）便于向数据逻辑模型转换。

标识实体的原则包括以下4项：

  （1）实体通常是一个名词，其名称应简明扼要、恰如其分。

  （2）每个实体仅描述一件事情或一个事物。

  （3）每个实体都是唯一的，即不能出现含义相同的实体。

  （4）联系通常是一个动词或动名词，其名称应反映出实体之间的内在关联。

四、逻辑结构设计：

    当1：m 的联系转换为关系模式时，通常采用与m端相对应的关系模式进行合并。

五、使用PowerDesigner设计数据库：

概念模型、物理模型、生成数据库。

第二章 基本查询应用

一、SELECT基本结构：

语法：

select <column1, column2, column3...>

from <table_name>

[where <条件表达式>]

[group by column1, column2, column3... | having <条件表达式>]

[order by < column1, column2, column3...> [ASC或者DESC]]

说明：

（1）必须的子句只有select和from子句。

（2）where子句用于对查询结果进行过滤。

（3）group by子句根据指定列分组，having子句对分组后的结果进行过滤。

（4）order by子句用于对查询结果进行排序。ASC表示升序排序，DESC表示降序排序，默认按照ASC排序。

LIMIT子句：

select * from table LIMIT [offset,] rows

二、聚合函数：

常用的聚合函数：

1.AVG 返回指定组中的平均值，空值被忽略。

例：select prd_no,avg(qty) from sales group by prd_no

2. COUNT 返回指定组中项目的数量。

例：select count(prd_no) from sales

3. MAX 返回指定数据的最大值。

例：select prd_no,max(qty) from sales group by prd_no

4. MIN 返回指定数据的最小值。

例：select prd_no,min(qty) from sales group by prd_no

5. SUM 返回指定数据的和，只能用于数字列，空值被忽略。

例：select prd_no,sum(qty) from sales group by prd_no

6. COUNT_BIG 返回指定组中的项目数量，与COUNT函数不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。

例：select count_big(prd_no) from sales

7. GROUPING 产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1.当所添加的行不是由CUBE或ROLLUP产生时，输出值为0.

例：select prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup

8. BINARY_CHECKSUM 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。

例：select prd_no,binary_checksum(qty) from sales group by prd_no

9. CHECKSUM_AGG 返回指定数据的校验值，空值被忽略。

例：select prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no

10. CHECKSUM 返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。

11. STDEV 返回给定表达式中所有值的统计标准偏差。

例：select stdev(prd_no) from sales

12. STDEVP 返回给定表达式中的所有值的填充统计标准偏差。

例：select stdevp(prd_no) from sales

13. VAR 返回给定表达式中所有值的统计方差。

例：select var(prd_no) from sales

14. VARP 返回给定表达式中所有值的填充的统计方差。

例：select varp(prd_no) from sales

分组查询：

1.使用group by进行分组查询

在使用group by关键字时，在select列表中可以指定的项目是有限制的，select语句中仅许以下几项：

〉被分组的列

〉为每个分组返回一个值得表达式，例如用一个列名作为参数的聚合函数

group by

例1：

select courseID,avg(score) as 课程平均成绩

from score

group by courseID

例2：

select studentID as 学员编号,courseID as 内部测试,avg(score) as 内部测试平均成绩

from score

group by studentID,courseID

2.使用having子句进行分组筛选

where子句只能对没有分组统计前的数据行进行筛选,对分组后的条件的筛选必须使用having子句。

例：

select studentID as 学员编号,courseID as 内部测试,avg(score) as 内部测试平均成绩

from score

group by studentID,courseID

having avg(score)>60

在select语句中，where、group by、having子句和统计函数的执行次序如下：

where子句从数据源中去掉不符合去搜索条件的数据；group by子句搜集数据行到各个组中，统计函数为各个组计算统计值；having子句去掉不符合其组搜索条件的各组数据行 。

联接查询：

概述：连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。

方式形式：内连接的连接查询结果集中仅包含满足条件的行，内连接是SQL Server缺省的连接方式，可以把INNERJOIN简写成JOIN，根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种；交叉连接的连接查询结果集中包含两个表中所有行的组合；外连接的连接查询结果集中既包含那些满足条件的行，还包含其中某个表的全部行，有3种形式的外连接：左外连接、右外连接、全外连接。

第三章 子查询

一、子查询的基本知识：

嵌套在select、insert、update和deleted语句或其他子查询中的查询，允许使用任何表达式的地方均可以使用子查询，但是子查询通常位于where子句中。

子查询的实质：一个select语句的查询结果能够座位另一个语句的输入值。

二、单行子查询：

单行子查询是指子查询的返回结果只有一行数据。

当主查询的条件语句中引用子查询的结果时，可使用单行比较符（=、>、<、>=、<=和<>）进行比较。

三、多行子查询：

多行子查询是指查询的返回结果是多行数据。常见的多行比较符包括IN、ALL。

IN比较符：

使用IN时，主查询会与子查询中的每一个值进行比较，如果预期中的任何一个值相同，则返回。NOT IN 与IN的含义恰好相反。

单行子查询中的“=”可以用多行子查询中的“IN”替换。

使用ALL关键字的子查询：

语法：表达式或字段 单行比较运算符 ALL（子查询）

ALL运算符的含义：

（1）<ALL，表示小于最小值

（2）>ALL，表示大于最大值

ANY运算符的含义：

（1）<ANY，表示大于最小值

（2）>ANY，表示小于最大值

四、在FROM子句中使用子查询：

在from中使用子查询的实质是将子查询看作一张虚表与主查询中的表作联接查询。

五、在SELECT子句中使用子查询：

实质：将子查询的执行结果作为SELECT子句的列，可以起到与联接查询异曲同工的作用。

六、EXISTS子查询：

语法：主查询表达式 [NOT] EXISTS （子查询）

EXISTS用于检查子查询是否会返回一行数据，该子查询实际上并不返回任何数据，而是返回TRUE或FALSE。EXISTS指定一个字查询，用于检测行的存在。当子查询的行存在时，则执行主查询表达式，否则不执行。

七、在DML语句中使用子查询：

在UPDATE子句中使用子查询：

UPDATE [原表] SET [要修改的列] WHERE [条件]

在DELETE子句中使用子查询：

DELETE FROM [原表] WHERE [条件]

八、补充：

子查询依赖子查询称为相关子查询。

子查询：

（1）表扫描适合外大内小（效率高）不适合外小内大。

（2）索引。

交叉连接(cross join)：

交叉连接是把所有第一个表和第二个表的值一一对应。

例：

select u.username , t.toyname from user as u cross join tay ast ;

第四章 视图、索引

一、视图：

概念和特点：

（1）视图是一种数据库对象，是一个从一张表、多张表或视图中导出的虚表。视图的结构和数据是数据表进行查询的结果。

（2）仅存放视图的定义，不存放视图所对应的数据。

（3）如果基表中的数据发生变化，则从视图中查询出的数据也随之改变。

优点：

1、关注点聚焦。

2、简化操作。

3、定制数据。

4、合并分割数据。

5、安全性。

创建和使用：

利用CREATE VIEW语句可以创建视图，该命令的基本语法如下：

CREATE VIEW view_name AS SELECT column_names FROM table_name1 , [table_name2 , table_name3 , ... table_namen] WHERE condition

参数说明：

view_name：视图名。

column_names：视图中的字段列表，可以来源于多个表。

table_name1：表名1.

condition：条件表达式，如果是多表则该表达式还包含表的联接条件。

使用视图创建复杂查询：

视图中的列不仅可以是基表的数据列，还可以是计算机或聚合函数列。

二、索引：

概述：

数据库中的索引是一个表中所包含值的列表，它注明了这些值所对应的存储位置。

基本价值：

提高效率。

作用和弊端：

作用：

（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（2）可以大大加快数据的检索速度。

（3）可以加速表与表之间的联接，在实现数据参考完整性方面有特别的意义。

（4）使用分组和排序子句进行数据检索时同样可以显著减少查询中分组和排序的时间。

（5）通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统性能。

弊端：

（1）因为创建索引所需的工作空间约为数据库表的1.2倍，所以带索引的表在数据库中会占据更多的空间。

（2）为维护索引，在对数据进行插入、更新和删除操作时会耗费系统时间。

（3）在建立索引时，由于需要复制数据，同样会耗费系统的时间和空间。

使用场合：

（1）在经常需要搜索的列上，可以加快搜索的速度。

（2）在作为主键的列上。

（3）在经常用联接的列（这些列主要是一些外键）上，建立索引可以加快联接的速度。

（4）在经常需要根据范围进行搜索的列上创建索引。因为索引已经排序，其指定的范围是连续的。

（5）在经常需要排序的列上创建索引。因为索引已经排序，这样查询可以利用索引的排序节省查询的时间。

（6）在使用WHERE子句的列上创建索引，加快条件的判断速度。

一般而言，不应该创建索引的列具有以下4个特点：

（1）对于那些在查询中很少使用或参考的列不应该创建索引。

（2）对于那些只有很少数据值的列而言，同样不应该增加索引。

（3）对于那些定义text、image和bit数据类型的列不应该增加索引。

（4）当修改性能远大于检索性能时，不应该创建索引。

分类：

（1）聚集索引：

聚集索引是将数据的值在表内排序并储存对应的数据记录，是数据表物理排序与索引顺序相一致。

（2）非聚集索引（普通索引）：

非聚集索引也称为普通索引，它是一种完全独立于数据进行的文件结构。数据储存在一个地方，索引储存在另一个地方。非聚集索引中的数据排列顺序并非表中结构的排列顺序。

（3）聚集索引和非聚集索引的比较：

a. 聚集索引相比非聚集索引，在插入数据时速度要慢（时间花费在“物理存储排序”上，即首先要找到位置然后插入），但在查询数据时速度要快。

b. 如果硬盘和内存空间有限，则应限制非聚集索引的使用。

创建索引的方法：

（1）MySQL自动创建索引。

MySQL在创建表中其他对象时可以附带创建新索引。通常情况下，MySQL在创建UNIQUE约束或PRIMARY KEY约束时，系统会自动在这些约束上创建聚集索引；另外系统通常也会在自动外键列上创建非聚集索引（即普通索引）。

（2）用户创建索引。

除了MySQL自动生成索引外，也可以根据实际需要，使用MySQL集成开发平台（如Navicat For MySQL），或者利用SQL语句CREATE INDEX命令直接创建索引。

使用ALTER命令创建索引：

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE table_name ADD UNIQUE (column_list);

<span sty 本文系统来源：https://www.cnblogs.com/ceet/p/10076942.html

内容总结

以上是互联网集市为您收集整理的MySQL高级查询和编程基础全部内容，希望文章能够帮你解决MySQL高级查询和编程基础所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端