python全栈开发第七天（MySQL数据库，安装与卸载，对数据的增删改查，对库、表的操作命令）

本文链接：https://blog.csdn.net/weixin_45410462/article/details/99995925

第七天

概念介绍

1、数据库： 存储数据的仓库，按照数据结构，来组织，管理和存储数据，并提供了增、删、改、查的功能。

2、事务： 将一组增删改的操作看成一个独立的执行的单元，要么都没成功，如果有一个操作失败，那么数据库会执行回滚，一组操作都会不生效。

3、事务的特性：
原子性：事物被当做一个不可分割的执行单元，要么全部成功，要么全部失败，成功则将数据应用到数据库，失败不会对数据产生影响。
一致性：开始事物前的状态必须和事物执行之后的状态保持一致。
隔离性：当用户并发访问数据库时，数据库会给每个用户线程开启一个事物，事物和事物之间相互隔离，互不影响。
持久性：一旦事物提交，那么数据就会永久行的应用于数据库，就算机器出现故障，也不会将数据修改。

4、事物的回滚：
事物作为一个独立不可分割的执行单元，要么都成功，只要这一组操作中有一个失败，事物会执行回滚，恢复到事物执行之前的状态。

5、在不考虑事物的隔离性的情况下，会导致以下情况
脏读：
当以一个事物执行时，访问了另一个未提交事物的数据，并使用了这条数据。
A给B 去转转账，
A账户扣钱 B账户增加钱
A事物并没有提交通知B查看自己账户
B看到账户收到转账
A在后续的操作中出现失误，导致事物回滚
B 之后B在查看账户时发现账户的钱又不见了
不可重复读：
事物A多次读取一条数数据，事物B对这条数据做了修改，最终导致A事物两次读取的数据不一样。
幻读：
事物A读取一张表中的所有数据，事物B在这个表中添加或者删除了一条数据，导致A的结果和实际结果不符，类似于出现了幻觉。

6、事物的四种隔离级别：
Read uncommit:读未提交最低级别，可能会出现任何情况
Read commit:读已提交可以避免脏读
Repeatable read:可重读可以避免脏读和不可重复度
Serializable:串行化可避免脏读不可重复读幻读最高的隔离级别

7、数据库的分类：
关系型数据库
是建立在数据模型的基础之上，借用集合、代数等数据概念，来去管理数据

非关系型数据库
Redis mongodb
{key:val}

8、Mysql引擎：
Myisam:在5.5版本之前是默认引擎
不支持事物
数据查询速度快
相对数据安全性差
将数据存在三个文件当中
一个文件存表结构一个文件存索引一个文件存数据

Innodb：在5.5之后默认引擎
支持事物
相对于myisam 查询效率低
数据安全性高
将数据存在两个文件中
一个文件存表结构一个文件存索引和数据

安装与卸载

mysql-5.7.25-winx64.zip下载路径

1.把mysql-5.7.25-winx64.zip解压放到C盘目录下

2.MySQL软件bin目录添加到PATH环境变量中 C:\mysql-5.7.25-winx64\bin

3.在MySQL安装目录下创建my.cnf文件

4.my.cnf中粘贴默认选项

[mysqld]
basedir=c:/mysql-5.7.25-winx64
datadir=c:/mysql-5.7.25-winx64/data
character-set-server=utf8
explicit_defaults_for_timestamp
[mysql]

5.安装MySQL服务，以管理员运行cmd
执行切换到bin目录下，执行mysqld --install MySQL

6.查看MySQL服务 services.msc 或者计算机->管理–>服务和应用程序–>服务

7.初始化安装MySQL数据库在bin目录下执行
mysqld --initialize-insecure，无密码安装，看是否多data文件夹

8.启动MySQL服务

9.MySQL连接测试 mysql -uroot -p
select user,host,authentication_string from mysql.user;

卸载mysql

1、停止服务，删除C:mysql该目录下剩余了所有文件，把mysql文件夹也删了

2、windows+R运行“regedit”文件，打开注册表

3、删除注册表：HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL文件夹

