MySQL 模块五、模块六复习笔记

模块五
一、添加与更新MySQL数据表数据

a、复制命令的方式创建数据表
例如：用现有的数据表“用户信息”创建数据表“user”
create table user like 用户信息;

5.1向mysql数据表中添加数据
5.1.2数据导入时的特殊情况

1.导入用户表  （源表和目标表名不一样，而且字段名也不一样）

2.主从表导入，必须先导入主数据，再导入从表数据（因为不导入主表数据，从表参照主表的字段就没有可参照数据）

5.1.3使用mysqlimport命令导入文本文件

1.使用mysqlimport命令导入数据时不需要登录MySQL客户端
语法：
mysqlimport -u root -p [--local] 数据库名 导入的文本文件[参数可选项]

例如：将D盘中的user.txt文本文件导入malldb数据库中，字段间使用半角逗号“,”隔开，字符类型字段值使用半角双引号，将转义字符定义为"\",每行记录以回车换行符“\r\n”结尾

Mysqlimport -u root -p MallDB D:\user.txt   --fields-terminated-by=, --fields-optionally-enclosed-by=\"     --fields-escaped-by=\  --lines-terminated-by=\r\n ;

出现错误时解决步骤：
1）停止MySQL服务
net stop mysql80

2）找到“my.ini”文件，复制备份（其中“my.ini”文件在隐藏文件programDate-MySQL-MySQL server8.0-my.ini）

3)打开my.ini文件找到secure-file-priv设置为空，保存不了时用管理员身份运行txt
[ 修改C:\ProgramData\MySQL\MySQL Server 8.0\my.ini文件
把secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
改为
secure-file-priv=""
存盘时编码为ANSI.]

4)再重启MySQL
net start mysql80

5)再一次执行导入

5.1.4使用Insert语句向数据表中添加数据
一、插入记录时应注意
（1）、插入字符型（char和varchar）和日期时间型（Date）数据时，必须在数据前后加半角单引号，只有数值型（int、float）的值前后不加半角单引号
（2）date类型的数据，插入时，必须使用“YYYY-MM-DD”的格式，且日期数据必须用半角单引号

1.一次插入一条完整的记录
1)不指定字段，按默认顺序插入数据
lnsert lnto  数据表名称 values(字段值1,2,3.....,字段值n);

2.）指定字段名，按指定顺序插入数据
lnsert lnto  数据表名称(字段名1,2,3.....,字段名n)
              values(字段值1,2,3.....,字段值n);

2.插入多条记录
lnsert lnto  数据表名称(字段名1,2,3.....,字段名n),
              values(字段值1,2,3.....,字段名n),
                        (字段值1,2,3.....,字段名n),
                        (字段值1,2,3.....,字段名n);

3.将一张数据表中的数据添加到另一张数据表中
Insert Into 目标数据表名称 Select * | 字段列表 from 源数据表名称;

4.插入查询语句的执行结果
Insert Into 数据表名称 [字段列表] select语句;

5.2修改数据表中的数据
5.2.2使用Update语句更新新数据表中的数据

1、updste 数据表名称 set <字段名1>=<字段值1> [,<字段名2>=<字段值2>,......,<字段名n>=<字段值n>] [where<条件表达式>];

5.3删除数据表中的记录数据
5.3.2使用Delete语句删除数据表中的记录数据

1.delete语句删除数据表记录语法格式
delete from 数据表名称 [where<条件表达式>];

5.4从MySQL数据表中导出数据

数据备份
1.使用MySQL命令将数据表导出到文本文件
语法：
MySQL -u root -p -e "select 语句" 数据库名称 > 路径\文本文件名

1)使用MySQLdump命令将数据表导出到文本文件
MySQLdump -t <导出路径> <数据库名称> [<数据表名称>] -u <用户名称> -p [<参数可选项>]

a、使用不带参数的“MySQLdump”命令导出数据

例如：将数据库“malldb”中的数据表“student”的所有记录导出到文件夹“数据备份”中
Mysqldump -T E:\MySQL\数据备份 MallDB student -u root -p

b、使用带参数的“MySQLdump”命令导出数据

Mysqldump -T E:\MySQL\数据备份 MallDB student -u root -p  --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=\  --lines-terminated-by=\r\n

模块六

6.1创建单表基本查询

1）select:根据where子句条件表达式，从from子句指定的数据表中找出满足条件的记录，再按select子句选出记录中的字段值，把查询结果以表格的形式返回。

2）from:是select语句必须的子句，用于标记从中检索数据的一张或多张数据表或视图。

3）where:用于设定查询条件

4）group by:用于将查询结果按指定的一个字段或多个字段进行分组统计，分组字段或表达式的值相等的被分为同组

5）having:与group by 子句配合使用，用于进一步筛选

6）order by:用于将查询结果按指定的字段进行排序（排序包括asc按升序排序，desc按降序排序）

