数据库mysql学习

目录

1. 数据库类型
2. 数据库核心元素
3. 安装mysql
4. sql语句的分类
5. DDL学习
6. DML学习
7. DQL查询语句
8. mysql内置函数
9. TCL 事务
10. 视图
11. 存储过程
12. 变量
13. 函数
14. 流程控制结构
15. 循环
16. mysql底层
17. 关于索引生效的问题
18. mysql的索引结构
19. 与python的交互

 

 

 

 

 

 

1.数据库类型

关系型数据库

非关系型数据库

2.数据库核心元素

1、sql:关系型数据库语言
2、Mysql 具体的数据库
3、rdbms 关系型数据库管理系统
4、数据库是一种特殊的文件

3.安装mysql

网上下载安装，不会就百度

4.sql语句的分类

sql：结构化查询语句

DML: 数据操作语言，对数据的增删改
DQL: 数据查询语言，数据查询语言
DCL: 数据控制语言，用于数据库的访问设置权限
DDL: 数据定义语言，对数据库、表的增删改

5.DDL学习

5.1 创建数据库

create database 数据库名 charset=编码;

1.不能重复创建数据库，否则会报1007错误
2.数据库语句不区分大小写

CREATE DATABASE IF NOT EXISTS 数据库名;			# 提高容错率

ALTER DATABASE 数据库名 CHARACTER SET 编码类型;

5.2 删除数据库

drop database 数据库名;
drop database if exists 数据库名;

5.3 使用数据库

use 数据库名;
1. 如果使用的数据库不存在，则报错1049

5.4 当前使用数据库

select database(); 			查看当前选中的数据库

1. 一定要加上()
2. 如果语句查询不到数据，我们没有使用数据库，也不会报错，返回null

select now();				查看当前时间

select version();			查看版本号

5.5 创建表

数据完整性：类型、约束

5.5.1 常见数据类型

使用数据的类型原则：尽量选择取值范围小的，节省存储空间

varchar				可变长度的字符串 在定义的过程中必须指定长度
char				固定长度的字符串  可不用指定长度，默认为一个字符，最长为255字符
decimal(m, n)		 m表示共有几位，n表示小数占多少位
int					整型, 使用4个字节

5.5.1.1 整型

 

 

5.5.1.2 浮点小数

 

 

1. float类型在存储的时候，存储的是二进制中最接近小数的那个值
2. float因为上述的特性，会存在误差的存在，精度不高
3. decimal，按照写入的数据进行存储，适用于场景为精度要求高的，比如货币的运算

 

5.5.1.3 字符串

 

text 大文本储存 数据大于4000时

 特点空间n的省略问题
char 固定长度的字符串 比较耗费 可以省略， 默认为1
varchar 可变长度的字符串 节省 不可以省略

5.5.1.4 日期

 

生产中根据产品规划设计来选择具体的类型

注意点：
1. timestamp 这种格式的数据，存在默认值，不允许为空值

查看当前时区

show variables like 'time_zone';

set time_zone='...';   更改时间

5.5.2 数据约束

not null			不能为空
primary key			主键
auto_increment		自增
enum()				枚举   规范数据 节省空间

zerofill 		   越界会使用0进行填充，将数据从有符号数，变为无符号数
unsigned		   设置无符号数
大于边界值时，默认使用边界值替换
如果出现int(num), 这里的num不是规定了存储的长度，规定的是显示宽度，一般配合的是zerofill使用
约束说明
primary key 主键，保证数据的唯一性，并且是非空的
default 默认值
not null 非空约束
unique 唯一值约束， 可以为空
foreign key 外键，限制两个表的关系，约束该字段的值必须来自与之相关联的主表的值
check 检查约束， CHECK <表达式> 约束字段条件，可以写但是mysql不支持

创建表时可以创建约束

修改表的时候可以创建约束

必须在插入数据之前创建约束

分类说明
字段约束 语法上都支持，但是外键约束是没有效果的
表级约束 除了非空、默认，其他都支持

外键

references 表2(外键字段) 在字段约束中不生效

