数据库介绍

为什么要学习数据库？

通常，我们存储数据，直接用本地文件即可。但是，本地文件不利于存放海量数据，也不利于用程序对文件的数据进行查询与管理。那么为了解决这些弊端，才有数据库的出现，那么数据库也是每个程序员必须掌握的技术。

数据库介绍

数据库(database)简称DB，实际上就是一个文件集合，是一个存储数据的仓库，本质就是一个文件系统，数据库是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查等操作。

数据库存储数据特点

  • 持久化存储
  • 读写速度极高
  • 保证数据的有效性
  • 对程序支持非常好，容易扩展

数据库的分类

  • 关系型数据库：可以保存现实生活中的各种关系数据，数据库中存储数据以表为单位；主流关系型数据库：MySQL，Oracle，SQLServer等
  • 非关系型数据库：通常用来解决某些特定的需求，比如高并发访问。主流非关系型数据库：Redis，Mongodb，memacache等

MySQL介绍

MySQL由来

MySQL介绍

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言——结构化查询语言（SQL）进行数据库管理。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

MySQL安装

MySQL直接安装

https://jingyan.baidu.com/article/90895e0fa6e7aa24ec6b0bc3.html

PhPStudy安装

PhPStudy非常适合用来搭建网站，是一个集成开发环境。

  • 注意：安装后，需要启动MySQL服务，才能连接到数据库。

图形界面管理工具

常用工具

SQLyog介绍

SQLyog 是一个快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理数据库，由业界著名的Webyog公司出品。也是一个开源软件。

开源软件（open source software），简称为OSS，公开源代码的软件。因此开源软件具备可以免费使用和公布源代码的特征。

注意：SQLyog只是一个连接数据库的工具，并不是数据库

SQLyog使用

  • 使用SQLyog连接MySQL

  • SQLyog界面

​

  • **创建数据库
    **

    • 第一步

​

    • 第二步

  • 创建表

    • 第一步

    • 第二步

​

  • 为表添加数据

image.png

SQL

SQL介绍

SQL是结构化查询语言，是一种用来操作RDBMS(关系型数据库管理系统)的数据库语言，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作oracle，sql server,mysql等关系型数据库。

SQL语句主要分为

  • DDL语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。
  • DML语句：数据操纵语句，用于添加、删除、更新、和查询数据库记录，并检查数据完整性
  • DCL语句：数据控制语句，用于控制不同数据段直接许可和访问级别的语句。

数据完整性

在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束。

数据类型

常用数据类型

  • 数值类型
  • 日期时间类型
  • 字符串类型

数值类型

整数类型 有符号范围 无符号范围
TINYINT(size) -128~127 0~255
SMALLINT(size) -32768~32767 0~65535
MEDIUMINT(size) -8388608~8388607 0~16777215
INT(size) -2147483648~2147483647 0~4294967295
BIGINT(size) -9223372036854775808~9223372036854775807 0~18446744073709551615
小数类型 描述
FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。(定点数类型)

字符串类型

字符串类型 字节大小 示例
CHAR(size) 0-255 char(3)输入’ab’，实际存储为’ab ‘，输入’abcd’，实际存储为’abc’
VARCHAR(size) 0-65535 varchar(3)输入’ab’，实际存储为’ab’，输入’abcd’，实际存储为’abc’

枚举类型

枚举类型英文为ENUM，对1255个成员的枚举需要1个字节存储；对于25565535个成员，需要2个字节存储。最多允许65535个成员。创建方式：enum(“M”,”F”)；

日期类型

数据类型 描述
DATE() 日期。格式：YYYY-MM-DD注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME() 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIME() 时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR() 2 位或 4 位格式的年。4 位格式：1901 到 2155。2 位格式：70 到 69，表示从 1970 到 2069
TIMESTAMP() 时间戳。格式：YYYY-MM-DD HH:MM:SS注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC

注意

  • decimal表示定点小数，如decimal(5,2)表示共存5位数，小数占2位。不写则默认为decimal(10,0)
  • char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab ‘
  • varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’
  • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
  • 枚举类型不区分大小写

详细数据类型可参考：http://blog.csdn.net/anxpp/article/details/51284106

约束

  • 主键primary key：物理上存储的顺序
  • 非空not null：此字段不允许填写空值
  • 惟一unique：此字段的值不允许重复(身份证号,银行卡号等)
  • 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
  • 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

操作数据库(DDL)

连接数据库

mysql -u用户名 -p

enter password：密码

退出数据库

exit

quit

查看已经创建的数据库

SHOW DATABASES;

注意

  • mysql不严格区分大小写
  • 分号作为结束符，必须写

查看数据库版本

select version();

创建数据库

CREATE DATABASE 数据库名;

CREATE DATABASE 数据库名 charset=utf8;

注意

  • charset：指定编码为utf8

image-20210405214903068

查看创建数据库的命令

SHOW CREATE DATABASE 数据库名;

查看当前使用的数据库

SELECT DATABASE();

使用数据库

USE 数据库名;

删除数据库

DROP DATABASE 数据库名;

注意

  • SQL语句不区分大小写
  • 每行必须以英文状态下分号作为结束
  • 当数据库名称有特殊字符时，使用``包括。如：Logic_edu

操作数据表

查看当前数据库中所有表

SHOW TABLES;

创建表

CREATE TABLE 数据表名(字段 类型 约束[, 字段 类型 约束])

  • 创建表名为mytable2，指定id为主键并且字段name为varchar(30)

查看表的创建语句

SHOW CREATE TABLE 数据表名;

查看表描述信息

DESC 数据表名;

练习

  • 创建 students 表(id、name、age、high、gender、cls_id)

添加表字段

ALTER TABLE 数据表名 ADD 字段 类型;

修改表字段

ALTER TABLE 数据表名 MODIFY 字段 类型; – 不重命名

ALTER TABLE

数据表名

CHANGE

原字段名 新字段名

类型及约束; – 将字段重命名

删除表字段

ALTER TABLE 数据表名 DROP 字段;

操作数据

新增数据

整行插入

INSERT INTO 数据表名 VALUES(值1,值2,值3…);

指定列中插入数据

INSERT INTO tabname (字段1, 字段2,…) VALUES (值1,值2,….);

指定列中插入多条数据

INSERT INTO 数据表名 (字段1, 字段2,…) VALUES (值1,值2,…),(值1,值2,…);

修改数据

UPDATE 数据表名 SET 字段1=新值，字段2=新值 [WHERE 条件];

删除数据

DELETE FROM 数据表名 [WHERE 条件];

查询数据

查询整个表数据

SELECT * FROM 数据表名;

查询指定字段数据

SELECT 字段1,字段2 FROM 数据表名;

查询指定字段数据，并给字段起别名

SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名;

查询指定字段并去重

SELECT DISTINCT 字段1 FROM 数据表名;

where子句

where子句通常结合增删改查使用，用于做筛选的条件。

比如，查询当id=1的数据

1
select * from Student where id=1

不仅如此，经常结合运算符使用。

比较运算符

运算符 描述
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
!=或<> 不等于

练习

查询年龄大于19岁的信息

3.查询姓名不是Amy的数据

逻辑运算符

运算符 描述
a and b 当同时满足a,b时，则为True
a or b 当满足a或b其中一个时，则为True
not a 否定

模糊查询

like关键字用来进行模糊查询，并且结合**%以及_**使用。

  • % 表示任意多个任意字符
  • _ 表示一个任意字符

练习

1.查询名字以a开始的学生信息

image-20210406212350646

2.查询名字含有a的学生信息

3.查询名字仅有3个字符的学生信息

4.查询名字至少有4个字符的学生信息

范围查询

关键字 描述
in 表示在一个非连续的范围内
between…and… 表示在一个连续的范围内

查询id是3-5的男同学信息

6.查询年龄不在18至20之间的学生信息

空判断

关键字 描述
is null 判断空
is not null 判断非空

查询没有填写身高的学生

常用聚合函数

count 总数

  • 求students总人数

  • 求男性的人数

max() 最大值

  • 查询最大的年龄

image-20210406212942064

  • 女性最大的id

min() 最小值

  • 查询最小年龄

sum 求和

  • 查询男生年龄和

avg 平均值

