MySQL表的增删改查(基础)
目录
1. CRUD
2. 新增 (Create)
3. 查询 (Retrieve)
3.1 全列查询
3.2 指定列查询
3.3 查询字段为表达式
3.4 别名
3.5 去重
3.6 排序
3.7 条件查询
3.7.1 基本查询
3.7.2 AND于OR
3.7.3 范围查询
3.7.3.1 BETWEEN...AND...
3.7.3.2 IN
3.7.4 模糊查询:LIKE
3.7.5 NULL的查询:IS [NOT] NULL
3.8 分页查询:LIMIT
4. 修改
5. 删除
6. 内容重点总结
1. CRUD
- 注释:在SQL中可以使用"--空格+描述"来表示注释说明
- CRUD即增加(Create),查询(Retrieve),更新(Update),删除(Delete)四个单词的首字母缩写
2. 新增 (Create)
语法:
insert into 表名 values(值,值.....);
//into是可以省略的,建议写上,
//values后面跟的是插入的内容
//values后面括号中的值要和列相匹配,(列的个数和类型)
这里的插入操作也是可以插入中文的:
指定列插入:
语法:
insert into 表名 (列名,列名...) values(值,值....);
//右边的值要和左边的列名相匹配
一次插入多行记录:
语法:
insert into 表名 values (值,值....),(值,值....)....;
//一个括号就相当于一行数据
datetime类型插入:
删除学生表,重新创建学生表,里面添加生日字段。
可以用一个固定格式的字符串,来表示时间日期。
年月日之前用-类分割,时分秒之间用:来分割,日期和时间之间用空格来分割,这个时候我们就构造出 一个具体的时间日期了。
当前时刻的插入:
SQL提供了一个现成的函数,now(),通过这个函数就直接可以获取当前的时间,不用去指定。
查询表中内容:
语法:
select * from 表名;
我们就可以看到第二行确实插入的是当前的时间。
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 -- 降序排序 如果省略不写,默认升序排序mysql是一个客户端服务器结构的程序。
把请求发给服务器之后,服务器进行查询数据,并且把查询到的结构进行排序之后,再组织成相应数据返回给客户端,排序仍然是针对临时数据来展开的,此处的排序不影响原有数据在mysql服务器上存储的顺序。
针对表达式进行排序
注意:null参与各种运算,结果还是null。
指定多个列进行排序
order by后面可以写多个列,使用","开分割开。
3.7 条件查询
会指定具体的条件,按照条件针对数据进行筛选,通过这个where关键字进行指定的。
select 列名 from 表名 where 条件;
//遍历这个表的每一行记录,把每一行的数据分别带入到条件中
//如果条件成立,这个记录就会被放入结果集合中,如果条件不成立
//这个记录就pass比较运算符:
逻辑运算符:
注:
- WHERE条件可以使用表达式,但不能使用别名
- AND的优先级高于OR,再同时使用时,需要使用小括号()包裹优先执行的部分
sql中没有 == ,是使用 = 表示比较相等
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;
注意:表中还有一条只有数学成绩的唐三藏,总成绩也是小于200,但这样的成绩没有被查出来。
因为98+NULL+NULL结果还是NULL,NULL小于200结果还是NULL,NULL就相当于是false,所有这样算下来,它就被列为条件不匹配。
这样的操作并不能正确的执行, 在where语句中的totle不认识。
这个时候我们就需要理解,select条件查询执行的顺序:
- 遍历表中的每个记录。
- 把当前记录的值,带入条件,根据条件进行筛选。
- 如果这个记录条件成立,就要保留,进行列上的表达式的计算。
- 如果有order by 会在所有的行都被获取到之后(表达式也算完了)再针对所有的结果进行排序。
定义别名是在第三步定义的,where是第二步执行的,执行where的时候,totle还处于"未定义"的状态。
算是理由,但是也不算是。
实现sql解析引擎的时候,是完全可以做到的,把这里的别名预先的定义好,然后再执行1 2 3,保证执行到where的时候,也能访问到别名,但是mysql当前没这样实现。
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里面就是比较相等。
将总成绩倒数前三的3位同学的数学成绩加上30分
为了方便观察,先把三个成绩都是null的同学已经成绩先删除。
我们首先查询总成绩倒是前三的同学都有哪些。
这里如果直接加30就会变成一百多,就比如98.0+30就是128.0,就不符合前面说的decimal(3,1)这种类型的要求,要求长度是3位,现在长度都成4位了,所有就不合适。
那么我们可以演示下:
这样就会报错,数据超出了范围。
那么我们这里就写成减30吧。
将所有同学的语文成绩更新为原来的2倍
update后面不写任何条件,就是针对所有行都进行修改。
报错的原因就是语文成绩*2会超出范围,而语句本身是没有错的。
那么除2的话这里面的数据就发生了修改,但是修改过程中我们看到了一些特别的地方。
5. 删除
delete from 表名 where 条件 /order by/limit;
//会把符合条件的行,从表中删除掉删除孙悟空同学的考试成绩
delete from exam_result where name = '孙悟空';
删除整张表数据
不指定任何条件就是删除整个表数据。
delete from exam_result;
那么此时查看表的时候是一条记录都查不到的。
删除整个表的记录和前面的drop table还不太一样,drop table是把表删了,同时也删除了表里的记录,而这里的delete操作是只删除了表里的记录,但是表还在,只不过变成了空表。所以我们可以看到前面可以正确的去查询。
如果把表删了再次查询的话会报这个表不存在。
6. 内容重点总结
-
新增
-- 单行插入
insert into 表(字段1, ..., 字段N) values (value1, ..., value N);-- 多行插入
insert into 表(字段1, ..., 字段N) values
(value1, ...),
(value2, ...),
(value3, ...);- 查询
-- 全列查询
select * from 表-- 指定列查询
select 字段1,字段2... from 表-- 查询表达式字段
select 字段1+100,字段2+字段3 from 表-- 别名
select 字段1 别名1, 字段2 别名2 from 表-- 去重DISTINCT
select distinct 字段 from 表-- 排序ORDER BYselect * 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 条件