数据库-DML（数据操作语言）和DQL（数据查询语言）

爱被打了一巴掌 2022-05-11 02:14 247阅读 0赞

## **MySQL****的存储引擎** ##

### 什么是存储引擎？ ###

关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。

### **存储引擎的类型** ###

**MyISAM****、****InnoDB** **、****Memory****、****CSV****等****9****种**

### **MyISAM****与****InnoDB****类型主要区别** ###

<table style="width:436px;"> 
 <tbody> 
 <tr> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:145px;"> 名称 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:140px;"> InnoDB </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:148px;"> MyISAM </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:145px;"> 事务处理 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:140px;"> 支持 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:148px;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:center;vertical-align:middle;width:145px;"> 数据行锁定 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:center;vertical-align:middle;width:140px;"> 支持 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:center;vertical-align:middle;width:148px;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:145px;"> 外键约束 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:140px;"> 支持 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:148px;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:center;vertical-align:middle;width:145px;"> 全文索引 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:center;vertical-align:middle;width:140px;"> 不支持 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:center;vertical-align:middle;width:148px;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:145px;"> 表空间大小 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:140px;"> 较大,约2倍 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:center;vertical-align:middle;width:148px;"> 较小 </td> 
 </tr> 
 </tbody> 
</table>

**MyISAM**表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb\_Demo表，那么就会生成以下三个文件：

1.tb\_demo.frm，存储表定义；

2.tb\_demo.MYD，存储数据；

3.tb\_demo.MYI，存储索引。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

**InnoDB**是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。一般电脑上安装的MySQL版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

4.外键约束。MySQL支持外键的存储引擎只有InnoDB。

5.支持自动增加列AUTO\_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

**<适用场合**

•**  使用MyISAM:   不需事务，空间小，以查询访问为主**

•**  使用InnoDB:     多删除、更新操作，安全性高，事务处理及并发控制>**

### **查看当前默认存储引擎** ###

**语法**

SHOW VARIABLES LIKE ‘storage_engine%’;

![70][]

**修改存储引擎:****修改****my.ini****配置文件**

![70 1][]

### **设置表的存储引擎** ###

**语法:**

CREATE TABLE 表名(
    
        #省略代码
    
    )ENGINE=存储引擎;

**示例:**

CREATE TABLE `myisam` (
       id  INT(4)
    )ENGINE=MyISAM;

## **数据表的存储位置** ##

### **MyISAM****类型表文件** ###

**\*.frm****：表结构定义文件**

**\*.MYD****：数据文件**

**\*.MYI****：索引文件**

![70 2][]

### **InnoDB****类型表文件** ###

**\*.frm****：表结构定义文件**

**ibdata1****文件**

![70 3][]

### **注意:** ###

**存储位置->因操作系统而异，可查my.ini**

<table style="width:436px;"> 
 <tbody> 
 <tr> 
 <td style="width:433px;"> datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" innodb_data_home_dir="D:/MySQL Datafiles/" </td> 
 </tr> 
 </tbody> 
</table>

# **DML****语句****——****插入****单条****数据记录** #

**语法:**

<table style="width:365px;"> 
 <tbody> 
 <tr> 
 <td style="width:362px;"> INSERT INTO 表名 [(字段名列表)] VALUES (值列表); </td> 
 </tr> 
 </tbody> 
</table>

**注意:**

**1.字段名****是可选的，****如省略则****依次插入所有字段**

**2.多****个列表和多个值之间使用逗号分隔**

**3.值列表和****字段名****列表一一对应**

**4.如插入****的是表中部分数据，字段名列表必填**

**示例:**

<table style="width:468px;"> 
 <tbody> 
 <tr> 
 <td style="width:465px;"> INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`) VALUES('123','黄小平',1,'13956799999','1996-5-8'); </td> 
 </tr> 
 </tbody> 
</table>

# **DML****语句****——****插入****多****条****数据记录** #

**语法:**

<table style="width:318px;"> 
 <tbody> 
 <tr> 
 <td style="width:315px;"> INSERT INTO 新表（字段名列表） &nbsp;VALUES(值列表1),(值列表2),……,(值列表n); </td> 
 </tr> 
 </tbody> 
</table>

**示例:**

<table style="width:453px;"> 
 <tbody> 
 <tr> 
 <td style="width:450px;"> INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`) VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2); </td> 
 </tr> 
 </tbody> 
</table>

**< 为避免表结构发生变化引发的错误，建议插入数据时****写明具体字段名****！>**

# **DML****语句****——****将查询结果插入新表** #