分组与分组之后的筛选

分组

在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组。

select … from students group by 需要分组字段;

练习：

  • 计算男生和女生中的人数

  • 男生最大年龄与女生最大年龄

group_concat(…)

我们通过group_concat(…)查看每组的详细信息

练习：

  • 查询同种性别(分组后)的姓名

分组后的筛选

  • 查询男生女生总数大于2的姓名

排序

order by 字段 默认升序

order by 字段 asc 指定升序

order by 字段 desc 指定降序

练习：

  • 查询年龄在18到21之间的女同学，按照年龄从小到大排序

  • 降序排列ID

限制

limit start,count

  • start 为偏移量，默认起始0
  • count 为条数

注意：

  • limit 不能写数学公式
  • limit只能写在末尾
  • 查询第1到4 降序排列ID

表连接

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回mysql。这时我们就需要使用表连接。

分类：

  • 内连接

  • 外连接

    • 左连接
    • 右连接

内连接

内连接仅选出两张表中互相匹配的记录

select * from 表1 inner join 表2 on 表1.列 = 表2.列;

练习：

  • 显示学生的所有信息，但只显示班级名称

  • 将班级名称显示在第一列
  • 查询 有能够对应班级的学生以及班级信息，按照班级进行排序

  • 当同一个班级时，按照学生的id进行从小到大排序

左连接

查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充

select * from 表1 left join 表2 on 表1.列 = 表2.列;

练习：

  • students表左连接classes表 并查看班级为null的数据

右连接

查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。

select * from 表1 right join 表2 on 表1.列 = 表2.列;

子查询

某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。

  • 查阅身高最高的人的姓名 这种为错误方法，因为姓名显示为第一个人的姓名

  • 需要子查询

自关联

可以简单的理解为自己与自己进行连接查询。

比如：

一张 areas 表里面有省市区，我们需要在通过这一张表查询某省对应的所有市。

1
SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.name="湖南";

外键

外键介绍

MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)**，外键所在的表就是从表(子表)**。

注意：

  • 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

创建表时设置外键约束

语法：

1
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

实现：

  • 班级表

  • 学生表

1
2
3
4
5
6
7
8
9
10
11
create table classes1(
    id int(4) not null primary key,
    name varchar(36)
);
create table student2(
    sid int(4) not null primary key,
    sname varchar(30),
    cid int(4) not null,
    constraint fk foreign key(cid) references classes(id)
);

在创建 student2表时将其 cid 设置为外键。

注意：

  • 从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致。
  • 无法先删除主表

在修改表时添加外键约束

1
alter table student2 add constraint fk foreign key(cid) references classes(id);

注意：

  • 主表需存在时创建从表

删除外键约束

1
ALTER TABLE student DROP FOREIGN KEY fk_cid;

MySQL与Python交互

准备数据

  • 创建数据表
1
2
3
4
5
6
7
8
9
10
-- 创建一个商品 goods 数据表
CREATE TABLE goods(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    NAME VARCHAR(150) NOT NULL,
    cate_name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,3) NOT NULL DEFAULT 0,
    is_show TINYINT NOT NULL DEFAULT 1,
    is_saleoff TINYINT NOT NULL DEFAULT 0
);
  • 插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 插入数据
INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

分表

分表是因为当一张表的数据量比较多时，但是我们只需要查询其中的某个字段数据，就会导致查询效率降低，所以此处我们需要使用到分表。当然后面会细讲。

  • 商品分类表

    • 创建 商品分类 表
1
2
3
4
create table goods_cates(
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);
    • 将 商品表 中的 商品种类 查询并插入到 商品分类表 里
1
insert into goods_cates (name) select cate_name from goods group by cate_name;
    • 将 商品表 中的 商品种类 更改为 商品表分类表 对应的 id
1
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

安装pymysql

在Windows操作系统上安装

Python3:pip install pymysql

Python2:pip install MySQLdb

Ubuntu安装：https://www.jianshu.com/p/d84cdb5e6273

Python操作MySQL步骤

Connection 对象

用于建立与数据库的连接

创建对象：调用 connect() 方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
conn=connect(参数列表)

"""
参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端口，默认是3306
参数database：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8
"""

