MySQL数据库基本操作2

主要内容

1. DQL

一.DQL

DQL（Data Query Language）是一种用于查询数据库中数据的语言。它是SQL（Structured Query Language）的一部分，用于从数据库中检索所需的数据。

DQL的用法如下：

1. SELECT语句：用于从数据库中选择数据。可以选择指定的列、特定的行或者使用聚合函数进行计算。例如：

   SELECT column1, column2 FROM table_name;
   SELECT * FROM table_name;
   1
   2

2. WHERE子句：用于指定查询条件，过滤满足条件的数据。可以使用比较运算符（如等于、大于、小于等）、逻辑运算符（如AND、OR）和通配符（如LIKE）来构建条件。例如：

   SELECT column1, column2 FROM table_name WHERE condition;
   SELECT * FROM table_name WHERE column1 = 'value';
   1
   2

3. ORDER BY子句：用于对查询结果进行排序。可以按照指定的列升序或降序排列。例如：

   SELECT column1, column2 FROM table_name ORDER BY column1 ASC;
   SELECT * FROM table_name ORDER BY column1 DESC;
   1
   2

4. GROUP BY子句：用于将查询结果按照指定的列进行分组。通常与聚合函数（如SUM、COUNT、AVG等）一起使用。例如：

   SELECT column1, SUM(column2) FROM table_name GROUP BY column1;
   1

5. HAVING子句：用于在GROUP BY子句后对分组进行过滤。可以使用聚合函数和比较运算符来构建条件。例如：

   SELECT column1, SUM(column2) FROM table_name GROUP BY column1 HAVING SUM(column2) > 100;
   1

6. LIMIT子句：用于限制查询结果的数量。可以指定返回的行数或者跳过指定数量的行。例如：

   SELECT column1, column2 FROM table_name LIMIT 10;
   SELECT column1, column2 FROM table_name LIMIT 10 OFFSET 5;
   1
   2

以上是DQL的一些常用用法，可以根据具体需求灵活运用。

1.语法格式

代码如下（示例）:

select
[all|distinct]
	< 目标列的表达式 1> [ 别名 ],
	< 目标列的表达式 2> [ 别名 ]...
from < 表名或视图名 > [ 别名 ],< 表名或视图名 > [ 别名 ]...
[where< 条件表达式 >]
[group by < 列名 >
[having < 条件表达式 >]]
[order by < 列名 > [asc|desc]]
[limit < 数字或者列表 >];

简化版语法
select *| 列名 from 表 where 条件
1
2
3
4
5
6
7
8
9
10
11
12
13

2.数据准备

代码如下（示例）:

创建数据库和表
--创建数据库
create database if not exist mydb2;
use mydb2;
-- 创建商品表：
create table product(
pid int primary key auto_increment, -- 商品编号
pname varchar(20) not null , -- 商品名字
price double, -- 商品价格
category_id varchar(20) -- 商品所属分类
);

• 添加数据：
insert into product values(null,' 海尔洗衣机 ',5000,'c001');
insert into product values(null,' 美的冰箱 ',3000,'c001');
insert into product values(null,' 格力空调 ',5000,'c001');
insert into product values(null,' 九阳电饭煲’ ,200,'c001');

insert into product values(null,' 劲霸休闲裤 ',266,'c002');
insert into product values(null,' 海澜之家卫衣 ',180,'c002');
insert into product values(null,' 杰克琼斯运动裤 ',430,'c002');
insert into product values(null,' 兰蔻面霜 ',300,'c003');
insert into product values(null,' 雅诗兰黛精华水 ',200,'c003');
insert into product values(null,' 香奈儿香水 ',350,'c003');
insert into product values(null,'SK-II 神仙水 ',350,'c003');
insert into product values(null,' 资生堂粉底液 ',180,'c003');
insert into product values(null,' 老北京方便面 ',56,'c004');
insert into product values(null,' 良品铺子海带丝 ',17,'c004');
insert into product values(null,' 三只松鼠坚果 ',88,null);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

3.简单查询

代码如下（示例）:

-- 1. 查询所有商品
select * from product;
-- 2. 查询商品名和商品价格 .
select pname,price from product;
-- 3. 别名查询 . 使用的关键字是 as （ as 可以省略的） .
-- 3.1 表别名 :
select * from product as p;
-- 3.2 列别名：
select pname as pn from product;
-- 4. 去掉重复值 .
select distinct price from product;
-- 5. 查询结果是表达式（运算查询）：将所有商品的价格 +10 元进行显示 .
select pname,price+10 from product;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.运算符

算术运算符

比较运算符

逻辑运算符

位运算符

5.运算符操作-算术运算符

代码如下（示例）:

select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;
-- 将每件商品的价格加 10
select name,price + 10 as new_price from product;
-- 将所有商品的价格上调 10%
select pname,price * 1.1 as new_price from product;

1
2
3
4
5
6
7
8
9
10

6.运算符操作-条件查询

代码如下（示例）:

--查询商品名称为“海尔洗衣机”的商品所有信息：
select * from product where pname = ' 海尔洗衣机 ';
-- 查询价格为 800 商品
select * from product where price = 800;
-- 查询价格不是 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 and price <=1000;
select * from product where price between 200 and 1000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

7.运算符操作-算数运算符

代码如下（示例）:

--查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);
-- 查询含有‘裤 ' 字的所有商品
select * from product where pname like ‘% 裤 %';
-- 查询以 ' 海 ' 开头的所有商品
select * from product where pname like ' 海 %';
-- 查询第二个字为 ' 蔻 ' 的所有商品
select * from product where pname like '_ 蔻 %';
-- 查询 category_id 为 null 的商品
select * from product where category_id is null;
-- 查询 category_id 不为 null 分类的商品
select * from product where category_id is not null;
--使用least求最小值
select least(10, 20, 30); -- 10
select least(10, null , 30); -- null
-- 使用 greatest 求最大值
select greatest(10, 20, 30);
select greatest(10, null, 30); -- null

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

