Mysql查询语句

查询语句

单表查询
  • 1.1 全表查寻
select * from employee;
  • 1.2 指定字段查询
查询职员表中所有职员姓名和入职时间
select empname,hirdate from employee;
  • 1.3剃重查询
查询所有的职位
select distinct job from employee;
  • 1.4单条件查询
查询奖金大于10000的职员
select * from employee where comn>10000;
  • 1.5多条件逻辑查询 and,or
查询奖金大于10000并且职位是骑兵头领的职员
select * from employee where comn>10000 and job="头领";
查询奖金大于10000或者职位是骑兵头领的职员
select * from employee where comn>10000 or job="骑兵头领";
  • 1.6范围查询 between..and in
查询奖金大于8000-10000的职员
select * from employee where comn between 8000 and 10000;
查寻岗位是头领,骑兵头领,五虎上将的职员
select * from employee where job in("头领","骑兵头领","五虎上将");
  • 1.7空值查询 is null
查看没有上级的员工
select * from employee where mgr is null;
  • 1.8 模糊查询
职级为头领的职员
select * from employee where job like "%头领";
  • 1.9排序查询
按照工资升序查看职员信息   asc 升序,desc降序
select * from employee order by salary asc;
  • 分页查询
每页20条,查询第三页数据
select * from employee order by salary asc limit 61,20;
  • 分组查询
查看每个部门的员工姓名,group_concat将相同的行组合起来
select group_concat(empname) from employee group by deptno;

聚合查询

  • 聚合查询聚合查询,是通过 MySQL 内建的聚合函数,完成数据库中查询数据的聚合运算结果,如求和、求平均值等

    • 1.1 count()
    按照指定条件,查询数据便中的所有记录
    select count(*) from employee;
    
    按照指定的列查询记录数
    select count(mgr) from employee;
    
    分组查询不同小组的记录数
    select deptno,count(*) from employee group by deptno;
    
    • 1.2 sum()
    求和函数,可以查询指定列所有数据的和
    查询所有员工的奖金总和
    select sum(comn) from employee;
    
    • 1.3求平均值 avg()
    求平均数函数,可以查询指定列所有数据的平均值
    查询所有员工的平均奖金
    select avg(comn) from employee;
    
    • 1.4 max()
    查询指定列所有数据的最大值
    查询所有员工的最高奖金
    select max(comn) from employee;
    
    • 1.5min()
    查询指定列所有数据的最小值
    查询所有员工的最低奖金
    select min(comn) from employee;
    
mysql常用字符串函数
  • char_length(str)

    计算str中有多少个字符
    例:查看字符串“我叫张伟强” 有几个字
    select char_length("我叫张伟强");
    
    查询学生表中名字是三个字的有哪些?
    select sname,char_length(sname) from stu where char_length(sname)=3;
    
  • concat(str1,str2)

    把参数str1和str2拼成一个字符串
    例:把“我是”和“张伟强”拼接起来
    select concat("我是","张伟强");
    
    输出已分班学生的姓名和班级,以 “xxx是xx班”的形式打印结果
    select concat(sname,"是",cno,"班") from stu where cno is not null;
    
  • sustring(str,pos,len)

    把字符床str从第pos位起,截取len为
    例:
    把“我是张伟强” 从第3位起,截取3位字符
    select substring("我是张伟强",3,3);
    
    查询二班的同学有哪些姓氏
    select sname,substring(sname,1,1) from stu where cno=2;
    
  • round(num,n)

    对数字进行四舍五入运算
    例:15.3475,保留两位小数
    select round(15.3475,2);
    
    计算肥胖学生"许褚"的BMI值,四舍五入保留2位小数, 体重/身高^2
    select round(weight/(height/100*height/100),2) from stu where sname="许褚";
    
mysql常用日期函数
  • year(date1) ,获取date1的年份

  • month(date),获取date1的月份

    获取当前年份
    select year("2020-05-25");
    获取当前月份
    select month("2020-05-25");
    例1:学生表中哪些同学是1990年出生的
    select sname,birth,year(birth) from stu where year(birth)=1990;
    例1:学生表中哪些同学是8月份出生的
    select sname,birth,month(birth) from stu where month(birth)=8;
    
  • curdate()获取当前日期

  • curtime()获取当前时间

  • now()获取当前日期和时间

  • datediff(date1,date2),返回的是两个日期相隔的天数

    例:计算2020年5月25日到2020年10月1日间隔的天数
    select datediff("2020-5-25","2020-10-1");
     
    计算学生表中的学生的年龄,显示姓名,生日,年龄(2位小数),只显示小于22岁的同学
    select round(datediff(curdate(),birth)/365,2) from stu;
    select sname,birth,round(datediff(curdate(),birth)/365,2) from stu where round(datediff(curdate(),birth)/365,2)<22;
    
