mysql增删该查语句讲解及示例

基本查询语法:

select * from 表名;
例：
select * from classes;

查询指定列:

注:可以使用as为列或表指定别名

select 列1,列2,... from 表名;
例：
select id,name from classes;

给字段(列)起别名
select id as 序号, name as 名字, gender as 性别 from students;

给表起别名
select s.id,s.name,s.gender from students as s;

查询的时候去重

select distinct 列1,... from 表名;   # 会将重复的数据删除 不返回
例：
select distinct gender from students;

全列插入和部分列插入:

全列单行插入:主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准
insert into 表名 values(...)
例：
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');
全列多行插入
insert into 表名 values(...),(...)...;
例：
insert into classes values(0,'python1'),(0,'python2');


部分列插入
insert into 表名(列1,...) values(值1,...)
例：
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');
部分列多行插入
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('杨康'),('杨过'),('小龙女');

修改

update 表名 set 列1=值1,列2=值2... where 条件
例：
update students set gender=0,hometown='北京' where id=5;

删除

物理删除:
delete from 表名 where 条件
例：
delete from students where id=5;

逻辑删除:  添加一个说明字段表示是否删除
update students set isdelete=1 where id=1;

mysql条件where

语法:

select * from 表名 where 条件;
例：
select * from students where id=1;

where 后面支持的运算符:

比较运算符 = > < != >= <=

select * from students where id > 3;

逻辑运算符 and or not

select * from students where id > 3 and gender=0;
select * from students where id not in (1,3,4,5);

模糊查询 like , %表示任意多个任意字符, _表示一个任意字符

查询姓黄或叫靖的学生
select * from students where name like '黄%' or name like '%靖';

查询姓黄并且“名”是一个字的学生
select * from students where name like '黄_';

范围查询 in | between start and end

in:不连续的范围
between :连续的范围
查询编号是1或3或8的学生
select * from students where id in(1,3,8);
查询编号为3至8的学生
select * from students where id between 3 and 8;

空判断,空判断要用is null , is not null

查询没有填写身高的学生
select * from students where height is null;
查询填写了身高的学生
select * from students where height is not null;

where运算的优先级

小括号>not>比较运算符>逻辑运算符
逻辑运算符中and>or ,同时出现and 和 or ,and先运算,想改变规则就使用小括号

mysql 排序 order

语法:

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认升序排序 asc
asc:升序
desc:降序

显示所有的学生信息，先按照年龄从大-->小排序，当年龄相同时 按照身高从高-->矮排序
select * from students  order by age desc,height desc;

mysql 聚合函数

计算总数 count

查询学生总数
select count(*) from students;

最大值max

查询女生的编号最大值
select max(id) from students where gender=2;

最小值min

查询未删除的学生最小编号
select min(id) from students where is_delete=0;

求和sum

查询男生的总年龄
select sum(age) from students where gender=1;

查询男生的平均年龄
select sum(age)/count(id) from students where gender=1;

平均值avg

查询未删除女生的年龄平均值
elect avg(age) from students where is_delete=0 and gender=2;

mysql分组

group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by可用于单个字段分组，也可用于多个字段分组
注意使用过分组之后,查询的字段必须是分组的字段,写其他字段会错误,不过可以写聚合函数动态添加字段
注:如果你想要查看一个分组下所有数据的非分组字段总数据,可以使用group_concat

select gender from students group by gender;
select gender,count(id) as num from students group by gender;
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+

使用group_concat

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+

select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

为分组加条件having
having 条件表达式：用来分组查询后指定一些条件来输出查询结果
having作用和where一样，但having只能用于group by
where是分组之前的筛选,having是分组之后的筛选

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

分组自动计算总和with rollup
with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+


select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

mysql 分页之limit

语法:

select * from 表名 limit [start,]count
从start开始，获取count条数据
默认start 为0,即不跳过

查询前3行男生信息
select * from students where gender=1 limit 3;

跳过5个男生查取3个男生信息
select * from students where gender=1 limit 3;

mysql连接查询

mysql支持三种连接查询:
内连接:inner join 查询的结果为两个表匹配到的数据
左连接:left join 查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充
右连接:right join查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
语法:

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

示例:

使用内连接查询班级表与学生表
select * from students inner join classes on students.cls_id = classes.id;
使用左连接查询班级表与学生表 并使用as
select * from students as s left join classes as c on s.cls_id = c.id;
查询学生姓名及班级名称
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
使用右连接查询班级表与学生表
select * from students as s right join classes as c on s.cls_id = c.id;

使用join的自关联,不使用外键

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int  # 父省编号
);

查询一共有多少个省
select count(*) from areas where pid is null;
查询省的名称为“山西省”的所有城市
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

查询市的名称为“广州市”的所有区县
select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';

子查询

标量子查询
select * from students where age > (select avg(age) from students);  #查询年龄大于班级平均年龄的学生信息

列级子查询
select name from classes where id in (select cls_id from students group by cls_id);  #查询在students表中出现的班级名

行级子查询
 select name from students where height = (select max(height) from students); # 查询身高最高的学生姓名

查询

