一、初识数据库

一 数据库管理软件的由来

基于我们之前所学，数据要想永久保存，都是保存于文件中，毫无疑问，一个文件仅仅只能存在于某一台机器上。

如果我们暂且忽略直接基于文件来存取数据的效率问题，并且假设程序所有的组件都运行在一台机器上，那么用文件存取数据，并没有问题。

很不幸，这些假设都是你自己意淫出来的，上述假设存在以下几个问题。。。。。。

 

1、程序所有的组件就不可能运行在一台机器上

#因为这台机器一旦挂掉则意味着整个软件的崩溃，并且程序的执行效率依赖于承载它的硬件，而一台机器机器的性能总归是有限的，受限于目前的硬件水平，就一台机器的性能垂直进行扩展是有极限的。

#于是我们只能通过水平扩展来增强我们系统的整体性能，这就需要我们将程序的各个组件分布于多台机器去执行。

2、数据安全问题

#根据1的描述，我们将程序的各个组件分布到各台机器，但需知各组件仍然是一个整体，言外之意，所有组件的数据还是要共享的。但每台机器上的组件都只能操作本机的文件，这就导致了数据必然不一致。

#于是我们想到了将数据与应用程序分离：把文件存放于一台机器，然后将多台机器通过网络去访问这台机器上的文件（用socket实现），即共享这台机器上的文件,共享则意味着竞争，会发生数据不安全，需要加锁处理。。。。

3、并发 

根据2的描述，我们必须写一个socket服务端来管理这台机器（数据库服务器）上的文件，然后写一个socket客户端，完成如下功能：

#1.远程连接（支持并发）
#2.打开文件
#3.读写（加锁）
#4.关闭文件

总结：

#我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，
这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，
专注于自己的程序逻辑的编写。

二 数据库概述 

1 什么是数据（Data）

描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机

在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容，如：

1 egon,male,18,1999,山东,计算机系,2017,oldboy

单纯的一条记录并没有任何意义，如果我们按逗号作为分隔，依次定义各个字段的意思，相当于定义表的标题

1 name,sex,age,birth,born_addr,major,entrance_time,school #字段
2 egon,male,18,1999,山东,计算机系,2017,oldboy #记录

2 什么是数据库（DataBase，简称DB）

数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的

过去人们将数据存放在文件柜里，现在数据量庞大，已经不再适用

数据库是长期存放在计算机内、有组织、可共享的数据即可。

数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享

3 什么是数据库管理系统（DataBase Management System 简称DBMS）

在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键

这就用到了一个系统软件---数据库管理系统

如MySQL、Oracle、SQLite、Access、MS SQL Server

mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。

三 mysql介绍

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。

MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

mysql是什么

#mysql就是一个基于socket编写的C/S架构的软件
#客户端软件
　　mysql自带：如mysql命令，mysqldump命令等
　　python模块：如pymysql

数据库管理软件分类

#分两大类：
　　关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
　　非关系型：mongodb，redis，memcache

#可以简单的理解为：
    关系型数据库需要有表结构
    非关系型数据库是key-value存储的，没有表结构

四  初识sql语句

有了mysql这个数据库软件，就可以将程序员从对数据的管理中解脱出来，专注于对程序逻辑的编写

mysql服务端软件即mysqld帮我们管理好文件夹以及文件，前提是作为使用者的我们，需要下载mysql的客户端，或者其他模块来连接到mysqld，

然后使用mysql软件规定的语法格式去提交自己命令，实现对文件夹或文件的管理。该语法即sql（Structured Query Language 即结构化查询语言）

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
#1、DDL语句    数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
#2、DML语句    数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
#3、DCL语句    数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

#1. 操作文件夹
        增：create database db1 charset utf8;
        查：show databases;
        改：alter database db1 charset latin1;
        删除: drop database db1;


