MySQL入门（二）

1.字段的别名

• 通过 字段名 as 别名 的语法,可以给字段起一个别名,别名可以是中文
• as可以省略
• 字段名 as 别名 和 字段名 别名 结果是一样的

  -- 通过as 给字段起一个别名
  select card as 身份证, name as 姓名,sex as 性别 from students;
  -- 别名的as可以省略
  select card 身份证, name 姓名,sex 性别 from students;

2.表的别名

通过 表名 as 别名 给表起一个别名

as 可以省略

-- 通过as 给表students起一个别名
 select * from students as stu;
 -- 可以省略as 
select * from students stu;

3.distinct过滤重复记录

通过 select distinct 字段名 , 字段名 from 表名 来过滤 select 查询结果中的重复记录

SELECT DISTINCT sex, class from students;

4.where

• -- 例 2：查询 students 表中年龄 age 等于 30 的姓名 name,班级 class
• select name, class from students where age = 30;

5.select查询的基本规律

• select * 或者select 字段名 控制了查询返回什么样的字段(列)
• where 条件 控制了查询返回什么样的记录(行)

6.比较运算符

• < 小于
• <= 小于等于
• > 大于
• >= 大于等于
• !=和<>不等于

-- 例 1：查询 students 表中 name（姓名）等于’小乔’学生的 age（年龄） 
select age from students where name = '小乔'; 
-- 例 2：查询 students 表中 30 岁以下的学生记录 
SELECT * from students where age < 30; 
-- 例 2：查询 students 表中 30 岁和30岁以下的学生记录 
SELECT * from students where age <= 30; 
-- 查询家乡不在'北京'的学生记录 
select * from students where hometown != '北京'; 
select * from students where hometown <> '北京';

7.逻辑运算符

and 与

• 条件1 and 条件2
• 两个条件必须都满足

or 或

• 条件1 or 条件2
• 两个条件只要有一个满足即可

not 非

• not 条件
• 条件成立,not以后就不成立,条件不成立,not以后就成立

-- 练习 1：查询 hometown 老家是’河南’或’河北’的学生记录； 
SELECT * from students where hometown = '河南' or hometown = '河北'; 
-- 练习 2：查询 class 班级为'1 班'，并且 hometown 老家为'北京'的学生记录； 
SELECT * from students where class = '1班' and hometown = '北京'; 
-- 练习 3：查询 age 年龄非 30 岁的学生记录； 
SELECT * from students where not age = 30; SELECT * from students where age != 30;

8.模糊查询

• like实现模糊查询
• %代表任意多个字符
• _代表任意一个字符
• 字段名 like '字符%'

-- 例 1：查询 name 姓名中以’孙’开头的学生记录 
SELECT * from students where name like '孙%'; 
-- 例 2：查询 name 姓名以’孙’开头，且名只有一个字的学生记录 
SELECT * from students where name like '孙_';

-- 练习 1：查询 name 姓名为两个字的学生记录； 
SELECT * from students WHERE name like '__'; 
-- 练习 2：查询 name 姓’白’且年龄大于 30 的学生记录； 
SELECT * from students WHERE name like '白%' and age > 30; 
-- 练习 3：查询 studentNo 学号以 1 结尾的学生记录； 
select * from students WHERE studentNo like '%1';

9. 范围查找

• in (值, 值, 值)
• 非连续范围查找
• between 开始值 and 结束值

-- 例 1：查询 hometown 家乡是’北京’或’上海’或’广东’的学生记录 
SELECT * from students where hometown = '北京' or hometown = '上海' or hometown = '广东'; SELECT * from students where hometown in ('北京', '上海', '广东'); 
-- 例 2：查询 age 年龄为 25 至 30 的学生记录 
SELECT * from students where age >= 25 and age <= 30; 
SELECT * from students where age BETWEEN 25 and 30; 

-- 练习 1：查询 age 年龄在 20 或 25 或 30 的女生记录； 
SELECT * from students where age in (20, 25, 30) and sex = '女'; 
-- 练习 2：查询 age 年龄 25 到 30 以外的学生记录； 
SELECT * from students where not age BETWEEN 25 and 30; 

10. 空判断

• null不是0,也不是' ',null在SQL里面代表空,什么也没有
• null不能用比较运算符的判断
• is null ---是否为null
• is not null ---是否不为null
• 不能用 字段名 = null 字段名 != null这些都是错误的

例 1：查询 card 身份证为 null 的学生记录 
SELECT * from students where card is null; 
例 2：查询 card 身份证非 null 的学生记录 
SELECT * from students where card is not null;

11.where 子句可以用到 update 和 delete 语 句后面

例 1：修改 age 为 25,并且 name 为’孙尚香’的学生 class 为’2 班’ 
update students set class = '2班' where age = 25 and name = '孙尚香'; 

例 2：删除 class 为’1 班’,并且 age 大于 30 的学生记录 
DELETE from students where class = '1班' and age > 30; 12345 

修改students表中 姓孙的同学,班级为'1班' 
update students set class = '1班' where name like '孙%'; 

删除students年龄在20到30之间所有的男同学 
DELETE from students where (age BETWEEN 20 and 30) and sex = '男';

