MySQL数据库中的DML语句、DQL语句以及高级查询重点总结

使用SQlyog管理工具：

1. 工具创建数据库：

数据库字符集：存什么字符就要选什么编码，如：要是中文就要选utf8

数据库校对规则：字符在比较时要遵循一定的规则，如：中文编码规则，不区分大小写要选utf8_general_ci

 

2. 工具建表

①显示表结构：desc表名

②显示表创建语句：show create table 表名

 

3. 字段的约束及属性

名称	关键字	说明
非空约束	NOT NULL	字段不允许为空
默认约束	DEFAULT	赋予某字段默认值
唯一约束	UNIQUE KEY(UK)	设置字段的值是唯一的 允许为空，但只能有一个空值
主键约束	PRIMARY KEY(PK)	设置该字段为表的主键 可唯一标识该表记录，不能有空值
外键约束	FOREIGN KEY(FK)	用于在两表之间建立关系 需要指定引用主表的哪一字段
自动增长	AUTO_INCREMENT	设置该列为自增字段 默认每条自增1 通常用于设置主键

①主键

CREATE TABLE student（

   `studentNo` INT(4)  PRIMARY KEY,

   ……）;

 

②注释

CREATE TABLE [IF NOT EXISTS] `表名` (  

`字段1` 数据类型 [字段属性|约束] [索引] [注释（comment）],

`字段2` 数据类型 [字段属性|约束] [索引] [注释],

  ……

`字段n` 数据类型 [字段属性|约束] [索引] [注释]  

)[表类型] [表字符集] [注释];     

CREATE TABLE test (

    `id` int(11) UNSIGNED COMMENT ‘编号’

)COMMENT='测试表’;

 

③设置字符集编码

a. 给表设字符集：

CREATE TABLE [IF NOT EXISTS] 表名（

     #省略代码

）CHARSET = 字符集名;

b. 给库设字符集：

CREATE DATABASE ******/*!40100 DEFAULT CHARACTER SET utf8 */

c. 给列设字符集

CREATE TABLE a(

  address CHAR(5) CHARACTER SET gbk

)

 

④设置表的类型

MySQL的数据表的类型:

MyISAM、InnoDB、HEAP、 BOB、CSV等

常见的MyISAM与InnoDB类型

名称	MylSAM	InnoDB
事务处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约2倍

 

CREATE TABLE [IF NOT EXISTS] 表名（

     #省略代码

）ENGINE = MyISAM;

或者

