mysql的select语句总结与索引使用
单表查询:
查询指定记录 select ....where
带IN的关键字查询 not in
带between and的范围查询
select column1,column2 from table1 where column between 3 and 14;
带like的查询 %匹配任意长度的字符
查询以b开头,并以y结尾的水果的名称
select f_name from fruit where f_name like 'b%y';
下划线‘_’,一次只能匹配任意一个字符。
查询空值: is null / is not null
select * from where c_email is null;
带and的多条件查询
select f_id , f_price from fruits where s_id = '101' and f_price >= 5;
带 or的多条件查询,or和in操作法实现的效果是一样的。但是in更简洁
查询结果不重复:
select distinct s_id from fruits;
排序:
order by id desc (降序) 默认ASC(升序)
如果order by后面有多个字段,则先对第一个排序,如果第一个比较相等,在对后面的排序;
分组查询:
group by column having <条件表达式>
这经常和聚合函数在一起使用 max() min() count() avg() sum()
select s_id ,count(*) as total from fruits group by s_id;
以上查询了id供应商提供的水果名称;
select s_Id , group_concat(f_name) as names from fruit group by s_id;
group_concat()将每个分组的名称显示
Having 过滤分组
select s_id , group_concat(f_name) as names from fruits group by s_id having count(f_name) > 1;
在group by 子句中使用with rollup
添加一行,显示字段的总和
也可多字段分组
select * from fruit group by s_id, f_name;
limit()限制查询结果
连接查询:
内连接: (inner join)
select suppliers.s_id , s_name , f_price from fruit , suppliers
where fruits.s_id = suppliers.s_d;
内连接查询语句:
select ...form fruits inner join suppliers on fruit.s_id = suppliers.s_id;
索引使用:
create index indexname on tablename (column1, column2);
添加主键索引: alter table tablename add primary key (column)
添加唯一索引: alter table tablename add unique (column)
添加普通索引: alter table tablename add index index_name (column);
添加全文索引: alter table tablename add fulltext (clomn);
添加多列索引: alter table tablename add index index_name (column1 , column2...)
删除索引 :drop index index_name on table_name;
查看索引使用情况: show status like "Handler_read%";
https://www.cnblogs.com/cxxjohnson/p/8625719.html