文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【MySQL】增删查改基础

2023-09-03 07:02

关注

文章目录

CRUD : Create(创建), Retrieve(读取),Update(更新),Delete(删除)

先创建一张表:

mysql> create table if not exists stu(    -> id int unsigned primary key auto_increment,    -> sn int unsigned unique key comment '学号',    -> name varchar(20) not null,    -> qq varchar(20) unique key    -> );

1.1 单行插入

mysql> insert into stu (sn, name, qq) values(123, '张三', '1161');Query OK, 1 row affected (0.01 sec)
mysql> insert into stu values(2, 128, '李四', '7077');Query OK, 1 row affected (0.00 sec)

into 可以省略。


mysql> select * from stu;+----+------+--------+------+| id | sn   | name   | qq   |+----+------+--------+------+|  1 |  123 | 张三   | 1161 ||  2 |  128 | 李四   | 7077 |+----+------+--------+------+2 rows in set (0.00 sec)

1.2 多行插入

可以直接一行全列插入。

mysql> insert into stu values(10, 132, '王五', '2498'),(11, 147, '赵六', '8088');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from stu;+----+------+--------+------+| id | sn   | name   | qq   |+----+------+--------+------+|  1 |  123 | 张三   | 1161 ||  2 |  128 | 李四   | 7077 || 10 |  132 | 王五   | 2498 || 11 |  147 | 赵六   | 8088 |+----+------+--------+------+4 rows in set (0.00 sec)

也可以指定行插入一列:

mysql> insert into stu (sn, name, qq) values(155, '刘备', '5279'),(452, '关羽', '7892');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 
mysql> select * from stu;+----+------+--------+------+| id | sn   | name   | qq   |+----+------+--------+------+|  1 |  123 | 张三   | 1161 ||  2 |  128 | 李四   | 7077 || 10 |  132 | 王五   | 2498 || 11 |  147 | 赵六   | 8088 || 14 |  155 | 刘备   | 5279 || 15 |  452 | 关羽   | 7892 |+----+------+--------+------+6 rows in set (0.00 sec)

1.3 插入否则替换更新

对数据进行插入,首先数据要合法(相关字段没有被唯一键、主键、外键等进行约束),其次如果插入数据的id已存在,将会更新sn、name和qq而不是插入失败。

mysql> insert into stu values (15, 452, '张飞', '7892') on duplicate key update sn=452, name='张飞', qq='7892';Query OK, 2 rows affected (0.00 sec)
mysql> select * from stu;+----+------+--------+------+| id | sn   | name   | qq   |+----+------+--------+------+|  1 |  123 | 张三   | 1161 ||  2 |  128 | 李四   | 7077 || 10 |  132 | 王五   | 2498 || 11 |  147 | 赵六   | 8088 || 14 |  155 | 刘备   | 5279 || 15 |  452 | 张飞   | 7892 |+----+------+--------+------+6 rows in set (0.00 sec)

1.4 替换replace

主键 或者 唯一键 没有冲突,则直接插入;
主键 或者 唯一键 如果冲突,则删除后再插入。

mysql> select * from stu;+----+------+--------+------+| id | sn   | name   | qq   |+----+------+--------+------+|  1 |  123 | 张三   | 1161 ||  2 |  128 | 李四   | 7077 || 10 |  132 | 王五   | 2498 || 11 |  147 | 赵六   | 8088 || 14 |  155 | 刘备   | 5279 || 15 |  452 | 张飞   | 7892 |+----+------+--------+------+6 rows in set (0.00 sec)mysql> replace into stu (sn, name, qq) values (258, '曹操', 5687);Query OK, 1 row affected (0.01 sec)mysql> select * from stu;+----+------+--------+------+| id | sn   | name   | qq   |+----+------+--------+------+|  1 |  123 | 张三   | 1161 ||  2 |  128 | 李四   | 7077 || 10 |  132 | 王五   | 2498 || 11 |  147 | 赵六   | 8088 || 14 |  155 | 刘备   | 5279 || 15 |  452 | 张飞   | 7892 || 16 |  258 | 曹操   | 5687 |+----+------+--------+------+7 rows in set (0.00 sec)

在这里插入图片描述
通过id也可以看出这行数据是先删除在插入的。

先创建一个期末成绩表:

