Mysql相关

一、游标

1.1、游标指的是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力，可以把游标当做一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。通俗点理解就是操作数据库的，执行数据库命令需通过游标这个对象。

1.2、示例：

import pymysql

# 创建连接
conn = pymysql.connect(host="192.168.48.136", port=3306, user="xiang", passwd="xiang", db="test")
# 创建游标
cus = conn.cursor()
# 定义sql
sql = "select * from test2;"

# 执行
# cus.execute(sql)
# 取所有的结果,取结果之前，一定要先执行sql
# cus.fetchall()
# 取一个结果
# cus.fetchone()
# 取10行数据
# cus.fetchmany(size=10)
# 关闭游标
# cus.close()
# cus.executemany()
try:
    cus.execute(sql)
    result = cus.fetchone()
    print(result)
except Exception as e:
    raise e
finally:
    cus.close()
    conn.close()

二、数据库连接池

2.1、python编程中可以使用mysqldb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当的浪费资源，而且访问达到一定数量时，对mysql的性能会产生比较大的影响，因此实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

三、MySQL常用操作

3.1、连接MySQL

格式： mysql -h 主机地址 -u 用户名 -p 用户密码

①、连接到本机上的MYSQL。

首先在打开DOS窗口，然后进入目录 mysql bin，再键入命令mysql -uroot -p，回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是： mysql>。

②、连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：

mysql -h 110.110.110.110 -uroot -p abcd123

（注:u与root可以不用加空格，其它也一样）

③、退出MYSQL命令： exit （回车）。

3.2、修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

①、给root加个密码ab12。首先在DOS下进入目录mysql bin，然后键入以下命令：

mysqladmin -uroot -password ab12

注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。

②再将root的密码改为djg345

mysqladmin -uroot -pab12 password djg345

3.3、增加新用户。

（注意：和上面不同，下面的因为是MySQL环境中的命令，所以后面都带一个分号作为命令结束符）

格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

①、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：

grant select,insert,update,delete on *.* to test2@localhost identified by "abc";

如果你不想test2有密码，可以再打一个命令将密码消掉。

grant select,insert,update,delete on mydb.* to test2@localhost identified by "";

在上面讲了登录、增加用户、密码更改等问题。下面我们来看看MySQL中有关数据库方面的操作。注意：你必须首先登录到MySQL中，以下操作都是在MySQL的提示符下进行的，而且每个命令以分号结束。

四、Mysql常用命令

4.1、常用命令

create database name; 创建数据库

use databasename; 选择数据库

drop database name 直接删除数据库，不提醒

show tables; 显示表

describe tablename; 表的详细描述

select中加上distinct去除重复字段

mysqladmin drop database name 删除数据库前，有提示。

在mysql中select top用法和ms sqlserver有所区别。若在mysql中实现select top功能得用：select * from tablename limit M,N, 这里M表示从（M+1）条记录开始，N表示返回的记录条数

显示当前mysql版本和当前日期

select version(),current_date;

4.2、修改mysql中root的密码：

shell>mysql -u root -p

mysql> update user set password=password(”xueok654123″) where user=’root’;

mysql> flush privileges //刷新数据库

mysql>use dbname； 打开数据库：

mysql>show databases; 显示所有数据库

mysql>show tables; 显示数据库mysql中所有的表：先use mysql；然后

mysql>describe user; 显示表mysql数据库中user表的列信息）；

4.3、grant

创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个

mysql> grant all privileges on *.* to user@localhost identified by ’something’ with

增加新用户

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;

删除授权：

mysql> revoke all privileges on *.* from root@”%”;

mysql> delete from user where user=”root” and host=”%”;

mysql> flush privileges;

创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb

mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’

重命名表:

mysql > alter table t1 rename t2;

4.4、mysqldump

备份数据库

shell> mysqldump -h host -u root -p dbname >dbname_backup.sql

恢复数据库

shell> mysqladmin -h myhost -u root -p create dbname

shell> mysqldump -h host -u root -p dbname < dbname_backup.sql

如果只想卸出建表指令，则命令如下：

shell> mysqladmin -u root -p -d databasename > a.sql

如果只想卸出插入数据的sql命令，而不需要建表命令，则命令如下：

shell> mysqladmin -u root -p -t databasename > a.sql

那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢？

mysqldump -T./ phptest driver

其中，只有指定了-T参数才可以卸出纯文本文件，表示卸出数据的目录，./表示当前目录，即与mysqldump同一目录。如果不指定driver 表，则将卸出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。

4.5、将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入

可利用外壳程序键入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查询，可如下执行这些查询：

例如，如果您想将建表语句提前写在sql.txt中:

mysql > mysql -h myhost -u root -p database < sql.txt

4.6、增删改查

仓库 --数据库
箱子 --表
数据库：
进入mysql 命令行: mysql -uroot -p
查看所有数据库: show databases;
创建数据库： create database niu charset utf8;
删除数据库： drop database niu;
选择数据库： use databases;
查看所有表： show tables;
查看创建数据库的语句：show create database databasename;
查看创建表的语句：show create table tablename;
查看表结构：desc tablenmae;
表：
约束
#自增长
auto_increment
#非空
not null
#默认值
default 'xx'
#唯一
unique
#指定字符集
charset
#主键
primary key
#外键
增加两个表之间的联系
增：
#学生表
create table students(
id int auto_increment primary key，
name varchar(10) not null，
sex varchar(3) default '女',
address varchar(50),
phone int not null unique,
age，
);
#成绩表
create table scores(
id int auto_increnent primary key,
s_id int not null,
grade float not null,
);
删:
drop table tablename;
truncate tablename;#快速删除表
改:
alter table oldtable rename newtable; #改表名
alter table tablename modify name varchar(20);#改表结构
alter table tablename change name newname varchar(20);#改表结构
alter （table tablename add age float
after name;#新增字段的位置

