第1章 初识MySQL
MySQL是一个开放源代码的数据库管理系统(DBMS),它是由MySQL AB公司开发、发布和支持的。MySQL是一个跨平台(Windows、Linux、UNIX、MacOS)的开源“关系型”数据库管理系统,广泛地应用在Internet上的中小型网站开发中。
1.1 数据库基础
1.1.1 什么是数据库
1)保管数据的“仓库”;
2)数据管理的方法和技术;
1.1.2 表
类似于Excel表,行记录,列字段!
1.1.3 数据类型
常见的数据类型有:整数、浮点数、精确小数、二进制、日期/时间、字符串
1.1.4 主键
1)主键又称为主码,用于唯一标识表中的每一条记录。
2)可定义表中一列或者多列为主键(Primary Key)
3)主键列上不能有两行相同的值,也不能为空值null
1.2 数据库技术构成
1.2.1 数据库系统
数据库管理系统的3个主要组成部分
1)数据库:用于存储数据的地方
2)数据库管理系统:用于管理数据库的软件software
3)数据库应用:为了提高数据库系统的处理能力所使用的管理数据库软件的补充
数据库管理系统(DataBase Management System,DBMS)是用户创建、管理和维护数据库时使用的软件,位于操作系统和用户之间,对数据库进行统一管理。DBMS能定义数据存储结构,提供数据的从操作机制,维护数据库的安全性、完整性和可靠性。
用户 用户 用户———— Layer5
应用程序 ————Layer4
数据库管理系统 ————Layer3
操作系统 ————Layer2
硬件————Layer1
1.2.2 SQL语言
对数据库进行查询和修改操作的语言叫做SQL。(Structured Query Language)工业标准SQL-92
SQL包含如下
1)数据定义语言(DDL):DROP、CREATE、ALTER等语句
2)数据操作语言(DML):INSERT、UPDATE、DELETE语句
3)数据查询语言(DQL):SELECT语句
4)数据控制语言(DCL):GRANT、REVOKE、COMMIT、ROLLBACK语句
1.2.3 数据库访问接口
1)ODBC(Open Database Connectivity开放数据库互连),最大优点是可以以统一的方式处理所有数据库。
2)JDBC(Java Data Base ConnectivityJava数据库连接),用于Java应用程序连接数据库的标准和方法。
3)ADO.NET,是在微软.NET框架下开发设计的一组用于和数据源进行交互的面向对象的类库。
4)PDO(PHP Data Object),为PHP访问数据库定义了一个轻量级的、一致性的接口。
针对不同的语言,MySQL提供了不同数据库的“访问连接驱动”,可登录官网下载。
1.3 什么是MySQL
MySQL是一个小型关系型数据库管理系统,与其他大象数据库管理系统(Oracle、DB2、SQL Server)相比,MySQL规模小、功能有限,但是它体积小、速度快、成本低且它提供的功能对稍微复杂的应用已经够用,这些特性使得MySQL成为世界上最受欢迎的开放源码数据库。
1.3.1 客户机-服务器软件
主从式架构(Client-server-model)或客户端——服务器(Client/Server)结构简称C/S结构,是一种网络架构,通常在该网络架构下软件分为客户端(Client)和服务器(Server)
这种系统的特点是,客户端和服务器程序不在同一台计算机运行,这些客户端和服务器程序通常归属不同的计算机。
主从式架构通过不同的途径应用于很多不同类型的应用程序,比如在因特网上使用的网页。
举例:当顾客从京东上买东西的时候,电脑和网页浏览器就被当做一个客户端,同时组成当当网的电脑、数据库和应用程序就被当做服务器。
服务器端一般使用高性能的计算机,并配合使用不同类型的数据,如oracle、Sybase或者MySQL。
1.3.2 MySQL版本
1)MySQL Community Server(社区版):完全免费,但官方不提供技术支持
2)MySQL Enterprise Server(企业版服务器):它能够以很高性价比为企业提供数据库仓库应用,支持ACID事物处理,提供完整的提交、回滚、崩溃恢复和行级锁定功能。需付费,官方提供电话技术支持。
3)MySQL Cluster主要用于架设集群服务器,需要在社区版或者企业版的基础上使用。
Server version: 5.7.13-log MySQL Community Server (GPL)
1)第1个数字,主版本号
2)第2个数字,发行级别
3)第3个数字,是再次发行系列的版本号,随每次新分发版本递增,通常是已发行的最新版本。
4)MySQL(Current Generally Available Release)版本已经过严格标准的测试,可以保证其安全可靠的使用。
1.3.3 MySQL优势
1)速度:运行速度快;
2)价格:MySQL对多数人来说是免费的;
3)容易使用:与其他大型数据库的设置和管理相比,其复杂程度较低,易于学习掌握;
4)可移植性(跨平台):Windows、Linux、UNIX、Mac OS等众多不同的系统平台上使用;
5)丰富的接口:提供了用于C、C++、Java、Perl、PHP、Python、Ruby等语言的API;
6)支持查询语言:MySQL可以利用标准的SQL语法和支持ODBC(开放式数据库连接)的应用程序;
7)安全性和连接性:十分灵活和安全的权限和密码系统,允许基于主机的验证。连接到服务器时,所有的密码传输均采用加密形式,从而保证了密码安全;
8)MySQL是网络化的,可以在因特网上的任何地方访问,提高数据共享的效率;
1.3.4 MySQL5.7的新功能
1)支持JSON
JSON(JavaScript Object Notation的缩写)是一种存储信息的格式,可以很好地替代XML。从MySQL5.7.8版本开始,MySQL将支持JSON。
2)性能和可拓展性
改进InnoDB的可拓展性和临时表的性能,从而实现更快的网络和大数据家在等操作
3)改进复制以提高可用性的性能
改进复制包括多源复制、多从线程增强、在线GTIDs和增强的半同步复制
4)性能模式提供更好的视角
5)安全
6)优化
7)GIS
1.4 MySQL工具
1.4.1 MySQL命令行实用程序
1)mysqld:SQL后台程序(即MySQL服务器进程)。该程序必须运行之后,客户端才能通过连接服务器来访问数据库。
2)mysqld_safe:服务器启动脚本。
3)mysql.server:服务器启动脚本。
4)mysql_multi:服务器启动脚本,可以启动或停止系统上安装的多个服务器
1.4.2 MySQL Workbench
MySQL Workbench是下一代可视化数据库设计软件,功能如下:
1)数据库设计和模型建立
2)SQL开发(取代MySQL Query Browser)
3)数据库管理(取代MySQL Administrator)
MySQL Workbench Community Edition(也叫MySQL Workbench OSS,社区版)
MySQL Workbench Standard Edition(也叫 MySQL Workbench SE,商业版)
1.5 如何学习MySQL
1)培养兴趣(兴趣是最好的老师,不论学习什么知识,兴趣都可以极大的提高学习效率)
2)夯实基础
计算机领域的技术非常强调基础,刚开始学习可能还认识不到这一点,随着技术应用的深入,只有有着扎实的基础功底,才能在技术的道路上走的更快、更远。对于MySQL的学习,SQL语句就是其中最为基础的部分。
3)及时学习新知识
搜索引擎、参考他人的解决问题的思路,吸取别人的经验以及获取最新的技术资料
4)多实践操作
数据库系统具有极强的操作性,需要多动手上机操作。
第2章 MySQL的安装与配置
Windows平台下安装MySQL,可以使用图形化的安装包,它提供了详细的安装向导,读者可一步一步地按照提示完成的MySQL的安装。
2.1 Windows平台下安装与配置MySQL5.7
2.1.1 安装MySQL5.7
Windows平台下提供两种安装方式:
1)MySQL二进制分发版(.msi安装文件)
2)免安装版.zip压缩文件
一般来讲,应当使用二进制分发版,因为该版本比其他的分发版本使用起来要简单,不需要借助其他工具来启动就可以运行MySQL。
2.1.2 配置MySQL5.7
1)Development Machine(开发机器):该选项代表典型的个人用桌面工作站。假定机器上运行着多个桌面应用程序,将MySQL服务器配置成使用最少的系统资源。
2)Server Machine(服务器):该选项该表服务器,MySQL服务器可以同其他应用程序一起运行,如FTP、Email和Web服务器,MySQL服务器配置成使用适当比例的系统资源
3)Dedicated Machine(专用服务器):该选项代表只运行MySQL服务的服务器。假定没有运行其他服务程序,MySQL服务器配置成使用所有可用的系统资源。
2.2 启动服务并登录MySQL数据库
MySQL安装完毕之后,需要启动服务器的进程,不然客户端无法连接数据库。客户端通过命令行工具登录数据库。
1)cmd-》net start mysql
2)cmd-》net stop mysql
2.2.1 启动MySQL服务
2.2.2 登录MySQL数据库
1)DOS窗口登录MySQL数据库
2)使用MySQL Commond Line Client登录MySQL
2.2.3 配置Path环境变量
1)解决窗口闪一下消失的问题,复制mysql-default.ini,然后将副本命名为mysql.ini即可。
2.3 MySQL常用图形化管理工具
MySQL图形化管理工具极大地方便了数据库的操作与管理,常用图形化工具介绍如下:
1)MySQL Workbench
2)phpMyAdmin
3)Navicat for MySQL
4)MySQL Dumper
5)SQLyog
2.4 Linux平台下安装与配置MySQL5.7
Linux操作系统有众多的发行版,不同平台下需要安装不同的MySQL版本,MySQL主要支持的Linux版本有SUSE Linux enterprise Server和Red Hat&Oracle Enterprise Linux
2.4.1 Linux操作系统下的MySQL版本介绍
Linux操作系统是自由软件和开放源代码发展中最著名的例子。其诞生以后,经过全世界各地计算机爱好者的共同努力,现已成为今天世界上使用最多的一种“类UNIX”操作系统,目前已经开发超过300个发行版本,比较流行的版本有:Ubuntu、Debian、GNU/Linux、Fedora、openSUSE、和RedHat系列。
目前MySQL主要支持的Linux版本为SUSE和Red Hat,Linux操作系统下MySQL安装包分为3类
1)RPM:RPM软件包是一种在Linux平台下的安装文件,通过安装命令可很方便地安装与卸载。(RedHat系列),MySQL的RPM安装文件分为两个:服务端和客户端,需分别下载和安装。
2)Generic Binaries:二进制软件包,经过编译生成的二进制文件软件包。
3)源码包:源码包是MySQL数据库的源代码,用户需要自己编译成二进制文件后方可安装。
2.4.2 安装和配置MySQL的RPM包
MySQL推荐使用RPM包进行Linux平台下的安装,从官方下载的RPM包能够在所有支持RPM packages,glibc2.3的Linux系统下安装和使用
Linux平台下的MySQL安装目录
/usr/bin 客户端和脚本
/usr/sbin MySQLD服务器
/var/lib/mysql 日志文件和数据库
/usr/share/info 信息格式的手册
/usr/share/man UNIX帮助页
/usr/include/mysql 头文件
/usr/lib/mysql 库
/usr/share/mysql 错误消息、字符集、示例配置文件等
mysql-5.7.13-1.el7.x86_64.rpm-bundle.tar解压缩之后如下:
mysql-community-client-5.7.13-1.el7.x86_64.rpm#是客户端安装包;
mysql-community-common-5.7.13-1.el7.x86_64.rpm#是服务端的安装包;
mysql-community-devel-5.7.13-1.el7.x86_64.rpm#是包含开发用的库文件安装的包;
mysql-community-embedded-5.7.13-1.el7.x86_64.rpm#是嵌入式MySQL的安装包;
mysql-community-embedded-compat-5.7.13-1.el7.x86_64.rpm;
mysql-community-embedded-devel-5.7.13-1.el7.x86_64.rpm;
mysql-community-libs-5.7.13-1.el7.x86_64.rpm;
mysql-community-libs-compat-5.7.13-1.el7.x86_64.rpm;
mysql-community-minimal-debuginfo-5.7.13-1.el7.x86_64.rpm;
mysql-community-server-5.7.13-1.el7.x86_64.rpm;
mysql-community-server-minimal-5.7.13-1.el7.x86_64.rpm;
mysql-community-test-5.7.13-1.el7.x86_64.rpm#是一些测试的安装包;
一般情况下,只需要安装client和server两个包,如需进行C、C++MySQL相关开发,请安装mysql-community-devel-5.7.13-1.el7.x86_64.rpm
安装之前,要查看机器上是否已经装有就办的MySQL,如果有,最好先把旧版MySQL卸载,否则可能产生冲突。
rpm -qa|grep -i mysql
从MySQL5.0开始,MySQL的服务名改为mysql,而不是4.*版本的mysqld
MySQL服务的操作命令是
1)service mysql start|stop|restart|status#CentOS6.X下
2)systemctl start|stop|restart|status mysqld#CentOS7.X下,因为CentOS7系列使用了systemd管理工具
2.4.3 安装和配置MySQL的源码包
进入下载页面:https://downloads.mysql.com/archives/community/
源码安装需要一些开发工具
1)CMaked(cross platform make),构建程序必需的一个跨平台的构建工具。官方地址:http://www.cmake.org/
2)一个好的make工具,MySQL官方推荐使用GNU make3.75,GNUmake下载地址:http://www.gun.org/software/make/
3)ANSI C++编译器,GCC 4.2.1及以上版本
4)Perl,运行test版本所必需的。
5)rpm包管理器,rpmbuild工具
输入如下命令:
rpmbuild --rebuild --clean MySQL-5.7.20-1.linux2.6.src.rpm,编译完成后会形成一个rpm包,然后按照rpm包的安装方法就可以了。
初级用户,不建议使用源码包进行安装。
2.5 专家解惑
计算机技术具有很强的操作性,MySQL的安装和配置是一件非常简单的事,但是在操作过程中也可能出现问题,多实践,多总结。
在安装MySQL5.7软件安装包之前,用户需要确保系统中已经安装了.Net Framework3.5和.Net Framework4.0,如何缺少,将不能正常安装。
2.6 经典习题
1)下载并安装MySQL
2)使用配置向导配置MySQL为系统服务,在系统服务对话框中,手动启动或关闭MySQL服务
3)使用net命令启动或者关闭MySQL服务
4)使用免安装的软件包安装MySQL
第3章 数据库的基本操作
MySQL安装好之后,首先要“创建数据库实例”,这是使用MySQL各种功能的前提。
3.1 创建数据库
MySQL安装好之后,将会在data目录之下自动创建几个必须的数据库,可使用SHOW DATABASES;语句来查看。
1)mysql是必须的,它描述用户访问权限;
2)用户经常用test数据库实例做测试工作;
3)创建数据库是在系统磁盘上划分一块区域,用于数据的存储和管理;
MySQL中创建数据库的基本SQL语法格式为:
CREATE DATABASE database_name;
【例3.1】创建测试数据库 test_db;
CREATE DATABASE test_db;
【例3.2】查看创建好的数据库test_db的定义,如下:
SHOW CREATE DATABASE test_db\G;
[root@iZ2ze3eaa380cnnuepvyrwZ ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.7.13 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec) mysql> create database test_db;
Query OK, 1 row affected (0.15 sec) mysql> show create database test_db;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| test_db | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.03 sec) mysql> show create database test_db\G;
*************************** 1. row ***************************
Database: test_db
Create Database: CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec) ERROR:
No query specified mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.00 sec) mysql>
3.2 删除数据库
删除数据库是将已存在的数据库从磁盘空间上清除,清除之后,数据库实例中的所有数据也将一同被删除。
1、MySQL中删除数据的基本语法格式为:
DROP DATABASE database_name(数据库实例名);如果不存在,则报错!
【例3.3】删除测试数据库test_db,输入语句如下:
DORP DATABASE test_db;
mysql> drop database test_db;
Query OK, 0 rows affected (0.01 sec) mysql> show create database test_db\G;
ERROR 1049 (42000): Unknown database 'test_db'
ERROR:
No query specified mysql> show create database test_db;
ERROR 1049 (42000): Unknown database 'test_db'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec) mysql>
小结:
1、使用DROP DATABASE命令时要非常谨慎,在执行该命令时,MySQL不会给出任何提示确认信息,DROP DATABASE 声明删除数据库实例后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复。
3.3 数据库存储引擎
数据库存储引擎是数据库底层软件的组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,可以获得特定的功能。MySQL数据库的核心就是存储引擎。
可以通过SHOW ENGINES语句查看系统所支持的引擎类型,结果如下:
mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec) ERROR:
No query specified mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec) mysql>