mysql数据库CURD中常用操作

• 更新 update
  增加一个年龄字段
  alter table user add age int unsigned not null default 0 comment '年龄' after gender;
  更新年龄数据
  update user set age= timestampdiff(year,borthday,now());
  
  条件更新
  update user set name='xiaowang' where name='wang';
• 删除
  delete from user where id=1;
  修改起始主键
  alter table user auto_increment =1;
  清空表
  truncate 表名；
• 查询
  SELECT [DISTRINCT] 表达式1|字段…（*表示所有列）
  FROM 表名 别名 [，数据源2 别名2]
  WHERE 查询条件
  GROUP BY 分组字段1…
  HAVING 分组筛选条件1,…
  ORDER BY 排序字段1 ASC|DESC, 排序字段2 ASC|DESC
  LIMIT 偏移量 数量；

查询数据库
select database()
查询版本
select vsersion()
查询当前时间
select now();
select * from user where id>950;

select name as 姓名,count(1) as数量 from user group by name;
select name 姓名,count(1) 数量 from user group by name;

• 排序查询
  select * from user where name='li' order by id desc;
  
• 分页查询
  limit 显示数量 offset 偏移量（跳过的记录数量）
  limit 偏移量，显示数量
  offset=(page-1)num;在查询语句的最后写
  前俩条 等价于limit 0,2；
  ` select from user order by age desc limit 2;![](https://img.php.cn/upload/image/669/941/590/1616663725280251.png) 从2到3select from user order by age desc limit 2 offset 1;![](https://img.php.cn/upload/image/163/911/787/1616663736497138.png) 年龄最大select from user where age=(select max(age) from user);`
  
• 区间查询

  select * from user where id>=10 and id <=20;
  select * from user where id between 10 and 20;

  
• 集合查询
  select * from user where id in (1,3,5);
  
• like
  查询name以z开始的
  select * from user where name like 'z%';
  
  查询name包含a的
  select * from user where name like '%a%';
  
  查询name以g结尾的
  select * from user where name like '%g';
  
• 聚合函数
  max()最大值,min()最小值，sum（最大值），avg（）平均数，count（）数量,四舍五入round(x,d) ，x指要处理的数，d是指保留几位小数
• 分组查询
  查询男女人数
  select gender ,count(*) from user group by gender;
  
  查询男女平均年龄
  select gender ,avg(age) 平均年龄 from user group by gender;
  
  分组带条件查询,having,不能用where查询
  select gender ,count(*) from user group by gender having gender='male';
  
• 关联查询
  • 内链接
    select a.id,a.title,b.name from articles a, cates b where a.cid=b.cid;
    
    使用inner jion on简化

    select id,title,name from articles a inner join cates b on a.cid=b.cid;
    //简化
    select id,title,name from articles a join cates b using(cid);
    //过滤
    select id,title,name from articles a inner join cates b on a.cid=b.cid where a.cid=1;

    
  • 外连接
    左外连接,左表文章表为主表，查询所有主表的信息，关联表只列出匹配的数据，没有匹配到的为null
    select * from articles a left join cates b on a.cid=b.cid;
    
    右外连接
    select * from articles a right join cates b on a.cid=b.cid;
    
    外连接转内连接，将从表为null的字段过滤掉
    select * from articles a left join cates b on a.cid=b.cid where a.cid is not null;
    
  • 自然连接
    自然连接是内连接的一个特例，前提是关联表中存在同名字段。natural join
    select id,title,name from articles natural join cates;
    

    视图

    创建视图
    create view v_user as select * from user; create view vc_user as select name,age from user;
    查询视图
    select * from vc_user;
    
    更新视图，会同步更新基本表
    update vc_user set age =40 where name='wang';
    
    删除视图
    drop view vc_user;

    索引

    索引应该创建在经常被查询的字段，或者常出现在结果集的字段上
    索引分类：普通索引，主键索引，唯一索引，全文索引。
    create index 索引名称 on 表名（表字段）；
    创建索引

    //普通索引
    create index age on user(age);
    //唯一索引
    create unique index unique_age on user(age);
    //主键索引
    alter table user add primary key id(id);

    查询索引
    show index from user;
    删除索引
    drop index age on user;

    预处理

    防止sql注入攻击，sql语句中的数据只有在执行阶段才会和字段进行绑定。也可以看做是某一条sql语句的模板，可以多次重复调用提高效率
    prepare生成预处理sql语句

    prepare user from 'select * from user where age > ? limit ? ';
    将真实的数据绑定到预处理语句中的占位符上
    set @age =10;
    set @num=5;
    execute user using @age,@num;