MySQL安装、配置及基础命令

在生活中MySQL还是使用的非常多的,我的WordPress就是使用的MySQL的数据库,虽然经常使用加之在学校学习过一些别的数据库,对SQL语句有一定的了解,但是还真的没有系统的学习MySQL的知识!!最近打算抽空花点时间系统的学习一下MySQL相关的知识。

mysql

0x00 关于MySQL

什么是MySQL?虽然已经从2011年建立第一个WordPress的时候就接触了MySQL,不过自己还是无法用自己的预言描述下MySQL是什么,所以下面的直接从Wikipedia上拷贝而来了!

MySQL原本是一個開放源碼的關聯式資料庫管理系統,原開發者為瑞典的MySQL AB公司,该公司于2008年被Sun Microsystems收购。2009年,甲骨文公司(Oracle)收购Sun Microsystems,MySQL成为Oracle旗下产品。

MySQL在過去由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被廣泛地應用在Internet上的中小型網站中。随着MySQL的不断成熟,它也逐渐用于更多大规模网站和应用,比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收購後,Oracle大幅調漲MySQL商業版的售價,且甲骨文公司不再支持另一個自由軟體專案OpenSolaris的發展,因此導致自由軟體社群們對於Oracle是否還會持續支援MySQL社群版(MySQL之中唯一的免費版本)有所隱憂,MySQL的創始人麥克爾·維德紐斯以MySQL為基礎,成立分支計劃MariaDB。而原先一些使用MySQL的開源軟體逐漸轉向MariaDB或其它的資料庫。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库。

0x01 MySQL下载安装、配置及Windows服务配置

MySQL下载安装

我使用学习的MySQL版本为5.7.23 MySQL Community Server (GPL),虽然不是最新版本,但是官网已经有该版本的安装包(压缩包)下载。当然如果没有找到官网的下载地址,也可以通过我的OneDrive来进行下载。

MySQL安装包分为2中类型,一个是可执行文件,另一个是压缩包版本的。这里我推荐使用压缩包版本的,解压后简单配置下即可使用。

#MySQL最新版下载地址

https://dev.mysql.com/downloads/mysql/

#MySQL 5.7.23下载地址

https://dev.mysql.com/downloads/mysql/5.7.html#downloads

解压后可以看到MySQL的目录结构,其中没有data文件夹,那么自己手动创建一个即可。

mysql-directorydata目录就是以后的数据库的目录了!其中bin文件夹里有我们的可执行程序。

MySQL环境变量配置及连接

由于MySQL由服务端与客户端两个部分组成,为了方便启动我们服务端,那么首先还是先将其路径配置到环境变量中。

mysql-environment-argument配置完环境变量后就可以通过命令直接启动MySQL服务了。

#启动MySQL服务端
mysqld
#客户端连接MySQL服务端
mysql -u root -p

这样就可以完成MySQL的连接了,在默认情况下,MySQL会生成一个用户名为root,密码为空的账户!!

mysqld的Windows服务配置

为了方便启动与关闭mysql,那么可以将其注册为Windows的服务。

#安装mysqld的Windows服务

D:\Program Files\mysql-5.7.23-winx64\bin\mysqld –install

#移出mysqld的Windows服务

D:\Program Files\mysql-5.7.23-winx64\bin\mysqld –remove

之后就可以通过Windows的系统命令来管理MySQL服务了。

#启动MySQL服务

net start mysql

#停用MySQL服务

net stop mysql

mysql-service完成上面的操作就完成了学习MySQL的准备工作了,之后就可以开始系统的学习MySQL了!

0x02 MySQL的用户管理及授权

刚才说了,安装MySQL后,MySQL会提供一个没有密码的root账户,那么为了安全必须尽快更改root账户的密码,当然多人进行数据库管理的话,那么就有必要新增数据库管理用户了。所以开始接触数据库的用户管理知识。

管理用户

创建用户,那么需要指明新增用户的用户名,及其管理的数据库的地址,当然还有密码。

 #创建用户
create user 'xzymoe'@'192.168.%' identified by 'password';

其中%是通配,只要ip地址前面部分相同即可!

#删除用户
drop user 'xzymoe'@'192.168.%';
#更改用户名
rename user 'xzymoe'@'192.168.%' to 'xzy'@'192.168.%';
#更改密码
set password for 'xzy'@'192.168.%' = Password('test');

