这篇文章主要向大家介绍数据库-DML(数据操做语言)和DQL(数据查询语言),主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

MySQL的存储引擎

什么是存储引擎?

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

存储引擎的类型

MyISAMInnoDBMemory、CSV等9种

MyISAM与InnoDB 类型主要区别

名称数据库InnoDBexpressMyISAM安全
事务处理服务器支持数据结构不支持并发
数据行锁定函数支持学习不支持
外键约束支持不支持
全文索引不支持支持
表空间大小较大,约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 学生编号,(studentResult0.9+5 ) AS 综合成绩FROM resultWHERE (studentResult0.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,bornDateFROM studentWHERE 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 subjectWHERE 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子句中,那么该子查询只能位于比较运算符的“右边”。

最后修改:2021 年 10 月 29 日
如果觉得我的文章对你有用,请随意赞赏