mysql基础及python操作mysql

mysql基础及python操作mysql

python操作mysql

1、mysql管理系统：基于套接字（socket）编写的C/S架构的软件，用于管理数据库文件

2、mysqld 套接字服务端
mysql 套接字客户端

3、windows
管理员身份下
tasklist |findstr mysql //查找进程
taskkill /F（强制杀死） /PID 10556 //杀死进程
mysqld --install //制作系统服务
mysqld --remove //解除系统服务
net start MySQL //启动服务（管理员）
net stop MySQL //关闭服务（管理员）

4、centOS
管理员身份下
yum -y install mariadb-server mariadb(centos7)
yum -y install mysql-server mysql(centos6)
systemctl start mariadb
systemctl status mariadb
mysql //连接数据库管理系统

5、windows
select user(); //查看当前登录的账号
exit //退出mysql
mysql -uroot -p //默认root没有密码，回车就好
mysqladmin -uroot -p(加旧密码，没有就空格) password “新密码” //修改用户密码
mysql -uroot -p(新密码) //登录mysql

mysql破解密码
mysqld --skip-grant-tables //跳过授权表启动mysql
update mysql.user set password=password("") where user=“root” and host=“localhost”;
//更改mysql库中的user表格 设置password,调用mysql的password()方法，设置密码为空
flush privileges; //刷新授权表，刷新权限
然后退出mysql(exit),然后使用管理员身份杀死当前mysql进程,重新正常启动mysql服务

mysql -uroot -p123 -h 127.0.0.1 -P 3306 //指定IP和端口

6、centos（步骤跟windows基本一致）
systemctl stop mariadb //关闭服务
mysqld_safe --skip-grant-tables //跳过授权表启动mysql服务
pkill -9 mysql //杀死mysql服务（因为不是通过正常步骤登录mysql，所以只能通过杀死方式关闭服务）

7、windows
\s 查看字符编码
\mysql文件下新建配置文件my.ini
mysql5.5以上统一字符编码配置格式
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8
user=“root” //设置默认用户
password="" //设置用户密码

8、centos（与windows基本一致）
vim /etc/my.cnf //修改配置文件
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8
然后重启服务

9、操作文件夹（库）
增：create database db1 charset utf8;(指定数据库字符编码)
查：show create database db1;(查看数据库参数)
show databases;
改：alter database db1 charset gbk;(改变数据库字符编码)
删：drop database db1;

10、操作文件（表）
切换文件夹：use db1;
select database();(查看当前所在文件夹)
增：create table t1(id int,name char);
frm:表结构、ibd:表数据
查：show create table t1;(查看表结构)
show tables;
desc t1;
改：alter table t1 modify(修改) name char(6);
alter table t1 change name NAME char(6);(改变字段名)
删：drop table t1;

11、操作文件内容（记录）
增：insert t1(id,name) values(1,‘egon1’),(2,‘egon2’)
insert t1 values(3,‘egon3’)(不写表结构则使用默认结构)
查：select id,name from db1.t1;
select * from db1.t1;
改：update db1.t1 set name=‘SB’;(修改name字段所有值)
update db1.t1 set name=‘ALEX’ where id=2;
删：delete from t1;(把表中所有记录都删了)
delete from t1 where id=2;

12、help create(查看命令使用)

13、存储引擎：就是表的类型

14、查看mysql支持的存储引擎（主要使用InnoDB）
show engines;

15、指定表类型/存储引擎
create table t1(id int)engine=innodb;
create table t2(id int)engine=blackhole;(黑洞数据存进去就没了)

16、创建表 //字段名不能重复
create table 表名{
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件]
}

17、查看表
desc t4; //查看表结构
show create table t4; //查看表详细信息
show create table t4\G; //按照一行一行显示

18、修改表结构
修改表名
ALTER TABLE 表名 RENAME 新表名；
增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 【完整性约束条件】，
ADD 字段名 数据类型 【完整性约束条件】；
ALTER TABLE 表名 ADD 字段名 数据类型 【完整性约束条件】FIRST;
ALTER TABLE 表名 ADD 字段名 数据类型 【完整性约束条件】AFTER 字段名;
删除字段
ALTER TABLE 表名 DROP 字段名；
修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型【完整性约束条件】；
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型【完整性约束条件】；
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型【完整性约束条件】；

19、复制表
create table t1 select * from mysql.user;
create table t1 select * from mysql.user where 1>5;(加入假条件，使得只复制表结构不复制表记录)
create table t1 like mysql.uesr;(只复制表结构不复制表记录)

20、删除表
DROP TABLE 表名；

21、create table t2(x tinyint unsigned);(设置无符号，默认都是有符号)

22、整型不能设置宽度，mysql存储宽度已经写死，加上只是显示宽度

