013MySQL

MySQL

​ MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

​ MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

​ MySQL是一款市场占有率非常高的数据库管理系统，技术成熟、配置步骤相对简单，而且具有良好的可扩展性。
​ 但是，由于Oracle公司在2009年收购了MySQL的母公司Sun，因此MySQL数据库项目也随之纳入Oracle麾下，逐步演变为保持着开源软件的身份，但又申请了多项商业专利的软件系统。
​ 因此由MySQL项目创始者重新研发了一款名为MariaDB的全新数据库管理系统。该软件当前由开源社区进行维护，是MySQL的分支产品，而且几乎完全兼容MySQL。

CentOS-7安装MariaDB服务

使用yum安装mariadb-server

[root@localhost~]# yum install mariadb mariadb-server

启动mariadb

[root@localhost ~]# systemctl start mariadb

初始化MariaDB服务

[root@localhost ~]# mysql_secure_installation 
Enter current password for root (enter for none): 当前数据库密码为空，直接按回车键
Set root password? [Y/n] y
New password: 输入要为root管理员设置的数据库密码
Re-enter new password: 再次输入密码
Password updated successfully!
Reloading privilege tables..
 ... Success!
Remove anonymous users? [Y/n] y（删除匿名账户）
Disallow root login remotely? [Y/n] n（禁止root管理员从远程登录）
Remove test database and access to it? [Y/n] y（删除test数据库并取消对它的访问权限）
Reload privilege tables now? [Y/n] y（刷新授权表，让初始化后的设定立即生效）

​ 在确认MariaDB数据库软件程序安装完毕并成功启动后请不要立即使用。为了确保数据库的安全性和正常运转，需要先对数据库程序进行初始化操作。

• 设置root管理员在数据库中的密码值（注意，该密码并非root管理员在系统中的密码，这里的密码值默认应该为空，可直接按回车键）。
• 设置root管理员在数据库中的专有密码。
• 随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
• 删除默认的测试数据库，取消测试数据库的一系列访问权限。
• 刷新授权列表，让初始化的设定立即生效。

在防火墙策略中服务名称统一叫作mysql：

[root@localhost ~]# firewall-cmd --permanent --add-service=mysql
[root@localhost ~]# firewall-cmd --reload

登录MariaDB数据库

[root@linuxprobe ~]# mysql -u root -p
Enter password: 此处输入root管理员在数据库中的密码

在登录MariaDB数据库后执行数据库命令时，都需要在命令后面用分号（;）结尾，这也是与Linux命令最显著的区别。

显示所有的库

MariaDB [(none)]> SHOW databases;

管理账户以及授权

​ root账户中的host项是localhost表示该账号只能进行本地登录，我们需要修改权限，输入命令：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Windows 上安装 MySQL

Windows 上安装 MySQL 相对来说会较为简单，点击链接 https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.11-winx64.zip 下载 zip 包。

最新版本可以在 MySQL 下载 中下载中查看。

点击 Download No thanks, just start my download.

下载完后，我们将 zip 包解压到相应的目录，这里我将解压后的文件夹放在 C:\web\mysql-8.0.11 下。

接下来我们需要配置下 MySQL 的配置文件

打开刚刚解压的文件夹 C:\web\mysql-8.0.11 ，在该文件夹下创建 my.ini 配置文件，编辑 my.ini 配置以下基本信息：

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

接下来我们来启动下 MySQL 数据库：

以管理员身份打开 cmd 命令行工具，切换目录：

cd C:\web\mysql-8.0.11\bin

初始化数据库：

mysqld --initialize --console

执行完成后，会输出 root 用户的初始默认密码，如：

...
2018-04-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.7 需要初始化 data 目录：

cd C:\web\mysql-8.0.11\bin 
mysqld --initialize-insecure 

初始化后再运行 net start mysql 即可启动 mysql。

---

登录 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 退出登录。

Ubuntu安装 Mariadb-server

下载安装

sudo apt install mariadb-server
链接、测试
sudo mysql -u root -p
安装数据库实例
sudo mysql_secure_installation

启动&重启关闭
查看状态
sudo systemctl status mysql
启动
sudo systemctl start mysql
重启
sudo systemctl restart mysql
关闭
sudo systemctl stop mysql
配置
登录
sudo mysql -u root -p #输入root 用户密码

创建远程登录用户 ，
格式：create user ‘用户名’@’%’ identified by ‘密码’;

create user 'us'@'%' identified by '123';

授权（如果权限不足）

GRANT ALL PRIVILEGES ON *.* TO 'us'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 不要开放root 远程登录权限

查看监听

netstat -an | grep 3306

#tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
#如果显示 监听端口 是127.0.0.1:3306 那么其他计算机无法连接

#修改配置
vi /etc/mysql/mariadb.conf.d/50-server.cnf#修改结束记得用重启指令重启mysql服务
Instead of skip-networking the default is now to listen only on
localhost which is more compatible and is not less secure.

# bind-address = 127.0.0.1 #注释掉这一行

# 重启后显示
#tcp6 0 0 :::3306 ::? LISTEN
# 此时就可以远程访问mariadb了
# 远程访问
# 直接登录
mysql -h localhost -u us -p 123 #输入us 用户密码123
# 用客户端工具登录
# sqlyog navicat  

# 异常处理
# Access denied for user ‘root’@‘localhost’
$ sudo systemctl stop mariadb # 停止服务
$ sudo mysqld_safe --skip-grant-tables & # 进入安全模式，并设置为后台进程
$ mysql -u root　　　# 登陆mysql

select Host,User,plugin from mysql.user where User='root';  # 查询用户
update mysql.user set plugin='mysql_native_password';  #重置加密模式
update mysql.user set password=PASSWORD("newpassword") where User='root';  #重置密码
flush privileges;  #刷新权限信息
exit

$ sudo kill -9 $(pgrep mysql) # 杀掉进程
$ sudo service mariadb start # 重新启动服务
$ mysql -u root -p #登陆mysql, 安装unix_soket
$ install plugin unix_socket soname ‘auth_socket’;

数据库(Database)

什么是数据库

​ 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，主要分为三大类：关系型数据库、层次式数据库、网络式数据库。

关系型数据库

​ 常用数据库多为关系型数据库RDBMS(Relational Database Management System)，RDMBS是SQL的基础，同样也是所有现代数据库的基础，比如MS SQL Server, IBM DB2, Oracle, MySQL 以及 Microsoft Access。
RDBMS数据库主要特点：

• 数据以表的形式出现
• 每行(row)为各种记录名称
• 每列(column)为记录名称所对应的数据域
• 行与列组成一张表(table)
• 若干的表组成数据库(Database)

Oracle - 目前世界上使用最为广泛的数据库管理系统
DB2 - IBM公司开发的，历史悠久且被认为是最早使用SQL的数据库产品，它拥有较为强大的商业智能功能。
SQL Server - 由Microsoft开发和推广的关系型数据库产品，最初适用于中小企业的数据管理
MySQL - MySQL是开放源代码的，GPL（General Public License）的许可下下载并根据个性化的需要对其进行修改。
PostgreSQL - 在BSD许可证下发行的开放源代码的关系数据库产品。

RDMBS术语

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

SQL

什么是SQL

SQL是一种ANSI的标准计算机语言，指结构化查询语言，使我们有能力访问数据库。

SQL语言

• SQL 面向数据库执行查询
• SQL 可从数据库取回数据
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限
  注释：除了SQL标准外，大部分SQL数据库程序都拥有自己的私有扩展

SQL语句

1. 常用命令。

   • 查看服务器版本。

     select version();
     

   • 查看所有数据库。

     show databases;
     

   • 切换到指定数据库。

     use mysql;
     

   • 查看数据库下所有表。

     show tables;
     

   • 其常用他命令。

     查看当前用户：select user();
     当前日期时间select now();
     查看数据库状态：\s;
     退出数据库：\q ;
     

SQL详解

基本操作

DDL(数据定义语言)

• 创建库、创建表、删除库、删除表、修改库、修改表

创建库

create database 库名 charset 字符集;

删除库

drop database 库名;

基本建表的语法

• 1.基本建表的语法：create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);engine=csv default charset=utf8mb4;

