前言
本篇内容是MySQL的基础——增删改查系列知识,适合新手学习了解。
一、新增
insert into 表名(列名,列名) values (值,值...),(值,值...);
insert into Students(id,name,grade) values (01,张三,58),(02,李四,98);
二、查询
1.全列查询
select * from 表名;
select * from Students;
2.指定列查询
select 列名,列名...from 表名;
select id,name from Students;
3.带有表达式的查询
select 表达式 from 表名;
select id+4 from Students;
4.带有别名的查询
select 表达式 as 别名 from 表名;
select id+4 as ClassId from Students;
5.去重查询
select distinct 列名 from 表名;
select distinct grade from Students;
6.排序查询
select 列名 from 表名 order by 列名,列名...;
#按id升序(默认情况)排列grade;
select grade from Students order by id;
#按id降序(desc)排列grade;
select grade from Students order by id desc;
7.条件查询
select 列名 from 表名 where 条件;
(此处的条件包括关系运算和逻辑运算)
select name from Students where id=01;
select name from Students where grade<60;
select name from Students where Chinese>60 and English<80;
select name from Students where Chinese>60 or English<80;
(此处and 优先级高于 or,如果同时出现在条件处,先执行and部分)
# 范围查询:between...and...
insert id from Students where grade between 60 and 80;
(between...and...语句包含该界限值,即在此句中包含60和80。如若不要求包含界限值,可以选择使用 >, <, >=, <=)
#范围查询:in
select id from Students where grade in(58,59,98,99);
(与between...and...语句的区别在于:in语句查询的是离散的集合)
#模糊查询:like
#'%'不限制字符数,如下,可能会查询到张三,张三三,张三三三...;
select id from Students where name like '张%';
#'_'限制字符数,一个'_'代表匹配满足其他条件外的一个字符,如下,会查询到张三,张四...;
select id from Students where name like '张_';
#NULL的查询:is [not] NULL
#查询qq已知的同学姓名
select name,qq from Students where qq is not NULL;
#查询qq未知的同学姓名
select name,qq from Students where qq is NULL;
8.分页查询
#从0开始,筛选n条结果;
select 列名 from 表名 [where 条件] [order by ... ] limit n;
#从s开始,筛选n条结果;
select 列名 from 表名 [where 条件] [order by ... ] limit s , n;
#从s开始,筛选n条结果;
select 列名 from 表名 [where 条件] [order by ... ] limit n offset s;
#按id进行分页,每页3条记录,分别显示第1、2、3页
select id,name,grade from Students order by id limit 3 offset 0;
select id,name,grade from Students order by id limit 3 offset 3;
select id,name,grade from Students order by id limit 3 offset 6;
三、修改
update 表名 set 条件 [where...] [order by...] [limit...]
#将01号同学的成绩改为68;
update Students set grade = 68 where id = 01;
#将总成绩倒数前三的三位同学的数学成绩加30分;
update Students set math = math + 30 order by chinese + math + english limit 3;
四、删除
delete from 表名 [where...] [order by...] [limit...]
#删除01号同学的成绩;
delete from Students where id=01;
总结
以上就是本次分享的内容,本文仅仅简单介绍了MySQL的增删改查,主动的代码编写练习 可以使大家更好的理解这部分内容。文章若有错处,欢迎大佬们批评指正!