mysql基本运算符

来源：动视网责编：小采时间：2020-11-09 07:24:13

mysql基本运算符

mysql基本运算符:批量导入数据： mysql load data local infile 'e://source/student.txt' into table student lines terminated by '\r\n';mysql select * from student;+--------+----------+---------+---------

推荐度：

点击下载本文 文档为doc格式

导读mysql基本运算符:批量导入数据： mysql load data local infile 'e://source/student.txt' into table student lines terminated by '\r\n';mysql select * from student;+--------+----------+---------+---------

批量导入数据：

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 |
+--------+----------+---------+-----------+

mysql基本运算符

推荐度：

点击下载本文 文档为doc格式

标签：批量数据导入

热门焦点

mysql基本运算符

mysql基本运算符

mysql基本运算符

最新推荐

猜你喜欢

热门推荐