vip2-day22 MySQL 基础语句

tp404

已于 2022-11-30 18:22:21 修改

阅读量298

点赞数

分类专栏：学习笔记文章标签： mysql 数据库服务器

于 2022-11-28 14:42:30 首次发布

本文链接：https://blog.csdn.net/tpwd1314/article/details/128077233

版权

学习笔记专栏收录该内容

10 篇文章 0 订阅

订阅专栏

MySQL 8 新特性

数据字典：包含一个事务数据字典，用于储存有关数据库对象的信息。在之前的版本中，字典数据存储在元数据文件和非事务表中。
原子数据定义语句：支持原子数据定义语言(DDL)。
- DDL 语句包含动词 CREATE 和 DROP，在数据库中创建新表或删除表(CREAT TABLE 或 DROP TABLE)，为表加入索引等。
- DDL 包含许多原数据库目录中获得数据有关的保留字。
- DDL 也是动作查询的一部分。
安全和账户管理：通过以下功能增强数据库的安全性，并在账户管理中实现更高的 DBA 灵活性。
- 开始支持角色，角色可以看成是一些权限的集合，为用户赋予统一的角色，权限的修改直接通过角色来进行，无需为每个用户单独授权。管理员可以创建和删除角色。
- 允许账户具有双密码，从而在多服务器系统中无缝地执行分阶段密码更改。无需停机。
- 默认字符集从 latin1 更改为 utf8mb4。
- 增强 JSON 功能。

MySQL 要点解析

特殊库解析：

mysql：系统自带核心数据库。存储用户账号、权限、存储过程、事件定义、帮助信息、时区信息及日志等。
information_schema：保存 MySQL 服务器维护的其它数据库信息。包含表、视图、触发器、列、索引等描述性信息，称为元数据。
performance_schema：存储 MySQL 服务器的运行状态。用来监控 MySQL 服务的各类指标，如：执行的语句，语句耗时，内存占用等。
sys：通过视图的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

data 目录文件解析：

表结构：*.frm文件，使用二进制存储。
表数据和索引：
- 系统表空间：
  - 一般大小为 12M，会自动增容，文件名可自定义。
  - 5.6 版本之前的 myisam 引擎将表数据存储在系统表空间。
- 独立表空间：
  - 5.6 版本之后的 innodb 引擎将表数据存储在独立表空间。
  - 文件名与表名相同，后缀为.ibd。
- 临时表空间
- 通用表空间
  - auto.conf：系统启动时会在该文件中查找 UUID，并写入全局变量中，如果不存在，则会新生成生成 UUID ，并保存在其中。
SSL 登录认证： 8.0 版本新增，后缀为.pem。

表空间设置：/etc/my.cnf

[server] 
# 0：使用系统表空间
# 1：使用独立表空间
innodb_file_per_table=0

UUID：如果数据库复制时发生故障，从会使用 UUID 来辨别 master 日志。

SQL 语句概述

结构化查询语句(Structured Query Language)，简称 SQL，是一种特殊目的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

常用基础语句

绝对路径：command [库名].[表名]
相对路径：use [库名]; command [表名];
,：用于分隔多个操作对象。
;：放在一条 sql 语句的末尾，表示语句结束。

查询

show

查询现有库：> show databases;
查询当前库的表：> show tables;
查看库文件存储路径：> show variables like 'datadir';
查询字符集：> show variables like 'character%';
查看表空间设置：> show variables like 'innodb_file_per_table';
查看现有表的创建信息：> show create table [表名] \G

select

功能：查询表数据。
格式：

# 查询当前库名
> select database();

# 查询当前版本号
> select version();

# 查询指定表内的数据
> select [键] from [表名];

示例：

# 查询指定表内的所有字段数据
> select * from hero;

# 查询指定表内的指定字段数据
> select name,skill from hero;

desc

查询表结构：> desc [表名];

explain

查询表结构：> explain [表名];

help

查看指定语句的帮助信息：> help [语句];

`\G`

可以代替;，表示一条语句结束。
查询结果以"行"的形式显示：> [查询语句] \G

增加

create

功能：创建表和库。
格式：

# 创建库
> create database [库名];

# 创建表
> create table [表名]([字段名] [数据类型]([约束]));

示例：

# 创建库
> create table students(name varchar(20));

# 创建表
> create table students1(name varchar(20), age int, id int);

insert

功能：插入表数据。
格式：

