数据库mysql&mariadb基础

什么是数据库?

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

---

什么是MySql？

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

---

什么是Mariadb？

MariaDB由MySQL的创始人Michael Widenius主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。

MySQL被收购后，面临着被闭源的风险，因此MySQL之父 Widenius离开Sun后，在2009年重新开发代码全部开源免费关系型数据库，推出了MariaDB。MariaDB名称来自他的女儿Maria的名字。

MariaDB 是一个采用 Maria 存储引擎的MySQL分支版本，是由原来 MySQL 的作者Michael Widenius创办的公司所开发的免费开源的数据库服务器。

mairadb和mysql命令一致

---

一、SQL是什么？

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

跟数据库相关的工作：DBD和DBA
DBA：数据库管理员（database administrator）
DBD：数据库开发人员（database developer）

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月又公布了SQL92标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。

---

二、SQL语句结构

结构化查询语言包含6个部分：

2.1 数据查询语言 DQL

（Data Query Language）

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

---

2.2 数据操作语言 DML

（Data Manipulation Language）

其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

---

2.3 事务处理语言 TPL

（Transaction Process Language）

跟shell有点类似 由多条sql语句组成的整体

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

---

2.4 数据控制语言 DCL

(Data Command Language)

它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

---

2.5 数据定义语言 DDL

(Data Definition Language, DDL)

其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

---

2.6 指针控制语言 CCL

（Cursor Control Language, CCL）

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

---

三、Mariadb安装初始化

3.1 配置环境

使用centos7.6版本，搭建好yum仓库（本文使用本地iso文件centos7.6）
centos6.5以上

yum -y install mariadb-server mariadb

centos6.5以下

yum install mysql-community-server mysql

查看未安裝包的信息

rpm -pqi  包名

查看版本

mysql -V

---

3.2 启动服务

centos6.5以上，服务为mariadb

启动服务
systemctl start mariadb

关闭服务
systemctl stop mariadb

服务自启动
systemctl enable mariadb

查看服务状态
systemctl status mariadb

centos6.5以上，服务为mysqld

启动服务
systemctl start mysqld

关闭服务
systemctl stop mysqld

服务自启动
systemctl enable mysqld

查看服务状态
systemctl status maysqld

3.2 初始化mairadb

启动服务后，配置数据库的账号和密码
Mysql的超级管理员是root，拥有最mysql数据库的最高权限。

3.2.1 场景一

如果没有密码可以直接进行配置

尝试登录
mysql -uroot -p #按下回车

修改密码
mysqladmin -uroot password 123 

3.2.2 场景二

如果服务有自动分配密码，查看密码后进行修改

查看日志的密码
grep 'temporary password' /var/log/mysqld.log

修改密码，输入原密码后就可以修改为新密码
myslqadmin -u用户名 password密码 

3.2.3 安装完成后，可以使用以下命令对mysql进行安全配置

语法 mysql_secure_installation

1、为root用户设置密码
2、删除匿名账号
3、取消root用户远程登录
4、删除test库和对test库的访问权限
5、刷新授权表使修改生效

回车键默认为y

详细步骤请参看以下命令:
[root@localhost ~]# mysql_secure_installation
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n	<– 是否设置root用户密码, 已设置密码输入n
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] y	<– 是否删除匿名用户,生产环境建议删除，所以直接回车或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? [Y/n] n	 <–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
 ... skipping.

By default, MariaDB 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? [Y/n] n	<– 是否删除test数据库,直接回车或Y
 ... skipping.

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

Reload privilege tables now? [Y/n] y	<– 是否重新加载权限表，直接回车
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

---

3.3 配置简单密码

3.3.1 方法一

进入数据库，修改validate_password_policy参数的值

先登录数据库，用数据库命令进行修改
set global validate_password_policy=0;  #定义复杂度

set global validate_password_length=1;  #定义长度 默认是8

set password for 'root'@'localhost'=password('123456');

flush privileges; 	#刷新MySQL的系统权限相关表­

---

3.3.1 方法二

在/etc/my.cnf 可关闭密码强度审计插件，然后重启服务

validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT #决定是否使用该插件(及强制/永久强制使用)。

---

3.4 远程登录mysql

语法 mysql -h ip/域名 -u账号 -p密码

3.5 登录mariadb

语法 mysql -u账号 -p密码

例如：

mysql -uroot -p123

---

3.5 获取帮助

语法 mysql --help

例如：

