mysql CRUD

2019-08-28 鲁鲁槟 收藏

一、插入

1.1、迅速插入百万条测试数据

①、新建表

create table test(
   id INT NOT NULL AUTO_INCREMENT,
   value SMALLINT(1) NOT NULL,
   PRIMARY KEY ( id )
);

②、先用PHP代码生成数据,再导入

<?php 
$t=mktime(); 
set_time_limit(1000); 
$myFile="e:/insert.sql"; 
$fhandler=fopen($myFile,wb); 
if($fhandler){ 
    $sql="265"; 
    $i=0; 
    while($i<10000000)
    { 
        $i++; 
        fwrite($fhandler,$sql."\r\n"); 
    } 
    echo"写入成功,耗时:",mktime()-$t; 
}

③、然后再导入

LOAD DATA local INFILE e:/insert.sql INTO TABLE test(`value`);

④、查询数量

select count(*) from test;

01.png

二、更新

三、删除

3.1、清空表数据后如何让自增ID仍从1开始

truncate test;

四、查询

4.1、一个查询案例

①、数据文件

tab_goods.sql

01.png

②、求所有电脑产品的平均价格,并且保留两位小数

SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;

查询所有价格大于平均价格的商品,并且按价格降序排序

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5845.10 ORDER BY goods_price DESC;

使用子查询来实现

SELECT goods_id,goods_name,goods_price FROM tdb_goods 
WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods) 
ORDER BY goods_price DESC;

③、查询类型为“超记本”的商品价格

SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';

查询价格大于或等于"超级本"价格的商品,并且按价格降序排列

SELECT goods_id,goods_name,goods_price FROM tdb_goods 
WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;

= ANY 或 = SOME 等价于 IN

SELECT goods_id,goods_name,goods_price FROM tdb_goods 
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')
ORDER BY goods_price DESC;

④、创建“商品分类”表

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
    cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    cate_name VARCHAR(40)
);

查询 tdb_goods 表的所有记录,并且按"类别"分组

SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

将分组结果写入到tdb_goods_cates数据表

INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

⑤、通过 tdb_goods_cates 数据表来更新 tdb_goods 表

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name 
SET goods_cate = cate_id ;

⑥、通过CREATE...SELECT来创建数据表并且同时写入记录

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

⑦、复制编号为 19,20 的两条记录

INSERT ... SELECT实现复制

INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);

⑧、查找重复记录

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

⑨、删除重复记录

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN 
(SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) 
AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

4.2、查询某字段中以逗号分隔的字符串的方法

①、创建表

CREATE TABLE test(
    id int(6) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),
    pname VARCHAR(20) NOT NULL,
    pnum VARCHAR(50) NOT NULL
);

②、插入带有逗号分隔的测试数据

INSERT INTO test(pname,pnum) VALUES(产品1,1,2,4);
INSERT INTO test(pname,pnum) VALUES(产品2,2,4,7); 
INSERT INTO test(pname,pnum) VALUES(产品3,3,4); 
INSERT INTO test(pname,pnum) VALUES(产品4,1,7,8,9); 
INSERT INTO test(pname,pnum) VALUES(产品5,33,4);

③、查找 pnum 字段中包含 3 或者 9 的记录

方法一:使用 find_in_set

SELECT * FROM test WHERE find_in_set(3,pnum) OR find_in_set(9,pnum);

方法二:使用正则

SELECT * FROM test WHERE pnum REGEXP (3|9);

这样会产生多条记录,33也被查找出来了。换一种方式:

SELECT * FROM test WHERE CONCAT(,,pnum,,) REGEXP[^0-9]+[3|9][^0-9]+";

CONCAT 用于将多个字符串连接成一个字符串,返回结果为连接参数产生的字符串。

4.3、concat、group_concat

本文中使用的例子均在下面的数据库表user下执行:

01.png

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `date` varchar(64) NOT NULL,
  `singin` tinyint(1) NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` VALUES ('1', '小明', '2018-07-05 00:00:00', '1', '1', '0');
INSERT INTO `user` VALUES ('2', '小王', '2018-07-05 01:21:35', '3', '0', '0');
INSERT INTO `user` VALUES ('3', '小丽', '2018-07-03 12:30:59', '2', '1', '0');
INSERT INTO `user` VALUES ('4', '小王', '2018-07-04 15:26:14', '4', '0', '0');
INSERT INTO `user` VALUES ('5', '小明', '2018-06-11 15:26:40', '4', '1', '0');
INSERT INTO `user` VALUES ('6', '小明', '2018-06-01 15:26:54', '2', '0', '0');
INSERT INTO `user` VALUES ('7', '', '2018-06-31 11:34:34', '0', '0', null);
INSERT INTO `user` VALUES ('8', 'maryleo', '2018-06-31 12:43:59', '0', '1', '60');
INSERT INTO `user` VALUES ('9', 'nancysun', '2018-06-31 12:43:59', '0', '1', '60');

①、concat()函数

A、功能:将多个字符串连接成一个字符串。

B、语法:concat(str1, str2,...)

返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。

C、举例:

例1:

select concat (id, name, score) as info from user;

01.png

中间有一行为null是因为user表中有一行的score值为null。

例2:在例1的结果中三个字段id,name,score的组合没有分隔符,我们可以加一个逗号作为分隔符:

select concat(id,',',name,',',score) as info from user;

01.png

这样看上去似乎顺眼了许多~~

但是输入sql语句麻烦了许多,三个字段需要输入两次逗号,如果10个字段,要输入九次逗号...麻烦死了啦,有没有什么简便方法呢?——于是可以指定参数之间的分隔符的concat_ws()来了!!!

②、concat_ws()函数

A、功能:和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符~(concat_ws就是concat with separator)

B、语法:concat_ws(separator, str1, str2, ...)

说明:第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。

C、举例:

例3:我们使用concat_ws()将 分隔符指定为逗号,达到与例2相同的效果:

select concat_ws(',',id,name,score) as info from user;

01.png

例4:把分隔符指定为null,结果全部变成了null:

select concat_ws(NULL,id,name,score) as info from user;

01.png

③、group_concat()函数

前言:在有group by的查询语句中,select指定的字段要么就包含在group by语句的后面,作为分组的依据,要么就包含在聚合函数中。

select name,min(id) from user group by name;

01.png

该例查询了name相同的的人中最小的id。如果我们要查询name相同的人的所有的id呢?

当然我们可以这样查询:

select name,id from user order by name;

01.png

但是这样同一个名字出现多次,看上去非常不直观。有没有更直观的方法,既让每个名字都只出现一次,又能够显示所有的名字相同的人的id呢?——使用group_concat()

A、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。

B、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] )

说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。

C、举例:

例7:使用group_concat()和group by显示相同名字的人的id号:

select name,group_concat(id) from user group by name;

01.png

例8:将上面的id号从大到小排序,且用'_'作为分隔符:

select name,group_concat(id order by id desc separator '_') as ids from user group by name;

01.png

例9:上面的查询中显示了以name分组的每组中所有的id。接下来我们要查询以name分组的所有组的id和score:

select name,group_concat(concat_ws('-',id,score) order by id) as value from user group by name;

01.png

4.4、gruop by 报错

①、问题

在mysql 工具 搜索或者插入数据时报下面错误:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column "database_tl.emp.id" which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

②、原因

A、看一下group by的语法:

select 选取分组中的列+聚合函数 from 表名称 group by 分组的列

从语法格式来看,是先有分组,再确定检索的列,检索的列只能在参加分组的列中选。

我当前Mysql版本5.7.17,

再看一下 ONLY_FULL_GROUP_BY 的意思是:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。

B、查看mysql版本命令

select version();

查看sql_model参数命令:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

发现:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

第一项默认开启ONLY_FULL_GROUP_BY,

③、解决方法

A、只选择出现在 group by 后面的列,或者给列增加聚合函数;(不推荐)

B、命令行输入:

set @@GLOBAL.sql_mode=;
set sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;

默认关掉ONLY_FULL_GROUP_BY!

这个时候 在用工具select 一下

SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;

发现已经不存在ONLY_FULL_GROUP_BY ,感觉已经OK。但是如果你重启Mysql服务的话,发现ONLY_FULL_GROUP_BY还是会存在的

C、彻底解决

想要彻底解决这个问题 就得去改my.ini 配置(如果你们mysql 没有这个文件,就把my-default.ini 改成my.ini,我这个版本就是没有my.ini配置问题)

在文件最后添加

[mysqld] 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启 mysql

/etc/init.d/mysql restart

4.5、in 查询优化

select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

67 rows in set (12.00 sec)

只有67行数据返回,却花了12秒,而系统中可能同时会有很多这样的查询,系统肯定扛不住。用desc看一下(注:explain也可)

desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
| 1 | PRIMARY | abc_number_prop | ALL | NULL | NULL | NULL | NULL | 2679838 | Using where |
| 2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70 | const,func | 1 | Using where; Using index |
+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+
2 rows in set (0.00 sec)

从上面的信息可以看出,在执行此查询时会扫描两百多万行,难道是没有创建索引吗,看一下

show index from abc_number_phone;
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abc_number_phone | 0 | PRIMARY | 1 | number_phone_id | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 0 | phone | 1 | phone | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 0 | phone | 2 | number_id | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 1 | number_id | 1 | number_id | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 1 | created_by | 1 | created_by | A | 36879 | NULL | NULL | | BTREE | | |
| abc_number_phone | 1 | modified_by | 1 | modified_by | A | 36879 | NULL | NULL | YES | BTREE | | |
+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.06 sec)

show index from abc_number_prop;
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abc_number_prop | 0 | PRIMARY | 1 | number_prop_id | A | 311268 | NULL | NULL | | BTREE | | |
| abc_number_prop | 1 | number_id | 1 | number_id | A | 311268 | NULL | NULL | | BTREE | | |
| abc_number_prop | 1 | created_by | 1 | created_by | A | 311268 | NULL | NULL | | BTREE | | |
| abc_number_prop | 1 | modified_by | 1 | modified_by | A | 311268 | NULL | NULL | YES | BTREE | | |
+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.15 sec)

从上面的输出可以看出,这两张表在number_id字段上创建了索引的。

看看子查询本身有没有问题。

desc select number_id from abc_number_phone where phone = '82306839';
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
| 1 | SIMPLE | abc_number_phone | ref | phone | phone | 66 | const | 6 | Using where; Using index |
+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec

没有问题,只需要扫描几行数据,索引起作用了。查询出来看看

select number_id from abc_number_phone where phone = '82306839';
+-----------+
| number_id |
+-----------+
| 8585 |
| 10720 |
| 148644 |
| 151307 |
| 170691 |
| 221897 |
+-----------+
6 rows in set (0.00 sec)

直接把子查询得到的数据放到上面的查询中

select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);
67 rows in set (0.03 sec)

速度也快,看来MySQL在处理子查询的时候是不够好。我在MySQL 5.1.42 和 MySQL 5.5.19 都进行了尝试,都有这个问题。

根据网上这些资料的建议,改用join来试试。

修改前:select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');
修改后:select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';

select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';

67 rows in set (0.00 sec)

效果不错,查询所用时间几乎为0。看一下MySQL是怎么执行这个查询的

desc select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
| 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | Using where; Using index |
| 1 | SIMPLE | a | ref | number_id | number_id | 4 | eap.b.number_id | 3 | |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+
2 rows in set (0.00 sec)

小结:当子查询速度慢时,可用JOIN来改写一下该查询来进行优化。

4.6、union 排序问题

①、实现功能,活动日期,当天的排在最前,其次是 大于当天的额,最后 是以前的活动 分开,startteim >= 当天的, union starttime< 当天的 问题,分开查,好使,放在一起,大于当天的乱掉

②、原来是:

(select * from v9_activity where starttime>now() and enable=1 order by starttime ASC) UNION ALL (select * from v9_activity where starttime < now() and enable=1 order by starttime DESC)

③、以上排序乱掉。修改为以下:

(SELECT * from (select * from v9_activity where starttime>now() and enable=1 order by starttime ASC) as activity1) UNION ALL (select * from (select * from v9_activity where starttime < now() and enable=1 order by starttime DESC) as activity2)

4.7、MYSQL查询~ 存在一个表而不在另一个表中的数据

select A.ID from A left join B on A.ID=B.ID where B.ID is null

4.8、查询在线人数,并能处理异常掉线的 SQL。

...WHERE ________< now()

WHERE 用户上次更新时间 + 正常更新间隔< now()

4.9、有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列

create table table1(a int,b int,c int);
insert into table1 values(22,24,23);
select (case when a>b then a when a>c then a when b>c then b else c end) as max from table1;

4.10、mysql 查询当天、本周、本月、上一个月的数据

①、内置时间函数

current_date()/curdate() 当天日期,2018-07-09
current_time()/curtime() 当前时间,11:50:59
sysdate() 执行到当前函数时的时间,2018-07-09 11:50:59
current_timestamp()/now() 执行SQL语句时的时间,2018-07-09 11:50:59
SELECT now(),current_timestamp(),sysdate(),sleep(2),now(),current_timestamp(),sysdate()
结果会发现最后一个SYSDATE()显示的时间会较其他时间大两秒。
unix_timestamp() 日期转时间戳
from_unixtime() 时间戳转日期
to_days() 函数返回一个天数! 从年份0开始的天数
有一张表!order表 有一个字段 create_time  类型 datetime  
如果要查询当前表中昨天的数据那么
select * from order where to_days(now())-to_days(create_time)<1
from_days()给出一个天数 N,返回一个 DATE 值
date_sub(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。
select date_sub(curdate(),interval 7 day)
date() 函数返回日期或日期/时间表达式的日期部分。
year(date)返回日期的年份,范围为1000到9999,或者对于“零”日期返回0。
quarter(date)返回日期的一年中的季度,范围为1到4。
month(date)返回日期的月份,1月至12月的范围为1至12,对于包含月份为零的日期(如“0000-00-00”或“2008-00-00”),返回0。
week(date[,mode])此函数返回日期的周号。 week()的双参数使您能够指定星期是从星期天还是星期一开始,以及返回值是在0到53还是从1到53的范围内。如果省略mode参数,则值 使用了default_week_format系统变量。
yearweek 是获取年份和周数的一个函数
cast函数语法规则是:cast(字段名 as 转换的类型 ),其中类型可以为:
char[(N)] 字符型 、date 日期型、datetime 日期和时间型、decimal float型、signed int、time 时间型
period_diff() 返回两个时段之间的月份差值

②、查询

今天:select * from 表名 where to_days(时间字段名) = to_days(now())
昨天:select * from 表名 where to_days(now()) - to_days(时间字段名) <= 1
近7天:select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)
近30天:select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)
本月:select * from 表名 where date_format(时间字段名,'%Y%m') = date_format(curdate(),'%Y%m')
上一月:select * from 表名 where period_diff(date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m')) = 1
查询本季度数据:select * from 表名 where quarter(时间字段名) = quarter(now())
查询上季度数据:select * from 表名 where quarter(时间字段名) = quarter(date_sub(now(),interval 1 quarter))
查询本年数据:select * from 表名 where year(时间字段名) = year(now())
查询上年数据:select * from 表名 where year(时间字段名) = year(date_sub(now(),interval 1 year))
查询当前这周的数据:select * from 表名 where yearweek(date_format(时间字段名,'%Y-%m-%d')) = yearweek(now())
查询上周的数据:select * from 表名 where yearweek(date_format(时间字段名,'%Y-%m-%d')) = yearweek(now())-1

4.11、mysql in、find_in_set、like

①、in查询相当于多个or条件的叠加

select * from user where user_id in (1,2,3);
等效于
select * from user where user_id = 1 or user_id = 2 or user_id = 3;
not in与in相反,如下
select * from user where user_id not in (1,2,3);
等效于
select * from user where user_id != 1 and user_id != 2 and user_id != 3;

②、FIND_IN_SET(str,strlist):str 要查询的字符串,strlist 字段名 参数以”,”分隔 如 (1,2,6,8)。如果str不在strlist 或strlist 为空字符串,则返回值为 0 。

select find_in_set('b','a,b,c'); //返回2

FIND_IN_SET函数用来比较是不是包含

③、like是广泛的模糊匹配,字符串中没有分隔符

select * from user where name like '%a%'

4.12、MySQL实现排名并查询指定用户排名功能

①、表结构

CREATE TABLE `testsort`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NULL DEFAULT 0 COMMENT '用户id',
  `score` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '测试排序' ROW_FORMAT = Compact;

INSERT INTO `testsort` VALUES (1, 12, 80.00);
INSERT INTO `testsort` VALUES (2, 13, 78.00);
INSERT INTO `testsort` VALUES (3, 8, 99.00);

②、思路

A、可以先排序,再对结果进行编号;也可以先查询结果,再排序编号。

B、说明:

@rownum := @rownum + 1 中 := 是赋值的作用,这句话的意思是先执行@rownum + 1,然后把值赋给@rownum;

(SELECT @rownum := 0) r 这句话的意思是设置rownum字段的初始值为0,即编号从1开始。

C、实现排名:

方法一:

SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t;

方法二:

SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, testsort AS t
ORDER BY t.score DESC;

结果:

01.png

③、查看指定用户排名

方法一:

SELECT b.* FROM
(
SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r,
(SELECT * FROM testsort ORDER BY score DESC) AS t
) AS b WHERE b.uid = 13;

方法二:

SELECT b.* from
(
SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, testsort AS t
ORDER BY t.score DESC
) as b where b.uid = 13;

结果:

01.png

④、实现从指定用户uid为8,12 中获取uid为8的排名

SELECT b.* FROM
(
SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r,
(SELECT * FROM testsort WHERE uid IN (8,12) ORDER BY score DESC) AS t
) AS b WHERE b.uid = 8;

结果:

01.png

4.13、内连接、左连接、右连接

①、内连接

select a.*,b.* from a inner join b on a.id=b.parent_id

②、左连接

select a.*,b.* from a left join b on a.id=b.parent_id

③、右连接

select a.*,b.* from a right join b on a.id=b.parent_id

暂时还没有评论,快来抢沙发吧~

发表评论

您需要登录后才可以评论。登录 | 立即注册
阅读 29