Py学习  »  DATABASE

1.MySQL(完结) 部分参照 https://blog.csdn.net/m0_37989980/article/details/103413942

子子new子 • 3 年前 • 53 次点击  

DDL (操作数据库)

1.创建数据库 : 
	create database 数据库名;
	Create database if not exist 数据库名;
	Create datebase if not exist 数据库名 character set 码表名字
2.查看数据库:
 	show databases; 
    	查看数据库的定义信息 :
		show create database 数据库名;
3.修改数据库(修改定义码表):
	alter database 数据库名 character set 码表名;
4.删除数据库: 
	drop database 数据库名;
5.使用数据库: 
	use 数据库名;
		查看正在使用的数据库:
		 select(选择) database();
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

#DML(操作表结构):

1.添加表:
	 create table 表名 (
	 列名1 字段类型,
	 列名2 字段类型)

	 创建一个表结构相同的表:	
				 Create table 新表名 like 旧表名;
	
【注】: 类型 int varchar(最大长度) double(x,x) like date TIMESTAMP(时间戳)
2.查看表:
	 show tables;
		查看表结构 : desc表名;
		查看创建表的sql语句:show create table 表名;
3.删除表 :
	 drop table 表名;
		判断表是否存在之后再删除:
		drop table if exists 表名;
4.修改表 :
	A.添加列:
		alter table 表名 add 列名 类型;
	B.修改列类型:
		alter table 表名 modify(修改) 列名 新类型;
	C.修改列名:
		alter table 表名 change 列名 新列名 新类型;
	D.删除列:
		alter table 表名 drop 列名;
	E.修改表名;
		rename table 表名 to 新表名;
	F.修改表的字符集;
		alter table 表名 character set 新码表;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

DML(操作表中的数据) 对表中的记录进行增删改查

1.插入记录:
	insert(插入) into 表名 (字段1, 字段2,...字段n) value(值1, 值2, ... 值n);
2.更新表中的记录 :
	update(更新) 表名 set 列名 = 要修改的值 where [条件];
3.删除表中的记录 :
	A.delete from表名 where [条件];
	B.Truncate table 表名;  [删除表结构 创建新表]
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

DQL(查询表中的数据)

1. 语法:
	select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定


1.查询: select 列名1, 列名2,.....列名n from 表名 where [条件];
	A.查询所有 :
		select * from 表名;
	B.查询指定列 :
		select 字段名 from 表名;
	C.指定的列的别名查询:
		select 字段名 as 别名 from 表名 as 表别名;
	D.清除重复值查询:
		select distinct(截然不同的) 字段名 from 表名;
	E.查询结果参与运算:
		select 字段1 + 字段2 as 别名 from 表名
	F.条件查询 :
		select 字段名 from表名 where [条件];
	G.运算符参与查询:
		select 字段名 from 表名 where 字段名 >= ?;
		select 字段名 from 表名 where 字段名 in (x,y);
			Between .. and;
	H.模糊查询:
		select 字段名 from 表名 where 字段名 like 通配符
	I.排序 :
		select 排序输出的字段 from 表名 order by 排序的标准字段名1 [asc][desc]
		select from where order by 
	J.在某个条件下多层排序查询 :
		排序输出的字段名 from 表名where 排序的范围 order by 排序的标准的字段名1 [asc][desc],排序的标准的字段名2 [asc][desc];
	    例如:select * from student order by math asc , chinese asc;
			 上面的sql代表 在数学分数相同时 用语文分数排序 而且输出的是排序后的整体
	K.聚合函数 :
		select avg (字段名) 别名form 表名; 
	L.分组: 
		select 输出的字段1,输出的字段2,...输出的字段n  from 表名 group by 分组字段 having 条件;  

	例如:按性别分组 输出显示性别和人数和数学平均分
	Select sex, count(number),avg(math) from student group by sex ;

	例如:按性别分组 并且只统计年纪大于25岁的人的平均分和人数 并且不输出人数少于2的组
	Select sex,count(*),avg(math) from student where age > 25 group by sex having count(*) > 2;

	复杂:
	SELECT DISTINCT *, AVG(math + IFNULL (english,0) + java) 总分 FROM student01 WHERE math != 0 GROUP BY id HAVING 总分 > 100 ORDER BY math DESC, java DESC , english DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56

