重点:
表结构的增删改:
alter table t_students add id int;
alter table t_students drop id;
alter table t_students modify id varchar(20);
表数据的增删改:
insert into table t_students (id,name)<可以省略> values(1,'关羽');
delte from table t_students where id =2;
update tabel t_students set id=2,name='刘备' where id=1;
表查询
select ...
from ...
where....
group by...
having ...(先有groupby进行统计,后有having进行筛选
order by...
limit 1,5; #这里1代表第二行数据,和python一样
优先级:
from >where>group by >select >having>order by(选出来后再排序)>limit
多表查询(内外连接)
select a.name,b.id from emp a join emp1 b on a.add=b.add;
#inner join 还可以有outer join ,left join ,right join ,区别在于inner求交集,left显示左边,right
显示右边,outer 显示两边
子查询,下面
1.在子查询中可以两个一起等于(行子查询,)
select playerno from players where (sex,town)=(select sex,town from players where playerno =100);
2.列子查询(通常和in (select ...) ,>any(select...),<all(select ...)连用)
#用in的情况,in+一列(获取球员性别为女的所有球员的球员号,名字及所在城市。)
select playerno,name,town from players where playerno in (select playerno from players where sex='f');
#用any的情况,这里也使用了两个表进行对比(获取至少比同城的另一球员年轻的所有球员的号码,日期和居住城市)
select playerno, birth_date, town from players as p1 where birth_date > any(select birth_d