mysql> create table exam_result(    -> id int unsigned primary key auto_increment,    -> name varchar(20) not null comment '学生姓名',    -> chinese float default 0.0 comment '语文成绩',    -> math float default 0.0 comment '数学成绩',    -> english float default 0.0 comment '英语成绩'    -> );Query OK, 0 rows affected (0.03 sec)

插入数据:

mysql> insert into exam_result (name, chinese, math, english) values    -> ('张三' ,67, 98, 56),    -> ('李四', 87, 78, 77),    -> ('王五', 88, 98, 90),    -> ('赵六', 82, 84, 67),    -> ('田七', 55, 85, 45),    -> ('孙八', 70, 73, 78),    -> ('周九', 75, 65, 30);Query OK, 7 rows affected (0.01 sec)Records: 7  Duplicates: 0  Warnings: 0

2.1 select查询

mysql> select * from exam_result;+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  1 | 张三   |      67 |   98 |      56 ||  2 | 李四   |      87 |   78 |      77 ||  3 | 王五   |      88 |   98 |      90 ||  4 | 赵六   |      82 |   84 |      67 ||  5 | 田七   |      55 |   85 |      45 ||  6 | 孙八   |      70 |   73 |      78 ||  7 | 周九   |      75 |   65 |      30 |+----+--------+---------+------+---------+7 rows in set (0.00 sec)

通常情况下不建议使用 * 进行全列查询
1、查询的列越多,意味着需要传输的数据量越大;
2、可能会影响到索引的使用。

mysql> select name from exam_result;+--------+| name   |+--------+| 张三   || 李四   || 王五   || 赵六   || 田七   || 孙八   || 周九   |+--------+7 rows in set (0.00 sec)mysql> select name,english from exam_result;+--------+---------+| name   | english |+--------+---------+| 张三   |      56 || 李四   |      77 || 王五   |      90 || 赵六   |      67 || 田七   |      45 || 孙八   |      78 || 周九   |      30 |+--------+---------+7 rows in set (0.00 sec)

select可以计算表达式:

mysql> select 1+1;+-----+| 1+1 |+-----+|   2 |+-----+1 row in set (0.00 sec)

所以我们可以计算总分:

mysql> select name, (chinese + math + english) from exam_result;+--------+----------------------------+| name   | (chinese + math + english) |+--------+----------------------------+| 张三   |                        221 || 李四   |                        242 || 王五   |                        276 || 赵六   |                        233 || 田七   |                        185 || 孙八   |                        221 || 周九   |                        170 |+--------+----------------------------+7 rows in set (0.00 sec)#觉得计算式太长,可以用as给它重命名为totalmysql> select name, (chinese + math + english) as total from exam_result;+--------+-------+| name   | total |+--------+-------+| 张三   |   221 || 李四   |   242 || 王五   |   276 || 赵六   |   233 || 田七   |   185 || 孙八   |   221 || 周九   |   170 |+--------+-------+7 rows in set (0.00 sec)# as也可以省略mysql> select name 姓名, (chinese + math + english) 总分 from exam_result;+--------+--------+| 姓名   | 总分   |+--------+--------+| 张三   |    221 || 李四   |    242 || 王五   |    276 || 赵六   |    233 || 田七   |    185 || 孙八   |    221 || 周九   |    170 |+--------+--------+7 rows in set (0.00 sec)
mysql> select distinct math from exam_result;+------+| math |+------+|   98 ||   78 ||   84 ||   85 ||   73 ||   65 |+------+6 rows in set (0.00 sec)

2.2 where条件判断

运算符 说明>, >=, <, <= 大于,大于等于,小于,小于等于= 等于,NULL 不安全,例如 NULL = NULL 的结果是 NULL<=>等于,NULL 安全,例如 NULL <=> NULL 的结果是 TRUE(1)!=, <> 不等于BETWEEN a0 AND a1 范围匹配,[a0, a1],如果 a0 <= value <= a1,返回 TRUE(1)IN (option, ...) 如果是 option 中的任意一个,返回 TRUE(1)IS NULL 是 NULLIS NOT NULL 不是 NULLLIKE 模糊匹配。% 表示任意多个(包括 0 个)任意字符;_ 表示任意一个字符
运算符 说明AND 多个条件必须都为 TRUE(1),结果才是 TRUE(1)OR 任意一个条件为 TRUE(1), 结果为 TRUE(1)NOT条件为 TRUE(1),结果为 FALSE(0)

