MySql总结

最新推荐文章于 2022-10-24 16:24:58 发布
原创 最新推荐文章于 2022-10-24 16:24:58 发布 · 371 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文介绍了MySQL数据库中的多种实用技巧，包括数据分类、年龄计算、随机数据抽取等SQL语句，以及内连接、外连接的使用方法。此外还详细列举了常用的数学函数、字符串函数、日期时间函数等，并提供了条件判断、系统信息、加密等其他类型函数的应用示例。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、Example

1）、为查询到的数据分类给出序号

SELECT
	(@i := CASE WHEN @pre_字段名 = 要分类的字段名 THEN @i + 1 ELSE 1 END ) rownum,
	( @pre_字段名 := 要分类的字段名 )
FROM
	表名 c ,(SELECT @i := 0, @pre_brand := '') AS a
WHERE 条件

例：
 

2）、知道出生日期求年龄：

(year(now())-year(birthDate)-1) + ( DATE_FORMAT(birthDate,
        '%m%d') <= DATE_FORMAT(NOW(),
        '%m%d') ) as age

3）、多条数据随机取出其中100条

SELECT * FROM pl_msg WHERE  id >= ((SELECT MAX(id) FROM pl_msg)-(SELECT MIN(id) FROM pl_msg)) * RAND() + (SELECT MIN(id) FROM pl_msg)  LIMIT 100

4)、多表合并

表A，表B，表C，如下图：

实现结果：

SQL语句：

select concat('P',cast(p.provinceID as char)) as id, p.name as text,0 as pid,'false' as leaf from ts_province p union
select concat('C',cast(c.cityID as char)) as id, c.name as text,concat('P',cast(c.father as char)) as pid,'false' as leaf from ts_city c union
select concat('A',cast(a.areaID as char)) as id, a.name as text,concat('C',cast(a.father as char)) as pid,'false' as leaf from ts_area a

 

二、MYSQL的内连接，外链接

假如有2个表，分别为表A，表B，如下图：

 

 

1）、内连接：SELECT * FROM  A INNER JOIN  B on A.id = B.id，为两个表的交集

,

结果为：

2）、外连接又分为以下几种：

左联：select * from A left join B on A.id=B.id，以左边的表为主表

，

结果为：

 

3）、右联：select * from A right join B on A.id=B.id；以右边表为主表

，

结果为：

全外链接：select * from A FULL OUTER JOIN B on A.id=B.id；将数据全部查出来

结果为：

 

三、函数总结

1）、数学函数

a、ABS(X)：求绝对值 SELECT ABS(-1); # 1 

b、MOD(N,M)、N%M：求N除以M的余数 SELECT MOD(5,3);SELECT 5%3; # 2 2 
   DIV：整除 SELECT 7 DIV 2; # 3

c、FLOOR(X)：求小于X的最大整数 SELECT FLOOR(1.23);SELECT FLOOR(-1.23); # 1 -2 

d、CEIL(X)，CEILING(X)：求大于X的最小整数 SELECT CEIL(1.23);SELECT CEIL(-1.23); # 2 -1 
   SELECT CEILING(1.23);SELECT CEILING(-1.23); # 2 -1 

e、ROUND(X):四舍五入返回一个整数 SELECT ROUND(1.23);SELECT ROUND(1.53);SELECT ROUND(-1.23);SELECT ROUND(-1.53); # 1 2 -1 -2 
   ROUND(X,D)：则四舍五入运行返回小数 SELECT ROUND(2.125,2);SELECT ROUND(2.0,2); # 2.13 2
   TRUNCATE(X,D)：不四舍五入，直接取到X的D位小数 SELECT TRUNCATE(2.251,2);SELECT TRUNCATE(2.256,2); # 2.25 2.25

f、SIGN(X):如果X是正数则返回1，为0则返回0，为负数则返回-1 
   SELECT SIGN(2);SELECT SIGN(0);SELECT SIGN(-3); # 1 0 -1 