#2. 操作文件
    先切换到文件夹下：use db1
        增：create table t1(id int,name char);
        查：show tables
        改：alter table t1 modify name char(3);
              alter table t1 change name name1 char(2);
        删：drop table t1;
    

#3. 操作文件中的内容/记录
        增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
        查：select * from t1;
        改：update t1 set name='sb' where id=2;
        删：delete from t1 where id=1;

        清空表：
            delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
            truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

            auto_increment 表示：自增
            primary key 表示：约束（不能重复且不能为空）；加速查找

 

 

不懂观察下面的程序：

-- 创建数据库
create database test;

-- 查看创建好的数据库
show create database test;

-- 查看所有数据库列表
show databases;

-- 使用数据库
use test;

-- 删除数据库
drop database test;

-- 创建员工信息表
create table emp(
    depid char(3),
    depname varchar(20),
    peoplecount int
);
        
-- 查看表是否创建成功
show tables;

-- 删除数据表
drop table emp;

-- 创建带约束条件的emp表
create table emp(
        depid char(3) primary key,
        depname varchar(20) not null default '-',
        peoplecount int unique
);

desc emp;

-- 创建含各种约束条件的数据表
CREATE TABLE example(id INT PRIMARY KEY AUTO_INCREMENT, -- 创建整数型自增主键
            lname VARCHAR(4) NOT NULL, -- 创建非空字符串字段
            math INT DEFAULT 0, -- 创建默认值为0的整数型字段
            minmax FLOAT UNIQUE -- 创建唯一约束小数型字段
 );
desc fruits;
-- 创建fruits数据表
create table fruits(
    f_id char(10) not null,
    s_id int not null,
    f_name varchar(255) not null,
    f_price decimal(8,2) not null,
    primary key(f_id)
);

