MySQL基础

MySQL基础
一、数据库概述

1、SQL分类

SQL，Structure Query Language，结构化查询语言，主要有下面三种：

一、数据定义语言 Data Defination Language (DDL)

数据库定义语言定义了数据库涉及的各种对象，定义数据的完整性约束、保密限制等约束。

二、数据操作语言 Data Manipulation Language (DML)

数据操作语言实现了对数据的操作。基本数据操作有两类：检索（查询）和更新（插入、删除和修改）。

三、数据控制语言 Data Control Language (DCL)

数据库控制语言实现对数据库的控制，包括数据完整性控制、数据安全性控制和数据库恢复等。

2、MySQL历史发展

1985年，David Axmark 、Allan Larsson 、 Michael Widenius 成立公司，该公司为 MySQL AB 的前身。

2000年4月，MySQL对搜索引擎进行整理，命名为MyISAM。

2001年，InnoDB集成到MySQL。

2004年10月，MySQL 4.1 版本发布。

2005年10月，MySQL5.0版本发布。5.0加入了游标、存储过程、触发器、视图和事务支持。

2008年1月16日，MySQL被SUN收购。

2009年，SUN被Oracle收购。

... ...

3、MySQL优点

1、体积小

2、速度快

3、拥有成本低

4、开放源码

4、数据库设计三大范式

1、原子性，字段不可再分割

比如：在user中有“联系方式”字段，存储内容：“ 64558@163.com,15854215521 ”，就不符合原子性，要设计成：email、phone对邮箱和手机号分别进行存储。

不符合第一范式：

符合第一范式：

2、完全依赖，没有部分依赖

比如：学生选课表的设计：

这里可以通过（学生,课程）可以确定老师、教师、时间，可以把（学生,课程）作为主键。但是，教材并不完全依赖主键，教材可以根据课程直接确定。这就是出现了不完全依赖、部分依赖，这样的设计就不符合第二范式。

符合第二范式的设计：

选课表:

教材表：

3、没有传递依赖

每个属性要跟主键直接联系，不能有传递关系。

比如，学生表：

学校的地址和学校的电话，就是和college有依赖关系，和student是没有直接依赖关系，不符合第三范式。

符合第三范式的修改如下：

学生表：

学校表：

一般来讲，范式越复杂，性能会越差。我们工作中满足以上三大范式即可。

二、MySQL安装和配置

1、MySQL下载

1、MySQL官方网站：http://www.mysql.com/

2、MySQL版本分类：社区版 Community 和企业版 Enterprise。

社区办支持只有下载，并且完全免费，官方不提供技术支持；

企业版不能自由下载并且收费，提供了更多的功能，享受完备的技术支持。

3、版本标识说明

GA：通用版本，官方推荐

RC：发布版

Alpha：内测版

Bean：公测版

4、社区版下载：

进入网站首页——>Downloads——>Community——MySQL on Windows ——>MySQL Installer,进入Windows版本下载页面。

其中 1.7 M大小的 mysql-installer-web-community 为在线安装文件；

385M大小的 mysql-installer-community 为本地安装。

2、MySQL安装

Typical：默认安装

Complete：完全安装

Custom：自定义安装

3、MySQL配置

配置文件为：C：ProgramData/MySQL/MySQL 5.6/my.ini

常用配置项：

lower_case_table_names = 0 // 0 ,表名区分大小写；1 , 表名不区分大小写

default_character_set = utf-8 // 客户端字符编码

character_set_server = utf-8 //服务端字符编码

port = 3306 //端口号

default_storage_engin = INNODB //默认存储引擎

三、MySQL对象操作

1、数据库操作

（1）创建数据库：
```
create database db_name;
```
（2）查看数据库：
```
show databases ;
```
（3）使用数据库：
```
use db_name;
```
（4）删除数据库：
```
drop database db_name;
```
注意： SQL语句可以用 ; g G 三种方式结尾，其中 ; 和 g 效果相同； G 优化结果显示。

2、表操作

（1）创建表:
```
create table company ( company_id  INT , company_name  VARCHAR(20) , company_address  VARCHAR(64));
```
（2）查看表结构：
```
describe company;     --查看表定义

show create table company;      --查看表详细定义
```
（3）删除表：
```
drop table company;
```
（4）修改表：
```
alter table company rename mycompany; --修改表名
alter table company add  tel varchar(20); --增加字段
alter table company add  desc varchar(32) first;--在表的第一个位置增加字段
alter table company add create_time varchar(32) after company_name; --在指定位置增加字段
alter table company drop company_name;--删除字段
alter table company modify company_name varchar(128);--修改字段类型
alter table company modify company_name first;--调整字段顺序到第一位
alter table company modify company_name after desc;--调整字段顺序到desc后面
alter table company change company_name new_name varchar(128);--修改字段名和字段类型
```
（5）操作表约束：

