mysql数据库
1 Mysql 安装 windows下
在mysql官网下载压缩包
https://dev.mysql.com/downloads/mysql/
将其解压的任意目录下
1.1 初始化
会自动创建data目录
1.2 安装完成，启动服务
进入阻塞状态，后面设置为windows服务就可以运行在后端
客户端连接
默认用户root 密码空
1.1 配置环境变量
D:\mysql-8.0.17-winx64\bin 将这写入环境变量中
1.2 创建windows服务
在windows服务中就可以查到
Net start MYSQL 启动服务
Net stop MYSQL 停止服务
2 mysql数据库操作
1.1 创建用户
create user 'alex'@'192.168.1.1' identified by '123123';
create user 'alex'@'192.168.1.%' identified by '123123';
create user 'alex'@'%' identified by '123123';
授权用户：
grant select,insert,update on db1.t1 to 'alex'@'%';
grant all privileges on db1.t1 to 'alex'@'%';
revoke all privileges on db1.t1 from 'alex'@'%'; 撤销权限
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super ??使用change master、kill、logs、purge、master和set global。还允许mysqladmin????调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
flush privileges，将数据读取到内存中，从而立即生效。
数据库操作 创建 删除 查看
create database db2;
create database db2 default charset utf8;
show databases;
drop database db2;
表的操作
创建，
show tables;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id int,name char(10))engine=innodb default charset=utf8;
create table t3(id int auto_increment,name char(10))engine=innodb default charset=utf8;
create table t1(
列名 类型 null,
列名 类型 not null,
列名 类型 not null auto_increment primary key,
id int,
name char(10)
)engine=innodb default charset=utf8;
# innodb 支持事务，原子性操作
# myisam myisam
auto_increment 表示：自增
primary key： 表示 约束(不能重复且不能为空); 加速查找
not null: 是否为空
是否可空，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;
主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) ) 主键
外键，一个特殊的索引，只能是指定内容 creat table color( nid int not null primary key, name char(16) not null ) create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid) )
添加列：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, drop 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;
create table t1( id int ...., num int, xx int, unique 唯一索引名称 (num,列名), constraint .... ) 唯一： 约束不能重复（可以为空） PS: 主键不能重复（不能为空）
create table userinfo2( id int auto_increment primary key, name char(10), gender char(10), email varchar(64) )engine=innodb default charset=utf8; create table host( id int auto_increment primary key, hostname char(64) )engine=innodb default charset=utf8; create table user2host( id int auto_increment primary key, userid int not null, hostid int not null, unique uq_user_host (userid,hostid), CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id), CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id) )engine=innodb default charset=utf8;
create table tb12( id int auto_increment primary key, name varchar(32), age int )engine=innodb default charset=utf8; 增 insert into tb11(name,age) values('alex',12); insert into tb11(name,age) values('alex',12),('root',18); insert into tb12(name,age) select name,age from tb11; 删 delete from tb12; delete from tb12 where id !=2 delete from tb12 where id =2 delete from tb12 where id > 2 delete from tb12 where id >=2 delete from tb12 where id >=2 or name='alex' 改 update tb12 set name='alex' where id>12 and name='xx' update tb12 set name='alex',age=19 where id>12 and name='xx' 查 select * from tb12; select id,name from tb12; select id,name from tb12 where id > 10 or name ='xxx'; select id,name as cname from tb12 where id > 10 or name ='xxx'; select name,age,11 from tb12; 其他： select * from tb12 where id != 1 select * from tb12 where id in (1,5,12); select * from tb12 where id not in (1,5,12); select * from tb12 where id in (select id from tb11) select * from tb12 where id between 5 and 12; 通配符： select * from tb12 where name like "a%" select * from tb12 where name like "a_" 分页： select * from tb12 limit 10; 前10条 select * from tb12 limit 0,10; 从0开始后面取10条 select * from tb12 limit 10,10; select * from tb12 limit 20,10; = select * from tb12 limit 10 offset 20; 从第20行开始读取，读取10行； 结合Python分页： # page = input('请输入要查看的页码') # page = int(page) # (page-1) * 10 # select * from tb12 limit 0,10; 1 # select * from tb12 limit 10,10;2 排序： select * from tb12 order by id desc; 大到小 select * from tb12 order by id asc; 小到大 select * from tb12 order by age desc,id desc; 取后10条数据 select * from tb12 order by id desc limit 10; 分组： select count(id),max(id),part_id from userinfo5 group by part_id; count max min sum avg **** 如果对于聚合函数结果进行二次筛选时？必须使用having **** select count(id),part_id from userinfo5 group by part_id having count(id) > 1; select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
连表操作： select * from userinfo5,department5 select * from userinfo5,department5 where userinfo5.part_id = department5.id select * from userinfo5 left join department5 on userinfo5.part_id = department5.id select * from department5 left join userinfo5 on userinfo5.part_id = department5.id # userinfo5左边全部显示 # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id # department5右边全部显示 select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id 将出现null时一行隐藏
数据操作：
插入数据：
insert into t1(id,name) values(1,'alex');
删除：
delete from t1 where id<6
修改：
update t1 set age=18;
update t1 set age=18 where age=17;
查看数据：
select * from t1;
1、增 1 2 3 insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表 2、删 1 2 delete from 表 delete from 表 where id＝1 and name＝'alex' 3、改 1 update 表 set name ＝ 'alex' where id>1 4、查 1 2 3 select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1
基本数据类型：
MySQL的数据类型大致分为：数值、时间和字符串
bit[(M)] 二进制位（101001），m表示二进制位的长度（1-64），默认m＝1 tinyint[(m)] [unsigned] [zerofill] 小整数，数据类型用于保存一些范围的整数数值范围： 有符号： -128 ～ 127. 无符号： ～ 255 特别的： MySQL中无布尔值，使用tinyint(1)构造。 int[(m)][unsigned][zerofill] 整数，数据类型用于保存一些范围的整数数值范围： 有符号： -2147483648 ～ 2147483647 无符号： ～ 4294967295 特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002 bigint[(m)][unsigned][zerofill] 大整数，数据类型用于保存一些范围的整数数值范围： 有符号： -9223372036854775808 ～ 9223372036854775807 无符号： ～ 18446744073709551615 decimal[(m[,d])] [unsigned] [zerofill] 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。 特别的：对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储。 FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 无符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 有符号： 1.175494351E-38 to 3.402823466E+38 **** 数值越大，越不准确 **** DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 无符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 有符号： 2.2250738585072014E-308 to 1.7976931348623157E+308 **** 数值越大，越不准确 **** char (m) char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。 PS: 即使数据小于m长度，也会占用m长度 varchar(m) varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。 注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡 text text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。 mediumtext A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters. longtext A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters. enum 枚举类型， An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) 示例： CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large') ); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small'); set 集合类型 A SET column can have a maximum of 64 distinct members. 示例： CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d'); DATE YYYY-MM-DD（1000-01-01/9999-12-31） TIME HH:MM:SS（'-838:59:59'/'838:59:59'） YEAR YYYY（1901/2155） DATETIME YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y） TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
参考博客：http://www.cnblogs.com/wupeiqi/articles/5713315.html
https://www.cnblogs.com/liwenzhou/p/8032238.html
作业：
http://images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.png
http://www.cnblogs.com/wupeiqi/articles/5729934.html
练习：www.cnblogs.com/wupeiqi/p/5748496.html
python 模块 ：pymysql
该模块需要用pip进行安装：pip3 install pymysql -i https://pypi.douban.com/simple
该模块是用来链接数据库的。
链接数据库：
import pymysql conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = 'select * from info ' cousor.execute(sql) #取到sql语句查询的数据 ret = cousor.fetchone() #取到第一行的数据 cousor.close() conn.close() print(ret) #打印查询到的数据
用户登录实例
import pymysql user = input("username:") passwd = input("passwd:") conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = "select * from info where name= '%s' and pwd= '%s' " %(user,passwd) cousor.execute(sql) #取到sql语句查询的数据 ret = cousor.fetchone() #取到第一行的数据 cousor.close() conn.close() if ret: print("登录成功") else: print("登录失败")
注意：
在sql = "select * from userinfo where username=%s and password=%s"%(user,pwd) 这里的字符串拼接会造成sql的注入问题
如客户端输入 long' or 1=1 -- 这样返回的都是成功
解决是 将上面拼接的user，pwd 改在execute中他会自动完成拼接
cousor.execute(sql,user,passwd)
另外两只写法：
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
批量操作：07实例
data = [("alex",12),("long",123),("xiao",1),("cui",123)]
cursor.executemany(sql,data)
对数据库操作：
创建表：
import pymysql conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = "create table student(tid int,tname char(10))" cousor.execute(sql) #取到sql语句查询的数据 conn.commit() cousor.close() conn.close()
插入数据：
import pymysql conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = "insert into info(name,pwd)value('long',123123)" cousor.execute(sql) #取到sql语句查询的数据 conn.commit() cousor.close() conn.close()
添加参数：
import pymysql conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = "insert into info(name,pwd)value(%s,%s)" cousor.executemany(sql,[('cui',123),('alex',1234),('wang','1qa')]) #插入多条 conn.commit() cousor.close() conn.close()
查不需要commit 进行提交，
需要fetchone 去拿去值
fetchone（） 一次只拿取一条结果
fetchmany(num) 自己定义一次拿取几条结果
fetchall() 一次性拿取所有结果 ，限制可以在sql语句中limit进行限制
cursor.scroll(1,mode="absolute") 光标移动到 第一位 absolute"： 模式是绝对移动
scroll(1, mode="relative") 相对移动 往后移动1位
try: 捕获异常 cursor.execute(sql) conn.commit() except Exception as e: print("报错拉",str(e)) conn.rollback 回滚
cousor = conn.cursor(cursor=pymysql.cursors.DictCursor)以字典形式显示 默认是以元祖返回
import pymysql conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = "select * from info" cousor.execute(sql) ret = cousor.fetchall() cousor.close() conn.close() print(ret)
import pymysql user = input("usename: ") pwd = input("passwd: ") conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 sql = "select * from info where name=%s and pwd=%s" cousor.execute(sql,(user,pwd)) ret = cousor.fetchone() if ret: print('登录成功') sql1 = "insert into student(tid,tname)value('1','long')" cousor.execute(sql1) conn.commit() else: print("登录失败") cousor.close() conn.close()
# 自动去重 select id,name from tb1 union select num,sname from tb2 # 不去重 select sid,sname from student UNION ALL select sid,sname from student
视图
是一个虚拟的临时表某个查询语句设置别名，日后方便使用
- 创建 create view 视图名称 as select * from student where id > 10; 视图是 虚拟出来的，并不是真实的，不能像视图直接插入数据 - 修改 alter view 视图名称 as SQL语句 - 删除 drop view 视图名称;
触发器
mysql知道触发器，当对某张表做：增删改操作时，可以使用触发器自定义关联的表进行关联的行为
定义 -- delimiter // 改遇到;不会终止 改变结束符 -- create trigger t1 BEFORE INSERT on student for EACH ROW 定义一个触发器 -- BEGIN -- INSERT into teacher(tname) values(NEW.sname); 当student插入一条数据，teacher插入student的sname值 -- -- END // -- delimiter ; 执行： -- insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根');
1、创建基本语法
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. NAME == 'alex' THEN INSERT INTO tb2 (NAME) VALUES ('aa') END END// delimiter ;
delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. num = 666 THEN INSERT INTO tb2 (NAME) VALUES ('666'), ('666') ; ELSEIF NEW. num = 555 THEN INSERT INTO tb2 (NAME) VALUES ('555'), ('555') ; END IF; END// delimiter ;
2、删除触发器
DROP TRIGGER tri_after_insert_tb1;
3、使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
insert into tb1(num) values(666)
函数
内置函数：
CHAR_LENGTH(str) 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ，则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接（自定义连接符） CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 CONV(N,from_base,to_base) 进制转换 例如： SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 例如： SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos：要替换位置其实位置 len：替换的长度 newstr：新字符串 特别的： 如果pos超过原字符串长度，则返回原字符串 如果len超过原字符串长度，则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 LTRIM(str) 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'
blog 表 id title ctime 1 asdf 2019-11 2 asdf 2019-11 3 asdf 2019-10 4 asdf 2019-10 select ctime,count(1) from blog group by ctime； count（） 内置函数 时间格式化： select DATE_FORMAT(时间, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m") 2019-11 2 2019-10 2
1、自定义函数
自定义函数(有返回值)： delimiter \\ create function f1( 头部 i1 int, i2 int) returns int BEGIN 函数体 declare num int default 0; set num = i1 + i2; return(num); END \\ delimiter ; SELECT f1(1,100);
2、删除函数 ：drop function func_name;
3、执行函数 ：
# 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
存储过程
保存在MySQL上的一个别名 => 是对一堆sql语句起一个别名
别名() 这样执行就可以查到结果了
与视图也是对sql语句进行别名，不同视图是当做一个表来使用的，不能进行插入
在存储过程中是别名代表一堆sql语句的操作，比视图实现功能更多
他用于替代程序员写SQL语句的，可以直接调用别人写好的存储过程
delimiter // create procedure p1() 可以传递参数 BEGIN select * from student; 操作 INSERT into teacher(tname) values("ct"); 操作 END // delimiter ; call p1(); 直接调用 cursor.callproc('p1') 在pymysql中的调用 替换 cursor.execute(sql) 这句 charset=‘utf-8’ 编码
对于存储过程，可以接收参数，其参数有三类：
- in 仅用于传入参数用
- out 仅用于返回值用
- inout 既可以传入又可以当作返回值
delimiter \\ create procedure p1( in i1 int, in i2 int, inout i3 int, out r1 int ) BEGIN DECLARE temp1 int; DECLARE temp2 int default 0; set temp1 = 1; set r1 = i1 + i2 + temp1 + temp2; set i3 = i3 + 100; end\\ delimiter ; -- 执行存储过程 set @t1 =4; set @t2 = 0; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2;
参数 out
delimiter // create procedure p3( in n1 int, out n2 int 表示n2需要传个变量 ) BEGIN set n2 = 123123; 这修改n2的值 在外部还获取到修改后的值 select * from student where sid > n1; END // delimiter ; set @v1 = 10; 变量v1 设置变量必须定义v1 call p2(12,@v1) v1 = n2 select @v1; 查看 v1这个变量值现在是123123
import pymysql conn = pymysql.connect(host='',user='root',password='',database='userinfo') cousor = conn.cursor() #拿数据 cousor.callproc('p3',(12,2)) ret = cousor.fetchall() print(ret) cousor.execute('select @_p3_0,@_p3_1') ret1 = cousor.fetchall() print(ret1) #打印两个参数
存储过程的几种应用：
1 事物：原子性操作
定义一个存储过程： delimiter // create procedure p4( out status int ) BEGIN 1. 声明如果出现异常则执行{ set status = 1; 出现异常 变量为1 rollback; 错误回滚 } 开始事务 ----操作 -- 由秦兵账户减去100 -- 方少伟账户加90 -- 张根账户加10 commit; 结束 set status = 2; END // delimiter ;
delimiter \\ create PROCEDURE p1( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\\ delimiter ;
2 游标：创建一个游标，将获取的数据放在游标中，再根据游标去一行一行的去操作
1 声明一个游标，获取A表中想要的数据
cursor = select id,num from A
for id,num in cursor:
insert into B(num) values(id + num)
两个表将A表中的查询的数据，插入到B表中，
创建一个游标，将获取的数据放在游标中，再根据游标去一行一行的去操作
游标的性性能是不高的
delimiter // create procedure p6() begin declare row_id int; -- 自定义变量1 声明变量 declare row_num int; -- 自定义变量2 declare done INT DEFAULT FALSE; 声明done = false 用于检测是否完成了 declare temp int; # 声明变量 declare my_cursor CURSOR FOR select id,num from A; 声明一个游标 declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 声明当游标没有值设置done=true open my_cursor; 打开游标 xxoo: LOOP 开始循环 LOOP 就是循环的意思 fetch my_cursor into row_id,row_num; 去游标中取一行数据赋值给 if done then 如果done= true leave xxoo; 离开循环 END IF; set temp = row_id + row_num; insert into B(number) values(temp); 若done = false 则插入数据 end loop xxoo; 终止循环 close my_cursor; 关闭游标 end // delimter ; call p6（）; 调用 一般对每一行数据进行操作时会使用到游标，他的性能相比较差
delimiter // create procedure p3() begin declare ssid int; -- 自定义变量1 declare ssname varchar(50); -- 自定义变量2 DECLARE done INT DEFAULT FALSE; DECLARE my_cursor CURSOR FOR select sid,sname from student; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor; xxoo: LOOP fetch my_cursor into ssid,ssname; if done then leave xxoo; END IF; insert into teacher(tname) values(ssname); end loop xxoo; close my_cursor; end // delimter ;
3 动态执行SQL（防SQL注入）在数据库里做
delimiter // create procedure p7( in tpl varchar(255), 参数 in arg int ) begin 1. 预检测某个东西 SQL语句合法性 2. SQL =格式化 tpl + arg 一个sql语句 3. 执行SQL语句 set @xo = arg; 1PREPARE xxx FROM 'select * from student where sid > ?'; 2EXECUTE xxx USING @xo; 3DEALLOCATE prepare prod; 去执行格式化完成的sql语句 end // delimter ; call p7("select * from tb where id > ?",9) ？ 占位符 id大于9 的
delimiter \\ CREATE PROCEDURE p4 ( in nid int ) BEGIN PREPARE prod FROM 'select * from student where sid > ?'; EXECUTE prod USING @nid; DEALLOCATE prepare prod; END\\ delimiter ;
索引介绍
参考博客：http://www.cnblogs.com/wupeiqi/articles/5713323.html
http://www.cnblogs.com/wupeiqi/articles/5716963.html
添加索引是为了加速查找，但是对于数据的插入，修改会比没有索引慢，
索引分类：
1 主键索引，按主键进行查询，不能为空，不能重复
2 普通索引，只是单纯的为了快速查找
3 唯一索引，不能重复，与主键区别是它可以为空
4联合索引，当有多列时合起来做一个索引
索引种类有2种：
1 hash索引：会对数据进行hash，将hash的值放在一张创建的hash表中，对应着存储的地址
缺点：因为在hash表中的存放时无序的，所一按单值查找时很快，但按范围进行查找时会很慢
2 btree索引： 类似于二叉树的存储方式，将数据转为数值，放在树上的某个位置，数据存放是有序的，适合一范围匹配查找
建立索引：
1 普通索引
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) )
create index index_name on table_name(column_name)
drop index_name on table_name;
show index from table_name;
2 唯一索引
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) )
create unique index 索引名 on 表名(列名)
drop unique index 索引名 on 表名
3 主键索引
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) )
alter table 表名 add primary key(列名);
alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
4 组合索引
create index ix_name_email on in3(name,email);
在组合索引中涉及到最左前缀匹配：
4.1 组合索引
4.2 索引合并
5 正确使用索引，提高索引命中率：
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效
- like '%xx' select * from tb1 where name like '%cn'; - 使用函数 select * from tb1 where reverse(name) = 'wupeiqi'; - or select * from tb1 where nid = 1 or email = 'seven@live.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' - 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where name = 999; - != select * from tb1 where name != 'alex' 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123 - > select * from tb1 where name > 'alex' 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 - order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc;
6 执行计划：让mysql预估执行操作(一般正确)
可以对sql语句进行估算用多久时间，看有没有命中索引，不真实运行
如：explain select * from userinfo3 where name='alex' 不会真实执行，会预估
在 返回显示中 type 一列显示all 表示全表扫描，没有走索引
type: const(走索引) 表示走了索引
mysql> explain select * from tb2; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 ... table 正在访问的表名 type 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A; possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
type ：都有哪些执行效率： all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
注意事项：
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
7 慢日志：
用来发现sql语句执行慢的，进而优化数据库
慢日志需要配置：
- 执行时间 > 10 就记录下来，来看有慢的sql
- 未命中索引 记录
- 日志文件路径
mysql服务端配置：
1 修改全局变量参数
mysql服务端配置： - 内存 show variables like '%query%' 找到包括query列出来，将slow_query_log = on 开启 long_query_time log_querys_not_using_indexes = on 配置参数： set global 变量名 = 值
2 修改配置文件my.conf
slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记
查看慢日志：
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
8分页：
无论是否有索引，limit分页是一个值得关注的问题
每页显示10条： 当前 118 120， 125 倒序： 大 小 980 970 7 6 6 5 54 43 32 21 19 98 下一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) order by nid desc limit 10; 上一页： select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1) order by nid desc limit 10;
a. select * from userinfo3 limit 0,10;分页10 条数据一页，显示10条数据
limlit 10,10 从第10页开始后10 条数据
但分页这样写，会到页数越多越慢
.解决方案：
1 - 索引表中扫：这个不会快很多，
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
-2 方案：
记录当前页最大或最小ID
1. 页面只有上一页，下一页
# max_id
# min_id
下一页：
select * from userinfo3 where id > max_id limit 10;
上一页：
select * from userinfo3 where id < min_id order by id desc limit 10;
2. 上一页 192 193 [196] 197 198 199 下一页
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)
ORM框架SQLAlchemy
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
参考博客：https://www.cnblogs.com/wupeiqi/articles/5713330.html
安装：
pip3 install SQLAlchemy
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，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.sqlalchemy.org/en/latest/dialects/index.html
操作
使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) #链接数据库，最大并发为5 # 执行SQL # cur = engine.execute( # "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)" # ) # 执行SQL # cur = engine.execute( # "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),] # ) # 执行SQL # cur = engine.execute( # "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)", # host='1.1.1.99', color_id=3 # ) # 执行SQL # cur = engine.execute('select * from hosts') # 获取第一行数据 # cur.fetchone() # 获取第n行数据 # cur.fetchmany(3) # 获取所有数据 # cur.fetchall()
ORM 框架作用：
1 提供简单规则
2 自动转为sql语句：如如果自己写可能写的sql语句不够优化， 这时候这种方式比较好
有两种工作架构：
1 DB first： 自己需先手动创建表和数据库， 通过框架自动生成类，
2 code first ：需要自己先创建数据库，编写类， 通过类生成表的操作SQLAlchemy 属于code first
1、创建表：
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) Base = declarative_base() # 创建单表 class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), Index('ix_id_name', 'name', 'extra'), ) # 一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) # 多对多 class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) port = Column(Integer, default=22) class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine)
2、操作表
#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) Base = declarative_base() # 创建单表 class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), Index('ix_id_name', 'name', 'extra'), ) def __repr__(self): return "%s-%s" %(self.id, self.name) # 一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer, primary_key=True) caption = Column(String(50), default='red', unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption) class Person(Base): __tablename__ = 'person' nid = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=True) favor_id = Column(Integer, ForeignKey("favor.nid")) # 与生成表结构无关，仅用于查询方便 favor = relationship("Favor", backref='pers') # 多对多 class ServerToGroup(Base): __tablename__ = 'servertogroup' nid = Column(Integer, primary_key=True, autoincrement=True) server_id = Column(Integer, ForeignKey('server.id')) group_id = Column(Integer, ForeignKey('group.id')) group = relationship("Group", backref='s2g') server = relationship("Server", backref='s2g') class Group(Base): __tablename__ = 'group' id = Column(Integer, primary_key=True) name = Column(String(64), unique=True, nullable=False) port = Column(Integer, default=22) # group = relationship('Group',secondary=ServerToGroup,backref='host_list') class Server(Base): __tablename__ = 'server' id = Column(Integer, primary_key=True, autoincrement=True) hostname = Column(String(64), unique=True, nullable=False) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session()
增：
obj = Users(name="alex0", extra='sb') session.add(obj) session.add_all([ Users(name="alex1", extra='sb'), Users(name="alex2", extra='sb'), ]) session.commit()
删
session.query(Users).filter(Users.id > 2).delete() session.commit()
改：需先查再改
session.query(Users).filter(Users.id > 2).update({"name" : "099"}) session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False) session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate") session.commit()
查：
ret = session.query(Users).all() ret = session.query(Users.name, Users.extra).all() ret = session.query(Users).filter_by(name='alex').all() ret = session.query(Users).filter_by(name='alex').first() ret = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(User.id).all() ret = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
其他条件查找：
# 条件 ret = session.query(Users).filter_by(name='alex').all() ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all() ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all() ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all() from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all() ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all() ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'eric', Users.id > 3), Users.extra != "" )).all() # 通配符 ret = session.query(Users).filter(Users.name.like('e%')).all() ret = session.query(Users).filter(~Users.name.like('e%')).all() # 限制 ret = session.query(Users)[1:2] # 排序 ret = session.query(Users).order_by(Users.name.desc()).all() ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() # 分组 from sqlalchemy.sql import func ret = session.query(Users).group_by(Users.extra).all() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() # 连表 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() ret = session.query(Person).join(Favor).all() ret = session.query(Person).join(Favor, isouter=True).all() # 组合 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all()
更多功能参见文档，猛击这里下载PDF
