数据挖掘——MySQL基础

最新推荐文章于 2023-09-19 22:41:41 发布

Tao_RY

最新推荐文章于 2023-09-19 22:41:41 发布

阅读量600

点赞数

分类专栏：数据分析专栏文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43008804/article/details/112479550

版权

数据分析专栏专栏收录该内容

16 篇文章 7 订阅

订阅专栏

一、MySQL入门

1、进入mysql

 mysql -uroot -p

2、显示数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+

3、选择数据库

mysql> use test_db;
Database changed

4、查看可用表

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
| tb_emp2           |
+-------------------+

5、查看表的列

mysql> show columns from tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name_  | varchar(25) | YES  |     | NULL    |       |
| deptid | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

6、导入、导出文件

导入

mysql> load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\wine_data.csv'
    -> into table tb_emp2
    -> fields terminated by ','
    -> lines terminated by '\r\n'
    -> ignore 1 lines;

导出

mysql> select id,name_ into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/1.csv'
    -> fields terminated by ','
    -> lines terminated by '\r\n'
    -> from tb_emp1

7、创建删除表

mysql> create table tb_emp2
    -> (
    -> id char(25),
    -> number float,
    -> salary int(25)
    -> );
mysql> drop table tb_emp2;

8、重命名表

mysql> rename table tb_emp2 to tb_emp3;

9、插入数据

mysql> insert into tb_emp2(id,name)
    -> values(6,7);
mysql> select * from tb_emp2;
+------+------+
| id   | name |
+------+------+
|    2 | bcd  |
|    3 | cde  |
|    4 | efg  |
|    5 | bob  |
|    6 | 7    |
+------+------+

二、检索数据

1、检索列

mysql> select id,name_ from tb_emp1;
+------+-------+
| id   | name_ |
+------+-------+
|    1 | abc   |
|    2 | bcd   |
|    3 | cde   |
|    4 | efg   |
|    5 | bob   |
|    6 | opp   |
|    7 | ckk   |
|    8 | llo   |
|    9 | kkp   |
|   10 | kkp   |
+------+-------+

select * from tb_emp1;检索所有列，其中*是通配符

2、检索不同的行

mysql> select distinct name_ from tb_emp1;
+-------+
| name_ |
+-------+
| abc   |
| bcd   |
| cde   |
| efg   |
| bob   |
| opp   |
| ckk   |
| llo   |
| kkp   |
+-------+

3、限制结果

mysql> select distinct name_ from tb_emp1
    -> limit 5;
+-------+
| name_ |
+-------+
| abc   |
| bcd   |
| cde   |
| efg   |
| bob   |
+-------+
limit 5,5表示从行5开始的5行

【注】我们可以使用完全限定的表名，这是一个很好的习惯

mysql> select distinct tb_emp1.name_ from tb_emp1 limit 3;
+-------+
| name_ |
+-------+
| abc   |
| bcd   |
| cde   |
+-------+

4、排序数据

mysql> select id,name_,salary from tb_emp1
    -> order by salary;
+------+-------+--------+
| id   | name_ | salary |
+------+-------+--------+
|   10 | kkp   |   2964 |
|    9 | kkp   |   2969 |
|    8 | llo   |   2974 |
|    7 | ckk   |   2979 |
|    6 | opp   |   2984 |
|    5 | bob   |   2989 |
|    4 | efg   |   2994 |
|    3 | cde   |   2999 |
|    2 | bcd   |   3004 |
|    1 | abc   |   3009 |
+------+-------+--------+

也可以按照多个列排序，有先后顺序；desc关键词可以指定降序排列。

三、过滤数据

1、简单筛选

mysql> select id,name_ from tb_emp1
    -> where id=1 or id between 5 and 7;
+------+-------+
| id   | name_ |
+------+-------+
|    1 | abc   |
|    5 | bob   |
|    6 | opp   |
|    7 | ckk   |
+------+-------+

and表示且，or表示或

mysql> select id,name_ from tb_emp1
    -> where (id between 7 and 10 and name_='kkp') or id=1;
+------+-------+
| id   | name_ |
+------+-------+
|    1 | abc   |
|    9 | kkp   |
|   10 | kkp   |
+------+-------+