数据的完整性是指数据的准确性与一致性，完整性检查是指检查数据的准确性和一致性。MySQL提供一致机制来检查数据库表中的数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性，这种机制就是约束。

MySQL支持的完整性约束：

NOT NULL（NK）：约束字段的值不能为空

DEFAULT：设置字段的默认值

UNIQUE KEY（UK）：约束字段的值唯一

PRIMARY KEY（PK）：约束字段为标的主键，作为表记录的唯一标识

AUTO_INCREMENT：约束字段的值为自动增加

FOREIGN KEY（FK）：约束字段为表的外键

a、设置非空约束
```
create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) NOT NULL);
```
如果增加的记录中，设置NK的字段没有值，则数据库管理系统会报错。

命令行错误信息：
```
ERROR 1048 (23000) : Column 'member_name' cannot be null 
```
管理工具错误提示：

b、设置字段的默认值
```
create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) DEFAULT 'huiyuan');
```
c、设置唯一约束（UK）
```
create table member(member_id INT(11) NOT NULL, member_name VARCHAR(32) UNIQUE);
```
对唯一字段插入相同记录时，提示信息：

d、设置主键约束

主键字段要满足：唯一、非空，可以是单一字段，也可以是多个字段。

单字段主键的两种方法：
```
create table member(member_id INT(11) PRIMARY KEY, member_name VARCHAR(32) UNIQUE, create_time varchar(32));  --（1）设置主键
create table member(member_id INT(11), member_name VARCHAR(32) UNIQUE, create_time varchar(32),
             CONSTRAINT pk_menberid PRIMARY KEY (member_id)); -- （2）设置主键并定义主键名称
```
多字段主键：
```
create table member(member_id INT(11), member_name VARCHAR(32) UNIQUE, create_time varchar(32),
                    CONSTRAINT pk_id_name PRIMARY KEY (member_id,member_name)); -- 设置主键并定义主键名称
```
e、自增（AUTO INCREMENT）

一个数据库只能有一个字段使用自增约束，而且该字段类型必须是整数类型。自增约束一般设置在主键上。
```
create table t_dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR(20) , loc VARCHAR(40));
```
f、外键（FOREIGN KEY）

描述两个表之间的约束。子表中的某个字段的取值范围由父表决定。比如部门和雇员表。
```
create table t_dept (deptno INT PRIMARY KEY auto_increment, dname VARCHAR(20) , loc VARCHAR(40)); --部门表
create table t_employee(employee_id INT PRIMARY KEY,e_name VARCHAR(32),create_time VARCHAR(32),
            dept_no INT(11), CONSISTENT fk_deptno FOREIGN KEY(dept_no) REFERENCES t_tept(deptno));--雇员表
```
3、索引操作

数据库的索引对象和书的目录类似，主要是为了提高从表中检索数据的速度（其次还可以保证字段唯一性，实现数据库表的完整性）。按索引的存储类型分类，可以分为：B型树索引（BTREE）和哈希索引（HASH），其中，InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY支持HASH索引。

MySQL支持的索引有6种：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

（1）索引的适用场景

以下情况适合创建索引：
- 经常被查询的字段，即在WHERE子句中出现的字段。
- 在分组的字段，即在 GROUP BY 子句中出现的字段。
- 存在依赖关系的子表和父表之间的联合查询，即主键和外键字段。
- 设置唯一完整性约束的字段。
以下情况不适合创建索引：
- 在查询中很少被使用的字段。
- 拥有许多重复值的字段。
（2）创建索引

a、普通索引

普通索引：在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

在创建索引时，可以指定索引的长度。这是因为不用的存储引擎定义了表的最大索引数和最大索引长度。MySQL每个表至少支持16个索引，总索引长度为256字节。

在创建索引时，可以定义索引的长度和排序。ASC 升序排列； DESC 降序排列
```
-- 创建表时创建普通索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
INDEX index_deptno(deptno));
-- 在已经存在的表上创建索引
create INDEX index_deptno ON t_dept(deptno(16) ASC);
-- 通过Alter table 创建索引
alter table t_dept add INDEX index_deptno(deptno);
```
b、唯一索引

唯一索引：创建索引时，限制索引的值必须是唯一的。唯一索引可以更快速的查询某条记录。
```
-- 创建表时创建 唯一 索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
UNIQUE INDEX index_deptno(deptno));
-- 在已经存在的表上创建 唯一 索引
create UNIQUE INDEX index_deptno ON t_dept(deptno(16) ASC);
-- 通过Alter table 创建 唯一 索引
alter table t_dept add UNIQUE INDEX index_deptno(deptno);
```
c、全文索引

