sql基础2单表查询

单表查询

drop table user;

creat table user(
id int primary key comment “id”,
name vachar(4) not null comment"名字",
age int default 18,
birthday date
);

alter table user modify name varchar(9); 改表

insert into user(id, name,age,birthday)values(1,“s”,14,“2000-01-12”) #时间用字符串格式

insert into user values(2,“sun”,13,“1999-09-12”);

插入部分列数据
insert into user (id,name) values(3,“zz”)

插入多条数据
insert into user(id, name, age ,birthday) values
(4,“s4”,23,“1999-09-12”),
(5,“s5”,23,“1999-09-12”);

修改自增
alter table user change id id int auto_increment;
insert into user(name,age,birthday) values
(“s4”,23,“1999-09-12”),
(“s5”,23,“1999-09-12”);

更新数据
update user set name=“s_new”,age=34 where id =3
update user set name=“sum_new” where id=3;

删除数据
delete from user where id=2;
detele from user; 删除所有数据

---

create table employee(
no int comment “编号”,
name varchar(40) ,
job varchar(30),
sal double(10,2) comment"工资"
)charset=utf8;

insert into employee values
(1,“n1”,“clerk”,8900),
(2,“n2”,“clerk”,8900);

#查询所有字段
select * from employee;

#distinct避免重复查询
select distinct job from employee;

#包含数学运算的查询
select job as “职位”，sal *12 as “薪资” from employee;

#concat格式化显示
select concat(name,“的年薪为”,sal*12) as 薪资 from employee;

#单条件查询
查询所有文员
select name from employee where job=“clerk”;

多条件查询
查工资大于八百的所有文员
select name, job, sal from employee where job=“clerk” and sal>800 ;
查询所有文员和销售
select name,job from emplyee where job=“clerk” or job=“saleman”;
查询除文员以外的所有人员
select name, job from employee where job != “clerk”;
select name, job from employee where not job=“clerk”;

between and
查询工资在1000到2000的员工
select name,job from employee where salary between 1000 and 2000;
select name ,job from employee where salary >=1000 and salary <=2000;
查询工资小于1000和＞2000的员工
select name from employee where sal not between 1000 and 2000;
select name from employee where sal <1000 or sal>2000;

not null
查询有奖金的员工
select name from employee where sal is not null;

in条件查询
查询所有的销售人员和文员
select name,job from employee where job in (“saleman”,“clerk”);
查询非销售人员和文员
select name from employee where job not in (“saleman”,“clerk”);

like查询 %代表匹配任意字符 _代表匹配单个字符
查询以A开头的员工
select name from employee where name like “A%”;
查包含a的员工
select name from employee where name like “%A%”;
查询以a为结尾的员工
select name from employee where name like “%A”;
查询第二个字符为m的员工
select name from employee where name like “_m%”;
查询不包含n的员工
select name from employee where name not like “%m%”；
select name from employee where not name like “%m%”；

排序查询 默认升序asc
根据薪资排序
select name ,sal from employee order by salary desc; 降序排列
薪资升序，日期降序
select name from employee order by salary asc,hireday desc;

限制记录查询
查询com列为null 只显示前两条
select * from employee where com is null limit 2;
没有显示两个，查询第二页
select * from employee where com is not null limit 2,2;

统计函数
统计领取奖金的人数
select count(comm) from employee;
select count(*) from employee where comm is not null;
统计平均薪资
select avg(comm) from employee; 会忽略null
计算每月所有薪资
select sum(sal) from employee;
统计最高 最低薪资
select max(sal),min(sal) from employee;

group by
按照job分组
select job,group_concat(name) “所有员工”,average(sal) “平均薪资” from employee group by job;
按照job分组 在所有员工中把员工和薪资都打印出来
select job,group_concat(name,":",sal order by sal desc) names from employee group by job
显示每个分组中雇员的个数
select depto ,group_concat(name) names,count(name) as nums from employee group by depto;

多个字段分组
对员工分组 先按照部门进行分组 再按照雇佣日期进行分组
select deptno,hiredata ,group_concat(name) names,count(name) from employee group by depto,hiredate;

where后面不能放聚合函数，where在聚合前先筛选记录，即在group by 和having前
having在聚合后对组记录进行塞选

选取sal大于2000的员工及其薪资
select name ,sal from employee where sal >2000;

只能用where
select name from employee where sal >2000;
select name from employee having sal >2000;错误
having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行筛选

只能用having
查询平均工资大于2000的部门
select name,deptno,AVG(sal) as ag from employee group by deptno having ag>2000;

having where 区别

1,where 是在列出现之前查询，having是在列出现之后筛选
where是数据从磁盘读入内存时候一条条判断的，having是将所有数据读入内存，根据having条件再将不符合条件的数据删除
2，where只能在group by前，
3，group by是分组语句，想获得where条件选择的，只能使用having语句
4，having不能单独出现 前面必须要有group by ，having筛选必须是根据前面select字段的值进行筛选
5，having可以使用字段别名，where不可以