4、删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL文件夹，如果没有可以不用删除了

5、使用管理员权限进去cmd,sc delete 要删除的服务名 ,这里是MySQL

数据的基本认识和操作

1、启动服务
      在命令行输入 net start mysql
      停止服务 net stop mysql
2、连接数据库
      mysql -u -p -h -P
      -u 后面加你的mysql的用户名其中root是权限最高
      -p 你的mysql密码
      -h 你mysql服务的地址 ip
      -P mysql数据库的端口号默认3306
      完整写法：
      Mysql -uroot -p -hlocalhost -P3306
      数据库服务装在了本地所以-h可以省略
      端口号使用是默认端口索引-P也可以省略

在这里插入图片描述
3、Mysql数据库的基本结构
Mysql关系型数据库管理软件
Mysql>database>table>filed
多条数据组成一张表
多张表组成一个库
Mysql管理多个库

4、Sql语句分类：
SQL结构化查询语言，用于关系型数据库数据的操作
DDL(定义) 一般适用于对库的操作和表的操作
DML(操作) 一般是数据的添加数据的修改数据的删除
DQL(查询) 一般是查询
DCL(控制) 一般使用权限管理一般是有dba来去操作

5、Sql的语法规则
sql语句必须以分号结尾
Sql 不去分大小写但是一般文档中 sql关键都是大写

6、数据库命令

查看所有的库
SHOW DATABASES;
在这里插入图片描述
创建库
CREATE DATABASE 库名;

切换数据库
USE 库名

查看当前所在的库
SELECT DATABASE()

删除数据库
DROP DATABASE 库名

7、表操作命令

查看当前库中所有的表
SHOW TABLES;
在这里插入图片描述

创建表
CREATE TABLE 表名(字段类型约束,字段类型约束)CHARSET=UTF8;

create table student(
      id int unsigned not null
      auto_increment primary key,
      name char(20),
      age int,
      gender char(1),
      email varchar(32),
      class varchar(32)
)charset=utf8;
在这里插入图片描述

查看表结构
DESC 表名;
在这里插入图片描述

查看建标语句
SHOW CREATE TABLE 表名;
在这里插入图片描述

删除表：
DROP TABLE 表名;

8、数据的基本操作

(1)、添加数据
格式：INSERT INTO 表名(字段1,字段2,…) values(值1,值2,…)
指定所有字段添加数据
不指定字段添加数据
      向student表中添加一条数据值的顺序必须按照表结构的顺序来添加
      INSERT INTO student values(5,‘赵六’,30,‘1’,‘z@163.com’,‘web’);
指定部分字段添加
      INSERT INTO student(id,name) values(6,‘田七’);
一次添加多条数据
      INSERT INTO student values
      (7,‘小九九’,28,‘0’,‘w@.com’,‘ui’),
      (8,‘九九’,19,‘1’,‘j@qq.com’,‘python’),
      (9,‘后羿’,80,‘1’,‘h@qq.com’,‘python’),
      (10,‘吴刚’,79,‘1’,‘w@qq.com’,‘python’);

(2)、更新数据
格式： UPDATE 表名 SET 修改的字段 WHERE 条件
更新name为吴刚的数据把年龄修给成77 把性别改成0
UPDATE student SET age=77,gender=‘0’ WHERE name=‘吴刚’;

(3)、删除数据
格式：DELETE FROM 表名 WHERE 条件
删除name是吴刚的数据
DELETE FROM student WHERE name=‘吴刚’;

(4)、查询数据
格式：SELECT 字段 FROM 表名;
查询所有的数据
SELECT * FROM student;

9、mysql 数据类型（常见的）

(1)、数值类型
Int 4字节
Tinyint 1字节
decimal 一般存浮点型
格式 decimal(5,1) 一共可以存5位有一位小数

(2)、字符串类型
char 定长字符串
varchar 变长字符串
char(20) 就算你当前字段中只存储了1个字符那么它也是占据20个字符的大小
varchaar(20)最多能存20个字符如果存了小于20个字符它的实际占据大小是以你存的内容为准

