MYSQL 查漏补缺+复习

最新推荐文章于 2024-06-16 05:34:45 发布

xiaowei0548

最新推荐文章于 2024-06-16 05:34:45 发布

阅读量166

点赞数

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/xiaowei0548/article/details/107544003

版权

MYSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MYSQL查漏补缺

MYSQL知识扫盲

MYSQL知识扫盲

建表

drop TABLE IF EXISTS test_chw;
CREATE TABLE IF NOT EXISTS test_chw (
	`id` INT (12) NOT NULL AUTO_INCREMENT,
-- 	id int unsigned not null primary key auto_increment comment '主键',
	name varchar(20) default null comment '姓名',
	mobile char(11) default null unique comment '手机号码',
	address varchar(100) default null comment '居住地址',
	description text comment '个人简介', -- 当不确定长度时，可以使用text
	sex char(1) default null comment '性别',
	age tinyint default 0 comment '年龄', -- 年龄可以使用tinyint，范围为-128~127
	idno char(18) default null comment '身份证号码',
	`create_time` datetime NOT NULL DEFAULT '1990-01-01' COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`id`),
  key telephone(mobile), -- 添加索引，提高查询效率
	UNIQUE KEY `uniq` (`idno`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'test';

正则

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

事务

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

MySQL 字符串函数

合并多个字符串，并添加分隔符：

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;

格式化数字 "#,###.##" 形式：

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56

从字符串第一个位置开始的 6 个字符替换为 runoob：

SELECT INSERT("google.com", 1, 6, "runnob");  -- 输出：runoob.com

返回字符串 abc 中 b 的位置：

SELECT LOCATE('b', 'abc') -- 2

返回字符串 runoob 中的前两个字符：

SELECT LEFT('runoob',2) -- ru

比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
SELECT STRCMP("runoob", "runoob");  -- 0
SELECT STRCMP("aunoob", "runoob");  -- -1
SELECT STRCMP("zunoob", "runoob");  -- 1

返回从字符串 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

MySQL 数字函数

返回大于或等于 x 的最小整数　	
SELECT CEIL(1.5) -- 返回2

SELECT 10/3;  -- 3.3333 
SELECT 10%3;  -- 1 取余
SELECT MOD(10,3);  -- 1 取余
SELECT 10 DIV 3;  -- 3 取整
SELECT CEIL(1.8); -- 2 向上取整
SELECT FLOOR(1.8); -- 1 向下取整

	
返回以下数字列表中的最大值：

SELECT GREATEST(3, 12, 34, 8, 25); -- 34


返回以下数字列表中的最小值：

SELECT least(1,2,3,4,-1); -- -1

返回 x 的 y 次方　	
2 的 3 次方：

SELECT POW(2,3) -- 8

返回 0 到 1 的随机数　　	
SELECT RAND() --0.93099315644334
返回离 x 最近的整数	
SELECT ROUND(1.23456) --1
返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	
SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL 日期函数

按表达式 f的要求显示日期 d	
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -- 2011-11-11 11:11:11 AM
SELECT DATE_FORMAT('2011-11-11 11:22:33','%Y-%m-%d %H-%m-%s'); -- 2011-11-11 11-11-33
SELECT DATE_FORMAT('2011-11-11 11:22:33','%Y-%m-%d %s'); -- 2011-11-11 33

返回日期 d 是星期几，如 Monday,Tuesday	
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday

MySQL 高级函数

返回参数中的第一个非空表达式（从左向右）	
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com

MySQL 运算符

1、算术运算符

MySQL 支持的算术运算符包括:

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

1、除

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+
2、商

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+
3、取余

mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

2、比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号	描述	备注
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配

1、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

3、逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号	作用
XOR	逻辑异或

异或：xor 、 ^

mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

工作中遇到的问题/需求

1、输入该月的每一天

SELECT
 DATE(date_add(DATE_ADD('2020-07-28', INTERVAL - DAY('2020-07-28') + 2 DAY),INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY)) as received_second_create_date 
-- ,round(33000*1.0/RIGHT(LAST_DAY('2020-07-28'),2),2) as warehouse_cunchu_fee
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY ( last_day( '2020-07-28' ) ) 
-- HAVING received_second_create_date='2020-07-28'
ORDER BY
	help_topic_id
;
结果：
received_second_create_date
2020-07-01
2020-07-02
2020-07-03
2020-07-04
2020-07-05
2020-07-06
2020-07-07
2020-07-08
2020-07-09
2020-07-10
2020-07-11
2020-07-12
2020-07-13
2020-07-14
2020-07-15
2020-07-16
2020-07-17
2020-07-18
2020-07-19
2020-07-20
2020-07-21
2020-07-22
2020-07-23
2020-07-24
2020-07-25
2020-07-26
2020-07-27
2020-07-28
2020-07-29
2020-07-30
2020-07-31

2、列转行

SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.name, ',', b.help_topic_id + 1 ), ',',- 1 ) AS tmp_optimizer_name
FROM
    (SELECT 'aaa,bbb,ccc,ddd,eee' as name) AS a
    JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( a.name ) - length( REPLACE ( a.name, ',', '' ) ) + 1 ) 
;
结果：
tmp_optimizer_name
aaa
bbb
ccc
ddd
eee

Mysql笔记

xiaowei0548

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL 查漏补缺+复习

MYSQL查漏补缺MYSQL知识扫盲建表正则事务MySQL 字符串函数MySQL 数字函数MySQL 日期函数生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入MYSQL知识扫盲建表drop TABLE IF EXISTS test_chw;CREATE TABLE IF NOT EXISTS test_chw ( `i
复制链接

扫一扫