MySQL-03 SQL语句设计

学习要点

1. SQL语句分类
2. DML语句
3. DML 查询语句

  

SQL语句分类

1. 数据操纵语言（DML）：用来操纵数据库中数据的命令。包括：SELECT、INSERT、UPDATE、DELETE。
2. 数据定义语言（DDL）：用来建立数据库、数据库对象和定义列的命令。包括：CREATE、DROP、ALTER。
3. 数据控制语言（DCL）：用来控制数据库组件的存取许可、权限等的命令。包括：GRANT、REVOKE。
4. 其他语言元素：如流程控制语言、内嵌函数、批处理语句等。

DML语句

INSERT语句

语法格式：

INSERT INTO 表名[(字段1，字段2，…，字段n)]  VALUES(值1，值2，…，值n);

　　

示例：

INSERT INTO grade(gradeid,gradename) VALUES(0,'大一');

INSERT INTO grade(gradename) VALUES('大一'); // gradeid为自动增长字段

　　

UPDATE语句

语法格式：

UPDATE 表名 SET 字段名=表达式[,…] 

    [WHERE条件]

    [ORDER BY 字段] 

    [LIMIT 行数]

　　

示例代码：

#UPDATE 更新表格中的记录

#更新课程表中最新录入三门课程的年级属性为2年级

SELECT * FROM `subject`;

UPDATE `subject` SET gradeid=2 ORDER BY subjectno DESC LIMIT 3;

#更新课程表中英语课程年级属性为1年级

UPDATE `subject` SET gradeid=1 WHERE subjectno=14;

#更新课程表中软件分层架构课程年级属性为3年级

UPDATE `subject` SET gradeid=3 WHERE subjectno=16;

　　

DELETE语句

语法格式：

DELETE  FROM  [表名] 

[WHERE 条件]

[ORDER BY 字段]

[LIMIT 行数]

　　

示例代码：

#删除课程表中的数理逻辑课程

SELECT * FROM `subject` WHERE subjectname='数理逻辑';

DELETE FROM `subject`

   WHERE subjectno=20;

　　

#删除最近录入的三门课程

DELETE FROM `subject`

   ORDER BY subjectno DESC

   LIMIT 3;

　　

清空表：TRUNCATE [表名]

示例代码：

#截断表之前，需要删除从表外键

SHOW CREATE TABLE scheduleinfo;#查询外键名称

ALTER TABLE scheduleinfo DROP FOREIGN KEY sche_team_hostid;#删除外键

ALTER TABLE scheduleinfo DROP FOREIGN KEY sche_team_awayid;#删除外键

#截断表

TRUNCATE teaminfo;

　　

#添加外键

ALTER TABLE scheduleinfo

  ADD CONSTRAINT sche_team_hostid

  FOREIGN KEY(hostteamid) REFERENCES teaminfo(id);

ALTER TABLE scheduleinfo

  ADD CONSTRAINT sche_team_awayid

  FOREIGN KEY(awayteamid) REFERENCES teaminfo(id);

　　

DML 查询语句

语法格式：

SELECT  查询体

FROM 数据来源（一张表或者多张表）

WHERE 条件

GROUP BY 分组

HAVING 分组的基础上筛选

ORDER BY 排序（按照一个或者多个字段）

LIMIT 限制结果个数

　　

查询特定字段

#查询学生的学号、姓名、email

SELECT studentno,studentname,email  FROM student;

　　

AS关键字为字段起别名

#使用AS为查询结果字段起别名

SELECT studentno AS '学 号',studentname AS 姓名,email AS 电子邮件  FROM student;

　　

DISTINCT关键字

#查看全校有多少学生有考试记录

SELECT * FROM result;

SELECT DISTINCT studentno FROM result;

如果DISTINCT后面跟随多个字段，则返回这些字段的唯一组合

　　

SELECT 语句中使用表达式

所有表达式支持算术运算：+  -  *  /  %

#课程编号为1的课程，每位同学加10分显示

SELECT * FROM result;

