MySQL数据库

一些数据库中常用的SQL语言：

#1、插入语句

insert into xinxi(name,sex,age) values('张三','男',20);

#2、修改语句

update xinxi set age=18 where name='李四'; 

#3、删除语句

delete from xinxi where id=4;

#4、查询全部数据

select * from xinxi;

#5、查询部分数据

select id,name from xinxi;

#6、带条件的查询

select id,name from xinxi where name='王五';

#7、查询姓张的学生信息

select id,name from xinxi where name like '张%';

#8、查询age为空的学生name和age

select name,age from xinxi where age is null;

#9、查询age不为空的学生name和age

select name,age from xinxi where age is not null;

#10、查询年龄在20–25的学生name和age

select name,age from xinxi where age between 20 and 25;
select name,age from xinxi where age>=20 and age<=25;

#11、去掉重复的张三

select distinct name from xinxi where name='张三';

#12、按年龄升序排序查询id,name和age

select id,name,age from xinxi order by age;
select id,name,age from xinxi order by age asc;

#13、按年龄降序排序查询id,name和age

select id,name,age from xinxi order by age desc;

#14、显示第一页（每页3条）

select id,name,age from xinxi limit 0,3;
select id,name,age from xinxi limit 3;

#15、显示第二页（每页3条）

select id,name,age from xinxi limit 3,3;

#16、显示第三页（每页3条）

select id,name,age from xinxi limit 6,3;

#17、年龄总和

select sum(age) from xinxi;

#18、平均年龄

select avg(age) from xinxi;

#19、最大、最小年龄

select max(age),min(age) from xinxi;

#20、总人数

select count(*) from xinxi;
select count(id) from xinxi;
select count(1) from xinxi;

#21、内连接查询

select x.id,x.name,s.money from xinxi x inner join salary s on s.eid=x.id;
select x.id,x.name,s.money from salary s inner join xinxi x on s.eid=x.id;

#笛卡尔积

select x.id,x.name,s.money from xinxi x,salary s where s.eid=x.id;

#22、左外连接查询

select x.id,x.name,s.money from xinxi x left  join salary s on s.eid=x.id;
select x.id,x.name,s.money from salary s left join xinxi x on s.eid=x.id;

#23、按课程编号分组查询学生平均分(聚合函数和其他字段一起查询时必须用分组)

select cid,avg(grade) from score group by cid;

#24、查询补考学员

select sid,cid,avg(grade) from score group by sid,cid having count(grade)>1;

#25、查询年龄比王五大的人的姓名和年龄

select name,age from stu where age>(select age from stu where name='王五');

#26、查询年龄和王五一样大的人的姓名和年龄(子查询结果为多个)

select name,age from stu where age in(select age from stu where name='王五');

#27、查询成绩是80、85分的人

select sid,grade from score where grade in(80,85);

#28、查询成绩不是80、85分的人

select sid,grade from score where grade not in(80,85);

#29、如果有 80分以上的成绩，显示分数排在前3名的学员学号和分数

select sid,grade from score where exists (select grade from score where grade>=80) order by grade desc limit 3;

2020年6月30日