MySQL8（Centos8）

最新推荐文章于 2024-06-05 12:57:40 发布
最新推荐文章于 2024-06-05 12:57:40 发布
阅读量606 收藏 1
点赞数
分类专栏： mysql8 centos 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/alun550/article/details/108426229
版权

Python —— MySQL

 
 
 
 
 
 
 
 

sql小点

功能关键字描述
数据定义（DDL）CREATE、DROP、ALTER增删改表结构
数据查询（DQL）SELECT查询已有数据
数据操作（DML）INSERT、UPDATE、DELETE增删改数据
数据控制（DCL）GRANT、REVOKE数据的访问、修改权限
事务处理（TPL）START TRANSACTION、COMMIT、ROLLBACK以原子化行为组来修改数据，保证在并发下的数据一致性
指针控制（CCL）CURSOR相关对多条记录逐行控制

 
 

mysql qps —> 5000~8000

 
 
 
 

域约束

create domain domainName fieldType check(value in ('value1', 'value2'));

 
 

域约束类似于字段枚举（ enum(‘value1’, ‘value2’)）

 
 
 
 
 
 
 
 

E-R 图

E-R图，即实体-关系（Entity-Relationship）图

 
 

E-R图的基本属性：

  • 实体（表）：客观存在的事物、事件、角色等，比如采购员、老师、课程、订单等
  • 实体属性（列）：用于描述实体的特性，每个实体可以有多个属性，比如老师的性别、名字、住址等
  • 关系（表关联）：反映两个实体之间客观存在的关系

 
 

E-R图中的描述

  • 方块 —— 实体
  • 椭圆 —— 实体属性
  • 菱形 —— 关系
    在这里插入图片描述
    上图的 任教 1：N 表示 1个老师只可带一个课，但是一个课可由多个老师带

 
 

关系表的设计过程

  • 需求分析
  • E-R图设计
  • 数据库选型
  • 实体转表，属性转列
  • 一对一关系的，设置相同的主键列
  • 一对多关系的，在多的表中设置外键关联一表的主键
  • 多（M）对多（N）的，创建一个单独的表表示该关系，该表的两列由两表的主键值填充。且使用这两个列组合作为主键
  • 重新审核所有的表，对需要的地方添加约束，对常用的条件字段设置索引

 
 

上图的 E-R 图可以将关系实例化为下表

 

课程表
列名类型索引、约束作用及备注
idINTPK唯一标识
titleTEXTNOT NULL课程名
periodINTIndex学时
descriptionTEXT课程描述

 

老师表
列名类型索引、约束作用及备注
idINTPK唯一标识
nameTEXTNOT NULL姓名
genderBOOLIndex性别True：男False：女
addressTEXT住址
course_idINTFK: course.id由于1:N关系所添加的外键

 

学生表
列名类型索引、约束作用及备注
student_idINTPK学号，唯一标识
nameTEXTNOT NULL姓名
ageINTIndex年龄
parentTEXT家长

 

学生证表
列名类型索引、约束作用及备注
student_idINTPK学号，唯一标识
startFromTIMESTAMP注册日期、入学日期
endToTIMESTAMP本学生证有效期的截止日

 

学生及课程多对多关系表
列名类型索引、约束作用及备注
student_idINTPK, FK: student.student_id学生标识
course_idINTPK, FK:course_id课程标

 
 

实体关系的实现

  • 1:1关系的实现 —— 学生表和学生证表的相同主键
  • 1:N关系的实现 —— 老师表的外键关联到课程表
  • M:N关系的实现 —— 新建一张独立的表，两列共同组成主键，且两列分别通过外键关联学生、课程表

 
 
 
 
 
 
 
 

远程登录linux服务的MySQL

 
 

登录本机时

mysql -uroot -hlocalhsot -P3306 -p

在这里插入图片描述

 
 
 
 

远程登录

远程使用 root 登录会提示无法登录，回到 linux 的 mysql 中，创建一个新的用户，用于远程登录

mysql -uroot -h192.168.1.103 -P3306 -p

在这里插入图片描述

 
 

--- 创建用户
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; 
--- 授予权限
GRANT ALL ON *.* TO '用户名'@'主机' WITH GRANT OPTION;
--- 刷新权限
FLUSH PRIVILEGES;
  • *.* 代表所有数据库的所有表
  • 权限类型 —— select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file

 
 

创建一个允许任意主机登录的账号

CREATE USER 'alun'@'%' IDENTIFIED BY '0403';
GRANT ALL ON *.* TO 'alun'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

 
 
 
 

一些其他命令

 
 

查看某库中存在哪些用户

use mysql;
select user, host from user;

在这里插入图片描述

 
 

查看权限

--- 查看当前用户权限
show grants;
--- 查看某用户权限
show grants for '用户'@'主机';

在这里插入图片描述

 
 

回收权限

--- 回收所有权限
revoke all privileges on *.* from '用户'@'主机';
--- 回收权限的传递
revoke grant option on *.* from '用户'@'主机';

 
 

删除用户

--- 切换数据库
use mysql;
--- 查看用户表中的用户及其主机
select user, host from user;
--- 删除用户
drop user '用户名'@'%';

 
 

mysql清屏 —— 仅linux

system clear;

在这里插入图片描述

 
 

修改密码

 
 
 
 
 
 
 
 

