MySQL使用

命令

MySQL客户端命令

#查看命令帮助
#每个命令都有完整形式和简写形式
mysql> \h

MySQL服务端命令

create database zabbix;
select version();
......

MySQL使用模式

• 交互式命令
• sql脚本

mysql -uUSERNAME -pPASSWORD < /path/test.sql

mysql> source /path/test.sql

mysql命令使用

mysql客户端选项

-u	#指定用户名
-p	#指定用户密码
-h	#指定远程主机
-P	#指定服务器端口
-S	#指定连接socket文件路径
-D	#指定登录数据库
-C	#启用压缩
-e	#指定sql命令
--print-defaults	#获取程序默认使用的配置

mysql命令

mysql> use mysql	#切换数据库
mysql> select database();	#查看当前所在数据库
mysql> select user,host from user;	#查寻用户
mysql> system ls	#执行shell命令

mysqladmin命令

[root@mysql ~]# mysqladmin -uUSERNAME -pPASSWORD ping			#查看MySQL服务是否正常
[root@mysql ~]# mysqladmin -uUSERNAME -pPASSWORD shutdown		#关闭MySQL服务，但是这个命令无法开启
[root@mysql ~]# mysqladmin -uUSERNAME -pPASSWORD create testdb	#创建数据库
[root@mysql ~]# mysqladmin -uUSERNAME -pPASSWORD drop testdb		#删除数据库
[root@mysql ~]# mysqladmin -uUSERNAME -pPASSWORD password 'NEW_PASSWORD'	#修改root密码
[root@mysql ~]# mysqladmin -uUSERNAME -pPASSWORD flush-logs			#日志滚动，生成新的日志文件

mycli命令

相当于强化版的mysql命令，基于python，具有命令补全和语法突出显示的功能

[root@mysql ~]# mycli
MySQL 
mycli 1.26.1
Home: http://mycli.net
Bug tracker: https://github.com/dbcli/mycli/issues
Thanks to the contributor - Karthikeyan Singaravelan
MySQL root@localhost:(none)>

服务器端配置

配置文件：类ini格式,集中式的配置，能够为mysql的各应用程序提供配置信息

服务器端配置文件：

/etc/my.cnf					#全局配置
/etc/mysql/my.cnf			#全局配置
~/.my.cnf #User-specific 选项	#特定于用户的配置

配置文件格式

[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqladmin]
[mysqldump]
[server]
[client]

格式：

parameter = value

说明：

_和- 相同
1，ON，TRUE意义相同， 0，OFF，FALSE意义相同,无区分大小写

数据库主从

最少3台数据库主机

连接

socket连接说明

服务器监听两种socket地址

• ip socket：监听在tcp的3306端口，支持远程通信，可以绑定在一个或者全部接口IP上
• unix socket：监听在sock文件上，仅支持本机通信，host为localhost时，自动使用unix sock

关闭mysqld网络连接

只侦听本地客户端连接，所有客户端和服务端的交互都通过一个socket文件实现

vim /etc/my.cnf
[mysqld]
skip-networking=1
bind_address=127.0.0.1

多实例

类似于应用双开，在同一台主机运行多个相同应用

为避免端口冲突，可开启多个不同端口，同时运行多个MySQL服务进程

好处：有效利用服务器资源，当一台服务器有足够的资源剩余时，可以充分利用剩余资源

坏处：存在资源抢占的问题，数据库是比较消耗CPU和磁盘IO的，当数据库的并发高或者SQL查询慢时，会降低提供服务的质量

配置方案

单一的配置文件，单一启动程序，耦合度太高，并且不方便管理

多配置文件，多启动程序，针对每个实例都有独立的配置文件和目录，管理灵活，耦合度低

为每个实例创建单独的目录

mkdir -p /apps/mysql_{3306,3307,3308}/{data,bin,etc,socket,log,pid}
chown -R mysql.mysql /apps/mysql_{3306,3307,3308}
/apps/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/apps/mysql_3306/data
/apps/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/apps/mysql_3307/data
/apps/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/apps/mysql_3308/data

#准备配置文件
vim /apps/mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/apps/mysql_3306/data
socket=/apps/mysql_3306/socket/mysql.sock
log-error=/apps/mysql_3306/log/mysql.log
pid-file=/apps/mysql_3306/pid/mysql.pid