-- 普通查询
select * from students;
select id,name from students \G
-- 分页查询
select TOP 10 * from students \G -- 取前10个数据,不建议使用,建议使用limit
select * from students limit 10 \G
select * from students limit 5,10 \G -- 跳过5个匹配的数据,然后取10个数据
-- 起别名
select id,name as n from students limit 10;
-- 条件查询
select * from students where age = 23 limit 10;
-- 范围查询
select * from students where id in (1,3,4,5);
select * from students where id in (1,3,4) and status = 0 \G
-- 模糊查询
select * from students where name like "小_" \G  -- _匹配单个任意的字符
select * from students where name like "小%" \G  -- * 匹配任意个任意字符
select * from students where name like "小[东红金]" -- 匹配小东或小红或小金
select * from students where age like "2[^56]" -- 匹配不是25 或 26 岁的所有人
select * from students where instr(name,"小")>0 -- 注意 instr方法是在字符串中搜寻
-- 当前语法是在name中搜寻存在小字符的数据, instr的下表索引是以1 开始的,
-- 即找到就返回字符所在的下标,找不到返回0,不存在返回负数情况,所以这里>0相当于like
-- 不过不能向like那样控制匹配的位数

-- 排序查询
select * from students order by id desc limit 1 ; -- 降序
select * from students order by id asc limit 1; -- 升序

-- 分组聚合查询
select name,count(*) as num from students group by name \G
select name,SUM(grade) as total_num from grade where name = "xiaodong"  group by name;
select name,SUM(grade) from grade where id > 20 group by name having SUM(grade)>300;
--注意 where是过滤行,having是过滤组,即where过滤后的结果交给后面group分组,然后having过滤分组后结果
# group 还可以同时分多个组
select name,class,SUM(grade) from grade group by name,class;
-- 注意分组之后,select 查询的字段不能包含分组字段之外的字段
#如果同时分多个组,select就会根据每个分组生成一条数据,可以理解为这里进行了2次分组查询,把查询的结果合并了
-- 返回xiaodong的总成绩
select name,SUM(grade) as num from grade group by name \G --返回所有人的总成绩
select name,AVG(grade) as avg_num from grade where name = "xiaodong" group by name; --求平均值
select id,name,MAX(grade) from grade group by name \G -- id和name都是最大值对应的那行的id和name
select id,name,MIN(grade) from grade group by name \G -- 同理最大值
select a.name as name,b.age as age from A as a inner join B as b on a.name=b.name;
-- 内联查询,  注意不加on 会触发笛卡尔乘积 ,加on 之后只会连接匹配的项,也就是说会筛选,为了性能必须要用
-- left join 和 right join 必须要加on,不加on 出错
-- left join 以左表为准,左表中不存在的不会和右表去连接,就算右表存在也会被舍弃
select a.name ,b.id from A left join B on A.id = B.id;

插入

insert into students value(1,"haha",23); -- 全部字段插入
insert into students(name,age) value("heihei",22) -- 指定字段插入
insert into students(name) value("haha"),("heihei"),("hehe") -- 插入多行
insert into students(name) values("haha"),("heihei"),("hehe") --插入多行
-- 注意 value 在插入多行时速度较快, values在插入单行时较快,不过sqlserver只认values
-- 所以尽量使用values
insert into students set name = 'haha', age = 23 ; --插入的另外一种方式
insert into students(name) select name from students1 ; -- 从另外一个表查询

更改

-- 单表更新
update students set name = "xiaodong" where id = 1;
update students set name = REPLACE(name,"dong","yu") where id = 1 ; 
--将id=1行的name字段的dong字符改为yu 即 xiaodong 改为 xiaoyu  部分更新操作
update students set name = "xiaodong" ; -- 将全部更新为xiaodong
update students set name = "xiaodong" limit 1 ;  -- 更新匹配到的第一条结果
update students set name = "xiaodong" order by id desc limit 1;

-- 多表更新
update student1,student2 set student1.id = student2.oid where student1.name = student2.name;
update student1 set student1.age = student2.age + 1 from student1,student2 where student1.id = student2.id
update student1 set student1.age = B.age from student2 as B where student1.id = B.id;

-- ignore 尽量不要使用
update students set id = 1 where id = 2 ; --会报错
update ignore students set id = 1 where id =2 ; -- 不会报错,但是有一个警告

-- 表达式
update students set age = age + 1 where age =23; -- 设置完后 age 会变为 24

删除

delete 在不加where的时候,是删除所有的匹配行,如果想清空整个表,使用 TRUNCATE table_name
注意MyISAM引擎和InnoDB引擎,在删除行数据后,AUTO_INCREMENT字段不会被重新使用,比如id字段,数据删除后
这个id并不会重新被使用,但有一种表除外BDB引擎

-- 单表删除
delete from students where id = 2;
delete from students limit 1;--删除匹配的第一条数据,在数据库更新和删除的时候最好都写limit防止误删
delete from students order by id desc limit 1 ;

-- 多表删除
delete from somelog where user='jcole' order by timestamp_column limit 1;
# 表名放在from前面不用使用using ,放在from 后面就需要使用using了
delete t1,t2 from t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;
delete from t1,t2 using t1 inner join t2 inner join t3 where t1.id=t2.id and t2.id=t3.id;
# 使用别名
delete a1,a2 from t1 as a1 inner join t2 as a2 where a1.id=a2.id;
delete from a1,a2 using t1 as a1 inner join t2 as a2 where a1.id=a2.id;

维护

对于mysql来说删除数据之后,并不会删除索引,所以导致id在删除数据后依然不能自动使用,可以手动使用.
解决删除索引的方法 optimize table table_name重新编排一下索引
对于存在经常增删的数据库可以定期清理一下索引

最后编辑于：2021.12.30 16:41:14

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,717评论 6赞 496
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,501评论 3赞 389
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 160,311评论 0赞 350
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,417评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,500评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,538评论 1赞 293
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,557评论 3赞 414
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,310评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,759评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,065评论 2赞 330
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,233评论 1赞 343
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,909评论 5赞 338
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,548评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,172评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,420评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,103评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,098评论 2赞 352