python自动化运维库_Python自动化运维 - day11 - MySQL数据库

数据库概述

常用的数据库种类为关系型数据库和非关系型数据库, 关系型数据库模型是把复杂的数据归结为简单的二元关系(即二维表格形式——)。

在关系数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算实现数据的管理。

常见的数据库有MySQL。

MySQL介绍

MySQL是Web世界中使用最广泛的数据库服务器

访问及管理mysql数据库的最常用标准化语言为SQL结构化查询语句。

安装MySQL

这里仅列出在Linux下的安装：

通过yum进行安装

1、安装MySQL

yum install -y mysql-server (centos 7以下)

yum install -y mariadb-server (centos 7及以上)

2、启动服务

/etc/init.d/mysqld start

3、关闭服务

/etc/init.d/mysqld stop

编译安装

1.安装依赖包

yum install -y ncurses-devel libaio-devel

rpm -qa ncurses-devel libaio-devel

2.安装cmake编译工具

yum install -y cmake

3.上传源码包

rz -y mysql-5.5.49.tar.gz

4.解压并安装

tar xf mysql-5.5.49.tar.gz

cd mysql-5.5.49

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49 \

-DMYSQL_DATADIR=/application/mysql-5.5.49/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.5.49/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENAINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FAST_MUTEXES=1 \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0

make && make install && cd ..

5.创建链接文件

ln -s /application/mysql-5.5.49/ /application/mysql

6.创建数据库用户及配置文件

useradd -M -s /sbin/nologin mysql

7.初始化数据库

/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

8.定义环境变量

