数据库系统原理第八节

数据库设计

数据更新

插入数据

insert values

insert into table_name[(col_name)] values ();

insert set

insert into table_name
set col_name = '值', col_name = '值';

insert select

insert into table_name
select * from table_name;

删除数据

delete from table_name where id = 1

修改数据

update table_name set col_name = '值' where id = 1

数据查询

select 语句

select * from table_name

列的选择与指定

select col_name from table_name

定义别名

select col_name as alias from table_name

替换查询结果集中的数据

case 
when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式
end as alias

计算列值

select col_name + 100 from table_name

from 子句与多表连接查询

交叉连接，笛卡尔积

select * from table_namme1 cross join table_name2

简写：
select * from table_name1,table_name2;

内连接

select col_name from table_name inner join table_name2 on table_name.id = table_name2.t_id;

外连接

left join

right join