陈佳浩
陈佳浩
发布于 2024-06-26 / 27 阅读
0
0

MySQL使用

  • MySQL使用
    • 命令
      • MySQL客户端命令
      • MySQL服务端命令
      • MySQL使用模式
      • mysql命令使用
        • mysql客户端选项
        • mysql命令
      • mysqladmin命令
      • mycli命令
    • 服务器端配置
      • 配置文件格式
      • 数据库主从
    • 连接
      • socket连接说明
      • 关闭mysqld网络连接
    • 多实例
      • 配置方案
    • SQL语言
      • 关系型数据库常见组件
      • 语言规范
        • 注释:
      • 数据库对象和命名
        • 数据库的组件(对象):
        • 命名规则:
      • SQL语句分类
      • SQL语句构成
      • 字符集和排序
        • 查看所有支持的字符集
        • 查看当前默认字符集
        • 查看所有支持的排序规则
        • 设置MySQL服务器默认字符集
        • 设置mysql客户端默认字符集
      • 管理数据库
        • 创建数据库
        • 修改数据库
        • 删除数据库
        • 查看数据库列表
      • 数据类型
        • 整数型
        • 浮点型
        • 定点数
        • 字符串
        • 二进制数据BLOB
        • 日期时间类型
        • 修饰符
      • DDL语句
        • 创建表
          • 方法1:直接创建
          • 方法二:查询现有的表创建,并导入查询而来的数据
          • 方法三:复制表结构,但不复制数据
          • 创建外键表
        • 表查看
        • 修改和删除表
      • DML语句
        • INSERT语句
        • UPDATE语句
        • DELETE语句
      • DQL语句
        • 单表操作
        • 多表查询
          • 子查询
          • 联合查询
          • 交叉连接
          • 内连接
          • 自然连接
          • 左和右外连接
            • 左外连接
            • 右外连接
          • 完全外连接
          • 自连接
        • SELECT语句处理顺序
      • FUNCTION函数
        • 聚合函数
        • 转换函数
        • 日期函数
        • 数字函数
        • 字符串函数
        • 系统函数
        • 文本和图像函数
        • 自定义函数
      • 杂项
        • 正则表达式REGEXP
    • VIEW视图

命令

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;

数据类型

graph LR 1[数据类型:] 2[数值型:] 3[字符型:] 4[时间\日期型:] 5[整数型:] 6[小数型:] 7[型号:] 8[用法:] 9[tinyint: 占据1个字节] 10[smallint: 占据2个字节] 11[mediumint: 占据3个字节] 12[int: 占据4个字节] 13[bigint: 占据8个字节] 14["1、tinyint unsigned zerofill:<br/>unsigned:影响存储范围;<br/>M:代表宽度,影响显示效果<br/>zerofill:零填充,unsigned下有效,影响显示效果"] 15[型号:] 16[用法:] 17["float(浮点型)":] 18["decimal(定点型)":] 19["1、float(M,D):<br/>M:精度(总位数,不包含点);<br/>D:标度(小数位)<br/>浮点型的M,D影响存储范围"] 20["char(定长)"] 21["varchar(变长)"] 22[text] 23[year: 2023] 24[date: 2023-06-26] 25[time: 00:32:59] 26[datetime: 2023-06-26 00:32:59] 1-->2 1-->3 1-->4 2-->5 2-->6 5-->7 5-->8 7-->9 7-->10 7-->11 7-->12 7-->13 8-->14 6-->15 6-->16 15-->17 15-->18 16-->19 3-->20 3-->21 3-->22 4-->23 4-->24 4-->25 4-->26

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

  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的比较:

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes
  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

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


评论