select语句

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

Select语句常规用法:

查看表中所有数据

Select * from students;

查看所有的sid和sname

Select sid,sname from students;

查看符合条件的数据

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; 

字段使用别名的方法1

Select sid as a,sname as b from students; 

字段使用别名的方法2

Select sid a,sname b from students; 

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中定义的字段别名,因为语句执行顺序是 whereselect之前,所以where在执行时字段别名未知01表中所有的数据

Select sid a,sname b from students where a>1;               -- 执行错误、 
ERROR 1054 (42S22): Unknown column 'a' in 'where clause'
Select sid a,sname b from students where sid>1;             -- 正确的写法 
+------+------+
|a      | b        | 
+------+------+ 
| 2     | ccc      |
+------+------+ 

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 |
2 | english |
1 | chinese |
1 | english |
1 | math | 90 |
84 | 95 |
86 | 88 |
  • 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|
  • sql 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/后重启再执行,成功
select * into dumpfile '/tmp/students3.txt' from students;
select * into dumpfile '/tmp/students4.txt' from students limit 1;
Query OK, 1 row affected (0.00 sec)

Select语句中的表连接

  • 当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语句中的表连接

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;

Select语句中的union

Union用来将多个select语句的执行结果合并成一个结果,第一个select语句的column_name会被当做最后查询结果的列名, 接下来的每个select语句所一一对应的列应该和第一个语句的列的 数据类型最好保持一致
默认情况下union语句会把最终结果中的重复行去掉,这和增加 distinct这个关键词的作用一样,如果使用union all则代表最终结 果中的重复行保留

Select语句中的union

 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 |

Select语句中的union Select

Union Select sid,sname from students all sid,sname from students2; 

Select语句中的union

  • 如果相对union语句的最后结果做排序或者limit限制,则需要将每 个select语句用括号括起来,把order by或limit语句放在最后
(Select sid,sname from students) Union (Select sid,sname from students2) order by sid limit 2;
+------+-------+
|sid |sname|
+------+-------+
| 1|aaa |
| 2 | ccc |
+------+-------+
2 rows in set (0.00 sec)

查询一个字段中有重复的记录

select user,count(*) as count from mysql.user group by user having count>1;

例子

有这么一个数据库course:
Students表(学生表):sid整型自增主键,sname字符串64位,gender字符串12位,dept_id整型并外键到dept表的id字段
Dept表(系表):id整型自增主键,dept_name字符串64位
Course表(课程表):id整型自增字段主键,course_name字符串64位,teacher_id整型外键到teacher表的id字段
Teacher表(老师表):id整型自增字段主键,name字符串64位,dept_id整型外键到dept表的id字段

需要查看每个系里面所有的老师name和对应的学生的sname,结果按照dept_id升序排序
select name,sname from teacher inner join students on teach-er.dept_id=students.dept_id order by teacher.dept_id;
求出每个系的学生个数和每个系里的老师个数?如果只用一个SQL语句实现怎么写?
每个系的学生个数:
select dept_name as '系名称',count(*) as '学生个数' from dept inner join students on dept.id=students.dept_id group by dept_name;
每个系的老师个数:
select dept_name as '系名称',count(*) as '老师个数' from teacher inner join dept on teacher.dept_id=dept.id group by dept_name;
通过老师表和课程表求出每个老师的课程个数,并且需要保证如果老师没有课程的时候则要显示课程为0
select teacher.name as '老师名称',count(teacher_id) as '课程数' from teacher left join course on teacher.id=course.teacher_id group by teacher.name order by 课程数 desc;
查看学生信息表里按照sid升序排序后的第7到第15行的学生数据
select * from students where sid  between 7 and 15 order by sid;
使用select into outfile将teacher表里的数据导出,注意字段之间用;号隔开,字符串用”号隔开
SELECT * INTO OUTFILE '/tmp/teacher.txt'
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM teacher;
创建一个包含每个老师姓名和对应的课程个数,且只显示所教课程个数在2个以上的视图view
create view teacher_name_and_course_view as select name,course_name from (select teacher.id,teacher.name from (select name,count(course_name) from course in-ner join teacher on course.teacher_id=teacher.id group by name having count(course_name)>2) as s inner join teacher on s.name=teacher.name) as i inner join course on i.id=course.teacher_id;