Mysql query example

Mysql query example

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', 'shuxu', 68 ),
('Xm8', 'yuwen', 84 ),
('Xm8', 'shuxue ', 78 ),
('Xm9', 'yuwen', 76 ),
('Xm9', 'shuxue', 98 ),
('Xm10', 'yuwen', 84 ),
('Xm10', 'shuxu', 78 ),
('Xm11', 'yuwen', 86 ),
('Xm11', 'shuxu', 68 );
Commit;

Select * from t1;

#1 the first 10 records with the highest scores are sorted in descending order of the scores.

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

#2 query records of all subjects with a score of more than 80
Select * from t1 where t1.tgrade> = 80;

S (sno, sn, sd, sa) sno, sn, sd, sa respectively represent the student ID, Student name, class, student age
C (cno, cn) cno and cn respectively represent the course number and Course name
SC (sno, cno, g) sno, cno, and g represent Student IDs, course IDs, and scores respectively.

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, 'xiaoming ', 'class 1', 24 ),
(1002, 'xiao Zhao ', 'class 1', 22 ),
(1003, 'xiao Huang ', 'class 2', 23 ),
(1004, 'xiao zhang', 'class 2', 25 ),
(1005, 'lil', 'class 3', 23 ),
(1006, 'Ron', 'class 3', 22 ),
(1007, 'small increment', 'class 3', 25 );
Commit;

Create table c (
Cno smallint (5 ),
Cn varchar (50)
) ENGINE = innodb default charset = utf8;

Insert into c (cno, cn)
Values (9001, 'function test '),
(9002, 'Automated test '),
(9003, 'performance test '),
(9004, 'security test '),
(9005, 'experience test '),
(9006, 'full stack test ');
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) query the student ID and name of the elective course named 'performance test?
Select s. sno, s. sn from s, SC, c
Where s. sno = SC. sno
And SC. cno = c. cno
And c.cn = 'performance test ';

2) query the student ID and class of more than 5 elective courses?
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) tasks with the username xuelei inserted on and.
The task name contains "Arrow". The file size is greater than 200.
Sort by files in ascending order and extract only the first five data records.

Create table User_task (
Username varchar (50) comment 'username ',
Taskid int comment 'Task id ',
Taskname varchar (50) comment 'Task name ',
Task_filesize int comment 'file size ',
Insert_time datetime comment 'insert date'
) ENGINE = innodb default charset = utf8;

Insert into User_task (Username, Taskid, Taskname, Task_filesize, Insert_time)
Values ('x', 1001, 'arrow is not a', 200, str_to_date ('2017-09-15 09:00:00 ',' % Y-% m-% d % H: % I: % s ')),
('Xuelei ', 1002, 'hao Arrow is not B', 201, str_to_date ('2017-09-16 09:00:01 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1003, 'CC Arrow is not C', 203, str_to_date ('2017-09-17 09:01:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1004, 'ddarrow is not d', 204, str_to_date ('2017-09-18 09:00:02 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1005, 'efarrow is not E', 203, str_to_date ('2017-09-19 09:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('Ceshi', 2001, 'agbrrow is not a', 203, str_to_date ('2017-09-16 09:10:00 ',' % Y-% m-% d % H: % I: % s ')),
('Ceshi', 2002, 'brrow is not B ', 203, str_to_date ('2017-09-16 16:20:00', '% Y-% m-% d % H: % I: % s ')),
('Ceshi', 2003, 'brrow is not C', 203, str_to_date ('2017-09-15 09:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('Xuelei ', 2002, 'afrrow is not B', 303, str_to_date ('2017-09-16 16:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 2003, 'arrow is not C', 303, str_to_date ('2017-09-16 09:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 3001, 'acrrow is not a', 200, str_to_date ('2017-09-15 09:00:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 3002, 'invalid row is not B ', 201, str_to_date ('2017-09-16 09:00:01', '% Y-% m-% d % H: % I: % s ')),
('X', 3003, 'aaarrow is not C', 203, str_to_date ('2017-09-17 09:01:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 3004, 'arrow is not d', 204, str_to_date ('2017-09-18 09:00:02 ',' % Y-% m-% d % H: % I: % s ')),
('X', 3005, 'acrrow is not E', 203, str_to_date ('2017-09-19 09:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('Ceshi', 4001, 'brrow is not a', 203, str_to_date ('2017-09-20 09:10:00 ',' % Y-% m-% d % H: % I: % s ')),
('Ceshi', 4003, 'brrow is not C', 203, str_to_date ('2017-09-22 09:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 5002, 'arrow is not B ', 303, str_to_date ('2017-09-23 16:20:00', '% Y-% m-% d % H: % I: % s ')),
('X', 5003, 'arrow is not C', 303, str_to_date ('2017-09-24 09:20:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1011, 'arrow is not C', 201, str_to_date ('2017-09-19 09:00:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1008, 'arrow is not a', 199, str_to_date ('2017-09-15 09:00:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1009, 'arrow is not B ', 200, str_to_date ('2017-09-15 09:00:00', '% Y-% m-% d % H: % I: % s ')),
('X', 1010, 'arrow is not C', 201, str_to_date ('2017-09-15 09:00:00 ',' % Y-% m-% d % H: % I: % s ')),
('X', 1012, 'arrow is not B ', 201, str_to_date ('2017-09-15 23:59:59', '% Y-% m-% d % H: % I: % s ')),
('X', 1013, 'arrow is not C', 201, str_to_date ('2017-09-17 23:59:59 ',' % Y-% m-% d % H: % I: % s '));-
('X', 1014, 'What is a Arrow is not C', 202, str_to_date ('2017-09-17 23:59:59 ', '% Y-% m-% d % H: % I: % s '));
('Xuelei ', 1015, 'arrow arrow', 203, str_to_date ('2017-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 '2017-09-16' and '2017-09-17'
Order by ut. task_filesize
Desc limit 5;