• 2.在建表的时候都需要注意的点：

  • 1.在同一张表中，字段名不可以重复；
  • 2.在创建表中，字段宽度和约束条件是可选的，字段名和类型是一定要有的；
  • 3.在创建表的最后一个字段名后不能加逗号
  • 4.mysql不区分大小写

  1.mysql中的基本数据类型

  整型、浮点型、字符类型、日期类型、枚举和集合。

  ​ 1. 整数型

  类型	字节	范围（有符号）	范围（无符号）
  TINYINT(m)	1	(-128，127)	(0，255)
  SMALLINT(m)	2	(-32768，32767)	(0，65535)
  MEDIUMINT(m)	3	(-8388608，8388607)	(0，16777215)
  INT(m)	4	(-2147483 648，2147483647)	(0，4294967295)
  BIGINT(m)	8	(-9223372036854775808，9223372036854775807)	(0，18446744073709551615)

  类型后面括号里的m指的是显示宽度，显示宽度与所能存储的数据范围无关，目前已知的是当设置了ZEROFILL（填充0）约束条件时，填充 0的 个数由m决定，

  例如：设置id int(5) ZEROFILL ，属性id会填充成如下形式

  +-----------+-----------+
  | id        | name      |
  +-----------+-----------+
  | 00001     |  Jane     |
  +-----------+-----------+
  | 00002     |  Mike     |
  +-----------+-----------+
  

  2. 浮点数和定点数

  类型	大小	用途
  FLOAT	4字节	单精度浮点数值
  DOUBLE	8字节	双精度浮点数值
  DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	小数值

  设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

  浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
  decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

  为了能够引起大家的重视，请看下面的例子：

  mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
  Query OK, 0 rows affected (0.29 sec)
  
  mysql> insert into test values(131072.32,131072.32);
  Query OK, 1 row affected (0.07 sec)
  
  mysql> select * from test;
  +-----------+-----------+
  | c1        | c2        |
  +-----------+-----------+
  | 131072.31 | 131072.32 |
  +-----------+-----------+
  1 row in set (0.00 sec)
  

  从上面的例子中我们看到c1列的值由131072.32变成了131072.31，这就是浮点数的不精确性造成的。

  在mysql中float、double（或real）是浮点数，decimal（或numberic）是定点数。

  浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。在今后关于浮点数和定点数的应用中，大家要记住以下几点：

  1. 浮点数存在误差问题；
  2. 对货币等对精度敏感的数据，应该用定点数表示或存储；
  3. 编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
  4. 要注意浮点数中一些特殊值的处理。

  3. 日期时间

  类型	字节	范围	格式	用途
  DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
  TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
  YEAR	1	1901/2155	YYYY	年份值
  DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
  TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

  若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

  4. 字符串类型

  类型	大小	用途
  CHAR	0-255字节	定长字符串
  VARCHAR	0-65535 字节	变长字符串
  TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
  TINYTEXT	0-255字节	短文本字符串
  BLOB	0-65 535字节	二进制形式的长文本数据
  TEXT	0-65 535字节	长文本数据
  MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
  MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
  LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
  LONGTEXT	0-4 294 967 295字节	极大文本数据

  CHAR和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

  1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。
  2.char(n) 固定长度，char(4) 不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入 3 个字符将占用 4 个字节。
  3.char 类型的字符串检索速度要比 varchar 类型的快。

  varchar 和 text：

  1.varchar 可指定 n，text 不能指定，内部存储 varchar 是存入的实际字符数 +1 个字节（n<=255）或 2 个字节(n>255)，text 是实际字符数 +2 个字节。
  2.text 类型不能有默认值。
  3.varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下，text 的索引似乎不起作用。

  BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

  BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

  有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

  2.常用的约束条件

  PRIMARY KEY、UNIPUE KEY、NOT NULL、FOREIGN KEY。

  MySQL属性约束条件

  MySQL关键字	含义
  NULL	数据列可包含NULL值
  NOT NULL	数据列不允许包含NULL值
  DEFAULT	默认值
  PRIMARY KEY	主键
  AUTO_INCREMENT	自动递增，适用于整数类型
  UNSIGNED	无符号
  ZEROFILL	填充零
  CHARACTER SET name	指定一个字符集

  MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。

  外键的使用条件：
  　　　 1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；
  　　　2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；
  　　　3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

  外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

  [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
  REFERENCES <主表名> 主键列1 [，主键列2，…]
  

-- 如果存在名为school的数据库就删除它
drop database if exists school;

-- 创建名为school的数据库并设置默认的字符集和排序方式
create database school default charset utf8 collate utf8_bin;

-- 切换到school数据库上下文环境
use school;

-- 创建学院表
create table tb_college
(
collid		int auto_increment comment '编号',
collname	varchar(50) not null comment '名称',
collmaster	varchar(20) not null comment '院长',
primary key (collid)
);

-- 创建学生表
create table tb_student
(
stuid		int not null comment '学号',
stuname		varchar(20) not null comment '姓名',
stusex		boolean default 1 comment '性别',
stubirth	date not null comment '出生日期',
stuaddr		varchar(255) default '' comment '籍贯',
collid		int not null comment '所属学院',
primary key (stuid),
foreign key (collid) references tb_college (collid)
);

-- 创建教师表
create table tb_teacher
(
teaid		int not null comment '工号',
teaname		varchar(20) not null comment '姓名',
teatitle	varchar(10) default '助教' comment '职称',
collid		int not null comment '所属学院',
primary key (teaid),
foreign key (collid) references tb_college (collid)
);

-- 创建课程表
create table tb_course
(
couid		int not null comment '编号',
couname		varchar(50) not null comment '名称',
coucredit	int not nul