6.表的增删改查CURD

1.CREATE，数据的插入

创建一张学生表

mysql> create table student(->     id int primary key comment '学生id',->     name varchar(32) comment '学生姓名',->     age int comment'学生年龄',->     email varchar(32) comment '学生邮箱'-> );
Query OK, 0 rows affected (0.60 sec)

单行数据+全列插入

--语法
insert into 表名 values(列1值,列2值,列3值);

mysql> insert into student values(1,'Alice',18,'1@1.com');
Query OK, 1 row affected (0.16 sec)mysql> select * from student;
+----+-------+------+---------+
| id | name  | age  | email   |
+----+-------+------+---------+
|  1 | Alice |   18 | 1@1.com |
+----+-------+------+---------+
1 row in set (0.00 sec)

多行数据+全部列插入

--语法
insert into 表名 values(列1值,列2值,列3值),(列1值,列2值,列3值);

mysql> insert into student values(2,'Bob',18,'2@2.com'),  (3,'Candy',18,'3@3.com');
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student;
+----+-------+------+---------+
| id | name  | age  | email   |
+----+-------+------+---------+
|  1 | Alice |   18 | 1@1.com |
|  2 | Bob   |   18 | 2@2.com |
|  3 | Candy |   18 | 3@3.com |
+----+-------+------+---------+
3 rows in set (0.00 sec)

单行数据+指定列插入

--语法
insert into 表名(列1名,列2名)values(列1值,列2值);

mysql> insert into student(id,name)values(4,'Davis');
Query OK, 1 row affected (0.07 sec)mysql> select * from student;
+----+-------+------+---------+
| id | name  | age  | email   |
+----+-------+------+---------+
|  1 | Alice |   18 | 1@1.com |
|  2 | Bob   |   18 | 2@2.com |
|  3 | Candy |   18 | 3@3.com |
|  4 | Davis | NULL | NULL    |
+----+-------+------+---------+
4 rows in set (0.00 sec)

多行数据+指定列插入

--语法
insert into 表名(列1名,列2名)values(列1值,列2值),(列1值,列2值);

mysql> insert into student(id,name)values(5,'Edsiom'),(6,'Frank');
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student;
+----+--------+------+---------+
| id | name   | age  | email   |
+----+--------+------+---------+
|  1 | Alice  |   18 | 1@1.com |
|  2 | Bob    |   18 | 2@2.com |
|  3 | Candy  |   18 | 3@3.com |
|  4 | Davis  | NULL | NULL    |
|  5 | Edsiom | NULL | NULL    |
|  6 | Frank  | NULL | NULL    |
+----+--------+------+---------+
6 rows in set (0.00 sec)

主键或者唯一键替换

在student表中，id是主键，不能重复，但是如果想要修改id为1的学生的姓名应该如何操作

这种同步更新操作会先删除原有数据，在进行新的插入，所以会有两行受到影响

--语法
insert into 表名（列1，列2） values (列1值, 列2值)  on duplicate key update 列1=列1值，列2=列2值;

mysql> insert into student(id,name)values(1,'Alice')on duplicate key update id =1,name='Amda';
Query OK, 2 rows affected (1.22 sec)mysql> select * from student;
+----+--------+------+---------+
| id | name   | age  | email   |
+----+--------+------+---------+
|  1 | Amda   |   18 | 1@1.com |
|  2 | Bob    |   18 | 2@2.com |
|  3 | Candy  |   18 | 3@3.com |
|  4 | Davis  | NULL | NULL    |
|  5 | Edsiom | NULL | NULL    |
|  6 | Frank  | NULL | NULL    |
+----+--------+------+---------+
6 rows in set (0.00 sec)

替换：直接替换有主键冲突的数据

--语法
replace into 表名(列1,列2)values(列1值,列2值);

mysql> replace into student(id,name)values(1,'Alice');
Query OK, 2 rows affected (0.14 sec)mysql> select * from student;
+----+--------+------+---------+
| id | name   | age  | email   |
+----+--------+------+---------+
|  1 | Alice  | NULL | NULL    |
|  2 | Bob    |   18 | 2@2.com |
|  3 | Candy  |   18 | 3@3.com |
|  4 | Davis  | NULL | NULL    |
|  5 | Edsiom | NULL | NULL    |
|  6 | Frank  | NULL | NULL    |
+----+--------+------+---------+
6 rows in set (0.00 sec)

2.RETRIEVE、查询

创建学生成绩表并插入数据得如下表

+----+-----------+------+---------+---------+
| id | name      | math | chinese | english |
+----+-----------+------+---------+---------+
|  1 | 张三      |   54 |      32 |      88 |
|  2 | 李四      |   88 |      99 |      25 |
|  3 | 王五      |   55 |      99 |      88 |
|  4 | 张六六    |   48 |      96 |      32 |
|  5 | 赵四      |   74 |      68 |      87 |
|  6 | 周七      |   70 |      60 |      58 |
|  7 | 王八      |   99 |      98 |      95 |
+----+-----------+------+---------+---------+

