mysql笔记

最新推荐文章于 2024-01-20 11:58:00 发布

夜羽122

最新推荐文章于 2024-01-20 11:58:00 发布

阅读量516

点赞数

文章标签： mysql 数据库 python

本文链接：https://blog.csdn.net/m0_55212788/article/details/120237398

版权

mysql笔记

前期mysql相关的学习是b站视频教程【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩 (゜-゜)つロ干杯~-bilibili

最后一章节JDBC为Java相关, 暂时不进行学习.进行python 连接相关mysql学习

1、命令提示符中(管理员身份打开cmd)

启动数据库:net start (mysql+名称);
关闭数据库:net stop (mysql+名称);
打开数据库:mysql -uroot -proot (-u+账户)(-p+密码);
打开数据库:mysql -uroot -p (-u+账户)(-p+不输入密码)可以隐藏密码输入; exit退出
链接他人的数据库:mysql -hlocalhost -P3306 -uroot -proot (-h主机名称可以是IP地址,localhost=本机)(-P大写数据库端口号)

2、数据库的基本命令(增加\删除\查看\选择)

2.1、查看

show databases;                 #命令用于显示所有数据库。#
SHOW DATABASES;                 #全部显示# 选着后执行#
 TABLES FROM sys;             #TABLES=表格对象,选着打开#
-- 查看mysql系统版本
SELECT VERSION()   

-- 查询权限
SHOW GRANTS FOR ggx2  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

2.2、添加

CREATE DATABASE IF NOT EXISTS 练习1.1;   #创建数据库如果不在就显示警告#
CREATE DATABASE txt1;               #创建数据库 + 数据库名称#
CREATE DATABASE txt1; 
CREATE DATABASE `数据库名` CHARACTER SET utf8 COLLATE utf8_general_ci  
-- 数据库的创建(无内容完整的)
-- [DEFAULT] CHARACTER SET utf8：数据库字符集。设置数据库的默认编码为utf8
-- COLLATE utf8_general_ci:数据库校对规则。

-- 显示所有的索引信息
SHOW INDEX FROM 表

2.3、删除

DROP DATABASE IF EXISTS hello;     #删除数据库  如果存在在删除#  
DROP DATABASE txt1;             #删除数据库 + 数据库名称,不存在就报错#
-- 删除用户
DROP USER ggx

2.4、选择

tab 键的上方,如果你的表名或者字段名是一个特殊字符,就需要带( `` )

USE `sys`;                  #切换(查询)数据库`练习1.1``sys``练习1.1`#  
SELECT `stuname` FROM `测试`;    #查询 + 什么(表的名称) + 来自 + 哪里(上一级)#

3、数据库列的类型

3.1、数值:

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节 常用
bigint 较大的数据 8个字节
fioat 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数金融计算的时候,一般是使用decimal 重点分开

3.2、字符串:

char 字符串固定大小的 0~255 bit
varchar 可变字符串 0~65535 bit 常用的变量
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

3.3时间日期:

date YYYY-MM-DD 日期格式(年/月/日)
time HH: mm : ss 时间格式(时/分/秒)
datetime YYYY-MM-DD+ HH: mm : ss 最常用的时间格式
timestamp 时间戳 1970.1.1 到现在的毫秒数也较为常用(使用麻烦)
year 年份表示

create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, -- 默认时间创建时间 
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    -- 默认修改时间自动更新

3.4null

没有值,未知
注意不要使用null进行运算, 使用结果一定为null

4.数据库的字段属性 (重点)

非空	无符号的	自增	零填充	默认
`假设设置为 not null , 如果不给它赋值, 就会报错!`	unsigned:	`通常理解为自增, 自动在上一条记录的基础上+1(默认)`	0填充的	`设置默认的值`
`null,如果不填写值, 默认就为空`	无符号的整数	`通常用来设计唯一的主键~index, 必须是整数类型`	设定好长度后,不足的位数使用0来填充. int(3) , 5 — 005	列如 sex ,默认值为男,如果不指定列的值,就为默认值.``列如 sex ,默认值为男,如果不指定列的值,就为默认值.
	声明了该列不能声明为负数	`可以自定义设计主键的起始值和步长 (高级中设置)`

主键\非空\无符号的\自增\零填充\更新

4.1unsigned(无符号的):

无符号的整数
声明了该列不能声明为负数
没有选中就可以为负数

4.2 zerofill

0填充的
设定好长度后,不足的位数使用0来填充. int(3) , 5 --- 005

4.3自增

通常理解为自增, 自动在上一条记录的基础上+1(默认)
通常用来设计唯一的主键~index, 必须是整数类型
可以自定义设计主键的起始值和步长 (高级中设置)
自增 =

4…4非空 (null与not null)

假设设置为 not null , 如果不给它赋值, 就会报错!
null,如果不填写值, 默认就为空

4.5默认

设置默认的值
列如 sex ,默认值为男,如果不指定列的值,就为默认值.

!重点拓展:!

/* 每一个表都必须存在以下5个字段!
id    主键
`version`    乐观锁
is_delete    伪删除
gmt_create   创建时间
gmt_update   修改时间
*/

代码练习(创建表)

-- 注意点,使用英文 () ,表的名称和字段尽量使用 `` 栝起来,
-- AUTO_INCREMENT 自增
-- COMMENT ' ' 备注
-- 字符串使用引号栝起来
-- 所有的语句后面加上 , 号(英文的), 最后一个不用加
-- DEFAULT ' ' 默认值

-- 创建表必须要有一个行类型