**编写****SQL****语句实现从学生表提取姓名、手机号两列数据存储到通讯录表中**

**语法:**

<table style="width:443px;"> 
 <tbody> 
 <tr> 
 <td style="width:440px;"> CREATE TABLE 新表(SELECT 字段1，字段2……&nbsp; FROM 原表); </td> 
 </tr> 
 </tbody> 
</table>

**示例:**

<table style="width:261px;"> 
 <tbody> 
 <tr> 
 <td style="width:258px;"> CREATE TABLE `phoneList`( &nbsp;&nbsp;&nbsp; SELECT `studentName`,`phone` &nbsp;&nbsp;&nbsp; FROM `student`); </td> 
 </tr> 
 </tbody> 
</table>

![70 4][]

**< 如新表已存在，将会报错！>**

## **数据更新** ##

**更新数据记录-语法：**

<table style="width:301px;"> 
 <tbody> 
 <tr> 
 <td style="width:298px;"> UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件]; </td> 
 </tr> 
 </tbody> 
</table>

**示例:**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> UPDATE student SET sex = ‘女’; UPDATE student SET address = ‘北京女子职业技术学校家政班’ WHERE address = ‘北京女子职业技术学校刺绣班’; </td> 
 </tr> 
 </tbody> 
</table>

## **数据删除** ##

**删除数据记录-语法:**

<table style="width:317px;"> 
 <tbody> 
 <tr> 
 <td style="width:314px;"> DELETE FROM 表名 [WHERE条件]; </td> 
 </tr> 
 </tbody> 
</table>

<可以选择性删除表中的某些记录>

<table style="width:318px;"> 
 <tbody> 
 <tr> 
 <td style="width:315px;">TRUNCATE TABLE 表名;</td> 
 </tr> 
 </tbody> 
</table>

<删除整个表，无法选择条件>

**<TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快>**

**示例:**

<table style="width:410px;"> 
 <tbody> 
 <tr> 
 <td style="width:407px;"> DELETE FROM student WHERE studentName = ‘王宝宝’; TRUNCATE TABLE student; </td> 
 </tr> 
 </tbody> 
</table>

# **什么是查询** #

![70 5][]

**<查询产生一个虚拟表**

**看到的是表形式显示的结果，但结果并不真正存储**

**每次执行查询只是从数据表中提取数据，并按照表的形式显示出来>**

**查询语法**

<table style="width:387px;"> 
 <tbody> 
 <tr> 
 <td style="width:384px;"> SELECT &nbsp;&nbsp;&nbsp;&lt;列名|表达式|函数|常量&gt; FROM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;表名&gt; [WHERE &nbsp;&nbsp;&nbsp;&lt;查询条件表达式&gt;] [ORDER BY &lt;排序的列名&gt;[ASC或DESC]]; </td> 
 </tr> 
 </tbody> 
</table>

**示例：**

