MYSQL学习2

1 .创建计算字段

创建一个水果表，表中有字段id，fruit_name,fruit_price ① 计算每种水果的总价格，生成新的列名

select fruit_name,fruit_price,count,fruit_price*count as total_price from fruits;

②测试计算

2 .使用数据处理函数

函数的可移植性不强

① 文本处理函数

使用upper()将水果名大写

select upper(fruit_name) from fruits;

常用文本处理函数

函数说明left()返回串左边的字符length()返回串的长度locate()找出串的一个字串lower()将串转换为小写ltrim()去掉串左边的空格right()返回右边的字符rtrim()去掉串右边的空格soundex()返回串的SOUNDEX值substring()返回字串的字符upper()将串转换为大小

SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式的算法。考虑发音字节和音节，使得能对串进行发音比较而不是字母比较。

②时间和日期处理函数

常用日期和时间处理函数

函数说明addDate()增加一个日期（天，周等）addTime()增加一个时间（时，分等）curDate()返回当前日期curTime()返回当前时间date()返回日期时间和日期部分dateDiff()计算两个日期之差date_add()高度灵活的日期运算函数date_format()返回一个格式化的日期或时间串day()返回一个日期的天数部分dayOfWeek()对于一个日期，返回对应的星期几hour()返回一个时间的小时部分minute()返回一个时间的分钟部分month()返回一个日期的月份部分now()返回当前日期和时间second()返回一个时间的秒部分time()返回一个日期时间的时间部分year()返回一个日期的年份部分

如果要的是日期，请使用date() 如果你想要仅是日期，则使用date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个time()函数，在你只想要时间时应该使用它。

select * from user where Date(user_date) between '2019-09-01' and '2020-07-01';

如上代码则为查询一个时间段的用户

selet * from user where year(user_date)=2019 and month(user_date)=10;

如上代码查询的则为年份2019 ，且月份在10月的用户信息

③ 数值处理函数

函数说明abs()返回一个数的绝对值cos()返回一个角度的余弦exp()返回一个数的指数值mod()返回除操作的余数pi()返回圆周率rand()返回一个随机数sin()返回一个角度的正弦sqrt()返回一个数的平方根tan()返回一个角度的正切

3 .汇总数据

SQL聚集函数

函数说明avg()返回某列的平均值count()返回某列的行数max()返回某列的最大值min()返回某列的最小值sum()返回某列值之和

① AVG()计算平均值

计算价格平均值

select avg(fruit_price) as avg_price from fruits;

② COUNT()计算行数

查询表中有多少行

select count(*) as total from fruits;

③ MAX()计算最大值

获取最大价格的水果

select max(fruit_price) as max_price from fruits;

④ MIN()计算最小值

获取最小价格的水果

select min(fruit_price) as min_price from fruits;

⑤ SUM()计算总和

获取价格总和

select sum(fruit_price) as total_price from fruits;

⑥组合聚集函数

select avg(fruit_price) as avg_price, count(*) as total, max(fruit_price) as max_price, min(fruit_price) as min_price, sum(fruit_price) as total_price from fruits;

4 .分组数据

主要涉及两个子句group by 以及 having

对普通用户user与管理员用户admin做分组查询

select count(*) as '用户数',rid from user group by rid;

对于上面查询出来的两种分组使用having进行过滤操作，过滤掉普通用户

select count(*) as '用户数',rid from user group by rid having rid=1;

如果使用where替代having则会报错

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集；

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足；

5 .子查询

① 嵌套查询

这里有两张表，用户表与角色表如下：

用户表中的rid字段为外键，对应role表中的id主键

例如：我们查询管理员用户(admin)

select * from user where rid in ( select id from role where name='admin' );

如图我们将role表中查询出来的id字段作为查询user的条件