[root@localhost ~]# mysql --help
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Usage: mysql [OPTIONS] [database]

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
The following groups are read: mysql client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults          Print the program argument list and exit.
--no-defaults             Don't read default options from any option file.
The following specify which files/extra groups are read (specified before remaining options):
--defaults-file=#         Only read default options from the given file #.
--defaults-extra-file=#   Read this file after the global files are read.
--defaults-group-suffix=# Additionally read default groups with # appended as a suffix.

  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --abort-source-on-error 
                      Abort 'source filename' operations in case of errors
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash 
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output 
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)
  -b, --binary-as-hex Print binary data as hex
  --character-sets-dir=name 
                      Directory for character set files.
  --column-type-info  Display column type information.
  -c, --comments      Preserve comments. Send comments to the server. The
                      default is --skip-comments (discard comments), enable
                      with --comments.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       Check memory and open file usage at exit.
  -T, --debug-info    Print some debug info at exit.
  -D, --database=name Database to use.
  --default-character-set=name 
                      Set the default character set.
  --delimiter=name    Delimiter to be used.
  -e, --execute=name  Execute command and quit. (Disables --force and history
                      file.)
  -E, --vertical      Print the output of a query (rows) vertically.
  -f, --force         Continue even if we get an SQL error. Sets
                      abort-source-on-error to 0
  -G, --named-commands 
                      Enable named commands. Named commands mean this program's
                      internal commands; see mysql> help . When enabled, the
                      named commands can be used from any line of the query,
                      otherwise only from the first line, before an enter.
                      Disable with --disable-named-commands. This option is
                      disabled by default.
  -i, --ignore-spaces Ignore space after function names.
  --init-command=name SQL Command to execute when connecting to MySQL server.
                      Will automatically be re-executed when reconnecting.
  --local-infile      Enable/disable LOAD DATA LOCAL INFILE.
  -b, --no-beep       Turn off beep on error.
  -h, --host=name     Connect to host.
  -H, --html          Produce HTML output.
  -X, --xml           Produce XML output.
  --line-numbers      Write line numbers for errors.
                      (Defaults to on; use --skip-line-numbers to disable.)
  -L, --skip-line-numbers 
                      Don't write line number for errors.
  -n, --unbuffered    Flush buffer after each query.
  --column-names      Write column names in results.
                      (Defaults to on; use --skip-column-names to disable.)
  -N, --skip-column-names 
                      Don't write column names in results.
  --sigint-ignore     Ignore SIGINT (CTRL-C).
  -o, --one-database  Ignore statements except those that occur while the
                      default database is the one named at the command line.
  --pager[=name]      Pager to use to display results. If you don't supply an
                      option, the default pager is taken from your ENV variable
                      PAGER. Valid pagers are less, more, cat [> filename],
                      etc. See interactive help (\h) also. This option does not
                      work in batch mode. Disable with --disable-pager. This
                      option is disabled by default.
  -p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --progress-reports  Get progress reports for long running commands (like
                      ALTER TABLE)
                      (Defaults to on; use --skip-progress-reports to disable.)
  --prompt=name       Set the mysql prompt to this value.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -q, --quick         Don't cache result, print it row by row. This may slow
                      down the server if the output is suspended. Doesn't use
                      history file.
  -r, --raw           Write fields without conversion. Used with --batch.
  --reconnect         Reconnect if the connection is lost. Disable with
                      --disable-reconnect. This option is enabled by default.
                      (Defaults to on; use --skip-reconnect to disable.)
  -s, --silent        Be more silent. Print results with a tab as separator,
                      each row on new line.
  -S, --socket=name   The socket file to use for connection.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert 
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  -t, --table         Output in table format.
  --tee=name          Append everything into outfile. See interactive help (\h)
                      also. Does not work in batch mode. Disable with
                      --disable-tee. This option is disabled by default.
  -u, --user=name     User for login if not current user.
  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.
  -v, --verbose       Write more. (-v -v -v gives the table output format).
  -V, --version       Output version information and exit.
  -w, --wait          Wait and retry if connection is down.
  --connect-timeout=# Number of seconds before connection timeout.
  --max-allowed-packet=# 
                      The maximum packet length to send to or receive from
                      server.
  --net-buffer-length=# 
                      The buffer size for TCP/IP and socket communication.
  --select-limit=#    Automatic limit for SELECT when using --safe-updates.
  --max-join-size=#   Automatic limit for rows in a join when using
                      --safe-updates.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol.
  --server-arg=name   Send embedded server this as a parameter.
  --show-warnings     Show warnings after every statement.
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.
  --binary-mode       By default, ASCII '\0' is disallowed and '\r\n' is
                      translated to '\n'. This switch turns off both features,
                      and also turns off parsing of all clientcommands except
                      \C and DELIMITER, in non-interactive mode (for input
                      piped to mysql or loaded using the 'source' command).
                      This is necessary when processing output from mysqlbinlog
                      that may contain blobs.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
abort-source-on-error             FALSE
auto-rehash                       TRUE
auto-vertical-output              FALSE
binary-as-hex                     FALSE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
database                          (No default value)
default-character-set             utf8
delimiter                         ;
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
ignore-spaces                     FALSE
init-command                      (No default value)
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
progress-reports                  TRUE
prompt                            \N [\d]> 
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
user                              (No default value)
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect-timeout                   0
max-allowed-packet                16777216
net-buffer-length                 16384
select-limit                      1000
max-join-size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE
plugin-dir                        (No default value)
default-auth                      (No default value)
binary-mode                       FALSE

---

四、数据库命令

需要先登录mysql，进入mysql的命令界面

ps: 所有命令以;结尾

4.1 查看数据库

语法 show databases;

例如：

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

注意：
1：information_schema这个数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型不访问权限等。
2：performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA
主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表
3：mysql库是系统库，里面保存有账户信息，权限信息等。
4：mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息
元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。

1、informance_schema
保存了MySQl服务所有数据库的信息。
具体MySQL服务有多少个数据库，各个数据库有哪些表，各个表中的字段是什么数据类型，各个表中有哪些索引，各个数据库要什么权限才能访问。

2、mysql
保存MySQL的权限、参数、对象和状态信息。
如哪些user可以访问这个数据、DB参数、插件、主从

3、performance_schema
主要用于收集数据库服务器性能参数
提供进程等待的详细信息，包括锁、互斥变量、文件信息；
保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

4、test
没有东西

前三个库不能删除

---

4.2 以行的方式显示

语法 \G

例如：

MariaDB [(none)]> show databases \G;
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
*************************** 4. row ***************************
Database: test
4 rows in set (0.00 sec)

---

4.3 配置退出数据库

语法 exit;（可以不加;号）

例如：

MariaDB [(none)]> exit
Bye

---

4.4 在linux终端查看数据库信息

语法 mysql -e '数据库命令'

例如：

[root@localhost ~]# mysql -uroot -p123 -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

在Linux终端查看数据库列表
语法 mysqlshow

例如：

[root@localhost ~]# mysqlshow -uroot -p123
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

---

4.5 创建数据库

语法 create database 库名;

例如：

MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.00 sec)

创建数据库注意事项：
1、在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库。因此，上面命令中的数据库名字必须与操作系统的约束的目录名字一致。例如不允许文件和目录名中有\,/,:,*,?,”,<,>,|这些符号，在MySQL数据库名字中这些字母会被自动删除。<遵从目录的约束>

2、数据库的名字不能超过64个字符，包含特殊字符的名字或者是全部由数字或保留字组成的名字，必须用单引号``包起来。(该符号在Tab上面)

3、数据库不能重名。

4、数据库名区分大小写

例如：

mysql> create database BB;  #创建一个名为BB的数据库
mysql> create database `BB-test`;	#创建一个包含特殊字符的数据库名

查看数据库存放目录

mysql的存放目录
/usr/local/mysql/data/ 

mariadb的存放目录
/var/lib/mysql/

---

4.5 使用数据库

语法 use 库名;

例如：

MariaDB [(none)]> use test1;
Database changed

---

4.6 查看自己所处的数据库位置及默认所在的位置

语法 select database();

例如：
打开数据库查询

MariaDB [(none)]> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec

Null在数据库中表示不知道的数据，有三种含义：
1、知道数据存在，但不知道具体值.
2、不知道数据是否存在.
3、数据不存在.

进入上面创建的test1库，查询目前所处的位置

MariaDB [test1]> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

---

4.7 查询当前时间，当前用户，当前库信息

语法 select now(),user(),database();

例如：
进入创建的test1，进行查询

MariaDB [test1]> select now(),user(),database();
+---------------------+----------------+------------+
| now()               | user()         | database() |
+---------------------+----------------+------------+
| 2022-04-25 01:00:30 | root@localhost | test1      |
+---------------------+----------------+------------+
1 row in set (0.00 sec)

---

4.8 删除数据库

4.8.1 方法一

语法 drop database 库名;

例如：删除创建的test1数据库

MariaDB [test1]> drop database test1;
Query OK, 0 rows affected (0.00 sec)

注意：删除数据库没有任何提示，要谨慎操作

---

4.8.2 方法二

【mairadb】
进入存放的目录/var/lib/mysql,将库目录删除就可以了

cd /var/lib/mysql
rm -rf test/

---

4.9 获取帮助

语法 help show;

例如：

MariaDB [(none)]> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/show.html

---

五、Mysql的数据类型

mariadb和mysql是一致的

5.1 MySQL的数据类型主要六大类

整数类型
BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

---

浮点数类型
FLOAT、DOUBLE

---

定点数
DECIMAL

---

字符串类型
CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

---

日期类型
Date、DateTime、TimeStamp、Time、Year

---

二进制类型
BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 、 LONGBLOB

---

5.2 整型

数据类型	大小	范围	说明
tinyint(m)	1个字节	(-128~127)	很小的整数
smallint(m)	2个字节	(-32768~32767)	小的整数
mediumint(m)	3个字节	(-8388608~8388607)	中等大小的整数
INT (INTEGHR)	4个字节	(-2147483648~2147483647)	普通大小的整数
bigint(m)	8个字节	(-9223372036854775808〜9223372036854775807)	大整数

---

5.3 浮点型

数据类型	大小	有符号范围	无符号范围	说明
FLOAT	4 个字节	（-3.402823466E+38～-1.175494351E-38）	（0 和 -1.175494351E-38～-3.402823466E+38）	单精度浮点数
DOUBLE	8 个字节	（-1.7976931348623157E+308～-2.2250738585072014E-308	0 和 -2.2250738585072014E-308～-1.7976931348623157E+308）	双精度浮点数

---

5.4 定点类型

数据类型	大小	范围	说明
DECIMAL (M, D)，DEC	M+2 个字节	-9.99 到99.99	压缩的“严格”定点数

在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

---

5.5 日期类型

数据类型	大小	日期格式	日期范围
YEAR	1 个字节	YYYY	1901 ~ 2155
TIME	3 个字节	HH:MM:SS	-838:59:59 ~ 838:59:59
DATE	3 个字节	YYYY-MM-DD	1000-01-01 ~ 9999-12-3
DATETIME	8 个字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP	4 个字节	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC

---

5.6 字符串类型

数据类型	大小	说明
CHAR(M)	M 字节，1<=M<=255	固定长度非二进制字符串
VARCHAR(M)	L+1字节，在此，L< = M和 1<=M<=255	变长非二进制字符串
TINYTEXT	L+1字节，在此，L<2^8	非常小的非二进制字符串
TEXT	L+2字节，在此，L<2^16	小的非二进制字符串
MEDIUMTEXT	L+3字节，在此，L<2^24	中等大小的非二进制字符串
LONGTEXT	L+4字节，在此，L<2^32	大的非二进制字符串
ENUM	1或2个字节，取决于枚举值的数目 (最大值为65535)	枚举类型，只能有一个枚举字符串值
SET	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）	一个设置，字符串对象可以有零个或 多个SET成员

5.7 二进制类型

数据类型	大小	说明
BIT(M)	大约 (M+7)/8 字节	位字段类型
BINARY(M)	M 字节	固定长度二进制字符串
VARBINARY (M)	M+1 字节	可变长度二进制字符串
TINYBLOB (M)	L+1 字节，在此，L<2^8	非常小的BLOB
BLOB (M)	最大长度为65535 (216-1)字节	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	L+3 字节，在此，L<2^24	中等大小的BLOB
LONGBLOB (M)	L+4 字节，在此，L<2^32	非常大的BLOB

六、表命令

使用创建的数据库

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

---

6.1 创建表

语法 create table 表名 (字段名 类型(长度));
长度可加也可以不加,多个字段用,号隔开

例如：

MariaDB [test]> create table test(name char(10));
\Query OK, 0 rows affected (0.04 sec)


MariaDB [test]> create table test1(name char);
Query OK, 0 rows affected (0.02 sec)

可以看看下图就有概念了，可以用excel表来理解

字段名 类型(长度)	字段名 类型(长度)
名字 文本(10)	年龄 数字(3)

---

6.2 查看表

语法 show tables;

例如 ：

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

---

6.3 查看表的结构

有多种方法，选择一种作为常用就可以了。

---

6.3.1 方法一

语法 desc 表名;

例如：

MariaDB [test]> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

---

6.3.2 方法二

语法 explain 表名;

例如

MariaDB [test]> explain test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

---

6.3.3 方法三

语法 show columns from 表名;

例如：

MariaDB [test]> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

6.4 查看创建表使用的命令

语法 show create table 表名;

例如：

MariaDB [test]> show create table test;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+---------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

6.5 表的存储引擎

6.5.1 引擎是什么？

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。

6.5.2 查看存储引擎

语法 show engines;

例如：

MariaDB [test]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                         | 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         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

---

6.5.3 存储引擎简介

6.5.3.1 InnoDB存储引擎(默认)

支持事务安全表(ACID)，支持行锁定和外键
设计遵循ACID模型，支持事务，具有从服务崩溃中恢复数据的能力，能够大限度包含用户的数据
支持行级所，可以提升多用户并发时的读写性能
支持外键，保持数据的一致性和完整性
innoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中

---

6.5.3.2 MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。
默认MyISAM的表会在磁盘中产生三个文件：.frm、.MYD和.MYI
MyISAM单表大支持的数据量是2的64次方条记录
每个表多可以建立64个索引
如果是复合索引，每个复合索引多包含16个列，索引值大长度是1000B

MyISAM引擎的存储格式：定长(FIXED静态)：是指字段中不包含VARCHAR\TEXTBLOB;动态(DYNAMIC)：只要字段中包含VARCHAR\TEXTBLOB;压缩(COMPRESSED):myisampack创建

---

6.5.3.3 MEMORY存储引擎

将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。
磁盘中产生一个以表名为名称的.frm文件，只保存表结构
如果关闭MySQL服务，此时数据会产生都是rr
max_head_table_size默认16MB

---

6.5.3.4 ARCHIVE存储引擎

适合对于不经常访问又删除不了的数据做归档储存
.frm文件结构文件，.arz数据文件
插入效率很高，而且占用空间小
ARCHIVE存储引擎只支持INSERT和SELECT操作，不支持UPDATE/DELECT/

---

6.5.4 指定表的引擎和字符集

语法 在创建表命令的最后进行指定ENGINE=引擎 DEFAULT CHARSET=字符集;

例如：

MariaDB [test]> create table test2(id int(20),name char(40))ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

---

6.6 表的字符集

6.6.1 字符集是什么？

