MySQL:CRUD,函数以及union&union all

发布时间:2024年01月04日

一. CRUD

1. SELECT(查询)

基础查询:

select 查询列表 from 表名:从表中查询。

查询列表可以是表中的字段、常量、表达式和函数等。

查询的结果是一个虚拟的表格。

查询多个字段,在查询列表中用逗号隔开。

*可以表示所有字段。

select distinct 查询列表 from 表名:去除查询结果中的重复部分。

所有字段都完全相同才算是重复。

例:SELECT DISTINCT salary FROM employees

为字段起别名:

select 查询列名 As 别名 from 表名

select 查询列名 别名 from 表名

如果要查询的字段有重名的情况,使用别名可以进行区分。

在from后为表起别名也是同理,但是为表起别名后,其原有的表名就无法使用了。

+号的作用:

在MySQL中,+号的作用是作为运算符,而无法作为连接符。

两个数值类型,则作加法运算。

一个数值一个字符,则试图将字符型数值转换为数值型。如果转换失败,则字符型看作0。

如果有一个是null,则结果也为null。

连接符的作用由concat(字段1,字段2)函数实现。

例:SELECT CONCAT(first_name,last_name) FROM employees

判断是否为null:

ifnull(字段,新值):对字段进行判断,如果不为null则返回字段原本的值,如果为null则返回新值。

isnull(字段):对字段进行判断,如果不为null则返回1,如果为null则返回0。

条件查询:

select 查询列表 from 表名 where 筛选条件:查询符合条件的结果。

执行过程:先定位到表名,再按条件进行筛选,最后进行查询。

筛选条件:

条件表达式:使用条件运算符<><>=!=>=<=

逻辑表达式:使用逻辑运算符&&||!、and、or、not。

例:SELECT * FROM employees WHERE salary>10000 AND salary<20000

使用模糊查询:like、between and、in、is null/is not null。

like:使用%(任意多个字符)、_(任意单个字符)通配符。通配符无法匹配null,但是也可以对数值型进行通配。

例:SELECT * FROM employees WHERE first_name LIKE '_a%'

通配符转义一般使用\,也可以用escape指定。

between and:包含临界值,但两个临界值不能颠倒(等价于大于等于左侧值,小于等于右侧值)。

in:判断某字段的值是否与列表中的某一项匹配(等价于使用多个=号,不支持通配符)。

例:SELECT * FROM employees WHERE job_id IN('IT_PROG','FI_MGR')

is null/is not null:使用=/<>号不能匹配null值。

安全等于<=>:可以匹配普通值和null值。

排序查询:

select 查询列表 from 表名 order by 排序列表 ASC/DESC:根据排序列表排序,并且指定或升序降序。排序列表还支持表达式、函数和别名。

例:SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC

length(字段):获取某个字段对应的各行值的长度。

排序列表也可以包含多个字段,先按先写的字段排序。

order by字句一般在语句的最后,但在limit字句之前。

?1.2 INSERT(新增)

插入语句:

insert into 表名(列名,...) value(值,...)

插入的值的类型要与列的类型一致或兼容。

表名后的列名可以省略,默认是所有列按顺序插入。

value后可以有多个()进行插入。

也可以用一个子查询替换value()进行插入。

insert into 表名 set 列名=值

1.3 UPDATE(修改)

修改单表:

update 表名 set 列=新值 where 筛选条件

修改多表:

update 表1 连接类型 join 表2 on 连接条件 set 列名=值 where 筛选条件

1.4 DELETE(删除)

删除单表:

delete from 表名 where 筛选条件

truncate table 表名

truncate table会删除整个表,不能接where。

删除多表:

delete 所要删掉的表 from 表1 连接类型 join 表2 on 连接条件 where 筛选条件

?出处:https://www.cnblogs.com/iwehdio/p/13893865.html

二. 函数

常见函数:

select 函数名(实参) from 表名

单行函数:

字符函数:

length(字段):获取字段对应的字节个数。

concat(字段1,字段2):拼接字符串。

upper(字段):全部变为大写。

lower(字段):全部变为消息。

substr(字段,起始索引,截取长度):截取字符串,索引从1开始,长度指的是字符长度。

instr(字段1,字段2):返回字段2在字段1中的第一次出现的起始索引。找不到返回0。

trim(去除字符 from 字段):去除字段中前后为去除字符的内容,不指定去除字符则默认为去除空格。

lpad(字段,总长度,填充字符):用指定的字符实现左填充到指定长度。rpad同理。

replace(字段,被替换字符,新字符):替换字段中的指定字符。

数学函数:

round(字段,小数点保留位数):四舍五入。

ceil(字段):向上取整。

floor(字段):向下取整。

truncate(字段,小数点保留位数):截断小数。

mod(字段,被除数):取余。

rand():获取0到1之间的随机数。

日期函数:

now():返回当前系统日期和时间。

curdate():返回当前日期。

curtime():返回当前时间。

year(日期字段):返回日期的年。同理还有月、日等。

str_to_date(字符串,格式):将字符串按指定格式转换为日期。

date_format(日期字段,格式):将日期转换为指定格式的字符串。

datediff(日期字段1,日期字段2):两个日期相差的天数。

流程控制函数:

if(条件表达式,结果1,结果2):根据条件表达式,true返回结果1,false返回结果2。

case类似switch case的结构,相当于多个where条件查询:

case 要判断的字段或表达式
when 常量1 then 要显示的值1
when 常量2 then 要显示的值2
else 要显示的值3
end

case类似多重if else语句:

case
when 条件1 then 要显示的值1
when 条件2 then 要显示的值2
else 要显示的值3
end

分组函数(聚合函数):?

sum(字段):求和。

avg(字段):平均值。

max(字段):最大值。

min(字段):最小值。

count(字段):个数。字段为*可以统计行数。字段为1,也有同样的效果。

以上分组函数都忽略null值。

sum(distinct 字段):去重后再求和。

分组查询:

select 分组函数,分组字段 from 表名 group by 分组字段

例:SELECT MAX(salary),job_id FROM employees GROUP BY job_id

如果where 筛选条件group by之前,是先筛选再分组查询。

如果having 筛选条件group by之后,是先分组查询再筛选。

group by后可以放多个字段,就可以按多个字段分组。

group by、having和order by后可以用别名,但是where后不行。

MySQL 字符串函数

函数描述
ASCII返回特定字符的 ASCII 值
CHAR_LENGTH返回字符串的长度(以字符为单位)
CHARACTER_LENGTH返回字符串的长度(以字符为单位)
CONCAT将两个或多个表达式加在一起
CONCAT_WS使用分隔符将两个或多个表达式添加在一起
FIELD返回值在值列表中的索引位置
FIND_IN_SET返回字符串在字符串列表中的位置
FORMAT将数字格式化为类似"#,###,###.##"的格式,四舍五入到指定的小数位数
INSERT在字符串中的指定位置插入字符串,并插入一定数量的字符
INSTR返回一个字符串在另一个字符串中第一次出现的位置
LCASE将字符串转换为小写
LEFT从字符串中提取多个字符(从左开始)
LENGTH返回字符串的长度(以字节为单位)
LOCATE返回字符串中子字符串第一次出现的位置
LOWER将字符串转换为小写
LPAD用另一个字符串向左填充一个字符串,达到一定的长度
LTRIM从字符串中删除前导空格
MID从字符串中提取子字符串(从任意位置开始)
POSITION返回字符串中子字符串第一次出现的位置
REPEAT按照指定的次数重复一个字符串
REPLACE用新的子字符串替换字符串中所有出现的子字符串
REVERSE反转一个字符串并返回结果
RIGHT从字符串中提取多个字符(从右开始)
RPAD用另一个字符串右填充一个字符串到一定长度
RTRIM从字符串中删除尾随空格
SPACE返回指定空格数的字符串
STRCMP比较两个字符串
SUBSTR从字符串中提取子字符串(从任意位置开始)
SUBSTRING从字符串中提取子字符串(从任意位置开始)
SUBSTRING_INDEX在出现指定数量的分隔符之前返回字符串的子字符串
TRIM从字符串中删除前导和尾随空格
UCASE将字符串转换为大写
UPPER将字符串转换为大写


MySQL 数值函数

函数描述
ABS返回一个数字的绝对值
ACOS返回数字的反余弦
ASIN返回数字的反正弦
ATAN返回一个或两个数字的反正切
ATAN2返回两个数的反正切
AVG返回表达式的平均值
CEIL将 >= 的最小整数值返回为数字
CEILING将 >= 的最小整数值返回为数字
COS返回数字的余弦
COT返回数字的余切
COUNT返回选择查询返回的记录数
DEGREES将弧度值转换为度数
DIV用于整数除法
EXP返回 e 的指定数次方
FLOOR将 <= 的最大整数值返回为数字
GREATEST返回参数列表的最大值
LEAST返回参数列表中的最小值
LN返回数字的自然对数
LOG返回一个数字的自然对数,或者一个数字的对数到指定底数
LOG10返回一个以 10 为底的自然对数
LOG2返回以 2 为底的数字的自然对数
MAX返回一组值中的最大值
MIN返回一组值中的最小值
MOD返回一个数除以另一个数的余数
PI返回 PI 的值
POW返回一个数字的另一个数字的幂
POWER返回一个数字的另一个数字的幂
RADIANS将度数转换为弧度
RAND返回一个随机数
ROUND将数字四舍五入到指定的小数位数
SIGN返回一个数字的符号
SIN返回数字的正弦
SQRT返回数字的平方根
SUM计算一组值的总和
TAN返回数字的正切
TRUNCATE将数字截断到指定的小数位数

