Java学习----数据库4

今日学习内容总结内容：

MySQL运算符

MySQL运算符主要包括3大类：比较运算符、算术运算符、逻辑运算符

算术运算符

加 + 、减 - 、乘 * 、除 / 、求余 %

mysql> select 1 + 2 ;

+-----+

| 1 + 2 |

+-----+

| 3 |

+-----+

1 row in set ( 0.00 sec)

mysql> select 1 /2;

+--------+

| 1 /2 |

+--------+

| 0.5000 |

+--------+

1 row in set ( 0.00 sec)

mysql> select 5 % 2 ;

+------+

| 5 % 2 |

+------+

| 1 |

+------+

1 row in set ( 0.00 sec)

特殊操作

mysql> select '5a' + 2 ;

+--------+

| '5a' + 2 |

+--------+

| 7 |

+--------+

1 row in set , 1 warning ( 0.00 sec)

mysql> select 'a5' + 2 ;

+--------+

| 'a5' + 2 |

+--------+

| 2 |

+--------+

1 row in set , 1 warning ( 0.00 sec)

mysql> select 123.45 % 2.52 ;

+-------------+

| 123.45 % 2.52 |

+-------------+

| 2.49 |

+-------------+

1 row in set ( 0.00 sec)

mysql> select - 123.45 % 2.52 ;

+--------------+

| - 123.45 % 2.52 |

+--------------+

| - 2.49 |

+--------------+

1 row in set ( 0.00 sec)

比较运算符

 

in或者not in

• in用于判断某个列的取值是否为指定的值，使用in运算符时指定的值是离散的数据，不是连续值
  •     select * from tb_student where age in(18,28,15)含义是 age=18 or age=28 or age=15
    • select * from tb_student where age not in(18,28,15)含义是 age!=18 and age!=28 and age!=15
    • 查询孙权、黄盖、张三以及李四同学的成绩值

-- 查询张三以及李四同学的成绩

select score from tb_student where name=' 张三 ' or name=' 李四 '

select score from tb_stuent where name in(' 张三 ',' 李四 ') -- in 中的数据个数没有限制

between/and

用于判断数据是否在指定的范围内，连续值

• 查询成绩及格并且不属于优秀的所有学生信息

-- 写法 1 ：使用条件拼接

select * from tb_student where score>= 60 and score<= 85 ;

-- 写法 2

select * from tb_student where score between 60 and 85 ;

-- 如果需要的是不在指定范围内部

select * from tb_student where score not between 50 and 85 ;

 

like/not like

主要针对字符串类型数据进行模糊查询，通配符 _ 和 %

查询不姓张的同学

select * from tb_student where name not like ' 张 %'

 

regexp 是在 mysql 中针对字符串类型进行正则式进行判断， not regexp

<=> 如果两数相同为 true ，即使是 null

is null/is not null

判断是否为空，为空则返回 true

逻辑运算符

5类聚集函数

聚集函数用于对于一个集合中的数据进行处理，不是一行一行的数据

count 统计行数、 sum 求和、 max 最大值、 min 最小值、 avg 平均值

计数