添加外键
alter table 表1 add constraint foreign key(字段名) references 表2(字段名);

表级约束
alter table 表 add constraint 约束条件(字段名);


外键注意点
1. 添加外键的时候，使用的是表级约束，字段约束可以写但不生效

表级约束

create table 表名(
字段名 字段类型,
...

constraint 重命名字段名 约束条件(原字段名),
...
constraint 重命名字段名 foreign key(原字段名) references 表2(字段)
)
简化
create table 表名(
字段名 字段类型,
...

约束条件(原字段名),
...
foreign key(原字段名) references 表2(字段)
)

通用格式
create table 表名(
字段名 字段类型 约束条件,
...
foreign key(原字段名) references 表2(字段)
)

外键使用表级约束
其他使用字段约束

查看索引（约束名）

show index from 表名;

删除约束
alter table 表名 drop foreigh key 外键名;   删除外键
alter table 表名 drop index 唯一字段名;  删除唯一
alter table 表名 drop primary key;   删除主键


注意点：
1. 在删除约束之前，必须查看一下约束名
2. 在1的基础上删除

查看变量

show variables like 变量名;

 

5.5.3 创建表

create table 表名 (
	字段 数据类型 [约束条件],
	...
	字段 数据类型 [约束条件]
);
1. 最后一个字段末位不能加 , 
2. 其他字段在设置的过程中，需要 , 隔开

拷贝表
create table 表名1 like 表名;
拷贝表结构 不拷贝表数据

拷贝表2
create table 表名1 查询语句;
拷贝表结构 拷贝表数据

拷贝表3
create table 表名 部分字段查询语句 where id < 0;
拷贝部分表结构 不拷贝数据
只要保证查询语句恒不成立即可

5.5.4 查看表结构

desc 表名;

5.6 修改表结构

5.6.1 修改字段名以及字段结构

alter table 表名 change 旧字段名 新字段名 类型以及约束;  重命名
alter table 表名 modify 字段名 类型以及约束;		   不重命名

5.6.2 增加字段

alter table 表名 add 字段名 类型以及约束;

5.6.3 删除字段

alter table 表名 drop 字段名;

5.6.4 修改表名

alter table 表名 rename 新表名;
rename table 原表名 to 新表名;

5.7 查看表的创建

show create table 表名;

6.DML学习

6.1 数据的查询

select * from 表名;             查询所有字段数据

select 字段名[, 字段名] from 表名;	   查询指定字段数据

select 语句 where 条件判断    

select distinct 字段名 from 表名;   去除重复的数据

6.1.1 查询的重命名

select 字段名 as 新字段名 from 表名;
select 字段名 as 新字段名 from 表名 as 新表名;

select 表名.字段名 as 新字段名 from as 新表名;

 

6.2 数据的添加

insert into 表名 values(每一列数据按照顺序填写);   全列插入

insert into 表名 (字段名，...) values(字段对应数据);  缺省插入

insert into 表名 values(每一列数据按照顺序填写),(每一列数据按照顺序填写),...;
insert into 表名 (字段名，...) values(字段对应数据),(字段对应数据),...;		#多条数据插入


insert into 表名 set 数据键值对;
对于空值可以直接不写入，也可以写入字段名=NULL

在mysql中：

1.value 多条插入时，性能比较快

2.values 单条插入时，性能比较快

6.2.1 value/values 和 set之间的对比

第一种方式支持多行插入，支持子查询
第二种方式不支持多行插入，不支持子查询

6.2.2 主键自增

id可以用0代替，也可以不写

6.2.2 添加子查询结果

insert into 表名 (select 查询语句);

6.3 数据的修改

update 表名 set 字段名=值... where 条件判断
如果有条件筛选，就将符合条件筛选的数据进行修改
如果没有条件筛选，所有的数据进行修改

6.4 数据的删除

delete from 表名 where 条件;

truncate 表名;    清空表的数据       不影响表结构

drop table 表名;

 

7.DQL查询语句

7.1 where 引导的条件筛选语句

