MySQL数据库操作全解析

### MySQL数据库操作全解析 #### 1. 引言 在数据库管理中，MySQL是一种广泛使用的关系型数据库管理系统。它提供了丰富的功能，能够帮助我们高效地存储、检索和管理数据。本文将详细介绍MySQL中的各种重要命令和操作，包括数据查询、表操作、表连接以及自增字段的使用等。 #### 2. SELECT命令基础 SELECT命令是MySQL中最常用的命令之一，其主要目的是从数据库表中检索信息。以下是一些基本的SELECT命令示例： - **显示全量数据**： ```sql SELECT * FROM mytable1 ``` 这里的`*`代表所有字段，该命令将显示`mytable1`表的完整内容。 - **指定字段查询**： ```sql SELECT Forename,Surname,sex,numchild FROM mytable1 ``` 此命令仅显示`Forename`、`Surname`、`sex`和`numchild`这几个字段的值。 在使用PHP执行MySQL命令时，除了SELECT命令，大多数命令返回的是逻辑值（1表示命令成功，空字符串表示命令失败）。而SELECT命令返回一个对象，我们可以从中提取行和列的值，并根据需要进行显示或使用。 #### 3. SELECT命令的子句 ##### 3.1 ORDER BY子句 使用SELECT命令时，结果表的顺序可能是系统任意决定的。为了获得更有意义的排序结果，我们可以使用ORDER BY子句。 - **按主键升序排序**： ```sql SELECT * FROM mytable1 ORDER BY refnum ``` 该命令将`mytable1`表的记录按`refnum`字段的升序排列。 - **指定升序或降序**： ```sql SELECT * FROM mytable1 ORDER BY refnum ASC -- 升序 SELECT * FROM mytable1 ORDER BY refnum DESC -- 降序 ``` - **多字段排序**： ```sql SELECT * FROM mytable1 ORDER BY surname, forename DESC ``` 此命令先按`surname`字段升序排序，若`surname`相同，则按`forename`字段降序排序。 - **自定义排序**： 对于一些不适合按字母或数字顺序排序的字段，我们可以使用ORDER BY FIELD子句。例如： ```sql ORDER BY FIELD(size, 'very small', 'small', 'average', 'large', 'very large') ``` ##### 3.2 WHERE子句 WHERE子句用于筛选满足特定条件的记录。它可以包含简单条件或复杂条件，由逻辑运算符（AND、OR、NOT）组合而成。 - **简单条件示例**： ```sql SELECT Surname,Forename,yearBorn FROM mytable1 WHERE sex="M" and numchild>0 AND numchild!=99 ORDER BY Surname,Forename ``` 该命令显示所有有孩子的男性的姓名和出生年份。 - **比较运算符**： | 运算符 | 含义 | | ---- | ---- | | = | 等于 | | != | 不等于 | | <> | 不等于 | | < | 小于 | | <= | 小于等于 | | > | 大于 | | >= | 大于等于 | - **复杂条件示例**： ```sql sex='F' AND (yearBorn>1980 OR numchild!=99) AND NOT (occupation='doctor' OR occupation="engineer") ``` - **函数和运算符的使用**： 条件中可以使用算术运算符（+、-、*、/）和函数（如GREATEST、LEAST、CONCAT）。例如： ```sql WHERE i1+i2 < i3-i4*i5 WHERE GREATEST(i1,i2,i3)>99 WHERE LEAST(i2,i5) > LEAST(i1,i3,i4) ``` ##### 3.3 显示非字段值 使用SELECT命令可以显示表中未明确作为字段的值。例如，使用CONCAT函数将`Forename`和`Surname`字段连接起来： ```sql SELECT CONCAT(Forename,' ',Surname), sex FROM mytable1 WHERE numchild>1 AND numchild!=99 ORDER BY Surname,Forename ``` ##### 3.4 LIMIT子句 LIMIT子句用于限制查询结果的行数。 - **基本LIMIT子句**： ```sql SELECT * FROM mytable1 ORDER BY yearBorn DESC LIMIT 4 ``` 该命令显示表中最年轻的4个人的所有信息。 - **带偏移量的LIMIT子句**： ```sql SELECT * FROM mytable1 ORDER BY yearBorn DESC LIMIT 2,4 ``` 此命令从第3行开始（偏移量为2），显示4行记录。 ##### 3.5 对字段值应用函数 在SELECT命令中，我们可以对字段值应用函数，如max、min和avg。 ```sql SELECT min(yearBorn),max(yearBorn),min(Surname),max(Surname),min(numchild), max(numchild), avg(numchild) FROM mytable1 ``` 该命令返回`yearBorn`、`Surname`和`numchild`字段的最小值、最大值和平均值。 ##### 3.6 统计记录数量 - **统计全量记录**： ```sql SELECT COUNT(*) FROM mytable1 ``` 此命令返回`mytable1`表中的记录总数。 - **统计特定字段非空记录**： ```sql SELECT COUNT(cityBorn) FROM mytable1 ``` 该命令返回`cityBorn`字段非空的记录数量。 - **统计满足特定条件的记录**： ```sql SELECT COUNT(if(yearBorn>1985 AND sex="F",1,NULL)) FROM mytable1 ``` ##### 3.7 查找字段的不同值 使用DISTINCT关键字可以查找字段的不同值。 ```sql SELECT DISTINCT sex FROM mytable1 ``` 该命令返回`sex`字段的不同值，即`M`和`F`。 也可以查找多个字段的不同组合值： ```sql SELECT DISTINCT sex,numChild FROM mytable1 ``` #### 4. 复杂的SELECT/UPDATE命令 在MySQL中，可以构建复杂的命令，通常将SELECT命令与UPDATE命令或INSERT INTO命令结合使用。例如： ```sql UPDATE mytable2 SET Fullname = (SELECT CONCAT(Forename,' ',Surname) FROM mytable1 WHERE cityBorn = 'paris' ORDER BY refnum LIMIT 1) ```