MySQL

字数 4584阅读 52

下载安装好 MySQL 以后就会出现在系统偏好设置里,

MySQL 在 Mac 上的安装位置

双击进入设置界面,此时 MySQL 还未启动


MySQL启动前的样式

点击 Start MySQL Server 按钮,启动 MySQL


MySQL启动后的样式

配置 MySQL 环境变量

这个时候还不能使用,需要在配置文件中进行一步配置。
如果你是使用 bash,那么使用如下命令打开 bash_profile 文件:
vim ~/.bash_profile
如果你是使用 zsh 那么使用如下命令打开zsh 配置文件:
vim ~/.zshrc
然后在配置文件中加入一行:
PATH=$PATH:/usr/local/mysql/bin
退出配置文件,重启终端,输入:
mysql -u root -p
就可以登录 MySQL。如果你在安装MySQL的时候设置了密码,那么此时需要输入你设置的密码。
登录成功:

MySQL登录成功

这句命令中 u 的意思是用户名(user),root就是用户名的值;p 的意思是密码(password),这两步可以写成一步:
mysql -uroot -p123456
123456就是密码(password)。

在开始搭建MySQL数据库的时候我们有时候会设置一个超级复杂的密码,好像保存了绝密信息一样,实际使用的时候才发现每次输入那么长的密码真的很麻烦,再说了练习用的数据库真的没啥价值,这时候需要修改一个简单的密码,修改方法:

格式mysqladmin -u用户名 -p旧密码 password 新密码

用法举例,我想把我的原密码asdfghjl 修改为123456:
mysqladmin -uroot -p asdfghjl password 123456

这里不介绍重置密码的方法,我认为密码都是自己设置的,不应该被忘记。



登录 MySQL 以后可以查看当前登录的用户:
SELECT USER();

查看当前用户

退出 MySQL 有以下三种命令行:
\q;
exit;
quit;

验证 MySQL 安装

你还可以通过下面的命令验证数据库是否安装成功:
mysqladmin --version
如果以上命令输出了内容,比如下面的内容,该结果基于你的系统信息。那么说明 MySQL 安装正常,如果什么内容都未输出,那说明未安装成功。
mysqladmin Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL)

SQL 语言


SQL 是一门 ANSI 的标准计算机语言,用来访问和操作数据库系统。SQL 语言一般使用大写,小写也可以,大写更规范。

MySQL 语句的规范:

  • 关键字与函数名称全部大写
  • 数据库名称、表名称、字段名称全部小写
  • SQL 语句必须以分号“;”结尾
DML 和 DDL

可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。

DDL 语言:

  • CREATE DATABASE - 创建一个新的数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建表
  • ALTER TABLE- 变更(改变)数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX - 创建索引(搜索键)
  • DROP INDEX - 删除索引

DML 语言:

  • SELECT - 从数据库表中获取数据
  • UPDATE - 更新数据库表中的数据
  • DELETE - 从数据库表中删除数据
  • INSERT INTO - 向数据库表中插入数据

数据库表

一个数据库通常包含一个或多个表。每个表由一个名字标识(例如“客户”或者“订单”)。表包含带有数据的记录(行)。

下面的例子是一个名为 "Persons" 的表:

id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

上面的表包含三条记录(每一条对应一个人)和五个列(Id、姓、名、地址和城市)。


使用 MySQL

1、CREATE DATABASE 语句
语法
CREATE DATABASE database_name;

用法举例,创建一个名字为my_db数据库
CREATE DATABASE my_db;

我们不能创建两个同名的数据库,否则会报错:

创建同名数据库警告

所以有时候我们在创建的时候加一个判断语句:
CREATE DATABASE IF NOT EXISTS database_name;

带有判断语句的数据库创建

可以看到此时没有报错,但是有一个警告(warning),那么我们可以查看 warning 信息:

查看警告信息

可以创建数据库,那么就可以删除数据库:
DROP DATABASE IF EXISTS database_name
判断条件 IF EXISTS 可以不加。

用以下命令使用某一个数据库
USE my_db;

可以用以下地命令来查看创建的数据库是否成功:
SHOW DATABASES;

显示数据库名称

注意:一共列出了5个数据库,其中4个是 MySQL 自带的数据库,只有 my_db是我们创建的。

可以使用以下命令查看当前数据库状态:
STATUS;

当前数据库状态

可以使用以下命令查看当前使用的数据库名称:
SELECT DATABASE();
如果是如下图所示结果,则说明当前未使用数据库

查看当前数据库