对用户进行增删改查后,都可以在mysql数据库中的user表中查询已有的账户。

mysql-user当然建立了用户是无法使用的了!因为还没有给用户规定他可以使用哪些数据库和表,所以一个用户可以使用就必须对其进行授权。

用户授权

因为只有授权了用户可以做什么?哪些数据库及表格可以操作,那么用户才能投入使用。接下来看看如何给用户授权。

#授权用户所有权限(除了grant)
grant all privileges on db1.* to 'xzymoe'@'192.168.%';
#查看用户权限
show grant for 'xzymoe'@'192.168.%';
#取消用户权限
revoke all privileges from 'xzymoe'@'192.168.%';

权限有比如查询、插入、更新或者删除等的权限。all privileges就是指所有的权限,但是除了grant操作外,因为grant仅仅是root用户才拥有的权限。

grant-to-user请忽略打错SQL语句的地方,哈哈!

如果只是赋予用户单一或者个别的权限,那么可以查询下表;

all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
...
usage 无访问权限
alter 使用alter table
alter routine 使用alter procedure和drop procedure
create 使用create table
create routine 使用create procedure
create temporary tables 使用create temporary tables
create user 使用create user、drop user、rename user和revoke all privileges
create view 使用create view
delete 使用delete
drop 使用drop table
execute 使用call和存储过程
file 使用select into outfile 和 load data infile
grant option 使用grant 和 revoke
index 使用index
insert 使用insert
lock tables 使用lock table
process 使用show full processlist
select 使用select
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
shutdown 使用mysqladmin shutdown(关闭MySQL)
super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client 服务器位置的访问
replication slave 由复制从属使用

0x03 数据库操作

#展示已有数据库
show databases;
#选择要操作的数据库
use DatabaseName;
#创建数据库
create database DatabaseName;
create database DatabaseName default charset=utf8;
#删除数据库
drop database DatabaseName;

默认情况下数据库中已经有3个数据库了:

  • mysql    #用户权限相关数据
  • test    #用于用户测试数据
  • information_schema     #MySQL本身架构相关数据

其次在创建数据库的时候,需要注意的是,如果数据库中需要用到中文的话,那么需要加上参数default charset=uft8,这样才可以让有中文的数据库不显示乱码。

0x04 数据表操作

基本操作

数据库中存放着数据表,信息都存在表单里。创建数据库后,默认情况下下里面是没有数据表的,因而需要新增表格才可以使用,那么得学习下数据表的操作。

#展示已有数据表
show tables;
#新增数据表
create table TableName(ColumnName Type null/not null,......)engine=innodb;
create table TableName(ColumnName Type null/not null,......)engine=innodb default charset=utf8;
#删除数据表
drop table TableName;

注意的是在创建一个表单的时候,需要写出表中列的名称及类型、是否为空及其他信息(下文介绍),最后需要选择器engine类型,一般默认innodb即可,最后还有和数据库一样的编码设置default charset=utf8。

数据类型

bit[(M)]
二进制位(101001),m表示二进制位的长度(1-64),默认m=1

tinyint[(m)] [unsigned] [zerofill]
小整数,数据类型用于保存一些范围的整数数值范围:
有符号:
-128 ~ 127.
无符号:
0 ~ 255
特别的: MySQL中无布尔值,使用tinyint(1)构造。

int[(m)][unsigned][zerofill]
整数,数据类型用于保存一些范围的整数数值范围:
有符号:
-2147483648 ~ 2147483647
无符号:
0 ~ 4294967295
特别的:整数类型中的m仅用于显示,对存储范围无限制。例如: int(5),当插入数据2时,select 时数据显示为: 00002

bigint[(m)][unsigned][zerofill]
大整数,数据类型用于保存一些范围的整数数值范围:
有符号:
-9223372036854775808 ~ 9223372036854775807
无符号:
0 ~ 18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。
特别的:对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
无符号:
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符号:
0
1.175494351E-38 to 3.402823466E+38
**** 数值越大,越不准确 ****

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。
无符号:
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号:
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大,越不准确 ****

char (m)
char数据类型用于表示固定长度的字符串,可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度,也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串,可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注:虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的50%。因此,用户在设计数据库时应当综合考虑各方面的因素,以求达到最佳的平衡

