【mysql】mysql(一)查数据

查询上面这种表：

1.查询表中所有数据：MariaDB [test]> select * from bug;

2.查询系统为11.3的bug列表：MariaDB [test]> select * from bug where system="iOS11.3";

3.设定返回的记录行数：MariaDB [test]> select * from bug where system="iOS11.3" limit 1;

4.设定开始查询的向下偏移量;MariaDB [test]> SELECT * FROM BUG LIMIT 2 OFFSET 1;

5.模糊查询，查询testname 以3结尾：MariaDB [test]> select * from bug where testername like "%3";

6.从bug表和tester表中，选中不同的name: select testername from bug union select name from tester;

7.从bug表和tester表中，选中重复的name:MariaDB [test]> select testername from bug union all select name from tester;

8.按iD升序排列：MariaDB [test]> select * from bug order by id;

9.按id降序排列：MariaDB [test]> select * from bug order by id desc;

10.按system分组，并统计每种system有多少个：MariaDB [test]> select system,count(*) from bug group by(system);

还可以用sum(),avg()等

11.内连接

select a.runoob_count,b.submission_date from tb1,a inner join tb2,b on a.runoob_author =b.runoob_author;

12.左连接

select a.runoob_count,b.submission_date from tb1,a left join tb2,b on a.runoob_author =b.runoob_author;

13.右连接

select a.runoob_count,b.submission_date from tb1,a right join tb2,b on a.runoob_author =b.runoob_author;