文章目录
系列目录
Python|Git remote|hosts|PyCharm常用快捷键|变量转换|命名|类型|运算符|分支|调整tab|循环|语言基础50课:学习记录(1)-项目简介及变量、条件及循环
Python|list|切片|列表的运算符、比较及遍历|生成式|元素位置和次数|元素排序和反转|sort() 方法|嵌套的列表|语言基础50课:学习记录(2)-常用数据结构之列表
Python|元组|字符串|语言基础50课:学习记录(3)-常用数据结构之元组及字符串相关
Python|集合|运算|哈希码|语言基础50课:学习记录(4)-常用数据结构之集合
Python|字典|函数和模块|应用及进阶|分数符号(Latex)|String库|operator库|处理数据三步骤|语言基础50课:学习记录(5)-常用数据结构之字典、函数和模块应用及进阶
Python|装饰器|执行时间|递归|动态属性|静态方法和类|继承和多态|isinstance类型判断|溢出|“魔法”方法|语言基础50课:学习记录(6)-函数的高级应用、面向对象编程、进阶及应用
Python|base64|collections|hashlib|heapq|itertools|random|os.path|uuid|文件|异常|JSON|API|CSV|语言基础50课:学习7
Python|xlwt|xlrd|调整单元格样式(背景,字体,对齐、虚线边框、列宽行高、添加公式)|xlutils|openpyxl|只读与只写|图表|语言基础50课:学习(8)
Python|python-docx|python-pptx|Pillow|smtplib|螺丝帽短信网关|正则表达式的应用|语言基础50课:学习(9)
Python|http|Chrome Developer Tools|Postman|HTTPie|builtwith库|python-whois库|爬虫及解析|语言基础50课:学习(10)
Python|线程和进程|阻塞|非阻塞|同步|异步|生成器和协程|资源竞争|进程间通信|aiohttp库|daemon属性值详解|语言基础50课:学习(11)
Python|并发编程|爬虫|单线程|多线程|异步I/O|360图片|Selenium及JavaScript|Scrapy框架|BOM 和 DOM 操作简介|语言基础50课:学习(12)
Python|MySQL概述|Windows-Linux-macOS安装|MySQL 基本命令|获取帮助|SQL注释|语言基础50课:学习(13)
Python|SQL详解之DDL|DML|DQL|DCL|索引|视图、函数和过程|JSON类型|窗口函数|接入MySQL|清屏|正则表达式|executemany|语言基础50课:学习(14)
原项目地址
Python-Core-50-Courses(https://hub.fastgit.org/jackfrued/Python-Core-50-Courses.git)
第40课:关系型数据库和MySQL概述
关系型数据库概述
-
数据持久化 - 将数据保存到能够长久保存数据的存储介质中,在掉电的情况下数据也不会丢失。
-
数据库发展史 - 网状数据库、层次数据库、关系数据库、NoSQL 数据库、NewSQL 数据库。
1970年,IBM的研究员E.F.Codd在Communication of the ACM上发表了名为A Relational Model of Data for Large Shared Data Banks的论文,提出了关系模型的概念,奠定了关系模型的理论基础。后来Codd又陆续发表多篇文章,论述了范式理论和衡量关系系统的12条标准,用数学理论奠定了关系数据库的基础。
-
关系数据库特点。
-
理论基础:关系代数(关系运算、集合论、一阶谓词逻辑)。
-
具体表象:用二维表(有行和列)组织数据。
-
编程语言:结构化查询语言(SQL)。
-
-
ER模型(实体关系模型)和概念模型图。
ER模型,全称为实体关系模型(Entity-Relationship Model),由美籍华裔计算机科学家陈品山先生提出,是概念数据模型的高层描述方式,如下图所示。
- 实体 - 矩形框
- 属性 - 椭圆框
- 关系 - 菱形框
- 重数 - 1:1(一对一) / 1:N(一对多) / M:N(多对多)
实际项目开发中,我们可以利用数据库建模工具(如:PowerDesigner)来绘制概念数据模型(其本质就是 ER 模型),然后再设置好目标数据库系统,将概念模型转换成物理模型,最终生成创建二维表的 SQL(很多工具都可以根据我们设计的物理模型图以及设定的目标数据库来导出 SQL 或直接生成数据表)。
-
关系数据库产品。
- Oracle - 目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;作为一个关系数据库,它是一个完备关系的产品;作为分布式数据库,它实现了分布式处理的功能。在 Oracle 最新的 12c 版本中,还引入了多承租方架构,使用该架构可轻松部署和管理数据库云。
- DB2 - IBM 公司开发的、主要运行于 Unix(包括 IBM 自家的 AIX)、Linux、以及 Windows 服务器版等系统的关系数据库产品。DB2 历史悠久且被认为是最早使用 SQL 的数据库产品,它拥有较为强大的商业智能功能。
- SQL Server - 由 Microsoft 开发和推广的关系型数据库产品,最初适用于中小企业的数据管理,但是近年来它的应用范围有所扩展,部分大企业甚至是跨国公司也开始基于它来构建自己的数据管理系统。
- MySQL - MySQL 是开放源代码的,任何人都可以在 GPL(General Public License)的许可下下载并根据个性化的需要对其进行修改。MySQL 因为其速度、可靠性和适应性而备受关注。
- PostgreSQL - 在 BSD 许可证下发行的开放源代码的关系数据库产品。
MySQL 简介
MySQL 最早是由瑞典的 MySQL AB 公司开发的一个开放源码的关系数据库管理系统,该公司于2008年被昇阳微系统公司(Sun Microsystems)收购。在2009年,甲骨文公司(Oracle)收购昇阳微系统公司,因此 MySQL 目前也是 Oracle 旗下产品。
MySQL 在过去由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被广泛地应用于中小型网站开发。随着 MySQL 的不断成熟,它也逐渐被应用于更多大规模网站和应用,比如维基百科、谷歌(Google)、脸书(Facebook)、淘宝网等网站都使用了 MySQL 来提供数据持久化服务。
甲骨文公司收购后昇阳微系统公司,大幅调涨 MySQL 商业版的售价,且甲骨文公司不再支持另一个自由软件项目 OpenSolaris 的发展,因此导致自由软件社区对于 Oracle 是否还会持续支持 MySQL 社区版(MySQL 的各个发行版本中唯一免费的版本)有所担忧,MySQL 的创始人麦克尔·维德纽斯以 MySQL 为基础,创建了 MariaDB(以他女儿的名字命名的数据库)分支。有许多原来使用 MySQL 数据库的公司(例如:维基百科)已经陆续完成了从 MySQL 数据库到 MariaDB 数据库的迁移。
安装 MySQL
Windows 环境
-
通过官方网站提供的下载链接下载“MySQL社区版服务器”安装程序,如下图所示,建议大家下载离线安装版的MySQL Installer。
-
运行 Installer,按照下面的步骤进行安装。
-
选择自定义安装。
-
选择需要安装的组件。
-
如果缺少依赖项,需要先安装依赖项。
-
准备开始安装。
-
安装完成。
-
准备执行配置向导。
-
-
执行安装后的配置向导。
-
配置服务器类型和网络。
-
配置认证方法(保护密码的方式)。
-
配置用户和角色。
-
配置Windows服务名以及是否开机自启。
-
配置日志。
-
配置高级选项。
-
应用配置及设置
- 安装成功
-
-
可以在 Windows 系统的“服务”窗口中启动或停止 MySQL。
-
配置 PATH 环境变量,以便在命令行提示符窗口使用 MySQL 客户端工具。
-
打开 Windows 的“系统”窗口并点击“高级系统设置”。
-
在“系统属性”的“高级”窗口,点击“环境变量”按钮。
-
修改PATH环境变量,将MySQL安装路径下的
bin
文件夹的路径配置到PATH环境变量中。
-
配置完成后,可以尝试在“命令提示符”下使用 MySQL 的命令行工具。
-
-
使用 MySQL 客户端工具连接服务器。
命令行工具:
mysql -u root -p
说明:启动客户端时,
-u
参数用来指定用户名,MySQL 默认的超级管理账号为root
;-p
表示要输入密码(用户口令);如果连接的是其他主机而非本机,可以用-h
来指定连接主机的主机名或IP地址。
进入客户端工具后,可以通过下面的指令来修改超级管理员(root)的访问口令为123456
。
set global validate_password_policy=0;
set global validate_password_length=6;
alter user 'root'@'localhost' identified by '123456';
说明:MySQL 较新的版本默认不允许使用弱口令作为用户口令,所以上面的代码修改了验证用户口令的策略和口令的长度。事实上我们不应该使用弱口令,因为存在用户口令被暴力破解的风险。近年来,攻击数据库窃取数据和劫持数据库勒索比特币的事件屡见不鲜,要避免这些潜在的风险,最为重要的一点是不要让数据库服务器暴露在公网上(最好的做法是将数据库置于内网,至少要做到不向公网开放数据库服务器的访问端口),另外要保管好
root
账号的口令,应用系统需要访问数据库时,通常不使用root
账号进行访问,而是创建其他拥有适当权限的账号来访问。
再次使用客户端工具连接 MySQL 服务器时,就可以使用新设置的口令了。在实际开发中,为了方便用户操作,可以选择图形化的客户端工具来连接 MySQL 服务器,包括:
- MySQL Workbench(官方工具)
- Navicat for MySQL(界面简单友好)
Linux 环境
下面以 CentOS 7.x 环境为例,演示如何安装 MySQL 5.7.x,如果需要在其他 Linux 系统下安装其他版本的 MySQL,请读者自行在网络上查找对应的安装教程。
-
安装 MySQL。
可以在 MySQL 官方网站下载安装文件。首先在下载页面中选择平台和版本,然后找到对应的下载链接,直接下载包含所有安装文件的归档文件,解归档之后通过包管理工具进行安装。
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar
如果系统上有 MariaDB 相关的文件,需要先移除 MariaDB 相关的文件。
yum list installed | grep mariadb | awk '{print $1}' | xargs yum erase -y
更新和安装可能用到的底层依赖库。
yum update yum install -y libaio libaio-devel
接下来可以按照如下所示的顺序用 RPM(Redhat Package Manager)工具安装 MySQL。
rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm rpm -ivh mysql-community-devel-5.7.26-1.el7.x86_64.rpm rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
可以使用下面的命令查看已经安装的 MySQL 相关的包。
rpm -qa | grep mysql
-
配置 MySQL。
MySQL 的配置文件在
/etc
目录下,名为my.cnf
,默认的配置文件内容如下所示。cat /etc/my.cnf
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
通过配置文件,我们可以修改 MySQL 服务使用的端口、字符集、最大连接数、套接字队列大小、最大数据包大小、日志文件的位置、日志过期时间等配置。当然,我们还可以通过修改配置文件来对 MySQL 服务器进行性能调优和安全管控。
-
启动 MySQL 服务。
可以使用下面的命令来启动 MySQL。
service mysqld start
在 CentOS 7 中,更推荐使用下面的命令来启动 MySQL。
systemctl start mysqld
启动 MySQL 成功后,可以通过下面的命令来检查网络端口使用情况,MySQL 默认使用
3306
端口。netstat -ntlp | grep mysql
也可以使用下面的命令查找是否有名为
mysqld
的进程。pgrep mysqld
-
使用 MySQL 客户端工具连接服务器。
命令行工具:
mysql -u root -p
说明:启动客户端时,
-u
参数用来指定用户名,MySQL 默认的超级管理账号为root
;-p
表示要输入密码(用户口令);如果连接的是其他主机而非本机,可以用-h
来指定连接主机的主机名或IP地址。如果是首次安装 MySQL,可以使用下面的命令来找到默认的初始密码。
cat /var/log/mysqld.log | grep password
上面的命令会查看 MySQL 的日志带有
password
的行,在显示的结果中root@localhost:
后面的部分就是默认设置的初始密码。进入客户端工具后,可以通过下面的指令来修改超级管理员(root)的访问口令为
123456
。set global validate_password_policy=0; set global validate_password_length=6; alter user 'root'@'localhost' identified by '123456';
说明:MySQL 较新的版本默认不允许使用弱口令作为用户口令,所以上面的代码修改了验证用户口令的策略和口令的长度。事实上我们不应该使用弱口令,因为存在用户口令被暴力破解的风险。近年来,攻击数据库窃取数据和劫持数据库勒索比特币的事件屡见不鲜,要避免这些潜在的风险,最为重要的一点是不要让数据库服务器暴露在公网上(最好的做法是将数据库置于内网,至少要做到不向公网开放数据库服务器的访问端口),另外要保管好
root
账号的口令,应用系统需要访问数据库时,通常不使用root
账号进行访问,而是创建其他拥有适当权限的账号来访问。再次使用客户端工具连接 MySQL 服务器时,就可以使用新设置的口令了。在实际开发中,为了方便用户操作,可以选择图形化的客户端工具来连接 MySQL 服务器,包括:
-
MySQL Workbench(官方工具)
-
Navicat for MySQL(界面简单友好)
-
macOS环境
macOS 系统安装 MySQL 是比较简单的,只需要从刚才说到的官方网站下载 DMG 安装文件并运行就可以了,下载的时候需要根据自己使用的是 Intel 的芯片还是苹果的 M1 芯片选择下载链接,如下图所示。
安装成功后,可以在“系统偏好设置”中找到“MySQL”,在主程序画面中,可以启动和停止 MySQL 服务器,也可以对 MySQL 核心文件的路径进行配置。
MySQL 基本命令
查看命令
- 查看所有数据库
show databases;
- 查看所有字符集
show character set;
- 查看所有的排序规则
show collation;
- 查看所有的引擎
show engines;
- 查看所有日志文件
show binary logs;
- 查看某数据库下的所有表(例如:切换数据库为mybases,再显示表)
use mybases;
show tables;
获取帮助
在 MySQL 命令行工具中,可以使用help
命令或?
来获取帮助,如下所示。
-
查看
show
命令的帮助。? show
返回值:
mysql> ? show Name: 'SHOW' Description: SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: SHOW AUTHORS SHOW {BINARY | MASTER} LOGS SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] SHOW CHARACTER SET [like_or_where] SHOW COLLATION [like_or_where] SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where] SHOW CONTRIBUTORS SHOW CREATE DATABASE db_name SHOW CREATE EVENT event_name SHOW CREATE FUNCTION func_name SHOW CREATE PROCEDURE proc_name SHOW CREATE TABLE tbl_name SHOW CREATE TRIGGER trigger_name SHOW CREATE VIEW view_name SHOW DATABASES [like_or_where] SHOW ENGINE engine_name {STATUS | MUTEX} SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW EVENTS SHOW FUNCTION CODE func_name SHOW FUNCTION STATUS [like_or_where] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW MASTER STATUS SHOW OPEN TABLES [FROM db_name] [like_or_where] SHOW PLUGINS SHOW PROCEDURE CODE proc_name SHOW PROCEDURE STATUS [like_or_where] SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n] SHOW PROFILES SHOW SLAVE HOSTS SHOW SLAVE STATUS SHOW [GLOBAL | SESSION] STATUS [like_or_where] SHOW TABLE STATUS [FROM db_name] [like_or_where] SHOW [FULL] TABLES [FROM db_name] [like_or_where] SHOW TRIGGERS [FROM db_name] [like_or_where] SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] SHOW WARNINGS [LIMIT [offset,] row_count] like_or_where: LIKE 'pattern' | WHERE expr If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL "%" and "_" wildcard characters. The pattern is useful for restricting statement output to matching values. Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See http://dev.mysql.com/doc/refman/5.5/en/extended-show.html. URL: http://dev.mysql.com/doc/refman/5.5/en/show.html
-
查看有哪些帮助内容。
? contents
返回值:
You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Storage Engines Table Maintenance Transactions User-Defined Functions Utility
-
获取函数的帮助。
? functions
返回值:
You asked for help about help category: "Functions" For more information, type 'help <item>', where <item> is one of the following categories: Bit Functions Comparison operators Control flow functions Date and Time Functions Encryption Functions Information Functions Logical operators Miscellaneous Functions Numeric Functions String Functions
查询某类函数用法:
mysql> help String Functions You asked for help about help category: "String Functions" For more information, type 'help <item>', where <item> is one of the following topics: ASCII BIN BINARY OPERATOR BIT_LENGTH CAST CHAR FUNCTION CHARACTER_LENGTH CHAR_LENGTH CONCAT CONCAT_WS CONVERT ELT EXPORT_SET EXTRACTVALUE FIELD FIND_IN_SET FORMAT HEX INSERT FUNCTION INSTR LCASE LEFT
-
获取数据类型的帮助。
? data types
返回值:
You asked for help about help category: "Data Types" For more information, type 'help <item>', where <item> is one of the following topics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOLEAN CHAR CHAR BYTE DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECISION ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT SET DATA TYPE SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARBINARY VARCHAR YEAR DATA TYPE
其他命令
-
新建/重建服务器连接 -
connect
/resetconnection
。 -
清空当前输入 -
\c
。在输入错误时,可以及时使用\c
清空当前输入并重新开始。 -
修改终止符(定界符)-
delimiter
。默认的终止符是;
,可以使用该命令修改成其他的字符,例如修改为$
符号,可以用delimiter $
命令。 -
打开系统默认编辑器 -
edit
。编辑完成保存关闭之后,命令行会自动执行编辑的内容。 -
查看服务器状态 -
status
。 -
修改默认提示符 -
prompt
。 -
执行系统命令 -
system
。可以将系统命令跟在system
命令的后面执行,system
命令也可以缩写为\!
。 -
执行 SQL 文件 -
source
。source
命令后面跟 SQL 文件路径。 -
重定向输出 -
tee
/notee
。可以将命令的输出重定向到指定的文件中。 -
切换数据库 -
use
。 -
显示警告信息 -
warnings
。 -
退出命令行 -
quit
或exit
。
TIPS:SQL语句中的注释
MySQL注释符有三种:
1、#注释内容,表示单行注释
2、“-- 注释内容” (注意–后面有一个空格)
3、/*注释内容*/
另外,需要注意以下几点:
/* .... */
在大部分编程语言中都是注释,这个注释之中的语句是不被执行的。但是,在MySQL中为了保持兼容,比如从mysqldump导出的SQL语句能被其它数据库直接使用,它把一些特有的仅在MySQL上的语句放在/*! ... */
中,这样这些语句如果在其它数据库中是不会被执行,但在MySQL中它会执行。在这里,*和!之间不能有空格。
语句例如“/*!50701 select * from test */;”,这里的50701表示假设数据库服务器是5.7.01以上版本,该语句才会被执行。