MySQL 笔记

| by


　　MySQL 是一个关系型数据库管理系统，原本是一个开放源代码的关系数据库管理系统，原开发者为瑞典的 MySQL AB 公司，该公司于 2008 年被昇阳微系统（Sun Microsystems）收购。2009 年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL 成为 Oracle 旗下产品，是当前最流行的关系型数据库管理系统之一。

1、术语

  • 数据库：数据库是一些关联表的集合；
  • 数据表：表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格；
  • 列：一列(数据元素) 包含了相同的数据, 例如邮政编码的数据；
  • 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据；
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性；
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据；
  • 外键：外键用于关联两个表，web 设计通常不使用数据库外键约束，而使用 Eloquen orm ，写逻辑约束；
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引；
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录；
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2、安装

　　Mac 下 MySQL 安装方法有多种，不过始终觉得直接下载 dmg 安装比较方便，直接按引导一路下一步即可完成，记得保存最后弹出框中的密码（它是你 mysql root 账号的密码），安装完成后设置中会有 MySQL 选项，可以设置开机启动、启动/停止等。后续设置也比较简单，只需要设置当前用户的的环境变量即可（为了方便终端输入命令方便，设置后可直接使用 mysql 命令，不设置的话命令需要加上路径 /usr/local/mysql/bin/mysql），设置方法如下：

  • 显示隐藏文件 command+shift+. ，找到用户文件夹下 .bash_profile 文件
  • 加入一行 PATH=$PATH:/usr/local/mysql/bin 即可
  • 或直接命令修改 echo 'PATH=$PATH:/usr/local/mysql/bin' >>~/.bash_profile
# .bash_profile 文件

# mysql 环境变量
PATH=$PATH:/usr/local/mysql/bin

# composer 环境变量
PATH=$PATH:~/.composer/vendor/bin/

3、升级

# 1、下载 dmg 安装

# 2、把新版本 data 重命名为 dataold
sudo mv /usr/local/mysql-5.7.20-macos10.13-x86_64/data /usr/local/mysql-5.7.20-macos10.13-x86_64/dataold

# 3、把旧版本数据复制到新版本位置
sudo cp -rf /usr/local/mysql-5.5.20-macos10.13-x86_64/data /usr/local/mysql-5.7.20-macos10.13-x86_64/

# 4、设置权限，正确为 ower _mysql 读写 wheel 只读 everyone 无权
sudo chown -R _mysql /usr/local/mysql-5.7.20-macos10.13-x86_64/data

# 5、升级
sudo /usr/local/mysql/bin/mysql_upgrade

# 6、重启服务 

# 7、删除 dataold 及老版本
sudo rm -rf /usr/local/mysql-5.7.20-macos10.13-x86_64/dataold
sudo rm -rf /usr/local/mysql-5.5.20-macos10.13-x86_64

4、登录管理

4.1、登录

# -u 用户名 -p 密码登录
mysql -u root -p    #然后输入密码回车即可
# 其他登录选项 -h 指定访问的是 ip 地址主机上的数据库，默认 localhost；-P 指定端口，默认3306
mysql -h 192.32.2.9 -u username -p -P 3308

4.2、设置

# 设置密码
# 方法 1
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
# 方法 2，IDENTIFIED WITH mysql_native_password 指定密码模式为 mysql_native_password
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘newpass’;

# 授权 GRANT privileges on databasename.tablename to 'test’@‘host’;
# 1、允许 test 用户从任何（% 为所有）主机访问 databasename 的任何（* 为所有）数据表，允许操作为 all
GRANT all on databasename.* to 'test'@'%';
# 2、只允许 test 用户从 IP 为 192.32.2.9 主机访问 databasename 的 user 数据表，允许操作为 select
GRANT select on databasename.user to 'test'@'192.32.2.9';
# select, insert, update, delete, create, drop,
# index, alter, grant, references, reload, shutdown, process, file等14个权限。

4.3、常用操作

