面试总结【MySQL】

本文主要是介绍面试总结【MySQL】，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

练习一【请写出删除和插入的SQL语句】

删除SQL语句

删除某个语句：delete from acd where name="jinhua";

删除整个表格信息（慎重）：delete from acd;

删除整个表格信息（百万级信息处理）truncate table acd;

//创建wugen数据库
mysql> create database wugen;
Query OK, 1 row affected (0.01 sec)

//查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| anruo              |
| app                |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| work               |
| wugen              |
+--------------------+
9 rows in set (0.00 sec)

//进入wugen数据库
mysql> use wugen;
Database changed

//创建acd表格
mysql> create table acd(
    -> name varchar(10),
    -> age int,
    -> sex varchar(5)
    -> );
Query OK, 0 rows affected (0.05 sec)

//查看cad表格
mysql> desc acd;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| sex   | varchar(5)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

//查看acd表格数据
mysql> select * from acd;
Empty set (0.00 sec)

//插入单条SQL语句
mysql> insert into acd(name,age,sex) values("zhangli",24,"boy");
Query OK, 1 row affected (0.00 sec)

//插入多条SQL语句
mysql> insert into acd values
    -> ("jinhua",7,"boy"),
    -> ("ruijie",8,"girl"),
    -> ("hui",10,"boy"),
    -> ("dan",25,"girl");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

//查看acd表格数据
mysql> select * from acd;
+---------+------+------+
| name    | age  | sex  |
+---------+------+------+
| zhangli |   24 | boy  |
| jinhua  |    7 | boy  |
| ruijie  |    8 | girl |
| hui     |   10 | boy  |
| dan     |   25 | girl |
+---------+------+------+
5 rows in set (0.00 sec)

//删除单条语句
mysql> delete from acd where name="jinhua";
Query OK, 1 row affected (0.00 sec)

//查看acd表格数据
mysql> select * from acd;
+---------+------+------+
| name    | age  | sex  |
+---------+------+------+
| zhangli |   24 | boy  |
| ruijie  |    8 | girl |
| hui     |   10 | boy  |
| dan     |   25 | girl |
+---------+------+------+
4 rows in set (0.00 sec)

//删除整个表的数据（truncate）
mysql> truncate table acd;
Query OK, 0 rows affected (0.02 sec)

//查看acd表格数据
mysql> select * from acd;
Empty set (0.00 sec)

//插入多条SQL语句
mysql> insert into acd values
    -> ("jinhua",7,"boy"),
    -> ("ruijie",8,"girl"),
    -> ("hui",10,"boy"),
    -> ("dan",25,"girl");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

//删除整个表的数据（detele）
mysql> delete from acd;
Query OK, 4 rows affected (0.00 sec)

//查看acd表格数据
mysql> select * from acd;
Empty set (0.00 sec)

练习二【having和where的区别】

1、"Where" 是一个约束声明，使用Where来约束数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

2、"Having"是一个过滤声明，是在查询返回结果集后对查询结果进行的过滤操作，在Having中通常与聚合函数结合使用。

//创建person表格
mysql> create table person(
    -> id int,
    -> name varchar(10),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)

//查看person表格
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//插入多条语句
mysql> insert into person values
    -> (1,"zl",24),
    -> (2,"al",20),
    -> (3,"qw",15),
    -> (4,"zs",12),
    -> (5,"sk",9),
    -> (6,"tr",8);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

//查看person表格数据
mysql> select * from person;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zl   |   24 |
|    2 | al   |   20 |
|    3 | qw   |   15 |
|    4 | zs   |   12 |
|    5 | sk   |    9 |
|    6 | tr   |    8 |
+------+------+------+
6 rows in set (0.00 sec)

//对age进行约束，显示person表格中，age>=10的数据
mysql> select * from person where age >=10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zl   |   24 |
|    2 | al   |   20 |
|    3 | qw   |   15 |·
|    4 | zs   |   12 |
+------+------+------+
4 rows in set (0.00 sec)

//查询person表格中的所有age的数据，过滤相同的age数据，并显示对应的age数值的总数为1的部分
mysql> select age,count(age) from person group by age having count(*)=1;
+------+------------+
| age  | count(age) |
+------+------------+
|    8 |          1 |
|    9 |          1 |
|   12 |          1 |
|   15 |          1 |
|   20 |          1 |
|   24 |          1 |
+------+------------+
6 rows in set (0.00 sec)

练习三【连接A、B两个表格，且显示a为1的所有B表格数据】

SQL语句

两表格右连接且约束a为1：select * from A right join  B on A.a=B.a where A.a='1';

mysql> use anruo;
Database changed