查询所有列

--语法
select * from 表名；

mysql> select * from score;
+----+-----------+------+---------+---------+
| id | name      | math | chinese | english |
+----+-----------+------+---------+---------+
|  1 | 张三      |   54 |      32 |      88 |
|  2 | 李四      |   88 |      99 |      25 |
|  3 | 王五      |   55 |      99 |      88 |
|  4 | 张六六    |   48 |      96 |      32 |
|  5 | 赵四      |   74 |      68 |      87 |
|  6 | 周七      |   70 |      60 |      58 |
|  7 | 王八      |   99 |      98 |      95 |
+----+-----------+------+---------+---------+
7 rows in set (0.00 sec)

查询指定列

--语法（不需要按照指定顺序查询）
select 列1，列2 from 表名；

--查询学生姓名对应的语文成绩和数学成绩
mysql> select name,chinese,math from score;
+-----------+---------+------+
| name      | chinese | math |
+-----------+---------+------+
| 张三      |      32 |   54 |
| 李四      |      99 |   88 |
| 王五      |      99 |   55 |
| 张六六    |      96 |   48 |
| 赵四      |      68 |   74 |
| 周七      |      60 |   70 |
| 王八      |      98 |   99 |
+-----------+---------+------+
7 rows in set (0.00 sec)

查询字段或表达式

--语法
select 表达式 as 新列名 from 表名;  --as可以省略

--查询学生姓名所对应的总成绩
mysql> select name,math+chinese+english as total from score;
+-----------+-------+
| name      | total |
+-----------+-------+
| 张三      |   174 |
| 李四      |   212 |
| 王五      |   242 |
| 张六六    |   176 |
| 赵四      |   229 |
| 周七      |   188 |
| 王八      |   292 |
+-----------+-------+
7 rows in set (0.10 sec)

结果去重

--语法
select distinct 列名 from 表名;

--查询所有的不重复的语文成绩
mysql> select distinct chinese from score;
+---------+
| chinese |
+---------+
|      32 |
|      99 |
|      96 |
|      68 |
|      60 |
|      98 |
+---------+
6 rows in set (0.02 sec)

条件查询

--语法
查询语句 where 查询条件;

--查询语文成绩为99或者数学成绩为48的同学信息
mysql> select  * from score where chinese=99 or math=48;
+----+-----------+------+---------+---------+
| id | name      | math | chinese | english |
+----+-----------+------+---------+---------+
|  2 | 李四      |   88 |      99 |      25 |
|  3 | 王五      |   55 |      99 |      88 |
|  4 | 张六六    |   48 |      96 |      32 |
+----+-----------+------+---------+---------+
3 rows in set (0.00 sec)

--查询姓名为三个字的学生信息
mysql> select  * from score where name like'___';
+----+-----------+------+---------+---------+
| id | name      | math | chinese | english |
+----+-----------+------+---------+---------+
|  4 | 张六六    |   48 |      96 |      32 |
+----+-----------+------+---------+---------+
1 row in set (0.02 sec)

--查询语文成绩小于等于数学成绩的同学信息
mysql> select  * from score where chinese<=math;
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  1 | 张三   |   54 |      32 |      88 |
|  5 | 赵四   |   74 |      68 |      87 |
|  6 | 周七   |   70 |      60 |      58 |
|  7 | 王八   |   99 |      98 |      95 |
+----+--------+------+---------+---------+
4 rows in set (0.00 sec)

--查询总分高于150的分数，tips：在where中不允许使用别名
mysql> select math+chinese+english 总分 from score where math+chinese+english>150;
+--------+
| 总分   |
+--------+
|    174 |
|    212 |
|    242 |
|    176 |
|    229 |
|    188 |
|    292 |
+--------+
7 rows in set (0.00 sec)

结果排序（order by）：ASC（升序，默认）DESC（降序）

--语法
查询语句 order by 字段 desc/asc;

--按数学成绩降序查询学生信息
mysql> select  * from score order by math desc;
+----+-----------+------+---------+---------+
| id | name      | math | chinese | english |
+----+-----------+------+---------+---------+
|  7 | 王八      |   99 |      98 |      95 |
|  2 | 李四      |   88 |      99 |      25 |
|  5 | 赵四      |   74 |      68 |      87 |
|  6 | 周七      |   70 |      60 |      58 |
|  3 | 王五      |   55 |      99 |      88 |
|  1 | 张三      |   54 |      32 |      88 |
|  4 | 张六六    |   48 |      96 |      32 |
+----+-----------+------+---------+---------+
7 rows in set (0.00 sec)

