MySQL

database [[MySQL]] MySQL 内置函数 字符串函数 查看字符的 ascii 码值 ascii(str)，str 是空串时返回 0 select ascii('a'); 查看 ascii 码值对应的字符 char(数字) select char(97); 拼接字符串 concat(str1,str2…) select concat(12,34,'ab'); 包含字符个数 length(str) select length('abc'); 截取字符串 -- left(str,len) 返回字符串 str 的左端 len 个字符 -- right(str,len) 返回字符串 str 的右端 len 个字符 -- substring(str,pos,len) 返回字符串 str 的位置 pos 起 len 个字符 select substring('abc123',2,3); 去除空格 -- ltrim(str) 返回删除了左空格的字符串 str -- rtrim(str) 返回删除了右空格的字符串 str -- trim([方向 remstr from str) 返回从某侧删除 remstr 后的字符串 str，方向词包括 both、leading、trailing，表示两侧、左、右 select trim(' bar '); select trim(leading 'x' FROM 'xxxbarxxx'); select trim(both 'x' FROM 'xxxbarxxx'); select trim(trailing 'x' FROM 'xxxbarxxx'); 返回由 n 个空格字符组成的一个字符串 space(n) select space(10); 替换字符串 replace(str,from_str,to_str) select replace('abc123','123','def'); 大小写转换，函数如下 -- lower(str) -- upper(str) select lower('aBcD'); 数字函数 求绝对值 abs(n) select abs(-32); 求 m 除以 n 的余数 mod(m, n)，同运算符 % select mod(10,3); select 10%3; 地板除 floor(n)，表示不大于 n 的最大整数 select floor(2.3); 天花板 ceiling(n)，表示不小于 n 的最大整数 select ceiling(2.3); 求四舍五入值 round(n,d)，n 表示原数，d 表示小数位置，默认为 0 select round(1.6); 求 x 的 y 次幂 pow(x,y) select pow(2,3); 获取圆周率 PI() select PI(); 随机数 rand()，值为 0-1.0 的浮点数 select rand(); 时间日期函数 获取子值，语法如下 -- year(date)返回date的年份(范围在1000到9999) -- month(date)返回date中的月份数值 -- day(date)返回date中的日期数值 -- hour(time)返回time的小时数(范围是0到23) -- minute(time)返回time的分钟数(范围是0到59) -- second(time)返回time的秒数(范围是0到59) select year('2016-12-21'); 日期计算，使用 +- 运算符，数字后面的关键字为 year、month、day、hour、minute、second select '2016-12-21'+interval 1 day; 日期格式化 date_format(date,format)，format 参数可用的值如下 -- 获取年%Y，返回4位的整数 -- 获取年%y，返回2位的整数 -- 获取月%m，值为1-12的整数 -- 获取日%d，返回整数 -- 获取时%H，值为0-23的整数 -- 获取时%h，值为1-12的整数 -- 获取分%i，值为0-59的整数 -- 获取秒%s，值为0-59的整数 select date_format('2016-12-21','%Y %m %d'); 当前日期 current_date() select current_date(); 当前时间 current_time() select current_time(); 当前日期时间 now() select now();

database MySQL MySQL 数据库简介 mysql 数据库，是当前应用非常广泛的一款关系型数据库。 知识点包括： 数据库与表的创建、删除 字段的类型、约束 关系的存储 数据行的增删改查 数据行的查找（重点） 视图、事务、索引 与 Python 交互 E-R 模型 当前物理的数据库都是按照 E-R 模型进行设计的 E表示entry，实体 R表示relationship，关系 一个实体转换成数据库中的一个表 关系描述两个实体之间的对应规则，包括： 1.一对一 2.一对多 3.多对多 关系转换为数据库表中的一个列*在关系型数据库中一行就是一个对象 三范式 经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式 第一范式（1NF）：列不可拆分 第二范式（2NF）：唯一标识 第三范式（3NF）：引用主键 后一个范式建立在前一个范式的基础上 数据完整性 一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中 在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束 字段类型 数字：int，decimal 字符串：char，varchar，text 日期：datetime 布尔：bit 约束 主键：primary key 非空：not null 唯一：unique 默认：default 外键：foreign key 自动增长：auto_increment

database MySQL MySQL 的 JSON 类型 之前的开发经验，让我了解到，在对表的设计时，可以预留一个 JSON 类型的字段，这样可以实现一定程度的扩展，我一开始有些反感，因为这可能滥用。例如说，建表的时候考虑不再那么周全，全部放在 attr 字段里，虽然后期实现没问题，但如果这样为什么不直接用 NoSQL，想怎么存就怎么存。 使用 JSON 类型存储，然后在代码里使用 ORM 的技术，也很容易读取。最近我遇到了一个需求，需要将监控数据以表格的形式展示，通用的 TSDB （使用 Grafana）很难渲染成表格，就决定使用 MySQL 实现。但是监控项那么多，可以考虑使用 JSON 类型存储。 设置 JSON 字段 mysql> CREATE TABLE user(id INT PRIMARY KEY, name VARCHAR(20) , lastlogininfo JSON); Query OK, 0 rows affected (0.27 sec) 设置类型为 JSON 即可。 插入 JSON 数据 字符串法 mysql> INSERT INTO user VALUES(1 ,"lucy",'{"time":"2015-01-01 13:00:00","ip":"192.168.1.1","result":"fail"}'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO user VALUES(1 ,"lucy",'{"time":"2015-01-01 13:00:00","ip":"192.168.1.1","result":"fail}'); ERROR 3140 (22032): Invalid JSON text: "Missing a closing quotation mark in string." at position 63 in value for column 'user.lastlogininfo'. 只要字符串符合 JSON 语法就可以直接插入，否则会报 Invalid JSON text 错误。 ...

database MySQL MySQL 相关命令 数据库连接 mysql -u username -p password:***** mysql> 数据库操作 创建数据库： create database dbname charsetutf-8; 删除数据库： drop database dbname; 切换数据库： use dbname; 查看当前选择的数据库： select database(); 展示所有数据库： show databases; 表操作 查看所有表： show tables; 创建表： create table tbname（ 列及类型约束 ）; # 例如： create table students( id int auto_increment primary key, sname varcahr(10) not null ); 修改表： alter table tbname add|change|drop 列名 类型; # 例如： alter table students add birthday datetime; 删除表： drop table tbname; 查看表结构： desc tbname; 更改表名称： rename table old-tbname to new-tbname; 查看表的创建语句： show create table tbname; 数据操作 查询： select * from tbname; 增加： ...

database MySQL Python MySQL 连接 Python 0.安装引入模块 使用接口Python DB API 安装mysql模块 pip install mysql-connector-python # python2.7，使用 Python-MySQL connector pip install pymysql # python3.4，安装 pymysql 模块 引入模块 import pymsql #python3.x import mysql.connector #py2.x 1.connection 对象 用于建立与数据库的连接 创建方法： # host：连接的 mysql 主机，本机填为 ‘localhost’ # port：连接的 mysql 主机的端口，默认为 3306 # user：连接的用户名 # passwd：连接的用户的密码 # db：连接的数据库名 conn = pymysql.Connect(host,port,user,passwd,db) # Python 3.x connection 对象的方法 close()：关闭连接 commit()：提交修改 rollback()：回滚事务 cursor()：返回 cursor 对象 2.cursor对象 用于执行语句 创建方法： cursor = conn.cursor() 方法 execute(op,[args]) # 执行一个数据库查询和命令 fetchone() # 取得结果集的下一行 fetchmany(size) # 取得结果集的下几行 fetchall() # 取得结果集的剩下的所有行 rowcount # 最近一次execute返回数据的行数或影响行数 close() # 关闭游标对象 属性 rowcount 只读属性，表示最近一次 execute() 执行后受影响的行数 connection 获得当前连接对象 3.查询的一般流程 select查询数据操作过程： 开始 创建connection 创建cursor 使用cursor.excute()执行select语句 使用cursor.fetch*()获取并处理数据 关闭cursor 关闭connection 结束 4.常见查询语句 # 增加 import pymysql try: conn = pymysql.Connect(host,port,user,passwd,db) cs1=conn.cursor() count=cs1.execute("insert into students(sname) values('张良')") print(count) conn.commit() cs1.close() conn.close() except Exception,e: print e.message # 删除 # -*- coding: utf-8 -*- import pymysql try: conn = pymysql.Connect(host,port,user,passwd,db) cs1=conn.cursor() count=cs1.execute("delete from students where id=6") print(count) conn.commit() cs1.close() conn.close() except Exception,e: print e.message # 查询一行数据 #-*- coding: utf-8 -*- import pymysql try: conn = pymysql.Connect(host,port,user,passwd,db) cur=conn.cursor() cur.execute('select * from students where id=7') result=cur.fetchone() print() cur.close() conn.close() except Exception,e: print e.message # 查询多行数据 #-*- coding: utf-8 -*- import pymysql try: conn = pymysql.Connect(host,port,user,passwd,db) cur=conn.cursor() cur.execute('select * from students') result=cur.fetchall() print(result) cur.close() conn.close() except Exception,e: print e.message 5.SQL 语句参数化 以参数传递的方式执行 sql 脚本，为之后的封装打基础 例如： #-*- coding: utf-8 -*- import pymysql try: conn = pymysql.Connect(host,port,user,passwd,db) cs1=conn.cursor() sname=raw_input("请输入学生姓名：") #参数 params=[sname] #参数封装成列表 count=cs1.execute('insert into students(sname) values(%s)',params) #%传参 print(count) conn.commit() cs1.close() conn.close() except Exception,e: print e.message 6.封装 将常见的语句封装成方法，以传参的方式执行语句，防注入 例如： #-*- coding: utf-8 -*- import pymysql class MysqlHelper(): def __init__(self,host,port,db,user,passwd,charset='utf8'): self.host=host self.port=port self.db=db self.user=user self.passwd=passwd self.charset=charset def connect(self): self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset) self.cursor=self.conn.cursor() def close(self): self.cursor.close() self.conn.close() def get_one(self,sql,params=()): result=None try: self.connect() self.cursor.execute(sql, params) result = self.cursor.fetchone() self.close() except Exception, e: print e.message return result def get_all(self,sql,params=()): list=() try: self.connect() self.cursor.execute(sql,params) list=self.cursor.fetchall() self.close() except Exception,e: print e.message return list def insert(self,sql,params=()): return self.__edit(sql,params) def update(self, sql, params=()): return self.__edit(sql, params) def delete(self, sql, params=()): return self.__edit(sql, params) def __edit(self,sql,params): count=0 try: self.connect() count=self.cursor.execute(sql,params) self.conn.commit() self.close() except Exception,e: print e.message return count