数据库之MQL

 1，查询所有

mysql> select * from grade;

2，

mysql> select id,firstname,lastname from grade;

3，

mysql> select firstname,lastname from grade where id > 4;

4，

mysql> select * from grade where sex = 'f';

5，

mysql> select * from grade where id=2 or id =4 or id=6;

6，

mysql> select * from grade where math between 85 and 94;

7，

mysql> select * from grade where firstname like 'A%';

8，

mysql> select * from grade where firstname like 'A%e';

9，

mysql> select * from grade where firstname like '%l%';

10，

mysql> select * from grade where firstname like 'A____';

11，

mysql> select * from grade where english between 85 and 94;

12，

mysql> select * from grade where math >90 or chinese >90 ;

13，

mysql> select * from grade where id !=1 and  id!=3 and id!= 5 and id!=7;

14，

mysql> select distinct sex from grade;

15，

mysql> select distinct lastname from grade;

16，

mysql> select count(id) from grade ;

17，

mysql> select count(id) from grade where english > 80 ;

18，

mysql> select sum(math) from grade;

19，

mysql> select sum(math) from grade where sex ='f';

20，

mysql> select avg(english) from grade;

21，

mysql> select avg(english) from grade where sex = 'm';

22，

mysql> select max(math) from grade;

23，

mysql> select max(math) from grade where sex='m';

24，

mysql> select * from grade order by math asc;

 

25，

mysql> select * from grade order by sex  asc,chinese desc;

26，

mysql> select sex,GROUP_CONCAT(CONCAT(firstname, ' ', lastname) SEPARATOR ', ') AS students_list from grade group by sex;

 

27，

mysql> select count(*) from grade group by lastname;

28，

29，

mysql> select * from grade where id between 3 and 6;

30，

select id,sex,firstname,lastname,(english + math + chinese) / 3 AS average from grade where sex = 'm' AND (english + math + chinese) / 3 > 85;