大数据基础——MySql篇

MySql

什么是数据库

• 数据库：保存数据的仓库，他在电脑中是一个文件系统，然后把数据都保存在这些特殊的文件中，并且使用固定的语言（SQL语言）去操作文件中的数据。
• 数据库就是按照数据结构来组织，存储和管理数据的建立在计算机存储设备上的仓库。

数据库的分类

• 关系型数据库
  • oracle，MySQL，SqlServer，DB2
• 非关系型数据库
  • Redis，Hbase，ElasticSearch ，MongoDB

SQL基础知识

• 结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范，是数据库认识的语句。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

• 普通话：各数据库厂商都遵循的ISO标准。

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

• SQL分类：

  • 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表， 列等。关键字：create，alter，drop等
  • 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行 更新。关键字：insert，delete，update等
  • 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级 别，及创建用户。
  • 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字： select，from，where等

• SQL通用语法

  • SQL语句可以单行或多行书写，以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写，关键字建议使用大写 例如：SELECT * FROM user。
  • 同样可以使用/**/的方式完成注释 – #
  • MySQL中的我们常使用的数据类型如下

• SQL数据类型

  分类	类型名称	说明
  整数类型	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 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
  文本、二进制类型	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~4294967295字节
  tinytext	允许长度0~255字节
  text	允许长度0~65535字节
  mediumtext	允许长度0~167772150字节
  longtext	允许长度0~4294967295字节
  varbinary(m)	允许长度0~M个字节的变长字节字符串
  binary(m)	允许长度0~m个字节的定长字节字符串

数据库的基础操作

登录MySQL数据库

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

• 方式一：

  mysql -u用户名 -p密码
  

• 方式二：

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

1：DDL之数据库操作：database

1.1：创建数据库

格式:

create database 数据库名;    #直接创建数据库，如果存在则报错
create database if not exists bigdata_db;  #如果数据库不存在则创建
create database 数据库名 character set 字符集; #创建数据库时设置字符集

实例：

create database bigdata_db;

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

show databases;

1.3. 删除数据库

格式：

drop database 数据库名称

实例：

drop database bigdata_db;

1.4. 使用数据库

• 选择数据库

  格式:

   use  数据库名字;
  

• 实例:

  use bigdata_db;
  

• 查看正在使用的数据库:

  select database();
  

2：DDL之表操作：table

2.1：创建表

• 格式

  create table if not exists 表名(
  字段名  类型(长度) [约束],
  字段名  类型(长度) [约束],
     ...
  );
  类型：
        varchar(n)字符串
        int   整形
  	  double 浮点
        date   时间
        timestamp时间戳
  约束：
  primary key  主键，被主键修饰字段中的数据，不能重复、不能为null。
  

• 实例

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

2.2：查看表

• 查看数据库中的所有表：

  • 格式:show tables;

• 查看表结构：

  • 格式：desc 表名;

  • 例如：desc sort;

2.3：删除表

• 格式：drop table 表名;

• 实例：

  drop table category;
  

2.4：修改表结构

• 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;
  

3：DML数据操作语言

3.1：插入表记录：insert

• 语法

  -- 向表中插入某些字段
  insert into 表 (字段1,字段2,字段3..) values(值1,值2,值3..);
  -- 向表中插入所有字段,字段的顺序为创建表时的顺序
  insert into 表 values(值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','蔬菜');

3.2：更新表记录：update

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

• 语法：

  -- 更新所有记录的指定字段
  update 表名 set 字段名=值,字段名=值,...;
  -- 更新符号条件记录的指定字段
  update 表名 set 字段名=值,字段名=值,... where 条件;
  

• 实例：

update category set cname  = '家电';  #将所有行的cname改为'家电'
update category set cname  = '水果' where cid = 'c001'; #将cid为c001的cname修改为水果

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

3.3：删除记录：delete

• 逻辑删除

• 磁盘删除

• 语法：

delete from 表名 [where 条件];
或者
truncate table 表名;

• 实例:

delete from category where cid = '005'; #删除cid为005的纪录
truncate category;  #清空表数据

• 注意:

  • delete 一条一条删除，不清空auto_increment记录数。

  • truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

4：DQL数据查询语言

4.1：数据准备

#创建商品表：
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');

4.2：查询语句语法

select [distinct]
*| 列名,列名
from 表
where 条件

4.3：简单查询

#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;

4.4：条件查询

比较运算符	> < <= >= = <> !=	大于、小于、大于(小于)等于、不等于
BETWEEN …AND…	显示在某一区间的值(含头含尾)
IN(set)	显示在in列表中的值，例：in(100,200)
LIKE ‘张%’ LIKE ‘%涛%’	模糊查询，Like语句中， %代表零个或多个任意字符， _代表一个字符， 例如：first_name like ‘_a%’;
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;

4.5：排序查询

• 通过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.6：聚合查询

聚合函数	作用
count()	统计指定列不为NULL的记录行数；
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

• 实例：

#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 category_id = 'c002';

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

4.7：分组查询

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

格式：

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

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

• having与where的区别:
  • having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤
  • 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;

4.8：分页查询

• 分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有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

#查询product表的前5条记录
SELECT *  FROM product LIMIT 0,5

4.9：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分类表，为一方，也就是主表，必须提供主键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,pname) VALUES('p001','商品名称');

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

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

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