SELECT

       studentno AS 学号,

       subjectno AS 课程编号,

       studentresult AS 原来的成绩,

       studentresult + 10 AS 加分后的成绩

FROM

       result

WHERE subjectno=1;

　　

WHERE 子句

逻辑和比较运算符

运算符	说明
AND(&&)、OR(||)、XOR、NOT(!)	逻辑运算符，分别表示否、并且、或，用于多个逻辑连接。 优先级：NOT > AND > OR
=	等于
<=>	等于，等同于=。通常用于null值比较
!=或者<>	不等于
>	大于
<	小于
>=	大于或等于
<=	小于或等于
BETWEEN … AND …	介于某个范围之内，例：WHERE age BETWEEN 20 AND 30
NOT BETWEEN …AND …	不在某个范围之内
IN(项1,项2,…)	在指定项内，例：WHERE city IN('beijing','shanghai')
NOT IN(项1,项2,…)	不在指定项内
LIKE	搜索匹配，常与模式匹配符配合使用
NOT LIKE	LIKE的反义
IS NULL	空值判断符
IS NOT NULL	非空判断符
%	模式匹配符，表示任意字串，例：WHERE username LIKE '%use’

WHERE子句示例

#查询学生信息

SELECT *

  FROM student;

#1年级，女生信息

SELECT *

  FROM student

  WHERE gradeid=1 AND sex='女';

#查询家庭地址为空的学生信息

SELECT *

  FROM student

 WHERE address IS NULL; 

#查询成绩在60-70分之间的学生的学号

SELECT *

  FROM result

 WHERE studentresult>=60

       AND studentresult<=70;
 

SELECT *

  FROM result

 WHERE studentresult BETWEEN 60 AND 70;


#查询大一和大三的学生信息

SELECT *

  FROM student

 WHERE gradeid=1 OR gradeid=3;

 
SELECT *

  FROM student

 WHERE gradeid NOT IN(1,3);

 

#模糊查询:查家庭地址在北京的学生

SELECT *

  FROM student

 WHERE address LIKE '%北京%';

#模糊查询：查询姓张同学,名字只有一个字

SELECT *

  FROM student

 WHERE studentname LIKE '张_';

　　

上机练习1：设计SQL语句，实现以下功能

1. 从学生信息表中查询家庭地址为空的学生信息。
2. 从学生信息表中查询家庭地址不为空的学生的信息。

上机练习2：设计SQL语句，实现以下功能

从成绩表中查询学生成绩在50分打80分之间的学生信息，要求输出学号和学生成绩。

上机练习3：设计SQL语句，实现以下功能

从学生信息表中查询大一和大三的学生，要求输出学生姓名、年级编号。

提示：使用in比较运算符。

上机练习4：设计SQL语句，实现以下功能

从学生信息表中查询家庭地址在河南的学生。

提示：使用like和%模糊查询。

上机练习5：设计SQL语句，实现以下功能

从学生信息表中查询张姓单名的同学。例如张三，张四。

提示使用‘ _ ’  匹配查询。

数据库的关系代数运算

 数据库关系代数五种基本运算：并、差、笛卡尔积、投影、选择。

多表连接查询

1、非等值和等值的多表查询

问题1：要求设计SQL语句，输出学生姓名和所在年级的名称。

#非等值查询：没有where条件的查询，结果为笛卡尔乘积

SELECT

       studentname,

       gradename

FROM

       student,

       grade;

　　 

#等值查询：多表查询

SELECT

       studentname,

       gradename

FROM

       student,

       grade

WHERE

  student.gradeid=grade.gradeid;

或者

SELECT

       s.studentname 姓名,

       g.gradename 年级

FROM

       student s,

       grade g

WHERE

  s.gradeid=g.gradeid;

　　

上机练习6：设计SQL语句，实现以下功能

从成绩表中查询每位同学的成绩，要求输出姓名，成绩。如下图所示：

  

2、复合连接查询

问题：设计SQL语句，要求实现

要求从成绩表查询成绩，输出学生姓名、课程名称，成绩？

解决途径：