12.order by 排序

• order by 字段名 [asc/desc]
• asc代表从小到大,升序,asc可以省略
• desc代表从大到小,不可以省略

例 1：查询所有学生记录，按 age 年龄从小到大排序 
select * from students order by age asc; 
select * from students order by age; 

例 2：查询所有学生记录，按 age 年龄从大到小排序 
select * from students order by age desc; 

例 2：查询所有学生记录，按 age 年龄从大到小排序， 年龄相同时，再按 studentNo 学号从小到大排序 
SELECT * from students ORDER BY age desc, studentNo;

• 当一条select语句出现了where和order by
• select * from 表名 where 条件 order by 字段1,字段2;
• 一定要把where写在order by前面

练习：查询所有男学生记录，按 class 班级从小到大排序，班级相同时， 
再按 studentNo 学号再按学号从大到小排序 
SELECT * from students where sex = '男' order by class, studentNo desc;

13. 聚合函数

count 求 select 返回的记录总数

查询学生总数(查询stuents表有多少记录) 
select count(*) from students; 
select count(name) from students; 
select count(DISTINCT class) from students; 
select count(DISTINCT sex) from students; 
查询女同学数量 
SELECT count(name) from students where sex = '女'; 
SELECT count(*) from students where sex = '女'; 
SELECT count(sex) from students where sex = '女';

max查询最大值和min 查询最小值

查询students中的最大年龄 
SELECT max(age) from students; 
查询students中的女生最大年龄 
SELECT max(age) from students where sex = '女'; 
查询students中的'1班'最大年龄 
SELECT max(age) from students where class = '1班'; 
查询students中的最小年龄 
SELECT min(age) from students; 
查询students中的女生最小年龄 
SELECT min(age) from students where sex = '女'; 
查询students中的'1班'最小年龄 
SELECT min(age) from students where class = '1班'; 

SUM求和

查询students中的年龄总和 
SELECT sum(age) from students; 
查询students中的女生年龄总和 
SELECT sum(age) from students where sex = '女'; 
查询students中的'1班'年龄总和 
SELECT sum(age) from students where class = '1班';

avg 求平均数

查询students中的年龄总和 
SELECT sum(age) from students; 
查询students中的女生年龄总和 
SELECT sum(age) from students where sex = '女';

avg 的字段中如果有 null,null 不做为分母计算平均

create table aa (age int, name varchar(10)); 
insert into aa values (10, 'a'), (20, 'b'), (null, 'c'); 
select avg(age) from aa;-- 结果为15,而不是10

14. 数据分组

• group by 字段名
• select 聚合函数 from 表名 where 条件 group by 字段
• select 聚合函数 from 表名 group by 字段
• group by就是配合聚合函数使用的

分别查询男女同学的数量
SELECT count(*) from students where sex = '男';
SELECT count(*) from students where sex = '女';
select sex, count(*) from students group by sex;//以性别为分组，分别查询男女同学的数量

where与group by

-- 分别查询'1班'不同性别学生数量

练习：用数据分组方法，统计各个班级学生总数、平均年龄、最大年龄、最 小年龄。
SELECT class, count(*), avg(age), max(age), min(age) 
from students GROUP BY class

练习：统计各个班级学生总数、平均年龄、最大年龄、最小年龄。
但不统计'3班',统计结果按班级名称从大到小排序
SELECT class, count(*), avg(age), max(age), min(age) 
from students 
where class <> '3班' GROUP BY class ORDER BY class desc;

 where和group by 和order by的顺序

• select * from 表名 where 条件 group by 字段 order by 字段

15.分组聚合之后的数据筛选

• having子句

• 总是出现在group by之后

• select * from 表名 group by 字段 having 条件

用where查询男生总数
where先筛选复合条件的记录,然后在聚合统计
SELECT count(*) from students where sex = '男';

用having查询男生总数
having先分组聚合统计,在统计的结果中筛选
SELECT count(*) from students GROUP BY sex HAVING sex = '男';

16.having与where筛选的区别

• where是对标的原始数据进行筛选

• having是对group by之后已经分过组的数据进行筛选

• having可以使用聚合函数, where不能用聚合函数

17.limit显示指定的记录数

• ​​​​select * from 表名 where 条件 group by 字段 order by 字段 limit start, count
• limit总是出现在select语句的最后,
• start代表开始行号,行号从0开始编号
• count代表要显示多少行
• 省略start,默认从0开始,从第一行开始

  查询前三行记录
  SELECT * from students limit 0, 3;
  SELECT * from students limit 3;
  
  查询从第4条记录开始的三条记录
  SELECT * from students limit 3, 3;

18.数据分页显示

• m 每页显示多少条记录
• n,第n页
• (n - 1) * m, m
• 把计算结果写到limit后面

每页显示4条记录,第3页的结果
select * from students limit 8, 4;
每页显示4条记录,第2页的结果
select * from students limit 4, 4;

-- 每页显示5条记录,分别多条select显示每页的记录
-- 第一页
SELECT * from students limit 5;
-- 第二页:
SELECT * from students limit 5, 5;
-- 第三页:
SELECT * from students limit 10, 5;