MySql常用语法-1-单表查询--艾文

MySql常用语法–单表查询

1. 插入数据
   insert into table TableName(field1，field2) values(value1,value2)
   insert into table TableName values(value1,value2)
   注意插入字段内容需要按照字段大小要求，否则无效

2. 更新数据
   eg：update user set name=“new_name”;

3. 删除数据
   eg：delete from user where id=2;
   delete from user; #删除所有数据

4. 查询数据
   eg：select * from user;
   select distinct name from user; #distinct去重
   select name as ‘姓名’ sal12 as ‘年薪’ from user; #四则运算查询
   select concat(name, ‘的年薪为’,sal12) from user; #格式化显示查询结果
   a.条件查询
   select name from user where age=18;
   select name from user where age=18 and sal > 8000;
   select name from user where age!=18;
   select name from user where not age=18;
   b.Between…and关键字查询
   select name from user where age between 18 and 28;
   或者：select name from user where age>=18 and age <=28;
   c.非范围筛选
   查询工资小于1000和大于8000的员工
   select name,sal from user where sal<1000 or sal >8000;
   select name,sal from user where sal not between 1000 and 8000; #非范围筛选
   d.is null条件使用
   注意null和空字符串不是一回事，null代码没有设置值，空字符串代表设置了值，只是值是一个空值而已
   select name,reward as ‘奖金’ from user where reward is not null;
   e.in关键字查询
   select name,job from user where job in(‘salesman’,‘clerk’);
   select name,job from user where job not in(‘salesman’,‘clerk’);
   select name,job from user where job=‘salesman’ or job=‘clerk’;
   f.like关键字查询
   、包含a、A结尾、第二字符为a、不包含a的
   select name from user where name like “A%”; #A开头
   select name from user where name like “%A%”; #包含a
   select name from user where name like “%A”; #A结尾
   select name from user where name like “_A%”;
   select name from user where name not like “%A%”; #不包含a的
   g.排序记录查询
   select name,sal from user order by sal; #默认升序
   select name,sal from user order by sal desc;
   多字段排序：
   select name,sal from user order by sal asc, dirdate desc;
   h.限制记录查询
   select * from user where reward is not null limit 2,2; #查询第二页
   i.统计函数
   count(),avg(),sum(),max(),min()
   select count() from user;
   select count() from user where reward != null; #错误写法 null只能通过is null或者is not null判断
   select avg(*) from user; #avg会忽略字段为null的记录
   select sum(sal) from user; #会忽略字段为null的记录
   select max(sal) from user; #如何查询薪资最高的（用子查询尝试）
   select min(sal) from user;
   注意：如果没有记录，count返回0，其他函数返回的是null
   select count(sal),sum(sal),avg(avg) from user where sal is null;
   j.分组记录查询(group by)
   select job,avg(sal) from user group by job;
   未分组的列显示：如name
   select job,group_concat(name),avg(sal) from user group by job;
   问题：查询平均薪资大于2000的工种
   错误语法：select job,avg(sal) from user group by job where avg(sal) > 2000;

5. having语句的引入
   引入having，因为where无法与聚合函数一起用，having子句可以筛选分组后的各组数据
   顺序：where作用于group by和having之前，where只能作用于已有的数据表字段
   eg：
   select job,group_concat(name),avg(sal) fron user group by job having avg(sal) > 2000;
   where和having一样的场景
   select name,sal from employee where sal > 2000;
   select name,sal from employee having sal > 2000;
   having与where的区别：
   1.where是数据从磁盘读入内存的时候一条条判断的
   having是将所有数据读入内存，在分别统计前，根据having的条件再将不符合条件的数据删除
   2.having可以用别名，where不行
   3.having可以使用聚合函数，where不行
   having的筛选必须是根据前面的select字段的值进行筛选
   总结：where是列出先之前查询，having是再列出现以后筛选
   where只能再group by前面，顺序不能改变
   group by是分组依据，只能通过having进行筛选
   having语句不能单独出现，前面必须有group by

7.结果去重distinct
select distinct name,job from user;