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;