g、SQRT(X)：求X的平方根 SELECT SQRT(16);SELECT SQRT(0);SELECT SQRT(-16); # 4 0 null 

h、POW(X,Y)、POWER(X,Y)：求X的Y次幂 SELECT POW(2,3); SELECT POWER(2,3); # 8 8

i、EXP(X)（ex）：返回值e(自然对数的底)的X次方 SELECT EXP(2); # 7.38905609893065

  LN(X)/LOG(X)(logex)：返回X的自然对数(logex=a,x=ea) SELECT LN(7.38905609893065); # 2

  LOG(B,X)(logBx)：返回X任意底B的对数 SELECT LOG(2,4); # 2

  LOG2(X)、LOG10(X)：返回X以2、10为底的对数 SELECT LOG2(4);SELECT LOG10(100); # 2 2

j、PI()：返回圆周率 SELECT PI(); # 3.141593 

k、度数与弧度的关系：X°的弧度为X/180*π（90°=0.5π）

    SIN(X)、COS(X)、TAN(X)：求X（X为弧度）的正弦、余弦、正切

    ASIN(X)、ACOS(X)、ATAN(X)：求X的反正弦、反余弦、反正切，ASIN(X)、ACOS(X)，如果X不在-1到1直接则返回null

    ATAN2(Y,X)：返回两个变量X和Y的反正切值，类似于求Y/X的反正切

    COT(X):返回X的余切 

l、RAND(N)：返回一个0~1之前的随机浮点数，当N有值时，则产生一个可重复的数值

2）、字符串函数

a、ASCII(str)：返回字符串str最左边字符的ASCII代码值（十进制） 

   SELECT ASCII('2');SELECT ASCII('bx');SELECT ASCII('b'); # 50 98 98 

b、CONCAT(str1,str2,...)：连接字符串 SELECT CONCAT('he','ll','o'); # hello 

c、LENGTH(str)：返回字符串长度 SELECT LENGTH('hello'); # 5 

d、LOCATE(substr,str)、INSTR(str,substr)：返回字符串substr在字符串str第一次出现的位置，如果没有则返回0 

   SELECT LOCATE('he','hello');SELECT LOCATE('heo','hello');SELECT LOCATE('l','hello'); # 1 0 3 

   SELECT INSTR('hello','he');SELECT INSTR('hello','heo');SELECT INSTR('hello','l'); # 1 0 3 

   LOCATE(substr,str,pos)：从第pos个位置（起始位置为1）算起，字符串substr在str第一次出现的位置，如果没有则返回0 

   SELECT LOCATE('l','hello',3);SELECT LOCATE('l','hello',4);SELECT LOCATE('l','hello',5); # 3 4 0 

e、LEFT(str,len)、RIGHT(str,len)：从最左/右边算起，显示len个字符,若len<=0则不显示，若len大于str本身长度则显示str本身

   SELECT LEFT('hello',2);SELECT RIGHT('hello',2); # he lo  

f、SUBSTR(str,pos)：从第pos个位置到最后一个位置显示str SELECT SUBSTR('hello',2); # ello 

g、TRIM([remstr FROM] str)：删除字符串str前后缀的remstr字符串，如果前半部分省略，则去除前后空格

   SELECT TRIM('o' FROM 'ohelloo');SELECT TRIM(' hello ');  # hell hello('hello')

   LTRIM(str),RTRIM(str):删除最左/右边的空格 SELECT LTRIM(' hello ');SELECT RTRIM(' hello ');

   # hello ('hello ')  hello(' hello') 

   TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str):删除指定首尾/首/尾字符

   SELECT TRIM(BOTH ',' FROM ',,hello,,'); # hello

   SELECT TRIM(LEADING ',' FROM ',,hello,,'); # hello,,

   SELECT TRIM(TRAILING ',' FROM ',,hello,,'); # ,,hello 