CREATE TABLE表名(

     #省略代码

）ENGINE= InnoDB;

适用场合：

1. 使用MyISAM：节约空间及响应速度快；不需事务，空间小，以查询访问为主
2. 使用InnoDB：安全性，事务处理及多用户操作数据表；多删除、更新操作，安全性高，事务处理及并发控制

注：查看mysql所支持的引擎类型（表类型）的方法：SHOW ENGINES;

查看默认引擎：SHOW VARIABLES LIKE 'storage_engine';

修改存储引擎：修改my.ini配置文件：default-storage-engine= InnoDB

【改为其他存储存储】

 

数据表的存储位置：

MySQL数据表以文件方式存放在磁盘中：

1. 包括表文件、数据文件以及数据库的选项文件
2. 位置：MySQL安装目录\data下存放数据表。目录名对应数据库名，该目录下文件名对应数据表

注：

InnoDB类型数据表只有一个*. frm文件，以及上一级目录的ibdata1文件

MylSAM类型数据表对应三个文件：

*. frm —— 表结构定义文件

*. MYD —— 数据文件

*. MYI —— 索引文件

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

datadir= "C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

innodb_data_home_dir="D:/MySQL Datafiles/"

 

数据库数据管理：

1. 数据库意义

①数据存储

②数据管理

2. 管理数据库数据方法

①通过SQLyog等管理工具管理数据库数据

②通过DML语句管理数据库数据

 

DML语言(数据操作语言)：

用于操作数据库对象中所包含的数据：

包括：

①INSERT（添加数据语句）

②UPDATE（更新数据语句）

③DELETE（删除数据语句）

 

1. 添加数据：INSERT命令

语法：INSERT INTO 表名 [(字段1,字段2,字段3,……)] VALUES ('  值1' ,'值2', '值3', '值4',……);

注：

①字段名是可选的，如省略则依次插入所有字段；字段或值之间用英文逗号隔开

②"字段1,字段2…"该部分可省略，但添加的值务必与表结构数据列顺序相对应，且数量一致；如插入的是表中部分数据，字段名列表必填

③可同时插入多条数据，values后用英文逗号隔开

示例：

#使用语句如何增加数据

INSERT INTO grade(gradename) VALUES('大一');  【尽量用这种写全的带自增的方法】

INSERT INTO grade VALUES(5,'大二');

INSERT INTO grade VALUES(6,'大二');

 

2. 插入多条数据记录

语法：INSERT INTO 表名 [(字段1,字段2,字段3,……)] VALUES ('  值1' ,'值2', '值3', '值4',……),('     值1' ,'值2', '值3', '值4',……);

【这种写法是MySQL里面独有的其他数据库没有，而且为避免表结构发生变化引发的错误，建议插入数据时写明具体字段名】

示例：

INSERT INTO grade(gradename) VALUES('大三'),("大四");

INSERT INTO student(studentno,studentname,sex,gradeid,phone,address,birthday,email,identitycard)

VALUES(1001,'张三',DEFAULT,5,'13833301425',NULL,NOW(),NULL,'22432432'),

(1002,'张',2,5,'144443301425','北京',NOW(),'82321312@qq.com','2343243243242')

【NOW()代表当前时间是一个函数】

 

3. 将查询结果插入新表

语法：CREATE TABLE 新表(SELECT 字段1，字段2……  FROM 原表);

如新表已存在，不能重复创建

示例：

CREATE TABLE `phoneList`(

SELECT `studentName`,`phone` FROM `student`);

 

4. 数据更新：UPDATE命令

语法：UPDATE表名SET column_name= value [,column_name2 = value2,…] [ WHERE condition];

注：

①column_name 为要更改的数据列

②value为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果

③condition为筛选条件，如不指定则修改该表的所有列数据

示例：

#使用语句修改数据

#将张三的地址改为中国南京

UPDATE student SET address='中国南京' WHERE studentno=1001

UPDATE student SET sex = ‘女’;

#同时修改多列

UPDATE student SET address='中国南京',email='1923234@qq.com' WHERE studentno=1001

 

5. WHERE条件子句

①简单理解为：有条件地从表中筛选数据

②WHERE中的运算符

运算符	含义	范例	结果
=	等于	5=6	false
<>或!=	不等于	5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
BETWEEN	在某个范围之间	BETWEEN 5 AND 10	-
AND	并且	5>1 AND 1>2	false
OR	或	5>1 0R 1>2	true

示例：

#条件可以使用运算符

UPDATE student SET sex=1 WHERE studentno=1001 OR studentno=1002 OR studentno=1003 OR studentno=1004

UPDATE student SET sex=2 WHERE studentno>=1001 AND studentno<=1004

UPDATE student SET sex=1 WHERE studentno BETWEEN 1001 AND 1004

#使用函数

UPDATE student SET studentname=CONCAT("姓名：",studentname)

 

6. 删除数据：DELETE命令（有主外键关系的都删不了）

语法：

#该表表类型（存储引擎）InnoDB

DELETE FROM表名 [WHERE condition ];

注：condition为筛选条件，如不指定则删除该表的所有列数据

示例一：

#删除数据

DELETE FROM grade WHERE gradeid=7;

示例：

CREATE TABLE test(

id INT(4) PRIMARY KEY AUTO_INCREMENT,

coll VARCHAR(20) NOT NULL

);

INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

#删除表全部数据（不带where条件的delete）

#自增的当前值依然从原来的基础上进行

DELETE FROM test;

INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

【执行删除之后再次执行插入操作序号会从4，5，6开始计数；删除时会一行一行删除并记录日志】

示例二

#创建两个表，存储引擎分别为InnoDB和MyISAM

CREATE TABLE test1(

id INT(4) PRIMARY KEY AUTO_INCREMENT,

coll VARCHAR(20) NOT NULL

)ENGINE=INNODB;

CREATE TABLE test2(

id INT(4) PRIMARY KEY AUTO_INCREMENT,

coll VARCHAR(20) NOT NULL

)ENGINE=MYISAM;

INSERT INTO test1(coll) VALUES('row1'),('row2'),('row3');

INSERT INTO test2(coll) VALUES('row1'),('row2'),('row3');

#清空表数据

DELETE FROM test1;

DELETE FROM test2;

#id-->6

#重启数据库服务后，test1：1，2，3；test2:7,8,9

#同样使用delete from清空数据库服务后，对于InnoDB的表，自增列从初始值重新开始（该数据放到内存里断电即失）

#而MyISAM类型的表，自增列依然从上一个自增数据基础上开始（该数据放到文件里能保存下来，不管怎样重启数据库文件都丢不了）

 

7. 删除数据：TRUNCATE命令（有主外键关系的都删不了）

用于完全清空表数据，但表结构、索引、约束等不变

语法：TRUNCATE [TABLE] table_name

示例：

#该表表类型（存储引擎）InnoDB

CREATE TABLE test(

id INT(4) PRIMARY KEY AUTO_INCREMENT,

coll VARCHAR(20) NOT NULL

);

INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

#删除表数据（truncate）

#自增值恢复到初始值重新开始

TRUNCATE TABLE test;

INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

【执行之后会接着从1，2，3开始计数；在删除时会把记录全部删除也不会记录日志】

TRUNCATE命令和DELETE命令的区别：

①相同：都能删除数据、不删除表结构，但TRUNCATE速度更快

②不同：

1. 使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
2. 使用TRUNCATE TABLE不会对事务有影响

 

DQL（Data Query Language）语言：数据查询语言

①查询数据库数据,如SELECT语句

②简单的单表查询或多表的复杂查询和嵌套查询

③数据库语言中最核心、最重要的语句

④使用频率最高的语句

 

查询语法SELECT：

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE …]                      #指定结果需满足的条件

