Mysql整理

最新推荐文章于 2024-07-11 18:40:48 发布

weixin_44574761

最新推荐文章于 2024-07-11 18:40:48 发布

阅读量584

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_44574761/article/details/123402274

版权

Mysql

特殊字符

问题叙述：留言的功能，结果客户端用户输入了一个emoji表情，直接导致接口报错了。

原因：数据库和表的字符编码都是用的utf8，mysql的utf8编码的一个字符最多3个字节，但是一个emoji表情为4个字节，所以utf8不支持存储emoji表情。
解决方法：将字符编码改成utf8mb4，utf8mb4最多能有4字节，不过，在mysql5.5.3或更高的版本才支持。
- 在mysql 的配置文件 my.cnf 或 my.ini 配置文件中修改如下：
  [client]
  default-character-set = utf8mb4
  [mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_general_ci
- 重启MySQL，然后使用以下命令查看编码，应该全部为utf8mb4，这是修改整个数据库的编码方式。
  SHOW VARIABLES WHERE Variable_name LIKE ‘character_set_%’ OR Variable_name LIKE ‘collation%’;ji
- 也可以单独修改某张表的编码方式：
  - alter table test_a convert to character set utf8mb4 collate utf8mb4_bin;
- 以及修改某个字段的编码方式：
  - ALTER TABLE test_a CHANGE a a VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

一、DDL（操作数据库）

1、Create创建

#创建数据库，判断不存在再创建。
- create database if not exists 数据库名称;
#创建数据库
- create database 数据库名称;

2、Retrieve查询

#查询所有数据库名称
- show databases；
#查询某个数据库的字符集
- show create database 数据库名称;

3、Update修改

#修改数据库的字符集
- alter database 数据库名称 character set 字符集名称

4、Delete删除

#删除数据库
- drop database 数据库名称;
#判断数据库是否存在，在删除
- drop database if exists 数据库名称;

5、使用数据库

#查询当前正在使用的数据库名称
- select database();
#使用数据库
- use 数据库名;

二、操作表

1、Create创建

create table 表名(
列名1 数据类型1,
列名n 数据类型n
);
注意:最后一列，不需要加逗号
- 数据类型
  - int ：整数类型
  - double ：小数类型
    #double(5,2)表示数一共有5位，且保留2位
  - 日期类型
    - date ：日期类型
      #只包含年月日，yyyy-MM-dd
    - datetime：日期类型
      #包含年月日时分秒 yyyy-MM-dd HH:mm:ss
    - timestamp：时间戳类型
      #包含年月日时分秒 yyyy-MM-dd HH:mm:ss
      #如果将来不给这个字段赋值，或赋值为null，则默认使用当前系统时间来自动赋值
  - varchar：字符串类型
    #varchar(20)表示最大20个字符的字符串
create table 表名 like 被复制的表名;
#复制表

2、Retrieve查询

show tables;
#查询某个数据库中所有表名称
desc 表名;
#查询表结构
show create table 表名;
#查看已创建表的结构

3、Update修改

alter table 表名 rename to 新的表名;
#修改表名
alter table 表名 character set 字符集名称;
#修改表的字符集
alter table 表名 add 列名数据类型;
#添加一列
#修改列名称类型
- alter table 表名 change 列名新列名新数据类型;
  #同时修改列名和数据类型
- alter table 表名 modify 列名新数据类型;
  #只修改数据类型
alter table 表名 drop 列名;
#删除列

4、Delete删除

drop table 表名;
#删除表
drop table if exists 表名;
#判断表是否存在，存在再删除

三、DML(增删改表中数据)

1、添加数据

insert into 表名(列名1,列名2,…,列名n) values(值1,值2,…,值n)
#①列名和值需要一一对应。
②如果表名后，不定义列名，则默认给所有列添加值。
insert into 表名 values(值1,值2,…,值n)
③除了数字类型，其他类型需要使用引号(单双都可以)

2、删除数据

delete from 表名 [where 条件];
#①如果不加条件，则删除表中所有记录
②如果要删除所有记录
truncate table 表名; #先删除表，然后再创建一张一样的表。

3、修改数据

update 表名 set 列名1=值1,列名2=值2,…, [where 条件];
#如果不加任何条件，则会将表中所有记录全部修改

四、DQL（数据查询语句）

SELECT

 字段列表

FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组之后的操作
ORDER BY
排序
LIMIT
分页限定

基础查询
- 多个字段的查询
  - SELECT * FROM 表名
    #查询表中所有列
  - SELECT 列1,列2,…,列n FROM 表名
- 去除重复
  - distinct关键字
    - eg:SELECT DISTINCT address FROM stu
- 计算列
  #一般可以使用四则运算计算一些列的值
  （一般只会进行数值型的计算）
  - eg：SELECT name,math+english FROM stu；
- 起别名
  - as关键字
    - eg：eg：SELECT name,math+english AS 总分 FROM stu；
条件查询
- where字句后跟条件
- 运算符
  - 、<、<=、>=、=、<>(不等号)
    - eg：SELECT * FROM stu WHERE age<>20；
      #查询年龄不等于20岁
  - between…and…
    #范围在…和…中
    - SELECT * FROM stu WHERE age BETWEEN 20 AND 30；
      #查询年龄大于等于20且小于等于30
  - in
    - eg：SELECT * FROM stu WHERE age IN(22,18,25)；
      #查询年龄22岁、18岁、25岁的信息
  - is null/is not null
    - eg：SELECT * FROM stu WHERE english IS NOT NULL;
      #查询英语成绩不为空
  - like：模糊查询
    - 占位符
      - _ ：下划线表示单个字符
        
        eg：SELECT * FROM stu WHERE name LIKE “_华”
        #查询姓名第二个字是华的人
      - % ：表任意多个字符
        
        eg：SELECT * FROM stu WHERE name LIKE ‘%德%’
        #查询姓名中包含德的人

1、排序查询

order by 排序字段1 排序方式1，排序字段2 排序方式2；
#如果有多个排序条件，则之前排序结果的条件值一样时，才会判断第二条件
- 排序方式
  - ASC：升序，默认的
  - DESC：降序

2、聚合函数

将一列数据作为一个整体，进行纵向的计算。
#聚合函数的计算会排除NULL值
使用ifnull()函数做判断
ifnull(english,0) 表示如果english列为空，则用0代替。
eg：select 聚合函数(列名) from 表名
- count：计算个数
  - 一般选择非空的列(主键)
  - count(id)
- max：计算最大值
- min：计算最小值
- sum：计算和
- avg：计算平均值

3、分组查询

group by 分组字段；
#分组之后查询的字段：分组字段、聚合函数
- eg：SELECT sex,AVG(math) FROM stu GROUP BY sex;
  #按照性别分组，分别查询男、女同学的平均分
- eg：SELECT sex,AVG(math) FROM stu WHERE math>70 GROUP BY sex;
  #按照性别分组，分别查询男、女同学平均分。要求：分数低于70分的人，不参与分组
- eg：SELECT sex,AVG(math),COUNT(id) FROM stu GROUP BY sex HAVING COUNT(id)>2;
  #按照性别分组，分别查询男、女同学平均分、人数。要求：分组之后，显示人数要大于2人的结果
- where和having的区别？
  - where在分组之前进行限定，如果不满足条件，则不参与分组。
  - having在分组之后进行限定，如果不满足结果。则不会被查询出来。

4、分页查询

limit 开始的索引，每页查询的条数；
#开始的缩印=（当前页码-1）*每页显示的条数
- eg:SELECT * FROM stu LIMIT 0,3；

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

1、主键约束：primary key
- 注意
  - 含义
    - 非空且唯一
  - 一张表只能有一个字段为主键
  - 主键就是表中记录的唯一标识
- 在创建表时，添加主键约束
  - eg：id int primary key
    #给id 添加主键约束
- 创建表后，添加主键约束
  - ALTER TABLE 表名 MODIFY 列名数据类型 PRIMARY KEY；
    #本质为修改表
- 删除主键
  - alter table 表名 drop primary key；
- 自动增长
  - 概念：如果某一列是数值类型，使用auto_ increment可以来完成值得自动增长
  - 在创建表时，添加主键约束，并且完成主键自增长
    - eg：id int primary key auto_increment
  - 删除自动增长
    - alter table 表名 modif id int；
      #主键修改不掉，自动增长能够修改。
  - 添加自动增长
    - alter table 表名 modify id int auto_increment；
      #本质为修改表
2、非空约束：not null
#值不能为空
- 创建表时，添加约束。
  - eg：name varchar(20) NOT NULL
    #name字段非空
- 创建表完成后，添加非空约束。
  - ALTER TABLE 表名 MODIFY 列名数据类型 NOT NULL
    #本质为修改表
3、唯一约束：unique
#值不能重复（NULL值可以重复）
- 创建表时，添加唯一约束
  - eg:phone_number VARCHER(20) UNIQUE
- 删除唯一约束
  - alter table 表名 drop index 列名
- 创建表后，添加唯一约束
  - ALTER TABLE 表名 MODIFY 列名数据类型 UNIQUE
    #本质为修改表
4、外键约束：foreign key
- 在创建表时，可以添加外键
  #让表与表产生关系，从而保证数据的正确性
  - create table 表名(
    …
    外键列
    constraint 外键名称 foreign key (外键列名) references 主表名称(主表列名称)

);

- 删除外键

	- alter table 表名 drop foreign key 外键名

- 创建表之后，添加外键

	- alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名称(主表列名称)

- 级联操作

	- 添加级联操作

		- alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名称(主表列名称) on update cascade on delete cascade;

	- 级联更新

		- on update cascade

	- 级联删除

		- on update cascade

数据库设计

1、多表之间的关系

一对一
- 一个人只有一个身份证，一个身份证只能对应一个人
  - 可以在任意一方添加唯一外键（用unique关键字指定）指向另一方的主键
一对多（多对一）
- 一个部门有多个员工，一个员工只能对应一个部门
  - 在多的一方建立外键，指向1的一方的主键
多对多
- 一个学生可以选择多课程，一个课程可以被很多学生选择
  - 需要借住第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键、

2、数据库设计的范式

概念：设计数据库时，要遵循后边的范式要求，必须先遵循前边所有范式要求
第一范式（1NF）
- 每一列都是不可分割的原子数据项
第二范式（2NF）
- 在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
- 函数依赖：A–>B，如果通过A属性（属性组）的值，可以唯一确定唯一B属性的值。则称B依赖于A
- 完全函数依赖：A–>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。
- 部分函数依赖：A–>B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中某一值即可。
- 传递函数依赖：A–>B，B–>C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A。
- 码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。
- 主属性：码属性组中的所有属性
- 非主属性：除码属性组的属性
第三范式（3NF）
- 在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF的基础上消除传递依赖）

数据库的备份和还原

命令行

备份
- mysqldump -u用户名 -p密码数据库名称 > 保存的路径
还原
- 1、登录数据库。2、创建数据库。3、使用数据库。4、执行文件。source 文件路径

1、多表查询

笛卡尔积

为两个集合X和Y的笛卡尔积，表示X*Y。假设集合A={a,b}，集合B={0,1,2}，则两个结合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

分类

内连接查询
- 隐式内连接：使用where条件消除无用数据
  - SELECT
    t1.name,t1.gender,t2.name
    FROM
    emp t1,dept t2
    WHERE
    t1.‘dept_id’ = t2.‘id’;
- 显式内连接
  - 语法
    - selec 字段列表 from 表名1 inner join 表名2 on 条件;
- 注意事项
  - 从哪些表中查询数据
  - 条件是什么
  - 查询哪些字段
外连接查询
- 左外连接
  - 语法
    - select 字段列表 from 表1 left outer join 表2 on 条件
    - 查询的是左表所有数据以及其交集部分。
- 右外连接
  - 语法
    - select 字段列表 from 表1 right outer join 表2 on 条件
    - 查询的是右表所有数据以及其交集部分。
子查询
- 概念
  - 查询中嵌套查询，称嵌套查询为子查询
- 不同种情况
  - 子查询的结果是单行单列的
    - 子查询可以作为条件，使用运算符去判断
      - 、<、<=、>=
    - eg：select * from emp where emp.salary < (select avg(salary) from emp);
      #查询员工工资小于平均工资的人
  - 子查询的结果是多行单列的
    - 子查询可以作为条件，使用运算符in来判断
    - eg:select * from emp where dept_id in(select id from dept where name = ‘财务部’ or name=‘市场部’)
      #查询’财务部’和’市场部’所有的员工信息
  - 子查询的结果是多行多列的
    - 子查询可以作为一张虚拟表参与查询
    - eg:select * from dept t1,(select * from emp where emp.‘join_date’>‘2011-11-11’) t2 where ti.id = t2.dept_id;
      #查询员工入职日期是2011-11-11日之后的员工信息和部门信息

2、事务

基本介绍

概念
- 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
操作
- 开启事务
  - start transaction;
- 回滚
  - rollback；
- 提交
  - commit;
- MySQL数据中心事务默认自动提交
  - 一条DML（增删改）语句会自动提交一次事务
  - 事务提交的两种方式
    - 自动提交
      - mysql就是自动提交
    - 手动提交
      - 需要先开启事务，再提交
  - 修改事务的默认提交方式
    - 查看事务的默认提交方式：select @@autocommit;
      #1代表自动提交，0代表手动提交
    - 修改默认提交方式：set @@autocommit=0;

四大特征

原子性
- 是不可分割的最小操作单位，要么同时成果，要么同时失败。
持久性
- 当事务提交或回滚后，数据库会持久化的保存数据。
隔离性
- 多个事务之间，相互独立。
一致性
- 事务操作前后，数据总量不变。

隔离级别（了解）

概念
- 多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同隔离级别就可以解决这些问题。
存在问题
- 脏读
  - 一个事务读取到另一个事务中没有提交的数据
- 不可重复读（虚读）
  - 在同一个事务中，两次读取到的数据不一样
- 幻读
  - 一个事务操作（DML）数据表中所有记录，请一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别
#隔离界别从小到大安全性越来越高
但是效率越来越低
- read uncommitted （读未提交）
  - 可能会产生：脏读、不可重复读、幻读
- read committed （读已提交）–Oracle默认
  - 可能会产生：不可重复读、幻读
- repeatable read （可重复读）–MySQL默认
  - 可能会产生：幻读
- serializable （串行化）
  - 可以解决所有的问题
查询隔离级别
- select @@tx_isolation;
修改隔离级别
- set global transaction isolation level 级别字符串;

3、DCL（管理用户，授权）

DDL：操作数据库和表

DML：增删改查中数据

DQL：查询表中数据

DCL：管理用户，授权

管理用户
- 添加用户
  - create user ‘用户名’@‘主机名’ identified by ''密码;
- 删除用户
  - drop user ‘用户名’@‘主机名’;
- 修改用户密码
  - update user set password = password(‘新密码’) where user = ‘用户名’；
  - set password for ‘用户名’@‘主机名’ = password(‘新密码’);
  - mysql中忘记root密码
    - 1、管理员权限cmd中输入net mysql
      #停止mysql 服务
    - 2、使用无验证方式启动mysql服务
      mysqld --skip-grant-tables
    - 3、打开新的cmd窗口，直接数据mysql命令，敲回车。就可以成功
    - 4、use mysql；
    - 5、update user set password = password(‘你的新密码’) where user = ‘root’；
    - 6、关闭两个窗口
    - 7、打开任务管理器，手动结束mysqld.exe的进程
    - 8、启动mysql服务
    - 9、使用新密码登录
- 查询用户
  - 1、切换到mysql数据库
    use mysql;
    2、查询user表
    select * from user;
  - 通配符：%表示可以在任意主机使用用户登录
授权
- 查询权限
  - show grants for ‘用户名’@‘主机名’;
- 授予权限
  - grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
  - #所有权限通配符：all
    所有表通配符： *
- 撤销权限
  - revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;