数据库2-数据的CURD操作和数据的备份与恢复

一 数据操作

对数据的增删查改操作有时候也被统称为CURD。

Create 增加数据

Update 修改数据

Read 查询数据

Delete 删除数据

1.1 添加数据

数据操作	语法说明	操作演示
添加数据	-- 指定字段添加数据（可以打乱顺序） INSERT INTO 表名 (字段1,字段2,字段3,....) VALUES (字段1的值,字段2的值,字段3的值,....); -- 也可以省略不写字段名，但是values后面字段值必须和表结构中的字段的排序位置与数量都保持一致（除了主键）。 INSERT INTO 表名 VALUES (字段值1,字段值2,字段值3,....); -- 一次性添加多条记录(指定要添加数据的字段名，可以打乱顺序) INSERT INTO 表名 (字段1,字段2,字段3,....) VALUES (字段值1,字段值2,字段值3,....), (字段值1,字段值2,字段值3,....), (字段值1,字段值2,字段值3,....) ... (字段值1,字段值2,字段值3,....); -- 末行一定不要忘了结束符号 -- 一次性添加多条记录(不指定要添加数据的字段名，不可以打乱顺序，一定是按照表结构的字段排列顺序一一填写，除了主键ID以外) INSERT INTO 表名 VALUES (字段值1,字段值2,字段值3,....), (字段值1,字段值2,字段值3,....), (字段值1,字段值2,字段值3,....) ... (字段值1,字段值2,字段值3,....); -- 末行一定不要忘了结束符号 -- 表数据的添加还支持表复制操作[这实际上是一种叫子查询的写法] insert into 表名 (字段1, 字段2, 字段3, ...) select (字段1, 字段2, 字段3, ...) from 表名	-- 删除原来的student_info; drop table if exists student_info; -- 新建数据表 CREATE TABLE `student_info` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `sex` int DEFAULT '1', `classes` int DEFAULT NULL, `age` int DEFAULT NULL, `description` text, PRIMARY KEY (`id`) ); -- 新增一条数据[可以选择指定ID] insert into student_info (id,name,sex,classes,age, description) values (101,'刘德华',1,508,17,'给我一杯忘情水~'); -- 新增一条数据[更多时候，我们是不指定主键ID，让其自动增长，同时，具有默认值的字段也可以不填写，mysql会自动采用默认值替换] insert into student_info (name, classes, age, description) values ('周华健', 501,17,'来也匆匆去也冲冲~恨不能相逢');

 

1.2 查询数据

 

查询数据

语法说明

操作演示

全列查询

select * from 表名;

-- 查找学生表中所有学生的所有信息
SELET * FROM student;

指定列查询

-- 查询指定单个字段
select 字段 from 表名;

-- 查询指定多个字段
select 字段1,字段2,... from 表名;

-- 查找学生表的姓名跟年龄
SELECT name,age FROM student;

表达式查询

-- 单个表达式
select 表达式 from 表名;
-- select 字段名 from 表名;
-- select 字段名+10 from 表名;         -- 注意：此处的字段名的数据类型必须支持+号运算。
-- select 函数名(字段名) from 表名;    -- 注意：此处的函数必须是MySQl内置的函数或者开发者基于MySQl语法声明的自定义函数

-- 多个表达式
select 表达式1,表达式2... from 表名;

select 1, name, age, 2022-age from student;  -- 查询学生年龄
select name, if(sex=1, '男', '女') from student;
-- if在mysql中既是一个语句关键字，也是一个函数，参数1：表达式，参数2：表示结果为True的值内容，参数3：表示结果为False的值内容，

去重查询

使用关键字 distinct可以把某列在不同行上相同的值的记录给去重。

-- 查询指定单个字段
select distinct 字段 from 表名;

-- 查询指定多个字段
select distinct 字段1, 字段2,... from 表名;  -- 反正distinct必须跟在select后面，否则报错。而且只能出现一个distinct