对未处理的数据进行排序

7.1.1 比较运算符

where 
>  大于
<  小于
=  等于
!  非
<> 不等于

7.1.2 逻辑运算符

and 与
or 或
not 非

 

7.1.3 范围查找

非连续范围查找

select * from 表名 where 字段名 in (范围);

连续范围查找

select * from 表名 where 字段名 between 起始 and 结束;

7.1.4 查找空值

select * from 表名 where 字段名 is null;
select * from 表名 where 字段名 is not null;
查找空值时，不能使用='' 因为 null != ''
同时不能使用=来判断

7.1.5 包含查找

where like '%贪婪匹配内容%';
where not like '...';

7.1.6 排序

order by 字段名[,..];  		升序
order by 字段名 asc[,...];	升序
order by 字段名 desc[,...];	降序
order by -字段名[,...]; 		降序

多个参照排序时，当前一个参照的值相同时，以第二个来排序，以此类推

7.1.7 分组

group by 字段名;

group_concat(字段名)    查看分组之后数据的集合  

当版本过高时，mysql默认存在ONLY_FULL_GROUP_BY，此时group by无法使用
解决方法
select @@sql_mode;
去掉结果中的ONLY_FULL_GROUP_BY  复制 
set session sql_mode = '复制结果'
set global sql_mode= '复制结果'

7.2 having 条件筛选

对处理过的数据进行筛选

序号语句相同点不同点
1 where 条件筛选 分组之前和条件判断，对于已经存在的原始数据做筛选，在group by的前面
2 having 条件筛选 用来查询分组之后的条件筛选，做出战士，但是having只能用于group by，并且只能在group by后面

7.3 limit

select * from 表名 limit start, count;
start: 起始行
count: 行数
select * from 表名 limit count;
从第一行开始 count行

7.4 完整的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

7.5 多表查询

7.5.1 多表查询

查询的过程中出现涉及多张表的现象

7.5.2 笛卡尔乘积现象

表一 m条 表二 n条 查询结果为 m * n

现象的原因：没有指定连接条件

7.5.3 分类

7.5.3.1 内连接 99

select * from 表一 inner join 表二 on 连接条件;

7.5.3.2 内连接 92

select * from 表一,表二 where 连接条件;

7.6 sql99语法学习

7.6.1 内连接

select 查询字段 
from 表名1 
inner join 表名2 
on 连接条件 
where 元数据筛选条件 
group by ... 
having ... 
order by ...

7.6.3 三表连接查询

select 查询字段
from (表名1 inner join 表二 on 连接条件)
inner join 表三 on 连接条件 ...

 

7.6.4 交叉连接

返回笛卡尔乘积

select 字段
from 表1 
cross join 表2;

7.6.5 外连接

7.6.5.1 左右连接

连接方式结果主表
left join(左连接) 内连接的查询结果+左表具有的特殊数据 左表
right join(右连接) 内连接的查询结果+右表具有的特殊数据 右表

7.6.5.2 全外连接

全外连接 = 内连接的查询结果+表一中有但是表二中没有的数据+表二中有表一中没有的数据

mysql 不支持全外连接

oracle 支持全外连接 full outer join

7.7 子查询

在一个select语句中，嵌入了另外一个select语句，就把被嵌入的语句叫做子查询语句

7.7.1 子查询分类

分类解释
标量子查询 子查询返回的结果是一行数据（一行一列）
列子查询 子查询的结果是一列（一列多行）
行子查询 子查询的结果是一行（一行多列）
表子查询 子查询的结果是多行多列

7.7.2 子查询放在where和having后面

7.7.2.1 特点

子查询放在()中
列子查询 一般使用 any all some in
子查询优先级高于主查询

7.7.2.2 标量子查询

一般配合的单行操作符 > < >= <= !=

7.7.2.3 列子查询

  
in / not in 等于查询列表中的任何一个对象
any / some 和子查询返回的某一个值进行比较
all 和子查询返回的结果中所有的值进行比较

7.7.3 select

select *, (一行一列的子查询) from 表名 ...;