多表查询

CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  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 KEY (category_id) REFERENCES category (cid)
);

初始化数据

#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

多表查询

1. 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

   • 语法：select * from A,B;

2. 内连接查询 (使用的关键字 inner join – inner可以省略)

   • 隐式内连接：select * from A,B where 条件;

   • 显示内连接：select * from A inner join B on 条件;

3. 外连接查询(使用的关键字 outer join – outer可以省略)

   • 左外连接：left outer join

   • select * from A left outer join B on 条件;

   • 右外连接：right outer join

   • select * from A right outer join B on 条件;

#1.查询哪些分类的商品已经上架
#隐式内连接
SELECT DISTINCT c.cname FROM category c , products p 
 WHERE c.cid = p.category_id AND p.flag = '1';

#内连接
SELECT DISTINCT c.cname FROM category c 
 INNER JOIN products p ON c.cid = p.category_id 
 WHERE p.flag = '1';

#2.查询所有分类商品的个数
#左外连接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(category_id) FROM category c 
 LEFT OUTER JOIN products p 
  ON c.cid = p.category_id 
 GROUP BY cname;

子查询

子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

select …查询字段 … from … 表… where … 查询条件

#3 子查询, 查询“化妆品”分类上架商品详情
#隐式内连接
SELECT p.* FROM products p , category c 
 WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询
##作为查询条件
SELECT * FROM products p 
 WHERE p.category_id = 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品'
 );
 ##作为另一张表
 SELECT * FROM products p , 
   (SELECT * FROM category WHERE cname='化妆品') c 
  WHERE p.category_id = c.cid;

 

#查询“化妆品”和“家电”两个分类上架商品详情
SELECT * FROM products p 
 WHERE p.category_id in 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品' or c.name='家电'
 );

SQL约束

1.1. 主键约束

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

• 主键必须包含唯一的值。

• 主键列不能包含 NULL 值。

• 每个表都应该有一个主键，并且每个表只能有一个主键。

1.1.1. 添加主键约束

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

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

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

• 格式：[constraint 名称] primary key (字段列表)

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

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

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

或

CREATE TABLE Persons3
(
  FirstName varchar(255) ,
  LastName  varchar(255),
  Address   varchar(255),
  City      varchar(255)
)

1.1.2. 删除主键约束

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

ALTER TABLE Persons DROP PRIMARY KEY;

1.1.3. 自动增长列

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

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

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

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

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

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

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

ALTER TABLE Persons AUTO_INCREMENT=100

1.2. 非空约束

• 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)
)

1.3. 唯一约束

• UNIQUE 约束唯一标识数据库表中的每条记录。

• UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

• PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

• 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

添加唯一约束

• 创建表时，在字段描述处，声明唯一：

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

1.4. 外键约束

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

  

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

• 外键特点：

  • 从表外键的值是对主表主键的引用。
  • 从表外键类型，必须与主表主键类型一致。

• 声明外键约束

  • 语法：

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

• [外键名称] 用于删除外键约束的，一般建议“_fk”结尾

  • alter table 从表 drop foreign key 外键名称

• 使用外键目的：

  • 保证数据完整性

数据库的备份和恢复

备份对于数据库而言是至关重要的。当数据文件发生损坏、MySQL服务出现错误、系统内核崩溃、计算机硬件损坏或者数据被误删等事件时，使用一种有效的数据备份方案，就可以快速解决以上所出现的问题。MySQL提供了多种备份方案，包括：逻辑备份、物理备份、全备份以及增量备份。

