MySQL实战面试题

create database hufei;

use hufei;

create table user_info(id int,device_id int(10),gender varchar(14),age int,university varchar(32),province varchar(32),gpa float);

insert into user_info values(1,2138,'male',21,'北京大学','Beijing',3.4);

insert into user_info values(2,3214,'male',19,'北京大学','Shanghai',4);

insert into user_info values(3,3214,'female',31,'复旦大学','Shanghai',3.2);

insert into user_info (id,device_id,gender,university,province,gpa) values(4,3215,'male','华东大学','Shanghai',3.6);

 

select id,device_id,gender,age,university,province from user_info;

select university from user_info group by university;

select device_id from user_info limit 2;

select device_id zhangfei_id from user_info limit 2;

select device_id,gpa,age from user_info order by gpa asc,age asc;

select device_id from user_info where university='北京大学';

select device_id,gender,age,university from user_info where age>20 and age <23;

select device_id,gender,age,university from user_info where university !='北京大学';

select device_id,gender from user_info where age is not null;

select device_id,gender from user_info where gpa>3.5 and gender='male';

select device_id,gender,age,university,gpa from user_info where gpa>=3.7 or university='北京大学';

select device_id,gender,age,university,gpa from user_info where university in ('北京大学','复旦大学');

select device_id,gender,age,university,gpa from user_info where (gpa>3.5 and university='山东大学') or (gpa>3.8 and university='复旦大学')

 

like模糊查询，在一些查询时，可能把握不准需要查询的确切值。模糊查询使用like关键字通过字符匹配检索出所需要的数据行。字符匹配使用通配符%（0个或者多个任意字符）和_（任意一个字符）

select device_id,age,university from user_info where university like '%北京%';

instr（源字符串，检索的字符串），使用instr代替like sql更加简洁以及性能更加强大

select device_id,age,university from user_info where instr(university,'师范');

select device_id,age,university from user_info where university regexp '^师范';

 

 

select gpa from user_info where university='复旦大学' order by gpa desc limit 1;

select max(gpa) as gpa from user_info where university='复旦大学';

select count(gender) as male_num,round(avg(gpa),1) from user_info where gender='male';

 

select count(gender) as male_num,round(avg(gpa),1) from user_info where gender='male';