小练习
1.
在数据库
exercise
中创建课程表
stu_course
，包含字段课程编号
(number)
，类型为整数，长度为
11
，是主键，自增长，非空、课程名称
(name)
，类型为字符串，长度为
20
，非空、学分
(score)
，类型
为浮点数，小数点后面保留
2
位有效数字， 长度为
5
，非空
CREATE DATABASE IF NOT EXISTS exercise DEFAULT CHARACTER SET UTF8 COLLATEUFT8_GENERAL_CI;USE exercise;CREATE TABLE IF NOT EXISTS stu_course (`number` INT ( 11 ) AUTO_INCREMENT NOT NULL PRIMARY KEY ***MENT ' 课程编号 ' ,name VARCHAR ( 20 ) NOT NULL ***MENT ' 课程名称 ' ,score FLOAT ( 5 , 2 ) NOT NULL ***MENT ' 学分 ') ENGINE = InnoDB CHARSET = UFT8 ***MENT = ' 课程表 ' ;
2.
将课程表重命名为
course
ALTER TABLE stu_score RENAME AS course;
3.
在课程表中添加字段学时
(time)
，类型为整数，长度为
3
，非空
ALTER TABLE course ADD `time` INT ( 3 ) NOT NULL ***MENT ' 学时 ' ;
4.
修改课程表学分类型为浮点数，小数点后面保留
1
位有效数字，长度为
3
，非空
ALTER TABLE course MODIFY score FLOAT ( 3 , 1 ) NOT NULL ***MENT ' 学分 ' ;
5.
删除课程表
DROP TABLE IF EXISTS course;
6.
删除数据库
exercise
DROP DATABASE IF EXISTS exercise;
章节内容
DML--插入语句
重点
DML--修改语句
重点
DML--删除语句
重点
DQL -- 查询语句
重点
聚合函数
重点
分组查询
重点
查询排序
重点
分页查询
重点
章节目标
掌握DML--
插入语句
掌握DML--
修改语句
掌握DML--
删除语句
掌握DQL--
查询语句
掌握聚合函数的使用
掌握分组查询
掌握查询排序
掌握分页查询
第一节 DML语句
1. 什么是DML
DML
全称为
Data Manipulation Language
，表示数据操作语言。主要体现于对表数据的增删改操作。因此
DML
仅包括
INSERT
、
UPDATE
和
DELEETE
语句。
2. INSERT语句
-- 需要注意， VALUES 后的字段值必须与表名后的字段名一一对应INSERT INTO 表名 ( 字段名 1, 字段名 2, ..., 字段名 n ) VALUES ( 字段值 1, 字段值 2, ..., 字段值n ) ;-- 需要注意， VALUES 后的字段值必须与创建表时的字段顺序保持一一对应INSERT INTO 表名 VALUES ( 字段值 1, 字段值 2, ..., 字段值 n ) ;-- 一次性插入多条数据INSERT INTO 表名 ( 字段名 1, 字段名 2, ..., 字段名 n ) VALUES ( 字段值 1, 字段值 2, ..., 字段值n ) , ( 字段值 1, 字段值 2, ..., 字段值 n ) , ... , ( 字段值 1, 字段值 2, ..., 字段值 n ) ;INSERT INTO 表名 VALUES ( 字段值 1, 字段值 2, ..., 字段值 n ) , ( 字段值 1, 字段值 2, ..., 字段值n ) , ..., ( 字段值 1, 字段值 2, ..., 字段值 n ) ;
示例
向课程表中插入数据
INSERT INTO course ( `number` , name, score, `time` ) VALUES ( 1 , 'Java 基础 ' , 4 , 40 ) ;INSERT INTO course VALUES ( 2 , ' 数据库 ' , 3 , 20 ) ;INSERT INTO course ( `number` , score, name, `time` ) VALUES ( 3 , 5 , 'Jsp' , 40 ) ;INSERT INTO course ( `number` , name, score, `time` ) VALUES ( 4 , 'Spring' , 4 , 5 ) , ( 5 ,'Spring Mvc' , 2 , 5 ) ;INSERT INTO course VALUES ( 6 , 'SSM' , 2 , 3 ) , ( 7 , 'Spring Boot' , 2 , 2 ) ;
3. UPDATE语句
UPDATE 表名 SET 字段名 1 = 字段值 1 [ , 字段名 2 = 字段值 2, ..., 字段名 n = 字段值 n ] [ WHERE 修改条件 ]
3.1 WHERE条件子句
在
Java
中，条件的表示通常都是使用关系运算符来表示，在
SQL
语句中也是一样，使用
>, <, >=, <=, !=
来表示。不同的是，除此之外，
SQL
中还可以使用
SQL
专用的关键字来表示条件。这些将在后面的
DQL
语
句中详细讲解。
在
Java
中，条件之间的衔接通常都是使用逻辑运算符来表示，在
SQL
语句中也是一样，但通常使用
AND来表示逻辑与
(&&)
，使用
OR
来表示逻辑或
(||)
示例
WHERE time > 20 && time < 40 ; <=> WHERE time > 20 and time < 40 ;
3.2 UPDATE语句
示例
将数据库的学分更改为
4
，学时更改为
15
UPDATE course SET score = 4 , `time` = 15 WHERE name = ' 数据库 ' ;
4. DELETE语句
DELETE FROM 表名 [ WHERE 删除条件 ] ;
示例
删除课程表中课程编号为
1
的数据
DELETE FROM course WHERE `number` = 1 ;
5. TRUNCATE语句
-- 清空表中数据TRUNCATE [ TABLE ] 表名 ;
示例
清空课程表数据
TRUNCATE course;
6. DELETE与TRUNCATE区别
1.DELETE语句根据条件删除表中数据，而
TRUNCATE
语句则是将表中数据全部清空；如果
DELETE
语
句要删除表中所有数据，那么在效率上要低于
TRUNCATE
语句。
2.如果表中有自增长列，TRUNCATE
语句会重置自增长的计数器，但
DELETE
语句不会。
3.TRUNCATE语句执行后，数据无法恢复，而
DELETE
语句执行后，可以使用事务回滚进行恢复。
第二节 DQL语句
1. 什么是DQL
DQL
全称是
Data Query Language
，表示数据查询语言。体现在数据的查询操作上，因此，
DQL
仅包括
SELECT
语句。
2. SELECT语句
SELECT ALL /DISTINCT * | 字段名 1 AS 别名 1 [ , 字段名 1 AS 别名 1, ..., 字段名 n AS 别名 n ]FROM 表名 WHERE 查询条件
解释说明
ALL
表示查询所有满足条件的记录，可以省略；
DISTINCT
表示去掉查询结果中重复的记录
AS
可以给数据列、数据表取一个别名
示例：
从课程表中查询课程编号小于
5
的课程名称
SELECT name FROM course WHERE `number` < 5 ;
从课程表中查询课程名称为
"Java
基础
"
的学分和学时
SELECT score, `time` FROM course WHERE name = 'Java 基础 ' ;
3. 比较操作符
示例：
从课程表查询课程名为
NULL
的课程信息
SELECT * FROM course WHERE name IS NULL ;
示例：
从课程表查询课程名不为
NULL
的课程信息
SELECT * FROM course WHERE name IS NOT NULL ;
示例：
从课程表查询学分在
2~4
之间的课程信息
SELECT * FROM course WHERE score BETWEEN 2 AND 4 ;
示例：
从课程表查询课程名包含
"V"
的课程信息
SELECT * FROM course WHERE name LIKE '%v%' ;
示例：
从课程表查询课程名以
"J"
开头的课程信息
SELECT * FROM course WHERE name LIKE 'J%' ;
示例：
从课程表查询课程名以
"p"
结尾的课程信息
SELECT * FROM course WHERE name LIKE '%p' ;
示例：
从课程表查询课程编号为
1,3,5
的课程信息
SELECT * FROM course WHERE `number` IN ( 1 , 3 , 5 ) ;
4. 分组
数据表准备：新建学生表
student
，包含字段学号（
no
），类型为长整数，长度为
20
，是主键，自增长，非空；姓名（
name
），类型为字符串，长度为
20
，非空；性别（
sex
），类型为字符串，长度为
2
，默认值为
"
男
"
；年龄（
age
），类型为整数，长度为
3
，默认值为
0
；成绩（
score
），类型为浮点
数，长度为
5
，小数点后面保留
2
位有效数字
DROP TABLE IF EXISTS student;CREATE TABLE student (no BIGINT ( 20 ) AUTO_INCREMENT NOT NULL PRIMARY KEY ***MENT ' 学号，主键 ' ,name VARCHAR ( 20 ) NOT NULL ***MENT ' 姓名 ' ,sex VARCHAR ( 2 ) DEFAULT ' 男 ' ***MENT ' 性别 ' ,age INT ( 3 ) DEFAULT 0 ***MENT ' 年龄 ' ,score DOUBLE ( 5 , 2 ) ***MENT ' 成绩 ') ENGINE = InnoDB CHARSET = UTF8 ***MENT = ' 学生表 ' ;
插入测试数据：
INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 张三 ' , ' 男 ' , 20 ,59 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 李四 ' , ' 女 ' , 19 ,62 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 王五 ' , ' 其他 ' ,21 , 62 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 龙华 ' , ' 男 ' , 22 ,75 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 金凤 ' , ' 女 ' , 18 ,80 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 张华 ' , ' 其他 ' ,27 , 88 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 李刚 ' , ' 男 ' , 30 ,88 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 潘玉明 ' , ' 女 ' ,28 , 81 ) ;INSERT INTO student ( no , name, sex, age, score ) VALUES ( DEFAULT , ' 凤飞飞 ' , ' 其他 ' ,32 , 90 ) ;
4.1 分组查询
SELECT ALL /DISTINCT * | 字段名 1 AS 别名 1 [ , 字段名 1 AS 别名 1, ..., 字段名 n AS 别名 n ]FROM 表名 WHERE 查询条件 GROUP BY 字段名 1 ，字段名 2,..., 字段名 n
分组查询所得的结果只是该组中的第一条数据。
示例：
从学生表查询成绩在
80
分以上的学生信息并按性别分组
SELECT * FROM student WHERE score > 80 GROUP BY sex;
示例：
从学生表查询成绩在
60~80
之间的学生信息并按性别和年龄分组
SELECT * FROM student WHERE score BETWEEN 60 AND 80 GROUP BY sex, age;
4.2 聚合函数
COUNT() ：
统计满足条件的数据总条数
示例：
从学生表查询成绩在
80
分以上的学生人数
SELECT COUNT ( * ) total FROM student WHERE score > 80 ;
SUM()：
只能用于数值类型的字段或者表达式，计算该满足条件的字段值的总和
示例：
从学生表查询不及格的学生人数和总成绩
SELECT COUNT ( * ) totalCount, SUM ( score ) totalScore FROM student WHEREscore < 60 ;
AVG()：
只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值
示例：
从学生表查询男生、女生、其他类型的学生的平均成绩
SELECT sex, AVG ( score ) avgScore FROM student GROUP BY sex;
MAX()：
只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值
示例：
从学生表查询学生的最大年龄
SELECT MAX ( age ) FROM student;
MIN()：
只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值
示例：
从学生表查询学生的最低分
SELECT MIN ( score ) FROM student;
4.3 分组查询结果筛选
SELECT ALL /DISTINCT * | 字段名 1 AS 别名 1 [ , 字段名 1 AS 别名 1, ..., 字段名 n AS 别名 n ]FROM 表名 WHERE 查询条件 GROUP BY 字段名 1 ，字段名 2,..., 字段名 n HAVING 筛选条件
分组后如果还需要满足其他条件，则需要使用
HAVING
子句来完成。
示例：
从学生表查询年龄在
20~30
之间的学生信息并按性别分组，找出组内平均分在
74
分以上的组
SELECT * FROM student WHERE age BETWEEN 20 AND 30 GROUP BY sex HAVINGavg ( score ) > 74 ;
5. 排序
SELECT ALL /DISTINCT * | 字段名 1 AS 别名 1 [ , 字段名 1 AS 别名 1, ..., 字段名 n AS 别名 n ]FROM 表名 WHERE 查询条件 ORDER BY 字段名 1 ASC | DESC ，字段名 2 ASC | DESC ,..., 字段名 n ASC | DESC
ORDER BY
必须位于
WHERE
条件之后。
示例：
从学生表查询年龄在
18~30
岁之间的学生信息并按成绩从高到低排列，如果成绩相同，则按年龄从小到大排列
SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC , age ASC ;
6. 分页
SELECT ALL /DISTINCT * | 字段名 1 AS 别名 1 [ , 字段名 1 AS 别名 1, ..., 字段名 n AS 别名 n ]FROM 表名 WHERE 查询条件 LIMIT 偏移量 , 查询条数
LIMIT
的第一个参数表示偏移量，也就是跳过的行数。
LIMIT
的第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。
示例：
从学生表分页查询成绩及格的学生信息，每页显示
3
条，查询第
2
页学生信息
SELECT * FROM student WHERE score >= 60 LIMIT 3 , 3 ;
注意：
如果一个查询中包含分组、排序和分页，那么它们之间必须按照
分组
->
排序
->
分页
的先后顺序列。
