准备数据
创建数据表
-- 创建 "京东" 数据库
create database jing_dong charset=utf8;
-- 使用 "京东" 数据库
use jing_dong;
-- 创建一个商品goods数据表
create table goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);
插入数据
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux )','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
SQL演练
SQL语句的强化
- 查询类型cate_name为 '超极本' 的商品名称、价格
select name,price from goods where cate_name = '超级本';
- 显示商品的种类
select cate_name from goods group by cate_name;
- 求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) as avg_price from goods;
- 显示每种商品的平均价格
select cate_name,avg(price) from goods group by cate_name;
- 查询每种类型的商品中 最贵、最便宜、平均价、数量
select cate_name,max(price),min(price),avg(price),count(*) from goods group b
- 查询所有价格大于平均价格的商品,并且按价格降序排序
select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc;
- 查询每种类型中最贵的电脑信息
select * from goods
inner join
(
select cate_name,
max(price) as max_price;
min(price) as min_price;
avg(price) as avg_price;
count(*) from goods group by cate_name
) as goods_new_info
on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max(price)
创建‘商品分类’类
- 创建商品分类表
-- 创建商品分类表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
if not exists表示如果之前没有这个表那么就新建这个表,如果之前已经存在了这张表就不再新建了
- 查询goods表中的种类
select cate_name from goods group by cate_name;
- 将分组结果写入到goods_cates数据表
insert into goods_cates (name) select cate_name from goods group by cate_name;
同步表数据
- 通过good_cates数据表来更新goods表
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
创建商品品牌表
- 通过create...select来创建数据表并且同时写入记录,一步到位
select brand_name from goods group by grand_name;
--在创建数据表的时候一起插入数据
--注意:需要对brand_name用as起别名,否则name字段就没有值
create table good_brands(
id int unsigned primary key auto_increment,
name varchar(40) not null select brand_name as name from goods group by brand_name
);
同步数据
- 通过goods_brand数据表来更新goods数据表
update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;
修改表结构
- 查看goods的数据表结构,会发现cate_name和brand_name对应的类型为varchar但是存储的数字
- 通过alter table语句修改表结构
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;
外键
- 分别在goods_cates和goods_brands表中插入记录
insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');
insert into goods_brands(name) values ('海尔'),('清华同方'),('神舟');
- 在goods数据表中写入任意记录
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');
如果运行之后可以看到goods中的cate_id并不是符合good_cates中的描述,brand_id也和goods_brands中的信息不符,那么如何解决这个问题呢?外键约束就是对数据的有效性进行约束,防止无效信息的随意插入
-- 给brand_id 添加外键约束成功
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 给cate_id 添加外键失败
-- 会出现1452错误
-- 错误原因:已经添加了一个不存在的cate_id值12,因此需要先删除
alter table goods add foreign key (cate_id) references goods_cates(id);
上面这段代码表示增加一个字段,references表示与其他表的哪个字段相关
如何在创建数据表的时候就设置外键约束呢?
create table goods(
id int primary key auto_increment not null,
name varchar(40) default '',
price decimal(5,2),
cate_id int unsigned,
brand_id int unsigned,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(id),
foreign key(brand_id) references goods_brands(id)
);
如何取消外键
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
在实际开发中,很少会用到外键约束,会极大的降低表更新的效率
Python操作MySQL步骤
引入模块
- 在py文件中引入pymysql模块
from pymysql import *
connection对象
- 用于创建于数据库的连接
- 创建对象:调用connect方法
conn = connection(参数列表)
- 参数host:连接的mysql主机,如果连接本机是'localhost'
- 参数port:连接的mysql主机的端口,默认是3306
- 参数database:数据库的名称
- 参数user:连接的用户名
- 参数password:连接的密码
- 参数charset:通信采用的编码方式,推荐使用utf8
对象的方法
- close()关闭连接
- commit()提交
- cursor()返回Cursor对象,用于执行sql语句并获得结果
Cursor对象
- 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
- 获取Cursor对象:调用Connection对象的cursor()方法
cs1 = conn.cursor()
对象的方法
- close()关闭
- execute(operation [, parameters ])执行语句,返回受影响的行数,主要用于执行insert、update、delete语句,也可以执行create、alter、drop等语句
- fetchone()执行查询语句时,获取查询结果集的第一个行数据,返回一个元组
- fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回
对象的属性
- rowcount只读属性,表示最近一次execute()执行后受影响的行数
- connection获得当前连接对象
增删改
from pymysql import *
def main():
# 创建Connection连接
conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()
# 执行insert语句,并返回受影响的行数:添加一条数据
# 增加
count = cs1.execute('insert into goods_cates(name) values("硬盘")')
#打印受影响的行数
print(count)
count = cs1.execute('insert into goods_cates(name) values("光盘")')
print(count)
# # 更新
# count = cs1.execute('update goods_cates set name="机械硬盘" where name="硬盘"')
# # 删除
# count = cs1.execute('delete from goods_cates where id=6')
# 提交之前的操作,如果之前已经之执行过多次的execute,那么就都进行提交
conn.commit()
# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()
if __name__ == '__main__':
main()
查询一行数据
from pymysql import *
def main():
# 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()
# 执行select语句,并返回受影响的行数:查询一条数据
count = cs1.execute('select id,name from goods where id>=4')
# 打印受影响的行数
print("查询到%d条数据:" % count)
for i in range(count):
# 获取查询的结果
result = cs1.fetchone()
# 打印查询的结果
print(result)
# 获取查询的结果
# 关闭Cursor对象
cs1.close()
conn.close()
if __name__ == '__main__':
main()
查询多行数据
from pymysql import *
def main():
# 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()
# 执行select语句,并返回受影响的行数:查询一条数据
count = cs1.execute('select id,name from goods where id>=4')
# 打印受影响的行数
print("查询到%d条数据:" % count)
# for i in range(count):
# # 获取查询的结果
# result = cs1.fetchone()
# # 打印查询的结果
# print(result)
# # 获取查询的结果
result = cs1.fetchall()
print(result)
# 关闭Cursor对象
cs1.close()
conn.close()
if __name__ == '__main__':
main()
参数化
sql注入:所谓sql注入,就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。
- sql语句的参数化,可以有效防止sql注入
- 注意:此处不同于Python的字符串格式化,全部用%s占位
from pymysql import *
def main():
find_name = input("请输入物品名称:")
# 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()
# # 非安全的方式
# # 输入 " or 1=1 or " (双引号也要输入)
# sql = 'select * from goods where name="%s"' % find_name
# print("""sql===>%s<====""" % sql)
# # 执行select语句,并返回受影响的行数:查询所有数据
# count = cs1.execute(sql)
# 安全的方式
# 构造参数列表
params = [find_name]
# 执行select语句,并返回受影响的行数:查询所有数据
count = cs1.execute('select * from goods where name=%s', params)
# 注意:
# 如果要是有多个参数,需要进行参数化
# 那么params = [数值1, 数值2....],此时sql语句中有多个%s即可
# 打印受影响的行数
print(count)
# 获取查询的结果
# result = cs1.fetchone()
result = cs1.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()
if __name__ == '__main__':
main()
视图
对于复杂的查询,往往有多个数据表进行关联查询而得到,如果数据库因为需求等原因发生了改变,为了保住查询出来的数据与之前相同,则需要在多个地方进行修改,维护起来非常麻烦
解决方法:定义视图
通俗的讲,视图就是一条select语句执行后返回的结果集。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。视图是对若干基本表的引用,一张虚表,查询语句执行的结果,不存在具体的数据(基本表数据发生了改变,视图也会跟着改变)
- 定义视图,建议以v_开头
create view 视图名称 as select语句;
- 查看视图,查看表会将所有的视图也列出来
show tables;
- 视图的用处就是查询
select * from v_stu_score;
- 删除视图
drop view 视图名称;
例:
drop view v_stu_sco;
事务
我们在终端直接输入sql语句,python解释器默认会调用commit语句,但是我们如果定义一个事务,在执行完所有操作的时候调用commit语句的时候才真正将所有的数据提交上去
事务广泛的运用于订单系统、银行系统等多种场景
例如,A用户和B用户是银行的储户,现在A要给B转账500元,那么需要做以下几件事:
- 检查A的账户余额>500元
- A账户中扣除500元
- B账户中增加500元
正常流程走下来,A账户扣了500元,B账户增加了500元,以上的案例中,隐藏着一个前提条件:A扣钱和B加钱,要么同时成功,要么同时失败,事务的需求就在于此
所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,他是一个不可分割的工作单位
事务的四大特性(简称ACID)
- 原子性
- 一致性
- 隔离性
- 持久性
下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表:支票表(checking)和储蓄表(savings)。现在要从用户Jane的支票账户转移200美元到她的储蓄账户,那么至少需要三个步骤:
- 检查支票账户的余额高于或者等于200美元。
- 从支票账户余额中减去200美元。
- 在储蓄帐户余额中增加200美元。
上述三个步骤的操作必须打包在一个事务中,任何一个步骤失败,则必须回滚所有的步骤
可用START TRANSACTION语句开始一个事务,然后要么使用COMMIT提交将修改的数据持久保存,要么使用ROLLBACK撤销所有的修改,事务的SQL的样本如下:
- start transaction;
- select balance from checking where customer_id = 10233276;
- update checking set balance = balance - 200.00 where customer_id = 10233276;
- update savings set balance = balance + 200.00 where customer_id = 10233276;
- commit;
一个很好的事务处理系统,必须具备这些标准特征:
- 原子性(atomicity)
一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性
- 一致性(consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态。(在前面的例子中,一致性确保了,即使在执行第三、四条语句之间时系统崩溃,支票账户中也不会损失200美元,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。)
- 隔离性(isolation)
当一个事务所作的修改在正式提交之前,是无法对这个数据表中正在进行修改的数据进行操作的
通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。(在前面的例子中,当执行完第三条语句、第四条语句还未开始时,此时有另外的一个账户汇总程序开始运行,则其看到支票帐户的余额并没有被减去200美元。)
- 持久性(durability)
一旦事务提交,则其所做的修改会永久保存到数据库。(此时即使系统崩溃,修改的数据也不会丢失。)
事务命令
- 开启事务后执行命令,变更会维护到本地缓存中,而不维护到物理表中
begin;
或者
start transaction;
- 提交事务,将缓存中的数据变更维护到物理表中
commit;
- 回滚事务,放弃缓存中所有变更的数据
rollback;
注意:
- 修改数据的命令会自动的触发事务,包括insert、update、delete
- 而在SQL语句中有手动开启事务的原因是:可以进行多次数据的修改,如果成功一起成功,否则一起会滚到之前的数据
提交
为了进行演示,需要打开两个终端,使用同一个数据库,操作同一张表
- 终端1:查询商品分类信息
- 终端2:增加数据,开启数据,插入数据
begin;
insert into good_cates(name) values('小霸王游戏机');
- 终端2:查询商品信息,发现有新增的数据
- 然后打开终端1,查询数据发现并没有新增数据
- 终端2commit提交,然后终端1再查询发现有新增的数据
回滚
回滚是回滚到整个事务开始时候的状态,而并不是仅仅回滚到上一步的状态
- 终端1:查询商品的分类信息
- 终端2:增加数据,查询数据,发现有新增的数据
- 终端1:查询数据,发现没有新增数据
- 终端2回滚,rollback,然后回到终端1发现没有新增的数据
索引
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度
索引的使用
- 查看索引
show index from 表名;
- 创建索引
- 如果指定字段是字符串,需要指定长度,建议长度和定义字段时长度一致
- 字段类型如果不是字符串,可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度));
- 删除索引
drop index 索引名称 on 表名;
索引Demo
创建测试样例
create table test_index(title varchar(10))
使用Python程序,向表中增加一万条数据
from pymysql import connect
def main():
# 创建Connection连接
conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
# 获得Cursor对象
cursor = conn.cursor()
# 插入10万次数据
for i in range(10000):
cursor.execute("insert into test_index values('ha-%d')" % i)
# 提交数据
conn.commit()
if __name__ == "__main__":
main()
查询
- 开启运行时间检测
set profiling=1;
- 查找第一万条数据
select * from test_index where title='ha-9999';
- 查看执行时间
show profiles;
- 为表test_index的title列创建索引
create index title_index on test_index(title(10));
- 再次查看执行时间
show profiles;
看到的结果如下:
从运行结果可以看到,在新增了索引之后运行时间大大减少,这就是创建索引的作用
注意:
要注意的是,建立太多的索引会影响更新和插入的速度,因为它需要同样更新每一个索引文件,对于很小的表,排序的开销不会太大,也没有必要建立另外的索引。建立索引会占用磁盘空间。
账户管理(了解)
- 在生产环境下操作数据库时,绝对不可能使用root账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要就是数据的crud
- MySQL账户体系:根据账户所具有的权限的不同,MySQL的账户可以分为以下几种:
- 服务实例级账号:,启动了一个mysqld,即为一个数据库实例;如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库、连同这些库中的表
- 数据库级别账号:对特定数据库执行增删改查的所有操作
- 数据表级别账号:对特定表执行增删改查等所有操作
- 字段级别的权限:对某些表的特定字段进行操作
- 存储程序级别的账号:对存储程序进行增删改查的操作
- 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等
- 进行账户操作时,需要以root账户登录,这个账户拥有最高的实例级权限,通常都是数据库操作权限
授予权限
需要使用实例级账户登录后操作,以root为例,主要操作就是:
- 查看所有用户
- 修改密码
- 删除用户
查看所有用户
- 所有用户及权限信息存储在mysql数据库中的user表中
- 查看user表的结构
desc user;
- 主要字段说明
- Host表示允许访问的主机
- User表示用户名
- authentication_string表示密码,为加密后的值
查看所有用户
select host,user,authentication_string from user;
创建账户、授权
- 需要使用实例级账户登录后操作,以root操作
- 常用的权限包括:create、alter、drop、insert、update、delete、select
- 如果分配所有权限,可以使用all privileges
创建账户&授权
grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
示例
创建一个laowang的账号,密码为123456,只能通过本地访问,并且只能对jing_dong数据库中的所有表进行读操作
- 使用root登录
mysql -uroot -p
回车后写密码,然后回车
- 创建账户并授予所有权限
grand select on jing_dong.* to 'laowang'@'localhost' identified by '密码';
说明
- 必须定义到数据库中的表,选中数据库中的所有表,方式为:jing_dong.*
- 访问主机通常使用百分号%表示此账户可以使用任何IP的主机登录访问此数据库
- 访问主机可以设置成localhost或具体的IP,表示只允许本机或者待定主机访问
- 查看用户有哪些权限
show grants for laowang@localhost;
- 退出root的登录然后使用laowang账户登录
mysql -ulaowang -p
然后输入密码,再回车
示例2
创建一个laoli的账号,密码为12345678,可以任意电脑进行连接访问,并且对jing_dong数据库中的所有表拥有所有权限
grand all privileges on jing_dong.* to 'laoli'@'%' identified by '12345678';
然后可以执行增删改查的操作
账户操作
修改权限
grand 权限名称 on 数据库 to 账户@主机 with grand option;
# 刷新权限
flush privileges;
修改密码
使用root登录,修改mysql数据库的user表
- 使用password()函数进行密码加密
update user set authentication_string=password('新密码') where user='用户名';
例:
update user set authentication_string=password('123') where user='laowang';
- 注意修改完之后需要刷新权限
flush privileges;
删除账户
- 语法1:使用root登录
drop user '用户名'@'主机';
例:
drop user 'laowang'@'%';
- 语法2:使用root登录,删除mysql数据库的user表中数据
delete from user where user='用户名';
例:
delete from user where user='laowang';
-- 操作结束之后需要刷新权限
flush privileges
推荐使用语法1删除用户,如果使用语法1失败,然后使用语法2