计算时间差;TIMESTAMPDIFF
–相差1天
select TIMESTAMPDIFF(DAY, ‘2018-03-20 23:59:00’, ‘2015-03-22 00:00:00’);
–相差49小时
select TIMESTAMPDIFF(HOUR, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
–相差2940分钟
select TIMESTAMPDIFF(MINUTE, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
修改字段格式
ALTER TABLE
app端-158
modify column开始答题时间
datetime,
MODIFY COLUMN结束答题时间
datetime
添加自增序列并设置主键到首列
– 添加自增序列并设置主键到首列
ALTER TABLEapp端-158
ADD id INT(10) NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id
);
修改表结构添加索引
ALTER table yz_company_main ADD INDEX indexName(gpdm)
join匹配练习
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
join匹配练习实例
SELECT
company,
gfq_business_name
FROM
fls_xuqiu_qy
LEFT JOIN dw_appliances_info ON四个大西瓜
.fls_xuqiu_qy.company = dw_appliances_info.gfq_business_name
WHERE
gfq_business_name IS NOT NULL
GROUP_CONCAT 使用练习实例
SELECT
business_name,
GROUP_CONCAT( gllb ORDER BY gllb DESC SEPARATOR ‘;’ ) AS CONCAT_mingxi
FROM
(
SELECT
a.business_name,
b.gllb
FROM
dw_appliances_info
AS a
LEFT JOINdw_appliances_detail
AS b ON a.business_code = b.business_code
WHERE
b.gllb IS NOT NULL
GROUP BY
a.business_name,
b.gllb
) AS a
GROUP BY
business_name;
SQL VIEW(视图)
在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。
注释:数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
SELECT
Customer,
SUM( OrderPrice )
FROM
Orders
GROUP BY
Customer
HAVING
SUM( OrderPrice )< 2000