-- 查询当前所有学生的年龄值
select distinct age from student;

 

结果排序

可以在查询语句的表名后面加上 order by 要排序的列名/表达式 [asc | desc] 来进行排序

• asc：表示升序（由小到大），不加具体要排序的方式，默认是 asc

• desc：表示降序（由大到小），是 descending的缩写
  

-- 单列排序
select 字段 from 表名 order by 字段 desc;

select 字段1, 字段2.... from 表名 order by 字段名 asc | desc;

-- 多列排序
select 字段1, 字段2.... from 表名 order by 字段1 asc | desc, 字段2 asc | desc ....;

 

-- 结果排序
-- 单个字段排序
select * from student order by age desc;

-- 多个字段排序
select * from student order by classes asc, age asc;  -- 每一个班最小的都在第一个位置。

结果限制

-- 方式1：limit后面跟着 一个参数  表示限制结果的数量
select 字段列表 from 表名 limit 结果数量;

-- 方式2： limit后面跟诊 两个参数，第一个参数表示取数据的开始下标[在表中下标从0开始]，第二个参数表示限制结果的数量。
select 字段列表 from 表名 limit 开始下标, 结果数量;

--方式3：也可以通过offset指定显示结果的开始下标
select 字段列表 from 表名 limit 结果数量 offset 开始下标;

 

-- 结果限制
SELECT * FROM student LIMIT 3;   -- 从下标=0开始查询3条数据，相当远 limit 0,3
select * from student limit 3,3; -- 从下标=3开始查询3条数据
select * from student limit 3 offset 0;  -- 从下标=0开始查询3条数据，相当远 limit 0,3

条件查询

select 字段 from 表名 where 条件表达式;

-- 查询id为13的学生
select * from student where id=13;

聚合查询

把多个数据放在一起计算，最终得到一个结果。聚合运算都是写在select关键字后面

SELECT 聚合函数("字段") FROM 表名 WHERE 条件;
SELECT 聚合函数("字段"),其他字段列表 FROM 表名 WHERE 条件;

-- 查询305、304、302、301的男生平均年龄
select avg(age) from student where classes in (301, 302, 304, 305) and sex =1;

 

分组查询

GROUP BY子句， 可以对表的查询结果中的指定字段进行分组，常常与聚合函数一起使用。格式：GROUP BY 字段名语句不能写在where 条件表达式之前。

-- 方式1：按单个字段的不同行的值进行分组
group by 字段

-- 方式2：按多个字段的值的组合进行分组
group by 字段1, 字段2...


-- 不管是单个字段或多个字段分组，实际上都是在数据表中查看出现的多少个不同的字段值或字段值的组合，那么查询结果就会有多少个组。

 

-- 查询各个班级的人数各是多少
select classes, count(id) from student group by classes order by classes;

-- 多个分组
-- 计算每一个班中男生女生的数量
select classes, sex, count(id) from student group by classes, sex order by classes;

 

结果过滤

where条件语句的作用是针对当前数据表中的原始数据（聚合前的数据）进行筛选，而如果我们希望对聚合后的数据进行筛选，则需要使用having关键字才可以，having只能跟在group by之后使用，也叫分组结果过滤。

group by 字段 having 条件表达式;

 

-- 获取人数超过5个人的班级
select classes, count(id) as c from student group by classes having c >= 5;

 

连表查询 

mysql中针对连表的方式有3种：左连（left join）、内连（inner join）与 右连（right join），在一些其他的重量级数据库管理系统中，如Oracle，实际上还支持外连（outer join）。

连表方式	描述	举例
左连	from 主表 left join 从表 on 连表条件表达式	以左表为主表，先查询左表数据，再根据左表数据查询右表数据，右表如果没有对应数据则，默认为null
内连	from 主表 inner join 从表 on 连表条件表达式	以左表为主表，先查询左表数据，再根据左表数据查询右表数据，右表如果没有对应数据则，左表对应数据会被剔除掉
右连	from 主表 right join 从表 on 连表条件表达式	以右表为主表，先查询右表数据，再根据右表数据查询左表数据，左表如果没有对应数据则，默认为null

 

 

 

 