import pymysql

con = pymysql.connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')

from pymysql import *

conn = connect(host = 'localhost',port=3306,database='python-01',user='root',password = 'root',charset = 'utf8')

对象的方法

  • close()关闭连接
  • commit()提交
  • cursor()返回Cursor对象,用于执行sql语句并获得结果

Cursor对象

  • 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
  • 获取Cursor对象:调用Connection对象的cursor()方法
1
cs1=conn.cursor()

对象的方法

  • close()关闭 先关闭游标,在关闭链接
  • execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
  • fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
  • fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

使用Python连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import pymysql
# 连接数据库
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='970612',db='mytest-01',charset='utf8')

# 获取游标对象
cs = conn.cursor()

#通过游标对象执行 mysql语句
r = cs.execute('select * from students;')
print(r)

#获取数据
print(cs.fetchone())
print(cs.fetchone())

#获取剩余全部(从上方接着获取)
print(cs.fetchall())

#先关闭游标
cs.close()

#关闭连接 
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
from pymysql import *
class MyDb():
    # 2初始化时自动连接数据库
    def __init__(self):
        self.my_conn()
    # 1连接数据库
    def my_conn(self):
        self.conn = connect(host='127.0.0.1',port=3306,user='root',passwd='970612',db='mb',charset='utf8')
    # 获取单条数据
    def get_one(self):
        #创建游标
        cs = self.conn.cursor()
        #执行sql语句
        sql = 'select * from goods;'
        cs.execute(sql)
        #获取结果
        res = cs.fetchone()
        #关闭游标
        cs.close()
        # #关闭连接
        # self.conn.close()
        return res
    def get_all(self):
        # 创建游标
        cs = self.conn.cursor()
        # 执行sql语句
        sql = 'select * from goods;'
        cs.execute(sql)
        # 获取结果
        res = cs.fetchall()
        # 关闭游标
        cs.close()

        return res
    #关闭连接  del 方法 在程序执行完毕后自己触发
    def __del__(self):
        # 此处不用关闭游标
        #cs.close()
        self.conn.close()

def main():
    db = MyDb()
    data = db.get_one()
    print(data)
    data = db.get_one()
    print(data)
    # db.sql_cls()
    data_all = db.get_all()
    print(data_all)
if __name__ == '__main__':
    main()

练习2：

使用面向对象完成商品查询

  • 输入1：查询所有商品
  • 输入2：所有商品种类
  • 输入3：查询所有品牌
  • 输入4：退出
  • 输入5：插入数据 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
"""
使用面向对象完成商品查询
• 输入1：查询所有商品
• 输入2：所有商品种类
• 输入3：查询所有品牌
• 输入4：退出
• 输入5：插入数据
"""
from pymysql import *

class MB():
    def __init__(self):
        self.conn = connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='970612',
            db='mb',
            charset='utf8')
        self.cs = self.conn.cursor()

    @staticmethod
    def pt():
        print("-----mb shop-----")
        print("输入1：查询所有商品")
        print("输入2：所有商品种类")
        print("输入3：查询所有品牌")
        print("输入4：退出")
        print("输入5：添加商品数据")

        num = input('请输入查询代码')
        return num

    def run(self):
        while True:
            num = self.pt()
            if num == '1':
                self.num1()
            elif num == '2':
                self.num2()
            elif num == '3':
                self.num3()
            elif num == '4':
                break
            elif num == '5':
                self.num5()
            else:
                print('输入错误')

    def run1(self,sql):
        self.cs.execute(sql)
        res = self.cs.fetchall()
        for req in res:
            print(req[0])

    def num1(self):
        sql = 'select distinct brand_name from goods'
        self.run1(sql)

    def num2(self):
        sql = 'select name from goods_cates'
        self.run1(sql)

    def num3(self):
        sql = 'select brand_name from goods'
        self.run1(sql)

    def num5(self):
        name = input('请输入商品名')
        sql = f'insert into goods() values("{name}")'
        self.run1(sql)
        self.conn.commit()

    def __del__(self):
        self.cs.close()
        self.conn.close()

def main():
    mb = MB()
    mb.run()

if __name__ == '__main__':
    main()