23、zerofill 填充0

24、浮点数
float:小数精度低，存储整数位多
double:小数精度中，存储整数位多
dedimal:小数精度高，存储整数位少

25、create table student(
id int,
name char(6),
born_year year,
birth_date date, //年月日
class_time time, //时分秒
reg_time datetime //年月日时分秒
);

26、now() //mysql获取当前时间

27、时间都有相应的范围

28、字符类型
char:定长
varchar:变长

29、宽度指的是字符的个数
create table t13(name char(5));
create table t14(name varchar(5));

30、select char_length(name) from t1; //查看字符长度

31、SET sql_mode = ‘PAD_CHAR_TO_FULL_LENGTH’; //设置sql模式：填充字符到完整模式，别删我末尾空格

32、mysql在做比较的时候，末尾的空格都去掉的
select name from t13 where name=‘李杰’; //即使是’李杰 '这些也会被找到

33、select name from t13 where name like like ‘李杰’； //模糊查询不会忽略尾部空格，找不到’李杰 '这些

34、更多使用是char，速度快，一张表中char和varchar不要混用

35、字符串系列（效率：char>varchar>text）

36、TEXT系列
TINYTEXT TEXT MEDIUMTEXT LONGTEXT
text:65535个字符2^16-1
mediumtext:16777215字符2^24-1
longtext:2^32-1

37、枚举类型
enum:单选，多个选一个

38、集合类型
set:多选，多个选多个

39、create table sonsumer(
id int,
name char(16),
sex enum(‘male’,‘female’),
level enum(‘vip1’,‘vip2’,‘vip3’),
hobbies set(‘play’,‘music’,‘read’,‘run’)
);

40、约束条件
Null:是否允许传空
Default:默认值
unique key:不允许重复 //name char(10) unique

41、单独唯一和联合唯一
name char(10) unique； //单独唯一
ip char(15),
port int,
unique(ip,port) //联合唯一

42、primary key:not null unique(不为空且唯一),通常将id字段设置为主键
对于innodb存储引擎来说，一张表必须有一个主键

43、单列主键
create table t1(
id int primary key,
name char(16)
);

44、复合主键
create table t1(
ip char(15),
port int,
primary key(ip,port)
);

45、auto_increment //自增长字段必须得定义成key(primary key或unique key都可)
create table t1(
id int primary key auto_increment,
name char(16)
);

46、清空表
delete from t1;(如果有自增长字段的话，还会保留数值，不推荐)
delete from t1 where id =3;(一般用来删除范围字段)
truncate t1; (推荐用来清空表)

47、foreign key:外键用于建立表之间关系
建立表关系
先建被关联的表(子表)，并且保证被关联的字段唯一
create table dep(
id int primary key,
name char(16),
comment char(50)
);
再建关联的表(主表)
create table emp(
id int primary key,
name char(10),
sex enum(‘male’,‘female’),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade //级联删除，如果被关联表删除了，关联表也跟着删除
on update cascade //级联更新，如果被关联表更新了，关联表也跟着更新
);
插入数据
先往被关联表插入记录
insert into dep values
(1,‘IT’,‘技术能力有限部门’)，
(2,‘销售’,‘销售能力不足部门’),
(3,‘财务’,‘花钱特别多部门’);
再往关联表插入记录
insert into emp values
(1,‘egon’,‘male’,1),
(2,‘alex’,‘male’,1);

48、尽量少用外键这种强耦合，只需要在逻辑上建立关系

49、两张表之间的关系：
多对一：在一张表中找是否有多条记录对应另一张表的一条记录（然后换过来找，只要有一边符合就行）出版社与书
多对多：作者与书（只要使用第三张表就可以存储两张表的关系）
一对一：潜在顾客与消费者关系（先有潜在顾客，再有消费者，消费者建立外键对应到潜在顾客，且外键需要唯一）

50、插入查询结果
insert into 表名(字段1，字段2，字段n) select (字段1，字段2，字段n) from 表2 where …;

51、修改字段的值
update 表名 set 字段1=值1，字段2=值2 where condition;

52、单表查询
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n; 限制条数

53、通过四则运算查询(加、减、乘、除)得到的表是虚拟表(内存)
select name,salary*12 as annual_salary from employee;(起别名)

54、定义查询显示格式
concat() //用于连接字符串
select concat('姓名: ‘,name,’ 性别: ',sex) as info,concat(‘年薪: ‘,salary*12) as anunual_salary from employee;
select concat ws(’:’,name,sex,age) from employee; //指定分隔符：

55、where多条件使用and拼接条件
where salary >= 20000 and salary <= 30000
where salary between 20000 and 30000
<20000 or >30000 //not between 20000 and 30000
age = 73 or age = 81 or age =28 //age in (73,81,28)
where post is Null //判断为空 is not Null

56、模糊匹配
where name like “jin%” //匹配name字段以jin开头的任意字符
where name like “jin_” //匹配name字段以jin开头的一个字符

57、select distinct 字段1，字段2，字段3 from 库.表
where 约束条件
group by 分组条件
having 过滤条件
order by 排序字段
limit n; 限制条数 //经常用于请求数据的分页
//写有顺序要求
顺序是from表、where、group by、having、distinct 字段、order by、limit n

58、分组就是分类
select * from employee group by post; //这样查询到的是每组的第一条数据(默认)
set global sql_mode=“ONLY_FULL_GROUP_BY”; //设置sql模式后
select post from employee group by post; //只能查看分组对应的字段以及聚合函数的结果

59、分组的目的是以组为单位进行统计，不分组默认全部为一组

60、聚合函数
max
min
avg
sum
count

61、聚合函数示例
每个职位员工个数
select post,count(id) as emp_count from employee group by post;
每个职位最大工资
select post,max(salary) as emp_count from employee group by post;
每个职位最小工资
select post,min(salary) as emp_count from employee group by post;
每个职位平均工资
select post,avg(salary) as emp_count from employee group by post;
每个职位年龄总和
select post,sum(age) as emp_count from employee group by post;

62、group_concat() //列举分组里面的个体
select post,group_concat(name) from employee group by post; //列举每个职位的人名字

63、因为where在group by前面，所以没法使用group by的分组条件，having可以
where中不能使用聚合函数,因为执行到where时还没有分组概念

64、order by
select * from employ order by age asc; //asc升序排序，desc降序排序
select * from employ order by age asc,id desc; //先按age排序，再按照id排序

65、数据分页
select * from employee limit 0,5;
select * from employee limit 5,5;

66、正则表达式
select * from employee where name regexp ‘^jin’; //以jin开头的

67、多表查询(原理是在笛卡尔积的基础上进行筛选查询)
先通过连表操作将多张表连在一起，然后通过筛选条件筛选出来

68、联表操作
内连接：只取两张表共同部分(inner join)
select * from employee inner join department on employee.dep_id = department.id;
左连接：在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id;
右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id;
全外连接：在内连接的基础上保留左右两表的没有对应关系的记录(将左右连接合并后去重)
select * from employee left join department on employee.dep_id = department.id;
union
select * from employee right join department on employee.dep_id = department.id;

69、子查询
将查询的结果作为另一个查询的条件

70、带EXISTS关键字的子查询
EXISTS(select id from department where name=“技术”) //判断括号内查询是否有结果，有则返回true
select * from employee where EXISTS(select id from department where name=“技术”);

71、select * from (select id,name,sex from employee) as t1; //t1就是一个表了，不过还是存在内存中

72、权限管理
创建账号
本地账号：create user ‘egon’@‘localhost’ identified by ‘123’; //mysql -uegon -p123
远程账号：create user ‘xiaoming’@‘172.16.47.130’ identified by ‘123’; //mysql -uxiaoming -p123 -h 服务端ip
create user ‘xiaoming’@’%’ identified by ‘123’; //mysql -uxiaoming -p123 -h 服务端ip //允许所有网段登陆
授权(建完的账号是没有权限的)只能root用户授权
user: .(控制所有库下所有表都有权限)
db: db1.*(库下所有表都有权限)
tables_priv: db1.t1(单张表有权限)
columns_priv: id,name(某些字段有权限)
grant all on . to ‘egon’@‘localhost’; //授权
revoke all on . from ‘egon’@‘localhost’; //回收权限

73、Navicat工具(图形界面)套接字客户端
https://pan.baidu.com/s/1bpo5mqj
ctrl + ? 注销
shift + ctrl + ? 取消注销

74、pymysql模块：本质是一个套接字客户端软件
pip install pymysql

75、通常新建账号授权之后，刷新下特权
flush privileges

76、import pymysql
建连接
conn=pymysql.connect(
host=’’,
port=3306,
user=‘root’,
password=’’,
db=’’,
charset=‘utf8’
)
拿到游标对象
cursor=conn.cursor()
为游标提交命令，执行sql语句
sql = ‘select * from userinfo where user="%s" and password="%s"’ %(user,pwd)
rows=cursor.execute(sql) //rows是操作数据库表的行数
cursor.close() //需要关闭游标
conn.close() //需要关闭连接

77、游标：给mysql提交命令的一种接口

78、sql注入问题
sql = ‘select * from userinfo where user="%s" and password="%s"’ %(user,pwd)
如果输入user=xx" or 1=1 – hahha(–是注释符，这样就会让1=1永远成立跳过认证机制)
处理手段：账号不让输特殊字符

