sql基础操作（案例用表在最后）

# 查询数据库所有表
show tables ;
# 查询表结构
desc tb_emp;
# 查询建表语句
show create table tb_emp;

# 表操作
# 1.添加字段
alter table tb_emp add qq varchar(11) comment 'QQ';
# 2.修改字段类型
alter table tb_emp modify qq varchar(13) comment 'QQ';
# 3.修改字段名和字段类型
alter table tb_emp change qq QQ varchar(11) comment 'QQ';
# 4.删除字段
alter table tb_emp drop column QQ;
# 5.修改表名
rename table tb_emp to emp;
rename table emp to tb_emp;

# insert into增
insert into tb_emp (username,name,gender,creat_time,updata_time) value ('wuji','站给鸡',1,now(),now());
insert into tb_emp(id, username, password, name, gender, image, job, entrydata, creat_time, updata_time) value (null,'zhiruo','1234','植入',2,'1.jpg',1,'2024-01-02',now(),now());
insert into tb_emp value (null,'zhiruo','1234','植入',2,'1.jpg',1,'2024-01-02',now(),now());

insert into tb_emp (username,name,gender,creat_time,updata_time) value ('wuhji','唯一性',1,now(),now()),('shiwang','失望',1,now(),now());

# update * set修

update tb_emp set name='长三' , updata_time=now() where id=1;
update tb_emp set entrydata='2024-05-05',updata_time=now();

# delete from * where删
delete from tb_emp where id=1;
delete from tb_emp;

# select * from查
select name,entrydata from tb_emp;
select id, username, password, name, gender, image, job, entrydata, creat_time, updata_time from tb_emp;
select * from tb_emp;
select name as 姓名,entrydata as 入职日期 from tb_emp;
select name  '姓 / 名',entrydata  入职日期 from tb_emp;
# distinct去重
select distinct job from tb_emp;

# 条件查询

select * from tb_emp where name='张三';
select * from tb_emp where id<=5;
select * from tb_emp where job is null ;
select * from tb_emp where job is not null ;
select * from tb_emp where password !='123456';
select * from tb_emp where entrydate >= '2000-01-01' and entrydate<='2010-01-01';
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01'and gender=2;
select * from tb_emp where job=2 or job=3 or job=4;
select * from tb_emp where job in (2,3,4);
select * from tb_emp where name like '__';
select * from tb_emp where name like '张%';

#分组查询
    select gender,count(*) from tb_emp group by gender;
#having分组后的过滤条件
select job,count(*) from tb_emp where entrydate <='2015-01-01' group by job having count(*)>=2;
#排序查询
    select * from tb_emp order by entrydate asc;
select * from tb_emp order by entrydate;

select * from tb_emp order by entrydate desc ;

select * from tb_emp order by entrydate ,updata_time desc ;

#分页查询
    select * from tb_emp limit 0,5;
select * from tb_emp limit 5;
#第二页
select * from tb_emp limit 5,5;
#起始索引=（页码-1）*每页展示记录数
select * from tb_emp limit 10,5;

#查询姓名带‘张’，性别男，入职时间在2000-01-01到2015-12-31之间的第一页数据（每页十条）并对update_time降序
select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2001-01-01' and '2012-12-31'
order by updata_time desc
limit 0,10;


#https://echarts.apache.org/zh/index.html 前端统计组件库
#统计男女人数
    select if(gender=1,'男性员工','女性员工')性别,count(*) from tb_emp group by gender;

#统计各职位人数
select
   (case job when 1 then '班主任' when 2 then '讲师' when 1 then '学工主管' when 1 then '教研主管' else '未分配职位' end )职位
,count(*)
from tb_emp group by job;

案例表：


-- 员工管理(带约束)
create table tb_emp (
                        id int unsigned primary key auto_increment comment 'ID',
                        username varchar(20) not null unique comment '用户名',
                        password varchar(32) default '123456' comment '密码',
                        name varchar(10) not null comment '姓名',
                        gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
                        image varchar(300) comment '图像',
                        job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
                        entrydate date comment '入职时间',
                        create_time datetime not null comment '创建时间',
                        update_time datetime not null comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydata, creat_time, updata_time) VALUES
#                    (null, 'jinyong', '123456', '金庸', 1,    '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
                   (null, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
                   (null, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
                   (null,'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
                   (null, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
                   (null, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
                   (null,'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
                   (null, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
                   ( null,'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
                   ( null,'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
                   ( null,'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
                   ( null,'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
                   ( null,'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
                   ( null,'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
                   (null,'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
                   ( null,'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
                   ( null,'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
                   ( null,'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
                   ( null,'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
                   (null,'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
                   (null,'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
                   ( null,'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
                   ( null,'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
                   ( null,'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
                   (null, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
                   ( null,'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');