text
text数据类型用于保存变长的大字符串,可以组多到65535 (2**16 − 1)个字符。

mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

enum
枚举类型,
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例:
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

set
集合类型
A SET column can have a maximum of 64 distinct members.
示例:
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

DATE
YYYY-MM-DD(1000-01-01/9999-12-31)

TIME
HH:MM:SS('-838:59:59'/'838:59:59')

YEAR
YYYY(1901/2155)

DATETIME

YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y)

TIMESTAMP

YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)

数据类型很多,不过大致可以分为数字类型、字符类型、时间类型和较特殊的enum、set类型。

数字型

  • tinyint
  • int
  • bigint
  • float
  • double
  • decimal

如果需要较长位数及精准计算的时候,那么推荐使用decimal类型即可。

字符型

  • char
  • varchar
  • text

text用于存储较长的字符。

而char和varchar又需要注意其区别。比如char(10)和varchar(10),那么都是一个可以存放10个字符的数据类型,当存储字符’xzymoe’的时候,由于只有6个字符,那么char则会用类似空格的东西填充其余4个字符呈现’  xzymoe  ‘,而varchar则只存储6个字符的’xzymoe’。可以看出varchar类型存储的时候比较节省空间,而由于char是固定的长度,在搜索数据库的时候,则可以固定的跳过10个字符,去检索下一个字段,那么char类型的数据库好处就是速度快。

从char与varchar的区别可以看出,为了最佳优化数据库的性能,那么将固定长度的数据放置于表的前面,而不固定长度的数据放置于表末端。

时间型

  • datetime

别的时间数据类型,要么就是没有年份,要么没有时间,总之缺斤少两的,所以基本只会用到datetime的数据类型。

其他参数

1.是否可以为空。

create table TableName(id int not null,name char(10))engine=innodb default charset=utf8;

可以通过设置not null及null来设置表格数据是否可以为空

2.默认值

create table TableName(id int not null,name char(10),gender char(2) default '男')engine=innodb default charset=utf8;

通过设置default参数来设置,当表格插入数据的时候,该列没有数据插入时候则填入默认数据。

3.主键与自增

主键是一个特殊的唯一索引,不允许为空,通过设置primary key来设置,由于主键不能为空,所以设置了primary key后,可以不用写not null;如果主键使用的是单列,则其对应的值必须是唯一的,如果是多列,则其中组合必须唯一。

自增列通过auto_increment设置,在插入数据时候无需手动插入数据,其默认会自增。对于自增列,其必须是所以(含主键),可以设置起始值和步长。

create table TableName(id int auto_increment primary key,name char(10))engine=innodb default charset=utf8;

4.外键

当比如创建一张单位人员信息表,里面有一个电脑配置一栏,比如有‘i7-xxx gtx10xx…’总之很长的一串,其实单位也就那么5~6种电脑。而当员工有1w多个的情况下,那么如果每个人后面的电脑配置都是一大行的话,那么表格就要存储1w多行这种很长的数据,并且不方便搜索,那么可以创建另一个配置表,比如id=1的时候,电脑配置为‘i7-xxx gtx10xx…’,id=2的时候电脑配置为。。。而员工表中配置一栏仅仅填写1,2…来链接到配置表中。那么这种骚操作就叫外键。

create table userinfo(id int auto_increment primary key,name char(10),pc int,constraint fk_userinfo_pcinfo foreign key (pc) references pcinfo(id))engine=innodb default charset=utf8;

通过constraint 外键名 foreign key (从表中列) references 主表(列名)来完成两张表的连接。外键名一般起名为fk_表1名_表2名。

0x05 数据行基本操作

表格数据主要有增删改查几个部分。

有一张学生表

create table student(id int auto_increment primary key,name char(10),gender char(5))engine=innodb default charset=utf8;

1.增加内容

insert into student(name,gender) values('xzymoe','man');

2.删除

删除名字为’xzymoe’的数据行

delete from student where name='xzymoe';

3.改

将姓名为’xzymoe’的学生改为’xzy’

update student set name = 'xzy' where name = 'xzymoe';

4.查

select * from student; #查看student表
select * from student where gender = 'man'; #查看student表中的男学生

发表评论