1、DQL查询语句
1.1、排序
通过ORDER BY 子句,可以将查询的结果进行排序(排序只是显示方式,不会影响数据库数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 排序字段 ASC|DESC
ASC:升序,默认值
DESC:降序
1.1.1、单列排序
就是只按某一个字段进行排序。
select * from students order by id asc;
1.1.2、组合排序
同时对多个字段进行排序,如果第一个字段相等,则按第二个字段排序,以此类推。
SELECT 字段名 FROM 表名 ORDER BY 字段1 [asc|desc],字段2 [asc|desc];
select * from students order by id asc ,age desc;
1.2、聚合函数
前面的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数是纵向查询,它是对一列的值进行计算,然后返回一个结果值,聚合函数都会忽略NULL值。
五个聚合函数
聚合函数 | 作用 |
---|---|
max(列名) | 求这一列的最大值 |
min(列名) | 求这一列的最小值 |
avg(列名) | 求这一列的平均值 |
count(列名) | 统计这一列有多少条记录 |
sum(列名) | 对这一列进行求和 |
语法:
SELECT 聚合函数(列名) FROM 表名;
聚合函数对于NULL的记录不会统计,但是如果需要把NULL也统计进去怎么办?
IFNULL(列名,默认值) 如果列名不为空,返回列值,如果为NULL,则返回默认值
select ifnull(age,0) from students;
select count(ifnull(age,0)) from students;
select count(age) from students;
select max(age) from students;
select min(age) from students;
select sum(age) from students;
select avg(age) from students;
select max(stuName) from students;
select min(stuName) from students;
1.3、分组
分组查询是指使用GROUP BY 语句对查询信息进行分组,相同数据作为一组。
SELECT 字段1,字段2,… FROM 表名 GROUP BY 分组字段 [HAVING 条件]
-- 根据年龄分组,查询每组的人数
select count(id),age from students group by age;
select count(id),birthday From students group by birthday;
-- 根据年龄分组,查询每组的人数,以及平均年龄>15
select count(id),age from students group by age having avg(age)>20;
having与where的区别
名 | 作用 |
---|---|
where子句 | 对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤再分组 where后面不可以使用聚合函数 |
having子句 | having子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤 having后面可以使用聚合函数 |
1.4、limit语句
limit是限制的意思,所以limit的作用是限制查询记录的条数
语法格式
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句] [limit 子句]
LIMIT offset,length;
offset:起始行数,从0开始计数,如果省略,默认就是0
length:返回的行数
select * from students limit 2,2;
LIMIT的使用场景:分页查询
2、数据库备份和还原
2.1、备份的应用场景
在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障,比如发生意外停机或存储介质损耗。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造车的损失是无法弥补的。
2.2、备份与还原的语句
2.2.1、备份格式
DOS下,不要登录MYSQL数据库,执行:
mysqldump –u用户名 –p密码 数据库>文件的路径
2.2.2、还原格式
mysql中的命令,需要登录后才可以操作
USE 数据库;
SOURCE 导入文件的路径;
备份
mysqldump -uroot -pRootbdit628@# db1>e:\db1.sql
还原,需要登录数据库
use db1;
source e:\db1.sql
3、数据库表的约束
3.1、约束概述
3.1.1、约束作用
对表中的数据进行限制。保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据无法插入到表中。约束在创建表的时候添加比较合适。
3.1.2、约束分类
约束名 | 约束关键字 |
---|---|
主键 | primary key |
唯一 | unique |
非空 | not null |
外键 | foreign key |
检查约束 | check |
3.2、主键约束
3.2.1、主键作用
用来唯一标识数据库中的每一条记录。
3.2.2、那个字段应该做为表的主键
通常不用业务字段作为主键,单独给每张表设计一个id的字段,把id作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
如:身份证,学号等都不建议做成主键
3.2.3、创建主键
主键关键字:primary key
主键的特点:非空、唯一
创建主键的方式:
1、在创建表的时候给字段添加主键
字段名 字段类型 primary key
2、在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名)
-- 给已存在的表中的字段添加主键
alter table students add primary key(id);
create table teachers(
id int primary key,
name varchar(20)
);
insert into teachers(id,name) values(1001,'张三');
3.2.4、删除主键约束
alter table 表名 drop primary key;
3.2.5、主键自增
主键如果让我们自己添加很有可能重复,我们希望每次插入新纪录时,数据库自动生成主键字段的值。
在mysql数据库中,可以通过AUTO_INCREMENT在创建表的时候指定主键自增。AUTO_INCREMENT默认值是1
create table teachers(
id int primary key auto_increment,
name varchar(20)
);
3.2.6、修改AUTO_INCREMENT的默认值
创建表时指定起始值:
create table teachers(
id int primary key auto_increment,
name varchar(20)
)AUTO_INCREMENT=起始值;
创建好以后修改起始值:
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
3.2.7 、DELETE和TRUNCATE对自增的影响
DELETE,删除所有的记录之后,自增没有影响
TRUNCATE,删除以后,自增又重新开始
3.3、唯一约束
就是表中某一列不能出现重复的值
唯一约束的基本格式
字段名 字段类型 UNIQUE
-- 唯一约束
create table teachers(
id int primary key auto_increment,
name varchar(20) unique
)auto_increment=1001;
insert into teachers(name) values('张三');
insert into teachers(name) values('李四');
3.4、非空约束
就是某一列不能为null
3.4.1、 基本语法
字段名 字段类型 NOT NULL
create table teachers(
id int primary key auto_increment,
name varchar(20) unique,
age int not null
)auto_increment=1001;
insert into teachers(name) values('张三');
3.4.2 、默认值
字段名 字段类型 DEFAULT 默认值;
create table teachers(
id int primary key auto_increment,
name varchar(20) unique,
age int not null default 0
)auto_increment=1001;
insert into teachers(name) values('张三');
如果一个字段设置了非空与唯一约束,该字段与主键的区别?
1、主键在一个表中,只能有一个,不能出现多个主键,主键可以是单列,也可以是多列(复合主键)
2、自增长只能用在主键上
3.5、外键约束
3.5.1、单表的特点
创建一个员工表包含(id,name,age,dept_name,dept_location),id主键并且自增
use db1;
CREATE TABLE emp(
id INT PRIMARY KEY auto_increment,
name varchar(20),
age int,
dept_name varchar(30),
dept_location varchar(30)
);
INSERT INTO emp(name,age,dept_name,dept_location) values('张三',20,'开发部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('李四',21,'开发部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('王五',22,'开发部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('赵六',20,'销售部','上海');
INSERT INTO emp(name,age,dept_name,dept_location) values('田七',21,'销售部','上海');
INSERT INTO emp(name,age,dept_name,dept_location) values('小明',22,'销售部','上海');
以上数据表的缺点:数据冗余、后期还会出现增删改的问题
3.5.2、解决方案
分成两张表,分别是员工表(emp)、部门表(dept)
-- 部门表 主表
CREATE TABLE dept(
id int primary key auto_increment,
dept_name varchar(30),
dept_location varchar(30)
);
-- 员工表
CREATE TABLE employee(
id int primary key auto_increment,
name varchar(20),
age int,
dept_id int
);
-- 添加部门
INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
INSERT INTO dept(dept_name,dept_location) values('销售部','上海');
-- 添加员工
INSERT INTO employee(name,age,dept_id) values('张三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
select * from employee;
问题:当我们在employee的dept_id里面输入不存在的部门,数据依然可以添加,但是并没有对应的部门。实际应用中不能出现这种情况,employee的dept_id中的数据只能是dept表中存在的id。
目标:需要约束employee中的dept_id只能是dept表中已经存在的id
解决方式:使用外键约束
3.5.3、什么是外键约束
什么是外键:在从表中与主表主键对应的那一列,如 员工表中的dept_id
主表:用来约束别人的表
从表:被别人约束的表
3.5.4、添加外键约束
新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
use db1;
-- 部门表 主表
CREATE TABLE dept(
id int primary key auto_increment,
dept_name varchar(30),
dept_location varchar(30)
);
-- 员工表
CREATE TABLE employee(
id int primary key auto_increment,
name varchar(20),
age int,
dept_id int,
constraint dept_fk_emp foreign key(dept_id) references dept(id)
);
-- 添加部门
INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
INSERT INTO dept(dept_name,dept_location) values('销售部','上海');
-- 添加员工
INSERT INTO employee(name,age,dept_id) values('张三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
INSERT INTO employee(name,age,dept_id) values('小红',22,5);
select * from employee;
3.5.5、删除外键约束
ALTER TABLE 从表 drop foreign key 外键约束名称;
-- 删除employee表的外键约束
alter table employee drop foreign key dept_fk_emp;
-- 表存在的情况下添加外键约束
alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);
3.5.6、外键的级联
出现新的问题
-- 要把部门表中的id值为2,改成5,能不能直接更新呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
update dept set id=5 where id=2;
-- 要删除部门id等于1的部门,能不能直接删除呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from dept where id=1;
什么是级联操作:
在修改和删除主表的主键时,同时更新或删除从表的外键值,称为级联操作
级联操作 | 说明 |
---|---|
ON UPDATE CASCADE | 级联更新,只能是创建表的时候创建级联关系,更新主表中的主键,从表中的外键列也自动同步更新 |
ON DELETE CASCADE | 级联删除 |
use db1;
-- 部门表 主表
CREATE TABLE dept(
id int primary key auto_increment,
dept_name varchar(30),
dept_location varchar(30)
);
-- 员工表
CREATE TABLE employee(
id int primary key auto_increment,
name varchar(20),
age int,
dept_id int,
constraint dept_fk_emp foreign key(dept_id) references dept(id) on update cascade on delete cascade
);
-- 添加部门
INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
INSERT INTO dept(dept_name,dept_location) values('销售部','上海');
-- 添加员工
INSERT INTO employee(name,age,dept_id) values('张三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
INSERT INTO employee(name,age,dept_id) values('小红',22,5);
delete from employee where name='小红';
select * from employee;
-- 删除employee表的外键约束
alter table employee drop foreign key emp_deptid_fk;
-- 表存在的情况下添加外键约束
alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);
select * from employee;
select * from dept;
-- 要把部门表中的id值为2,改成5,能不能直接更新呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
update dept set id=5 where id=2;
-- 要删除部门id等于1的部门,能不能直接删除呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from dept where id=1;
4、表与表之间的关系
表与表关系的概述
现实生活中,实体与实体之间有一定的关系,比如:部门和员工,老师和学生等,那么我们在设计表的时候,就应该体现出表与表之间的这种关系。
表和表之间的三种关系:
(1)一对多:最常用的关系 部门和员工
(2)多对多:学生表和选课表,一门课程可以有多个学生选择,一个学生可以选择多门课程
(3)一对一:相对使用比较少,公民和身份证号码
5、数据库设计规范
5.1、什么是范式
好的数据设计对数据的存储性能和后期的程序开发,都会产生至关重要的影响。建立科学的,规范化的数据库就需要满足一些规则来优化数据的设计和存储,这些规则就称为范式。
5.2、三大范式
目前关系型数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式、第四范式(4NF)、第五范式(5NF),又称为完美范式。
满足最低要求的范式是第一范式,在第一范式的基础上进一步满足更多的规范要求称为第二范式,其余范式以此类推。一般来说,数据库只需要满足第三范式就行了。
5.3、1NF
数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据。即表中的某个列有多个信息时,必须拆分为不同的列,简而言之,第一范式每一列不可再拆分,称为原子性。
5.4、2NF
在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。
所谓完全依赖是指不能存在仅依赖主键一部分的列,也就是说,第二范式就是在第一范式的基础上所有列完全依赖于主键,当存在一个符合主键包含多个主键列的时候,才会发生不符合第二范式的情况。比如:有一个主键有两个列,部分属性它只依赖于其中一列,这就不符合第二范式
第二范式的特点:
(1)一张表只描述一件事情
(2)表中的每一列都完全依赖于主键
5.5、3NF
在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其他的列来间接依赖于主键。也就是说,第三范式就是所有列不依赖其他非主键列。也就是在满足2NF的基础上,任何非主键列不得传递依赖于主键,所谓传递依赖,指定是如果存在“A-B-C”的关系,则C传递依赖A,因此,不满足第三范式。