re_mysql_20221209

-- navicat

SHOW TABLES;

DESC tb_user;

SHOW CREATE TABLE tb_user;
/*
CREATE TABLE `tb_user` (
  `id` int(11) DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
*/

DROP TABLE IF EXISTS emp2;

CREATE TABLE employee(
	id INT COMMENT '编号',
	workno VARCHAR(10) COMMENT '工号',
	name VARCHAR(10) COMMENT '姓名',
	gender CHAR(1) COMMENT '性别',
	age TINYINT UNSIGNED COMMENT '年龄',
	idcard CHAR(18) COMMENT '身份证号',
	entrydate date COMMENT '入职时间'
) COMMENT '员工表';


ALTER TABLE employee ADD nickname VARCHAR(20) COMMENT '昵称';

ALTER TABLE employee CHANGE nickname username VARCHAR(30) COMMENT '用户名';

ALTER TABLE employee RENAME TO tb_emp;

-- idea


show tables;

desc tb_emp;

insert into tb_emp(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');

select * from tb_emp;

truncate table tb_emp;


insert into tb_emp(id,workno,name,gender,age,idcard,entrydate)
values(2,'2','Itcast','男',-1,'123456789012345678','2000-01-01');


insert into tb_emp
values (2,'2','Itcast','男',55,'123456789012345678','2000-01-01', 'hm');


insert into tb_emp
values (3,'3','Itcast','男',55,'123456789012345678','2000-01-01', 'hm'),
       (4,'4','Itcast','男',55,'123456789012345678','2000-01-01', 'hm'),
       (5,'5','Itcast','男',55,'123456789012345678','2000-01-01', 'hm');



update tb_emp
set name = "itheima"
where id = 1;

update tb_emp set entrydate = '2022-12-09';

update tb_emp set name='秋荷', gender='女', age=28 where id=2;

delete from tb_emp where username='1';

-- 去重
select distinct gender as "?" from tb_emp;


select * from tb_emp where username is null;

select * from tb_emp where username is not null;

select gender, count(*) from tb_emp group by gender ;


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

select * from tb_emp order by age asc, entrydate desc;

select * from tb_emp limit 0,2;
select * from tb_emp limit 2;
select * from tb_emp limit 2,2;


select sum(score_chinese)/count(*) as avg_chinese from tb_score; -- 88.5

-- select sum(score_chinese)/count(stuNo) as avg_chinese from tb_score; -- 118

select avg(score_chinese) as avg_chinese from tb_score; -- 88.5


select * from mysql.user;