学习要点

  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：输出学生姓名、课程所属的年级名称、课程名称、考试日期、考试成绩

如下图所示：

最新文章

  1. 设计模式之美：Product Trader（操盘手）
  2. using-ef-code-first-with-an-existing-database
  3. rockmongo用法
  4. Centos6.7安装Apache2.4+Mysql5.6+Apache2.4
  5. Topcoder SRM 597
  6. EntityFramework 4使用存储过程分页
  7. eclipse4.2.1插件安装（二）之Eclipse HTML Editor
  8. Sql语句批量更新数据(多表关联)
  9. 关于.net类型转换判断问题
  10. Git基础 1 ---- 版本控制系统的介绍
  11. 文件上传&lt;springmvc&gt;
  12. Python中执行系统命令常见的几种方法--转载
  13. BZOJ1857 传送带 （三分法求单峰函数极值）
  14. zoj1383 zoj3418 二进制 基础
  15. React native 中使用Fetch请求数据
  16. 启用chacha20和salsa20等加密方法
  17. javascript功能插件大集合，写前端的亲们记得收藏
  18. webstorm命令行无法使用node-gyp进行编译
  19. go语言之进阶篇Ticker的使用
  20. opencv3.2将中文输出到图片上

热门文章

  1. hdoj1728【搜索的两种写法】
  2. IT兄弟连 JavaWeb教程 请求转发案例
  3. elasticsearch 查询 query
  4. 使用selesium和pytesseract识别验证码，达到登录网页目的
  5. Centos 6.x 搭建 Zabbix Agent 客户端
  6. 生产环境中配置的samba
  7. 页面html图片按钮多种写法
  8. spring在非容器管理的类里获取bean
  9. Java关键字-volatile
  10. 牛人cad二次开发网站（.net）