MySQL（数据库）-优快云博客

本文链接：https://blog.youkuaiyun.com/kouchangsheng/article/details/144570682

数据库(DataBase简称DB)，分为（关系型数据库）和（非关系型数据库）

关系型数据库

采用了关系模型来组织数据的数据库。

关系模型指的就是二维表格模型（表结构），而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

关系型数据库优点

容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解

关系型数据库缺点

1.网站的并发性很高，对于关系型数据库来说硬盘IO是一个瓶颈

2.在一张包含海量数据的表中查询，效率是非常低的

3.很难横向拓展，无法通过添加节点来扩展性能和负载能力，当需要对数据库系统进行升级和扩展时，往往需要停机维护和数据迁移。

4.性能欠佳：在关系型数据库中，导致性能欠佳的最主要原因是多表的关联查询，以及复杂的数据分析类型的复杂SQL报表查询

非关系型数据库

指非关系型的，分布式的，且一般不保证遵循事务四大原则的数据存储系统

非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

优点

1.用户可以根据需要去添加自己需要的字段，为了获取用户的不同信息，不像关系型数据库中，要对多表进行关联查询。

2.系统的升级，功能的增加，往往意味着数据结构巨大变动，这一点关系型数据库难以应付，需要新的结构化数据存储。由于不可能用一种数据结构化存储应付所有的新的需求，因此，非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。

缺点

只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显的更为合适。不适合持久存储海量数据

二者比较

非关系的成本低

非关系的数据在缓存中，关系的数据在硬盘中，非关系的查找快

非关系的支持键值对，图片，文档，对象，集合等多种形式，而关系型的只支持基本数据类型

非关系的没有耦合性容易水平扩展，而关系的有连表查询的限制，很难扩展

非关系不适合持久储存，关系的适合

非关系不支持事务。关系支持。

库的配置文件

[client]
port=3306 如果更改端口号可以从这里该

[mysql]
default-character-set
更改默认的编码集一般我们不需要更改

【mysqld】
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data	数据表的存放位置
default-storage-engine=	INNODB默认存储引擎
max_connections  	最大同时连接数，会保留一个给管理员用

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度

以及INNODB的一些设置参数

数据库管理系统(DataBase Management System简称DBMS)

DataBase Management System是操作和管理数据库的大型软件，所以从本质上来讲它是一种管理软件；用户可通过数据库管理系统访问数据库中的数据。DataBase Management System的主要功能是建立数据库、使用数据库、维护数据库、并对数据库进行统一管理以保证数据库的安全性和完整性。

常见的数据库管理系统 Oracle DB2 SQL Server MySQL

库的基本使用

select 列名【，列名】from 表名 where 行选择条件 group by 分组依据列 having 组选择条件 order by 排序依据

1、查看所有数据库 show databases 注意s

2、切换（选择要操作的）数据库 use 数据库名

3、创建数据库 create database 【if not exists】mydb1 【charset=utf8】

4、删除数据库 drop database 【if exists】mydb1

5、修改数据库编码 alter database mydb1 character set utf8

6.查看该数据库基本信息 show database 数据库名;

