mysql学习笔记 0816

单表查询

查询所有列：

select * from 表名;
select * from student;

查询指定的列：

select id,`name`,age,gender from student;
select id,`name`,age from student;

补充：开发中，严禁使用select *。

如果表中有完全重复的记录只显示一次，在查询的列之前加上distinct。

select DISTINCT `name` from book;

列运算

select id,`name`,age/10 from student;

注意：我们写的所有的查询语句，最终执行的结果，都是生成一张虚拟表。

select id,`name`,sal+1000 from employee;

注意：

1. null值和任何值做计算都为null，需要用到函数ifnull()函数。select IFNULL(sal,0) + 1000 from employee;如果薪资是空，则为0。

2. 将字符串做加减乘除运算，会把字符串当0处理。

别名

我们可以给列起【别名】，因为我们在查询过程中，列名很可能重复，可能名字不够简洁，或者列的名字不能满足我们的要求。

select id `编号`,`name` `姓名`,age `年龄`,gender `性别` from student;
select id as `编号`,`name` as `姓名`,age as `年龄`,gender as `性别` from student;

条件控制

条件查询：在后面添加where指定条件

select * from student where id = 3;
select * from student where id in (1,3,5);
select * from student where id BETWEEN 6 and 7 or age > 20;

模糊查询：我想查询所有姓张的。

select * from student where `name` like '张%';
select * from student where `name` like '张_';
select * from student where `name` like '%明%';
select * from student where `name` like '_明_';

通配符：_下划线代表一个字符，%百分号代表任意个字符。

排序

• 升序

  select * from student ORDER BY age ASC;
  -- ASC是可以省略

• 降序

  select * from student ORDER BY age DESC;

• 使用多列作为排序条件：当第一个排序条件相同时，根据第二列排序条件进行排序（第二列如果还相同，.....）

  select * from student ORDER BY age asc,id desc;

举例：

创建一张用户表，id，username，password。

几乎所有的表都会有两个字段，create_time，update_time。

几乎所有的查询都会按照update_time降序排列。

聚合函数

count

查询满足条件的记录行数，后边可以跟where条件。

如果满足条件的列值为空，不会进行统计。

如果我们要统计真实有效的记录数，最好不要用可以为空列。

select count(列名) from 表名;

select count(id) from student where gender='男';

max

select max(age) from student where gender='女';

min

select MIN(age) from student where gender='男';

sum

select sum(age) from student where gender='男';

avg

select avg(score) from scores where c_id = 3;

分组查询

顾名思义：分组查询就是将原有数据进行分组统计。

select 分组列名,聚合函数1,聚合函数2... from 表名 group by 该分组列名;

分组要使用关键词group by，后面可以是一列，也可以是多个列，分组后查询的列只能是分组的列，或者是使用了聚合函数的其他的列，剩余列不能单独使用。

-- 根据性别分组，查看每一组的平均年龄和最大年龄
select gender,avg(age),max(age) from student group by gender;
-- 根据专业号分组，查看每一个专业的平均分
select c_id,avg(score) from scores group by c_id;

我们可以这样理解：一旦发生了分组，我们查询的结果只能是所有男生的年龄平均值、最大值，而不能是某一个男生的数据。

分组查询前，可以通过关键字【where】先把满足条件的人分出来，再分组。

select 分组列,聚合函数1... from 表名 where 条件 group by 分组列;

select c_id,avg(score) from scores where c_id in (1,2,3) group by c_id;

分组查询后，也可以通过关键字【having】把组信息中满足条件的组再细分出来。

select 分组列,聚合函数1... from 表名 where 条件 group by 分组列 having 聚合函数或列名（条件）;

select gender,avg(age),sum(age) `sum_age` from student GROUP BY gender HAVING `sum_age` > 50;

面试题：where和having的区别？

1. where是写在group by之前的筛选，在分组前筛选；having是写在group by之后，分组后再筛选。

2. where只能使用分组的列作为筛选条件；having既可以使用分组的列，也可以使用聚合函数列作为筛选条件。

分页查询

limit字句，用来限定查询结果的起始行，以及总行数。

limit是mysql独有的语法。

select * from student limit 4,3;
select * from student limit 4;

• 如果只有一个参数，说明从起始位置查找4条记录。

• 如果两个参数，说明从第4行下一行，向后查找3条记录。

面试题：

• MySQL：limit

• Oracle：rownum

• SqlServer：top

分析：

student表中有10条数据，如果每页显示4条，分几页？3页

3页怎么来的？(int)(Math.ceil(10 / 4));

显示第一页的数据：select * from student limit 0,4;

第二页：select * from student limit 4,4;

第三页：select * from student limit 8,4;

一个问题：我想要判断在student表中有没有叫"小红"的这个人？

1.0版本

select * from student where name = '小红';
select id from student where name = '小红';

2.0版本

select count(id) from student where name = '小红';

3.0版本

select id from student where name = '小红' limit 1;

注意：Limit子句永远是在整个的sql语句的最后。

多表查询

多表连接的方式有四种：

• 内连接

• 外连接**

• 全连接

• 子查询(见下篇)

内连接

在我们刚才的sql当中，使用逗号分隔两张表进行查询，mysql进行优化默认就等效于内连接。

使用【join】关键字，使用【on】来确定连接条件。【where】只做筛选条件。

使用【join】关键字，使用【on】来确定连接条件。【where】只做筛选条件。

SELECT
    t.*,
    c.* ,
    sc.*
FROM
    teacher t
    INNER JOIN course c ON c.t_id = t.id
    INNER JOIN scores sc ON sc.c_id = c.id;

外连接（常用）

内连接和外连接的区别：

• 对于【内连接】的两个表，如果【驱动表】在【被驱动表】找不到与之匹配的记录，则最终的记录不会出现在结果集中。

• 对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中。针对不同的【驱动表】的位置，有分为【左外连接】和【右外连接】。

• 对于左连接，左边的表为主，左边的表的记录会完整的出现在结果集里。

• 对于右连接，右边的表为主，左边的表的记录会完整的出现在结果集里。

外连接的关键字【outter join】，也可以省略outter，连接条件同样使用【on】关键字。

左连接

SELECT
    t.*,
    c.* 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id;

右连接

SELECT
    t.*,
    c.* 
FROM
    course c
    RIGHT JOIN teacher t ON t.id = c.t_id;

全连接

mysql不支持全连接。oracle支持全连接。

SELECT
    * 
FROM
    teacher t
    FULL JOIN course c ON c.t_id = t.id;

我们可以通过一些手段来实现全连接的效果

SELECT
    t.*,
    c.* 
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id
UNION
SELECT
    t.*,
    c.* 
FROM
    teacher t
    RIGHT JOIN course c ON t.id = c.t_id