数据库的基本查询

创建一个数据库

create database java charset=utf8;

使用一个数据库

use java;

显示使用当前数据

select database();

当创建数据库不记得规格时

show create table students;

向students 表中插入数据

创建三个班级

查询

查询所有字段

select * from 表名;

select * from students;

select * from classes;

select id, name from classes;

查询指定字段

select 列1，列2...from 表名;

select name,age from students;

使用 as 给字段起别名

select 字段 as 名字... from 表名;

select name as 姓名,age as 年龄 from students;

select 表名.字段....from 表名;

select students.name,students.age from students;

可以通过as 给表起别名

select 别名.字段...from 表名 as 别名;

select s.name,s.age from students as s;

消除重复行

distinct 字段

select distinct gender from students;

条件查询

比较运算符

select ...from 表名 where ....

查询大于18岁的信息

select * from students where age>18;

查询小于18岁的信息

select * from students where age<18;

查询小于或者等于18岁的信息

select * from students where age=18;

逻辑运算符

and 

18岁到28岁之间的所有学生信息

select * from students where age>18 and age<28;

18岁以上的女性

select * from students where age>18 and gender="女";

or

18以上或者身高超过180(包含)以上

select * from students where age>18 or height>= 180.00;

not

不在18岁以上的女性这个范围的信息

select * from students where not age>18 and gender="女";

年龄不是小于或者等于18并且是女性

select * from students where (not age<=18) and gender=2;

模糊查询

like 

% 替换一个或者多个

_替换一个

查询姓名中以“小“开始的名字

select name from students where name like "小%";

查询姓名中有“小”所有的名字

select name from students where name like "%小%";

查询有2个字的名字

select name from students where name like "__";

查询有3个字的名字

select name from students where name like "___";

查询至少有2个字的名字

select name from students where name like "__%";

rlike 正则

查询以周开始的姓名

select name from students where name rlike "^周.*";

查询以周开始 花结尾的姓名

select name from students where name rlike "^周.*花$";

 范围查询

in (1,3,8)表示在一个非连续的范围内

查询年龄为18,34的姓名

select name,age from students where age=18 or age=34,or age=12;

简洁版

select name,age from students where age in(12,18,34);

not in 不非连续的范围之内

年龄不是18,34岁之间的信息

select name,age from students where age not  in (12,18,34);

between ...and ....表示在一个连续的范围内

查询年龄在18到34之间的信息

select name,age from students where age between 18 and 34;

not between ...and ....表示不在一个连续的范围内

查询年龄不在18到34之间的信息

select * from students where age not between 18 and 34;

空判断

判空 is null

查询身高为空的信息

select * from students where height is null;

判非空 is not null

select * from students where height is not null;

排序

order by 字段

asc从小到大排列即升序

desc从大到小排序即降序

查询年龄在18到34岁之间的男性按照年龄从小到大排序

select * from students where (age between 18 and 34) and gender=1;

select * from students where (age between 18 and 34) and gender=1 order by age;

select * from students where (age between 18 and 34) and gender=1 order by age asc;

查询年龄在18到34岁之间的女性身高从高到矮排序

select * from students where (age between 18 and 34) and gender=2 order by height desc; 

order by 多个字段

查询年龄在18到34岁之间的女性，身高从高到矮排序如果身高相同的情况下按照年龄从小到大排序

select * from students where (age between 18 and 34) and gender=2 order by height desc,id desc;

查询年龄在18到34岁之间的女性,身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序，

如果年龄也相同那么按照id从到小排序

select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;

按照年龄从小到大身高从高到矮的排序

select * from students order by age asc,height desc;

聚合函数

总数

count

查询男性有多少人女性有多少人

select * from students where gender=1;

select count(*) from students where gender=1;

select count(*) as 男性人数 from students where gender=1;

select count(*) as 女性人数 from students where gender=2;

最大值

max

查询最大的年龄

select max(age) from students;

查询女性的最高 身高

select max(height) from students where gender=2;

最小值

min

select min(height) from students where gender=2;

求和

sum

计算所有人的年龄总和

select sum(age) from students;

平均值

avg 

计算平均年龄

select avg(age) from students;

计算平均年龄sum(age)/count(*)

select sum(age)/count(*) from students;

四舍五入 round(123.23,1)保留一位小数

计算所有人的平均年龄保留俩位小数

select round(sum(age)/count(*),2) from students;

计算男性的平均身高保留俩位小数

select round(avg(height),2) from students where gender=1;

分组

group by

按照性别分组查询所有的性别

select gender from students group by gender;

计算每种性别中的人数

select gender,count(*) from students group by gender;

计算男性的人数

select gender,count(*) from students where gender=1 group by gender;

group_concat(....)

查询同种性别中的姓名

select gender,group_concat(name) from students where gender=1 group by gender;

select gender,group_concat(name,age,id) from students where gender=1 group by gender;

select gender,group_concat(name,"_",age," ",id) from students where gender=1 group by gender;

having

查询平均年龄超过30岁的性别以及姓名having avg(age) >30

select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;

查询每种性别中的人数多于2个的信息

select gender,group_concat(name) from students group by gender having count(*)>2;

分页

limit start,count

限制查询出来的数据个数

select * from students where gender=1 limit 2;

查询前5个数据

select * from students limit 0,5;

查询id6-10(包含)的书序

select * from students limit 5,5;

每页显示俩个第一个页面

select * from students limit  0,2;

每页显示俩个第俩个页面

select * from students limit 2,2;

每页显示俩个第三个页面

select * from students limit 4,2;

每页显示俩个第四个页面

select * from students limit 6,2;

每页显示俩个显示第六页的信息按照年龄从小到大的排序

select * from students order by age asc limit 10,2;

连接查询

内链接查询：查询的结果为俩个表匹配到的数据

右链接查询：查询的结果为俩个表匹配到的数据，右表特有的数据对于左表中不存在的数据使用null填充

左链接查询：查询的结果为俩个表匹配到的数据左表特有的数据对于右表中不存在的数据使用null填充

inner join ..on

select ... from 表A inner join 表B；

select * from students inner join classes;

查询有能够对应班级的学生以及班级信息

select * from students inner join classes on students.cls_id=classes.id;

按照要求显示姓名，班级

select students.*,classes.name from students inner join classes on students.cls_id=classes.id;

给数据表起名字

select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

查询 有能够对应班级的学生及班级信息显示学生的所有信息只显示班级名称

select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;

在以上的查询中，将班级姓名显示在第一列

select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;

查询有能够对应班级的学生以及班级信息按照班级进行排序

select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;

left join

查询每位学生对应的班级信息

select * from students as s left join classes as c on s.cls_id=c.id;

查询没有对应班级信息的学生

select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;

 子查询

标量子查询

查询出高于平均身高的信息

查询最高的男生信息

select * from students where height=188;

select * from students shere height=(select max(height)from students);