[GROUP BY …]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                   #指定查询记录按一个或者多个条件排序

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }]; #指定查询的记录从哪条至哪条

【上面的顺序是固定的】

 

注：

[ ]括号代表可选的；

{ }括号代表必须的；

# 代表MySQL语句中的注释符，也可以用   /*该处为注释*/

 

where条件语句：

语法：

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE …]                      #指定结果需满足的条件

[GROUP BY …]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                   #指定查询记录按一个或者多个条件排序

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }]; #指定查询的记录从哪条至哪条

1. 用于检索数据表中符合条件的记录
2. 搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假
3. 搜索条件的组成

①逻辑操作符

操作符名称	语法	描述
AND 或 &&	a AND b或a && b	逻辑与，同时为真结果才为真
OR 或 ||	a OR b或a || b	逻辑或，只要一个为真，则结果为真
NOT 或 !	NOT a或 ! a	逻辑非，若操作数为假，结果则为真

 

②比较操作符

操作符名称	语法	描述
IS NULL	a IS NULL	若操作符为NULL则结果为真
IS NOT NULL	a IS NOT NULL	著操作符不为NULL.则结果为真
BETMEEN	a BETWEEN b AND c	著a范围在b与c之间则结果为真
LIKE	a LIKE b	SOL模式匹配，若a匹配b，则结果为真
IN	a IN (a1，a2，a3，…)	若a等于a1，a2…中的某一个，则结果为真

注：

1. 数值数据类型的记录之间才能进行算术运算
2. 相同数据类型的数据之间才能进行比较

 

简单查询语句汇总示例：

1. 查询所有学生信息（所有列，效率低）

SELECT * FROM student;

#查询指定列（学号，姓名）

SELECT studentno,studentname FROM student;

 

2. 取别名（原表里的名字没有变，只影响了查询出来的结果，想修改原表数据要update）

SELECT studentno AS 学号,studentname AS 姓名 FROM student;

#AS可以省略

SELECT studentno 学号,studentname 姓名 FROM student;

#使用as也可以为表取别名（作用：单表查询意义不大，但是当多个表的时候取别名就好操作，当不同的表里有相同名字的列的时候区分就会好区分）

SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;

#给表取别名的AS也可以省略

SELECT studentno 学号,studentname 姓名 FROM student s;

 

3. 使用CONCAT，为查询结果取一个新名字

SELECT CONCAT('姓名：',studentname) AS 新姓名 FROM student;

#查看考试成绩

SELECT * FROM result;

#查看哪些同学参加了考试（学号）

SELECT studentno FROM result;

#查看哪些同学参加了考试（学号）——去除重复项（distinct，默认是all）

SELECT DISTINCT studentno FROM result;

 

4. SELECT查询中可以使用表达式

SELECT @@auto_increment_increment

SELECT VERSION()