<table style="width:473px;"> 
 <tbody> 
 <tr> 
 <td style="width:470px;"> SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` FROM `student` WHERE `gradeId` = 1 ORDER BY `studentNo`; </td> 
 </tr> 
 </tbody> 
</table>

## **数据查询****基础** ##

**查询全部的行和****列**

<table style="width:308px;"> 
 <tbody> 
 <tr> 
 <td style="width:305px;"> SELECT * FROM `student`; </td> 
 </tr> 
 </tbody> 
</table>

**查询部分列**

<table style="width:357px;"> 
 <tbody> 
 <tr> 
 <td style="width:354px;"> SELECT `studentNo`,`studentName`,`address` FROM `student` WHERE `address`=‘河南新乡’; </td> 
 </tr> 
 </tbody> 
</table>

## **数据查询****-****列别名** ##

**使用****AS****命名列**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `studentNo`&nbsp; AS 学生编号,`studentName` AS 学生姓名, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; `address `AS 学生地址&nbsp; FROM `student` WHERE `address` &lt;&gt; '河南新乡‘; </td> 
 </tr> 
 </tbody> 
</table>

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `firstName` + '.' + `lastName` AS 姓名 FROM `employees`; </td> 
 </tr> 
 </tbody> 
</table>

**<注意：**

**1. + 连接的数据类型必须兼容**

**2. 如果 + 连接字符型数据，结果为字符串数据的连接**

**3. 如果 + 连接数值型数据，结果为数值的和>**

## **数据查询****-****空行、常量列** ##

### **查询空行** ###

<table style="width:563px;"> 
 <tbody> 
 <tr> 
 <td style="width:560px;"> SELECT `studentName` FROM `student` WHERE `email` IS&nbsp; &nbsp; &nbsp;NULL; </td> 
 </tr> 
 </tbody> 
</table>

### **原来有数据，但数据被清除的****列如何查询？** ###

**使用常量列**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `studentName` AS 姓名，`address` AS 地址， ’北京信息中心’ AS 学校名称 FROM `student`; </td> 
 </tr> 
 </tbody> 
</table>

### **常用****函数****——****聚合函数** ###

<table style="width:507px;"> 
 <tbody> 
 <tr> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:201px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;函数名 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:303px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;作用 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:201px;"> &nbsp;AVG() </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:303px;"> 返回某字段的平均值 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:201px;"> COUNT() </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:303px;"> 返回某字段的行数 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:201px;"> MAX() </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:303px;"> 返回某字段的最大值 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:201px;"> MIN() </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:303px;"> 返回某字段的最小值 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:201px;"> SUM() </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:303px;"> 返回某字段的和 </td> 
 </tr> 
 </tbody> 
</table>

### **常用****函数****——****字符串函数** ###

<table style="width:604px;"> 
 <tbody> 
 <tr> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:141px;"> &nbsp; &nbsp; &nbsp; 函 数 名 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:177px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 作&nbsp;&nbsp;&nbsp; 用 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:285px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;举&nbsp;&nbsp;&nbsp; 例 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:141px;"> CONCAT(str1, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; str1...strn) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:177px;"> 字符串连接 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:285px;"> SELECT &nbsp;&nbsp;&nbsp;CONCAT('My','S','QL'); 返回：MySQL </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:141px;"> INSERT(str, pos,len, newstr) </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:177px;"> 字符串替换 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:285px;"> SELECT INSERT( &nbsp;&nbsp;&nbsp; '这是SQL Server数据库', &nbsp;&nbsp;&nbsp; 3,10,'MySQL'); 返回：这是MySQL数据库 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:141px;"> LOWER(str) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:177px;"> 将字符串转为小写 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:285px;"> SELECT LOWER('MySQL'); 返回：mysql </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:141px;"> UPPER(str) </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:177px;"> 将字符串转为大写 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:285px;"> SELECT UPPER('MySQL'); &nbsp;返回：MYSQL </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:141px;"> SUBSTRING &nbsp; (str,num,len) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:177px;"> 字符串截取 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:285px;"> SELECT SUBSTRING( &nbsp;&nbsp;&nbsp; 'JavaMySQLOracle',5,5); 返回：MySQL </td> 
 </tr> 
 </tbody> 
</table>

### **常用****函数****——****时间日期函数** ###

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:justify;vertical-align:middle;width:138px;"> 函数名 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;vertical-align:middle;width:297px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 作用 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> 举例（结果与当前时间有关） </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:138px;"> CURDATE() </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:297px;"> 获取当前日期 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT CURDATE(); 返回：2016-08-08 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:138px;"> CURTIME() </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:297px;"> 获取当前时间 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT CURTIME(); 返回：19:19:26 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:138px;"> NOW() </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:297px;"> 获取当前日期和时间 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT NOW(); 返回：2016-08-08 19:19:26 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:138px;"> WEEK(date) </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:297px;"> 返回日期date为一年中的第几周 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT WEEK(NOW()); 返回：26 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:138px;"> YEAR(date) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:297px;"> 返回日期date的年份 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT YEAR(NOW()); 返回：2016 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:138px;"> HOUR(time) </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:297px;"> 返回时间time的小时值 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT HOUR(NOW()); 返回：9 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:138px;"> MINUTE(time) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:297px;"> 返回时间time的分钟值 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:257px;"> SELECT MINUTE(NOW()); 返回：43 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:138px;"> DATEDIFF(date1,date2) </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:297px;"> 返回日期参数date1和date2之间相隔的天数 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;vertical-align:middle;width:257px;"> SELECT DATEDIFF(NOW(), &nbsp;&nbsp;&nbsp;&nbsp; '2008-8-8'); 返回：2881&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:138px;"> ADDDATE(date,n) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:297px;"> 计算日期参数date加上n天后的日期 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;vertical-align:middle;width:257px;"> SELECT ADDDATE(NOW(),5); 返回：2016-09-02&nbsp; 09:37:07&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

### **常用****函数****——****数学函数** ###

<table style="width:579px;"> 
 <tbody> 
 <tr> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:111px;"> 函数名 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:241px;"> 作&nbsp;&nbsp;&nbsp; 用 </td> 
 <td style="background-color:#0070c0;border-color:#2163b4;text-align:center;vertical-align:middle;width:225px;"> 举&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 例 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:111px;"> CEIL(x) </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:241px;"> 返回大于或等于数值x的最小整数 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:225px;"> &nbsp; SELECT CEIL(2.3) 返回：3 &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:111px;"> FLOOR(x) </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:241px;"> 返回小于或等于数值x的最大整数 </td> 
 <td style="background-color:#eaf1fb;border-color:#2163b4;text-align:justify;vertical-align:middle;width:225px;"> &nbsp; SELECT FLOOR(2.3) 返回：2 &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:111px;"> RAND() </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:241px;"> 返回0~1间的随机数 </td> 
 <td style="background-color:#d3e2f7;border-color:#2163b4;text-align:justify;vertical-align:middle;width:225px;"> &nbsp; SELECT RAND() 返回：0.5525468583708134 &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

<table style="width:579px;"> 
 <tbody> 
 <tr> 
 <td style="width:576px;">ROUND()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 返回四舍五入数&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; ROUND(5.6)&nbsp; &nbsp; &nbsp;返回：6&nbsp;&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

**ORDER BY****子句实现按一定顺序显示查询结果**

示例：把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `studentNo` AS 学生编号,(studentResult*0.9+5 ) AS 综合成绩 FROM `result` WHERE (`studentResult`*0.9+5) &gt;=60 ORDER BY studentResult DESC; </td> 
 </tr> 
 </tbody> 
</table>

## **LIMIT****子句** ##

**MySQL****查询语句中使用****LIMIT****子句限制结果集**

语法：

<table style="width:318px;"> 
 <tbody> 
 <tr> 
 <td style="width:315px;"> SELECT&nbsp; &lt;字段名列表&gt; FROM&nbsp; &lt;表名或视图&gt; [WHERE&nbsp; &lt;查询条件&gt;] [GROUP BY &lt;分组的字段名&gt;] [ORDER BY&nbsp; &lt;排序的列名&gt;[ASC 或 DESC]] [LIMIT [位置偏移量,]行数]; </td> 
 </tr> 
 </tbody> 
</table>

**查询所有年级编号为****1****的学员信息，按学号****升序排序**

**      1.显示前****4****条****记录**

**      2.每页****4****条，显示第****2****页，即从****第****5****条记录开始显示****4****条数据**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` FROM `student` WHERE `gradeId` = 1 ORDER BY studentNo LIMIT 4;--------------------------&gt;从第5条开始显示4条 </td> 
 </tr> 
 </tbody> 