#重复上面步骤设置3307，3308
sed 's/3306/3307/' /mysql/3306/etc/my.cnf > /apps/mysql/3307/etc/my.cnf
sed 's/3306/3308/' /mysql/3306/etc/my.cnf > /apps/mysql/3308/etc/my.cnf

#准备启动脚本
未完待续，懒得写了，知道有这么回事，有空再补。。。

SQL语言

关系型数据库常见组件

• 数据库：database，物理上表现为一个目录
• 表：table，行：row，列：column
• 索引：index
• 视图：view，虚拟的表
• 存储过程：procedure
• 存储函数：function
• 触发器：trigger
• 事件调度器：event scheduler，任务计划
• 用户：user
• 权限：privilege

语言规范

SQL语句不区分大小写，建议使用大写

SQL语句可单行可多行书写，默认以”；“结尾

关键词不能跨多行或简写

用空格和TAB缩进来提高语句可读性

子句通常位于独立行，便于编辑，提高可读性

注释：

• SQL标准

单行注释，注意有空格
-- 注释内容

多行注释
/*注释内容
注释内容
注释内容*/

• MySQL注释

# 注释内容

数据库对象和命名

数据库的组件（对象）：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头，后续可以包括字母，数字和三个特殊字符（#_$）

不要使用MySQL的保留字

SQL语句分类

• DDL：Data Defifination Language数据定义语言
  • CREATE，DROP，ALTER
• DML：Data Manipulation Language数据操纵语言
  • INSERT，DELETE，UPDATE
  • 软件开发：CRUD
• DQL：Data Query Language数据查询语言
  • SELECT
• DCL：Data Control Language数据操纵语言
  • GRANT，REVOKE
• TCL：Transaction Control Language事务控制语言
  • COMMIT，ROLLBACK，SAVEPOINT

SQL语句构成

关键字（keyword）组成子句（clause），多条clause组成语句

示例：

SELECT user,host	#SELECT子句
FROM mysql.user		#FROM子句
where User="root";	#where子句

字符集和排序

查看所有支持的字符集

SHOW CHARACTER SET;
SHOW CHARSET;

查看当前默认字符集

SHOW VARIABLES LIKE 'CHARACTER%';

查看所有支持的排序规则

SHOW COLLATION;

查看当前使用的排序规则

SHOW VARIABLES LIKE 'collation%';

设置MySQL服务器默认字符集

vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

设置mysql客户端默认字符集

vim /etc/my.cnf
#针对mysql客户端
[mysql]
default-character-set=utf8mb4

#针对所有MySQL客户端
[client]
default-character-set=utf8mb4

管理数据库

创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DBNAME'	#创建数据库或者其他类型（SCHEMA）；当它不存在时（IF NOT EXISTS）
CHARACTER SET 'character set name'				#指定字符集
COLLATE 'collate name'							#指定排序规则

修改数据库

ALTER DATABASE testdb CHARACTER SET 'character set name';

删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DBNAME';

查看数据库列表

SHOW DATABASES;

数据类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型
2. 简单就好，简单数据类型的操作通常需要更少的CPU周期
3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

整数型

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bitint(m) 8个字节 范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

浮点型

浮点型为近似值,不精确,分为单精度和双精度

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

示例:

设一个字段定义为flfloat(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

定点数

在数据库中存放的是精确值,存为十进制

格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。flfloat使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

char和varchar的比较：

1. char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2. char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3. char类型的字符串检索速度要比varchar类型的快

varchar 和 text：

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。

2.text类型不能有默认值

3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

二进制数据BLOB

BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写

BLOB存储的数据只能整体读出

TEXT可以指定字符集，BLOB不用指定字符集

日期时间类型

date 日期 '2008-12-2'

time 时间 '12:25:36'

datetime 日期时间 '2008-12-2 22:06:44'

timestamp 自动存储记录修改时间

YEAR(2), YEAR(4)：年份

timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

适用所有类型的修饰符：

NULL 数据列可包含NULL值，默认值

NOT NULL 数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项

DEFAULT 默认值

PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL

UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL

CHARACTER SET name 指定一个字符集

适用数值型的修饰符：

AUTO_INCREMENT 自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key

UNSIGNED 无符号

DDL语句

表：二维关系

设计表：遵循规范

定义：字段，索引

• 字段：字段名，字段数据类型，修饰符
• 约束，索引：应该创建在经常用作查询条件的字段上

创建表

创建表

方法1：直接创建

CREATE TABLE [IF NOT EXISTS] 'TABLENAME'
(字段1 类型1 修饰符，
 字段2 类型2 修饰符
)ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：

• Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
• 同一库中不同表可以使用不同的存储引擎
• 同一个库中表建议要使用同一种存储引擎类型

方法二：查询现有的表创建，并导入查询而来的数据

create table TABLENAME select 字段1,字段2,字段3 from OLD_TABLENAME;

方法三：复制表结构，但不复制数据

create table TABLENAME like OLD_TABLENAME;

创建外键表

create table TABLENAME(字段1 int primary key auto_increment,name varchar(10), 字段2 int,foreign key(字段2) references OLD_TABLENAME(字段));

表查看

查看表

SHOW TABLES [FROM db_name]；

查看表创建的命令

SHOW CREATE TABLE table_name;

查看表结构

DESC table_name;
SHOW COLUMNS FROM mysql.user;

查看表状态

SHOW TABLE STATUS LIKE 'table_name';

查看库中所有表状态

SHOW TABLE STATUS FROM db_name

查看支持的engine类型

SHOW ENGINES;

修改和删除表

修改表

ALTER TABLE tb_name;

修改表名称

ALTER TABLE tb_name RENAME new_name;

添加/删除字段

ALTER TABLE tb_name ADD new_字段 type [FIRST|AFTER 字段名]	#在指定字段之前或之后生成新字段
ALTER TABLE tb_name ADD new_字段 type;
ALTER TABLE tb_name DROP new_字段 type;

修改字段

ALTER TABLE tb_name CHANGE 字段 字段名 属性 属性 ...;

修改字符集

ALTER TABLE tb_name CHARACTER SET 字符集名称

添加外键

ALTER TABLE tb_name2 ADD foreign key(字段) references tb_name1(字段);

DML语句

DML:INSERT,DELETE,UPDATE

INSERT语句

功能：一次插入一行或多行数据

INSERT tb_name (字段1,字段2，...) VALUES(值1,值2，...);

UPDATE语句

注意：此命令可能会修改所有行，需要指定WHERE子句

UPDATE tb_name SET 字段1 = 值1 WHERE 字段2 = 值2

DELETE语句

删除表中数据，但不会自动缩减数据文件大小

注意：此命令可能会删除所有行，需要指定WHERE子句

DELETE FROM tb_name WHERE 字段=值;

如果要清空表也可以使用下面的语句，此语句会自动缩减数据文件大小

TRUNCATE TABLE tb_name;

缩减表大小

OPTIMIZE TABLE tb_name;

DQL语句

单表操作

SELECT 字段1 FROM tb_name WHERE 字段2=值;
SELECT 字段1 FROM tb_name ORDER BY 字段2;	#排序
SELECT 字段1 FROM tb_name ORDER BY 字段2 DESC;	#倒序
SELECT 字段1 FROM tb_name LIMIT 3;	#查询表中的前三行数据
SELECT 字段1 FROM tb_name LIMIT 1,3	#从第一行查询，查三行，不包括第一行

ifnull函数判断指定的字段是否为空值,如果空值则使用指定默认值

select 字段1,ifnull(字段2,"默认值") from tb_name;

去重

SELECT distinct 字段 FROM tb_name;

聚合函数

select sum(字段1)/count(字段2) from tb_name;
查询字段1的总数 除以 查询字段2共有多少条记录 得到平均数

分组统计

SELECT 字段,COUNT(字段) 字段别名 FROM tb_name GROUP BY 字段;
SELECT 字段1，avg(字段2) 字段别名 FROM tb_name GROUP BY 字段1 HAVING 字段别名=值

分组信息集合

SELECT 字段1,group_concat(字段2) FROM tb_name group by 字段1;

查询区间

SELECT 字段1 FROM tb_name WHERE 字段2 BETWEEN 起始值 AND 结束值 ;

多表查询

多表查询，即查询结果来自于多张表

• 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
• 联合查询：UNION
• 交叉连接：笛卡尔乘积CROSS JOIN
• 内连接：
  • 等值连接：让表之间的字段以“等值”建立连接关系
  • 不等值连接
  • 自然连接：去掉重复列的等值连接，语法：FROM tb1 NATURAL JOIN tb2
• 外连接：
  • 左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
  • 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
    • 完全外连接：FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col注意：MySQL不支持此SQL语法
• 自连接：本表和本表进行连接查询

子查询

子查询即SQL语句调用另一个SELECT子句，可以是对同一张表，也可以是对不同的表，只要有以下四种常见的语法

1. 对于比较表达式中的子查询；子查询仅能返回单个值

   SELECT 字段1 FROM tb_name1 WHERE 字段2=(SELECT 字段3 FROM tb_name2);
   

2. 用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表

   SELECT 字段1 FROM tb_name1 WHERE 字段2 IN (SELECT 字段3 FROM tb_name2)
   

3. 用于EXISTS和Not EXISTS

   EXISTS（包括Not EXISTS）子句返回的是一个BOOL值。EXISTS内部有一个子查询语句，将其称为EXISTS的内查询语句，其内查询结果返回一个结果集，EXISTS子句根据其内查询语句的结果集为空或非空，返回一个BOOL值，如果内查询返回的结果为非空值，则EXISTS子句返回TRUE，外查询数据便可作为外查询的结果返回，否则不能作为结果。

   SELECT 字段1 FROM tb_name1 WHERE [NOT ]EXISTS (SELECT 字段2 FROM tb_name2 WHERE 字段3=值);
   

4. 用于FROM子句中的子查询

   SELECT的执行结果，被其他SQL调用

   SELECT 字段1 FROM tb_name1 WHERE 字段2=(SELECT 字段3 FROM tb_name2);
   

联合查询

联合查询Union实现的条件，多个表的字段数量相同，字段名和数据类型可以不同，但一般数据类型是相同的

SELECT 字段1 FROM tb_name1 UNION SELECT 字段2 FROM tb_name2;
SELECT 字段1 FROM tb_name1 UNION ALL SELECT 字段2 FROM tb_name2;	#不去重

交叉连接

cross join即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加，“雨露均沾”

例如：第一个表3行4列，第二个表5行6列，cross join后的结果为3*5=15行，4+6=10列

会生成非常多的记录，慎用

SELECT 字段1 FROM tb_name1 CROSS JOIN tb_name2;

内连接

inner join内连接取多个表的交集

SELECT 字段1 FROM tb_name1 INNER JOIN tb_name2 ON tb_name1.字段2=tb_name2.字段3;

自然连接

当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列

在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）

SELECT tb_name1.字段1,tb_name2.字段2 FROM tb_name1 NATURAL JOIN tb_name2;

左和右外连接

左连接：以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在，使用NULL值填充

右连接：以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在，使用NULL值填充

左外连接

SELECT 字段1 from tb_name1 LEFT OUTER JOIN tb_name2 ON tb_name1.字段=tb_name2.字段;

左外连接扩展

SELECT 字段1 from tb_name1 LEFT OUTER JOIN tb_name2 ON tb_name1.字段=tb_name2.字段 WHERE tb_name2.字段2 IS NOT NULL;

多条件左外连接

SELECT 字段1 from tb_name1 LEFT OUTER JOIN tb_name2 ON tb_name1.字段=tb_name2.字段 WHERE tb_name1.字段2=tb_name2.字段3 and tb_name1.字段4=tb_name2.字段5;

右外连接

SELECT 字段1 from tb_name1 RIGHT OUTER JOIN tb_name2 ON tb_name1.字段=tb_name2.字段;

完全外连接

MySQL不支持完全外连接full outer join语法

MySQL可以使用左外连接 union 右外连接来代替完全外连接

select testtb.name,testtb2.age from testtb left outer join testtb2 on testtb.name=testtb2.name where testtb2.age is not null
-> union
-> select testtb.name,testtb2.age from testtb right outer join testtb2 on testtb.name=testtb2.name where testtb2.age is not null;

自连接

自连接，即表自身连接自身

SELECT 字段1,字段2 FROM tb_name1 AS a inner join testtb as b on a.name=b.name;

SELECT语句处理顺序

FROM --> ON --> JOIN --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT

FUNCTION函数

函数：分为系统函数和自定义函数

系统内置函数参考

聚合函数

它对其应用的每个行集返回一个值

• AVG()：返回表达式中所有的平均值，仅用于数字列，并自动忽略NULL值
• COUNT()：返回表达式中非NULL值的数量。可用于数字和字符列
• COUNT(*)：返回表达式中的行数（包括有NULL值的列）
• MAX()：返回表达式中的最大值，忽略NULL值，可用于数字、字符和日期时间列
• MIN()：返回表达式中的最小值，忽略NULL值，可用于数字、字符和日期时间列
• SUM()：返回表达式中所有的总和，忽略NULL值，仅用于数字列

转换函数

有CONVERT和CAST两种

SELECT CONVERT(字段,类型) FROM tb_name;

SELECT CAST(字段 AS 类型) FROM tb_name;

日期函数

获取当前日期时间

SELECT NOW();

获取当前日期

SELECT CURDATE()

获取当前时间

SELECT CURTIME()

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

SELECT EXTRACT(YEAR FROM NOW());	--年
SELECT EXTRACT(MONTH FROM NOW()); --月
SELECT EXTRACT(DAY FROM NOW());	--日
SELECT EXTRACT(HOUR FROM NOW());	--时
SELECT EXTRACT(MINUTE FROM NOW());	--分
SELECT EXTRACT(SECOND FROM NOW());	--秒

日期增加/减少

时间减少

SELECT DATE_SUB(NOW(),INTERVAL 数值 YEAR|MONTH|DAY|HOUR|MINUTE|SECOND);

时间增加

SELECT DATE_ADD(NOW(),INTERVAL 数值 YEAR|MONTH|DAY|HOUR|MINUTE|SECOND);

日期格式化、字符串转日期

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT(NOW(),'2012-12-12 12:12:12');

字符串转日期

SELECT STR_TO_DATE('2012-12-12 12:12:12','%Y-%m-%d %H:%i:%s');

数字函数

• ABS(num_expr) 返回数值表达式的绝对值
  • ACOS(float_expr)返回角(以弧度表示),它的余弦值近似于指定的浮点表达式
  • ASIN(float_expr)返回角(以弧度表示),它的正弦值近似于指定的浮点表达式
  • ATAN(float_expr)返回角(以弧度表示),它的正切值近似于指定的浮点表达式
  • ATN2(float_expr1, float_expr2)返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间
  • CEILING(num_expr)返回大于或等于数值表达式的最小整数
  • COS(float_expr)返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值
  • COT(float_expr)返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值
  • DEGREES(num_expr)返回数值表达式表示的弧度值对应的度值
  • EXP(float_expr)根据指定的近似浮点表达式,返回指数值
  • FLOOR(num_expr)返回小于或等于数值表达式的最大整数
  • LOG(float_expr)根据指定的近似浮点表达式,返回自然对数值
  • LOG10(float_expr)根据指定的近似浮点表达式,返回以为底的对数
  • PI()返回常量值.141592653589793
  • POWER(num_expr,y)返回幂为y的数值表达式的值
  • RADIANS(num_expr)返回数值表达式表示的度值对应的弧度值
  • RAND([seed])随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)
  • ROUND(num_expr,length)对数值表达式截取指定的整数长度,返回四舍五入后的值
  • SIGN(num_expr)对正数执行+1操作,对负数和零执行-1操作
  • SIN(float_expr)返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值
  • SQUARE(float_expr)返回浮点表达式的平均值
  • SQRT(float_expr)返回指定的近似浮点表达式的平方根
  • TAN(float_expr)返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值

