Python技术交流与分享

第2章 MySQL入门

2.1 MySQL简介

2.1.1数据库泛述
为什么要学习数据库?
这个问题我觉得还是从反面来回答比较好，数据库出故障了，会发生啥呢？
学数据库与我找工作或找到更好工作有关系吗？有，关系还很大哦。当然，如果你去应聘不用电脑的职业除外。否则，很可能产生“一丑遮百俊”。
学了数据库有哪些好处？
其他好处不好说，但如果你学了熟悉数据库，对学习其他技术有非常大得帮助，尤其对学习大数据相关技术如Hive、HBase、SparkSQL、SparkRDD等等更是如此，数据库很多都是相通或相似的，学好一个学其他的就轻松多了。
有哪些数据库？
数据库种类很多，从大得方面来说，可分为关系型数据库和非关系型数据库，如MySQL、SQL Server、Oracle、DB2、Sybase等属于关系型数据库，近些年比较火的HBase、MongoDB、Redis等属于非关系型数据库，从存储方式方面来说，可分为行存储数据库、列存储数据库、键值数据库、NoSQL数据库等，当然各类关系型数据库或非关系型数据库自身都各有一些特点。这里就不展开说了。
如何学习数据库？
这个问题有点仁者见仁智者见智，一百人可能有一百个答案，不过我个人认为，数据库作为一个基础性非常强、使用非常广泛的系统，多花些时间进行一些有系统的学习是非常必要，好的基础将大大提升你的竞争力、拓展你的职业发展空间。
2.1.2　MySQL特点
为何选择MySQL? 它有哪些特点？
MySQL是由原MySQL AB公司自主研发的，现在已经被Sun公司收购，是目前IT行业最流行的开放源代码的数据库管理系统，同时它也是一个支持多线程高并发多用户的关系型数据库管理系统。支持Linux、Windows、MAC等多种操作系统，虽然功能上没有其他的大型数据库Oracle、DB2等那么齐全，但好用、易用、开源、可靠性等特点受到成千上万企业和用户的青睐重要原因。
向大家推荐几个学习MySQL的网站：
MySQL社区:http://www.mysqlpub.com/
MySQL菜鸟教程: http://www.runoob.com/mysql/mysql-tutorial.html
MySQL官网:http://www.mysql.com/
《深入浅出MySQL》唐汉名等著
2.1.3　数据库基础
数据库是一个长期存储在计算机内的、有组织、共享、统一的数据集合。作为关系型数据其关系可理解为数据库表，表是一系列二维数组的集合。
 表定义：表（Table），在关系型数据库中，表是一系列二维数组的集合，由纵向的列和横向的行组成，列又称为字段，行又称为记录。
表样例：stud_info(学生基本信息表)

 表要素有：
关系或表、列，字段行，记录
 数据类型：
字符串数据类型、整数型、日期/时间类型、浮点数据型等
 关键字，主键：
主键（Primary key），唯一标志表的每一条记录，可以定义表中一列或多列为主键， 主键列上不能有重复的值，也不能为空，如stud_info表中代码字段为该表的主键。
关系模式或表结构，格式为：表名称（属性1，属性2，…属性n）
2.1.4　数据库语言
我们一般通过数据库语言与数据库打交道，其中SQL是我们常用的，SQL的含义是结构化查询语言（Structured Query Language）。
SQL语言分类
 数据定义语言（DDL）
如：DROP,CREATE,ALTER等
 数据操作语言（DML）
如：INSERT,UPDATE,DELETE等
 数据查询语言（DQL）
SELECT等
 数据控制语言（DCL）
GRANT,REVOKE,COMMIT,ROLLBAK等。
为进一步理解SQL语句含义，下面以创建一张表的SQL语句为例，表名为t_student,具体SQL语句如下：

这是一个典型的数据定义语言（DDL）,该表共有4个字段，分别为stud_id,stud_name,stud_sex,stud_birthday,其中stud_id为主键。
目前这个表是空表，只有结构没有数据，我们可以用数据操作语句（DML）往表插入记录或数据。

以上记录是否成功插入到表中呢？我们可以通过数据查询语言（DQL）来验证一下：

2.1.5 数据库系统架构

（图1-1 数据库系统架构）

2.1.6　Windows平台下安装配置
MySQL支持多平台，如常见的windows，linux等，这里我们以Windows下安装为主，然后简单说明在Linux平台上的安装。
这里介绍一个针对初学者的Windows版的MySQL安装程序（mysqlSetup.exe，文件下载链接：http://pan.baidu.com/s/1kVHK3eZ）,文件大小为35M左右，版本为V5.0，依赖较少，安装方便，但基础功能都有。
以下为详细的安装步骤：
第1步：点击“mysqlSetup.exe”文件，弹出如下界面

