mysql学习-DML语句和DQL语句

DML语句: 增删改表中数据(很重要)

添加数据

• 语法:

  • insert into 表名(列名1, 列名2,…列名n) values(值1, 值2,…,值n);
  • 应该在插入单行的时候使用VALUES，在插入多行的时候使用VALUE, 这样速度比较快一点,不过两个都是对的.
    • 插入多行是这样的: insert into student(name, age) value(‘a’, 1)(‘b’,2);

• 注意事项:

  • 列名和值要一一对应.
  • 如果表名后不定义列名,则默认给所有列添加值.
  • 字符串类型需要使用引号引起来,用单引号双引号都可以.

删除数据

• 语法:

  • delete from 表名 [where 条件];
  • 如: delete from student where id = 1;

• 注意:

  • []表示这个可选部分,可以写也可以不写.
  • 如果不加where条件,则删除表中所有记录,和truncate效果一致.
  • truncate table student; 表示删除表,然后在创建一个一模一样的空表,一般我们理解为删除所有数据但是保留表结构.
  • 不推荐用delete删除表中所有数据,因为有多少条语句就会执行多少次delete语句,效率很低,但是如果我们用truncate, 它就只会执行两条语句,先drop表,然后create表,效率快很多.

修改数据

• 语法:

  • update 表名 set 列名1 = 数据1,列名2 = 数据2,…列名n = 数据n [where 条件];

• 注意:

  • []表示这个可选部分,可以写也可以不写.
  • 不加条件就会修改所有记录.

DQL语句: 查询表中的记录

• select * from 表名;

查询语句(单表)

• 语法:

    select
    	字段1,字段2... 
    from 
    	表名 
    where 
    	条件1, 条件2...
    group by
    	分组字段
    having
    	分组之后的条件
    order by
    	排序字段1,排序字段2
    limit
    	开始索引,每页条数
  

基本查询

• 多个字段查询
  • 例: 查询student表中的姓名和年龄
    • select name, age from student;
• 去除重复
  • 例: 查询student表中所有姓名,去除姓名重复的.
    • select distinct name from student;
  • 例: 查询student表中所有姓名,年龄,去除姓名和年龄重复的.
    • select distinct name, age from student;
  • 这里要注意,这样写要保证distinct后面的字段必须都相同才会被去除
• 计算列(数值型的加减乘除,字符型的没意义)
  • 例: 计算student列中每个学生的数学和英语的分数以及两门课加起来的总分
    • select name, math, english, math + english from student;
    • 这里会有一个问题,就是比如我数学有分数,但是英语是个null,总分就会是null
  • 例: 计算student列中每个学生的数学和英语的分数以及两门课加起来的总分(排除null影响)
    • select name, math, english, ifnull(math,0) + ifnull(english,0) from student;
• 起别名
  • • 例: 计算student列中每个学生的数学和英语的分数以及两门课加起来的总分(排除null影响以及起别名)
      • select name, math, english, ifnull(math,0) + ifnull(english,0) as total from student;
      • as可以省略

条件查询

• where子句后跟条件
• 运算符
  • >, <, <=, >=, =, <>(不等号), !=.
    • 例: 查询student表中分数大于60的人的信息.
      • select * from student where score > 60.
  • between...and....
    • 例: 查询student表中分数在60-100的人的信息(包括60和100),
      • select * from student where score between 60 and 100;
      • select * from student where score >= 60 and score <= 100;
  • in(集合).
    • 例: 查询score为60,70的人的所有信息.
      • SELECT * FROM student WHERE score = 60 OR score = 70;
      • SELECT * FROM student WHERE score IN (60,70);
  • is null.
    • 注意两个单词之间有空格,以及注意区分和ifnull()的区别.
      • is null是用来判断是否某个字段.
      • ifnull是用来设置当碰到空值的时候如何处理.
      • is null中间有空格而ifnull中间没有.
    • 例: 查询score为null的人的信息
      • select * from student where score = null;-- 这样什么都查不出来,即使用score为null的也不会显示任何信息,因为null不能用=(!=)判断.
      • 正确写法: select *from student where score is null;
    • 例: 查询score不为null的人的信息
      • select * from student where score is not null;
  • and 或 &&.
  • or 或 ||.
  • not 或 !.

排序查询

• 语法: order by 子句
  • order by 排序字段1 排序方式1, 排序字段2 排序方式2…
  • 例1:查询student表中所有信息,按照分数升序排序
    • select * from student.
  • 例2:查询student表中的所有信息,按照分数升序,如果分数一样,则按照年龄升序.
    • SELECT * FROM student ORDER BY score ASC, age ASC;
• 排序方式:
  • 升序: asc, 不指定排序方式就默认升序.
  • 降序: desc.
• 注意:
  • 如果有多个排序条件,只有当第一个条件的值一样时,才会去判断第二个条件,以此类推…

聚合函数