注意点：

select后面只支持标量子查询，一行一列

7.7.4 from

select * from (子查询) ...;

注意点：

在from后面的子查询充当是一个表（数据源），一定要给这个子查询起个名字，只有起名了，才能被当成一张表

7.7.5 exists

select exists (查询语句);
结果 0 | 1

判断是否存在值

7.7.6 union

查询语句1
union
查询语句2

注意点：

  1. 联合查询的过程中，两次查询的字段个数必须一致
  2. 尽量使得相同意义的字段放在同一列
  3. 使用union会自动去重
  4. 使用union all不会去重

适用场景：查询结果来自于多张表，并且多张表之间没有直接联系，但是查询的信息一致

8.mysql内置函数

8.1 database()

select database()

查看当前使用的数据库

8.2 now()

select now()

查看当前时间

8.3 version()

select version()

查看当前版本

8.4 数字函数

8.4.1 round()

select round(2.225, 2)	--> 2.23
select round(2.224, 2)  --> 2.22
指定保留几位小数，四舍五入

8.4.2 truncate()

select truncate(2.225, 2) --> 2.22
指定保留几位小数，截断

8.4.3 mod()

取余

8.4.4 聚合函数

avg() 							平均值
min()							最小值
max()							最大值
count()							计算行数

8.4.5 greatest()

判断最大值

8.4.6 least()

判断最小值

8.4.7 concat()

字符串拼接

8.4.8 随机函数

substring(原始字符串, startpoint, 个数)

rand()  0-1随机小数

FLOOR(i+RAND()*(j-i+1)) 产生一个 （i，j）之间的整数
ROUND(RAND()*(j-i+1)+i-0.5)

 

9. TCL 事务

9.1 事务的定义

一个或多个sql语句组成一个执行单元，这个执行单元，要么全部成功，要么全部失败

9.2 存储引擎

定义：数据库中使用不容的存储技术将数据存在文件中

show engines;										查看当前数据库支持的存储引擎

show variables like '%storage_engine%';				  查看当前数据库默认的存储引擎

注意点：
1. 经常使用的是MyISAM MEMORY InnoDB
2. innoDB是mysql默认的存储引擎
3. innodb是支持事务的，myisam、memeory不支持事务

9.3 ACID

9.3.1 原子性 (Atomicity)

事务是一个不可拆分的工作单位，其中的操作要么全部完成，要么全部失败

9.3.2 一致性 (Consistency)

事务操作成功后，前后数据的完整性必须保持一致

9.3.3 隔离性 (Isolation)

并发操作中的，事务与事务之间有隔离性，事务的执行不能被其他事务干扰

9.3.4 持久性 (Durability)

一旦事务提交后，它对数据库的操作数据改变是永久的，接下来的任何突发情况都不会发生影响

9.4 事务的创建

9.4.1 隐式事务

没有明显的开启和结束的标记

insert\update\delete

9.4.2 显式事务

有明显的开启和结束的标记

必须设置自动提交功能关闭

show variable like '%autocommit%';

set autocommit=0;
注意点：只针对当前会话有效

9.4.2.1 提交操作 (事务成功)

start transaction;
操作语句;
commit;

9.4.2.2 回滚操作 (事务失败)

start transaction;
操作语句;
rollback;

注意点：

truncate table 表名; 不支持回滚

delete from 表名 筛选条件 支持回滚

9.5 数据库的隔离级别

对于多个事务运行时， 如果事务访问的是相同的数据，如果没有设置隔离级别，就会导致并发问题

9.5.1 事务的并发问题

并发问题具体表现
脏读 对于两个事务， t1、t2 如果t1读取了已经被t2修改过但是没有提交的数据，但是最后t2回滚了。t1读取的内容是临时并且无效的
不可重复读 对于两个事务， t1、t2 读取了一个字段的值，但是t2更新了这个字段，t1再次读取的时候，两次读取的数据是不一致的
幻读 对于两个事务t1、t2，t1从一个表中按照一定条件读取数据，接着t2将表进行更新，插入了几行新数据，t1再次以同样条件读取，相比第一次多了几行数据

