mysql第一天: 知道使用一张表可以实现自连接查询
1.能够知道数据库设计三范式
1NF: 不能拆分
2NF: 不能部分依赖主键，应该全部依赖
3NF: 不能传递依赖
2.知道drop foreign key可以删除外键约束
alter table sutdents drop foreign key 外键名称
3.能够写出分组和聚合函数组合使用的SQL语句
select gender, avg(age), max(age), min(age) from students group by gender;
4.能够写出将查询结果插入到其它表中的SQL语句
复制表:
insert into tbl_gender (name) select distinct gender from students;
5.能够知道使用连接更新表中某个字段数据的SQL语句
6.能够写出创建表并给某个字段添加数据的SQL语句
7.能够写出修改表结构的SQL语句
alter table students change cate_name cate_id int unsigned;
8.能够使用PyMySQL完成数据库的增删改查
import pymysql
conn
cursor
cursor.execute(sql)
cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
9.能够知道事务的四大特性
原子性
一致性
隔离性
持久性
10.能够写出创建索引的SQL语句
alter table students add index 索引名字 (字段列表);
mysql第一天: 自连接
-- 省市区三级联动
-- 数据操作前的准备
-- 创建数据库表
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
-- 从sql文件中导入数据
-- source 具体地址/areas.sql;
-- 先把areas.sql复制到桌面目录下
source /home/python/Desktop/areas.sql
-- 查询一共有多少个省
-- select * from areas where pid is null;
-- select count(*) from areas where pid is null;
select count(*) from areas where pid is null;
-- 例1：查询省的名称为“山西省”的所有城市
-- 查询山西省的所有城市。
-- 1、先查山西省id
select aid from areas where atitle='山西省';
-- select * from areas where pid = 山西省aid
select * from areas where pid = 140000;
select * from areas where pid = (select aid from areas where atitle='山西省');
-- 例2：查询市的名称为“广州市”的所有区县:
-- 1、先查广州市的id
select aid from areas where atitle='广州市';
-- 2、再查pid等于广州id的区县
select * from areas where pid = 440100;
select * from areas where pid = (select aid from areas where atitle='广州市');
-- 自连接, 使用内连接查询广州市所有的区县，显示广州市和区县的信息。
select * from areas as a1 inner join areas as a2 on a1.aid = a2.pid where a1.atitle='广州市';
外键约束的作用: 对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性
添加外键约束:
alter table 从表 add foreign key(外键字段) references 主表(主键字段);
删除外键约束:
alter table 表名 drop foreign key 外键名;
-- 目前我们可以在students中插入cls_id为10的记录:
insert into students(name,cls_id) values("老王", 10);
-- 但是这并不符合实际！所以删除：delete from students where id=15;
-- 我们需要外键约束来使到数据库识别这个数据是否有效 (保证数据的有效性)
-- 约束 数据的插入 使用 外键 foreign key
-- alter table 主表名 add foreign key(主表的外键cls_id) references 外键表(外键主键);
-- 给cls_id字段添加外键约束
-- alter table students add foreign key(cls_id) references classes(id);
alter table students add foreign key(cls_id) references classes(id);
-- 添加外键约束的时候会报错，需要先把学生中的cls_id都设置为classes存在的班级。
update students set cls_id=2 where cls_id > 2;
-- 添加外键之后：再次插入cate_id为10的记录，就报错了
insert into students (name,cls_id) values ('佳燕', 18);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`python_test_12`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`cls_id`) REFERENCES `classes` (`id`))
-- 如果想要在创建表的同时设置外键 (classes表必须先存在)
-- foreign key (主表的外键) references 外键表(主键)
-- create table students(
-- id int unsigned primary key auto_increment not null,
-- name varchar(20) default '',
-- age tinyint unsigned default 0,
-- height decimal(5,2),
-- gender enum('男','女','中性','保密') default '保密',
-- cls_id int unsigned default 0,
-- is_delete bit default 0,
-- foreign key (cls_id) references classes(id)
-- );
create table students(
id int unsigned primary key auto_increment,
name varchar(20) not null,
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned not null,
is_delete bit default 0,
foreign key (cls_id) references classes(id)
);
-- 如何取消外键约束(2个步骤)
-- 1、需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称(xxxxxx_ibfk_1)
show create table students;
-- 2、获取名称之后就可以根据名称来删除外键约束
-- alter table 表名 drop foreign key xxxxxx_ibfk_1;
alter table students drop foreign key students_ibfk_1;
-- 3、(可以不做)如果要去掉表结构中的"MUL"标记：
-- alter table 表名 drop key 字段名;
alter table students drop key cls_id;
3.jing_dong数据库演练
-- 准备数据 复制课件11.10中的数据准备
-- sql强化演练( goods 表练习)
-- 查询类型 cate_name 为 '超级本' 的商品名称 name 、价格 price
select name, price from goods where cate_name='超级本';
-- 显示商品的种类
-- 1 分组的方式( group by )
select cate_name from goods group by cate_name;
-- 2 去重的方法( distinct )
select distinct cate_name from goods;
-- 求所有电脑产品的平均价格 avg ,并且保留两位小数( round )
select round(avg(price), 2) from goods;
-- 显示 每种类型 cate_name 的 平均价格
select avg(price), cate_name from goods group by cate_name;
-- 查询 每种类型 的商品中 最贵 max 、最便宜 min 、平均价 avg 、数量 count
select max(price), min(price), avg(price), count(*), cate_name from goods group by cate_name;
-- 查询所有价格大于 平均价格 的商品，并且按 价格降序 排序
-- 1.先查询平均价格
select avg(price) from goods;
-- 2.再查询大于平均价格的商品
select * from goods where price > 5570.5714286 order by price desc;
-- 整合
select * from goods where price > (select avg(price) from goods) order by price desc;
4.复制表-将查询结果插入到其他表中
1.存在表的情况下复制。
复制表语法
insert into .. select .. SQL语句
创建跟goods表结构一样的goods_copy
1.获取创建goods的sql语句
show create table goods;
2.把表名改成goods_copy, 并生成表
完整拷贝：
insert into goods_copy select * from goods;
部分拷贝：
insert into goods_copy (name, cate_name, brand_name) select name, cate_name, brand_name from goods;
2.创建表立刻复制
CREATE TABLE `goods_copy` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`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.000',
`is_show` bit(1) NOT NULL DEFAULT b'1',
`is_saleoff` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 select * from goods;
-- 创建分类表:
create table goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
-- 从goods表中拷贝分类信息:
insert into goods_cates(name) select distinct cate_name from goods;
insert into goods_cates(name) select cate_name from goods group by cate_name;
-- 连接查询 inner join (内连接)
select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;
-- 修改cate_name的值为对应的cate_id
update goods as g inner join goods_cates as gc on g.cate_name = gc.name set g.cate_name = gc.id;
select * from goods inner join goods_cates on goods.cate_name = goods_cates.id;
-- 修改cate_name字段 改成cate_id
alter table goods change cate_name cate_id int unsigned;
7.jing_dong商品表-添加品牌表
-- 创建品牌表, 并直接从商品表中拷贝品牌数据:
create table goods_brands(
id int unsigned primary key auto_increment,
name varchar(40) not null
) select brand_name as name from goods group by brand_name;
-- 连接查询
select * from goods as g inner join goods_brands as gb on g.brand_name=gb.name;
-- 修改brand_name的值为对应的brand_id
update goods as g inner join goods_brands as gb on g.brand_name=gb.name set g.brand_name = gb.id;
select * from goods as g inner join goods_brands as gb on g.brand_name=gb.id;
-- 修改brand_name字段 改成brand_id;
alter table goods change brand_name brand_id int unsigned;
d04_pymysql_update.py
sql ='update students set age=19 where name="老王";'
count = cursor.execute(sql)# 提交，增删改操作都需要提交才会生效
conn.commit()
12.使用pymysql删除数据
d05_pymysql_delete.py
sql ='delete from students where name="老王";'
count = cursor.execute(sql)# 提交，增删改操作都需要提交才会生效
conn.commit()
13.sql注入&防止
d06_pymysql_sql_injection.py
需求：查询名字等于输入字符串的学生信息
业务流程:1.获取输入
name =input('请输入学生姓名:')2.组装sql
sql ='select * from students where name = "%s"'%(name)3.查询，返回结果
count = cursor.execute(sql)for i in cursor.fetchall():print(i)
sql注入:
老王" or 1=1 or "
防止注入:1.获取输入
name =input('请输入学生姓名:')2.使用pymysql提供的方式，组装sql
sql ='select * from students where name = %s'3.把参数列表传给execute，让pymysql帮我们阻止sql注入问题
cursor.excute(sql,[name])
14.事务-转账
d07_transaction.py
先来看一个例子:
需求:
小明给小芳转100块钱。
1.创建表
create table account(idint unsigned primary key auto_increment,
name varchar(40)not null,
money int unsigned not null default 0);2.插入测试数据
insert into account values(0,'小明',100),(0,'小芳',100);3.转账
update account set money = money+100 where name ='小芳';
update account set money = money-100 where name ='小明';
问题:
只要有一条语句执行失败，总金额都不对了，就失去了总金额的一致性。
解释: 事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。
使用步骤:1.开启事务
begin;2.执行sql语句
update account set money = money+100 where name ='小芳';
update account set money = money-100 where name ='小明';3.提交事务/回滚事务
commit;/rollback;
使用pymysql演示
# 小明给小芳转账try:
sql ='update account set money = money+100 where name = "小芳";'
cursor.execute(sql)
sql ='update account set money = money-100 where name = "小明";'
cursor.execute(sql)
conn.commit()except:
conn.rollback()
MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作
当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该事务结束。
set autocommit =0;
insert into students(name) values('刘三峰');-- 需要执行手动提交，数据才会真正添加到表中, 验证的话需要重新打开一个连接窗口查看表的数据信息
commit
解释：表的存储引擎就是提供存储数据一种机制，不同表的存储引擎提供不同的存储机制。
InnoDB:
1.InnoDB 是mysql的默认存储引擎
2.InnoDB 是支持事务
MyISAM:
MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表
查看mysql支持的所有存储引擎
show engines;
查看表的存储引擎
show create table account;
修改表的存储引擎
alter table account engine = 'MyISAM';
修改后，转账就不能保证一致性了。
17.索引
解释: 索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
作用: 提高数据库的查询速度。
查看表中已有索引:
show index from 表名;
创建索引:
-- 索引名不指定，默认使用字段名
alter table classes add index 索引名字 (字段);
删除索引:
alter table classes drop index 索引名字;
验证索引作用:
1.创建测试表testindex:
create table test_index(title varchar(10));
2.向表中插入十万条数据:
from pymysql import connect
def main():
# 创建Connection连接
conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
# 获得Cursor对象
cursor = conn.cursor()
# 插入10万次数据
for i in range(100000):
cursor.execute("insert into test_index values('ha-%d')" % i)
# 提交数据
conn.commit()
if __name__ == "__main__":
main()
3.验证索引性能操作：
-- 开启运行时间监测：
set profiling=1;
-- 查找第1万条数据ha-99999
select * from test_index where title='ha-99999';
-- 查看执行的时间：
show profiles;
-- 给title字段创建索引：
alter table test_index add index (title);
-- 再次执行查询语句
select * from test_index where title='ha-99999';
-- 再次查看执行的时间
show profiles;
18.联合索引
解释：联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。
联合索引的好处(相对普通索引而言):
减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。
-- 创建联合索引
alter table students add index name_age (name,age);
-- 删除联合索引
alter table students drop index name_age;
联合索引的最左原则:
在使用联合索引的时候，我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询，因为没有用到创建的联合索引。
-- 下面的查询使用到了联合索引
select * from students where name='张三' -- 这里使用了联合索引的name部分
select * from students where name='李四' and age=10 -- 这里完整的使用联合索引，包括 name 和 age 部分
-- 下面的查询没有使用到联合索引
select * from students where age=10
19.索引总结
优点：
加快数据的查询速度
缺点：
创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加
使用原则：
通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
数据量小的表最好不要使用索引(2000)，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。
索引是加快数据库的查询速度的一种手段
创建索引使用: alter table 表名 add index 索引名[可选] (字段名, xxx);
删除索引使用: alter table 表名 drop index 索引名;
20.mysql数据库总结
1.mysql是关系型数据库
2.mysql分为客户端和服务端。
3.数据库操作命令
create database 数据库 charset=utf8; -- 创建数据库
drop database 数据库; -- 删除数据库
4.数据表结构操作
alter table 表名 add 列名 数据类型 约束; -- 添加列
alter table 表名 change 列名 数据类型 约束; -- 修改列
alter table 表名 drop 列名; -- 删除
外键约束:
alter table 从表 add foreign key(外键字段) references 外键表(主键字段); -- 创建外键
alter table 从表 drop foreign key 外键名; -- 删除外键
索引:
alter table 表名 add index 索引名 (字段列表); -- 添加索引
alter table 表名 drop index 索引名; -- 删除索引
唯一索引
alter table 表名 add unique(字段列表);
5.表的curd
insert into 表名(字段列表) values(值列表); -- 增加， 注意值要跟字段列表一一对应。
delete from 表名 where 条件; -- 删除
update 表名 set 字段=值, 字段2=值2 where 条件; -- 修改
select 显示列 from 表名 where 条件; -- 查询
6.排序
order by 列1(asc/desc), 列2(asc/desc);
7.分页
limit(放在最后) start(偏移量), count(返回数量)
分页公式:
每页返回10条数据，返回第n页数据:
数据 = limit 10*(n-1), 10
8.聚合函数:
count(), avg(), sum(), max(), min(), group_concat()
9.分组查询:
select 分组字段, 聚合函数 from 表 group by 分组字段;
10.连接查询
-- 内连接 (交集)(重点)
select 表1.字段, 表2.字段 from 表1 inner join 表2 on 表1.xx=表2.xx(关联条件);
-- 左连接 (显示左表所有的数据，右表没有的以null填充)
select 表1.字段, 表2.字段 from 表1 left join 表2 on 表1.xx=表2.xx(关联条件);
-- 右连接 (显示右表所有的数据，左表没有的以null填充)
select 表1.字段, 表2.字段 from 表1 right join 表2 on 表1.xx=表2.xx(关联条件);
11.事务
begin;
sql语句
commit;/rollback;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