注意:如果在输入命令的时候忘记输入分号“;”,直接敲回车了,此时终端没有任何结果,这时候不需要重新输入命令,直接补上分号再次回车即可。如下图所示

补全分号;

2、CREATE TABLE 语句
语法

CREATE TABLE 表名称(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
);

数据类型有如下表所列的类型:

数据类型 描述
integer(size)
int(size)
smallint(size)
tinyint(size)
仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)
numeric(size,d)
容纳带有小数的数字。
"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。
char(size) 容纳固定长度的字符串(可容纳字母、数字以及特殊字符)。
在括号中规定字符串的长度。
varchar(size) 容纳可变长度的字符串(可容纳字母、数字以及特殊的字符)。
在括号中规定字符串的最大长度。
date(yyyymmdd) 容纳日期。

用法举例,创建一个名为“Person”的表,该表包含5个列,列名分别是:“id”、“LastName”、“FirstName”、“Address”以及“City”:

CREATE TABLE Persons(
Id_P int,  
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
创建数据库表

用下面的命令来查看该数据库中的表:
SHOW TABLES;

显示当前数据库已创建的 table

如果要查看指定数据库中的表:
SHOW TABLES FROM database_name;

此时的表格是空的,没有任何数据。我们可以通过命令行查看表的结构:
SHOW COLUMNS FROM table_name

查看表结构
ID_P LastName FirstName Address City

创建了表格以后就需要向表格中插入数据

3、INSERT INTO 语句
语法
INSERT INTO 表名称 VALUES(值1,值2,...)

这种没有指定列的输入必须保证数据的一一对应,比如我们输入值但是不输入 City对应的值:


不指定列的输入必须输入完整

也可以往指定列插入数据

INSERT INTO 表名称(列1,列2...) VALUES(值1,值2...)

用法举例:

比如我们在之前创建的表 Persons 中插入一组数据:
INSERT INTO Persons VALUES (1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

在指定列中插入数据:
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

向指定列中输入数据,可以输入不完整,未输入的值默认为 NULL:

不完整的输入

4、SELECT 语句

插入数据以后可以通过SELECT命令查看表中数据
语法
SELECT * FROM 表名称
用法举例:
比如查看上文插入的数据
SELECT * FROM Persons

查看表中数据

5、WHERE 语句

如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句。

语法
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

WHERE 中可用的运算符:

运算符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式

比如我们查找居住地址在北京的人员信息
SELECT * FROM Persons WHERE City='Beijing'

有条件的选取信息

比如我们查找 FirstName 中以 B 开头的人员信息
SELECT * FROM Persons WHERE FirstName LIKE 'B%';

LIKE 用法举例

6、UPDATE 语句

Update 语句用于修改表中的数据。

语法
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 旧值
现在的表内容如下

76C9B388-06DE-4D2E-9CA3-9AB069D09C3A.png

我们需要把 ZhangSan 的地址改为广州
UPDATE Persons SET Address = '广州' WHERE Address = 'GuGong';
UPDATE

7、DELETE 语句
DELETE 语句用于删除表中的行。

语法
DELETE FROM 表名称 WHERE 列名称 = 值

比如我们删除表中第二行

DELETE FROM Persons WHERE LastName = 'Wilson'

删除某一行

8、空值与非空

  • NULL 字段值可以为空(默认字段可以为空)
  • NOT NULL 字段值禁止为空

比如我们创建一个表,其中一个字段 NOT NULL:


字段 NOT NULL
ABBE18BF-D7EA-4539-97E8-6B5FF4ED16A3.png

现在我们插入数据,username 为空,那么就会直接报错:


插入数据非空字段为空



9、主键(PRIMARY KEY)

  • 主键约束
  • 每张数据表只能有一个主键
  • 主键保证记录的唯一性
  • 主键自动为 NOT NULL



10、AUTO_INCREMENT

  • 自动编号,且必须与主键一起使用主键可以不和AUTO_INCREMENT一起使用
  • 默认情况下起始值为1,默认每次增量为1

AUTO_INCREMENT 必须要和主键(PRIMARY KEY) 一起使用,否则创建表的时候报错:


0F6FCE8B-0125-4D8A-8340-12821B47572C.png

AUTO_INCREMENT和PRIMARY KEY 一起使用:


AUTO_INCREMENT 必须要和主键(PRIMARY KEY) 一起使用

查看我们创建的表:

查看表

向表中插入值,此时 id 字段就可以不需要赋值,会自动从1开始:

B537AE1E-5514-4DEA-94D1-9A6144F039F2.png

我们向表中插入了4行数据,此时我们查看 id 字段是否从1开始自动递增:

id 字段自动递增

我们可以去掉AUTO_INCREMENT,只使用主键,此时主键所修饰的字段必须赋值(主键自动为 NOT NULL)且赋值不能相同(主键具有唯一性)。


11、唯一约束(UNIQUE KEY)

AUTO_INCREMENT必须要和主键一起使用,而一张表内只能有一个主键,如果还有其他字段需要保证其唯一性,该怎么办呢?此时我们可以使用 UNIQUE KEY 修饰该字段。

  • 唯一约束可以保证记录的唯一性
  • 唯一约束的字段可以为空
  • 每张数据表可以存在多个唯一约束字段


12、默认约束(DEFAULT)
当插入值时,如果没有为字段赋值,那么该字段有默认值。

现在我们创建一个表:

CREATE TABLE student(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
sex ENUM('boy','girl','secret') DEFAULT 'secret'
);

sex 取值有3个,boy,girl,如果不填写,默认为 secret。

现在我们向表中写入两条记录:

写入完整记录

省略 DEFAULT 字段:

省略 DEFAULT 字段

查看填入表中的记录:

省略的字段使用了默认值 secret

&#8195;
&#8195;

问题记录


问题一:我在修改一次密码以后就再也无法登录数据库,报错内容如下:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

如何解决这个问题呢?网上给出了很多答案,结果没一个适用于我的。

其中一篇文章讲的很详细,可能会适用于很多遇到这个问题的朋友。
解法大多是一个,就是更改 MySQL 的.cnf配置文件。可是我的电脑就是找不到这个配置文件。这篇文章 给出了可能的原因。

该怎么办呢?没有这个文件那么我们就创建这么一个文件。
在哪创建呢?首先我们需要知道 MySQL 读取这个文件的路径顺序,在该路径下创建该文件,以保证 MySQL 能读取到。

1、先使用以下命令找到 MysQL 的位置
which mysql
结果如图:

找到 MySQL 的路径.png

2、然后再查看 mysql 读取文件的路径:

/usr/local/mysql/bin/mysql --verbose --help | grep -A 1 'Default options'

结果如图所示:


mysql 读取配置文件的顺序

我们可以看到首先读取配置文件my.cnf的路径一共有3个,分别是:/etc/my.cnf/etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf ~/.my.cnf.首先会读取路径一的配置文件,如果读取不到则会读取路径二下的配置文件my.cnf;最后才会读取路径三下的配置文件。如果三个路径均读取不到,则会读取失败,导致数据库某些功能无法使用或者报错。

3、那么咱们就在这个路径下创建一个 my.cnf 文件,使用如下两行命令:

cd /etc

如图所示:

跳转到/etc 目录

创建 my.cnf 文件:

vim my.cnf

如图:


创建文件
在.cnf 文件里编辑以上内容

这个文件第一次创建的时候是空的,填写以上内容,保存即可。

慢着,好像无法保存,提示该文件是只读的,很奇怪,自己创建的文件还不能写入,我也不知道什么原因。不外乎就是没有权限,那么就给他增加权限。好吧,先退出(:q!)这个文件吧。重新创建一个可读写的文件。

所以在创建.cnf 文件的时候提高权限,使用以下命令:
sudo vim my.cnf

然后就可以编辑文件了,同样输入上面的内容,然后保存退出(:wq)。

这个时候登录数据库发现不需要密码了,是不是很没有安全感啊。那就设置一个密码吧。设置好之后一定要重启数据库服务(MySQL Server)哦。


问题二:MySQL 数据库无法Start

解决办法如下:

cd /usr/local/mysql
sudo chown -R _mysql data/

上面的步骤是根据实际 mysql 路径来的,如果没有修改 mysql 路径,默认就是在/usr/local

上面两步结束之后咱们重启数据库:
sudo /usr/local/mysql/support-files/mysql.server start

restart mysql

问题三:mysql 数据库无法 stop

写到这里我真想大喊一声 caonima。什么鬼,我只是想学习一下 MySQL,为什么会出现这么多问题,让我知难而退吗?

To stop the auto start I used:
sudo launchctl unload -w /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

And to kill the service I used:
sudo pkill mysql

正常退出 MySQL 可以直接使用这一步命令就行。

Start MySQL

sudo /usr/local/mysql/support-files/mysql.server start

Stop MySQL

sudo /usr/local/mysql/support-files/mysql.server stop

Restart MySQL

sudo /usr/local/mysql/support-files/mysql.server restart


问题四、MySQL数据库的端口是0,怎么改为3306

这真的是一个蛋疼的问题。通过终端连接数据库没有任何问题,但是通过 Python 脚本连接就是不行,报错如下:
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)")
意思就是连接被拒绝(Connection refused)。网上查到三种解决办法:
1、将 localhost 改为127.0.0.1
2、关掉防火墙
3、确定端口号3306是否被占用,如果是那就将 mysql 的端口号改为其他的,并在代码中作为参数传入。
好,已经验证1和2都不能解决我这个问题,着手尝试方法3.
首先查看我的 MySQL 使用的哪一个端口,查看方法:
show global variables like 'port';
结果如下:

mysql Port is 0.png

这是什么鬼,port 竟然是0。可是怎么将端口号改为3306呢?

对于这个问题我在网上找到了几个方法,现一一记录如下:

1、修改com.oracle.oss.mysql.mysqld.plist文件


这个文件的路径是:/Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
在这个文件下添加一个参数--port=3306,如下图:

8F0D90AD-00DF-4032-8325-E371184D38B5.png

2、修改 MySQL 配置文件 my.cnf


在 MySQL 配置文件添加一行配置,如下图:


FED7E5E6-BF94-44DD-B220-033995377665.png

3、修改 skip-networking 变量值


造成 port=0的可能性还有一个就是因为 skip-networking = ON
同样是在配置文件 my.cnf 中将该变量值改为 off,如上图。
查看当前数据库的 skip-networking 的属性值使用如下命令:
show global variables like 'skip_networking';
如下图:

D2A611CE-B900-46FD-ACD5-71F506FD31A1.png

以上三种方法修改之后都需要restart MySQL Server。

很遗憾,这三种方法都未能解决我的问题。
为了解决这个问题真的是太折腾了,最终还是在官网上找到了答案。上面有提到因为修改密码以后无法登录数据库,在配置文件里加了一行skip-grant-tables。这行是解决了无法登录的问题,但同时带来了更多其他问题:
1、无需密码直接登录(我竟然还傻傻的使用密码登录),使用以下命令即可免密登录

  • mysql
  • mysql -uroot
    但是如果使用以下正常步骤会提示你需要密码(实际此时输入任何密码都可登录)
    mysql -u root -p
    这时候之所以还需要输入密码,可能是因为-p 这个参数缺少值,哪怕是空值也要传入。

2、无法设置密码
这是因为使用了skip -grant-tables,他会禁用诸如更改用户和设置密码之类的账户管理语句。所以当你希望设置密码的时候总会提示语法错误,如下图所示:

修改密码提示语法错误

3、无法修改配置文件中 skip-networking变量的值
不管你如何修改,skip-networking 的值依然是 ON。

4、无法修改端口号,端口号始终为0

2、3、4三个问题都是因为skip -grant-tables引起的。官网有这么一句话:

Stop the MySQL server if necessary, then restart it with the –skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, you might want to use –skip-grant-tables in conjunction with –skip-networking to prevent remote clients from connecting.

如果需要,停止MySQL服务器,然后使用-skip-grant-tables选项重新启动它。这使得任何人都可以在没有密码和所有特权的情况下连接,并禁用诸如更改用户和设置密码之类的帐户管理语句。因为这是不安全的,所以您可能希望将-skip-grant-tables与-skip-networking结合使用,以防止远程客户机连接。

问题根源找到了,怎么解决,官网说:

In the mysql client, tell the server to reload the grant tables so that account-management statements work:

在mysql客户端,告诉服务器重新加载grant tables,这样账户管理报表就可以工作:

FLUSH PRIVILEGES;

然后就可以修改(设置)密码了:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassWord'

A0D25071-7E19-4891-8CF9-CFF3C2263E2E.png

修改之后一定要记得将skip-grant-tables删除或者注释。

这时候再查看一下变量 skip_networking的值:

17CC3FE4-7887-45BB-86D3-1B0B365D6B75.png

再查看端口号:


BA80A560-3808-47B2-BF11-8B9078F98912.png

官网详细介绍

好吧。上面这些问题是我在使用 Python 脚本连接 MysQL 数据库的时候出现的,现附上脚本以供需要的朋友练习:

#!/usr/bin/python3

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost", "root", "123456", "TESTDB" )

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print ("Database version : %s " % data)

# 关闭数据库连接
db.close()

root是数据库用户,123456是数据库密码,TESTDB 是需要链接的数据库。
脚本执行结果:

/usr/local/Cellar/python3/3.6.2/Frameworks/Python.framework/Versions/3.6/bin/python3.6 /Users/guxuecheng/Desktop/test_sample.py/test22.py
Database version : 8.0.11 

Process finished with exit code 0

推荐阅读更多精彩内容