数据结构模型主要有：
关系模型：
二维关系：row（行,代表一个记录），column（列，代表一个字段）
数据库管理系统：
DBMS（DataBase Management System）
关系型数据库管理系统
RDBMS（Relationship DataBase Management System）
关系：Relational，RDBMS
常见的关系型数据库管理系统：
SQL：Structure Query Language，结构化查询语言
约束：constraint，向数据表提供的数据要遵守的限制
索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储
关系型数据库的常见组件有：
SQL语句有三种类型：
| SQL语句类型 | 对应操作 |
|---|---|
| DDL | CREATE：创建 DROP：删除 ALTER：修改 |
| DML | INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据 |
| DCL | GRANT：授权 REVOKE：移除授权 |
mysql安装方式有三种：
安装mariadb
[root@localhost ~]# yum -y install mariadb*
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 21:10:39 ago on Mon 21 Dec 2020 06:23:39 AM EST.
Dependencies resolved.
=============================================================================
Package Arch Version Repo Size
=============================================================================
Installing:
mariadb x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
AppStream 6.1 M
mariadb-backup x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
AppStream 6.0 M
mariadb-common x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
AppStream 62 k
mariadb-connector-c x86_64 3.0.7-1.el8 AppStream 148 k
mariadb-connector-c-config noarch 3.0.7-1.el8 AppStream 13 k
mariadb-connector-c-devel x86_64 3.0.7-1.el8 AppStream 63 k
mariadb-connector-odbc x86_64 3.0.7-1.el8 AppStream 113 k
mariadb-devel x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
AppStream 1.0 M
mariadb-embedded x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
perl-URI-1.73-3.el8.noarch
perl-Unicode-Normalize-1.25-396.el8.x86_64
perl-constant-1.33-396.el8.noarch
Complete!
设置开机自动启动，并且现在启动
[root@localhost ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor >
Active: active (running) since Tue 2020-12-22 03:39:18 EST; 10s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 8402 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited,>
Process: 8267 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.servi>
Process: 8243 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, s>
Main PID: 8370 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 30 (limit: 4928)
Memory: 111.2M
CGroup: /system.slice/mariadb.service
└─8370 /usr/libexec/mysqld --basedir=/usr
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: MySQL manu>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: Please rep>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: The latest>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: You can fi>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: http://dev>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: Consider j>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: https://ma>
Dec 22 03:39:17 localhost.localdomain mysqld[8370]: 2020-12-22 3:39:17 0 [N>
Dec 22 03:39:17 localhost.localdomain mysqld[8370]: 2020-12-22 3:39:17 0 [E>
Dec 22 03:39:18 localhost.localdomain systemd[1]: Started MariaDB 10.3 datab>
[root@localhost ~]# ss -antl //3306端口号已经起来
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]#
登录mysql
//-u 是指定用户
[root@localhost ~]# mysql -uroot //这里的root用户只是数据库的管理员账户
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> quit
Bye
//-p 需要输入密码登录
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
//设置密码
MariaDB [(none)]> set password = password(‘123456‘); //=后面的password是一个加密函数
Query OK, 0 rows affected (0.001 sec)
//-p后面加密码可以登录（不推荐这种方式登录，容易让别人看见密码）
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> quit
Bye
//修改密码
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> alter user root@localhost identified by ‘987654312‘; //修改密码
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot -p123456
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)
[root@localhost ~]# mysql -uroot -p987654312
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql //tab两下
mysql mysql_fix_extensions
mysqlaccess mysqlhotcopy
mysqladmin mysqlimport
mysqlbinlog mysql_install_db
mysqlcheck mysql_plugin
mysql_client_test mysql_secure_installation
mysql_client_test_embedded mysql_setpermission
mysql_config mysqlshow
mysql_convert_table_format mysqlslap
mysqld_multi mysqltest
mysqld_safe mysqltest_embedded
mysqld_safe_helper mysql_tzinfo_to_sql
mysqldump mysql_upgrade
mysqldumpslow mysql_waitpid
mysql_find_rows
[root@localhost ~]# ss -antlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=1079,fd=5))
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:* users:(("mysqld",pid=8370,fd=21))
LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=1079,fd=7))
[root@localhost ~]#
初始化数据库
[root@localhost ~]# mysql_secure_installation
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.
You already have a root password set, so you can safely answer ‘n‘.
Change the root password? [Y/n] Y //是否更改密码
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!
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 //是否不允许root账户远程登陆
... skipping.
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] Y //移除测试数据库和他的访问权限
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
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!
//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本
演示如下
[root@localhost ~]# mysql -V
mysql Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 28
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.126 sec)
MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot -p123456 -e ‘show databases‘ //在外面查看
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@localhost ~]#
创建数据库
语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME‘;
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
删除数据库
语法：DROP DATABASE [IF EXISTS] ‘DB_NAME‘;
MariaDB [(none)]> drop database school;
Query OK, 0 rows affected (0.311 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
创建表
语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=‘存储引擎类型‘;
MariaDB [(none)]> use school; //进入school数据库
Database changed
MariaDB [school]> CREATE TABLE student(id int not null,name varchar(50) null,age tinyint); //创建student表
Query OK, 0 rows affected (0.145 sec)
MariaDB [school]> show tables; //查看已创建的student表
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.002 sec)
MariaDB [school]> DESC student; //查看student表的结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)
MariaDB [school]>
添加表里的数据
MariaDB [school]> ALTER TABLE student ADD class varchar(20);
Query OK, 0 rows affected (0.072 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.002 sec)
MariaDB [school]>
删除表里的数据
MariaDB [school]> ALTER TABLE student DROP class;
Query OK, 0 rows affected (2.510 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.002 sec)
MariaDB [school]>
删除表
MariaDB [school]> DROP TABLE student;
Query OK, 0 rows affected (0.032 sec)
MariaDB [school]> show tables;
Empty set (0.001 sec)
MariaDB [school]>
//查看创建database的帮助文档
MariaDB [school]> help create database;
Name: ‘CREATE DATABASE‘
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://mariadb.com/kb/en/create-database/
MariaDB [school]>
//查看创建table的帮助文档
MariaDB [school]> help create table;
Name: ‘CREATE TABLE‘
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT ‘string‘]
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT ‘string‘
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
table_options:
table_option [[,] table_option] ...
table_option:
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] ‘string‘
| CONNECTION [=] ‘connect_string‘
| DATA DIRECTORY [=] ‘absolute path to directory‘
| DELAY_KEY_WRITE [=] {0 | 1}
| INDEX DIRECTORY [=] ‘absolute path to directory‘
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] ‘string‘
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (tbl_name[,tbl_name]...)
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] ‘comment_text‘ ]
[DATA DIRECTORY [=] ‘data_dir‘]
[INDEX DIRECTORY [=] ‘index_dir‘]
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] ‘comment_text‘ ]
[DATA DIRECTORY [=] ‘data_dir‘]
[INDEX DIRECTORY [=] ‘index_dir‘]
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement)
CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.
Rules for permissible table names are given in
https://mariadb.com/kb/en/identifier-names/. By default,
the table is created in the default database, using the InnoDB storage
engine. An error occurs if the table exists, if there is no default
database, or if the database does not exist.
URL: https://mariadb.com/kb/en/create-table/
MariaDB [school]>
原文：https://www.cnblogs.com/leixixi/p/14205938.html