07-单表查询

字段表达式

• select 语句;

示例一:
select 6*6;
select 6*6 as mul; #起别名
select 6*6 mul2; #起别名

示例二:
- 以上语句虽然可以执行, 但是看上去不符合MySQL查询语句的规范
- 如果想要执行字段表达式, 又想要符合MySQL查询语句的规范, 那么可以使用伪表
- 什么是伪表? 占位符,但是实际上什么都不会做

select 6*6 mul from dual;

where子语句

在MySQL中where子语句支持简单的运算符
> < >= <= = != and or not

• in | not in
  • 一般用于查找包含关系的语句

示例一:
需求: 要求找出表中城市在北京和武汉的人
过去的做法:
弊端如果需要查找的城市太多, 语句会变得很冗余
select * from stu where city='北京' or city='武汉';
如果需要查找的城市太多, 可以利用in来简化语句
select * from stu where city in ('北京', '武汉');

示例二:
需求: 要求找出表中城市不在北京和武汉的人
select * from stu where city!='北京' and city!='武汉';
select * from stu where city not in ('北京', '武汉');

• between...and | not between...and
  • 一般用于查找区间范围的语句

示例一:
需求: 要求找出表中年龄在17~23岁之间的人
select * from stu where age>=17 and age<=23;
select * from stu where age between 17 and 23;

示例二:
需求: 要求找出表中年龄不在17~23岁之间的人
select * from stu where age<17 or age>23;
select * from stu where age not between 17 and 23;

• is null | is not null

注意点: 在MySQL中判断某一个字段保存的数据是否为null不能用等于符号

select * from stu where age=18;
insert into stu (name) values('it66');
select * from stu where age is null;
select * from stu where age is not null;

---

模糊查询

• _通配符: 表示任意一个字符
• %通配符: 表示任意0~n个字符

a_c: abc, adc
abc,adc,abbc,ac
_a_c: 1abc,3adc
1abc,abc1,2abbc,3adc

a%c:abc, adc,abbc, ac
abc,adc,abbc,ac
%a%c:1abc,2abbc, 3adc
1abc,abc1,2abbc,3adc


格式:
select 字段 from 表名 where 字段 like '条件';

select * from stu where name like 'z_c';
select * from stu where name like 'z%';

排序 order by

• 格式:
  select 字段 from 表名 order by 字段 [asc | desc]

示例一:
 select * from stu order by age; #默认是升序排序
 select * from stu order by age asc; #升序
 select * from stu order by age desc; #降序

 示例二:
 insert into stu values(null, 'itzb', 23, 100, '广州');
 需求: 按照年龄升序排序, 如果年龄相同那么按照成绩降序排序

 select * from stu order by age asc, score desc;

聚合函数

统计类

• count(); 统计数据条目数

select count(*) from stu;
select count(*) from stu where score >60;

• sum(); 求和

select sum(id) from stu;

• avg();平均值

 select avg(id) from stu;

• max(); 最大值

select max(id) from stu;

• min(); 最小值

select min(id) from stu;

数值类

• 随机数

 select rand(); #生成随机数
 select * from stu order by age;
 select * from stu order by rand(); #随机排序

• 四舍五入

select round(3.3); #四舍五入
select round(3.5);

• 向上向下取整

select ceil(3.1); #向上取整
select floor(3.7); #向下取整

• 截取小数位

select truncate(3.12345, 2); #截取小数位 3.12

字符串类

• 大小写转换

select ucase("hello world"); #转换为大写
select lcase("HELLO WORLD"); #转换为小写

• 字符截取

 select left('abcdefg', 2); #从左边开始截取2个字符
 select right('abcdefg', 2);#从右边开始截取2个字符
 select substring('abcdefg', 2, 3); #从第二个字符开始截取3个字符

---

数据分组 group by

• 在企业开发中, 一般情况下使用分组都是用来统计

select city, avg(score) from stu group by city;
 # 如果分组查询, 那么查询的字段必须包含分组字段和聚合函数
 # city就是分组字段/avg()就是聚合函数

 #如果查询的字段不是分组字段, 那么只会返回分组中的第一个值
 select name from stu group by city;
 #group_concat()函数可以将每一组中的所有数据连接在一起
 select group_concat(name) from stu group by city;

 在企业开发中, 一般情况下使用分组都是用来统计
 select city, count(*) from stu group by city;

条件 having

• 默认情况下都是去数据库的表中查询数据, 如果想在查询结果的基础上查询数据, 那么就可以使用having
• where条件会去表中查询是否符合条件, having条件会去查询结果集中查询是否符合条件

示例一:
 select * from stu where city='武汉';  #去数据库的表中匹配条件
 select * from stu having city='武汉'; #去查询的结果集中匹配添加

 示例二:
 #可以找到武汉的人, 因为是去数据库的表中匹配
 select name,age from stu where city='武汉';
 #不可以找到, 因为结果集中只有name和age,没有city,所以找不到
 select name,age from stu having city='武汉';

 # 前面部分代码查询返回的结果我们称之为结果集
 # 如下语句返回的结果集中包含了name和age
 select name,age from stu;

 示例三:
 需求: 查看表中哪些城市的平均分>=60分
 select city , avg(score) as avgscore from stu group by city;
 select city , avg(score) as avgscore from stu group by city where avgscore>=60; #报错, 因为数据库的表中没有avgscore字段
 select city , avg(score) as avgscore from stu group by city having avgscore>=60;

分页 limit

• select 字段 from 表 limit 索引, 个数;

 示例一:
 返回表中的前两条数据
 select * from stu limit 0, 2;
 select * from stu limit 2;
 返回表中的第3条数据和第4条数据
 select * from stu limit 2, 2;

查询选项

 all:      显示所有数据[默认]
 distinct: 去重结果集中重复的数据,重复的数据只显示一条

 select all name from stu;
 select distinct name from stu;

完整的查询语句

• select [查询选项] 字段名称 [from 表名] [where 条件] [order by 排序] [group by 分组] [having 条件] [limit 分页];