mysql语句之select

• Select语句是指从现存的一个或多个表中查看满足条件的数据

• Select语句常规用法：
• Select from students; ##查看表中所有数据
• Select sid,sname from students; ##查看所有的sid和sname
• Select sid,sname from students where sid=1; ##查看符合条件的数据
• Select from students order by sid; ##查看排序后的数据
• Select sex,count() from students group by sex having count()>=2; ##查看分
组的数据
• Select from students a inner join students2 b on a.sid=b.sid; ##查看两个表
链接后的数据
• Select sid as a,sname as b from students; ##字段使用别名的方法1
• Select sid a,sname b from students; ##字段使用别名的方法2
• Select_expr关键词代表要查询的字段，至少要有一个select_expr，或者如果
是查询所有的字段，则用号代替
• Table_references关键词代表查询数据来自的一个或多个表
• Where子句代表只查询满足条件的表数据，如果没有where子句则代表查询
表中所有的数据
• 查询t1和t2表中所有的字段
• SELECT FROM t1 INNER JOIN t2 ...
• SELECT t1., t2. FROM t1 INNER JOIN t2 ...
• Select_expr也可以使用MySQL内部的函数，另外字段也可以使用别名
• SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;
• SELECT CONCAT(last_name,', ',first_name) full_name
FROM mytable ORDER BY full_name;
• Where条件中不能使用select_expr中定义的字段别名，因为语句执行顺序是
where在select之前，所以where在执行时字段别名未知
From table_references子句中指定表名，tbl_name也可以指定别名，
当涉及的表不在当前的数据库时，需要使用db_name.tbl_name来
指定表和所在的数据库名
• SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
WHERE t1.name = t2.name;
• SELECT t1.name, t2.salary FROM employee t1, info t2
WHERE t1.name = t2.name;
• 当多个表中有相同的字段名，且需要查询出来时，需要在
select_expr中使用tbl_name.column_name来显视指定要查询哪个
表的字段
• From table_references子句中指定表名，tbl_name也可以指定别名，
当涉及的表不在当前的数据库时，需要使用db_name.tbl_name来
指定表和所在的数据库名
• SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
WHERE t1.name = t2.name;
• SELECT t1.name, t2.salary FROM employee t1, info t2
WHERE t1.name = t2.name;
• 当多个表中有相同的字段名，且需要查询出来时，需要在
select_expr中使用tbl_name.column_name来显视指定要查询哪个
表的字段
• 查看另外的数据库的表数据
• Select from test2.students;
• 两个数据库里的表关联查询
• Select from students a inner join test2.students b on a.sid=b.sid;
• +------+-------+------+------+-------+
• | sid | sname | sex | sid | sname |
• +------+-------+------+------+-------+
• | 1 | aaa | 0 | 1 | abc |
• | 2 | ccc | 1 | 2 | bcd |
• 有相同字段名时要指定表名，可以用表的别名(注意执行顺序)
• mysql> select sname from students a inner join test2.students b on a.sid=b.sid;
• ERROR 1052 (23000): Column 'sname' in field list is ambiguous
• mysql> select a.sname from students a inner join test2.students b on a.sid=b.sid;
• +-------+
• | sname |
• +-------+
• | aaa |
• | ccc |
• Group by子句代表分组，通常和聚合函数配合使用，如最大值
max, 最小值min, 平均值avg, 个数count，求和sum
• insert into scores
values(1,'english',88),(1,'chinese',86),(1,'math',90),(2,'english',95),(2,'chinese',
84);
• select sum(score) from scores;
• select sid,count(),max(score),min(score),avg(score),sum(score) from scores
group by sid;
• +------+----------+------------+------------+------------+------------+
• | sid | count() | max(score) | min(score) | avg(score) | sum(score) |
• +------+----------+------------+------------+------------+------------+
• | 1 | 3 | 90 | 86 | 88.0000 | 264 |
• | 2 | 2 | 95 | 84 | 89.5000 | 179 |
• Order by和group by子句可以引用select_expr中的列，通过以下三种方式：
• SELECT college, region, seed FROM tournament
ORDER BY region, seed;
• SELECT college, region AS r, seed AS s FROM tournament
ORDER BY r, s;
• SELECT college, region, seed FROM tournament
ORDER BY 2, 3;
• Order by子句表示查询结果按照顺序排列，默认是升序排列，可以指定DESC
表明按照降序排列
• Having子句一般是跟在group by子句之后，代表限制分组之后的结果
• SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) > 10;
• Limit子句用来限制查询结果的条数，其后可以带两位>0的整数，第一位代表
offset，第二位代表取多少行
• SELECT FROM tbl LIMIT 5; # Retrieve first 5 rows，等同于select from tbl limit 0,5
• SELECT FROM tbl LIMIT 5,10; # Retrieve rows 6-15
• select sid,avg(score) from scores group by sid having avg(score)>88
• +------+------------+
• | sid | avg(score) |
• +------+------------+
• | 2 | 89.5000 |
• +------+------------+
• select from scores order by sid desc,score;
• +------+---------+-------+
• | sid | class | score |
• +------+---------+-------+
• | 2 | chinese | 84 |
• | 2 | english | 95 |
• | 1 | chinese | 86 |
• | 1 | english | 88 |
• | 1 | math | 90 |
• Select … into语句代表将查询结果写入文件中或者定义的参数变量
中
• For update关键词代表将查询的数据行加上写锁，直到本事务提
交为止
• Lock in share mode关键词代表将查询的数据行加上读锁，则其他
的链接可以读相同的数据但无法修改加锁的数据
• ALL/Distinct关键词代表是否将查询结果中完全重复的行都查询出
来，ALL是默认值代表都查询出来，指定distinct代表重复行只显示
一次
• HIGH_PRIORITY代表赋予读操作较高的操作优先级
• Max_statement_time=N子句代表设置语句执行超时时间（毫秒）
• Straight_join关键词代表强制优化器在表连接操作时按照语句中
from子句中的表的顺序执行
• Sql_big_result/sql_small_result通常是和group by/distinct一起使用，
其作用是事先告诉优化器查询结果是大还是小，以便优化器事先
准备好将查询结果存放在磁盘临时表或者快速临时表中以便后续
操作
• Sql_buffer_result强制将查询结果存入临时表中
• Sql_calc_found_rows关键词代表要求查询结果的同时计算结果的
行数，以便后续通过SELECT FOUND_ROWS()直接获取行数
• Sql_cache/sql_no_cache代表是否直接从query cache中获取查询结
果
mysql> select count(),count(all sid),count(distinct sid) from scores;
• +----------+----------------+---------------------+
• | count() | count(all sid) | count(distinct sid) |
• +----------+----------------+---------------------+
• | 5 | 5 | 2 |
• Select … into语句代表将查询结果存入定义的变量或者文件
• SELECT ... INTO var_list将查询结果存入定义的变量
• SELECT ... INTO OUTFILE将查询结果按照一定的格式写入到文件中
• SELECT ... INTO DUMPFILE将查询结果以一行的格式写入到文件中，且只能
写入一行
• 当使用存入变量方法是，需要保证查询结果返回一行，如果不返
回数据则报no data错误，如果返回多行则报Result consisted of
more than one row错误，当返回行数不确定时，可以用limit 1强制
只返回一行
• SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
• 使用Select … into outfile ‘file_name’时，文件会创建在本地服务器
上，所以要确保你的用户能创建文件，而且此file_name不能已经
存在在服务器上以免覆盖其他文件
• SELECT sid,sname,sex INTO OUTFILE '/tmp/students.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;
• ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement
• My.ini配置文件中添加secure_file_priv=/tmp/后重启再执行，成功
• mysql> select into dumpfile '/tmp/students3.txt' from students;
• mysql> select into dumpfile '/tmp/students4.txt' from students limit
1;
• Query OK, 1 row affected (0.00 sec）
当select语句中涉及到多表查询结果时，就会用到表连接操作

• 在MySQL中，join/inner join/cross join三者的意思是一样的
• Join语句中表别名的用法
• SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
• SELECT t1.name, t2.salary
FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
• From子句后面还可以跟子查询，但子查询必须带别名
• SELECT FROM (SELECT 1, 2, 3) AS t1;
• 当inner join或者表之间用逗号隔开，且没有表之间的关联字段，则代
表结果是两者的笛卡尔积
• conditional_expr子句一般代表指定两个表之间的关联条件，而where条
件中指定查询结果的筛选条件
• STRAIGHT_JOIN和Join的用法大致相同，唯一不同是确保左表是先被读
取的，以保证优化器的读取顺序
• SELECT FROM students, scores; ##没有关联条件，表示笛卡尔积
• SELECT FROM students INNER JOIN scores ON students.sid=scores.sid;
• SELECT FROM students LEFT JOIN scores ON students.sid=scores.sid;
• SELECT FROM students LEFT JOIN scores USING (sid);
• SELECT * FROM students LEFT JOIN students2 ON
students.sid=students2.sid
LEFT JOIN scores ON students2.sid=scores.sid;
• Union用来将多个select语句的执行结果合并成一个结果
• 第一个select语句的column_name会被当做最后查询结果的列名，
接下来的每个select语句所一一对应的列应该和第一个语句的列的
数据类型最好保持一致
• 默认情况下union语句会把最终结果中的重复行去掉，这和增加
distinct这个关键词的作用一样，如果使用union all则代表最终结
果中的重复行保留
• mysql> select sid,sname from students
• union
• select sid,gender from students2;
• +-----+-------+
• | sid | sname |
• +-----+-------+
• | 3 | a |
• | 4 | a |
• | 5 | a |
• | 1 | abc |
• | 2 | abc |
• | 1 | -2 |
• | 2 | -1 |
• mysql> select sid,sname from students
• union
• select sid,gender from students2;
• +-----+-------+
• | sid | sname |
• +-----+-------+
• | 3 | a |
• | 4 | a |
• | 5 | a |
• | 1 | abc |
• | 2 | abc |
• | 1 | -2 |
• | 2 | -1 |
• mysql> select sid,sname from students
• union
• select sid,gender from students2;
• +-----+-------+
• | sid | sname |
• +-----+-------+
• | 3 | a |
• | 4 | a |
• | 5 | a |
• | 1 | abc |
• | 2 | abc |
• | 1 | -2 |
• | 2 | -1 |