MySQL中的聚合函数及常用函数

#----------------------聚合函数(mysql中的内置函数)--------------------
# 在mysql中函数使用select关键字调用: select 函数名(字段) 【from 表名】

# 找出最大值：max(字段名)

# 找出users表中javaScore的最高分
SELECT MAX(javaScore) AS 最高分 FROM users;

# 找出最小值: min(字段名)
SELECT MIN(javaScore) AS 最低分 FROM users;
SELECT MIN(javaScore) AS 最低分,username FROM users;

SELECT us.`username`,uu.* FROM users us,(SELECT MIN(javaScore) AS js FROM users) uu WHERE us.javaScore=uu.js;
SELECT * FROM users;

# 求平均数：avg(字段名)
SELECT AVG(javaScore) AS 平均分 FROM users;

# 求和
SELECT SUM(javaScore) AS 总分数 FROM users;

# 统计记录 count(字段名) 如果字段值为null这对应数据条数不在统计之内。使用字段名做只统计非空的记录 count(*) 使用*统计时是统计所有记录数不会少记录  mysql数据库字段与表名不区分大小写
SELECT * FROM users;

SELECT COUNT(IDCard) AS 总条数 FROM users;

SELECT COUNT(phone) AS 总条数 FROM users;

SELECT COUNT(*) FROM users;

# -----------常用函数--------
# NOW();时间函数 获取当前系统时间 时间格式包括年月日时分秒
# CURTIME(); 时间函数 获取当前系统时间 时间格式包括时分秒
# CURDATE(); 年月日
SELECT NOW() AS 当前系统时间;
SELECT CURTIME() AS 当前系统时间;
SELECT CURDATE() AS 当前系统时间;

# -----------数学函数--------
# 向上取舍
SELECT CEIL(2.3);
# 向下取舍
SELECT FLOOR(2.3);
# 随机数 rand()不用接收参数，返回是0-1之间的小数
SELECT RAND();
# 获取一个随机的4位数，没有小数
SELECT RAND()*10000;
SELECT CEIL(RAND()*10000);

# -----------同时查询多条记录-----------
SELECT * FROM users WHERE id=1;

# 获取id=1或者id=2或者id=4的记录

SELECT * FROM users WHERE id=1 OR id=2 OR id=4;

# in (数据1,数据2,...); 判断某一个字段数值是否在in后面的参数列表之中
SELECT * FROM users WHERE id IN(1,3,4);
# not in (数据1,数据2,...); 判断某一个字段是否不在in后面的参数列表之中
SELECT * FROM users WHERE id NOT IN(2,3);

SELECT * FROM users WHERE id NOT IN(2,3) ORDER BY javaScore ASC;

SELECT * FROM users WHERE username='古';

# ----------- 分组查询(group by) --------
CREATE TABLE goods(
  id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  goodName VARCHAR(40) COMMENT '商品名称',
  goodCategory VARCHAR(20) COMMENT '商品种类'
);

RENAME TABLE goods TO good;

INSERT INTO goods SET goodName='联想air7000',goodCategory='数码';
INSERT INTO goods SET goodName='《Java开发大全》',goodCategory='书籍';
INSERT INTO goods SET goodName='小娃娃',goodCategory='玩具';
INSERT INTO goods SET goodName='《Mysql从删库到跑路》',goodCategory='书籍';
INSERT INTO goods SET goodName='《php从入门到放弃》',goodCategory='书籍';
# 查询goods表中商品种类
SELECT goodcategory FROM goods GROUP BY goodcategory;

# 查询goods表中是否有衣服种类 having分组的条件关键字 与group by 配合使用
SELECT goodcategory FROM goods GROUP BY goodcategory HAVING goodcategory = '数码';

# ----------分页(limit 起始下标, 每页显示的数据量)-----------
# 传递参数 当前页码pageNum 每页显示条数 pageSize