全文索引主要是关联在数据类型为:CHAR 、VARCHAR 和 TEXT 的字段上，以便能够更加快速的查询数据量较大的字符串类型的字段。只能在存储引擎为MyISAM的数据库表上创建全文索引。默认情况下，全文索引的搜索执行方式不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。
```
-- 创建表时创建 全文 索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
FULLTEXT INDEX index_deptno(deptno)) ENGINE = MyISAM;
-- 在已经存在的表上创建 全文 索引
create FULLTEXT INDEX index_deptno ON t_dept(deptno(16) ASC);
-- 通过Alter table 创建 全文 索引
alter table t_dept add FULLTEXT INDEX index_deptno(deptno);
```
d、多列索引

多列索引：创建索引时，所关联的字段不是一个字段，而是多个字段。只有查询条件中使用了所关联字段中的第一个字段，多列索引才被使用。
```
-- 创建表时创建  多列 索引
create table t_dept(deptno INT PRIMARY KEY,dname VARCHAR(20),loc VARCHAR(40),
INDEX index_deptno(deptno,loc));
-- 在已经存在的表上创建 多列 索引
create INDEX index_deptno ON t_dept(deptno,loc);
-- 通过Alter table 创建 多列 索引
alter table t_dept add INDEX index_deptno(deptno,loc);
```
（3）删除索引
```
-- 删除索引
DROP INDEX index_dname_loc ON t_dept;
```
4、视图操作

（1）视图的特点
- 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
- 视图是由基本表（实表）产生的表（虚表）。
- 视图的建立和删除不影响基本表。
- 对视图内容的更新（添加、删除和修改）直接影响基本表。
- 当视图来自多个基本表时，不允许添加和删除数据。
（2）创建视图

基本表结构：

创建视图：
```
CREATE VIEW view_selectproduct AS SELECT id,name FROM t_product;
```
注意：在SQL语句命名规范中，视图一般用 view_xxx 或者 v_xxx 命名。

（2）使用视图

视图的使用和正常的表查询一样：
```
SELECT * FROM view_selectproduct;
```
（3）不同种类的视图

a、常量视图
```
CREATE VIEW view_pi AS SELECT 3.1415926;
```
b、封装聚合函数（SUM/MIN/MAX/COUNT等）
```
CREATE VIEW view_count_name AS SELECT COUNT(name) FROM t_product;
```
c、封装排序（order by）
```
CREATE VIEW view_order_by AS SELECT name FROM t_product ORDER BY id DESC;
```
d、封装内连接
```
CREATE VIEW view_inner_join AS SELECT s.name FROM t_student AS s,t_group as g WHERE s.group_id = g.id;
```
e、封装外连接
```
CREATE VIEW view_left_join AS SELECT s.name FROM t_student AS s LEFT JOIN t_group AS g ON s.group_id = g.id;
```
其他子查询、联合查询的视图创建方式相似。

（4）查看视图
```
SHOW TABLES;
SHOW TABLES STATUS FROM DB_NAME; -- 查看视图的详细信息
SHOW CREATE VIEW viewname; -- 查看视图的定义信息
```
（5）删除视图
```
DROP VIEW view_name1,view_name2; -- 可以同时删除多个视图
```
（6）视图操作基本表数据
```
INSERT INTO view_product (id, name, price ,order_id) VALUES (11,"apple",6,54); -- 插入数据
DELETE FROM view_product WHERE name = "apple"; -- 删除数据
UPDATE view_product SET price = 4 WHERE name = "apple"; -- 修改数据
```
5、触发器操作

触发器：在表发生更改时，自动进行处理。

可以触发器执行的语句： delete 语句、insert 语句、update 语句。
```
-- 触发器
CREATE TRIGGER tri_diarytime 
    BEFORE INSERT ON t_dept FOR EACH ROW
         INSERT INTO t_diary VALUES(null,"t_dept",NOW());
```
四、MySQL数据操作

1、增、改、删操作

（1）插入数据
```
-- 插入一条完整记录
INSERT INTO t_dept(deptno,dname,loc) VALUES (5,'开发部','程序开发'); -- 推荐使用
INSERT INTO t_dept VALUES (5,'开发部','程序开发');-- 缺省格式，不推荐使用

-- 插入一条部分记录
INSERT INTO t_dept(dname) VALUES ('设计部');

-- 插入多条记录
INSERT INTO t_dept(dname) VALUES ('设计部'),('产品部'),('市场部'),('行政部');

-- 插入查询的结果
INSERT INTO t_dept(dname) SELECT dname FROM t_company;
```
（2）更新数据
```
UPDATE t_dept SET dname = '测试部门' ,loc = '' where deptno = 1;
```
（3）删除
```
DELETE FROM t_dept WHERE deptno < 10;
```
2、单表查询

原始表：

