23-MySQL数据库

MySQL数据库

内容

SQL简介
MySQL 安装
MySQL基本语句
MySQL与Python交互
MySQL高级

1. SQL简介

当前主要使用两种类型的数据库：关系型数据库、非关系型数据库.
所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

RDBMS和数据库的关系

在这里插入图片描述

SQL(Structured Query Language)

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

SQL语句主要分为：
- 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 是一门特殊的语言,专门用来操作关系数据库
不区分大小写

2. MySQL安装

Ubuntu-Linux安装MySQL

安装服务器端：在终端中输入如下命令，回车后，然后按照提示输入
```
 sudo apt-get install mysql-server
```
当前使用的ubuntu镜像中已经安装好了mysql服务器端，无需再安装，并且设置成了开机自启动
服务器用于接收客户端的请求、执行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官网下载
将压缩文件拷贝到ubuntu虚拟机中，放到桌面上，解压
```
   tar zxvf navicat112_mysql_cs_x64.tar.gz
```
进入解压的目录，运行如下命令
```
  ./start_navicat
```

命令行客户端

在终端运行如下命令，按提示填写信息
```
  sudo apt-get install mysql-client
```
当前使用的ubuntu镜像中已经安装好了mysql客户端，无需再安装
详细连接的命令可以查看帮助文档
```
  mysql --help
```
最基本的连接命令如下，输入后回车
```
  mysql -u root -pmysql
```
按ctrl+d或输入如下命令退出
```
  quit 或者 exit
```

3. MySQL基本使用

数据库设计

关系型数据库建议在E-R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步
在开发中有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系
设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成

三范式

经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式(Normal Form)
目前有迹可寻的共有8种范式，一般需要遵守3范式即可
◆ 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
- 考虑这样一个表：【联系人】（姓名，性别，电话）如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。
◆ 第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
- 考虑一个订单明细表：【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
- 可以把【OrderDetail】表拆分为【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。
◆ 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
- 考虑一个订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。通过拆分【Order】为【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）从而达到 3NF。 *第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

数据类型

可以通过查看帮助文档查阅所有支持的数据类型
使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
常用数据类型如下：
- 整数：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时推荐使用
- 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

约束

主键primary key：物理上存储的顺序
非空not null：此字段不允许填写空值
惟一unique：此字段的值不允许重复
默认default：当不填写此值时会使用默认值，如果填写时以填写为准
外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

数据库的操作

连接数据库
mysql -uroot -p
退出数据库
quit/exit/ctrl+d
sql语句最后需要分号；结尾
显示数据库版本
select version();
查询时间
select now();
查询有哪些数据库
show databases;
新建数据库
create database 数据库名 charset=utf8;
create database jingdong;
create database jingdong charset=utf8;
查看创建数据库的语句
show create database…;
show create database jingdong;
查看当前使用的数据库
select database();
使用数据库
use 数据库的名字
use jingdong;
删除数据库
drop database 数据库名；
drop database jingdong;

数据表的操作

desc 数据表的名字；
查看当前数据库中所有的表
show tables；
创建表
auto_increment表示自动增长
not null 表示不能为空
primary key 表示主键
default 默认值
create table 数据库名字 (字段类型约束[，字段类型约束]);
创建classes表(id,name)
create table xxxxx(id int, name varchar(30));
create table xxxxx(
id int primary key not null auto_increment,
name varchar(30)
);
desc 数据表的名字；
desc xxxxx;
创建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,
high decimal(5,2),
gender enum(“男”，“女”，“保密”) default “保密”,
cls_id int unsigned
);
- 查入表数据
  insert into students values(0, “老王”，18，188.66，“男”，0)；
查看表数据
select * from students;
查看表的创建语句
show create table 表名字；
show create table shopp;
只要修改表前面全是 alter table
修改表——添加字段
alter table 表名 add 列名类型；
alter table shopp add birthday datatime;
修改表——修改字段：不重命名版
alter table 表名 modify 列名类型及约束
alter table shopp add birthday data；
修改表——修改字段：重命名版
alter table 表名 change 原名新名类型及约束；
alter table shopp change birthday birth data default “2000-01-01”;
修改表——删除字段
alter table 表名 drop 列名；
alter table shopp drop high;
删除表
drop table 表名；
drop database 数据库;
drop table xxxxxx;

增删该查(curd)