• 物理备份通过直接复制包含有数据库内容的目录与文件实现，这种备份方式适用于对重要的大规模数据进行备份，并且要求实现快速还原的生产环境。

  • 典型的物理备份就是复制 MySQL数据库的部分或全部目录，物理备份还可以备份相关的配置文件。但采用物理备份需要让MySQL处于关闭状态或者对数据库进行锁操作，防止在备份的过程中改变发送数据。
  • 物理备份可以使用mysqlbackup对InnoDB数据进行备份，使用mysqlhotcopy对MyISAM数据进行备份。另外，也可以使用文件系统级别的cp、scp、tar、rsync等命令。

• 逻辑备份通过保存代表数据库结构及数据内容的描述信息实现，如，保存创建数据结构以及添加数据内容的SQL语句，这种备份方式适用于少量数据的备份与还原。

  • 逻辑备份需要查询MySQL服务器获得数据结构及内容信息，因为需要查询数据库信息并将这些信息转换为逻辑格式，所以相对于物理备份而言比较慢。
  • 逻辑备份不会备份日志、配置文件等不属于数据库内容的资料。
  • 逻辑备份的优势在于不管是服务层面、数据库层面还是数据表层面的备份都可以实现，由于是以逻辑格式存储的，所以这种备份与系统、硬件无关。

• 全备份将备份某一时刻所有的数据，增量备份仅备份某一段时间内发生过改变的数据。

• 通过物理或逻辑备份工具就可以完成完全备份，而增量备份需要开启MySQL二进制日志，通过日志记录数据

相关操作：

1. 备份所有的数据库

mysqldump -u username -ppassword --all-databases > bak.sql

2. 备份指定的数据库db1、db2和db3

mysqldump -u username -ppassword --databases db1 db2 db3 > bak.sql

3. 备份db数据库，当仅备份一个数据库时，–databases可以省略

mysqldump --u username -ppassword db4 > bak.sql

mysqldump -u username -ppassword --databases db4 > bak.sql

• 两者之间的差别在于
• 不使用 --databases 选项，则备份输出信息中不会包含CREATE DATABASE或USE语句。
• 不使用 --databases 选项备份的数据文件，在后期进行数据还原操作时，如果该数据库不存在，必须先创建该数据库。

（4）备份db数据库指定的表tb_name1、tb_name2和tb_name3

mysqldump -u username -ppassword -t db --tables tb_name1 tb_name2 tb_name3 > bak.sql

**注意：**mysqldump默认会将多条插入语句导出成一条insert语句格式，不方便查看，如：

insert into t values(1),(2),(3);

如果想导出多条insert语句，如：

insert into t values(1);
insert into t values(2);
insert into t values(3);

加上参数 --skip-extended-insert 即可。

mysqldump -u username -ppassword --skip-extended-insert --databases db_name --tables tb_name > tb_name.sql

使用mysql命令读取备份文件，实现数据还原功能：

mysql -u username -ppassword < bak.sql

mysql -u username -ppassword db4 < bak.sq1

案例：下面将 testDB 数据库中的内容导出成一个文件，并保存到 /home/目录下

 mysqldump -u username -ppassword testDB > /home/bak.sql

mysqldump参数大全

使用mysqldump迁移MySQL数据

MySql 元数据

• 元数据（MetaData，是指定义数据结构的数据。

• 那么数据库元数据就是指定义数据库各类对象结构的数据。 例如数据库中的数据库名，表明， 列名、用户名、版本名以及从SQL语句得到的结果中的大部分字符串是元数据

• 数据库元数据的作用

  • 在应用设计时能够充分地利用数据库元数据
  • 深入理解了数据库组织结构，再去理解数据访问相关框架的实现原理会更加容易。

• information_schema库中的表，保存的是Mysql的元数据。
  官网元数据表介绍

MySQL索引

概述

• 索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。

• 索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1. 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。

2. 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

索引的分类

• ** 根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。**

1. B-树索引

   B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

2. 哈希索引

   哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
   HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。

• 根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 3 类：

1. 普通索引

   普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

2. 唯一性索引

   唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。
   创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

3. 主键索引

   主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。

普通索引：

• 创建索引：

• 方式1-直接创建

CREATE INDEX indexName ON mytable(username([length])); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

• 方式2-修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

• 方式3-创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX indexName(username(length))  
);  

查询索引

#1、查看表中所有索引
SHOW INDEX FROM table_name;
#2、查看数据库所有索引
SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = '数据库名';

#2、查看某一表索引
SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = '数据库名' and a.table_name like '%表名%';

删除索引

DROP INDEX [indexName] ON mytable;
alter table mytable drop index indexName;

唯一索引

• 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

• 创建索引：

• 方式1-直接创建

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

• 方式2-修改表结构(添加索引)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

• 方式3-创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

删除索引

DROP INDEX [indexName] ON mytable;
alter table mytable drop index indexName;