2、条件范围过滤

mysql> select id,name_,salary from tb_emp1
    -> where id in (4,6)
    -> order by salary;
+------+-------+--------+
| id   | name_ | salary |
+------+-------+--------+
|    6 | opp   |   2984 |
|    4 | efg   |   2994 |
+------+-------+--------+

可以in前面加not表示相反条件过滤

3、通配符

%匹配任意字符出现任意次数
_匹配单个任意字符

mysql> select id,name_,salary from tb_emp1
    -> where name_ like '%p'
    -> order by salary;
+------+-------+--------+
| id   | name_ | salary |
+------+-------+--------+
|   10 | kkp   |   2964 |
|    9 | kkp   |   2969 |
|    6 | opp   |   2984 |
+------+-------+--------+

4、正则表达式

.可以匹配任意一个字符

mysql> select id,name_,salary from tb_emp1
    -> where salary regexp '2...';
+------+-------+--------+
| id   | name_ | salary |
+------+-------+--------+
|    3 | cde   |   2999 |
|    4 | efg   |   2994 |
|    5 | bob   |   2989 |
|    6 | opp   |   2984 |
|    7 | ckk   |   2979 |
|    8 | llo   |   2974 |
|    9 | kkp   |   2969 |
|   10 | kkp   |   2964 |
+------+-------+--------+

|、[]、可以进行or匹配
还有匹配字符类，多用用就熟悉了

四、字段拼接和计算

1、拼接

mysql> select concat(id,'\'','name_','\'') as name from tb_emp1;
+-----------+
| name      |
+-----------+
| 1'name_'  |
| 2'name_'  |
| 3'name_'  |
| 4'name_'  |
| 5'name_'  |
| 6'name_'  |
| 7'name_'  |
| 8'name_'  |
| 9'name_'  |
| 10'name_' |
+-----------+

2、计算

mysql> select id,name_,salary,id*salary as i_salary from tb_emp1
    -> order by i_salary desc;
+------+-------+--------+----------+
| id   | name_ | salary | i_salary |
+------+-------+--------+----------+
|   10 | kkp   |   2964 |    29640 |
|    9 | kkp   |   2969 |    26721 |
|    8 | llo   |   2974 |    23792 |
|    7 | ckk   |   2979 |    20853 |
|    6 | opp   |   2984 |    17904 |
|    5 | bob   |   2989 |    14945 |
|    4 | efg   |   2994 |    11976 |
|    3 | cde   |   2999 |     8997 |
|    2 | bcd   |   3004 |     6008 |
|    1 | abc   |   3009 |     3009 |
+------+-------+--------+----------+

五、数据处理函数

1、文本处理函数

删除空白（rtrim删除左边空白，ltrim删除右边空白，trim删除左右空白）

mysql> select rtrim(name_) as name_ from tb_emp1;
+-------+
| name_ |
+-------+
| abc   |
| bcd   |
| cde   |
| efg   |
| bob   |
| opp   |
| ckk   |
| llo   |
| kkp   |
| kkp   |
+-------+

2、日期处理函数

mysql>select id,name_ from tb_emp1
	->from tb_emp1
	->where date(order_date) between '2015-09-01' and '2016-09-01';

3、数值处理函数

abs()、mod()等等

mysql> select mod(salary,2) as mod_ from tb_emp1;
+------+
| mod_ |
+------+
|    1 |
|    0 |
|    1 |
|    0 |
|    1 |
|    0 |
|    1 |
|    0 |
|    1 |
|    0 |
+------+

4、聚集函数

avg、max、min、sum、count函数

mysql> select avg(distinct salary) as a_salary from tb_emp1;
+----------+
| a_salary |
+----------+
|   2986.5 |
+----------+

mysql> select count(id) as count_ from tb_emp1;
+--------+
| count_ |
+--------+
|     10 |
+--------+

5、分组函数

频率统计

