一文带你复习MySQL单表CRUD

create table a(
  name varchar(10)
)

create table b(
  name varchar(10),
  height decimal(5,2)
)

create table c(
  id int,
  name varchar(20),
  age tinyint unsigned
)

insert into c values(0,'tony',18)

insert into c (id,name) values(0,'tim')

insert into c (id,name) values(1,'tom'),(2,'bob')

update a set name = 'bob' where name = 'tony'

update c set age = 50

update c set age = age + 1 where id > 10

delete from c where id = 6

delete from c

truncate table c

delete和truncate的区别：

速度上truncate更快，但是如果只想删除部分数据只能选择delete，delete是删除记录而truncate会删除表再新建一张。

drop table a

drop table if exists b

select distinct sex from students

select * from a order by id asc, num desc

select count(*) from students

select count(distinct sex) from students

# 分组的意义就是配合聚合函数使用
select sex,count(*) from students group by sex

select class,count(*),avg(age),max(age),min(age)
from students
where class != '3'
order by class desc

# having是分组之后再筛选 必须出现在groupby之后
select class,count(*)
from students
group by class
having avg(age) > 30

select * from limit 0,10