大数据开发--1.4 MySQL基础

一. 数据库介绍

数据库的概述

• 数据库就是存储数据的仓库，其本质是一个文件系统，按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。
• 随着互联网的高速发展，大量的数据在不断地产生，伴随而来的是如何高效安全的存储数据和处理数据，而这一问题成为了信息时代的一个非常大的问题。
• 使用数据库可以高效的有条理的存储数据：
  1. 可以结构化存储大量数据。
  2. 可以有效的保持数据的一致性、完整性。

常见数据库

        数据库 又分为关系型数据库和非关系型数据库。

数据库类型	数据库	介绍
关系型数据库	MYSQL	开源免费的数据库，中型的数据库，已经被Oracle收购了，MySQL6.x版本也开始收费，
	Oracle	收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MySQL.
	DB2	IBM公司的数据库产品，收费的。常应用在银行系统中。
	SQLServer	MicroSoft公司收费的中型数据库。C#、.net等语言常使用。
	SQLite	嵌入式的小型数据库，应用在手机端。
非关系型数据库	Redis	是一个小而美的数据库，主要用在key-value的内存缓存，读写性能极佳。
	HBase	HBase是列式数据库，目标是高校存储大量数据。
	MongoDB	MongoDB是文档型数据库，非常接近关系型数据库的。

                常用数据库：MySQL、Oracle、Redis、Hbase、MongoDB

二. MySQL数据库安装和使用

*默认已经更换阿里云的yum源

卸载系统自带的mariadb-lib

1. 查看mariadb版本

   rpm -qa | grep mariadb

   
2. 卸载Mariadb

   rpm -e mariadb-libs --nodeps

   

3. 重启机器
    

   reboot

   

上传安装包并解压

1. 创建/opt/software目录用于上传文件

   mkdir software

   
2. 将mysql的安装包上传到/opt/software目录中
   
3. 创建/opt/server目录

   mkdir server

4. 解压安装包到/opt/server

    tar -xvf mysql-8.0.13-1.el7.x86_64.rpm-bundle.tar -C /opt/server/

   

按顺序安装

1. 按顺序安装【必须安装-注意顺序】

   rpm -ivh mysql-community-common-8.0.13-1.el7.x86_64.rpm 

   

   rpm -ivh mysql-community-libs-8.0.13-1.el7.x86_64.rpm 

   
    

    rpm -ivh mysql-community-client-8.0.13-1.el7.x86_64.rpm 

   
    

    rpm -ivh mysql-community-server-8.0.13-1.el7.x86_64.rpm 

   

2. 初始化数据库

   mysqld --initialize --console

3. 目录授权，否则启动失败

   chown -R mysql:mysql /var/lib/mysql/

4. 启动mysql服务

   systemctl start mysqld

   将mysql服务设置为开机启动

   systemctl enable mysqld

   systemctl daemon-reload

5. 查看mysql的服务状态

   service mysqld atatus

   

6. 在/var/log/mysqld.log下查看临时密码

   cat /var/log/mysqld.log

   

7. 用临时密码登录到数据库

   mysql -u root -p
   输入临时密码（输入时不会显示出来，输入完直接回车）

   

8. 执行下列mysql命令，修改密码：

   alter USER 'root'@'localhost' IDENTIFIED BY '新密码';

   

9. 依次执行下列mysql命令 授权远程连接
   查看所有用户是否可以远程连接，依次执行下列命令：

   show databases;

   
    

   use mysql;

   
    

   select host,user,authentication_string,plugin from user;

   
        host为localhost，说明用户只能本地连接mysql服务
        修改root用户host值，使root用户可以远程登录

   update user set host = "%" where user = 'root';

   
        刷新

   flush privileges；

   
         开启mysql远程访问权限

   use mysql;

   
    

   alter USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root用户的密码'；

   
    

   flush privileges;

   

三. 登录MySQL数据库

        MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录，有两种登陆方式。

方式一：

mysql -u 用户名 -p 密码

或者：

