学习笔记｜MySQL

本文最后更新于：3 years ago

1.数据库基本概念

**数据库（DateBase）：**用于储存和管理数据的仓库

1.1特点

  • 持久化储存数据，数据库是一个文件系统
  • 方便储存和管理数据
  • 使用了统一的方式操作数据库——SQL

2.MySQL 数据库软件

2.1安装

CentOS 需要安装 mariadb。分别为 mariadb-server 和 mariadb-client。

1
yum install mariadb-server

2.2卸载

1
yum remove mariadb-server

2.3启动

1
systemctl start mariadb

2.4开机自启

1
systemctl enable mariadb

2.5配置

执行以下语句，开始进入配置步骤。

1
mysql_secure_installation   # mysql 安全安装

根据提示，完成以下步骤：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

# 修改密码
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

# 删除匿名用户
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

# 关闭 root 管理员的远程登陆
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

# 删除用于测试的数据库 “test”
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

# 重新加载权限表
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

# 配置成功
Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

2.7配置字符集

1
vim /etc/mysql/my.cnf

在[mysqld]中添加 default-character-set=utf8 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in https://fedoraproject.org/wiki/Systemd
character-set-server=utf8

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

PS：Client 的字符集在 /etc/my.cnf.d/client.cnf 中的 [client] 和/etc/my.cnf.d/mysql-clients.cnf 中的 [mysql] 添加 default-character-set=utf8 。

2.8创建用户

1
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
参数说明
username将要创建的用户名
host指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

2.9其他命令

2.9.1登陆

1
2
3
4
5
# 显示密码登陆
mysql -uroot -pYourPassword

# 不显示密码登陆
mysql -uroot -p

2.9.2远程登陆

利用 -h 加 ip。

1
2
3
mysql -h127.0.0.1 -uroot -p
# 或者
mysql --host=127.0.0.1 --user=root --password

2.9.3退出

1
2
3
exit
# 或者
quit

3.目录结构

3.1路径

  • usr/bin/mysql 是指：mysql的运行路径
  • var/lib/mysql 是指：mysql数据库文件的存放路径
  • usr/lib/mysql 是指：mysql的安装路径（有可能是 usr/lib64/mysql）

3.2基本概念

3.2.1数据库

目录

  • information_schema：视图（描述mysql 下的库和文件）

  • mysql：mysql的核心数据库，存放mysql服务的核心数据

  • proformance_schema：对性能提升的操作的表

3.2.2表

文件

表：文件（.frm）

3.2.3数据

数据：表中的记录

4.SQL

4.1基本概念

Structured Query Language（结构化查询语言）

SQL：定义了所有操作关系型数据库的规则。

4.2SQL 语法规则

  1. SQL语句可以单行/多行书写，以分号结尾。

  2. 用空格和缩进增强语句可读性。

  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

  4. 注释

    • 单行注释：
    1
    2
    3
    -- 两个横杠一个空格后面加注释。

    # 号后直接写注释（MySQL特有的）。
    • 多行注释
    1
    2
    3
    4
    /*
    这是
    注释 
    */

4.3SQL分类

分类备注
DDL
Data Definition Language，数据定义语言		操作数据库和表
DML
Data Manipulation Language，数据操纵语言		增删改表中的数据
DQL
Data Query Language，数据查询语言		查询表中的数据
DCL
Data Controlling Language，数据控制语言		定义数据库访问权限和安全级别

5.DDL

5.1操作数据库：CRUD

5.1.1Create：创建

  1. 创建数据库 db1
1
2
3
4
5
create database db1;

-- result
[MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.01 sec)
  1. 若数据库 db1 不存在，则创建db1
1
2
3
4
5
create database if not exists db1;

-- result
[MariaDB [(none)]> create database if not exists db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
  1. 创建数据库 db1 时，指定字符集为 utf-8
1
2
3
4
5
create database db1 character set utf8;  -- 注意是 utf8，不是 utf-8。

-- result
[MariaDB [(none)]> create database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)
  1. 创建数据库 db1 时，检查是否存在，若不存在指定其字符集为 utf-8
1
2
3
4
5
create database if not exists db1 character set utf8;

-- result
[MariaDB [(none)]> create database if not exists db5 character set utf8;
Query OK, 1 row affected (0.00 sec)

5.1.2Retrieve：查询

  1. 查询所有数据库的名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
show databases;

-- result
[MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
  1. 查询数据库 db1 的字符集
1
2
3
4
5
6
7
8
9
10
show create database db1;

-- result
[MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

5.1.3Updata：修改

  1. 修改数据库的字符集
1
2
3
4
5
alter database db1 character set gbk;

-- result
[MariaDB [(none)]> alter database db1 character set gbk;
Query OK, 1 row affected (0.00 sec)

5.1.4Delete：删除

  1. 删除数据库
1
2
3
4
5
drop database db2;

-- result 
MariaDB [(none)]> drop database db2;
Query OK, 0 rows affected (0.00 sec)
  1. 删除之前判断是否存在
1
2
3
4
5
drop database if exists db2;

-- result
MariaDB [(none)]> drop database if exists db2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

5.1.5使用数据库

1.进入数据库（使用数据库）

1
2
3
4
5
use db1;

-- result
[MariaDB [(none)]> use db1
Database changed
  1. 查询正在使用的数据库
1
2
3
4
5
6
7
8
9
10
select database();

-- result 
[MariaDB [db1]> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

5.2操作表：CRUD

5.2.1Create：创建

  1. 创建表

5.2.2Retrieve：查询

  1. 查询数据库中所有表的名称

  2. 查询表结构

5.2.3Updata：修改

5.2.4Delete：删除

Mark
#MySQL
学习笔记｜MySQL
https://www.aimtao.net/mysql/
Posted on
2019-07-14
Licensed under