按数学成绩降序查询姓张的同学的信息
mysql> select  * from score where name like'张%' order by math desc;
+----+-----------+------+---------+---------+
| id | name      | math | chinese | english |
+----+-----------+------+---------+---------+
|  1 | 张三      |   54 |      32 |      88 |
|  4 | 张六六    |   48 |      96 |      32 |
+----+-----------+------+---------+---------+
2 rows in set (0.00 sec)

分页查询

--语法
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET n;

--插叙数学成绩前三的同学信息
mysql> select  * from score order by math desc limit 3 offset 0;
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  7 | 王八   |   99 |      98 |      95 |
|  2 | 李四   |   88 |      99 |      25 |
|  5 | 赵四   |   74 |      68 |      87 |
+----+--------+------+---------+---------+
3 rows in set (0.10 sec)mysql> select  * from score order by math desc limit 0,3;
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  7 | 王八   |   99 |      98 |      95 |
|  2 | 李四   |   88 |      99 |      25 |
|  5 | 赵四   |   74 |      68 |      87 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)mysql> select  * from score order by math desc limit 3;
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  7 | 王八   |   99 |      98 |      95 |
|  2 | 李四   |   88 |      99 |      25 |
|  5 | 赵四   |   74 |      68 |      87 |
+----+--------+------+---------+---------+
3 rows in set (0.00 sec)

3.UPDATE、修改

--语法
update 表名 set 列名 = 新的列名;

--给张三同学的数学成绩加五分
mysql> select * from score where name like '张三';
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  1 | 张三   |   54 |      32 |      88 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)mysql> update score set math = math+10 where name like'张三';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from score where name like '张三';
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  1 | 张三   |   64 |      32 |      88 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)

4.DELETE、删除

--语法
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

--删除张三同学的成绩
mysql> select * from score where name like'张三';
+----+--------+------+---------+---------+
| id | name   | math | chinese | english |
+----+--------+------+---------+---------+
|  1 | 张三   |   64 |      32 |      88 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)mysql> delete from score where name like '张三';
Query OK, 1 row affected (0.19 sec)mysql> select * from score where name like'张三';
Empty set (0.00 sec)

5.截断表（truncate）和删除表

--建立两张一样的表a和b，id自增长
mysql> create table a(-> id int primary key auto_increment,-> name varchar(32)-> );
Query OK, 0 rows affected (1.88 sec)mysql> insert into a (name)values('alice'),('bob');
Query OK, 2 rows affected (0.16 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from a;
+----+-------+
| id | name  |
+----+-------+
|  1 | alice |
|  2 | bob   |
+----+-------+
2 rows in set (0.00 sec)mysql> create table b(-> id int primary key auto_increment,-> name varchar(32)-> );
Query OK, 0 rows affected (0.63 sec)mysql> insert into b (name)values('alice'),('bob');
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from b;
+----+-------+
| id | name  |
+----+-------+
|  1 | alice |
|  2 | bob   |
+----+-------+
2 rows in set (0.00 sec)

--对表a进行整表删除，然后插入一条数据
mysql> delete from a;
Query OK, 2 rows affected (0.18 sec)mysql> insert into a (name)values('cindy');
Query OK, 1 row affected (0.15 sec)mysql> select * from a;
+----+-------+
| id | name  |
+----+-------+
|  3 | cindy |
+----+-------+
1 row in set (0.00 sec)

可以看到，删除整张表后id自增长是接着没有删除前的id继续增长的

--对表b进行截断，然后插入数据
mysql> truncate b;
Query OK, 0 rows affected (0.97 sec)mysql> insert into b (name)values('cindy');
Query OK, 1 row affected (0.16 sec)mysql> select * from b;
+----+-------+
| id | name  |
+----+-------+
|  1 | cindy |
+----+-------+
1 row in set (0.00 sec)

可以看到，id是重新进行了自增长

截断表的特点：1.只能对整表操作；2.速度比delete快；3.重置auto_increment

6.数据库中的聚合函数

count()	返回查询到的数据的数量
sum()	返回查询到的数据的总和，不是数字没有意义
avg()	返回查询到的数据的平均值，不是数字没有意义
max()	返回查询到的数据的最大值，不是数字没有意义
min()	返回查询到的数据的最小值，不是数字没有意义

6.表的增删改查CURD

相关文章

JAVA lambda 简单例子

转载华硕A6的拆解

请问python如何处理url带有“？”参数的接口？

T2-U开发板实现红外遥控接收与发送

10 概率方法

Linux进程间通信【共享内存】

ChatGPT 入门教程||ChatGPT 应用场景3：推理||ChatGPT 应用场景4：生成代码

北京市乡镇人口数据