mysql中select简单查询

1.简单查询select语句(1)

(1)

create database chapter04;

use chapter04;

create table exam

( id int not null primary key default null auto_increment,

name varchar(20) not null,

Chinese double,

math double,

english double

);

insert into exam

values(null,'关羽',85,76,70);

insert into exam

values(null,'张飞',70,75,70);

insert into exam

values(null,'赵云',90,65,95);

(2)

select name,english from exam;

(3)

select distinct english from exam;

2.简单查询select语句（2）

(1)select Chinese+10,math+10,english+10 from exam;

(2)select sum(Chinese+math+english) from exam where name='关羽’;

select sum(Chinese+math+english) from exam where name='张飞’; select sum(Chinese+math+english) from exam where name='赵云’;

(3)select sum(Chinese+math+english) as score from exam;

3.简单查询select语句（3）

(1)select name,score from exam where name='张飞';

(2)select name,english from exam where english>90;

(3)select name,score from exam where score>230;

4.简单查询select语句（4）

(1)select name,english from exam where english between 80 and 100;

(2)select name,math from exam where math in (75,76,77);

(3)insert into exam

values(null,'张三',80,70,78);

select score,name from exam where name like'张%';

(4)select name,score from exam where math>70 and Chinese>80;

5.聚合函数－count

(1)select count (*) from exam;

(2)select count (*) from exam where math>70;

(3)select count (*) from exam where Chinese+english+math>230;

6.聚合函数－sum

(1)select sum(math) from exam;

(2)Select sum(Chinese) from exam;

Select sum(math) from exam;

Select sum(english) from exam;

(3)select sum(Chinese+english+math) from exam;

(4)Select sum(chinese)/select count(*)from exam;

7.聚合函数－avg

(1)select avg(math) from exam;

(2)select avg(math+english+Chinese) from exam;

8.聚合函数－max/min

(1)select max(math+english+Chinese) from exam;

select min(math+english+Chinese) from exam;

9.排序查询select语句（5）

(1)select * from exam order by english asc;

(2)select * from exam order by math+english+Chinese desc;

(3)select * from exam order by math+english+Chinese asc where name like'张%';

10.分组查询select语句（6）

use chapter04;

create table orders

( id int(10) ,

product varchar(20),

price float

);

insert into orders

values(1,'电视',900)，

(2,'洗衣机',100),

(3,'洗衣粉',90),

(4,'桔子',9),

(5,'洗衣粉',90);

(1)select * from orders group by product;

(2)select product sum(price) from orders group by product having sum(price)>100;

(3)select * from orders where price<100 group by product having sum(price)>100;

11.使用limit限制查询结果数量

(1)select * from exam limit 5;

(2)select * from exam limit 5,4;