语法 count([all/distinct] 列名称 /*)

获取总学生数

select count (*) from tb_student;

select count ( 1 ) from tb_student;

select count ( distinct dept) from tb_student; -- 获取系的个数

mysql> select * from tb_student;

+----+--------+------+------+--------+

| id | name | age | sex | dept |

+----+--------+------+------+--------+

| 1 | 小胖 | 18 | 1 | 软工 |

| 2 | 东方 | 16 | 0 | 机壳 |

| 3 | 仗义 | 19 | 1 | 大数据 |

| 4 | 张骞 | 16 | 1 | 软工 |

| 5 | 张小骞 | 16 | 1 | 软工 |

| 6 | 从来 | 16 | 1 | NULL |

+----+--------+------+------+--------+

6 rows in set ( 0.00 sec)

select count (dept) from tb_student; -- 进行计数统计时， null 不参与统计

 需要统计选修1号课程的学生人数

select count (*) from tb_choice where cid= 1 ;

假设选择表中需要记录补考成绩，也就是意味着课程会有重复，则问题 1 ： primary key(cid,sid) 就是错误的，

学生编号        课程编号         成绩

1                         3                   45

1                         3                   70

由于出现重复计数，则选修人数统计出错

select count(distinct sid) from tb_choice where cid=1;

统计值

用于数值类型的列，不要用于其他类型。 max min sum avg

• max/min可以用于日期类型比较，最新的日期最大

ysql> select * from tb_student;

+----+--------+------+------+--------+

| id | name | age | sex | dept |

+----+--------+------+------+--------+

| 1 | 小胖 | 18 | 1 | 软工 |

| 2 | 东方 | 16 | 0 | 机壳 |

| 3 | 仗义 | 19 | 1 | 大数据 |

| 4 | 张骞 | 16 | 1 | 软工 |

| 5 | 张小骞 | 16 | 1 | 软工 |

| 6 | 从来 | 16 | 1 | NULL |

| 7 | 张展 | 16 | 1 | NULL |

+----+--------+------+------+--------+

7 rows in set ( 0.00 sec)

mysql> select max(age) from tb_student;

+----------+

| max(age) |

+----------+

| 19 |

+----------+

1 row in set ( 0.00 sec)

mysql> select min(age) from tb_student;

+----------+

| min(age) |

+----------+

| 16 |

+----------+

1 row in set ( 0.00 sec)

mysql> select avg(age) from tb_student; -- null 值不参与计算，除非使用空值处理函数

将其转换为确定数值才可以

+----------+

| avg(age) |

+----------+

| 16.7143 |

+----------+

1 row in set ( 0.00 sec)

mysql> select sum(age) from tb_student;

+----------+

| sum(age) |

+----------+

| 101 |

+----------+

1 row in set ( 0.00 sec)

 查询选修1号课程的学生最高成绩和平均成绩

select max(score),avg(score) from tb_choice where cid= 1 ;

mysql> select sum(age),max(age),min(age),avg(age) from tb_student;

+----------+----------+----------+----------+

| sum(age) | max(age) | min(age) | avg(age) |

+----------+----------+----------+----------+

| 101 | 19 | 16 | 16.8333 |

+----------+----------+----------+----------+

1 row in set ( 0.00 sec)

问题

select count(*) 、 select count(1) 和 select count(列名称)

• 列名为主键 count(列名) 比 count(1) 速度快；如果列名不是主键，则 count(1) 比 count(列名) 快
• 如果表多个列并且没有主键，则 count(1) 比 count(*) 速度快，如果有主键 `count(主键列名) 速 度最快，如果表中只有一个列 count(*) 速度最快
• count(1) 会统计表中的所有记录数，包括字段为null的记录； count(列名称) 则列为null时不统计
• MySQL针对不同的存储引擎进行了优化处理，MyISAM会将表的总行数记录下来，供 count(*) 查 询使用； Innodb则会在扫描表时选择最小的索引成本执行，所以在Innodb中 count(*) 和
• count(1) 实质上没有区别，而且执行效率一致，只有 count(列名称) 需要进行null值列的判断， 所以效率低一些

对查询结果分组

可以使用 group by 子句对查询结果进行分组处理，经常会使用聚集函数

• 如果不使用分组，聚集函数则用于处理所有查询结果数据
• 如果使用分组，则分别作用于各个分组查询的结果数据

获取男女生的平均年龄

• 按照性别进行分组 group by sex ，不同的sex值则放入不同的组中
• 平均值就是聚集函数，针对一个集合中的数据进行处理

mysql> select sex,avg(age) from tb_student group by sex;

+------+----------+

| sex | avg(age) |

+------+----------+

| 1 | 17.0000 |

| 0 | 16.0000 |

| NULL | 16.0000 |

+------+----------+

3 rows in set ( 0.00 sec)

注意：如果在 select 之后不在聚集函数中的列名称一定出现在 group by 之后，否则语法错误

having 可以对分组进行条件选择

需要获取人数多余 2 人的不同性别学生的平均年龄

• 按照性别分组
• 要统计的组必须人数多余2人，小于等于2人的分组不进行显示处理

select sex,avg(age) from tb_student group by sex having count ( 1 )> 2 ;

 排序处理

SQL 中可以针对查询结果进行排序

语法 select ... from ... order by 列名称 1 [asc/desc] ，列名称 2 [asc/desc],...

• 排序过程中首先按照列名称1进行排序，如果列1对应值相等时，才按照列名称2对应列值进行排序
• 注意默认按照指定列的自然序排序，如果需要倒序则使用关键字desc，asc是正序

按照学生年龄从大到小输出学生信息

select * from tb_student order by age desc ;