MySQL使用学习记录

最新推荐文章于 2024-09-20 21:26:51 发布
最新推荐文章于 2024-09-20 21:26:51 发布
阅读量284 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 函数 datetime
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cheetahlover/article/details/77371700
版权
  • 2017.8.18 find_in_set函数
    find_in_set函数

FIND_IN_SET(str,strlist)

假如字符串str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。
一个字符串列表就是一个由一些被‘,’符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则 FIND_IN_SET() 函数被优化，使用比特计算。
如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

基本用法：

select FIND_IN_SET('2','1，2');返回2 
select FIND_IN_SET('6','1'); 返回0

关联查询：用(x,y,z)存储同表或另一张表中的字段值，如多个id（以逗号隔开），关联查询多条记录。

SELECT
	*
FROM
	zz,
	classify c
WHERE
	FIND_IN_SET(
		c.id,
		(
			SELECT
				zzid
			FROM
				zz
			WHERE
				zz.id = 1
		)
	)

结果：
mysql中find_in_set函数

  • 2017.8.21 truncate table清空表并重置自增id
    使用truncate 表名清空某张表并重置该表的自增id，但无法操作有外键约束的表
  • 2017.9.2记录 MySQL删除一个数据库中的所有表
  1. 用图形工具多选删除。
  2. 先用select语句获取所有表的删除语句，然后全选复制执行。有外键的需要先暂时关闭外键约束检查。
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='数据库名';

set foreign_key_checks=0;     //关闭外键检查

set foreign_key_checks=1;  //打开外键检查

//执行第一条select的复制结果
drop table account;
drop table address;
drop table admin;
drop table admin_authority;
……
…
  • 2017.11.1 Date相关，按天、周、月、年统计
知识关键词：DATE_FORMAT 
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;  
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;  
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months; 
DATE_FORMAT(date,format) 