h、REPLACE(str,from_str,to_str)：将to_str代替from_str并输出新的str SELECT REPLACE('hello','h','H'); # Hello 

i、REPEAT(str,count)：重复count次输出str，若str为空或者count<=0则输出空 SELECT REPEAT('hello',2); # hellohello 

j、REVERSE(str)：颠倒字符串顺序并输出 SELECT REVERSE('hello'); # olleh 

k、INSERT(str,pos,len,newstr)：将str中第pos个位置到len个字符替换成newstr字符串并输出 

    SELECT INSERT('hello',2,4,'i'); # hi    

3）、日期和时间函数

a、DAYOFWEEK(date)：返回date当天是星期几（1=星期日，2=星期一，3=星期二...,7=星期六）

   SELECT DAYOFWEEK('2018-3-17'); # 7 

   WEEKDAY(date)：返回date当天是星期几（0=星期一,1=星期二..,5=星期六,6=星期日）

   SELECT WEEKDAY('2018-3-17'); # 5 

   DAYNAME(date):返回date当天星期的名字 SELECT DAYNAME('2018-3-17'); # Saturday

   MONTHNAME(date)：返回date月份的名字 SELECT MONTHNAME('2018-3-17'); # March 

   QUARTER(date)：返回date当前的季度（1~4）SELECT QUARTER('2018-3-17'); # 1  

b、DAYOFMONTH(date)：返回date的月份中的日期（1~31）SELECT DAYOFMONTH('2018-2-9'); # 9

c、DAYOFYEAR(date)：返回date在当年的日数（1~366）SELECT DAYOFYEAR('2018-2-9'); # 40 

d、MONTH(date)：返回date的月数(1~12) SELECT MONTH('2018-3-9'); # 3

e、YEAR(date)：返回date的年数（1000~9999） SELECT YEAR('18-3-17'); # 2018 

f、HOUR(time)、MINUTE(time)、SECOND(time)：返回time的小时（0~23）/分钟（0~59）/秒数（0~59）

   SELECT HOUR('18:17:39');SELECT MINUTE('18:17:39');SELECT SECOND('18:17:39'); # 18 17 39

g、WEEK(date[,mode]):model代表每一周的第一天（0：有星期日就为一周，1：有星期一就为一周）

SELECT WEEK('2018-2-5',0);SELECT WEEK('2018-2-5',1); # 5 6

h、DATE_ADD(date,INTERVAL expr unit)、DATE_SUB(date,INTERVAL expr unit)：日期的加减，精确到秒

SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL 1 YEAR); # 2019-02-02 18:17:39
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL 1 YEAR); # 2017-02-02 18:17:39

SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL 2 MONTH); # 2018-04-02 18:17:39
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL 2 MONTH); # 2017-12-02 18:17:39

SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL 2 DAY); # 2018-02-04 18:17:39
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL 2 DAY); # 2018-01-31 18:17:39

SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL 2 HOUR); # 2018-02-04 20:17:39
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL 2 HOUR); # 2018-02-04 16:17:39

SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL 2 MINUTE); # 2018-02-02 20:19:39
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL 2 MINUTE); # 2018-02-02 18:15:39

SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL 2 SECOND); # 2018-02-02 18:19:41
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL 2 SECOND); # 2018-02-02 18:17:37

YEAR_MONTH，DAY_HOUR，DAY_MINUTE，DAY_SECOND，HOUR_MINUTE，HOUR_SECOND，MINUTE_SECOND
SELECT DATE_ADD('2018-2-2 18:17:39',INTERVAL '1:1' YEAR_MONTH); # 2019-03-02 18:17:39
SELECT DATE_SUB('2018-2-2 18:17:39',INTERVAL '1:1' YEAR_MONTH); # 2017-01-02 18:17:39 

