MySQL基础之数据管理【2】

傷城~ 2023-06-01 11:12 6阅读 0赞

### where条件筛选记录 ###

select id,username,age from uesr where id=5; alter table user add userDesc varchar(100); update user set userDesc="This is a test" where id<=5; select id,username,age,userDesc from user where userDesc<=>null; -- <=>检测null --is [not] null 检测null select id,username,age,userDesc from user where userDesc is null; --[not] between ... and 选定范围 select id,username,age,sex from user where age between 18 and 20; --[not] in(值...) 指定集合 select id,username,age,sex from user where id in(1,3,5,7,9); --and / or select id,username,age,sex from user where sex="男" and age>=20; select id,username,age,sex from user where salary between 20000 and 100000 and sex="男"; select id,username,age,sex from user where id=1 or username="queen"; --[not] like 匹配字符 select id,username,age,sex from user where username like "queen"; --模糊查询 通配符 %任意长度的字符串 _任意一个字符 select id,username,age,sex from user where username like '%三%'; --查询username中有三的用户 select id,username,age,sex from user where username like "___"; --三个_匹配三个字符 select id,username,age,sex from user where username like "张_%"; --查询以张开头的最少两个字符大小 --默认忽略大小写

### group by 对记录进行分组 ###

--把值相同放到一个组中，最终查询出的结果只会显示组中一条记录 select id,username,age,sex from user group by sex; --分组配合group_concat()查看组中某个字段的详细信息 select id,group_concat(username),age,sex from user group by sex; --显示 +----+----------------------------------------------+-----+-----+ | id | group_concat(username) | age | sex | +----+----------------------------------------------+-----+-----+ | 1 | king,张三,张子枫,刘德华,吴亦凡,张阿文,经过历 | 23 | 男 | | 2 | queen,imooc,子怡,王菲 | 27 | 女 | +----+----------------------------------------------+-----+-----+ --配合聚合函数使用 count() sum() max() min() avg() select count(*) as total_users from user; --得到总记录数，null也统计，别名total_users select count(userDesc) from user; --null不统计 --按照sex分组，得到用户名详情，并且分别统计组中的总人数 select group_concat(username) as usersDetail,sex,addr,count(*) as totalUsers from user group by sex; --显示 +----------------------------------------------+-----+------+------------+ | usersDetail | sex | addr | totalUsers | +----------------------------------------------+-----+------+------------+ | king,张三,张子枫,刘德华,吴亦凡,张阿文,经过历 | 男 | 上海 | 7 | | queen,imooc,子怡,王菲 | 女 | 上海 | 4 | +----------------------------------------------+-----+------+------------+ --按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值 select addr, group_concat(username) as usersDetail, count(*) as totalUsers, sum(age) as ageSum, min(age) as ageMin, avg(age) as ageAvg from user group by addr; --显示 +------+-------------------------------+------------+--------+--------+---------+ | addr | usersDetail | totalUsers | ageSum | ageMin | ageAvg | +------+-------------------------------+------------+--------+--------+---------+ | 上海 | king,queen,张三,张子枫,吴亦凡 | 5 | 161 | 23 | 32.2000 | | 北京 | imooc,子怡 | 2 | 56 | 25 | 28.0000 | | 南京 | 刘德华 | 1 | 14 | 14 | 14.0000 | | 广州 | 王菲 | 1 | 62 | 62 | 62.0000 | | 湖南 | 经过历 | 1 | 25 | 25 | 25.0000 | | 西安 | 张阿文 | 1 | 14 | 14 | 14.0000 | +------+-------------------------------+------------+--------+--------+---------+ --配合with rollup 会在记录末尾添加一条记录，是上面所有记录的总和 select group_concat(username) as usersDetail, count(*) as totalUsers from user group by sex with rollup; --按照字段位置来分组(addr) select addr, group_concat(username) as usersDetail, count(*) as totalUsers from user group by 1; --查询age>=30的用户并且按照sex分组 select age, group_concat(username) as usersDetail, count(*) as totalUsers from user where age>=30 group by sex; --having子句对分组结果进行二次筛选 select addr, group_concat(username) as usersDetail, count(*) as totalUsers from user group by addr having count(*)>=3; --having totalUsers>=3; 也可以通过别名的形式二次筛选 --having后面可以加通过聚合函数操作的列(如：sum(),max())或者select查询到的列 --having进行分组条件的指定时，一定要保证分组条件要么为聚合函数，要么条件中的字段必须出现在当前的select语句中

### order by 实现排序效果 ###

order by 字段名称 asc|desc --升序|降序 --测试 select id,username,age from user order by id desc; --order by age; --按照age升序 select id,username,age from user order by age asc,id asc; --按照多个字段排序，先age,相同部分id select id,username,age from user where age>=30 order by age desc; --有条件的字段排序 select rand(); --产生(0-1)随机数 --实现随机记录 select id,username,age from user order by rand(); --每次产生的结果都不同

### limit限制结果集的显示条数 ###

limit 值 --显示结果集的前几条记录 limit offset,row_count --从offset开始，显示row_count条记录 --测试 --方式一 select id,username,age,sex from user limit 5; --方式二 select id,username,age,sex from user limit 1,5; --offset从0开始，用这种方式实现分页 --显示 +----+----------+-----+-----+ | id | username | age | sex | +----+----------+-----+-----+ | 2 | queen | 27 | 女 | | 3 | imooc | 31 | 女 | | 4 | 张三 | 38 | 男 | | 5 | 张子枫 | 38 | 男 | | 6 | 子怡 | 25 | 女 | +----+----------+-----+-----+ --1.更新user表中的前三条记录，将age加5 update user set age=age+5 limit 3; --2.将user表中id字段降序排列，更新前三条记录，将age减10 update user set age=age-10 order by id desc limit 3; --3.删除user表中的前三条记录 delete from user limit 3; --4.删除user表中id字段降序排列的前三条记录 delete from user order by id desc limit 3; --update或者delete时limit只支持一个参数形式

### 单表查询完整select语句的形式 ###

select addr, group_concat(username) as usersDetail, count(*) as toTalUsers, sum(age) as sum_age, max(age) as max_age, min(age) as min_age, avg(age) as avg_age from user where id>=2 group by addr having sum_age>=25 order by sum_age limit 2;

转载于:https://www.cnblogs.com/huowuyan/p/11456139.html