多字段回溯 mysql_Mysql笔记（二）

1.主键：

①增加主键：primary key

方案一：在创建表时

create tablemy_shcool(Number char(10) not null null '姓名',

StudentIDvarchar(20) primary key comment '学号')charset utf8;

方案二(符合主键)：在创建表的时候，在所有字段之后

1 create tablemy_studenty(2 Number char(10) IS NOT NULL,3 Course char(10) comment'课堂代码',4 Score tinyint unsigned defaule 60 comment'成绩',5 primary key(Number,Score) --复合主键

6 )charset utf8;

方案三：再表建好之后进行追加

Alter table 表名 add primary key(字段);

②删除主键：

Alter table 表名 drop primary key;

③主键冲突：

解决方案一：更新，某一条数据中存在相同的数据，插入数据失败，主键冲突，使用更新方式对相应的字段信息进行更新

onduplicate key update root = '要更新的数据'

解决方案二：替换，存在主键冲突，对已存在的数据进行删除重新插入

Replace into 表([字段列表：包含主键]) values(值列表);replace into student values('Age',12)

2.主键的分类：逻辑主键

create tablemy_student(

IDint primary key auto_incerment comment'逻辑主键，自增长')charset utf8;

3.修改自增长(修改只能比当前已有的自增长的最大值，修改的自增长值大于最后一次索引)：

Alter table 表 auto_incerment = 10;

*查看自增长对应的变量：

show viariables like 'auto_increment%';

4.删除自增长：

Alter table 表名 modify id int(paimary key 有主键时不加);

5.唯一键(空字段不参与唯一性比较)：

①增加唯一键：

方案一：在创建表的时候字段后直接加unique/unique key

create tablemy_school(number char(10) unique comment'学号：唯一，允许为null')charset utf8;

方案二：在创建表之后追加唯一键

Alter table 表 add unique key(字段);

②删除唯一键：

Alter table 表 drop index 字段索引;

6.索引

①主键索引：primary key

②唯一索引：unique key

③全文索引：fulltext index(?如何确定关键字)

④普通索引：index

7.范式：数据库设计基本满足前3范式要求

①满足逻辑主键：Id，不使用业务数据作为主键，避免主键冲突

②数据不可再拆分：如时间：只存储开始值，结束值，不存储某一个时间段：开始-结束

③数据不存在传递依赖：一个字段的取值不依赖表中另一个字段，如教室依赖班级，班级依赖主键；解决方案：将存在依赖的字段取出重新形成一个表

④逆规范：以一个表存储多个表的业务数据，不使用逻辑主键作为答题，提高查询效率

8.蠕虫复制：从已有的数据中去获取数据，然后对数据进行新增操作(数据成倍增加)

①表创建高级操作：从已有表创建新表(复制表结构)

②create table 表(新表)like 表(被复制的表结构)

③蠕虫复制：先查出数据，然后将查出的数据新增一遍

insert into 表(新表) select(字段列表) * from 被复制的表; --将一个表的数据复制到一个新的表结构中

9.新增语句

update 表 set 字段 = 值 [where] [limit(限制更新数据量)];

10.删除语句

delete from 表 where 字段 = 值 [limit];delete from student where name='A' linit 1; --删除一个大写A

truncate 表; --清空表

11.查询

select[select选项] 字段列表[字段别名] *from 表 [where][group by][having][order by][limit];--select 选项：selcet对查出数据的处理方式--All:默认，保留所有的结果--Distinct: 去重，去除重复的结果数据

12.where 条件：

①用来判断筛选数据，可用于查询、更新、删除语句中

②运算符：

1、<>、!=不等于2、in(值)：select*from student where id='1 || 3 || 5';→select*from student where id in(1,3,5);3、like：select*from student where like '%海';4、between (值) and (值):select * from student where height between 180 and 190; --查找身高在180~190之间的数据，并且between条件的左边的值要等于或者小于右边的值

5、in/not in

③where之后的数据从磁盘进行获取，判断的数据成立保存到内存，失败则终止

④添加随机数：update student set age=floor(rend()*20+20),[条件2];-- floor 向下取整，ceil向上取整， rend()*20 取0~20之间的随机数

13.group by 分组

①基本运用

1、group by 字段[ASC/DESC]; --对分组的结果的整个结果进行排序

2、max()，min()，sum()，avg()，count(*) --常用函数

②多字段分组

select c_id as 班级,sex as 性别,count(*) as 统计人数，group_count(name) as 姓名 from student group by c_id,sex;--先班级，后男女

14.回溯统计

①with roullup：返回一个为空的字段对数据结果进行统计

1、select c_id,count(*) from student grouo by c_id with rollup; --对最后的数据进行统计

2、SELECT s.ServiceRepresentativeName as 施,s.ServiceStatusName as 状态,count(*) as总量from v_receipts assWHERE s.ServiceStatusName='已上线'

GROUP BYs.ServiceRepresentativeNamewithrollup--对实施的上线量进行查询统计

②多字段回溯统计：考虑第一层分组会有此回溯；第二次分组要看第一层分组的组数，组数是多少，回溯就是多少，然后加上第一层回溯即可

select c_id as 班级,sex as 性别,count(*) as 统计人，group_count(name) as姓名fromstudentgroup byc_id,sexwith rollup

15.having 条件语句：

①与where语句一样，进行条件判断对查询结果进行数据筛选，但分组统计的结果或者统计函数都只能having来做

select c_id,count(*) as total from student group by c_id having total>=2; --having 后可加字段别名，where在磁盘上没有别名数据一说，只有字段数据

②与where对比：where是将有效数据先存储到磁盘，再转存与内存中，提高查询效率；having调用内存数据进行查询，建议少用，节省内存资源

16.order by 排序：

①.排序，依赖校对集

②.order by 字段名 [ASC/DESC]; --默认升序

select * from student order by c_id;

③.多字段排序：

select * from student order by c_id,sex DESC;

17.limit 语句：限制数量

①限制查询长度：

select * from student limit 2; --查询前两条数据

②限制起始位置，限制数量：limit 起始位置，长度;(主要用于分页)

select * from student limit 0,2;