-DQL操作表记录-查询【重点】

7.1 基本查询语法

select 要查询的字段名 from 表名 [where 条件] 

7.2 简单查询

7.2.1 查询所有行和所有列的记录

• 语法

select * form 表

• 查询商品表里面的所有的列

select * from product;

7.2.2 查询某张表特定列的记录

• 语法

select 列名,列名,列名... from 表

• 查询商品名字和价格

select pname, price from product;

7.2.3 去重查询 distinct

• 语法

SELECT DISTINCT 字段名 FROM 表名;   //要数据一模一样才能去重

• 去重查询商品的名字

SELECT DISTINCT pname,price FROM product

注意点: 去重针对某列, distinct前面不能先出现列名

7.2.4 别名查询

• 语法

select 列名 as 别名 ,列名  from 表   //列别名  as可以不写
select 别名.* from 表 as 别名      //表别名(多表查询, 明天会具体讲)

• 查询商品信息，使用别名

SELECT pid ,pname AS '商品名',price AS '商品价格',num AS '商品库存' FROM product

7.2.5 运算查询(+,-,*,/,%等)

• 把商品名，和商品价格+10查询出来:我们既可以将某个字段加上一个固定值，又可以对多个字段进行运算查询

select pname ,price+10 as price from product;select name,chinese+math+english as total from student

注意

• 运算查询字段,字段之间是可以的
• 字符串等类型可以做运算查询，但结果没有意义

7.3 条件查询(很重要)

7.3.1语法

select ... from 表 where 条件 
//取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

7.3.2 运算符

1、比较运算符

大于：>
小于：<
大于等于：>=
小于等于：<=
等于：=   不能用于null判断
不等于：!=  或 <>
安全等于: <=>  可以用于null值判断

2、逻辑运算符（建议用单词，可读性来说）

逻辑与：&& 或 and
逻辑或：|| 或 or
逻辑非：! 或 not
逻辑异或：^ 或 xor

3、范围

区间范围：between x  and  ynot between x  and  y
集合范围：in (x,x,x) not  in (x,x,x)

4、模糊查询和正则匹配（只针对字符串类型，日期类型）

like 'xxx'  模糊查询是处理字符串的时候进行部分匹配
如果想要表示0~n个字符，用%
如果想要表示确定的1个字符，用_

regexp '正则'

5、特殊的null值处理

#（1）判断时
xx is null
xx is not null
xx <=> null

7.3.3 练习

• 查询商品价格>3000的商品

select * from product where price > 3000;

• 查询pid=1的商品

select * from product where pid = 1;

• 查询pid<>1的商品

select * from product where pid <> 1;

• 查询价格在3000到6000之间的商品

select * from product where price between 3000 and 6000;

• 查询pid在1，5，7，15范围内的商品

select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;select * from product where id in (1,5,7,15);

• 查询商品名以iPho开头的商品(iPhone系列)

select * from product where pname like 'iPho%';

• 查询商品价格大于3000并且数量大于20的商品 (条件 and 条件 and…)

select * from product where price > 3000 and num > 20;

• 查询id=1或者价格小于3000的商品

select * from product where pid = 1 or price < 3000;

7.4 排序查询

排序是写在查询的后面，代表把数据查询出来之后再排序

7.4.1 环境的准备

# 创建学生表(有sid,学生姓名,学生性别,学生年龄,分数列,其中sid为主键自动增长)
CREATE TABLE student(sid INT PRIMARY KEY auto_increment,sname VARCHAR(40),sex VARCHAR(10),age INT,score DOUBLE
);INSERT INTO student VALUES(null,'zs','男',18,98.5);
INSERT INTO student VALUES(null,'ls','女',18,96.5);
INSERT INTO student VALUES(null,'ww','男',15,50.5);
INSERT INTO student VALUES(null,'zl','女',20,98.5);
INSERT INTO student VALUES(null,'tq','男',18,60.5);
INSERT INTO student VALUES(null,'wb','男',38,98.5);
INSERT INTO student VALUES(null,'小丽','男',18,100);
INSERT INTO student VALUES(null,'小红','女',28,28);
INSERT INTO student VALUES(null,'小强','男',21,95);

