Linux基本数据库mysql了解

关系型数据库与非关系型数据库的区别

什么是关系型数据库
关系型数据库是依据关系模型来创建的数据库。
所谓关系模型就是“一对一、一对多、多对多”等关系模型，关系模型就是指二维表格模型,因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。
关系型数据可以很好地存储一些关系模型的数据，比如一个老师对应多个学生的数据（“多对多”），一本书对应多个作者（“一对多”），一本书对应一个出版日期（“一对一”）数据的存储形式：

什么是非关系型数据库（NOSQL）？
非关系型数据库主要是基于“非关系模型”的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）
非关系型模型比如有：
存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，IO很快，主要是一些分布式数据库）

关系型数据库与非关系型数据库的区别
1.关系型数据库:
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询；
4、学习成本低。

缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。吞吐量
2.非关系型数据库
优点：
1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛.
2、速度快：可以使用硬盘或者内存作为载体，而关系型数据库只能使用硬盘；redis数据库单线程10W
3、成本低：数据库部署简单，基本都是开源软件。

缺点：
1、不提供sql支持，学习和使用成本较高；
2、无事务处理；

系统数据库解析

information_schema：          虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等

performance_schema：主要存储数据库服务器的性能参数

mysql：                                  授权库，主要存储系统用户的权限信息

sys：                                         主要存储数据库服务器的性能参数（目标是把performance_schema的把复杂度降低）

数据库命名规则：

区分大小写

唯一性

不能使用关键字如 create SELECT

不能单独使用数字

不要使用中文

MyISAM和InnoDB的区别

MyISAM和InnoDB的区别

Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB

MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即 MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以 MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

InnoDB支持外键，MyISAM不支持。

Innodb能存储64TB，MyISAM能存储256TB

InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、 varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。（书的目录）

MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。

数据库的安全控制：

1、数据库不要暴露在公网

2、禁止root用户远程登录，授权一个普通账号。指定一个具体IP。

3、修改端口。

4、普通用户启动MySQL。

5、删除空口令账号。

6、开启二进制日志。

Mysql调优策略

1. 选择合适的存储引擎: InnoDB

2. 选取磁盘读写速度较快的设备作为Mysql服务器

3. 充分使用索引

4. 增加慢查询日志功能

5. 频繁访问的数据，用缓存数据库解决 redis

6. 单条查询最后增加 LIMIT 1，停止全表扫描

7. 设置最大连接数

8. 设置引擎的读写速度

NULL（null）说明：

1、等价于没有任何值、是未知数。

2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。

3、对空值做加、减、乘、除等运算操作，结果仍为空。

4、比较时使用关键字用“is null”和“is not null”。

5、排序时比其他数据都小，所以NULL值总是排在最前。

约束条件说明

NOT NULL 标识该字段不能为null

UNIQUE KEY (UK)         标识该字段的值是唯一的，可以为空，一个表中可以有多个

FOREIGN KEY (FK)         标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联

PRIMARY KEY (PK)         标识该字段为该表的主键，可以唯一的标识记录，不可以为空

AUTO_INCREMENT         标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT                      为该字段设置默认值

UNSIGNED               无符号(正数)

ZEROFILL                     使用0填充，例如0000001

说明：
1. 是否允许为空，默认NULL；可设置NOT NULL，字段不允许为空，必须赋值

2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
    sex enum('male','female') not null default 'male'
    age int unsigned NOT NULL default 20        必须为正值（无符号）不允许为空默认是20

MySQL数据类型

数值类型

TINYINT： 1 字节，范围为 -128 到 127 或 0 到 255。

SMALLINT：2 字节，范围为 -32768 到 32767 或 0 到 65535。

MEDIUMINT：3 字节，范围为 -8388608 到 8388607 或 0 到 16777215。

INT：4 字节，范围为 -2147483648 到 2147483647 或 0 到 4294967295。

BIGINT：8 字节，范围为 -9223372036854775808 到 9223372036854775807 或 0 到 18446744073709551615。

FLOAT：4 字节，单精度浮点数，范围为 -3.402823466E+38 到 -1.175494351E-38，0，1.175494351E-38 到 3.402823466E+38。

DOUBLE：8 字节，双精度浮点数，范围为 -1.7976931348623157E+308 到 -2.2250738585072014E-308，0，2.2250738585072014E-308 到 1.7976931348623157E+308。

DECIMAL：变长的精确数值，支持 65 位整数位和 30 位小数位。

字符串类型

CHAR：定长字符串，最大长度为 255 个字符。

VARCHAR：变长字符串，最大长度为 65535 个字符。

BINARY：定长二进制字符串，最大长度为 255 个字节。

VARBINARY：变长二进制字符串，最大长度为 65535 个字节。空间会变小。