字符串函数

可用于binary 和varbinary数据类型列,但主要用于char和varchar数据类型

• Expr1+expr2 返回两个表达式的组合形式的字符串
  • ASCII(char_expr)返回表达式最左边字符的ASCⅡ代码值
  • CHAR(int_expr)返回到之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内,则返回NULL
  • CHARINDEX('pattern',char_expr)返回字符表达式中指定模式的起始位置
  • DIFFERENCE(char_expr1,char_expr2)根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳
  • LEN(char_expr)返回字符表达式的长度
  • LOWER(char_expr)将字符表达式全部转换为小写
  • LTRIM(char_expr)返回删除掉前面空格的字符表达式
  • PATINDEX('%pattern%',expr)返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式
  • REPLICATE(char_expr,int_expr)返回重复指定次数的字符表达式产生的字符串
  • REVERSE(char_expr)反转字符表达式
  • RIGHT(char_expr,int_expr)返回从字符表达式最右端起根据指定的字符个数得到的字符
  • RTRIM(char_expr)返回删除掉其后空格的字符表达式
  • SOUNDEX(char_expr)评估两个字符串的相似度后得到的位代码
  • SPACE(int_expr)返回包含指定空格数的字符串
  • STR(float_expr[,length[,decimal]])返回浮点表达式的字符串表示法
  • STUFF(char_expr1,start,length,char_expr2)使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。
  • SUBSTRING(char_expr,start,length)返回从字符表达式的指定位置开始,截取指定长度得到的字符集
  • UPPER(char_expr)将字符表达式全部转换为大写

