什么是CRUD?
CRUD,即增加(Create)、查找(Retrieve)、修改(Update)、删除(Delete)四个单词的首字母缩写。
在进行下面所有操作的前提都是得选中一个数据库,并且已经创建了可以用来操作的表。
默认我们现在已经创建了learning数据库,在数据库中有一张student表。后续操作都基于此进行
新增(Create)
insert into 表名 values(值,值,值...);
注:这里值的类型和个数要和表的 列的类型和个数匹配。
单行数据 + 全列插入
mysql> insert into student values (1,"zhangsan");
Query OK, 1 row affected (0.00 sec)
注:
- 在SQL中没有字符串类型,所以既可以用‘ ’来引用字符串,又可以使用“ ”来引用字符串;
- 还可以直接插入中文字符(需要把数据库字符集改为UTF-8)
- 进行插入操作时,插入值的个数和类型要于表中字段的个数和类型匹配
多行数据 + 指定列插入
mysql> insert into student values (1,"zhangsan"),(2,"lisi"),(3,"wangwu");
Query OK, 3 row affected (0.00 sec)
注:比一条一条插入更快
查询(Retrieve)
MySQL是一个客户端—服务器结构的程序,显示在客户端的查询结果是一个“临时表”,服务器端的数据并不是这样的组织形式。
全列查询
select* from 表名;
mysql> select * from student;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
注:
- 通配符* 表示匹配所有的列,即查询所有列,把所有的数据都查询出来;
- 这是一个危险操作(当数据量大的时候):进行此操作的时候,服务器要先读取磁盘,把这些数据都查出来,再通过网卡,把这些数据传输给客户端,由于数据量非常大,极有可能把磁盘IO(输入输出)吃满,或者网络带宽吃满。这时其他数据就无法正常返回了,最直观的感受就是客户端感受到卡顿。
指定列查询
select 列名,列名,列名.... from 表名;
mysql> select id from student;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
注:当我们查询时省去一些不必要的列时,就可以节约大量的磁盘IO和网络带宽了。
查询字段为表达式
select 表达式 from 表名;
- 演示此操作需要创建一个新的表:
mysql> create table exam_result (id int, name varchar(20), chinese decimal(3,1),math decimal(3,1), english decimal(3,1));
Query OK, 0 rows affected (0.01 sec)
//decimal(3,1) 表示共有三位有效数字,保留一位小数。 比如:32.1、10.5
- 查看一下表结构:
mysql> desc exam_result;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| chinese | decimal(3,1) | YES | | NULL | |
| math | decimal(3,1) | YES | | NULL | |
| english | decimal(3,1) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 插入数据
mysql> INSERT INTO exam_result (id,name, chinese, math, english) VALUES
-> (1,'zhangsan', 67, 98, 56),
-> (2,'lisi', 87.5, 78, 77),
-> (3,'wangwu', 88, 98.5, 90),
-> (4,'zhaoliu', 82, 84, 67),
-> (5,'sunqi', 55.5, 85, 45),
-> (6,'zhouba', 70, 73, 78.5),
-> (7,'wujiu', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
- 查询表内全部数据
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)
- 带表达式的查询:让所有人的语文成绩+10分
mysql> select name,chinese + 10 from exam_result;
+-----------+--------------+
| name | chinese + 10 |
+-----------+--------------+
| zhangsan | 77.0 |
| lisi | 97.5 |
| wangwu | 98.0 |
| zhaoliu | 92.0 |
| sunqi | 65.5 |
| zhouba | 80.0 |
| wujiu | 85.0 |
+-----------+--------------+
7 rows in set (0.00 sec)
起别名查询
select 表达式 as 别名 from 表名;
- 普通情况查询语、数、英三科总分
mysql> select name, chinese + math + english from exam_result;
+-----------+--------------------------+
| name | chinese + math + english |
+-----------+--------------------------+
| zhangsan | 221.0 |
| lisi | 242.5 |
| wangwu | 276.5 |
| zhaoliu | 233.0 |
| sunqi | 185.5 |
| zhouba | 221.5 |
| wujiu | 170.0 |
+-----------+--------------------------+
7 rows in set (0.00 sec)
- 起别名查询语、数、英三科总分
mysql> select name, chinese + english + math as total from exam_result;
+-----------+-------+
| name | total |
+-----------+-------+
| zhangsan | 221.0 |
| lisi | 242.5 |
| wangwu | 276.5 |
| zhaoliu | 233.0 |
| sunqi | 185.5 |
| zhouba | 221.5 |
| wujiu | 170.0 |
+-----------+-------+
7 rows in set (0.00 sec)
注:as可以写着,也可以省略。 建议写着!
去重查询
select distinct 列名 from 表名;
- 演示此操作需增加相同信息
mysql> insert into exam_result (name, math) values ('zhangsan', 98.0);
Query OK, 1 row affected (0.00 sec)
- 查看当前表的全部信息
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
- 进行去重查询
mysql> select distinct name, math from exam_result;
+-----------+------+
| name | math |
+-----------+------+
| zhangsan | 98.0 |
| lisi | 78.0 |
| wangwu | 98.5 |
| zhaoliu | 84.0 |
| sunqi | 85.0 |
| zhouba | 73.0 |
| wujiu | 65.0 |
+-----------+------+
7 rows in set (0.00 sec)
注:当用distinct指定多个列时,必须是这几个列的值同时相同时才会去重。
排序查询
select 列名 from 表名 order by 列名;
- 按语文成绩升序排序
mysql> select * from exam_result order by chinese;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| NULL | zhangsan | NULL | 98.0 | NULL |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
- 按语文成绩降序排序
mysql> select * from exam_result order by chinese desc;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
- order by 也可以针对别名进行排序
mysql> select name, chinese + math + english as total from exam_result order by total desc;
+-----------+-------+
| name | total |
+-----------+-------+
| wangwu | 276.5 |
| lisi | 242.5 |
| zhaoliu | 233.0 |
| zhouba | 221.5 |
| zhangsan | 221.0 |
| sunqi | 185.5 |
| wujiu | 170.0 |
| zhangsan | NULL |
+-----------+-------+
8 rows in set (0.00 sec)
- order by 进行排序的时候还可以指定多个列进行排序 效果是:先以第一列为标准进行比较,如果第一列不分胜负,那么继续按照第二列进行比较,一次类推
mysql> select * from exam_result order by math desc,chinese;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
注:
- 升序排序末尾加asc 但默认是升序排序可以省略,降序排序末尾加desc
- 在SQL中,拿NULL和其他类型进行混合计算,结果仍然是NULL
- 在select操作中,如果没有使用order by 那么查询结果的顺序是不确定的。
条件查询
select* from 表名 where 条件;
引入where子句,对条件进行筛选,即:用where子句对最初的每一行查询结果进行筛选,如果满足条件,就把这一行放入到最终的查询结果;如果不满足条件,则舍弃这一行;最后返回最终查询结果。
- 比较运算符
注:
- 在SQL中没有== 使用=进行比较
- 在SQL中,NULL = NULL 结果还是NUULL ,相当于false; NULL <=> NULL 结果是true
- like进行模糊匹配,匹配过程中可以带上通配符
- 逻辑运算符
注:
- 在where条件中,可以使用表达式,但不能使用别名;
- and的优先级高于or,在使用时注意次序或者加()
- 基本查询:查询语文成绩比英语成绩好的人
mysql> select * from exam_result where chinese > english;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)
- and / or查询
mysql> select * from exam_result where chinese > 80 or english > 70 and math > 70;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)
- 范围查询:查询语文成绩在80-90之间的人
mysql> select * from exam_result where chinese >= 80 and chinese <= 90;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result where chinese between 80 and 90;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)
- in 查询 查询数学成绩是58 或者59 或者98 或者99的人
mysql> select * from exam_result where math in (58,59,98,99);
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
+------+-----------+---------+------+---------+
1 rows in set (0.00 sec)
mysql> select * from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
+------+-----------+---------+------+---------+
1 rows in set (0.00 sec)
- 模糊查询like 不一定完全相同,只要有一部分匹配即可。
mysql> select * from exam_result where name like 'w%';
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 3 | wangwu | 88 | 98.5 | 90 |
| 7 | wujiu | 75 | 65 | 30 |
+------+-----------+---------+------+---------+
2 rows in set (0.00 sec)
注: % 可以替代任意个字符,_ 可以替代任意一个字符
- NULL的查询
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
mysql> select * from exam_result where chinese = NULL;
Empty set (0.00 sec)
mysql> select * from exam_result where chinese <=> NULL;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| NULL |zhangsan| NULL | 98.0 | NULL |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
mysql> select * from exam_result where chinese is NULL;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| NULL |zhangsan| NULL | 98.0 | NULL |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
注:
- 直接使用 = 来进行匹配是不能正确进行筛选的
- 使用 <=> 可以正确和NULL匹配
- 使用 is NULL也可以正确和NULL匹配
分页查询
select 列名 from 表名 limit N offset M;
select 列名 from 表名 limit M,N;
N:返回结果的条数 M:跳过M条结果再开始返回
- 从M条开始查询 最多返回N条结果
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
mysql> select * from exam_result limit 3;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 78.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from exam_result limit 3 offset 3;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)
注:select* 这样的操作容易把数据库搞挂了,除了select*外,其他的查询操作只要你返回的结果足够多,都有可能把数据库搞挂;即使你加上了where子句进行筛选,但是返回的结果仍然可能很多。最保险的办法就是加上limit
修改(Update)
update 表名 set 列名 = 值..... where 条件;
- 把lisi 的数学成绩修改为80分
mysql> update exam_result set math = 80 where name = 'lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.5 | 80.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 82.0 | 84.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
| NULL | zhangsan | NULL | 98.0 | NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)
注: update后面的where条件很重要。加上条件,表示修改符合条件某些行;不加条件,表示修改所有行。文章来源:https://www.toymoban.com/news/detail-720644.html
删除(Delete)
delete from 表名 where 条件;
- 删除zhangsan的信息
mysql> delete from exam_result where name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 2 | lisi | 87.5 | 80.0 | 77.0 |
| 3 | wangwu | 88.0 | 98.5 | 90.0 |
| 4 | zhaoliu | 70.0 | 60.0 | 67.0 |
| 5 | sunqi | 55.5 | 85.0 | 45.0 |
| 6 | zhouba | 70.0 | 73.0 | 78.5 |
| 7 | wujiu | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
6 rows in set (0.00 sec)
注:文章来源地址https://www.toymoban.com/news/detail-720644.html
- delete后面的 where 条件很重要。加上条件,表示删除符合条件某些行;不加条件,表示删除表中的全部信息。
- delete from 表名;表示删除表内的所有信息,但是表还在。
drop table 表名;表示删除整个表,表也不存在了。
到了这里,关于MySQL---表的增查改删(CRUD基础)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!