构造数据
1，学生表
create table `student` ( `id` int unsigned primary key auto_increment, `name` char(32) not null unique, `sex` enum('男', '女') not null, `city` char(32) not null, `description` text, `birthday` date not null default '1995-1-1', `money` float(7, 2) default 0, `only_child` boolean ) charset=utf8;
insert into `student` (`name`, `sex`, `city`, `description`, `birthday`, `money`, `only_child`) values ('郭德纲', '男', '北京', '班长', '1997/10/1', rand() * 100, True), ('陈乔恩', '女', '上海', NULL, '1995/3/2', rand() * 100, True), ('赵丽颖', '女', '北京', '班花, 不骄傲', '1995/4/4', rand() * 100, False), ('王宝强', '男', '重庆', '超爱吃火锅', '1998/10/5', rand() * 100, False), ('赵雅芝', '女', '重庆', '全宇宙三好学生', '1996/7/9', rand() * 100, True), ('张学友', '男', '上海', '奥林匹克总冠军！', '1993/5/2', rand() * 100, False), ('陈意涵', '女', '上海', NULL, '1994/8/30', rand() * 100, True), ('赵本山', '男', '南京', '副班长', '1995/6/1', rand() * 100, True), ('张柏芝', '女', '上海', NULL, '1997/2/28', rand() * 100, False), ('吴亦凡', '男', '南京', '大碗宽面要不要？', '1995/6/1', rand() * 100, True), ('鹿晗', '男', '北京', NULL, '1993/5/28', rand() * 100, True), ('关晓彤', '女', '北京', NULL, '1995/7/12', rand() * 100, True), ('周杰伦', '男', '台北', '小伙人才啊', '1998/3/28', rand() * 100, False), ('马云', '男', '南京', '一个字：贼有钱', '1990/4/1', rand() * 100, False), ('马化腾', '男', '上海', '马云死对头', '1990/11/28', rand() * 100, False);
2，成绩表
create table score ( `id` int unsigned primary key auto_increment, `math` float not null default 0, `english` float not null default 0 ) charset=utf8;
insert into score (`math`, `english`) values (49, 71), (62, 66.7), (44, 86), (77.5, 74), (41, 75), (82, 59.5), (64.5, 85), (62, 98), (44, 36), (67, 56), (81, 90), (78, 70), (83, 66), (40, 90), (90, 90);
常见的查询语句
1,select ：字段表达式
- selsct 既可以做查询，也可以做输出。
select rand(); #随机数
select unix_timestamp();#显示Unix时间戳
select id,name from student;
FROM 子句
- 语法 select 字段 from 表名；
- FROM 后面的是数据源，数据源可写多个，数据源一般是表明，也可以是其他查询的结果
- 示例
select student.name, score.math from stuent,score;
where 子句：按指定条件过滤
- 语法：select 字段 from 表名 where 条件；
- where是做条件查询的，只返回结果为True的数据
select name from student where city='上海';
- 空值判断 is null 或者is not null
select name from student where description is null;
select name from student where description is not null;
- 范围判断
between and
not between and
select id,math from score where math betwwen 60 and 70;
selsct id,math from score where math not between 60 and 70;
select * from score where math>=80 and english<=60; #直接做比较判断
having
- having 和where 功能类似，都可以用来实现条件查询，很多情况下可以使用where或者having 甚至可以混合使用
select name ,birthday from student where birthday >'1992-1-1';
select name,birthday from student having birthday>'1993-1-1';
select * from student where id >=3 and city ='北京';
select * from student where id >=3 having city ='北京';
- where和having 的区别
#只能使用where 不能使用having的情况。
select name birthday from student where id >2;
#下面这种情况会报错，having后面的语句中的id在前面select中并没有，只能查询前面所包含的结果。
select name birthdaay from student having id>2;
# 只能使用having而不能使用where的情况。
select name as n,birthday as b,id as i from student having i>2;
#以下情况会报错,别名不在原数据表中
select name as n,birthday as b,id asi from student where i>2;
select city, group_concat(birthday) from student group by city having min(birthday) > '1998-1-1';
#上面这条语句是查询每个城市中满足最小出生年年份大于1995的。
group by:分组查询
- 如果有where 需要放在它的后面
- 语法：select 字段 from 表名 group by 分组字段;
select sex,count(id) from student group by sex;
- 在group 将需要发结果通过"聚合函数"拼接
select sex,group_concat(name) from student group by sex;
order by :按字段排序
- order by 主要作用是排序
- order by 写在groupby后面，如果有having 也要写在它后面。
语法：select 字段from 表名 order by 排序字段 asc |desc;
分为升序asc降序desc,默认为asc
select * from student order by age;
select * from student order by age desc;
select city ,avg(money),group_concat(name),sum(money)from student group by city having sum(money)>70 order by sum(money);
limit :限制取出数量
- 语法
#从第一行到第m行
select 字段from 表名limit m;
#从第m行开始，往下取n行
select 字段 from 表名 limit m,n;
#跳过前n行，取后面的m行
select 字段from 表名limit m offset n
distinct:去重
select distinct city from student;
dual表
- dual只是一个虚拟表，仅仅只是为了保证select from 语句的完整性
select now() from dual;
