数据库简介
数据库
数据库就是一种特殊的文件, 其中存储着需要的数据;
关系型数据库核心元素:
数据行(记录)
数据列(字段)
数据表(数据行的集合)
数据库(数据表的集合)
Ubuntu 进入数据库:
- sudo -s;
- cd /var/lib/MySQL;
- ls -h;
4.cd 数据库名;
5.ls
一个表中:
一列 --->一个字段
一行 --->一条记录
字段和记录组成表;
几个表组成一个数据库;
一个大的系统可能有几个数据库, 而几个数据库里面有很多表;
- RDBMS
- Relational Database Management System
通过表来表示关系型
当前主要使用两种类型的数据库: 关系型数据库, 非关系型数据库, 本部分主要讨论关系型数据库, 对于非关系型数据库会在后面学习;
所谓的关系型数据库 RDBMS, 是建立在关系模型基础上的数据库, 借助于集合代数等数学概念和方法来处理数据库中的数据;
查看数据库排名: https://db-engines.com/en/ranking
关系型数据库的主要产品:
Oracle: 在以前的大型项目中使用, 银行, 电信等项目
MySQL:web 时代使用最广泛的关系型数据库
ms sql server: 在微软的项目中使用
SQLite: 轻量级数据库, 主要应用在移动平台
Python 中常用的数据库:
MySQL, 关系型数据库, 一般用来做网站,
Redis 一般用来做缓存,
MongoDB, 非关系型数据库, 一般做爬虫, 用来存储非关系型数据;
RDBMS 和数据库的关系
因为 RDBMS 和数据库的关系, 所以我们只需要关心在数据库客户端通过 SQL 语句操作数据库服务端的数据库表即可;
- SQL
- Structured Query Language
SQL 是结构化查询语言, 是一种用来操作 RDBMS 的数据库语言, 当前关系型数据库都支持使用 SQL 语言进行操作, 也就是说可以通过 SQL 操作 oracle,sql server,MySQL,SQLite 等等所有的关系型的数据库.
SQL 语句主要分为:
DQL: 数据查询语言, 用于对数据进行查询, 如 select
DML: 数据操作语言, 对数据进行增加, 修改, 删除, 如 insert,udpate,delete
TPL: 事务处理语言, 对事务进行处理, 包括 begin transaction,commit,rollback
DCL: 数据控制语言, 进行授权与权限回收, 如 grant,revoke
DDL: 数据定义语言, 进行数据库, 表的管理等, 如 create,drop
CCL: 指针控制语言, 通过控制指针完成表的操作, 如 declare cursor
对于 Web 程序员来讲, 重点是数据的 crud(增删改查), 必须熟练编写 DQL,DML, 能够编写 DDL 完成数据库, 表的操作, 其它语言如 TPL,DCL,CCL 了解即可;
SQL 是一门特殊的语言, 专门用来操作关系数据库;
不区分大小写;
学习要求
熟练掌握数据增删改查相关的 SQL 语句编写
在 Python 代码中操作数据就是通过 SQL 语句来操作数据
- # 创建 Connection 连接
- conn = connect(host='localhost', port=3306, user='root', password='mysql', database='python1', charset='utf8')
- # 得 Cursor 对象
- cs = conn.cursor()
- # 更新
- # sql = 'update students set name=" 刘邦 "where id=6'
- # 删除
- # sql = 'delete from students where id=6'
- # 执行 select 语句, 并返回受影响的行数: 查询一条学生数据
- sql = 'select id,name from students where id = 7'
- # sql = 'SELECT id,name FROM students WHERE id = 7'
- count=cs.execute(sql)
- # 打印受影响的行数
- print(count)
MySQL 简介
MySQL 官方网站: http://www.mysql.com
MySQL 是一个关系型数据库管理系统, 由瑞典 MySQL AB 公司开发, 后来被 Sun 公司收购, Sun 公司后来又被 Oracle 公司收购, 目前属于 Oracle 旗下产品
特点
使用 C 和 C++ 编写, 并使用了多种编译器进行测试, 保证源代码的可移植性
支持多种操作系统, 如 Linux,Windows,AIX,FreeBSD,HP-UX,MacOS,NovellNetware,OpenBSD,OS/2 Wrap,Solaris 等
为多种编程语言提供了 API, 如 C,C++,Python,Java,Perl,PHP,Eiffel,Ruby 等
支持多线程, 充分利用 CPU 资源
优化的 SQL 查询算法, 有效地提高查询速度
提供多语言支持, 常见的编码如 GB2312,BIG5,UTF8
提供 TCP/IP,ODBC 和 JDBC 等多种数据库连接途径
提供用于管理, 检查, 优化数据库操作的管理工具
大型的数据库. 可以处理拥有上千万条记录的大型数据库
支持多种存储引擎
MySQL 软件采用了双授权政策, 它分为社区版和商业版, 由于其体积小, 速度快, 总体拥有成本低, 尤其是开放源码这一特点, 一般中小型网站的开发都选择 MySQL 作为网站数据库
MySQL 使用标准的 SQL 数据语言形式
MySQL 是可以定制的, 采用了 GPL 协议, 你可以修改源码来开发自己的 MySQL 系统
在线 DDL 更改功能
复制全局事务标识
复制无崩溃从机
复制多线程从机
开源 免费 不要钱 使用范围广, 跨平台支持性好, 提供了多种语言调用的 API;
是学习数据库开发的首选;
MySQL 安装
服务器端安装
安装服务器端: 在终端中输入如下命令, 回车后, 然后按照提示输入
sudo apt-get install MySQL-server
服务器用于接收客户端的请求, 执行 sql 语句, 管理数据库
服务器端一般以服务方式管理, 名称为 MySQL
启动服务
sudo service MySQL start
查看进程中是否存在 MySQL 服务
ps ajx|grep MySQL
停止服务
sudo service MySQL stop
重启服务
sudo service MySQL restart
配置
配置文件目录为 / etc/MySQL/MySQL.cnf
进入 conf.d 目录, 打开 MySQL.cnf, 发现并没有配置
进入 MySQL.conf.d 目录, 打开 MySQL.cnf, 可以看到配置项
主要配置项如下
bind-address 表示服务器绑定的 ip, 默认为 127.0.0.1
port 表示端口, 默认为 3306
datadir 表示数据库目录, 默认为 / var/lib/MySQL
general_log_file 表示普通日志, 默认为 / var/log/MySQL/MySQL.log
log_error 表示错误日志, 默认为 / var/log/MySQL/error.log
客户端安装
客户端为开发人员与 dba 使用, 通过 socket 方式与服务端通信, 常用的有 navicat, 命令行 MySQL
图形化界面客户端 navicat
可以到 Navicat 官网 https://www.navicat.com.cn/ 下载
将压缩文件拷贝到 Ubuntu 虚拟机中, 放到桌面上, 解压
tar zxvf navicat112_mysql_cs_x64.tar.gz
进入解压的目录, 运行如下命令
./start_navicat
点两次取消后, 点击 "试用" 按钮;
问题一: 中文乱码
解决: 打开 start_navicat 文件
将 export LANG="en_US.UTF-8" 改为 export LANG="zh_CN.UTF-8"
问题二: 试用期
解决: 删除用户目录下的. navicat64 目录
- cd ~
- rm -r .navicat64
命令行客户端
在终端运行如下命令, 按提示填写信息
sudo apt-get install MySQL-client
详细连接的命令可以查看帮助文档
MySQL --help
最基本的连接命令如下, 输入后回车
- MySQL -uroot -proot
- # u 后面的是用户名, p 后面的是密码;
连接成功.
按 ctrl+d 或输入如下命令退出
quit 或者 exit
数据完整性
一个数据库就是一个完整的业务单元, 可以包含多张表, 数据被存储在表中
在表中为了更加准确的存储数据, 保证数据的正确有效, 可以在创建表的时候, 为表添加一些强制性的验证, 包括数据字段的类型, 约束
数据类型
可以通过查看帮助文档查阅所有支持的数据类型
使用数据类型的原则是: 够用就行, 尽量使用取值范围小的, 而不用大的, 这样可以更多的节省存储空间
常用数据类型如下:
整数: int,bit
小数: decimal
字符串: varchar,char
日期时间: date, time, datetime
枚举类型(enum)
特别说明的类型如下:
decimal 表示浮点数, 如 decimal(5,2)表示共存 5 位数, 小数占 2 位
char 表示固定长度的字符串, 如 char(3), 如果填充'ab'时会补一个空格为'ab'
varchar 表示可变长度的字符串, 如 varchar(3), 填充'ab'时就会存储'ab'
字符串 text 表示存储大文本, 当字符大于 4000 时推荐使用
对于图片, 音频, 视频等文件, 不存储在数据库中, 而是上传到某个服务器上, 然后在表中存储这个文件的保存路径
更全的数据类型可以参考 http://blog.csdn.net/anxpp/article/details/51284106
约束
主键 primary key: 物理上存储的顺序
非空 not null: 此字段不允许填写空值
惟一 unique: 此字段的值不允许重复
默认 default: 当不填写此值时会使用默认值, 如果填写时以填写为准
外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常
说明: 虽然外键约束可以保证数据的有效性, 但是在进行数据的 crud(增加, 修改, 删除, 查询)时, 都会降低数据库的性能, 所以不推荐使用, 那么数据的有效性怎么保证呢? 答: 可以在逻辑层进行控制
数值类型(常用)
类型 | 字节大小 | 有符号范围 (Signed) | 无符号范围 (Unsigned) |
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 |
字符串
类型 & nbsp; | 字节大小 | 示例 |
CHAR | 0-255 | 类型: char(3) 输入'ab', 实际存储为'ab', 输入'abcd' 实际存储为'abc' |
VARCHAR | 0-255 | 类型: varchar(3) 输'ab', 实际存储为'ab', 输入'abcd', 实际存储为'abc' |
TEXT | 0-65535 | 大文本 |
日期时间类型
类型 | 字节大小 | 示例 |
DATE | 4 | '2020-01-01' |
TIME | 3 | '12:29:59' |
DATETIME | 8 | '2020-01-01 12:29:59' |
YEAR | 1 | '2017' |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC |
命令行脚本
数据库的操作
sql 语句最后需要有分号; 结尾
链接数据库
- MySQL -uroot -p
- MySQL -uroot -pmysql
退出数据库
exit/quit/ctrl+d
查看所有数据库
show databases;
创建数据库
create database 数据库名 charset=utf8;
示例:
- create database python04;
- create database python05 charset=utf8;
使用数据库
use 数据库的名字
示例:
use python05;
删除数据库
drop database 数据库名;
示例:
drop database python04;
查看创建数据库的语句
show crate database databasename;
示例:
show create database python04;
查看当前使用的数据库
select database();
显示数据库版本
select version();
显示时间
select now();
数据表的操作
查看当前数据库中所有表
show tables;
创建表
auto_increment 表示自动增长
not null 表示不能为空
primary key 表示主键
default 默认值
create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
示例:
- create table xxxxx(id int, name varchar(30));
- create table yyyyy(id int primary key not null auto_increment, name varchar(30));
- create table zzzzz(
- id int primary key not null auto_increment,
- name varchar(30)
- );
创建 students 表(id,name,age,high,gender,cls_id)
- create table students(
- id int unsigned not null auto_increment primary key,
- name varchar(30),
- age tinyint unsigned default 0,
- high decimal(5,2),
- gender enum("男", "女", "中性", "保密") default "保密",
- cls_id int unsigned default 0
- );
创建 classes 表(id,name)
- create table classes(
- id int unsigned not null auto_increment primary key,
- name varchar(30)
- );
修改表 - 添加字段
alter table 表名 add 列名 类型;
例:
alter table students add birthday datetime;
修改表 - 修改字段: 重命名版
alter table 表名 change 原名 新名 类型及约束;
例:
alter table students change birthday birth datetime not null;
修改表 - 修改字段: 不重命名版
alter table 表名 modify 列名 类型及约束;
例:
alter table students modify birth date not null;
修改表 - 删除字段
alter table 表名 drop 列名;
例:
alter table students drop birthday;
删除表
drop table 表名;
例:
drop table students;
查看表结构
desc 表名;
查看表的创建语句
show create table 表名;
例:
show create table classes;
增删改查(curd)
curd 的解释: 代表创建 (Create), 更新(Update), 读取(Retrieve) 和删除(Delete)
查询基本使用
查询所有列
select * from 表名;
例:
select * from classes;
查询指定列
可以使用 as 为列或表指定别名
select 列 1, 列 2,... from 表名;
例:
select id,name from classes;
增加
格式:
INSERT [INTO] tb_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
说明: 主键列是自动增长, 但是在全列插入时需要占位, 通常使用 0 或者 default 或者 null 来占位, 插入成功后以实际数据为准
全列插入: 值的顺序与表中字段的顺序对应
insert into 表名 values(...)
例:
insert into students values(0,'郭靖',1,'蒙古','2016-1-2');
部分列插入: 值的顺序与给出的列顺序对应
insert into 表名(列 1,...) values(值 1,...)
例:
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');
上面的语句一次可以向表中插入一行数据, 还可以一次性插入多行数据, 这样可以减少与数据库的通信
全列多行插入: 值的顺序与给出的列顺序对应
insert into 表名 values(...),(...)...;
例:
insert into classes values(0,'python1'),(0,'python2');
insert into 表名(列 1,...) values(值 1,...),(值 1,...)...;
例:
insert into students(name) values('杨康'),('杨过'),('小龙女');
修改
格式:
UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]...[where 条件判断]
update 表名 set 列 1 = 值 1, 列 2 = 值 2... where 条件
例:
update students set gender=0,hometown='北京' where id=5;
删除
DELETE FROM tbname [where 条件判断]
delete from 表名 where 条件
例:
delete from students where id=5;
逻辑删除, 本质就是修改操作
update students set isdelete=1 where id=1;
增删改查示例
- 增删改查(curd)
-- 增加
-- 全列插入
-- insert [into] 表名 values(...)
-- 主键字段 可以用 0 null default 来占位
-- 向 classes 表中插入 一个班级
- insert into classes values(0, "菜鸟班");
- +--------+-------------------------------------+------+-----+------------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+-------------------------------------+------+-----+------------+----------------+
- | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
- | name | varchar(30) | YES | | NULL | |
- | age | tinyint(3) unsigned | YES | | 0 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
- | cls_id | int(10) unsigned | YES | | NULL | |
- | birth | date | YES | | 2000-01-01 | |
- +--------+-------------------------------------+------+-----+------------+----------------+
-- 向 students 表插入 一个学生信息
- insert into students values(0, "小李飞刀", 20, "女", 1, "1990-01-01");
- insert into students values(null, "小李飞刀", 20, "女", 1, "1990-01-01");
- insert into students values(default, "小李飞刀", 20, "女", 1, "1990-01-01");
-- 失败
-- insert into students values(default, "小李飞刀", 20, "第 4 性别", 1, "1990-02-01");
-- 枚举中 的 下标从 1 开始 1---"男" 2--->"女"....
insert into students values(default, "小李飞刀", 20, 1, 1, "1990-02-01");
-- 部分插入
-- insert into 表名(列 1,...) values(值 1,...)
insert into students (name, gender) values ("小乔", 2);
-- 多行插入
- insert into students (name, gender) values ("大乔", 2),("貂蝉", 2);
- insert into students values(default, "西施", 20, "女", 1, "1990-01-01"), (default, "王昭君", 20, "女", 1, "1990-01-01");
-- 修改
-- update 表名 set 列 1 = 值 1, 列 2 = 值 2... where 条件;
update students set gender=1; -- 全部都改
update students set gender=1 where name="小李飞刀"; -- 只要 name 是小李飞刀的 全部的修改
update students set gender=1 where id=3; -- 只要 id 为 3 的 进行修改
update students set age=22, gender=1 where id=3; -- 只要 id 为 3 的 进行修改
-- 查询基本使用
-- 查询所有列
-- select * from 表名;
select * from students;
--- 定条件查询
select * from students where name="小李飞刀"; -- 查询 name 为小李飞刀的所有信息
select * from students where id>3; -- 查询 name 为小李飞刀的所有信息
-- 查询指定列
-- select 列 1, 列 2,... from 表名;
select name,gender from students;
-- 可以使用 as 为列或表指定别名
-- select 字段[as 别名] , 字段[as 别名] from 数据表 where ....;
select name as 姓名, gender as 性别 from students;
-- 字段的顺序
select id as 序号, gender as 性别, name as 姓名 from students;
-- 删除
-- 物理删除
-- delete from 表名 where 条件
delete from students; -- 整个数据表中的所数据全部删除
delete from students where name="小李飞刀";
-- 逻辑删除
-- 用一个字段来表示 这条信息是否已经不能再使用了
-- 给 students 表添加一个 is_delete 字段 bit 类型
- alter table students add is_delete bit default 0;
- update students set is_delete=1 where id=6;
增删改查示例
来源: http://www.linuxidc.com/Linux/2019-07/159233.htm