第2步：选择安装类型，选择typical（典型安装）。

第3步：是否注册，选择skip sign-up(不注册)

第4步：开始配置服务器

第5步：在配置类型界面中，选择Detailed configuration（详细配置）

第6步：在服务器类型中，作为初学者，可以选择Developer Machine（作为开发机），占用系统资源较少，但基本功能都有。

第7步：字符集选择界面，第一个为西文编码，第二个是多字节的通用utf8编码，都不是我们通用的编码，这里选择第三个，然后在Character Set那里选择或填入“gbk”，当然也可以用“gb2312”，区别就是gbk的字库容量大，包括了gb2312的所有汉字。

第8步：在数据库用途界面，选择Mutifunctional Database（多功能数据库）。

第9步：进入服务器最多并发连接数界面，作为初学者，不涉及很复杂的业务逻辑，而且并发数也需要很多，可选择第一项OLAP或OLTP，并发连接数（concurrent connection）缺省为15，当然你也可以根据需要进行修改。

第10步：配置网络，使用缺省配置即可。

第11步：在Windows设置界面，记得在勾上“Inclode bin Directory in windows PATH”，这样自动把mysql命令所在目录放在Windows的环境变量Path中，接下来你便可在任何目录下启动mysql。

第12步，确认root用户密码

第13步，显示将执行的内容（无需选择），点击“Execute”

第14步安装结束

第15步登录MyQL系统。
 通过Windows命令行登录
第1步，从开始菜单选择“运行”，打开运行对话框，输入‘cmd’

第2步，按确定，打开DOS窗口。

第3步，在DOS窗口中，可以通过输入命令登录MySQL系统，命令格式为：
mysql -h hostname -u username -p
其中mysql为登录命令，
-h 后的参数为服务器名称或IP地址，如果为本地服务器，可为localhost 或127.0.0.1， -u后的参数为用户名称，还没有创建其他用户时，只有root用户。
-p 后的参数为用户密码。

第4步，退出mysql系统，只要在mysql>后，输入quit 然后回车即可。
 通过MySQL命令行登录
选择“开始”菜单，点击含“MySQL Command Line Client”字样的图标，进入登录界面，然后输入root用户密码即可登录。

这是在登录windows下MySQL系统的几种方式，下章我们将介绍如何登录远程服务器上的MySQL系统。

2.2 维护表结构

