愚公移山日记·30

愚公移山日记·30

学习进度

今天上午学习了MySQL的代码，因为知识点感觉太多，今天上午先整理一部分，下午继续整理。
查询所有的字段
select * from students;
查询指定字段
select id,name,age from students;
使用as给字段起别名
select id as ‘编号’,name as ‘姓名’ from students;
select id ‘编号’ ,name ‘姓名’ from students
as 的其他用法
select s.id from students as s;
消除重复
select distinct genter from students;
where 条件之比较运算
比较运算符
select … from 表名 where …
select * from students where age >18;
查询年龄为18岁的所有学生的名字
select name form students where age = 18
逻辑运算符
and
select * from students where age >18 and age< 28;
select * from students where age >18 and gender = ‘女’;
or
select * from students where age >18 or height > 180;
not
select * from students not where age >18 and gender=‘女’;
模糊查询
like
查询以小开头的名字
select * from students where name like ‘小%’;
查询姓名中含有小的所有的名字
select * from stidents where name like ‘%小%’;
查询有两个字的名字
select * from students where name like ‘’;
select * from students where name like '’;（查询三个字的名字）
查询至少两字的名字
select * from students where name like '_%’;
范围查询
between and 和in 的区别
查询范围为连续范围查询和非连续范围查询
查询年龄为18，34
in相当于多个值的或关系
select name from students where age = 18 or age =34;
select name from students where age in (18,34);
查询年龄为13到18
select * from students where age >=18 and age<= 34;
select * from students where age between 18 and 34;
select * from students where age not between 18 and 34;
空值判断
is null 为空白
is not null 不为空白
查询身高为空
select * from student where height is null;
select * from student where height is not null;
where 条件判断的总结：
比较运算 > < <= >= != <>
逻辑运算 and or not
模糊查询 like %任意o个或多个字符串 _任意一个字符
范围 连续 between and 不连续 in()
空置判断 is null is not null

order排序
select * from 表名 order by 列1 asc|desc [,列2]
asc 从小到大 desc 从大到小 默认是从大到小
select * from students where age between 18 adn 34 and gender = ‘男’ order by age;
select * from students where age between 19 and 34 and gender = ‘女’ order by hight desc;
两个排序的的要求
sleect * from students where age between 18 and 34 and gender = ‘女’ order by height desc,age desc;
聚合函数
聚合函数的作用是对当前所在的表当众做一个组进行统计
count() 计算总行数
max(列) 求此列的最大值
mian(列) 求此列的最小值
sum(列) 求此列的和
avg(列) 求此列的平均值
计算班级学生总数
select cont() from student;
select cont() from ‘总人数’ student; (起别名)
查询男生有的多少，女生有多少
select count() from students gender = ‘男’;
select count() from students gender = ‘女’;
查询最大值
select max(age) from student;
select max(age) from student where gender = ‘女’;
求和
select sum(age) from students
求平均值
select avg(age) from students;
select sun(age)/count() from students;
四舍五入（非聚合函数)
select round(avg(age),2) from students
select round(avg(height),2) form students where gender = ‘男’;