CREATE TABLE IF NOT EXISTS `测试3`(
   `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
   `pwd` VARCHAR(10) NOT NULL DEFAULT '123456789' COMMENT '密码',
   `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
   `birthday` DATETIME 	DEFAULT NULL COMMENT '出生日期'	,
   `address` VARCHAR(100) NOT NULL COMMENT '家庭地址',
   `email` VARCHAR(30) DEFAULT NULL COMMENT '电子邮件',
   PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

一个完整的数据库添加

-- 创建一个school数据库
CREATE DATABASE school -- 创建学生数据库
USE `school`;-- 打开学生数据库
-- drop table if exists `student`;  -- 如果有就先删除
CREATE TABLE `student`(               -- 新建一个学生表
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

代码格式

CREATE TABLE [IF NOT EXISTS] `新建表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释],
    PRIMARY KEY (`字段名`)        --主键设置
)[表类型][字段集][注释]

设置数据库表的字符集编码

DEFAULT CHARSET=utf8       --  默认 编码格式

不设置的话,会是mysql 默认的字符集编码(不支持中文格式)

5.常用命令:

5.1查看创建数据库的定义语句

中文命名加上 `` !!!

SHOW CREATE DATABASE `练习1.1` -- 查看创建数据库的语句
SHOW CREATE TABLE `测试3`    -- 查看创建数据表的定义
DESC `测试3`             -- 显示表的结构

5.2关于引擎

INNODB 默认使用
MYISAM 早些年使用

	MYISAM	INNODB
外键约束	不支持	支持
事务支持	不支持	支持
数据行锁定	不支持	支持
全文索引	支持	支持(5.7版本后支持全英文索引)
表空间大小	较小	较大,大约是前者的2倍

常规使用操作:

MYISAM 节约空间,速度较快
INNODB 安全性较高,事务的处理,多表多用户操作.

在物理空间存在的位置

所有的数据库文件都存在 data 目录下

本质上还是文件存储

mysql 引擎在物理文件上的区别:

INNODB在数据库表中只有一个*.frm文件, 以及上级目录下的ibdata1文件;

MYISAM对应文件:

*.frm 表结构的定义文件

*.myd 数据文件(data)

*.myl 索引文件(index)

6.修改和删除表

6.1修改:

6.1.1修改表名

修改表的名称: ALTER TABLE 旧表名 RENAME AS 新表名;

alter=修改 table=表格 rename=重命名 as=为

ALTER TABLE `测试` RENAME AS `测试1`;

6.1.2添加表的列字段

添加表字段: ALTER TABLE 表名 ADD 新字段名 VARCHAR(30) + 需要的属性.

alter=修改 table=表格 add=添加字段的列属性

ALTER TABLE `测试1` ADD `email` VARCHAR(30) DEFAULT NULL COMMENT '电子邮件';

6.1.3字段的修改与重命名

CHANGE = 改变 ,建议使用

-- 修改表的约束:
-- ALTER TABLE `表名` MODIFY `字段名` 列属性[];
ALTER TABLE `测试1` MODIFY `email` INT(10);
-- 修改表的名字和约束
-- ALTER TABLE `表名` CHANGE `救名字` 和 `新名字` 列属性[];
ALTER TABLE `测试1` CHANGE `email` `email.1` VARCHAR(30);

6.2删除

6.2.1字段的删除

DROP = 删除 ALTER = 改动,修改,变更

-- ALTER TABLE `表名` DROP `字段名`
ALTER TABLE `测试1` DROP `email.1`

6.2.2删除表

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS `测试1`

mysql`注意使用的点`

所有的创建与删除尽量加上判断,以免报错

`` 字段名,使用这个包裹
注释 – /* */
sql关键字大小写不敏感,建议用小写
所以的符号全部用英文

mysql数据管理

1.外键（了解）：

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gardeid` (`gradeid`),
CONSTRAINT `FK_gardeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (gradeid)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须先删除引用的表（从表），再删除被引用的表（主表）

方式二：创建表成功后添加外键

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE `表名` ADD CONSTRAINT 约束名 FOREIGN KEY（作为外键的列） 引用到哪个表的哪个字段

以上的操作都是物理外键，数据库级别外键，不建议使用。（避免数据库过多造成困扰）

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想使用多张表的数据，想使用外键（程序去实现）

2.DML语句(需要全部记住 ! ! )

数据库的意义: 数据储存, 数据管理

DML语句: 数据操作语言

insert = 添加
update = 修改
delete = 删除

2.1添加

添加时注意数值的('')不是飘号

insert = 添加

-- 插入语句（添加）

--``注意需要取消非空( 不一定主要看错误代码提示)

-- insert into 表名（[字段一], [字段二]）values('值1'),('值2')

-- insert = 添加   into = 变成  VALUES = 数值

INSERT INTO `测试3` (`address`) VALUES('大四');

由于主键自增我们可以省略（如何不写表的字段，他会一一匹配）

INSERT INTO `测试3` VALUES('大三') 
INSERT INTO `测试3` (`id`,`name`) VALUES ('大三','null')

一般写插入语句，我们一定要数据和字段一一对应。

插入多个字段

INSERT INTO `测试3` (`pwd`) VALUES ('大二'),('大一');

多字段插入多个值(VALUES 后面的值必须要用 , 隔开)

INSERT INTO `测试4` (`name`,`sex`,`pwd`) VALUES ('张三',"男",'123456'),('李四',"女",'1234567')

字段可以省略, 但是后面的值必须一一对应

INSERT INTO `测试4` VALUES ('0000000021','niub','123456','女','2020-12-14','12','云南','5')

2.2修改

update = 修改

-- 修改内容
UPDATE `测试4` SET `name`='王铁柱' WHERE id=1
-- 在不设定条件的情况下, 会改动所有表     (不建议使用)
UPDATE `测试4` SET `name`='匿名'
-- 修改多个属性, 需要逗号隔开
UPDATE `测试4` SET `name`='张麻子',`email`='2134566@qq.com' WHERE `id`=2

语法:

UPDATE = 修改 SET = 设置 WHERE = 在…的地方(规定选择的标准)

UPDATE 表名 SET 字段名='设定的 值’ WHERE 那一字段的=什么位置

**条件语句:**where 子句运算符 id 等于某个值，大于某个值，在某个区间内修改

操作符返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
!= <>	不等于	5！=6	true
>	大于
<	小于
>=
<=
between and	[在某个范围内，闭合区间]
and	我和你 &&	5>1and1>2	false
or	我或你\|\|	5>1or1>2	true

and语句:
UPDATE `测试4` SET `name`='黄四郎' WHERE `pwd`='1234567' AND `sex`='女';
or 语句:
UPDATE `测试4` SET `name`='黄四郎' WHERE `pwd`='123456' OR `sex`='男';
between and 语句:
UPDATE `测试4` SET `sex`='女' WHERE `id` BETWEEN 1 AND 10;
CURRENT_TIME 当前时间:
UPDATE `测试4` SET `birthday`= CURRENT_TIME

2.3删除

delete = 删除

语法: DELETE FROM 表名 [WHERE + 条件]

-- 删除指定数据
DELETE FROM `测试4` WHERE `id`='1'
-- 删库跑路(全部内容删除)
DELETE FROM `表名`

TRUNCATE 命令

-- 清空表
TRUNCATE `测试4`

他会完全清空一个表, 但是表的结构和约束不会变

delete 与 TRUNCATE 的区别

-- 测试delete 与 TRUNCATE 的区别
CREATE TABLE `测试5`(
  `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `coll` VARCHAR(20) NOT NULL COMMENT '学院' DEFAULT '计算机信息学院',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8	

INSERT INTO `测试5`(`coll`) VALUES('电商'),('老王')
UPDATE `测试5` SET `coll`=('老王学院') WHERE `coll`='老王'
DELETE FROM `测试5`        -- 不影响自增
TRUNCATE TABLE `测试5`      -- 自增会归零

相同点：都能删除数据，都不会删除表结构
不同：
- TRUNCATE 重新设置自增列计数器会归零
- TRUNCATE 不会影响事务

了解即可：delete删除的问题 重启数据库，现象

innoDB 自增列会从1开始（存在内存当中，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

3.DQL查询数据(重点)

3.1dql (data query language: 数据查询语言)

所有查询语句都需要用到 select

简单的查询, 复杂的查询它都能做

数据库最核心的语言, 最重要的语言

使用频率最高的语句

select语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件 排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 分页 指定查询的记录从哪条至哪条

3.2指定查询字段

SELECT = 选着

-- 查询一个字段   SELECT 字段 FROM `表`;
SELECT * FROM `student`;

-- 查询指定字段 SELECT `字段名`,`字段名` FROM `表`
SELECT `subjectno`,`gradeid` FROM `subject`;

查询时表头显示一个名字 (可以给字段起别名,也可以给表起别名)

-- 别名, 给结果起一个新名字  as   SELECT `字段名` as '新名字',`字段名` as as '新名字' FROM `表`;
SELECT `sex` AS '性别',`studentname` AS '学号' FROM `student`;
SELECT `sex` AS '性别',`studentname` AS '学号' FROM `student` AS 学生表;

拼接字符串函数 concat (a,b)

-- 函数 concat (a,b)  拼接字符串 SELECT concat ('想加入的字符',`字段名`) as '新名字', FROM `表`;
SELECT CONCAT ('姓名:',`studentname`) AS 学生姓名 FROM `student`;

3.3查询时去除重复数据

DISTINCT = 不同的 ,清晰地

-- 语法 SELECT DISTINCT `字段名` FROM `表名`
SELECT DISTINCT `studentno` FROM `result`

查看mysql系统版本

SELECT VERSION();

使用sql进行计算

-- 用于计算
SELECT 22+12*22 AS '结果';

3.4数据库的列表达式

SELECT VERSION()  --查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment --查询自增的步长（变量）

-- 学员考试成绩+1 分 查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

数据库中的表达式：文本值，列，Null , 函数，计算表达式，系统变量…

select 表达式 from 表

3.4where条件语句

作用：检索数据中符合条件的值

逻辑运算符

运算符	语法	结果
and &&	a and b a&&b	逻辑与
or \|\|	a or b a\|\|b	逻辑或
Not !=	not a !a	逻辑非

-- 查询考试成绩在95分到100分之间
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100

-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentNo = 1000

模糊查询: 比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null 结果为真
IS NOT NULL	a is not null	如果操作符为not null 结果为真
BETWEEN	a between b and c	若a在b 和c之间则为真
LIKE	a like b	SQL匹配，如果a 匹配到b 则为真
IN	a in (a1,a2,a3…)	假设a 在 a1,a2,a3其中的某一个中，为真

-  查询姓刘的同学
-- like结合 %（代表0到任意字符）  _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';

-- 查询姓刘的同学，名字后只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';

-- 查询姓刘的同学，名字后只有两个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';

-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';


=========================IN(具体的一个或者多个值)===========================
-- 查询1001 1002 1003 学员信息
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);

-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');


===================NULL NOT NULL===================================
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL

-- 查询有出生日期的同学  不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;

3.5连表查询

=========联表查询 join =============
– 查询参加考试的同学（学号，姓名，考试编号，分数）

SELECT * FROM student 
SELECT * FROM result

1. 分析需求，分析查询的字段来自哪些表

2.确定使用哪种连接查询？7种

确定交叉点（这两个表中哪个数据是相同的）

判断的条件：学生表中 studentNo = 成绩表中 studentNo*

INNER JOIN / RIGHT JOIN 右链接 / LEFT Join 左链接/

-- JION（表） ON （判断的条件）连接查询
-- where 等值查询
-- INNER JOIN
SELECT `studentname`,`subjectname`,s.`gradeid`,`phone`,`sex`
FROM `student` AS s
INNER JOIN `subject` AS r
WHERE s.`gradeid` = r.`gradeid`
-- RIGHT JOIN
SELECT `studentname`,`subjectname`,s.`gradeid`,`phone`,`sex`
FROM `student` AS s
RIGHT JOIN `subject` AS r
ON s.`gradeid` = r.`gradeid`
WHERE s.`gradeid` IS NOT NULL  -- 不能为空
-- LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo

操作
INNER JOIN	如果表中至少有一个匹配，就返回行
RIGHT JOIN	即使左表中没有匹配，也会从右表中返回所有的值
LEFT Join	即使右表中没有匹配，也会从左表中返回所有的值

join (链接的表) on (判断的条件) 链接查询

where 等值查询

-- 查询参加考试同学的信息:学号\学生姓名\科目名\分数

SELECT `studentname`,s.`studentno`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS su
ON r.`subjectNo` = su.`subjectNo

-- on之后的语句可以用表中的相同字段

– 我要查询哪些数据 SELECT …
– 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
– 假设存在一中多张表查询，先查询两章表，然后再慢慢增加

–FROM a LEFT JOIN b 左为准
–FROM a RIGHT JOIN b 右为准

-- 查询学员所属的年级( 学号 \姓名 \年级名称)

SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
-- on s.`gradeid` = g.`gradeid`

3.6自连接查询

自己的表跟自己的表连接，核心：一张表拆为两张一样的表

-- 自连接数据库：
CREATE TABLE `category`(
 `categoryid` INT(10) NOT NULL COMMENT '主题id', 
 `pid` INT(3) NOT NULL COMMENT '父id', 
 `categoryname` VARCHAR(10) NOT NULL COMMENT '主题id', 
 PRIMARY KEY (`categoryid`) 
 ) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) 
VALUES ('2', '1', '信息技术'),
('3', '1', '软件开发'),
('5', '1', '美术设计'),
('4', '3', '数据库'), 
('8', '2', '办公信息'),
('6', '3', 'web开发'), 
('7', '5', 'ps技术');

-- 查询父子信息

SELECT a.`categoryname` AS `父栏目`,b.`categoryname` AS `子栏目`
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`= b.`pid`

3.7分页与排序

================分页 limit 和排序order by================

– 排序：升序ASC 降序 DESC

排序练习
SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
-- on s.`gradeid` = g.`gradeid`
ORDER BY `studentno` ASC

-- 排序：  升序ASC  降序  DESC
SELECT  xx
FROM xx
JOIN xx
WHERE  xx
ORDER BY  xx
ASC   ||  DESC

分页:

– 为什么要分页

– 缓解数据库压力，给人的体验更好

– 分页，每页显示五条数据

– 语法： limit 当前页，页面的大小 = limit x,y

– limit 0,5 1-5 – limit 1,5 1-5-- limit 6,5

SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
-- on s.`gradeid` = g.`gradeid`
ORDER BY `studentno` ASC
LIMIT 0,2

– 第一页 limit 0,5

– 第二页 limit 5,5

– 第三页 limit 10,5

– 第N页 limit 5*（n-1）,5

3.8子查询

where (这个值是计算出来的)

本质：在where语句中嵌套一个子查询语句

-- ===========================where=========================

-- 1.查询 数据库结构-1的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一： 连接查询
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC

-- 方式二：使用子查询(由里及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
	SELECT SubjectNo FROM  `subject` 
    WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC


-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80

-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
    SELECT Subject FROM `subject`
    WHERE SubjectName='高等数学-2'
)

-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80


-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
    SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
         SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
    )
)

