表一 student
表结构
mariadb root@localhost:bit_student> desc student
-> ;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| name | varchar(20) | NO | | | |
| chinese | float(3,1) | NO | | 0.0 | |
| english | float(3,1) | NO | | 0.0 | |
| math | float(3,1) | NO | | 0.0 | |
+---------+-------------+------+-----+---------+----------------+
-> ;
+----+----------+---------+---------+------+
| id | name | chinese | english | math |
+----+----------+---------+---------+------+
| 1 | 李涛 | 89.0 | 78.0 | 90.0 |
| 2 | 唐僧 | 67.0 | 98.0 | 56.0 |
| 3 | 孙悟空 | 87.0 | 78.0 | 77.0 |
| 4 | 老妖婆 | 88.0 | 98.0 | 90.0 |
| 5 | 红孩儿 | 82.0 | 84.0 | 67.0 |
| 6 | 如来佛祖 | 55.0 | 85.0 | 45.0 |
| 7 | 菩萨 | 75.0 | 65.0 | 30.0 |
+----+----------+---------+---------+------+
表查询
> 1.查询 总分大于200分 并且 数学成绩小于语文成绩 的 姓唐 的学生
mariadb root@localhost:bit_student> select id,name,(chinese+english+math) as total
-> from student
-> where (chinese+english+math) >200 and math < chinese
-> and name like '唐%';
+----+------+-------+
| id | name | total |
+----+------+-------+
| 2 | 唐僧 | 221.0 |
+----+------+-------+
1 row in set
Time: 0.013s
注意 as别名只对查询结果有效,所以where中还得写(chinese+english+math)而不是total,
如果需要使用通配符,使用like而不是=,而且如果不使用通配符时,虽然like和=等效,但是=更高效(可以忽略不计)
>2.查询英语分数在80 - 90 之间的同学
mariadb root@localhost:bit_student> select id,name,english
-> from student
-> where english between 80 and 90;
+----+----------+---------+
| id | name | english |
+----+----------+---------+
| 5 | 红孩儿 | 84.0 |
| 6 | 如来佛祖 | 85.0 |
+----+----------+---------+
注意事项 在mysql 中between 是闭区间,not between是开区间,还有在日期中
想查询某一天的日期时
between '2017-07-25 00:00:00' and '2017-07-25 24:00:00';
后面那个日期实际相当于 ‘2017-07-25 00:00:00’ and前后是一个时间,所以应该改为
between '2017-07-25 00:00:00' and '2017-07-26 00:00:00';
> 3.查询数学成绩为89,90,91的同学
mariadb root@localhost:bit_student> select id,name,math
-> from student
-> where math in (89,90,91);
+----+--------+------+
| id | name | math |
+----+--------+------+
| 1 | 李涛 | 90.0 |
| 4 | 老妖婆 | 90.0 |
+----+--------+------+
2 rows in set
Time: 0.009s
in 多用于多表查询的内连接
>4.要求显示student表中的信息,显示格式:“XXX的语文是XXX分,数学XXX分,英语XXX分
mariadb root@localhost:bit_student> select concat(name,'的语文',chinese,'分,数学',math,'分
-> ,英语',english,'分') as '通知单'
-> from student;
+--------------------------------------------+
| 通知单 |
+--------------------------------------------+
| 李涛的语文89.0分,数学90.0分,英语78.0分 |
| 唐僧的语文67.0分,数学56.0分,英语98.0分 |
| 孙悟空的语文87.0分,数学77.0分,英语78.0分 |
| 老妖婆的语文88.0分,数学90.0分,英语98.0分 |
| 红孩儿的语文82.0分,数学67.0分,英语84.0分 |
| 如来佛祖的语文55.0分,数学45.0分,英语85.0分 |
| 菩萨的语文75.0分,数学30.0分,英语65.0分 |
+--------------------------------------------+
7 rows in set
Time: 0.009s
这个不太常用 知道就好
5>.对数学成绩进行排序
mariadb root@localhost:bit_student> select * from student order by math desc;
+----+----------+---------+---------+------+
| id | name | chinese | english | math |
+----+----------+---------+---------+------+
| 1 | 李涛 | 89.0 | 78.0 | 90.0 |
| 4 | 老妖婆 | 88.0 | 98.0 | 90.0 |
| 3 | 孙悟空 | 87.0 | 78.0 | 77.0 |
| 5 | 红孩儿 | 82.0 | 84.0 | 67.0 |
| 2 | 唐僧 | 67.0 | 98.0 | 56.0 |
| 6 | 如来佛祖 | 55.0 | 85.0 | 45.0 |
| 7 | 菩萨 | 75.0 | 65.0 | 30.0 |
+----+----------+---------+---------+------+
降序 desc
6> 统计一个班有多少学生
mariadb root@localhost:bit_student> select count(*)as '人数' from student;
+------+
| 人数 |
+------+
| 7 |
+------+
1 row in set
Time: 0.006s
count(*) 是统计这张表的列数
也可以使用coutn(列名)
但注意会排除此列为NULL的情况
>7.计一个班各科加起来的成绩
mariadb root@localhost:bit_student> select sum(chinese) as '语文',sum(math) as '数学',sum(
-> english) as '英语' from student;
+-------+-------+-------+
| 语文 | 数学 | 英语 |
+-------+-------+-------+
| 543.0 | 455.0 | 586.0 |
+-------+-------+-------+
1 row in set
Time: 0.005s
>7.计一个班各科平均成绩
mariadb root@localhost:bit_student> select avg(chinese) as '语文',avg(math) as '数学',avg
-> (english) as '英语' from student;
+----------+------+----------+
| 语文 | 数学 | 英语 |
+----------+------+----------+
| 77.57143 | 65.0 | 83.71429 |
+----------+------+----------+
1 row in set
Time: 0.008s
>8 统计数学成绩高于平均成绩的同学的姓名和数学成绩
mariadb root@localhost:bit_student> select name,math from student
-> having math > avg(math);
+------+------+
| name | math |
+------+------+
| 李涛 | 90.0 |
+------+------+
1 row in set
Time: 0.007s
having是在结果集里进行筛选,而
where子句是筛选出结果集,
在sql中语句的执行顺讯
from-->where-->group by -->having --- >order by --> select;
from :确定表
where:在表中筛选出特定的结果集
group by 对结果集进行按列排序
having 在结果集中再次筛选
select 在结果集返回需求数据
聚集函数 avg sum 等只能在结果集中进行,个人猜想是如果对全表进行这些函数会严重影响性能.(找个理由就好记忆了..)
统计数学成绩高于平均成绩的同学的姓名和数学成绩 还有一种写法 就是in的子查询
sql
mariadb root@localhost:bit_student> select name from student where
-> student.math >
-> (select avg(math) from student);
+--------+
| name |
+--------+
| 李涛 |
| 孙悟空 |
| 老妖婆 |
| 红孩儿 |
+--------+