MySQL高级查询一

MySQL高级查询一
一、修改表
#修改表名
alter table info2 rename info;
#添加字段
alter table info2 add password varchar(10) not null;
#修改字段
alter table info2 change name username varchar(10) not null;
#删除字段
alter table info2 drop password;
二、创建成绩表
create table score(
id int(4) not null,
grade int(3) not null,
sid int(5) not null
);
drop table score;（删除score表）
#为成绩表添加主键
alter table score add constraint pk_score primary key score(id);
#为成绩表添加外键
alter table score add constraint pk_info_score foreign key(sid)
references info(id);
#为成绩表插入一条数据
insert into score(grade,sid) values (97,4);
#为成绩表插入多条数据
insert into score(grade,sid) values (98,2),(99,3);
三、DML语句——将查询结果插入新表
#事先创建且与插入数据字段相等
insert into info3(name,age) select username,age from info;
#无需事先创建
create table info4(select username,age from info);
四、DML语句——数据更新、删除
#更新数据
update info set age=20 where id=2;
update info set age=21 where username=‘111’;
update info set username=‘123’,age=22 where id=1;
#删除数据
delete from info where id=1;
insert into info(username,sex,age) values(‘zhang’,‘男’,25);
truncate table info;
#去掉重复内容
select distinct username from info;
五、DML语句——查询
#查询全部数据
select * from info;
#查询部分字段
select id,username from info;
#查询字段用别名
select id as 编号,username 姓名 from info;
#查询姓名中含有’a’的人( %：任意长度的任意字符)
select username from info where username like ‘%a%’;
#查询年龄为空的人
select username from info where age is null;
#查询年龄不为空的人
select username from info where age is not null;
#查询年龄在20–24的人
select username from info where age between 20 and 24;
select username from info where age>=20 and age<=24;
select username,age from info where age>=20 or age<=24;
六、数据查询-排序
#按年龄升序排列
select id,username,age from info order by age;
select id,username,age from info order by age asc;
#按年龄降序排列
select id,username,age from info order by age desc;
#按年龄降序(多列)排列：先按第一个字段排列，当第一个字段有相同值时，再按第二个字段排列
select id,username,age from info order by age desc,id desc;
#查询前三条数据
select id,username from info limit 0,3;
#查询id=2–4数据
select id,username from info limit 1,3;
七、多表联结查询
#内连接
select i.username,s.grade from score s inner join info i on i.id=s.sid;
#笛卡尔积
select i.username,s.grade from info i,score s where i.id=s.sid;
#左外连接
select i.username,s.grade from info i left join score s on i.id=s.sid;
select i.username,s.grade from score s left join info i on i.id=s.sid;
#右外连接
select i.username,s.grade from info i right join score s on i.id=s.sid;
select i.username,s.grade from score s right join info i on i.id=s.sid;
八、常用函数——聚合函数
#求年龄的平均值
select AVG(age) from info;
#求最大、最小年龄
select MAX(age) 最大年龄,MIN(age) 最小年龄 from info;
#年龄总和
select SUM(age) 年龄总和 from info;
#求总人数
select COUNT(*) 总人数 from info;
select COUNT(id) from info;
select COUNT(1) from info;（最好用）