（1）简单数据记录查询
```
SELECT item_name,brand_id FROM z_item; -- 查询商品名称和品牌id
SELECT * FROM z_item; -- 查询商品表中的所有字段
```
a、去重 DISTINCT:
```
SELECT DISTINCT  item_name ,brand_id FROM z_item;  -- 去重会取并集，即  去重的 所有字段 都重复 才进行 去重。
```
b、四则运算：

MySQL支持的数学运算符：

+ 加法； - 减法； * 乘法； / 除法；% 取余
```
SELECT price,(price+0.2) AS 'price+0.2',(price -0.2) AS 'price-0.2' , (price * 2) AS 'price * 2 ' ,
(price / 2) AS 'price/2',(price % 2 ) AS 'price % 2' FROM z_item ;
```
c、字符串连接CONCAT()
```
SELECT CONCAT('商品名称是',item_name,'的商品，价格为',price) FROM z_item;
```
（2）条件查询

a、MySQL支持的比较运算符和逻辑运算符

> 大于; < 小于 ; = 等于 ; != (<>) 不等于 ; >= 大于等于; <=小于等于;

AND(&&) 逻辑与 OR(||) 逻辑或 XOR 逻辑异或 NOT(!) 逻辑非

b、简单条件查询
```
SELECT * FROM z_item WHERE item_name = '鞋子';
SELECT * FROM z_item WHERE item_id <= 10 AND price > 1000;
SELECT * FROM z_item WHERE item_id <= 10 && price > 1000;
```
c、BETWEEN AND 关键字
```
SELECT * FROM z_item WHERE price BETWEEN 100 AND 1000;
SELECT * FROM z_item WHERE price NOT BETWEEN 100 AND 1000;
```
d、NULL 和 NOT NULL
```
SELECT * FROM z_item WHERE item_name IS NULL;
SELECT * FROM z_item WHERE item_name IS NOT NULL;
```
e、IN
```
SELECT * FROM z_item WHERE item_name = '冰箱' OR item_name = '空调' OR item_name = '洗衣机';
SELECT * FROM z_item WHERE item_name IN('冰箱','空调','洗衣机');
SELECT * FROM z_item WHERE item_name NOT IN('冰箱','空调','洗衣机');
```
注意：在具体使用IN 关键字时，查询的结果如果存在NULL，则不会影响查询；如果使用关键字 NOT IN ，查询的集合中如果存在NULL，则不会有任何的查询结果。

f、LIKE

通配符：用来实现匹配部分值的特殊符号。
```
SELECT * FROM z_item WHERE item_name LIKE '%机';
SELECT * FROM z_item WHERE NOT item_name LIKE '%机';
SELECT * FROM z_item WHERE item_name NOT LIKE '%机';
SELECT * FROM z_item WHERE price LIKE '_9%';
```
g、ORDER BY

ASC 升序 DESC 降序
```
SELECT * FROM z_item ORDER BY price DESC , item_id ASC;
```
h、LIMIT
```
SELECT * FROM z_item LIMIT 1;
SELECT * FROM z_item LIMIT 3,2; -- 第四条记录开始显示，显示2条
```
i、统计函数和分组函数
```
-- 统计函数经常和分组一起使用
-- 统计条数   COUNT(*),统计所有值，包括null值和非null值； count(price)指定之后不统计null值。
SELECT COUNT(*) FROM z_item ;
-- 平均值
SELECT AVG(price) FROM z_item;
-- 求和
SELECT SUM(price) FROM z_item;
-- 最大值、最小值
SELECT MAX(price) FROM z_item;
SELECT item_name,MIN(price) FROM z_item;

-- 分组
SELECT *,SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id;
SELECT GROUP_CONCAT(item_name),SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id,is_delete;

SELECT GROUP_CONCAT(item_name),SUM(price) FROM z_item WHERE is_delete = 0 GROUP BY brand_id,is_delete HAVING AVG( price) > 1000;
```
注：资料来源为书籍《MySQL数据库应用从入门到精通》，本文为个人学习笔记。
相关阅读:
kafka consumer防止数据丢失(转)
Cglib源码分析 invoke和invokeSuper的差别(转)
如何查看k8s存在etcd中的数据(转)
k8s Nodeport方式下service访问，iptables处理逻辑(转)
项目演化系列--验证体系
 项目演化系列--路由解析
 项目演化系列--开篇
 基于SWFUpload的angular上传组件
 Query Object--查询对象模式(下)
Query Object--查询对象模式(上)
原文地址：https://www.cnblogs.com/lashou/p/6140135.html

一、数据库概述

1、SQL分类

2、MySQL历史发展

3、MySQL优点

4、数据库设计三大范式

二、MySQL安装和配置

1、MySQL下载

2、MySQL安装

3、MySQL配置

三、MySQL对象操作

1、数据库操作

2、表操作

3、索引操作

4、视图操作

5、触发器操作

四、MySQL数据操作

1、增、改、删操作

2、单表查询