MySQL 疑难 SQL

by · 2016年12月07日 · 910 Words · ~2min reading time | Improve on


原文链接： MySQL 疑难 SQL
  • content
    {:toc}

指定IP和端口

mysql -uroot -p -h192.168.3.146 -P3306

时间自动更新

ALTER TABLE `tb_user` CHANGE `modified_time` `modified_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

补充自增主键

alter table `表格名` add column `列名` int not null auto_increment primary key comment '主键' first;

分组排序取前N条记录

建表并初始化数据：

CREATE TABLE lin(
    id integer NOT NULL,
    type varchar(1) , 
    CONSTRAINT lin_pkey PRIMARY KEY (id) 
);

INSERT INTO lin(id, type) VALUES (1, 'a'),(2, 'a'),(3, 'a'),(4, 'b'),(5, 'b'),(6, 'b'),(7, 'b'),(8, 'c'),(9, 'c'),(10, 'c');

执行查询：

select * from lin a where (select count(1) from lin b where a.type = b.type and a.id < b.id) < 2

执行结果为：

+----+------+
| id | type |
+----+------+
|  2 | a    |
|  3 | a    |
|  6 | b    |
|  7 | b    |
|  9 | c    |
| 10 | c    |
+----+------+

SQL解释：

  1. 子查询是查询所有和当前记录type一样但是id比当前记录大的记录的总和
  2. type和当前记录一样，但是id比当前记录大的记录如果大于或者等于两个，说明当前至少排在第三个
  3. type和当前记录一样，但是id比当前记录大的记录如果小于两个，说明当前记录可以排在前两个

查询结果加序号

select (@i:=@i+1) num,u.user_name from tb_user u,(select @i:=0) t

MySQL变量不用事前声明，只需要“@变量名”使用即可

LPAD

find_in_set

日期

  1. 时间函数

    select now(), sysdate(), curdate(), curtime();

"now()":     "2018-01-06 10:05:19",
"sysdate()": "2018-01-06 10:05:19",
"curdate()": "2018-01-06",
"curtime()": "10:05:19"

  2. now()和sysdate()的区别
    now() 在执行开始时值就得到了，sysdate() 在函数执行时动态获取值

    MariaDB [(none)]> select sysdate(), sleep(3), sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(3) | sysdate()           |
+---------------------+----------+---------------------+
| 2018-01-12 17:23:05 |        0 | 2018-01-12 17:23:08 |
+---------------------+----------+---------------------+
1 row in set (3.01 sec)

MariaDB [(none)]> select now(), sleep(3), now();
+---------------------+----------+---------------------+
| now()               | sleep(3) | now()               |
+---------------------+----------+---------------------+
| 2018-01-12 17:23:13 |        0 | 2018-01-12 17:23:13 |
+---------------------+----------+---------------------+
1 row in set (3.00 sec)

  3. 日期和毫秒数转换

    select UNIX_TIMESTAMP('2018-01-06 10:04:30');
select from_unixtime(1515204270);

  4. 为日期增加/减少时间间隔

    select date_add(now(), interval 1 day);
select date_add(now(), interval -1 day);

增加/减少的时间单位可选：
day、hour、minute、second、microsecond、week、month、quarter、year

中文排序

GBK编码时一级汉字3755个采用拼音排序，二级汉字不是
但考虑到常用汉字都是一级汉字，所以中文排序依赖GBK编码基本上可以满足需求

若数据库采用GBK编码，则直接order by [field] desc/asc即可
若数据库采用utf8编码，则使用order by convert([field] using gbk) desc/asc即可

示例：

INSERT INTO `tb_order` (`id`, `name`) VALUES
(1,'赵'),
(2,'钱'),
(3,'孙'),
(4,'李'),
(5,'赵赵'),
(6,'赵钱'),
(7,'赵孙'),
(8,'赵李'),
(9,'啦'),
(10,'lb'),
(11,'la');

结果如下：

MariaDB [learn]> select * from tb_order order by convert(name using gbk) asc;
+----+--------+
| id | name   |
+----+--------+
| 11 | la     |
| 10 | lb     |
|  9 | 啦     |
|  4 | 李     |
|  2 | 钱     |
|  3 | 孙     |
|  1 | 赵     |
|  8 | 赵李   |
|  6 | 赵钱   |
|  7 | 赵孙   |
|  5 | 赵赵   |
+----+--------+

Tags:

`