MySQL数据库操作

SQL语句

1、SQL的背景

2、SQL的概念

SQL的分类

SQL的书写规范

MySQL数据库

1、MySQL数据库的编码

（1）utf8和utf8mb4的区别

（2）MySQL的字符集

（3）MySQL默认编码为 latin1 ，如何更改为utf8mb4

（4）查看数据库编码情况

show variables like "char%";

2、数据库操作

（1）创建数据库（create）

（2）删除数据库（drop）

表的操作

1、创建表格（create）

2、查询

3、插入

4、删除表格

数据类型

Text类型

Number类型

Date类型

常用的数据类型

案例：创建数据库db_ck, 再创建表t_hero ，将四大名著中的主要任务都插入这个表中。

关系数据库，都是遵循SQL语法进行数据查询和管理的。

SQL语句

1、SQL的背景

SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，深受计算机工业界和计算机用户的欢迎。1980年10月，经美国国家标准局（ANSI）的数据库委员会X3H2批准，将SQL作为关系数据库语言的美国标准，同年公布了标准SQL，此后不久，国际标准化组织（ISO）也作出了同样的决定。

1986年了,ISO提出SQL的一个标准，SQL86。

2、SQL的概念

SQL：结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。

使用SQL语句，程序员和数据库管理员可以完成如下的任务：

改变数据库的结构
更改系统的安全设置
增加用户对数据库或表的许可权限
在数据库中检索需要的信息
对数据库的信息进行更新

SQL的分类

根据SQL的功能，进行如下分类：

DDL（Data Definition Language）：数据定义语言，定义对数据库对象(库、表、列、索引)的操作。 CREATE、DROP、ALTER、RENAME、 TRUNCATE等。
DML（Data Manipulation Language）：数据操作语言，定义对数据库记录的操作。 INSERT、DELETE、UPDATE、SELECT等。
DQL（Data Query Language）：数据查询语言，SELECT将数据的查询单独说明。
DCL（Data Control Language）：数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别。 GRANT、REVOKE等。

SQL的书写规范

在数据库系统中，

SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写。
SQL语句可单行或多行书写，以“;”结尾。
关键词不能跨多行或简写。
用空格和缩进来提高语句的可读性。
子句通常位于独立行，便于编辑，提高可读性。

注释：

SQL标准：

        /**/。多行注释

        “-- ” 单行注释

MySQL注释：

        “#”

MySQL数据库

1、MySQL数据库的编码

MySQL的默认编码：Lain1编码。
早期MySQL为了兼容中文等字符：提供 utf8编码。
由于第四个字节被用于存储表情包，所以X现在的MySQL数据库建议使用真正的Unicode编码：utf8mb4 。
MySQL字符集包括字符集（CHARACTER/character）和校对规则（COLLATION/collation）两个概念。
各种编码支持的字符：

latin1：支持西欧字符、希腊字符等。
gbk：支持中文简体字符。
big5：支持中文繁体字符。
utf8：几乎支持世界所有国家的字符。
MySQL自带的数据库：

Information_schema: 主要存储了系统中的一些数据库对象信息：如用户表信息、列信息、权限信息、字符集信息、分区信息等。(数据字典表)
performance_schema：主要存储数据库服务器的性能参数
mysql：存储了系统的用户权限信息及帮助信息。
sys：5.7新增，之前版本需要手工导入。这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据
test：系统自动创建的测试数据库，任何用户都可以使用。

（1）utf8和utf8mb4的区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

既然utf8能够存下大部分中文汉字，那为什么还要使用utf8mb4呢？原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符，如表情等等(utf8的缺点)。

因此在8.0之后，建议大家使用utf8mb4这种编码。

（2）MySQL的字符集

MySQL字符序命名规则：以字符序对应的字符集名称开头，以国家名居中（或以general居中），以ci、cs或bin结尾。

其中，ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较。

character_set_client：MySQL客户机字符集。
character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。
character_set_database：数据库字符集。
character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。
character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。
character_set_server：MySQL服务实例字符集。
character_set_system：元数据(字段名、表名、数据库名等) 的字符集，默认值为utf8。

