mysql SQL语句设计 笔记

最新推荐文章于 2022-07-31 11:41:49 发布
最新推荐文章于 2022-07-31 11:41:49 发布
阅读量248 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_28710983/article/details/103795278
版权

products表 

/*
Navicat MySQL Data Transfer

Source Server         : 本地windows上的mysql
Source Server Version : 50626
Source Host           : 192.168.222.1:3306
Source Database       : yii

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2017-04-07 00:53:36
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `products`
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_name` varchar(30) DEFAULT NULL,
  `p_type` varchar(20) DEFAULT NULL,
  `p_view` int(11) DEFAULT NULL,
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('1', '西瓜', '水果类', '21');
INSERT INTO `products` VALUES ('2', '瓜子', '干果类', '32');
INSERT INTO `products` VALUES ('22', '苹果', '水果类', '32');
INSERT INTO `products` VALUES ('28', '桔子', '水果类', '33');
INSERT INTO `products` VALUES ('32', '香蕉', '水果类', '21');
INSERT INTO `products` VALUES ('35', '花生', '干果类', '3');
INSERT INTO `products` VALUES ('37', '猪肉', '生鲜类', '5');
INSERT INTO `products` VALUES ('48', '牛肉', '生鲜类', '23');
INSERT INTO `products` VALUES ('60', '开心果', '干果类', '56');
INSERT INTO `products` VALUES ('61', '鸡翅', '生鲜类', '23');
INSERT INTO `products` VALUES ('77', '樱桃', '水果类', '41');
INSERT INTO `products` VALUES ('87', '杜蕾斯', '其他类', '123');
INSERT INTO `products` VALUES ('102', '开瓶器', '其他类', '88');
INSERT INTO `products` VALUES ('114', '五花肉', '生鲜类', '4');

sql语句加上行号

select p_name, p_type, p_view, @rownum:= @rownum + 1 as rnum from products a, (select @rownum:=0) b

2.分组后在分组内排序、每个分组中取前N条

select p_type, p_name, p_view, row_num from 
   (select p_type, p_name, p_view, IF(@bak=p_type, @rownum:=@rownum+1, @rownum:=1) as row_num, @bak:=p_type
   from 
   (select p_type, p_name, p_view from products order by p_type, p_view desc) a, (select @rownum:=0, @bak:='') b ) c
   where c.row_num <=2

4.自连接查询的妙用: 推荐人统计

webusers

/*
Navicat MySQL Data Transfer

Source Server         : 本地windows上的mysql
Source Server Version : 50626
Source Host           : 192.168.222.1:3306
Source Database       : yii

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2017-04-16 00:05:40
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `webusers`
-- ----------------------------
DROP TABLE IF EXISTS `webusers`;
CREATE TABLE `webusers` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT,
  `u_name` varchar(20) NOT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of webusers
-- ----------------------------
INSERT INTO `webusers` VALUES ('1', '张三', '0');
INSERT INTO `webusers` VALUES ('2', '大胖胖', '0');
INSERT INTO `webusers` VALUES ('3', '李四', '2');
INSERT INTO `webusers` VALUES ('4', '大长脸', '2');
INSERT INTO `webusers` VALUES ('5', '小朱', '1');
INSERT INTO `webusers` VALUES ('6', '小狗', '5');
INSERT INTO `webusers` VALUES ('7', '刘九', '5');

SQL语句

select b.u_name, a.referee from (    
    select GROUP_CONCAT(u_name, u_id SEPARATOR '|') as referee, p_id from webusers GROUP by p_id )a
    inner join webusers b where a.p_id = b.u_id

6、有重复数据不插入或更新

insert into user(username, user_qq)values("shenyi","123123")
    on DUPLICATE key update user_updatetime=now(), user_qq = values(user_qq)

5、找到重复数据、并删除之

/*
Navicat MySQL Data Transfer

Source Server         : 本地windows上的mysql
Source Server Version : 50626
Source Host           : 192.168.222.1:3306
Source Database       : yii

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2017-04-19 00:57:39
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `reviews`
-- ----------------------------
DROP TABLE IF EXISTS `reviews`;
CREATE TABLE `reviews` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `r_content` varchar(2000) NOT NULL,
  `r_userid` int(11) NOT NULL,
  `news_id` int(11) NOT NULL,
  PRIMARY KEY (`r_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of reviews
-- ----------------------------
INSERT INTO `reviews` VALUES ('1', '不错啊', '101', '5');
INSERT INTO `reviews` VALUES ('2', '很好的文章', '102', '6');
INSERT INTO `reviews` VALUES ('3', '作者用心了', '103', '5');
INSERT INTO `reviews` VALUES ('4', '顶赞', '102', '7');
INSERT INTO `reviews` VALUES ('5', '不错啊', '101', '5');
INSERT INTO `reviews` VALUES ('6', '不错啊', '101', '5');
INSERT INTO `reviews` VALUES ('7', '写的不错', '105', '7');
INSERT INTO `reviews` VALUES ('8', '很好的文章', '102', '6');
INSERT INTO `reviews` VALUES ('9', '很好的文章', '102', '6');
INSERT INTO `reviews` VALUES ('10', '知道了', '108', '11');

SQL语句过程

1. 按content,userid分组查看重复次数
    select r_content r_userid,count(*) from reviews
    GROUP BY r_content, r_userid

    2. having 是对group by的数据再进行过滤， 和where不一样
    select r_content r_userid,count(*) as num from reviews
    GROUP BY r_content, r_userid having  num > 1

    3. 取出reviews中重复数据的ID
    sselect a.r_id from reviews a inner join 
    (
    select r_content, r_userid,count(*) as num from reviews
    GROUP BY r_content, r_userid having  num > 1
    s)b on a.r_content = b.r_content and a.r_userid = b.r_userid

    4. 加入行号，要留一条数据，不要全部删除了
    select r_id from (
    select a.r_id, IF(@tmp=CONCAT(r_content,r_userid),@rownum:=@rownum+1, @rownum:=1) as row_num, @tmp:=CONCAT(r_content,r_userid) from 
    (select a.* from reviews a inner join 
    (select r_content, r_userid,count(*) as num from reviews
    GROUP BY r_content, r_userid having  num > 1 )b on a.r_content = b.r_content and a.r_userid = b.r_userid) a,(select @rownum:=0,@tmp:='') b)a 
    where a.row_num > 1

7、更新数据技巧之: update表子查询、都条件判断

/*
Navicat MySQL Data Transfer

Source Server         : 本地windows上的mysql
Source Server Version : 50626
Source Host           : 192.168.222.1:3306
Source Database       : myweb

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2017-05-04 23:49:23
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user_level`
-- ----------------------------
DROP TABLE IF EXISTS `user_level`;
CREATE TABLE `user_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `user_total` decimal(10,2) DEFAULT NULL,
  `user_rank` varchar(10) DEFAULT '吃瓜',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_level
-- ----------------------------
INSERT INTO `user_level` VALUES ('1', '张三', '5.00', '吃瓜');
INSERT INTO `user_level` VALUES ('2', '李四', '30.30', '吃瓜');
INSERT INTO `user_level` VALUES ('3', '赵龙', '22.00', '吃瓜');
INSERT INTO `user_level` VALUES ('4', '王五', '489.00', '黄金用户');
INSERT INTO `user_level` VALUES ('5', '刘飞', '175.00', '黄金用户');
INSERT INTO `user_level` VALUES ('6', '王菲', '123.00', '白金用户');
INSERT INTO `user_level` VALUES ('7', '章子怡', '101.00', '吃瓜');
INSERT INTO `user_level` VALUES ('8', '陈晨', '20.00', '吃瓜');
INSERT INTO `user_level` VALUES ('9', '老蒋', '11.00', '吃瓜');

SQL语句

update user_level, (select avg(user_total) as avgtotal from user_level) b set user_rank =
case 
   when round(user_total / b.avgtotal) >= 1 and round(user_total / b.avgtotal)< 2 then '白金用户2'
   when round(user_total / b.avgtotal) >= 2 then '黄金用户2'
else '吃瓜'
END
where user_total > avgtotal 

上面这个SQL查分两条sql理解
请求大于平均值的数据
select id from user_level where user_total > (select avg(user_total) as avgtotal from user_level)

然后循环更新
update user_level set user_rank
case 
   when round(user_total / avgtotal) >= 1 and round(user_total / avgtotal)< 2 then '白金用户2'
   when round(user_total / avgtotal) >= 2 then '黄金用户2'
else '吃瓜'
END
where id in (1,2,3,4,5,6)

8、利用order实现排名作弊

场景: 比如游戏里面做排行榜,需要弄几个假的用户 排在排行榜的前面

mysql特有语法
select * from user_level order by id in(4,6,2) desc, user_total desc


正常思维:  把246和非246排序好以后。联合起来
select * from (select * from user_level where id in (2,4,6) order by user_total desc) a
union
select * from (select * from user_level where id not in(2,4,6) order by user_total )b

9、获取连续签到X天的用户列表

/*
Navicat MySQL Data Transfer

Source Server         : 本地windows上的mysql
Source Server Version : 50626
Source Host           : 192.168.222.1:3306
Source Database       : myweb

Target Server Type    : MYSQL
Target Server Version : 50626
File Encoding         : 65001

Date: 2017-05-11 16:43:17
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user_sign`
-- ----------------------------
DROP TABLE IF EXISTS `user_sign`;
CREATE TABLE `user_sign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `sign_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_sign
-- ----------------------------
INSERT INTO `user_sign` VALUES ('1', 'shenyi', '2017-04-01');
INSERT INTO `user_sign` VALUES ('2', 'shenyi', '2017-04-02');
INSERT INTO `user_sign` VALUES ('3', 'zhangsan', '2017-04-01');
INSERT INTO `user_sign` VALUES ('4', 'zhangsan', '2017-04-02');
INSERT INTO `user_sign` VALUES ('5', 'shenyi', '2017-04-03');
INSERT INTO `user_sign` VALUES ('6', 'shenyi', '2017-04-04');
INSERT INTO `user_sign` VALUES ('7', 'lisi', '2017-04-01');
INSERT INTO `user_sign` VALUES ('8', 'shenyi', '2017-04-04');
INSERT INTO `user_sign` VALUES ('9', 'zhangsan', '2017-04-03');
INSERT INTO `user_sign` VALUES ('10', 'shenyi', '2017-04-05');
INSERT INTO `user_sign` VALUES ('11', 'shenyi', '2017-04-16');
INSERT INTO `user_sign` VALUES ('12', 'zhangsan', '2017-04-16');
INSERT INTO `user_sign` VALUES ('13', 'lisi', '2017-04-16');
INSERT INTO `user_sign` VALUES ('14', 'shenyi', '2017-04-17');
INSERT INTO `user_sign` VALUES ('15', 'shenyi', '2017-04-18');
INSERT INTO `user_sign` VALUES ('16', 'zhangsan', '2017-04-17');
INSERT INTO `user_sign` VALUES ('17', 'zhangsan', '2017-04-18');
INSERT INTO `user_sign` VALUES ('18', 'zhangsan', '2017-04-19');

SQL语句，查询连续签到超过3天的用户

select c.user_name,c.sign_date,c.num from (
select user_name,sign_date,IF(@pre=user_name and DATEDIFF(sign_date,@pre_date)=1,@rownum:=@rownum+1,@rownum:=1) as num,
@pre:=user_name,@pre_date:=sign_date
 from (
select user_name,sign_date from user_sign
GROUP BY user_name,sign_date ORDER BY user_name   ,sign_date  ) a ,(select @pre:='',@rownum:=0,@pre_date:='' ) b ) c
where c.num > 3

 

qq_28710983
关注
专栏目录
mysql8导出5的sql_Mysql 8.0版本导出的sql文件在Mysql 5.5中运行出错
weixin_30675619的博客
02-02 755
出现错误[err] 1064 - you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near '(0) null default current_timestamp(0) on update ...
MySQL语句设计
weixin_34342207的博客
09-25 59
表数据插入：1、insert into 表名 （字段名...）values（’值‘）；扩展：2、insert into 表名 select*from 表名2；3、insert into 表名（字段名...）select 对应的字段名... from 表名2 where 条件 limit 10；4、insert into bbs_user values(null,'xiaoho...
MySQL-03 SQL语句设计
weixin_30436891的博客
01-12 342
学习要点 SQL语句分类 DML语句 DML 查询语句 SQL语句分类 数据操纵语言（DML）：用来操纵数据库中数据的命令。包括：SELECT、INSERT、UPDATE、DELETE。 数据定义语言（DDL）：用来建立数据库、数据库对象和定义列的命令。包括：CREATE、DROP、ALTER。 数据控制语言（DCL）：用来控制数据库组件的存取许可、权限等的命令。...
mysql 列转行
婲落ヽ红颜谇的博客
03-31 155
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50528 Source Host : localhost:3306 Source Database : test Target Server Type : MYSQL ...
SpringBoot整合MyBatis框架（学习笔记六）
Li_RC_XCN的博客
07-31 144
MyBatis 最强大的特性之一就是它的动态语句功能。如果您以前有使用JDBC或者类似框架的经历，您就会明白把SQL语句条件连接在一起是多么的痛苦，要确保不能忘记空格或者不要在columns列后面省略一个逗号等。动态语句能够完全解决掉这些痛苦。...
MySQL数据库sql语句增删改查笔记
10-10
### MySQL数据库SQL语句增删改查笔记 #### 一、插入数据（Insert） 在MySQL中，`INSERT INTO`语句用于向表中插入新记录。语法格式为： ```sql INSERT INTO 表名 (列1, 列2, ..., 列n) VALUES (值1, 值2, ..., 值n...
sql_node-master.zip_MYSQL_node笔记_sql
最新发布
09-25
在本压缩包“sql_node-master.zip_MYSQL_node笔记_sql”中，主要涵盖了使用Node.js进行MySQL数据库操作的相关知识。Node.js是一种基于Chrome V8引擎的JavaScript运行环境，它以其高效、非阻塞I/O和事件驱动的特点，...
MySQL笔记.zip_MySQL数据库_SQL__MySQL数据库_SQL_
08-09
这份“MySQL笔记”包含了丰富的SQL语言知识，对于初学者和经验丰富的开发者来说都是一份宝贵的资源。 笔记首先可能从基础概念开始，讲解数据库的基本原理，如数据模型（包括层次、网状和关系模型），以及MySQL...
day01-sql安装与SQL语句-笔记.zip
09-25
在本压缩包“day01-sql安装与SQL语句-笔记.zip”中，我们可以预见到包含的内容主要是关于SQL（Structured Query Language）的基础知识，包括SQL的安装过程以及基本的SQL语句。SQL是用于管理和处理关系数据库的强大...
java网络编程——网络版题库程序
08-25
java网络编程——网络版题库程序（主要技术java+socket+多线程+mysql），程序是C/S 版本的，由于服务器端是java写的，所有客户端使用android也是很容易与服务器对接的
MySQL用source命令执行外部sql脚本文件时出现failed to open file的错误可能之一
热门推荐
hongyunqing1994的博客
10-22 1万+
在学习《MySQL必知必会》时，首先要在建立的crashcourse空数据库中加入表，所以要执行外部的sql文件。在cmd命令行中通过source命令执行sql脚本文件的步骤如下： 1、连接数据库：在命令行中输入mysql -u root -p; （若没有密码，直接输入mysql即可） 2、进入crashcourse数据库：USE crashcourse;  3、输入source命令语句：s
查看mysql server版本信息_如何查看MySQL软件版本信息
weixin_28994935的博客
01-18 2412
一、问题描述今日上午接到同事的召唤，让帮着给查看一下生产数据MySQL数据库软件版本。呵呵，对于小白的我，当然是先答应后学习喽。并且漂亮的完成任务。二、实验(非生产环境)1.登录mysql环境时，有服务版本号，此处为5.6.22[root@linuxidc ~]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your ...
sql
Xu_yan的博客
11-06 1177
/* Navicat MySQL Data Transfer Source Server         : localhost Source Server Version : 50717 Source Host           : localhost:3306 Source Database       : travel Target Server Type   
sql地区表最新
宋
11-23 2507
/* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50547 Source Host : 127.0.0.1:3306 Source Database : ceshi Target Server Type : MYSQL Targ
sql -- update表子查询、多条件判断case when
weixin_30352191的博客
07-11 2275
表结构: 需求 思路: 求出平均数 select avg(user_total) as avg from user_level 更新他的等级 update user_level set user_rank= xxx where user_total >= 平均数 when case 表达式: case when ...
MySQL SQL语句精华笔记
"这篇笔记涵盖了SQL语句的多种主要功能，包括数据操作、数据定义、数据控制、事务控制以及程序化SQL等。它提供了一系列的关键命令，如SELECT用于查询数据，INSERT用于添加新数据，DELETE用于删除数据，UPDATE用于...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值