mysql查询例,MySQL查询示例

use test;

create table t1(

tid smallint(5) unsigned auto_increment,

tname varchar(50),

tkecheng varchar(50),

tgrade smallint(10),

primary key(tid)

)ENGINE=innodb default charset=utf8;

INSERT into t1(tname,tkecheng,tgrade)

values('xm','yuwen',76),

('xm','shuxue',89),

('xm1','yuwen',73),

('xm1','shuxue',84),

('xm2','yuwen',72),

('xm2','shuxue',88),

('xm3','yuwen',70),

('xm3','shuxue',69),

('xm4','yuwen',69),

('xm4','shuxue',79),

('xm5','yuwen',76),

('xm5','shuxue',78),

('xm6','yuwen',84),

('xm6','shuxue',83),

('xm7','yuwen',76),

('xm7','shuxue',68),

('xm8','yuwen',84),

('xm8','shuxue',78),

('xm9','yuwen',76),

('xm9','shuxue',98),

('xm10','yuwen',84),

('xm10','shuxue',78),

('xm11','yuwen',86),

('xm11','shuxue',68);

commit;

select * from t1;

#1 数学成绩最高的前10条记录，按数学成绩降序排列

select * from t1 where t1.tkecheng='shuxue' order by t1.tgrade desc limit 10;

#2 查询所有科目都在80分以上记录

select * from t1 where t1.tgrade>=80;

s(sno，sn,sd,sa)sno，sn,sd,sa分别代表学号，学员姓名，班级，学员年龄

c(cno，cn)cno,cn分别代表课程编号，课程名称

sc(sno，cno,g)sno,cno,g分别代表学号，所选的课程编号，学习成绩

create table s(

sno smallint(5),

sn varchar(50),

sd varchar(50),

sa smallint(10)

)ENGINE=innodb default charset=utf8;

insert into s(sno,sn,sd,sa)

values(1001,'小明','班级1',24),

(1002,'小赵','班级1',22),

(1003,'小黄','班级2',23),

(1004,'小张','班级2',25),

(1005,'小李','班级3',23),

(1006,'小罗','班级3',22),

(1007,'小增','班级3',25);

commit;

create table c(

cno smallint(5),

cn varchar(50)

)ENGINE=innodb default charset=utf8;

insert into c(cno,cn)

values(9001,'功能测试'),

(9002,'自动化测试'),

(9003,'性能测试'),

(9004,'安全性测试'),

(9005,'体验性测试'),

(9006,'全栈测试');

commit;

create table sc(

sno smallint(5),

cno varchar(50),

g varchar(50)

)ENGINE=innodb default charset=utf8;

insert into sc(sno,cno,g)

values(1001,9001,78),

(1001,9002,68),

(1001,9003,84),

(1001,9004,62),

(1001,9005,98),

(1001,9006,89),

(1002,9001,68),

(1002,9002,64),

(1002,9003,83),

(1002,9004,63),

(1002,9005,90),

(1003,9003,68),

(1004,9004,99),

(1005,9005,99),

(1006,9001,69),

(1006,9002,67),

(1006,9003,86),

(1006,9004,87),

(1006,9005,90),

(1006,9006,91),

(1007,9001,99);

commit;

1)查询选修课程名称为‘性能测试’的学员学号和姓名？

select s.sno,s.sn from s,sc,c

where s.sno=sc.sno

and sc.cno=c.cno

and c.cn='性能测试';

2)查询选修课程超过5门的学员学号和所属班级？

select s.sno,s.sd from s

where s.sno

in (

select sc.sno

from c,sc

where c.cno=sc.cno

group by sc.sno

having count(*)>5

);

3)用户名为xuelei在2013-09-16和2013-09-17天插入的任务。

任务名包含"Arrow"，文件大小大于200

按文件由大到小排序，只提取出前5条数据

