表关系 表与表之间（实体）有什么样的关系，每种关系应该如何设计表结构
一对一 一张表一条记录与另一张表中最多有一条明确的关系，通常此设计方案保证两个表中使用同样的主键即可 《学生信息表》 常用表：学生ID（PRI） 姓名、年龄、性别 不常用表：学生ID（PRI） 籍贯、婚否、住址
一对多（也叫作多对一） 孩子对妈妈来说是多对一，而妈妈对孩子相当于一对多，类似这样的关系 在多关系的表中去维护一个字段，这个字段是“一”关系的主键
多对多 一张表中的一条记录在另一张表中可以匹配多条记录，反过来也是一样 这种情况，会用第三张表来关系
高级数据操作——新增数据 多数据插入： 只要写一次insert指令，但是可以直接插入多条记录 insert into 表名 [字段列表] values(值列表),(值列表2),(值列表3)…; insert into my_gbk values(“张三”),(“李四”),(“王五”);
主键冲突 在有的表中，使用的业务主键（字段有业务含义），但是往往进行数据插入的时候，又不确定数据表中是否已经存在对应的主键（比如说是否有对应ID）； create table my_student( stu_id varchar(10) primary key comment “主键，学生ID”, stu_name varchar(10) not null comment “姓名，不能为空” )charset utf8;
insert into my_student values(“stu001”,“张三”),(“stu002”,“张四”),(“stu003”,“张五”),(“stu004”,“张六”); insert into my_student values(“stu004”,“小婷”); 主键重复，报错
主键冲突的解决方案 1、主键冲突更新，如果冲突就更新 insert into 表名 【字段列表】values[值列表] on duplicate key update 字段=新值 insert into my_student values(“stu004”,“小婷”) on duplicate key update stu_name=“小婷”; 张六更新成了小婷 2、主键冲突替换 如果所有的都要更新，那么数据操作就比较多了，所以另一种方法是直接替换数据 replace into 表名 [字段列表]values(值列表); replace into my_student values(“stu001”,“夏洛”),(“stu002”,“山姆”);
蠕虫复制 一分为二，成倍增加 从已有数据中获取数据，并且将数据插入到数据表中 insert into 表名 [(字段列表)] select */字段列表 from 表（可以是其它表）； create table my_simple( name char(1) not null )charset utf8;
insert into my_simple values(“a”),(“b”),(“c”), (“d”),(“e”),(“f”); insert into my_simple(name) select name from my_simple; 注意： 可以在短期内快速增加表的数据量，可以测试表的压力，还可以通过大量数据来测试表的效率（索引） 但要注意主键冲突。
更新数据 在更新数据的时候，一般都是跟随条件更新，而不是批量更新 update 表名set 字段名=新值 where 判断条件; 如果没有条件，就全表更新 但是可以使用limit来限制更新的数量 update 表名set 字段名=新值 (where 判断条件) limit 数量;改变4个a成g Update my_simple set name=“e” where name=“a” limit 4;
成功改变了前4个。
删除数据 删除数据一般无法重置auto_increment; mysql有一个能够重置选项中自增长的语法： truncate表名：相当于先drop,再create;
查询数据 完整的查询语句： select select选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制； select选项：系统该如何对待查询得到的结果 All：默认的，表示保存所有的记录 Distinct：去除重复的记录，只保留一条（这里的重复指所有的字段都相同） select all * from my_simple;
select distinct * from my_simple; 已经自动去重了
字段列表： 有的时候需要从多张表中获取数据，在获取数据的时候，可能存在不同表中有同名的字段，这时候就需要将同名的字段命名成不同名的。利用别名：alias 字段名 [as] 别名； select distinct name as name1,name name2 from my_simple; from数据源： sql为前面提供查询的数据源，只要是一个符合二维表结构的数据即可，一张表，多张表，甚至是一张虚拟的表数据都可以 多张表：from 表1，表2，。。。 select * from my_int,my_date; 这里相当于把字段叠加，数据数量相乘得到了一份新的数据 这种结果的专业说法叫：笛卡尔积，这个结果给数据库造成压力，没有其它意义 动态数据 from后面跟的数据不是一个实体表，而是一个从表中查询出来得到的二维结果（子查询） 基本语法：from (select 字段列表 from 表) as 别名 select * from (select int_1,int_8 from my_int) as int_my;
where子句 用来从数据表获取数据的时候进行条件筛选 原理：一条条获取，拿到一条判断一条，符合就保存，不符合就不拿到内存中， where 通过运算符进行判断数据
group by 子句 分组统计 表示分组的含义，根据指定的字段，将数据进行分组，分组的目标是为了统计 基本语法：
添加班级id alter table my_student add class_id int; 修改班级： update my_student set class_id =1 where stu_id in (“stu001”,“stu002”); update my_student set class_id =2 where stu_id in (“stu003”,“stu004”); update my_student set class_id =3 where stu_id in (“stu0001”,“stu0002”); 根据班级id进行分组 select * from my_student group by class_id; 发现数据反而变少了 group by只是为了数据统计，如果想用它看数据，结果会很让你失望。分组之后，只会保留每组的第一条数据 如何统计： 利用统计函数：统计函数（聚合函数） count():统计每组数中的数量，如果统计的目标字段为NULL，则不统计,如果为*（count(*)）就表示为统计记录。 avg()：求平均值 sum():求和 max(): min(): alter table my_student add stu_age tinyint unsigned;
alter table my_student add stu_height tinyint unsigned; update my_student set stu_age=18,stu_height=177 where stu_id=“stu001”; update my_student set stu_age=17,stu_height=165 where stu_id=“stu002”; update my_student set stu_age=19,stu_height=170 where stu_id=“stu003”; update my_student set stu_age=16,stu_height=180 where stu_id=“stu004”; update my_student set stu_age=17,stu_height=175 where stu_id=“stu0001”; update my_student set stu_age=18,stu_height=171 where stu_id=“stu0002”;
按照班级统计： 每班人数，最大的年龄，最矮的身高，平均年龄： select class_id,count(),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id; 此处如果不用group by class_id;也没问题，默认group by anything; select class_id,count(),max(stu_age),min(stu_height),avg(stu_age) from my_student; 发现这里就只剩下一条数据了，因为class_id没有分组 我们再试试用max(stu_age)分组呢： select class_id,count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by max(stu_age); 报错：
group_concat():为了将分组中指定的字段进行合并； select class_id, group_concat(stu_name),count(*),max(stu_age),min(stu_height),avg(stu_age) from my_student group by class_id;这里将stu_name拼接显示了出来
多分组 将数据按照某个字段进行分组后，对已经分组的数据进行再次分组 基本语法：group by 字段1，字段2 先按照字段1分组，再按照字段2分组，以此类推 先加一个性别字段： alter table my_student add gender enum(“男”,“女”,“保密”); update my_student set gender = 1 where stu_id in (“stu001”,“stu003”,“stu0001”); update my_student set gender = 2 where stu_id in (“stu002”,“stu004”,“stu0002”);多分组： select class_id,gender,count(*),group_concat(stu_name) from my_student group by class_id,gender;
分组排序 mysql中，分组默认有排序的功能，按照分组字段进行，默认是升序 基本语法：group by字段[asc|desc],字段[asc|desc] 班级升序，性别降序 select class_id,gender,count(*),group_concat(stu_name) from my_student group by class_id asc,gender desc;
回溯统计 当分组进行多分组之后，往上统计的过程中，需要进行层层上报，将这种层层上报统计的过程称之为回溯统计，每一次分组向上统计的过程都会产生一次新的统计数据，而且当前数据对应的分组字段为NULL 基本语法：group by字段[asc|desc] with rollup select class_id,count() from my_student group by class_id; select class_id,count() from my_student group by class_id with rollup; select class_id,gender,count(*),group_concat(stu_name) from my_student group by class_id,gender with rollup;
having字句： 本质和where一样，是用来进行数据条件筛选， having在group by子句之后，是可以对group by的分组进行筛选的，但where在它之前，所以where是不能的 insert into my_student values(“stu005”,“小江”,1,19,178,1); 查询一个班级人数大于等于3的结果： 我们先试试where，这里发现number只有在group by之后才会计算，所以报错！ select class_id,count(*) as number from my_student where number>=3 group by class_id; where不能使用聚合函数
select class_id,count() as number from my_student group by class_id having number>=3; 这里number 也可以用count() 说明having可以做where能做的所有事，但where却不能做having的
hvaing是对统计结果进行操作的
另外，sql语句的母子句中，子句可以不存在，但一旦存在，就需要按从左往右去执行
where是内存操作
order by子句 基本语法：order by 字段 esc|desc create table my_student( stu_id int, stu_name varchar(10), stu_gender enum(“男”,“女”,“保密”), stu_class int )charset utf8;insert into my_student values(2020001,“王二”,1,3); insert into my_student values(2020004,“王三”,2,2); insert into my_student values(2020002,“王四”,2,7); insert into my_student values(2020007,“王五”,1,4);
排序： select * from my_student order by stu_class asc; order by 也可以像group by一样进行多字段排序
insert into my_student values(2020002,“张二”,1,3); insert into my_student values(2020005,“张三”,2,2); insert into my_student values(2020008,“张四”,2,7); insert into my_student values(2020009,“张五”,1,4);
select * from my_student order by stu_class asc,stu_id desc;
limit子句 用于限制子句，主要是限制记录数量获取 select * from my_student order by stu_class asc limit 3; limit在查询的时候，如果限定为一条记录的时候，使用的比较多，有时候获取多条记录并不能解决业务问题，但会增加服务器压力。
分页 limit用来限制指定区间的数据 limit offset,length //offset偏移量，从哪儿开始，length长度 mysql记录的数量从0开始 limit 0,2表示获取前两条数据 select * from my_student limit 0,2; select * from my_student limit 2,2;
limit后面的length表示最多获取的数量，但是如果数量不够，系统不会强求
算术运算符 通常不在条件中使用，而是用在结果运算（select字段中） ±*/% create table ysf1( int_1 int, int_2 int, int_3 int, int_4 int )charset utf8;insert into ysf1 values(100,-100,0,default); select int_1+int_2-int_2,int_1-int_2,int_1*int_2,int_1/int_2,int_2/int_3,int_2/6,int_4/5 from ysf1; int_1/int_2 获得的是浮点数 在mysql中除法是用浮点数计算的 结果如果是0，系统会给NULL，用NULL进行任何算术运算结果都是NULL，这里的计算错误，会给NULL
比较运算 =,或者<=>都是表示等于 select * from my_student where stu_class>=4; 特殊运算： select “1”<=>1,0.02<=>0,0.02<>0; 此处只能用<=>进行判断，而不能用=，这里用=就相当于赋值了； 另外，在mysql中，会将相比较的类型自动转换成同类型，再比较 在mysql中没有bool值，只有0和1，正确就是1 在条件判断的时候，还有相对应的比较运算符，计算区间： between 条件1 and 条件2； select * from my_student where stu_class between 2 and 5; 这种查找是闭区间查询，也就是2<=x<=5 这里的条件1必须小于条件2 如果是5 and 2,就会一条数据也没有
特殊运算符 and or not 与 或 非 select * from my_student where stu_id>2020001 and stu_class>2;
IN运算符 在……里面，是用来替代=，当结果不是一个值，而是一个结果集的时候；基本语法 in(结果1，结果2……），只要结果在其中满足条件，返回1 即true
Is运算符 Is是专门用来判断字段是否为NULL的运算符 基本语法：is null… insert into my_student values(2020011,default,default,8); 查找为空： select * from my_student where stu_name = NULL;//无结果 select * from my_student where stu_name is NULL;//有结果
LIKE运算符 用来进行模糊匹配 基本语法 like 匹配模式 匹配单个字符 %匹配多个字符 select *from my_student where stu_name like "张";
联合查询 联合查询是合并多个相似的选择查询的结果集，等同于将一个表追加到另一个表，从而实现将两个表查询组合到一起，使用谓词为UNION或UNIONALL 联合查询：将多个查询结果合并到一起（纵向合并），字段数不多，多个查询的记录数合并 应用场景： 将同一张表中不同的结果（需要多条查询语句来实现）合并到一起展示数据； 最常见，在数据量大的情况下，会对表进行分表操作，需要对每张表进行分数据统计，使用联合查询来将数据存放到一起显示；
基本语法 select 语句 union(union 选项) select 语句 union选项，与select 选项基本一样 distinct 去重，去掉完全重复的数据（默认的） all保存所有的结果
alter table my_student add stu_height int; update my_student set stu_height=180 where stu_id=2020001; update my_student set stu_height=173 where stu_id=2020002; update my_student set stu_height=182 where stu_id=2020004; update my_student set stu_height=181 where stu_id=2020005; update my_student set stu_height=170 where stu_id=2020007; update my_student set stu_height=182 where stu_id=2020008; update my_student set stu_height=177 where stu_id=2020009; update my_student set stu_height=169 where stu_id=2020011; select * from my_student where stu_gender=1 order by stu_height asc; select * from my_student where stu_gender=2 order by stu_height desc; 我们可以在两个语句之间加上一个union --默认选项distinct select * from my_student where stu_gender=1 union select * from my_student where stu_gender=2; select * from my_student where stu_gender=1 order by stu_height asc union select * from my_student where stu_gender=2 order by stu_height desc; 这样是报错的，具体原因见第32条
只要字段数相同，不需要类型相同 select stu_id,stu_name,stu_height from my_student union all select stu_height,stu_id,stu_name from my_student; 我的这两句单独执行没问题，但是连在一起就报错了， 经测试，我是在网页中写的代码，可能和txt中写的格式不同，把换行换成空格即可 select stu_id,stu_name,stu_height from my_student union all select stu_height,stu_id,stu_name from my_student;
它只保留第一句的字段，但这样已经没意义了
order by的使用 order by如果想要在联合查询里使用，就必须用括号括起来 (select * from my_student where stu_gender=1 order by stu_height asc) union (select * from my_student where stu_gender=2 order by stu_height desc); 实现是实现了，但我发现并没有按我想要的方式排序
order by在联合查询中，必须与limit一起使用 (select * from my_student where stu_gender=1 order by stu_height asc limit 10) union (select * from my_student where stu_gender=2 order by stu_height desc limit 10); 这样就可以了
连接查询 将多张表连接一起进行查询（会导致记录数行和字段数列发生改变） 在关系型数据库的设计过程中，遵循着一对一，一对多和多对多，所以通常在实际操作的过程中，需要利用这层关系保证数据的完整性。 分类： 交叉连接 内连接 外连接 自然连接
交叉连接 将两张表的数据与另一张表彼此交叉 取出每一条记录后，与另外一张表的全部记录挨个匹配 迪卡尔积 语法：表1 cross join 表2 select * from my_student cross join my_int; 结果是27行 尽量不用交叉连接。
内连接 从一张表中取出所有的记录去另一张表中匹配，利用匹配条件进行匹配，成功了则保留，失败了放弃。 匹配到：继续匹配 匹配失败：向下继续，如何全表匹配结束，没有返回结果 表1 【inner】 join 表2 on匹配条件 create table my_class( id int primary key auto_increment, name varchar(10) not null unique )charset utf8;
insert into my_class values(null,“1班”),(null,“2班”),(null,“3班”); 1、如果内联没有条件，那么就是交叉连接 select * from my_student inner join my_class; 2、使用匹配条件 select * from my_student inner join my_class on class_id=id; 3、因为表的设计通常容易产生同名字段，尤其是ID，所以为了避免发生错误，通常使用表名.字段名 select * from my_student inner join my_class on my_student.class_id=my_class.id; 4、通常，如果条件中使用对应的表名，而表名通常比较长，所以可以通过表别名来简化。 select * from my_student as s inner join my_class as c on s.class_id=c.id; 5、内联接，必须保证匹配到，记录才会保存，如果没有匹配到就不显示 6、内连接因为不强制必须使用匹配条件（on）因此可以在数据匹配完成之后，使用where 条件来限制，效果与on一样（建议使用on） select * from my_student as s inner join my_class as c where s.class_id=c.id;
内连接的应用 内连接通常在对数据有精确要求的地方使用，必须保证两种表中能进行数据匹配
外连接 outer join 按照某一张表为主表（表中所有记录都会保留），根据条件去连接另一张表，从而得到目标数据 左外连接：左边是主表 右外连接：右边是主表
原理： 确定连接主表：左连接就是left join,right join就是右连接
语法： 左连接，主表left join 从表on 连接条件，主表数据在左边 右连接，从表right join 从表on 连接条件，右表数据在右边
select * from my_student as s left join my_class c on s.class_id=c.id; select * from my_student as s right join my_class c on s.class_id=c.id;
using关键字 在连接查询中用来代替对应的on关键字，进行条件匹配 在连接查询时，使用on的地方用using代替 使用using的前提是对应的两张表连接的字段是同名（类似自然连接自动匹配） 使用using关键字，那么对应的同名字段，最后在结果中只会保留一个。语法： 表1【inner left right】
select * from my_student left join my_class using(class_id);
