数据库的 高级数据操作 和 连接查询
新增数据
基本语法：insert into 表名 [(字段列表)] values(值列表);
主键冲突（Duplicate key）
当主键存在冲突的时候，可以选择性地进行处理，进行更新和替换
更新操作：insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段=新值;
替换：replace insert into 表名 [(字段列表)] values(值列表);
表创建的高级操作
从已有表创建新表（复制表结构）：create table 表名 like 数据库.表名;
蠕虫复制：先查出数据，然后将查出的数据新增一遍
insert into 表名[(字段列表)] select 字段列表/* from 数据表名;
蠕虫复制的意义
从已有表拷贝数据到新表中
可以迅速地让表中的数据膨胀到一定的数量级，用来测试表的压力以及效率
更新数据
基本语法：update 表名 set 字段=值 [where条件];
高级语法：update 表名 set 字段=值 [where条件] [limit 更新数量];
删除数据
delete from 表名 [where条件] [limit 数量];
truncate 表名; -- 先删除该表，后新增该表
查询数据
基本语法：select 字段列表/* from 表名 [where条件];
完整语法：select [select 选项] 字段列表[字段别名]/* from 数据源 [where条件子句] [group by子句] [having 子句] [order by子句] [limit 子句];
select 选项：select对查出来的结果的处理方式
all：默认的，保留所有的结果
distinct：去重，查出来的结果，将重复给去除
字段别名
字段名 [as] 别名;
数据源：单表数据源、多表数据源、查询语句
单表数据源：select * from 表名;
多表数据源：select * from 表名1,表名2, ...;
子查询：select * from (select 语句) as 别名;
where子句：返回结果0或1，0代表false，1代表true
判断条件
比较运算符：>、<、>=、<=、!=、<>、=、like、between、and、in/not in
逻辑运算符：&&(and)、||(or)、!(not)
group by子句
基本语法：group by 字段名 [asc|desc];
统计函数：
count()：统计分组后的记录数，每一组有多少记录
max()：统计每组中最大的值
min()：统计最小值
avg()：统计平均值
sum()：统计和
多字段排序
group_concat(字段);
回溯统计
with rollup;
having子句
与where子句一样，是进行条件判断的
having能够使用字段别名
order by子句
基本语法：order by 字段名 [asc|desc]
limit子句
方案一：只用来限制长度，即数据量：limit 数据量;
方案二：限制起始位置，限制数量：limit 起始位置,长度;
limit offset,length;
length：每页显示的数据量，基本不变
offset = (页码-1)*每页显示量
连接查询
连接查询（join）分类：内连接、外连接、自然连接、交叉连接
使用方式：左表 join 右表
交叉连接（cross join）
基本语法：左表 cross join 右表; -- 等价于：from 左表,右表;
内连接（[inner] join）
基本语法：左表 [inner] join 右表 on 左表.字段=右表.字段;
on表示连接条件
外连接（outer join）
left join：左外连接（左连接），以左表为主表
right join：右外连接（右连接），以右表为主表
基本语法：左表 left/right join 右表 on 左表.字段=右表.字段;
自然连接（natural join）
自然内连接：左表 natural join 右表;
自然外连接：左表 natural left/right join 右表;
模拟自然连接：左表 left/right/inner join 右表 using(字段名);
代码：
-- 给班级表增加主键
alter table my_class add primary key(name);
-- 插入数据
insert into my_class values(
'python1907','B408';
insert into my_classvalues (
'python1907','B407');
insert into my_classvalues (
'python1907','B407')
-- 冲突处理
on duplicatekey update
-- 更新教室
room='B407';
insert into my_classvalues (
'python1903','B408');
-- 主键冲突；替换
replaceinto my_classvalues (
'python1903','B406');
replaceinto my_classvalues (
'python1910','B409');
-- 复制创建表,
create table my_copylike my_class;
-- 删除主键
alter table my_copydrop primary key;
-- 蠕虫复制
insert into my_copyselect * from my_class;
insert into my_copyselect * from my_copy;
-- 更新部分B406变成A406
update my_copyset room='A408' where room='B408' limit3;
-- 删除数据；限制记录数为5
delete from my_copywhere room='B409' limit5;
-- 给学生表增加主键
alter table my_student modify idint primary key auto_increment;
-- 清空表，重置自增长
truncate my_student;
-- 以下三句有什么区别？
delete from 表名;-- 只删除数据
truncate表名;-- 删除数据，重置自增长
drop table 表名;-- 删除数据表
-- select 选项
select * from my_copy;
select all * from my_copy;
-- 去重
select distinct * from my_copy;
-- 向学生表插入数据
insert into my_studentvalues(null,'bc20190001','张三','男'),
(null,'bc20190002','李四','男'),
(null,'bc20190003','王五','女'),
(null,'bc20190004','赵柳','男'),
(null,'bc20190005','六七','男');
-- 字段别名
select id,
number as 学号,
name as 姓名,
sex性别 from my_student;
-- 多表数据源
select * from my_student,my_class;
-- 子查询
select * from (select * from my_student)as s;
-- 增加age年龄和height身高字段
alter table my_studentadd age tinyint unsigned;
alter table my_studentadd height tinyint unsigned;
-- 增加字段的值；rand取得一个0-1之间的随机数，floor向下取整
update my_studentset age=floor(
rand()*20+20),height=floor(rand()*20+170);
-- 找学生ID为1,3,5,的学生
select * from my_studentwhere id=1 || id=3 || id=5;-- 逻辑判断
select * from my_studentwhere idin(1,3,5);-- in表示在集合中
-- 找出身高在180到190之间的学生
select * from my_studentwhere height>=180 and height<=190;
select * from my_studentwhere heightbetween 180 and 190;
select * from my_studentwhere height
between 190 and 180;-- 不成立，相当于height>=190 and height<=180
select * from my_studentwhere 1;-- 所有条件都满足；
-- 根据性别分组
select * from my_studentgroup by sex;
-- 分组统计；身高高矮，平均年龄、总年龄
select sex,count(*),max(height),min(height),avg(age),
sum(age)from my_studentgroup by sex;
-- 修改ID为4的记录，把年龄置为null
update my_studentset age=null where id=4;
select sex,count(*),max(height),min(height),avg(age),
sum(age)from my_studentgroup by sex;
-- 修改ID为1 的记录，把性别置为女
update my_studentset sex='女' where id=1;
-- nan
-- nv
select sex,count(*),max(height),min(height),avg(age),
sum(age)from my_studentgroup by sexdesc;
-- 删除班级表原主键
alter table my_classdrop primary key;
-- 给班级表增加主键
alter table my_classadd idint primary key auto_increment;
-- 给学生表增加班级ID
alter table my_studentadd c_idint;
update my_studentset c_id=ceil(rand()*3);
-- 多字段分组；先班组；后男女
select c_id,sex,count(*)from my_studentgroup by
c_id, sex;-- 多字段排序
select c_id,sex,count(*),group _concat(name)
from my_studentgroup by c_id, sex;-- 多字段排序
-- 统计
select c_id,count(*)from my_studentgroup by c_id;
-- 回溯统计
select c_id,count(*)from my_studentgroup by c_idwith rollup;
-- 多字段分组回溯统计
select c_id,sex,count(*),group _concat(name)
from my_studentgroup byc_id, sex;-- 多字段排序
select c_id,sex,count(*),group _concat(name)
from my_studentgroup by c_id, sexwith rollup;
-- 求出所有班级人数大于等于2的学生人数
select c_id,count(*)from my_student
group by c_idhaving count(*)>=2;
select c_id,count(*)from my_student
where count(*)>=2 group by c_idhaving by c_id;-- 错误
-- having字句进行条件查询
select name as 名字,number as 学号 from my_student
having 名字 like '张%';
-- 排序
select * from my_studentgroup by c_id;-- 分组 ，为了进行统计
select * from my_studentorder by c_id;-- 排序
-- 多字段排序；先班级排序，后性别排序
select * from my_studentorder by c_id,sexdesc;
-- 查询学生；前两个
select * from my_student limit2;
select * from my_student limit0,2;-- 记录数是重0开始编号
select * from my_student limit2,2;
select * from my_student limit4,2;
-- 更改ID为班级表的第一列
alter table my_class change id idint first;
-- 交叉连接
select * from my_studentcross join my_class;
-- my_student cross join my_class 是数据源；
-- 内连接
select * from my_studentinner join my_classon c_id=my_class.id;
select * from my_studentinner join my_classon c_id=id;--错误
,因为两张表都有ID字段
-- 字段和表别名
select s.*,c.nameas c_name,c.room -- 字段别名
from my_studentas sinner join
my_studentas c on s.c_id=c.id;
-- 把学生表ID为5的记录的c_ID设置为null
update my_studentset c_id=null where id=5;
-- where 代替on
select s.*,c.nameas c_name,c.room-- 字段别名
from my_studentas sinner join
my_studentas c where s.c_id=c.id;
-- 左连接
select s.*,c.nameas c_name,c.room-- 字段别名
from my_studentas sleft join
my_studentas c -- 左表为主表；最终记录数至少不少于左表已有的记录数
on s.c_id=c.id;
-- 右连接
select s.*,c.nameas c_name,c.room-- 字段别名
from my_studentas sright join
my_studentas c -- 右表为主表；最终记录数至少不少于左表已有的记录数
on s.c_id=c.id;
select s.*,c.nameas c_name,c.room-- 字段别名
from my_studentas c right join
my_studentas s-- 左表为主表；最终记录数至少不少于左表已有的记录数
on s.c_id=c.id;
-- 自然内连接
select * from my_studentnatural join my_class;
-- 修改班级表name字段名为c_name
alter table my_class changename
c_namevarchar (20)not null;
-- 自然左外连接
select * from my_studentnatural
left join my_class;
-- 外连接模拟自然外连接：using
select * from my_studentleft
join my_classusing (id);