增加
全列插入
insert [into] 表名 values(…)
主键字段可以用 0 null default 来占位
向 classes 表中插入一个班级
insert into calsses values（0，“菜鸟班”）；
向students表插入一个学生信息
insert into students values （0，“小李飞刀”，20，“女”，1，“2000-01-02”）；
insert into students values （null，“小李飞刀”，20，“女”，1，“2000-01-02”）；
insert into students values （default，“小李飞刀”，20，“女”，1，“2000-01-02”）；
失败
insert into students values(default，“小李飞刀", 20，“第四性别”，1，“1990-02-01”)；
部份插入
insert into 表名(列1，…) values（值1，…）
insert into students(name,gender) values (“小乔”，2)；
多行插入
insert into students (name，gender) vlues（“大乔”，2),("貂蝉”，2)；
insert into students values(default, “西施”，20，“女”，1，“1990-01-01”)，(default，“王昭君”，20，“女”，1，“1990-02-02”)；
修改
update 表名 set 列1= 值1，列2 = 值2 … where 条件；
update students set gender=2； – 全部都该
update students set gender=“女” where id=3；-- id为3进行修改
删除
物理删除
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；
查询基本使用
查询所有系列
select * from 表名；
select * from students;
指定条件查询
select * from stduents 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 gender as 性别，name as 姓名 from students；

4. MySQL与Python交互

Python 中操作 MySQL 步骤

在这里插入图片描述

引入模块

在py文件中引入pymysql模块
```
  from pymysql import *
```

Connection 对象

用于建立与数据库的连接
创建对象：调用connect()方法
```
  conn=connect(参数列表)
```
参数host：连接的mysql主机，如果本机是’localhost’
参数port：连接的mysql主机的端口，默认是3306
参数database：数据库的名称
参数user：连接的用户名参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8

对象的方法

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

Cursor对象

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

对象的方法

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

对象的属性

rowcount只读属性，表示最近一次execute()执行后受影响的行数
connection获得当前连接对象

参数化

sql语句的参数化，可以有效防止sql注入

注意：此处不同于python的字符串格式化，全部使用%s占位

  from pymysql import *
  
  def main():
  
      find_name = input("请输入物品名称：")
  
      # 创建Connection连接
      conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
      # 获得Cursor对象
      cs1 = conn.cursor()
  
  
      # # 非安全的方式
      # # 输入 " or 1=1 or "   (双引号也要输入)
      # sql = 'select * from goods where name="%s"' % find_name
      # print("""sql===>%s<====""" % sql)
      # # 执行select语句，并返回受影响的行数：查询所有数据
      # count = cs1.execute(sql)
  
      # 安全的方式
      # 构造参数列表
      params = [find_name]
      # 执行select语句，并返回受影响的行数：查询所有数据
      count = cs1.execute('select * from goods where name=%s', params)
      # 注意：
      # 如果要是有多个参数，需要进行参数化
      # 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可 
  
      # 打印受影响的行数
      print(count)
      # 获取查询的结果
      # result = cs1.fetchone()
      result = cs1.fetchall()
      # 打印查询的结果
      print(result)
      # 关闭Cursor对象
      cs1.close()
      # 关闭Connection对象
      conn.close()
  
  if __name__ == '__main__':
      main()

5.MySQL高级

视图

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

定义视图
建议以v_开头
create view 视图名称 as select语句;
查看视图
查看表会将所有的视图也列出来
show tables;
使用视图
视图的用途就是查询
select * from v_stu_score;
删除视图
drop view 视图名称;
例：
drop view v_stu_sco;
视图的作用
1. 提高了重用性，就像一个函数
2. 对数据库重构，却不影响程序的运行
3. 提高了安全性能，可以对不同的用户
4. 让数据更加清晰

事务

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务四大特性(简称ACID)

原子性(Atomicity)
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
一致性(Consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
隔离性(Isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
持久性(Durability)
一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

查看表的创建语句，可以看到engine=innodb

  -- 选择数据库
  use jing_dong;
  -- 查看goods表
  show create table goods;

开启事务，命令如下：

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
```
  begin;
  或者
  start transaction;
```

提交事务，命令如下

将缓存中的数据变更维护到物理表中
```
  commit;
```

回滚事务，命令如下：

放弃缓存中变更的数据
```
   rollback;
```

注意

修改数据的命令会自动的触发事务，包括insert、update、delete
而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

在这里插入图片描述

索引的使用

查看索引
```
  show index from 表名;
```
创建索引
- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
- 字段类型如果不是字符串，可以不填写长度部分
```
  create index 索引名称 on 表名(字段名称(长度))
```
删除索引：
```
  drop index 索引名称 on 表名;
```

注意：

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

建立索引会占用磁盘空间

账户管理

查看所有用户
```
  	 desc user;
  	 select host,user,authentication_string from user;
```
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码，为加密后的值
创建账户、授权
grant 权限列表 on 数据库 to ‘用户名’@‘访问主机’ identified by ‘密码’;
- 需要使用实例级账户登录后操作，以root为例
- 常用权限主要包括：create、alter、drop、insert、update、delete、select
- 如果分配所有权限，可以使用all privileges
查看用户有哪些权限
```
  show grants for laowang@localhost;
```

修改密码

  update user set authentication_string=password('新密码') where user='用户名';
  例：
  update user set authentication_string=password('123') where user='laowang';

注意修改完成后需要刷新权限刷新权限：
```
  flush privileges
```
远程登录（危险慎用）
如果向在一个Ubuntu中使用msyql命令远程连接另外一台mysql服务器的话，通过以下方式即可完成，但是此方法仅仅了解就好了，不要在实际生产环境中使用修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
```
  vim /etc/mysql/mysql.conf.d/mysqld.cnf
  在bind-addr = 127.0.0.1前#注释
```

然后重启

	msyqlservice mysql restart

posted @ 2019-11-12 15:25 Kailinymq 阅读(88) 评论(0) 收藏举报

刷新页面返回顶部

Kailinymq

23-MySQL数据库

MySQL数据库

内容

1. SQL简介

RDBMS和数据库的关系

SQL(Structured Query Language)

2. MySQL安装

Ubuntu-Linux安装MySQL

配置

客户端

图形化界面客户端navicat

命令行客户端

3. MySQL基本使用

数据库设计

三范式

数据类型

约束

数据库的操作

数据表的操作

增删该查(curd)

4. MySQL与Python交互

Python 中操作 MySQL 步骤

引入模块

Connection 对象

对象的方法

Cursor对象

对象的方法

对象的属性

参数化

5.MySQL高级

视图

事务

事务四大特性(简称ACID)

事务命令

开启事务，命令如下：

提交事务，命令如下

回滚事务，命令如下：

注意

索引

索引的使用

注意：

账户管理

公告