-
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;
