MySql相关知识

一，mysql的元数据库

1.1，什么是元数据库？

记录MySQL自身数据的数据库

1.2，元数据库的分类:

information_schema:信息数据库，保存MySQL所维护其他数据库的信息，例如：数据库名，数据库表，表栏的数据类型与访问权限等；

MySQL：核心数据库，主要负责存储数据库的用户，权限设置，关键字等mysql自己需要使用的控制和管理信息；

performance_schema:用于mysql的监控数据的存放；

切换数据库:use 数据库名

use mysql

二，账户管理

2.1,创建账户

命令格式：create user 用户名 identified by ’密码‘

说明：indentified by 会将纯文本密码加密作为散列值存储

示例：create user testindentified by’123456‘

2.2，查看账户

命令格式：select host,user,password from user;

host列说明：localhost不会被解析成ip地址，直接通过unixsoket连接

127.0.0.1：会通过tcp/ip协议连接，并且只能在本机访问

：：1 ：兼容支持ipv6的，表示同ipv4127.0.0.1

%：匹配所有主机

localhost：localhost不会被解析成IP地址，直接通过UNIXsocket连接

2.3，删除账户

命令格式：drop user 用户名

示例：drop user test

一般不用，用时需要谨慎！

2.4，修改密码

命令格式：set password for 用户名=password（‘新密码’）

示例：set password for test = password（‘123456’）

2.5，刷新配置

命令格式：flush privileges

2.6，设置权限

命令格式：grant privileges on databasename.tablename to username@‘host’

privileges：指定select，update等权限，全部权限用all

databasename：指定数据库，所有数据库请使用*

tablename：指定数据表，所有表请使用*

username：需要赋权的用户名，@带的是host，表示赋权操作针对那些链接

使用示例：

··将bookshop数据库中的t_book表的select，update权限赋予test用户，并且不对ip地址限制
GRANT SELECT, UPDATE ON bookshop.`t_book` TO test@'%';

--一般用于赋予管理员最高权限，谨慎使用
grant all on *.* to dba@'localhost'

--以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建视图的权限
grant create view on testdb.* to developer@'192.168.0.%'; 

--以192.168.0开头的账户名deveoper用户针对testdb数据库赋予查看视图的权限
grant show   view on testdb.* to developer@'192.168.0.%'

--以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建索引的权限
grant index on testdb.* to developer@'192.168.0.%';

--以192.168.0开头的账户名deveoper用户针对testdb数据库赋予操作存储过程与函数的权限
grant create routine on testdb.* to developer@'192.168.0.%'

--以192.168.0开头的账户名deveoper用户针对testdb数据库赋予创建删除存储过程与函数的权限
grant alter  routine on testdb.* to developer@'192.168.0.%';

授权用户可以将拥有的权限再赋予其他用户

命令格式：grant privileges on databasename.tablename to username@'host' with grant option

示例：grant select on testdb.* to dba@localhost with grant option

说明：一般不用，建议与数据库管理员（DBA）统一管理

2.7，查看权限

show gants：查看当前用户（自己）权限

show grants for dba@localhost：查看其它mysql权限

8，撤销权限

命令格式：revoke privileges on databasename.tablename from username@'host'

示例：REVOKE UPDATE ON bookshop.t_book FROM test@'%';

三，MySQL引擎

什么是数据库引擎?

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据， 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能

查看数据引擎：

命令：show engines

Support字段说明：

默认的引擎DEFAULT

为YES表示可以使用

为NO表示不能使用

MyISAM引擎

MYISAM强调了快速读取操作

使用场景： 大量查询，很少修改的场景

存储限制：256TG

事务支持：不支持事务

memory：

所有的数据都保存在内存中，一旦服务器重启，所有Memory存储引擎的表数据会消失但是表结构会保存下来

使用场景：由于易失性，可以用于存储在分析中产生的中间表

存储限制：取决与RAM

事务支持：不支持事务

InnoDB：

后者修改快，支持事务

使用场景：一般事务性，均使用该引擎，用途最广，如果把握不准使用何种引擎，就使用该引擎

存储限制：64TG

事务支持：支持事务

ARCHIVE：

只允许插入和查询，不允许修改和删除，压缩存储，节约空间，可以实现高并发的插入，支持在自增id上建立索引

使用场景：在日志和数据采集的时候可以使用

特点：

Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%

不支持索引（自增id列除外）

支持insert，select操作，但不支持delete，update操作

数据库的命令

创建：

命令格式：create database 数据库名

create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;

默认的数据库编码集：utf8

collate表示校验规则为utf8_general_ci

查看：show databases

删除：drop database 数据库名

基本类型

四，建表与约束

建表：

CREATE TABLE 表名称(
        列名称1 数据类型 NOT NULL,
        列名称2 数据类型,
        列名称3 数据类型,
        unique(列名称1[,列名称2,...,列名称N])
    )

示例：

create table t_student
(
   sid                  int not null comment '学号',
   sname                varchar(60) not null comment '姓名',
   sex                  tinyint not null default 1 comment '性别：1男， 2女',
   age                  tinyint not null comment ' 年龄',
   icard                varchar(18) not null comment '身份证，唯一约束',
   primary key (sid),
   unique key AK_Key_2 (icard)
) comment '学生信息表';

约束：

主键约束PRIMARY KEY

增加主键：ALTER TABLE 表名 ADD PRIMARY KEY(主键名称)

删除主键：ALTER TABLE 表名 DROP PRIMARY KEY

非空约束NOT NULL COMMENT

 sid                  INT NOT NULL COMMENT '学号',