9.5.2 隔离级别

隔离级别说明
未提交读（读取未提交的数据） 允许事务读取未被其他事务提交的更改，所以脏读、不可重复读、幻读都会出现
已提交读（读取提交内容） 只允许事务读取已经被其他事务提交的更改，能够解决脏读问题，但是不可重复读，和幻读都会出现
可重复读 确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可避免脏读和不可重复读，幻读还是会出现
可序列化（可串行化） 确保事务可以在一个表中读取相同的行，在这个事务持续期间，不允许其他事务对于表数据执行插入，更新和删除操作，会解决所有的并发问题，但是性能很低
数据库支持的隔离级别
oracle 已提交读、可序列化，默认为已提交读
mysql 未提交读、已提交读、可重复度、可序列化，默认为可重复读

9.5.3 设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;				未提交读
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;					已提交读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;				可重复读
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;					可序列化
SHOW VARIABLES LIKE 'transaction_isolation';						   查看隔离级别

9.6 事务保存点

savepoint 名字;					设置保存点
rollback to 名字;					回滚到保存点

10. 视图

10.1 定义

一种虚拟存在的表，行和列的数据来自于视图中的查询所用的表，并且是使用视图的时候动态生成的，只保存sql逻辑，不保存结果

视图：是虚拟的表，和普通的表一样，通过表，动态生成新数据

10.2 视图使用的场景

在不同的使用场景下，比如商品展示的时候，商品列表、商品详情，可能会用到相同的业务逻辑，而且这个逻辑的sql语句特别麻烦，没有必要重复书写多次，可以使用视图，一次创建，下次使用相同的逻辑，使用视图

10.3 基本的视图格式

create view 视图名 as sql查询语句;

10.4 修改视图

create or replace 视图名 as 查询语句;

alter view 视图名 as 查询语句;

10.5 视图内容查看

show create view 视图名;			查看视图逻辑
desc 视图名;					   查看视图结构

10.6 删除视图

drop view 视图名;

10.7 更新视图

  1. 对于视图的增删改查，会涉及原始表，会将原始表的数据进行修改
  2. 针对这个安全问题，通过设置权限对的方式进行解决

10.7.1 关于更新视图的问题（不能更新）

  1. 包含分组函数、distinct、union、union all等 不能更新视图

  2. 常量视图不能更新

  3. 存在子查询视图不能更新

  4. 数据源是一个不能更改的视图，查询语句中的from后面是一个不能更改的视图 不能更新

     

  5. 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。

  6. DISTINCT

  7. GROUP BY

  8. HAVING

  9. UNION或UNION ALL

  10. 位于选择列表中的子查询

  11. Join

  12. FROM子句中的不可更新视图

  13. WHERE子句中的子查询，引用FROM子句中的表。

  14. 仅引用文字值（在该情况下，没有要更新的基本表）。

  15. ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

10.8 视图与表的区别

 创建语句占用物理空间CRUD
视图 create view 视图名 as 查询语句 基本没有，保存的是逻辑 查询
表 create table 表名 ... 占用，保存的是数据 全部

11. 存储过程

11.1 存储过程的格式

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
合法有效的sql语句
END;

注意点：
1. 参数列表  参数模式 参数名字 参数类型
	参数模式：
		in 该参数是可以作为输入，说明当储存过程进行调用的时候需要传入
		out 该参数是作为输出的，类似返回值
		inout 该参数输入时被调用，输出时返回
2. 存储过程需要设置结束符，因为sql语句需要结束符			delimiter 符号

11.2 关于存储过程中的参数的问题

11.2.1 参数可以为空值

参数可以不传

11.2.2 参数类型为in---单个参数

参数模式 参数名 参数数据类型
调用的过程中，call 存储过程的名字(填写参数)

11.2.3 参数类型为in---多个参数

如果存在多个数据，用“,”隔开
注意调用过程中的参数个数

11.2.4 变量的声明

