高级查询

1.修改语句
#修改表名
alter table info2 rename score;
#添加字段
alter table info2 add password varchar(10) not null;
#修改字段
alter table info2 change name username varchar(10) not null;
#删除字段
alter table info2 drop password;
2. 添加主键
#为成绩表添加主键（重点）
alter table score add constraint pk_sore primary key score(id);
#为成绩表添加主外键（重点）
alter table score add constraint fk_info_score foreign key(sid) references info(id);
3. 查询语句
#查询全部数据
select * from info;
#查询部分字段
select id,name from info;
#查询字段别用名
select id as 编号,name as 姓名 from info;
#查询姓名中含有’a’的人（%：任意长度的任意字符）
select name from info where name like ‘%a%’;
#查询年龄为空的人
select name from info where age is null;
#查询年龄不为空的人
select name from info where age is not null;
#查询年龄在20–24的人
select name from info where age between 20 and 24;
select name from info where age>=20 and age<=24;
select name from info where age>=20 or age<=24;
select name from info where age between 20 and 24;
4. 多表连接查询
#内连接
select i.name,s.grade from score s inner join info i on i.id=s.sid;
#内连接表（score）与顺序无关
select i.name,s.grade from info i inner join score s on i.id=s.sid;
#左连接
select i.name,s.grade from info i left join score s on i.id=s.sid;
select i.name,s.grade from score s left join info i on i.id=s.sid;
#右连接
select i.name,s.grade from info i right join score s on i.id=s.sid;
select i.name,s.grade from score s right join info i on i.id=s.sid;
5. 子查询
#IN 子查询，可以返回多条记录( = 改为 in)
select name from info where id in(select sid from score where grade>=80);
#NOT IN 子查询( )
select name from info where id not in(select sid from score where grade=80);