一、MySQL安装(以mysql8.0.11压缩版为例)
1、软件下载
Windows 上安装 MySQL 相对来说会较为简单,最新版本可以在 MySQL官网上下载。
2、配置文件
下载完后,我们将 zip 包解压到相应的目录,这里我将解压后的文件夹放在C:\Program Files\mysql-8.0.11下,接下来我们需要配置下 MySQL 的配置文件。打开刚刚解压的文件夹 C:\Program Files\mysql-8.0.11下,在该文件夹下创建 my.ini 配置文件,编辑 my.ini 配置以下基本信息:
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\Program Files\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错
# datadir=C:\\Program Files\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
3、设置环境变量
我的电脑——属性——高级设置——环境变量
4、启动MySQL
以管理员身份打开 cmd 命令行工具,切换目录:
cd C:\program files\mysql-8.0.11\bin
初始化数据库:
mysqld --initialize --console
执行完成后,会输出 root 用户的初始默认密码,如:
...
2019-06-20T02:35:05.464644Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: APWCY5ws&hjQ
...
APWCY5ws&hjQ 就是初始密码,后续登录需要用到,你也可以在登陆后修改密码。
输入以下安装命令:
mysqld install
启动输入以下命令即可:
net start mysql
5、登录mysql
当 MySQL 服务已经运行时, 我们可以通过 MySQL 自带的客户端工具登录到 MySQL 数据库中, 首先打开命令提示符, 输入以下格式的命名:
mysql -h 主机名 -u 用户名 -p
参数说明:
-h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
-u : 登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
如果我们要登录本机的 MySQL 数据库,只需要输入以下命令即可:
mysql -u root -p
按回车确认, 如果安装正确且 MySQL 正在运行, 会得到以下响应:
Enter password:
若密码存在, 输入密码登录, 不存在则直接按回车登录。登录成功后你将会看到 Welcome to the MySQL monitor… 的提示语。
然后命令提示符会一直以 mysq> 加一个闪烁的光标等待命令的输入, 输入 exit 或 quit 退出登录。
二、mysql常用操作语句
MYSQL服务的启动、停止与卸载
启动:net start mysql
停止:net stop mysql
卸载:sc delete mysql
标识符:windows下不区分,linux区分
语句:SQL标准语句+MYSQL扩展语句
登录到MYSQL:mysql -h 主机名 -u 用户名 -p
创建数据库
create database 数据库名 [其他选项]
create database 数据库名 character set gbk;显示中文
创建数据库表:
create table 表名称(列名称)
例:
create table students (is int unsigned not null auto_increment primary key, name char(8) not null, sex char(4) not null,age tingint unsigned not null, tel char(13) null default “-”);
在登录前输入mysql -D数据库名 -u root -p<C:1bbb.sql;则将脚本文件导入MySQL中,或登录后
source C:/bbb.sql;
show tables 查看本数据库中有多少个表
desc 表名 表示描述该表的性质
操作数据库
向表中插入数据库
insert into 表名[(列名1,列名2,...)] values (值1,值2,...);
例:
insert into students values(NULL,’王刚’,’男’,20,’13811371377’)
insert into students (name,sex,age)values(‘孙俪’,’女’,21)
查询表中数据
select 列名称 from 表名称[查询条件]
例:
select name, age from students;
select * from students;
select 列名称 from 表名称 where 条件
例:
select * from students where age>21;#查询年龄在21岁以上的所有人信息
select * from students where name like “%王%”;#查询名字中带有“王”字信息的所以人信息
select * from students where id<5 and age>20;#查询id小于5且年龄大于20的所有人信息
更新表中数据
update 表名 set 列名=新值 where 更新条件
例:
update students set tel=default where id=5;
update students set age =age+1;
update students set name=’张伟’,age=19 where tel=’13288097888’;
删除表中的数据
delete from 表名 where 删除条件
例:
delete from 表名 where id=2;
delete from students where age<20;
delete from students;
创建表后的修改:
添加列:alter table 表名 add 列名 列数据类型[after 插入位置]
例:alter table students add address char(60);
修改列:alter table 表名 change 列名 新列名 新数据类型
例:alter table students change tel telphone char(13) default ‘-‘;
删除列:alter table 表名 drop 列名
重命名表:alter table 表名 rename 表名
删除整张表:drop table 表名
删除整个数据库:drop database sudu;
改root用户密码
退出后:mysql admin -u root -p password 新密码
Mysql语句执行顺序:
编写顺序:select-from-where-group by-having-order by
执行顺序:from-where-group by-having-select-order by
数据库
Desktop——Web Server——Database Server
几大数据库巨头:
1、 Microsoft SQL Server
2、 Oracle Database
3、 IBM DB2
4、 MySQL Database (open source)
5、 Monge DB Database
关系型数据库管理系统(Relational Database Management System)
关系型数据库的基本要素是二维表,这些二维表可以被独立使用或者通过使用Join语句连接起来使用。主键(primary key)和外键(foreign key)是用来连接二维表之前的重要工具。
主键是用来唯一标识一行数据,而且主键列必须包含唯一的值,并且不能包含空值(null)。
主键可以建立在每张二维表中单列或多列上。
一张二维表上的外键可以引用另一张二维表上对应的主键。
1.MySQL创建分区(Range类型)
create table text(
id INT,name char(8),totalnum char(20),hiredate datetime)
PARTITION by range(to_days(hiredate))(
partition p1 values less than (to_days('20151202')),
partition p2 values less than (to_days('20151203')),
partition p3 values less than (to_days('20151204')),
partition p4 values less than (to_days('20151205')),
partition p5 values less than (to_days('20151206')),
partition p6 values less than maxvalues
);#这行代码说明当输入最新日期时,根据最新日期进行分区
Mysql的分区主要有两种方式:
水平分区:对表的行进行分区,表的特性依然保持,水平分区一定要通过某个列进行分割,常见的有年份,日期等。
垂直分区:通过对表的垂直划分来减少目标表的宽度,使某些特定列被划分到特定分区,每个分区都包含了其中的列所对应的所有行。
分区的作用:数据库性能提升和简化数据管理
分区技术使得技术管理变得简单,删除某个分区不会对另外的分区造成影响。
2、分区类型
Range分区:基于属于一个给定连续区间的列值,把多行分配给分区。
List分区:类似于Range分区,区别在于List分区基于列值匹配一个离散值集合中的某个值来进行选择。
Hash分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含Mysql中有效的,产生非负整数值的任何表达式。
Key分区:类似于Hash分区,区别在于key分区只支持计算一列或多列,且Mysql服务器提供其自身的哈希函数,必须有一列或多列包含整数值。
复合分区:基于Range/List类型的分区表中每个分区的再次分割,子分区可以是Hash/key等类型。
3、创建分区(List类型)
#根据List类型进行分区,此处注意的是如果创建的表中id为主键,那么分区字段也必须为主键,否则报错,称为复合主键。
drop table if exists partition_list;
create table partition_list
(id int not null auto_increment comment '学生id号',
#auto_increment 递增,在插入数据时,可使用NULL填写,自动生成id号,
name char(32) comment '学生姓名',
department int comment '所属部门',
primary key(id,department)
)
partition by list(department)(
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3)
);
#插入数据
insert into partition_list(id,name,department) values (1,'王珂',1),(null,'李政道',2),...
#在表partition_list中增加一列salary
alter table partition_list add salary float after name;
replace into partition_list (id,name,salary,department)
values (1,'王珂',600,1),(2,'李政道',500,2),...
查询:select department,sum(salary) from partition_list group by department having sum(salary)>1500;
3.创建Hash分区
Hash分区主要用来确保数据在预先确定数目的分区中平均分布,在Range和List分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在Hash分区中,Mysql自动完成这些工作,你所要做的只是基于将要被哈希的列值或表达式,以及被指定分区的表将要被分割成的分区数量。
create table temp(
id int not null,
birthdate datetime not null,
salary int)
partition by hash(year(birthdate))
partitions 4;
如何查看Mysql中分区的情况;
select partition name, partition_expression, partition_description,
table_rows from information-schema.partitions where table_schema=schema() and table_name='表名';
三、局域网互联
第一步: 先查看本机ip,在本机操作。
第二步:查看客户机ip,在客户机操作。
第三步:本机ping客户机。
第四步:客户机ping本机。
第五步:如果两台机器彼此能ping通的话,在本机进行如下操作。
在本机创建look用户,注意指定look所在的ip。
远程用户就可以登入本机mysql了。
第六步:远程用户操作如下。发现可以登入,并可以操作简单的查询,但创建数据库权限不够。
第七步 :在主机上位远程用户look授予权限
第八步: 客机已look身份重新登入主机,就可以在客户机对本机的mysql进行和root一样权限的操作了。
附:python连接数据库
#pthon操作mysql数据库
import pymysql.cursors
import pandas as pd
#连接Mysql数据库
connection=pymysql.connect(host='192.168.8.106',port=3306,user='root',password='***',db='wangke',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
#通过cursor创建游标
cursor=connection.cursor()
#创建sql语句,并执行
sql="select * from students1"
cursor.execute(sql)
#查询数据库单条语句
result=cursor.fetchall()
print(result)
四、解决NAVICAT连接别人数据库连接不上的问题
配置新连接报错:错误号码 2058,分析是 mysql 密码加密方法变了
1.首先用select user,host,plugin from mysql.user;
进行查询,查询结果如下所示:
将图片中的plugin加密方式改为mysql_native_password这种方式
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
例如,为别人创建了一个look用户,这需对这个look用户进行以上的操作即可。
五、三种方法对数据进行一对多匹配
5.1 EXCEL:VLOOKUP函数
原始数据如下图所示:
我们的目标是找到client_id对应的ucs_source_id,但是我们发现client_id1那一列中,1、2、3对应着多个值,那么如果我们使用VLOOKUP函数,系统会选择哪个值呢?
下面我们使用VLOOKUP函数,参数的选择如下图所示:
然后我们发现函数为1匹配到的值是100,2对应的是102,3对应的是104,4对应的是105,6没有其对应的数字。这说明在EXCEL中VLOOKUP函数匹配的是lookup_value所对应的第一个值。
结果如下图所示:
5.2 PYTHON:MAP函数
用python根据字典匹配数据的过程可以归纳为:
1、读取数据;
2、创建字典,本文用了两种方式创建字典,一个是用dict函数,一个是自己手动按照Excel中的数据创建字典;
3、用map函数匹配数据。
代码如下所示:
import pandas as pd
import numpy as np
data = pd.read_excel('./aaa.xlsx')
user_id = data['client_id'][0:5]
user_id = pd.DataFrame(user_id)
dict1 = data['client_id1']
dict2 = data['ucs_source_id']
id_dict = dict(zip(dict1,dict2))
######手动创建字典########
dictt = {1:'100',1:'101',2:'102',2:'103',3:'104',4:'105',5:'106',1:'107',2:'108',3:'109'}
user_id['ucs_source_id'] = user_id['client_id'].map(dictt)
其中user_id相当于VLOOKUP函数中的lookup_value,dict1为client_id1那一列,dict2为ucs_source_id那一列。首先可以用dict函数将dict1、dict2合并称为字典并命名为id_dict,我们发现在一对多情况下,系统默认最大值为value,id_dict变量如下图所示:
然后我们自己根据Excel表格中的值自己手动创建字典dictt,然后使用map函数匹配对应的值,运行结果如图所示:
我们发现map函数所匹配的值为各个数字所对应的的最大值。
5.3 使用SQL语句进行匹配
在这一部分,我们主要使用的是row_number() over() 以及partition by 函数。SQL代码如下所示:
SELECT
*
FROM
(
SELECT
*,
row_number ( ) over ( PARTITION BY client_id ORDER BY ucs_source_id desc) rn
FROM
( SELECT A.client_id, B.client_id1, B.ucs_source_id FROM aaa A LEFT JOIN bbb B ON client_id = client_id1 ) C
) D
WHERE
rn = 1;
其中表aaa中存放的是client_id那一列,表bbb中存放的是client_id1以及ucs_source_id那一列。语句中rn=1也可改为rn=2或3。如果为升序排列,rn=1对应着匹配最小值。SQL语句运行结果如下图所示: