MySQL基础（三）

简介

• 本篇详细介绍MySQL增删改的常见问题和查询操作
• 很多细节在代码注释里

新增数据

• 主键冲突问题

-- 字段：name/room（主键）
insert into my_class values('mysql01', '129');
-- 处理主键冲突 Duplicate Key，使用新主键
insert into my_class values('mysql02', '129') on duplicate key update room='130';
-- 会显示 Query OK, 2 rows affected，由于更新引起的

-- 第二种方式:replace into
-- 有冲突就覆盖
replace into my_class values('mysql03', '130');-- Query OK, 2 rows affected
-- 如果没有冲突就直接插入
replace into my_class values('mysql02', '131');-- Query OK, 1 row affected

创建表

• 蠕虫复制

-- 简单复制
create table my_copy like my_class;	-- 只会复制表结构
-- 蠕虫复制：从自己的表中，先查出数据，然后将数据新增
insert into my_copy select * from my_class; -- 先从已有表拷贝
insert into my_copy select * from my_copy;	-- 这一步就叫蠕虫复制
insert into my_copy select * from my_copy;	-- 我们会发现数据是成倍增加的
insert into my_copy select * from my_copy;	-- 如果主键冲突则不复制主键即可
...
-- 就这么low

• 一般用于表的稳定性测试

更新数据

• 限制更新

-- 只更新前三条符合条件的数据
update my_class set name = 'mysql666' where name = 'mysql02' limit 3;

删除数据

• 限制删除

delete from my_copy where name = 'mysql01' limit 3;
-- 如果有主键自增，删除后并不会还原
-- 清空表：重置自增长
truncate my_copy;	-- 保留表结构frm，清空数据

查询

-- select选项，默认为all
select distinct * from my_class;	-- 没加条件，针对所有字段去重，即完全一样的记录才去除
select distinct room from my_class;	-- 针对某一字段去重查询
-- 字段别名
select name as '班级', room as '教室' from my_class;-- 使用别名显示
-- 子查询，说明了数据源可以是表也可以是有表结构的数据
select * from (select * from my_class) as class;	-- 必须有别名

WHERE字句

• 用于提取那些满足指定条件的记录成功返回1，失败返回0
• where是唯一一个从磁盘获取数据时就开始判断的条件

alter table my_class add sits int default 0;	-- 增加个字段先
SELECT * FROM my_class WHERE name='mysql03';
-- floor()向下取整，rand()取得0到1的随机数
update my_class set sits=floor(rand() *20+1);-- 更新所有, rand()返回> = 0且<1的随机数

• WHERE 子句中的运算符

运算符	描述
=	等于
<>	不等于，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内，区间
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

select * from emp where sal > 2000 and sal < 3000;
select * from my_class where name in('mysql01','mysql03');-- 集合中

• AND OR运算符

SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA');

• 关注语法就行，数据是之前两篇中创建的表

Order By字句

-- 默认asc
SELECT * FROM Websites ORDER BY alexa DESC;	-- DESC降序（倒序）
-- 当然可以多字段排序
select * from my_stu order by c_id, sex desc;-- 先按c_id排，内部再按照sex排，先来后到懂吗？

Group By字句

• 根据某个字段进行分组，主要是为了分组之后进行数据统计
• SQL也提供了一系列统计函数
  • count()
  • max()
  • min()
  • avg()
  • sum()

-- 以性别进行分组，统计各组情况！
select sex, count(*), max(height), min(age), avg(weight), sum(age) from my_stu group by sex;	

• 多字段分组

-- 当然，还是讲求个先来后到，先按sex分
 select sex, entrance, count(*), group_concat(sname) from student group by sex,entrance; 
-- 名字合并显示
-- 这里用哪个字段group就select哪个字段显示，不能少不能多
select sex, entrance, count(*), group_concat(sname) from student where sex='男' group by sex,entrance;
-- where只能写在group前面，出磁盘时就过滤

• 回溯统计

-- 在每一层分组后进行统计，向下一层分组汇报结果
select sex, entrance, count(*), group_concat(sname) from student group by sex,entrance with rollup;

Having字句

• 类似于where，但处理的是进入到内存中的数据（已经被选出来的）
• where能做的它几乎都可以做，where不能做的它也能做（这就是西厂…）

-- 内存运算，不能使用where
select entrance, count(*) from student group by entrance having count(*)>1;
-- where不能使用别名，别名是进入内存中产生的
select c_id count(*) as total from my_stu group by c_id having total>=2;

• 使用where优先，可以尽量避免内存读入错误数据

Limit字句

select * from my_stu limit 2;	-- 取两条
select * from my_stu limit 0,2;	-- 从查询到的第0条记录开始，查询两条（offset）

连接查询

• 用户查询的数据需要来自多张表时使用，与表间记录的关系有相似的地方

内连接

• 从左表中取出一条记录，去右表中与所有的记录进行匹配，匹配必须是某个条件满足才可

