MySQL数据操作

DML

添加数据

0. 给指定列添加数据

     insert into 表名(列名1,列名2,...) values(值1,值2,...);
   

1. 给全部列添加数据

     insert into user values(1,'小诗诗','root');
   

2. 批量添加数据

     insert into 表名(列名1,列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
     insert into user values(1,'小惠惠','123456'),(1,'小香香','1234')...;
   

修改数据

0. 修改表数据

     update 表名 set 列名1=值1,列名2=值2,...[where 条件];
     update user set password='gzca' where username='小香香';
   

删除数据

0. 删除表数据

     delete from 表名 [where 条件];
     delete from user where username='小诗诗';
   

DQL

查询数据

0. 查询表数据

    select * from 表名 
    where 条件 
    group by 分组字段 
    having 分组后条件 
    order by 排序字段 
    limit 分页限定
   

基础查询

0. 查询两列

 select name,age from user;

2. 查询所有列

   select * from user;

3. 查询结果去重 distinct 取别名

   select distinct password as 密码 from user;

条件查询

0. 查询id大于0的用户的用户名

 select username as 名字 from user WHERE id> 0;
 #and
 select username as 名字 from user WHERE id >= 0 and id =< 2;
 #between ... and
 select username as 名字 from user WHERE id between 0 and 2;
 #or   in
 select username as 名字 from user WHERE id = 0 or id = 1 or id = 2;
 select username as 名字 from user WHERE id in (0,1,2);
 #查询空
 select username as 名字 from user WHERE id is null;
 select username as 名字 from user WHERE id is not null;

模糊查询 like

0. _ 单个任意字符
1. % 任意个数自读

 #名字是小开头的信息
 select username as 名字 from user WHERE username like '小%';
 #名字第二个是诗的信息
 select username as 名字 from user WHERE username like '_诗%';
 #名字中有诗的信息
 select username as 名字 from user WHERE username like '%诗%';

排序查询 order by

0. 排序查询的语法

 #ASC 升序排列(默认)
 select username as 名字 from user order by id;
 #DESC 降序排列
 select id,username as 名字 from user order by id DESC;
 select id,username as 名字 from user order by id ASC,username DESC;

分组查询 group by

0. 排序查询的语法

 select id,AVG(age) as 平均年龄,count(*) from user where id is not null group by id;
 select id,AVG(age) as 平均年龄,count(*) from user where id is not null group by id HAVING count(*)>2;

聚合函数

0. 概念

   将一列数据作为整体，进行纵向计算

   注意：聚合函数都不会将null加入计算

   0 小诗诗 null 小惠惠 1 香香 1 惠惠 1 小香香 1 aa 1 ab 1 ac

   avg(id)

   0.8571

1. 分类

   函数名	功能
   count(列名)	统计数量（一般选用不为null的列）
   max(列名)	最大值
   min(列名)	最小值
   sum(列名)	求和
   avg(列名)	平均值

2. 代码

 select max(id) from user;
 select count(username) as 人数 from user;

分页查询 limit

0. 代码

 #从0开始，查询3条数据
 select * from user limit 0,3;
 #每页3条数据，查第1页数据
 select * from user limit 0,3;
 #每页3条数据，查第2页数据
 select * from user limit 3,3;
 #每页3条数据，查第3页数据
 select * from user limit 6,3;

\