MySql教程

mysql

本文主要是介绍mysql,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

mysql

目录
  • 1. mysql数据库基础
    • 1.1数据库
      • 1.1.1常见的数据库
      • 1.1.2数据库结构模型
      • 1.1.3 数据库管理系统
      • 1.1.4常见的关系型数据库管理系统
      • 1.1.5 关系型数据库的常见组件
  • 2. SQL语句
    • 2.1 mysql安装
      • 2.1.1 mysql方式
      • 2.2.2mysql配置
      • 2.2.3 mysql的程序组成
      • 2.2.4 mysql工具使用
      • 2.2.5 服务器监听的两种socket地址
  • 3. mysql数据库操作
    • 3.1 DDL操作
      • 3.1.1 数据库操作
      • 3.1.2 表操作
      • 3.1.3 用户操作
      • 3.1.4 查看命令show
  • 4. MySQL数据类型

1. mysql数据库基础

1.1数据库

1.1.1常见的数据库

  • 关系型数据库:数据存放在硬盘当中
    字段:每一列的第一行就是字段
    记录:每一行代表一条记录

    • mysql(5.7之前都是开源,8.0开始商业化)
    • mariadb -->10.0
    • MSsql MicreSoft Sql
    • Oracle 商业软件
    • sqlserver
  • 非关系型数据库:数据存放在内存当中,变量,key=value

    • mongodb
    • redis
    • memcache
  • sqlite:数据存放在文件当中 sql

1.1.2数据库结构模型

  • 层次模型
  • 网状结构
  • 关系模型

1.1.3 数据库管理系统

数据库管理系统:DBMS DateBase Managment System mysql

关系:Relational,RDBMS
Relationship 关系
RDBMS 关系数据库系统

1.1.4常见的关系型数据库管理系统

  • MySQL:MySQL,MariaDB,Percona-Server
  • PostgreSQL:简称为pgsql
  • Oracle
  • MSSQL

SQL:Structure Query Language,结构化查询语言

约束:constraint,向数据表提供的数据要遵守的限制

  • 主键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。且必须提供数据,不能为空(NOT NULL)。

    • 一个表只能存在一个
  • 惟一键约束:一个或多个字段的组合,填入的数据必须能在本表中唯一标识本行。允许为空(NULL)

    • 一个表可以存在多个
  • 外键约束:一个表中的某字段可填入数据取决于另一个表的主键已有的数据

  • 检查性约束

索引:将表中的一个或多个字段中的数据复制一份另存,并且这些数据需要按特定次序排序存储

1.1.5 关系型数据库的常见组件

关系型数据库的常见组件有:

  • 数据库:database
  • 表:table,由行(row)和列(column)组成
  • 索引:index
  • 视图:view
  • 用户:user
  • 权限:privilege
  • 存储过程:procedure
  • 存储函数:function
  • 触发器:trigger
  • 事件调度器:event scheduler

2. SQL语句

SQL语句有三种类型:

  • DDL:Data Defination Language,数据定义语言
  • DML:Data Manipulation Language,数据操纵语言
  • DCL:Data Control Language,数据控制语言
SQL语句类型 对应操作
DDL CREATE:创建,DROP:删除,ALTER:修改
DML INSERT:向表中插入数据,DELETE:删除表中数据,UPDATE:更新表中数据,SELECT:查询表中数据
DCL GRANT:授权,REVOKE:移除授权

2.1 mysql安装

2.1.1 mysql方式

mysql安装方式有三种:

  • 源代码:编译安装
  • 二进制格式的程序包:展开至特定路径,并经过简单配置后即可使用
  • 程序包管理器管理的程序包:
    • rpm:有两种
      • OS Vendor:操作系统发行商
      • 提供的项目官方提供的
  • deb
