Mysql 学习笔记

• alter

  • 用alter添加列

        alter table table_name add row_name type_name;
        alter table ant_statistics.ant_visitor add ip varchar(20);
  

  • 用alter修改现有列的属性

        alter table table_name modify row_name new_properties;
        alter table ant_statistics.ant_visitor add ip int(10);
  

• 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 用法

**if语法 if(condition, for_true, for_false) **

#创建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');

 #根据条件查看考试通过情况: 低于60分算不通过
SELECT `name`, IF(`score` >= 60, 'pass', 'faile') AS 'result' FROM demo;