• 主键索引

主键索引的操作就是主键约束的操作。

索引的使用原则和注意事项

• 虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

• 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

• 对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。

MySQL事务

• 可以把一系列要执行的操作称为事务，而事务管理就是管理这些操作要么完全执行，要么完全不执行（很经典的一个例子是：A要给B转钱，首先A的钱减少了，但是突然的数据库断电了，导致无法给B加钱，然后由于丢失数据，B不承认收到A的钱；在这里事务就是确保加钱和减钱两个都完全执行或完全不执行，如果加钱失败，那么不会发生减钱）。
• 事务管理的意义：保证数据操作的完整性。
• mysql中并不是所有的数据引擎都支持事务管理的，只有innodb支持事务管理。

事务管理的特性：

• 原子性：事务的整个操作是一个整体，不可以分割，要么全部成功，要么全部失败。
• 一致性：事务操作的前后，数据表中的数据没有变化。
• 隔离性：事务操作是相互隔离不受影响的。
• 持久性：数据一旦提交，不可改变，永久的改变数据表数据。

事务管理的相关操作：

• 开启事务管理：开启之后，下面的sql语句并不会马上执行并把结果写到表中，而是会写到事务日志中。
  • start transaction;
• 回退操作：回退会清掉开始事务管理之后写到事务日志中的内容，即恢复到开启事务管理之前。
  • 语法：rollback;
  • 注意：回退操作只是回退"写"的内容，对于普通的读表select语句不能回退。
• 事务提交：将sql语句的结果写到数据表中。
  • 语法：commit:

注意：

• 当 commit 或 rollback 语句执行后，事务会自动关闭（将来的更改会隐含提交）。

• 锁机制：在事务操作一个表时，如果使用索引来取值，那么会锁定到对应行；如果没有使用索引来取值，那么会锁定整个表。锁定之后其他连接无法操作指定行或表。

• 回滚点：

  • 回滚点可以指定rollback回退的位置【比如现在打了100条命令，发现第81打错了，如果回滚到打了81命令之前一点而不是回滚到开启事务之前就可以节省下很多时间。】
  • 语法：
    • 创建回滚点：savepoint 回滚点名;
    • 回滚到回滚点：rollback to 回滚点名;

• 回滚点在事务管理关闭(rollback或commit之后)之后失效，不要在事务之外使用回滚点。

默认的事务管理：

• 默认情况下，mysql的事务管理是关闭（自动事务）的，语句的结果会马上写到数据表中。
  • 可以通过show variable like ‘autocommit’;来查看是否开启自动事务，值为1为自动事务已开启，为0则为关闭。
• 关闭自动事务：set autocommit =0;【关闭后需要commit来执行每一条语句，相当于开始了事务管理】
  • 不过注意的是set autocommit针对的是会话变量，所以这个设置只在此次会话连接中生效。

MySQL执行引擎

MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

• 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

• 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

• 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

• 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

• BLOB和TEXT列可以被索引

• NULL被允许在索引的列中，这个值占每个键的0~1个字节

• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩

• 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

• 可以把数据文件和索引文件放在不同目录

• 每个字符列可以有不同的字符集

• 有VARCHAR的表可以固定或动态记录长度

• VARCHAR和CHAR列可以多达64KB

• 使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

InnoDB存储引擎
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

• InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

• InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

• InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

• InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

• InnoDB被用在众多需要高性能的大型数据库站点上

• InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：

• MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度

• MEMORY存储引擎执行HASH和BTREE缩影

• 可以在一个MEMORY表中有非唯一键值

• MEMORY表使用一个固定的记录长度格式

• MEMORY不支持BLOB或TEXT列

• MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

• MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）

• MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享

• 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

存储引擎的选择

• 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

• 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

• 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
  设置数据库表的字符集编码：

CHARSET=utf8    -- 设置数据库的编码集

不设置的话，会是MySQL默认的字符集编码（是不支持中文的）

MySQL的默认编码是Latin1，不支持中文。

在my.ini中配置默认的编码

character-set-server=utf8

规范化数据库设计

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

• 数据冗余,存储空间浪费
• 数据更新和插入的异常
• 程序性能差

良好的数据库设计 :

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

• 收集信息

  • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

• 标识实体[Entity]

  • 标识数据库要管理的关键对象或实体,实体一般是名词

• 标识每个实体需要存储的详细信息[Attribute]

• 标识实体之间的关系[Relationship]

三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

• 信息重复

• 更新异常

• 插入异常

  • 无法正确表示信息

• 删除异常

  • 丢失有效信息

三大范式

- 第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

SQL优化