机器是通过二进制码（比如0101）来进行识别执行的。在电脑上看到的文字，每一个字符都有一个对应的二进制数字，字符集就是这些字符的集合。通过字符集，将二进制机器码转换为人可读的字符。

例如：
打开某些文件是乱码，是因为没有配置好字符集。

---

6.6.2 查看字符集

语法 show character set;

例如：

MariaDB [(none)]> 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 |
| 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 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)

---

6.6.3 查看当前MYSQL服务器字符集设置

语法 show variables like 'character_set%';

例如：
MariaDB [(none)]> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

如果在mysql服务器里面修改字符集，修改是临时生效的，重启服务会恢复默认的
如果需要永久生效，需要在配置文件里面进行修改

【mysql】
vim /etc/my.cnf
【mariadb】
vim /etc/my.cnf.d/server.cnf

在最后面添加一条配置

character_set_server=utf8

重启一下服务就可以了

systemctl restart mairadb

---

6.6.4 查看当前MYSQL服务器字符集校验设置

语法 SHOW VARIABLES LIKE 'collation_%';

例如：

MariaDB [(none)]> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

---

6.6.5 查看数据库字符集设置

语法 show create database 库名;

例如：

MariaDB [(none)]> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

---

6.6.6 查看数据表字符集设置

语法 show create table 表名;

例如：

例如：
MariaDB [test]> show create table test2;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `name` char(10) DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

6.6.7 修改数据库字符集

语法：alter database 库名 default character set '字符集';

例如：

MariaDB [(none)]> alter database test default character set 'utf8';
Query OK, 1 row affected (0.00 sec)

---

6.6.8 修改数据表字符集

语法：alter table 表名 default character set '字符集';

例如：

MariaDB [test]> alter table new  default character set 'utf16';
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

---

6.7 删除表

语法 drop table 表名;

例如：

MariaDB [test]> drop table test2;
Query OK, 0 rows affected (0.00 sec)

【结合if exists/not exists】
drop table test if not 

---

6.8 修改表名

6.8.1 方法一

语法 alter table 表名 rename 新表名;

例如：

MariaDB [test]> alter table test rename new;
Query OK, 0 rows affected (0.00 sec)

6.8.2 方法二

语法 rename table 表名 to 新表名;

6.9 修改表的字段类型和字段名

有两种方法： MODIFY和CHANGE

两者的区别：
MODIFY ：可以改变列的类型，不需要重命名字段名，直接可以修改字段类型
CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。

---

6.9.1 方法一 修改字段类型长度

语法 alter table 表名 modify 原字段名 类型(长度);
类型长度可加可不加

例如：

【先查看表结构】
MariaDB [test]> desc new;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

【修改字段类型】
MariaDB [test]> alter table new modify name int(10);
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

【查看修改后的表结构】
MariaDB [test]> desc new;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

---

6.9.2 方法二 修改字段名和字段类型

语法 alter table 表名 change 原字段名 新字段名 类型(长度);

例如：

【先查看表结构】
MariaDB [test]> desc new;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

【修改字段类型和字段名】
MariaDB [test]> alter table new change name newname char(10);
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

【查看修改后的表结构】
MariaDB [test]> desc new;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| newname | char(10) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

---

6.10 删除表中字段

语法 alter table 表名 drop 字段名;

例如：

【创建多字段的表】
MariaDB [test]> create table test2 (name char(10),id int(18),age int(3));
Query OK, 0 rows affected (0.01 sec)

【查看多字段表结构】
MariaDB [test]> desc test2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| id    | int(18)  | YES  |     | NULL    |       |
| age   | int(3)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

【删除表中的字段】
MariaDB [test]> alter table test2 drop id;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

【查看表结构，id字段不见了】
MariaDB [test]> desc test2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int(3)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

---

6.11 在表中添加字段

6.11.1 在表最后添加字段

默认会添加到最后

语法 alter table 表名 add 字段名 类型(长度);

例如：

【先查看表】
MariaDB [test]> desc test1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

【添加性别字段，类型为enum,值为男m女w，后续插入字段值只能在此范围选其一】
MariaDB [test]> alter table test1 add sex enum('M','W');
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

【ps】
ENUM是枚举类型,它虽然只能保存一个值,却能够处理多达65535个预定义的值。范围可以配置多个。


【查看表结构，已添加成功】
MariaDB [test]> desc test1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name  | char(1)       | YES  |     | NULL    |       |
| sex   | enum('M','W') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

---

6.11.2 在表第一列添加字段

语法 alter table 表名 add 字段名 类型(长度) first;

例如：

【查看表】
MariaDB [test]> desc test1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name  | char(1)       | YES  |     | NULL    |       |
| sex   | enum('M','W') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

【添加id字段在最前】
MariaDB [test]> alter table test1 add id int(10) first;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

【查看表结构】
MariaDB [test]> desc test1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(10)       | YES  |     | NULL    |       |
| name  | char(1)       | YES  |     | NULL    |       |
| sex   | enum('M','W') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

---

6.11.3 在表的某字段之后插入