(3)、text文本类型
时间日期类型
datetime 混合事件日期类型年月日时分秒格式年-月-日时:分:秒

10、常见的字段约束

unsigned 无符号一般给数值型的字段添加
如果添加代表当前字段只能存正数
int(5) 5代表显示的宽度如果是有符号默认是11 如果是无符号默认10
not null 不能为空默认字段可以为空如果添加了那么此字段不能为空
default 默认值
primary key 主键约束(主键索引) 不能为空且唯一用于表示数据的唯一标识
unique 唯一约束(唯一索引) 可以为空不能重复具有唯一性

创建一张表 demo
有字段 id 主键索引
uname 字符串类型不能为空
email 字符串类型可以为空
gender 字符串不能为空默认值为 2

create table demo(
	id int unsigned not null  auto_increment primary key,
	uname varchar(32) not null,
	email varchar(32),
	gender varchar(5) not null default '2'
)charset=utf8;

11、表结构的操作

格式： ALTER TABLE 表名操作

添加字段 add
	给student表添加一个phone
	ALTER TABLE student ADD phone varchar(11) not null default '110';

修改字段 modify/change
	修改字段约束
	将phone字段的类型改成char类型
	ALTER TABLE student MODIFY phone char(11);
	将phone字段的类型改成int类型
		注意：将字符串转成数值型的时候，当前字段的值都必须是数字表示的字符串
	ALTER tABLE student MODIFY phone int;
	修改字段名
	将phone字段的名字改成 aa
	ALTER TABLE student CHANGE phone aa varchar(11);
	将字段的约束改成 char(11);
	ALTER TABLE student CHANGE aa aa char(11);
	
删除字段 drop
	删除aa字段
	ALTER TABLE student DROP aa;
	
修改表名 rename as
	修改student表名为 students
	ALTER TABLE student RENAME AS students;
	
修改表的编码格式
	ALTER TABLE students CHARSET=GBK;
	
修自增值
	修改自增值为20
	ALTER TABLE students AUTO_INCREMENT=20;

12、运算符

算术运算符： + - * / %
比较运算符：> < >= <= = != <>
特殊比较运算符：in, not in,is null,is not null,like,between and
逻辑运算符：and or not
逻辑运算符优先级 or<and<not

13、基本查询语句

1、Where条件查询
运算符可以用在where条件

查询所有的数据
	SELECT * FROM 表名 WHERE 条件

练习:
1.查询年龄不等于18岁的所有用户信息
SELECT * FROM students WHERE age!=18;

2.查询班级为python的所有用户信息
SELECT * FROM students WHERE class='python';

3.查询年龄大于16岁的所有用户信息
SELECT * FROM students WHERE age>16;

4.查询年龄在18到24岁之间的所有用户信息 包含18和24岁;
SELECT * FROM students WHERE age>=18 and age<=24;
SELECT * FROM students WHERE age between 18 and 24;

5.查询年龄不在18到24之间的所有用户信息 not between 不包含开始和结束
SELECT * FROM students WHERE age<18 or age>24;
SELECT * FROM students WHERE age not between 18 and 24;

6.查询id值是1，3，4，6的所有用户信息
SELECT * FROM students WHERE id=1 or id=3 or id=4 or id=6;
SELECT * FROM students WHERE id in (1,3,4,6);

7.查询python班和web班级的所有女生信息
SELECT * FROM students WHERE (class='python' or class='web') and gender='0';

2、模糊查询
like
% 任意位的任意字符
_ 1位任意字符

1.查询名字以‘小’开头的用户信息
SELECT * FROM students WHERE name like '小%';

2.查询emial中包含q的所有用户信息
SELECT * FROM students WHERE email like '%q%';

3.查询姓名是两个字的用户信息
SELECT * FROM students WHERE name like '__';

4.查询姓名以小开头并且是两个字的用户信息
SELECT * FROM students WHERE name like '小_';

3、聚合函数
max() min() count() avg() sum()

1.统计当前表中一共有多少条数据
	SELECT count(*) FROM students;

2.查询python班级最大年龄最小年龄以及平均
SELECT max(age),min(age),avg(age) FROM students WHERE class='python';

