CONCAT、CONCAT_WS、GROUP_CONCAT
- contact(str1,str2,…)
处理行数据,合并一行中的多个字段,没有分隔符隔开。 - contact_ws(separator,str1,str2…)
处理行数据,合并一行中的多个字段,并以自定义符号隔开。 - group_contact(expr)
处理列数据,合并一列中的一个字段,默认以,隔开,可定义隔开符号。
demo:
SELECT type, name, `key` FROM test
type | name | key
------------------------
0 | maria | 123
1 | gabriel | 455
0 | rihanna | 69
1 | chris | 7
1 | martin | 112
SELECT GROUP_CONCAT(type ORDER BY type) types,
GROUP_CONCAT(name ORDER BY type) names,
GROUP_CONCAT(`key` ORDER BY type) `keys` FROM test
types | names | keys
------------------------------------------------------------------
0,0,1,1,1 | rihanna,maria,martin,chris,gabriel | 69,123,112,7,455
LIMIT X,Y 与 LIMIT x OFFST y
limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据.
## 返回符合条件的前10条语句
select * from user limit 10
## 返回符合条件的第11-20条数据
select * from user limit 10,20
## 为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1
select * from user limit 100,-1
limit 在数据库量很大只查询一部分数据情况下情况下,查询效率高。
高效率的原理是:避免全表扫描,提高查询效率。
eg:LIMIT 1,只要找到了对应的一条记录,就不会继续向下扫描了,效率会大大提高。
在一种情况下,使用limit效率低,那就是:只使用limit来查询语句,并且偏移量特别大的情况
- limit x offset y
注意
- offset X是跳过X个数据,limit Y是选取Y个数据
- limit X,Y 中X表示跳过X个数据,读取Y个数据
IFNULL() 函数
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
IFNULL(expression, alt_value)
eg:返回第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n= N-1;
RETURN (
# Write your MySQL query statement below.
select IFNULL((select distinct Salary from Employee order by Salary desc limit n,1 ),null)
);
END
IF(expr1,expr2,expr3)
如果expr1 位true返回expr2否则返回expr3
eg:力扣交换工资
给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句
update salary set sex=if(sex='m','f','m')
MOD(x,y)
MOD(x,y) 返回 x 被 y 除后的余数
primary key 和unique的区别
primary key = unique + not null 主键不能为空每个字段值都不重复,unique可以为空,非空字段不重复。
unique 一个或者多个字段定义,primary key 单字段主键或多字段联合主键。
primary key一个表只能有一个,unique一个表可以有多个
逻辑设计上primary key用来作记录标识,unique用来保证唯一性,但是在他们创建时都会去相应创建一个unique index,可以用来做sql优化。
MyISAM 与 InnoDB 区别
- myisam支持事务InnoDB不支持事务
事务是一种高级的处理方式,如在一些列增删改中只要哪个出错还可以回滚还原,而 MyISAM就不可以了。 - MyISAM 适合查询以及插入为主的应用,InnoDB 适合频繁修改以及涉及到安全性较高的应。
- InnoDB 支持外键,MyISAM 不支持。
- 清空整个表时,InnoDB 是一行一行的删除,效率非常慢。MyISAM 则会重建表。
- InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁。
索引
- 索引可以加快数据库的检索速度
- 索引会降低插入、删除、修改等维护任务的速度。
- 索引需要占物理和数据空间。
- 了解过索引的最左匹配原则 知道索引的分类:
- 聚集索引和非聚集索引
- Mysql支持Hash索引和B+树索引两种
- 索引实际上是将无序的数据变成有序
聚集和非聚集索引
-
聚集索引是以主键创建的索引
-
非聚集索引是以非主键创建的索引
-
区别:
聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列
使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找 的数据。(拿到主键再查找这个过程叫做回表)
两个表合并
SELECT
distinct(user_id)
FROM
tiku_practice_1.t_exer_record_2 AS p1
where user_id<1000
UNION ALL
SELECT
distinct(user_id)
FROM
tiku_practice_1.t_exer_record_1 AS p2
where user_id<1000