语法 alter table 表名 add 字段名 类型(长度) after 某字段;

例如：

【查看表结构】
MariaDB [test]> desc test1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(10)       | YES  |     | NULL    |       |
| name  | char(1)       | YES  |     | NULL    |       |
| sex   | enum('M','W') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

【在name字段后面插入test字段】
MariaDB [test]> alter table test1 add text char(19) after name;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

【查看表格结构】
MariaDB [test]> desc test1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(10)       | YES  |     | NULL    |       |
| name  | char(1)       | YES  |     | NULL    |       |
| text  | char(19)      | YES  |     | NULL    |       |
| sex   | enum('M','W') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

---

七、插入数据命令

7.1 插入字段值

语法 insert into 表名 values(字段值1,字段值2,字段值3)

插入多条记录使用,分开
插入空值使用null

例如：

【先查看表结构】
MariaDB [test]> desc test2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int(3)   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

【插入字段值，插入时要对应相对的类型】
MariaDB [test]> insert into test2 values('mike',20);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into test2 values('小明',21);
Query OK, 1 row affected, 1 warning (0.00 sec)

【插入多条记录】
MariaDB [test]> insert into test2 values('wayne',23),('tom',24);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

【插入空记录】
MariaDB [test]> insert into test2 values();
Query OK, 1 row affected (0.00 sec)

---

7.2 查询表内的记录

语法 select * from 表名;

例如：

【查看test2表内的记录】
MariaDB [test]> select * from test2;
+------+------+
| name | age  |
+------+------+
| mike |   20 |
| ??   |   21 |
| wayne  |   23 |
| tom    |   24 |
| NULL   | NULL |
+--------+------+
5 rows in set (0.00 sec)

【查看表中是否有mike记录】
MariaDB [test]> select 'mike' from test2;
+------+
| mike |
+------+
| mike |
| mike |
| mike |
| mike |
+------+
4 rows in set (0.00 sec)

注意：
* 表示为所有，也可以为字段值
因为字符集不匹配，所以输入中文数据显示为问号

---

7.3 插入中文数据显示为问号

需要修改字符集，重新插入后显示的就是正常的

【修改server.cnf文件默认字符集为utf8】
vim /etc/my.cnf.d/server.cnf 

[mysqld]
default-storage-engine = innodb
innodb_file_per_table
max_connections = 4096
collation-server = utf8_general_ci
character-set-server = utf8

【修改client.cnf默认字符集为utf8】
vim my.cnf.d/client.cnf

[client]
default-character-set=utf8

【查看数据库的字符集，如果不是utf8，修改数据库字符集】
查看
show create database 库名;

修改
alter database 库名 default character set 'utf8';

【查看表的字符集，如果不是utf8，修改表的字符集】
查看
show cteate table 表名；

修改
alter table 表名  default character set 'utf16';

---

7.4 删除记录

语法 delete from 表名 where 字段 = ’字段值‘;

如果记录为空(NULL)，语法如下：

语法 delete from 表名 where 字段 is null;

例如：

【先查看表的记录】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   20 |
| 小明   |   21 |
| wayne  |   23 |
| tom    |   24 |
| NULL   | NULL |
+--------+------+
5 rows in set (0.00 sec)


【删除name字段的wayne值】
MariaDB [test]> delete from test2 where name = 'wayne';
Query OK, 1 row affected (0.00 sec)


【查看表记录】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   20 |
| 小明   |   21 |
| tom    |   24 |
| NULL   | NULL |
+--------+------+
4 rows in set (0.00 sec)


【删除空值】
MariaDB [test]> delete from test2 where name is null;
Query OK, 1 row affected (0.00 sec)

【查看表记录】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   20 |
| 小明   |   21 |
| tom    |   24 |
+--------+------+
3 rows in set (0.00 sec)

---

7.5清空表记录命令

语法truncate table 表名

注意：

不能和where一起用。
删除数据后不可以rollbac，会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，不是原来的ID。不写服务器log，整体删除速度快。不激活trigger(触发器)

---

7.6 更新记录

可以理解为修改插入的字段值

语法 update 表名 set 要修改的字段=新的字段值 where 用于定位的字段和字段值;

同时更新多个字段，用,号隔开

例如：

【先查看表的记录值】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   20 |
| 小明   |   21 |
| tom    |   24 |
+--------+------+
3 rows in set (0.00 sec)


【更新tom的年龄为22】
MariaDB [test]> update test2 set age=22 where name='tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【查看表记录】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   20 |
| 小明   |   21 |
| tom    |   22 |
+--------+------+
3 rows in set (0.00 sec)

【更新tom的名字为marry】
MariaDB [test]> update test2 set name='marry' where name='tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【查看表记录】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   20 |
| 小明   |   21 |
| marry  |   22 |
+--------+------+
3 rows in set (0.00 sec)

【更新整个字段，将所有年龄修改为18】
MariaDB [test]> update test2 set age = 18;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