# 向指定表内插入数据
> insert into [表名] value([数据1],[数据2],[数据3]);

示例：

# 向指定表内插入一行数据
> insert into hero value(1,'张三丰',110,'太极');

# 向指定表内插入多行数据
> insert into hero value (2,'小龙女',22,'yunvsuxin'),(3,'杨过',19,'黯然销魂掌');

删除

drop

删除库：> drop database [库名];
删除表：> drop table [表名];
当要删除的库存在时删除：> drop database if exists [库名];

delete

功能：删除表中的数据。
格式：

# 删除指定表与指定字段匹配的数据
> delete from [表名] where [字段]=[值];

# 删除指定表指定字段为空的数据
> delete from [表名] where [字段] is null;

示例：

# 删除指定表与指定字段匹配的数据
> delete from hero where id=1;

# 删除指定表指定字段为空的数据
> delete from hero where skill is null;

更改

use

切换数据库：> use [库名];

alter

格式：

# 修改表名
> alter table [原表名] rename [新表名];

# 修改字段数据类型
> alter table [表名] modify [字段] [数据类型]([约束]);

# 修改字段名和数据类型
> alter table [表名] change [原字段名] [新字段名] [数据类型]([约束]);

# 在指定表的字段尾添加新字段
> alter table [表名] add [字段] [数据类型]([约束]);

# 在指定表的字段首添加新字段
> alter table [表名] add [字段] [数据类型]([约束]) first;

# 在指定表的指定字段后方添加新字段
> alter table [表名] add [字段] [数据类型]([约束]) after [位置字段];

# 删除指定表的指定字段
> alter table [表名] drop [字段];

解析：

change：对列进行重命名并更改字段的数据类型。需给定旧列名、新列名、数据类型。
modify：修改字段的数据类型，无需重命名字段。

示例：

# 修改表名
> alter table students1 rename zhanshi;

# 修改字段数据类型
> alter table zhanshi modify name int(20);

# 修改字段名和数据类型
> alter table zhanshi change name stname varchar(20);

# 在指定表的字段尾添加新字段
> alter table zhanshi add hp int;

# 在指定表的字段首添加新字段
> alter table zhanshi add mp int first;

# 在指定表的指定字段后方添加新字段
> alter table zhanshi add address varchar(30) after age;

# 删除指定表的指定字段
> alter table zhanshi drop id;

update

功能：更改表数据。
格式：

# 更改指定表指定字段的数据
> update [表名] set [更改字段]=[更改值] where [匹配字段]=[匹配值]

示例：

> update hero set skill='玉女素心剑法' where id=2;

命令行参数

`-e`

功能：在命令行执行 sql 语句。
格式：

$ mysql -e '[语句]' -u[用户名] -p[密码]

示例：

# 查看现有数据库
$ mysql -e 'show databases;' -uroot -pWahahaAyaya123!

# 查看数据库并将结果集导入文件
$ mysql -e 'show databases;' -uroot -pWahahaAyaya123! >> db.txt

`-A`

功能：禁止预读表信息，可以加快切换表的过程。
格式：

$ mysql -u[用户名] -p[密码] -A

示例：

$ mysql -uroot -pWahahaAyaya123! -A

保留字符失效

说明：

库或表之类的名字定义时需要避免使用保留字符。
使用单引号可以使保留字符失效，表明其只是个普通字符。
使用单引号使其失效的保留字符，在引用时也需要添加单引号，避免产生歧义。

示例：

> create database 'student-new';
> create tabls 'create';

实例：数据库迁移

实例适用条件：数据库数据较少时。
步骤：

停止数据库服务：$ service mysql.server stop
拷贝数据库：$ cp -a /usr/local/mysql /usr/local/mysql_01
查看目录权限：$ ll /usr/local/
目录属主非 mysql 则需要授权：$ chown mysql:mysql -R /usr/local/mysql_01
编辑配置文件：$ vim /etc/my.cnf

# mysql 安装根目录
basedir = /usr/local/mysql_01

# 数据库文件所在位置
datadir = /usr/local/mysql_01/data

初始化：$ mysqld --initialize --user=mysql --basedir=/usr/local/mysql_01 --datadir=/usr/local/mysql_01/data
启动服务：$ service mysql.server start
登录数据库：$ mysql -pWahahaAyaya123!
查看库文件存储路径：$ show variables like 'datadir'