SELECT 100*3-1 AS 计算结果

#给学员考试成绩集体提分1分

SELECT studentno,studentresult+1 AS '提分后' FROM result;

#满足条件的查询（where）考试

SELECT studentno,studentresult

FROM result

WHERE studentresult >=95 AND studentresult<=100;

 

5. 精确查询

SELECT studentno,studentresult

FROM result

WHERE studentno=1000;

#除了1000号同学，我要其他同学的考试成绩

SELECT studentno,studentresult

FROM result

WHERE studentno!=1000;

SELECT studentno,studentresult

FROM result

WHERE NOT studentno=1000;

 

6. 模糊查询 between and\ like \in \ is null\ is not null

SELECT studentno,studentresult

FROM result

WHERE studentresult BETWEEN 95 AND 100;

#或者写成

SELECT studentno,studentresult

FROM result

WHERE studentresult >=95 && studentresult<=100;

#查询姓李的同学的学号及姓名

 

7. 模糊查询中like结合使用的通配符：%（0到任意个字符）   _ (一个字符)  【like使用的通配符就这两个】

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '李%';

#姓李的，但是名字中只有一个字

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '李_';

#姓李的，但是名字中只有两个字

#一个_代表一个字，两个_代表两个字

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '李_';

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '李__';

#想要先输出两个字的后输出三个字的

SELECT studentno,studentname FROM student

WHERE studentname LIKE '李_';

UNION

SELECT studentno,studentname FROM student

WHERE studentname LIKE '李__';

#姓名中含有“文”字的同学

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '%文%';

 

8. 查询有%号的同学的学号及姓名（此时要使用转义符\）

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '%\%%';

#查询有_号的同学的学号及姓名

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '%\_%';

#转义符\   使用ESCAPE关键字，来使用自己的转义符（:）

SELECT studentno,studentname

FROM student

WHERE studentname LIKE '%:%%' ESCAPE ':';

 

9. 模糊查询：in   null

SELECT studentno,studentname

FROM student

WHERE studentno IN(1000,1001,1002,1003)

SELECT studentno,studentname

FROM student

WHERE address IN('北京','安徽','苏州','扬州');

 

10. null 空

#查询出生日期没有填写的同学   

#=null  是错误的

SELECT studentno,studentname

FROM student

WHERE borndate=NULL;

#和null比较必须写is null

SELECT studentno,studentname

FROM student

WHERE borndate IS NULL;

#查询出生日期填写的同学，也就是出生日期不为空的同学

SELECT studentno,studentname

FROM student

WHERE borndate IS NOT NULL;

 

11. 区别空字符串与null

#查询家庭住址没有填写的同学，是没有填写同时包括为null的，要写在一起

SELECT studentno,studentname

FROM student

WHERE address="" OR address IS NULL;

 

EXISTS子查询：

语法：

SELECT …… FROM 表名 WHERE EXISTS(子查询);

子查询有返回结果： EXISTS子查询结果为TRUE

子查询无返回结果： EXISTS子查询结果为FALSE，外层查询不执行

 

注：

相关子查询，非相关子查询

非相关子查询示例：

SELECT r.studentNo,r.`studentResult`

FROM result r WHERE

EXISTS(SELECT * FROM result t WHERE

t.studentResult>=80)

相关子查询示例：

SELECT r.studentNo,r.`studentResult`

FROM result r WHERE

EXISTS(SELECT * FROM result t WHERE

t.studentResult>=80

AND r.` studentNo `=t.` studentNo `)

 

 

连接查询：

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE …]                      #指定结果需满足的条件

[GROUP BY …]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                   #指定查询记录按一个或者多个条件排序

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }];#指定查询的记录从哪条至哪条

 

1. 内连接  inner join 

查询两个表中的结果集中的交集

方法一：

SELECT `student`.`studentName`,`result`.`subjectNo`,`result`.`studentResult`

FROM `student`,`result`

WHERE `student`.`studentNo` = `result`.`studentNo`;

 

方法二：

SELECT s.`studentName`,r.`subjectNo`,r.`studentResult`

FROM `student` AS s

INNER JOIN `result` AS r ON (s.`studentNo` = r.`studentNo`);

 

三个表内连接的方法：

方法一：

SELECT s.`studentName`,r.`studentResult`,sub.SubjectName

FROM result r

INNER JOIN student s

ON s.StudentNo=r.StudentNo

