MySQL笔记

基础篇

0、卸载与安装

2、用户权限认证

创建用户

3、基础操作

4、增

5、删

6、改

7、查

MySQL原理解析

---

基础篇

0、卸载与安装

安装之后：服务器软件+数据文件+mysql进程+环境变量

1、卸载：

控制面板卸载软件+删除数据文件（可选）+清除环境变量+（如果安装依然失败还需要清理注册表）

2、安装（可以安装多个版本）

Downloads下载 

下载.msi格式的文件，(microsoft installer)

安装之后：服务器软件+数据文件+mysql进程+环境变量

连接数据库：

cmd下 mysql -uroot -pabc123 -hlocalhost -P3306

本机下可以省略-h和-P

推荐使用：

msql -u root -P 端口号 -h 主机名ip -p

本机省略-P和-h

5.7版本需要设置一下字符集编码；

启动mysql

启动mysql服务端程序

cmd管理员方式运行，然后net start mysql80，关闭使用net stop mysql80

启动mysql客户端程序并连接：

1、基本原理

基于客户机-服务器的数据库，服务器软件负责所有数据访问和处理，客户机软件负责和用户打交道。初次安装MySQL，要求输入管理登录(root+口令)，如果使用本地服务器，使用管理登录即可。MySQL是否区分大小写可以由管理员确定，一般不区分大小写。

数据库本质上是一个文件系统，它保存一系列有组织的数据。

2、用户权限认证

mysql数据库中的3个权限表：user 、db、 host

权限表的存取过程是：

1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证；

2) 通过权限验证，进行权限分配时，按照user?db?tables_priv?columns_priv的顺序进行分配。即先检查全局权限表 user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

创建用户

 

 

3、基础操作

USE 数据库名;     //使用某个数据库
SHOW DATABASES;   //显示所有可用的数据库的列表，列表中可能包含MySQL内部使用的数据库
SHOW TABLES;      //显示当前数据库内可用表的列表
SHOW COLUMNS FROM 表名;    //对每个字段返回一行，行中包括字段名、数据类型、是否允许NULL、键信息、默认值与其他信息；
DESCRIBE 表名;    //与上条语句的功能相同
SHOW GRANTS;      //显示所有用户的安全权限

4、增

CREATE DATABASE 数据库名;   //创建一个新的数据库

新建一张表

不写默认为允许NULL，如果不允许为空，必须指定该列的值为是NOT NULL 

如果想指定默认值，可以使用NOT NULL DEFAULT XXX;

 

插入多条记录：多次insert()或者采用以下语法：

 向另一张表中添加其他表的数据。

5、删

delete只能删除行不能删除整个表

删除整个表：

6、改

修改表名：

7、查

1、返回查询结果的前几行： LIMIT x; 返回查询结果的前x行；

2、指定检索的开始行和检索的行数：LIMIT x,y; 从第x行开始，检索y行；行的索引从0开始；

为了防止混淆，也可以是应用LIMIT x OFFSET y;

3、ORDER BY子句中将要使用的列将是为显示所选择的列，但实际上并不一定要这样，用非检索的列排序数据完全合法；ORDER BY默认升序排列；

4、LIMIT子句必须在ORDER BY之后，通过ORDER BY子句和LIMIT子句可以查找前K大或者前K小的元组。

//选择价格最高的商品
select prod_price
from products
order by prod_price desc
limit 1;              

5、SQL的where子句中的AND的优先级比OR高

6、not in 不在关系内，not between ... and ...;不在之间，not exists 不存在；

7、用正则表达式进行搜索

①基本字符匹配

包含某个文本的所有元组，WHERE prod_name REGEXP 'abc'; 返回包含'abc'的元组

特殊字符 . (.代表任意字符)  WHERE prod_name REGEXP '.000' 返回包含_000的字符串

OR匹配                                WHERE prod_name REGEXP '1000|2000' 返回包含1000或2000的字符串

介绍[]匹配符

匹配几个字符之一 用[]来选择    WHERE prod_name REGEXP '[123] Ton'，[123]在123中选择一个，1 Ton 或者 2 Ton 或者 3 Ton  //先当于OR，它事实上是[1|2|3]的缩写

为了匹配除指定字符外的任何东西，可以在集合的开始除放置一个^  [^123]除了1、2、3之外的任意一个符号都可以

太长了怎么办 [0123456789]   可以写成[0-9]; [123]可以写成[1-3]；[abc....z]可以写成[a-z]