• WHERE等值连接查询
• 内连接查询：作用和WHERE等值连接查询相同

INNER  JOIN …. ON…

　　

示例代码：

SELECT

  s.studentname 姓名,

  j.subjectname 课程名称,

  r.studentresult 成绩

FROM

  result r 

INNER JOIN

  student s

ON

  r.studentno=s.studentno

INNER JOIN

  subject j

ON

  r.subjectno=j.subjectno;

　　

左外连接查询：返回包括左表中的所有记录和右表中连接字段相等的记录。

LEFT JOIN …. ON …

　　

查询没有参加考试的学生如何查?

SELECT

s.studentname 姓名,

r.studentresult 成绩

FROM

student s

LEFT JOIN result r ON r.studentno=s.studentno

WHERE r.studentresult IS NULL;

　　

右外连接查询：返回包括右表中的所有记录和左表中连接字段相等的记录。

RIGHT JOIN … ON ..

　　

如何查询没有考过试的课程？使用右外连接查询？

SELECT

j.subjectname 课程名称,

r.studentresult 成绩

FROM result r

RIGHT JOIN `subject` j ON j.subjectno=r.subjectno

WHERE r.studentresult IS NULL;

　　

上机练习7：设计SQL语句，实现以下功能

从成绩表中查询每位同学的成绩，要求输出姓名，年级，课程名称，成绩。如下图所示：

 

3、自连接查询

问题：需要输出赛程表，主队和客队要输出球队名称，要如何实现？

     　　

上机练习8：设计SQL语句，输出赛程表

子查询

 

问题：查询年龄比何强小的同学，如何查？

 

上机练习9：查询“PHP基础”课程至少一次考试刚好等于65分的学生学号和姓名

需求：两种实现方式表连接查询和子查询。

ORDER BY对查询结果进行排序

问题：查询学生信息，要求按照出生日期最近的排在前面，如何查询？

 

注意：子查询中不能使用order by。Order by只能对最终结果排序。

LIMIT限定结果行数

问题：查询学生信息，要求每次显示两条，按照学号增序排序。

使用LIMIT实现分页查询

#LIMIT (pageindex-1)*pagesize,pagesize;

SELECT * FROM student ORDER BY studentno LIMIT 0,2;

SELECT * FROM student ORDER BY studentno LIMIT 2,2;

SELECT * FROM student ORDER BY studentno LIMIT 4,2;

　　

参数说明：LIMIT 从记录的偏移量开始，取出的记录条数

统计函数（聚合函数）

函数名	作用
COUNT()	返回记录数。不统计null值。
SUM()	返回总数
AVG()	返回平均数
MAX()	返回最大数
MIN()	返回最小数

问题1：统计在校学生总数？

 

问题2：统计成绩表中学生的最高分，最低分，平均分，分数总和？

 

GROUP BY分组查询

问题1：如何从学生表中统计每个年段学生人数？

 

问题2：如何从学生表中统计每个年段男女学生人数？

 

查询优化

最重要原则：避免查询条件遍历整个数据表

1、WHERE子句中最先出现的条件，一定是过滤和排除更多结果的条件。以下哪个WHERE条件设计更合理？

 WHERE studentresult>60 AND studentno<20015

 WHERE studentno<20015 AND studentresult>60

2、WHERE子句中的条件尽量避免OR

WHERE studentresult>60 OR grade=2

3、针对ORDER BY 后面的字段，尽量和WHERE后的字段构成联合索引。

4、尽量少使用*作为查询体。

5、对于海量数据库设计。推荐海量数据库解决方案。

综合练习

上机练习10：查询大一开设的课程

上机练习11：查询参加最近一次“PHP基础”考试成绩最高分和最低分

上机练习12：查询参加“PHP基础”课程最近一次考试的在读学生名单

上机练习13：查询未参加“PHP基础”课程最近一次考试的在读学生名单

上机练习14：查询最近一次考试缺考的学生

上机练习15：输出学生姓名、课程所属的年级名称、课程名称、考试日期、考试成绩

如下图所示：