约束:




    
主键约束: primary(主要的)  key 
非空约束: not null
唯一约束: unique(独特的)
外键约束: foreign (外国)  key

1.主键约束:
	A.在创建表的时候 添加主键约束
	语法:create table 表名(
	Id int primary key auto_increment       (增量)
	);
	注:这里的auto_increment是自增

	B.再创建表之后 删除主键约束 或者添加主键约束
	语法_删除: alter table 表名 drop primary key;
	[因为主键只有一个 所以不用声明drop的字段]
	语法_添加:alter table 表名 add primary key(主键字段);
2.唯一约束
	A.语法:
	create table 表名(
	Id int primary key auto_increment,
	Name varchar(20) unique
	);

3.非空约束
	语法:
	create table 表名(
	Id int primary key auto_increment,
	Name varchar(20) not null,
	);
4.外键约束
	语法:
	1. 创建时:
	Create table 表名 (
	Id int primary key auto_increment,
	Name varchar(20) not null,
	dep_id int,

	//外键约束
	Constraint dep_emp_fk foreign key (dep_id) references department (id);
	约束 外键名称 外键关键字 (外键字段) 参照 主表(主键字段)
	) 
	2. 创建完:
	Alter table 表名add constraint dep_emp_fk foreign key (dep_id) references department(id);
	3. 删除:
	Alter table employee drop foreign key dep_emp_fk;

【注:】 外键约束的使用:
	1. 必须有主键
	2. 在一对一的表关系中 任意定义副表定义外键 关联主表中的主键
	3. 在一对多 或者 多对一 的表关系中 
		1. 多的那方 为副表 在多的那方定义外键 关联主表的主键
		例如:
			有一个学生表 一个课程表 
			那么一个学生可以对应多个课程
			写法:  create table class (
				   	c_id int,
					c_name varchar(20),
					stu_id,
					-- 定义外键 
					foreign key (stu_id) references student(id)
					);
	4. 在多对多的表关系中 要是用一张中间表 扮演副表的角色 分别关联两个多的主表
		1. 注意:中间表需要有集合主键
		2. 写法:
			例如:
				有一个账号表 一个要购买的游戏道具表 那么这两个表之间就是多对多的关系
				以为 一个账号可以购买多个游戏道具 而一种道具也可以被多个账号购买
								写法:
				1. 首先创立一个账号表
					 create table user (
					u_id int primary key unique auto_incream,
					u_name varchar(20) not null 
					);	
				2. 然后创立一个游戏道具表
					create table gametools(
					gt_id int primary key unique auto_incream,
					ft_name varchar(30) not null unique
					);
				3. 创立一个中间表
					create table tool_user(
					gameToolsId int,
					userId int,
					/*集合主键*/
					primary key(gameToolsId,userId),
					/*外键*/
					foregin key (gameToolsId) references gametools(gt_id),
					foregin key (userId) references user(u_id)
					);
			4. 级联操作
					1. 添加级联操作
						语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
										
							 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
					2. 分类:
						1. 级联更新:ON UPDATE CASCADE 
						2. 级联删除:ON DELETE CASCADE
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96

数据库的设计