对于这些特殊字符，我们怎么进行转义？ 前面加\\            \\.查找.    \\-查找 -

.、|、[]、\、(、)都需要转义

匹配字符类：使用预定义的字符集合

[:alnum]   任意字母和数字(同[a-zA-Z0-9]

[:alpha:]   任意字符(同[a-zA-Z]

[:blank:]    空格或制表

[:digit:]      任意数字

[:upper:]   任意大写字母

[:lower:]    任意小写字母

前面的正则表达式都试图匹配单次出现，如果是多次呢？

*0个或多个匹配；+1个或多个匹配，?0个或1个匹配 

{n}指定数目的匹配，{n,}不少于指定数目的匹配；{n,m}匹配数目的范围

8、日期字段的创建以及日期记录的插入

日期类型：

datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038
date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31

示例：

创建：create table orders (order_time datetime);

           create table orders (order_time timestamp);

           create table orders (order_time date);

插入：curdate(); 只有日期部分，yyyy-mm-dd，如果字段包括时间则  默认为00:00:00

           curtime();只有时间

           now();日期+时间

insert into orders values(now());

9、MySQL中的联结

①内部联结（其实就是from做笛卡尔积，然后where筛选）

from a inner join b;

相当于，作笛卡尔积，筛选相同属性相等的列。

也可以指定某些属性相等：

from a inner join b

on a.xx = b.xx;

②外部联结

联结包含了那些在相关表中没有关联行的行

OUTER JOIN

LEFT OUTER JOIN包含左边表的所有行；RIGHT OUTER JOIN包含右边表的所有行

FROM A LEFT OUTER JOIN B

ON A.XXX = B.XXX;

10、全文本搜索

MySQL原理解析

1、引擎

2、客户端与服务器端连接：

MySQL是程序，程序就是一个进程，进程之间通信是不是要用IP+端口号，默认是IP:3306（怎么修改？-P 3307），所以可以用TCP/IP协议。IP相同就是同一主机的不同进程。既然是进程间通信，那么还有哪些进程间通信方式？命名管道和共享内存（共享内存必须在同一主机，找时间总结）！！！

 客户端--处理连接------查询缓存--语法解析--查询优化--（MyISAM、InnoDB、Memory...）--文件系统

3、MySQL索引

八百万条记录，查询一次4.5s，文件大小524M；

CREAT INDEX 索引名称 ON 表名(列名)；

CREAT INDEX empno_index ON emp(empno)；

创建索引后文件变为655M，索引本身也会占用空间，创建索引后速度大大加快。 ---0.003s

创建索引后，只对创建了索引的列有效！！！

索引的原理：

当我们没有索引，会进行全表扫描，查询速度慢

索引的类型：

主键索引：主键自动的为主索引（类型Primary key），主键一定是索引

唯一索引：(UNIQUE)  指定一列是unique，则该列是唯一的，同时也是索引

普通索引：（INDEX）

全文索引（FULLTEXT）（适用于MyISAM），实际开发中很少使用

开发中考虑：全文搜索Solr和ElasticSearch（ES）

语法：

查询某找表中的索引：

SHOW INDEXES FROM 表名;

create [UNIQUE] index index_name on tbl_name (col_name[(length)]);

添加唯一索引（如果在创建表的时候指定为UNIQUE，默认添加唯一索引）：

 CREATE UNIQUE INDEX id_index ON hspt25(id);   //创建唯一索引，同时id转为UNIQUE类型

如果不希望id是唯一的，则选择普通索引，如果id是唯一的，优先选择唯一索引，普通索引如下：

 CREATE INDEX id_index ON hspt25(id);

添加普通索引方式2：

ALTER TABLE 表名 ADD INDEX 索引名 （列名）;

添加主键索引：

方式一：创建表的时候添加primar key

方式二：ALTER TABLE 表名 ADD PRIMARY KEY （列名）;

删除索引：

DROP INDEX 索引名称 ON 表名；

删除主键索引：

ALTER TABLE 表名 DROP PRIMARY KEY;

修改索引：

先删除，再添加新的索引

查询索引：

SHOW INDEX FROM 表名；

SHOW INDEXES FROM 表名；

SHOW KEYS FROM 表名；

DESC 表名；

创建索引的原则：

较频繁的作为查询条件的字段应该创建索引，唯一性太差的字段不适合单独创建索引，更新非常频繁的字段不适合创建索引，不会出现在where子句中子段不该创建索引。