Mysql总结-1 单表查询

1. mysql在dos中查看中文乱码问题解决

2. 字段修改
   alter table <table名> modify column <column名> xxxx.
   alter table student modify column age int unsigned not null.

3. 单表查询

1、查询学生"百里守约"的基本信息
select * from student where name='百里守约'
2、查询学生"百里守约"或”百里玄策”的基本信息
select * from student where name='百里守约' or name='百里玄策'
3、查询姓"张"学生的姓名，年龄，班级
select name,age,class from student where name like '张%'
4、查询姓名中含有"约"字的学生的基本信息
select * from student where name like '%约%'
5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号
select studentNo,name,age,class,card from student where name like '孙__'
6、查询姓"百"或者姓”孙”的学生的基本信息
select * from student where name like '百%' or name like '孙%'
7、查询姓"百"并且家乡是"山西"的学生信息
select * from student where name like '百%' and hometown='山西'
8、查询家乡是"北京"、”新疆”、”山东”或者"上海"的学生的信息
select * from student where hometown in('北京','新疆','山东','上海')
9、查询姓"孙"，但是家乡不是"河北"的学生信息
select * from student where name like '孙%' and hometown!='河北'
10、查询家乡不是"北京"、"新疆"、"山东"、"上海"的学生的信息
select * from student where hometown not in('北京','新疆','山东','上海')
11、查询全部学生信息，并按照“性别”排序
select * from student order by sex
12、查询现有学生都来自于哪些不同的省份
select hometown from student group by hometown
13、查询所有男生，并按年龄升序排序
select * from student where sex='男' order by age
14、统计共有多少个学生
select count(*) from student
15、统计年龄大于20岁的学生有多少个
select count(*) from student where age>20
16、统计男生的平均年龄
select avg(age) from student where sex='男'
17、查询1班学生中的最大年龄是多少
select max(age) from student where class='1班'
18、统计2班男女生各有多少人
select class, sum(sex='男') as '男', sum(sex='女') as '女' from student group by class;
19、统计每个班级中每种性别的学生人数，并按照班级升序排序
select class,sex,count(*) from student group by class,sex order by class
20、查询年龄最小的学生的全部信息
select * from student order by age limit 1
select * from student where age=(select min(age) from student);

1、查询学号是'007'的学生的身份证号
select * from student where studentNo='007'
2、查询'1班'以外的学生信息
select * from student where class!='1班'
3、查询年龄大于20的学生的姓名和性别
select studentNo,sex from student where age>20
1、查询河南或河北的学生
select * from students where hometown='河南' or hometown='河北'
2、查询'1班'的'上海'的学生
select * from student where class='1班' and hometown='上海'
3、查询非20岁的学生
select * from student where not age=20
1、查询姓名为两个字的学生
select * from student where name like '__'
2、查询姓百且年龄大于20的学生
select * from students where name like '百%' and age>20
3、查询学号以1结尾的学生
select * from student where studentNo like '%1'
1、查询年龄在18或19或22的女生
select * from student where age between 18 and 22
2、查询年龄在20到25以外的学生
select * from student where not age between 20 and 25
1、查询所有学生信息，按班级从小到大排序，班级相同时，再按学号再按学号从小到大排序
select * from student order by class,studentNo
1、查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from students
2、一班共有多少个学生
select count(*) from student where class='1班'
3、查询3班年龄小于18岁的同学有几个
select count(*) from student where age<18 
查询各个班级学生的平均年龄、最大年龄、最小年龄
select class,max(age),min(age),avg(age) from student group by class
查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
select class,max(age),min(age),avg(age) from student group by class having class!='1班'
查询第4到第6行学生信息
select * from student limit 3,3
每页显示5条数据，显示每一页的数据
select * from student limit 0,5