1. 多表之间的关系
	1. 分类:
		1. 一对一(了解):
			* 如:人和身份证
			* 分析:一个人只有一个身份证,一个身份证只能对应一个人
		2. 一对多(多对一):
			* 如:部门和员工
			* 分析:一个部门有多个员工,一个员工只能对应一个部门
		3. 多对多:
			* 如:学生和课程
			* 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
	2. 实现关系:
		1. 一对多(多对一):
			* 如:部门和员工
			* 实现方式:在多的一方建立外键,指向一的一方的主键。
		2. 多对多:
			* 如:学生和课程
			* 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
		3. 一对一(了解):
			* 如:人和身份证
			* 实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。

	3. 案例
		-- 创建旅游线路分类表 tab_category
		-- cid 旅游线路分类主键,自动增长
		-- cname 旅游线路分类名称非空,唯一,字符串 100
		CREATE TABLE tab_category (
			cid INT PRIMARY KEY AUTO_INCREMENT,
			cname VARCHAR(100) NOT NULL UNIQUE
		);
		
		-- 创建旅游线路表 tab_route
		/*
		rid 旅游线路主键,自动增长
		rname 旅游线路名称非空,唯一,字符串 100
		price 价格
		rdate 上架时间,日期类型
		cid 外键,所属分类
		*/
		CREATE TABLE tab_route(
			rid INT PRIMARY KEY AUTO_INCREMENT,
			rname VARCHAR(100) NOT NULL UNIQUE,
			price DOUBLE,
			rdate DATE,
			cid INT,
			FOREIGN KEY (cid) REFERENCES tab_category(cid)
		);
		
		/*创建用户表 tab_user
		uid 用户主键,自增长
		username 用户名长度 100,唯一,非空
		password 密码长度 30,非空
		name 真实姓名长度 100
		birthday 生日
		sex 性别,定长字符串 1
		telephone 手机号,字符串 11
		email 邮箱,字符串长度 100
		*/
		CREATE TABLE tab_user (
			uid INT PRIMARY KEY AUTO_INCREMENT,
			username VARCHAR(100) UNIQUE NOT NULL,
			PASSWORD VARCHAR(30) NOT NULL,
			NAME VARCHAR(100),
			birthday DATE,
			sex CHAR(1) DEFAULT '男',
			telephone VARCHAR(11),
			email VARCHAR(100)
		);
		
		/*
		创建收藏表 tab_favorite
		rid 旅游线路 id,外键
		date 收藏时间
		uid 用户 id,外键
		rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
		*/
		CREATE TABLE tab_favorite (
			rid INT, -- 线路id
			DATE DATETIME,
			uid INT, -- 用户id
			-- 创建复合主键
			PRIMARY KEY(rid,uid), -- 联合主键
			FOREIGN KEY (rid) REFERENCES tab_route(rid),
			FOREIGN KEY(uid) REFERENCES tab_user(uid)
		);

	
2. 数据库设计的范式
	* 概念:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求

		设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
		目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

	* 分类:
		1. 第一范式(1NF):每一列都是**不可分割**的原子数据项
		2. 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的**部分函数**依赖)
			* 几个概念:
				1. 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
					例如:学号-->姓名。  (学号,课程名称) --> 分数
				2. 完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
					例如:(学号,课程名称) --> 分数
				3. 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
					例如:(学号,课程名称) -- > 姓名
				4. 传递函数依赖:A-->B, B -- >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
					例如:学号-->系名,系名-->系主任
				5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
					例如:该表中码为:(学号,课程名称)
					* 主属性:码属性组中的所有属性
					* 非主属性:除过码属性组的属性
					
		3. 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除**传递依赖**)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111

数据库的备份和还原

1. 命令行:
	* 语法:
		* 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
		* 还原:
			1. 登录数据库
			2. 创建数据库
			3. 使用数据库
			4. 执行文件 source 文件路径
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

* 查询语法:
	* select 
		列名列表
	  form 
		表名列表
	  where.. 
* 笛卡尔积:
	* 有两个集合A,B .取这两个集合的所有组成情况。
	* 要完成多表查询,需要消除无用的数据