3.9分组与过滤 group by与having

对函数进行过滤用having

where是判断每一条记录，having二次过滤可以判断组

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT `subjectname` , AVG(`studentresult`) AS 平均分,
MAX(`studentresult`) AS 最高值, 
MIN(`studentresult`) AS 最低值
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`= sub.`subjectno`
GROUP BY r.`subjectno`  -- 通过什么字段来分组    
HAVING 平均分>=71  -- 函数过滤条件

4.mysql函数:

4.1MySQL 数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

4.2MySQL 字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

4.3MySQL 日期和时间函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

-- 数学运算

SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('2323232') -- 返回字符串长度
SELECT CONCAT('我','233') -- 拼接字符串
SELECT INSERT('java',1,2,'cccc') -- 从某个位置开始替换某个长度
SELECT UPPER('abc')      -- 转换为大写
SELECT LOWER('ABC')       -- 转换为小写
SELECT REPLACE('坚持就能成功','坚持','努力')  -- 替换字符

-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
SELECT SYSDATE()  -- 系统时间

SELECT YEAR(NOW())  -- 年
SELECT MONTH(NOW())   -- 月
SELECT DAY(NOW())       -- 日
SELECT HOUR(NOW())      -- 时
SELECT MINUTE(NOW())     -- 分
SELECT SECOND(NOW())    -- 秒

-- 系统
SELECT SYSTEM_USER()   -- 使用用户
SELECT USER()      -- 使用用户
SELECT VERSION()  -- 系统版本

4.4MySQL 聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

-- 聚合函数
-- 都能够查询 表中的数据 (想查询表中有多个记录,就使用count)有多少行
SELECT COUNT(`borndate`) FROM`student`;  -- count(字段),会忽略所有的unll 值
SELECT COUNT(*) FROM `student`;     -- count(*)不会忽略 null 值, 本质是 计算行数
SELECT COUNT(1) FROM `student`;     -- count(1)不会忽略所有的 null 值 本质是 计算行数

SELECT SUM(`studentresult`) AS 总和,
       AVG(`studentresult`) AS 平均分,
       MAX(`studentresult`) AS 最高值, 
       MIN(`studentresult`) AS 最低值
       FROM `result`;
SELECT AVG(`studentresult`) AS 平均分 FROM `result`;
SELECT MAX(`studentresult`) AS 最高值 FROM `result`;
SELECT MIN(`studentresult`) AS 最低值 FROM `result`;

-- 查询表中的平均值, 保留小数点后两位
SELECT ROUND(AVG(`studentresult`),2) AS '平均值' FROM`result`

4.5MySQL 流程控制函数

函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

4.6数据库md5加密

-- 测试md5 加密
CREATE TABLE `testmd5`(
   `id` INT(5) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50), 
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES('1','张三','123456'),('2','李四','123456'),('3','王五','123456')

-- 加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) WHERE id = 2;
UPDATE `testmd5` SET `pwd`=MD5(`pwd`);  -- 加密全部密码

-- 插入时加密
INSERT INTO `testmd5`  VALUES(5,'小米',MD5('123456'))

-- 如何校验: 将用户传递进来的密码,进行 md5 加密, 然后在比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='李四' AND pwd=MD5(MD5("123456"))  -- 多重加密查看
SELECT * FROM `testmd5` WHERE `name`='李四' AND pwd=MD5("123456")    -- 加密查看

4.7select小结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ooa3nCnD-1631342091508)(C:\Users\gouguoxiong\Desktop\mysql数据库\select小结.jpg)]

5.事务

5.1什么是事务

要么都成功，要么都失败

SQL执行， A给B转账 A 1000–> 200 B200
SQL 执行， B收到A的钱 A800 — B400

将一组SQL放在一个批次中执行

事务原则： ACID原则原子性，一致性，隔离性，持久性（脏读，幻读…）

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保持一致

持久性（Durability）–事务提交

事务一旦提交就不可逆转，被持久化到数据库中

隔离性

事务产生多并发时，互不干扰

隔离产生的问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读):

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

执行事务

-- mysql 自动开启事务提交
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启（默认的）

-- 手动处理事务
SET autocommit =0 -- 关闭自动提交

-- 事务开启

START TRANSACTION -- 标记一个事务的开始，从这个之后的SQP都在同一个事务内

INSERT XX
INSERT XX

-- 提交 ： 持久化(成功)
COMMIT 
-- 回滚：  回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 删除保存点

创建一个完整的数据库流程

-- DEFAULT CHARACTER SET utf8：数据库字符集。设置数据库的默认编码为utf8
-- COLLATE utf8_general_ci:数据库校对规则。

-- 创建数据库
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop` -- 选着表
-- 创建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL, -- decimal(9,2)浮点数,代表一个九位数，其中小数位是两位
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 添加表的内容
INSERT INTO `account` (`name`,`money`)
VALUES ('小明',2000.00),('小李',2999.25),('小马',1999.12),('晓雯',12.12)

事务使用模拟

-- 转账模拟:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启事务（一组事务）之后的都会在这一组中
UPDATE `account` SET `money`=`money`-500 WHERE `name`='小李'  -- '小李'减少500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='晓雯'  -- '小李'加500

COMMIT;-- 提交事务(提交就被持久化)
ROLLBACK; -- 回滚(可撤回)
SET autocommit = 1; -- 回复自动提交

6.索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

6.1索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
– 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
– 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引（KEY/INDEX）
– 默认的，index,key关键字来设置
全文索引（FULLTEXT）
– 在特点的数据库引擎下才有，MyISAM
– 快速定位数据

– 建立全文检索的字段类型必须是char,varchar,text

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
--  AGAINST 与什么相反
-- MATCH 匹配
-- EXPLAIN 解释 信息

-- 显示所有的索引信息
SHOW INDEX FROM 表

-- 增加一个全文索引 (索引名)列名
ALTER TABLE 表.[列名] ADD FULLTEXT INDEX 索引名（字段名）;

-- EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('赵');

6.2测试索引

添加一个app用户数据库, 并且添加一百万条数据

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

/*
  第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
  让存储过程内的命令遇到”;” 不执行
*/
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;$$

SELECT mock_data()$$ -- 执行此函数 生成一百万条数据

6.3添加索引与删除索引与测试

– drop index 索引名 on 表名；删除索引 (改变表索引删除)

SELECT * FROM `app_user` WHERE `name` = '用户10000';  -- 用时 1.561 sec
SELECT * FROM `app_user` WHERE `name` = '用户999999';  -- 用时 1.379 sec

EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户999999';  -- 用时 1.379 sec


-- 添加索引
-- id_表名_字段名
-- CREATE INDEX 索引名 on 表(字段名)
CREATE INDEX id_app_user_name ON `app_user`(`name`);

SELECT * FROM `app_user` WHERE `name` = '用户999999';  -- 添加完索引后用时 0.004 sec

-- drop index 索引名 on 表名；删除索引

索引在小数据的时候，用处不大，但是在大数据的时候，区别十分明显

6.4 索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree: 默认innodb 的数据结构

阅读： http://blog.codinglabs.org/articles/theory-of-mysql-index.html

7.权限管理与备份

7.1用户管理

SQLyog可视化管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C6XW3fyF-1631342091510)(C:\Users\gouguoxiong\Desktop\mysql数据库\用户管理.png)]

sql命令操作

本质: 对这张表进行,增删改查

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER ggx IDENTIFIED BY '123456'

-- 修改密码（修改当前密码）
SET PASSWORD = PASSWORD('111111')


-- 修改密码（修改指定用户密码）

SET PASSWORD FOR ggx = PASSWORD('111111')


-- 重命名  rename user 原名字 to 新名字
RENAME USER ggx TO ggx2

-- 用户授权   ALL PRIVILEGES 全部的权限   库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON * .* TO ggx2

-- 查询权限
SHOW GRANTS FOR ggx2  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost


-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM ggx2

-- 删除用户
DROP USER ggx

新链接用户是报错

mysql -uroot -p

密码

错误号码 2058，是因为mysql 密码加密方法变了。

先登录你的数据库，然后执行

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

password是你的登陆密码。
先登录你的数据库，然后执行 mysql> show global variables like ‘port’;

7.2mysql备份

为什么要备份:

保证重要数据不丢失
数据转移

mysql 数据库备份方式