INNER JOIN `subject` sub

ON sub.SubjectNo=r.SubjectNo;

 

方法二：

SELECT s.`studentName`,r.`studentResult`,sub.SubjectName

FROM result r,student s,`subject` sub

WHERE s.StudentNo=r.StudentNo AND sub.SubjectNo=r.SubjectNo;

 

2. 外连接  outer join

①左外连接  left join  ：

以左表作为基准，右边表来一一匹配，匹配不上的，返回左表的记录，右表以null填充

示例：

SELECT   s.studentName,r.subjectNo,r.studentResult

FROM     student AS s

LEFT JOIN result AS r

ON s.studentNo = r.studentNo;

 

②右外连接  right join  ：

以右表作为基准，左边表来一一匹配，匹配不上的，返回右表的记录，左表以null填充

3. 自连接（自连接的表一定要取别名）

4. 等值连接（等同于内连接）

5. 非等值连接

 

不同的SQL JOIN对比：

JOIN对比

操作符名称	描述
INNER JOIN（JOIN）	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表中返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表中返回所有的行

 

连接查询语句汇总示例：

#查询参加了考试的同学信息（学号、学生姓名、科目编号、分数）

SELECT * FROM student;

SELECT * FROM result;

#思路：（1）分析需求，确定查询的列来源于两个表 student  result，连接查询

#（2）确定使用哪一种连接查询？  ——内连接；右连接；等值连接

1. 内连接

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

INNER JOIN result AS r

ON r.StudentNo=s.StudentNo

#JOIN代表就是INNER JOIN

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

JOIN result AS r

ON r.StudentNo=s.StudentNo

 

2. 左外连接（查询了所有的同学，不考试的也查出来）

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

LEFT JOIN result AS r

ON r.StudentNo=s.StudentNo

 

3. 右外连接

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

RIGHT JOIN result AS r

ON r.StudentNo=s.StudentNo

#查一下缺考的同学

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

LEFT JOIN result AS r

ON r.StudentNo=s.StudentNo

WHERE studentresult IS NULL

#当join和where在一起用时要先写连接查询join后写where

 

4. 等值连接（和内连接效果一样）

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

INNER JOIN result AS r

WHERE r.StudentNo=s.StudentNo

 

5. 非等值连接（返回的记录行数，左边表的行数m*右边表的行数n）

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

 

6. 连接查询（自连接）

CREATE TABLE IF NOT EXISTS practice(

       num INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

       pnum INT(10) NOT NULL,

       practiceName VARCHAR(50) NOT NULL,

       PRIMARY KEY(num)

)

INSERT INTO practice

VALUES(2,1,"数学"),(3,1,"英语"),(4,1,"语文"),(5,2,"高数"),(6,3,"音标"),(7,4,"古文"),(8,2,"线代"),(9,3,"语法"),(10,4,"古诗");

SELECT * FROM practice AS p

#编写sql语句，将栏目的父子关系呈现出来，（父栏目名称、子栏目名称）

#父栏目    子栏目

#数学      高数

#数学      线代

#英语      音标

#英语      语法

#语文      古文

#语文      古诗

#把practice表看做两张一模一样的表（自连接），然后将这两张表连接查询（自连接的表一定要取别名）

SELECT a.practiceName AS '父栏目', b.practiceName AS '子栏目'

FROM practice AS a,practice AS b

WHERE a.num=b.pnum

 

#思考题：查询参加了考试的同学信息（学号，学生姓名、科目号、分数）

SELECT s.studentno,studentname,subjectno,studentresult

FROM student AS s

INNER JOIN result AS r

ON r.StudentNo=s.StudentNo

 

#查询参加了考试的同学信息（学号，学生姓名、科目名称、分数）

#连接第三个表就在前两个表写好之后再连第三个表

#和前后两个表都有关系的放在中间

SELECT s.studentno,studentname,subjectname,studentresult

FROM student AS s

INNER JOIN result AS r

ON r.StudentNo=s.StudentNo

INNER JOIN `subject` AS sub

ON r.SubjectNo = sub.subjectno

#查询学员及所属的年级（学号  学生姓名  年级名）

SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名

FROM student

INNER JOIN grade

ON student.GradeId=grade.GradeID

#查询科目及所属的年级（科目名称   年级名称）

SELECT subjectname AS 科目名称,gradename AS 年级名称

