这篇文章主要向大家介绍数据库-DML(数据操做语言)和DQL(数据查询语言),主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。
MySQL的存储引擎
什么是存储引擎?
关系数据库表是用于存储和组织信息的数据结构,能够将表理解为由行和列组成的表格,相似于Excel的电子表格的形式。有的表简单,有的表复杂,有的表根本不用来存储任何长期的数据,有的表读取时很是快,可是插入数据时不好;而咱们在实际开发过程当中,就可能须要各类各样的表,不一样的表,就意味着存储不一样类型的数据,数据的处理上也会存在着差别,那么。对于MySQL来讲,它提供了不少种类型的存储引擎,咱们能够根据对数据处理的需求,选择不一样的存储引擎,从而最大限度的利用MySQL强大的功能。mysql
存储引擎的类型
MyISAM
、InnoDB
、Memory
、CSV等9种
MyISAM与InnoDB 类型主要区别
名称数据库 | InnoDBexpress | 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` <> '河南新乡‘;
SELECTfirstName
+ '.' +lastName
AS 姓名 FROMemployees
;注意:
注意:
- 链接的数据类型必须兼容
- 若是 + 链接字符型数据,结果为字符串数据的链接
- 若是 + 链接数值型数据,结果为数值的和>
数据查询-空行、常量列
查询空行
SELECTstudentName
FROMstudent
WHERE
原来有数据,但数据被清除的列如何查询?
使用常量列
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 学生编号,(studentResult0.9+5 ) AS 综合成绩FROM result WHERE (studentResult 0.9+5) >=60ORDER BY studentResult DESC; |
---|
LIMIT子句
MySQL查询语句中使用LIMIT子句限制结果集
语法:
SELECT <字段名列表>FROM <表名或视图>WHERE <查询条件>[ORDER BY <排序的列名>[ASC 或 DESC]][LIMIT [位置偏移量,]行数]; |
---|
查询全部年级编号为1的学员信息,按学号升序排序
1.显示前4条记录
2.每页4条,显示第2页,即从第5条记录开始显示4条数据
SELECT studentNo ,studentName ,phone ,address ,bornDate FROM student WHERE gradeId = 1ORDER BY studentNoLIMIT 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 ProductsWHERE ProductID NOT IN(SELECT ProductID FROM OrderDetailsWHERE Discount >= .25); |
---|
4.使用子查询的规则:
1)子查询必须“自身就是一个完整的查询”。即,它必须至少包括一个SELECT子句和FROM子句。
2)子查询SELECT语句不能包括在ORDER BY子句中。由于ORDER BY字句只能对最终查询结果排序,若是显示的输出须要按照特定顺序显示,那么ORDER BY子句应该做为外部查询的最后一个子句列出。
3)子查询“必须包括在一组括号中”,以便将它与外部查询分开。
4)若是将子查询放在外部查询的WHERE或HAVING子句中,那么该子查询只能位于比较运算符的“右边”。