6.2 查询时选择与设置字段

6.2.1数据表中所有的字段
例如：查询student表的所有字段

select * from student;

6.2.2查询指定的字段
例如：查看订单信息表的订单编号和送货方式

select 订单编号,送货方式 from 订单信息;

6.2.3查询经过计算后的字段
例如：

6.2.4查询时为查询结果指定别名
例如：
select userid as 用户编号,name as 名字 from 用户信息;

6-3查询时选择行
1. 比较运算符：
= ：等于           <>、!>：不等于        <=：小于或等于
<：小于             !<：不小于                >=：大于或等于
>：大于              !>：不大于

2.逻辑运算符：（and或&&、or或||、not或!、Xor）
1）and语法格式：......where 条件表达式1 and 条件表达式2 [and 条件表达式n]

2)or语法格式：......where 条件表达式1 or件表达式2 [and 条件表达式n]
              
3)not语法格式：.....where not 条件表达式

3.模糊匹配运算符：（like、not like）
1）语法格式：[not]  ike '带通配符的字符串'
字符串中可以包括：“%”、"_"
%:0~n个任意字符
_:单个任意字符

4.范围运算符(between and、not between and、in、not in)
1)between and:表示指定范围内
2）not between and：表示指定范围外

5.空值运算符：(is null、is not null)
is null:查询某字段值为空的记录
is not null:查询某字段值不为空的记录

6.子查询比较运算符（all、any、some）

6.3.2查询时去除重复项（distinct)
例如：
select distinct 送货方式 from 订单信息;

6.3.3使用limit关键字查询限定数量的记录
1.不指定初始位置（从第一条开始显示）
格式：limit     <记录数量>
例如：select 订单编号,送货方式 from 订单信息 limit 
3;

2.指定初始位置
格式：limit     <初始位置>,<记录数量>;
例如：select 订单编号,送货方式 from 订单信息 limit 2,6
;

6-3-4用between and创建范围查询
例如：select 商品编号,图书名称,出版日期 from 图书信息 where 出版日期 between '2019-10-01' and '2021-05-01'; 

6-3-5用in关键字创建查询
例如：select 订单编号,客户,收货人 from 订单信息 where 收货人 in ('金元','高首','文云');

6-3-6用like创建模糊查询
例如：select  订单编号,客户,收货人 from 订单信息 where 收货人 like '廖%';

6-3-7创建搜索空值的查询
例如：select  订单编号,客户,收货人 from 订单信息 where 付款方式 is not null;

6-3-8用聚合函数查询(distinct:保证计算时不包含重复的行)
count(*)  统计总记录、count(字段名称)   统计记录数，忽略空值
avg(字段名称)   平均值、sum(字段名称)  总和、max(字段名称)  最大值、min(字段名称)    最小值

例如：1）select count(*) as 图书种数 from 图书信息 where 价格 between 20 and 45;
2）select count(distinct(商品编号)) as 商品种类 from 订购商品;
3）select max(价格) as 最高价格,min(价格) as 最低价格,avg(价格) as 平均价格 from 图书信息;
4)select sum(购买数量) as 总购买数量 from 订购商品;

6-3-9用and创建多条件查询
例如：select  商品编号,图书名称,出版日期 from 图书信息 where 作者='陈承欢' and year(出版日期)>2020;

6-3-10用or创建多条件查询
例如：select  商品编号,图书名称,出版日期 from 图书信息 where 作者='陈承欢' or 出版日期>'2020-05-01';

6-3-11将查询结果保存到另一张数据表中
创建一张数据表“图书汇总信息”：
create table 图书汇总信息1(
出版社名称 varchar(16),
数量合计 int,
金额合计 decimal(10,2),
图书名称列表 varchar(100)
);

向“图书汇总信息1”中插入查询语句的执行结果
insert into 图书汇总信息1
select 出版社信息.出版社名称,
sum(订购商品.购买数量),
sum(订购商品.购买数量*订购商品.优惠价格-优惠金额),
group_concat(图书信息.图书名称)
from 订购商品,图书信息,出版社信息
where 订购商品.商品编号=图书信息.商品编号
and 图书信息.出版社=出版社信息.出版社ID
group by 出版社信息.出版社名称;

6-4对查询结果进行排序     order by 
order by 字段名称1,....,字段名称n [asc|desc]

6-5分组进行数据查询   group by  (havinig  进一步筛选）
group_concat():把每个分组中指定指定值都显示出来
       
group by 字段名称 [having<条件表达式>][with rollup]

6.2创建多表连接查询
格式一：
select <输出字段或表达式列表> from <数据表1>,<数据表2> [where <数据表1.列名> <连接操作符> <数据表2.列名>]

格式二：
select <输出字段或表达式列表>
from <数据表1> <连接类型> <数据表2> [on(<连接条件>)]

内连接：inner join 
外连接：out join
交叉连接：cross join