7.4.2 单列排序

1. 语法: 只按某一个字段进行排序，单列排序

SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC];  //ASC: 升序，默认值; DESC: 降序

1. 练习: 以分数降序查询所有的学生

SELECT * FROM student ORDER BY score DESC

7.4.3 组合排序

1. 语法: 同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

1. 练习: 以分数降序查询所有的学生, 如果分数一致,再以age降序

SELECT * FROM student ORDER BY score DESC, age DESC

7.5 聚合函数

聚合函数通常会和分组查询一起使用，用于统计每组的数据

7.5.1 聚合函数列表

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

1. 语法

SELECT 聚合函数(列名) FROM 表名 [where 条件];

1. 练习

-- 求出学生表里面的最高分数
SELECT MAX(score) FROM student
-- 求出学生表里面的最低分数
SELECT MIN(score) FROM student
-- 求出学生表里面的分数的总和(忽略null值)
SELECT SUM(score) FROM student
-- 求出学生表里面的平均分
SELECT AVG(score) FROM student
-- 统计学生的总人数 (忽略null) 
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

注意: 聚合函数会忽略空值NULL

我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。

SELECT AVG(IFNULL(score,0)) FROM student;

7.6 分组查询

GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用

7.6.1分组

1. 语法

SELECT 字段1,字段2... FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];

1. 练习:根据性别分组, 统计每一组学生的总人数

-- 根据性别分组, 统计每一组学生的总人数
SELECT sex '性别',COUNT(sid) '总人数' FROM student GROUP BY sex-- 根据性别分组，统计每组学生的平均分
SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex-- 根据性别分组，统计每组学生的总分
SELECT sex '性别',SUM(score) '总分' FROM student GROUP BY sex

7.6.2 分组后筛选 having

• 练习根据性别分组, 统计每一组学生的总人数> 5的(分组后筛选)

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(*) > 5

• 练习根据性别分组，只统计年龄大于等于18的，并且要求组里的人数大于4

SELECT sex '性别',COUNT(sid) '总人数' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4

7.6.3 where和having的区别【面试】

子名	作用
where 子句	1) 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。2) where后面不可以使用聚合函数
having字句	1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2) having后面可以使用聚合函数

7.7 分页查询

7.7.1 语法

select ... from .... limit a ,b

LIMIT a,b;
a 表示的是跳过的数据条数
b 表示的是要查询的数据条数

7.7.2 练习

-- 分页查询
-- limit 关键字是使用在查询的后边，如果有排序的话则使用在排序的后边
-- limit的语法: limit offset,length  其中offset表示跳过多少条数据，length表示查询多少条数据
SELECT * FROM product LIMIT 0,3
-- 查询product表中的前三条数据(0表示跳过0条，3表示查询3条)SELECT * FROM product LIMIT 3,3
-- 查询product表的第四到六条数据(3表示跳过3条，3表示查询3条)
-- 分页的时候，只会告诉你我需要第几页的数据，并且每页有多少条数据
-- 假如，每页需要3条数据，我想要第一页数据: limit 0,3
-- 假如,每页需要3条数据，我想要第二页数据: limit 3,3
-- 假如，每页需要3条数据，我想要第三页数据: limit 6,3
结论: length = 每页的数据条数，offset = (当前页数 - 1)*每页数据条数limit (当前页数 - 1)*每页数据条数, 每页数据条数

7.8 查询的语法小结

select...from...where...group by...order by...limitselect...from...where...
select...from...where...order by...
select...from...where...limit...
select...from...where...order by...imit

导入和导出数据(了解)

8.1 单个数据库备份

mysql5.5

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码 --database  数据库名 > 文件路径/文件名.sql

例如：

C:\Windows\System32>mysqldump -hlocalhost -P3306 -uroot -p123456 --database  test > d:/test.sql

mysql5.7版

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码  数据名 > 文件路径/文件名.sql

不要再写–database

8.2 导入执行备份的sql脚本

先登录mysql，然后执行如下命令：

mysql> source  sql脚本路径名.sql

例如：

mysql>source d:/test.sql;