获取数据库的元数据

  • show columns from weiboes like "%\_%";
  • show index from weiboes;
  • SHOW COLUMNS FROM student WHERE Field = "student_id";

    这一语句可以用来查询表中是否有相应的字段。

  • SELECT COUNT(*) FROM tbl_name;

  • select * from mydatabase.weiboes where false; 如果表存在,则会执行成功,否则失败。

利用联结操作对多个数据表进行检索

1
2
3
4
5
6
7
SELECT select_list
FROM table_list
WHERE row_constraint
GROUP BY grouping_columns
ORDER BY sorting_columns
HAVING group_constraint # 组必须满足的条件
LIMIT count;

SELECT可以直接对表达式进行求值:SELECT SQRT(POW(3, 2)+POW(4,2))

内联结

内联结会让一个数据表的数据行和另一个数据表里的数据行进行匹配来产生结果,也就是这个表的每一项都会跟另一个表的每一项进行匹配。即笛卡尔积。

一般会使用WHERE语句来将数据联结操作只在某个数据列相等的情况进行。select * from t1 inner join t2 where t1.i1 = t2.i2;

另外CROSS JOINJOIN的联结类型类似于INNER JOIN。还有逗号关联操作符的效果与INNER JOIN也是相似的: SELECT * FROM t1, t2 WHERE t1.i1 = t2.i2;,不过应该避免逗号操作符。

  • ON:select * from t1 inner join t2 on t1.i1 = t2.i2;
  • USING(): select * from mytbl1 inner join mytbl2 using (b); 其中两个表中都有b这一列。

避免数据列或者表名的歧义

  • select a, mytbl1.b, mytbl2.b, c from mytbl1 inner join mytbl2;
  • select mytbl1.col1, m.col2 from mytbl inner join mytbl as m where mytbl.col1 > m.col1; 自联结

左联结和右联结

外联结会把其中一个数据表在另一个数据表里没有匹配的数据行也显示出来。左联结LEFT JOIN会把左数据表在右数据表里没有匹配的数据行也显示出来。而右联结RIGHT JOIN会将右数据表在左数据表里没有匹配的数据行也显示出来。

原理就是从左表的数据行来和右表匹配的时候,如果匹配上的话,就输出。如果左表数据在右表没有找到匹配,就会假设一个全为NULL的右表数据行,这样左表的这行数据也匹配到了。也就是说左表的每一行都有一个对应的数据行,不管在右表有没有匹配。

比如select * from t1 left join t2 on t1.i1 = t2.i2;

左联结可以让你找到在右数据表中没有匹配的左数据表的行。也就是查找哪些值没有出现过。

这本书关于联结这块的例子讲的很好!

用子查询进行多数据表检索

子查询与关系比较操作符

  • select * from score where event_id in (select event_id from grade_event where category = 'T');
  • select * from president where birth = (select min(birth) from president);
  • select last_name, first_name, city, state from president where (city, state) = (select city, state from president where last_name = 'Adams' and first_name = 'john');

IN和NOT IN子查询

  • select * from student where student_id in (select student_id from absence);
  • select last_name, first_name, city, state from president where (city, state) in (select city, state from president where last_name = 'Roosevelt');

all, any和some子查询

  • select last_name, first_name, birth from president where birth <= all (select birth from president); 最小的
  • select last_name, first_name, city, state from president where(city, state) = any (select city, state from president where last_name = 'Roosevelt');
  • IN(= any)< > all(NOT IN)

exists和not exists子查询

  • select exists (select * from absence);
  • select not exists (select * from absence);

from中的子查询

  • select * from (select 1, 2) as t1 inner join (select 3, 4) as t2;

子查询改写成联结查询

  • select * from score where event_id in (select event_id from grade_event where category = 'T');

    • select score.* from score inner join grade_event on score.event_id = grade_event.event_id where grade_event.category = 'T';
  • select * from student where student_id not in (select student_id from absence);

    • select student.* from student left join absence on student.student_id = absence.student_id where absence.student_id is NULL; 为NULL的时候则是学生表没有在absence表匹配到的记录,也就是没有缺席的学生

UNION语句进行多数据表检索

  • select i from t1 union select i from t2 union select i from t3;

默认UNION从结果集中剔除重复数据行,即UNION DISTINCT == UNION,如果保留重复行,UNION ALL

使用视图

视图是虚拟的数据表,提供查看数据表数据的另一种方法。

  • create view vpres as select last_name, first_name, city, state from president;
  • select * from vpress;

但是只能引用在该视图的定义里列出的数据列。

视图也是可以更新的,但是其必须直接映射到一个数据表上,选取的数据列是数据表数据列的简单引用。

事务处理