create table User_task(

Username varchar(50) comment '用户名',

Taskid int comment '任务id',

Taskname varchar(50) comment '任务名',

Task_filesize int comment '文件大小',

Insert_time datetime comment '插入日期'

)ENGINE=innodb default charset=utf8;

insert into User_task(Username,Taskid,Taskname,Task_filesize,Insert_time)

values('xuelei',1001,'Arrow is not a ',200,str_to_date('2013-09-15 09:00:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1002,'hao Arrow is not b ',201,str_to_date('2013-09-16 09:00:01', '%Y-%m-%d %H:%i:%S')),

('xuelei',1003,'cc Arrow is not c ',203,str_to_date('2013-09-17 09:01:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1004,'ddArrow is not d ',204,str_to_date('2013-09-18 09:00:02', '%Y-%m-%d %H:%i:%S')),

('xuelei',1005,'efArrow is not e ',203,str_to_date('2013-09-19 09:20:00', '%Y-%m-%d %H:%i:%S')),

('ceshi',2001,'agbrrow is not a ',203,str_to_date('2013-09-16 09:10:00', '%Y-%m-%d %H:%i:%S')),

('ceshi',2002,'brrow is not b ',203,str_to_date('2013-09-16 16:20:00', '%Y-%m-%d %H:%i:%S')),

('ceshi',2003,'brrow is not c ',203,str_to_date('2013-09-15 09:20:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',2002,'Afrrow is not b ',303,str_to_date('2013-09-16 16:20:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',2003,'arrow is not c ',303,str_to_date('2013-09-16 09:20:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',3001,'Acrrow is not a ',200,str_to_date('2013-09-15 09:00:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',3002,'AArrow is not b ',201,str_to_date('2013-09-16 09:00:01', '%Y-%m-%d %H:%i:%S')),

('xuelei',3003,'aaArrow is not c ',203,str_to_date('2013-09-17 09:01:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',3004,'Arrow is not d ',204,str_to_date('2013-09-18 09:00:02', '%Y-%m-%d %H:%i:%S')),

('xuelei',3005,'Acrrow is not e ',203,str_to_date('2013-09-19 09:20:00', '%Y-%m-%d %H:%i:%S')),

('ceshi',4001,'brrow is not a ',203,str_to_date('2013-09-20 09:10:00', '%Y-%m-%d %H:%i:%S')),

('ceshi',4003,'brrow is not c ',203,str_to_date('2013-09-22 09:20:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',5002,'Arrow is not b ',303,str_to_date('2013-09-23 16:20:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',5003,'arrow is not c ',303,str_to_date('2013-09-24 09:20:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1011,'Arrow is not c ',201,str_to_date('2013-09-19 09:00:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1008,'arrow is not a ',199,str_to_date('2013-09-15 09:00:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1009,'arrow is not b ',200,str_to_date('2013-09-15 09:00:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1010,'arrow is not c ',201,str_to_date('2013-09-15 09:00:00', '%Y-%m-%d %H:%i:%S')),

('xuelei',1012,'Arrow is not b ',201,str_to_date('2013-09-15 23:59:59', '%Y-%m-%d %H:%i:%S')),

('xuelei',1013,'Arrow is not c ',201,str_to_date('2013-09-17 23:59:59', '%Y-%m-%d %H:%i:%S'));-

('xuelei',1014,'what is a Arrow is not c ',202,str_to_date('2013-09-17 23:59:59', '%Y-%m-%d %H:%i:%S'));

('xuelei',1015,' Arrow Arrow Arrow',203,str_to_date('2013-09-17 23:59:59', '%Y-%m-%d %H:%i:%S'));

commit;

select * from User_task ut

where ut.username='xuelei'

and INSTR(ut.Taskname,binary 'Arrow')<>0

and ut.task_filesize>200

and date_format(ut.insert_time,'%Y-%m-%d') between '2013-09-16' and '2013-09-17'

order by ut.task_filesize

desc limit 5;

内容来源于网络如有侵权请私信删除