SQL基础教程

查询基础

基础语法
select  
		列名1,
		列名2
from 
		表名
-- 查询多例用逗号分隔 
-- 查询结果的列的顺序和select 子句中的顺序相同 

-- 查询表中所有的列  
select 
			*
from 
			表名
-- 使用 * 无法设定列的显示顺序, 会按照 create table 语句的定义对列进行排序
  

起别名 AS

一般用来简化书写, 可以给列, 表, 起别名 关键字 as , as 可以省略
当别名为中文时 注意用 双引号("")

常数的查询

select 子句中可以 写常数信息, 他将与查询结果一起被查询出来 

查询删除重复记录

我们发现在订单表中, 好多商品的类别重复, 所以我们在select 子句中使用 distinct 但是要注意distinct 只能用在第一列名之前 , 而且在使用distinct 时, null 数据也会被当成一行
如下图:

使用Where选择查询的记录

select 子句需要通过where子句查询特定条件的记录,

select 列名1, ...
from 表名
where 条件表达式

查询结果0,1 显示是, 否

SELECT
	id,
	case_name AS caseName,
IF
	( CAST( show_home AS CHAR ) = '1', '是', '否' ) AS showHome,
	img_url AS imgUrl,
	case_detail AS caseDetail,
	create_time AS createTime,
	modify_time AS modifyTime,
	create_user_id AS createUserId,
	modify_user_id AS modifyUserId,
	sort 
FROM
	portal_case_info 
WHERE
	1 = 1

行列转换

说明

SELECT
	tmm.id,
	tmm.project_id AS projectId,
	tmm.company_id AS companyId,
	GROUP_CONCAT( mm.id SEPARATOR ',' ) AS maintainId,
	GROUP_CONCAT( mm.maintain_name SEPARATOR ',' ) AS maintainName,
	tpi.project_name AS projectName,
	tfc.company_name AS companyName,
	tmm.maintain_remark AS maintainRemark,
	tmm.maintain_people AS maintainPeople,
	tmm.equipment_id AS equipmentId,
	tei.equipment_name AS equipmentName,
	DATE_FORMAT( tmm.maintain_time, '%Y-%m-%d %H:%i:%s' ) AS maintainTime 
FROM
	t_maintain_info AS tmm
	LEFT JOIN t_formal_company_info AS tfc ON tmm.company_id = tfc.id
	LEFT JOIN t_project_info AS tpi ON tmm.project_id = tpi.id
	LEFT JOIN t_maintain AS mm ON FIND_IN_SET( mm.id, tmm.maintain_id )
	LEFT JOIN t_equipment_info AS tei ON tmm.equipment_id = tei.id 
GROUP BY
	tmm.id

筛选过滤条件

不要空的结果

        and trail.lon is not null
        and trail.lon != ''

距离当前时间2分钟设置离线

SELECT
IF
	( CURRENT_TIMESTAMP < DATE_ADD( ths.last_update_time, INTERVAL '2' MINUTE ), "在线", "离线" ) AS STATUS 
FROM
	t_device device
	LEFT JOIN t_helmet_status ths ON device.device_num = ths.terminal_num 
WHERE
	device.device_num = '7758521';