直接拷贝物理文件
在sqlyog 可视化工具中手动导出( 在想要导出的表或者库中，右键选择备份和导出)
使用命令行导出 mysqldump 命令行使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6JCwWtJ9-1631342091512)(C:\Users\gouguoxiong\Desktop\mysql数据库\sql备份.png)]

8.规范数据库设计:

8.1设计数据库的方法

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
程序的性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性
方便我们开发系统

软件开发中，关于数据库的设计

分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图 E-R图

设计数据库的步骤（个人博客）

收集信息，分析需求

用户表（用户登录注销，用户的个人信息，写博客，创建分类）
分类表（文章分类，谁创建的）
文章表（文章的信息）
友链表（友链信息）
自定义表（系统信息，某个关键的字，或者某些主字段）
说说表（发表心情…id ,content ,time）
标识实体（把需求落地到每个字段）
标识实体之间的关系

写博客 user–>blog
创建分类 user–>category
关注 user–>user
友链–>links
评论 user–>user

8.2三大范式

为什么需要数据规范化？

信息重复
更新异常
插入异常
删除异常
无法正常显示异常
删除异常
丢失有效的信息

三大范式

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

（规范数据库的设计）

规范性和性能的问题

关联查询的表，不得超过三张表

考虑商业化的需求和目标（成本和用户体验）数据库的性能更加重要
再规范性能的问题的时候，需要适当的考虑一下，规范性
故意给某些表加一些冗余的字段（从多表，变成单表）
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

9.python 中操作 mysql 的方式;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YwCrysZP-1631342091514)(C:\Users\gouguoxiong\Desktop\mysql数据库\python访问api流程.png)]

9pymysql操作方法

引入模块

在py文件中引入pymysql

from pymysql import *
import pymysql

光标

cursor .scroll(1,mode="relative")  #光标的位置,延续之前的光标位置,从1开始
cursor .scroll(1,mode="absolute")  #光标的位置从1开始固定

9.1查询方法

import pymysql

#将mysql登录信息封装(数据库连接)
conn = pymysql.connect(host = 'localhost', port = 3306, user = 'root', password = 'root', database ='school', charset = 'utf8')
#创建一个游标
#cursor = conn.cursor()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 创建一个游标 cursor=pymysql.cursors.DictCursor,是为了将数据作为一个字典返回
#mysql 命令封装
sql ="SELECT `sex` AS '性别',`studentname` AS '姓名' FROM `student` AS 学生表"
# 使用游标执行sql
ret = cursor.execute(sql)   #######################
# fetchall()（获取所有的数据），fetchmany(size)（size指定获取多少条数据 数字），fetchone()（获取第一条数据）
books = cursor.fetchall()
#查看运行的行数
print(ret)
#查看运行的行内容
print(books)
# 执行完之后别忘了关闭游标和数据库连接
cursor.close()
conn.close()

execute()逐行插入

executemany()批量插入

executemany()用法:

在数据库连接后，使用cursor.excutemany(sql, list)执行批量插入，其中sql为数据库SQL语句，其中的变量可以写为%s；list为要插入数据库的元组列表，其中的元组元素依次与SQL语句中的%s对应。

注意： List中数据类型必须为Tuple元组 eg.[(0, 3132), (1, 1298), (2, 6543), (3, 4553) ……]

DictCursor
SSCursor和SSDictCursor

之前使用的Cursor返回的数据是以元组的方式保存，而DictCursor是以字典的形式保存。创建这种类型的游标方法也很简单，在conn.sursor()方法中传入DictCursor这个类即可：
cursor = conn.cursor(pymysql.cursors.DictCursor) # 创建一个字典游标
SSCursor和SSDictCursor被称为流式游标，这类游标不会像上面使用的Cursor和DictCursor那样，一次性返回所有的数据，流式游标会陆陆续续一条一条得返回查询数据，所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。

流式游标的使用方法类似于迭代器，再循环中每取一条，生成一条
cursor = conn.cursor(pymysql.cursors.SSCursor) # 创建一个流式游标

基本固定的内容

conn = pymysql.connect( # 创建数据库连接
    host='10.10.11.131', # 要连接的数据库所在主机ip
    user='chb', # 数据库登录用户名
    password='123456!', # 登录用户密码
    database='test_db', # 连接的数据库名，也可以后续通过cursor.execture('user test_db')指定
    charset='utf8' # 编码，注意不能写成utf-8
)
cursor = conn.cursor() # 创建一个游标

# 需要执行的创建表的sql语句
sql = '###################  '

cursor.execute(sql) # 使用游标执行sql
 
# 执行完之后别忘了关闭游标和数据库连接
cursor.close()
conn.close()
cursor.commit()

9.2插入

插入操作可以通过游标的execute和executemany两个方法来完成。注意：只要是对数据表有修改的操作（插入、更新、删除）在使用execute方法后，都需要再次调用commit方法对数据库的修改才会最终生效。

execute方法一次插入一条记录，executemany一次插入多条记录：

（1）execute：一次插入一条记录

cursor.execute('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);' % ('Python从入门到放弃', '乔布斯', 2019))
conn.commit()

上面这种写法是先用字符串利用%s占位生成一条完整的sql语句，然后去执行。其实，pymysql的游标也自带这一功能，而且看起来更加方便，所以，我们还可以这样执行execute方法：

cursor.execute('insert into book(bookname, authors, year_publication) values(%s, %s, %s);', ('Python从入门到放弃', '乔布斯', 2019))
conn.commit()

1）execute：一次插入一条记录

cursor.execute('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);' % ('Python从入门到放弃', '乔布斯', 2019))
conn.commit()

cursor.execute('insert into book(bookname, authors, year_publication) values(%s, %s, %s);', ('Python从入门到放弃', '乔布斯', 2019))
conn.commit()

这种方法的好处是sql语句中的%s不需要加引号（如果加了引号，引号也会被当做数据插入到数据表中），游标执行这一语句时，会根据数据类型来判断是否要加上引号。

（2）executemany：一次插入多条记录

data = [
    ('21天完全入门Java', '扎克伯格', 2018),
    ('Linux学习手册', '李纳斯', 2017),
    ('MySQL从删库到跑路', '比尔盖茨', 2018),
]
cursor.executemany('insert into book(bookname, authors, year_publication) values("%s", "%s", %s);', data)
conn.commit()

9.3更新

cursor.execute('update book set authors=%s where bookname=%s;', ["马云", "Python从入门到放弃"])
conn.commit()

9.4 删除

使用pymysql执行插入、更新、删除操作都是相似的，最后都需要commit提交：

cursor.execute('delete from book where bookid=%s;', [1])
conn.commit()

这时候，bookid为1的记录已经被删除，如下图所示：

也可以使用executemany()一次性删除多条：

cursor.executemany('delete from book where bookid=%s;', [[2], [4]])
conn.commit()

如下图所示，bookid为2和4的记录已经被删除：

9.5修改

3.SQLAlchemy - Column详解

SQLAlchemy - Column详解

Column常用参数：

primary_key：设置某个字段为主键。
autoincrement：设置这个字段为自动增长的。
default：设置某个字段的默认值。在发表时间这些字段上面经常用。
nullable：指定某个字段是否为空。默认值是True，就是可以为空。
unique：指定某个字段的值是否唯一。默认是False。
onupdate：在数据更新的时候会调用这个参数指定的值或者函数。在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用的就是update_time（每次更新数据的时候都要更新的值）。
name：指定ORM模型中某个属性映射到表中的字段名。如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为参数。这个参数也可以当作位置参数，在第1个参数来指定。
comment：设置该字段的注释

