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），为表加入索引等。是动作查询的一部分

小结：

  1. 我们日常开发会用到的SQL语言类型为：DML、DQL以及DDL
  2. 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;

各字段含义：

  1. 列名
  2. 数据类型
  3. 是否可以为空(null/not null)
  4. 是否默认值(default value)
  5. 是否自增(auto_icrement)：一个表只能存在一个自增列并且必须有索引(普通索引或主键索引),类型必须是数值型。
  6. 主键(primarr key)：数据不能为空、不能重复，可以加速查找(数据库的B树结构)
  7. 外键(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         TinyBlob
20         Blob 
21         MediumBlob
22         LongBlob
23         存文件:虽然可以用二进制进行存储,但是一般是存储文件在服务器上的路径(URL)
24 时间：
25     date  YYYY-MM-DD
26     time  HH:MM:SS
27     year  YYYY
28     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类：

  1. 增加 insert
  2. 删除 delete
  3. 修改 update
  4. 查找 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练习题

表结构如下：

查询类基础练习题

  1. id=1的老师任教的课程名称
  2. 老师姓名瞎猫任教的课程命令
  3. 已选课程id=1,所有学生的姓名
  4. 已选体育课，所有学生的姓名
  5. 已选波多任教任意课程,所有学生姓名

创建目标数据

 

posted @ 2017-07-13 11:21  SpeicalLife  阅读(421)  评论(0)  编辑  收藏  举报