查询语句
单表查询
- 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) );