SQL查询

# 插入数据
insert into  employee(id, workno, name, gender, age, idcard, entrydate)
values (1,'1','itcat','男',18,'431228200008261522','2020-01-01');

insert into  employee(id, workno, name, gender, age, idcard, entrydate)
values (2,'2','itcat2','男',11,'431228200008261512','2020-01-02');

select * from employee;

insert into  employee(id, workno, name, gender, age, idcard, entrydate)
values (3,'3','itcat3','男',12,'431228200008261514','2020-01-03'),
(4,'4','itcat4','男',21,'431228200008261513','2020-01-09');

insert into  employee(id, workno, name, gender, age, idcard, entrydate)
values (5,'5','itcat5','男',17,'431228200008261514','2020-01-03'),
(6,'6','itcat6','男',26,'431228200008261513','2020-01-09');


insert into  employee(id, workno, name, gender, age,  entrydate)
values (8,'8','itcat8','男',19,'2020-01-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (9,'9','小明','男',19,'2020-01-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (10,',10','小红','男',17,'2020-02-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (11,',11','小红1','男',17,'2020-02-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (14,',14','小红1','男',17,'2020-02-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (15,',15','小红1','男',17,'2020-03-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (14,',14','小红1','男',17,'2020-02-01');

insert into  employee(id, workno, name, gender, age,  entrydate)
values (16,',16','小红1','男',17,'2020-03-01');

# --更新数据
update employee set name='itc' where id=1;
update employee set gender='女', idcard='4312282000011111' where id=1;
update employee set entrydate='2018-01-01';


delete from employee where gender='女';
-- 删除所有数据
delete from employee;


select name,employee.workno,employee.age from employee;

select employee.idcard as '身份证号' from employee;

select employee.entrydate from employee;
select distinct employee.entrydate from employee;

select * from employee where idcard is null;
select employee.age from employee where age between 12 and 21;
select * from employee where age = 12 or age=17 or age =19;
select age from employee where age in (12,17,19);

# 模糊匹配 like _ %
select employee.name from employee where name like '__';

select  employee.name from employee where name like 'i%';

select employee.name from  employee where name like '%红%';

select employee.name from employee where name like '_____2';

-- 聚合函数
select count(name) from employee;
select avg(employee.age) as '平均年龄' from employee;
select max(employee.age) from employee;
select max(employee.age) from employee;
select min(employee.age) from employee;
select sum(employee.age) from employee where entrydate='2018-01-01';

update employee set gender = '女' where id=2;

-- 分组查询
select employee.gender from employee;
select gender,max(age) from employee group by gender;
# 返回每个性别分组gender为2  count(*)每个分组中的数量 就是 有几个分组 每个分组中有多少个
select gender,count(*) as '男女员工数量' from employee group by gender;
select entrydate,count(*) from employee group by entrydate;

select gender,avg(age) from employee group by gender;

select employee.entrydate,count(*),avg(age) as 平均年龄 from employee where employee.gender='男' group by employee.entrydate having  count(*)>=2;

select * from employee order by entrydate,age desc ;

# 第十条数据
select * from employee limit 10,10;