FROM `subject`

INNER JOIN grade

ON subject.GradeID=grade.GradeID

 

分组查询GROUP BY：

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE …]                      #指定结果需满足的条件

[GROUP BY …]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                  #指定查询记录按一个或者多个条件排序

（默认是ASC：升序排列    DESC：降序排列）  

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }];#指定查询的记录从哪条至哪条

使用GROUP BY关键字对查询结果分组：

1. 对所有的数据进行分组统计
2. 分组的依据字段可以有多个，并依次分组
3. 与HAVING结合使用，进行分组后的数据筛选

 

示例：

#查询不同课程的平均分，最高分，最低分

SELECT subjectno,SUM(studentresult) AS 总和,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分

FROM result

GROUP BY subjectno;

 

分组之后再限制条件：

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE…]                      #指定结果需满足的条件

[GROUP BY…]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                  #指定查询记录按一个或者多个条件排序

（默认是ASC：升序排列    DWHERE ESC：降序排列）  

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }];#指定查询的记录从哪条至哪条

HAVING的使用方法和WHERE相同，但是不同点是HAVING用在分组GROUP BY之后

WHERE与HAVING对比：

1. WHERE子句：用来筛选 FROM 子句中指定的操作所产生的行
2. GROUP BY子句：用来分组 WHERE 子句的输出
3. HAVING子句：用来从分组的结果中筛选行

 

示例：

#查询不同课程的平均分，最高分，最低分；并且取出大于80分的

SELECT subjectno,SUM(studentresult) AS 总和,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分

FROM result

GROUP BY subjectno;

HAVING 平均分>80;

 

按条件排序：

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE …]                      #指定结果需满足的条件

[GROUP BY …]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                  #指定查询记录按一个或者多个条件排序

（默认是ASC：升序排列    DESC：降序排列）  

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }];#指定查询的记录从哪条至哪条

 

示例：

#查询《数据库结构-1》的所有的考试结果（学号   学生姓名   科目名称   成绩），按成绩降序排列

SELECT s.studentno,studentname,subjectname,studentresult

FROM student AS s

INNER JOIN result AS r

ON r.StudentNo=s.StudentNo

INNER JOIN `subject` AS sub

ON r.SubjectNo = sub.subjectno

WHERE subjectname='数据库结构-1'

ORDER BY studentresult ASC   #默认ASC升序

ORDER BY studentresult DESC  #DESC降序排列

ORDER BY studentresult DESC,studentno ASC    #成绩降序排列，学号升序排列

#常见错误：ORDER BY studentresult,studentno DESC  （此时的排序规则是按照成绩：studentresult升序排列，之后按照学号studentno降序排列）

 

分页操作：

分页必须会，必会用到

优点：提高用户体验、增加网络传输速度、缓解查询压力

SELECT [ALL | DISTINCT]

{ * | table.* | [table.field1 [as alias1] [,table.field2 [as alias2]] [,…]] }

FROM table_name [as table_alias ]

[left | out | inner join table_name2 ]  #联合查询

[WHERE …]                      #指定结果需满足的条件

[GROUP BY …]                   #指定结果按照哪几个字段来分组

[HAVING …]                     #过滤分组的记录必须满足的次要条件

[ORDER BY …]                  #指定查询记录按一个或者多个条件排序

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }];#指定查询的记录从哪条至哪条

 

LIMIT 当页的起始序号（但是不包含这个序号），显示几条

注：

1. 数据库不同分页语句也不一样
2. Limit 0,5：这种写法是最常用的写法等同于limit 5 offset 0;

 

示例：

#查询《数据库结构-1》的所有的考试结果（学号   学生姓名   科目名称   成绩），按成绩降序排列、

#结果每页显示5条

SELECT s.studentno,studentname,subjectname,studentresult

FROM student AS s

INNER JOIN result AS r

ON r.StudentNo=s.StudentNo

INNER JOIN `subject` AS sub

ON r.SubjectNo = sub.subjectno

WHERE subjectname='数据库结构-1'

ORDER BY studentresult DESC

#Limit 0,5;        #第一个数字代表从哪条记录开始（pageno-1）*pagesize（起始行，从0开始）   第二个数字代表要显示几条pagesize

#limit 5,5;    #查询第二页

#limit 10,5;   #第三页

#limit 15,5;   #第四页