3.统计男生的数量
SELECT count(*) FROM students WHERE gender='0';

4、去除重复的数据：distinct
用的少一般用group by

1.查询所有的班级
	SELECT distinct class FROM student;
	SELECT class FROM students group by class;

2.查询不重复的班级和性别
SELECT distinct class,gender FROM student;

5、分组group by
注意：count(*) 在分组中使用时，统计的分别是分组后每个组的数据条数

1.查询表中有哪些班级
SELECT class FROM students GROUP BY class;

2.统计当前表中男生和女生的人数各多少人
SELECT gender,count(*) FROM students GROUP BY gender;

3.统计每个班级的人数
SELECT class,count(*) FROM student GROUP BY class;

4.统计每个班，男生和女生个多少人
SELECT class,gender,count(*) FROM students GROUP BY class,gender;

Having 子句查询
	只能在分组后面去使用 
	类似于where 但是having筛选的是分组后的数据

1.统计每个班级的人数，并查询出班级人数大于2的班级
	SELECT class,count(*) FROM students GROUP BY class HAVING count(*)>2;

2.查询每个班级男生人数大于2的班级
SELECT class,gender,count(*) FROM students GROUP BY class,gender HAVING gender='1' and count(*)>2;

先查询出所有的男生 在按照班级分组 然后在用having过滤
SELECT class,count(*) FROM students WHERE gender='1' GROUP BY class HAVING COUNT(*)>2;

6、排序：order by
默认升序排序 asc 可以省略不写
desc 是降序排序

 1.查询所有学员信息，并按照年龄进行降序排序
 select * from student order by age desc;

2查询班级为python 的男生信息，并按照年龄进行降序排序
select * from student where class='python' gender='1' order by age desc;

7、分页获取数据：limit
limit 1 ；从前面获取一条数据
limit 2,1 ；跳过前两条数据获取一条数据

1.获取前三个学员的数据
select * from student limit 3;

2.获取第二个学员的数据
select * from student limit 1,1;

使用limit 实现分页效果
要求 每两条数据为一页
获取第一页数据
select * from student limit 0,2; (1-1)*每一页数据的条数
获取第二页数据
select * from student limit 2,2；（2-1）*2=2
获取第三页数据
select * from student limit 4,2；（3-1）*2=4

获取第五页数据 （5-1）* 2=8
select * from student limit 8,2;

练习：

1.获取python班级年龄最大的2个学生
select * from student where class='python'  order by age desc limit 2;

2.获取每个班级的平均年龄，并按照平均年龄进行降序排序
select avg(age) from student group by class order by avg(age) desc;

3.统计每个班级的人数 获取人数最多的两个班
select class count(*) from student group by class order by count(*) desc limit 2;

注意：顺序问题
where group by order by limit

14、数据关系

一般用于描述多张表之间的关系

一对一：
一张表中唯一一条数据对应另一张表中唯一一条数据

	users								
		id	uname	phone	password	age	emial	pic	address

	把一张表的数据库查分成两张表，并通过外键字段将两张表中的数据进行关联

	user			
	id	uname	password
	1	张三	123
	2	李四	123

	uinfo							
	id	phone	age	emial	pic	address	u_id
	1	110	18	zh@qq.com	1.jpg	beijing	1
	2	120	25	123	123	231	2

	1.创建两个表
	create table user(
		id int unsigned not null auto_increment primary key,
		uname char(32)
	)
	create table uinfo(
		id int unsigned not null auto_increment primary key, 
		phone varchar(11),
		age int,
		email varchar(32),
		gender varchar(10),
		address varchar(255) default '北京',
		u_id int
	)

	2.插入数据
	insert into user values(null,'老张'),(null,'老牛'),(null,'老刘'),(null,'老钟');
	insert into uinfo values(null,'110',18,'laozhang@qq.com','1','北京昌平',1),
							(null,'120',28,'laoniu@qq.com','1','北京朝阳',2),
							(null,'119',38,'laoliu@qq.com','1','黑龙江',3);

	通过用户去查询用户的详情
		根据用户名老张 查询老张的详细信息
		1.select id from user where uname='老张';
		2.select * from uinfo where u_id=1;

		select * from uinfo where u_id=(select id from user where uname='老刘');

	通过用户详情去查询用户
		已知手机号为120 查询手机号对应的用户

		select * from user where id=(select u_id from uinfo where phone='120');

		查询年龄为28和38的用户信息
		1.select u_id from uinfo where age=28 or age=38;
		2.select * from user where id in(select u_id from uinfo where age=28 or age=38);