在关系型数据库中，表是是数据库中最重要、最基本的操作对象，是数据存储的基本单位，数据是按行存储的（非关系型数据库有些是按列存储的），同时通过表中的主键、外键、索引、非空等约束来保证数据的一致性和完整性。这一章主要介绍数据表的基本操作：如何创建表、查看表的结构、修改数据表、删除数据表等。
创建表的方式，可以命令行的方式，也可以通过客户端（navicat for mysql）或通过该客户端的建模方式创建，然后，把模型同步到数据库即可。
2.2.1　创建表
创建表的过程就是确定数据列的属性、制定数据完整性、一致性等约束的过程。而这些约束主要通过主键、外键、是否可空、索引等约束来实现的。
创建表的语法形式：
CREATE TABLE 表名称
(
字段名称1 数据类型 [列级的约束] [默认值]，
字段名称2 数据类型 [列级的约束] [默认值]，
--------
[表级的约束]
）;
注：
（1）、表的名称不区分大小写，不能以SQL中的一些关键字为表名，如CREATE、ALTER、INSERT等；
（2）、列名间用逗号隔开。
下面以创建一个学生的基本信息表为例，说明如何创建一张表。
表的定义或结构如下：
学生信息（t_stud_info）表结构

注：
（1）、stud_code这个字段为主键，作为主键（PRIMARY KEY）的字段不能为空，为空的字段不能为主键，主键可以建的一个字段上，也可建的两个或两个以上的字段上，称为组合主键；
（2）、非空字段，是指这些字段的值，不能为空（即为null）。
创建表的SQL语句：
CREATE TABLE t_stud_info
(
stud_code varchar(20) NOT NULL,
stud_name varchar(100) NOT NULL,
stud_gend varchar(10) ,#没有说明NULL，说明是NULL
college_name varchar(300) NULL,
PRIMARY KEY (stud_code) #指明构成主键的字段
);#最后是分号
创建表的SQL以写好，如何执行，如何查看表结构？通过实例来说明：
 直接把创建表的SQL语句放在mysql命令行执行：

 通过执行脚本创建表
如果一次要创建几十张表，把创建表的语句直接放在mysql命令行运行就不方便了，此时我们可以把创建这些表的SQL语句保存本地当前目录下，名为t_stud_info.sql的文件中，，具体内容请参看以下system cat t_stud_info.sql部分，然后用source命令在mysql命令下执行这个文件即可。这个source命令有点像shell命令。

除了以上两种方法外，还有其他方法，如通过客户端创建、通过模型来创建等等，这里就不展开来说了。
2.2.2　修改表结构
表创建好以后，有时间我们可能根据新的需求，需要修改字段类型、字段名称、添加字段、新建其它约束如索引、是否可空等。当表中无数据时做这些修改比较方便，如果表已有数据可能就需要慎重，否则可能导致修改失败，此时建议备份原表数据，然后清空数据，再做修改，修改后根据新的规则把数据导入新表中。但添加字段、放大自动长度等与是否有数据无关。
2,2.2.1 修改字段类型
如果发现创建的表的某个字段长度太小，需要放大其长度，该如何修改呢？我们可以使用ALTER TABLE 语句来修改。
【注意】如果是正式环境的数据，记得先备份，后修改，有备无患。
修改表字段类型的语法格式：
ALTER TABLE <表名> MODIFY <字段名><字符类型>;
我们创建一张表test01 (a1 varchar(20),a2 int,a3 date),然后，修改a1字段的数据类型，由varchar（20）改为varchar（60）。具体操作如下：

大家考虑一下，是否可以把INT修改为字符型，或把字符型修改整数型？如果要修改需要满足哪些条件？
除了可以修改字段类型，我们还可以修改表名称、字段名称、字段属性等。
2.2.2.2 修改字段名称
修改字段名称的语句与修改字段类型的不一样，其语法格式为：
ALTER TABLE <表名> CHANGE <旧字段名><新字段名><数据类型>;
现在我们把test01表中a1字段名称改为name，数据类型不变。

2.2.2.3 新增字段
表创建后，根据需要我们可以修改表名、修改表中字段名称、字段类型，当然也可添加字段，而且可以更加指定位置的添加，如果没有指定，缺省是添加到最后。添加字段的语法格式为：
ALTER TABLE <表名> ADD <新增字段名><字段类型> [字段约束条件][FIRST|AFTER 已有字段名];
我们还是以test01表为例，在name字段后，添加一个名为code的字段，数据类型为varchar（20），并且不能为空或not null。

2.2.3 修改表的字符集
表的字符集或缺省字符集也能修改？能，这应该也是MySQL特色之一吧，一般数据库系统字符集与数据库绑定在一起，但MySQL把字符集粒度精确到了表甚至字段。虽然这个功能很强大，但也存在很大风险，特别是表中有数据时，可能导致字符类型不兼容问题，为降低风险，还是这句老话，先备份，后修改。
这里的修改一般指修改表的缺省字符集，常用的字符集有：UTF8、GBK、GB2312、latin1等，其中UTF-8用以解决国际上字符的一种多字节编码，它对英文使用8位（即一个字节），中文使用24为（三个字节）来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，即不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，通用性比UTF8差，不过UTF8占用的数据库比GBK大。
GBK、GB2312等与UTF8不兼容，需要通过Unicode编码才能相互转换：
GBK、GB2312－－Unicode－－UTF8
UTF8－－Unicode－－GBK、GB2312 。
字符集涉及面比较广，如服务器或数据库或表字符集、应用字符集（如连接字符集、文件字符集等）、客户端字符，一般这些环节的字符集需要一致或兼容，尤其对中文而言，否则可能导致乱码。如何解决乱码问题，后面我们也有介绍。
以下是修改表的字符集的一个简单实例：

修改方式或命令还有很多，大家可以借助其强大的help进一步获取其他命令的使用方法，如可以通过help ALTER TABLE查询其他使用方法，这里就不在一一例举了。
2.2.4　插入数据
往表中插入数据，有多种方法，如通过SQL语句、客户端、数据备份工具等。
1、通过SQL命令：insert into table_name (列1，列2，..) Values(‘’,’’,’’,….);
用这种方法需要注意列与值的对应关系；如果不指定列名，则指所有列：
如insert into table_name values(‘’,’’,’’，…)，values（），
2、通过客户端导入；
3、利用工具（load data file或mysqlimport等）借助数据文件来导入数据。
这里我们介绍第1种方法，其它2种后面讲数据备份时将介绍。
往表test01插入一条记录。

这是往插入1条记录，如果想一次往表插入多条记录如何实现呢？我们只要在后面添加个values即可，如同时往表中插入2条记录：
insert into table_name values(‘’,’’,’’，…) ,(‘’,’’,’’，…)
注意记录间用逗号。
具体操作请看下例

2.3 增删改数据

存储数据时用来查询分析用的，所以查询分析数据是平时重要任务，但数据库中数据它不会自动生成，需要我们去维护，当然大部分是系统自动维护，不需要手工去操作，不过维护程序还是需要写的，这章我们介绍如何维护数据库数据。这里我们主要介绍如何新增数据、如何修改数据、如何删除数据等。
2.3.1　插入数据
插入数据语句的语法：
INSERT INTO表名[(列名1，……列名n)] values(值1,…..值n);
这个SQL语句一次往表中插入1条记录，如果一次要插入多条记录是否可以呢？可以，而且很方便，插入多条语句为：
INSERT INTO表名[(列名1，……列名n)] VALUES(值1,…..值n), (值1,…..值n),..;
下面我们还是通过一些实例来进步说明如何操作。

2.3.2　修改数据
修改数据也是很常见的，不过修改数据前，记得备份数据。如何备份数据后面将介绍。
修改数据的一般语法：
UPDATE表名SET列名1=值1，….列名n=值n
[WHERE条件];
以下以修改id为200对应的name为例，假设发现id为200对应的name输错了，不是刘婷，而是刘涛。

2.3.3　删除数据
删除数据时，我们可以选择删除几条，或满足某些条件的记录，当然也可以删除所有记录。在日常工作中，删除数据需要非常谨慎，务必养成一个良好习惯，先备份，后删除。对生产环境数据、或正式环境的数据，不建议物理删除，最好采用逻辑删除的方式（即修改对应记录的状态或有效时间等）。数据都有价值。
用DELETE删除数据的一般语法：
DELETE FROM表名 [WHERE条件];
DELETE 加上条件，就可以有条件删除记录；如果没有条件，将删除所有数据。删除所有数据也可用TRUNCATE命令（这种方式删除数据比较快），其命令格式为：
TRUNCATE [table] 表名;
使用DELETE FROM 表名或TRUNCATE [table] 表名命令删除全部记录时，有一种情况需要注意，如果一个表中有自增字段，这个自增字段将起始值恢复成1.如果你不想这样做的话，可以在DELETE语句中加上永真的WHERE，如WHERE 1或WHERE true。

2.4 查询数据

数据库主要功能是存储数据，但存储数据不是最终目的，存储数据最终目的是为了展示和分析，如何分析展示数据库中数据，数据查询就是重要手段。MySQL提供了功能强大、又非常灵活、非常方便的语句实现这些操作。这一章将介绍使用SELECT语句实现简单查询、子查询、连接查询、分组查询及利用正则表达式查询等。
2.4.1　一般查询语句
最简单的是SELECT [列名]FROM [表名] WHERE [条件] 。然后你可以在后面加上像[LIMIT][ORDER BY][GROUP BY][HAVING]等。
[列名]: 可以多个字段（列间用逗号分隔），也可所以字段（一般用*表示所有字段）
[表名]: 可以是一个表名或视图名，也可以是多表或多视图（表间用逗号分隔）。
[条件]: 为可选项，如果选择该项，将限制行必须满足的查询条件。
[LIMIT]: 后跟[位置偏移量,] 行数（第1行的位置偏移量为0，第2行为1，以此类推。）
[ORDER BY]: 后跟字段，可一个或多个，根据这些字段进行分组。
[GROUP BY]: 后跟可一个或多个字段，根据这些字段进行排序，升序(ASC)或降序（DESC）。
其后也可跟WITH ROLLUP，增加一条合计记录。
[HAVING]: 一般与GROUP BY一起使用，用来显示满足条件的分组记录。

2.4.2　单表查询
单表查询就是从1张表中查询数据，后续将介绍多表查询。为查询表数据我们需要先做些准备工作。
2.4.2.1 准备工作
准备工作包括：1、定义表结构，创建表；2、查看分析数据文件；3.把数据导入到表中。
1.首先我们创建一个存储学生各科成绩的表（stud_score）,表的定义如下：

（表6-1 学生成绩表 stud_score）
字段名称 字段描述 数据类型 主键 外键 非空 自增
stud_code 学生代码 VARCHAR(20) 是 否 是 否
sub_code 学科代码 VARCHAR(20) 是 否 是 否
sub_name 学科名称 VARCHAR(100) 否 否 否 否
sub_tech 学科老师代码 VARCHAR(20) 否 否 否 否
sub_score 学科成绩 SMALLINT(10) 否 否 否 否
stat_date 统计日期 DATE 否 否 否 否
转换为建表的SQL语句为：
DROP TABLE IF EXISTS stud_score;
CREATE TABLE stud_score (
stud_code varchar(20) NOT NULL,
sub_code varchar(20) NOT NULL,
sub_name varchar(100) default NULL,
sub_tech varchar(20) default NULL COMMENT '教师代码',
sub_score smallint(10) default NULL,
stat_date date default NULL,
PRIMARY KEY (stud_code,sub_code)
);
2、创建这个表以后，我们需要把一个包含该表数据的文件（在slave02节点的/tmp目录下，名称为stud_score.csv）导入该表，另该文件第1行为字段名称，需过滤掉。我们先操作，具体语法等我们后续会介绍。
查看该数据文件信息

3.把数据文件导入表中

2.4.2.2 查看指定行数据

2.4.2.3 模糊查询
利用LIKE 关键字可以进行模糊查询。下例查询学科代码以101开头的记录总数。

2.4.2.4 分组查询
如果我们希望按学科来统计学生成绩、班级成绩该如何实现呢？这就涉及到分组统计问题，如果需要按学科统计，可以GROUP BY sub_code;然后取前3名学科，具体实现请看下例：

2.4.3　多表查询
多表查询，需要连接2张或2张以上的表一起查询，连接有多种方式，如内连接（通常缺省连接）、外链接（又分为左连接，右连接）等。多表连接时，建议不宜一下连接很多表，尤其是数据比较大时，可以采用两两连接等方式。
要实现多表连接，还需创建一个存储学生基本信息的表（stud_info）并导入数据.
2.4.3.1 准备工作
创建一个学生基本信息的表（表定义如下），并把数据（一个数据文件）导入到表中。
准备步骤，1.定义并创建表，2，查看分析数据文件，3. 导入数据，并验证导入结果。
1.定义并创建表

2.查看分析数据文件

3.导入数据并验证结果

2.4.3.2 多表连接查询
两表内连接、左连接，右连接的含义及使用关键字请参看下图：

（图6-1 多表连接示意图）

以下通过几个实例进一步说明多表连接的使用方法：

2.4.3.3 子连接查询
这里介绍以用IN关键字实现子链接，基本格式为：
SELECT * FROM 表名WHERE 字段 IN (SELECT 字段 FROM 表名 WHERE 条件)；
使用子查询，比较灵活，且有利于把大表关联转换为小表关联。下例为表stud_info中
stud_code从表stud_score中成绩为大于或等于90分的子查询中获取。

2.4.3.4 视图查询

上面的查询语句，有的比较简单，有的比较复杂，像对那些复杂的查询语句，包含了很多信息量，而且有可能还要经常使用，但命令行是无法保证这些语句的，如果下次还要使用是否又重新写一遍呢？大可不必，我们可以把这个查询语句以视图的形式保存到数据库中，然后直接查询这个视图即可。
如上面内连接的SQL语句：SELECT a.stud_name,b.sub_name,b.sub_score FROM stud_info a,stud_score b WHERE a.stud_code=b.stud_code LIMIT 3;
我们可以把它定义为一个视图V_STUD，然后查询视图即可,非常方便！而且视图会保存到数据库中，但视图本身不保存数据。具体实现请看下例：

2.5课后练习

一、在testdb库上创建表，表结构如下：

写一个sql文件，功能包括：
1、创建以上表结构，表名为：test_学号；
2、往表test_序号插入至少3条数据；
3、在表最后位置添加一个字段，字段要求如下：

二、创建表并把数据导入表中，然后查询相关数据，把这些操作放到一个SQL文件中，具体要求如下：
1、创建两表，表名分别为，stud_score_学号：stud_info_学号，表结构请参考第6.2.1节和6.3.1节
2、导入数据，把/tmp目录下stud_info.csv,stud_score.csv分别导入到表stud_info_学号，stud_score_学号中
3、查询数据，查询表stud_info_学号中，把姓刘的同学过滤出来。
三、多表查询，在一个SQL语句实现以下功能
1、内连接，实现stud_score_学号：stud_info_学号的内连接，连接字段为stud_code
2、左连接, 实现stud_score_学号：stud_info_学号的左连接，连接字段为stud_code
四、聚合查询
1、把选了大于5门课的同学的名称及各科总成绩打印出来。
五、写一个存储过程，实现功能，输入学院代码，输出，学生名称、学院名称、各科平均成绩，如下图：