</table>

**<使用LIMIT子句时，注意第1条记录的位置是0！>**

## **什么是子查询** ##

### **1.概念** ###

子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的[程序][Link 1]模块。当一个查询是另一个查询的条件时，称之为子查询。

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询或内层查询。上层的查询块称为父查询或外层查询。子查询的结果作为输入传递回“父查询”或“外部查询”。父查询将这个值结合到计算中，以便确定最后的输出。

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。以层层嵌套的方式来构造程序正是SQL中"结构化"的含义所在。

子查询是本质上就是一个完整 的SELECT 语句，它可以使一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

### 2.语法结构 ###

可用四种种语法来创建子查询：

1.带有比较运算符的子查询(sqlstatement)

comparison(>,<,=,!=)

2.带有ANY(some)或ALL谓词的子查询

comparison \[ANY | ALL | SOME\] (sqlstatement)

3.带有谓词IN的子查询

expression \[NOT\] IN (sqlstatement)

4.带有EXISTS谓词的子查询

\[NOT\] EXISTS (sqlstatement)

**编写****SQL****语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息**

![70 6][]

### **实现****方法一****：****分两步实现** ###

1.**查找****出“李斯文”的出生****日期**

<table style="width:535px;"> 
 <tbody> 
 <tr> 
 <td style="width:532px;"> SELECT `bornDate` FROM `student` WHERE `studentName` = '李斯文'; </td> 
 </tr> 
 </tbody> 
</table>

![70 7][]

2.**利用****WHERE****语句筛选出生日期比“李斯文”大的学生**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `studentNo`, `studentName`,`sex`,`bornDate`,`address` FROM `student` WHERE bornDate &gt; '1993-07-23'; </td> 
 </tr> 
 </tbody> 
</table>

### **实现方法二：采用子查询****实现** ###

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `studentNo`,`studentName`,`sex`,`bornDate`,`address` &nbsp;FROM `student` &nbsp;WHERE `bornDate` &gt; &nbsp; &nbsp; &nbsp; &nbsp; --------&gt;父查询 </td> 
 </tr> 
 </tbody> 
</table>