一对多
一张表中的一条数据对应另一张表中的多条数据

parent		
		id	name
		1	老张
		2	老李

	child			
		id	name	p_id
		1	张1		1
		2	张2		1
		3	李1		2
		4	李2		2

	根据父亲找儿子
		查找老张所有的孩子
		select * from child where p_id=(select id from parent where name='老张');
	根据儿子找父亲
		查询张1的父亲
		select * from parent where id=(select p_id from child where name='张1' );

多对多
一张表中的多条数据对应另一张表中的多条数据

teachers		
		id	name
		1	老张
		2	老李
		3	老边
	students		
		id	name
		1	小黄
		2	小兰
		3	小绿
		4	小花
		5	小红
			
	t_s		
		t_id	s_id
		1		1
		1		2
		1		3
		3		5
		1		5
		2		5

1.创建表
	create table teacher(
	id int unsigned not null auto_increment primary key, 
	name varchar(10)
	);

	create table teacher_student(
	id int unsigned not null auto_increment primary key, 
	t_id int,
	s_id int 
	);

2.添加数据
	insert into teacher values(null,'老刘'),(null,'老边'),(null,'老温');

表数据：
	老师：
		+----+------+
		| id | name |
		+----+------+
		|  1 | 老刘 |
		|  2 | 老边 |
		|  3 | 老温 |
		+----+------+
	学生：
		+----+--------+------+--------+-----------------+--------+
		| id | name   | age  | gender | email           | class  |
		+----+--------+------+--------+-----------------+--------+
		|  2 | 小张   |   19 | 1      | xiaowang@qq.com | python |
		|  5 | 赵六   |   30 | 1      | z@163.com       | web    |
		|  6 | 田七   | NULL | NULL   | NULL            | NULL   |
		|  7 | 王八   |   28 | 0      | w@.com          | ui     |
		|  8 | 九九   |   19 | 1      | j@qq.com        | python |
		|  9 | 后羿   |   80 | 1      | h@qq.com        | python |
		| 10 | 庄周   | NULL | NULL   | NULL            | NULL   |
		| 11 | 阿珂   |   18 | 0      | NULL            | python |
		| 12 | 贾克斯 |   24 | 1      | NULL            | 上单   |
		+----+--------+------+--------+-----------------+--------+
	关系：
		添加关系

			1     2
			1     5
			1     12
			2     2
			2     5
			2     12
			2     7
		insert into teacher_student values(null,1,2),(null,1,5),(null,1,12),(null,2,2),(null,2,5),(null,2,12),(null,2,7);

		+----+------+------+
		| id | t_id | s_id |
		+----+------+------+
		|  1 |    1 |    2 |
		|  2 |    1 |    5 |
		|  3 |    1 |   12 |
		|  4 |    2 |    2 |
		|  5 |    2 |    5 |
		|  6 |    2 |   12 |
		|  7 |    2 |    7 |
		+----+------+------+

	查询：
		查老刘老师带过的所有学生的信息
		1.查老师的id
		select id from teacher where name='老刘';
		2.根据老师的id去关系表查对应学生id;
		select s_id from teacher_student where t_id=(select id from teacher where name='老刘');
		3.根据学生id去查学生表
		select * from students where id in(select s_id from teacher_student where t_id=(select id from teacher where name='老刘'));

		查老刘老师带过的年龄大于19岁的学生信息
		select * from students where id in (select s_id from teacher_student where t_id in(select id from teacher where name='老刘')) and age>19;

		查询贾克斯被哪些老师带过
			1.根据名字查询学生id
			select id from students where name='贾克斯'
			2.根据学生的id去关系表查询对应t_id
			select t_id from teacher_student where s_id=(select id from students where name='贾克斯');
			3.根据t_id 去老师表查
			select * from teacher where id in(select t_id from teacher_student where s_id=(select id from students where name='贾克斯'));

