CREATE DATABASE IF NOT EXISTS nba;

USE nba;

DROP TABLE IF EXISTS player;
CREATE TABLE player (
 `player_id` INT(11) NOT NULL AUTO_INCREMENT,
 `team_id` INT(11) NOT NULL,
 `player_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `height` float(3, 2) NOT NULL DEFAULT 0.00,
 PRIMARY KEY `player_id`(`player_id`) USING BTREE,
 UNIQUE INDEX `player_name`(`player_name`) USING BTREE,
 CHECK(height>=0 AND height<3) -- MySQL8.0.16版本及以上才支持check，以前的版本解析后就忽略了，不起作用
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;


ALTER TABLE player ADD (age int(11));
ALTER TABLE player CHANGE `age` `player_age` float(3,2);
ALTER TABLE player MODIFY COLUMN player_age float(3,1);
-- ALTER TABLE player RENAME COLUMN age to player_age oracle 这么些
-- 当条件满足if语句时，执行动作体
create trigger playerAgeTrigger before insert on player for each row
begin 
    if (new.player_age < 0) then  
   new.player_age = 0;
    end if;
end;

SHOW TABLE STATUS LIKE "player";
show CREATE TABLE player;