cp /application/mysql/bin/* /usr/local/sbin

或者

PATH=/application/mysql/bin:$PATH

9.开启数据库

/application/mysql/bin/mysql start

10.登录数据库

mysql

如果设置了用户密码

mysql -uroot -p123456

11.关闭数据库

/application/mysql/bin/mysql stop

12.设置密码

mysqladmin password 123456 -S /data/3306/mysql.sock

SQL

structured query language,是一种对关系数据库中的数据进行定义和操作的语言方法,是大多数关系数据库管理系统所支持的工业标准。

主要分为以下6类:

数据查询语言(DQL):data query language,也成为数据检索语句，作用是从表格中获取数据，确定数据怎么样在应用程序给出。关键字select是SQL用的最多的动词，其他DQL常用的保留字段有where、order by、group by和having

数据操作语言(DML):data manipulation language,其中包括动词insert、update和delete，他们用于添加、修改、删除表中的行，也称动作查询语句。

数据处理语言(TPL):它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括，begin、transaction、commit和rollback

数据控制语言(DCL):data control languag,它的语句通过grant或revoke 获得许可，确定 单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或revoke控制对单个列的访问。

指针控制语言(CCL):它的语句，像 declare cursor、fetch into、update where current用于对一个或多个表单独执行的操作

数据定义语言(DDL):data definition language,其语句包括create和drop、alter。在数据库中创建新表或删除表(create table 或者 drop table)，为表加入索引等。是动作查询的一部分

小结：

我们日常开发会用到的SQL语言类型为：DML、DQL以及DDL

RDBMS的数据库、表的概念其实就相当于目录，文件，及内容

库相关操作

数据库在文件系统上就是用目录体现的，所以对库的操作，可以理解为对目录的操作。

创建数据库,会在MySQL的data目录下创建同名文件夹

创建数据库

create database db_name;

create database db_name default charset utf8;

--> 语法格式：create database 数据库名称

--> 建议同时指定数据库的字符集

删除数据库

drop database db_name;

--> 语法格式：drop database 数据库名称

--> 删除数据库目录，注意会删除库下的所有表文件

查看及进入数据库

show databases;

use db_name;

--> 查看库的信息：show create database db_name;

表相关操作

表在文件系统上是用文件体现的，所以对表的操作，可以理解为对文件的操作。

创建表,会在对应的库目录下创建表空间文件

创建表

create table user_info(

id int not null auto_increment primary key,

name char(20),

age int,

gender char(1),

deparment_id int,

constraint 约束名称 foreign key(deparment_id) references dep_info(id)

)engine = innodb default charset=utf8;

--> 语法格式：

--> create table 表名(

--> 列名 类型 [是否为空] [是否默认值] [自增] [主键] ,

--> 列名2 类型

--> .... ....

--> [ constraint 外键名称 foreign key(本表的被约束字段) reference 目标表名(字段) ]

--> ) engine = 存储引擎名称 default charset = utf8;

各字段含义：

列名

数据类型

是否可以为空(null/not null)

是否默认值(default value)

是否自增(auto_icrement)：一个表只能存在一个自增列并且必须有索引(普通索引或主键索引),类型必须是数值型。

主键(primarr key)：数据不能为空、不能重复，可以加速查找(数据库的B树结构)

外键(constraint) ：对表内某字段的内容进行约束,必须是某个表的某个字段已有的值,含外键的表可以理解为1对多，注意外键关联的两个字段数据类型要一致

基本数据类型

MySQL的数据类型大致分为：数值、时间 和 字符串。

1 数字：2 整数3 tinyint小整数,数据类型用于保存一些范围的整数数值范围。4 smallint

5 int

6 bigint

7 小数8 float浮点型(长度越长越不精准)9 double浮点型(双精度,精度比float稍高) 范围比float更大10 decimal 精准(内部使用字符串进行存储的) ->适合对精度有要求的11 字符串12 char(19)[字符长度] 定长字符串 --> 占用空间大，但是效率高

13 varchar(19)[字符长度] 不定长字符串 --> 占用空间是可变的，但是效率低

14 注意：最大可以存放255个字符15 text() 65535个字符16 mediumtext() 16777215个字符17 longtext() 4294967254个字符18 二进制：19 TinyBlob20 Blob21 MediumBlob22 LongBlob23 存文件:虽然可以用二进制进行存储,但是一般是存储文件在服务器上的路径(URL)24 时间：25 date YYYY-MM-DD26 time HH:MM:SS27 yearYYYY28 DATETIME YYYY-MM-DD HH:MM:SS -->常用

29 TIMESTAMP 时间戳格式

View Code

创建多表外键关联

一对多：一个表的某个字段的数据来自于另一个表已存在的数据。

多对多：一个表的某几个字段的数据来自于另一个或几个表已存在的数据。

一对多：

create table user_info(

id int not null auto_increment primary key,

name char(20),

age int,

gender char(1),

deparment_id int,

constraint 约束名称 foreign key(deparment_id) references dep_info(id)

)engine = innodb default charset=utf8;

create table dep_info(

id int not null auto_increment primary key,

title char(32),

)engine=innode default charset=utf8;

多对多：(关系表)

create table boy(

id int not null auto_increment primary key,

name char(32)

)engine = innodb default charset=utf8;

create table girl(

id int not null auto_increment primary key,

name char(32)

)engine = innodb default charset=utf8;

create table b2g(

id int not null auto_increment primary key,

b_id int,

g_id int,

constraint 约束名称1 foregin key(b_id) references boy(id),

constraint 约束名称2 foregin key(g_id) references girl(id)

)engine = innodb default charset = utf8;

删除表

drop table tb_name;

查看相关

--> 查看创建表的语句

show create table table_name;

--> 查看表结构

desc table_name;

--> 查看表

show tables;

操作表数据

针对表的数据进行操作，主要涉及4类：

增加 insert

删除 delete

修改 update

查找 select

而插入内容就相当于在表文件中按照MySQL的格式写数据

插入数据

insert into table_name(field) values(value),(value2)

--> 语法格式： insert into 表名(字段) values(值1),(值2)

--> 两个(value)表示插如多行数据

--> 当字典省略时,表示插入字段所有数据，values后面的值需要列出所有字段

insert into table_name(cname) select field from table_name

--> 把select查到的结果,当作数据来赋值给value

--> 查询到的数据字段要和插入的字段数量一致

删除数据

--> 清空表

delete from table_name

--> 删除之后,插入新数据自增列会继续之前的ID

truncate table table_name

--> 物理删除，速度快，重新计算ID

--> 删除某一条

delete from table_name where filed = values and/or ...

--> 只删除符合条件的数据

例子：

delete from table_name where filed in (1,2,3,4)

delete from table_name where id between 5 and 10

修改数据

update table_name set field = 'value'

--> 更新所有数据的field字段的值，加 where 只修改匹配到的行

例子：

update table_name set id = 8 , name = 'daxin' where age = 18;

查询数据

--> where条件

select * from table_name where id > 2

select field as '别名' from table_name --> 加别名

select * from table_name where id in (1,2)

select * from table_name where cid in (select tid from teacher)

--> 排序限制条件

select * from table_name order by field asc/desc(正序/倒序)

select * from table_name order by field asc limit 1 取第一个值

select * from table_name limit 1,2(起始位置,找几个)

--> 查找field字段包含key的数据,% 表示任意个任意字符， _表示任意一个字符

select * from table_name where field like '%key%'

--> 连表查询

select student.sid,student.sname,class.caption from student LEFT JOIN class on student.class_id = class.cid ;

--> 把class表中的字段放在student表的左边,并且进行 student.class_id = class.cid 匹配后显示，数据量以from指定的表为基准

--> left join:以 from 指定的表为基准,对数据进行显示

--> right join: 不常用,以 join 后面的表为基准进行显示。

--> inner join:(join 使用的就是),只保留连个表中都有数据的条目

--分组显示:

select * from table_name group by field

--> 分组显示,会去重,需要使用聚合函数来统计重复的次数

select field,count(id) from table_name group by field

--> 对id字段进行聚合(其他的还有min(),max(),sum(),avg()等)

例子：

1、获取每个班级多少人

SELECT class.caption,count(sid) from class LEFT join student on student.class_id = class.cid

group by class.caption

2、获取每个班级有多少人并且选出认识大于2的班级，

注意：如果针对 group by 的结果进行筛选,那么需要使用 having 不能在使用 where 了.

SELECT class.caption,count(sid) as number from class

LEFT join student on student.class_id = class.cid

group by class.caption

HAVING number >= 2

3、每个课程的不及格的人数。

select course.cname,count(sid) from score

left join course on score.corse_id = course.cid

where number < 60

group by course.cname

--> union:

把两个SQL的结果进行组合(上下合并)

select * from student

union / union all

select * from teacher;

注意上下两个表中的列数要统一

注意：

1、如果所有数据都一致，那么union会对结果进行去重

2、union all ,会保存所有的

MySQL练习题

表结构如下：

查询类基础练习题

id=1的老师任教的课程名称

老师姓名瞎猫任教的课程命令

已选课程id=1,所有学生的姓名

已选体育课，所有学生的姓名

已选波多任教任意课程,所有学生姓名

创建目标数据