mysql常用条件判断函数
  • if(expr,v1,v2)

    如果expr表达式成立,返回v1的值
    否则,返回v2的值
    例:如果学生高考分数大于520分,其为统招生,否则为管培生,从学生表查找,显示姓名,考分,类型(统招/管培)
    select sname,score,"stu" 类型 from stu;
    select sname,score,if(score>520,"统招","管培") 类型 from stu;
    
  • case运算符

    case when expr1 then v1 ...else vn  end
    如果高考分数700分以上,优秀,600以上,良好,520以上,及格,否则,较差,按着此原则列出学生表中的学生,显示姓名,考分,等级
    select sname,score,(case when score>=700 then "优秀" when score>=600 then "良好" when score>=520 then "及格" else "较差" end) 等级 from stu;
    
列的别名
给列额外的名称代替原来的名称
select sname [as] 姓名 from stu;  as可不写
例:列出2班的学生姓名,性别,生日,表头用对应中文显示
select sname 姓名,sex 性别,birth 生日 from stu;

表的别名

给表额外的名称代替原来的名称
select s.sname,s.sex from stu as s;  as可以不写
例:列出2班的学生的姓名,性别,生日
select s.sname,s.sex,s.birth from stu s where s.cno=2;
  • order by字句

    对查询结果按照指定的1列或者多列排序
    分为增序和降序
    增序asc ,默认可以不写
    降序desc
    对于数值,增序是从小到大
    对于日期和时间,增序是由远到近
    对于英文字符,增序是从a到z
    
  • group by

    group by 按照指定的列对表数据进行分组
    group by 后面跟的列叫分组特性列
    使用grop by后,能选择的列通常只能包括分组特性列和聚合函数
    
    按照班号分组,列出学生表的班号,统计每个班的平均身高,平均体重,人数,最高分,不包括未分班的同学
    select cno 班号,avg(height) 平均身高,avg(weight) 平均体重,count(*),max(score) 最高分 from stu where cno is not null group by cno;
    
  • 完整的select 语句

    select distince *
    from 表名
    where ...
    group by...having...
    order by...
    limit ...
    
  • having字句

    having是对group by产生的结果集进行过滤
    having可以对分组特性列和聚合函数进行过滤
    
    例1:按照学生出生年份分组,统计出所有学生每个出生年份的人数,最高分,最低分,按照年份排序,并从结果中找出人数超过两个,且最高分超过600的年份
    select year(birth) 出生年份,count(*) 人数,max(score) 最高分,min(score) 最低分 from stu group by year(birth) having count(*)>2 and max(score)>600 order by 1;
    
    例2:找出已分班学生中,哪些班学生的平均身高超过175,列出其班号和人数
    select cno,avg(height) from stu where cno is not null group by cno;
    select cno 班号,avg(height) 平均身高 from stu where cno is not null group by cno having avg(height)>175;
    
    例3:找出已分班学生中,哪些班的每个人的身高都超过165,列出班号和人数
    select cno,count(*) from stu where cno is not null group by cno having min(height)>165;
    
    例4:统计1班的人数,列出班号和人数
    方法1:
    select cno,count(*) from stu group by cno having cno=1;
    方法2:
    select cno,count(*) from stu where cno=1;
    第一种先使用group by统计,再用having过滤统计结果
    统计过程中统计了和1班不相干的其他班级人数,浪费了系统资源,效率低
    第二种,先用where过滤不相干的班级学生,然后直接统计1班的人数,效率高
    where可以先把结果集缩小
    

多表关联查询

  • 数据不可以放在同一张表,会造成大量的数据冗余

    学号 学生姓名 班级 班主任 课程名称 分数
    1 张伟强 1班 陈老师 语文 78
    1 张伟强 1班 陈老师 数学 75
    1 张伟强 1班 陈老师 物理 82
    2 徐伟明 1班 陈老师 语文 85
    2 徐伟明 1班 陈老师 数学 83
    3 徐伟明 1班 陈老师 物理 89

    如果班主任变了,会修改很多地方

    使用多张表,建立外键

    外键:保持数据的一致性,完整性

  • 多表关联关系

    • 一对一关系

      通过主键关联主键实现

      通过外键关联主键可以实现

      案例:游戏用户(user)和账号(game_num)

  • 一对一实现

    第一种方式,通过主键关联主键实现,建完表后添加外键
    用户表user
    create table user(
    	id int primary key auto_increment,
    	name varchar(50),
    	age int);
    账号表game_num
    create table game_num(
    	id int primary key auto_increment,
    	nickname varchar(50),
    	level int);
    没有添加外键约束可以任意删除
    外键
    alter table game_num
    	add constraint foreign key(id) references user(id);
    constraint可以省略
    
    添加数据
    insert into user values(1,"张伟强",22);
    insert into game_num values(1,"国服李白",15);
    insert into game_num values(2,"国服韩信",25); --error
    
    第二种方式实现:建表的时候添加外键
    用户表user
    create table user(
    	id int primary key auto_increment,
    	name varchar(50),
    	age int);
    账号表game_num
    create table game_num(
    	id int primary key auto_increment,
    	nickname varchar(50),
    	level int,
    	foreign key(id) references user(id)
    	);
    

Mysql查询语句

上一篇:Jmeter发送jdbc请求(操作mysql)


下一篇:MongoDB的数据导入和导出(mongoimport和mongoexport)