系统函数

用于返回元数据或配置设置

• COALESCE(expr1,expr2, xprN) 返回第一个非NULL表达式
  • COL_LENGTH('table_name','column_name')返回列的长度
  • COL_NAME(table_id,column_id)返回指定的表中的列名
  • DATALENGTH('expr')返回任何数据类型的实际长度
  • DB_ID([‘database_name'])返回数据库的标识号
  • DB_NAME([database_id])返回数据库的名称
  • GETANSINULL([‘database_name'])返回数据库的默认空性(Nullability)
  • HOST_ID()返回工作站的标识号
  • HOST_NAME()返回工作站的名称
  • IDENT_INCR('table_or_view')有新的记录添加入到表中时计数加
  • IDENT_SEED('table_or_view')返回标识列的起始编号
  • INDEX_COL('table_name',index_id,key_id)返回索引的列名
  • ISNULL(expr,value)使用指定的值替换的NULL表达式
  • NULLIF(expr1,expr2)Expr1与Expr2相等时,返回Null
  • OBJECT_ID('obj_name')返回数据库对象标识号
  • OBJECT_NAME('object_id')返回数据库对象名
  • STATS_DATE(table_id,index_id)返回上次更新指定索引的统计的日期
  • SUSER_SID([‘login_name'])返回用户的登录标识号
  • SUSER_ID([‘login_name'])返回用户的登录标识号。这个函数类似于SUSER_SID()函数,并且保留了向后的兼容性
  • SUSER_SNAME([server_user_id])返回用户的登录标识号
  • SUSER_NAME([server_user_id])返回用户的登录标识号。这个函数类似于SUSER_SNAME()函数,并且保留了向后的兼容性
  • USER_ID('user_name')返回用户的数据库标识号
  • USER_NAME(['user_id'])返回用户的数据库名称

文本和图像函数

通常返回有关文本和图像数据所需的信息。文本和图像数据是以二进制格式的形式进行存储的。

• TEXTPTR(col_name) 返回varbinary格式的文本指针值。对文本指针进行检查以确保它指向第一个文本页
• TEXTVALID('table_name.col_name',text_ptr)检查给定的文本指针是否有效。返回表示有效,返回表示指针无效

自定义函数

自定义函数：user-defined function UDF，保存在mysql.proc表中（MySQL8.0中已取消此表）

创建UDF语法：

DELIMITER //
CREATE FUNCTION test(name varchar(20)) RETURNS varchar(50)
BEGIN
SET @createdb=name;
CREATE DATABASE @createdb;
END //
DELIMITER ;

DELIMITER //
DROP FUNCTION IF EXISTS deleteById //
CREATE FUNCTION deleteById(idd SMALLINT UNSIGNED) RETURNS VARCHAR(20) 
BEGIN
create table idd(ID int(10) PRIMARY KEY AUTO_INCREMENT NOT NULL);
END//
DELIMITER ;

CREATE FUNCTION heihei(IID int(10),nnaa varchar)
BEGIN
	RETURNS (SELECT IDD,nnaa from testtb2);
END//

MySQL8.0默认开启二进制日志不允许创建函数

show variables like 'log_bin_trust_function_creators';

修改此变量以允许创建函数

set global log_bin_trust_function_creators=ON;

杂项

正则表达式REGEXP

SELECT 字段1 FROM tb_name WHERE 字段2 REGEXP '正则表达式'

VIEW视图

视图：虚拟表，保存有实表的查询结果，相当于别名

利用视图，可以隐藏表的真实结构，在程序中利用视图进行查询，可以避免表结构的变化，而修改程序，降低程序和数据库之间的耦合度。

创建视图

CREATE VIEW view_name AS SELECT语句;

查看视图定义

SHOW CREATE VIEW view_name;	#查看视图定义
SHOW CREATE TABLE view_name;	#查看表和视图

删除视图

DROP VIEW [IF EXISTS] view_name

注意：视图中的数据实际存储于“基表”中，因此，其修改操作也会针对基表实现，其修改操作受基表限制