SQL基础教程

爱被打了一巴掌 2022-01-17 00:13 334阅读 0赞

# 查询基础 #

基础语法
    select  
    		列名1,
    		列名2
    from 
    		表名
    -- 查询多例用逗号分隔 
    -- 查询结果的列的顺序和select 子句中的顺序相同 
    
    -- 查询表中所有的列  
    select 
    			*
    from 
    			表名
    -- 使用 * 无法设定列的显示顺序, 会按照 create table 语句的定义对列进行排序

## 起别名 AS ##

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

## 常数的查询 ##

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70]

## 查询删除重复记录 ##

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

## 使用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';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70]: /images/20220117/bf2eca8b65cb44b4be0c9c605ae15e94.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70 1]: /images/20220117/2306cb1430c54608a24275a4208902dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MjQxMDAz_size_16_color_FFFFFF_t_70 2]: /images/20220117/49586a7866be4dd2a076309b429120d9.png