i、CURDATE()，CURTIME()，NOW()：以不同格式显示当前日期时间

   SELECT CURDATE();SELECT CURTIME();SELECT NOW(); # 2018-03-15 18:58:29 2018-03-15 18:58:29 

   SELECT CURRENT_DATE();SELECT CURRENT_TIME();SELECT CURRENT_TIMESTAMP(); # 2018-03-15 18:58:29 2018-03-15 18:58:29 

j、字符串转日期 data_format

data_format('2019年-08月-20日 09：29：10','%y%m%d %H:%i:s') ——19/08/20 09:29:10

4）、条件判断函数

a、select case [条件] when [结果] then [返回值] when [返回值] then [返回值] else [返回值] end：当查询的条件符合
   某个特定结果时返回对应的值,在case后加‘BINARY’可区分大小写
   select case 11 when 1 then 'one'  when 2 then 'two' else 'more' end; # more
   select case BINARY 'A' when 'a' then 1 when 'b' THEN 2 end; # null
   select case 'A' when 'a' then 1 when 'b' then 2 end; # 1
   select case when [条件] then [返回值1] else [返回值2] end：当查询条件结果正确时返回值1，错误则返回2
   select case when 1>0 then 'true' else 'false' end; # true

b、IF(expr1,expr2,expr3)：如果expr1条件成立则返回expr2，否则返回expr3
   select IF(1>0,'true','flase'); # true 

c、STRCMP(expr1,expr2)：如果字符串相同，STRCMP()返回0，如果第一参数根据当前的排序次序小于第二个，返回-1，否则返回1
   SELECT STRCMP('abc','abc');SELECT STRCMP('bbc','abc');SELECT STRCMP('aabc','bbc'); # 0 1 -1


5）、系统信息函数

a、VERSION()：查询数据库版本号  select VERSION(); # 5.6.3

b、CONNECTION_ID()：返回数据库的连接次数 select CONNECTION_ID(); # 41

v、DATABASE()、SCHEMA()：返回当前数据库名

d、USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER：返回当前用户

e、CHARSET(str)：返回字符串str的字符集 select CHARSET('rr'); # utf8

f、COLLATION(str)：返回字符串str的字符排列方式 select COLLATION('rr'); # utf8_general_ci

g、LAST_INSERT_ID()：返回最后生成的AUTO_INCREMENT值 https://www.cnblogs.com/duanxz/p/3862356.html

6）、加密函数

a、PASSWORD(str)、MD5(str)、SHA(str)（加密长度40）、SHA1(str)（加密长度40）：数据不同方式加密
   SELECT PASSWORD('a'); # *667F407DE7C6AD07358FA38DAED7828A72014B4E 
   SELECT MD5('a')； # 0cc175b9c0f1b6a831c399e269772661
   SELECT SHA('a');  # 86f7e437faa5a7fce15d1ddcb9eaeaea377667b8
   SELECT SHA1('a'); # 86f7e437faa5a7fce15d1ddcb9eaeaea377667b8

  ENCODE(str,pass_str)：用pass_str作为密码加密str
  DECODE(crypt_str,pass_str)：解密crypt_str(使用函数encode加密的结果), pass_str用作密码
  SELECT ENCODE('a','12'); # :
  SELECT DECODE(':','12'); # a


7）、其他函数

a、FORMAT(X,D)：对数字X进行四舍五入并保留D位小数
   SELECT FORMAT(2.605,2); # 2.61

 

 

 

 

 

 

 

 

 

 

 

