面试总结【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)练习二
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)分段解释
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)