79、避免sql注入问题，以后就不要自己拼接字符串了，让pymysql的execute去拼接
sql=‘select * from userinfo where user=%s and pwd=%s’
rows=cursor.execute(sql,(user,pwd))

80、pymysql增删改
建链接
拿游标
执行sql
sql=‘insert into userinfo(user,pwd) values(%s,%s)’
rows=cursor.execute(sql,(‘wxx’,‘123’))
需要将语句提交才真正操作了数据库
conn.commit()
关游标
关链接

81、批量执行
rows=cursor.executemany(sql,[(‘yxx’,‘123’),(‘egon’,‘123’),(‘alex’,‘123’)])
print(cursor.lastrowid) //可以查看表格从哪个ID开始插入数据

82、pysql查
拿游标
cursor=conn.cursor(pymysql.cursors.DictCursor) //设置游标暂存数据格式为字典
rows=cursor.execute(‘select * from userinfo;’)
print(cursor.fetchone()) //取出一条查询数据，游标会偏移1
print(cursor.fetchmany(2)) //取出多条数据，游标会偏移n
print(cursor.fetchall()) //取出所有数据，游标偏移到最后
如果要重复取出数据，要将游标偏移回去
cursor.scroll(3,mode=‘absolute’) 绝对位置移动
cursor.scroll(3,mode=‘relative’) 相对位置移动

83、视图(在硬盘中只有表结构，没有表数据)不推荐使用视图，并且视图是主要用于方便查看，不推荐修改视图，因为牵扯到数据太多
把sql语句查询下来的虚拟表保存下来
create view course2teacher as select * from course inner join teacher on course.teacher_id = teacher_tid; //创建视图
select * from course2teacher;

84、触发器(针对增删改操作前后)
插入前
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
删除和修改也是一样

85、创建触发器示例
准备表
create table cmd(
id int primary key auto_increment,
user char(32),
priv char(10),
cmd char(64),
sub_time datetime, #提交时间
success enum (‘yes’,‘no’) #no代表执行失败
);
create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
delimiter // 将mysql结束符改成//
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if new.success = ‘no’ then #等值判断只有一个符号
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time); #必须加分号，但是如果加分号mysql就会以为结束，所以改结束符
end if; #必须加分号
end // #代表语句结束
delimiter ; 最后将结束符改回分号

86、new表示即将插入的数据行，old代表即将删除的数据行(触发器)

87、存储过程
通过调用它的名字可以执行其内部sql代码

88、需要使用存储过程操作哪个数据库，就将存储过程建在哪个库下
无参存储过程
delimiter //
create procedure p1()
BEGIN
select * from db7.teacher;
END //
delimiter ;

89、show create procedure p1; 查看存储过程

90、MySQL中调用存储过程
call p1()

91、python中调用
cursor.callproc(‘p1’)

92、有参存储过程
delimiter //
create procedure p1(in n1 int,in n2 int,out res int) in代表往里传入参数n1，并且指定数据类型，out代表传出
BEGIN
select * from db7.teacher where tid > n1 and tid < n2;
set res =1; 设置返回值
END //
delimiter ;
MySQL中调用
set @x=0;
call p1(2,4,@x);
select @x;
Python中调用
cursor.callproc(‘p1’,(2,4,0)) #callproc所做的事情(定义三个变量)@_p1_0=2,@_p1_1=4,@_p2_2=0(012分别代表三个参数索引，前两个参数值不会变，后面那个会变)
cursor.execute(‘select @_p1_2’)
print(cursor.fetchone()) 查看返回值

93、mysql中设置值
set res = 1;

94、定义参数inout n int代表参数n可进可出

95、存储过程：实现数据库与应用程序的解耦

96、解耦方式对比
一：
python:调用存储过程
mysql:编写存储过程
二：
python:编写纯生SQL
MySQL:啥事都没干
三：
python：基于ORM框架来写类去产生对象，由ORM帮忙转化成纯生SQL
MySQL:啥事都没干
推荐：三>二>一

97、事务
将一顿SQL语句放在一起，要不同时运行成功，要不同时运行失败

98、函数是用于SQL语句中

99、date_format()函数用于格式化时间
date_format(sub_time,’%Y-%m’)

100、索引——键，用于优化查询，索引是一棵B+树，真实数据只存放在叶子节点

101、索引会提高查询速度，但是会降低写的效率，因为最后改变数据后还要写索引，所以索引不是越多越好

102、索引字段要尽量的小

103、InnoDB存储引擎索引分为两大类
聚集索引和辅助索引
聚集索引：InnoDB中将主键设置为索引
辅助索引：可以给其他字段设置索引

104、创建索引
create index idx_id on s1(id);