【查看表记录】
MariaDB [test]> select * from test2;
+--------+------+
| name   | age  |
+--------+------+
| mike   |   18 |
| 小明   |   18 |
| marry  |   18 |
+--------+------+
3 rows in set (0.00 sec)

---

八、条件查询语句

创建用于测试的数据表：
语法 update 表名 set 要修改的字段=新的字段值 where 用于定位的字段值;

【创建测试表test3】
MariaDB [test]> create table test3 (id int(10),name char(20),age int(3));
Query OK, 0 rows affected (0.01 sec)

【插入值】
MariaDB [test]> insert into test3 values(1,'mike',20),(2,'ben',null),(3,'jarry',20),(4,'john',22),(5,'wayne',22),(6,'mike',30),(7,'Lisa',24),(8,'lisa',22);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

8.1 查询字段

语法 select 字段 from 表名;

多个字段用,隔开

【查询test3表中的id字段和name字段】
MariaDB [test]> select * from test3;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | mike  |   20 |
|    2 | ben   | NULL |
|    3 | jarry |   20 |
|    4 | john  |   22 |
|    5 | wayne |   22 |
|    6 | mike  |   30 |
|    7 | Lisa  |   24 |
|    8 | lisa  |   22 |
+------+-------+------+
8 rows in set (0.00 sec)

---

8.2 distinct去重查询

语法 select distinct 字段 from 表名;

例如：

【去重复查询name字段】
MariaDB [test]> select distinct name from test3;
+-------+
| name  |
+-------+
| mike  |
| ben   |
| jarry |
| john  |
| wayne |
| Lisa  |
+-------+
6 rows in set (0.00 sec)	

【结合where使用，去重查询id为4的id，name，age字段值】
MariaDB [test]> select distinct id,name,age from test3 where id = 4;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    4 | john |   22 |
+------+------+------+
1 row in set (0.00 sec)

---

8.3 算数运算符

=	等于
<>	不等于  !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

---

8.4 and查询

语法 select 字段 from 表名 where 条件1 and 条件2;

and（和），结合where使用

例如：

【查询name字段=mike，年龄大于18的记录】
MariaDB [test]> select name,age from test3 where name='mike' and age>18;
+------+------+
| name | age  |
+------+------+
| mike |   20 |
| mike |   30 |
+------+------+
2 rows in set (0.00 sec)

---

8.5 or查询

语法 select 字段 from 表名 where 条件1 or 条件2;

or（或），结合where使用

【查询name字段为mike或者sean的值】
MariaDB [test]> select name from test3 where name='mike' or name='sean';
+------+
| name |
+------+
| mike |
| mike |
+------+
2 rows in set (0.00 sec)

8.6 and和or结合查询

语法 select 字段 from 表名 where 条件1 and (条件2 or 条件3);
语法 select 字段 from 表名 where 条件1 or (条件2 and 条件3);

例如：

【查询name字段为mike，和（age字段大于20或id小于7）的记录】
MariaDB [test]> select * from test3 where name='mike' and (age=20 or id<7);
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | mike |   20 |
|    6 | mike |   30 |
+------+------+------+
2 rows in set (0.00 sec)

【查询name字段为wayne，或（name字段为mike和age大于20）的记录】
MariaDB [test]> select * from test3 where name='wayne' or (name='mike' and age>20);
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    5 | wayne |   22 |
|    6 | mike  |   30 |
+------+-------+------+
2 rows in set (0.00 sec)

---

8.7 like查询

语法 select 字段 from 表名 where 字段 like 条件;
% 以xx值为开头的记录
$ 以xx值为结尾的记录

like（类型，含有），结合where使用

例如：

【查看mike字段的记录】
MariaDB [test]> select * from test3 where name  like 'mike';
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | mike |   20 |
|    6 | mike |   30 |
+------+------+------+
2 rows in set (0.00 sec)

---

8.8 in查询

语法 select 字段 from 表名 where 字段名 in （字段值）;

in（包含），结合where使用

例如：

【id字段里为1和2的记录】
MariaDB [test]> select * from test3 where id in (1);
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | mike |   20 |
+------+------+------+
1 row in set (0.00 sec)

---

8.9 not查询

not（不）

例如：

【name字段不包含mike的记录】
MariaDB [test]> select * from test3 where name not like 'mike';
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    2 | ben   | NULL |
|    3 | jarry |   20 |
|    4 | john  |   22 |
|    5 | wayne |   22 |
|    7 | Lisa  |   24 |
|    8 | lisa  |   22 |
+------+-------+------+
6 rows in set (0.00 sec)


【id字段不含有1的记录】
MariaDB [test]> select * from test3 where id  not in (1);
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    2 | ben   | NULL |
|    3 | jarry |   20 |
|    4 | john  |   22 |
|    5 | wayne |   22 |
|    6 | mike  |   30 |
|    7 | Lisa  |   24 |
|    8 | lisa  |   22 |
+------+-------+------+
7 rows in set (0.00 sec)

---

8.8 Binary查询

mysql和mariadb的查询默认不区分大小写

BINARY是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。

语法 select 字段 from 表名 where binary 条件;

例如：

