#----------------------聚合函数(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;