MySQL
启动数据库server端
net start mysql
终止数据库server端
server net stop mysql
启动数据库client端
mysql -u用户名 -p密码 -h地址ip
终止数据库client端
exit & q
SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）
1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
3、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE
4、DQL语句 数据库操纵语言：查询数据SELECT
方法
select now() 获取当前时间
select user() 查看当前用户
select database() 查看当前使用的文件夹
select concat(id,":",name,":") from t1; 拼接字段值
set password = password("密码") 设置密码,自动存为密文
show engines 查看所有储存引擎
show variables like "storage_engine%"; 查看当前正在使用的引擎
1,基本操作
创建用户
create user "ceshi"@"公网ip" identified by "密码";
删除用户
drop user "ceshi"@"ip"
查看权限
show grants for "用户名"@"ip"
设置权限
grant 权限 on 数据库.表名 to "用户名"@"ip" identified by "密码";
授权并创建(所有权限)
grant all on 数据库.表名 to "用户名"@"ip" identified by "密码";
删除权限
revoke 权限 on 数据库.表名 from "用户名"@"ip";
刷新权限,立刻生效
flush privileges
1.1 操作库(文件夹)
数据库 database
# 增:create
1,创建数据库
create database 库名 charset 编码级
# 删:drop
drop database 库名
# 改:alter
1,修改编码级
alter database 库名 charset 编码级
# 查:show
1,查看所有数据库
show databases
2,查看建库语句
show create database 库名
# 使用库(相当于双击,切换)
use 库名
1.2 操作表(文件)
表 table
# 增 create
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],);
# 删 drop
drop table 表名;
# 改 alter
1,修改字段类型 modify
alter table 表名 modify 字段 类型;
1.1,修改字段顺序
alter table 表名 modify 字段2 字段3 after 字段1
alter table 表名 modify 字段2 first
2,修改字段名和类型 change
alter table 表名 change old字段 new字段 类型; # 字段name 变为newname 必须跟类型
3,添加字段 add
alter table 表名 add 字段 类型;
alter table 表名 add 字段 类型first/after 字段;
4,删除字段 drop column
alter table 表名 drop column 字段;
5,修改表名 rename
alter table old表名 rename new表名;
# 查 show
1,查看所有表
show tables;
2,查看建表语句
show create tables 表名;
3,查看表结构
desc 表名
1.3 操作记录(一条一条的数据)
# 增 insert into
1,增加单条数据
insert into 表名 values(data1,data2,data3...);
2,增加多条
insert into 表名 values(data1,data2,data3...),(data1,data2,data3...);
3,指定字段增加
insert into 表名(字段1,字段2) values (data1,data2),(data1,data2);
# 删
1,删除 值 这条数据
delete from 表名 where 条件;
2,重置表
truncate table 表名
# 改
update 表名 set 字段 = 值 where 条件;
# 查
1,查看表的所有数据
select * from 表名;
2,指定字段查看数据
select 字段 from 表名;
常用数据类型
参考网址:https://www.runoob.com/mysql/mysql-data-types.html
# 整型
tinyint 1字节 无符号范围(0~255) unsigned(-128~127)
int 4字节 无符号范围(0~42亿左右) unsigned(+-21亿左右)
# 浮点型
float 单精度 (255,30) 默认小数位 5
double 双精度 (255,30) 默认小数位 16
decimal 金钱类型(字符串) (65, 30) 默认小数位 0
# 字符串
char 定长(固定开辟) 255字符
varchar 变长(速度较慢) 21845字符
text
# 枚举 和 集合
enum 枚举 : 从列出来的数据当中选一个(性别)
set 集合 : 从列出来的数据当中选多个(自动去重) 爱好
create table t3(
id int,
name varchar(10),
money float(6,3),
sex enum("男性","女性","禽兽","人妖","雌雄同体","半兽人","阿凡达"),
hobby set("吃肉","抽烟","喝酒","喝假酒")
);
# 正常
insert into t6(id , name ,money , sex , hobby) values(1,"克拉伊买买提",56.7891,"半兽人","喝假酒,抽烟");
# 不能重复,报错!
insert into t6(id , name ,money , sex , hobby) values(1,"克拉伊买买提",56.7891,"半兽人","喝假酒,抽烟,抽烟,抽烟,抽烟");
# 时间
data 3 YYYY-MM-DD （1000-01-01/9999-12-31）
time 3 HH:MM:SS （'-838:59:59'/'838:59:59'）
year 1 YYYY （1901/2155）
datetime 8 YYYY-MM-DD HH:MM:SS （1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
timestamp 4 YYYYMMDD HHMMSS （1970-01-01 00:00:00/2037 年某时）当前时间自动更新
数据类型的约束
unsigned 无符号
zerofill 0填充
not null 某一个字段不能为空
default 某一个字段设置默认值
unique 某一个字段不能重复
auto_increment int类型自增(自带 not null)
# 设置条件 int/unique
primary key 主键:某一个字段唯一unique,且不为空not null
# 联合主键(PRI出现2个),当在添加primary key时,降为普通索引MUL
create table t(
id int not null,
ip char(15) not null,
port int,
unique(id,ip) #id与ip,不为空且唯一,
);
foreign key 外键:多张表通过关联字段联合在一起
# 使用方法
foreign key (要设置表外键的字段) references 要联合的表名(字段)
# 级联删除和级联更新
foreign key (要设置表外键的字段) references 要联合的表名(字段) on update cascade on delete cascade (set null)? 无数据时补 null
表的存储引擎 engine
InnoDB
存储文件个数:表结构,表的数据和索引
InnoDB特点：
1）支持事务：begin开启事务;commit提交事务;rollback回滚事务
2）行级锁定:给一张表中的每一行上一把锁,保证数据安全,支持高并发!
3）表级锁定:给一张表上锁,保证表数据安全,看修改数据的东西来决定上什么锁
4）支持外键:foreign key,有一定的约束
5）整个表和主键以cluster方式存储，组成一棵平衡树
6）所有secondary index都会保存主键信息
7）支持分区，表空间，类似oracle
8）支持外键约束，5.5以前的版本不支持全文索引，以后支持
9）对硬件资源的要求相比MyISAM高
InnoDB适用的场景：
1）需要事务支持的业务
2）行级锁对高并发有很好的适应能力，但需确保查询通过索引完成
3）数据读写及更新较为频繁的场景
4）数据一致性要求较高的业务
5）硬件设备内存较大，可用InnoDB较好的缓存能力来提高内存利用率，尽可能减少IO
MyISAM
存储文件个数:表结构,表的数据
MyISAM特点：
1）不支持事务
2）表级锁定（更新时锁整个表）
3）读写互相阻塞
4）只会缓存索引（key_buffer_size），以大大提高访问性能减少磁盘IO，但是不会缓存数据
5）读取速度较快，占用资源相对少
6）不支持外键约束，但支持全文索引
MyISAM适用的场景：
1）不需要事务支持的业务
2）一般为读数据比较多的网站应用，读写都频繁的场景不适合，读多或写多的场合适合
3）并发访问相对比较低的业务（纯读纯写高并发也可以）
4）数据修改相对较少的业务
5）以读为主的业务，如www，blog，图片信息数据库，用户数据库，商品库等业务
6）对数据一致性要求不是很高的业务
7）硬件资源比较差的机器还想打到比较好的效果可用MyISAM
MyISAM引擎调优：
1）设置合适的索引
2）调整读写优先级，根据实际需求保证重要操作更优先执行
3）启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）
4）尽量顺序操作让insert数据都写入到尾部，减少阻塞
5）分解大的时间长的操作，降低单个操作的阻塞时间
6）降低并发数（减少对mysql访问），对于高并发应用场景可采用排队机制
7）对于更改不频繁的数据，可用Query Cache或memcached缓存服务可以极大的提高访问效率
MEMORY
存储文件个数:表结构
MEMORY特点:
1）增删改查速度快
2）断电即消失
3）容量有限
表关系(俩张表之间的数据)
一对多(foreign key 在多的那张表设置外键)
多个学生都是一个班级的;
一对一(foreign key unique 后出现的那张表)
客户关系;
多对多(产生第三张表,把关联关系的字段作为第三张表的外键)
书与作者;
单表查询
#sql查询语句的完整语法
select .. from .. where .. group by .. having .. order by .. limit ..
where 用法:
1,比较运算符 = > >= < <= !=
2,逻辑运算符 and or not
3,查询区间 between 小值 and 大值
4,in()
5,like "%" like "_"
group by 用法:
group_concat() 分类拼接
聚合函数:max/min/avg/sum/count/distinct
having 用法:
order by 用法:
asc 升序(默认)
desc 降序
limit 用法:
limit x,y 从x开始,往后查y条
limit x 从头开始查x条
regexp 用法:
正则匹配,不识别?号,速度较慢
多表查询
pymysql
1,基本语法
# 与数据库建立链接
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0827")
# 创建(游标对象)操作句柄
cr = conn.cursor(cursor = pymysql.cursors.DictCursor) # 默认为元组,指定返回值是字典
# 创建表
sql = """create table t2(
id int primary key auto_increment,
uesr char(255) not null,
password char(255) not null
)"""
res = cr.execute(sql)
print(res)
# 查看表结构
sql = "desc t2"
print(cr.execute(sql)) # 返回表结构条数
# 查看结构数据
print(cr.fetchall())
# 增加记录
sql = "insert into t2 values (1,'bajie','123456')"
sql = "insert into t2 values (2,'baozhu','123456')"
sql = "insert into t2 values (4,'baozhu','123456')"
cr.execute(sql)
cr.execute("commit") # 增删改 都需要commit 确认提交数据
# 查看记录数据
sql = "select * from t2"
cr.execute(sql)
print(cr.fetchall())
# 删除表
sql = "drop table t2"
cr.execute("commit")
print(cr.execute(sql))
conn.close()
cr.close()
2,sql注入
import pymysql
# 与数据库建立链接
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0827")
# 创建(游标对象)操作句柄
cr = conn.cursor(cursor = pymysql.cursors.DictCursor) # 默认为元组,指定返回值是字典
# 创建表
# sql = """create table t2(
# id int primary key auto_increment,
# uesr char(255) not null,
# password char(255) not null
#
# )"""
# cr.execute(sql)
# # 增加记录
# sql = "insert into t2 values (1,'bajie','123456')"
# cr.execute(sql)
# sql = "insert into t2 values (2,'baozhu','123456')"
# cr.execute(sql)
# sql = "insert into t2 values (3,'meizhu','123456')"
# cr.execute(sql)
# cr.execute("commit") # 增删改 都需要commit 确认提交数据
sql = "select * from t2"
cr.execute(sql)
user = input("请输入用户名: >>> ").strip()
pwd = input("请输入密码: >>> ").strip()
# sql = "select * from t2 where uesr = '%s' and password = '%s'"%(user,pwd)
# print("登录成功" if cr.execute(sql) else "登录失败")
"""
输入时 : sfsdf' or 3=3 -- sdfsd
# -- 后面的字符串都会被注释掉, 前面账号虽然是错的 但是 2=2是真的 绕开了账号和密码的判断;
select * from usr_pwd where username='afasdfasdfasdf' or 2=2 -- sfasdf' and password='3434
"""
# (2) 使用预处理,提前对sql语句中的特殊符号进行处理
"""
使用预处理机制,可以避免绝大多数sql注入的问题
execute 如果参数为2个,将默认开启预处理
execute(sql , (参数1,参数2,参数3 .... ) )
"""
sql = "select * from t2 where uesr = %s and password = %s"
print("登录成功" if cr.execute(sql,(user,pwd)) else "登录失败")
cr.close()
conn.close()
3, 增删改查
"""
python 操作mysql时,默认开启事务,必须在增删改之后
提交数据,才会真正对数据库发生变化,默认默认是回滚
提交数据: conn.commit()
回滚数据: conn.rollback()
execute 一次插入一条
executemany 一次插入多条
"""
import pymysql
# 与数据库建立链接
conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db0827")
# 创建(游标对象)操作句柄
cr = conn.cursor(cursor = pymysql.cursors.DictCursor) # 默认为元组,指定返回值是字典
"""表结构
sql = "desc t2"
cr.execute(sql)
print(cr.fetchall())
[{'Field': 'id', 'Type': 'int(11)', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': 'auto_increment'},
{'Field': 'uesr', 'Type': 'char(255)', 'Null': 'NO', 'Key': '', 'Default': None, 'Extra': ''},
{'Field': 'password', 'Type': 'char(255)', 'Null': 'NO', 'Key': '', 'Default': None, 'Extra': ''}]
"""
# 增
sql = "insert into t2 values(%s,%s,%s)"
"""
print(cr.execute(sql,(4,"ceshi1","111"))) #一次增加一条
print(cr.executemany(sql,[
(5,"ceshi2","111"),
(6,"ceshi3","111"),
(7,"ceshi4","111")])) #一次增加多条
cr.execute("commit")
"""
# 删
sql = "delete from t2 where id = %s"
"""
多条删除
print(cr.executemany(sql,(5,6,7)))
cr.execute("commit")
"""
# 改
sql = "update t2 set uesr = %s,password = %s where id = %s"
"""
print(cr.execute(sql,("wahaha","555",5)))
cr.execute("commit")
"""
# 查
# fetchone fetchmany fetchall 都是基于上一条数据往下查询.
"""
sql = "select * from t2"
print(cr.execute(sql)) # 总条数
res = cr.fetchall()
print(res) # 所有数据
"""
# 自定义查询位置
sql = "select * from t2 where id >= 2"
cr.execute(sql)
"""
res = cr.fetchall()
[{'id': 2, 'uesr': 'baozhu', 'password': '123456'},
{'id': 3, 'uesr': 'meizhu', 'password': '123456'},
{'id': 5, 'uesr': 'wahaha', 'password': '555'},
{'id': 6, 'uesr': 'ceshi3', 'password': '111'},
{'id': 7, 'uesr': 'ceshi4', 'password': '111'}]
print(res)
"""
# 1,相对滚动(正数相对于当前位置往后滚,负数反之.)
cr.scroll(1,mode="relative") # 相对于2往后数 1 个
res = cr.fetchone() # 相当于 1 + 1
print(res) # {'id': 3, 'uesr': 'meizhu', 'password': '123456'}
cr.scroll(1,mode="relative") # 相对于3往后数 1 个
res = cr.fetchone() # 相当于 1 + 1
print(res) # {'id': 6, 'uesr': 'ceshi3', 'password': '111'}
"""
"""
# 2.绝对滚动 , 永远基于第一条数据的位置进行移动
cr.scroll(1,mode="absolute") # +1 条