집 >데이터 베이스 >MySQL 튜토리얼 >mysql基本运算符
批量导入数据: 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 | +--------+----------+---------+-----------+