文章目录
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)
- 筛选筛选结果去重distinct
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)
演练:
- 找到英语小于60分的同学及其英语成绩
mysql> select name, english from exam_result where english<60;+--------+---------+| name | english |+--------+---------+| 张三 | 56 || 田七 | 45 || 周九 | 30 |+--------+---------+3 rows in set (0.00 sec)
- 找到语文成绩在 [80, 90] 分的同学及其语文成绩
# >=和<=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)
- 数学成绩是 98 或者 99 分的同学及数学成绩
# 多个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)
- 筛选出姓张的以及张X同学(模糊匹配)
先插入一行数据:
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)
- 找到总分在200以下的同学
先看错误用法:
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)
- 找到英语成绩 > 80 并且不姓李的同学
mysql> select name, english from exam_result where english>80 and name!='李%';+--------+---------+| name | english |+--------+---------+| 王五 | 90 |+--------+---------+1 row in set (0.00 sec)
- NULL 的查询
mysql> select * from t10;+--------+------+--------+| name | age | gender |+--------+------+--------+| 李四 | 20 | 男 || 李四 | NULL | 男 |+--------+------+--------+2 rows in set (0.00 sec)
- 找到age是null的人
mysql> select name, age from t10 where age<=>null;+--------+------+| name | age |+--------+------+| 李四 | NULL |+--------+------+1 row in set (0.00 sec)
- 找到age不为null的人
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)
- 把王五的数学成绩改成80
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)
- 把李四的语文成绩改成60,数学成绩改成80
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)
- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
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)
去重思路:
- 1.创建一张属性和原表一样的空表
# 创建一张属性和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)
- 2.利用insert + distinct筛选出原表中去重后的数据
# 对原表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)
- 3.将原表名字修改为其他,将新表数据修改为原表名字
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