[root@mr ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-25 17:42:52--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.2.135.207, 2600:1402:3800:2ab::2e31, 2600:1402:3800:28b::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.2.135.207|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-25 17:42:52--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|23.2.135.207|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-25 17:42:53--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 184.27.29.58
Connecting to repo.mysql.com (repo.mysql.com)|184.27.29.58|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’

mysql57-community-releas 100%[================================>]  25.08K   167KB/s    in 0.2s    

2022-07-25 17:42:53 (167 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]
[root@mr ~]# ls
123   htop-1.0.2                                   sleep
2     htop-1.0.2.tar.gz                            wget-1.14-18.el7_6.1.x86_64.rpm
a     mysql57-community-release-el7-11.noarch.rpm  wget-1.19.5-10.el8.x86_64.rpm
abc   nohup.out                                    xixi
hehe  outfile
[root@mr ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm 
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
  1:mysql57-community-release-el7-11 ################################# [100%]
[root@mr ~]# 
[root@mr ~]# ls /etc/yum.repos.d/
CentOS-Stream-AppStream.repo  CentOS-Stream-HighAvailability.repo  mysql-community.repo
CentOS-Stream-BaseOS.repo     CentOS-Stream-Media.repo             mysql-community-source.repo
CentOS-Stream-Debuginfo.repo  CentOS-Stream-PowerTools.repo
CentOS-Stream-Extras.repo     CentOS-Stream-RealTime.repo
[root@mr ~]# 
[root@mr ~]# yum module disable mysql
Last metadata expiration check: 0:03:45 ago on Mon 25 Jul 2022 05:46:28 PM CST.
Dependencies resolved.
==================================================================================================
Package                Architecture          Version                Repository              Size
==================================================================================================
Disabling modules:
mysql                                                                                           

Transaction Summary
==================================================================================================

Is this ok [y/N]: y
Complete!
[root@mr ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
Last metadata expiration check: 0:04:07 ago on Mon 25 Jul 2022 05:46:28 PM CST.
Dependencies resolved.
==================================================================================================
Package                    Arch       Version                        Repository             Size
==================================================================================================
Installing:
mysql-community-client     x86_64     5.7.38-1.el7                   mysql57-community      28 M
mysql-community-common     x86_64     5.7.38-1.el7                   mysql57-community     311 k
mysql-community-devel      x86_64     5.7.38-1.el7                   mysql57-community     4.2 M
mysql-community-server     x86_64     5.7.38-1.el7                   mysql57-community     178 M
Installing dependencies:
mysql-community-libs       x86_64     5.7.38-1.el7                   mysql57-community     2.6 M
ncurses-compat-libs        x86_64     6.1-9.20180224.el8             baseos                328 k
net-tools                  x86_64     2.0-0.52.20160912git.el8       baseos                322 k

Transaction Summary
==================================================================================================
Install  7 Packages

Total download size: 213 M
Installed size: 916 M
Downloading Packages:
(1/7): net-tools-2.0-0.52.20160912git.el8.x86_64.rpm              787 kB/s | 322 kB     00:00    
(2/7): ncurses-compat-libs-6.1-9.20180224.el8.x86_64.rpm          506 kB/s | 328 kB     00:00    
(3/7): mysql-community-common-5.7.38-1.el7.x86_64.rpm             143 kB/s | 311 kB     00:02    
(4/7): mysql-community-devel-5.7.38-1.el7.x86_64.rpm              2.1 MB/s | 4.2 MB     00:02    
(5/7): mysql-community-libs-5.7.38-1.el7.x86_64.rpm               832 kB/s | 2.6 MB     00:03    
(6/7): mysql-community-client-5.7.38-1.el7.x86_64.rpm             4.2 MB/s |  28 MB     00:06    
(7/7): mysql-community-server-5.7.38-1.el7.x86_64.rpm             7.9 MB/s | 178 MB     00:22    
--------------------------------------------------------------------------------------------------
Total                                                             8.3 MB/s | 213 MB     00:25     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
 Preparing        :                                                                          1/1 
 Installing       : mysql-community-common-5.7.38-1.el7.x86_64                               1/7 
 Installing       : mysql-community-libs-5.7.38-1.el7.x86_64                                 2/7 
 Running scriptlet: mysql-community-libs-5.7.38-1.el7.x86_64                                 2/7 
 Installing       : net-tools-2.0-0.52.20160912git.el8.x86_64                                3/7 
 Running scriptlet: net-tools-2.0-0.52.20160912git.el8.x86_64                                3/7 
 Installing       : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                            4/7 
 Installing       : mysql-community-client-5.7.38-1.el7.x86_64                               5/7 
 Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                               6/7 
 Installing       : mysql-community-server-5.7.38-1.el7.x86_64                               6/7 
 Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                               6/7 
 Installing       : mysql-community-devel-5.7.38-1.el7.x86_64                                7/7 
 Running scriptlet: mysql-community-devel-5.7.38-1.el7.x86_64                                7/7 
[/usr/lib/tmpfiles.d/mysql.conf:23] Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.

 Verifying        : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                            1/7 
 Verifying        : net-tools-2.0-0.52.20160912git.el8.x86_64                                2/7 
 Verifying        : mysql-community-client-5.7.38-1.el7.x86_64                               3/7 
 Verifying        : mysql-community-common-5.7.38-1.el7.x86_64                               4/7 
 Verifying        : mysql-community-devel-5.7.38-1.el7.x86_64                                5/7 
 Verifying        : mysql-community-libs-5.7.38-1.el7.x86_64                                 6/7 
 Verifying        : mysql-community-server-5.7.38-1.el7.x86_64                               7/7 
Installed products updated.

Installed:
 mysql-community-client-5.7.38-1.el7.x86_64     mysql-community-common-5.7.38-1.el7.x86_64       
 mysql-community-devel-5.7.38-1.el7.x86_64      mysql-community-libs-5.7.38-1.el7.x86_64         
 mysql-community-server-5.7.38-1.el7.x86_64     ncurses-compat-libs-6.1-9.20180224.el8.x86_64    
 net-tools-2.0-0.52.20160912git.el8.x86_64     

Complete!
[root@mr ~]# 

2.2.2mysql配置

yum安装

[root@mr ~]# systemctl enable --now mysqld(设置开机自启)
[root@mr ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2022-07-25 17:56:50 CST; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7107 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYS>
  Process: 7057 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7109 (mysqld)
    Tasks: 27 (limit: 12221)
   Memory: 311.3M
   CGroup: /system.slice/mysqld.service
           └─7109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 25 17:56:48 mr systemd[1]: Starting MySQL Server...
Jul 25 17:56:50 mr systemd[1]: Started MySQL Server.
[root@mr ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port         Peer Address:Port     Process     
LISTEN     0          128                  0.0.0.0:111               0.0.0.0:*                    
LISTEN     0          128                  0.0.0.0:22                0.0.0.0:*                    
LISTEN     0          25                   0.0.0.0:514               0.0.0.0:*                    
LISTEN     0          80                         *:3306                    *:*                    
LISTEN     0          128                     [::]:111                  [::]:*                    
LISTEN     0          128                     [::]:22                   [::]:*                    
LISTEN     0          25                      [::]:514                  [::]:*                    
[root@mr ~]# grep "password" /var/log/mysqld.log
2022-07-25T09:56:48.633177Z 1 [Note] A temporary password is generated for root@localhost: 9MS,WsyWhp># 
[root@mr ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mr ~]# mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  exit
Bye

[root@mr ~]# rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
[root@mr ~]# rpm -e mysql57-community-release-el7-11.noarch
[root@mr ~]# 

yum安装mariadb:

  • 1.yum install -y mariadb*
    1. systemctl enable --now  mariadb
    1. mysql
    1. set password = password('xxxx');
    1. exit
    1. mysql -uroot -pxxxx

2.2.3 mysql的程序组成

  • 客户端
    • mysql:CLI交互式客户端程
    • mysql_secure_installation:安全初始化,强烈建议安装完以后执行此命令
    • mysqldump:mysql备份工具
    • mysqladmin
  • 服务器端
    • mysqld
[root@mr ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 0 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password: 

Re-enter new password: 

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 
[root@mr ~]# 
[root@mr ~]# mysql -uroot -pmarui
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 


[root@mr ~]# ps aux | grep mysqld
mysql       7109  0.0  9.5 1383676 189808 ?      Sl   17:56   0:04 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root        7373  0.0  0.0  12144  1168 pts/0    S+   21:08   0:00 grep --color=auto mysqld
[root@mr ~]# 

2.2.4 mysql工具使用

//语法:mysql [OPTIONS] [database]
//常用的OPTIONS:
-uUSERNAME //指定用户名,默认为root
-hHOST //指定服务器主机,默认为localhost,推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出,常用于脚本

[root@mr ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper
[root@mr ~]# mysql -uroot -pmarui -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@mr ~]# mysql -uroot -pmarui -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mr ~]# 

2.2.5 服务器监听的两种socket地址

socket类型 说明
ip socket 默认监听在tcp的3306端口,支持远程通信
unix sock 监听在sock文件上(/tmp/mysql.sock,/var/lib/mysql/mysql.sock),仅支持本地通信,server地址只能是:localhost,127.0.0.1

3. mysql数据库操作

3.1 DDL操作

3.1.1 数据库操作

[root@mr ~]# mysql -uroot -pmarui
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database runtime;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

3.1.2 表操作

mysql> create table Linux(id int not null,name varchar(10),age tinyint (2));
Query OK, 0 rows affected (0.01 sec)

mysql> desc Linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from Linux;
Empty set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| Linux             |
+-------------------+
1 row in set (0.00 sec)

mysql>  drop table Linux;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> 

3.1.3 用户操作

mysql用户帐号由两部分组成,如'USERNAME'@'HOST',表示此USERNAME只能从此HOST上远程登录这里('USERNAME'@'HOST')的HOST用于限制此用户可通过哪些主机远程连接mysql程序,其值可为:

  • IP地址,如:172.16.12.129
  • 通配符
    • %:匹配任意长度的任意字符,常用于设置允许从任何主机登录
    • _:匹配任意单个字符
mysql> create user 'tom'@'mr' identified by 'marui';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'tom'@'mr';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *9370E4584BA357054A156F3EF8CBCE33FBAB4BF3 | N                | 2022-07-25 21:03:40   |              NULL | N              |
| localhost | mysql.session | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-07-25 17:56:48   |              NULL | Y              |
| localhost | mysql.sys     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-07-25 17:56:48   |              NULL | Y              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set (0.00 sec)

mysql> 

3.1.4 查看命令show

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

mysql> 
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from runtime;
Empty set (0.00 sec)

mysql> 

4. MySQL数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的MySQL支持 多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、 SMALLINT、 DECIMAL和NUMERIC), 以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值,并且支持MyISAM,MEMORY、InnoDB和BDB表。作为SQL标准的扩展,MySQL也支持整数类型TINYINT,MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1byte (-128,127) (0,255) 小整数值
SMALLINT 2 bytes (-32 768, 32 767) (O.65 535) 大整数值
MEDIUMINT 3 bytes (-8388 608, 8 388 607) (O,16 777215) 大整数值
INT或INTEGER 4 bytes (-2147 483 648, 2 147 483 647) (0, 4 294 967 295) 大整数值
BIGINT 8 bytes (-9.223,372,036,854.775,808, 9 223 372 036 854 775 807) (0, 18 446 744 073 709 551 615) 极大整数值
LOAT 4 bytes (-3.402 823 466 E+38, -1.175 494351E-38),(1.175 494 351 E-38,3.402 832 466 351 E+38) 0, (1 175494351 E-38, 3.402 823 466 E+38) 单精度,浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308, - 2.225 073 858507 201 4 E-308),0,1.797 693 134 862 315 7 E+308) 0, (2 .225 073 858 507 201 4 E-308) (2.225 073 858507 2014 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的值 小数值

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE,TIMESTAMP、 TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值,当 指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性,将在后面描述。

类型 大小( bytes) 范围 格式 用途
DATE 3 1000-01-019999-12-31 YYY-MM-DD 日期值
TIME 3 ‘-838 59:59/838 59.59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12 -31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTA MP 4 1970-01-01 00:00:00/2038 结束时间是第2147483647秒,北京时间2038-1-19 11:14:07,格林尼治时间2038年1月19日凌晨03:14:07 YYYYMMDDHHMMSS 混合日期和时间值,时间戳

字符串类型
字符串类型指CHAR、VARCHAR、BINARY、 VARBINARY、BLOB、TEXT、 ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过255个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

注意: char(n) 和varchar(n)中括号中n代表字符的个数,并不代表宇节个数,比如CHAR(30)就可以存储30个字符。

CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是它们包含二进制宇符串而不要非二进制宇符串。也就
是说,它们包含字节字符串而不是字符宇符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型: TINYBLOB、 BLOB、 MEDIUMBLOB 和
LONGBLOB。它们区别在于可容纳存储范围不同。

有4种TEXT类型: TINYTEXT、 TEXT、MEDIUMTEXT和LONGTEXT。对应的这4种BLOB类型,可存储的最大长度
不同,可根据实际情 况选择。

这篇关于mysql的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!