外键约束FOREIGN KEY

示例：

DROP TABLE IF EXISTS t_score;
CREATE TABLE t_score
(
   id                   INT NOT NULL COMMENT '记录流水号',
   sid                  INT NOT NULL COMMENT '学号',
   cid                  INT NOT NULL COMMENT '课程ID',
   score                FLOAT COMMENT '成绩',
   PRIMARY KEY (id),
   FOREIGN KEY (sid) REFERENCES t_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT,
   UNIQUE KEY AK_Key_2 (sid, cid)
);
说明： sid为本表的外键，关联t_student表中的的sid主键，ON DELETE RESTRICT ON UPDATE RESTRICT说明在本表有数据的情况下，主表的关联键不能删除或更新。

增加外键：ALTER TABLE 表名 ADD FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称)

删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 约束名

唯一约束UNIQUE KEY

表示：UNIQUE KEY 约束名 (字段)

创建唯一约束：ALTER TABLE 表名 ADD UNIQUE(列名称1[,列名称2,..])

示例：create unique index UserNameIndex on 't_user' ('username')

删除唯一约束：ALTER TABLE 表名 DROP INDEX 唯一约束缩影名称

默认值约束：default 

五，常用函数

5.1，字符串函数：

UPPER()

作用：转换成大写字符

示例：

SELECT UPPER('faafafa')

LOWER()

作用：转化成小写字符

示例：

SELECT LOWER('FEFEFF')

REPLACE()

作用：搜索并替换字符串中的子字符串

示例：

SELECT REPLACE('www.google.net','w','n')

SUBSTRING()

作用：从具有特定长度的位置开始的最一个子字符串

示例：

SELECT SUBSTRING('abcdefghijk', 1, 3)
SELECT SUBSTRING('abcdefghijk', 4);
SELECT SUBSTRING('abcdefghijk', -3);

TRIM()

作用：去除首尾空格

示例：

SELECT TRIM('    fdfdfdfd    ');

LENGTH()

作用：获取字符串长度

示例：

SELECT LENGTH('abcdef');

5.2，数字函数：

FLOOR()

作用：向下取整

示例：

SELECT FLOOR(123.8934);

CEIL()

作用：向上取整

示例：

SELECT CEIL(123.8934)

ROUND()

作用：四舍五入

示例：

SELECT ROUND(123.8934, 2);

5.3，聚合函数：

特点：常用group by一同使用，也可单独使用，如需求过滤，可以使用having子句

sum：求和

count：统计记录数

avg：求平均值

max：求最大值

min：求最小值

5.4，合并

合并的前提：

结果集列数个数相同

使用场景：在项目统计报表模块，用来合并数据

UNION

作用：将所有的查询结果放在一起，并去掉相同的记录

示例：

SELECT 'abc', 123
UNION 
SELECT 'def',456

SELECT 'abc', 123
UNION 
SELECT 'abc', 123

UNION  ALL

作用：将所有的查询结果合并在一起，不去掉相同的记录

示例：

SELECT 'abc', 123
UNION ALL
SELECT 'def', 456

SELECT 'abc', 123
UNION ALL
SELECT 'abc', 123

六，视图

概念及特点

视图是一种虚拟表，是从数据库中的一个或多个表中导出的表

数据库中存放视图的定义，而不会存放视图的数据，数据依然存放在原来的表中。

使用视图查询数据时，数据库会从原来的表中获取数据

（注意：此处并没有包括物化视图，目前mysql默认不支持物化视图）

作用

1）简化操作

2）增加数据的安全性

3）提高表的逻辑独立性

基本语法

CREATE VIEW 视图名 AS SELECT 语句

示例：

CREATE VIEW stu_score_statistics AS
 SELECT t1.sid, t1.sname, t1.ssex, t2.courses, t3.total total_score
  FROM t_student t1 
  LEFT JOIN (SELECT sid, COUNT(*) courses FROM t_score GROUP BY sid) t2 ON t1.sid=t2.sid
  LEFT JOIN (SELECT sid, SUM(score) total FROM t_score GROUP BY sid) t3 ON t1.sid=t3.sid

七，索引

概念

索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度

可以理解为书本的目录的作用

优点和缺点

优点：对于中型或大型表，恰当的使用索引可以显著提高查询性能

缺点：增加了索引维护的工作，使的插入，修改，删除操作变慢

分类

普通索引：基本索引，目的就是提高查询性能

唯一索引：除提高查询性能外，还可起到避免列值出现重复

主键索引：特殊的唯一索引，一个表只能有一个主键，不允许有空值

组合索引：由多列组合生成的索引，使用是需要注意索引顺序

全文索引：用于支持全文搜索（FULLTEXT）

索引维护

创建：

语法：CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])

示例：

CREATE INDEX sname_inx ON t_student(sname);

修改：

语法：ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])

示例：

ALTER TABLE t_student ADD INDEX birthday_inx(birthday);

删除：

语法：DROP INDEX 索引名 ON 表名

示例：DROP INDEX birthday_inx ON t_student;

常见错误代码

1075：有自增键，但不是未将其设置为主键

1142：操作被拒绝，一般是没有权限

1064：一般是存在语法错误，如关键字错误，缺少空格，sql脚本中sql语句后缺少;等原因

1048：列不能为空

1055：不在GROUP BY中

1265：保存数据的格式与定义是不同

1366：数据编码

1451：违反外键约束

关于数据库的一些题目参考博客：

https://blog.csdn.net/m0_74229802/article/details/136454770?spm=1001.2014.3001.5501

嘿嘿，数据库基础大概就这么多啦！