根据format字符串格式化date值。下列修饰符可以被用在format字符串中： 
%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
%Y 年, 数字, 4 位   www.2cto.com  
%y 年, 数字, 2 位 
%a 缩写的星期名字(Sun……Sat) 
%d 月份中的天数, 数字(00……31) 
%e 月份中的天数, 数字(0……31) 
%m 月, 数字(01……12) 
%c 月, 数字(1……12) 
%b 缩写的月份名字(Jan……Dec) 
%j 一年中的天数(001……366) 
%H 小时(00……23) 
%k 小时(0……23) 
%h 小时(01……12) 
%I 小时(01……12) 
%l 小时(1……12) 
%i 分钟, 数字(00……59) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%T 时间,24 小时(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 一个星期中的天数(0=Sunday ……6=Saturday ） 
%U 星期(0……52), 这里星期天是星期的第一天 
%u 星期(0……52), 这里星期一是星期的第一天 
%% 一个文字“%”。

示例

当天
SELECT
	*
FROM
	orders
WHERE
	TO_DAYS(now()) = TO_DAYS(pay_datetime)

SELECT
	*
FROM
	orders
WHERE
	date(now()) = date(pay_datetime)


一周
SELECT
	*
FROM
	`orders`
WHERE
	date(
		date('2017-10-13 18:31:24') - WEEKDAY(
			date('2017-10-13 18:31:24')
		)
	) <= pay_datetime
AND pay_datetime <= DATE_ADD(
	date(
		date('2017-10-13 18:31:24') - WEEKDAY(
			date('2017-10-13 18:31:24')
		)
	),
	INTERVAL 6 DAY);

一周 (yearweek(date,first)),first参数取1即可
SELECT
	*
FROM
	orders
WHERE
	yearweek('2017-11-01', 1) = yearweek(pay_datetime, 1);


SELECT
	*
FROM
	`orders`
WHERE
	date(pay_datetime) > last_day('2017-10-02');

当月第一天
SELECT
	date(
		concat(DATE_FORMAT('2017-10-02', '%Y-%m'),'-','1')
	);


一月
SELECT
	*
FROM
	orders
WHERE
	DATE_FORMAT(pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

一月
SELECT
	*
FROM
	`orders`
WHERE
	date(
		concat(
			YEAR ('2017-10-13 18:31:24'),
			'-',
			MONTH (curdate()),
			'-',
			'1'
		)
	) < pay_datetime
AND pay_datetime < LAST_DAY('2017-10-13 18:31:24');


当年
SELECT
	*
FROM
	orders
WHERE
	YEAR (now()) = YEAR (pay_datetime)
  • 2017.11.21 statistics report
//每月销售报总表 

	SELECT
	g.goods_name goodsName,
	g.m_price price,
	sum(sku_count) saleCount,
	count(DISTINCT(og.order_id)) orderCount,
	sum(sex = '男') AS male,
	sum(sex = '女') AS female,
	sum(
		(YEAR(now()) - YEAR(birthday)) < 20
	) ltTwenty,
	sum(
		(YEAR(now()) - YEAR(birthday)) >= 20
		AND (YEAR(now()) - YEAR(birthday)) <= 30
	) twenty2thirty,
	sum(
		(YEAR(now()) - YEAR(birthday)) > 30
		AND (YEAR(now()) - YEAR(birthday)) <= 40
	) thirty2forty,
	sum(
		(YEAR(now()) - YEAR(birthday)) > 40
		AND (YEAR(now()) - YEAR(birthday)) <= 50
	) forty2fifty,
	sum(
		(YEAR(now()) - YEAR(birthday)) > 50
	) gtFifty
FROM
	order_goods og
INNER JOIN goods g ON (og.goods_id = g.id)
INNER JOIN orders o ON (og.order_id = o.id)
INNER JOIN muser m ON (o.muser_id = m.id)
WHERE
	DATE_FORMAT(pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
GROUP BY
	goodsName
ORDER BY
	saleCount DESC;

地区销售占比表 SELECT
	a.city,
	sum(total_price) turnover,
	count(*) orderCount,
	ROUND(
		(
			sum(total_price) / (
				SELECT
					sum(total_price)
				FROM
					orders
				WHERE
					DATE_FORMAT(orders.pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
			)
		),
		4
	) cityTurnoverRate,
	count(*) orderCount,
	ROUND(
		(
			count(*) / (
				SELECT
					count(*)
				FROM
					orders
				WHERE
					DATE_FORMAT(orders.pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
			)
		),
		4
	) cityCountRate,
	sum(sex = '男') AS male,
	sum(sex = '女') AS female,
	sum(
		(YEAR(now()) - YEAR(birthday)) < 20
	) ltTwenty,
	sum(
		(YEAR(now()) - YEAR(birthday)) >= 20
		AND (YEAR(now()) - YEAR(birthday)) <= 30
	) twenty2thirty,
	sum(
		(YEAR(now()) - YEAR(birthday)) > 30
		AND (YEAR(now()) - YEAR(birthday)) <= 40
	) thirty2forty,
	sum(
		(YEAR(now()) - YEAR(birthday)) > 40
		AND (YEAR(now()) - YEAR(birthday)) <= 50
	) forty2fifty,
	sum(
		(YEAR(now()) - YEAR(birthday)) > 50
	) gtFifty
FROM
	orders o
INNER JOIN muser m ON (o.muser_id = m.id)
INNER JOIN address a ON (m.id = a.muser_id)
WHERE
	a.tag = '0'
AND DATE_FORMAT(o.pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
GROUP BY
	a.city
ORDER BY
	orderCount DESC 每月区域销售表 SELECT
		g.goods_name goodsName,
		g.m_price price,
		sum(sku_count) saleCount,
		a.city,
		sum(sex = '男') AS male,
		sum(sex = '女') AS female,
		sum(
			(YEAR(now()) - YEAR(birthday)) < 20
		) ltTwenty,
		sum(
			(YEAR(now()) - YEAR(birthday)) >= 20
			AND (YEAR(now()) - YEAR(birthday)) <= 30
		) twenty2thirty,
		sum(
			(YEAR(now()) - YEAR(birthday)) > 30
			AND (YEAR(now()) - YEAR(birthday)) <= 40
		) thirty2forty,
		sum(
			(YEAR(now()) - YEAR(birthday)) > 40
			AND (YEAR(now()) - YEAR(birthday)) <= 50
		) forty2fifty,
		sum(
			(YEAR(now()) - YEAR(birthday)) > 50
		) gtFifty
	FROM
		order_goods og
	INNER JOIN goods g ON (og.goods_id = g.id)
	INNER JOIN orders o ON (og.order_id = o.id)
	INNER JOIN muser m ON (o.muser_id = m.id)
	INNER JOIN address a ON (m.id = a.muser_id)
	WHERE
		DATE_FORMAT(pay_datetime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
	AND a.tag = '0'
	GROUP BY
		goodsName,
		a.city
	ORDER BY
		goodsName,
		saleCount DESC;

//订单年月查询
SELECT
	sum(summ),
	paymonthflag
FROM
	(
		SELECT
			sum(total_price) summ,
			count(*) cnt,
			count(pay_datetime),
			MONTH (pay_datetime) paymonthflag,
			MONTH (gmt_datetime) monthflag
		FROM
			orders
		WHERE
			YEAR (gmt_datetime) = 2017
		GROUP BY
			monthflag,
			paymonthflag
	) AS zz
WHERE
	paymonthflag IS NOT NULL
GROUP BY
	paymonthflag;

SELECT
	sum(total_price) monthPay,
	sum(delivery_money) monthDelivery,
	sum(coupon_val) monthCoupon,
	count(*) monthPayCount,
	MONTH (pay_datetime) monthPayFlag
FROM
	orders
WHERE
	YEAR (pay_datetime) = 2017
GROUP BY
	monthPayFlag;

SELECT
	sum(total_price)
FROM
	orders
WHERE
	YEAR (pay_datetime) = 2017;

//购买次数 
SELECT
	count(1) purchaseCount
FROM
	(
		SELECT
			COUNT(1) countP
		FROM
			muser m
		LEFT JOIN orders o ON (m.id = o.muser_id)
		WHERE
			o.pay_type IS NOT NULL
		AND m.user_name IS NOT NULL
		GROUP BY
			m.id
		HAVING
			count(m.id) >= 2
	) t
  • MySQL sum()函数参数为表达式

原数据：

select * from student;
原数据

sum条件表达式命令：

SELECT class_id, SUM(sex = '男') AS male, SUM(sex = '女') AS female FROM student
GROUP BY class_id

结果：
结果

  • 2018.2.28 参考数据库设计范式

    不一定要完全遵循范式，只是作为参考，例如多图上传只用一个字段逗号将url隔开不符合第一范式，但不影响核心业务且能简化实现。

第一范式（1NF）属性不可分。是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第三范式（3NF）在1NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

巴斯-科德范式（BCNF）Boyce-Codd Normal Form（巴斯-科德范式）在1NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）

***********************前四个常用************************

第四范式（4NF），要求把同一表内的多对多关系删除。

第五范式（5NF），又称完美范式，从最终结构重新建立原始结构。

  • 2018.3.21 group_concat()函数
    MySQL ***GROUP_CONCAT()***函数将组中的非NULL字符串连接成为具有各种选项的单个字符串
    group_concat
    DISTINCT去重，ORDER BY排序，SEPARATOR指定分隔符(默认逗号)。更重要的是还可以和group by配合使用连接各个组中的指定字段
SELECT 
    employeeNumber,
    firstName,
    lastName,
    GROUP_CONCAT(DISTINCT customername
        ORDER BY customerName)
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;
  • case when

case when (条件) then XX when (条件) else XX end，为指定值赋值排序，相当于oracle中的decode，可以在select （case when …）as 结果字段，当作结果，也可以用于特定值优先的排序。
如3月份的在最前，其他月份排后，可以用order by case when month=3 then 1 else 2 end asc 来排序。

SELECT 
(case term_month when term_month=3  then 1 when (term_month<>0 and term_month<>3) then 2 else 3 end) zzz

FROM ZXZ

ORDER BY money_level desc,
case term_month when term_month=3  then 1 
when (term_month<>0 and term_month<>3) then 2 else 3 end,
case length when 45 then 3 when 30 then 4 else 5 end
  • 2018.7.25 MySQL比较两个时间字段大小，取较大者,case when
    两个时间字段中有可能有的为NULL，先取时间not null的，都有时间则取较大者。
    思路：用case when配合if语句将取值统一用时间戳比较大小，NULL作为时间戳的0。
select 
(CASE WHEN IF (time_field_A IS NULL,0,UNIX_TIMESTAMP(time_field_A)) > IF (time_field_B IS NULL,0,UNIX_TIMESTAMP(time_field_B)) THEN time_field_A ELSE time_field_B END) greater_time
from table
  • 对于select 语句，innodb不会加任何锁，也就是可以多个并发去进行select的操作，不会有任何的锁冲突，因为根本没有锁。
  • 对于insert，update，delete操作，innodb会自动给涉及到的数据加排他锁，只有查询select需要我们手动设置排他锁。
cheetahlover
关注
专栏目录
mysql高级篇学习笔记
java
07-28 7294
尚硅谷宋红康康师傅mysql高级篇学习
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用程序中被广泛使用。了解并掌握MySQL对于任何想要从事IT行业的人员，尤其是数据管理、开发或系统管理员来说，都是至关重要的技能。 一、为什么学习...
MySQL 学习记录（表的操作）
退役ACMer
02-25 570
表的操作表创建use database_name;/**先选择数据库(表操作前提)*/ create table table_name( field_name1 int, field_name2 varchar(20) );表查看use database_name;/**先选择数据库(表操作前提)*/ desc table_name;/**查看表名为table_name的表的结构(字
MySQL学习笔记8——游标
qq_44778824的博客
04-24 2074
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
第二十四章 MySQL学习笔记
你好, 我在学java的博客
01-23 1万+
存放文本时，也可以使用Text数据类型，可以将TEXT列视为VARCHAR列，注意Text不能有默认值，大小0-2^16字节；同一查询在同一事务中多次进行，由于其它提交事务所做的修改和删除，每次返回不同的结果集，则发生不可重复读；多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据是的准确性；同一查询在同一个事务中多次执行，由于其它提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读；同真是的表一样，视图包含列，其数据来自对应的真实表(基表)
MySQL的知识点整理（学习笔记）
随心笔记
06-06 7847
MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。Mysql是开源的，所以你不需要支付额外的费用。Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL（黑马程序员学习笔记）
weixin_59368946的博客
04-12 1489
黑马程序员MySQL学习笔记
MySQL学习笔记5——函数和索引
qq_44778824的博客
04-22 2483
- 删除索引DROP INDEX 索引名 ON 表名；--删除主键索引ALTER TABLE 表名 DROP PRIMARY KEY;索引的成本存储空间的开销，是指索引|需要单独占用存储空间。数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索弓|能够指向正确的记录。因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。
MySQL学习心得体会
2303_82269754的博客
06-23 1612
一、引言在当今信息化时代，数据成为了企业运营和决策的重要支撑。而数据库作为数据的核心存储和管理系统，其重要性不言而喻。MySQL作为一款开源的关系型数据库管理系统，因其高效、稳定、易用等特点，得到了广泛的应用。作为一名IT从业者，我深感学习MySQL的必要性和紧迫性。在过去的几个月里，我投入了大量的时间和精力来学习MySQL，现在将我的学习心得体会分享出来，希望能对大家有所帮助。二、学习MySQL的初衷与准备我学习MySQL的初衷是为了提高自己的数据库操作能力，为未来的职业发展打下坚实的基础。
尚硅谷MySQL学习笔记
热门推荐
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
MYSQL学习过程笔记
zhineng2LizZ_的博客
02-06 472
包括但不限于增删改查基础sql语句 索引 优化 键 多表联查 子查询 left join 锁 触发器 事务等相关知识及同步练习代码。记录了学习mysql数据库以来基础和进阶篇的知识。
MySQL数据库学习笔记.pdf
07-14
总结来说，MySQL数据库的学习涵盖了数据库的基础概念、SQL语言的使用、数据类型的选择、索引优化以及变量和运算等内容，这些是构建和维护高效数据库系统的基础。通过深入理解和实践，你可以更好地掌握这个强大的...
MySQL学习笔记MySQL学习笔记
05-03
学习MySQL不仅包括了数据库的基本概念，还包括了SQL语言的使用，如查询、插入、更新和删除数据，以及数据库设计和管理。 首先，数据库是存储和组织数据的系统，它提供了数据的安全性、一致性和可访问性。在描述中...
MySQL个人学习笔记
04-10
个人学习MySQL的一套学习笔记，记录了我所学的各种MySQL语句及其知识要点，采用思维导图的方式记录，逻辑清晰，分类准确，方便查找所需的知识点，也方便随时打开查阅，可以拿来复习、学习、或者查阅相应语句，也可以...
MySQL5.7学习笔记.pdf
09-07
MySQL 5.7学习笔记 数据库概述： 数据库（Database）是一种存储数据的“仓库”，其本质是一个文件系统，保存了一系列有组织的数据。数据库管理系统（Database Management System）是一种操纵和管理数据库的大型...
基于SpringBoot+Vue+MySQL的养老院管理系统
最新发布
程序员大金的博客
09-20 606
基于SpringBoot+Vue+MySQL的养老院管理系统，附源码。
MySQL中去除重复
hdjag的博客
09-18 282
示例：查询employees表，显示唯一的部门ID。
MySQL练手题--体育馆的人流量(困难)
weixin_66919047的博客
09-15 706
此题稍微微有一点难度，如果看不明白可以对题目进行分解分析，分析出一步之后再接下一步来查询，题目已经要求人数大于等于100且id连续三行，就可以先筛选出大于等于100 的id 然后排序求差 差值相等的均为id连续的 ，最后筛选差值总数大于等于3 的，便出来了最后结果；
Mysql 面试题
AVICCI的博客
09-18 1384
SQL注入漏洞是一种常见的网络安全攻击方式，攻击者通过在输入的数据中注入恶意SQL代码，从而在后台数据库中执行非法的SQL操作，例如删除、修改、插入、查询等，从而获得敏感信息或者破坏数据库的完整性。为了避免SQL注入漏洞，可以采取以下措施：使用预编译语句或参数化查询来防止 SQL 注入，这样可以将 SQL 语句与用户输入的数据分离开来，有效地避免了 SQL 注入漏洞。对用户输入的数据进行严格的数据验证和过滤，过滤掉不合法的字符，例如单引号、双引号等。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值