MySQL总结
qq_70997667的博客
02-07 679
数据库：DataBase（DB），是存储和管理数据的仓库。数据库管理系统：DataBase Management System（DBMS），操纵和管理数据库的大型软件。SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。是现在流行的开源的，免费的关系型数据库由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。免费，开源数据库小巧，功能齐全使用便捷可运行在windows或Linux操作系统。
Mysql总结
学仔仔的博客
01-24 822
1.字段类型隐式转换 没加单引号（‘     ’）后果：索引失效/逻辑错误 2.sql顺序 FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1 ON: 对虚表VT1进行ON筛选，只有那些符合&lt;join-condition&gt;的行才会被记录在虚表VT2中。 JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表...
数据库事务的四大特性和隔离级别
做一个善良的码农
07-25 366
四大特性(ACID) 原子性（Atomicity） 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。 一致性（Consistency） 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致...
MySql数据库函数集
KarlDoenitz的专栏
09-16 4364
MySQL函数集 一、数学函数 ABS(x)                    返回x的绝对值 BIN(x) 　　　　　　　　　　返回x的二进制（OCT返回八进制，HEX返回十六进制） CEILING(x)                返回大于x的最小整数值 EXP(x) 　　　　　　　　　　返回值e（自然对数的底）的x次方 FLOOR(x)　　　　　　　　　返回小于x的
Mysql总结.png
08-04
该脑图专为入门学习mysql的小伙伴所准备的，包含【安装步骤、启动连接、数据库常常用的基本命令[查库、建库、删库、建表、删表]、数据库表详细的增删查改语句以及事例、表的基本操作、索引操作、外键约束、表关联、...
mysql总结xmind版
05-02
关于mysql知识点的xmind总结，包含mysql的基础介绍，普通用法以及高级用法
MySQL大总结
热门推荐
耀的博客
01-23 5万+
MySQL是程序员必须要掌握的，其实语法结构相比于其他的数据结构、Java、C++来说应该算是简单的，只要多多练习，拿下它应该不成问题。本文是将我学过的面试笔试重点的知识进行归纳总结，如果你认为写的不错的话，求点赞，求收藏，感谢！！！ 一、MySQL数据库基础 1，数据库的操作 1.1、显示当前数据库 show databases; 1.2 、创建数据库 语法： CREATE DATABASE [IF NOT EXISTS] db_name [create_specif..
MySQL总结流程图大全
06-14
MySQL是一种流行的关系型数据库管理系统。它是一个开源项目，由瑞典公司MySQL AB创建和维护。MySQL使用SQL语言来存储、查询和管理数据。 MySQL具有以下特点： 1.跨平台：支持多个操作系统，例如Linux、Windows和Mac...
MYSQL总结
u010237121的博客
09-20 564
一、MYSQL语言 数据库语言分类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。 名字 类型 作用对象 作用 DDL 英文全称 (Data Definition Language) 数据定义语言 库、表、索引、视图、同义词、聚簇 创建、删除、修改、库或表结构，对数据库或表的结构操作 DML
mysql总结
weixin_46136875的博客
10-24 1112
mysql总结（深度） 文章目录mysql总结（深度）前言一、存储引擎1、体系结构图连接层服务层引擎层存储层2、2.读入数据三、 SQL优化四、视图/存储过程/触发器五、锁六、InnoDB引擎七、MySQL管理总结 前言 一、存储引擎 1、体系结构图 连接层 是一些客户端和链接服务，主要完成一些类似于链接处理、授权认证、及相关的安全方案。服务器也会为接入的每个客户端验证它所具有的操作权限 服务层 主要完成大多数的核心服务功能。如SQL接口，并完成缓存的查询，SQL的分析和优化。部分内置函数的执行。所有跨
MYSQL 常用命令
weixin_33877092的博客
11-13 293
1.导出整个数据库 mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 &gt; 导出的文件名(数据库默认编码是latin1) mysqldump -u wcnc -p smgp_apps_wcnc &gt; wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名&gt; 导出的文...
MySQL常用命令与操作总结
"这篇文档是关于MySQL数据库常用单词的总结，涵盖了从数据库创建、表的管理、数据类型、约束、查询操作到事务处理和权限控制等多个方面，旨在提供全面的MySQL基础参考。" 在MySQL中，数据定义语言（DDL）是用于创建...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值