8.运算符操作-位运算符

代码如下（示例）:

select 3&5; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移
select 3<<1; -- 位右移
select ~3; -- 位取反
1
2
3
4
5
6

9.排序查询

代码如下（示例）:

• 介绍
如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 order by子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
select
字段名 1 ，字段名 2 ，......
from 表名
order by 字段名 1 [asc|desc] ，字段名 2[asc|desc]......

• 特点
1.asc 代表升序， desc 代表降序，如果不写默认升序
2.order by 用于子句中可以支持单个字段，多个字段，表达式，函数，别名
3.order by 子句，放在查询语句的最后面。 LIMIT 子句除外

1
2
3
4
5
6
7
8
9
10
11
12

--1.使用价格排序（降序）
select * from product order by price desc;
-- 2. 在价格排序 ( 降序 ) 的基础上，以分类排序 ( 降序 )
select * from product order by price desc,category_id asc;
-- 3. 显示商品的价格 ( 去重复 ) ，并排序 ( 降序 )
select distinct price from product order by price desc;
1
2
3
4
5
6

10.聚合查询

操作

代码如下（示例）:

-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于 200 商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为 'c001' 的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为 'c002' 所有商品的平均价格
select avg(price) from product where category_id = 'c002';
1
2
3
4
5
6
7
8
9
10
11
12

11.聚合查询-null值处理

1. count 函数对 null 值的处理如果 count 函数的参数为星号（ *），则统计所有记录的个数。而如果参数为某字段，不统计含 null 值的记录个数。
2. sum 和 avg 函数对 null值的处理这两个函数忽略 null 值的存在，就好象该条记录不存在一样。
3. max 和 min 函数对 null 值的处理max 和 min 两个函数同样忽略 null 值的存在。

代码如下（示例）:

-- 创建表
create table test_null(
	c1 varchar(20),
	c2 int
);
-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);
-- 测试
select count(*), count(1), count(c2) from test_null;
select sum(c2),max(c2),min(c2),avg(c2) from test_null;
1
2
3
4
5
6
7
8
9
10
11
12
13

12.分组查询-group by

代码如下（示例）:

分组查询是指使用 group by 字句对查询信息进行分组。

格式：
-- 1 统计各个分类商品的个数
select category_id ,count(*) from product group by category_id ;
操作
select 字段 1, 字段 2... from 表名 group by 分组字段 having 分组条件 ;

如果要进行分组的话，则 SELECT 子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：
1
2
3
4
5
6
7
8
9

• 分组之后的条件筛选 -having
	分组之后对统计结果进行筛选的话必须使用 having ，不能使用 where
	where 子句用来筛选 FROM 子句中指定的操作所产生的行
	group by 子句用来分组 WHERE 子句的输出。
	having 子句用来从分组的结果中筛选行
• 格式
select 字段 1, 字段 2... from 表名 group by 分组字段 having 分组条件 ;

• 操作
-- 2. 统计各个分类商品的个数 , 且只显示个数大于 4 的信息
select category_id ,count(*) from product group by category_id having count(*) > 1;

1
2
3
4
5
6
7
8
9
10
11
12

13.分页查询-limit

代码如下（示例）:

分页查询：
分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。
例如数据共有 30 条，每页显示 5 条，第一页显示 1-5 条，第二页显示 6-10 条。
• 格式
-- 方式 1- 显示前 n 条
select 字段 1 ，字段 2... from 表明 limit n
-- 方式 2- 分页显示
select 字段 1 ，字段 2... from 表明 limit m,n
m: 整数，表示从第几条索引开始，计算方式 （当前页 -1 ） * 每页显示条数
n: 整数，表示查询多少条数据

• 操作
-- 查询 product 表的前 5 条记录
select * from product limit 5
-- 从第 4 条开始显示，显示 5 条
select * from product limit 3,5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

INSERT INTO SELECT 语句
 
格式
insert into Table2(field1,field2,...) select value1,value2,... from Table1
或者：
insert into Table2 select * from Table1
1
2
3
4
5
6

---

总结

基本操作1
以上是今天要讲的内容，学到了MySQL数据库基本语句及基本操作，包括DQL。