MySQl查询练习

查询学生表

select * from student

添加SQL语句

insert into student(name,sex,age,tel,height,city)  
	values('小红','女',20,'13111111111',1.68,'湘潭')  
insert into student  
	values(null,'小明','男',22,'13222222222',1.73,'长沙')  

批量添加 union表示数据的合并(union也可用于后面会说到的全连接)

insert into student  
	select 'null','小军','男','27','13333333333',1.74,'株州' union  
  select 'null','小晶','女','24','13444444444',1.66,'长沙' union  
  select 'null','小黄','男','25','13555555555',1.71,'湘潭' union  
  select 'null','小朱','男','26','13666666666',1.71,'长沙' union  
  select 'null','小婷','女','27','13777777777',1.75,'岳阳'  

一、基本查询

1. 查询所有的学生信息
   select * from student
   
   
2. 查询女生姓名、年龄、电话（取别名）
   select name as'姓名',age as'年龄',tel as'电话' from student
   
   
3. 查询含有'三'的学生信息
   select * from student where name like '%三%'
   
   
4. 查询年龄20-30之间手机以138开头的学生

   select * from student where age between 20 and 30   
      	and tel like '138%'  <br>

5. 查询手机号第二位为3并且以9结尾的学生
   select * from student where tel like '_3%9'
   
   
6. 查询编号为1,2,4,5的学生编号和姓名信息
   select * from student where id in(1,2,4,5)
   
   
7. 查询电话为空但城市不为空的学生信息 （判断空值或者非空值需要用到 is 关键字）
   select * from student where tel is null and city is not null
   
   
8. 显示所有男学生按年龄升序排列（order by 要写在SQL语句最后）
   select * from student where sex='男' order by age asc
   注释：默认为升序(asc),desc为降序
   9.查询编号不为（1.3.5）的学生信息(in , not in)
   select * from student where id not in(1,3,5);

二.聚合函数

注：聚合函数是用来针对列的所有值操作，只返回一个结果
聚合函数不能与其它列名一起来进行查询操作，除非分组

1. 最大值：显示最大年龄值 max() 函数
   select max(age) as'最大值' from student
   
2. 最小值：显示最低身高值 min()函数

`select min(height) as'最低身高',max(height) as'最高身高' `<br>
`from student`

3. 平均值：显示平均年龄值
   select avg(age) from student
   
4. 求和值：显示总年龄值
   select sum(age) from student
   
5. 统计值：统计一共有多少学生
   count针对重复值只算一条，针对null值算0
   所以count函数内需要放主键id列

select count(id) from student
select count(*) from student -- 效率低于count(id)

6. 显示如下数据： 总人数 最小年龄 最大年龄 平均年龄 最大差距

select 
	count(id) as'总人数',
	min(age) as'最小年龄',
	max(age) as'最大年龄',
	avg(age) as'平均年龄',
  max(age)-min(age) as'最大差距'
from student

三.分组查询

注：分组通常都会与聚合函数一起使用
以哪个列分组就需要显示哪个列

使用技巧：

分组前的条件是 where ... group by ...
分组后的条件是 group by 列名 having 聚合条件
select sex as'性别',count(id) as'人数'
from student
group by sex

2. 显示每个城市下分别有多少名学员,如下:

select city as'城市',count(id) as'人数'
       from student 
       group by city

3. 显示每个城市下男生有多少名

select city as'城市',count(id) as'人数'
       from student 
       where sex='男'
       group by city

4. 按城市进行分组
   显示属于该城市人数要有3名以上的城市名和人数

  select city as'城市',count(id) as'人数'
       from student 
+
       where sex='男'
       group by city
       having count(id)>=3

5. 按城市进行分组显示女生的平均年龄是多少

select city as'城市',avg(age) as'平均年龄'
       from student 
       where sex='女'
       group by city

四.模糊查询

select * from 表名 where  列名 like 值

sql语句通配符

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]or [!charlist]	不在字符列中的任何单一字符
查询姓苏刘李的学生信息
select * from student where name like '[^苏刘李]%'

五. 函数应用查询

函数名	作用	用法
char_length()	求字符串长度	char_length(列名)
concat(a,b,c...)	连接字符串	concat(sex,'人')
substring(str,pos,len)	截取函数	str:列名,pos:截取开始位置,len:开始截取几位
now()	获取当前时间	直接用
Date_format(a,b)	取特定的时间段	date_format(now(),'%y'),对象字符

六.多表联查

查询留言信息表,数据如下:多表连查

select a.mId as'留言编号',
a.mTest as'留言内容',
a.mDate as '留言时间',
b.bkName as'板块名称',
c.uName as '留言人姓名' 
from msg as a, bk as b,users as c
where a.bkId = b.bkId and a.mName = c.uId

内连接
select a.id as '编号',a.name as'姓名'
from stuinfo a inner join blood b on a.id=b.id
inner join star c on a.sid = c.id

外联接
select * from 表1 别名1 left join 表2 别名2 on 表1.公共列 = 表2.公共列


全连接

union 作左外和右外连接的合并（相同的结果只显示一次）