#  获取第1页的数据
SELECT * FROM goods LIMIT 0,3; 
#  获取第2页的数据
SELECT * FROM goods LIMIT 3,3;
#  获取第3页的数据
SELECT * FROM goods LIMIT 6,3;

# index = (pageNum - 1) * pageSize

SELECT * FROM goods LIMIT (pageNum-1)*pageSize,pageSize;

SELECT * FROM goods ORDER BY id DESC LIMIT (pageNum-1)*pageSize,pageSize;

CREATE TABLE publisher(
	P_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '出版社编号',
	P_NAME VARCHAR(20) NOT NULL UNIQUE COMMENT '出版社名称',
	p_LINKMAN VARCHAR(20) NOT NULL COMMENT '出版社联系人',
	P_TEL VARCHAR(20) COMMENT '电话',
	P_ADDRESS VARCHAR(50)
)
#往指定表中添加一个字段
ALTER TABLE `publisher` ADD price FLOAT;
#修改字段名
ALTER TABLE `publisher` CHANGE P_ID id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '出版社编号';
ALTER TABLE `publisher` CHANGE P_NAME `name` VARCHAR(20) COMMENT '出版社名称';
#同时添加5条数据记录
INSERT INTO `publisher` SET NAME='北京大学出版社',p_LINKMAN='王二麻子',price=99;
INSERT INTO `publisher` SET NAME='清华大学出版社',p_LINKMAN='李四',price=20;
INSERT INTO `publisher` SET NAME='武汉大学出版社',p_LINKMAN='战三',price=40;
#找出最高价
SELECT MAX(price) AS 最高价 FROM `publisher`;
SELECT MIN(price) AS 最低价 FROM `publisher`;

SELECT * FROM `publisher` ORDER BY price DESC LIMIT 2,2
  
#------------------------------------------------------------------------------
#复制某一张指定的表以及表数据

SELECT * FROM `publisher`

# 此新表无主键
CREATE TABLE publisherBak(
   SELECT * FROM `publisher`
);

#插入数据
INSERT INTO publisherBak 
SELECT * FROM publisherBak WHERE id=3;

#---------------------------------时间格式函数--------------------------------------
# 数据库日期一般datatime类型
CREATE TABLE persons(
	id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
	personName VARCHAR(40) COMMENT '人名',
	birthday DATETIME 
);
SELECT personName,DATE_FORMAT(birthday,'%Y/%m/%d/ %H:%i:%s') AS birthday FROM `persons`;

SELECT personName,DATE_FORMAT(birthday,'%Y年%m月%d日 %H:%i:%s') AS birthday FROM `persons`; # 2018年7月4号

#-----------------------#子查询(****)-----------------------------
# select 中嵌套 select 
SELECT * FROM users WHERE id IN(1,3,4);
SELECT * FROM users WHERE id = 1 OR id=3 OR id=4 ;

# 聚合函数：max()、min()、avg()、sum()、count()
# 最高分
SELECT * FROM users;
SELECT MAX(javaScore) AS topScore FROM users;
SELECT MIN(javaScore) AS topScore,username FROM users;

# 最低分的那个人名和分数
SELECT MIN(javaScore) AS minScore FROM users;

SELECT username FROM users WHERE javaScore=50;

#同时查询多张表
SELECT * FROM 表1,表2...表n WHERE 条件

# 多表查询时给每个表取别名 不用写as 空格即可

SELECT u.username,temp.minScore 
FROM users u,(SELECT MIN(javaScore) AS minScore FROM users) temp 
WHERE u.javaScore = temp.minScore;

#-----------------------#多表查询(****)-----------------------------
SELECT * FROM users WHERE id IN(1,3,4);
SELECT * FROM users WHERE id=1 OR id=3 OR id=4
#聚合函数：max()、min()、avg()、sum()、count
#最高分
SELECT MIN(javaScore) AS minScore FROM users;

SELECT username FROM users WHERE javaScore=60;