mysql基础及python *** 作mysql_python

概述mysql基础及python *** 作mysqlpython *** 作mysql1、mysql管理系统：基于套接字（socket）编写的C/S架构的软件，用于管理数据库文件2、mysqld套接字服务端mysql套接字客户端3、windows管理员身份下tasklist|findstrmysql//查找进程taskkill/F（强制杀死）/PID10556//杀死进 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: @H_797_404@.(控制所有库下所有表都有权限)
db: db1.*(库下所有表都有权限)
tables_priv: db1.t1(单张表有权限)
columns_priv: ID,name(某些字段有权限)
grant all on @H_797_404@. to ‘egon’@‘localhost’; //授权
revoke all on @H_797_404@. 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);