当前位置:Gxlcms > mysql > mysql基本运算符

mysql基本运算符

时间:2021-07-01 10:21:17 帮助过:62人阅读

批量导入数据: mysql load data local infile 'e://source/student.txt' into table student lines terminated by '\r\n';mysql select * from student;+--------+----------+---------+-----------+| stu_id | stu_name | stu_tel | stu_score |+--------+

批量导入数据:

mysql> load data local infile 'e://source/student.txt' into table student lines terminated by '\r\n';
mysql> select * from student;
+--------+----------+---------+-----------+
| stu_id | stu_name | stu_tel | stu_score |
+--------+----------+---------+-----------+
|      1 | a        |     151 |        60 |
|      2 | b        |     152 |        61 |
|      3 | c        |     153 |        62 |
|      4 | d        |     154 |        63 |
|      5 | e        |     155 |        64 |
|      6 | a        |     156 |        65 |
|      7 | b        |     157 |        66 |
|      8 | c        |     158 |        67 |
|      9 | d        |     159 |        68 |
|     10 | e        |     160 |        69 |
+--------+----------+---------+-----------+
在where字句之后还可以使用group by运算符,根据给定字段的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。
mysql> select *,max(stu_score) 
     > from student 
     > group by stu_name;
+--------+----------+---------+-----------+----------------+
| stu_id | stu_name | stu_tel | stu_score | max(stu_score) |
+--------+----------+---------+-----------+----------------+
|      1 | a        |     151 |        60 |             65 |
|      2 | b        |     152 |        61 |             66 |
|      3 | c        |     153 |        62 |             67 |
|      4 | d        |     154 |        63 |             68 |
|      5 | e        |     155 |        64 |             69 |
+--------+----------+---------+-----------+----------------+

group by 可以对包含两个或多个列进行分组。

mysql> select * from student;
+--------+----------+---------+-----------+
| stu_id | stu_name | stu_tel | stu_score |
+--------+----------+---------+-----------+
|      1 | a        |     151 |        60 |
|      2 | a        |     152 |        60 |
|      3 | a        |     153 |        61 |
|      4 | b        |     154 |        62 |
|      5 | b        |     155 |        62 |
|      6 | b        |     156 |        63 |
|      7 | c        |     157 |        64 |
|      8 | c        |     158 |        64 |
|      9 | c        |     159 |        65 |
+--------+----------+---------+-----------+

mysql> select *,count(stu_tel) from student group by stu_name,stu_score;
+--------+----------+---------+-----------+----------------+
| stu_id | stu_name | stu_tel | stu_score | count(stu_tel) |
+--------+----------+---------+-----------+----------------+
|      1 | a        |     151 |        60 |              2 |
|      3 | a        |     153 |        61 |              1 |
|      4 | b        |     154 |        62 |              2 |
|      6 | b        |     156 |        63 |              1 |
|      7 | c        |     157 |        64 |              2 |
|      9 | c        |     159 |        65 |              1 |
+--------+----------+---------+-----------+----------------+


在group by子句之后使用having运算符,对查询结果限定条件,系统仅返回满足条件的组结果。having子句可包含一个或多个用and和or连接的谓词。

mysql> select *,max(stu_score) as Max 
     > from student 
     > group by stu_name 
     > having Max>65;
+--------+----------+---------+-----------+------+
| stu_id | stu_name | stu_tel | stu_score | Max  |
+--------+----------+---------+-----------+------+
|      2 | b        |     152 |        61 |   66 |
|      3 | c        |     153 |        62 |   67 |
|      4 | d        |     154 |        63 |   68 |
|      5 | e        |     155 |        64 |   69 |
+--------+----------+---------+-----------+------+
在having子句之后使用order by运算符,可以是查询结果按照顺序排序
mysql> select *,max(stu_score) as Max 
     > from student 
     > group by stu_name 
     > having Max>65 
     > order by Max desc;
+--------+----------+---------+-----------+------+
| stu_id | stu_name | stu_tel | stu_score | Max  |
+--------+----------+---------+-----------+------+
|      5 | e        |     155 |        64 |   69 |
|      4 | d        |     154 |        63 |   68 |
|      3 | c        |     153 |        62 |   67 |
|      2 | b        |     152 |        61 |   66 |
+--------+----------+---------+-----------+------+
在order by子句之后使用limit运算符,限制显示多少条数据
mysql> select *,max(stu_score) as Max 
     > from student 
     > group by stu_name 
     > having Max>65 
     > order by Max desc 
     > limit 1,3;
+--------+----------+---------+-----------+------+
| stu_id | stu_name | stu_tel | stu_score | Max  |
+--------+----------+---------+-----------+------+
|      4 | d        |     154 |        63 |   68 |
|      3 | c        |     153 |        62 |   67 |
|      2 | b        |     152 |        61 |   66 |
+--------+----------+---------+-----------+------+
select子句中可以插入case表达式,这个表达式充当一种if-then-else语句。
mysql> select *,
     > case 
     > when stu_score>'65' then '1' 
     > when stu_score='65' then '2' 
     > else '3' end as level
     > from student;
+--------+----------+---------+-----------+-------+
| stu_id | stu_name | stu_tel | stu_score | level |
+--------+----------+---------+-----------+-------+
|      1 | a        |     151 |        60 | 3     |
|      2 | b        |     152 |        61 | 3     |
|      3 | c        |     153 |        62 | 3     |
|      4 | d        |     154 |        63 | 3     |
|      5 | e        |     155 |        64 | 3     |
|      6 | a        |     156 |        65 | 2     |
|      7 | b        |     157 |        66 | 1     |
|      8 | c        |     158 |        67 | 1     |
|      9 | d        |     159 |        68 | 1     |
|     10 | e        |     160 |        69 | 1     |
+--------+----------+---------+-----------+-------+
match运算符:用于查找某一列中字符串内的某一单词。用于产品介绍,图书内容,帮助手册等存有大量信息的查找(待续)。

like运算符:使用模式查找,其中百分号%代表任意0个、1个或多个任意字符,下划线_代表一个随机字符。若不用百分号或者下划线,like就相当于等于号=。

mysql> select * from student 
     > where stu_tel like '_6%';
+--------+----------+---------+-----------+
| stu_id | stu_name | stu_tel | stu_score |
+--------+----------+---------+-----------+
|     10 | e        |     160 |        69 |
+--------+----------+---------+-----------+

like的模式匹配中可以使用转义字符定义escape。escape可以定义任何字符为转移字符。如下例定义‘#’为转义字符,跟在‘#’后面的‘_’失去了原有意义。

mysql> select * from student 
     > where stu_name like '%#_%' escape '#';
+--------+----------+---------+-----------+
| stu_id | stu_name | stu_tel | stu_score |
+--------+----------+---------+-----------+
|     11 | a_b      |     166 |        70 |
+--------+----------+---------+-----------+

人气教程排行