MySql练习（1）

        MysqlExercise1

select database();

create table emp_info(
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age varchar(10) comment '年龄',
    id varchar(7) comment '编号',
    entryDate date comment '入职时间',
    address varchar(20) comment '籍贯'
) comment '员工信息表';

alter table emp_info change age age tinyint unsigned comment '年龄';

insert into emp_info values('张三', '男', 18, 1, '2000-01-01', '重庆'),
                           ('李四', '男', 28, 2, '2002-02-01', '重庆'),
                           ('王五', '男', 38, 3, '2002-03-01', '重庆'),
                           ('赵六', '男', 48, 4, '2050-03-01', '重庆'),
                           ('钱七', '男', 8, 5, '2008-04-01', '四川'),
                           ('周八', '男', 78, 6, '2003-06-01', '四川'),
                           ('王小明', '男', 22, 7, '2100-04-01', '四川'),
                           ('李大明', '男', 35, 8, '2005-11-01', '北京'),
                           ('张伟', '男', 65, 9, '2003-03-01', '北京'),
                           ('小芳', '女', 15, 10, '2007-02-01', '北京'),
                           ('小花', '女', 98, 11, '2008-09-01', '上海');

insert into emp_info values('杰伦', '男', 35, 8, '2005-11-01', '北京');

update emp_info set age = '16' where name = '杰伦';

select gender, count(*) from emp_info group by gender;
-- 分组之前过滤，用where；分组之后过滤用having
select gender, count(*) from emp_info group by gender;
select address, count(*) from emp_info where age < 45 group by address having count(address) >= 3;

-- 排序 asc：升序 desc：降序
-- 支持多字段排序，若第一个字段值相同，那么根据第二个字段排序

-- 根据年龄升序（默认升序）
select * from emp_info order by age asc;
-- 根据年龄降序
select * from emp_info order by age desc;
-- 根据入职时间排序
select * from emp_info order by entryDate asc, age desc;

update emp_info set name = 'zhangSan' where id = 1;
update emp_info set name = '张三' where name = 'zhangSan';
update emp_info set name = '张san', gender = '女' where id = 1;

-- 对表内数据全部进行修改，无需用where条件
update emp_info set entrydate = '2001-11-01';

delete from emp_info where id = 8;

update emp_info set name = null where name = '张san';

-- select查询表中多个字段
select name, gender, age from emp_info;

-- select查询整张表（使用通配符*）
-- 尽量少用通配符*，不直观，而且影响效率
select * from emp_info;

-- 查询还可以为字段起别名
select name as '姓名' from emp_info;

-- 查询不重复的记录（distinct）
select distinct id from emp_info;

-- 条件查询
select name from emp_info where age = 18;
select name from emp_info where age > 18;
select name from emp_info where age >= 18;

select name from emp_info where name is null;
select name from emp_info where name is not null;

-- 使用between and进行查询，注意，between后面跟最小值，and后跟最大值
select name from emp_info where age between 15 and 20;
-- between and的顺序不能调换（这么写没有用）
select name from emp_info where age between 20 and 15;

select name from emp_info where age = 18 or age = 19;
select name from emp_info where age in (18, 19);

-- 聚合函数，所有的null不参与计算
-- count 统计字段的条数
select count(id) from emp_info;
-- avg 求字段的平均数
select avg(age) from emp_info;
-- max 找到字段中的最大值
select max(age) from emp_info;
-- min 找到字段中的最小值
select min(id) from emp_info;
-- sum 求字段中数据的和
select sum(age) from emp_info;
-- 聚合函数可以搭配where使用
select sum(age) from emp_info where name is not null;

-- 分组查询
select * from emp_info;
select gender, count(id) from emp_info group by gender;
select gender, avg(age) from emp_info group by gender;
select gender, avg(age) from emp_info where age >= 19 group by gender;