DELIMITER $
CREATE PROCEDURE p4(username VARCHAR(11), pwd VARCHAR(11))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result FROM userinfo WHERE userinfo.`name`=username AND userinfo.`password`=pwd;
SELECT IF(result>0, '成功', '失败');
END$

CALL p4('二毛',2);


注意点：
1. 声明一个变量  declare 变量名 数据类型

11.2.5 参数out

DELIMITER $
CREATE PROCEDURE p5(brandname VARCHAR(11), OUT goodsname VARCHAR(11))
BEGIN
SELECT goods.name INTO goodsname FROM goods INNER JOIN brand ON goods.`brande_id`=brand.`id` WHERE brand.`brandname`=brandname;
END$

CALL p5('天猫一小时', @bname);
SELECT @bname;

11.2.6 参数inout

DELIMITER $

CREATE PROCEDURE p7(INOUT a INT, INOUT b INT)
BEGIN
SET a=a*a;
SET b=b*2;
END$

SET @m=2;
SET @n=4;
CALL p7(@m, @n);

SELECT @m, @n

11.3 存储过程构成的查看

show create procedure 存储过程名;

11.4 存储过程的删除

drop procedure 存储过程名;

12. 变量

12.1 系统变量

12.1.1 定义

由系统提供的，属于服务器层面的，启动之后就会出现的，已经有初始值的变量

12.1.2 系统变量的分类

1. 全局变量
	mysql在启动的时候由服务器将它们进行初始化成默认值
2. 会话变量
	针对的是每次创建的链接，由mysql来进行初始化操作，会将当前所有的全局变量复制一份，存储为会话变量

12.1.3 全局变量

(1) 查看所有全局变量
	show global variables;
(2) 模糊查找全局变量
	show global variables like '%变量%';
(3) 查找具体的某个全局变量
	select @@变量名;
	select @@global.变量名
(4) 对于某个具体的全局变量进行赋值
	set @@global.变量名=值

12.1.4 会话变量

(1) 查看所有会话变量
	show variables;
	show session variables;
(2) 模糊查找会话变量
	show variables like '%变量%';
	show session variables like '%变量%';
(3) 查找具体的某个会话变量
	select @@变量名;
	select @@session.变量名;
(4) 对于某个具体的会话变量进行赋值
	set @@session.变量名=值;
	set session 变量名=值;

12.2 自定义变量

12.2.1 定义

变量由用户定义，不是系统提供的

12.2.2 变量的使用步骤

  1. 声明
  2. 赋值
  3. 使用

12.2.3 自定义变量

12.2.3.1 用户变量

作用域：针对当前的会话

1. 声明
    set @变量名=值;
    set @变量名:=值;
    select @变量名:=值;

2. 赋值
	select 字段名 into @变量名 ...

3. 调用
	select @变量名;
	
注意点:
	可以在会话的任何地方使用

12.2.3.2 局部变量

仅仅定义在begin ... end中

1. 声明 
	declare 变量名 变量类型;

2. 赋值
	set 变量=值;
	set 变量:=值;
	select @变量:=值;
3. 调用
	select 变量名;

12.2.3.3 用户变量和局部变量

 作用域定义与使用的位置语法
用户变量 当前会话 会话的任何位置，都可以定义 需加@
局部变量 begin...end begin...end 不需要加@，需要声明数据类型

13. 函数

13.1 函数的结构

CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
BEGIN
函数体
return 值;
END

set global log_bin_trust_function_creators=TRUE;
解决必须填参数问题

注意点：
1. 参数列表：参数名 参数类型
2. 必须有且只有一个返回值
3. 函数的参数可以不写

13.2 实例

CREATE FUNCTION f1() RETURNS INT
BEGIN
	DECLARE c INT;
	SELECT COUNT(*) INTO c FROM goods;
	RETURN c;
END$
select f1()

CREATE FUNCTION f2(brand_name VARCHAR(20)) RETURNS FLOAT
BEGIN
DECLARE a FLOAT;
SELECT AVG(goods.price)INTO a FROM goods INNER JOIN brand ON brand.id=goods.`brande_id` WHERE brand.`brandname`=brand_name;
RETURN a;
END
select f2('老马生鲜')