（3）MySQL默认编码为 latin1 ，如何更改为utf8mb4

方法：修改my.cnf配置文件，可修改MySQL默认的字符集，修改完毕重启MySQL

1.在[mysqld]下添加
default-character-set=utf8 #适合5.1及以前版本
（mysql 5.5及以后版本添加character-set-server=utf8）
init_connect = 'SET NAMES utf8'
2.在[client]下添加
default-character-set=utf8

（4）查看数据库编码情况

show variables like "char%";

2、数据库操作

（1）创建数据库（create）

# 创建数据库

CREATE DATABASE 数据库名称 ;

# 创建数据库，同时指定编码

## 使用 charset 简写指定字符集为utf8mb4。

create database db_name default charset="utf8mb4";

## 使用character set 全写指定字符集为utf8mb4。
create database school DEFAULT CHARACTER SET utf8mb4;

## 使用 collation 指定校对集为utf8mb4_general_ci。
create database school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;

查询数据库的详细信息：

# 查询当前数据库是哪个数据库
select database();
# 查看数据库版本
SELECT VERSION();
# 查看当前用户
SELECT USER();
# 查看所有用户
SELECT User,Host,Password FROM mysql.user;

# 查看创建的数据库
show create databsse 数据库名称;

（2）删除数据库（drop）

# 写法1

drop database 数据库名称;

# 写法2：判断如果数据库不存在就创建，如果存在就不创建。

drop database [if exists] db_chengke;

3、表的操作

数据表（table），是一种二维表格，类似于execel，用来存储真正的数据。

1、创建表格（create）

# 语法结构

create table [if not exists] t_name (
# 定义表结构
字段名称1 类型 [约束条件],
字段2 类型 [约束条件],
……
字段n 类型 [约束条件]
)

# 例如：

/**
   创建一个用户表
   用户的姓名
   用户的年龄
   用户的性别
   用户的地址
   用户的电话
**/

create table t_user (
id int,
name varchar(50),
age int,
gender char(5),
address varchar(255),
tel char(11)
)

2、查询表数据（select * from ）

# 查询表中的数据
select * from t_name;

show columns from t_name;

3、插入表数据（insert）

# 插入数据
insert into t_name values(1, "小明", 16, '男', "四川", "110");

4、删除表格（drop）

drop table [if exists] t_name;

数据类型

MySQL数据库主要分为三大类：

数值型
字符串类型
时间和日期类型

Text类型

Number类型

注意：这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED属性，那么范围将从 0 开始，而不是某个负数。

Date类型

常用的数据类型

数据库约束

为了保证数据库表的完整性、可靠性，存在如下六种数据库表的约束：

默认值约束
主键约束
非空约束
外键约束
唯一约束
检查约束【MySQL8之后，支持检查约束】

1、主键约束

主键从功能上看相当于非空且唯一，一个表中只允许有一个主键

主键是表中唯一确定一行数据的字段

①一般建议主机那采用“int类型”，即数字（但是如果电脑做集群就不好，解决问题可以根据集群电脑取模，保证唯一，但是效率不高；所以还有更好的解决办法：uuid是字符串）

②一般建议由数据库自身维护这个字段的值，不用用户插入数据，系统自身创建就不会重复

删除表的约束

自动增长和默认值

存储引擎

主键字段可以是单字段或者多字段的组合

当建立主键约束时，MySQL为主键创建对应的索引——主键索引，主键约束名总为PRIMARY（primary）

主键的值没意义，auto_increament只加在创建表时的主键，好处就是可以自增

name是关键字，最好写`name`

2、唯一约束——使用UNIQUE

作用：保证字段的值是唯一不重复，但是可以为空。特殊场景使用

规则：

唯一性约束条件去报所在的字段或者字段组合不出现重复值

唯一性约束条件的字段允许出现一个NULL

同一张表内可建多个组合而成

建唯一约束时，MySQL会为之建立对应的索引——唯一索引

