sql 积累 ; mysql 学习笔记; Compilation of SQL syntax;

本文会记录一些开发过程中学到的Mysql相关的知识，如有不足请指正：

MYSQL

• mysql 选择保留group中的哪条记录

  SELECT * FROM lzh_order_payment_transaction;
  SELECT * FROM lzh_order_payment_transaction WHERE id IN(
  	SELECT MAX(id)
  	FROM `lzh_order_payment_transaction` 
  	WHERE `status` ！= 1 
  	GROUP BY payment_id 
  );
  

• if 用法

#创建demo表 记录考试通过情况
CREATE TABLE `demo` (
  `id` INT NOT NULL,
  `name` VARCHAR (48),
  `score` INT,
  `home_town` VARCHAR (48) NOT NULL,
  PRIMARY KEY(`id`) 
) 
DEFAULT CHARACTER SET = utf8 ;

ALTER TABLE `demo` MODIFY `id` INT NOT NULL AUTO_INCREMENT;
SELECT * FROM demo;

# 插入测试数据
INSERT INTO demo (`name`, `score`, `home_town`)
VALUES
('Paul', 90, 'England'),
('Kobe', 60, 'America'),
('James', 35, 'America'),
('Durant', 59, 'Soutch Africa');

# 根据条件查看考试通过情况
#if语法 if(condition, for_true, for_false) 
SELECT `name`, IF(`score` >= 60, 'pass', 'faile') AS 'result' FROM demo;