14. 流程控制结构

14.1 if函数

if (表达式1, 表达式2, 表达式3)

执行顺序：
首先判断表达式1，如果表达式1成立，则返回表达式2的值，否则返回表达式3
运行在任何地方

例子
SELECT IF(5 > 6, 'a', 'b');   --> b

14.2 if结构

if 条件判断 then 语句;
elseif 条件判断 then 语句;
...
else 语句;
end if;

适用于函数与存储过程中

在存储过程中的实例：
DELIMITER $
CREATE PROCEDURE p14(zhijing FLOAT, OUT levels CHAR)
BEGIN
IF 0 < zhijing AND zhijing <= 1 THEN SET levels='d';
ELSEIF 1 < zhijing AND zhijing <= 2 THEN SET levels='c';
ELSEIF 2 < zhijing AND zhijing <= 3 THEN SET levels='b';
ELSE SET levels='a';
END IF;
END

CALL p14(5, @levels)

SELECT @levels

14.3 case结构

case
when 条件判断 then 语句;
...
else 语句;
end case;

注意点：
在真实的使用场景下，else可以省略


在函数中的实例：
DELIMITER $
CREATE FUNCTION f3(grade DECIMAL) RETURNS CHAR
BEGIN
	DECLARE levels CHAR;
	CASE
	WHEN grade BETWEEN 0 AND 59 THEN SET levels='F';
	WHEN grade BETWEEN 60 AND 69 THEN SET levels='E';
	WHEN grade BETWEEN 70 AND 79 THEN SET levels='D';
	WHEN grade BETWEEN 80 AND 89 THEN SET levels='C';
	WHEN grade BETWEEN 90 AND 95 THEN SET levels='B';
	WHEN grade BETWEEN 96 AND 100 THEN SET levels='A';
	END CASE;
	RETURN levels;
END
SELECT f3(70)

15. 循环

15.1 while

while 限制条件 do
	循环体
end while;

whilename: while 限制条件 do
	if 条件判断 then leave whilename; end if;
	循环体
end while;


在存储过程中的例子：
DELIMITER $
CREATE PROCEDURE f15()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
INSERT INTO girls(gname) VALUES(CONCAT('小红', i));
SET i=i+1;
END WHILE;
END
语句与python类似语句作用
iterate continue 跳出当前循环并继续循环
leave break 跳出当前循环并退出循环

15.2 loop

loopname: loop
			if 条件判断 then leave loopname;
			end if;
			循环体
end loop;

15.3 repeat

repeat
	循环体
until 结束条件 end repeat;

16. mysql底层

16.1 数据库底层

  1. 连接层 连接客户端与服务器
  2. 服务层 提供各种用户接口select等 提供sql优化器
  3. 引擎层 提供各种储存数据的方式 innodb等
  4. 存储层 存储数据

16.2 索引

定义：帮助mysql高效获取数据的数据结构
索引的本质就是一个数据结构


优点：
1. 类似图书馆的书籍目录，提高数据的检索效率，降低数据库的io成本
2. 通过索引列对数据进行了排序，降低了数据排序的成本，降低了cpu的消耗


缺点：
1. 索引本质上是一个数据结构，索引本质上也是一张表，保存的是主键和索引字段，并指向实体表的记录，所以索引也需要占用物理空间
2. 索引只是提高效率的手段之一，如果数据库有大数据量的表就需要花费更多的时间研究创建最优秀的索引或者优化语句
3. 如果出现了增加修改删除操作的时候，除了要更新数据库的表，还需要更新索引列的字段

16.3 索引分类

分类说明
单值索引 索引只包含一个字段，一个表可以有很多个单值索引
唯一值索引 索引字段的值必须是唯一，但是允许空值
复合索引 一个索引存在多个字段
主键索引 id不能重复，而且不能存在空值

16.4 创建索引

16.4.1 单值索引

create index 索引名 on 表名(字段);
alter table 表名 add index 索引名(字段);

