MySQL表基础的增删改查操作
目录
1. CRUD
-
注释:在SQL中可以使用"--空格+描述"来表示注释说明
-
CRUD即增加(Create),查询(Retrieve),更新(Update),删除(Delete)四个单词的首字母缩写
2. 新增 (Create)
2.1 insert插入
语法:
insert into 表名 values(值,值.....);
//into是可以省略的,建议写上,
//values后面跟的是插入的内容
//values后面括号中的值要和列相匹配,(列的个数和类型)
这里的插入操作也是可以插入中文的:
指定列插入:
语法:
insert into 表名 (列名,列名...) values(值,值....);
//右边的值要和左边的列名相匹配
一次插入多行记录:
语法:
insert into 表名 values (值,值....),(值,值....)....;
//一个括号就相当于一行数据
2.2 datetime类型插入
删除学生表,重新创建学生表,里面添加生日字段。
可以用一个固定格式的字符串,来表示时间日期。
年月日之前用-类分割,时分秒之间用:来分割,日期和时间之间用空格来分割,这个时候我们就构造出 一个具体的时间日期了。
当前时刻的插入:
SQL提供了一个现成的函数,now(),通过这个函数就直接可以获取当前的时间,不用去指定。
查询表中内容:
语法:
select * from 表名;
我们就可以看到第二行确实插入的是当前的时间。
2.3 插入否则更新
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败
-- 主键冲突
insert into students (id, sn, name) values (100, 10010, '唐大师');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
-- 唯一键冲突
insert into students (sn, name) values (20001, '曹阿瞒');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'可以选择性的进行同步更新操作语法：
insert into students (id, sn, name) values (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
-
若插入时触发主键冲突（如
id=100已存在）或唯一键冲突（如sn列有唯一约束且10010已存在），则不执行插入，而是更新该冲突行的sn和name字段为指定值（这里更新为sn=10010，name='唐大师'）。
2.4 替换
主键 或者 唯一键 没有冲突，则直接插入
主键 或者 唯一键 如果冲突，则删除后再插入
retrieve into students (sn, name) values (20001, '曹阿瞒');
3. 查询 (Retrieve)
sql中的增删改都是非常简单的,但是这个查询能玩出各种花样了,其实我们学习SQL,主要就是学习查询怎么写。
首先我们得创建表,并且插入数据,才能一系列的查询操作。
案例:
-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);
//chinese DECIMAL(3,1),括号中的3,1表示总长度是3,小数点后1位,比如95.5 80.0这些都是可以的,100.0
//100 1.25这些都是不可以的
3.1 全列查询
语法:
select * from 表名;
//把表中所有行和所有列都查询出来
//*表示"通配符"可以代指所有的列
这里是查询出来之后,服务器通过网络把这些数据返回给客户端的,并且在客户端以表格的形式,打印出来。
3.2 指定列查询
一个表的列数,可能是非常多的,某个场景下的操作,只需要关注其中的几个列就可以了。
语法:
select 列名,列名...from 表名;
3.3 查询字段为表达式
一边查询,一边进行计算,在查询的时候,写作由列名构成的表达式,把这一列中的所有行都带入到表达式中,参与运算。
比如像查询所有同学语文成绩都-10分的效果
计算每个同学的总成绩 chinese + math + english
3.4 别名
查询的时候给列/表达式/表指定别名
select 表达式 as 别名 from 表名;
//as可以省略,但是不建议省略,可读性不强
3.5 去重
使用distinct关键字最某列数据进行去重。
distinct修饰多列
3.6 排序
把行进行排序
明确排序规则
1. 针对哪个列作为比较规则
2. 排序的时候是升序还是降序
select 列名 from 表名 order by 列名 asc/desc;
//order by 列名 -- 指定某个列进行排序
//asc -- 升序排序 desc -- 降序排序 如果省略不写,默认升序排序
针对表达式进行排序
注意:null参与各种运算,结果还是null。
指定多个列进行排序
3.7 条件查询
会指定具体的条件,按照条件针对数据进行筛选,通过这个where关键字进行指定的。
select 列名 from 表名 where 条件;
//遍历这个表的每一行记录,把每一行的数据分别带入到条件中
//如果条件成立,这个记录就会被放入结果集合中,如果条件不成立
//这个记录就pass比较运算符:
逻辑运算符:
注:
where条件可以使用表达式,但不能使用别名
and的优先级高于or,再同时使用时,需要使用小括号()包裹优先执行的部分
ql中没有 == ,是使用 = 表示比较相等
3.7.1 基本查询
查询英语不及格的同学的英语成绩(<60)
SELECT name, english FROM exam_result WHERE english < 60;查询语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result WHERE chinese > english;
查询总分在200分以下的同学
SELECT name, chinese + math + english 总分 FROM exam_result
WHERE chinese + math + english < 200;
select条件查询执行的顺序:
遍历表中的每个记录。
把当前记录的值,带入条件,根据条件进行筛选。
如果这个记录条件成立,就要保留,进行列上的表达式的计算。
如果有order by 会在所有的行都被获取到之后(表达式也算完了)再针对所有的结果进行排序。
定义别名是在第三步定义的,where是第二步执行的,执行where的时候,totle还处于"未定义"的状态。
3.7.2 AND于OR
查询语文成绩大于80分,且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
查询语文成员大于80,或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
观察AND和OR的优先级
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;sql中and的运算符优先解决更高,不建议记这个优先级,遇到不知道的可加括号。
3.7.3 范围查询
3.7.3.1 BETWEEN...AND...
查询语文成绩再[80,90]分的同学的语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
这里的查询结果会包含边界的。
这里的查询使用ADN也可以实现。
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese
<= 90;
3.7.3.2 IN
使用in来表示一个"离散"的集合
查询数学成绩是58或者59或者98或者99分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
这样看可能不是很明显,所有我们可以修改一下查询的数字。
3.7.4 模糊查询:LIKE
通配符,就是一些特殊的字符,能够表示特定的含义。
%:代指任意个任意字符
_:代指一个任意字符
查询姓孙的同学的成绩
SELECT name FROM exam_result WHERE name LIKE '孙%';
//当前谁的名字是以孙开头的,只要是以孙开头,后面几个字都无所谓
%也是可以匹配0个字符,也就是说只有一个孙也是可以的。
此时我们孙这条记录就可以被查询到。
_ 匹配严格的一个任意字符
例子:
3.7.5 NULL的查询:IS [NOT] NULL
3.8 分页查询:LIMIT
使用select * 这种方式查询,是比较危险的,需要保证一次查询,不要查出来的东西太多。
所以limit可以限制这次查询最多能查出来多少个结果。
有的时候,数据非常多,一次全都显示出来,会影响到效率,也会不方便用户去看,所以limit就可以对此做出限制。
一般来说limit子句写在整个子句之后,如果搭配条件查询的话。
查询前三条记录:
select * from exam_result limit 3;
查询前三条后面的三条:
依次类推:
我们可以通过这样的方式依次往下找到所有的记录:
这里的limit表示这次查询查几条记录,offset3是偏移量,其实也就是一个下标, 从0开始的,就表示当前是第几天记录。
所有查询前三条记录也可以写成offset0。
offset：偏移量,是计算机中广泛使用的概念。
4. 修改
语法:
update 表名 set 列名 = 值 where 条件;
表中的内容:
将孙悟空同学的数学成绩变更为80分
update exam_result set math = 80 where name = '孙悟空';
//where会筛选出若干行记录,如果筛选出一行,那就是只改这一行,
//如果筛选出多行,那就把这多行都会进行修改
当修改完成之后再次查询里面的数据,发现孙悟空的数学成绩确实被修改为80了。
将曹孟德同学的数学成绩变更为60分,语文成绩变更为70分
update exam_result set math = 60, chinese = 70 where name = '曹孟德';使用update,可以依次修改多个列,这里的等号就是赋值了,where里面就是比较相等。
5. 删除
delete from 表名 where 条件 /order by/limit;
//会把符合条件的行,从表中删除掉删除孙悟空同学的考试成绩
delete from exam_result where name = '孙悟空';
删除整张表数据
不指定任何条件就是删除整个表数据。
delete from exam_result;
那么此时查看表的时候是一条记录都查不到的。
删除整个表的记录和前面的drop table还不太一样,drop table是把表删了,同时也删除了表里的记录,而这里的delete操作是只删除了表里的记录,但是表还在,只不过变成了空表。所以我们可以看到前面可以正确的去查询。
如果把表删了再次查询的话会报这个表不存在。
6. 聚合函数
| 函数 | 说明 |
| count() | 返回查询到的数据的 数量 |
| sum() | 返回查询到的数据的 总和，不是数字没有意义 |
| avg() | 返回查询到的数据的 平均值，不是数字没有意义 |
| max() | 返回查询到的数据的 最大值，不是数字没有意义 |
| min() | 返回查询到的数据的 最小值，不是数字没有意义 |
统计班级共有多少同学
select count(*) from students;
统计有数学成绩的个树
select count(math) from students;
统计的是去重成绩数量
select count(distinct math) from students;
统计数学成绩总分
select sum(math) from students;
统计平均分
select avg(chinese + math + english) from students;
返回数学最高分
select max(math) from students;
返回>70以上的数学最低分
select min(math) from students where math>70;7. 分组
group by 从字面意义上理解就是根据“by”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
分组前的数据：
分组后的数据：
分组后的数据与分组前的数据相比，分组后的数据是先以sage开始从小到大排序的，然后在以sname的中文首字母从a到z开始排序的，最后在以ssex的中文首字母从a到z开始排序的。
2、GROUP BY子句和聚合函数
grouo by 子句通常与聚合函数一起用于统计数据。聚合函数对组执行计算并返回每个组的唯一值。 例如， count() 函数返回每个组中的行数。
其他常用的聚合函数是： sum() ， avg() ， min() ， max() 。 group by 子句将行排列成组，聚合函数返回每个组的统计量(总数量，最小值，最大值，平均值，总和等)。
带有count()函数示例的group by子句
group by句带有min和max函数示例
带有avg()函数示例的group by子句
带有sum函数示例的group by子句
having 子句通常与[group by]子句一起使用，以根据指定的条件列表过滤分组。 以下是 having 子句的语法：
在此语法中， group by子句将行汇总为分组， having 子句将一个或多个条件应用于这些每个分组。
只有使条件评估为 true 的组才会包含在结果中。 换句话说，过滤掉条件评估为 false 或 unknown 的组。
因为SQL Server在 group by 子句之后处理 having 子句，所以不能通过使用列别名来引用选择列表中指定的聚合函数。
8. 内容重点总结
-
新增
-- 单行插入
insert into 表(字段1, ..., 字段N) values (value1, ..., value N);
-- 多行插入
insert into 表(字段1, ..., 字段N) values
(value1, ...),
(value2, ...),
(value3, ...);
-- 插入并更新
insert into students (id, sn, name) values (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
-- 冲突则替换
retrieve into students (sn, name) values (20001, '曹阿瞒');- 查询
-- 全列查询
select * from 表
-- 指定列查询
select 字段1,字段2... from 表
-- 查询表达式字段
select 字段1+100,字段2+字段3 from 表
-- 别名
select 字段1 别名1, 字段2 别名2 from 表
-- 去重DISTINCT
select distinct 字段 from 表
-- 排序ORDER BY
select * from 表 order by 排序字段
-- 条件查询WHERE：
-- (1)比较运算符 (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR
(8)NOT
select * from 表 where 条件- 修改
update 表 set 字段1=value1, 字段2=value2... where 条件- 删除
delete from 表 where 条件
扫一扫
601
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
