MySQL常用命令和函数的讲解以及表之间的联结

Mysql的中一些语句的用法：

有表：

CREATE TABLE `book`  (
  `id` int(20) NOT NULL,
  `book_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书名',
  `press` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出版社',
  `book_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书籍类型',
  `time` datetime NULL DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
 
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '西游记', '国家图书馆出版社', '中国文学', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (2, '西游记', '人民教育出版社', '中国文学', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (3, '西游记', '北京大学出版社', '中国文学', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (4, '第四世界', '国家图书馆出版社', '哲学类', '2020-11-30 17:22:53');
INSERT INTO `book` VALUES (5, '第五世界', '人民教育出版社', '科幻', '2020-11-30 17:25:34');
INSERT INTO `book` VALUES (7, '第7世界', '北京出版社', '科幻', '2020-11-30 17:47:11');
INSERT INTO `book` VALUES (9, '第9世界', '人民教育出版社', '科幻', '2020-11-30 17:50:56');
INSERT INTO `book` VALUES (10, '唐诗三百首', '北京出版社', '文化教育', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (11, '灵魂只能独行', '江苏教育出版社', '哲学类', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (12, '神雕侠侣', '江苏教育出版社', '小说', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (13, 'old-book', '人民教育出版社', '哲学类', '2020-09-18 10:24:20');
INSERT INTO `book` VALUES (14, '三国演义', '国家图书馆出版社', '中国文学', '2020-12-04 15:31:47');
 
SET FOREIGN_KEY_CHECKS = 1;

一、有关LIMIT的用法：

• limit使用一个参数：表示返回前3行数据

select * from book limit 3;

• limit使用两个参数：第一个参数表示从第几行开始查，第二个参数表示取第几个条数据

LIMIT的下标是从0开始的：
limit 1 offset 1;--跳过一条数据，获取第一条数据
limit 1,1;--从第一行开始查，查一条数据
SELECT * FROM book LIMIT 1 OFFSET 1;

二、有关count的用法：

1. count（*），count（1）：相当于统计表的行数，不会忽略列值为null的记录。

select count（*） from book;
select count（1） from book;

2. count(column_name)：返回指定列的列数，会忽略列值为NULL的记录（不包括空字符串和0）

select count（boook_name） from table_name ;

3. count（distinct column_name）：会对这列的数据去重后再统计数目

select count（distinct column_name） from table_name ;

三、order by 的用法：

1. ORDER BY 关键字可以使查询返回的「结果集」按照指定的列进行排序，可以按照某「一列」排序或者同时按照「多列」进行排序，排序的顺序可以是「升序ASC」或者「降序DESC」。

select * from book order by id;--升序排

select * from book order by id desc;--降序排

2. 当ORDER BY 后有两列时，先根据第一列进行排序，当第一列相同时，再根据第二列排序：

SELECT * FROM book ORDER BY book_name,press

四、group by的用法：

• group by 关键字可以根据一个或多个字段对查询结果进行分组
• group by 一般都会结合Mysql聚合函数来使用
• 在 Group By 中指定的列可以是表达式或函数，但在 Select 子句中必须使用别名来引用这些列。
• 如果需要指定条件来过滤分组后的结果集，需要结合 having 关键字；原因：where不能与聚合函数联合使用 并且 where 是在 group by 之前执行的

1. Group By 是一种 SQL 查询语句，常用于根据一个或多个列对查询结果进行分组。在 Group By 子句中指定的列将成为分组依据，而在 Select 子句中指定的列必须是聚合函数（例如 SUM、AVG、COUNT 等）或分组列。

SELECT COUNT(1) num,book_type FROM book GROUP BY book_type

2. 结合group by 结合 having使用：

• Having 子句中的条件是基于聚合函数计算的结果进行过滤的。
• 如果在 Select 子句中没有使用聚合函数，则必须在 Group By 子句中包含该列。
• 如果条件中只涉及到一个聚合函数，可以使用 WHERE 子句代替 Having 子句。

SELECT COUNT(1) num,book_type FROM book GROUP BY book_type HAVING num >1

3. 经常结合group by 使用的聚合函数

AVG(col)--返回指定列的平均值
COUNT(col)--返回指定列中非NULL值的个数
MIN(col)--返回指定列的最小值
MAX(col)--返回指定列的最大值
SUM(col)--返回指定列的所有值之和
GROUP_CONCAT(col) --返回由属于一组的列值连接组合而成的结果

五、表之间的联结（inner join、left join、right join…）

• Inner join…on…：即内连接，同时将两表作为参考对象，根据ON后给出的两表的条件将两表连接起来。结果则是两表同时满足ON后的条件的部分才会列出。
  

• Left join…on…：即左连接，是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。
  

• Right join…on….：即右连接，是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。
  

• union 和 union all：

  • 使用UNION必须有两条或者两条以上的SELECT语句组成，语句之间用UNION关键字分割
  • 使用UNION关联的每个子查询必须包含相同的检索列、表达式或这聚集函数(次序可以不一样)
  • 列数据类型必须兼容;类型不必完全相同，但必须是DBMS可以隐含转换的类型(不同的数值类型或者不同的日期类型)
  • UNION几乎总是完成与多个WHERE条件相同的工作，UNION ALL是UNION的一种形式,它完成WHERE子句完成不了的工作,因为他将返回每个条件的匹配行(包括重复行)
  • 使用组合查询,当需要对结果进行排序是,只能指定一条Order By语句,这条语句只能放在最后一天SELECT语句的后面
  • UNION会对结果进行去重 UNION ALL 将子查询的结果全部合并
    

--好友申请：谁有最多的朋友
select ids id,count(1) num from
(select requester_id as ids from RequestAccepted
union all
select accepter_id from RequestAccepted
) a1
group by ids 
order by num desc
limit 1

六、Mysql常用函数

不同数据库的函数往往各不相同，因此不可移植。本节主要以 MySQL 的函数为例。

1. 文本处理

函数	说明
LEFT()、RIGHT()	左边或者右边的字符
LOWER()、UPPER()	转换为小写或者大写
LTRIM()、RTRIM()	去除左边或者右边的空格
LENGTH()	长度，以字节为单位
SOUNDEX()	转换为语音值

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

2. 日期和时间处理

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS

函 数	说 明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

3. 数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

4. 汇总

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和
AVG()	会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable