MySQL面试实战

一、testDev表中数据

 

表中具体数据

 

 

1、查询person中男女比例各有多少？

select sex,count(1) from person group by sex;

 

 

2、work表中，查询所有薪资，并将薪资从高到低排序

select * from work salary order by salary desc;

 

 

3、查询所有薪资，并对其进行等级划分

 select(case when salary>13000 then "优秀" when salary>12000 then "不错" else "一般" end)company,salary from work;

 

 

4、查询work中所有工资，然后查看相同薪资的内容

select * from work where salary in(select salary from work group by salary having count(1)>1);

 

 

5、通过内连接，查询姓名，工作类型，工作安全性，学历和学校类型

命令再客户端美化页面

 

 

 

 

二、请写出插入和删除的SQL语句

1、首先创建一个表格

    

2、插入分为两种情况：单条插入和多条插入

单条插入一个信息：insert into bg values("lh",98);

 

多条插入：insert into bg values("dm",98),("dt",94.5),("dn",84),("lx",78);

 

 

3、删除分为三种表达：三种方式删除整个表格，也可以定位信息删除表格中的某一项内容

删除整个表格

 

 删除表格中的一项，定位的信息必须是唯一性的

 

 

三、简述having和where的区别

1、以学生成绩为例

 

 

2、having是筛选某一个范围（如：成绩大于90，小于60之类的）

select name,grades from bg having grades>85;

 

 

3、where是定位具体的内容（如：需要查看具体多少成绩的学生，或者某一个学生的成绩)

where后跟字符串的时候需要加双引号。

 

 

 

 

1、表的结构信息；
mysql> desc A;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(10) | YES | | NULL | |
| b | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from A;
+------+------+
| a | b |
+------+------+
| 1 | b |
+------+------+
1 row in set (0.00 sec)

mysql> desc B;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(10) | YES | | NULL | |
| c | varchar(10) | YES | | NULL | |
| d | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from B;
+------+------+------+
| a | c | d |
+------+------+------+
| 1 | c | d |
| 1 | cc | dd |
+------+------+------+
2 rows in set (0.01 sec)

解决方案：使用右连接，具体SQL如下：
mysql> select * from A right join B on A.a=B.a where A.a='1';
+------+------+------+------+------+
| a | b | a | c | d |
+------+------+------+------+------+
| 1 | b | 1 | c | d |
| 1 | b | 1 | cc | dd |
+------+------+------+------+------+
2 rows in set (0.00 sec)

2、mysql> select * from employee;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 001 | lisi | 女 | 23 | 90 |
| 002 | wangwu | 男 | 23 | 98 |
| 003 | wangmazi | 女 | 20 | 56 |
| 004 | zhaosi | 男 | 26 | 88 |
+------+----------+------+------+-------+

 

聚合函数的应用
mysql> select sex,sum(score) as score from employee group by sex;
+------+-------+
| sex | score |
+------+-------+
| 女 | 146 |
| 男 | 186 |
+------+-------+
2 rows in set (0.01 sec)

根据分数显示不同的判断情况：
mysql> select (case when score>90 then "优秀" when score>80 then "良 好" else "其他" end) score,name from employee;
+---------+----------+
| score | name |
+---------+----------+
| 良 好 | lisi |
| 优秀 | wangwu |
| 其他 | wangmazi |
| 良 好 | zhaosi |
| 优秀 | lisi |
+---------+----------+
5 rows in set (0.01 sec)
3、重复字段
select * from employee where name in (select name from employee group by name having count(*)>1);

4、统计人数：

select sex,count(1) as 总人数 from employee group by sex;