musql -u 用户名 -p 回车输入密码

方式二：

mysql --host=ip地址 --user=用户名 --password=密码

四. MySQL图形化开发工具-DataGrip

概述

        DataGrip是JetBrains公司推出的管理数据库的产品，功能非常强大，可以兼容各种数据库，另外 JetBrains公司还有一款知名的IDE开发工具IDEA，用户体验非常不错。        
                             

安装

打开

五. DataGrip连接MySQL

打开数据库资源管理器

六. SQL语句

介绍

        结构化查询语句（Structured Query Language）简称SQL，是关系型数据库管理系统都需要遵循的规范。是数据库认识的语句。不同的数据库生产厂商都支持SQL语句，但都有特有内容。
普通话：各数据库厂商都遵循的ISO标准。

方言：数据库特有的关键字。

SQL语句分类

1. 数据定义语句

        简称DDL（Data Definition Language），用来定义数据库对象：数据库，表列等。

关键字：create、alter、drop等。

2. 数据操作语言

        简称DML（Data Manipulation Language），用来对数据库中表的记录进行更新。

关键字：insert、delete、update等。

3. 数据控制语言

        简称DCL（Data Control Language），用来定义数据库的访问权限和安全级别，及创建用户。

4. 数据查询语言

        简称DQL（Data Query Language）用来查询数据库中表的记录。

关键字：select、from、where等。

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾，可使用空格和缩进来增强语句的可读性。
• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  例如：

  SELECT * FROM user;

• 同样可以使用/**/的方式完成注释       --      #

MySQL常使用的数据类型

分类	类型名称	说明
整数类型	tinyInt	很小的整数
	smallint	小的整数
	mediumint	中等大小的整数
	int（integer）	普通大小的整数
小数类型	float	单精度浮点数
	double	双精度浮点数
	decimal（m，d）	压缩严格的定点数        decimal（10，2）
日期类型	year	YYYY             1901~2155
	time	HH:MM:SS          -838:59:59~838:59:59
	date	YYYY-MM-DD         1000-01-01~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS        1000-01-01 00:00:00~9999-12-31-23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 1970~01~01 00:00:00 UTC~2038-01-19 23:59:59
文本、二进制类型	char（m）	m为0~255之间的整数定长            char(10) tom
	varchar（m）	m为0~65535之间的整数变长        varchar(10) tom
	tinyblob	允许长度0~255字节
	blob	允许长度0~65535的字节
	mediumblob	允许长度0~167772150字节
	longblob	允许长度0~4292967295字节
	tinytext	允许长度0~255字节
	text	允许长度0~65535字节
	mediumtext	允许长度0~167772150字节
	longtext	允许长度0~4294967295字节
	varbinary（m）	允许长度0~M个字节的变长字节字符串
	binary（m）	允许长度0~m个字节的定长字节字符串

七. DDL之数据库操作

1. 创建数据库

CREATE DATABASE 数据库名;    #直接创建数据库，如果存在则报错

CREATE DATABASE IF NOT EXISTS teat1;    #如果数据库不存在则创建

CREATE DATABASE 数据库名 CHARACTER SET 字符集;    #创建数据库时设置字符集

2. 查看MySQL服务器中所有的数据库

SHOW DATABASES;

3. 删除数据库

DROP DATABASE 数据库名;

4. 使用数据库

选择数据库

USE 数据库名字;

查看正在使用的数据库

SELECT DATABASE();

八. DDL之数据表操作

创建表

CREATE TABLE IF NOT EXISTS 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束],
 ……
);

类型

• varchar(n)        字符串
• int        字符型
• double            浮点
• date         时间
• timestamp         时间戳

约束

        primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。

实例

#创建分类表
CREATE TABLE category(
cio varchar(20) primary key,    #分类ID
cname varchar(100)              #分类名称
);

查看表

查看数据库中的所有表

SHOW TABLES;

查看表结构

DESC 表名;

删除表

DROP TABLE;

实例

DROP TABLE category;

