MySQL一. 概述什么是数据库? 数据库可以理解成保存有组织数据的容器(通常是一个文件或一组文件),很多时候我们所说的数据库就是我们使用的数据库软件(专业点就是数据库管理系统DBMS),我们并不是直接访问数据库而是使用数据库软件,它为我们访问数据库。 什么是数据表? 数据表就是我们要存放数据的东西,就像一个图书馆里的书架一样,并不是随便堆到里面就完事了,有人要借书,你需要根据目录(数据库里为索引)很快的找到书在哪排那层哪个位置,从而快速的取出要借的书。 什么是RDBMS? RDBMS即关系数据库管理系统(Relational Database Management System),是将数据组织为相关的行和列的系统,而管理关系数据库的计算机软件就是关系数据库管理系统. 有哪些RDBMS?
RDBMS的相关术语?
什么是SQL? SQL:Structure Query Language,结构化查询语言,是一种专门用来与数据库通信的语言。遵循的标准主要有:
SQL语句分类 Data defination language(DDL):
Data Manipulation language(DML)
Data Control Language(DCL)
Transaction Control Language(TCL)
二. 安装MySQL安装MySQL有好多种方式:
包管理器安装: 源码安装(RHEL平台,其他平台大同小异),此处版本是5.5.28,可以下载最新的,大同小异。
cmake编译安装5.5.28
通用二进制包安装:
通用二进制包安装5.6.26
三. 数据库操作:1. 显示所有数据库: 2. 使用/进入数据库 USE db_name; 3. 显示库的的所有表 SHOW TABLES; 4. 授权管理 用户管理: 创建用户 create user '用户名'@'IP地址' identified by '密码'; 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 修改密码 set password for '用户名'@'IP地址' = Password('新密码') PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议) 授权管理: show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
对于权限
对于数据库
对于用户和IP
示例
四. 表操作1、创建表 create table 表名( 列名 类型 是否可以为空, 列名 类型 是否可以为空 ) 是否可空,null表示空,非字符串
not null - 不可空
null - 可空
默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null ) 自增,如果为某列设置自增列,插入数据时无需设置此列,默认将自增(表中只能有一个自增列) create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) #注意:1、对于自增列,必须是索引(含主键)。 2、对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
外键
2、删除表 drop table tb_name; 3、清空表 delete from tb_name; truncate table tb_name; #清空表推荐使用truncate 4、修改表 #添加列: ALTER TABLE 表名 ADD 列名 类型; #删除列: ALTER TABLE 表名 DROP COLUMN 列名; #修改列: ALTER TABLE 表名 MODIFY COLUMN 列名 类型; -- 类型 ALTER TABLE 表名 CHANGE 原列名 新列名 类型; -- 列名,类型 #添加主键: ALTER TABLE 表名 ADD PRIMARY KEY(列名); #删除主键: ALTER TABLE 表名 DROP PRIMARY KEY; ALTER TABLE 表名 MODIFY 列名 INT, DRIP PRIMARY KEY; #添加外键: ALTER TABLE 从表 ADD CONSTRAINT 外键名称(形如:FK_从表_主表) FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段); #删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; #修改默认值: ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; #删除默认值: ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; 5、基本数据类型
基本数据类型
五. 基本操作这里主要就是一些DML语句 1、增(INSERT) insert into 表 (列名,列名...) values (值,值,值...);
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);
insert into 表 (列名,列名...) select (列名,列名...) from 表;
2. 删(DELETE) delete from 表 delete from 表 where id=1 and name='sam' 3. 改(UPDATE) update 表 set name = 'SAM' where id>1 4. 查(SELECT) select * from 表; select * from 表 where id > 1; select nid,name,gender as gg from 表 where id > 1; 5、其他 1、条件 select * from 表 where id > 1 and name != 'jerry' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表) 2、通配符 select * from 表 where name like 'jerry%' - jerry开头的所有(多个字符串) select * from 表 where name like 'jerr_' - jerr开头的所有(一个字符) 3、限制 select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行 4、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序 5、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10 特别的:group by 必须在where之后,order by之前 6、连表 无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示,如果B中无对应关系,则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示,如果B中无对应关系,则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid 7、组合 组合,自动处理重合 select nickname from A union select name from B 组合,不处理重合 select nickname from A union all select name from B
View Code
pymysqlpymysql是Python中操作MySQL的模块,使用方法和MySQLDB几乎一样。 一. 安装二. 使用1. 执行SQL语句 #!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) #创建游标 cursor = conn.cursor() #执行SQL,并返回受影响的行数 effect_row = cursor.execute("UPDATE tb1 SET host='1.1.1.1'") # 执行SQL,并返回受影响行数 #effect_row = cursor.execute("UPDATE tb1 SET host='1.1.1.2' WHERE id > %s",(1,)) # 执行SQL,并返回受影响行数 #effect_row = cursor.executemany("INSERT INTO tb1(host) VALUES(%s),(%s)", [("1.1.1.11",1),("1.1.1.11",1)]) print(effect_row) # 提交,不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close() 2、获取新创建数据自增ID #!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) # #创建游标 cursor = conn.cursor() cursor.executemany("INSERT INTO tb1(host,id) VALUES(%s,%s)", [("1.1.1.30",12),("1.1.1.30",13)]) conn.commit() cursor.close() conn.close() new_id = cursor.lastrowid print(new_id) 3、获取查询数据 #!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) # #创建游标 cursor = conn.cursor() cursor.execute("SELECT * FROM tb1") #获取第一行数据 row_1 = cursor.fetchone() #获取前N行数据 row_2 = cursor.fetchmany(3) #获取所有数据 row_3 = cursor.fetchall() conn.commit() cursor.close() conn.close() print(row_1) #游标的位置会变的,获取了第一行之后,游标就到第二行位置了 print(row_2) #因此打印前三行的时候,是打印的2,3,4 print(row_3) #同理,打印所有的,实际上是当前游标到最后的位置 #代码执行结果: (1, '1.1.1.1') ((2, '1.1.1.2'), (3, '1.1.1.2'), (4, '1.1.1.11')) ((5, '3'), (6, '1.1.1.11'), (7, '3'), (8, '1.1.1.11'), (9, '1'), (10, '1.1.1.11'), (11, '1'), (12, '1.1.1.30'), (13, '1.1.1.30')) PS: 在fetch数据时按照顺序进行,可以使用cursor.scoll(num,mode) 来移动游标位置,如下:
#获取第一行数据 row_1 = cursor.fetchone() #获取前N行数据 row_2 = cursor.fetchmany(3) #获取所有数据 #先移动游标 cursor.scroll(0,mode='absolute') #绝对位置移动 row_3 = cursor.fetchall() print(row_1) print(row_2) print(row_3) #再次执行的结果: (1, '1.1.1.1') ((2, '1.1.1.2'), (3, '1.1.1.2'), (4, '1.1.1.11')) ((1, '1.1.1.1'), (2, '1.1.1.2'), (3, '1.1.1.2'), (4, '1.1.1.11'), (5, '3'), (6, '1.1.1.11'), (7, '3'), (8, '1.1.1.11'), (9, '1'), (10, '1.1.1.11'), (11, '1'), (12, '1.1.1.30'), (13, '1.1.1.30')) 4、fetch数据类型 关于默认获取的数据是元祖类型,如果想要或者字典类型的数据,即:
View Code
SQLAlchemySQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射来操作数据库。简而言之,就是将对象转换为SQL语句,然后使用数据API执行SQL并获取执行结果。 对象关系映射(Object Relational Mapping,简称ORM,或O/RM,或O/R mapping),是一种程序技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说,它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。 Python其他的ORM框架,推荐博文:http://www.oschina.net/translate/sqlalchemy-vs-orms 既然ORM是一个框架或者说是中间件,无论怎样,肯定是会有消耗的,所以,用还是不用?推荐这个博文:https://www./article/orm/ SQLAlchemy本身无法操作数据库,其必须以pymysql等第三方插件。 Dialect用于和数据API交互,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如下代码: MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多:http://docs./en/latest/dialects/index.html 一. 底层处理使用Engine/ConnectionPooling/Dialect进行数据库操作,Engine使用ConnectionPooling连接数据库,而后再通过Dialect执行SQL语句。 #!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://tom:tom123@172.16.30.162:3306/db1",max_overflow=5) #执行SQL cur = engine.execute( "INSERT INTO tb1(host) VALUES ('172.16.30.253')" #插入一条数据 ) print('ID,',cur.lastrowid) #打印新插入行的自增ID #执行SQL cur1 = engine.execute( "INSERT INTO tb1(host) VALUES(%s),(%s)",[('172.16.0.1'),('172.16.0.2')] #一下插入多条记录, 使用%s方式 ) print('ID,',cur1.lastrowid) #执行SQL方式3 cur2 = engine.execute( "INSERT INTO tb1(host) VALUES(%(host)s),(%(host1)s)",host='172.16.1.1',host1='172.16.1.2' #插入多条记录,使用占位符的形式 ) print('ID,',cur2.lastrowid) #执行SQL cur_select = engine.execute("SELECT * FROM tb1") #获取第一行数据 res = cur_select.fetchone() #也存在指针的问题 print(res) #前三行 res1 = cur_select.fetchmany(3) print(res1) #获取所有 res2 = cur_select.fetchall() print(res2) 二. ORM功能的使用使用ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect所有的组件对数据进行操作。根据类创建对象,将对象转换成SQL,而后执行SQL。 1. 创建表
View Code
2. 操作表
表结构+连接数据库
表结构操作
参考:
|
|
来自: xiongzhang2017 > 《python》