sqlalchemy常用数据类型：

Integer：整形
Float：浮点类型
Boolean：传递True/False
DECIMAL：定点类型
enum：枚举类型
Date：传递datetime.date()进去
Time：传递datatime.time()
String：字符类型，使用时需要指定长度，区别于Text类型
Text：文本类型
LONGTEXT：长文本类型
query可以参数：

Integer：整形，映射到数据库中是int类型。

Float：浮点类型，映射到数据库中是float类型。他占据的32位。

Double：双精度浮点类型，映射到数据库中是double类型，占据64位。

String：可变字符类型，映射到数据库中是varchar类型.

Boolean：布尔类型，映射到数据库中的是tinyint类型。

DECIMAL：定点类型。是专门为了解决浮点类型精度丢失的问题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。并且这个类型使用的时候需要传递两个参数，第一个参数是用来标记这个字段总能能存储多少个数字，第二个参数表示小数点后有多少位。

Enum：枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举，示例代码如下：

聚合函数：

func.count：统计行的数量
func.avg：求平均值
func.max：求最大值
func.min：求最小值
func.sum：求和

result = session.query(func.count(User.id)).all()
print(result)

resultavg = session.query(func.avg(User.price)).all()
print(resultavg)

resultmax = session.query(func.max(User.price)).all()
print(resultmax)

resultmin = session.query(func.min(User.price)).all()
print(resultmin)

resultsum = session.query(func.sum(User.price)).all()
print(resultsum)

过滤方法：
过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行详解，并且这些过滤条件都是只能通过filter方法实现的：

1.equals：
query.filter(User.name == ‘ed’)

2.not equals:
query.filter(User.name != ‘ed’)

3.like:
query.filter(User.name.like(’%ed%’))

4.in:
query.filter(User.name.in_([‘ed’,‘wendy’,‘jack’]))
#同时
query.filter(User.name.in_(session.query(User.name).filter(User.name.like(’%ed%’))))

5.not in:
query.filter(~User.name.in_(‘ed’,‘wendy’,‘jack’))

6.is null:
query.filter(User.name==None)
query.filter(User.name.is_(None))