# 命令包含分号

# 显示数据库
SHOW DATABASES;
# 创建数据库
CREATE DATABASE dbName;
# 删除数据库
DROP DATABASE dbName;

# 选择数据库
USE dbName;
# 显示数据表
SHOW TABLES;
# 创建数据表 字段名-类型-属性-空-默认值-主键
CREATE TABLE `tableName` (`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `sex` CHAR(1) NOT NULL DEFAULT 'F');
# 数据表插值
INSERT INTO `tableName` (name, sex) VALUES ('hyq','M');
# 在某个字段后面添加字段
ALTER TABLE `tableName` Add COLUMN `new_filed` int(10) NOT NULL DEFAULT 0 AFTER `someone_filed`;
# 删除字段
ALTER TABLE `tableName` DROP COLUMN `someone_filed`;
# 更新表中数据
UPDATE `tableName` SET sex='F' WHERE id='1';
# 删除数据表
DROP TABLE `tableName`;
# 显示表结构
DESCRIBE `tableName`;
# 显示表中的记录
SELECT * FROM `tableName`;
# 清空表中记录
DELETE FROM `tableName`;

# 退出
exit;

# 导入 .sql 数据
# 方法一
mysql -u root -p
USE dbName;
source ~/data.sql;
# 方法二
# mysql -u用户名 -p密码 数据库名 < 数据库名.sql
mysql -u root -p dbName < ~/data.sql
# 导出 .sql 文件
mysqldump -u root -p dbName > ~/dbName.sql

5、PHP 操作 MySQL

  • mysqli 连接相关的
  • mysqli_result 处理结果集
  • mysqli_stmt 预处理类
// 基于 PHP7

// 连接数据库
$host = $dbHost . ':' . $dbPort;
$mysqli = new mysqli($host, $dbUser, $dbPassword, $dbName);
// 持久连接
$host = 'p:' . $dbHost . ':' . $dbPort;
$mysqli = new mysqli($host, $dbUser, $dbPassword, $dbName);

// 抛出连接错误
if(mysqli_connect_error()){
    echo mysqli_connect_error();
}
// 关闭
$mysqli->close();
// 数据库列表
$host = $dbHost . ':' . $dbPort;
$mysqli = new mysqli($host, $dbUser, $dbPassword);
$result=$mysqli->query('SHOW DATABASES');
$filter_dbs = array('information_schema', 'mysql', 'sys', 'performance_schema', 'tableviewdemo');
$databases = array();
while($row = $result->fetch_assoc()){
    if (in_array($row['Database'], $filter_dbs)) {
        continue;
    }
    $databases[] = $row['Database']; //遍历查询结果
}
// 关闭
$mysqli->close();
// 插入数据
$host = $dbHost . ':' . $dbPort;
$mysqli = new mysqli($host, $dbUser, $dbPassword, $dbName);
$sql = "insert into table_name (`name`, `address`) values ('xiaoming', 'adddddrrreess')";
$result = $mysqli->query($sql);
if($result === false){//执行失败
    echo $mysqli->error;
    echo $mysqli->errno;
}
// 关闭
$mysqli->close();
// 预处理
//获得预处理对象
$host = $dbHost . ':' . $dbPort;
$mysqli = new mysqli($host, $dbUser, $dbPassword, $dbName);
$stmt = $mysqli->prepare($sql);
//绑定参数 第一个参数为绑定的数据类型
/*
i:integer 整型
d:double 浮点型
s:string 字符串
b:a blob packets blob数据包
*/
$name = "xiaoming";
$address = "adddressss";
$stmt->bind_param("ss", $name, $address);//绑定时使用变量绑定
//执行预处理
$stmt->execute();
//错误号
echo $stmt->errno;
//错误信息
echo $stmt->error;
//关闭
$stmt->close();
// 关闭
$mysqli->close();
MySQL
下一篇 Markdown 基本语法
上一篇 PHP 笔记
查看评论