<table style="width:640px;"> 
 <tbody> 
 <tr> 
 <td style="width:637px;"> (SELECT `bornDate` FROM `student` WHERE `studentName`='李斯文');&nbsp; -------&gt;子查询 </td> 
 </tr> 
 </tbody> 
</table>

**子查询是一个嵌套在** **SELECT****、****INSERT****、****UPDATE** **或** **DELETE** **语句或其他子查询中的查询**

**子查询在****WHERE****语句中的一般用法**

**语法：**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT … FROM 表1 WHERE 字段1&nbsp; 比较运算符(子查询) </td> 
 </tr> 
 </tbody> 
</table>

**<先执行子查询，返回所有来自子查询的结果**

**再执行外围的父查询，返回查询的最终结果>**

**<注意：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个>**

### **3.IN****子****查询** ###

**查询****“****Logic** **Java”****课程考试成绩为60分的****学****生名单**

![70 8][]

**解决****方法：采用** **IN** **子查询**

![70 9][]

**常用****IN****替换等于（＝）的子****查询**

**IN****后面的子查询可以返回多条****记录**

### **查询参加“****Logic Java”****课程最近一次考试的****在读学生****名单** ###

**实现步骤：**

1.**获得** **“Logic Java”****课程****的课程编号**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT `subjectNo` FROM `subject` &nbsp;WHERE `subjectName`='Logic Java'; </td> 
 </tr> 
 </tbody> 
</table>

2.**根据****课程编号查询****得到“****Logic Java****”课程****最近一次的考试日期**

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`= ( SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' ); </td> 
 </tr> 
 </tbody> 
</table>

3.**根据****课程编号和最近一次的考试日期查询出在读学生信息**

**参考代码如下：**

![70 10][]

在嵌套查询中，子查询的结构往往是一个集合，所以谓词 IN是嵌套查询中最经常使用的谓词。

如查询与“刘晨”同一个系学习的学生。先要确定刘晨所在系名，在用它来查找所在在这个系中学习的学生。

<table style="width:280px;"> 
 <tbody> 
 <tr> 
 <td style="width:277px;"> SELECT 学号，姓名，系名 FROM 学生表 WHERE IN (SELECT 系名 FROM 学生表 WHERE 姓名=“刘晨”） </td> 
 </tr> 
 </tbody> 
</table>

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。

用 IN 谓词，只能在主查询检索那些记录，在子查询中的某些记录也包含和它们相同的值。相反，可用 NOT IN 在主查询中检索那样的记录，在子查询中没有包含与它们的值相同的记录。下列示例返回有比 25%更低 的折扣的所有产品：

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> SELECT * FROM Products WHERE ProductID NOT IN (SELECT ProductID FROM OrderDetails WHERE Discount &gt;= .25); </td> 
 </tr> 
 </tbody> 
</table>

### **4.使用子查询的规则：** ###

1）子查询必须“自身就是一个完整的查询”。即，它必须至少包括一个SELECT子句和FROM子句。

2）子查询SELECT语句不能包括在ORDER BY子句中。因为ORDER BY字句只能对最终查询结果排序，如果显示的输出需要按照特定顺序显示，那么ORDER BY子句应该作为外部查询的最后一个子句列出。

3）子查询“必须包括在一组括号中”，以便将它与外部查询分开。

4）如果将子查询放在外部查询的WHERE或HAVING子句中，那么该子查询只能位于比较运算符的“右边”。

![70 11][]

[70]: /images/20220511/0dae63be837d4b63ac1a7ec79c844733.png
[70 1]: /images/20220511/e5e617fc403c40afb3f80890422641c8.png
[70 2]: /images/20220511/3bffdb7885d84ddaaf80df8943e73e56.png
[70 3]: /images/20220511/2ca8b49ba9a64a4c96562be35ec0bf28.png
[70 4]: /images/20220511/1985fe9705a645cfa3315e4d38cec97d.png
[70 5]: /images/20220511/fc937194269d42d7948b158e57fba6be.png
[Link 1]: https://baike.baidu.com/item/%E7%A8%8B%E5%BA%8F/71525
[70 6]: /images/20220511/6fa270f7f3ba4cf6890adfc8632947fc.png
[70 7]: /images/20220511/cf78e83d6c664c07bae3c3a525322a9b.png
[70 8]: /images/20220511/cd95763867984718a8d2eaeaacb5b7ab.png
[70 9]: /images/20220511/0b4f401d10a74cbaa062769e69eec58f.png
[70 10]: /images/20220511/034562e51149453a88b2cf882f977bc2.png
[70 11]: /images/20220511/4528c72bfed74fb0a4116eaca41b2d66.png