时间:2022-12-06来源:www.pcxitongcheng.com作者:电脑系统城
概念:DQL(data query language)数据查询语言 select操作
排序规则:
- select 表达式1|字段,.... - from 表名 where 条件 - group by 列名 - having 条件 - order by 列名 asc|desc - limit 位置,数量
语法结构:
SELECT [ALL | DISTINCT] ALL表示查询出所有的内容 DISTINCT 去重
{* | 表名.* | 表名.字段名[ AS 别名][,...]} 指定查询出的字段的
FROM
表名[AS 别名][,表1... AS 别名]
[INNER | [LEFT | RIGHT] [OUTER] JOIN 另一张表名 [AS 别名] ON 关联条件]
[WHERE 条件]
[GROUP BY 分组字段[,...]]
[HAVING 给分组后的数据进行条件筛选]
[ORDER BY 排序字段[,...]]
[LIMIT [startIndex,]pageSize]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
create database if not exists test; use test; create table if not exists data( id tinyint primary key auto_increment, price double NOT null , name varchar (20) not null , type varchar (20) not null ) ; insert into data values ( null ,900, '洗衣机' , 'b' ), ( null ,1900, '冰箱' , 'b' ), ( null ,2900, '空调' , 'b' ), ( null ,3900, '电视' , 'b' ), ( null ,150, '衣服' , 'c' ), ( null ,180, '裤子' , 'c' ), ( null ,200, '鞋子' , 'c' ), ( null ,188, '洗面奶' , 'a' ), ( null ,188, '洗发水' , 'a' ), ( null ,199, '洗衣液' , 'a' ), ( null ,88, '沐浴露' , 'a' ), ( null ,5, '泡面' , 'd' ), ( null ,15, '饼干' , 'd' ), ( null ,30, '咖啡' , 'd' ); |
1 2 3 4 5 6 7 |
select * from data; select name ,price from data; select * from data as d; select * from data d; select d. name ,d.price from data d; select distinct price from data; select name ,price +100 newprice from data; |
算术运算符
1 | select name ,price *1.5 newprice from data; |
1 2 3 4 5 6 7 8 9 10 11 |
select * from data where name = '洗衣机' ; select * from data where !(price>100); select * from data where price between 200 and 1000; select * from data where price in (188,900); -- 等于下面两句 select * from data where price = 188 or price =900; select * from data where price = 188 || price =900; select * from data where name like '%衣%' ; select * from data where name like '衣%' ; select * from data where name like '_衣%' ; select * from data where id is null ; |
注释:当有NULL作为比较大小的对象时,最大值和最小值均为null
1 2 3 4 |
select * from data order by price; select * from data order by price desc ; select distinct price from data order by price desc ; select * from data order by price,id; |
1 2 3 4 5 6 7 8 9 10 |
select count (*) from data; -- 不全为空的行数 select count (id) from data; -- 通过主键值查询行数 select count (*) from data where price<200; select sum (price) from data where type= 'A' ; select max (id) from data; select min (price) from data; select max (price) max_price, min (price) min_price from data; select avg (price) from data where type= 'c' ; |
1 2 |
select sum (price) from data group by type; select type, count (id) from data group by type; |
条件筛选
1 | select type, count (id) count from data group by type having count =4 order by type; |
分页显示
1 2 3 |
select * from data limit 5; -- 从第四条开始依次向后显示五条 select * from data limit 3,5; |
1 2 3 4 5 6 7 8 9 10 11 |
create table data2( name varchar (10), price double ); insert into data2 select name ,price from data; select * from data2; create table data3( type varchar (10), num int ); insert into data3 select type, count (*) from data group by type order by count (*); select * from data3; |
到此这篇关于Mysql的DQL查询操作全面分析讲解的文章就介绍到这了
2022-12-06
Mysql多表操作方法讲解教程2022-12-06
MySQL索引与事务定义到使用详解2022-12-06
MySQL数据库的约束使用实例我们做数据分析的时候经常会遇到去重问题,下面总结 sql 去重的几种方式,后续如果还有再补充,大数据分析层面包括 hive、clickhouse 也可参考。...
2022-12-06
很多使用电脑的小伙伴安装MYSQL端口被占用的情况,这个时候我们该如何解决呢?今天小编带来了详细的解决方法,主要在命令窗口里进行设置就可以了,具体的教程一起来看看吧。...
2022-10-24