• 将一列数据作为一个整体进行纵向的计算.
• count: 计算这一列的总个数.
  • 例1: 计算student表中有多少条记录.
    • select count(*) from student;
  • 例2: 计算student表中age这一列一共有多少条记录.
    • select count(age) from student;
  • 例3: 计算student表中description这一列一共有多少记录(其中部分行为null,我们不能忽略null).
    • 为了保证可以计算为空值的列,我们可以判断一下,如果为null,就当作为’未知’这个值来判断,这样子这个null的行也会被算进去,但是原表中的数据不是改变.
    • select count(ifnull(description, ‘未知’)) from student;
    • 我们也可以这样子,选择一个非空的列(例如id这个主键列)计算,或者写成count(*),只要这一条记录有一个不为null就会被计算(不过这个不推荐,选择主键列更好).
    • select count(id) from student;
• max: 计算这一列的最大值.
  • 例: 找出student表中score的最大值
    • SELECT MAX(score) FROM student;
• min: 计算这一列的最小值.
  • 例: 找出student表中score的最小值
    • SELECT MIN(score) FROM student;
• sum: 计算这一列的和.
  • 例: 计算student表中score列的总和.
    • SELECT SUM(score) FROM student;
• avg: 计算这一列的平均值.
  • 例: 计算student表中score列的平均值
    • SELECT AVG(score) FROM student;
• 注意: 所有聚合函数的计算会排除null值的列,也就是说,比如查询某一列的总数,null值得那一行会被忽略.
  • 都可以通过**IFNULL()**函数解决,以上面的平均值为例
    • SELECT AVG(IFNULL(score, 0)) FROM student.这样就会把null的当作0处理,就会被计算在其中了.

分组查询

• 语法: group by 分组字段;
  • 例: 将student表按照gender分组,分别查询男同学和女同学的平均年龄,要防止为空.
    • SELECT AVG(IFNULL(age, 0)) FROM student GROUP BY gender;
  • 例: 将student表按照gender分组,分别查询男女同学的平均分以及人数,其中分数低于60分的不参与分组.
    • SELECT gender, AVG(IFNULL(score, 0)), COUNT(id) FROM student WHERE score > 60 GROUP BY gender;
    • 这就表示如果你的score小于60,你就不会参与分组过程,就相当于这个表中没有这些记录一样,所以count也不会把它计算在内.
  • 例: 将student表按照gender分组,分别查询男女同学的平均分以及人数,其中分数低于60分的不参与分组,同时要使得分组之后显示的数据中,count(id)计算的人数都超过一个人.
    • SELECT gender, AVG(IFNULL(score, 0)), COUNT(id) FROM student WHERE score > 60 GROUP BY gender HAVING COUNT(id) > 1;
    • SELECT gender, AVG(IFNULL(score, 0)), COUNT(id) total FROM student WHERE score > 60 GROUP BY gender HAVING total > 1;
    • 这就表示在满足要求的前提下,给count(id)加了个别名total.
• 注意:
  • 分组之后查询的字段应该为分组字段或者聚合函数,其它的没有意义.
    • 例: SELECT gender FROM student GROUP BY gender;-- 输出’男’和’女’.
    • 例: SELECT score FROM student GROUP BY gender;-- 这样写只会输出第一个男和第一个女的分数.
    • 例: 在student中按照性别分组,然后分别计算其score的平均值(防止null值).
      • SELECT sex,AVG(IFNULL(score, 0)) FROM student GROUP BY gender;-- 输出性别以及对应的平均分.
    • 在student中按照性别分组,然后分别计算其score的平均值和男女同学各自的人数(防止null值).
      • SELECT gender,AVG(IFNULL(score, 0)), COUNT(id) FROM student GROUP BY gender;–输出性别以及对应的平均分以及男女人数.
  • 分组后查询的字段可以有多个,可以是多个聚合函数和分组字段结合.
  • where和having的区别:
    • where在分组之前进行限定,如果不满足where中的条件,则不参与分组.
    • having在分组之后进行限定,如果不满足结果,则不会被查询出来.
    • where后不可以跟聚合函数.
    • having后可以进行聚合函数的判断,也可以跟字段名.

分页查询

• 语法: limit 开始的索引 每页查询的条数;

• 例: 在student表中,每页显示3条记录,而且当前是第一页.

  • select * from student limit 0, 3;

• 例: 在student表中,每页显示3条记录,而且当前是第二页.

  • select * from student limit 3, 3;

• 例: 在student表中,每页显示3条记录,而且当前是第三页.

  • select * from student limit (3 - 1) * 3, 3;

• 公式: 开始的索引 = (当前页码-1) * 每页查询的条数.

• 注意:

  • 索引从0开始.
  • 注意这里是索引,不是id,和id没有关系的,指定开始的索引之后,它就会从指定的索引(比如索引0就表示第一行)开始输出,输出查询的条数,不管你的id是否是连续的,比如说我索引从1开始,查询3个,他就会从表中第二行开始输出,输出三行.
  • limit进行分页操作是一个"方言",每个数据库分页的关键字不一样,limit关键字只能在mysql中用.

模糊查询(like)

• like的占位符:
  • _: 表示单个任意字符.
  • %: 表示多个任意字符.
• 例: 查询student表中姓樊的同学的信息
  • select * from student where name like ‘樊%’;
• 例: 查询第二个字是磊的同学的信息
  • select * from student where name like ‘_磊%’;
• 例: 查询姓名是三个字的同学的信息(三个_就可以了)
  • select * from student where name like ‘___’;
• 例: 查询姓名中包含李的人
  • selcet * from student where name like ‘%李%’;