sql笔记，面试

排名函数
rank() dense_rank() row_number()

limit 3,2 从第四条数据开始，取两条数据

mysql 关系型数据库
MySQL 基本使用

数据库是特殊文件，用特殊软件操作
主键：能够唯一标记某个字段
字段：列
记录：行
mysql 网站，关系型数据库 Redis 缓存 Mongodb 非关系型数据库
通过客户端发明语言操作数据库管理系统，从而给出结果（控制）
SQL 是结构化查询语言，是一种用来操作 RDBMS 的数据库语言
主要成分：增删改查
DQL：数据查询语言，用于对数据进行查询 ，如 select
DML：数据操作语言，对数据进行增加，修改，删除如 insert，update，delete
特点：
可移植，开源，支持多线程，充分利用 CPU 资源
定义表：类型，约束
常用数据类型：
整型：int，bit， 小数：decimal 字符串：varchar（选择） char
日期时间：date time datetime
枚举类型：enum 可能出现的结果列举出来
字符串 text 类型，字符大于 4000 推荐使用
对于图片，音频，视频等文件，不存储在数据库里面，而是上传到某个服务器内，
存的是图片在电脑上的路径
参考：https://blog.csdn.net/anxpp/article/details/51284106
类型来验证数据
约束：
primary key not null unique default foreign kry
外键：一个表存储的字段是另一个表的主键，限制必须存在（外键不要太多）
链接数据库，进入命令字符
退出：exit quit
查看所有数据库：、show databases、
显示当前时间：select now（）；
创建数据库：create databases ** charset=utf8;
查看创建当前数据库的语句 show create database python04;
删除数据库：drop database **;
创建数据库，使用数据库 use **;，然后创建数据表
查看当前使用数据库：select database;数据表的操作：
查看当前数据库所有表：show tables;
创建数据表：
create table **(字段名，类型，约束);
create table xxxx(id int, name varchar(30));
create table yyyy(
id int primary key not null auto_increment,
name varchar(30));
create table zzzz(
id int primary key not null auto_increment,
name varchar(30));
desc 查看表的结构：desc xxxx;
创建 student 表：
create table students(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum(“男”,”女”,”中性”,”保密”) default “保密”,
cls_id int unsigned
);
给数据库里面插入值：
insert into student values(0,”老王”,18,188.88，”男”,0);
查询数据：select * from students;
创建一个班级表：
create table classes(
id int unsigned not null auto_increment primary key,
name varchar(30)
);
插入数据
insert into classes values(0,“python04 大神”);
查询数据
select * from classes;
修改表结构：
添加：alter table 表名 add
alter table students add birthday datetime;
修改:
不重命名：alter table students （表名）modify （列名，类型，约束）birthday
date;
重命名：alter table students （表名）change（原名，新名，类型，约束）birthday
birth date default “1997-06-19”;alter table students change birthday birth date default “2000-01-01”;
删除:
alter table students drop high;
drop table 表名；
drop database 数据库；
查看创建数据表：
查看表的创建数据
show create table students;
insert into students values (0,“老李”,19,“女”,12321,“2010-01-01”);加数据
数据的增删改查：
curd：create use retrieve delete
插入数据：
insert into classes values(0,“菜鸟班”);
没有指明在哪个字段插入，这个时候必须是全部插入
向 student 表中插入信息
insert into students values (0,“小李飞刀”,20,“男”,1,“1990-01-01”);
主键字段可以用 0，null ，default 来占位
失败：
insert into students values (default,“小李飞刀”,20,“第 4 性别”,1,“1990-01-01”);
–枚举中的下标从 1 开始，1 对应男。。。
但可以插入数字
insert into students values (default,“小李飞刀”,20,“3”,1,“1990-01-01”);
–部分插入
–insert into 表名（列 1，… ）values（值 1，…）
insert into students(name,gender) values (“小乔”,“2”);
–多行插入
insert into students(name,gender) values (“大乔”,“2”),(“貂蝉”,“2”);
insert into students values (default,“西施”,20,“2”,1,“1990-01-01”),(default,"王昭君
",20,“女”,1,“1990-01-01”);
–修改
update 表名 set 列 1=值 1，列 2=值 2.。。where 条件;
update students set gender = 2 where id = 4; 对应唯一 id 修改一个
update students set gender = 2 where name = "小李飞刀 "; 对应所有 name 成立
的全部修改
–一行中多个值被修改
update students set age=22,gender=2 where id = 4;–查询
–查询所有列
select * from students;
–指定条件查询
select * from students where name=“小李飞刀”;查询 name 为小李飞刀所有信息
select * from students where id>6;
–查询指定列
select 列 1，列 2 from 表名；
select name,gender from students;
–可以用 as 为列或表指定列名
–select 字段（as 别名），字段（as 别名） from 数据表 where；
select name as 姓名,gender as 性别 from students;
–字段的顺序（交换即可）
select gender as 性别,name as 姓名 from students;
–删除数据
delete from 表名 where 条件（物理删除）
–全部删除
delete from students;
–按指定条件删除
delete from students where name=“小李飞刀”;
–逻辑删除（轻柔）
–给 students 表添加一个 is_delete 字段 bit（0/1）类型
alter table students add is_delete bit default 0;
select * from students where is_delete=0;
update students set is_delete=1 where id=12;MySQL 查询

查询（实际中用的最多）
基本查询
条件/排序/聚合函数/分组/分页/连接查询/（多表）自关联/子查询
–数据准备
数据库通俗来说是特殊的存储数据的方式，真正严格意义是特殊文件
数据库里面有各种数据表组成，数据表由字段和记录组成
–创建一个数据库
create database python_test charset=utf8;
–使用数据库
use python_test;
–显示使用当前数据库：
select database(); 如果想要得到一个信息，一定是 select
–创建一个数据表
create table students(
id int unsigned not null auto_increment primary key,
name varchar(20) default ‘’,
age tinyint unsigned default 0,
high decimal(5,2),
gender enum(“男”,“女”,“中性”,“保密”) default “保密”,
cls_id int unsigned default 0,
is_delete bit default 0
);
auto_increment 自动增长 enum 枚举 只写 bit，一个 bit 存 0,1
如果 bit(2)可以存 0,1,2,3（00,01,10,11）
如果用 10,20 50 之内的问题，那就直接选择 tinyint
–创建 classes 表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
–查看是如何创建的（忘记是怎么创建的，就可以查看）
show create table students;
–准备数据
–向 student 表中插入数据
insert into students VALUES
(0,‘小明’,18,180.00,2,1,0),
(0,‘小月月’,18,180.00,2,2,1),(0,‘彭于晏’,29,185.00,1,1,0),
(0,‘刘德华’,59,175.00,1,2,1),
(0,‘黄蓉’,38,160.00,2,1,0),
(0,‘凤姐’,28,150.00,4,2,1),
(0,‘王祖贤’,18,172.00,2,1,1),
(0,‘周杰伦’,36,null,1,1,0),
(0,‘陈坤’,27,181.00,1,2,0),
(0,‘刘亦菲’,25,166.00,2,2,0),
(0,‘金星’,33,162.00,3,3,1),
(0,‘静香’,12,180.00,2,4,0),
(0,‘郭靖’,12,170.00,1,4,0),
(0,‘周杰’,34,176.00,2,5,0);
–插入两个班级
‘python_01 期’),(0,‘python_02 期’),(0,‘python_04 期’);
数据添加完毕
–查询
–查询所有字段
select * from students;
select * from classes;
select id,name from classes;
–查询指定字段
select name,age from students;
–使用 as 给字段起名
select name as 姓名,age as 年龄 from students;
–通过 as 给表起名
select students.name,students.age from students;
select s.name,s.age from students as s;
–去重
distinct 字段
select distinct gender from students;
–条件查询
–比较运算符（> < = !=）
-查询大于 18 信息
select * from students where age>18;
–查询等于 18 ，等号就一个
select * from students where age=18;
select name,id,gender from students where age=18;–逻辑运算符
–and
–18 到 28 之间的所有信息(and 左右具体)
select * from students where age>18 and age<28;
–18 岁以上的女性
select * from students where age>18 and gender =’女’；
–or
–十八岁以上或者身高超过一米八的
select * from students where age>18 or high>180.00;
–not(在谁的条件是上加 not 仅仅否定这一个条件)
–不在 18 岁以上的女性，这个范围内的信息
select * from students where not (age>18 and gender = 2);
–可用加括号来解决优先级的问题
–不在 18 岁以上并且是女性
select * from students where not age>18 and gender = 2;
–模糊查询
–like
–%替换一个或多个
–_替换 1 个
–查询姓名当中以 小 开头
select name from students where name like"小%";
–查询姓名当中有小的所有的名字
select name from students where name like’%小%’;
–查询有 2 个字的名字
select name from students where name like ‘’;
–查询至少有两个字的名字
select name from students where name like '%’;
–rlike 正则
–^以什么什么开头
select name from students where name rlike’^周.’;
–以周开头，以伦结尾
–$以什么什么结尾selectnamefromstudentswherenamerlik{e}^{\prime 周}.\ast 伦$’;
MySQL 中使用 REGEXP 操作符来进行正则表达式匹配。
mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^st’;–范围查询
–in（1,3,8）表示在一个非连续的范围内
–查询年龄为 18,34 的姓名
select name,age from students where age=18 or age =34 or age = 12;
select name,age from students where age in(18,34,12);
–not in 不非连续的范围内
select name,age from students where age not in(18,34,12);
–between … and …表示一个在连续的范围内
select name,age from students where age between 17 and 29;
–not between … and … 不在一个范围之内（不当独立，不加括号）
select name,age from students where age not between 17 and 29;
select name,age from students where not age between 17 and 29;
–空判断
–判断空 is null（NULL）
select * from students where high is null;
–判非空 is not null
–排序
–order by 字段（默认从小到大排）
–asc 从小到大排序，即升序
–desc 从大到小排序，即降序
–查询年龄 18 到 34 岁之间的男性，按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender=“男” order by
age asc;
–查询 18 到 34 岁之间的女性，身高从高到矮排序
select * from students where (age between 18 and 34)and gender=‘女’ order by high
desc;
–order by 支持多个字段（第一个字段排完排第二个，之后排第二个字段）
–身高相同按年龄排，年龄相同按身高，身高相同按照 id
select * from students where (age between 18 and 34)and gender=‘女’ order by high
desc,age asc, id desc;
–年龄从小到大，身高从高到矮排序
select * from students order by age asc,high desc ;
–聚合函数
–总数
–count
–查询男（女）性有多少人
select count() from students where gender=1;
select count() as 女性人数 from students where gender=2;
select count() 人数 from students ; 总共人数–最大值
–max
–查询最大年龄
select max(age) from students;
–最小值
–min
–求和
–sum
–计算所有人年龄总和
select sum(age) from students;
–平均值
–avg
–计算平均年龄
select avg(age) from students;
select sum(age)/count() from students;
–四舍五入 round(123.23,1)保留一位小数
–计算所有人的平均年龄，保留两位小数
select round(avg(age),2) from students;
select round(avg(age),2) from students where gender=1;
–分组：原来数据先分组，再从组里面取数据
–group by（特点：和聚合函数一起用）
–按照性别分组，查询所有性别
select … from students group by gender;
select gender from students group by gender;
–计算每种性别的人数（对分组后的数据计算结果）
select gender,count() from students group by gender;
select gender,max(age) from students group by gender;
–想要看到每组里面的人名
select gender,group_concat(name) from students group by gender;
–计算男性的人数
select gender,count() from students where gender=1 group by gender;
（where 在 group by 前面）
–男性里面有谁啊
select gender,group_concat(name,age) from students where gender=1 group by
gender;
–group_concat 你写上谁，就把谁连在一起写(可以写字符串)
select gender,group_concat(name,‘年龄：’,age) from students where gender=1 group
by gender;
彭于晏年龄：29,刘德华年龄：59,周杰伦年龄：36–having(对分组进行条件判断)
–查询平均年龄超过 30 岁的性别，以及姓名， having avg(age) >30
select gender,GROUP_CONCAT(name) from students group by gender having
avg(age)>30;
–where 在 group by 之前，having 在之后
–where 对原始表进行条件，having 是对查出来的结果进行判断
–查询每种性别中的人数少于 2 个的信息
select gender,group_concat(name) from students group by gender having count()<2;
分页
–limit start ,count
–限制查询出来的数据个数
select * from students where gender=1 limit 2;
–从哪个数据开始 逗号 几条数据
select * from students limit 2,5;
–查询数据前五个
select * from students limit 0,5;
select * from students limit 5,5; 第二组的五个
–每页显示两个，第 1/2/3/4 个页面
select * from students limit 0/2/4/6/8,2;
–limit(第 n 页-1)每页的个数，每页的个数；
–每页显示 2 个，显示第六页的信息，按照年龄从小到大排序
–失败 select * from students limit 10,2 order by age asc; limit 在最后
select * from students order by age asc limit 10,2 ;
–作业：查询所有女性的信息并且按照身高从高到矮排序只显示两个
select * from students where gender=‘女’ order by high desc limit 2;–链接查询（多个表的关联查询）
–inner join … on
–在 on 后面写条件，两个表值对应的上那就取交集
select * from 表 A inner join 表 B;
– 查询能对的上的学生的信息
select * from students inner join classes on students.cls_id=classes.id;
–按照要求显示姓名班级
select students.name,classes.name from students inner join classes on
students.cls_id=classes.id;
–给数据表起名
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;
—查询，能够显示对应班级的学生以及班级信息，显示学生的所有信息，只显示
班级名称
select students.,classes.name from students inner join classes on
students.cls_id=classes.id;
–班级名字放在第一列
select c.name,s.name from students as s inner join classes as c on s.cls_id=c.id;
–有能够对应班级的学生以及班级的信息，按照班级的信息进行排序
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by
c.name;
–同一个班级的时候，按照学生 id 从小到大排序
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by
c.name,id asc;
–外连接：左连接，右连接
–left join
–查询每位学生对应的信息
select * from students as s left join classes as c on s.cls_id=c.id;
–谁写在左边，以谁为基准，有显示，没有默认显示为 null
–查询没有对应班级的学生信息
–select xx from xx as s left join classes as c on xxx having xxx;
–select xx from xx as s left join classes as c on xxx where xxx;（从原表找）
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;（从
查出来新的结果集里面查询）
–right join 一般用的很好，只需要调换 left join 数据表
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。–自关联
一个表里的一个字段关联另一个字段
—创建 areas 表
create table areas(
aid int primary key,
atitle varchar(20),
pid INT
);
–从 sql 文件导入数据
source areas.sql
insert into areas values(140000,‘陕西省’,NULL);
insert into areas values(140100,‘石家庄市’,‘140000’);
insert into areas values(140101,‘唐山市’,‘140100’);
insert into areas values(140102,‘秦皇岛’,‘140100’);
insert into areas values(140103,‘邯郸市’,‘140100’);
–查询一共有多少省
select * from areas where pid is null;
–查看陕西里面有多少地级市
select aid from areas where atitle=‘陕西省’; 先查出陕西省对应的 aid
select * from areas where pid=110100; pid=aid
select … from … atitle =’山东省’;
–一张表按照多个表替代（自关联）
select province.atitle,city.atitle from areas as province inner join areas as city on
city.pid=province.aid having province.atitle=‘北京市’;
–子查询（慢一点）
什么是子查询？怎么用
一个 select 嵌套另一个子查询
–查询最高的男生的信息
select * from students where high=(select max(high) from students);
select * from areas where pid=(select aid from areas where atitle=‘陕西省’);数据库设计：
设计数据库实际上设计数据表，真正开发有很多设计数据库的软件
三范式：
范式：经过研究和对实际使用过程的总结，对数据库提供一些规范，这些规范称
为范式，一般遵循前三种范式
1NF：不可再拆，列不可再分
2NF：第一范式的基础上，必须有主键，没有包含主键的列必须完全依赖主键
主键有两个或者多个字段的时候，其余部分完全依赖于这些主键，不能依赖于部
分主键
3NF：在二范式基础上，另外非主键的字段必须直接依赖于主键而不能传递
关系型数据库有多个表组成，核心就在他们之间的关系上
E-R 模型
实体和关系之间的模型
多对一：在多里面添加字段
多对多：建多个表（新开一个表）称为聚合表MySQL 与 Python 交互