* 多表查询的分类:
	1. 内连接查询:
		1. 隐式内连接:使用where条件消除无用数据
		2. 显式内连接:
			1. 语法:select 字段列表 from 表名1 join 表名2 on 条件
			2. 这里的 on 条件 相当于 where 条件
		3. 内连接查询
			1. 从哪些表中查询数据
			2. 条件是什么
			3. 查询哪些字段
	2. 外链接查询:
		1. 左外连接:
			* 语法: select 字段名 from 表1 left join 表名2 on 条件
			* 这里的 on 相当于 where
			* 查询的是左表中的所有数据 和两个表的交集部分(可以解决 null 值不显示的弊端)
		2. 右外连接:
	3. 子查询
		1. 概念 查询中嵌套查询,被嵌套查询 叫做子查询


		* 分类
			1.  子查询的结果是单行 单列的 
				* 子查询可以作为条件, 使用运算符来判断
				* -- 查询员工工资小于平均工资的人
				SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
			2. 子查询的结果是多行单列的:
				* 子查询可以作为条件,使用运算符in来判断
				-- 查询'财务部'和'市场部'所有的员工信息
				SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
				SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
				-- 子查询
				SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

			3. 子查询的结果是多行多列的:
				* 子查询可以作为一张虚拟表参与查询
				-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
				-- 子查询
				SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
				WHERE t1.id = t2.dept_id;
				
				-- 普通内连接
				SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51

事务




    
* 语法
	start transaction (事务,交易)
	commit(提交,犯罪,交付)
	rollback
* 单词
	1. transaction 事务
	2. commit 交付
	3. rollback 回滚
	4. repeatable read 重复的
			(mysql默认隔离级别)
	5. read uncommit  
	6. read commit  
			 (oracle默认隔离级别)
	7. serializable 可串行化的
	8. 查询隔离级别: 
	select @@tx_isolation;
	9. 设置隔离级别:
	set global transaction isolation level 级别字符串;
	10. identified 被认同的
	11. grant 授予
	12. revoke 撤销
1. 事务的操作
	1. 事务的概念:
		* 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
	2. 操作:
		1. 开启事务: start transaction;
		2. 提交事务: commit
			1. mysql在执行(dml_ 表的增删改) 操作后默认提交
			2. oracl不默认提交
			3. 修改事务的默认提交方式:
				* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
				* 改默认提交方式: set @@autocommit = 0;
		3. 回滚: rollback
	3. 执行的例子:
		* start transaction
		* update lisi set balance = balance - 500 where name = 'lisi';
		* 出错了.. (程序停止 此时lisi少了500 而wangwu没多 违背了事务的特征之一 -> 一致性)
		* update wangwu set balance = balance + 500 where name = 'wangwu';
		* -- 发现执行没有出现问题
		* commit; --提交
		* -- 发现执行出现问题
		* rollback; -- 回滚
2. 事务的四大特征
	1. 原子性
	2. 持久性
	3. 隔离性
	4. 一致性
3. 事务的隔离级别
		1. 多个事务读取表中数据存在问题
			1. 脏读
			2. 虚读
			3. 幻读
		2. 隔离级别
			1. read uncommitted;读未提交
			* 产生的问题 所有问题
			2. read committed 
			* 产生的问题 虚读 幻读(oracle默认)
			3. repetable read; 重复读
			* 产生的问题 幻读(mySql默认)
			4. serialozable 串行化(锁表)
			*  可解决所有问题 但效率太低
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61

DCL(管理用户 授权等操作)

* SQL分类:
	1. DDL(数据库和表的操作)
	2. DML(表的增删改)
	3. DQL(表的查询)
	4. DCL(管理用户 授权等操作)
* DCL的操作人员 一般为DBA
* DCL:
	1. 用户管理
		1. 增
			1. create user '用户名'@'主机名' identified by '密码';
		2. 删
			1. drop user '用户名'@'主机名';
		3. 改
			1. 修改密码_1
				* update user set passwrod = password('新密码') where user = '用户名';
			2. 修改密码_2
				* set password for '用户名'@'主机' = password('新密码');
		4. 查
			1. use mysql
			2. select * from user;
	2. 权限管理:
		1. 查询权限:
			-- 查询权限
			SHOW GRANTS FOR '用户名'@'主机名';

		2. 授予权限:
			-- 授予权限
			grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

		3. 撤销权限:
			-- 撤销权限:
			revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/72149
 
53 次点击