-- inner可以省略，即连接查询默认使用内连
select * from my_stu inner join my_class on my_stu.c_id = my_class.id;-- my_stu是左表
select * from my_stu inner join my_class on c_id = my_class.id; -- c_id唯一
-- 当字段有重复时，可以使用别名
select s.*, c.name as c_name, c.room from my_stu as s inner join my_class as c on my_stu.c_id = my_class.id;	
-- 数据查出之后才select，所以可以使用别名s.*  c

可以使用where代替on，但是没有on效率高（where需要逐个匹配判断）

外连接

• 以某张表为主（基表），其每条记录与另外一张表连接，若能匹配则保留正确结果，未能匹配则只保留主表字段，select的其他表字段（连接字段）置空（NULL）
• 外连接分为左外连接、右外连接和全外连接

-- 左外连接即以左表为主表
select s.*, c.name as c_name, c.room from my_stu as s left join my_class as c on my_stu.c_id = my_class.id;	-- 最终记录数不少于左表
-- 右表为主表
select s.*, c.name as c_name, c.room from my_stu as s right join my_class as c on my_stu.c_id = my_class.id;	-- 最终记录数不少于右表

• 左表的数据显示在左边，右表的显示在右边
• 全外连接：也叫联合查询，用于合并两个或多个 SELECT 语句的结果集

-- 每条select语句查询的字段必须对齐
select * from my_fore2 union all select * from my_fore2; -- all表示不去重
-- 这个就不是匹配了，而是结合，两边都是好表，得罪不了！

自然连接

• 系统自动匹配连接条件，通常以同名字段作为条件

-- 自然内连接
select * from my_stu natural join my_class;
-- 自然外连接
select * from my_stu natural left join my_class;

• 自动合并同名字段
• 基本不使用，因为无法保证字段的设计满足要求

交叉连接

select * from my_stu cross join my_class;

• 由于交叉连接在做笛卡尔积，应避免
• 没有实际的意义，只是保证连接结构的完整性

子查询

• 在某个查询结果之上再次进行查询

• 按子查询语句出现位置分类

  • from子查询：出现在from之后
  • where子查询：where之中
  • exists子查询：exists里

  -- exist子查询用来判断条件是否满足
  -- 即exists返回true/false
  select * from my_stu where exists(select * from my_class where id = 1);
  -- 如果exists中的语句返回数据，就返回真，则查到全部学生
  select * from my_stu where exists(select * from my_class where id=666);-- 无
  -- 一般并不用，常有意外发生！
  

• 按照子查询返回结果分类

  • 标量子查询：子查询得到的结果只有一行一列

  -- 举例：想查询班级名为N1的所有学生
  select * from my_stu where c_id = ???; -- 确定数据源
  select * from my_class where name = 'N1';	-- 子查询：返回一个id值
  -- 注意要使用括弧
  select * from my_stu where c_id = (select * from my_class where name = 'N1');
  

  • 列子查询：返回的结果是一行多列，关键字in

  -- 举例：查询所有正常运行（有学生的）班级的学生
  select * from my_stu where c_id in(???);
  select id from my_class; -- 返回一列多行
  select * from my_stu where c_id in(select id from my_class);
  -- 可以使用any/some/all代替in，必须使用等号
  select * from my_stu where c_id =any(select id from my_class);-- 正常
  select * from my_stu where c_id =some(select id from my_class);-- 正常
  -- 空，不可能等于全部class表中的id
  select * from my_stu where c_id =all(select id from my_class);
  

  • 行子查询：子查询的结果是多行多列，需要构造行元素

  -- 举例：查询身高最高且年龄最大的学生
  select * from my_stu where age=? height=?;
  select max(age) from my_stu;-- 子查询结果
  select max(height) from my_stu;-- 子查询结果
  -- 虽然where不能使用函数，但是这里函数使用在子查询，与where无关！
  select * from my_stu where age=(select max(age) from my_stu) height=(select max(height) from my_stu);
  -- 构造行元素(age,height)
  select * from my_stu where (age,height) = (select max(age),max(height) from my_stu);-- 返回的结果可能是多行多列！
  

  • 表子查询：子查询的结果当做二维表使用，也叫from子查询

  -- 举例：查询每个班各自最高的学生
  select * from (select * from my_stu order by height desc) as stuent group by c_id;	-- from 后只能跟表！
  

  注：group by默认返回的是每组的第一条记录！！！

  目前已经删除了group by all命令

• 子查询更重要的是其思想，在一次查询的结果上进行查询，常和where、in、from搭配使用

• OK，到目前为止，介绍了普通查询、连接查询、联合查询以及子查询

• 啥时候用啥查询？视需求而定，重点是掌握每种方式解决问题的特点！

小结

• 介绍完了查询操作，你肯定感觉很空虚，在我的PythonWeb专栏里会结合一个商城订餐小程序，从建库建表到业务查询梳理一遍，进一步提高！
• 下一篇又回到理论了，重点介绍MySQL的外键、视图和数据备份与恢复