7.查看当前使用的数据库 select database(); 注意该语句最后有个括号(）

数据类型

int 整数型 	
double浮点型格式：double(5,2)最多五位。小数点后两位。
decimal浮点型不会出现精度缺失问题。
varchar可变长度字符串类型型。
text(clob)字符串类型范围：2^16-1B。
bloo字节类型
date日期类型格式：YYYY-MM-dd
time时间类型格式：hh:mm:ss
timestamp	时间戳格式

SQL分类

注意：在数据库中所有字符串类型，必须使用单引，不能使用双引；日期类型也要使用单引

DDL语句：

创建表 create table【if not exists 】表名（列名列类型，列名列类型，列名列类型）;

查看当前数据库所有表名称 show tables 模糊查询 show tables like '%u%'

查看表结构 desc 表名；

删除表 drop table 表名

修改表： alter table 表名

添加列：alter table 表名 ADD（列名列明类型，列名列明类型）；

修改列类型如果已存在数据，可能有影响：alter table 表名 modify 列名列类型；

修改列名 alter table 表名 change 原列名新列名列类型；

删除列 alter table 表名 drop 列名；

修改表名称 alter table 原表名 rename to 新表名；

DCL

常用语句：grant、deny

数据查询语言

一、基本查询

1、字段（列）控制

查询所有列 select * from 表名； *代表所有列

查询指定列 select 列1[,列2，...,列n] from 表名;

去除重复行查询 select distinct *|列1[,列2，...,列n] from 表名;如果列里的数据存在相同的，则查询结果显示一次

关键字：distinct

列运算

数量类型可以做加减乘除 select 列名*1.5 from emp；注意列的类型

字符串类型可以做连续运算 select concat（‘我叫’，ename，‘我是’，job）from emp；concat（，）连接字符串我叫小明我是文员。

转换null值 select ifnull（comm，0）+100 from emp；comm列如果存在null值，则使用0替换掉。注意：任何东西加null为null

给列起别名 as

2、条件控制

条件查询 where子句控制，和update、delete语句一样

模糊查询 select * from emp where ename like ‘张%’；百分号匹配任意一个字符下划线匹配一个任意字符

二、排序

select * from emp order by sal asc,comm desc,empno asc;

关键字 order by 排序字段 asc|desc【，排序字段 asc|desc】

升序 asc 默认可不写降序 desc

注意：

1、多列排序，先排前边的，如果分出胜负则没有后边的事了，如果没有，则按后边的排序接着排 2、limit关键字若有，则在其之后

三、聚合函数

聚合函数用来做某列的纵向运算比如统计总人数

select count（*）总人数，sum（sal）工资总和，max（sal）最高工资，min（sal）最低工资，avg（sal）平均工资 from emp；

1、count

count（*）==count（随便一个数字）计算表中所有列都不为null的记录的行数

count（comm）计算表中comm列不为null的记录的行数

2、max（列名）

查询此列中的最大数

3、min（列名）

查询此列中的最小数

4、sum（sal）查询工资和

5、avg（sal）查询平均工资

四、分组查询

把记录使用某一列进行分组，然后查询组信息比如按部门分组，查询每个部门的人数

select deptno,count(*) from emp group by deptno; 注意：要查询的东西只能是分组列和聚合函数.

group by 分组依据列 having 分组后条件

和分组无关的条件写在group by前的where语句中，和分组后有关的写在having 后，并且having后的条件只能使用聚合函数做条件

having是对于 group by 对象进行筛选

顺序

查询关键字 select、from、where、group by、having、order by

书写顺序

select --> from -- >where --> group by--> having --> order by --> limit

执行顺序

from --> where --> group by --> having > --select > --order by > --limit

五、limit子句（mysql方言）

用来限定查询结果的起始行，以及总行数

查询起始行第一行，查询5行数据 select * from emp limit 0,5；

一般用于分页查询

一页的记录数为10行，查询第三页 select * from emp limit 20,10；

查询起始行的计算：（当前页-1）*每页记录数例：查询第17页，每页8行数据（17-1）*8=128

select * from emp limit 128,8；

备份与恢复

1、数据库导出sql脚本（备份数据库内容，并不是备份数据库）

mysqldump -u用户名 -p密码数据库名>生成的脚本路径（例c:\mydb.sql）

2、执行sql脚本

第一种 mysql -u用户名 -p密码数据库<脚本文件路径

第二种登录mysql，删库建库，切换到库 source sql脚本路径

注意：若有重复数据库，先删库再建库，然后进行操作

多表查询

1、分类

合并结果集（了解）

要求被合并的表中，列的类型和列数相同（查询的结果集相同即可）

union 去除重复行 union all 不去除重复行

select * from cd union select * from ab;

连接查询

内连接

select * from 表1 别名1，表2 别名2 where 别名1.xx=别名2.xx；

select * from 表1 别名1，表2 别名2；笛卡尔积

标准：select * from 表1 别名1 inner join 表2 别名2 on 别名1.xx=别名2.xx；

自然连接：

select * from 表1 别名1 natural join 表2 别名2；

实际还是标准连接，自动加on条件，条件为两张表完全一样的列的等可读性差

自然连接：只是省略on条件，默认添加

内连接查询出的所有记录都满足条件

外连接

左外连接

select * from 表1 别名1 left join 表2 别名2 on 别名1.xx=别名2.xx；

左表记录无论满足什么条件都会查询出来，右表只有满足条件才会查询出来，左表中不满足条件的记录，右表部分都为null

左外自然

select * from 表1 别名1 natural left join 表2 别名2；

右外连接

select * from 表1 别名1 right join 表2 别名2 on 别名1.xx=别名2.xx；

右外自然

select * from 表1 别名1 natural right join 表2 别名2；

全连接

外连接＋合并结果集：可查两表所有信息，包括为null的

子查询

1、出现的位置

where后作为条件存在

from后作为表存在（多行多列，一般需要起别名）

2、条件

单行单列

select * from 表1 别名1 where 列1 [=、>、<、>=、<=、!=](select 列 from 表2 别名2 where 条件)；

多行单列

select * from 表1 别名1 where 列1【=、>、<、>=、<=】[in、all全部、any任意]（select 列 from 表2 别名2 where 条件）；

单行多列

select * from 表1 别名1 where （列1，列2） in （select 列1，列2，from 表2 别名2 where 条件）；

多行多列

select * from 表1 别名1，（select）别名2 where条件

索引

分类

普通索

-- 创建表并建立普通索引
DROP TABLE IF EXISTS student; 
CREATE TABLE student ( 
sid INT,
sname VARCHAR(50),
age INT, 
gender VARCHAR(50) DEFAULT 'male',
INDEX(sid) 
);

-- 查看表结构 SHOW CREATE TABLE student;
-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female'); 

-- 查询数据 SELECT * FROM student WHERE sid=1004; 在该示例中，我们在sid字段上建立了一个普通索引。

普通索引由KEY或INDEX定义，它可以创建在任何数据类型的字段上。

唯一性索引

-- 创建表并建立唯一性索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid INT，
sname VARCHAR(50),
age INT, 
gender VARCHAR(50) DEFAULT 'male',
UNIQUE INDEX uniqueIndex (sid ASC) );

