文章目录
数据库的基本使用
数据库
1. 数据库介绍
数据库就是存储和管理数据的仓库,数据按照一定的格式进行存储,用户可以对数据库中的数据进行增删改查等操作。
2. 数据库分类
- 关系型数据库
- 非关系型数据库
关系型数据库
是指采用了关系模型来组织数据的数据库,简单来说,关系模型指的就是二维表格模型,好比Excel文件中的表格,强调使用表格的方式存储数据。
关系型数据库中核心元素
- 数据行(row,一行数据,一条记录,一个元组)
- 数据列(filed,一列数据,一个字段,一个属性)
- 数据表(table)
- 数据库(数据表的集合)(database)
常用关系型数据库
- Oracle
- Microsoft SQL Server
- MySQL
- SQLite
非关系型数据库
非关系型数据库,又被称为 NoSQL(Not Only SQL)
常用非关系型数据库
- MongoDB
- Redis
数据库作用
数据库的作用就是存储和管理数据
数据库特点
- 持久化存储
- 读写速度极快
- 保证数据的有效性
关系型数据库管理系统
1. 关系型数据库管理系统介绍
数据库管理系统(全称:Relational Database Management System · 简称 RDBMS)是为管理关系型数据库而设计的软件系统
关系型数据库管理系统可以分为:
- 关系型数据库服务端软件
- 关系型数据库客户端软件
关系型数据库服务端软件:
主要负责管理不同的数据库,而每个数据库里面会有一系列的数据文件,数据文件是用来存储数据的。数据库就是一系列数据文件的集合。
关系型数据库客户端软件:
主要负责和关系型数据库服务端软件进行通信,向服务端传输数据或从服务端获取数据。
关系型数据库管理系统说明:
- 用户操作关系型数据库客户端,实现数据库相关操作
- 关系数据库客户端借助网络使用SQL语言和关系型数据库服务端进行数据通信
- 关系型数据库服务端管理者不同的数据库,每个数据据库会有一系列的数据文件,数据都保存在数据文件里,每个数据库可以理解成一个文件夹
2. SQL介绍
SQL(Structured Query Language)是结构化查询语言,是一种用来操作 RDBMS 的数据库语言。
SQL的作用是实现数据库客户端和数据库服务端之间的通信,SQL就是通信的桥梁
SQL语言主要分为:
- DQL:数据查询语言,用于对数据进行查询,如 select
- DML:数据操作语言,对数据进行增加、修改、删除,如 insert、update、delete
- DPL:事务处理语言,对事物进行处理,包括begin、commit、rollback
- DCL:数据控制语言,进行授权与权限回收,如 grant、revoke
- DDL:数据定义语言,进行数据库、表的管理,如 creat、drop
重点是数据库的增删改查,DQL、DML
MySQL数据库
1. MySQL数据库特点
- 是开源的
- 支持大型的数据库
- 使用标准的SQL数据语言形式
- 可以安装在不同的操作系统,并且提供多种编程语言的操作接口
2. MySQL数据库服务端安装
ubuntu:sudo apt-get install mysql-server
查看MySQL服务效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9WzsipU2-1646665063196)(C:\Users\13729\AppData\Roaming\Typora\typora-user-images\image-20220302092901935.png)]
ps:查看当前系统中的进程
-a:表示所有用户
-u:显示用户名
-x:显示所有执行程序
查看MySQL版本信息
mysql --version
查看MySQL服务状态
sudo service mysql status
停止MySQL服务
sudo service mysql stop
启动MySQL服务
sudo service mysql start
重启MySQL服务
sudo service mysql restart
MySQL配置文件介绍
配置文件路径:/etc/mysql/mysql.conf.d/mysql.cnf
主要配置信息:
port:默认3306
bind-address:服务器绑定IP,默认127.0.0.1
datadir:数据库保存路径,默认 /var/lib/mysql
log_error:错误日志,默认 /var/log/mysql/error.log
数据库客户端使用
客户端连接MySQL服务端命令:mysql -u root -p
- -u:表示MySQL服务端用户名
- -p:表示MySQL服务端密码
- quit / exit 表示退出
数据类型和约束
1.数据类型
数据类型是指在创建表的时候为表中字段指定数据类型,只有数据类型符合要求才能存储起来。使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多地节省存储空间。
常用数据类型:
- 整数:int、bit
- 小数:decimal
- 字符串:varchar、char
- 日期类型:date、time、datetime
- 枚举类型:enum
数据类型说明:
- decimal 表示浮点数,如 decimal(5, 2) 表示共存储5位数,小数占两位
- char 表示固定长度的字符串,如 char(3),如果填充 ‘ab’ 时就会补一个空格在ab末尾,3表示字符数
- varchar 表示可变长度的字符串,如varchar(3),填充 ‘ab’ 时就会存储本身,3表示最大字符数
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
- 字符串 text 表示存储大文本,当字符大于 4000 时推荐使用,例如博客
2. 数据约束
约束是指数据在数据类型限定的基础上额外增加的要求
常见的约束如下:
- 主键 primary ket:物理上存储的顺序,MySQL建议所有表的主键字段都叫 id,类型为 int unsigned
- 非空 not null:此字段不允许填写空值
- 唯一 unique:此字段的值不允许重复
- 默认 default:当不填写字段对应的值时会使用默认值
- 外键 foreign key:对关系字段进行约束,党委关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,否则抛出错误异常
3. 数据类型附录表
1. 整数类型
类型 | 字节大小 | 有符号范围(signed) | 无符号范围(undigned) |
---|---|---|---|
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 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
2. 字符串
类型 | 说明 | 使用场景 |
---|---|---|
char | 固定长度,小型数据 | 身份证号、手机号、电话、密码 |
varchar | 可变长度,小型数据 | 姓名、地址、品牌、型号 |
text | 可变长度,字符个数大于 4000 | 存储小型文章或新闻 |
longtext | 可变长度,极大型文本数据 | 存储及大型文本数据 |
3. 时间类型
类型 | 字节大小 | 示例 |
---|---|---|
date | 4 | ’2022-01-01‘ |
time | 3 | ’12:12:59‘ |
datetime | 8 | ‘2022-02-02 12:12:59’ |
year | 1 | ‘2022’ |
timestamp | 4 | ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC |
命令行客户端MySQL的使用
数据库成功登录后查看效果
# 显示当前时间
select now();
# 注释
-- 我是注释
数据库操作的SQL语句
- 查看所有数据库
show databases;
- 查看当前使用的数据库
select database();
- 创建数据库
create database 数据库名 charset=utf8;
# 修改字符集
alter database 数据库名 字符集;
- 查看创建数据库信息
show create database 数据库名;
- 使用数据库
use 数据库名;
- 删除数据库
drop database 数据库名;
表结构操作SQL语句
- 查看当前数据库中所有表
show tables;
- 创建表
create table 表名(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
age tinyint unsigned default 0,
height decimak(5,2),
gender enum('男','女','保密')
);
- 查看创建表信息
show create table 表名;//表格形式
show create table 表名 \G; //键值对形式
- 查看表结构
desc 表名;
- 为数据表添加新字段
alter table 表名 add 字段 类型 约束;
- 修改字段类型
# modify
# 注意:modify只能修改字段类型或者约束,不能修改字段名
alter table 表名 modify 字段 类型 约束;
# change
alter table 表名 change 原名 新名 类型 约束;
- 删除字段
alter table 表名 drop 字段;
- 删除数据表
drop table 表名;
表数据操作的SQL语句
- 查询数据
# 查询所有列
select * from 表名;
# 查询指定列
select 列1,列2,... from 表名;
- 添加数据
# 全列插入:值的顺序与表结构字段的顺序一一对应
insert into 表名 values (...)
# 部分列插入:值的顺序与给出的列顺序对应
insert into 表名 (列1,...) values(值1,...)
# 全列多行插入
insert into 表名 values (...)(...)...;
# 部分列多行插入
insert into 表名(列1,...)... values (值1,...)...;
as 和 distinct 关键字
1. as关键字
在使用SQL语句显示结果的时候,往往在屏幕上显示的字段不具备良好的可读性,此时可以使用 as 给字段起一个别名。
- 使用 as 给字段起别名
select 字段名 as 别名, 字段名 as 别名...;
- 通过 as 给表起别名
-- 如果是单表查询,可以省略表名
select s.id, s.name, s.gender from stu as s;
2. distinct关键字
对查询的数据进行去重
select distinct 字段,... from 表;
where条件查询
1. where条件查询介绍
使用 where条件查询可以对表中的数据进行筛选,条件成立的记录会出现在结果集中。
where语句支持的运算符:
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断
查询格式:
select * from 表名 where 条件;
2. 比较运算符查询
- 等于:=
- 大于:>
- 大于等于:>=
- 小于:<
- 小于等于:<=
- 不等于:!= 或 <>
3. 逻辑运算符查询
- and
- or
- not
4. 模糊查询
- like 是模糊查询关键字
- % 表示任意多个任意字符
- _ 表示一个任意字符
5. 范围查询
- between…and… 表示在一个连续的范围内查询
- in 表示在一个非连续的范围内查询
6. 空判断
- 判断为空:is null
- 判断非空:is not null
注意:
- 不能使用 where height = null 判断为空
- 不能使用 where height != null 判断非空
- null 不等于 ‘’ 空字符串
排序
语法:
select 字段 from 表名 order by 列1 asc / desc [, 列2 asc / desc, ...]
语法说明:
- 先按照列1进行排序,如果列1的值相同时,则按照列2排序,以此类推
- asc升序
- desc降序
- 默认升序排列
分页查询
语法:
select 字段 from 表名 limit start, count
--获取第n页数据
select 字段 from 表名 limit (page - 1) * count, count
MySQL数据库的条件查询
聚合函数
聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据。
常用聚合函数
- count(col):表示求指定列的总行数,不统计空值
- max(col):求指定列的最大值
- min(col):求指定列的最小值
- sum(col):求指定列的和
- avg(col):求指定列的平均值
聚合函数会默认忽略字段为null的记录,要想列值为null的记录也参与计算,必须使用ifnull函数对null值做替换
例:select avg(ifnull(age, 0)) from stu;
这里表示如果字段age为空,则使用0替代null参与计算
count 一般使用 *
分组查询
1、分组查询介绍
分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组。
语法格式:
group by 列名 [having 条件表达式] [with rollup]
说明:
- 列名:是指按照指定字段的值进行分组
- having:条件表达式,用来过滤分组后的数据
- with rollup:在所有记录的最后加上上一条记录,显示select查询时聚合函数的统计和计算结果
2、group by使用
group by 可用于单个字段分组,也可用于多个字段分组
-- 根据单个字段分组
select gender from stu group by gender;
-- 根据多个字段分组
select name, gender from stu group by name, gender
-- 注意,分组字段前后要一样
-- 错误语句
select * from stu group by gender;
3、group by + group_concat()使用
group_concat(字段名):统计每个分组指定字段的信息集合,每个信息之间使用都好进行分割
select gender, group_concat(name) from stu group by gender;
4、groupby + 聚合函数
select gender, avg(age) from stu group by gender;
5、group by + having
having 类似于 where,都是数据过滤,但是 having 只能用于 group by
select gender, count(*) from stu group by gender having count(*) > 2;
6、group by + with rollup
with rollup 是在最后记录后面新增一行,显示select查询时聚合函数的统计和就算结果
select gender, count(*) from stu group by gender with rollup;
连接查询–内连接
1、连接查询介绍
连接查询可以实现多个表的查询,当查询的字段数据来自不同的表就可以使用连接查询来完成。
连接查询可分为:
- 内连接查询
- 左连接查询
- 有连接查询
- 自连接查询
2、内连接查询
查询两个表中符合条件的共有记录(取交集)
语法格式:
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2;
说明:
- inner join 是内连接查询关键字
- on 就是连接查询条件
- 多个字段时可以指定别名,起了别名后无法在使用原先的名字
连接查询–左连接
1、左连接查询介绍
以左表为主根据条件查询右表数据,如果根据条件查询右表数据不存在使用null值填充
语法格式·:
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2;
说明:
- left join 就是左连接查询关键字
- on 连接查询条件
- 表1 是 左表,表2 是右表
连接查询–右查询
1、右连接查询介绍
以右表为主根据条件查询左表数据,如果根据条件查询左表数据不存在使用 null’值 填充
语法格式:
select 字段 from 表1 right join 表2 on 表1.字段 = 表2.字段2;
说明:
- right join 右连接查询关键字
- on 连接查询条件
- 表1 是左表,表2 是右表
连接查询–自连接
自连接查询介绍
左表和右表是同一个表,根据连接查询条件查询两个表中的数据。
子查询
子查询介绍
在一个 select 语句中,嵌入了另外一个 select 语句,那么被嵌的 select 语句称之为子查询语句,外部那个 select 语句则为主查询。
主查询和子查询的关系
- 子查询是嵌入到主查询中
- 子查询是辅助主查询,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
子查询使用
select * from stu where age > (select avg(age) from stu);
MySQL数据库的高级使用
将查询结果插入到其他表中
insert into good(name) select good_name from goods;
使用连接更新表中某个字段数据
update 表名 set 字段=值;
PyMySQL使用
Python程序操作MySQL数据库
安装pymysql第三方包:
sudo pip3 install pymysql
- 安装命令
sudo pip3 install
第三方包名 - 卸载命令
sudo pip3 uninstall
第三方包 pip3 show pymysql
命令查看第三方包信息pip3 list
查看使用 pip 命令安装的第三方包列表
pymysql使用
- 导入 pymysql 包
import pymysql
- 创建连接对象
调用 pymysql模块 中 connect()函数 来创建连接对象
conn=connect(参数列表)
+ host:连接的mysql主机,本机是'localhost'
+ port:连接的mysql主机的端口,默认是 3306
+ user:连接的用户名
+ password:连接的密码
+ database:数据库名称
+ charset:通信采用的编码方式,推荐使用 utf8
连接对象操作说明:
- 关闭连接:conn.close()
- 提交数据:conn.commit()
- 撤销数据:conn.rollback()
3.获取游标对象
获取游标对象的目的就是要执行sql语句,完成对数据库的增、删、改、查的操作。
cur = conn.cursor()
游标操作说明
- 使用游标执行SQL语句:execute(operation [parameters]) 执行SQL语句,返回受影响的行数,主要用于执行insert、update、delete、select等语句
- 获取查询结果集中的一条数据:cur.fetchone()返回一个元组
- 获取查询结果集中所有数据:cur.fetchall()返回一个元组,里面包含许多元组
- 关闭游标:cur.close()
- pymysql完成数据的查询操作