python第十二周：MySql
MySql数据库
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- MySQL 是开源的，所以你不需要支付额外的费用。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
- MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统
什么是数据库？
数据库（database）是按照数据结构来组织、存储、管理数据的仓库。每个数据库都有一个或多个不同的API用于创建、访问、管理、搜索和复制所保存的数据。MySQL是关系型数据管理系统(RDBMS)所谓关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS的特点：
#数据以表格的形式出现
#每行为各种记录的名称
#每列记录名称所对应的数据域
#许多的行和列组成一张表单
#若干的表单组成database
RDBMS术语：
#数据库：数据库是一些关联表的集合
#数据表：表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格
#列：一列包含了相同的数据
#冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性
#主键：主键是唯一的。可以使用主键来查询数据
#外键：用于关联两个表
#复合键：将多个列作为一个索引键，一般用于符合索引
#索引：使用索引可快速访问数据库中的特定信息。索引是对数据库表中一列或多列值进行排序的
一种结构。类似书籍的目录
#参照完整性：参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须
满足的完整性约束条件，目的是保证数据的一致性
#表头：每一列的名称
#列：具有相同数据类型类型的数据集合
#行：每一行用来描述某条记录的具体信息
#值：行的具体信息，每个值必须与该列的数据类型相同
#键：键的值在当前列中具有唯一性
MySQL数据库的安装
#第一步：下载
下载地址：https://dev.mysql.com/downloads/mysql/
下载后解压：E:\mysql-5.7.2
#第二步：配置环境变量
变量名：MYSQL_HOME
变量值：E:\mysql-5.7.2
path里添加：%MYSQL_HOME%\bin
#第三步：生成data文件
以管理员身份运行cmd
进入E:\mysql-5.7.2\bin下
执行命令：mysqld --initialize-insecure --user=mysql 在E:\mysql-5.7.2目录下生成data目录
#第四步：启动服务
执行命令：net start mysql 启动MySQL服务，若提示失败，则执行第五步
#第五步：解决启动服务失败
执行命令：mysqld -install 即可（无需配置my.ini文件）
#第六步：启动mysql
登陆mysql：E:\mysql-5.7.2\bin>mysql -u root -p
Enter password: ******(第一次登陆mysql密码为空)
#第七步：查询用户密码
执行命令：mysql> select host,user,authentication_string from mysql.user;
#第八步：设置或修改root用户密码
执行命令：mysql> update mysql.user set authentication_string=password("123456") where user="root"; #password("123456"),此处引号中的内容是密码，自己可以随便设置
mysql> flush privileges; #作用：相当于保存，执行此命令后，设置才生效，若不执行，还是之前的密码不变
#第九步：退出MySQL
执行命令mysql> quit;
管理MySQL的基本命令
#use 数据库的名字; 选择要操作的数据库
#show databases; 列出数据库列表
#show columns from 数据表;或desc 数据表; 显示数据表的属性
#create database testdb charset "utf8";创建一个叫testdb的数据库，且让其支持中未能
#drop databse testdb; 删除数据库testdb
#show index from 数据表; 显示数据表的详细索引信息，包括primary key。
MySQL数据类型
MySQL支持多种类型，大致可以分为：数值、时间、字符串类型
#数值类型
MySQL支持所有标准SQL数值数据类型，包括严格数值数据类型(Integer[Int]、Smallint、Decimal[Dec]、Numeric)，以及近似数值数据类型(Float、Real、Double Precision)
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT、BIGINT。
类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
TINYINT | 1字节 | （-128，127） | （0，255） | 小整数值 |
SMALLINT | 2字节 | （-32768，32767） | （0，65535） | 大整数值 |
MEDIUMINT | 3字节 | （-8388608，8388607） | （0，16777215） | 大整数值 |
INT或INTEGER | 4字节 | （-2147483648，2147483647） | （0，4294967295） | 大整数值 |
BIGINT | 8字节 | （-9233372036854775808，9223372036854775807） | （0，18446744073709551615） | 极大整数值 |
FLOAT | 4字节 | （-3.402823466E+38，1.175494351E-38），0，（1.175494351E-38，3.402823466361E+38） | 0，（1.175494351E-38，3.402823466E+38） | 单精度浮点数值 |
DOUBLE | 8字节对 | （1.7976931348623157E+308，2.2250738585072014E308），0，（2.2250738585072014E-308，1.7976931348623157E+308） | 0，（2.2250738585072014E-308，1.27976931348623157E+308） | 双精度浮点数值 |
DECIMAL | 对DECIMAL(M,D)，若M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 |
#日期和时间类型
每个时间类型有一个有效范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。
类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
DATE | 3字节 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3字节 | -838:59:59/838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1字节 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8字节 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4字节 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合时期和时间值,时间戳 |
#字符串类型
类型 | 大小（字节） | 用途 |
|---|---|---|
CHAR | 0-255 | 定长字符串 |
VARCHAR | 0-65535 | 变长字符串 |
TINYBLOB | 0-255 | 不超过255个字符的二进制字符串 |
TINYTEXT | 0-255 | 短文本字符串 |
BLOB | 0-65535 | 二进制形式的长文本数据 |
TEXT | 0-65535 | 长文本数据 |
MEDIUMBLOB | 0-16777215 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16777215 | 中等长度文本数据 |
LONDBLOB | 0-4294967295 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4294967295 | 极大文本数据 |
*CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
*BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没
有字符集，并且排序和比较基于列值字节的数值值。
*BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
*有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
MySQL基本命令使用
#创建数据表
格式：create table table_name(
column 数据类型 [约束类型],
。。。。。。。。。。。。);
例如：创建一个学校表
*primary key用来定义主键，可以使用auto_increment将主键列定义为自增属性，即不用为这个
字段赋值，其数值自动加1。
*NUll(空值)，即是否允许这个字段为空，若不允许则设置为not null。
*可以使用多个列来定义主键，列间用逗号分隔
#为数据表插入数据
格式：insert into table_name (field1,field2,field3......) values (value1,value2,value3.......)
例如：向学校表中插入数据
注：若字段设置为非空，则必须为这个字段赋值，否则报错
#从数据表中查询数据
格式： column_name,column_name....
from table_name
[where Clause]
[offset M][limit N]
例：从学校表中查询所有所有字段的数据
*查询语句中可以使用多个表，表之间使用逗号分隔，并使用where语句是定查询条件
*select命令可以读取一条或多条记录
*可以使用（*）来代替其他字段，select语句会返回表的所有字段数据
*可以使用where语句来包含任何条件
*可以通过offset指定select语句开始查询的数据偏移量。默认为0
*可以使用limit属性来限定返回的记录条数
例如：从学生表中查询从2号id开始的5条记录
#修改数据表的数据
格式：update table_name set field1=new-value1, filed2=new-value2
[where calues]
例如：将学生表中id=3的学生年龄修改为100岁
#删除数据表中的数据
格式：delete from table_name [where clause]
例如：删除学生表中id=7的记录
#where 子句（条件查询）
格式：select field1，filed2，。。。。 from table_name1，table_name2。。。
[where condition1 [and [or]] condition2。。。。。]
可以使用 and or 来包含多个条件
where语句的操作符列表：
操作符 | 描述 | 例子 |
|---|---|---|
= | 等号，检测两个值是否相等，如果相等返回true | （A=B）返回true |
<>,!= | 不等于，检测两个值是否相等，如果不相等返回true | （A!=B）返回true |
> | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | （A>B）返回true |
< | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | （A<B）返回true |
>= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | （A>=B）返回true |
<= | 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | （A<=B）返回true |
#like 子句（模糊查询）
格式：select field1,field2。。。。。filedn from table_name1,table_name2。。。。
where filed1 like condition1 [and [or]] field2 = 'somevalue'
例如：查询学生表中名字以3结尾的学生的记录
其中%表示任意0个或多个字符
#MySQL排序
格式：select field1,filed2，。。。。 from table_name1,table_name2。。。。
order by filed1,[field2.....] [asc [desc]]
使用asc 或 desc 关键字来设置查询结果时升序还是降序。默认情况下，它时按升序排列的。
例如：将学生表按照年龄大小降序排列
#group by子句（分组）
格式：select column_name,function(column_name) from table_name
where column_name operator value
group by column_name;
例子：将学生表按照名字进行分组，并统计每组的总数
例子：使用with rollup来统计所有分组的总和
#正则表达式
MySQL支持正则表达式的匹配，MySQL中使用REGEXP操作符进行正则表达式的匹配
模式 | 描述 |
|---|---|
^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
$ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
[...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
[^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
* | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
+ | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
{n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
{n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
. | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。 |
例1：查找学生表中name字段以N开头的所有数据
例二：查找学生表中name字段中以1结尾的所有数据
例三：查找学生表中name字段包含1，3的所有数据
例四：查找学生表中name字段以N1结尾的数据
#修改数据表结构
当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL alter命令
*删除字段
alter table table_name drop column_name;
*添加字段
alter table table_name add column_name 数据类型 [约束条件];
*修改字段名称及属性
*修改字段的属性
alter table table_name modify column_name new_数据类型 [约束条件];
*修改字段名称和属性
alter table table_name change column_name new_column_name new_数据类型 [约束条件];
*修改表名
alter table table_name rename to new_table_name;
*添加主键
alter table table_name add primary key(column_name);
*删除主键
alter table table_name drop primay key;
*添加索引
alter table table_name add index indexName(column_name);
*添加唯一索引
alter table table_name add unique indexName(column_name);
#主键和外键
主键能够唯一确定一行记录，外键能够关联两个表。
注：外键表中外键引用的数据必须在主键表中存在；删除表时必须先删除主键表，然后才能删除外
键表，否则报错；以上两点是为了保证数据的一致性。
#MySQL null值处理
为处理当提供的查询条件时null时，MySQL提供了三大运算符
名称 | 描述 |
|---|---|
IS NULL | 当列的值时NULL，返回true |
IS NOT NULL | 当列的值不是NULL时，返回true |
<=> | 比较操作符，当比较的两个值为null时返回true |
在MySQL中，NULL值于任何其他值的比较（即使是NULL）永远返回false
#MySQL联合查询
描述：MySQL UNION操作符用于连接两个以上的select语句的结果组合到一个结果集合中。多个select语句会删除重复的语句。
语法格式：SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
参数：
*ALL：可选，返回所有结果集，包括重复数据
*DISTINCT：可选，删除结果集中的重复数据。默认情况下UNION操作符已经删除了重复数据
例子：查询student表和study_record表中所有id（不重复）
#MySQL连接查询*INNER JOIN（内连接或等值连接）：获取两个表中字段匹配关系的记录
*LEFT JOIN（左连接）：获取左表所有的记录
*RIGHT JOIN（右连接）：于left join相反，用于获取右表所有记录，即使左表没有匹配对应的记
录
查询实例：查询student表和study_record表中stu_id>3且status='Y'的数据
事务
MySQL事务主要用于处理操作量大，复杂度高的数据。简单来说事务就是一篮子的数据库操作语句。
注：#在MySQL中只有使用了innodb数据库引擎的数据库或表才支持事务
#事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执
行
#事务用来管理insert，update，delete语句
一般来说，事务必须满足4个条件（ACID）：Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
解释：1.事务的原子性：一组事务，要么成功，要么撤回
2.稳定性：有非法数据（外键约束之类），事务撤回
3.隔离性：事务独立运行。一个事务处理的结果，影响了其他事务，那么其他事务会撤回。
事务的100%隔离，需要牺牲速度
4.可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性与高速
度不可兼得，innodb_flush_log_at_trx_commit选项决定什么时候把事务保存到日志
里。
语法格式：
begin; #开启一个事务
insert\delete\alter等数据库语句
rollback; #回滚，事务撤回\commit; #提交事务
索引
MySQL索引的建立对于MySQL的高效运行时重要的，索引可以大大提高MySQL的检索速度
索引分为单列索引和组合索引。
单列索引：一个索引只包含一个列，一个表可以有多个单列索引，但不是组合索引。
组合索引：一个索引包含多个列
注：创建索引时，要确保该索引时应用在SQL查询语句的条件（一般是where子句的条件）
索引的弊端：索引会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存以下索引文件。建立索引会占用磁盘空间的索引文件。
创建索引的语法格式：
create index indexName on table_name(column_name(length));
如果时char,varchar类型，length可以小于字段的实际长度；如果是BLOB和TEXT类型，必须指定length
修改表结构时加入索引：
alter table table_name add index[IndexName] (column_name(length))
创建表时直接指定：
create table table_name(
id int not null,
index[indexName] (column_name(length))
);
删除索引：drop index [indexName] on table_name;
alter table table_name drop index indexName;
唯一索引：值必须时唯一的，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引的方式：只需将上述方法中的index换成unique即可。
显示索引信息：show index from table_name;