MySQL 日期函数

函数描述
ADDDATE将时间/日期间隔添加到日期,然后返回日期
ADDTIME将时间间隔添加到时间/日期时间,然后返回时间/日期时间
CURDATE返回当前日期
CURRENT_DATE返回当前日期
CURRENT_TIME返回当前时间
CURRENT_TIMESTAMP返回当前日期和时间
CURTIME返回当前时间
DATE从日期时间表达式中提取日期部分
DATEDIFF返回两个日期值之间的天数
DATE_ADD将时间/日期间隔添加到日期,然后返回日期
DATE_FORMAT格式化日期
DATE_SUB从日期中减去时间/日期间隔,然后返回日期
DAY返回给定日期的月份日期
DAYNAME返回给定日期的工作日名称
DAYOFMONTH返回给定日期的月份日期
DAYOFWEEK返回给定日期的工作日索引
DAYOFYEAR返回给定日期的一年中的哪一天
EXTRACT从给定日期提取部分
FROM_DAYS从数字日期值返回日期
HOUR返回给定日期的小时部分
LAST_DAY提取给定日期当月的最后一天
LOCALTIME返回当前日期和时间
LOCALTIMESTAMP返回当前日期和时间
MAKEDATE根据年份和天数值创建并返回日期
MAKETIME根据小时、分钟和秒值创建并返回时间
MICROSECOND返回时间/日期时间的微秒部分
MINUTE返回时间/日期时间的分钟部分
MONTH返回给定日期的月份部分
MONTHNAME返回给定日期的月份名称
NOW返回当前日期和时间
PERIOD_ADD将指定的月数添加到期间
PERIOD_DIFF返回两个句点之间的差
QUARTER返回给定日期值的一年中的季度
SECOND返回时间/日期时间的秒部分
SEC_TO_TIME根据指定的秒数返回一个时间值
STR_TO_DATE根据字符串和格式返回日期
SUBDATE从日期中减去时间/日期间隔,然后返回日期
SUBTIME从日期时间中减去一个时间间隔,然后返回时间/日期时间
SYSDATE返回当前日期和时间
TIME从给定时间/日期时间中提取时间部分
TIME_FORMAT按指定格式格式化时间
TIME_TO_SEC将时间值转换为秒
TIMEDIFF返回两个时间/日期时间表达式之间的差异
TIMESTAMP根据日期或日期时间值返回一个日期时间值
TO_DAYS返回日期和日期"0000-00-00"之间的天数
WEEK返回给定日期的周数
WEEKDAY返回给定日期的工作日编号
WEEKOFYEAR返回给定日期的周数
YEAR返回给定日期的年份部分
YEARWEEK返回给定日期的年和周数

MySQL 高级函数

函数描述
BIN返回数字的二进制表示
BINARY将值转换为二进制字符串
CASE遍历条件并在满足第一个条件时返回一个值
CAST将(任何类型的)值转换为指定的数据类型
COALESCE返回列表中的第一个非空值
CONNECTION_ID返回当前连接的唯一连接 ID
CONV将数字从一种数字基系统转换为另一种数字系统
CONVERT将值转换为指定的数据类型或字符集
CURRENT_USER返回服务器用来验证当前客户端的 MySQL 帐户的用户名和主机名
DATABASE返回当前数据库的名称
IF如果条件为 TRUE,则返回一个值,如果条件为 FALSE,则返回另一个值
IFNULL如果表达式为NULL,则返回指定值,否则返回表达式
ISNULL根据表达式是否为 NULL 返回 1 或 0
LAST_INSERT_ID返回表中已插入或更新的最后一行的 AUTO_INCREMENT id
NULLIF比较两个表达式,如果相等则返回 NULL。否则返回第一个表达式
SESSION_USER返回当前 MySQL 用户名和主机名
SYSTEM_USER返回当前 MySQL 用户名和主机名
USER返回当前 MySQL 用户名和主机名
VERSION返回 MySQL 数据库的当前版本

三. MySQL UNION 运算符

UNION?运算符用于组合两个或多个?SELECT?语句的结果集。

  • UNION?中的每个?SELECT?语句必须具有相同的列数
  • 列还必须具有相似的数据类型
  • 每个?SELECT?语句中的列也必须是相同的顺序

UNION 语法

SELECT?column_name(s)?FROM?table1
UNION
SELECT?column_name(s)?FROM?table2;

UNION ALL 语法

UNION?运算符默认只选择不同的值。 要允许重复值,请使用?UNION ALL:

SELECT?column_name(s)?FROM?table1
UNION?ALL
SELECT?column_name(s)?FROM?table2;

注意:结果集中的列名通常等于第一个?SELECT?语句中的列名。

文章来源:https://blog.csdn.net/liaozhixiangjava/article/details/135397087
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。