mysql数据库-基础

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()