演练:

mysql> select name, english from exam_result where english<60;+--------+---------+| name   | english |+--------+---------+| 张三   |      56 || 田七   |      45 || 周九   |      30 |+--------+---------+3 rows in set (0.00 sec)
# >=和<=mysql> select name, chinese from exam_result where chinese>=80 and chinese<=90;+--------+---------+| name   | chinese |+--------+---------+| 李四   |      87 || 王五   |      88 || 赵六   |      82 |+--------+---------+3 rows in set (0.00 sec)# between a0 and a1mysql> select name, chinese from exam_result where chinese between 80 and 90;+--------+---------+| name   | chinese |+--------+---------+| 李四   |      87 || 王五   |      88 || 赵六   |      82 |+--------+---------+3 rows in set (0.00 sec)
# 多个ormysql> select name, math from exam_result where math=98 or math=99;+--------+------+| name   | math |+--------+------+| 张三   |   98 || 王五   |   98 |+--------+------+2 rows in set (0.00 sec)# in关键字mysql> select name, math from exam_result where math in (98, 99);+--------+------+| name   | math |+--------+------+| 张三   |   98 || 王五   |   98 |+--------+------+2 rows in set (0.00 sec)

先插入一行数据:

mysql> insert into exam_result (name, chinese, math, english) values ('张翼德', 45, 98, 66);Query OK, 1 row affected (0.01 sec)

先找出所有姓张的同学:

# '张%'找到所有张姓同学,%代表后面模糊匹配0-n个字符mysql> select name from exam_result where name like '张%';+-----------+| name      |+-----------+| 张三      || 张翼德    |+-----------+2 rows in set (0.00 sec)

接下来找到叫张某的同学:

# _代表模糊匹配1个字符mysql> select name from exam_result where name like '张_';+--------+| name   |+--------+| 张三   |+--------+1 row in set (0.00 sec)
mysql> select name, chinese, english from exam_result where english<chinese;+--------+---------+---------+| name   | chinese | english |+--------+---------+---------+| 张三   |      67 |      56 || 李四   |      87 |      77 || 赵六   |      82 |      67 || 田七   |      55 |      45 || 周九   |      75 |      30 |+--------+---------+---------+5 rows in set (0.00 sec)

先看错误用法:

mysql> select name, chinese+math+english 'total' from exam_result where total < 200;ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

这主要是执行顺序的原因:
1️⃣ 执行from
2️⃣ 执行where字句
3️⃣ 筛选要打印的信息

这里执行where的时候发现没有total别名所以会出错。

所以后边不能用别名:

mysql> select name, chinese+math+english 'total' from exam_result where chinese+math+english < 200;+--------+-------+| name   | total |+--------+-------+| 田七   |   185 || 周九   |   170 |+--------+-------+2 rows in set (0.00 sec)
mysql> select name, english from exam_result where english>80 and name!='李%';+--------+---------+| name   | english |+--------+---------+| 王五   |      90 |+--------+---------+1 row in set (0.00 sec)
mysql> select * from t10;+--------+------+--------+| name   | age  | gender |+--------+------+--------+| 李四   |   20 ||| 李四   | NULL ||+--------+------+--------+2 rows in set (0.00 sec)
mysql> select name, age from t10 where age<=>null;+--------+------+| name   | age  |+--------+------+| 李四   | NULL |+--------+------+1 row in set (0.00 sec)
mysql> select name, age from t10 where age is not null;+--------+------+| name   | age  |+--------+------+| 李四   |   20 |+--------+------+1 row in set (0.00 sec)

2.3 order by排序

对于没有 order by 子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序。

ASC 为升序(从小到大)DESC 为降序(从大到小)默认为 ASCNULL 视为比任何值都小
# 升序 ASCmysql> select name, math from exam_result order by math ASC;+-----------+------+| name      | math |+-----------+------+| 周九      |   65 || 孙八      |   73 || 李四      |   78 || 赵六      |   84 || 田七      |   85 || 张三      |   98 || 王五      |   98 || 张翼德    |   98 |+-----------+------+8 rows in set (0.00 sec)# 降序 DESCmysql> select name, math from exam_result order by math DESC;+-----------+------+| name      | math |+-----------+------+| 张三      |   98 || 王五      |   98 || 张翼德    |   98 || 田七      |   85 || 赵六      |   84 || 李四      |   78 || 孙八      |   73 || 周九      |   65 |+-----------+------+8 rows in set (0.00 sec)