查：
show create table tablename ;#查看新建表语句
desc table;#查看表结构
show tables ;#查看所有表

数据：
增
insert into student (name,money,sex,phone) values ('hk',10000,'男',188);
insert into student values('','小明',100,'',120);
删
turncate tablename； #删除整表数据，自增长id从头再来，快速，从磁盘直接删除，不可恢复
delete from student； 
#删除整个表的数据，自增长继续
改
update student set money=100；#不指定条件，修改所有
update student set money=110 where name='hk';#只改hk
自动提交
取消自动提交   set @@autocommitt=0;
                　　  select @@autocommitt=0;
#自动提交取消后，当前会话显示已经成功执行，其实后台并没有执行

查：
select * from students limit 1,5; #从第几条开始，下面的x条，不包含开始的那一条
SELECT * from students limit 5;查询5条
SELECT id,stu_name,sex,money,phone from students;#指定查询的字段
SELECT * from students;#查询所有的数据
SELECT * from students where sex='男';#指定条件
SELECT * from students where sex='男' and money>100; #多个条件，必须同时满足
SELECT * from students where sex='男' or sex='未知' ; #多个条件，有一个满足即可
SELECT * from students where sex !='男'; #<>也是不等于
SELECT * FROM students where addr like '%东京%';#模糊匹配，%代表的是通配符，必须得用like
SELECT * from students a where a.stu_name like '姚_';#_通配符表示任意一个单字符，姚字后面只能跟一个字
SELECT a.stu_name '学生名称',a.phone '学生电话' from students as a where a.stu_name='姚远';#给表起别名,as可以省略
SELECT * from students a where a.stu_name in ('牛牛','林倩','林远');# in
SELECT * from students a where a.money BETWEEN 1000 and 10000;#在什么什么之间的数据
SELECT * from students ORDER BY money desc;
#order by xxx desc，根据哪个字段继续排序，默认是升序，
降序是desc，升序asc
SELECT * from students a where a.addr = '' or a.addr is null; #查询字段为空的数据
SELECT DISTINCT a.money from students a ;#去重
SELECT COUNT(*) '学生人数' from students where sex='女'; #统计行数
SELECT MAX(a.money) 钱最多 from students a; #最大值
SELECT min(money) 钱最少 from students;#最小值
SELECT AVG(a.money) 平均多少钱 from students a; #平均数
SELECT sum(a.money) 总共多少钱 from students a;#总和
SELECT sex 性别,count(*) 人数 from students GROUP BY sex; #分组
SELECT
sex 性别,
count(*) 人数,
a.stu_name 名字

FROM
students a  WHERE a.money > 300 GROUP BY a.id HAVING a.stu_name LIKE '姚%';
#如果group by后面有条件的话，必须得用having子句，having子句里面用到的字段必须出现在select后面，如果group by和order by一起用的话，order by必须写在group by后面
SELECT *,COUNT(*) from students GROUP BY sex,class; #多个字段进行分组

SELECT id,stu_name from students UNION SELECT id,t_name from teacher;
#用来合并两条select语句的结果，两条select语句字段数量要一致，并且数据类型也要一致
union和union all的区别就是一个会去重一个不会

多表关联：
SELECT * FROM USER a, accounts b WHERE
a.id = b.user_id
AND a.username = 'niuhy';
-- SELECT * from students a ,scores b where a.id=b.s_id; -- 多表关联
-- 两个表里面都存在的数据查出来
SELECT * from students a LEFT JOIN scores b on a.id=b.s_id;
-- LEFT JOIN会把左边表所有的数据都查出来，右边表有匹配的就查出来
SELECT * from students a RIGHT JOIN scores b on a.id=b.s_id;
-- RIGHT JOIN会把右边表所有的数据都查出来，左边表有匹配的就查出来
SELECT * from students a inner JOIN scores b on a.id=b.s_id;
-- INNER JOIN两边表里都匹配的数据才查到
子查询：
把一条sql的结果，作为另一条sql的条件
SELECT * from scores a where a.s_id = (SELECT id from students where stu_name='牛牛');

把子查询当成一个表
SELECT
a.grade 成绩,
b.stu_name 学生名称,
b.id 学号
FROM
scores a,
( SELECT id,stu_name FROM students WHERE stu_name = '牛牛') b
WHERE
a.s_id = b.id;
数据库权限：
mysql数据的权限实质上都是在user表里控制的
①、grant
#所有的权限 所有数据库下面的所有表 用户 用户ip 
grant all on *.* to 'andashu'@'localhost' IDENTIFIED BY '123456' with grant option;
密码 #有执行grant语句的权限
grant all on *.* to 'andashu'@'%' IDENTIFIED BY '123456' with grant option;
取消授权：
Revoke select on *.* from dba@localhost;
Revoke all on *.* from andashu@localhost;

②、修改user表的数据
对user表进行增加、修改和删除
flush privileges;#刷新权限
备份数据库：
mysqldump -uroot -p123456 db > db.sql
mysqldump -uroot -p123456 -A > all.sql
恢复数据：
mysql -uroot -p123456 db < db.sql
存储过程：
批量的造数据
delimiter $$; #为了改结束符
CREATE PROCEDURE big_data1(num int)#代表要造多少条数据 100
BEGIN
DECLARE i int;
set i=0;
WHILE i<num do
insert into students (stu_name,money) VALUES (CONCAT('小明',i),20000);
#CONCAT的作用是连接不同类型的数据
#把字符串和数字拼接到一起
set i=i+1;
end WHILE;
End
$$;
delimiter;

call big_data1(500); #调用