【查寻小写的lisa】
MariaDB [test]> select * from test3 where binary name='lisa';
+------+------+------+
| id   | name | age  |
+------+------+------+
|    8 | lisa |   22 |
+------+------+------+
1 row in set (0.00 sec)

【查询大写的lisa】
MariaDB [test]> select * from test3 where binary name='Lisa';
+------+------+------+
| id   | name | age  |
+------+------+------+
|    7 | Lisa |   24 |
+------+------+------+
1 row in set (0.00 sec)

---

8.9 排序查询

8.9.1 asc升序查询

语法 select 字段 from 表名 order by 字段 asc;

例如:

【test表按照name字段升序排序】
MariaDB [test]> select * from test3 order by name asc;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    2 | ben   | NULL |
|    3 | jarry |   20 |
|    4 | john  |   22 |
|    7 | Lisa  |   24 |
|    8 | lisa  |   22 |
|    1 | mike  |   20 |
|    6 | mike  |   30 |
|    5 | wayne |   22 |
+------+-------+------+

【结合去重distinct，将name字段去重升序排序】
MariaDB [test]> select distinct name from test3 order by name asc;
+-------+
| name  |
+-------+
| ben   |
| jarry |
| john  |
| Lisa  |
| mike  |
| wayne |
+-------+
6 rows in set (0.00 sec)

---

8.9.2 desc降序查询

语法 select 字段 from 表名 order by 字段 desc;

例如：

【test表按照name字段降序排序】
MariaDB [test]> select * from test3 order by name desc;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    5 | wayne |   22 |
|    1 | mike  |   20 |
|    6 | mike  |   30 |
|    7 | Lisa  |   24 |
|    8 | lisa  |   22 |
|    4 | john  |   22 |
|    3 | jarry |   20 |
|    2 | ben   | NULL |
+------+-------+------+
8 rows in set (0.00 sec)

【结合去重distinct，将name字段去重降序排序】
MariaDB [test]> select distinct name from test3 order by name desc;
+-------+
| name  |
+-------+
| wayne |
| mike  |
| Lisa  |
| john  |
| jarry |
| ben   |
+-------+
6 rows in set (0.00 sec)

---

8.10 mysql子查询

实现查询中嵌套着查询

概念：在select 的where条件中又出现了select

例如：

【寻找id号为1的名字】
MariaDB [test]> select name from test3 where id=(select id from test3 where id = 1);
+------+
| name |
+------+
| mike |
+------+
1 row in set (0.01 sec)

---

8.11 LIMIT 限制显示的记录行数

语法 select * from 表名 limit 行数起始,行数;

是从0开始计算的，0就是第一行

例如：

【只显示第一行开始，5行的记录】
MariaDB [test]> select * from test3 limit 0,5;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | mike  |   20 |
|    2 | ben   | NULL |
|    3 | jarry |   20 |
|    4 | john  |   22 |
|    5 | wayne |   22 |
+------+-------+------+
5 rows in set (0.00 sec)

---

8.12 查询表的数据条数

语法 select count(*) from 表名;

---

九、常用查看命令

9.1 select命令

【查看当前日期和时间】
select now();

【查看当前日期】
select curdate();

【查看当前时间]
select curdate();

【查看数据库版本】
select version();


【查看当前用户】
select user();

【查看当前数据库】
select database();

---

9.2 show命令

【查看变量】
show variables;

【查看全局变量】
show global variables;

show global variables like '%version%';

【查看默认的存储引擎】
show variables like '%storage_engine%'; 
#like是模糊搜索，除了like 还有not like

也可以使用where字句，例如
select * from students where stname like '%l%1%2%3%';

【查看支持的引擎】
show engines;	

【查看系统运行状态信息】
show status;

【查看进程使用情况】
show global status like 'Thread%';

---

十、导出导入数据库

10.1 导出数据库

语法 mysqldump -u用户名 -p密码 数据库名 >导出的文件名.sql
-A 导出所有数据库的数据
-B 导出整个数据库（包括建库语句）
-Y导出所有表空间的数据
-E 导出事件
-d 只导出表结构
-t (–no-create-info:只导出数据，而不添加CREATE TABLE 语句)
-n (–no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
-R (–routines:导出存储过程以及自定义函数)
-E (–events:导出事件)
--triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
--tables 表列表（单个表时可省略）
--ignore-table 指定要忽略备份的表。

例如：

mysqldump -uroot -p123 test >test.sql

---

10.2 导入数据库

10.2.1 创建空数据库

导入之前需要创建一个空数据库，作为导入的对象

在命令行进行创建

mysql -e 'create database test6' -uroot -p123

---

进入数据库创建

create database test2；

---

10.2.2 导入方法一

语法 mysql -u用户名 -p密码 数据库名 <导入的文件名.sql

例如：

mysql -uroot -p123 test2 < test.sql

---

10.2.3 导入方法二

进入数据库导入，使用source命令

语法 source 路径/文件名.sql

例如：

MariaDB [(none)]> use test2;
Database changed
MariaDB [test2]> source /root/test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

---

十一、