mysql> create table A(
    -> a varchar(10),
    -> b varchar(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

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> create table B(
    -> a varchar(10),
    -> b varchar(10),
    -> c varchar(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into A(a,b) values("1","b");
Query OK, 1 row affected (0.00 sec)

mysql> insert into B values
    -> ("1","c","d"),
    -> ("1","cc","dd")，
    -> ("2","ccc","ddd");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> select * from B;
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 1    | c    | d    |
| 1    | cc   | dd   |
| 2    | ccc  | ddd  |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from A right join  B on A.a=B.a;
+------+------+------+------+------+
| a    | b    | a    | b    | c    |
+------+------+------+------+------+
| 1    | b    | 1    | c    | d    |
| 1    | b    | 1    | cc   | dd   |
| 2    | b    | 2    | ccc  | ddd  |
+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from A right join  B on A.a=B.a where A.a='1';
+------+------+------+------+------+
| a    | b    | a    | b    | c    |
+------+------+------+------+------+
| 1    | b    | 1    | c    | d    |
| 1    | b    | 1    | cc   | dd   |
+------+------+------+------+------+
2 rows in set (0.00 sec)

练习四

问题一、聚合函数的应用

问题二、对每个人的数据进行分级

//创建employee表格
mysql> create table employee(
    -> id int,
    -> name varchar(10),
    -> sex varchar(10),
    -> age int,
    -> score int
    -> );
Query OK, 0 rows affected (0.03 sec)


//查看employee表格结构
mysql> desc employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| score | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


//插入多条字段
mysql> insert into employee values
    -> (001,"zl","boy",24,90),
    -> (002,"ll","boy",23,85),
    -> (003,"lk","girl",23,95),
    -> (004,"zx","gilr",20,80);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0


//查看employee表格字段
mysql> select * from employee;
+------+------+------+------+-------+
| id   | name | sex  | age  | score |
+------+------+------+------+-------+
|    1 | zl   | boy  |   24 |    90 |
|    2 | ll   | boy  |   23 |    85 |
|    3 | lk   | girl |   23 |    95 |
|    4 | zx   | girl |   20 |    80 |
+------+------+------+------+-------+
4 rows in set (0.00 sec)


//——————————————————聚合函数的应用——————————————————
//按性别，查询boy与gilr各自的score之和
mysql> select sex,sum(score) from employee group by sex;
+------+------------+
| sex  | sum(score) |
+------+------------+
| boy  |        175 |
| girl |        175 |
+------+------------+
2 rows in set (0.00 sec)


//按性别，查询boy与gilr各自的score最小值
mysql> select sex,min(score) from employee group by sex;
+------+------------+
| sex  | min(score) |
+------+------------+
| boy  |         85 |
| girl |         80 |
+------+------------+
2 rows in set (0.00 sec)


//————————————————————等级划分————————————————————
//按性别，查询boy与gilr各自的score最大值
mysql> select sex,max(score) from employee group by sex;
+------+------------+
| sex  | max(score) |
+------+------------+
| boy  |         90 |
| girl |         95 |
+------+------------+
2 rows in set (0.00 sec)


//按性别，查询boy与gilr各自的score平均值
mysql> select sex,avg(score) from employee group by sex;
+------+------------+
| sex  | avg(score) |
+------+------------+
| boy  |    87.5000 |
| girl |    87.5000 |
+------+------------+
2 rows in set (0.00 sec)


//对每个人的score进行评级，并显示每个人的名字
mysql> select
    -> (
    -> case
    -> when score>=90 then "1"
    -> when score>=85 then "2"
    -> else "3"
    -> end
    -> )
    -> score,name
    -> from employee;
+-------+------+
| score | name |
+-------+------+
| 1     | zl   |
| 2     | ll   |
| 1     | lk   |
| 3     | zx   |
+-------+------+
4 rows in set (0.00 sec)


//对每个人的score进行评级，并显示每个人的名字与分数
mysql> select
    -> (
    -> case
    -> when score>=90 then "1"
    -> when score>=85 then "2"
    -> else "3"
    -> end
    -> )
    -> appraise,name,score
    -> from employee;
+----------+------+-------+
| appraise | name | score |
+----------+------+-------+
| 1        | zl   |    90 |
| 2        | ll   |    85 |
| 1        | lk   |    95 |
| 3        | zx   |    80 |
+----------+------+-------+
4 rows in set (0.00 sec)


//查询相同name
mysql> select * from employee where name in (select name from employee group by name having count(*)>1);
Empty set (0.01 sec)


//查询男女各总人数
mysql> select sex,count(1) as count from employee group by sex;
+------+-------+
| sex  | count |
+------+-------+
| boy  |     2 |
| girl |     2 |
+------+-------+
2 rows in set (0.00 sec)


//查询总数
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

情景一：等级划分

mysql> select
    -> (
    -> case when salary>=2000 then "大佬"
    -> when salary >1500 then "大哥"
    -> when salary >1000 then "小弟"
    -> else "其他" end
    -> )
    -> company,salary from work;
+---------+--------+
| company | salary |
+---------+--------+
| 其他    |   1000 |
| 小弟    |   1500 |
| 大佬    |   2600 |
| 其他    |    700 |
| 大哥    |   1700 |
+---------+--------+
5 rows in set (0.00 sec)

情景二：查询薪资相同且大于1的数据

分段解释

select * from 表格名称 where 条件字段 in（）； #子查询

select 条件字段 from 表格名称 group by 条件字段 having count() >数字； #通过having来过滤group by字句的结果信息

mysql> select * from work where salary in (select salary from work group by salary having count(*)>1);
+--------+------+---------+--------+
| workid | code | company | salary |
+--------+------+---------+--------+
|      1 | 1001 | alibaba |   1000 |
|      5 | 1005 | d       |   1700 |
|      6 | 1006 | zxc     |   1700 |
|      7 | 1007 | asd     |   1000 |
+--------+------+---------+--------+
4 rows in set (0.01 sec)

情景三：通过内连接输出指定字符

命令

select 表格名称.字段 from 表格名称 inner join 关联表格名称 on 表格名称.字段=关联表格名称.关联字段;

mysql> select workinfo.type,workinfo.issafe,schoolinfo.xueli,schoolinfo.schooltype
    -> from person inner join school
    -> on person.code=school.code
    ->  inner join work
    -> on school.code=work.code
    -> inner join workinfo
    -> on work.workid=workinfo.workid
    -> inner join schoolinfo
    -> on school.schoolid=schoolinfo.schoolid
    -> ;
+-----------+--------+-------+------------+
| type      | issafe | xueli | schooltype |
+-----------+--------+-------+------------+
| Full-time | safe   | BS    | 985        |
+-----------+--------+-------+------------+
1 row in set (0.00 sec)

这篇关于面试总结【MySQL】的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！