Mysql8+数据库安装和使用
一、Mysql的版本选择
Mysql目前分文社区版和企业版，社区版在技术方面会加入许多新的未经严格测试的特性，而企业版经过严格测试认证，更加稳定、安全、可靠，性能也比社区版好。社区版没有实时图形监控器支持，没有任何技术支持服务，而企业版提供了企业级实时图形监控器，有完善的技术支持服务。
MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。社区版可以自由从网上下载获得，没有实时图形监控器支持，没有任何技术支持服务。企业版是收费的，而且不能下载，但是该版本拥有完善的技术支持服务。
二、Mysql安装和使用(以Centos7为例)
1、查看linux版本：cat /proc/version
2、下载对应的mysql版本包：比如---mysql-8.0.25-1.el7.x86_64.rpm-bundle，官网去搜索不同的linux系统对应的版本，rpm-bundle版本功能最全。
3、传入linuxl系统文件夹local中。（也可以在linux系统中直接通过网址下载到该文件夹-定位到该文件夹后运行命令wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar，还可以更新源后直接运行sudo apt-get install mysql-client mysql-server命令安装）。
4、清理当前已安装的相关包：
rpm -qa|grep -i mysql 或 rpm -qa|grep -i mariadb
5、卸载相关包：
rpm -e --nodeps mysql-community-server-8.0.22-1.el7.x86_64
6、删除相关目录：
find / -name mysql
rm -rf /var/lib/mysql /var/lib/mysql/mysql /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /var/log/mysqld.log
7、解压已下载的包：
tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
8、使用yum安装先行依赖包：
yum -y install libaio
yum -y install perl
yum -y install net-tools
9、按下面顺序安装解压后的rpm包：
rpm -ivh mysql-community-common-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.22-1.el7.x86_64.rpm
10、查看和启动服务：
systemctl status mysqld
systemctl start mysqld
11、获取一个临时密码(必须先启动服务)：
grep "temporary password" /var/log/mysqld.log
12、使用临时密码登录并设置新密码：
mysql -uroot -p
13若需使用简单密码（8.0不支持），执行以下命令：
set global validate_password_policy=0;
set global validate_password_length=1;
14、设置密码：
select alter user 'root'@'localhost' identified by '123456'; flush privileges;
15、创建远程登录账号：
#MySQL 5.7使用方式
grant replication slave on *.* to repl@'192.168.73.%' identified by '123456';flush privileges;
#MySQL 8.0使用方式
create user 'remoteuser'@'192.168.73.%' identified by '123456'; //remoteuser表示用户名，@后面为可以访问的ip，%表示所有ip都可访问，也可设置localhost即为本机访问
grant all privileges on *.* to repl@'192.168.73.%' with grant option;flush privileges; //设置权限
16、旧密码插件（远程连接出现 Authentication plugin 'caching_sha2_password' cannot be loaded时 需要执行以下修改命令）：
ALTER USER repl@'192.168.73.%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER repl@'192.168.73.%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;
17、开启远程访问端口：
运行 vi /etc/mysql/mysql.conf.d/mysqld.cnf ，编辑文件my.cnf，将bind-address = 127.0.0.1注释掉或者设置为接受的访问地址。
三、连接使用数据库管理软件连接Mysql
常用的数据库工具有navicat、phpMyAdmin、DBeaver、webcatEE、sqlserver management studio，公司使用注意版权问题，我们使用DBeaver免费的。
四、Mysql安装目录解读
- bin： 包含客户端程序和mysqld等二进制可执行文件。
- docs： 包含ChangeLog、INFO_BIN、INFO_SRC等信息。
- include： 包含（头） 文件的目录。
- lib： 可动态加载的so库文件目录。
- man： 包含man1、 man8。
- share： 包含MySQL初始化的一些SQL脚本以及错误代码、 本地化语言文件等。
- support-files： 包含单实例启停脚本mysql.server和多实例启停脚本 mysqld_multi.server等。
- mysql-files：自定义的数据文件目录。
- data：登录数据库后，可使用
SHOW GLOBAL VARIABLES LIKE "%Datadir%";命令查看 Data 目录位置。Data 目录中用于放置一些日志文件以及数据库。我们创建和保存的数据都存在这个目录里。
五、Mysql配置文件解读
在windows中mysql的配置文件为my.ini，在linux中为my.cnf。my.ini 是 MySQL 默认使用的配置文件，其它的配置文件都是适合不同数据库的配置文件的模板，例如：
- my-huge.ini：适合超大型数据库的配置文件。
- my-large.ini：适合大型数据库的配置文件。
- my-medium.ini：适合中型数据库的配置文件。
- my-small.ini：适合小型数据库的配置文件。
- my-template.ini：是配置文件的模板，MySQL 配置向导将该配置文件中选择项写入到 my.ini 文件。
- my-innodb-heavy-4G.ini：表示该配置文件只对于 InnoDB 存储引擎有效，而且服务器的内存不能小于 4GB。
常用配置代码解析如下：
[client]
port = 3309
socket = /home/mysql/mysql/tmp/mysql.sock
[mysqld]
!include /home/mysql/mysql/etc/mysqld.cnf #包含的配置文件 ，把用户名，密码文件单独存放
port = 3309
socket = /home/mysql/mysql/tmp/mysql.sock
pid-file = /longxibendi/mysql/mysql/var/mysql.pid
basedir = /home/mysql/mysql/ #指定mysql的安装位置
datadir = /longxibendi/mysql/mysql/var/ #指定数据的存放目录
# tmp dir settings
tmpdir = /home/mysql/mysql/tmp/
slave-load-tmpdir = /home/mysql/mysql/tmp/
#当slave 执行 load data infile 时用
#language = /home/mysql/mysql/share/mysql/english/
character-sets-dir = /home/mysql/mysql/share/mysql/charsets/
# skip options
skip-name-resolve #grant 时，必须使用ip不能使用主机名
skip-symbolic-links #不能使用连接文件
skip-external-locking #不使用系统锁定，要使用myisamchk,必须关闭服务器
skip-slave-start #启动mysql,不启动复制
#sysdate-is-now
# res settings
back_log = 50 #接受队列，对于没建立tcp连接的请求队列放入缓存中，队列大小为back_log，受限制与OS参数
max_connections = 1000 #最大并发连接数 ，增大该值需要相应增加允许打开的文件描述符数
max_connect_errors = 10000 #如果某个用户发起的连接error超过该数值，则该用户的下次连接将被阻塞，直到管理员执行flush hosts ; 命令；防止黑客
#open_files_limit = 10240
connect-timeout = 10 #连接超时之前的最大秒数,在Linux平台上，该超时也用作等待服务器首次回应的时间
wait-timeout = 28800 #等待关闭连接的时间
interactive-timeout = 28800 #关闭连接之前，允许interactive_timeout（取代了wait_timeout）秒的不活动时间。客户端的会话wait_timeout变量被设为会话interactive_timeout变量的值。
slave-net-timeout = 600 #从服务器也能够处理网络连接中断。但是，只有从服务器超过slave_net_timeout秒没有从主服务器收到数据才通知网络中断
net_read_timeout = 30 #从服务器读取信息的超时
net_write_timeout = 60 #从服务器写入信息的超时
net_retry_count = 10 #如果某个通信端口的读操作中断了，在放弃前重试多次
net_buffer_length = 16384 #包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节
max_allowed_packet = 64M #
#table_cache = 512 #所有线程打开的表的数目。增大该值可以增加mysqld需要的文件描述符的数量
thread_stack = 192K #每个线程的堆栈大小
thread_cache_size = 20 #线程缓存
thread_concurrency = 8 #同时运行的线程的数据 此处最好为CPU个数两倍。本机配置为CPU的个数
# qcache settings
query_cache_size = 256M #查询缓存大小
query_cache_limit = 2M #不缓存查询大于该值的结果
query_cache_min_res_unit = 2K #查询缓存分配的最小块大小
# default settings
# time zone
default-time-zone = system #服务器时区
character-set-server = utf8 #server级别字符集
default-storage-engine = InnoDB #默认存储
# tmp & heap
tmp_table_size = 512M #临时表大小，如果超过该值，则结果放到磁盘中
max_heap_table_size = 512M #该变量设置MEMORY (HEAP)表可以增长到的最大空间大小
log-bin = mysql-bin #这些路径相对于datadir
log-bin-index = mysql-bin.index
relayrelay-log = relay-log
relayrelay_log_index = relay-log.index
# warning & error log
log-warnings = 1
log-error = /home/mysql/mysql/log/mysql.err
log_output = FILE #参数log_output指定了慢查询输出的格式，默认为FILE，你可以将它设为TABLE，然后就可以查询mysql架构下的slow_log表了
# slow query log
slow_query_log = 1
long-query-time = 1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /home/mysql/mysql/log/slow.log
#log-queries-not-using-indexes
#log-slow-slave-statements
general_log = 1
general_log_file = /home/mysql/mysql/log/mysql.log
max_binlog_size = 1G
max_relay_log_size = 1G
# if use auto-ex, set to 0
relay-log-purge = 1 #当不用中继日志时，删除他们。这个操作有SQL线程完成
# max binlog keeps days
expire_logs_days = 30 #超过30天的binlog删除
binlog_cache_size = 1M #session级别
# replication
replicate-wild-ignore-table = mysql.% #复制时忽略数据库及表
replicate-wild-ignore-table = test.% #复制时忽略数据库及表
# slave_skip_errors=all
key_buffer_size = 256M #myisam索引buffer,只有key没有data
sort_buffer_size = 2M #排序buffer大小；线程级别
read_buffer_size = 2M #以全表扫描(Sequential Scan)方式扫描数据的buffer大小 ；线程级别
join_buffer_size = 8M # join buffer 大小;线程级别
read_rnd_buffer_size = 8M #MyISAM以索引扫描(Random Scan)方式扫描数据的buffer大小 ；线程级别
bulk_insert_buffer_size = 64M #MyISAM 用在块插入优化中的树缓冲区的大小。注释：这是一个per thread的限制
myisam_sort_buffer_size = 64M #MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_max_sort_file_size = 10G #MyISAM 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出.重建MyISAM索引(在REPAIR TABLE、ALTER TABLE或LOAD DATA INFILE过程中)时，允许MySQL使用的临时文件的最大空间大小。如果文件的大小超过该值，则使用键值缓存创建索引，要慢得多。该值的单位为字节
myisam_repair_threads = 1 #如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)
myisam_recover = 64K#允许的GROUP_CONCAT()函数结果的最大长度
transaction_isolation = REPEATABLE-READ
innodb_file_per_table
#innodb_status_file = 1
#innodb_open_files = 2048
innodb_additional_mem_pool_size = 100M #帧缓存的控制对象需要从此处申请缓存，所以该值与innodb_buffer_pool对应
innodb_buffer_pool_size = 2G #包括数据页、索引页、插入缓存、锁信息、自适应哈希所以、数据字典信息
innodb_data_home_dir = /longxibendi/mysql/mysql/var/
#innodb_data_file_path = ibdata1:1G:autoextend
innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend #表空间
innodb_file_io_threads = 4 #io线程数
innodb_thread_concurrency = 16 #InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制
innodb_flush_log_at_trx_commit = 1 #每次commit 日志缓存中的数据刷到磁盘中
innodb_log_buffer_size = 8M #事物日志缓存
innodb_log_file_size = 500M #事物日志大小
#innodb_log_file_size =100M
innodb_log_files_in_group = 2 #两组事物日志
innodb_log_group_home_dir = /longxibendi/mysql/mysql/var/#日志组
innodb_max_dirty_pages_pct = 90 #innodb主线程刷新缓存池中的数据，使脏数据比例小于90%
innodb_lock_wait_timeout = 50 #InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的 锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
#innodb_flush_method = O_DSYNC
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
disable-auto-rehash #允许通过TAB键提示
default-character-set = utf8
connect-timeout = 3
六、Mysql系统数据库
Mysql安装完成后会生成一个系统数据库mysql、information_schema、performance_schema、sys，作用为：
| 数据库 | 作用 |
| mysql | 存储MySQL服务器正常运行所需要的各种信息 （时区、主从、用户、权限、日志等） |
| information_schema | 提供访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图 |
七、Mysql常用的管理操作命令
| 命令格式 | 说明 |
| grep "temporary password" /var/log/mysqld.log | 在初次安装mysql后使用，该命令不是mysql自带的命令，这是linux系统查看文本的命令 |
| mysql -h 主机地址 -u 用户名 －p 用户密码 | 使用指定的账号密码登录MySQL，进入目录mysqlbin，键入命令mysql -u root -p，回车后提示你输密码，输入密码后完成登录；登录成功后显示 mysql> 提示符。 |
| exit | 退出已登录的MySQL,断开cmd等控制窗口到mysql的连接。 |
| use mysql | 切换数据库 |
| select host,user from user | 查看用户信息 |
| create user`test`@`*` identified by '123456' | 创建用户并设置密码，@前为用户名，@后为限制用户的登录ip正则表达式，*表示所有地址。 |
| rename user feng to newuser | 修改用户名，mysql5之后可以使用，之前需要使用update 更新user表 |
| drop user newuser | 删除指定用户，mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限 |
| set password for zx_root =password('xxxxxx') | 更改指定用户密码 |
| update mysql.user set password=password('xxxx') where user='otheruser' | 更改指定用户密码 |
| show grants for zx_root | 查看用户权限 |
| grant all privileges on *.* to remoteuser | 赋予用户所有权限 |
| revoke select on dmc_db.* from zx_root | 回收权限，如果权限不存在会报错 |
| flush privileges | 立即看到结果执行的命令,如果以上命令执行后需要立即在控制台显示结果，则需要附加该指令。 |
| show variables like 'general_log' | 查看general_log的状态是开启还是关闭，以及所属账号的general_log文件存放的目录。 |
| set global general_log=on | 设置general_log的值，ON或者OFF，表示是否开启日志。(正式上线的应用请关闭日志记录功能) |
| show variables like 'log_output' | 查看日志的输出类型，分为TABLE、FILE，TABL表示日志记录在mysql.general_log表中(不推荐)，FILE表示存于文件中。 |
| selsect * from mysql.general_log | 查询操作日志，当log_output='TABLE'时才会存入该表。 |
| show variables like 'general_log_file' | 查看日志文件的保存路径 |
| set global general_log_file='/tmp/general.log' | 配置当log_output='FILE'时,存储日志的文件的路径。 |
八、结构化查询语言(SQL语言)
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
在编写数据语句时，我们应该遵循如下几大规范：
- 关键字与函数名全部大写。
- 数据库名称、表名称、字段名称全部小写。
- SQL语句必须以分号结尾。
SQL语言从功能上可以分为六个部分，定义如下：
| 定义 | 说明 |
| 数据定义语言(DDL) | 其语句包括动词CREATE、ALTER和DROP。在数据库中创建新表或修改、删除表 |
| 数据查询语言(DQL) | 其语句包括动词SELECT，也称为“数据检索语句”，用以从表中获得数据，通常结合关键字WHERE，ORDER BY，GROUP BY和HAVING一起使用 |
| 数据操作语言(DML) | 其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除数据行记录 |
| 数据控制语言(DCL) | 其语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问 |
| 指针控制语言(CCL) | 规定SQL语句在宿主语言的程序中的使用的规则，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作 |
| 事务控制语言(TCL) | 为了确保被DML语句影响的表要么都成功，要么都失败(DML操作的原子性)，包括COMMIT、SAVEPOINT和ROLLBACK命令 |
九、MySql字符集
数据库字符集指的是数据的存储方式，常用的字符集有GB2312、GBK、UTF8，推荐使用UTF8方式，它支持的语言更多。MySQL中的“utf8”只支持每个字符最多三个字节，而真正的UTF-8是每个字符最多四个字节，因此在mysql5.5.3+中，增加了一个utf8mb4(四个字节)类型，这个才是mysql真正的utf8，而旧版本的mysql的utf8模式的类型为utf8mb3(三个字节)。
总之：如果你在使用MySQL或MariaDB新建数据时，不要用“utf8”编码，改用“utf8mb4”。如果你已经使用了“utf8”编码，你应该将现有数据库的字符编码从“utf8”转成“utf8mb4”。
十、数据库表的字段类型
| 分类 | 类型/说明/存储大小 | |||||||||||||||||||||||||||
| 数值 |
|
|||||||||||||||||||||||||||
| 日期时间 |
|
|||||||||||||||||||||||||||
| 字符(串) |
|
|||||||||||||||||||||||||||
| 二进制串 |
|
十一、MySQL常见的资源管理、操作、查询命令
| 分类 | SQL语句 | 说明 |
| 数据库操作 | create database 数据库名; | 创建数据库 |
| drop database 数据库名; | 删除数据库 | |
| use 数据库名; | 选择数据库 | |
| 表操作
|
create table table_name (column_name column_type ); | 创建表，如存在则报错，通用方式 |
|
CREATE TABLE IF NOT EXISTS `runoob_tbl` ( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` )， INDEX [indexName] (runoob_title) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
如果表不存在则创建，存在则跳过，参数说明如下：
|
|
| DROP TABLE table_name ; | 删除指定表 | |
| ALTER TABLE testalter_tbl DROP fieldname; | 删除表的字段fieldname，如果数据表中只剩余一个字段则无法使用DROP来删除字段。 | |
| ALTER TABLE testalter_tbl ADD fieldname INT; | 数据表中添加列fieldname，列类型为INT。 | |
| ALTER TABLE testalter_tbl ADD i INT AFTER c; | 如果需要指定新增字段i的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。 | |
| ALTER TABLE testalter_tbl MODIFY c CHAR(10); | 修改表字段的类型，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)。 | |
| ALTER TABLE testalter_tbl CHANGE i j BIGINT; | 修改表的字段名和类型，把字段名为i的字段修改为名为j ，并设置类型为BIGINT。 | |
| ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100; | 修改表的字段j类型，并且指定默认值100，同时不允许为NULL。 | |
| ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; | 修改表字段i的默认值为1000。 | |
| ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; | 删除字段的默认值。 | |
| ALTER TABLE testalter_tbl ENGINE = MYISAM; | 修改数据表类型。 | |
| ALTER TABLE testalter_tbl RENAME TO alter_tbl; | 修改数据表 testalter_tbl 重命名为 alter_tbl。 | |
| ALTER TABLE testalter_tbl DROP foreign key keyName; | 删除表的外键keyName。 | |
| SHOW CREATE TABLE runoob_tbl \G; | 获取数据表的完整结构。 | |
| 视图操作 | CREATE VIEW view_name AS SELECT * FROM t_dept; | 创建一个视图，视图的本质就是保存的一个select语句作为虚表，我们操作向查询表一个从视图中获取数据，单表视图可以添加、更新和删除，多表视图不允许添加、更新和删除 |
| SHOW CREATE VIEW view_dept; | 查看视图定义信息 | |
| DESCRIBE | DESC viewname; | 查看视图设计信息 | |
| DROP VIEW view_name 【,view_name】; | 通过DROP VIEW语句可以一次删除一个或多个视图。 | |
| ALTER VIEW viewname AS SELECT * FROM t_dept; | ALTER语句修改视图 | |
| 存储过程 | CREATE PROCEDURE procedure_name ([parameters[,...]]) begin ...sql语句.. end | 创建存储过程，存储过程就是具有名字的一段代码，用来完成一个特定的功能 |
| call procedure_name (); | 调用存储过程 | |
| show procedure status; | 查看存储过程的状态信息 | |
| drop procedure procedure_name; | 删除存储过程 | |
| 索引操作 | CREATE INDEX indexName ON table_name (column_name); | 为指定表的指定列创建索引。 |
| ALTER table tableName ADD INDEX indexName(columnName); | 为指定表的指定列添加索引。 | |
| DROP INDEX [indexName] ON tableName ; | 删除指定表的指定索引。 | |
| ALTER TABLE tbl_name ADD UNIQUE index_name (column_list); | 为指定表的指定列创建索引。 | |
| ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); | 为指定表的指定列创建全文索引。 | |
| ALTER TABLE testalter_tbl DROP PRIMARY KEY; | 删除指定表的指定索引。 | |
| 临时表使用 |
CREATE TEMPORARY TABLE SalesSummary ( product_name VARCHAR(50) NOT NULL , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); |
使用TEMPORARY 关键字创建临时表，临时表的使用与真实表一样。 MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。 |
| 元数据查看 | SELECT VERSION( ) | 服务器版本信息 |
| SELECT DATABASE( ) | 当前数据库名 (或者返回空) | |
| SELECT USER( ) | 当前用户名 | |
| SHOW STATUS | 服务器状态 | |
| SHOW VARIABLES | 服务器配置变量 | |
| LAST_INSERT_ID() | 获取最后的插入表中的自增列的值。 |
十二、MySQL 函数
MySQL 有很多内置的函数，以下列出了这些函数的说明。
MySQL 字符串函数：
| 函数 | 描述 | 实例 |
|---|---|---|
| ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 |
返回 CustomerName 字段第一个字母的 ASCII 码： SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; |
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 |
返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString; |
| CHARACTER_LENGTH(s) | 返回字符串 s 的字符数，等同于 CHAR_LENGTH(s) |
返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString; |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 |
合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString; |
| CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 |
合并多个字符串，并添加分隔符： SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; |
| FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 |
返回字符串 c 在列表值中的位置： SELECT FIELD("c", "a", "b", "c", "d", "e"); |
| FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 |
返回字符串 c 在指定字符串中的位置： SELECT FIND_IN_SET("c", "a,b,c,d,e"); |
| FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 |
格式化数字 "#,###.##" 形式： SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56 |
| INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 |
从字符串第一个位置开始的 6 个字符替换为 runoob： SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com |
| LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 |
获取 b 在字符串 abc 中的位置： SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置： SELECT LOCATE('b', 'abc') -- 2 |
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 |
字符串 RUNOOB 转换为小写： SELECT LCASE('RUNOOB') -- runoob |
| LEFT(s,n) | 返回字符串 s 的前 n 个字符 |
返回字符串 runoob 中的前两个字符： SELECT LEFT('runoob',2) -- ru |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 |
字符串 RUNOOB 转换为小写： SELECT LOWER('RUNOOB') -- runoob |
| LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len |
将字符串 xx 填充到 abc 字符串的开始处： SELECT LPAD('abc',5,'xx') -- xxabc |
| LTRIM(s) | 去掉字符串 s 开始处的空格 |
去掉字符串 RUNOOB开始处的空格： SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB |
| MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 |
返回字符串 abc 中 b 的位置： SELECT POSITION('b' in 'abc') -- 2 |
| REPEAT(s,n) | 将字符串 s 重复 n 次 |
将字符串 runoob 重复三次： SELECT REPEAT('runoob',3) -- runoobrunoobrunoob |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 |
将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE('abc','a','x') --xbc |
| REVERSE(s) | 将字符串s的顺序反过来 |
将字符串 abc 的顺序反过来： SELECT REVERSE('abc') -- cba |
| RIGHT(s,n) | 返回字符串 s 的后 n 个字符 |
返回字符串 runoob 的后两个字符： SELECT RIGHT('runoob',2) -- ob |
| RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len |
将字符串 xx 填充到 abc 字符串的结尾处： SELECT RPAD('abc',5,'xx') -- abcxx |
| RTRIM(s) | 去掉字符串 s 结尾处的空格 |
去掉字符串 RUNOOB 的末尾空格： SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB |
| SPACE(n) | 返回 n 个空格 |
返回 10 个空格： SELECT SPACE(10); |
| STRCMP(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 |
比较字符串： SELECT STRCMP("runoob", "runoob"); -- 0 |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length) |
从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 |
SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 |
去掉字符串 RUNOOB 的首尾空格： SELECT TRIM(' RUNOOB ') AS TrimmedString; |
| UCASE(s) | 将字符串转换为大写 |
将字符串 runoob 转换为大写： SELECT UCASE("runoob"); -- RUNOOB |
| UPPER(s) | 将字符串转换为大写 |
将字符串 runoob 转换为大写： SELECT UPPER("runoob"); -- RUNOOB |
MySQL 数字函数：
| 函数名 | 描述 | 实例 |
|---|---|---|
| ABS(x) | 返回 x 的绝对值 |
返回 -1 的绝对值： SELECT ABS(-1) -- 返回1 |
| ACOS(x) | 求 x 的反余弦值（单位为弧度），x 为一个数值 | SELECT ACOS(0.25); |
| ASIN(x) | 求反正弦值（单位为弧度），x 为一个数值 | SELECT ASIN(0.25); |
| ATAN(x) | 求反正切值（单位为弧度），x 为一个数值 | SELECT ATAN(2.5); |
| ATAN2(n, m) | 求反正切值（单位为弧度） | SELECT ATAN2(-0.8, 2); |
| AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 |
返回 Products 表中Price 字段的平均值： SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) -- 返回2 |
| CEILING(x) | 返回大于或等于 x 的最小整数 | SELECT CEILING(1.5); -- 返回2 |
| COS(x) | 求余弦值(参数是弧度) | SELECT COS(2); |
| COT(x) | 求余切值(参数是弧度) | SELECT COT(6); |
| COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 |
返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| DEGREES(x) | 将弧度转换为角度 | SELECT DEGREES(3.1415926535898) -- 180 |
| n DIV m | 整除，n 为被除数，m 为除数 |
计算 10 除于 5： SELECT 10 DIV 5; -- 2 |
| EXP(x) | 返回 e 的 x 次方 |
计算 e 的三次方： SELECT EXP(3) -- 20.085536923188 |
| FLOOR(x) | 返回小于或等于 x 的最大整数 |
小于或等于 1.5 的整数： SELECT FLOOR(1.5) -- 返回1 |
| GREATEST(expr1, expr2, expr3, ...) | 返回列表中的最大值 |
返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值： SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob |
| LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 |
返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值： SELECT LEAST("Google", "Runoob", "Apple"); -- Apple |
| LN | 返回数字的自然对数，以 e 为底。 |
返回 2 的自然对数： SELECT LN(2); -- 0.6931471805599453 |
| LOG(x) 或 LOG(base, x) | 返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。 | SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2 |
| LOG10(x) | 返回以 10 为底的对数 | SELECT LOG10(100) -- 2 |
| LOG2(x) | 返回以 2 为底的对数 |
返回以 2 为底 6 的对数： SELECT LOG2(6); -- 2.584962500721156 |
| MAX(expression) | 返回字段 expression 中的最大值 |
返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) | 返回字段 expression 中的最小值 |
返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS MinPrice FROM Products; |
| MOD(x,y) | 返回 x 除以 y 以后的余数 |
5 除于 2 的余数： SELECT MOD(5,2) -- 1 |
| PI() | 返回圆周率(3.141593） | SELECT PI() --3.141593 |
| POW(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： SELECT POW(2,3) -- 8 |
| POWER(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： SELECT POWER(2,3) -- 8 |
| RADIANS(x) | 将角度转换为弧度 |
180 度转换为弧度： SELECT RADIANS(180) -- 3.1415926535898 |
| RAND() | 返回 0 到 1 的随机数 | SELECT RAND() --0.93099315644334 |
| ROUND(x) | 返回离 x 最近的整数 | SELECT ROUND(1.23456) --1 |
| SIGN(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 | SELECT SIGN(-10) -- (-1) |
| SIN(x) | 求正弦值(参数是弧度) | SELECT SIN(RADIANS(30)) -- 0.5 |
| SQRT(x) | 返回x的平方根 |
25 的平方根： SELECT SQRT(25) -- 5 |
| SUM(expression) | 返回指定字段的总和 |
计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TAN(x) | 求正切值(参数是弧度) | SELECT TAN(1.75); -- -5.52037992250933 |
| TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |
MySQL 日期函数：
| 函数名 | 描述 | 实例 |
|---|---|---|
| ADDDATE(d,n) | 计算起始日期 d 加上 n 天的日期 |
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); |
| ADDTIME(t,n) | n 是一个时间表达式，时间 t 加上时间表达式 n |
加 5 秒： SELECT ADDTIME('2011-11-11 11:11:11', 5); SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); |
| CURDATE() | 返回当前日期 |
SELECT CURDATE(); |
| CURRENT_DATE() | 返回当前日期 |
SELECT CURRENT_DATE(); |
| CURRENT_TIME | 返回当前时间 |
SELECT CURRENT_TIME(); |
| CURRENT_TIMESTAMP() | 返回当前日期和时间 |
SELECT CURRENT_TIMESTAMP() |
| CURTIME() | 返回当前时间 |
SELECT CURTIME(); |
| DATE() | 从日期或日期时间表达式中提取日期值 |
SELECT DATE("2017-06-15"); |
| DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 |
SELECT DATEDIFF('2001-01-01','2001-02-02') |
| DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期，type 值可以是：
|
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH); |
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d |
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') |
| DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 |
Orders 表中 OrderDate 字段减去 2 天： SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate |
| DAY(d) | 返回日期值 d 的日期部分 |
SELECT DAY("2017-06-15"); |
| DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday |
SELECT DAYNAME('2011-11-11 11:11:11') |
| DAYOFMONTH(d) | 计算日期 d 是本月的第几天 |
SELECT DAYOFMONTH('2011-11-11 11:11:11') |
| DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 |
SELECT DAYOFWEEK('2011-11-11 11:11:11') |
| DAYOFYEAR(d) | 计算日期 d 是本年的第几天 |
SELECT DAYOFYEAR('2011-11-11 11:11:11') |
| EXTRACT(type FROM d) | 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为：
|
SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') |
| FROM_DAYS(n) | 计算从 0000 年 1 月 1 日开始 n 天后的日期 |
SELECT FROM_DAYS(1111) |
| HOUR(t) | 返回 t 中的小时值 |
SELECT HOUR('1:2:3') |
| LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 |
SELECT LAST_DAY("2017-06-20"); |
| LOCALTIME() | 返回当前日期和时间 |
SELECT LOCALTIME() |
| LOCALTIMESTAMP() | 返回当前日期和时间 |
SELECT LOCALTIMESTAMP() |
| MAKEDATE(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 |
SELECT MAKEDATE(2017, 3); |
| MAKETIME(hour, minute, second) | 组合时间，参数分别为小时、分钟、秒 |
SELECT MAKETIME(11, 35, 4); |
| MICROSECOND(date) | 返回日期参数所对应的微秒数 |
SELECT MICROSECOND("2017-06-20 09:34:00.000023"); |
| MINUTE(t) | 返回 t 中的分钟值 |
SELECT MINUTE('1:2:3') |
| MONTHNAME(d) | 返回日期当中的月份名称，如 November |
SELECT MONTHNAME('2011-11-11 11:11:11') |
| MONTH(d) | 返回日期d中的月份值，1 到 12 |
SELECT MONTH('2011-11-11 11:11:11') |
| NOW() | 返回当前日期和时间 |
SELECT NOW() |
| PERIOD_ADD(period, number) | 为 年-月 组合日期添加一个时段 |
SELECT PERIOD_ADD(201703, 5); |
| PERIOD_DIFF(period1, period2) | 返回两个时段之间的月份差值 |
SELECT PERIOD_DIFF(201710, 201703); |
| QUARTER(d) | 返回日期d是第几季节，返回 1 到 4 |
SELECT QUARTER('2011-11-11 11:11:11') |
| SECOND(t) | 返回 t 中的秒钟值 |
SELECT SECOND('1:2:3') |
| SEC_TO_TIME(s) | 将以秒为单位的时间 s 转换为时分秒的格式 |
SELECT SEC_TO_TIME(4320) |
| STR_TO_DATE(string, format_mask) | 将字符串转变为日期 |
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); |
| SUBDATE(d,n) | 日期 d 减去 n 天后的日期 |
SELECT SUBDATE('2011-11-11 11:11:11', 1) |
| SUBTIME(t,n) | 时间 t 减去 n 秒的时间 |
SELECT SUBTIME('2011-11-11 11:11:11', 5) |
| SYSDATE() | 返回当前日期和时间 |
SELECT SYSDATE() |
| TIME(expression) | 提取传入表达式的时间部分 |
SELECT TIME("19:30:10"); |
| TIME_FORMAT(t,f) | 按表达式 f 的要求显示时间 t |
SELECT TIME_FORMAT('11:11:11','%r') |
| TIME_TO_SEC(t) | 将时间 t 转换为秒 |
SELECT TIME_TO_SEC('1:12:00') |
| TIMEDIFF(time1, time2) | 计算时间差值 |
mysql> SELECT TIMEDIFF("13:10:11", "13:10:10"); |
| TIMESTAMP(expression, interval) | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 |
mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11"); |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 |
mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天 |
| TO_DAYS(d) | 计算日期 d 距离 0000 年 1 月 1 日的天数 |
SELECT TO_DAYS('0001-01-01 01:01:01') |
| WEEK(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
SELECT WEEK('2011-11-11 11:11:11') |
| WEEKDAY(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 |
SELECT WEEKDAY("2017-06-15"); |
| WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
SELECT WEEKOFYEAR('2011-11-11 11:11:11') |
| YEAR(d) | 返回年份 |
SELECT YEAR("2017-06-15"); |
| YEARWEEK(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 |
SELECT YEARWEEK("2017-06-15"); |
MySQL 高级函数：
| 函数名 | 描述 | 实例 |
|---|---|---|
| BIN(x) | 返回 x 的二进制编码 |
15 的 2 进制编码: SELECT BIN(15); -- 1111 |
| BINARY(s) | 将字符串 s 转换为二进制字符串 |
SELECT BINARY "RUNOOB"; |
| CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END | CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 |
SELECT CASE |
| CAST(x AS type) | 转换数据类型 |
字符串日期转换为日期： SELECT CAST("2017-08-29" AS DATE); |
| COALESCE(expr1, expr2, ...., expr_n) | 返回参数中的第一个非空表达式（从左向右） |
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); |
| CONNECTION_ID() | 返回唯一的连接 ID |
SELECT CONNECTION_ID(); |
| CONV(x,f1,f2) | 返回 f1 进制数变成 f2 进制数 |
SELECT CONV(15, 10, 2); |
| CONVERT(s USING cs) | 函数将字符串 s 的字符集变成 cs |
SELECT CHARSET('ABC') SELECT CHARSET(CONVERT('ABC' USING gbk)) |
| CURRENT_USER() | 返回当前用户 |
SELECT CURRENT_USER(); |
| DATABASE() | 返回当前数据库名 |
SELECT DATABASE(); |
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 |
SELECT IF(1 > 0,'正确','错误') |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 |
SELECT IFNULL(null,'Hello Word') |
| ISNULL(expression) | 判断表达式是否为 NULL |
SELECT ISNULL(NULL); |
| LAST_INSERT_ID() | 返回最近生成的 AUTO_INCREMENT 值 |
SELECT LAST_INSERT_ID(); |
| NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 |
SELECT NULLIF(25, 25); |
| SESSION_USER() | 返回当前用户 |
SELECT SESSION_USER(); |
| SYSTEM_USER() | 返回当前用户 |
SELECT SYSTEM_USER(); |
| USER() | 返回当前用户 |
SELECT USER(); |
| VERSION() | 返回数据库的版本号 |
SELECT VERSION() |
十三、MySQL 运算符
算数运算符：MySQL 支持的算术运算符包括+ - * / %，在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。
逻辑运算符：逻辑运算符包括NOT(取反) ! (取反) AND(两个都有) OR(至少一个) XOR(有且只有一个) 用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。
位运算符：运算符包括&(按位与) |(按位或) ^(按位异或) !(取反) <<(左移) >>右移，是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。
比较运算符：SELECT 语句中的条件语句经常要使用比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。
| 符号 | 描述 | 备注 |
|---|---|---|
| = | 等于 | |
| <>, != | 不等于 | |
| > | 大于 | |
| < | 小于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| BETWEEN | 在两值之间 | >=min&&<=max |
| NOT BETWEEN | 不在两值之间 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 | |
| REGEXP 或 RLIKE | 正则式匹配 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 |
运算符的优先级如下：1-14由低到高，依次运算，通常不确定优先级时，请使用括号包裹。
十四、MySQL 正则表达式
MySQL可以通过 LIKE ...% 来进行模糊匹配。MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。下表中的正则模式可应用于 REGEXP 操作符中：
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
十五、MySQL常见的数据行增删改查命令
| 语法格式 | 说明 |
| INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); | INSERT INTO ... VALUES 用于向指定表中插入数据行集合 |
| SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M] |
通用的数据查询语法，其中：
|
| UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] |
根据where条件更新指定表的字段值，其中：
|
| DELETE FROM table_name [WHERE Clause] |
从指定表中删除数据，请注意删除数据和删除表的区别，其中：
|
| Insert into Table2(field1,field2,...) select value1,value2,... from Table1 | 将查询结果集插入到另一张表中 |
|
SELECT expression1, expression2, ... expression_n |
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。其中：
|
|
SELECT field1, field2,...fieldN FROM table_name1, table_name2... |
使用 ORDER BY 子句将查询数据排序后再返回数据，其中：
|
|
SELECT column_name, function(column_name) |
GROUP BY 语句用于对结果集进行按列分组，其中：
|
| SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP; | 分组后进行统计，并且给统计行的NULL列名设置一个 '总数'，coalesce(a,b,c)函数用来设置一个可以取代 NULL 的名称。 |
|
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
|
JOIN 用于在两个或多个表中查询数据，JOIN 按照功能大致分为如下三类：
|
十六、MySQL事务
事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句。
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
mysql中使用事务的关键字为begin(开始事务)、commit(提交事务)、rollback(回滚事务)，事务控制语句语法格式如下：
begin; # 开始事务 insert into runoob_transaction_test value(5); #begin与commit之间的所有语句成为一个事务，这些语句的执行要么都成功，要么都失败！ commit; # 提交事务 begin; # 开始事务 insert into runoob_transaction_test values(7); #默认情况下，一条语句就形成一个事务， 事务管理的是insert,update,delete操作，而不管理select操作。 rollback; # 回滚事务
十七、MySQL 数据导入导出和备份
MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。
- LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
- SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
- 输出不能是一个已存在的文件。防止文件数据被篡改。
- 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
- 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。
#导出查询结果到runoob.tx文本中 SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/runoob.txt'; #通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式 SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
除此以外，还可以使用mysqldump，它是 mysql 用于转存储数据库的实用程序，它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。
#将数据表 runoob_tbl 导出到 /tmp 目录中 $ mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl password ****** #导出 SQL 格式的数据到指定文件 $ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt password ****** #导出整个数据库的数据 $ mysqldump -u root -p RUNOOB > database_dump.txt password ****** #备份所有数据库 $ mysqldump -u root -p --all-databases > database_dump.txt password ****** #在源主机上执行以下命令，将数据表table_name 备份至 dump.txt 文件中，如果完整备份数据库，则无需使用特定的表名称 $ mysqldump -u root -p database_name table_name > dump.txt password ***** #将备份的数据库导入到MySQL服务器中 $ mysql -u root -p database_name < dump.txt password ***** #将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的 $ mysqldump -u root -p database_name | mysql -h other-host.com database_name
十八、Mysql变量、流程控制语句、自定义函数
函数是封装一段sql代码，完成一种特定的功能，返回结果，它与存储过程的区别：
- 函数有且只有一个返回值，而存储过程不能有返回值。
- 函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
- 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句。
- 存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
- 存储过程可以调用存储函数。但函数不能调用存储过程。
- 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
函数中的变量使用DECLARE关键字来定义，返回值使用RETURN返回，以下是创建一个函数的例子：
#如果函数存在，则删除 DROP FUNCTION IF EXISTS `FORMAT_DATE_TIME`; #创建一个日期格式化函数，参数为DATETIME类型 CREATE FUNCTION FORMAT_DATE_TIME(`datetimes` DATETIME) #定义函数的返回值类型 RETURNS VARCHAR(100) #begin和end之间为函数体，此处返回一个格式化后的日期字符串。如果只有一句函数体，可以省略begin、end BEGIN
DECLARE num DOUBLE; #定义变量，此外未使用
RETURN DATE_FORMAT(`datetimes`,'%Y年%m月%s日 %H时%i分%s秒'); END # 调用自定义函数FORMAT_DATE_TIME SELECT FORMAT_DATE_TIME(create_time) FROM users;
Mysql的函数体中同样存在类似其他变成语言的流程控制结构，比如分支结构、循环结构、循环结构等，语法格式 如下：
IF 表达式1 THEN 操作1 [ELSEIF 表达式2 THEN 操作2]…… [ELSE 操作N] END IF
CASE 表达式 WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) ... ELSE 结果n或语句n(如果是语句，需要加分号) END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
[loop_label:] LOOP ....循环执行的语句 END LOOP [loop_label]
[while_label:] WHILE 循环条件 DO ...循环体 END WHILE [while_label];
[repeat_label:] REPEAT ...循环体的语句 UNTIL 结束循环的条件表达式 END REPEAT [repeat_label]
以上语法格式中，CASE语句称为情况语句，LOOP语句、WHILE语句、REPEAT语句都是循环语句，不同的是：
- LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
- WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。
- REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
举例如下：
# DELIMITER是用来设置边界符的，这里使用$$作为分割符号，相当于分号； DELIMITER $$ DROP FUNCTION IF EXISTS hellow $$ CREATE FUNCTION hellow(s VARCHAR(255)) RETURNS varchar(255) BEGIN IF(ISNULL(s)) THEN RETURN '你好，无名氏'; ELSEIF CHAR_LENGTH(s)<4 THEN RETURN '你好，中国人'; ELSE RETURN '你好，外国人'; END IF; END $$ DELIMITER ; #恢复分隔符号；
DELIMITER $$ DROP FUNCTION IF EXISTS hellow $$ CREATE FUNCTION hellow(n INT) RETURNS text BEGIN DECLARE i INT DEFAULT 0; DECLARE s TEXT DEFAULT ''; myloop:LOOP SET i=i+1; SET s = CONCAT(s,'*'); IF i > n THEN LEAVE myloop; END IF; END LOOP myloop; RETURN s; END $$ DELIMITER ;
注意：DELIMITER的妙用，常情况下，MySQL命令行窗口中默认遇到分号;时判断为语句结束，立即执行分号';'前SQL语句，有时候我们并不想要立即执行，而是要求统一执行代码段，因此使用DELIMITER指定MySQL使用其他分隔符，待所有语句执行完毕后，再恢复;作为分割符。
#指定$$作为语句的分隔符号
DELIMITER $$
.....一系列的sql语句1; #由于在前面指定了分隔符为$$,因此在遇到;时语句不会立即执行
.....一系列的sql语句2;
.....一系列的sql语句3;
.....一系列的sql语句4; $$ #遇到指定的分隔符号$$，这些代码将会一并执行
#还原默认的分隔符号为；
DELIMITER ;
十九、mysqlbinlog工具
前一节讲到了mysqlbinlog的基本使用，它是mysql提供的用于管理mysql二进制日志的工具，其语法格式和参数选项介绍如下：
#语法格式
mysqlbinlog [options] logfile1 logfile2 ...
----------选项说明--------------
-d, --database=name 仅显示指定数据库的转储内容。
-o, --offset=# 跳过前N行的日志条目。
-r, --result-file=name 将输入的文本格式的文件转储到指定的文件。
-s, --short-form 使用简单格式。
--set-charset=name 在转储文件的开头增加'SET NAMES character_set'语句。
--start-datetime=name 转储日志的起始时间。
--stop-datetime=name 转储日志的截止时间。
-j, --start-position=# 转储日志的起始位置。
--stop-position=# 转储日志的截止位置。
-----------选项说明--------------
二十、mysqldump工具
mysqldump是mysql自带的逻辑备份命令行工具，备份原理是通过mysql协议连接到mysql服务器，将查询出的数据转换成对应的insert等sql语句，使用时再执行sql语句还原。其选项信息可通过mysqldump --help命令查看。语法格式如下：
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
------------------选项说明------------------------------
--host -h 服务器主机IP地址
--port -p 服务器mysql端口号
--user -u mysql用户名
--password -p mysql密码
--databases -B 指定备份数据库，包括create database语句
--all-databases -A 备份mysql服务器所有数据库，含create database语句
--compact 压缩模式，产生更少的输出
--comments -i 添加注释信息
--complete-insert -c 输出完成的插入语句
--no-data -d 只备份表结构，不备份数据
--no-create-info -t 只备份数据，不备份表结构
--flush-privileges 备份mysql或相关是需要使用
--quick -q 不缓存查询，直接输出，加快备份速度
--lock-tables -l 备份前，锁定所有数据库表
--no-create-db -n 禁止生成创建数据库语句
--force -f 当出现错误时仍然继续备份操作
--default-character-set 指定默认字符集
--add-locks 备份数据库时锁定数据库表
--verbose -v 备份时打印各阶段信息
------------------选项说明------------------------------
二十一、mysqlimport工具
mysqlimport是客户端数据导入，用来导入 mysqldump -t 导出的txt文件。
mysqlimport -u root -password [options] db_name textfile1 [textfi1e2...] ---------------选项说明------------------------------------- local：是在本地计算机中查找文本文件时使用的； dbname：指数据库名称； file：指定了文本文件的路径和名称； option：表示附加选项，如下： --fields-terminated-by=字符串： 设置字符串为字段的分隔符，默认值是“\t”； --fields-enclosed-by=字符： 设置字符来括上字段的值； --fields-optionally-enclosed-by=字符： 设置字符括上char、varchar、text等字符型字段； --fields-escaped-by=字符： 设置转义字符； --lines-terminated-by=字符串： 设置每行的结束符； --ignore-lines=n： 表示可以忽略前几行。 ---------------选项说明-------------------------------------
二十二、source工具指令
source命令用于导入sql文件，该命令需要先登录后才使用。
mysql> create database abc; # 创建数据库
mysql> use abc; # 使用已创建的数据库
mysql> set names utf8; # 设置编码
mysql> source /home/abc/abc.sql # 导入备份数据库
二十三、mysqladmin工具
mysqladmin是执行管理操作的客户端工具命令，可以使用它来检查服务器的配置和当前状态，以创建和删除数据库等。
mysqladmin [OPTIONS] command command....
-------------选项说明-----------------
--bind-address=name # 绑定的IP
-c, --count=# # 自动运行次数统计
-#, --debug[=#] # 编写调试日志。典型的调试选项字符串是d:t:o，文件名。默认值为d:t:o，/tmp/mysqladmin.trace
--debug-check # 当程序退出时打印一些调试信息
--debug-info # 当程序退出时打印调试信息和内存和CPU使用统计信息
-f, --force # 不要求确认删除数据库；使用多个命令，即使发生错误也继续。
-C, --compress # 在服务器/客户端协议中使用压缩
--character-sets-dir=name # 字符集的文件目录
--default-character-set=name # 设置默认字符集
-?, --help # 显示帮助并退出
-h, --host=name # 连接到的主机
-b, --no-beep # 关闭错误提示音
-p, --password[=name] # 连接到服务器设置的密码
-P, --port=# # 用于连接的端口号,位于优先顺序，my.cnf，$mysql_tcp_port，/etc/services，内置默认值(3306)
--protocol=name # 用于连接的协议(TCP、套接字、管道、内存)
-r, --relative # 与-i一起使用时，显示当前值和以前值之间的差异。当前仅适用于扩展状态
--secure-auth # 如果客户端使用旧的（4.1.1之前的）协议，则拒绝连接到服务器
-s, --silent # 无法连接到服务器时自动退出
-S, --socket=name # 用于连接的套接字文件
-i, --sleep=# # 重复执行命令，中间有一个睡眠时间。
--ssl-mode=name # SSL连接模式
--ssl-ca=name # PEM格式的CA文件
--ssl-capath=name # CA目录
--ssl-cert=name # PEM格式的X509证书
--ssl-cipher=name # 要使用的SSL密码
--ssl-key=name # PEM格式的X509密钥
--ssl-crl=name # 证书吊销列表
--ssl-crlpath=name # 证书吊销列表路径
--tls-version=name # 要使用的TLS版本，允许值为：tlsv1、tlsv1.1
-u, --user=name # 登录用户（如果不是当前用户）
-v, --verbose # 写更多信息
-V, --version # 输出版本信息并退出
-E, --vertical # 垂直打印输出
-w, --wait[=#] # 如果连接断开，等待并重试
--connect-timeout=# # 连接超时之前等待的秒数
--shutdown-timeout=# # 等待服务器关机的最大秒数
--plugin-dir=name # 客户端插件目录
--default-auth=name # 要使用的默认身份验证客户端插件
--enable-cleartext-plugin # 启用/禁用明文身份验证插件
--show-warnings # 执行完显示警告
-------------选项说明-----------------
二十四、mysql客户端工具
mysql客户端工具是指命令行工具，并非mysql服务。
mysql [options] [database] -------------------选项说明-------------------------- -u,--user=name 指定用户名 -p,--password[=name] 指定密码 -h，--host=name 指定服务器P或域名 -P， --port=# 指定连接端口 -e, --execute=sql 执行sQL语句并退出 -------------------选项说明--------------------------
浙公网安备 33010602011771号