单条SQL语句记录

测试数据库

查询语句书写顺序：select – from- where- group by- having- order by-limit 
查询语句执行顺序：from - where -group by - having - select - order by-limit

CREATE TABLE `test_car` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `car_letter` varchar(255) DEFAULT NULL COMMENT '首字母',
  `car_name` varchar(255) DEFAULT NULL COMMENT '车型名称',
  `car_money` double(10,2) DEFAULT NULL COMMENT '车型价格',
  `bonus` double(10,2) DEFAULT NULL COMMENT '销售奖励',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

#查询所有
SELECT * FROM `test_car`;

#all：默认，保留所有的查询结果；
SELECT ALL * FROM test_car;

#distinct：去重，将查出来的结果中所有字段都相同的记录去除。
SELECT DISTINCT * FROM test_car;

数据统计 参考"https://blog.csdn.net/u012099869/article/details/52861479"
SELECT COUNT(1) FROM `test_car`;

集合中
SELECT * FROM test_car WHERE id IN(1,2)

排除集合中
SELECT * FROM test_car WHERE id NOT IN(1,2)

在范围内
SELECT * FROM test_car WHERE car_money BETWEEN 0 AND 100000

排除在范围内
SELECT * FROM test_car WHERE car_money NOT  BETWEEN 0 AND 100000

like 字符匹配查询
SELECT * FROM test_car WHERE car_name LIKE '长%'

下划线通配符"_",一次只能匹配任意一个字符
SELECT * FROM test_car WHERE car_name LIKE '长_'

查询空值
SELECT * FROM test_car WHERE car_money IS NULL

SELECT * FROM test_car WHERE car_money IS NOT NULL

使用GROUP BY、COUNT 获取首字母品牌数量统计
SELECT car_letter, COUNT(car_letter) AS count FROM test_car GROUP BY car_letter

试用GROUP_CONCAT 将每个分组中各个品牌的值集合显示出来
SELECT car_letter, GROUP_CONCAT(car_name) AS NAMES FROM test_car GROUP BY car_letter

AND OR DISTINCT
SELECT * FROM test_car WHERE car_letter = 'C' AND car_name = '长安' 

SELECT * FROM test_car WHERE car_letter = 'C' OR car_name = '长安' 

SELECT DISTINCT car_letter FROM test_car

IF 
SELECT *, IF(car_money>500000, "豪华", "家用") AS level FROM test_car

CASE
SELECT *, CASE WHEN car_money>500000 THEN '豪华' ELSE '家用' END as level FROM test_car

正则表达式查询，规则自行搜索
SELECT * FROM test_car WHERE car_money REGEXP '^2'

sum()函数 某个字段值的总和
SELECT sum(car_money) FROM test_car 

avg()函数：某个字段的平均值
SELECT avg(car_money) FROM test_car 

max()函数：某个字段的最大值
SELECT max(car_money) FROM test_car 

min()函数：某个字段的最小值
SELECT min(car_money) FROM test_car 

限制查询
SELECT * FROM test_car limit 0, 5;

计算
SELECT *,car_money-bonus as actual FROM test_car limit 0, 5;

分组取前2记录
https://blog.csdn.net/acmain_chm/article/details/4126306
http://www.laruence.com/2008/07/16/227.html

SELECT
	*
FROM
	test_car a
WHERE
	2 > (
		SELECT
			count(*)
		FROM
			test_car
		WHERE
			car_letter = a.car_letter
		AND car_money > a.car_money
	)
AND a.car_money IS NOT NULL
ORDER BY
	a.car_letter,
	a.car_money DESC

插入数据
	
INSERT INTO test_car(id,car_name,car_money) SELECT null,car_name,car_money FROM test_car LIMIT 2;

插入 遇主键冲突，保持原纪录，忽略新插入的记录。

INSERT IGNORE INTO test_car VALUES ('10', 'A', '奥迪A6L', '380000', '10000')

遇主键冲突，替换原纪录，即先删除原纪录，后insert新纪录

REPLACE INTO test_car VALUES ('10', 'A', '奥迪A6L', '380000', '10000')

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中。
1、如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。(存在则影响2rows)
2、否则，直接插入新数据。(不存在仅插入影响1rows)
要注意的是：插入数据的表必须有主键或者是唯一索引！ 否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

当符合某种条件的数据存在时，去修改它，不存在时，则新增，也就是saveOrUpdate操作

# 单条记录
INSERT INTO test_car VALUES ('11', 'A', '奥迪A8L', '1380000', '20000') ON DUPLICATE KEY UPDATE id = 11, car_letter = 'A', car_name = '奥迪A8L', car_money= '1380000',  bonus = '20000'


# 批量插入
INSERT INTO `test_car` (id, car_letter, car_name, car_money, bonus) VALUES 
('12', 'B', '奔驰C200', '218000' , '4500'),
('13', 'B', '奔驰S400', '688000' , '15000'),
('14', 'C', '传祺GS5', '168000' , '2500')
ON DUPLICATE KEY UPDATE
id = VALUES(id),
car_letter = VALUES(car_letter),
car_name = VALUES(car_name),
car_money = VALUES(car_money),
bonus = VALUES(bonus)

SELECT
    userId,
    COUNT(userId) AS counts
FROM
    `jmd_user_cash_order`
WHERE
    createTime > 1556640000

GROUP BY
    userId
HAVING counts>= 4