外键

逻辑外键
物理外键会增加表和表之间的复杂度，会降低查询的速度

外键约束：foreign key(当前表的外键字段) references 关联的表名(关联表的字段)
外键主要使用约束一对多关系
一般情况下多表中的外键字段关联一表中的主键

学员和宿舍的关系
	student      dormitory
					id       num

1.创建表
	create table dormitory(
		id int unsigned not null auto_increment primary key,
		num int
	); 
2.学生
	create table student(
		id int unsigned not null auto_increment primary key,
		name char(32),
		age int,
		gender int,
		d_id int unsigned,
		foreign key(d_id) references dormitory(id)
	);

专业mojar 课程course  mojar_course
	create table mojar(
		id int unsigned not null auto_increment primary key,
		name char(32)
	); 

	create table course(
		id int unsigned not null auto_increment primary key,
		name char(32)
	);

	create table mojar_course(
		id int unsigned not null auto_increment primary key,
		m_id int unsigned,
		c_id int unsigned,
		foreign key(m_id) references mojar(id),
		foreign key(c_id) references course(id)
	);

注意：添加外键约束时
	1.要先有外键字段
	2.外键字段的类型 必须和要关联的字段类型保持一至

15、数据库多表查询

嵌套查询（子查询）

将一条查询语句嵌入到另一条查询语句当中去
1.可以做为数据源
2.也可以作为查询条件

	1.查询年龄大于18岁的男生
	select * from student where age >18 and gender= '1';

    select * from student where age >18;
    select * from (  select * from student where age >18)  as newtable where newtable.gender='1';
    
    练习在数据关系里面

where 关联查询

只能查询两张表都有对应数据的信息

    1.查看每个学生的宿舍信息
	select * from student s,dormitory d where s.d_id=d.id;
	2.查询小红住在哪个宿舍
	select s.id,s.name,d.num from student s,dormitory d where s.d_id=d.id and name='小红';
	3.所有专业对应的课程
	select * from mojar m,mojar_course m_c,course c where m.id=m_c.m_id and m_c.c_id=c.id;
	4.查询python都要学习那些课程
	select m.id,m.name,c.name from mojar m,mojar_course m_c,course c where m.id=m_c.m_id and m_c.c_id=c.id and m.name='python';

连接查询

inner join 内链接 结果和where关联查询结果一样
格式： select * from 表1 inner join 表2 on 关联条件

	   1.查看每个学生的宿舍信息
		select * from student s inner join dormitory d on s.d_id=d.id; 
		2.查询小红住在哪个宿舍
		select s.id,s.name,d.num from student s inner join dormitory d on s.d_id=d.id and s.name='小红';
		3.所有专业对应的课程
		select * from 
			mojar m 
		inner join 
			mojar_course m_c 
		on 
			m.id=m_c.m_id 
		inner join 
			course c 
		on 
			m_c.c_id=c.id;

left join 外左连接
以左表为准，将左表中所有的数据查询出来，去右表匹配信息，如果没有对应的信息以null占位

	    1.查看每个学生的宿舍信息
			select * from student s left join dormitory d on s.d_id=d.id;
		2.哪些学员没有宿舍
			select * from student s left join dormitory d on s.d_id=d.id where d.num is null;
		3.哪些宿舍是空宿舍
			select * from dormitory d left join student s on s.d_id=d.id where s.id is null;
		4.统计每个宿舍住了多少人(有问题)
		select d.num,count(s.id) from dormitory d left join student s on s.d_id=d.id group by d.num;

right join 外右链接
以右表为准，将右表中所有的数据查询出来，去左表匹配信息，如果没有对应的信息以null占位

		1.查看每个学生的宿舍信息
		select * from dormitory d right join student s on d.id=s.d_id;