修改表结构格式

        修改表添加列

ALTER TABLE 表名 ADD 列名 类型(长度)[约束];

例如：

#为分类表添加一个新的字段为分类描述 varchar(20)
ALTER TABLE category ADD 'desc' VARCHAR(20);

        修改表修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型(长度) 约束;

例如：

# 为分类表的分类描述字段更换为description varchar(30)
ALTER TABLE category CHANGE 'desc' description VARCHAR(30);

        修改表删除列

ALTER TABLE 表名 DROP 列名;

例如：

# 删除分类表中的description这列
ALTER TABLE category DROP description;

        修改表名

RENAME TABLE 表名 TO 新表名;

例如：

# 为分类表category改名成category2
RENAME TABLE 'category' TO category2;

九. DML数据库操作语言

插入表记录：insert

-- 向表中插入数据

INSERT INTO 表(字段1，字段2，字段3……) VALUES(值1，值2，值3);

-- 向表中插入所有字段，字段的顺序为创建表时的顺序

INSERT INTO 表 VALURS(值1，值2，值3……);

注意

• 值与字段必须对应，个数相同，类型相同
• 值的数据大小必须在字段长度范围内
• 除了数值类型外，其他的字段类型的值必须使用引号引起。（建议单引号）
• 如果要插入空值，可以不写字段，或者插入null。

例如

INSERT INTO category （cid,cname） VALUES('c001','电器');
INSERT INTO category （cid,cname） VALUES('c002','服饰');
INSERT INTO category （cid,cname） VALUES('c003','化妆品');
INSERT INTO category （cid,cname） VALUES('c004','书籍');
INSERT INTO category （cid） VALUES('c005');
INSERT INTO category values（'06','玩具'),('07','蔬菜');

更新表记录：update

        用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

-- 更新所有记录的指定字段

update 表名 set 字段名 = 值,字段名 = 值,……;

-- 更新符号条件记录的指定字段

update 表名 set 字段名 = 值,字段名 = 值,…… where 条件;

注意

• 列名的类型与修改的值要一致
• 修改值的时候不能超过最大长度
• 除了数值类型外，其他的字段类型的值必须使用引号引起

删除记录：delete

DELETE FROM 表名 [WHERE 表名];

                或者

TRUNCATE TABLE 表名;    #清空表

例子

DELETE FROM CATEGORY WHERE CID = '005';    #删除cid为005的记录
TRUNCATE CATEGORY;    #清空表数据

十. SQL约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录

1. 主键必须包含唯一的值。
2. 主键列不能包含NULL值。
3. 每个表都应该有一个主键，并且每个表只能有一个主键。

添加主键约束

方式一：创建表时，在字段描述处，声明指定字段为主键

CREATE TABLE Persons2;
(
ID INT PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

方式二：创建表时，在constraint约束区域，声明指定字段为主键

格式：

[constraint 名称] primary key (字段列表)

        关键字constrain可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。

        字段列表需要使用小括号括住，如果有多字段需要使用都好分隔符。声明俩个以上字段为主键，我们称为联合主键。

CREATE TABLE Persons2
(
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName)
);

删除约束主键

        如需撤销 PRIMARY KEY 约束，请使用下面的SQL语句：

ALTER TABLE Persons DROP PRIMARY KEY;

自动增长列        auto_increment

        我们通常希望在每次插入新记录时，数据库自动生成字段的值。

        我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整型，自动增长列必须为键（一般是主键）。

        下列SQL语句把“Persons”表中的“id”列定义为auto_increment主键

CREATE TABLE Persons4
(
Id int PRIMARY KEY AUTO_INCRMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)

        向persons添加数据时，可以不为Id字段设置值，也可以设置成null，数据库将自动维护主键值：