TEXT：变长字符串，最大长度为 65535 个字符。

BLOB：变长二进制字符串，最大长度为 65535 个字节。

ENUM：枚举类型，最多可以包含 65535 个值之一。

SET：集合类型，最多可以包含 64 个值之一。

日期/时间类型

DATE：日期，格式为 'YYYY-MM-DD'。

TIME：时间，格式为 'HH:MM:SS'。

DATETIME：日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'。

TIMESTAMP：自动存储记录插入或更新的日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'。

MySQL常见的数据类型

数值类型：
     整数类型 TINYINT（tinyint）SMALLINT（smallint） MEDIUMINT（mediumint） INT（int） BIGINT（bigint）
     整型可以指定是有符号的和无符号的，默认是有符号的
可以通过UNSIGNED（unsigned）来说明某个字段是无符号的。

     浮点数类型 FLOAT DOUBLE float double


字符串类型：
     CHAR系列    CHAR VARCHAR
BINARY系列 BINARY VARBINARY
枚举类型： ENUM
集合类型： SET

时间和日期类型：
DATE TIME DATETIME TIMESTAMP YEAR

基本数据库sql

什么是sql

SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。

SQL包含四个部分：

> - DDL 数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。

> - DML 数据操作语言包含允许更新数据的语句。

> - DCL 数据控制语言允许授予用户权限访问数据库中特定数据的权限。

> - DQL 数据库查询语言

DDL定义语言

#查看数据库
mysql> show databases;

#查看创建的库信息
mysql> show create database 库名;

#查看当前库
mysql> select database();

#建库
mysql> create database 库名;
#建库时指定字符集
mysql> create database 库名 default charset 'utf8'

#切换数据库
mysql> use 库名;

#删除库
mysql> drop database 库名;

#建表
mysql> create table 表名(
        字段名1 类型[(宽度) 约束条件],
        字段名2 类型[(宽度) 约束条件],
        字段名3 类型[(宽度) 约束条件]
)[存储引擎字符集];
==在同一张表中，字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的

DML操作语言

#表中插入数据
mysql> insert into 表名 (字段名) values ("字段内容","字段内容")

#显示数据的详细信息
mysql> show table status like '表名' \G

#修改表名称修改字段名称修改字段数据类型修改字段的修饰符
mysql>alter tables 表名 change 原字段名新字段名需要修改的字段数据类型和修饰符

#修改字段类型以及约束条件

mysql>alter table 表名 modify 字段名需要修改的字段数据类型和修饰符

#查看表
mysql> show tables;

#修改表名称
mysql> rename table 原名 to 新名;
mysql> alter table 原名 rename 新名;

#查询表结构
mysql> desc 表名;
mysql> show create table;

#删除表
mysql> drop table 库名;

#删除表中的数据
mysql> delete from 表名 where ;

#复制表
mysql> create table 新表名(select 可指定字段 from 原表名);

#更新数据
mysql> update 表名 set 字段1=值1,字段2=值2 WHERE CONDITION;

DCL控制语言

-- 授权
mysql> grant 需授权的权限 on *.* to '用户名'@'授权网段' identified by "密码";
mysql> flush privileges;

-- 回收权限
mysql> revoke 需要回收的权限 on *.* from '用户名'@'网段';

DQL查询语言

#查询表中指定字段的值
mysql> select 指定字段 from 表名;

#查询表中所有字段的值
mysql> select * from 表名;

- -查询字符小于某值
mysql> select * from 表名字符<某值 # > = != <= >=

-- 查询一年值的总和并且重命名
mysql> select 字符*12 as 新名 from 表名

-- distinct避免重复 | 去重
mysql> select distinct 字符 from 表名

-- concat字符串的拼接
mysql> select CONCAT(字符,"连接语言",字符) FROM 表名

-- 查询某年的数据
mysql> select * from 表名 where 字符 like "某年%"
mysql> select * from 表名 where left(字符,4)=某年 -- right
mysql> select * from 表名 where 字符<"下年底" and 字符>"上年初"
mysql> select * from 表名 where 字符 between "下年底" and "上年初"
mysql> select * from 表名 where 字符 regexp="某年" REGEXP-- 正则表达式

--集合
mysql> select * from 表名 where 字符 id=1 or id=3 or id=5
mysql> select * from 表名 where 字符 id in(1,3,5)

-- 排序 DESC降序 ASC 升序（默认）
mysql> select * from 表名 order by 字符
mysql> select * from 表名 order by 字符 desc

-- 分组
mysql> select 字符 from 表名 group by 字符

函数
count()             统计数量
max()               最大值
min()               最小值
avg()               平均值
database()          当前所在库
user()              当前登录用户
now()               当前时间
sum()               总和
password()          指定密码