数据库操作

 
 
 
 

创建数据库

-- 创建数据库，并设置字符集为 utf-8
create database python_mysql charset=utf8;
-- 查看是否创建成功
show databases

在这里插入图片描述

 
 
 
 

修改数据库编码方式

alter database <databaseName> charset=utf8;

 
 
 
 

创建表

1、创建表需要先选中某个数据库
2、创建表时若不指定编码方式则默认使用数据库的编码方式

-- 选中数据库
use python_mysql;
-- 创建表
create table if not exists tableName(columnName columnType [params], .....)
-- 查看表是否创建成功
show tables;

pramas：

  • AUTO_INCREMENT —— 字段自增，一般用于主键
  • NOT NULL —— 设置字段非空
  • PRIMARY KEY —— 在定义字段时设置主键
  • COMMENT —— 注释，用户的唯一标识
  • UNIQUE —— 字段唯一约束，值只允许存在一次

 

设置主键，在tableName(col1, col2…, PRIMARY KEY ( columnName ))

在这里插入图片描述
在这里插入图片描述

 

支持字段类型如下

类型大小范围（有符号）范围（无符号）用途
TINYINT1 byte(-128，127)(0，255)小整数值
SMALLINT2 bytes(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 bytes(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 bytes(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 bytes(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度浮点数值
DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

 
 
 
 

删除表

drop table [if exists] <tableName>;

在这里插入图片描述

 
 
 
 

查看表结构

desc <tableName>;
describe <tableName>;

在这里插入图片描述

 
 
 
 

查看建表语句

show create table <tableName>;

在这里插入图片描述

 
 
 
 

修改表

对表的修改基本都使用 alter table，但中间所用的关键字有所不同

关键字作用
rename重命名表
rename to移动至别的库
add / add first / add after添加字段 / 表最后添加 / 表某个字段后添加
modify修改字段属性
change / change after修改字段名 / 修改字段位置
drop删除字段

 

后面子节点中所有的 【属性】 均代表 primary key 等属性

 
 

修改表名

alter table <tableName> rename  <newTableName>;

在这里插入图片描述

 
 

移动表到别的数据库

alter table <tableName> rename to <dbName.table>;

在这里插入图片描述

 
 

添加字段

末尾添加新字段

alter table 表名 add 字段名 数据类型 【属性】;

首位添加新字段

alter table 表名 add 字段名 数据类型 【属性】 first;

在某字段后添加新字段

alter table 表名 add 字段名 数据类型 【属性】 after 指定字段;

 
 

修改字段

修改字段属性

alter table 表名 modify 字段名 数据类型 【属性】;

修改字段名

alter table 表名 change 原字段名 新字段名 数据类型 【属性】;

修改字段位置

alter table 表名 change 原字段名 新字段名 数据类型 after 指定字段;

 
 

删除字段

alter table 表名 drop 字段名;

 
 

修改表的存储引擎 —— 最简便法

alter table 表名 engine=InnoDB;

原理：MySQL按行将数据从原表复制到一张新表中

 

特点：

  • 适用于任何引擎
  • 执行时间很长
  • 期间可能会消耗掉系统所有的I/O能力
  • 会对原表上锁

 
 

修改表的存储引擎 —— 最安全法

使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎，同时修改表名（不可存在同名表），且要注意删除掉DROP TABLE语句，以免原数据丢失

 
 

修改表的存储引擎 —— 最实用法

原理：先创建一张新存储引擎表，再分块（或者不分块）的插入数据

 

不分块插入，数据量大时则执行速度很慢

create table 新表名 like 原表名;
alter table 新表名 engine=InnoDB;
insert into 新表名 select * from 原表名;

 

分块插入，需要以事务提交的方式运行，以免产生过多的undo

create table 新表名 like 原表名;
alter table 新表名 engine=InnoDB;

start transaction;
insert into 新表名 select * from 原表名 where 主键 between 值1 and 值2;
commit;

start transaction;
insert into 新表名 select * from 原表名 where 主键 between 值3 and 值4;
commit;

 
 
 
 

多表查询

 
 

union

作用：将两表上下合并（将表2追加到表1中）

 

用法：

select * from table1 union select * from table2;

在这里插入图片描述

 

注意：
1、列的数量必须匹配

2、列名、类型均无须匹配

3、默认列名取左边表

 
 

inner join…on

作用：横向连接两表，当没有on条件时，则求笛卡尔积。当有on条件时，则仅取符合on的两表交集

在这里插入图片描述

 

用法：

select xxx from t1 inner join t1 on t1.id = t2.id

select t1.field1, t1.field2, t2.field1 from t1 inner join t2

在这里插入图片描述

在这里插入图片描述

 
 

left join…on / left outer join…on

作用：横向连接两表，会保留左侧表中不符合 on 条件的数据

在这里插入图片描述

 
 

right join…on / right outer join…on

作用：横向连接两表，会保留右侧表中不符合 on 条件的数据

在这里插入图片描述

在这里插入图片描述

 
 
 
 

视图

语句：

create view viewName as ...

drop view viewName;

 

如：

create view V_STUDENT as (select s1.*, s2.math math, s2.english english from student s1 inner join score s2 on s1.id = s2.id);

在这里插入图片描述

 

特点：

 

1、视图相当于是一组查询语句，可以提升开发效率，但不会提升查询效率

 

2、创建视图依赖于一个查询

 

3、视图占用空间，且不会自动清除

 

4、视图适合多表联合查询，但不适合增、删、改，这可以提高执行效率

 

5、修改原表数据，会影响视图数据（视图是动态的）。修改视图数据，原表也会改变（视图表与原表联动）

 

6、在多表查询的视图中，无法同时修改多个基表

在这里插入图片描述

 

7、视图表是否可以被修改，依赖于创建视图表时的算法值 ALGORITHM —— 有三个可选值 UNDEFINED（默认，可修改）、MERGE（可修改）、TEMPTABLE（不允许修改）

在创建视图时指定算法

 create algorithm=temptable view viewName as ...

 
 
 
 

约束

 
 

约束条件名的规范：
非外键：
2个字符的约束条件_作用的表名_作用的列

外键：
2个字符的约束条件_作用的表名_外键关联的表_作用的列

外键约束的写法：
CONSTRAINT 约束条件名 FOREIGN KEY( 列名 ) REFREENCES( 外键连接表名( 外键主键列名 ) )

 
 

外键约束

功能：

1、通过某个字段将两个表关联起来

2、两表存在主表和从表

3、主表中存在的，从表中可以存在或者不存在对应数据。但主表中不存在的，不可在子表中存在

 
 

特点：

1、可以很好的保持数据一致性

2、外键的存在会导致每次数据插入、删除、修改的时候，均会对数据进行检查，以满足外键约束，所以对性能会有损耗

 
 

语法：

alter table 从表名 add constraint ‘fk_从表名_主表名_从表列名’ foreign key(字段) references 主表名(主表字段) ;

 
 
 
 
 
 
 
 

事务

作用：保证一个业务的一系列动作同时生效或失效，主要为 insert，update，delete 语句而设置

 
 

语法:

– 开始事务
start transaction;
***
***
***
commit;

 
 

特性：

  • 原子性（Atomicity） —— 事务中所有操作，要么全部成功，要么全部失败，即不可细分执行事务的某一部分
  • 一致性（Consistency） —— 写入的资料必须完全符合所有的预设规则，包含资料的精确度、串联性
  • 隔离性（Isolation） —— 各个事务互相隔离，一个事务在提交前的修改，其他事务不可见（与事务隔离级别有关，可能会引发脏读、幻读、不可重复度的情况）
  • 持久性（durability）—— 事务提交之后，其所作的修改即可持久化的保存在数据库中

 
 
 
 

脏读

含义：某个事务读取到了其他事务未提交的修改、删除或新增；

 

例：

 

原始数据
在这里插入图片描述

 

设置事务A的会话事务隔离级别为 read uncommitted，并开启事务A，查询表
在这里插入图片描述

 

设置事务B的会话事务隔离级别为 read uncommitted，并开启事务B，之后修改id = 1 的 age 为 20

-- 事务B
set session transaction isolation level read uncommitted;
start transaction;
update test1 set age = 20 where id = 1;
select * from test1;

此时，事务B的查询结果为
在这里插入图片描述

此时，事务A的查询结果如下
在这里插入图片描述

两次对比（此时的事务B并未提交修改）

在这里插入图片描述

 

此时再在事务B中新增一条数据

insert into test1 values(9, '马云', 50);

事务A再次查询，查询到未提交的新增数据
在这里插入图片描述
 

此时事务B在删除一条数据

delete from test1 where id = 5;

事务A再次查询，未提交的删除也会被读取到
在这里插入图片描述

 
 
 
 

不可重复读

含义：某个事务上次和下次的读取，相同的数据 两次读取结果不同。即在某个事务中读取到了其他事务提交后的修改

 

例：

 

原始数据
在这里插入图片描述

 

设置会话事务隔离级别read committed，开启事务A，查询数据

在这里插入图片描述

 

设置会话事务隔离级别read committed，开启事务B，修改某条数据。并不提交

-- 事务B
set session transaction isolation level read committed;
start transaction;
update test1 set age = 20 where id = 1;
select * from test1;

此时从事务A中读取数据，并未读到数据修改
在这里插入图片描述

 

提交事务B，使修改生效

commit;

从事务A中进行查询，读取到了事务B提交修改的数据
在这里插入图片描述

 
 
 
 

幻读

含义：幻读与不可重复读类似，只是不可重复读是对数据修改而言，幻读则是对数据的新增而言，因为新增的数据是无法添加行级锁的，故二者存在区别

 

例：

 

原始数据
在这里插入图片描述

 

设置事务A会话事务隔离级别为 repeatable read，并开启事务A，读取数据
在这里插入图片描述

 

设置事务B会话事务隔离级别为 repeatable read，并开启事务B，修改数据，并不提交

set session transaction isolation level repeatable read;
start transaction;
update test1 set age = 20 where id = 1;
select * from test1;

事务A读取不到被修改的数据
在这里插入图片描述
 

事务B提交修改

commit;

事务A查询，依旧查询不到已经修改并提交后数据
在这里插入图片描述

 

此时提交事务A，再进行查询，查询到了事务B的修改
在这里插入图片描述
 

同上一系列操作，只是把修改数据改为新增数据

 

1、事务A开启事务并查询
在这里插入图片描述

 

2、事务B开启事务，并新增数据，不提交

set session transaction isolation level repeatable read;
start transaction;
insert into test1 values(9, '东方不败', 999);
select * from test1;

事务A查询不到新增
在这里插入图片描述

 

3、事务B提交修改，事务A查询，依旧查询不到新增
在这里插入图片描述
那是不是可以说明，repeatable read不存在幻读呢？ 其实不然，这里我们通过在事务A中插入一条与事务B所插入数据相同id（主键）的数据，看看是否可以执行

在这里插入图片描述
然而提示，id 9 已经存在。所以幻读还是存在的，只是repeatable read的MVCC对部分幻读有过滤功能

 
 
 
 

隔离级别与脏读、幻读、不可重复读的关系

隔离级别脏读不可重复度幻读
read uncommited存在存在存在
read commited不存在存在存在
repeatable read不存在不存在存在
serializable不存在不存在不存在

 
 

查询当前的隔离级别

show variables like ‘%isolation%’

在这里插入图片描述

 
 

修改隔离级别

set session/global transaction isolation level read uncommitted/read committed/repeatable read/serializable;

 
 

创建保存点

savepoint identifier;

 
 

删除保存点

release savepoint identifier;

 
 

事务回滚到保存点

rollback to identifier;

 
 
 
 
 
 
 
 

存储过程

功能：是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

 

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行

 

语法：
1、修改结束符（默认为分号，但是分号常表示将SQL语句传递至服务器，所以需要修改结束符）

将$$替换;作为结束符，分号即表示普通字符
delimiter $$
– 或者
delimiter //
– 之后使用
select * from demp $$

 

2、声明存储过程

create procedure 存储过程名(in 参数名 参数类型)

 

3、存储过程的作用域标识符

begin … end

 

4、变量定义

declare 变量名 变量类型;

 

5、变量赋值

set @变量名=值

 

6、创建mysql存储过程

create procedure 存储过程名（参数）

 

7、存储过程体

create function 郭村函数名（参数）

 

优点：

  • 存储过程可封装，并隐藏复杂的商业逻辑
  • 存储过程可以回传值，并接受参数
  • 存储过程无法使用select执行来运行
  • 存储过程可以用在数据检验，强制实行商业逻辑等

 

缺点：

  • 存储过程往往定制化于数据库，难以跨不同的数据库
  • 存储过程的性能调教，受限于各个数据库

 

简例：

-- 定义
-- 如果存储过程中只有一条SQL语句，则可省略begin...end
create procedure get_info()
select * from student;

-- 调用
call get_info();

 

标准过程：

-- 修改结束符，以$$为结束符
delimiter $$;

-- 定义存储过程
create procedure get_info()
-- 开始
begin
-- 存储过程要执行的东西
select * from table1;
select * from table2;
select * from table3;
-- 结束时，要以修改后的结束符($$)作为结尾
end$$


--调用
call get_info()$$

 
 
 
 

查询procedure信息

语法

show procedure status like ‘%存储过程名%’

在这里插入图片描述

 
 
 
 

查询procedure创建语句

show procedure ‘存储过程名’

在这里插入图片描述

 
 
 
 

删除procedure

drop procedure 存储过程名;

在这里插入图片描述

 
 
 
 

可传参的procedure

例：

delimiter $$
create procedure foo(in uid int, in new_name varchar(32))
begin
select * from student;
update student set name=new_name where id=uid;
select * from student; 
end$$

在这里插入图片描述

 
 
 
 
 
 
 
 

MySQL函数

 
 
 
 

concat()

用途：合并两个或以上的字符串

 

用法：concat( str/field, str/field, …)

 

在这里插入图片描述

 
 
 
 

order by的部分排序

用途：仅对某些数据进行排序

 

用法：order by field in(‘value1’, ‘value2’, …) —— 仅对 value1、value2、…等数据排序，其他数据不重新排序

 
 
 
 

now()

功能：用于生成当前时间，会自动匹配该列的时间格式

 

在这里插入图片描述

 
 
 
 

rand()

功能：生成 0 ~ 1之间的随机数

 
 
 
 

聚合函数

函数作用
AVG()取均值
BIT_AND()按位与
BIT_OR()按位或
BIT_XOR()按位异或
COUNT()计数
COUNT( DISTINCT )去重后计数
GROUP_CONCAT()连接字符串
JSON_ARRAY()将结果作为单个JSON数组返回
JSON_OBJECT将结果作为单个JSON对象返回
MAX()取最大值
MIN()取最小值
STD()求标准差
SUM()求和
VAR_POP()求标准差
VAR_SAMP()求方差

 
 
 
 

日期函数

函数功能
str_to_date(str, format)字符串转日期
date_format(str, format)字符串转日期
unix_timestamp(日期)日期转时间戳
from_unixtime(时间戳)时间抽转日期

 

如：

  • str_to_date(‘1980-12-31’, ‘%Y-%m-%d’);
  • date_format(‘1980-12-31’, ‘%Y-%m-%d’);
  • unix_timestamp(now())
  • from_unixtime(1602989057)

 
 
 
 

常用内置函数

函数功能
abs()取绝对值
floor()向下取整
ceil()向上取整
mod(x, y)取 x/y 的模
rand()取 0~1 随机数
round(x, y)对 x 以四舍五入的方式取到 y 位小数。若y为负数则向小数点前进行四舍五入
TRUNCATE(x, y)类似 round()，只是以舍尾法进行

 
 
 
 

日期计算类函数

函数功能
CURDATE()返回当前日期
CURTIME()当前时间
NOW()当前日期和时间
YEAR(日期)年份
MONTH(日期)年份
DAY(日期)年份
WEEK(日期)一年中的第几周
HOUR(日期)小时
MINUTE(日期)分
SECOND(日期)秒
MONTHNAME(日期)月份英文名的全称
DATE_ADD(日期，时间间隔)在日期上加一个时间间隔的结果日期
DATEDIFF(时间1， 时间2)返回两个时间之间的天数，时间1-时间2

如：

select datediff('1980-12-12', '2020-12-12')

select datediff(str_to_date('1980-12-12', '%Y-%m-%d'), str_to_date('2020-12-12', '%Y-%m-%d'));

在这里插入图片描述

 
 
 
 

字符串函数

函数功能
CONCAT(str1, str2, …)将多个字符串拼接为一个
INSERT(str1, x, y, str2)将str1的位置位于x，长度为y的内容替换为str2
LOWER(str) / UPPER(str)切换大小写
LEFT(str, x) / RIGHT(str, x)取str的左/右的x长子串
LPAD(str, n, pad) / RPAD(str, n, pad)用字符串pad对str的左/右进行填充，填充至n个字符长度
LTRIM(str) / RTRIM(str) / TRIM(str)去除字符串 左/右/两侧 的空格
REPEAT(str, x)将str重复x次
REPLACE(str1, a, str2)用str2来替换str1中所有的a
STRCMP(str1, str2)比较str1和str2，返回 1 —— str1 包含 str2，0 —— 匹配成功，-1 —— 其他情况
SUBSTRING(str, x, y)从str中取x开始y个长度的子串

 
 
 
 

其他函数

函数作用
DATABASE()返回数据库名
VERSION()数据库版本
USER()登录用户
INET_ATON(IP)返回IP的数字表示
INTE_NTOA(IP的数值表示)将IP的数值转回IP
PASSWORD(str)返回str的加密，mysql8已经弃用
MD5(str)返回str的MD5值

 
 
 
 
 
 
 
 

mycli

用途：具有关键字高亮、自动填充、多行等功能的增强版 cmd 中的 mysql

 

安装：pip3 install mycli

 

用法：mycli -uroot -h192.168.1.109

在这里插入图片描述

通过快捷键 F3 开启多行模式
在这里插入图片描述
 
 
 
 

删除mycli的历史记录

$ rm -rf .mycli-history

 
 
 
 
 
 
 
 

数据操作

 
 
 
 

增（insert into）

用途：向表中插入数据

 

用法：insert into table(column1, column2, …) values(value1, value2, …)

 

注意：
1、若设置主键自增，则主键可以显示的传值，也可以不传值；
2、若给自增主键传一个跳跃性的值，则后续的值自动在最大的值后进行递增
3、可以同时插入多行，插入多行的时候 … values 后不加括号，每一行为一个元祖，多行之间以逗号分隔

inset into 表名(列1，列2) values (列1值1，列2值1), (列1值2，列2值2);

在这里插入图片描述

 
 
 
 

改 （update set）

用途：用于修改表中的值

 

用法：update 表 set 字段a=值a where 筛选条件;

 
 
 
 

删 （delete from、truncate）

用途：用于删除表中数据

 

用法：delete from 表 where 条件;

 

注意：

1、若直接使用 delete from 表; 则代表清空表中所有数据

2、可以使用 truncate 表; 来清空表格

 

二者区别：
1、delete from 表; 逐行删除，并且会产生rollback，效率低。但是可以使用rollback回滚数据

2、 truncate 表; 直接清空表格，不会产生rollback，效率高。但不能rollback

 
 
 
 

复制表

 
 

复制表结构

功能：

 

语句：

create table 表名1 like 表名2;

 

表结构创建成功
在这里插入图片描述

但无数据

在这里插入图片描述

 
 

复制表数据

功能：将表2数据全部复制到表1，但不建议使用， 原表中字段的属性都不会被复制过来（通过 desc 表名 来查看表的属性）

 

语句：

create table 表名1 select * from 表名2;

 

表中字段属性没有复制成功
在这里插入图片描述

表中数据复制成功
在这里插入图片描述

 
 

复制表结构并插入数据

功能：在保留原表字段属性的同时，将数据同时复制过来

 

语句：

-- 复制表结构
create table 表2 like 表1;

-- 复制数据
insert into 表2 select * from 表1;

 
 
 
 
 
 
 
 

数据类型及字符编码

 
 
 
 

字符编码

 
 
 
 

字符编码

注意：mysql中，数据库、表及字符型字段均具有独自的编码类型。

1、在修改表的字符编码时，要注意同时修改字符型字段的编码类型；

2、要保持字段、表及数据库的编码类型一致

 

查看 mysql 的默认编码类型

show variables like '%character%';

 

在这里插入图片描述

 

查看某个数据库的编码类型

show create database 数据库名;

在这里插入图片描述

 
 

修改表字符编码

alter table 表名 charset=utf-8;

 
 

修改字段字符编码

注意：修改字段的编码类型时，使用 charset utf-8 没有等号（非 charset=utf-8） 与 改表不同

alter table 表名 modify 字段名 字段类型 charset utf-8;

 
 
 
 

校对集

1、查看校对集

show character set;

在这里插入图片描述

 

字段含义：

  • Charset —— 字符集（各种编码类型，如utf-8，ascii，gbk，big5等）
  • Description —— 字符集的描述信息
  • Default collation —— 对字符集的详细描述，其中最后的字母为_ci则为大小写不敏感，为_cs则为大小写敏感
  • Maxlen —— 字符集中每个字符允许的最大字节数

 
 
 
 

数据类型

 
 

整数类型

类型字节有符号范围无符号范围
TINYINT1-128 ~ 1270 ~ 255
SMALLINT2-28 ~ 28-10 ~ 216-1
MEDIUMINT3-212 ~ 212-10 ~ 224-1
INT、INTEGER4-216 ~ 216-10 ~ 232-1
BIGINT8-232 ~ 2320 ~ 264-1

 
 

小数类型

类型字节最小值最大值
FLOAT4±1.17549E-38±3.402823E+38
DOUBLE8±2.2250738E-308±1.79769313E+308

 
 

定点数类型

类型字节描述
DEC(M, D)M+2取值范围与DOBLE相同，具体范围由M和D决定
DECIMAL(M, D)M+2同上

M —— 支持的总长度
D —— 小数点后面的位数

 

用法：

float(10, 2)
double(10, 2)
decimal(10, 2)

 
 

位类型

类型字节最小值最大值
BIT(M)1 ~ 8BIT(1)BIT(64)

 
 

字符串类型

类型字节描述
CHAR(M)MM为 0 ~ 255 之间的整数
VARCHAR(M)M为 0 ~ 65535 之间的整数，值的长度+1个字节
TINYBLOB允许长度 0 ~ 255 字节，值的长度+1个字节
BLOB允许长度 0 ~ 65535 字节，值的长度+2个字节
MEDIUMBLOB允许长度 0 ~ 167772150 字节，值的长度+4个字节
LONGBLOB允许长度 0 ~ 4294967295 字节，值的长度+4个字节
TINYTEXT允许长度 0 ~ 255 字节，值的长度+2个字节
TEXT允许长度 0 ~ 65535 字节，值的长度+2个字节
MEDIUMTEXT允许长度 0 ~ 167772150 字节，值的长度+3个字节
LONGTEXT允许长度 0 ~ 4294967295 字节，值的长度+4个字节
VARBINARY(M)允许长度 0 ~ M 个字节的变长字节字符串，值的长度+1个字节
BINARY(M)M允许长度 0 ~ M 个字节的定长字节字符串

CHAR(M) 和 VARCHAR(M) 的M是指字符长度，中英文字符相同（MySQL引擎自动处理不相同处）

 

CAHR和VARCHAR的区别：
1、CHAR(M) —— Size 固定为 5 Bytes
VARCHAR(M) —— Size 变长，大小为内容+1 Bytes，多的一个用来记录长度

2、VARCHAR会删除最后的空格，不显示空格，但是会把长度记录下来。由此可以节省空格所占的空间

3、CHAR浪费空间，节省时间。VARCHAR节省空间，浪费时间。但二者速度差距不大，一般使用VARCHAR即可。

 

如：有下表
在这里插入图片描述

现插入输入如下
在这里插入图片描述
当英文字符长度超过3个时，插入失败
在这里插入图片描述
中英文混合长度超过3个时，同样失败
在这里插入图片描述

 
 

枚举

功能：提供一个选择集，该字段的值仅允许从创建字段时设置的枚举项中进行选择，不匹配则抛出异常，值仅可为枚举选项的其中一个。

 

语法：enum(‘选项1’, ‘选项2’, ‘选项3’)

 

在这里插入图片描述
在这里插入图片描述

 
 

集合

功能：

 

语法：

1、创建字段语法：set(‘值1’, ‘值2’, ‘值3’)

2、插值语法：values(‘值1, 值2, 值3’)

 
 

时间类型

类型字节最小值最大值
DATE41000-01-019999-12-31
DATETIME81000-01-01 00:00:009999-12-31 23:59:59
TIMESTAP4197001010800012038年的某个时刻
TIME3-838:59:59838:59:59
YEAR119012155

 

-- 时间列为 datetime 数据类型
insert into 表名(时间列) values('2000-01-01 12:00:00')
insert into 表名(时间列) values(now())

时间分隔可以用 “-” 或者 “/”

在这里插入图片描述

 
 

布尔类型

True、False或其他可以布尔化的值，若以数值传入则最大接受 127，因为布尔类型的字段以TINYINT存储

 
 
 
 
 
 
 
 

字段的常见属性

属性含义用法
PRIMARY KEY主键可以设置组合主键（primary key(id, sid)），但是建议不要这么做
AUTO_INCREMENT自增若此字段不给值，则从上一行增加1作为此行值，默认从1开始
NOT NULL非空约束
DEFAULT默认值若某行不给此值，则使用default的值， default 123
UNIQUE唯一约束
COMMENT注释用来给开发者查看，用于说明字段
UNSIGNED无符号型用于对数值型字段进行设置

 
 
 
 
 
 
 
 

MySQL运算符

运算符作用
=、>、<、!=、<>、<=、>=常规运算符
<=>比较是否为空，是返回1，否则返回0。若用 = 比较，则结果为 NULL
BETWEEN指定范围
IN指定集合
IS (NOT) NULL判断是否为NULL
LIKE通配符匹配，% —— 任意多个任意字符，_ —— 一个任意字符
REGEXP 或 RLIKE正则匹配

 

正则表达式的使用

select * from emp
where hiredate regexp '1987.*';

在这里插入图片描述

 
 
 
 
 
 
 
 

注意点

 
 
 
 

where 和 having 的区别

相同点：都用来进行条件筛选

 

不同点：
1、where 执行顺序在 group by 之前，having执行顺序在group by之后

 

2、having只能对 select 筛选出的字段进行条件筛选，若 select 中没有选择出字段，则having无法以此字段作为筛选条件

 

3、where 无法使用 select 选择出的字段的别名进行筛选，having 可以

如：
1、where 以字段别名进行筛选 —— 失败
在这里插入图片描述

2、having以字段别名进行筛选 —— 成功
在这里插入图片描述

 

4、having后可以跟聚合函数，where不可以。当having后跟聚合函数的时候，被聚合的列可以不在select中

在这里插入图片描述

 
 
 
 

其他语句

 
 

order by

升序 —— asc
降序 —— desc

 
 

limit

limit n —— 仅选出前n项
limit m, n —— 选出行数为（m, n] 的数据
limit n offset m —— 跳过前m个，从第m+1个开始，向下选出n个

 
 
 
 
 
 
 
 

经典实例

 
 
 
 

实例一、求各地区最高分的学生的详细信息

基础数据：

1、表1 —— 学生信息表，不包含成绩
在这里插入图片描述

2、表2 —— 学生成绩表，仅有ID与学生信息对应
在这里插入图片描述

 
 

需求：按城市分组，求出每个城市中数学最高的那个学生的所有详细信息及成绩

 
 

思路：
1、创建具有成绩的学生详细信息视图

2、求此视图与按城市分组的最高分的交集，交集以城市和数学最高分为条件

 
 

答案：

-- 创建具有成绩信息的学生信息视图
create or replace view V_EXAM as select t1.*, t2.math, t2.english from student t1 inner join score t2 on t1.id = t2.id;

select v1.* from V_EXAM v1 inner join ( select city, max(math) math from V_EXAM group by city ) t1 on v1.city = t1.city and v1.math = t1.math;

 
 
 
 
 
 
 
 

pymysql

使用库：pymysql

 

使用过程：

 

1、连接数据库

import pymysql

# 连接mysql
db = pymysql.connect(host="192.168.1.106", port=3306, charset="utf8", user="用户", password="密码", database="mysql_python")

 

2、创建游标

# 创建游标
cur = db.cursor()

 

3、执行SQL语句

# 执行sql语句
cur.execute("select * from test1;")

 

4、获取执行结果

# 获取执行结果
res = cur.fetchall()
print(res)

 

5、关闭数据库连接

cur.close()
db.commit()
db.close()

 
 
 
 
 
 
 
 

sql注入

原理：通过传入注释符，修改代码中的sql语句来达到侵入的目的

 

需求：

1、sql语句在代码中的传参需要以字符串模板的形式进行，如

sql = “select * from table1 where name=’%s’ and password = ‘%s’” % (name, password)

2、在传参时，用户名后传注释符，如

name = zhangsan’#
原因：后面的引号表示字符串结束，#表示注释语句。则效果为修改sql语句为
“select * from table1 where name=‘zhangsan’# and password =’%s’”

 

解决：

sql的传参不以模板字符串的形式进行，而是通过库的借口来传，如

sql = “select * from table1 where name=’%s’ and password = ‘%s’”
cur.execute(sql, (name, password))

 
 
 
 
 
 
 
 

数据库的备份与恢复

 
 
 
 

备份

备份数据库

mysqldump -hlocalhost -uroot -p <数据库名> <输出的sql文件名>

 
 

如：

mysqldump -hlocalhost -uroot -p mysql_python > \home\alun\mysql_dump\home_mysql_python.sql

在这里插入图片描述

 
 

文件内容如下：

在这里插入图片描述

 
 
 
 

恢复

mysql -hlocalhost -uroot -p mysql中要恢复的数据库名 < 备份的.sql文件

 
 

要恢复的数据库必须已经创建，若要恢复的数据库不存在，则报错
在这里插入图片描述

 
 

正常情况如下，无任何提示
在这里插入图片描述

可以看到表已恢复成功
在这里插入图片描述

 
 
 
 
 
 
 
 

空间数据导入

https://my.oschina.net/u/4599572/blog/4473542

alun550
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql中varchar长度设置方法
01-19
如果某一项中设置的是varchar(50) 那么对英文当然是50 那么对中文呢 utf-8的中文占3个字节 那么,这个varchar(50)是不是只能存16个汉字了? mysql varchar(50) 不管中文 还是英文 都是存50个的 MySQL5的文档，其中对varchar字段类型这样描述：varchar(m) 变长字符串。M 表示最大列长度。M的范围是0到65,535。(VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，最大有效长度是65,532字节）。 为何会这般变换？真是感觉MySQL的手册做的太不友好了，因为你要仔细的继续往下读才会发现这段描述：MyS
centos8查看mysql状态，开启/停止mysql
文鑫
11-26 5851
1，查看mysql状态 systemctl status mysqld.service 2，启动mysql systemctl start mysqld.service 3，停止mysql systemctl stop mysqld.service 4，重启mysql systemctl restart mysqld.service
Centos8安装Mysql
最新发布
d1451545368的博客
06-05 1233
Mysql命令目录MySql数据目录在服务器上安装过MySql之后，我们就有条件去研究MySQL深层次内容，运维相关，对MySql的理解也会更加深入。
详细：centos 查看mysql数据库命令
qq_40559632的博客
09-16 2831
centos 查看mysql数据库命令 使用MySQL时，需要了解当前数据库的情况，例如当前的数据库大小、字符集、用户等等。下面总结了一些查看数据库相关信息的命令 1：查看显示所有数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | INVOICE ...
8.0.15 MySQL 查看用户_CentOS 7重装mysql8.0.15
weixin_33393017的博客
02-06 220
查看是否安装有mysqlrpm -qa | grep mysql下载MySQL yum源wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm安装yum源yum localinstall mysql80-community-release-el7-1.noarch.rpm更新yum源yum clean al...
Mysql8 操作用户管理及注意事项
qwer1154928613的博客
01-03 142
1、mysql创建用户的时候，如果不指定Host，默认为%。%表示所有的ip都可以登录。
mysql8 centos离线安装
05-05
在Linux环境中，特别是CentOS系统上，MySQL 8.0的离线安装是一个常见的需求，尤其是在没有网络连接或者网络环境不稳定的情况下。以下是如何利用提供的压缩包文件进行离线安装MySQL 8.0的详细步骤： 首先，我们看到...
Centos8 mysql8.0.29安装包
07-11
Centos8 mysql8.0.29安装包
Centos7安装mysql8
05-05
Centos7安装mysql8
centos 安装mysql8 亲自测试成功
03-25
centos 安装mysql8 亲自测试成功
Centos7 yum安装MySQL8
02-28
本文档是MySQL官方文档，内容是如何在Centos7环境下用yum工具安装MySQL8。
【MySQL】mysql8的权限管理
王富贵的博客
03-30 2385
权限管理1. 查看用户2. 创建用户3.授权用户4.设置加密规则与更改用户密码5.撤销用户权限：6.删除用户7.刷新权限 1. 查看用户 select * from mysql.user; 进入 mysql数据库的user表，查看有什么用户。user 用户名；host 可登录的ip地址，%为不指定；plugin 字段为密码加密方式。mysql8较旧版更新了密码加密方式。旧版本mysql_native_password，新版本caching_sha2_password(默认)。 user表host，user
MySQL查看所有用户
热门推荐
m0_47073109的博客
08-20 11万+
MySQL查看所有用户 列出所有用户 如下语句可以查询所有用户 SELECT user FROM 数据库的名字.user; 在这条语句中,我们从mysql数据库的用户表中查询用户的数据.在这里要注意数据库的名字,在执行的时候要注意. 注意:要执行上面的这个查询,必须以管理员的身份登录 如果需要登录MySQL数据库服务器,命令如下 >Mysql -u root -p >Enter password:你的密码 mysql>use mysql; Datase change
MySQL8用户密码管理
麦田
01-06 1347
密码字段存储在authentication_string。
修改hosts文件在本地调试微信JS-SDK
Song719662321的博客
12-23 1251
1、修改hosts文件 路径：c:\windows\system32\drivers\etc 2、打开测试公众号填写第一步修改的域名地址 JS接口安全域名 3、工程文件，vue.config.js devServer: { open: flase, disableHostCheck: true, //手机局域网 // host: "192.168.43.223", host: "example.com",//对应自己域名 .
CentOS8环境下配置MySQL
CheneyG的博客
11-13 2209
初来乍到，记录一下自己在Linux下安装Tomcat的心酸历程，其中存在的不足之处，欢迎大家多多指教。下面就开始我的安装了。。。 一、准备工作 CentOS 8 MySQL 8.0.27 二、安装MySQL 2.1：首先查看系统中是否已经安装过MySQL，如果安装过MySQL，需要先把已经安装的MySQL卸载掉。 执行： rpm -qa | grep mysql 我的机器上没有安装过，安装过的小伙伴请自行Google卸载 三、下载 下载MySQL：可以使用Yum...
CentOS8.4 MariaDB服务（MySQL数据库）
Al_1的博客
02-21 885
梳理mariadb的常用命令
centos7下mysql 用户管理和权限设置
weixin_42231507的博客
07-27 1万+
1.进入mysql命令行，输入root及密码 [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.22 MySQL Comm...
MySQL查看用户
Do-Program的专栏
01-17 2万+
环境：CentOS 6.5 64bit mysql-server版本：5.1.73 一、查看所有用户 在MySQL中有一个内置名为mysql的数据库，这个数据库中存储的是MySQL的一些数据，比如用户、权限信息、存储过程等。所以我们可以通过如下查询语句来查看所有用户。 mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''
CentOS7安装与配置MySQL8详解
本文档详细介绍了在 CentOS 7 系统上安装 MySQL 8.0 的过程以及一些基本的操作步骤。首先，通过编辑 `/etc/my.cnf` 配置文件来设定 MySQL 的关键参数，例如数据目录、临时文件目录、错误日志位置以及套接字路径。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值