INSERT INTO Person4 (FirstName,LastName) VALUES ('Bill','Gates')
INSERT INTO Person4 (Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')

        扩展：默认AUTO_INCREMENT 的开始值是1，如果希望修改起始值，请使用下列SQL语法：

ALTER TABLE Persons AUTO_INCREMENT = 100;

非空约束

        NOT NULL约束强制列不接受NULL值。

        NOT NULL约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。

        下面的SQL语句强制“Id”列和“LastName”列不接受NULL值。

CREATE TABLE Persons5
(
Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

唯一约束

• UNIQUE约束唯一标识数据库表中的每条记录。
• UNIQUE和PRIMARY KEY约束均为列或列集合提供了唯一性的保证。
• PRIMARY KEY拥有自动定义的UNIQUE约束。
• 注意：每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束。

        添加唯一约束，创建表时，在字段描述处，声明唯一：

CREATE TABLE Persons
(
Id int UNIQUE,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

十一. DQL操作--（实例）

准备工作

# 创建商品表：
create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,' 联想 ',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,' 海尔 ',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,' 雷神 ',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,' 杰克琼斯 ',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,' 真维斯 ',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,' 花花公子 ',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,' 劲霸 ',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,' 香奈儿 ',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,' 相宜本草 ',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,' 面霸 ',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,' 好想你枣 ',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,' 香飘飘奶茶 ',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,' 海澜之家 ',1,'c002');

语法

SELECT [DISTINCT]
* | 列名，列名
from 表
where 条件

1. 简单查询

# 1. 查询所有的商品
SELECT * FROM product;

# 2. 查询商品名和商品价格
SELECT pname,price FROM product;

# 3. 别名查询，使用关键字时as （as可以省略的）
# 3.1 表别名：
SELECT * FROM product AS p; 

# 3.2 列别名
SELECT pname AS pn FROM product;

# 4. 去掉重复值
SELECT DISTINCT price FROM product;

# 5. 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
SELECT pname,price+10 FROM product;

2. 条件查询

比较运算符	> < <= >= = <> !=	大于、小于、大于（小于）等于、不等于
	BETWEEN……AND……	显示再某一区间的值 （含头含尾）[]
	IN（set）	显示在in列表中的值，例：in（100，200）
	LINE'张%'LIKE'%涛%'	模糊查询，Like语句中，%代表零个或多个任意字符，_代表一个字符，例如：first_name like
	IS NULL IS NOT NULL	判断是否为空
逻辑运算符	and	多个条件同时成立
	or	多个条件任一成立
	not	不成立，例：where not(salary>100)

实例：

# 查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname='花花公子';

# 查询价格为800商品
SELECT * FROM product WHERE price=800;

# 查询价格不是800的所有商品：
SELECT * FROM product WHERE price!=800;
SELECT * FROM product WHERE price<>800;
SELECT * FROM product WHERE NOT(price=800);

# 查询价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;

# 查询价格在200到1000之间所有商品
SELECT * FROM product WHERE price >=200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

# 查询商品价格是200或800的所有商品：
SELECT * FROM product WHERE price=200 OR price=800;
SELECT * FROM product WHERE price IN(200,800);

# 查询含有‘霸’字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';

# 查询以‘香’开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';

# 查询第二个字为‘想’的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';

# 查询没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;

# 查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;

3. 排序查询--升序、降序、去重复

        通过order by语句，可以将查询出的结果进行排序。在那时放置在select语句的最后。

格式：

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
    ASC 升序 ( 默认 )
    DESC 降序

# 1. 使用价格排序（降序）
SELECT * FROM product ORDER BY price DESC;

# 2. 在价格排序（降序）的基础上，以分类排序（降序）
SELECT * FROM product ORDER BY price DESC,category_id DESC;

# 3. 显示商品的价格（去重复），并排序（降序）
SELECT DISTINCT price FROM product ORDER BY price DESC;

4. 聚合查询--总条数、总和、平均、最大最小

# 1. 查询商品的总条数
SELECT COUNT(*) FROM product;

# 2. 查询价格大于 200 商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;

#3 查询分类为‘c001’的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';

#4 查询分类为‘c002’所有商品的平均价格
SELECT AVG(price) FROM product WHERE categ ory_id = 'c002';

# 5. 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

5. 分组查询--GROUP BY、HAVING

        分组查询时指使用group by字句对查询信息进行分组。

格式：

SELECT 字段1,字段2,…… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;

        分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

having与where的区别：

1. having是在分组后对数据进行过滤；where是分组前对数据进行过滤。
2. having后面可以使用分组函数（统计函数）；where后面不可以使用分组函数。

实例：

# 1. 统计各个分类商品的个数
SELECT category_id,COUNT(*) FROM product GROUP BY category_id;

# 2. 统计各个分类商品的个数，且只显示个数大于1的信息
SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

6. 分页查询

        分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式：

SELECT 字段 1 ，字段 2... FROM 表名 LIMIT M,N
    M: 整数，表示从第几条索引开始，计算方式 （当前页 -1 ） * 每页显示条数
    N: 整数，表示查询多少条数据
    SELECT 字段 1 ，字段 2... FROM 表明 LIMIT 0,5
    SELECT 字段 1 ，字段 2... FROM 表明 LIMIT 5,5

7. INSERT INTO SELECT 语句

        INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已经存在的表中。

基本语法：

INSERT INTO table2 SELECT column_name(s) FROM table1;

实例：

create table product2(
pid int primary key,
pname varchar(20),
price double
);

insert into product2 select pid,pname,price from product where category_id = 'c001';

十二. 多表操作

概述

        实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项就需要分类表（category）、商品表（product）、订单表（orders）等多张表。且这些表的数据之间存在一定的关系。

表与表之间的关系

一对多关系：

        常见实例：客户和订单，分类和商品，部门和员工

一对多建表原则：

        在从表（多方）创建一个字段，字段作为外键指向主表（ 一方）的主键。

外键约束

        现在我们有两张表“分类表”和“商品表”，为了表名商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键。

        此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点：

        从表外键的值是对主表主键的引用。

        从表外键类型，必须与主表主键类型一致。

声明外键约束

alter table 从表 add [constraint] [ 外键名称 ] 
foreign key (从表外键字段名) references 主表 (主表的主键);

        [ 外键名称 ] 用于删除外键约束的，一般建议“ _fk” 结尾： alter table 从表 drop foreign key 外键名称
        alter table 从表 add [constraint] [ 外键名称 ] foreign key (从表外键字段名) references 主表 ( 主表的主键);

分析

• category分类表：为一方，也就是主表，必须提供主键cid
• products商品表，为多方，也就是从表，必须提供外键category_id

实现

# 创建分类表
CREATE TABLE category(
cid varchar(32) PRIMARY KEY,
cname varchar(100) #分类名称
);

# 创建商品表
CREATE TABLE products(
pid varchar(32) PRIMARY KEY,
name varchar(40),
price double,
category_id varchar(32),
);

# 添加约束
ALTER TABLE products ADD CONSTRAINT product_fk FOREIGN KEY (category_id) references category (cid);

操作

# 1 向分类表中添加数据
INSERT INTO category (cid,cname) VALUES('c001','服装');

# 2 向商品表添加普通数据，没有外键数据，默认为null
INSERT INTO products(pid,name) values('p001','商品名称');

# 3 向商品表添加普通数据，含有外键信息（category表中存在这条数据）
INSERT INTO products(pid,name,category_id) VALUES('p002','商品名称2','c001');

# 4 向商品表添加普通数据，含有外键信息（category表中存在这条数据）--失败，异常

INSERT INTO products(pid,name,category_id) values('p003','商品名称2','c999');

# 5 删除指定分类（分类被商品使用）--执行异常
DELETE FROM category WHERE cid ='c001';

实例

准备工作

CREATE TABLE category(
cid VARCHAR(32) PRIMARY KET,
cname VARCHAR(50)
);
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY,
pname VARCHAR(50),
price INT,
flag VARCHAR(2),    # 是否上架标记：1标识上架、0表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN (category_id) REFERENCES category(cid)
);