MySql超级好用教程

1、select查询语句

使用select语句查询数据，其通用语法：

Select columnName1,columnName2,... #columnName所需查询列名 使用星号（*）来代替所有字段
From  tableName #从数据表tableName查询数据
[WHERE Clause] #[]是可选项 where包含任何条件
[LIMIT N][ OFFSET M] #limit设定返回的记录数 offset开始查询的数据偏移量

实例所示：

select多表关联查询：

#左外连接查询
#左表tableName1 右表tableName2  左表重命名a 右表重命名b on包含两个表关联条件
select a.column1,b.column2 from tableName1 a left join tableName2 b on a.id = b.id;
#右外连接查询
select a.column1,b.column2 from tableName1 a right join tableName2 b on a.id = b.id;
#内连接查询
select a.column1,b.column2 from tableName1 a inner join tableName2 b on a.id = b.id;
#全连接查询
select a.column1,b.column2 from tableName1 a full join tableName2 b on a.id = b.id;
#左连接查询
select a.column1,b.column2 from tableName1 a left join tableName2 b on a.id = b.id where b.id is null;
#右连接查询
select a.column1,b.column2 from tableName1 a right join tableName2 b on a.id = b.id where a.id is null;
#两表独有数据集查询
select a.column1,b.column2 from tableName1 a full join tableName2 b on a.id = b.id where a.id is null or b.id is null;

如下图所示：

select子查询：

#查询tableNmae2的id值作为tableName1 的id条件值
select column1,column2 from tableName1 where id in (
select id from tableNmae2 where id between  3  and  10
)

select语句查询执行顺序：

select a.column1,a.column2... 
from tableName1 a left join tableName2 b on a.id = b.id 
where a.column3 = 'hello' 
group by a.column1,a.column2... 
having sum(a.column3) >1000
order by a.column1 desc
limit 10 offset 2
#sql语句查询执行顺序如下：
#1、执行from tableName1 
#2、执行on a.id = b.id 得到它们的交集
#3、执行left join tableName2
#4、执行where a.column3 = 'hello'判断条件
#5、执行group by a.column1,a.column2...进行分组 
#6、执行having sum(a.column3) >1000对结果再次进行筛选
#7、执行select a.column1,a.column2... 
#8、执行order by a.column1 desc进行降序排序
#9、limit 10 offset 2

删除重复行数据

DELETE a FROM wangdiantong_ordersale_detail a  
INNER JOIN (  
    SELECT stockout_id,goods_count,goods_num,goods_name,spec_name,paid,modified as min_id  
    FROM wangdiantong_ordersale_detail  
    GROUP BY stockout_id,goods_count,goods_num,goods_name,spec_name,paid,modified
    HAVING COUNT(*) > 1  
) b ON a.stockout_id = b.stockout_id and a.goods_count = b.goods_count and a.goods_num = b.goods_num and a.goods_name = b.goods_name


WHERE t1.id <> t2.min_id;

CTE临时结果集

WITH temps as (
	SELECT attend_name FROM attend_info limit 1
)
SELECT temps.attend_name from temps

with recursive temp as(
	SELECT 1 as n
	UNION ALL
	SELECT n + 1 FROM temp WHERE n <6
)
SELECT *FROM temp

关注我，不断为您更新精彩内容，敬请期待…