如果不给唯一约束起名，该唯一约束默认与列名相同

3、默认值约束

给给定的字段设置默认值，有了默认值就不会默认为空

4、非空约束 ——not null,可以多个字段设置

不允许字段的值为空，有些字段必须有值（如密码等核心字段）

5、外键约束

6、检查约束

注意：检查约束在MySQL8之前不生效，也不报错

comment：表字段的描述，相当于可显示的注释

修改表的结构（alter）

在sql中，也动态提供修改表的机构

使用alter指令，实现对数据库对象的结构的调整和

modify：修改已存在列的类型或约束条件——注意存在值得情况下，修改类型不一定成功

ADD：动态添加列

DROP：删除列名

CHANGE：修改字段名称

RENAME：修改表名

复制表的结构

两种方法：

方法一：create table 会将约束复制过来

方法二：不会将约束复制过来，

数据库字典

数据库字典是专门的数据库

案例：创建数据库db_ck, 再创建表t_hero ，将四大名著中的主要任务都插入这个表中。

MySQL代码：root@localhost 8.0.40 [(none)]> create database db_ck default charset="utf8mb4";
root@localhost 8.0.40 [(none)]> show create database db_ck-> ;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database|
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| db_ck    | CREATE DATABASE `db_ck` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost 8.0.40 [(none)]> use db_ck
Database changed
root@localhost 8.0.40 [db_ck]> create table t_hero (-> id int,-> name varchar(50),-> works varchar(100)-> );
root@localhost 8.0.40 [db_ck]> insert into t_hero values(1,"宋江","《水浒传
》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(2,"武松","《水浒传
》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(3,"刘备","《三国演 义》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(4,"诸葛亮","《三国
演义》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(5,"唐僧","《西游记
》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(6,"孙悟空","《西游
记》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(7,"贾宝玉","《红楼
梦》");
root@localhost 8.0.40 [db_ck]> insert into t_hero values(8,"林黛玉","《红楼
梦》");root@localhost 8.0.40 [db_ck]> select * from t_hero;
+------+-----------+--------------------+
| id   | name      | works              |
+------+-----------+--------------------+
|    1 | 宋江      | 《水浒传》         |
|    2 | 武松      | 《水浒传》         |
|    3 | 刘备      | 《三国演义》       |
|    4 | 诸葛亮    | 《三国演义》       |
|    5 | 唐僧      | 《西游记》         |
|    6 | 孙悟空    | 《西游记》         |
|    7 | 贾宝玉    | 《红楼梦》         |
|    8 | 林黛玉    | 《红楼梦》         |
+------+-----------+--------------------+

mysql -h：

3.6 MySQL用户授权

3.6.1 密码策略

3.6.1 远程登录

注意：MySQL官方禁止root账号远程登录！所以在真实的生产环境下，不是必要情况可以不使用

【补充】数据库，表，数据等都是有包含关系的，所以如果没有进入数据库，可以使用mysal.use

其中，host相当于白名单，只允许host列表中的localhost主机登录

host的改法：

①%：相当于0.0.0.0，任何网络都可以访问；

②建立白名单机制：

host中不一定要%，且一定要带其他条件；

不要随便重启服务，容易出大问题；

开启远程登录，必须保证你登录的账号存在在即权限，一般是root；

登录MySQL，之后访问 mysql 数据库；

#进入内置的mysql数据库中。
root@localhost 8.0.40 [mysql]>use mysql#查询user表中的host和user字段。
#方式一
root@localhost 8.0.40 [mysql]>select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
#方式二
root@localhost 8.0.40 [mysql]> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+# 发现host指定的主机是localhost,则修改为%，或者需要的IP地址，或者进行动态匹配，其中where指定其他条件。
root@localhost 8.0.40 [mysql]> update user set host="192.168.1.10%" where user="abc";#此时修改成功，要让权限生效，必须重启服务（但是重启服务不好），如果不想重启服务，可以刷新权限。
root@localhost 8.0.40 [mysql]> flush privileges;