Mysql-DQL语句,数据库的备份和还原,数据库的约束(笔记)

准备工作:

-- 创建一个表
create table student(
			id int,
    		name varchar(10),
    		age int,
    		gender varchar(5),
    		address varchar(50),
    		math int,
    		english int	
);
-- 添加数据
insert into student values
(1, '马云', 55, '男', '杭州', 66, 78),
(2, '马化腾', 45, '女', '深圳', 85, 87),
(3, '马景涛', 55, '男', '香港', 60, 77),
(4, '柳岩', 20, '女', '湖南', 61, 65),
(5, '柳青', 20, '女', '香港', 63, 88),
(6, '刘德华', 57, '男', '深圳', 69, 94),
(7, '马德', 22, '女', '陕西', 86, 79),
(8, '德玛西亚', 18, '男', '四川', 36, 63),
(9, '高圆圆', 42, '女', '甘肃', 76, 75),
(10, '张佳宁', 32, '女', '杭州', 96, 43);
-- 查看表

结果:

DQL语句其他语法:分组group by ,筛选having, 分页查询limit以及分页查询使用场景

1.DQL语句之分组查询:group by

语法:

​ select 字段列表 from 表名 group by 分组字段名称;

注意事项:

​ a.查询的字段列表中可以使用 分组字段
​ b.group by之后不能使用聚合函数

代码:

-- 需求:按照性别分组,查询出他们的数学成绩的平均分
SELECT  
     gender '性别', -- 查询的分组字段
     AVG(math) '数学平均分' -- 查询每一个组的数学平均分
FROM
	student 
GROUP BY 
	gender;

运行结果:

带条件分组查询的语法: where 条件 必须放在group by 之前,否则语法错误!

语法:

​ select 字段列表包含分组字段,聚合函数… from 表名 where 条件 group by 分组字段;

代码

-- 需求:按照性别分组,查询出他们的数学成绩的平均分,数学成绩大于50分的参与分组
SELECT
	gender '性别',
	AVG(math) '数学平局分'
FROM
	student
WHERE 
	math > 50    -- 先满足条件,然后才能分组;
GROUP BY 	
	gender;

结果:

2.DQL语句之筛选查询 having

where条件,group by,having 必须先有条件,分组,然后才筛选!
注意:筛选的后面可以使用聚合函数,group by的后面是不能使用聚合函数的

代码:

-- 需求:按照性别分组,查询数学平局分,条件:数学成绩大于50的人参与分组, 筛选出人数大于2的这一组
SELECT 
	gender  '性别',
	COUNT(id) '总人数',
	AVG(math) '数学平均分'  
FROM
	student	
WHERE 
	math > 50
GROUP BY 
	gender 
HAVING      -- 后面可以使用聚合函数
	COUNT(id) > 2;
	
-- 优化为
SELECT 
     gender  '性别',
     COUNT(id) 人数,
     AVG(math) '数学平均分'  
FROM
    student	
WHERE 
	math > 70
GROUP BY 
	gender 
HAVING      -- 后面可以使用聚合函数
	人数 > 2 ;

3.分页查询 limit

语法:

​ select 字段列表 from 表名 limit 起始行数,每页显示的条数;

代码:

-- 起始行数:从0开始算的,    
-- 起始行数 = (当前页码-1)*每页显示的条数
-- 前提条件:每页显示2条, 
-- 第一页数据
SELECT * FROM student LIMIT 0,2;
-- 第二页数据
SELECT * FROM student LIMIT 2,2 ;
-- 第三页数据
SELECT * FROM student LIMIT 4,2 ;

结果:

第一页:

第二页:

第三页:

数据库的备份和还原

方式1:可视化界面的备份和还原

​ 备份:

​ 选中库名,右键----->BACKUP/Export 备份导出---->
​ 选中Sql脚本,存储到本地某个磁盘上----->
​ StructureAndData:表的结构和数据都需要备份

​ 还原:

​ 先在SqlYog将myee_2203删除了,新建库右键---->
​ Import 导入---->选中执行execute sql 脚本

方式2:命令行的方式数据库的备份和还原

​ 备份:

​ 以管理员身份进入dos,不需要登录mysql
​ mysqldump -uroot -p你的密码 库名 > 磁盘上的路径xx\xx\xx.sql 保存到指定路径上
​

​ 还原:

​ 进入dos控制台,登录mysql,先将库删除,新建一个库,使用这个库 use库名;
​ 加载指定路径的sql脚本, source 备份的sql脚本路径(D:\EE_2203\day9\code\mysql_01.sql)

