一 数据库基本操作
create table test(
id int(10) primary key auto_increment,
name varchar(20) comment '姓名',
age int(10)
) charset=utf8 comment '哈哈';
insert into test(id,name) values(1,"战三");
insert into test(id,name) values(2,"两居");
update test
set name="李四"
where id = 1;
delete from test where id =1;
select * from test;
alter table test
drop column age;
drop table test;
二 关联查询
create table t_employee(
id int(10) primary key auto_increment,
name varchar(20),
age int(10)
) CHARSET=utf8 comment "员工表";
create table t_salary(
id int(10) primary key auto_increment,
money int(20),
monch int(10)
) CHARSET=utf8 comment "工资表";
insert into t_employee values(1,"张三",18);
insert into t_employee values(2,"李四",28);
insert into t_employee values(3,"王五",38);
insert into t_salary values(1,1000,1);
insert into t_salary values(2,20,2);
insert into t_salary values(3,300,3);
#平局值/和
select avg(money),sum(money) from t_salary;
#交叉查询(夺表查询，笛卡尔查询，列是和 行是积)
select * from t_employee,t_salary;
select * from t_employee cross join t_salary;
#内连接(就是交叉查询多了查询条件)
select * from t_employee e,t_salary s where e.age>20 and s.monch>2;
select * from t_employee e inner join t_salary s on e.age>20 and s.monch>2;
#外连接(outer 可以省略)
select * from t_employee e left outer join t_salary s on e.age>20 and s.monch>2;
select * from t_employee e right outer join t_salary s on e.age>20 and s.monch>2;
select * from t_employee e full outer join t_salary s on e.age>20 and s.monch>2;
#联合查询(union all 会显示重复)
select * from t_employee union select * from t_salary;