-- 查看表结构 SHOW CREATE TABLE student;

 -- 插入数据
 INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
 INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
 INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
 INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');
 
 -- 查询数据 SELECT * FROM student WHERE sid=1004; 在该示例中我们在sid字段上创建了名为uniqueIndex的sid按照升序排列的唯一索引。

唯一性索引由UNIQUE定义，该索引所在字段的值必须是唯一的。

全文索引

-- 创建表并建立唯一性索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid INT，
sname VARCHAR(50),
age INT, 
gender VARCHAR(50) DEFAULT 'male',
FULLTEXT INDEX fullTextIndex (sname)
)ENGINE=MyISAM;

-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sname='xixi';

在该示例中，我们在sname字段上建立了一个名为fullTextIndex的全文索引。 请注意：目前只有MylSAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引；因此，在建立全文索引时要留意表存储引擎的类型。

全文索引由FULLTEXT定义，它只能创建在CHAR、VARCHAR或 TEXT类型的字段上。

单列索引

-- 创建表并建立单列索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid INT,
sname VARCHAR(50),
age INT,
gender VARCHAR(50) DEFAULT 'male',
UNIQUE singleIndex (sname (20))
);

-- 查看表结构
show create table student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sname='dodo';

在该示例中，我们在sname字段上建立了一个名为singleIndex长度为20的单列索引。

单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一性索引或者全文索引，只要保证该索引只对应表中一个字段即可。

多列索引

-- 创建表并建立多列索引
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid INT,
sname VARCHAR(50),
age INT,
gender VARCHAR(50) DEFAULT 'male',
INDEX mulIndex (sid,sname (20))
);

-- 查看表结构
SHOW CREATE TABLE student;

-- 插入数据
INSERT INTO student (sid,sname,age,gender) VALUES (1001, 'lili', 17, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1002, 'wang', 18, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES (1003, 'dodo', 19, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES (1004, 'xixi', 20, 'female');

-- 查询数据
SELECT * FROM student WHERE sid=1003;

在该示例中，我们在sid和sname字段上建立了一个名为mulIndex 长度为20的多列索引。

多列索引指的是在表中多个字段上创建索引，只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。例如，在student表的id、name和score字段上创建一个多列索引；那么，只有查询条件中使用了 id字段时该索引才会被使用。

空间索引

空间索引由SPATIAL定义，它只能创建在空间数据类型的字段上。 MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和 POLYGON。请注意：必须将创建空间索引的字段声明为NOT NULL，并且空间索引只能在存储引擎为MylSAM的表中创建。空间索引使用较少