数据库-DML（数据操作语言）和DQL（数据查询语言）
MySQL的存储引擎
什么是存储引擎？
关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。
存储引擎的类型
MyISAM、InnoDB 、Memory、CSV等9种
MyISAM与InnoDB类型主要区别
|
名称 |
InnoDB |
MyISAM |
|
事务处理 |
支持 |
不支持 |
|
数据行锁定 |
支持 |
不支持 |
|
外键约束 |
支持 |
不支持 |
|
全文索引 |
不支持 |
支持 |
|
表空间大小 |
较大,约2倍 |
较小 |
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%’;
修改存储引擎:修改my.ini配置文件
设置表的存储引擎
语法:
CREATE TABLE 表名(
#省略代码
)ENGINE=存储引擎;示例:
CREATE TABLE `myisam` (
id INT(4)
)ENGINE=MyISAM;
数据表的存储位置
MyISAM类型表文件
*.frm：表结构定义文件
*.MYD：数据文件
*.MYI：索引文件
InnoDB类型表文件
*.frm：表结构定义文件
ibdata1文件
注意:
存储位置->因操作系统而异，可查my.ini
|
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" innodb_data_home_dir="D:/MySQL Datafiles/" |
DML语句——插入单条数据记录
语法:
|
INSERT INTO 表名 [(字段名列表)] VALUES (值列表); |
注意:
1.字段名是可选的，如省略则依次插入所有字段
2.多个列表和多个值之间使用逗号分隔
3.值列表和字段名列表一一对应
4.如插入的是表中部分数据，字段名列表必填
示例:
|
INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`) VALUES('123','黄小平',1,'13956799999','1996-5-8'); |
DML语句——插入多条数据记录
语法:
|
INSERT INTO 新表（字段名列表） VALUES(值列表1),(值列表2),……,(值列表n); |
示例:
|
INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`) VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2); |
< 为避免表结构发生变化引发的错误，建议插入数据时写明具体字段名！>
DML语句——将查询结果插入新表
编写SQL语句实现从学生表提取姓名、手机号两列数据存储到通讯录表中
语法:
|
CREATE TABLE 新表(SELECT 字段1，字段2…… FROM 原表); |
示例:
|
CREATE TABLE `phoneList`( SELECT `studentName`,`phone` FROM `student`); |
< 如新表已存在，将会报错！>
数据更新
更新数据记录-语法：
|
UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件]; |
示例:
|
UPDATE student SET sex = ‘女’; UPDATE student SET address = ‘北京女子职业技术学校家政班’ WHERE address = ‘北京女子职业技术学校刺绣班’; |
数据删除
删除数据记录-语法:
|
DELETE FROM 表名 [WHERE条件]; |
<可以选择性删除表中的某些记录>
| TRUNCATE TABLE 表名; |
<删除整个表，无法选择条件>
<TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快>
示例:
|
DELETE FROM student WHERE studentName = ‘王宝宝’; TRUNCATE TABLE student; |
什么是查询
<查询产生一个虚拟表
看到的是表形式显示的结果，但结果并不真正存储
每次执行查询只是从数据表中提取数据，并按照表的形式显示出来>
查询语法
|
SELECT <列名|表达式|函数|常量> FROM <表名> [WHERE <查询条件表达式>] [ORDER BY <排序的列名>[ASC或DESC]]; |
示例：
|
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` FROM `student` WHERE `gradeId` = 1 ORDER BY `studentNo`; |
数据查询基础
查询全部的行和列
|
SELECT * FROM `student`; |
查询部分列
|
SELECT `studentNo`,`studentName`,`address` FROM `student` WHERE `address`=‘河南新乡’; |
数据查询-列别名
使用AS命名列
|
SELECT `studentNo` AS 学生编号,`studentName` AS 学生姓名, `address `AS 学生地址 FROM `student` WHERE `address` <> '河南新乡‘; |
|
SELECT `firstName` + '.' + `lastName` AS 姓名 FROM `employees`; |
<注意：
1. + 连接的数据类型必须兼容
2. 如果 + 连接字符型数据，结果为字符串数据的连接
3. 如果 + 连接数值型数据，结果为数值的和>
数据查询-空行、常量列
查询空行
|
SELECT `studentName` FROM `student` WHERE `email` IS NULL; |
原来有数据，但数据被清除的列如何查询？
使用常量列
|
SELECT `studentName` AS 姓名，`address` AS 地址， ’北京信息中心’ AS 学校名称 FROM `student`; |
常用函数——聚合函数
|
函数名 |
作用 |
|
AVG() |
返回某字段的平均值 |
|
COUNT() |
返回某字段的行数 |
|
MAX() |
返回某字段的最大值 |
|
MIN() |
返回某字段的最小值 |
|
SUM() |
返回某字段的和 |
常用函数——字符串函数
|
函 数 名 |
作 用 |
举 例 |
|
CONCAT(str1, str1...strn) |
字符串连接 |
SELECT CONCAT('My','S','QL'); 返回：MySQL |
|
INSERT(str, pos,len, newstr) |
字符串替换 |
SELECT INSERT( '这是SQL Server数据库', 3,10,'MySQL'); 返回：这是MySQL数据库 |
|
LOWER(str) |
将字符串转为小写 |
SELECT LOWER('MySQL'); 返回：mysql |
|
UPPER(str) |
将字符串转为大写 |
SELECT UPPER('MySQL'); 返回：MYSQL |
|
SUBSTRING (str,num,len) |
字符串截取 |
SELECT SUBSTRING( 'JavaMySQLOracle',5,5); 返回：MySQL |
常用函数——时间日期函数
|
函数名 |
作用 |
举例（结果与当前时间有关） |
|
CURDATE() |
获取当前日期 |
SELECT CURDATE(); 返回：2016-08-08 |
|
CURTIME() |
获取当前时间 |
SELECT CURTIME(); 返回：19:19:26 |
|
NOW() |
获取当前日期和时间 |
SELECT NOW(); 返回：2016-08-08 19:19:26 |
|
WEEK(date) |
返回日期date为一年中的第几周 |
SELECT WEEK(NOW()); 返回：26 |
|
YEAR(date) |
返回日期date的年份 |
SELECT YEAR(NOW()); 返回：2016 |
|
HOUR(time) |
返回时间time的小时值 |
SELECT HOUR(NOW()); 返回：9 |
|
MINUTE(time) |
返回时间time的分钟值 |
SELECT MINUTE(NOW()); 返回：43 |
|
DATEDIFF(date1,date2) |
返回日期参数date1和date2之间相隔的天数 |
SELECT DATEDIFF(NOW(), '2008-8-8'); 返回：2881 |
|
ADDDATE(date,n) |
计算日期参数date加上n天后的日期 |
SELECT ADDDATE(NOW(),5); 返回：2016-09-02 09:37:07 |
常用函数——数学函数
|
函数名 |
作 用 |
举 例 |
|
CEIL(x) |
返回大于或等于数值x的最小整数 |
SELECT CEIL(2.3) 返回：3
|
|
FLOOR(x) |
返回小于或等于数值x的最大整数 |
SELECT FLOOR(2.3) 返回：2
|
|
RAND() |
返回0~1间的随机数 |
SELECT RAND() 返回：0.5525468583708134
|
| ROUND() 返回四舍五入数 SELECT ROUND(5.6) 返回：6 |
ORDER BY子句实现按一定顺序显示查询结果
示例：把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序
|
SELECT `studentNo` AS 学生编号,(studentResult*0.9+5 ) AS 综合成绩 FROM `result` WHERE (`studentResult`*0.9+5) >=60 ORDER BY studentResult DESC; |
LIMIT子句
MySQL查询语句中使用LIMIT子句限制结果集
语法：
|
SELECT <字段名列表> FROM <表名或视图> [WHERE <查询条件>] [GROUP BY <分组的字段名>] [ORDER BY <排序的列名>[ASC 或 DESC]] [LIMIT [位置偏移量,]行数]; |
查询所有年级编号为1的学员信息，按学号升序排序
1.显示前4条记录
2.每页4条，显示第2页，即从第5条记录开始显示4条数据
|
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` FROM `student` WHERE `gradeId` = 1 ORDER BY studentNo LIMIT 4;-------------------------->从第5条开始显示4条 |
<使用LIMIT子句时，注意第1条记录的位置是0！>
什么是子查询
1.概念
子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。
在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语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息
实现方法一：分两步实现
1.查找出“李斯文”的出生日期
|
SELECT `bornDate` FROM `student` WHERE `studentName` = '李斯文'; |
2.利用WHERE语句筛选出生日期比“李斯文”大的学生
|
SELECT `studentNo`, `studentName`,`sex`,`bornDate`,`address` FROM `student` WHERE bornDate > '1993-07-23'; |
实现方法二：采用子查询实现
|
SELECT `studentNo`,`studentName`,`sex`,`bornDate`,`address` FROM `student` WHERE `bornDate` > -------->父查询 |
|
(SELECT `bornDate` FROM `student` WHERE `studentName`='李斯文'); ------->子查询 |
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
子查询在WHERE语句中的一般用法
语法：
|
SELECT … FROM 表1 WHERE 字段1 比较运算符(子查询) |
<先执行子查询，返回所有来自子查询的结果
再执行外围的父查询，返回查询的最终结果>
<注意：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个>
3.IN子查询
查询“Logic Java”课程考试成绩为60分的学生名单
解决方法：采用 IN 子查询
常用IN替换等于（＝）的子查询
IN后面的子查询可以返回多条记录
查询参加“Logic Java”课程最近一次考试的在读学生名单
实现步骤：
1.获得 “Logic Java”课程的课程编号
|
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'; |
2.根据课程编号查询得到“Logic Java”课程最近一次的考试日期
|
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`= ( SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' ); |
3.根据课程编号和最近一次的考试日期查询出在读学生信息
参考代码如下：
在嵌套查询中，子查询的结构往往是一个集合，所以谓词 IN是嵌套查询中最经常使用的谓词。
如查询与“刘晨”同一个系学习的学生。先要确定刘晨所在系名，在用它来查找所在在这个系中学习的学生。
|
SELECT 学号，姓名，系名 FROM 学生表 WHERE IN (SELECT 系名 FROM 学生表 WHERE 姓名=“刘晨”） |
本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。
用 IN 谓词，只能在主查询检索那些记录，在子查询中的某些记录也包含和它们相同的值。相反，可用 NOT IN 在主查询中检索那样的记录，在子查询中没有包含与它们的值相同的记录。下列示例返回有比 25%更低 的折扣的所有产品：
|
SELECT * FROM Products WHERE ProductID NOT IN (SELECT ProductID FROM OrderDetails WHERE Discount >= .25); |
4.使用子查询的规则：
1）子查询必须“自身就是一个完整的查询”。即，它必须至少包括一个SELECT子句和FROM子句。
2）子查询SELECT语句不能包括在ORDER BY子句中。因为ORDER BY字句只能对最终查询结果排序，如果显示的输出需要按照特定顺序显示，那么ORDER BY子句应该作为外部查询的最后一个子句列出。
3）子查询“必须包括在一组括号中”，以便将它与外部查询分开。
4）如果将子查询放在外部查询的WHERE或HAVING子句中，那么该子查询只能位于比较运算符的“右边”。
上一篇： php获取远程图片体积大小的实例
下一篇： Oracle做学生信息系统的脚本