Mysql的各种语句

这是mysql的学生表的各种方法语句

create database demo
use demo

create table classinfo(
classid int primary key auto_increment,
className varchar(20)
)

create table student(
stuid varchar(20) primary key,
stuname varchar(20),
stusex char(2),
stuage int,
stuaddress varchar(50),
stuclassid int references classinfo(classid)
)
–添加数据
insert into classinfo values(‘1001′,’软件A班’)
insert into classinfo values(‘1002′,’软件B班’)
insert into classinfo values(‘1003′,’软件C班’)
insert into classinfo values(‘1004′,’软件D班’)

insert into student values(1,’张三’,’男’,25,’河南’,’1001′);
insert into student values(2,’李四’,’男’,24,’河南’,’1001′);
insert into student values(3,’王五’,’男’,20,’河南’,’1002′);
insert into student values(4,’赵六’,’男’,17,’河南’,’1003′);
insert into student values(5,’小丽’,’女’,19,’河南’,’1004′);
insert into student values(6,’翠花’,’女’,27,’河南’,’1002′);
insert into student values(7,’嗡嗡嗡’,’男’,25,’开封’,’1001′);
insert into student values(8,’咳咳咳’,’女’,28,’洛阳’,’1002′);
insert into student values(9,’我猜’,’女’,25,’东北’,’1003′);
insert into student values(10,’六四’,’男’,24,’济源’,’1004′);
–查询班级信息
select*from classinfo
–修改学生地址
update student set stuaddress=’南阳’ where stuid=’1′
update student set stuaddress=’郑州’ where stuid=’2′
update student set stuaddress=’北京’ where stuid=’3′;
update student set stuaddress=’深圳’ where stuid=’4′;
update student set stuaddress=’朝阳’ where stuid=’5′;
select*from student
–删除某个学生信息
delete from student where stuid=’3′;
–查询某个学生年龄大于20的学生信息
select *from student where stuage>20
–查询学生年龄在20到30之间的学生信息
select *from student where stuage BETWEEN 20 and 30
–查询性别实女生并且年龄大于20的学生信息
select *from student where stusex=’女’ and stuage>20
–查询性别为女的学生信息
select *from student where stusex=’女’
–查询地址是北京的学生信息
select*from student where stuaddress=’南阳’
–查询班级编号为3的学生信息
select*from student where stuclassid=’3′
–查询学生姓张的学生信息 模糊查询
select *from student where stuname like ‘张%’
–查询学生姓翠的学生信息并且年龄大于24岁的学生信息
select*from student where stuname like’翠%’ and stuage>24
–查询学生姓名中有张的学生信息
select*from student where stuname like ‘张%’
–内连接查询 inner join on 查询学生信息，显示班级名称
select s.*,c.className from student s inner JOIN classinfo c on s.stuclassid=c.classid
select s.*,c.className from student s,classinfo c where s.stuclassid=c.classid
–查询每个班级的学生人数
select count(*) as 数量,stuclassid as 班级人数 from student GROUP BY stuclassid

–查询每个班级的学生人数,学生性别是男
select count(*) as 数量,stuclassid as 班级人数 from student where stusex=’男’ GROUP BY stuclassid ;

–查询每个班级平均年龄大于24的信息  分组再筛选
select AVG(stuage) as 平均年龄,stuclassid from student GROUP BY stuclassid HAVING AVG(stuage)>24
–对年龄做一个升序的排序  降序desc
select * from student ORDER BY stuage desc
select *from student order by stuage asc
–分页-每页显示5行数据  pageSize=5,PageCode=1
select *from student LIMIT (PageCode-1)*pageSize,pageSize
–外连接  左外（以左边的表为基准），右外(以右边的表为基准)
select *from student s left OUTER JOIN classinfo c on s.stuclassid=c.classid
select s.*,className from classinfo c right OUTER JOIN student s on c.classid=s.stuclassid
–查询前10条数据
select *from student LIMIT 0 ,10
–查询班级编号为1的学生信息（子查询）
select *from student where stuclassid in(select classid from classinfo where classid=’1001′ )
–查询年龄大于平均年龄的所有学生信息
select *from student where stuage>(select AVG(stuage) from student)

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树数据库组成表31525 人正在系统学习中

来源：陶华碧桑