-- 插入数据
insert into fruits(f_id,s_id,f_name,f_price)
values('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',25.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('b5',107,'xxxx',3.6);

select * from fruits;

-- 创建大气质量表
create table Monthly_Indicator(
    city_name varchar(20) not null,
    month_key date not null,
    aqi int(4) not null default 0,
    aqi_range varchar(20) not null default '-',
    air_quality varchar(20) not null default '-',
    pm25 float(6,2) not null default 0,
    pm10 float(6,2) not null default 0,
    so2 float(6,2) not null default 0,
    co float(6,2) not null default 0,
    no2 float(6,2) not null default 0,
    o3 float(6,2) not null default 0,
    ranking int(4) not null default 0,
    primary key(city_name,month_key)
    );
    desc monthly_indicator;
-- 为Monthly_Indicator表导入外部txt文件
load data local infile 'D:/data/all.txt' 
    into table Monthly_Indicator
    fields terminated by '\t'
    ignore 1 lines;
    
-- 检查倒入内容Monthly_Indicator
Select * from Monthly_Indicator;

-- 检查导入数据总行数Monthly_Indicator
Select count(*) from Monthly_Indicator;

-- 检查表结构
Desc Monthly_Indicator;

-- 更改表名
alter table emp rename empdep;

-- 更改字段数据类型
alter table empdep modify depname varchar(30);

-- 查看表结构
desc empdep;

-- 更改字段名称
alter table empdep change depname dep varchar(30);

-- 更改字段名称及字段数据类型
alter table empdep change dep depname varchar(20);

-- 为表添加新字段
alter table empdep add maname varchar(10) not null;

-- 将字段顺序改为第一位
alter table empdep modify maname varchar(10) not null first;

-- 将字段顺序改为另一个字段之后
alter table empdep modify maname varchar(10) after depid;

-- 删除字段
alter table empdep drop maname;

-- 删除主键
alter table empdep drop primary key;

-- 增加主键
alter table empdep add primary key(city_name,month_key);

-- 查看表结构
desc empdep;

-- SQL语句查询
-- 查询大气质量表中的全部内容
select * from monthly_indicator;

-- 查询北京的大气质量数据
select * from monthly_indicator
where city_name = '北京';

-- 查询不同月份PM2.5的最大值
select month_key, max(pm25) from monthly_indicator
group by month_key;

-- 降序查询不同城市PM10的平均值
select city_name, avg(pm10) from monthly_indicator
group by city_name
order by avg(pm10) ;

-- 对大气质量表进行有选择的查询
select city_name, avg(pm25), avg(pm10) from Monthly_Indicator
where pm25 > 50
group by city_name, month_key having city_name <> '北京'
order by avg(pm25) desc;
 /*
165 以上程序是这样读的，从数据表Monthly_Indicator中，先找出pm25>50的所有记录，
166 然后再对这些记录按照【city_name, month_key having city_name <> '北京'】
167 不同的city_name，不同的month_key进行分组，同时取city_name 不是 '北京'的，
168 也就是删除city_name是北京的记录。在得到以上结果后，【elect city_name, avg(pm25), avg(pm10)】选出不同的city_name下，
pm25的平均值，pm10的平均值，最后【order by avg(pm25) desc】，以降序的顺序排列。
*/


select city_name, pm25, pm10 from Monthly_Indicator
where pm25 > 50 and city_name <> '北京'
order by pm25 desc;

-- 链接练习
create table t1(
    key1 varchar(20),
    v1 int(4)
    );
    
load data local infile 'D:/data/t1.csv' 
    into table t1
    fields terminated by ','
    ignore 1 lines;
    
create table t2(
    key2 varchar(20),
    v2 int(4)
    );

load data local infile 'D:/data/t2.csv' 
    into table t2
    fields terminated by ','
    ignore 1 lines;
    
select * from t1;
select * from t2;

select * from t1 left join t2 on t1.key1 = t2.key2; -- 左链接
/*
以上程序是这个意思，【t1 left join t2 on t1.key1 = t2.key2】意思是以表t1作为主表，用左连接的方式连接表 t2，
 on 后面跟 关键字段，分别是   表t1中的key1     表t2中的key2,  [select *]表示选取全部结果
 其实其功能就相当于power query当中的表的合并查询，方式就是左反。   下同
*/

select t1.*, t2.* from t1 right join t2 on t1.key1 = t2.key2; -- 右链接
select t1.*, t2.* from t1 inner join t2 on t1.key1 = t2.key2; -- 内链接


/*
以上的方式统称横向合并，注意当中字段的对应关系   
                        多对多（不符合业务逻辑） 
                        多对一（最常用，就是选取其中一个表的主键（非空且唯一）作为关键字段，另一个表选取非主键作为关键字段）
                        一对一（不符合数据库设计者的初衷，如果两个关键字段都是主键，就没有必要这样做了）
*/


-- 以下是纵向合并，类似于power query中的追加查询 

-- 用union合并t1与t2表
-- union: 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行
select t1.* from t1
union
select t2.* from t2;


-- 用union all合并t1与t2表
-- 用于合并两个或多个 SELECT 语句的结果集，保留重复行
select t1.* from t1
union all
select t2.* from t2;

-- -----------------------------------------------------------------------------------------------------------------------
-- 查询操作符
-- 用and操作符查询s_id为101并且f_id为a1的水果记录
select * from fruits
where s_id = 101 and f_id = 'a1';


-- 用or操作符查询苹果或者橙子的相关记录
select * from fruits
where f_name = 'apple' or f_name = 'orange';


-- 用in操作符查询苹果和橙子的相关记录
select * from fruits
where f_name in('apple','orange');


-- 用not in操作符查询苹果和橙子之外的水果的相关记录
select * from fruits
where f_name not in('apple','orange');


-- 用between...and操作符查询f_price在10元到20元之间的水果记录
select * from fruits
where f_price between 10 and 20;


-- 用like操作符查询所有f_name由a开始的水果记录，%表示不限字符数，是通配符
select * from fruits
where f_name like 'a%';

-- 用like操作符查询所有f_name中含有a的，不限定a的位置
select * from fruits
where f_name like '%a%';

-- 用like操作符查询所有f_id由b开始且字符长度为两位的水果记录
select * from fruits
where f_id like 'b_';

-- 用is null操作符查询所有f_name为空的水果记录
select * from fruits
where f_name is null;

-- 查询fruits表中所有不重复的s_id
select distinct s_id from fruits;

-- -----------------------------------------------------------------------------------------------------------------------
-- 子查询：写在()中，把内层查询结果当做外层查询参照的数据表来用


-- 用in操作符与子查询语句来查询所有f_id对应的f_price在10元到20元之间的水果记录
select * from fruits where f_id in
(select f_id from fruits where f_price between 10 and 20);

-- 用all操作符与子查询语句来查询所有f_price大于20元的水果记录
select * from fruits where f_price > all
(select f_price from fruits where f_price <=20);
/*
以上程序的解读：【(select f_price from fruits where f_price < 20);】先找出所有价格小于20的f_price，再找出所有价格
都比之前范围大的f_price所对应的记录
*/

-- 用exists操作符与子查询语句来查询是否存在f_price大于30元的水果记录
select * from fruits where exists
(select * from fruits where f_price > 30);

-- 用as将fruits表名重命名为f后使用
select f.* from fruits as f;

-- 显示f_price金额最大的前三名水果记录
select * from fruits
order by f_price desc
limit 3;

-- 使用abs函数求所有水果平均值与最大值差值的绝对值
select abs(avg(f_price)-max(f_price)) from fruits;

-- 使用length函数求每个f_name的名字与他们的字符长度
select f_name, length(f_name) from fruits group by f_name;

-- 使用now函数求当前的日期和时间
select now();

-- 使用group_concat函数查询不同s_id下对应的所有f_name信息
SELECT s_id, GROUP_CONCAT(f_name) FROM fruits
GROUP BY s_id;
/*
这段话的是这样读的，找出所有的s_id的记录，然后把后面的f_name以逗号的形式连接起来
*/
--  ---------------------------------------------------------------------------------------


--  concat()的用法就是用来连接字符串
-- 语法：concat(str1, str2,...)
-- 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
-- 使用concat函数在f_name字段值前添加'fruit_'信息
update fruits set f_name = concat('fruit_',f_name);
select * from fruits;

-- 删除f_id为'b5'的数据记录
delete from fruits where f_id = 'b5';
select * from fruits;

-- 单表查询练习，彩票数据规则
-- 奖票分析 --------------------------------------------------------------
-- 导入测试用完整数据
create table Final(
    FNo varchar(10) not null,
    TNo varchar(10) not null,
    Mark varchar(20) not null,
    reward varchar(20) not null,
    bingovalue int not null
);

load data local infile 'E:/LiWork/CDA/data/final.csv' 
    into table Final
    fields terminated by '\,';
    
alter table Final add RowNumber int primary key auto_increment; -- 自增字段，用来记录彩票张数

select * from Final;
select count(*) from Final;


#1求总中奖张数及金额
select count(bingovalue) as 中奖总张数, sum(bingovalue) as 中奖总金额
from Final
where bingovalue <> 0;

#2求各不同奖幅的张数及金额
select bingovalue as 奖幅, count(bingovalue) as 张数, sum(bingovalue) as 金额
  from Final 
  group by bingovalue
  having bingovalue <> 0;

#3求中奖张数与总张数占比，中奖金额与总金额的占比
set @allcount = (select count(bingovalue) from Final);
set @allsum = (select count(bingovalue) * 5 from Final);
select count(bingovalue)/@allcount as 中奖张数占比, 
sum(bingovalue)/@allsum as 中奖金额占比 from Final
where bingovalue <> 0;

#4检查每个本号下有100张彩票
select FNo, count(FNo) from Final
group by FNo
having count(FNo) <> 100;

#5检查每个本号下最多有一张中奖票金额超过50元
select FNo, count(FNo) from Final
where bingovalue > 50
group by FNo
having count(FNo)>1;

#6检查每本彩票中最多连续7张无奖票
#创建bingonumber1
create table bingonumber1 as (
select Rownumber, bingovalue, FNo from Final
where bingovalue > 0
order by rownumber);

select * from bingonumber1;

-- drop table bingonumber1; -- 删除表

#删除第一条记录
delete from bingonumber1 limit 1;

alter table bingonumber1 add numberkey int primary key auto_increment; -- 自增

#重新排序
alter table bingonumber1 modify numberkey int first;

#创建bingonumber2
create table bingonumber2 as ( 
select Rownumber, bingovalue, FNo from Final
where bingovalue > 0
order by rownumber);

-- drop table bingonumber2; -- 删除表

alter table bingonumber2 add numberkey int primary key auto_increment; -- 自增

#重新排序
alter table bingonumber2 modify numberkey int first;

#检查数据内容及记录行数
select * from bingonumber1;
select * from bingonumber2;
select count(*) from bingonumber1;
select count(*) from bingonumber2;

#检查测试结果
select b1.*, b2.*, (b1.rownumber - b2.rownumber) as gap from bingonumber1 as b1, bingonumber2 as b2
where b1.numberkey = b2.numberkey
and b1.FNo = b2.FNo
and (b1.rownumber - b2.rownumber) > 7;

-- 多表查询，电商数据查询练习
use test;
-- ----GoodsColor----
create table goodscolor(
    ColorID varchar(4) not null default '-',
    ColorNote varchar(20) not null default '-',
    ColorSort int not null default 0,    
    pt varchar(9) not null default '-'
);

#导入数据
load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/goodscolor.csv' 
    into table goodscolor
    fields terminated by ','
    ignore 1 lines;
    
-- ----GoodsSize----
create table goodssize(
    SizeID varchar(4) not null default '-',
    SizeNote varchar(100) not null default '-',
    SizeSort int not null default 0,    
    pt varchar(9) not null default '-'
);

#导入数据
load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/goodssize.csv' 
    into table goodssize
    fields terminated by ','
    ignore 1 lines;

-- ----OrderDetail----
create table OrderDetail(
    OrderID varchar(6) not null default '-',
    GoodsID varchar(6) not null default '-',
    GoodsPrice double not null default 0,
    ColorID varchar(4) not null default '-',
    SizeID varchar(4) not null default '-',
    Amount int not null default 0
);

#导入数据
load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/OrderDetail.txt' 
    into table OrderDetail
    fields terminated by '\t'
    ignore 1 lines;
    
select * from orderdetail;
select * from goodscolor;
select * from goodssize;

-- 1.倒序查询卖的金额最多的产品
select GoodsID, sum(GoodsPrice*amount) from orderdetail
group by goodsid
order by sum(GoodsPrice*amount) desc;

-- 2.查询不同尺码下的产品销售数量
select SizeNote, sum(amount) from orderdetail
left join goodssize on orderdetail.sizeid = goodssize.sizeid
group by orderdetail.sizeid
order by sum(amount) desc;

-- 3. 查询不同颜色下的产品销售金额
select colornote as 颜色, sum(goodsprice * amount) as 销售额 from orderdetail as od
left join goodscolor as gc on od.colorid=gc.colorid
group by od.colorid
order by sum(goodsprice * amount) desc;

-- 4. 查询不同尺码下的不同颜色的产品销售金额
select sizenote,colornote,sum(goodsprice * amount) from orderdetail as od
left join goodssize as gs on od.sizeid = gs.sizeid
left join goodscolor as gc on od.colorid = gc.colorid
group by od.sizeid, od.colorid
order by sum(goodsprice * amount) desc;