MySQL_day02
DQL 查询表中记录
-
查询语法：
select 字段列表 from 表名列表where 条件列表 group by 分组字段having 分组之后的条件限定order by 排序 limit 分页限定
-
基础查询
-
查询多个字段
select 列1 , 列2 , 列n from 表; -
去除重复
select distinct 列名 from 表;多字段去重
-
-
计算列
select name ,math,english,math+english from student;当出现null时 结果就会null 所以要使用 ifnull（可能null的列，出现null赋的值）函数
select name,math,english,math+ifnull(english,0) from student; -
起别名
select name,math 数学,english 英语,math+ifnull(english,0) as total from student;
-
条件查询
wherre 自居后跟条件
-
运算符
< 、> 、<= 、 >= 、 = 、 <> !=不等号
-
between and
包含边界
in（集合）
-
like 模糊查询
占位符：
- _：单个任意字符（1个）
- %：多个任意字符（0或者多个）
-
is null
null值不可以使用 = 来判断，要使用isnull
不为null：is not null
与 and &&
或 or ||
非 not !
-
排序查询
语法：order by 子句
order by 排序字段1 排序方式1，排序字段2 排序方式2
不写排序方式 默认升序
排序方式：
- ASC ：升序 ，默认
- DESC：降序
按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
select * from student order by math asc , english asc;第一排序规则按数学升序，如果数学成绩一样，按照英语成绩排
注意：
如果有多个排序条件，当前边条件值一样时 才会判断第二条件
-
聚合函数
将一列数据作为一个整体，进行纵向的计算
-
count：计算个数
select count(name) from student;如果有null 不会计算其中
可以改为
select count(ifnull(name,0)) from student;解决null：
解决方案：
- 一般选择费控的列（主键）
- count（*） 只要有不是null的就算一行记录
max：计算最大值
min：计算最小值
sum：求和
avg：计算平均值
注意：聚合函数的计算会排出null值
-
-
分组查询
统计具有相同特征的数据，将数据作为整体分析。
-
语法：
group by 分组字段：
-
按照性别分组 分别查询男、女的平均分
select sex，avg(math) from student group by sex; -
分组之后人数要大于2人 ----> 使用having
select sex，avg(math) from student groupby sex having count(id) > 2;注意：
- 分组之后查询的字段：分组字段、聚合函数
- where 和 having 的区别？
- where 在分组之前进行限定，如果不满足条件则不参与分组，having 在分组之后进行限定，不满足条件则不会查询出来
- where 后不可以加聚合函数 而having可以
-
-
分页查询
-
语法：
limit 开始的索引 ，每页查询的条数
select * from student limit 0,3;第一页select * from student limit 3,3;第二页select * from student limit 6,3;第三页 -
公式：
开始的索引 = （当前的页码 - 1）* 每页的条数
limit语法 是mysql的一个“方言“
-
约束
概念：对表中数据进行限定，保证数据的正确性，有效性和完整性
-
分类：
- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 外键约束：foreign key
-
非空约束 not null
值不能为null
-
在创建表时添加约束
create table stu(id int,name varchar(20) not null); -
删除name的非空约束
alter table stu modify name varchar(20); -
创建表后添加非空约束
alter table stu modify name varchar(20) not null;
-
-
唯一约束 unique
值不能重复
-
创建唯一约束（建表时）
create table stu(id int,phone_number varchar(20) unique);
注意mysql中，唯一约束限定的列值可以多个null
-
删除唯一约束：（特殊）
alter table stu drop index phone_number; -
创建表后添加唯一约束：
alter table stu modify phone_number varchar(20) unique;
-
-
主键约束 primary key
-
注意：
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
-
在创建表时 添加主键约束
create table stu(id int primary key, -- 给id添加主键约束name varchar(20)); -
删除主键：
alter table stu drop primary key; -
创建表后添加主键
alter table stu modify id int primary key; -
自动增长
如果某一列是数值类型的，使用auto_increment 可以来完成值得自动增长，一般配合int类型主键完成
-
在创建表时 添加主键约束，并且完成主键自增长
create table stu(id int primary key auto_increment,name varchar(20));加自增长时 加数据使用null会默认上一个数据加一，如果自增长后，手动赋值非null的值，下一次自增长是根据手动赋的值来自增长，因为是默认上一条数据加一
-
-
删除自增长
alter table stu modify id int; -
添加自动增长
alter table stu modify id int auto_increment;
-
外检约束
让表与表产生关系，从而保证正确性-
在数据冗余时（重复），解决：
拆分表，将重复数据拆分成新表
-
在创建表时，可以添加外键
-
语法：
create table 表名(...外键列constraint 外键名称 foreign key 外键列名称 reference 主表名(主表列名称));
-
-
删除外键
alter table 表名 drop foreign key 外键名;这里使用外键名称 不是列名
-
再创建表之后添加外键
alter table employee add constraint emp_dept_fk foreign key (dept_id) references department (id);给emp表 dept_id列 加外键 关联departemnt列id
emp列：dept_id
department列：id
外键名：emp_dept_fk
外键值可以为null 不可以为不存在的外键值
-
-
级联操作：
在添加外键时 设置级联更新
alter table emp add contraint emp_dept_fk foreign key dept_id reference department (id) on update cascade;on update cascade
级联删除：
on delete cascade
级联删除可以和级联更新一起设置
多表之间的关系
-
一对一的关系
例：一个人对一个身份证
-
一对多的关系
例：一个部门对多个员工，一个员工对一个部门
-
多对多的关系
例：一个学生对多个课程，一个课程对很多学生
-
实现这些关系：
-
一对多（多对一）
例：员工和部门
image.png实现方式：
在多的一方建立外键，指向一的一方
-
多对多
例：学生和课程
image.png实现方式：
中间表
-
一对一
例：学生和身份证
image.png
实现方式：
* 任意一方添加外键 指向另一方主键并且添加Unique唯一约束
* 主键相同 -
-
案例：
分析 旅游线路分类 旅游线路 用户的关系
image.png数据库中SQL实现：
[image.png
范式
概念：设计数据库时，需要遵循的一些规范
-
范式分类：
-
第一范式（1NF）：每一列都是不可分割的原则数据项
属性不可再分
第二范式（2NF）：第一范式基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第三范式（3NF）：在第二范式的基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）
-
-
图示不合理的设计表
image.png -
概念：
-
函数依赖：
A -----> B 通过A属性值可以确定B的值，我们称B依赖于A
如果通过A属性（属性组）的值，可以确定唯一B属性的值
例如：学号 -----> 姓名
-
完全函数依赖
A ----> B 如果A是一个属性组，则B属性值得确定需要依赖于A属性组的所有的属性值，成B完全依赖于A属性组
例如：（学号、课程名称）----> 分数
-
部分函数依赖
A ------> B 如果A是一个属性组，则B属性值通过A属性组其中部分的属性值就可以确定，则成B部分依赖于A属性组
例如：（学号，课程名）-----> 姓名
-
传递函数依赖
A -----> B ， B ---->C 如果通过A属性（属性组）可以唯一确定B属性的值，再通过B属性（属性组）的值可以确定C属性的值，我们成C传递函数依赖于A
例如：学号------>姓名，姓名-------> 系主任
-
码：如果在一张表中，一个属性或属性组被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码
例如：该表中为 属性组（学号，课程名称）
主属性：码属性组的所有属性
非主属性：除过码属性组的属性
在该表中，姓名、系名和系主任部分依赖于码，分数完全依赖于码
-
-
消除部分依赖方案：
表的拆分
- 如图：
image.png在选课表中 分数完全依赖于 码（学号，课程名）属性组
在学生表中 姓名、系名和系主任完全依赖于学号（码）
- 仍然存在问题：
-
消除传递依赖（3NF）
解决：再拆分表
image.png学生表：系名、姓名完全依赖于学号
系表：系主任完全依赖于系名
刚刚所有的三个问题全部被解决掉
数据库的备份和还原
防止数据丢失
-
两种方式：
-
命令行
备份的语法：
mysqldump -u用户名 -p密码 数据库名 > 保存的路径还原：
登录数据库
创建数据库
使用数据库
执行文件，source 文件路径
-
图形化工具
-
右键导出
image.png -
还原：
右键连接 执行sql脚本
image.png -
-
