Fork me on GitHub

1-7-2-MySQL数据库增删改查学习

发表于 |

1. 增

  1. 添加一整条数据

    1
    insert into my_test values(0,'小明',0,'1999-1-1',0);

  2. 添加不完整数据

    1
    insert into my_students(name) values('小家电');

  3. 添加多条数据

    1
    insert into my_test(name) values('阿姨'),('神雕'),('郭襄');

2. 删

  1. 物理删除
    1
    delete from stus where id=5;

3. 改

  1. 修改某条数据
    1
    update students set birthday='1990-2-2' where id=2

4. 查

  1. 查看列表所有数据

    1
    select * from my_test;

  2. 筛选数据

    1
    select * from stus where isdelete=0;

  3. 逻辑与运算与或非 and , or , not

    1
    select * from stus where name='小米' and isdelete=0;

  4. 模糊查询

    1
    2
    # %表示任意多个字符
    select * from stus where name like '小%';

image.png

1
2
# 下划线数量表示占位符数量
select * from stus where name like '小_'；

  1. 范围查询
    1
    select * from stus where id in (1,4,6);

image.png

1
2
# 编号3-8的男生
select * from stus where id between 3 and 8 and gender=1;

image.png

  1. 空判断

    1
    2
    3
    # null 与 ‘’ 是不同的
    select * from stus where birthday is null;
    select * from stus where birthday is not null;

  2. 优先级

    1
    2
    # 小括号>not>比较运算符>逻辑运算符
    # and > or

4.1 聚合

  1. 求总数 count()

    1
    select count(*) from stus where isDelete=0;

  2. 最大值 max()

    1
    select max(id) from stus;

  3. 最大值 min()

    1
    select min(id) from stus;

  4. 平均值 avg()

    1
    select avg(id) from stus;

  5. 求和 sum()

    1
    select sum(id) from stus where gender=1;

4.2 分组

  1. 统计男女生人数

    1
    select gender,count(*) from stus group by gender;

  2. 分组后筛选

    1
    2
    3
    select gender,count(*) from stus group by gender having gender=0;
    select gender,count(*) from stus group by gender having count(*)>2;
    select gender,count(*) as rs from stus group by gender having rs>2;

image.png

4.3 排序 select * from 表 order by 列1 asc|desc,列2 asc|desc,...

  1. 默认由小到大
    1
    2
    # 男生按id降序排列
    select * from stus where isDelete=0 and gender=1 order by id desc;

4.4 分页

语法 select * from 表 limit start,count; 从start开始，获取count条数据，start索引从0开始

1
select * from stus where isDelete=0 limit 1,3;

image.png
每页显示m条数据，当前是n页，n从1开始

1
select * from stus where idDelete=0 limit (n-1)*m,m;