#limit（pageno-1）*pagesize，pagesize  【（当前页码-1）*页容量，页容量】

注意事项：

记住语法；分析要求；逐条书写；注意顺序

 

总示例：

#查询1. 《JAVA第一学年》课程成绩2. 前10名且3. 分数大于80的学生信息（学号、姓名、课程名、分数）

SELECT s.studentno,studentname,subjectname,studentresult

FROM student AS s

INNER JOIN result AS r

ON r.StudentNo=s.StudentNo

INNER JOIN `subject` AS sub

ON r.SubjectNo = sub.subjectno

WHERE subjectname='JAVA第一学年' AND studentresult>80

ORDER BY studentresult DESC

LIMIT 0,10;

 

子查询：

什么是子查询？

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句，子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询

 

注：

嵌套查询可由多个子查询组成，求解的方式是由里及外；

先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果；

子查询返回的结果一般都是集合，故而建议使用IN关键字；

 

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

SELECT … FROM 表1 WHERE 字段1 比较运算符（子查询）

示例：

 

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. 根据课程编号和最近一次的考试日期查询出在读学生信息

 

连接查询，子查询示例汇总：

示例一：

#查询《数据结构-1》的所有考试结果（学号、科目编号、分数），并按成绩降序排列

#方法一：使用连接查询

SELECT studentno,r.SubjectNo,studentresult

FROM result AS r

INNER JOIN `subject` sub

ON r.SubjectNo=sub.SubjectNo

WHERE subjectname='数据库结构-1'

ORDER BY studentresult DESC;

#方法二：使用子查询

SELECT studentno,r.SubjectNo,studentresult

FROM result AS r

WHERE subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='C语言-1')

ORDER BY studentresult DESC;

#这就是子查询的写法，外面的select叫做父查询，里面的select叫做子查询，他们两个是嵌套的关系

#查询顺序是由里向外的，会先执行里面的查询吧结果放在那里，再执行外面的查询

#并不是所有外查询和内查询之间的位置是等于号

 

示例二：

#查询课程为《高等数学-2》且分数不小于80份的学生的学号和姓名

#方法一：使用连接查询

SELECT r.studentno,studentname

FROM student AS s

INNER JOIN result AS r

ON s.StudentNo=r.StudentNo

INNER JOIN `subject`sub

ON r.SubjectNo=sub.SubjectNo

WHERE subjectname='高等数学-2' AND StudentResult>=80

#方法二：使用连接查询+子查询(将需求拆分、细化、逐步完善SQL语句)

#1. 分数不小于80份的学生的学号和姓名

SELECT studentno,studentname

FROM student AS s

INNER JOIN result AS r

ON s.StudentNo=r.SubjectNo

WHERE studentresult>=80

#2. 在上面SQL基础上，添加需求：查询课程为《高等数学-2》的成绩

AND subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2')

#方法三：使用子查询

SELECT studentno,studentname FROM student WHERE studentno IN(1,2,3,4,……)   #会查出多个学生对多个学生比较,当返回值有多个的时候就要用in

SELECT studentno FROM result WHERE studentresult>=80 AND subjectno=2  #  1   2   3   4   ……

SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'   #2

#分步写简单SQL语句，然后将其嵌套起来