准备数据
–创建京东数据库
create database jing_dong charset=utf8;
–创建数据表
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,'x500cc 15.6 英寸笔记本 ‘,’ 笔 记 本 ‘,’ 华 硕
',‘2799’,default,default);
insert into goods values(0,‘x240 超级本’,‘超级本’,‘联想’,‘4888’,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,‘商务双肩背包’,‘笔记本配件’,‘索尼’,‘99’,default,default);
insert into goods values(0,'x3250 m4 机 架 式 服 务 器 ‘,’ 服 务 器 / 工 作 台
',‘ibm’,‘6889’,default,default);
insert into goods values(0,'hmz-t3w 头 戴 显 示 设 备 ‘,’ 笔 记 本 配 件 ‘,’ 索 尼
',‘6399’,default,default);
联系基本 sql 语句
把一张表拆成多个表SQL 演练

–查询 cate_name 为超级本的信息
select * from goods where cate_name=“超级本”;
select name,price from goods where cate_name=“超级本”;
select name,price as 商品价格 from goods where cate_name=“超级本”;
–查看有多少种
select distinct cate_name from goods ;（简单）
select cate_name from goods group by cate_name;（更强）
–查询每个种类里面具体的商品名称
select cate_name ,group_concat(name) from goods group by cate_name;
–求所有电脑的平均价格并且保留两位小数
select round(avg(price),2) as 平均价格 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 by
cate_name;
–查询所有价格大于平均价格的是商品，并且按照价格降序排序
select * from goods where price>(select round(avg(price),2) from goods) order by
price desc;
–查询每种商品中最贵的电脑信息
select * from xxx as g_new left join goods as g on xxx
(g_new.cate_name=g.cate_name and g_new.max_price = g.price);
select * from (select cate_name,max(price) as max_price
from goods group by cate_name) as a left join goods as b
on a.cate_name=b.cate_name and a.max_price=b.price;
把子查询的结果就当做一个表来对待，用到子查询和关联查询
select * from (select cate_name,max(price) as max_price from goods
group by cate_name) as a left join goods as b on
a.cate_name=b.cate_name and a.max_price=b.price order by a.cate_name;（如果
同牌子最高价有两个，用 order by 相同牌子放在一起）
*可代替其他你想看到的值
select a.cate_name,b.name,b.price from (select cate_name,max(price) as max_price
from goods group by cate_name) as a left join goods
as b on a.cate_name=b.cate_name and a.max_price=b.price order by a.cate_name;–创建商品分类表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not NULL);
create table if not exists goods_brand(
id int unsigned primary key auto_increment,
name varchar(40) not null);
–查询 cate_name 种类
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;
insert into goods_brand(name) select brand_name from goods group by brand_name;
注意：这里没有 values 此处 name 是字段
–同步两个表（更新表的信息）
update goods xx set g.cate_name=xxxx
update goods as g inner join goods_cates as c on g.cate_name=c.name set
g.cate_name=c.id;
update goods as g inner join goods_brand as b on g.brand_name=b.name set
g.brand_name=b.id;
–向 goods_cates 插入数据
insert into goods_cates(name) values (‘路由器’),(‘交换机’),(‘网卡’);
–在 goods 插入没有索引为 12 的值（超出范围）
insert into goods (name,cate_name,brand_name,price) values(‘LasterJet Pro P1606dn
黑白激光打印机’,12,4,‘1849’);
把 cate_name 改成 cate_id ,cate_id 和 id 两个类型应该一致
原来学过：更改表结构是 altable(change,add ,drop,modify)
–修改表结构
–查看 goods 的数据表结构，会发现 cate_name 和 brand_name 对应的类型 varvhar
但是储存的都是数字
desc goods;
–通过 alter table 语句修改表的结构
alter table students （表名）change（原名，新名，类型，约束）birthdayn birth
date default “1997-06-19”;
alter table goods change cate_name cate_id int unsigned not null;
alter table goods change brand_name brand_id int unsigned not null;–外键
alter table goods add foreign key(cate_id) references goods_cates(id);
alter table goods add foreign key(brand_id) references goods_brand(id);
创建表-向新表插入数据-同步数据(update)-修改表结构(alter table)-外键
alter table 逗号隔开可以一次性重命名多个字段
用外键会降低效率（尽量少使用）
–如何取消外键约束
–先获取外键约束名称，该名称系统会自动生成，通过查看表创建语句来获取名
称
show create table goods;
alter table goods drop foreign key 外键名；
alter table goods drop foreign key goods_ibfk_3;
真正的数据库是先设计再填数据python 操作 sql（查询）

from pymysql import *
#创建 connection 连接
conn = connect(host=‘localhost’,port=3306,user=‘root’,
password=‘rong1997’,database=‘jing_dong’,charset=‘utf8’)

conn
<pymysql.connections.Connection object at 0x0000011116ECB220>
#获得 cursor 对象

cursor=conn.cursor()
#执行 select 语句，并返回受影响的行数；查询一条数据

cursor.execute(‘select * from goods;’) #生效行数
11

cursor.fetchone() #以元组给结果
(1, ‘r510vc 15.6 英寸笔记本’, 4, 2, Decimal(‘3399.000’), b’\x01’, b’\x00’)

cursor.fetchone()
(2, ‘y400n 14.0 英寸笔记本’, 4, 5, Decimal(‘4999.000’), b’\x01’, b’\x00’)

cursor.fetchone()
(3, ‘g150th 15.6 英寸游戏本’, 3, 7, Decimal(‘8499.000’), b’\x01’, b’\x00’)

cursor.fetchone()
(4, ‘x500cc 15.6 英寸笔记本’, 4, 2, Decimal(‘2799.000’), b’\x01’, b’\x00’)

cursor.fetchmany()
((5, ‘x240 超级本’, 6, 5, Decimal(‘4888.000’), b’\x01’, b’\x00’),)

cursor.fetchmany(3) #元组套元组
((6, ‘ipad mini 7.9 英寸平板电脑’, 1, 6, Decimal(‘1998.000’), b’\x01’, b’\x00’), (7, ‘ipad
air 9.7 英寸平板电脑’, 1, 6, Decimal(‘3388.000’), b’\x01’, b’\x00’), (8, ‘商务双肩背包
‘, 5, 3, Decimal(‘99.000’), b’\x01’, b’\x00’))
#提交之前的操作，如果之前已经执行过多次 execute 那么就进行提交
conn.commit()
#关闭 cursor 对象

cursor.close()
conn.close()
开始
创建 connection
获取 cursor
执行查询，执行命令，获取数据，处理数据
关闭 cursor
关闭 connection
结束举例查询：

conn = connect(host=‘localhost’,port=3306,user=‘root’,
password=‘rong1997’,database=‘jing_dong’,charset=‘utf8’)

cursor=conn.cursor()
cursor.execute(‘select * from goods;’)
11

line_content = cursor.fetchone()
line_content
(1, ‘r510vc 15.6 英寸笔记本’, 4, 2, Decimal(‘3399.000’), b’\x01’, b’\x00’)

line_content[4]
Decimal(‘3399.000’)

for temp in line_content:
print(temp)
1
r510vc 15.6 英寸笔记本
4
2
3399.000
b’\x01’
b’\x00’

lines = cursor.fetchmany(5)
lines
((2, ‘y400n 14.0 英寸笔记本’, 4, 5, Decimal(‘4999.000’), b’\x01’, b’\x00’), (3, ‘g150th
15.6 英寸游戏本’, 3, 7, Decimal(‘8499.000’), b’\x01’, b’\x00’), (4, ‘x500cc 15.6 英寸笔
记 本 ‘, 4, 2, Decimal(‘2799.000’), b’\x01’, b’\x00’), (5, ‘x240 超级本 ‘, 6, 5,
Decimal(‘4888.000’), b’\x01’, b’\x00’), (6, ‘ipad mini 7.9 英寸平板电脑 ‘, 1, 6,
Decimal(‘1998.000’), b’\x01’, b’\x00’))

for temp in lines:
print(temp)
(2, ‘y400n 14.0 英寸笔记本’, 4, 5, Decimal(‘4999.000’), b’\x01’, b’\x00’)
(3, ‘g150th 15.6 英寸游戏本’, 3, 7, Decimal(‘8499.000’), b’\x01’, b’\x00’)
(4, ‘x500cc 15.6 英寸笔记本’, 4, 2, Decimal(‘2799.000’), b’\x01’, b’\x00’)
(5, ‘x240 超级本’, 6, 5, Decimal(‘4888.000’), b’\x01’, b’\x00’)
(6, ‘ipad mini 7.9 英寸平板电脑’, 1, 6, Decimal(‘1998.000’), b’\x01’, b’\x00’)作业：
写程序，问用户查什么，1 所有信息 2 所有分类 3 所有数据商品分类品牌分类
如果提交 4,前面有三，不用 commit，用 conn.rollback()重新来一遍
—京东----
1:所有的商品
2:所有的商品分类
3:所有的商品品牌
4：添加一个商品分类
5：根据名字查询一个商品
请输入功能对应的序号：5
请输入查询商品的名字’ or 1=1 or ‘1
-------->select * from goods where name = ‘’ or 1=1 or ‘1’;<---------
(1, ‘r510vc 15.6 英寸笔记本’, 4, 2, Decimal(‘3399.000’), b’\x01’, b’\x00’)
(2, ‘y400n 14.0 英寸笔记本’, 4, 5, Decimal(‘4999.000’), b’\x01’, b’\x00’)
(3, ‘g150th 15.6 英寸游戏本’, 3, 7, Decimal(‘8499.000’), b’\x01’, b’\x00’)
(4, ‘x500cc 15.6 英寸笔记本’, 4, 2, Decimal(‘2799.000’), b’\x01’, b’\x00’)
(5, ‘x240 超级本’, 6, 5, Decimal(‘4888.000’), b’\x01’, b’\x00’)
(6, ‘ipad mini 7.9 英寸平板电脑’, 1, 6, Decimal(‘1998.000’), b’\x01’, b’\x00’)
(7, ‘ipad air 9.7 英寸平板电脑’, 1, 6, Decimal(‘3388.000’), b’\x01’, b’\x00’)
(8, ‘商务双肩背包’, 5, 3, Decimal(‘99.000’), b’\x01’, b’\x00’)
(9, ‘x3250 m4 机架式服务器’, 2, 1, Decimal(‘6849.000’), b’\x01’, b’\x00’)
(10, ‘hmz-t3w 头戴显示设备’, 5, 3, Decimal(‘6399.000’), b’\x01’, b’\x00’)
(11, ‘老王寸笔记本’, 4, 4, Decimal(‘4999.000’), b’\x01’, b’\x00’)
添加防止 sql 注入
#非安全方式
#输入 or 1=1 or ‘1
#sql = “”“select * from goods where name = ‘%s’;”""% find_name
#print(’-------->%s<---------’%sql)、
##执行 select 语句
#count = cs1.execute(sql)
#安全的方式
#构建参数列表
params = 【find_name】
#执行 select 语句，并且返回受影响的行数：查询所有数据
#count = cs1.execute(‘select * from goods where name = %s’params)
#注意：如果要是有多个参数，需要进行参数化
#此时 sql 语句中有多个%s 即可作业：
额外创建顾客表，订单表，订单详情表
注册，登录，下订单（三个功能写入程序）MySQL 高级
不用背用的时候查一下

视图 事务 索引 账户管理（了解） MySQL 主从
视图：一条 select 语句执行后返回的结果
对若干张表的引用，一张虚表（原表改，新表改），查询语句执行的结果
视图往往用在查数据，用在更新数据有时不行
–创建视图
create view v_goods_info as select g.*,c.name as cate_name,b.name as brand_name
from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brand as b
on g.brand_id=b.id; #创建一个虚表
–查看视图
show tables;
–使用视图
select * from v_goods_info;
–删除视图
drop view 视图名称
视图的作用：
1.提高重用性 2.对数据库重构，不影响程序运行
3.提高安全性能，可以对不同用户 4.让数据更加清晰事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除
一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等
等，这样，这些数据库操作语句就构成一个事务！
• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不
执行。
• 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足 4 个条件（ACID）：：原子性（Atomicity，或称不可分割性）、
一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
一个操作序列，要么都执行，要么都不执行，是一个不可分割的工作单位 例如：
银行转账工作
ACID：原子性 一致性 隔离性 持久性
银行数据库有两张表，支票和储蓄，从支票转 200 到储蓄，至少 3 个步骤
以上三个步骤必须打包在一个事务中，任何一个步骤失败，则必须回归所有步骤
1.start transaction;
2.select balance from checking where custom_id = 1922
3.update checking set balance = balance-200 where custom_id = 1922
3.update saving set balance = balance+200 where custom_id = 1922
5.commit;
原子性：
不可再分割，要么成功要么不成功
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个
环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务
从来没有执行过一样。
一致性：
最终执行结果一样，保证中途不出现状况
隔离性：
一个事务所所做的修改在最终提交前，对其他事务是不可见的
start transaction; 开启事务 commit 提交；rollback 回滚
持久性：
一旦事务提交，则所做的修改会永远保存在数据库1、用 BEGIN, ROLLBACK, COMMIT 来实现
• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

提交
create table money (id int unsigned not null primary key auto_increment,
num int unsigned not null);
desc money;
insert into money values (0,100),(0,200),(0,0);
begin;
update money set num=num-100 where id=1;
update money set num=num+100 where id=2;
select * from money;
commit;
两边同时运行，谁先 commit 谁先修改掉，在另一个客户端才能显示
索引
索引是一种特殊的文件（lonnDB 数据表上的索引是表空间的一个组成部分）它们
包含着对数据表里所有记录的引用指针
更通俗的说，数据索引好比是一本书前面的目录，能加快数据库的查询速度
create table test_index(title varchar(10));
#插入 100000 数据
from pymysql import connect
def main():
#创建 connecion 连接
conn =
connect(host=‘localhost’,port=3306,database=‘jing_dong’,user=‘root’,password=‘rong1997’,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()
#查看数据有多少条
select count() from test_index;
查询：
#开启运行时间监测
set profiling = 1;
#查找第 1 万条数据 ha-9999
select * from test_index where title = ‘ha-9999’;
#查看执行的时间
show profiles;
#为表 title 字段建立索引
create index title_index on test_index(title(10));
#执行查询语句
select * from test_index where title = ‘ha-9999’;
#再次查看执行的时间
show profiles;
原理：通过不断缩小数据的范围
#查看索引
show index from test_index;
#创建索引
create index 索引名称 on 表名（字段名称（长度））；
#删除索引
drop index 索引名称 on 表名；
数据表建立主键外键的时候自动创建了索引
如果数据很少，不需要建索引，哪些列常用并且数据多才建
建立太多索引将会影响更新和插入的速度账户管理（了解）
目的：保证数据更安全更稳定
授予权限：创建用户顺便给权限
–查看所有用户
select user,host from user;
–展现用户名和密码
select host,user,authentication_string from user;
创建账户并授予权限
grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ identified by ‘密码’；;
grant select on jing_dong. to ‘laowang’@‘localhost’ identified by ‘123456’;
grant all privilege on xxxx 所有权限
–此处只有查询权限
–查看用户有哪些权限
show grants for laowang@localhost;
–修改权限
grant 权限列表 on 数据库 to ‘用户名’@’访问主机’ with grant option;
–修改密码
update user set suthentication_tring=password(‘新密码’) where user=’用户名’
刷新权限：flush privilegesMySQL 主从同步配置
（没听完）

面试：问你服务器的架构如何操作？
主从：备份
（读写分明，负载均衡，让网站更快的响应）
1.手动备份主所有数据
主里面的数据先导成 sql 文件，从里面把整个过程执行，让主从保持数据线一模
一样，让后再同步
查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号
dept_no(请注意输出结果，dept_no 列是最后一列)
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL comment ‘部门编号’,
emp_no int(11) NOT NULL comment ‘员工编号’,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL comment ‘员工编号’,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));select s.emp_no,s.salary,s.from_date,s.to_date,d.dept_no from salaries as s
inner join dept_manager as d
on s.emp_no=d.emp_no and
s.to_date=‘9999-01-01’ and d.to_date=‘9999-01-01’;
查找所有员工的 last_name 和 first_name 以及对应部门编号 dept_no，也包括暂时没有分
配具体部门的员工
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
select e.last_name,e.first_name,d.dept_no from employees as e
left join dept_emp as d on d.emp_no=e.emp_no;
left join 以左边为基准，左边表所有的元素都包含，没有的显示 null查找所有员工入职时候的薪水情况，给出 emp_no 以及 salary， 并按照 emp_no 进行逆
序
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
2、根据题意注意到 salaries.from_date 和 employees.hire_date 的值应该要相等，因此
有限制条件 e.hire_date = s.from_date
select e.emp_no,s.salary from employees as e
inner join salaries as s on e.emp_no=s.emp_no
and e.hire_date=s.from_date
order by e.emp_no desc;查找薪水涨幅超过 15 次的员工号 emp_no 以及其对应的涨幅次数 t
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
select emp_no,count(*) as t from salaries
group by emp_no having t>15;
–where 对原始表进行条件，having 是对查出来的结果进行判断
获取所有非 manager 的员工 emp_no
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
#1
select e.emp_no from employees as e
left join dept_manager as d
on e.emp_no = d.emp_no
where d.emp_no is Null; #选取 manager 列表中不包含的值
#2
select emp_no from employees
where emp_no not in(select emp_no from dept_manager);
题目描述
获取所有员工当前的 manager，如果当前的 manager 是自己的话结果不显示，当前表示
to_date=‘9999-01-01’。
结果第一列给出当前员工的 emp_no,第二列给出其 manager 对应的 manager_no。
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
select d.emp_no,m.emp_no as manager_no
from dept_emp as d inner join dept_manager as m
on d.dept_no = m.dept_no
where d.to_date = ‘9999-01-01’ and m.to_date = ‘9999-01-01’
and d.emp_no <> m.emp_no;
链接：https://www.nowcoder.com/questionTerminal/e50d92b8673a440ebdf3a517b5b37d62
来源：牛客网
本题应注意以下三点：
1、用 INNER JOIN 连接两张表，因为要输出自己的经理，得知自己与经理的部门要相同，故
有限制条件 de.dept_no = dm.dept_no
2、再用 WHERE 限制当前员工与当前经理的条件，即 dm.to_date 等于 ‘9999-01-01’ 、
de.to_date 等于 ‘9999-01-01’ 、 de.emp_no 不等于 dm.emp_no
3、为了增强代码可读性，将 dept_emp 用别名 de 代替，dept_manager 用 dm 代替，最
后根据题意将 de.emp_no 用别名 manager_no 代替后输出获取所有部门中当前员工薪水最高的相关信息，给出 dept_no, emp_no 以及其对应的
salary
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
select d.dept_no,s.emp_no,max(s.salary)as salary from salaries as s
inner join dept_emp as d
on d.emp_no=s.emp_no
where d.to_date = ‘9999-01-01’ and s.to_date= ‘9999-01-01’
group by d.dept_no;
1.注意这里 group by 和 order by 的区别
2.group by 在 where 条件之后
3，用 inner join 连接
4.注意看题说的是当前时间，所以有一个 where 限制条件
5.不是 s.max(salary) 而是 max(s.salary)从 titles 表获取按照 title 进行分组，每组个数大于等于 2，给出 title 以及对应的数目 t。
注意对于重复的 emp_no 进行忽略。
CREATE TABLE IF NOT EXISTS titles (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL)
1.去除重复 emp_no ,可以用 distinct , 用 COUNT(DISTINCT emp_no)可以统计同一
title 值且不包含重复 emp_no 值的记录条数
2.count 函数不可和 where 连用，having 在更新之后的表进行条件限制
select title,count(distinct emp_no) as t from titles group by title having t>1