dos命令框utf8不支持中文,查看编码字符集并修改为gbk可以显示中文

数据库的约束

简介:
	限定DBA(数据库管理员)操作数据库的时候一种行为(非法行为);
举例:
	直接插入null值或者某个非业务字段id一直在重复
1)默认约束 default
2)非空约束 not null
3)唯一约束 unique
3)主键约束 primary key  非空且唯一
4)自增长约束 auto_increment ,用户不给值的时候,每次插入数据会自增1
5)外键约束 foreign key 

1.默认约束 default

代码:

-- 创建一张表stu表
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20),
	gender VARCHAR(3)
);
-- 添加两个正常数据
INSERT INTO stu VALUES(1,'高圆圆','女'),(2,'文章','男') ;
-- 添加没有gender的数据
INSERT INTO stu(id,NAME) VALUES(3,'王宝强') ;
-- 上面gender字段没有插入数据,默认就是null,空值(没有意义)
-- 查看表
SELECT * FROM stu;

结果:

-- 删除表
DROP TABLE stu ;
-- 重新创建 使用默认值
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20),
	gender VARCHAR(3) DEFAULT '男'    -- 加入默认约束
);
-- 添加
INSERT INTO stu VALUES(1,'高圆圆','女'),(2,'文章','男') ;
INSERT INTO stu(id,NAME) VALUES(3,'王宝强') ;

结果:

2.非空约束 not null,当前这个字段值不能为null

代码:

-- 删除表
DROP TABLE stu ;
-- 重新创建表
CREATE TABLE stu(
   id INT, -- 编号
   NAME VARCHAR(20), -- 姓名
   age INT, -- 年龄
   address  VARCHAR(50) NOT NULL-- 地址 ,加入非空约束
) ;
INSERT INTO stu VALUES(1,'高圆圆',42,'西安市'),(2,'赵又廷',45,'北京市') ;
-- 地址插入一个null值
INSERT INTO stu VALUES(3,'张佳宁',32,NULL) ;  -- 这种直接插入null也属于非法行为 --添加不进去

会报错

insert into stu(id,name,age) values(3,‘张佳宁’,32) ;

‘address’ doesn’t have a default value:地址没有默认值

代码:

-- 通过sql语句将非空约束去掉--修改表的类型
ALTER TABLE stu MODIFY address VARCHAR(50) ;
-- 此时添加没有地址的数据就可以加进去
INSERT INTO stu VALUES(3,'张佳宁',32,NULL) ; 
SELECT * FROM stu;

结果:

-- 删除id为3的数据
DELETE FROM stu WHERE id = 3;
-- 不然下边语句会报错
-- 通过sql语句将非空约束加上
ALTER TABLE stu MODIFY address VARCHAR(50) NOT NULL ;

3.唯一约束unique (属于一种index :索引)

-- 删除表
DROP TABLE stu ;
-- 重新建表
CREATE TABLE stu(
	id INT , -- 编号
	NAME VARCHAR(20), -- 姓名
	phone_number VARCHAR(11) UNIQUE -- 电话号码 必须设置唯一的
) ;

-- 添加数据
INSERT INTO stu VALUES(1,'高圆圆','13388886666'),(2,'张三','13255559999') ;
INSERT INTO stu VALUES(3,'张佳宁','13388886666') ;

结果：

​ 重复值 Duplicate entry ‘13388886666’ for key ‘phone_number’

代码:

-- 通过sql语句将唯一约束删除

-- alter table stu modify phone_number varchar(11) ; -- 修改表的字段类型
-- 删除唯一约束的语法:atler table 表名 drop index  索引的字段名称(就当前类的字段名称一致的) ;
ALTER TABLE stu DROP INDEX phone_number ;
-- 重新添加
INSERT INTO stu VALUES(3,'张佳宁','13388886666') ;
-- 查看
SELECT * FROM stu;

结果:

-- 删除id为3的数据
DELETE FROM stu WHERE id = 3 ;

-- 通过sql语句添加唯一约束,此时语法就和修改表的字段类型的语法一致了
ALTER TABLE stu MODIFY phone_number VARCHAR(11) UNIQUE ;

4.主键约束 primary key(非空且唯一的) 一般和auto_increment(自增长约束)使用多一些

代码:

-- 删除表
DROP TABLE stu ;
-- 设置在id非业务字段上
CREATE TABLE stu(
      id INT PRIMARY KEY AUTO_INCREMENT , -- 编号   加入主键约束,加入自增长
      NAME VARCHAR(20) -- 姓名
);
-- 添加
INSERT INTO stu VALUES(1,'高圆圆'),(2,'文章') ;
INSERT INTO stu VALUES(1,'赵又廷') ;-- Duplicate entry '1' for key 'PRIMARY' id是一个主键,值不能重复
INSERT INTO stu VALUES(NULL,'王宝强') ;-- Column 'id' cannot be null

报错:

INSERT INTO stu VALUES(1,‘赵又廷’) ;-- Duplicate entry ‘1’ for key ‘PRIMARY’ id是一个主键,值不能重复
INSERT INTO stu VALUES(NULL,‘王宝强’) ;-- Column ‘id’ cannot be null —可以加进去

-- 通过sql将主键约束删除
-- alter table 表名 drop PRIMARY  KEY ;
ALTER TABLE stu DROP PRIMARY  KEY;
-- 通过sql语句将主键约束加入(唯一起作用)
ALTER TABLE stu MODIFY id INT PRIMARY KEY  ;
-- 加入自增长约束之后,不需要给id赋值了,自己自增
INSERT INTO stu(NAME) VALUES('高圆圆'),('文章') ;
-- 自增长约束可以自己插入新的id,然后在之前id上继续自增
INSERT INTO stu VALUES(17,'赵又廷') ;

INSERT INTO stu(NAME) VALUES('张三') ;
-- mysql有一个函数:获取最后一次自增长主键的id值
SELECT LAST_INSERT_ID() ;
SELECT * FROM stu ;

5.外键约束 foreign key

-- 描述员工信息,创建一个表,员工编号,员工姓名,员工性别,员工所在部门
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	emp_name VARCHAR(10) , -- 员工姓名
	emp_gender VARCHAR(5), -- 员工性别
	emp_dept_name VARCHAR(10) -- 员工所在的部门名称
 
) ;
-- 插入员工表数据
INSERT INTO emp(emp_name,emp_gender,emp_dept_name)
VALUES('文章','男','开发部'),
('高圆圆','女','测试部'),
('王宝强','男','运维部'),
('姚笛','女','测试部'),
('赵又廷','男','开发部') ,
('马保国','男','运维部') ;
DROP TABLE emp;
-- 上面存在的问题:员工表中包含员工信息以及部门信息,这种情况不能在数据库中出现
-- 查询出来的数据部门信息存在冗余,重复性大,优化改进:一张表始终描述一件事情
-- 将员工表拆分出来:将部门信息放在部门表中

-- 创建一张部门表 dept表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长约束 部门编号
	dept_name VARCHAR(20)   -- 部门名称
) ;
-- 插入三个部门
INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;



-- 创建员工表
CREATE TABLE emp(

	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	emp_name VARCHAR(10) , -- 员工姓名
	emp_gender VARCHAR(5), -- 员工性别
	dept_id INT -- 员工的部门id
 
) ;

INSERT INTO emp(emp_name,emp_gender,dept_id)
VALUES('文章','男',1),
('高圆圆','女',2),
('王宝强','男',3),
('姚笛','女',2),
('赵又廷','男',1) ,
('马保国','男',3) ;

-- 插入了一条员工数据
INSERT INTO emp(emp_name,emp_gender,dept_id) VALUES('张三丰','男',4) ;

-- 问题2:员工表中插入一条不存在的部门,两种表没有建立关系
-- 解决:外键约束 foreign key ,员工表dept_id 和部门表主键id进行关联
-- 外键所在的表----从表   (员工表)
-- 部门表: 主表 

-- 员工表:从表
CREATE TABLE emp(

	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	emp_name VARCHAR(10) , -- 员工姓名
	emp_gender VARCHAR(5), -- 员工性别
	dept_id INT,  -- 员工的部门id
	CONSTRAINT  -- 声明 
	 dept_emp_fk -- 外键名称  命名规则:主表名称_从表名称_fk
	 FOREIGN KEY (dept_id)   -- 作用在这个dept_id字段上 
	 REFERENCES dept(id)  -- 关联主表的主键id 
) ;

DROP TABLE emp ;

-- 删除3号部门
-- 加入外键约束:修改和删除是很麻烦,需要先修改或者删除从表数据,然后修改/删除主表数据
-- 将id为3的和id为的3清除
DELETE FROM emp WHERE id= 3 OR id = 6 ;
DELETE FROM dept WHERE id = 3;

SELECT * FROM dept ;
SELECT * FROM emp ;