1.1安装数据库
1.1.1安装数据库
更新apt相关源 sudo apt-get update
1.1.2mysql安装
服务端安装 apt-get install mysql-server
客户端安装 apt-get install libmysqlclient-dev
注意:一般情况下安装完数据库,自动开启,且开机后自动开启
1.1.3其他相关命令
开启mysql服务端 sudo service mysql start
重启mysql服务端 sudo service mysql restart
关闭mysql服务端 sudo service mysql stop
开启mysql客户端 进入客户端目录,启动 ./start_navicat
连接mysql mysql -uroot -p密码
退出登录mysql quit/eixt/ctrl+d
查看版本：select version();
显示当前时间：select now();
查看mysql相关程序 ps -aux | grep "mysql"
1.1.4配置数据库
配置文件目录为 /etc/mysql/mysql.cnf
进入mysql.conf.d入录，打开mysql.cnf
主要配置项如下：
bind-address表示服务器绑定的ip，默认为127.0.0.1
port表示端口，默认为3306
datadir表示数据库目录，默认为/var/lib/mysql
general_log_file表示普通日志，默认为/var/log/mysql/mysql.log
log_error表示错误日志，默认为/var/log/mysql/error.log
1.1.5可能相互此案的问题问题
问题一：中文乱码
解决：打开start_navicat文件
将export LANG="en_US.UTF-8"改为export LANG="zh_CN.UTF-8"
问题二：试用期
解决：删除用户目录下的.navicat64目录
rm -r .navicat64
1.2表格规范
1.2.1数据类型
整数：int,tinyint,bit
小数：decimal(5,2)表示共存5位数,小数占2位
字符串：varchar,char,text
char(3) 必须输出三个字节
varchar(3) 不够可以省略
text大文本，超过4000个字符才推荐使用
日期时间: date, time, datetime
枚举类型(enum)，所有情况列举出来
1.2.3约束
主键primary key：物理上存储的顺序
非空not null：此字段不允许填写空值
惟一unique：此字段的值不允许重复
默认default：当不填使用默认值,填写时以填写为准
外键foreign key：关联表的主键
unsigned 为无符号的,修饰整型
1.3数据库相关命令
查看所有数据库 show databases;
创建数据库 create database 数据库名 charset=utf8;
查看当前使用的数据库 select database();
使用数据库 use 数据库名;
删除数据库 drop database 数据库名;
数据库备份 进入数据库存储路径 /var/lib/mysql
mysqldump –uroot –p 原数据库名 > 新数据名
数据库备份 连接mysql，创建新的数据库，退出连接，执行命令
mysql -uroot –p 新数据库名 < python.sql
导入数据库 连接mysql，创建使用数据库，执行命令
source 路径 要导入的数据库;
1.4数据表创建相关命令
查看当前数据库中所有表 show tables;
查看表结构 desc 表名;
1.4.1创建表
create table 数据表名字 (字段 类型 约束, 字段 类型 约束);
create table 美术班(
id int unsigned not null auto_increment primary key,
name varchar(30),
gender enum("男","女") default "男",
age tinyint unsigned default 25,
high decimal(4,1),
cls_id int unsigned
);
1.4.2修改表字段
添加字段 alter table 表名 add 字段名 类型及约束;
alter table 美术班 add birthday datetime not null;
修改字段-类型及约束 alter table 表名 modify 字段名 类型及约束;
alter table 美术班 modify birthday date default "2000-01-01";
修改-字段名和类型及约束 alter table 表名 change 原字段名 新字段名 类型及约束;
alter table 美术班 change birthday birth date not null;
删除字段 alter table 表名 drop 字段名;
alter table 美术班 drop birth;
1.4.2删除表 drop table 表名;
1.5增加
全列插入 insert into 表名 values(值1,...);
主键/外键字段 可以用 0 null default 来占位
insert into 美术班 values(0, "小丽", "女", 20, 166.6, 0, "1990-01-01");
部分插入 insert into 表名(字段1,...) values(值1,...);
insert into 美术班(name, gender) values ("小乔", "女");
多行插入 insert into 表名 values(值1,...), (值1,...), (值1,...)..;
insert into 美术班 values(default, "西施", "女", 20, 166.8, 1, "1990-01-01"), (default, "王昭君","女", 20,177.7, 1, "1990-01-01");
1.6删除
物理删除delete from 表名 where 条件;
delete from 美术班; --删除所有
delete from 美术班 where name="美女"; --条件删除
逻辑删除:用一个字段来表示 这条信息是否已经不能再使用了
alter table 美术班 add is_delete bit default 0;
update 美术班 set is_delete=1 where id=6;
1.7修改
update 表名 set 字段1=值1,字段2=值2... where 条件;
update python11班级 set age=22, gender=1 where id=3;
1.8查询
1.8.1查询字段
查询所有字段 select * from 表名;
指定条件查询 select * from 美术班 where name="貂蝉";
select * from 美术班 where id>3;
查询指定字段 select 字段1,字段2,... from 表名;
使用as为字段或表指定别名
select name as 姓名, gender as 性别 from 美术班 as m;
消除字段的重复 select distinct gender from students;
1.8.2按条件查询
1.8.2.1比较运算符= < <= > >= !=("<>"是python2中的不等于)
select * from students where age>18;
select * from students where name != '黄蓉';
select * from students where is_delete=0;
1.8.2.2逻辑运算符or、and、not
select * from students where age>18 and gender=2;
select * from students where age>18 or height>=180;
select * from students where not(age>18 and gender=2);
select * from students where (not age>18) and gender=2;
1.8.2.3模糊查询
1.8.2.3.1 like
% 替换一个或则多个
_ 替换一个
select name from students where name like "小%";
select * from students where name like '黄_';
select * from students where name like '黄%' or name like '%靖';
select * from students where name like '__';
1.8.2.3.2 rlike 正则表达式
select * from students where name rlike '^周.*';
select * from students where name rlike '黄.';
1.8.2.4范围查询
1.8.2.4.1 in not in(不连续)
select * from students where age in(18,23);
select * from students where age not in(18,23);
1.8.2.4.2 between not between(连续)---不能用括号
select * from students where age between 18 and 23;
select * from students where age not between 18 and 23;
1.8.2.5空判断
is null
is not null
select * from students where height is null;
select * from students where height is not null;
1.8.2.6优先级
优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用。
select * from students where (age between 18 and 34) and gender=1;
1.8.3排序查询
order by 字段
asc从小到大排列，即升序--默认排序
desc从大到小，即降序
属性相同时、默认按主键排序
select * from students where (age between 18 and 34) and gender=1 order by age asc;
select * from students where (age between 18 and 34) and gender=2 order by height desc;
select * from students order by age asc, height desc;
1.8.4聚合函数
1.8.4.1总数--count(字段)
select count(*) as 男生人数 from students where gender=2;
1.8.4.2最大值--max(字段)
select max(age) from students where gender=1;
1.8.4.3最小值--min(字段)
select min(age) from students where gender=1;
1.8.4.4总和--sum(字段)
select sum(age) from students where gender=1;
1.8.4.5平均值--avg(字段)=sum(age)/count(*)
select avg(age) from students where gender=1;
1.8.4.6四舍五入--round(数值,小数点位数) 保留几位小数
--男生平均年龄，四舍五入，保留两位数
select round(avg(age), 2) from students where gender=1;
1.8.5分组（与聚合一起用）
将查询结果按1个或多个字段分组，字段值相同的为一组
1.8.5.1 group by:
select gender,count(*) from students group by gender;
select gender,count(*) from students where gender=1 group by gender;
1.8.5.2 group by + group_concat(字段)
group_concat(字段名)可以作为一个输出字段来使用,表示分组之后,根据分组结果,来放置每一组的某字段的值的集合。
select gender,group_concat(name) from students group by gender;
select gender,group_concat(name,"_",age," ",id) from students where gender=1 group by gender;
1.8.5.3 group by + having + 聚合函数
having条件表达式：用来分组查询后指定一些条件来输出查询结果。
having作用和where一样，但having只能用于group by
-- 查询平均年龄超过30岁的性别，以及对应的姓名，平均年龄
select gender, group_concat(name),avg(age) from students group by gender having avg(age)>30;
-- 查询每种性别中的人数多于2个的信息
select gender, group_concat(name) from students group by gender having count(*)>2;
1.8.5.4 group by + with rollup
--with rollup的作用：最后新增一行，记录当前列所有记录的总和。
--最后记录所有性别人数
select gender,count(*) from students group by gender with rollup;
--最后记录所有年龄
select gender,group_concat(age) from students group by gender with rollup;
1.8.6分页查询（放在最后）
limit strat,count
limit 限制个数
start 起始位置
count 个数
--起始位置为1,限制个数为5
select * from students limit 1,5;
--每页显示两个，分页
select * from students limit 0,2; --第一页
select * from students limit 2,2; --第二页
--每页显示2个,显示第6页的信息,按照年龄从小到大排序
select * from students order by age asc limit 10,2;
--女,身高从高到低,起始0,限制5
select * from students where gender=2 order by height desc limit 0,5;
1.8.7连接查询
事例（学生表30人，1-10是1班，11-20是2班，21-30是4班）（班级表3各班，1班，2班，3班）
1.8.7.1内连接(交集)
表1 inner join 表2 on 关系条件
--查询所有有对应班级的学生信息和班级信息（学生表1-20，班级表1班，2班）
--显示所有学生表字段和和所有对应班级表字段
select * from students inner join classes on students.cls_id=classes.id;
--显示所有学生表字段和对应班级名
select students.*,classes.name 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 c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
1.8.7.2左连接（表1全部加对应的表2信息）
表1 left join 表2 on 关系条件
--查询所有学生信息和对应的班级信息（学生表1-30全部，班级表1班，2班）
select * from students as s left join classes as c on s.cls_id=c.id;
--查询没有对应班级信息的学生（学生表21-30）
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;
1.8.7.3右连接 right join on
将数据表名字互换位置，用left join完成
1.8.8自关联查询
(省市县类数据库)
--查询所有省份
select * from areas where pid is null;
--查询出山东省有哪些市
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";
--查询山东省有哪些市，显示省,市
select province.atitle as 省份, city.atitle as 城市 from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";
--子查询青岛市有哪些县城
select * from areas where pid=(select aid from areas where atitle="青岛市");
1.8.9子查询
--查询最高的男生信息
select * from students where height = (select max(height) from students);
--查询学生的班级号能够对应的学生信息
select * from students where cls_id in (select id from classes);
1.8.10完整的select语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count
执行顺序为：
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count
1.9数据库设计
1.9.1三范式
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
第二范式（2NF）：首先是1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的已部分。
第三范式（3NF）：首先是2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。
1.9.2E-R模型
E表示entry，实体，设计实体就像定义一个类一样，指定从哪些方面描述对象，一个实体转换为数据库中的一个表；
R表示relationship，关系，关系描述两个实体之间的对应规则，关系的类型包括包括一对一、一对多、多对多；
关系也是一种数据，需要通过一个字段存储在表中；
实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值；
1.10增加/修改进阶
基础：insert into 表名(字段名) values(值1,...);
进阶：
--将分组结果写入到goods_cates数据表
insert into goods_cates (name) select cate_name from goods group by cate_name;
基础：update 表名 set 字段1=值1,字段2=值2... where 条件;
进阶：
--通过goods_cates数据表来更新goods表
update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
1.11设置外键
--给cate_id 添加外键约束
alter table goods add foreign key (cate_id) references goods_cates(id);
--在创建表的时候就创建外键
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;
--系统生成的外键名：goods_ibfk_1
goods_ibfk_2
--获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
1.12python中操作mysql
流程:导包-->创建connoction-->获取cursor-->执行sql语句-->关闭cursor-->关闭connoction
a.导包:from pymysql import *
b.创建Connection对象(用于建立与数据库的连接):
创建对象：调用connect()方法
conn=connect(参数列表)
参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端?，默认是3306
参数database：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8
对象的方法
close()关闭连接
commit()提交--插入数据时，必须执行才能添加
cursor()返回Cursor对象，用于执行sql语句并获得结果
c.获取对象(Cursor对象):
用于执行sql语句
获取Cursor对象：调用Connection对象的cursor()方法
cs1=conn.cursor()
对象的方法
close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一元组，再将这些元组装成一个元组返回。
对象的属性
rowcount只读属性，表示最近一次execute()执行后受影响的行数connection获得当前连接对象。
d.参数化
sql语句的参数化，可以有效防止sql注入
用python实现sql语句，在查询商品时，如果如下：
goods_name = input("请输入你要查询的商品名：")
sql = 'select * from goods where name=%s;' % goods_name
cs1.execute('select * from goods where name=%s;' % goods_name)
如果输入 1'or 1=1 or '1
相当于sql = 'select * from goods where name= '1' or '1=1' or '1 ;'
这就是sql注入，为了防止sql注入，使用参数化
即：cs1.execute('select * from goods where name=%s', [goods_name])
例子1 插入数据、修改数据、删除数据
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，那么就都执行coon.commit
conn.commit()
# 关闭cursor对象
cs1.close()
# 关闭connection对象
conn.close()
if __name__ == '__main__':
main()
例子2 面向对象思想实现
from pymysql import *
class JD(object):
"""商城类"""
def __init__(self):
"""
初始化属性
创建connection链接
获取surcor对象
"""
self.conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
self.cs1 = self.conn.cursor()
def __del__(self):
"""
销毁对象时，关闭链接和对象
"""
self.cs1.close()
self.conn.close()
def exe_sql(self,sql):
"""执行sql语句，打印结果"""
self.cs1.execute(sql)
for temp in self.cs1.fetchall():
print(temp)
def show_all_items(self):
"""显示所有商品"""
sql = "select *from goods;"
self.exe_sql(sql)
def show_all_cates(self):
"""显示所有商品分类"""
sql = "select *from goods_cates;"
self.exe_sql(sql)
def show_all_brands(self):
"""显示所有商品品牌"""
sql = "select *from goods_brands;"
self.exe_sql(sql)
def add_cate(self):
"""添加商品分类"""
brand_name = input("请输入商品的品牌:")
sql = "insert into goods_brands (name) values ('%s');" % brand_name
self.cs1.execute(sql)
self.conn.commit()
def get_into_byname(self):
"""查询一个商品的详情"""
name = input('请输入你要查询的商品的名称：')
sql = "select * from goods where name=%s"
self.cs1.execute(sql,[name])
print(self.cs1.fetchall())
def print_menu(self):
print("---------京东-----------")
print("1:所有商品")
print("2:所有商品分类")
print("3:所有品牌分类")
print("4:添加一个品牌分类")
print("5:查询一个商品的详情")
print("6:退出")
num = input("请输入对应的序号:")
return num
def run(self):
while True:
num = self.print_menu()
if num == "1":
self.show_all_items()
elif num == "2":
self.show_all_cates()
elif num == "3":
self.show_all_brands()
elif num == "4":
self.add_cate()
elif num == "5":
self.get_into_byname()
elif num == "6":
break
else:
print("请输入对应的序列号:")
def main():
jd = JD()
jd.run()
if __name__ == '__main__':
main()