DQL:查询语句
1.排序查询
语法:order by 子句
order by 排序字段1 排序方式1,排序字段2 排序方式2 ......
排序方式:
ASC:升序,默认的
DESC:降序
例:select * from test01 order by age math, english asc;
//按照数学成绩升序排列,如果数学成绩一样,则根据英语程序升序排列
2.聚合函数:将一列数据作为一个整体,进行纵向的计算
注意:聚合函数会排除null值
解决办法:1.选择不包含非空的列进行计算
2.利用之前的 ifnull 函数
聚合函数种类:
1.count : 计算个数
select count(列名) from 表名;
select count(name)from test01;//计算name列的个数
2.max:计算最大值
select max(math)from test01;//计算math的最大值
3.min:计算最小值
select min(math)from test01; //计算math的最小值
4.sum:计算和
select sum(math)from test01; //计算math的总和
5.avg:计算平均值
select avg(math)from test01; //计算math的平均值
3.分组查询
注意:1.分组之后查询的字段:分组字段,聚合字段
2.where 和 having 区别:
①where在分组之前进行限定,如果不满足条件则不参与分组;having在分组之后进行限定,如果不满足结果,则不会被查询出来;
②where后不可以根聚合函数,having可以进行聚合函数的判断
语法:group by 分组字段
例如:select sex,avg(math),count(id) from test01 group by sex;
//将test01表中的数据通过sex分组,并且计算出各组的平均值和人数
select sex,avg(math),count(id) from test01 where math>80 group by sex;
//按照sex分组,分别查询各组平均分,人数。要求:分数低于80分的人,不参与分组
select sex,avg(math),count(id) from test01 where math>80 group by sex having ount(id) > 2;
//按照sex分组,分别查询各组平均分,人数。要求:1.分数低于80分的人,不参与分组 2.分组之后,人数要大于两人的分组才能显示出来
4.分页查询(limit是一个mysql方言)
语法:limit 开始的索引 ,每页显示的条数;
公式:开始的索引 = (当前的页码 - 1)* 每页显示的条数
例如:select * from test01 limit 0,3; //查询表中从索引0开始三条数据
约束:对表中数据进行限定,从而保证数据的正确性,有效性和完整性
分类:1.主键约束:primaary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foregin key
1.非空约束:not null,值不能为空
1.在创建表时添加约束
name varchar(20) not null; //name 为非空
2.创建表完成后添加非空约束
alter table test01 modify name varchar(20) not null;
3.删除name分非空约束
alter table test01 modify name varchar(20);
2.唯一约束:unique ,值不能重复
1.name varchar(20) unique; //name值不能重复
注意:在mysql中,唯一约束限定的列的值可以有多个null
2.删除唯一约束
alter table test01 drop index name;
3.在创建表之后添加唯一约束
alter table test01 modify name varchar(20) unique;
3.主键约束
注意:1.含义:非空且唯一
2.一张表只能由一个字段为主键
3.逐渐就是表中记录的唯一标识
1.在创建表时添加主键约束
create table test01(
id int primary key, //给id添加主键约束
name varchar(20)
);
2.删除主键
alter table test01 drop parimary key;
3.创建完表后添加主键
alter table test01 modify id int primary key;
4.自动增长:如果某一列是数值类型,使用auto_increment 可以来完成值的自增长
insert into test01 values(null,'aaa'); //给test01中的主键字段添加自增长,并且赋值为aaa
5.删除自动增长
alter table test modify id int;
6.添加自动增长
alter table test01 modify id int auto_increment;
4.外键约束:foreign key, 让表与表之间产生关系,从而保证数据的正确性
1.在创建表时可以添加外键
create teble 表名(
...
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
例如:create teble test01(
...
dep_id;
constraint emp_id foreign key (dep_id) references department(id);
//创建一个外键名为emp_id,将表test01中的dep_id与departemnt表中的id相关联
);
2.删除外键
alter table 表名 drop foreign key 外键名称;
例如:
alter table test01 drop foreign key emp_id;
3.在创建表之后添加外键
alter table 表名 add constraint 外键名称 foreign key(外键列名) references 主表名称(主表列名称);
例如:
alter table test01 add constraint emp_id foreign key(dep_id) references department(id);
4.级联操作:在修改外键时,相关联的列也会改变(下面的这两个也可以写在一块)
4.1alter table test01 add constraint emp_id foreign key(dep_id) references department(id) on update cascade;
//添加外键,并且设置级联更新(on update cascade)
4.2alter table test01 add constraint emp_id foreign key(dep_id) references department(id) on update cascade;
//添加外键,并且设置级联删除(on delete cascade)
数据库的设计
1.多表之间的关系
1.分类:1.一对一:一个人对应一张身份证
2.一对多(多对一):一个部门有多个员工
3.多对多:一个学生可以选择多个课程和一个课程里面也包含多个学生
2.实现:
1.一对多(部门和员工):在多的一方建立外键,指向一的一方即可
2.多对多(学生和课程):多对多关系实现需要借助第三张中间表。
中间表至少需要包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。
3.一对一(人和身份证):可以在任意一方添加唯一外键指向另一方的主键(使用约束)
2.数据库设计的范式:
概念:设计数据库时,要遵循一些规范,要遵循后边的范式要求,必须遵循前边的所有范式。
不同规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据冗余越小。
分类:第一范式(1NF),第二范式(2NF),第三范式(3NF),巴斯-科德范式(BCNF),第四范式(4NF),第五范式(5NF,又叫完美范式)
1.第一范式(1NF):每一列都是不可分割的原子数据项
2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数)依赖
3.第三范式(3NF):在2NF的基础上,任何非主属性不依赖于其他非主属性(在2NF的基础上消除传递依赖)
函数依赖:A---->B,如果通过A属性(属性组)的值,可以确定唯一的B属性的值,称B依赖于A
完全依赖:A---->B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值。
部分函数依赖:A---->B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一些属性值即可
传递函数依赖:A---->B,B---->C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A
码:如果在一张表中,一个属性(属性组),被其他所有属性所完全依赖,则称这个属性(属性组)位该表的码
主属性:码属性组中所有的属性
非主属性:除过码属性组的属性
数据库的备份和还原
备份语法:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原语法:
1.登陆数据库
2.创建数据库
3.使用数据库
4.执行文件:source 文件路径;