意思是如果数学成绩是相等的话就按照英语降序的顺序排,语文同理。

mysql> select name, math, english, chinese from exam_result order by math desc, english desc, chinese asc;+-----------+------+---------+---------+| name      | math | english | chinese |+-----------+------+---------+---------+| 王五      |   98 |      90 |      88 || 张翼德    |   98 |      66 |      45 || 张三      |   98 |      56 |      67 || 田七      |   85 |      45 |      55 || 赵六      |   84 |      67 |      82 || 李四      |   78 |      77 |      87 || 孙八      |   73 |      78 |      70 || 周九      |   65 |      30 |      75 |+-----------+------+---------+---------+8 rows in set (0.00 sec)
mysql> select name, chinese+math+english as total from exam_result order by total desc;+-----------+-------+| name      | total |+-----------+-------+| 王五      |   276 || 李四      |   242 || 赵六      |   233 || 张三      |   221 || 孙八      |   221 || 张翼德    |   209 || 田七      |   185 || 周九      |   170 |+-----------+-------+8 rows in set (0.00 sec)

上面的where语句是不能用别名的。但在这里可以使用别名,还是因为顺序的问题。

第一优先级:明确找的是哪张表exam_result
第二优先级:where子句
第三优先级:chinese+math+english 总分
第四优先级:order by 总分 desc(要有合适的数据再进行排序)
第五优先级:limit(数据准备好了,才进行显示)

mysql> select name, math from exam_result where name like '张%' or name like '李%' order by math desc;+-----------+------+| name      | math |+-----------+------+| 张三      |   98 || 张翼德    |   98 || 李四      |   78 |+-----------+------+3 rows in set (0.00 sec)

2.4 limit筛选分页结果

mysql> select * from exam_result;+----+-----------+---------+------+---------+| id | name      | chinese | math | english |+----+-----------+---------+------+---------+|  1 | 张三      |      67 |   98 |      56 ||  2 | 李四      |      87 |   78 |      77 ||  3 | 王五      |      88 |   98 |      90 ||  4 | 赵六      |      82 |   84 |      67 ||  5 | 田七      |      55 |   85 |      45 ||  6 | 孙八      |      70 |   73 |      78 ||  7 | 周九      |      75 |   65 |      30 ||  8 | 张翼德    |      45 |   98 |      66 |+----+-----------+---------+------+---------+8 rows in set (0.00 sec)

这样显示数据太多了。
我们想要看表的前五行:

mysql> select * from exam_result limit 5;+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  1 | 张三   |      67 |   98 |      56 ||  2 | 李四   |      87 |   78 |      77 ||  3 | 王五   |      88 |   98 |      90 ||  4 | 赵六   |      82 |   84 |      67 ||  5 | 田七   |      55 |   85 |      45 |+----+--------+---------+------+---------+5 rows in set (0.00 sec)

当然也可以从中间开始截取:

mysql> select * from exam_result limit 1,3;+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  2 | 李四   |      87 |   78 |      77 ||  3 | 王五   |      88 |   98 |      90 ||  4 | 赵六   |      82 |   84 |      67 |+----+--------+---------+------+---------+3 rows in set (0.00 sec)# limit s offset n其中s代表步长,n代表下标mysql> select * from exam_result limit 3 offset 1;+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  2 | 李四   |      87 |   78 |      77 ||  3 | 王五   |      88 |   98 |      90 ||  4 | 赵六   |      82 |   84 |      67 |+----+--------+---------+------+---------+3 rows in set (0.00 sec)

这里要注意limit的本质并不是条件筛选,而是显示。

直接举例子:

mysql> select * from exam_result;+----+-----------+---------+------+---------+| id | name      | chinese | math | english |+----+-----------+---------+------+---------+|  1 | 张三      |      67 |   98 |      56 ||  2 | 李四      |      87 |   78 |      77 ||  3 | 王五      |      88 |   98 |      90 ||  4 | 赵六      |      82 |   84 |      67 ||  5 | 田七      |      55 |   85 |      45 ||  6 | 孙八      |      70 |   73 |      78 ||  7 | 周九      |      75 |   65 |      30 ||  8 | 张翼德    |      45 |   98 |      66 |+----+-----------+---------+------+---------+8 rows in set (0.00 sec)
mysql> update exam_result set math=80 where name='王五';Query OK, 1 row affected (0.01 sec)mysql> select name, math from exam_result where name='王五';+--------+------+| name   | math |+--------+------+| 王五   |   80 |+--------+------+1 row in set (0.00 sec)
mysql> update exam_result set chinese=60, math=80 where name='李四';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from exam_result where name='李四';+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  2 | 李四   |      60 |   80 |      77 |+----+--------+---------+------+---------+1 row in set (0.00 sec)
mysql> update exam_result set math=math+30 order by chinese+math+english asc limit 3;Query OK, 3 rows affected (0.00 sec)Rows matched: 3  Changed: 3  Warnings: 0
mysql> update exam_result set chinese=chinese*2;Query OK, 8 rows affected (0.02 sec)Rows matched: 8  Changed: 8  Warnings: 0mysql> select * from exam_result;+----+-----------+---------+------+---------+| id | name      | chinese | math | english |+----+-----------+---------+------+---------+|  1 | 张三      |     134 |   98 |      56 ||  2 | 李四      |     120 |   80 |      77 ||  3 | 王五      |     176 |   80 |      90 ||  4 | 赵六      |     164 |   84 |      67 ||  5 | 田七      |     110 |  115 |      45 ||  6 | 孙八      |     140 |   73 |      78 ||  7 | 周九      |     150 |   95 |      30 ||  8 | 张翼德    |      90 |  128 |      66 |+----+-----------+---------+------+---------+8 rows in set (0.00 sec)

没有where子句直接更新全表,需谨慎!update的搞错了危害不亚于delete。

4.1 删除一列

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
mysql> select * from exam_result where name='周九';+----+--------+---------+------+---------+| id | name   | chinese | math | english |+----+--------+---------+------+---------+|  7 | 周九   |     150 |   95 |      30 |+----+--------+---------+------+---------+1 row in set (0.01 sec)mysql> delete from exam_result where name='周九';Query OK, 1 row affected (0.00 sec)mysql> select * from exam_result where name='周九';Empty set (0.00 sec)
mysql> delete from exam_result order by chinese+math+english desc limit 1;Query OK, 1 row affected (0.01 sec)

4.2 删除整张表数据

语法:

delete from 表名

这样只是情况了表的数据,但是表还在。如果存在自增长约束,再插入数据就会按照上一个自增最大值+1。

语法:

truncate [table] 表名

只能对整表操作,不能像 DELETE 一样针对部分数据操作;
实际上 MySQL 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事物,所以无法回滚
会重置 AUTO_INCREMENT 项

# 创建表mysql> create table duplicate_table(    -> id int,    -> name varchar(20));Query OK, 0 rows affected (0.04 sec)# 插入数据mysql> insert into duplicate_table values    -> (100, 'a'),    -> (100, 'a'),    -> (200, 'b'),    -> (200, 'b'),    -> (200, 'b'),    -> (300, 'c');Query OK, 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0

查看表:

mysql> select * from duplicate_table;+------+------+| id   | name |+------+------+|  100 | a    ||  100 | a    ||  200 | b    ||  200 | b    ||  200 | b    ||  300 | c    |+------+------+6 rows in set (0.00 sec)

去重思路:

# 创建一张属性和duplicate_table一样的表no_duplicate_table(空表)mysql> create table no_duplicate_table like duplicate_table;Query OK, 0 rows affected (0.03 sec)

查看两张表得属性发现是一样的:

mysql> desc no_duplicate_table;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc duplicate_table;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)
# 对原表select出来的结果insert进新表中mysql> insert into no_duplicate_table select distinct * from duplicate_table;Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from no_duplicate_table;+------+------+| id   | name |+------+------+|  100 | a    ||  200 | b    ||  300 | c    |+------+------+3 rows in set (0.00 sec)
mysql> rename table duplicate_table to old_duplicate_table, no_duplicate_table to duplicate_table;Query OK, 0 rows affected (0.02 sec)mysql> select * from duplicate_table;+------+------+| id   | name |+------+------+|  100 | a    ||  200 | b    ||  300 | c    |+------+------+3 rows in set (0.00 sec)

这里的rename改名就是单纯的改名,其实就是文件名和inode的映射关系的改变。

来源地址:https://blog.csdn.net/qq_66314292/article/details/132121593

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