mysql学习：单表的数据增删改查和内置函数

文章问题导向

mysql单表的数据增删改查如何操作？内置函数？

如果你都有了答案，可以忽略本文章，或去mysql学习导图寻找更多答案

---

学前数据准备

CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `ssex` varchar(10) NOT NULL,
  `class` varchar(20) DEFAULT NULL,
  `sbirthday` datetime DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf;

INSERT INTO `student2` VALUES ('101', '小明', '男', '202001', '1988-08-08 00:00:00', '100');
INSERT INTO `student2` VALUES ('102', '李四', '男', '202002', '1988-08-09 00:00:00', '90');
INSERT INTO `student2` VALUES ('103', '王五', '女', '202001', '1988-06-08 00:00:00', '80');
INSERT INTO `student2` VALUES ('104', '秦明', '男', '202002', '1978-06-09 00:00:00', '70');
INSERT INTO `student2` VALUES ('105', '陈锋', '女', '202003', '1948-02-08 00:00:00', null);

select * from student;
+-----+-------+------+--------+---------------------+-------+
| sno | sname | ssex | class  | sbirthday           | score |
+-----+-------+------+--------+---------------------+-------+
| 101 |	小明  | 男   | 202001 | 1988-08-08 00:00:00 |   100 |
| 102 | 李四  | 男   | 202002 | 1988-08-09 00:00:00 |    90 |
| 103 | 王五  | 女   | 202001 | 1988-06-08 00:00:00 |    80 |
| 104 | 秦明  | 男   | 202002 | 1978-06-09 00:00:00 |    70 |
| 105 | 陈锋  | 女   | 202003 | 1948-02-08 00:00:00 |  NULL |
+-----+-------+------+--------+---------------------+-------+

---

常用内置函数

语法：fn(字段/*)

max() 计算某一列的最大值
min() 计算某一列的最小值
select max(score) count from student;
+-------+
| count |
+-------+
|   100 |
+-------+


count()  获取总数
select count(*) count from student;
+-------+
| count |
+-------+
|   5   |
+-------+


sum()    计算某一列的合计值，该列必须为数值类型
select sum(score) count from student;
+-------+
| count |
+-------+
|   340 |
+-------+


avg()    计算某一列的平均值，该列必须为数值类型，以不为null的数据平均分
select avg(score) count from student;
等于 
select avg(score) count from student where score is not null;
+---------+
| count   |
+---------+
| 85.0000 |
+---------+


coalesce(a,b,c)  合计行，配合group by（分组） 和 with rollup（合计，多出一行合计）使用。
参数：如果a == null,则选择b；如果b == null,则选择c；如果a != null,则选择a；如果a b c 都为null ，则返回为null。
select class, count(*) as 人数 from student2 group by class with rollup;
+--------+------+
| class  | 人数 |
+--------+------+
| 202001 |    2 |
| 202002 |    2 |
| 202003 |    1 |
| NULL   |    5 |
+--------+------+

按例子翻译：如果class为null，则使用总数替代，在这个例子中，实际上只有最后一行才会为null，因为用了with rollup多出来一行，一般用来当做合计行。
select coalesce(class, '总数') as 班级, count(*) as 人数 from student group by class with rollup;
+--------+------+
| 班级   | 人数  |
+--------+------+
| 202001 |    2 |
| 202002 |    2 |
| 202003 |    1 |
| 总数    |    5 |
+--------+------+

---

增删改查

增(插入)insert

语法一：声明插入的是什么字段
insert into tb_name (field1, field2) values(value1, value2)

语法二：按字段顺序插入
insert into tb_name values(value1, value2)

语法三：插入多条
insert into tb_name (field1, field2) values(value1, value2), (value1, value2)；

insert into student (sno,sname,ssex) values(106, '阿牛', '男');

+-----+-------+------+--------+---------------------+-------+
| sno | sname | ssex | class  | sbirthday           | score |
+-----+-------+------+--------+---------------------+-------+
| 101 |	小明  | 男   | 202001 | 1988-08-08 00:00:00 |   100 |
| 102 | 李四  | 男   | 202002 | 1988-08-09 00:00:00 |    90 |
| 103 | 王五  | 女   | 202001 | 1988-06-08 00:00:00 |    80 |
| 104 | 秦明  | 男   | 202002 | 1978-06-09 00:00:00 |    70 |
| 105 | 陈锋  | 女   | 202003 | 1948-02-08 00:00:00 |  NULL |
| 106 | 阿牛   | 男   | NULL   | NULL                |  NULL |
+-----+-------+------+--------+---------------------+-------+

删delete

修改一般需要添加条件
语法：delete from tb_name where id = 1;

删除sno = 106的同学
delete from student where sno = 106;
Query OK, 1 row affected (0.47 sec)

删除student表的全部数据
delete from student;

改update

修改需要添加条件
语法：update tb_name set field = value where id = 1;

 将sno = 101的同学的sname 改为 ‘大神’
 
 update student set sname = '大神' where sno = 101;
 Rows matched: 1  Changed: 1  Warnings: 0

---

查select

查询一般需要添加条件
语法：select * from tb_name where id = 1;

select语法顺序

inner join > where > group by > having > order by > limit

---

字段

查询全部字段，不要条件
select * from user;
+-----+-------+------+--------+--