16.4.2 唯一值索引

create unique index 索引名 on 表名(字段);
alter table 表名 add unique index 索引名(字段);

16.4.3 复合索引

create index 索引名 on 表名(字段1, ..., 字段n);
alter table 表名 add index 索引名(字段1, ..., 字段n);

16.5 查看索引

show index 表名;

16.6 删除索引

drop 索引类型 索引名 on 表名;

16.7 创建索引的情况

1. 主键自动创建主键索引
2. 字段频繁的作为查询条件应该设置成索引
3. 查询中与其他表产生关联的字段，外键建立索引
4. 稳定、基本不修改的字段适合创建索引
5. 单值索引/复合索引的选择问题   高并发情况下优先使用复合索引
6. 查询中需要排序，排序的字段建立索引，会提高排序效率
7. 查询中统计或者分组的字段创建索引

16.8 不创建索引的情况

1. 经常发生增删改的表 (频繁更新的索引不仅要更新原表还要修改索引表，加重io负担)
2. where条件语句中用不到的字段
3. 如果数据重复的字段没有必要创建
4. 表的数据量小

17. 关于索引生效的问题

17.1 关于怎么查看索引生效的问题

explain + sql语句 模拟优化器质性sql查询语句 分析查询语句的性能

17.2 explain 执行的结果

EXPLAIN SELECT * FROM goods WHERE goods.`brande_id`=(SELECT brand.`id` FROM brand WHERE brand.`brandname`='老马生鲜')

 

 

17.3 id的具体内容

select查询的序列号，包含的是一组数字，表示的是查询中执行的select子句或者表的顺序
id 相同，执行的顺序由上而下
id 不同，如果是子查询，id的序号会进行递增操作，id越大优先级越高，越先被执行

17.4 select_type

类型说明
primary 查询中包含任何复杂的子部分，最外层的查询被标记为primary
subquery 在select或者where中包含了子查询
simple 简单的select查询，不包含子查询
union 第二个select语句出现在union之后，就会被标记为union
union result 从union表中获取到结果
derived 在from后面的列表中包含子查询，mysql会执行这些子查询，会把这些结果放在临时表里面，union包含在from子句的子查询中，外层的select被标记为derived

17.5 table

显示这个数据与哪张表有关

17.6 type

类型说明
all full table scan 遍历全表，找到匹配的行
range 只检索给定范围的行，一般情况下在出现> < between in 等查询
index full index scan 按照索引顺序的全表扫描，速度比all快
ref 使用了非主键或者非唯一索引的普通索引(即索引可以重复情况下的)
ref_eq 使用了唯一索引的查询
const 使用了主键索引的查询
system 表只有一行数据，这个平常一般不会出现，一般忽略

system > const > ref_eq > ref > range > index > all

最优 -----------------------------------------------------------最差

语句一般达到range 合格 最好达到ref

17.7 possible_keys

可能存在的索引

17.8 key

实际用到的索引

 

18. mysql的索引结构

 
BTree索引
hash索引
full-text索引
RTree索引

19. 与python的交互

import pymysql
# todo 创建连接
ms = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', database='shanghui', charset='utf8')
# todo 获取操作句柄
cs = ms.cursor()
# todo goods表查询
cs.execute('select * from goods;')
print(cs.fetchall())
cs.execute('insert into userinfo values (0, "张", 2, 3)')
ms.commit()
cs.execute('select * from userinfo')
print(cs.fetchall())
cs.close()
ms.close()


查
cur = cursor()
ret = cur.execute(查询语句)
cur.fetchone()	 获取一条数据
cur.fetchall()   获取所有信息

增删改
cur = cursor()
cur.execute(查询语句)
con.commit()			
注意点： 增删改 牢记提交事务

19.1 防止sql注入问题

解决字符串拼接 sql注入

后端获取前端数据时利用正则校验

pymysql中使用参数列表化可以解决

posted @ 2020-05-12 18:53  爱学习的红领巾  阅读(119)  评论(0)  编辑  收藏  举报