查询语句的完整格式：

select [distinct] 字段1 as A,字段2 as B....
from 表名1 as 表别名2, 表名2 as 表别名2
left join 从表1 on 表名.主键=从表1.外键
left join ....
where ....
group by ... having ...
order by ...
limit start,count

• 执行顺序为：

  • from 表名[包括连表]

  • where ....

  • group by ...

  • select distinct *

  • having ...

  • order by ...

  • limit start,count

• 实际使用中，只是语句中某些部分的组合，而不是全部

 

运算符操作：

MySQL中提供的运算符主要有：算术运算符、比较运算符、逻辑运算符与位运算符等。其中，常用的运算符有算术运算符、比较运算符、逻辑运算符。

1. 算数运算符

   运算符	描述	示例
   +	加法	-- 在成绩中查询中所有学生被扣的分数 select sid, cid, achievement, 100-achievement from student_course; -- id为偶数的学生，在mysql中判断两边的值是否相等，使用一个=来完成. select id,name from student where id % 2 = 0;
   -	减法
   *	乘法
   / 或 DIV	除法
   % 或 MOD	取余

2. 比较运算符

   比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

   符号	描述
   =	等于
   <>, !=	不等于
   >	大于
   <	小于
   <=	小于等于
   >=	大于等于
   BETWEEN	叫范围运算符，在两值之间，相当于 >=min &&<=max
   NOT BETWEEN	不在两值之间
   IN	叫成员运算符，判断字段值是否在集合中
   NOT IN	判断字段值是否不在集合中
   <=>	严格比较两个NULL值是否相等，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
   LIKE	模糊匹配
   REGEXP 或 RLIKE	正则式匹配
   IS NULL	为空
   IS NOT NULL	不为空

    

3. 逻辑运算符

   用于组合判断多个子条件形成一个整体的表达式，如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

   运算符写法1	运算符号写法2	作用
   !	NOT	逻辑非，并列，如果组合的条件都是TRUE,返回TRUE
   &&	AND	逻辑与，或者，如果组合的条件其一是TRUE,返回TRUE
   ||	OR	逻辑或，取反，如果条件是FALSE,返回TRUE

4. 优先级

   • 优先级由高到低的顺序为：小括号 > not > 算术运算符 > 比较运算符 > and > or

   • 使用过程中， 如果不清晰运算符之间的优先级，可以使用小括号( )提升优先级。

 

1.3 更新数据

数据操作	语法说明	操作演示
更新数据	update 表名 set 字段1=字段值1,字段2=字段值2 where 条件表达式;	-- 修改学生的年龄 UPDATE student set age=18 where name='刘福荣';

1.4 删除数据

 

数据操作	语法说明	操作演示
删除数据	DELETE FROM 表名 WHERE 条件表达式;	-- 删除信息 DELETE FROM student WHERE id=104;

 

二 数据备份与恢复

1 备份数据

备份数据就是把数据库中的数据保存到指定格式文件中。公司中为了保证数据安全，往往都会进行周期性的数据备份。

在cmd终端下运行mysqldump命令可以完成数据备份（注意：不需要进入MySQL的交互终端）：

mysqldump –uroot –p 数据库名 > python.sql;

-- 按提示输入mysql的密码

2 数据恢复

-- 方式1：不登陆进入mysql交互终端的情况下导入数据
mysql -uroot –p 新数据库名 < python.sql
-- 根据提示输入mysql密码


-- 方式2：登陆进入mysql交互终端的情况下导入数据
mysql -uroot -p
-- 根据提示输入mysql密码

use 数据库名

-- source SQL文件绝对路径[windows下的反斜杠路径要改成正斜杠]
source /student_system.sql