Java学习日志(二十九): SQL查询语句，SQL备份与恢复

JavaEE学习日志持续更新----> 必看！JavaEE学习路线（文章总汇）

SQL查询语句

创建以下表，方便查询练习

CREATE DATABASE day02;
USE day02;
#创建商品表：
CREATE TABLE product( 
  pid INT PRIMARY KEY, 
  pname VARCHAR(20), 
  price DOUBLE,
  category_id VARCHAR(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

简单查询语句

格式一：查询指定列
select 列名1，列名2...from 表名;

-- 查询商品名称和商品价格的所有信息
SELECT pname,price FROM product

格式二：查询所有列
select * from 表名;

SELECT * FROM product

格式三：过滤重复的数据
select distinct 字段1,字段2,... from 表名;
注意：

• distinct关键字之后只有一个字段，那么就会过滤掉这个字段中重复的数据
• distinct关键字之后如果有多个字段，多个字段必须同时满足重复，才会过滤

-- 查询商品价格，过略掉重复的价格
SELECT DISTINCT price FROM product;
-- 查询商品名称和价格，过滤掉名称和价格同时重复的数据
SELECT DISTINCT pname,price FROM product;

格式四：别名查询

1. 给表起别名：多表查询时使用
   select *|字段 from 表名 [as] 别名;
2. 给表中的字段起别名
   select 字段1 [as] 别名, ...字段n [as] 别名 from 表名;

注意：

1. 别名只是对查询的结果临时起个名字，不会改变表中原有的名字
2. 别名中如果有特殊符号或空格，必须使用引号包裹起来

-- 1.给表起别名
SELECT * FROM product AS p;
SELECT * FROM product p;
-- 2.给表中的字段起别名
SELECT pname AS 商品名,price AS 商品价格 FROM product
SELECT pname 商品名,price 商品价格 FROM product

格式五：查询语句中，可以直接进行数据计算

SELECT (1+1); -- 2
SELECT (5/2); -- 2.5000
-- 查询商品名和商品价格，把商品价格在查询的时候增加10000
SELECT pname,price+10000 FROM product;

条件查询语句

运算符：看图

条件查询语句的格式：
select 字段|* from 表名 [where 条件]

查询商品名为"花花公子"的商品所有信息

SELECT * FROM product WHERE pname='花花公子';

查询价格不是800的商品

SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE NOT(price = 800);

查询价格大于60的商品

SELECT * FROM product WHERE price > 60;

查询商品价格在200到1000之间的所有商品

SELECT * FROM product WHERE price>=200 && price<=1000;

使用between…and优化sql语句
注意：

1. 小的值要写在前边
2. 可以对时间区间进行查询 ‘2018-01-01’到’2020-01-01’

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

查询商品价格是200 或800的商品

SELECT * FROM product WHERE price=800 OR price=200;
-- 使用in优化代码
SELECT * FROM product WHERE price IN(200,800);

模糊查询：关键字like

• %：多个任意字符
• _：1个任意字符
  查询商品名称中以雷开头的商品信息

SELECT * FROM product WHERE pname LIKE '雷%';

查询商品名称中以霸结尾的商品信息

SELECT * FROM product WHERE pname LIKE '%霸';

查询商品名称中包含’霸’的商品信息

SELECT * FROM product WHERE pname LIKE '%霸%';

查询商品名称中第二个字为想的商品信息

SELECT * FROM product WHERE pname LIKE '_想%';

查询商品名称是5个字符的商品信息

SELECT * FROM product WHERE pname LIKE '_____';

查询没有分类的商品

SELECT * FROM product WHERE category_id IS NULL;

查询有分类的商品

SELECT * FROM product WHERE category_id IS NOT NULL;
SELECT * FROM product WHERE NOT(category_id IS NULL);

排序查询语句

排序查询语句：对查询的结果进行排序
使用关键字：order by [ASC]|[DESC]
格式：
select *|字段 from 表名 [where 条件] order by 被排序的字段 [ASC]|[DESC];

• ASC：升序，不写默认
• DESC：降序

注意：无论sql语句多么复杂，排序语句必须写在sql语句的末尾

使用价格排序（升序）

SELECT * FROM product ORDER BY price;
SELECT * FROM product ORDER BY price ASC;

使用价格排序（降序）

SELECT * FROM product ORDER BY price DESC;

显示商品价格（去重复），并排序（降序）

SELECT DISTINCT price FROM product ORDER BY price DESC;

在价格排序（降序）的基础上，以分类排序（降序）

SELECT DISTINCT * FROM product ORDER BY price DESC,category_id DESC;

聚合查询语句

聚合查询语句：对某列的数据进行查询，结果返回一个单一的值，会忽略null值
关键字：

• count：统计指定列不为NULL的记录行数；
• sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
• max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

格式： select 关键字(字段|*) from 表名

查询商品的总条数

SELECT COUNT(pid) FROM product; -- 13
SELECT COUNT(pname) FROM product; -- 13
SELECT COUNT(category_id) FROM product; -- 12
SELECT COUNT(*) FROM product; -- 13

查询商品价格大于200的总条数

SELECT COUNT(*) FROM product WHERE price>200; -- 7

查询商品的总金额

SELECT SUM(price) FROM product; -- 17503

查询分类为’c001’的商品的总价格

SELECT SUM(price) FROM product WHERE category_id = 'c001'; -- 13000

查询商品的平均价格

SELECT AVG(price) FROM product; -- 1346.3846153846155

查询分类为’c002’的商品的平均价格

SELECT AVG(price) FROM product WHERE category_id='c002';-- 860

查询商品的最大值和最小值

SELECT MAX(price),MIN(price) FROM product -- 5000  1

sum,avg如果指定列类型不是数值类型，那么计算结果为0

SELECT SUM(pname),AVG(pname) FROM product -- 0 0

分组查询

关键字：group by 被分组的字段
格式：
select 被分组的字段,(求和，平均，个数，最大，最小) from 表名 [where] group by 被分组的字段 [having 条件]
注意：被分组的字段，一般写在select语句的后边，作为查询条件，方便查看结果

按照商品类别分组，统计各个分组商品的个数

SELECT category_id,COUNT(*) FROM product GROUP BY category_id;

按照商品类别分组，统计各个分组商品价格的平均值

SELECT category_id,AVG(price) FROM product GROUP BY category_id;

where和having的区别：

• where：用于分组前进行条件过滤
• having：用于分组后，对分组的数据进行条件过滤

按照商品类别分组，统计各个分组商品的个数，且显示个数等于3的信息

SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*)=3;

按照商品类别分组，统计各个分组商品的个数，只查询每组商品价格大于200的商品

-- 先where过滤，再分组，再统计
SELECT category_id,COUNT(*) FROM product WHERE price>200 GROUP BY category_id;

分页查询

分页查询语句：
关键字：limit
格式：

• 只要前n条数据
  select *|字段 from 表名 limit n;
• 分页查询
  select *|字段 from 表名 limit m,n;
  m：每页开始行的索引（变化的）
  n：每页显示的数量，不变的

注意：数据库行的索引从0开始，列的索引从1开始
只要前6条数据

SELECT * FROM product LIMIT 6;

分页查询，每页显示5条数据，查询第一页

SELECT * FROM product LIMIT 0,5;

分页查询，每页显示5条数据，查询第二页

SELECT * FROM product LIMIT 5,5;

分页查询，每页显示5条数据，查询第三页

SELECT * FROM product LIMIT 10,5;

分页查询可以用于模拟翻页查询商品：

SQL备份与恢复

使用可视化工具进行备份与恢复
备份：

导入：

使用命令行进行备份与导入

备份：

mysqldump -uroot -proot day02>d:\day02.sql

导入：

mysqldump -uroot -proot day02<d:\day02.sql