7.is not null:
query.filter(User.name != None)
query.filter(User.name.isnot(None)

8.and:
from sqlalchemy import and_
query.filter(and_(User.name==‘ed’, User.fullname==‘Ed Jones’))
或者
query.filter(User.name==‘ed’, User.fullname==‘Ed Jones’)
或者
query.filter(User.name==‘ed’,).filter(User.fullname==‘Ed Jones’)
复制代码

9.or:
from sqlalchemy import or_
query.filter(or_(User.name=‘ed’, User.name=‘wendy’)

3.1mysql的连接

参考文档作者奋斗吧，青年！

文档连接:(3条消息) Python FastAPI 框架操作 MySQL 数据库_lilygg的博客-CSDN博客

参考视频:FastAPI教程第二季（四）：数据库（ORM+SQLite）（最快python异步并发web框架之一）_哔哩哔哩_bilibili

数据库表的连接

# -*- coding = utf-8 -*-
# @Time : 2021/5/31 22:49
# @File : mysqldb.PY
# @Author : 夜羽
# @Software: PyCharm
from sqlalchemy import Boolean, Column, Integer, String, DateTime
from sqlalchemy import Column, Integer, String
# 创建数据库模型（定义表结构:表名称，字段名称以及字段类型）
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base   #定义映射
from sqlalchemy.orm import sessionmaker      #建立会话
import pymysql
from fastapi import FastAPI, Request, Request
import datetime
import uvicorn

app = FastAPI()
#app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/school'
#DB_CONNECT = 'mysql+pymysql://root:102@localhost/tset'
#engine = create_engine('mysql+pymysql://root:root@localhost:3306/school')       #用户名\密码\数据库的地址\端口号\数据库名称
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:root@localhost:3306/school'
# 生成一个SQLAlchemy引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI, pool_pre_ping=True)
# 生成sessionlocal类--工厂，这个类的每一个实例都是一个数据库的会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
session = SessionLocal()
Base = declarative_base()
# Base是用来给模型类继承的，类似django中的models.Model

#以上基本为固定

# 模型类，tablename指表名，如果数据库中没有这个表会自动创建，有表则会沿用
"""
class order_order(Base):         #申明数据库某表的属性结构
    __tablename__ = "order_order"
    order_id = Column(String(18), primary_key=True, index=True)
    state_type = Column(Integer, default=1)
    submit_time = Column(DateTime)
    cus_phone = Column(String(11))
    cabinet_id = Column(String(4))
    Base.metadata.create_all(bind=engine)
"""


class User(Base):
    # 定义表名
    __tablename__ = 'tbluser'
    # 定义字段
    # primary_key=True 设置为主键
    userid = Column(Integer, primary_key=True)
    username = Column(String(255))

    # 构造函数
    def __init__(self, userid, username):
        self.userid = userid
        self.username = username

    # 打印形式
    def __str__(self):
        return "id:%s, name:%s" % (str(self.userid), self.username)


# 在数据库中生成表
Base.metadata.create_all(bind=engine)

练习

"""↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓    数据库操作（依赖项）    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓"""
def get_db():
    try:
        db = SessionLocal() # 这时，才真正产生一个'会话'，并且用完要关闭
        yield db            # 生成器
    finally:
        db.close()
        print('数据库已关闭')


"""↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓    数据库操作方法    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓"""
# 通过id查询用户信息
def get_user(db: Session, user_id: int):
    CCCCCC = db.query(M_User).filter(M_User.id == user_id).first()
    print(CCCCCC)           # 过滤器
    return CCCCCC

# 新建用户（数据库）
def db_create_user(db: Session, user: UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = M_User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()     # 提交即保存到数据库
    db.refresh(db_user) # 刷新
    return db_user

"""↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓    post和get请求    ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓"""
# 新建用户(post请求)
@app.post("/users/", response_model=User)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    # Depends(get_db)使用依赖关系可防止不同请求意外共享同一链接
    return db_create_user(db=db, user=user)

# 用ID方式读取用户
@app.get("/users/{user_id}", response_model=User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = get_user(db, user_id=user_id)
    print(db_user)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")

    return db_user


if __name__ == '__main__':
    import uvicorn
    uvicorn.run(app, host="127.0.0.1", port=8000)

3.2数据库的添加

# 定义数据模型
class CreatUser(BaseModel):
    userid: int
    username: str

## 添加单个
@app.post("/user/addUser")
async def InserUser(user: CreatUser):
    try:
        # 添加数据
        dataUser = User(userid=user.userid, username=user.username)
        session.add(dataUser)
        session.commit()
        session.close()
    except ArithmeticError:
        return {"code": "0002", "message": "数据库异常"}
    return {"code": "0000", "message": "添加成功"}


## 添加多个
@app.post("/user/addUserList")
async def addUserList(*, user: List[CreatUser]):
    try:
        # user是一个列表，每个内部元素均为CreatUser类型
        for u in user:
            # 自定义的数据模型可以用.访问属性
            dataUser = User(userid=u.userid, username=u.username)
            session.add(dataUser)
        session.commit()
        session.close()
    except ArithmeticError:
        return {"code": "0002", "message": "数据库异常"}
    return {"code": "0000", "message": "添加成功"}


if __name__ == '__main__':
    uvicorn.run('mysqldb:app', host="127.0.0.1", port=8000, reload=True, debug=True, workers=1)
#woker=1进程数量

code

[
  {
    "userid": 4,
    "username": "老马"
  },
  {
    "userid": 5,
    "username": "小马"
  }
]

3.3查询

查询数据

one与first的区别：
one：要求结果集中只有一个结果；如果数据库返回0或2个或更多结果，并且将引发异常，则为错误。
first：返回可能更大的结果集中的第一个，如果没有结果，则返回None。不会引发异常。
filter_by与filter的区别：
filter_by接收的参数形式是关键字参数，而filter接收的参数是更加灵活的SQL表达式结构

## 按照user_id查询

@app.get("/user/{user_id}")
async def queryUserByUserId(user_id: int):
    # 创建Query查询，filter是where条件，调用one返回唯一行，调用all则是返回所有行
    #filter_by接收的参数形式是关键字参数，而filter接收的参数是更加灵活的SQL表达式结构
          #会以.查询(定义名称表的).where(表中.数据==输入的).结果集
    try:
        # user1 = session.query(User).filter_by(userid=user_id).first()
        # user1 = session.query(User).filter(User.userid==user_id).one()
        user1 = session.query(User).filter(User.userid==user_id).first()
        session.close()
        # 由于user1只有一个值，所以它直接是一个字典
        if user1:
            return {"code":"0000","message":"请求成功","data":user1}
        else:
            return {"code":"0001","message":"查询无结果"}
    except ArithmeticError:

查询全部

## 查询所有
@app.get("/user/selectall/")
async def queryUserByUserId():
    # 创建Query查询，filter是where条件，调用one返回唯一行，调用all则是返回所有行
    try:
        user1 = session.query(User).all()
        session.close()
        # user1 是一个列表，内部元素为字典
        return {"code": "0000", "message": "请求成功", "data": user1}
    except ArithmeticError:
            return {"code":"0002","message":"数据库异常"}

3.4删除

删除命令

方法1

@app.delete("/user/deleteUser/{user_id}")
async def deleteUser(user_id: int):
    try:
        user1 = session.query(User).filter(User.userid == user_id).first()
        if user1:
            session.delete(user1)
            session.commit()
            session.close()
            return {"code": "0000", "message": "删除成功"}
        else:
            return {"code": "0001", "message": "参数错误"}
    except ArithmeticError:
        return {"code": "0002", "message": "数据库错误"}

方法2

本质上与方法1没区别

class AlterUser(BaseModel):
    userid: int
    username: str


@app.put("/user/updateUser01/")
async def deleteUser(user: AlterUser):
    try:
        user1 = session.query(User).filter(User.userid == user.userid).first()
        if user1:
            user1.username = user.username
            session.commit()
            session.close()
            return {"code": "0000", "message": "修改成功"}
        else:
            return {"code": "0001", "message": "参数错误"}
    except ArithmeticError:
        return {"code": "0002", "message": "数据库错误"}

3.5完整版

# -*- coding = utf-8 -*-
# @Time : 2021/5/31 22:49
# @File : mysqldb.PY
# @Author : 夜羽
# @Software: PyCharm
from sqlalchemy import Boolean, Column, Integer, String, DateTime
from sqlalchemy import Column, Integer, String
# 创建数据库模型（定义表结构:表名称，字段名称以及字段类型）
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base   #定义映射
from sqlalchemy.orm import sessionmaker,Session    #建立会话
import pymysql
from fastapi import FastAPI, Request, Request, Depends, HTTPException
import datetime
import uvicorn
from pydantic import BaseModel
from typing import List


app = FastAPI()
#app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/school'
#DB_CONNECT = 'mysql+pymysql://root:102@localhost/tset'
#engine = create_engine('mysql+pymysql://root:root@localhost:3306/school')       #用户名\密码\数据库的地址\端口号\数据库名称
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:root@localhost:3306/school'
# 生成一个SQLAlchemy引擎
engine = create_engine(SQLALCHEMY_DATABASE_URI, pool_pre_ping=True)
# 生成sessionlocal类--工厂，这个类的每一个实例都是一个数据库的会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
session = SessionLocal()
Base = declarative_base()
# Base是用来给模型类继承的，类似django中的models.Model

# 模型类，tablename指表名，如果数据库中没有这个表会自动创建，有表则会沿用
"""
class order_order(Base):         #申明数据库某表的属性结构
    __tablename__ = "order_order"
    order_id = Column(String(18), primary_key=True, index=True)
    state_type = Column(Integer, default=1)
    submit_time = Column(DateTime)
    cus_phone = Column(String(11))
    cabinet_id = Column(String(4))
    Base.metadata.create_all(bind=engine)
"""


class User(Base):
    # 定义表名
    __tablename__ = 'tbluser'
    # 定义字段
    # primary_key=True 设置为主键
    userid = Column(Integer, primary_key=True)
    username = Column(String(255))



    # 构造函数
    def __init__(self, userid, username):
        self.userid = userid
        self.username = username

    # 打印形式
    def __str__(self):
        return "id:%s, name:%s" % (str(self.userid), self.username)


# 在数据库中生成表
Base.metadata.create_all(bind=engine)


# 定义数据模型
class CreatUser(BaseModel):
    userid: int
    username: str

## 添加单个
@app.post("/user/addUser")
async def InserUser(user: CreatUser):
    try:
        # 添加数据
        #数据库定义中(定义名称=赋值后.id, 赋值后.name)
        dataUser = User(userid=user.userid, username=user.username)
        session.add(dataUser)   #添加
        session.commit()     #提交会提
        session.close()         #关闭
    except ArithmeticError:
        return {"code": "0002", "message": "数据库异常"}
    return {"code": "0000", "message": "添加成功"}



## 添加多个
@app.post("/user/addUserList")
async def addUserList(*, user: List[CreatUser]):
    try:
        # user是一个列表，每个内部元素均为CreatUser类型
        for u in user:
            # 自定义的数据模型可以用.访问属性
            dataUser = User(userid=u.userid, username=u.username)
            session.add(dataUser)
        session.commit()
        session.close()
    except ArithmeticError:
        return {"code": "0002", "message": "数据库异常"}
    return {"code": "0000", "message": "添加成功"}


## 按照user_id查询
@app.get("/user/{user_id}")
async def queryUserByUserId(user_id: int):
    # 创建Query查询，filter是where条件，调用one返回唯一行，调用all则是返回所有行
    #filter_by接收的参数形式是关键字参数，而filter接收的参数是更加灵活的SQL表达式结构
                   #会以.查询(定义名称表的).where(表中.数据==输入的).结果集
    try:
        # user1 = session.query(User).filter_by(userid=user_id).first()
        # user1 = session.query(User).filter(User.userid==user_id).one()
        user1 = session.query(User).filter(User.userid==user_id).first()
        session.close()
        # 由于user1只有一个值，所以它直接是一个字典
        if user1:
            return {"code": "0000", "message": "请求成功", "data": user1}
        else:
            return {"code":"0001","message":"查询无结果"}
    except ArithmeticError:
            return {"code":"0002","message":"数据库异常"}


## 查询所有
@app.get("/user/selectall/")
async def queryUserByUserId():
    # 创建Query查询，filter是where条件，调用one返回唯一行，调用all则是返回所有行
    try:
        user1 = session.query(User).all()
        session.close()
        # user1 是一个列表，内部元素为字典
        return {"code": "0000", "message": "请求成功", "data": user1}
    except ArithmeticError:
            return {"code":"0002","message":"数据库异常"}


@app.delete("/user/deleteUser/{user_id}")
async def deleteUser(user_id: int):
    try:
        user1 = session.query(User).filter(User.userid == user_id).first()
        if user1:
            session.delete(user1)
            session.commit()
            session.close()
            return {"code": "0000", "message": "删除成功"}
        else:
            return {"code": "0001", "message": "参数错误"}
    except ArithmeticError:
        return {"code": "0002", "message": "数据库错误"}





# 删除多个
@app.delete("/user/deleteUserList")
async def deleteUser(user_ids: List[int]):
    try:
        for user_id in user_ids:
            user1 = session.query(User).filter(User.userid == user_id).first()
            if user1:
                session.delete(user1)
                session.commit()
                session.close()
        return {"code": "0000", "message": "删除成功"}
    except ArithmeticError:
        return {"code": "0002", "message": "数据库错误"}







## 根据user_id修改user_name
@app.put("/user/updateUser/")
# 定义查询参数user_id和user_name
async def deleteUser(user_id: int, user_name: str):
    try:
        user1 = session.query(User).filter(User.userid == user_id).first()
        if user1:
            user1.username = user_name
            session.commit()
            session.close()
            return {"code": "0000", "message": "修改成功"}
        else:
            return {"code": "0001", "message": "参数错误"}
    except ArithmeticError:
        return {"code": "0002", "message": "数据库错误"}


class AlterUser(BaseModel):
    userid: int
    username: str


@app.put("/user/updateUser01/")
async def deleteUser(user: AlterUser):
    try:
        user1 = session.query(User).filter(User.userid == user.userid).first()
        if user1:
            user1.username = user.username
            session.commit()
            session.close()
            return {"code": "0000", "message": "修改成功"}
        else:
            return {"code": "0001", "message": "参数错误"}
    except ArithmeticError:
        return {"code": "0002", "message": "数据库错误"}


if __name__ == '__main__':
    uvicorn.run('mysqldb:app', host="127.0.0.1", port=8000, reload=True, debug=True, workers=1)
#woker=1进程数量

3.6返回对象转化成python字典

固定

方法1:

#对象转化成python字典
def class_to_dict(obj):
    is_list = obj.__class__ == [].__class__
    is_set = obj.__class__ == set().__class__
    if is_list or is_set:
        obj_arr = []
        for o in obj:
            dict = {}
            a = o.__dict__
            if "_sa_instance_state" in a:
                del a['_sa_instance_state']
            dict.update(a)
            obj_arr.append(dict)
        return obj_arr
    else:
        dict = {}
        a = obj.__dict__
        if "_sa_instance_state" in a:
            del a['_sa_instance_state']
        dict.update(a)
        return dict

方法2:

加上了时间判断

def class_to_dict_all(obj):
    """
    例如 GlobalRegion.query.all()
    :param obj:
    :return:
    """
    is_list = obj.__class__ == [].__class__
    is_set = obj.__class__ == set().__class__
    if is_list or is_set:
        obj_arr = []
        for o in obj:
            for k, v in vars(o).items():      #时间
                if isinstance(v, datetime.datetime):
                    o.__dict__[k] = v.strftime('%Y-%m-%d %H:%M:%S')   
            dict = {}
            a = o.__dict__
            if "_sa_instance_state" in a:
                del a['_sa_instance_state']
            dict.update(a)
            obj_arr.append(dict)
        return obj_arr
    else:
        dict = {}
        for k, v in vars(obj).items():
            if isinstance(v, datetime.datetime):
                obj.__dict__[k] = v.strftime('%Y-%m-%d %H:%M:%S')
        a = obj.__dict__
        if "_sa_instance_state" in a:
            del a['_sa_instance_state']
        dict.update(a)
        return dict

输出

user1 = session.query(data).all()
print(class_to_dict(user1))
session.close()

thmeticError:
return {“code”: “0002”, “message”: “数据库错误”}

class AlterUser(BaseModel):
userid: int
username: str

@app.put("/user/updateUser01/")
async def deleteUser(user: AlterUser):
try:
user1 = session.query(User).filter(User.userid == user.userid).first()
if user1:
user1.username = user.username
session.commit()
session.close()
return {“code”: “0000”, “message”: “修改成功”}
else:
return {“code”: “0001”, “message”: “参数错误”}
except ArithmeticError:
return {“code”: “0002”, “message”: “数据库错误”}

if name == ‘main’:
uvicorn.run(‘mysqldb:app’, host=“127.0.0.1”, port=8000, reload=True, debug=True, workers=1)
#woker=1进程数量








### 3.6返回对象转化成python字典

固定

`方法1`:

```python
#对象转化成python字典
def class_to_dict(obj):
    is_list = obj.__class__ == [].__class__
    is_set = obj.__class__ == set().__class__
    if is_list or is_set:
        obj_arr = []
        for o in obj:
            dict = {}
            a = o.__dict__
            if "_sa_instance_state" in a:
                del a['_sa_instance_state']
            dict.update(a)
            obj_arr.append(dict)
        return obj_arr
    else:
        dict = {}
        a = obj.__dict__
        if "_sa_instance_state" in a:
            del a['_sa_instance_state']
        dict.update(a)
        return dict

方法2:

加上了时间判断

def class_to_dict_all(obj):
    """
    例如 GlobalRegion.query.all()
    :param obj:
    :return:
    """
    is_list = obj.__class__ == [].__class__
    is_set = obj.__class__ == set().__class__
    if is_list or is_set:
        obj_arr = []
        for o in obj:
            for k, v in vars(o).items():      #时间
                if isinstance(v, datetime.datetime):
                    o.__dict__[k] = v.strftime('%Y-%m-%d %H:%M:%S')   
            dict = {}
            a = o.__dict__
            if "_sa_instance_state" in a:
                del a['_sa_instance_state']
            dict.update(a)
            obj_arr.append(dict)
        return obj_arr
    else:
        dict = {}
        for k, v in vars(obj).items():
            if isinstance(v, datetime.datetime):
                obj.__dict__[k] = v.strftime('%Y-%m-%d %H:%M:%S')
        a = obj.__dict__
        if "_sa_instance_state" in a:
            del a['_sa_instance_state']
        dict.update(a)
        return dict

输出

user1 = session.query(data).all()
print(class_to_dict(user1))
session.close()

夜羽122

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql笔记

mysql笔记前期mysql相关的学习是b站视频教程【狂神说Java】MySQL最新教程通俗易懂_哔哩哔哩 (゜-゜)つロ干杯~-bilibili最后一章节JDBC为Java相关, 暂时不进行学习.进行python 连接相关mysql学习1、命令提示符中(管理员身份打开cmd)启动数据库:net start (mysql+名称);关闭数据库:net stop (mysql+名称);打开数据库:mysql -uroot -proot (-u+账户)(-p+密码);打开数据库:mysql
复制链接

扫一扫