SQL的基础操作 (2023-07-11)

[yaogengzhu]

笛卡尔积

select * from height_grades, player;

等值连接

select * from player, team where player.team_id = team.team_id;

非等值连接

select
    p.player_name as name,
    p.height, h.height_level
from player as p, height_grades as h
where p.height between h.height_lowest and h.height_highest
order by p.height desc;

外连接

左连接：指的是左边的是主表

SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id;
select * from player left join player_score on player.player_id = player_score.player_id;

同理 右连接表示右边是主表

select * from player, team where player.team_id(+) = team.team_id; # (+)表示主表
select * from player right join team on player.team_id = team.team_id;

自连接

select b.player_name, b.height from player as a , player as b where a.player_name = '布雷克-格里芬' and a.height < b.height

[yaogengzhu]

如何快速的拷贝一个表

第一步：复制表的结构

create table new_table Like origin_table

第一步：拷贝数据

insert into new_table select * from origin_table

[yaogengzhu]

解决唯一索引，值不能重复的问题

删除唯一索引

drop index command_id on command_task;

创建非唯一索引

create index command_id on command_task (command_id);