mysql> select name_,count(*) as num_prods from tb_emp1 group by name_;
+-------+-----------+
| name_ | num_prods |
+-------+-----------+
| abc   |         1 |
| bcd   |         1 |
| cde   |         1 |
| efg   |         1 |
| bob   |         1 |
| opp   |         1 |
| ckk   |         1 |
| llo   |         1 |
| kkp   |         2 |
+-------+-----------+

分组过滤having

mysql> select id,name_,count(*) as num_prods
    -> from tb_emp1
    -> where salary<=3000
    -> group by name_
    -> having count(*)>=1;
+------+-------+-----------+
| id   | name_ | num_prods |
+------+-------+-----------+
|    3 | cde   |         1 |
|    4 | efg   |         1 |
|    5 | bob   |         1 |
|    6 | opp   |         1 |
|    7 | ckk   |         1 |
|    8 | llo   |         1 |
|    9 | kkp   |         2 |
+------+-------+-----------+

六、子查询

1、表之间关联查询

mysql> select id,name_ from tb_emp1 where id in (select id from tb_emp2);
+------+-------+
| id   | name_ |
+------+-------+
|    2 | bcd   |
|    3 | cde   |
|    4 | efg   |
|    5 | bob   |
+------+-------+

2、作为字段使用子查询

mysql> select id,name_,(select count(*) from tb_emp2 where tb_emp1.id=tb_emp2.id) as num from tb_emp1;
+------+-------+------+
| id   | name_ | num  |
+------+-------+------+
|    1 | abc   |    0 |
|    2 | bcd   |    1 |
|    3 | cde   |    1 |
|    4 | efg   |    1 |
|    5 | bob   |    1 |
|    6 | opp   |    0 |
|    7 | ckk   |    0 |
|    8 | llo   |    0 |
|    9 | kkp   |    0 |
|   10 | kkp   |    0 |
+------+-------+------+

七、表联结

在这里插入图片描述

1、内联接

mysql> select tb_emp1.name_,tb_emp2.name from tb_emp1
    -> inner join tb_emp2
    -> on tb_emp1.id=tb_emp2.id;
+-------+------+
| name_ | name |
+-------+------+
| bcd   | bcd  |
| cde   | cde  |
| efg   | efg  |
| bob   | bob  |
+-------+------+

mysql> select tb_emp1.name_,tb_emp2.name from tb_emp1,tb_emp2
    -> where tb_emp1.id=tb_emp2.id;
+-------+------+
| name_ | name |
+-------+------+
| bcd   | bcd  |
| cde   | cde  |
| efg   | efg  |
| bob   | bob  |
+-------+------+

2、外联接

①左联接

mysql> select tb_emp1.name_,tb_emp2.name from tb_emp1
    -> left join tb_emp2
    -> on tb_emp1.id=tb_emp2.id;
+-------+------+
| name_ | name |
+-------+------+
| bcd   | bcd  |
| cde   | cde  |
| efg   | efg  |
| bob   | bob  |
| abc   | NULL |
| opp   | NULL |
| ckk   | NULL |
| llo   | NULL |
| kkp   | NULL |
| kkp   | NULL |
+-------+------+

②右联接

mysql> select tb_emp1.name_,tb_emp2.name from tb_emp1
    -> right join tb_emp2
    -> on tb_emp1.id=tb_emp2.id;
+-------+------+
| name_ | name |
+-------+------+
| bcd   | bcd  |
| cde   | cde  |
| efg   | efg  |
| bob   | bob  |
+-------+------+

3、全联接

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

mysql> select tb_emp1.name_,tb_emp2.name from tb_emp1 left join tb_emp2 on tb_emp1.id=tb_emp1.id
    -> union
    -> select tb_emp1.name_,tb_emp2.name from tb_emp1 right join tb_emp2 on tb_emp1.id=tb_emp1.id;

Tao_RY

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据挖掘——MySQL基础

MySQL基础一、MySQL入门1、进入mysql2、显示数据库3、选择数据库4、查看可用表5、查看表的列二、检索数据1、检索列2、检索不同的行3、限制结果三、排序数据四、过滤数据一、MySQL入门1、进入mysql mysql -uroot -p2、显示数据库mysql> show databases;+--------------------+| Database |+--------------------+| information_schema ||
复制链接

扫一扫