SELECT studentno,studentname FROM student WHERE studentno IN(

SELECT studentno FROM result WHERE studentresult>=80 AND subjectno=(

SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'))

 

示例三：

#查询《C语言-1》的前五名学生的成绩信息：学号、姓名、分数

#方法一：使用连接查询

SELECT r.studentno,studentname,studentresult

FROM student AS s

INNER JOIN result AS r

ON s.StudentNo=r.StudentNo

INNER JOIN `subject` AS sub

ON sub.SubjectNo=r.SubjectNo

WHERE subjectname='C语言-1'

ORDER BY studentresult DESC

LIMIT 0,5;

#方法二：使用连接查询+子查询(将需求拆分、细化、逐步完善SQL语句)

#1. 查询出学号和姓名

SELECT r.studentno,studentname,studentresult

FROM student AS s

INNER JOIN result AS r

ON s.StudentNo=r.StudentNo

#2. 在上面SQL基础上，添加需求：查询课程为《C语言-1》的成绩

WHERE subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='C语言-1')

ORDER BY studentresult DESC

LIMIT 0,5;

 

示例四：

#使用子查询实现，查询郭靖同学所在的年级名称

SELECT gradename FROM grade WHERE gradeid=(

SELECT gradeid FROM student WHERE studentname='郭靖')

注：

1. 任何允许使用表达式的地方都可以使用子查询
2. 嵌套在父查询SELECT语句的子查询可包括

SELECT子句；FROM子句；WHERE子句；GROUP BY子句；HAVING子句

其中：WHERE、GROUP BY、HAVING是可选子句，根据业务需求决定

只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

 

MySQL函数：

1. 数学函数

函数名	作用	举例
ABS(x)	返回数值x的绝对值	SELECT ABS(-3) 返回：3
CEILING(x)	返回大于或等于数值x的最小整数	SELECT CEIL(2.3) 返回：3
FLOOR(x)	返回小于或等于数值x的最大整数	SELECT FLOOR(2.3) 返回：2
ROUND()	四舍五入	SELECT ROUND(5.3) 返回：5
RAND()	返回0~1间的随机数	SELECT RAND() 返回：0.5525468583708134
RAND(10)	以某个数作为种子，返回重复随机数	SELECT RAND(10) 返回：重复随机数
SIGN(0)	符号函数，正数1；负数-1 0返回0	SELECT SIGN(0) 返回：0

 

2. 字符串函数

函数名	作用	举例
CHAR_LENGTH(str)	计算字符数	SELECT CHAR_LENGTH('学习数据库');     #返回：5
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
LEFT(str,len)	从左面截3个字符	SELECT LEFT('课工场欢迎你',3);  返回：课工场
RIGHT(str,len)	从右面截3个字符	SELECT LEFT('课工场欢迎你',3);  返回：欢迎你
REPLACE(str1,str2,str3)	替换字符	SELECT REPLACE('课工场欢迎你','你','你们');   返回：课工场欢迎你们
REVERSE(str)	反转	SELECT REVERSE('课工场欢迎你'); 返回：你迎欢场工课
SUBSTRING(str,num,len)	字符串截取	SELECT SUBSTRING('JavaMySQLOracle',5,5); 返回：MySQL

 

3. 日期和时间函数

函数名	作用	举例（结果与当前时间有关）
CURRENT_DATE()	获取当前日期	SELECT CURRENT_DATE() 返回：2018-09-10
CURDATE()	获取当前日期	SELECT CURDATE(); 返回：2016-08-08
CURTIME()	获取当前时间	SELECT CURTIME(); 返回：19:19:26
NOW()	获取当前日期和时间	SELECT NOW(); 返回：2016-08-08 19:19:26
LOCALTIME()	获取当前日期和时间	SELECT LOCALTIME(); 返回：2016-08-08 19:19:26
SYSDATE()	获取当前日期和时间	SELECT SYSDATE (); 返回：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

 

4. 系统信息函数

函数名	作用	举例
VERSION()	获取系统版本	SELECT VERSION() 返回：5.5.40
USER()	获取用户	SELECT USER() 返回：root@localhost

 

6. 聚合函数

函数名	作用	举例
AVG()	通常为数值字段或表达列作统计，返回一列的平均值	SELECT AVG(studentresult)  AS 平均分 FROM result
COUNT()	返回满足SELECT条件的记录总和数，如SELECT COUNT(*). .	SELECT COUNT(email) FROM student SELECT COUNT(*) FROM student SELECT COUNT(1) FROM student
MAX()	可以为数值字段、字符字段或表达式列作统计，返回最大的值	SELECT MAX(studentresult)  AS 最高分 FROM result
MIN()	可以为数值字段、字符字段或表达式列作统计，返回最小的值	SELECT MIN(studentresult)  AS 最低分 FROM result
SUM()	返回数字字段或表达式列作统计，返回一列的总和	SELECT SUM(studentresult)  AS 总和 FROM result

示例：

#查询不同课程的平均分，最高分，最低分

SELECT subjectname,SUM(studentresult)  AS 总和,AVG(studentresult)  AS 平均分,MAX(studentresult)  AS 最高分,MIN(studentresult)  AS 最低分

FROM result AS r

INNER JOIN `subject` AS sub

ON r.subjectno=sub.subjectno

GROUP BY r.subjectno

HAVING 平均分>80

ORDER BY 平均分 DESC

LIMIT 0,4;

注：

写数据库时候一定要注意报错时错误码后面的提示；明白各种错误的比对，记住解决方案

 

总结：