数据库造神计划第八天---增删改查（CRUD）（4）

 INSERT INTO table_name [(column [, column ...])] SELECT ...

# 创建测试表，并构造数据
CREATE TABLE t_recored (id int, name varchar(20));
# 插⼊测试数据
INSERT INTO t_recored VALUES
 (100, 'aaa'),
 (100, 'aaa'),
 (200, 'bbb'),
 (200, 'bbb'),
 (200, 'bbb'),
 (300, 'ccc');
# 查看结果
select * from t_recored;

# 创建⼀张新表，表结构与t_recored相同
create table t_recored_new like t_recored;
# 新表中没有记录
selec * from t_recored_new;
# 原表中的记录去重后写⼊到新表
insert into t_recored_new select distinct * from t_recored;
# 查询新表中的记录，实现去重
select * from t_recored_new;

# 新表与原来重命名
rename table t_recored to t_recored_old, t_recored_new to t_recored;
# 查询重命名后表中的记录，实现需求且原来中的记录不受影响
select * from t_recored;

select * from t_recored_old;

#创建表结构
CREATE TABLE exam (
    id BIGINT,
    name VARCHAR(20) COMMENT '同学姓名',
    chinese float COMMENT '语⽂成绩',
    math float COMMENT '数学成绩',
    english float COMMENT '英语成绩'
);

#插⼊测试数据 
INSERT INTO exam (name, chinese, math, english) VALUES
    (1, '唐三藏', 134, 98, 56),
    (3, '猪悟能', 176, 98, 90),
    (4, '曹孟德', 140, 90, 67),
    (5, '刘⽞德', 110, 115, 45),
    (6, '孙权', 140, 73, 78),
    (7, '宋公明', 150, 95, 30)，
    (8, '张飞', 54 ,0 ,NULL);

# 使⽤ * 做统计
select count(*) from exam;

# 使⽤常量做统计
select count(1) from exam;

# 使⽤指定列做统计
select count(math) from exam;

# 查看表中的记录
select * from exam;

# NULL 的数据不会计⼊结果
select count(english) from exam;

# 加⼊where条件
select count(chinese) from exam where chinese < 10;

 select sum(math) from exam;

# 值为NULL的列不参与统计
select sum(english) from exam;

select sum(name) from exam;

# NULL值不参与统计
select avg(english) from exam;

select avg(chinese + math + english) as 总分 from exam;

select max(english) from exam;

select min(math) from exam where math > 70;

# 可以使⽤多个聚合函数
select max(math), min(english) from exam;

SELECT {col_name | expr} ,... , aggregate_function (aggregate_expr)
    FROM table_references
    GROUP BY { col_name | expr}, ...
    [H AVING where_condition]

 drop table if exists emp;
 create table emp (
     id bigint primary key auto_increment,
     name varchar(20) not null,
     role varchar(20) not null,
     salary decimal(10, 2) not null
 );

 insert into emp values (1, '⻢云', '⽼板', 1500000.00);
 insert into emp values (2, '⻢化腾', '⽼板', 1800000.00);
 insert into emp values (3, '张三', '程序员', 10000.00);
 insert into emp values (4, '李四', '程序员', 12000.00);
 insert into emp values (5, '王五', '程序员', 9000.00);
 insert into emp values (6, '赵六', '程序员', 8000.00);
 insert into emp values (7, '孙悟空', '游戏⻆⾊', 956.8);
 insert into emp values (8, '猪悟能', '游戏⻆⾊', 700.5);
 insert into emp values (9, '沙和尚', '游戏⻆⾊', 333.3);
 
select * from emp;

select role, count(*) from emp group by role;

select role, ROUND(avg(salary),2) as 平均⼯资, ROUND(max(salary),2) as 最⾼⼯资, ROUND(min(salary),2) as 最低⼯资 from emp group by role;

select role, avg(salary) from emp group by role having avg(salary) < 1500;