欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql数据库-基础

程序员文章站 2022-06-01 16:47:11
...

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