数据库增删改查和聚合函数
增加一条记录
MariaDB [db3]> insert teacher(name,age,gender) values ('wang',30,'M'),('mage',29,'M'),('jiege',23,'M');
Query OK, 3 rows affected (0.00 sec)
MariaDB [db3]> select * from teacher;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | wang | 30 | M |
| 2 | mage | 29 | M |
| 3 | jiege | 23 | M |
+----+-------+------+--------+
3 rows in set (0.00 sec)
删除一条记录
MariaDB [db3]> delete from teacher where id=3;
MariaDB [db3]> select * from teacher;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 1 | wang | 30 | M |
| 2 | mage | 29 | M |
+----+------+------+--------+
2 rows in set (0.00 sec)
修改一条记录
MariaDB [db3]> update teacher set name='qiange' where id=6;
Query OK, 1 row affected (0.00 sec)
查记录
- 字段显示可以使用别名
- WHERE子句：指明过滤条件以实现"选择"的功能
- GROUP BY 根据指定的条件把查询结果进行"分组"以用于做"聚合"运算 （ 一旦分组 group by ，select语句后只跟分组的字段，聚合函数 ）
- 常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
- HAVING: 对分组聚合运算后的结果指定过滤条件
- ORDER BY: 根据指定的字段对查询结果进行排序 （ 升序：ASC 降序：DESC ）
- LIMIT 对查询的结果进行输出行数数量限制
创建字段别名
MariaDB [hellodb]> select tid 老师ID,name 姓名 from teachers;
+----------+---------------+
| 老师ID | 姓名 |
+----------+---------------+
| 1 | Song Jiang |
| 2 | Zhang Sanfeng |
| 3 | Miejue Shitai |
| 4 | Lin Chaoying |
+----------+---------------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where classid is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
分页查询
MariaDB [hellodb]> select * from teachers limit 3;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from teachers limit 1,3;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
求数量
MariaDB [hellodb]> select count(*) from students where gender='M';
+----------+
| count(*) |
+----------+
| 15 |
+----------+
1 row in set (0.00 sec)
求和函数
MariaDB [hellodb]> select sum(age) from students where gender='M';
+----------+
| sum(age) |
+----------+
| 495 |
+----------+
1 row in set (0.00 sec)
分组统计
MariaDB [hellodb]> select stuid,name,classid from students group by classid;
+-------+--------------+---------+
| stuid | name | classid |
+-------+--------------+---------+
| 24 | Xu Xian | NULL |
| 2 | Shi Potian | 1 |
| 1 | Shi Zhongyu | 2 |
| 5 | Yu Yutong | 3 |
| 4 | Ding Dian | 4 |
| 6 | Shi Qing | 5 |
| 9 | Ren Yingying | 6 |
| 8 | Lin Daiyu | 7 |
+-------+--------------+---------+
8 rows in set (0.00 sec)
分组统计求和
MariaDB [hellodb]> select classid, count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,gender,count(*) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | count(*) |
+---------+--------+----------+
| NULL | M | 2 |
| 1 | F | 2 |
| 1 | M | 2 |
| 2 | M | 3 |
| 3 | F | 3 |
| 3 | M | 1 |
| 4 | M | 4 |
| 5 | M | 1 |
| 6 | F | 3 |
| 6 | M | 1 |
| 7 | F | 2 |
| 7 | M | 1 |
+---------+--------+----------+
12 rows in set (0.00 sec)
对分组聚合运算后的结果指定过滤条件
MariaDB [hellodb]> select classid,avg(age) as 平均年龄 from students group by classid having 平均年龄>30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+--------------+
3 rows in set (0.00 sec)
排序
MariaDB [hellodb]> select * from students order by age desc limit 5;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
列出所有的学生
MariaDB [hellodb]> select * from students;
列出班级编号是2的学生
MariaDB [hellodb]> select * from students where classid=2;
列出各班级有多少学生
MariaDB [hellodb]> select classid 班级编号,count(*) from students group by classid;
列出以X开头的学生姓名
MariaDB [hellodb]> select * from students where name like "X%";
列出姓xu的班级号和年龄
MariaDB [hellodb]> select name,classid,age from students where name like "xu%";
列出班级号为2和4的信息
MariaDB [hellodb]> select * from students where classid=2 or classid=4;
列出年龄大于30并且按班级编号进行排序
MariaDB [hellodb]> select * from students where age>30 order by classid;
列出年龄在20-40之间的学生信息
MariaDB [hellodb]> select * from students where age between 20 and 40;
列出班级号为null的学生信息
MariaDB [hellodb]> select * from students where classid is null;
|