关系型数据库 ---- MariaDB(用户管理，数据库创建，数据增删改)-CSDN博客

数据库简介

数据库--即电子文件柜，用户可以对文件中的数据进行增，删，改，查等操作。

数据库分类

关系型数据库
关系型数据库管理系统（Relational Database Management System，RDBMS）
非关系型数据库
noSQL（Not Only SQL )

关系型数据库与非关系型数据库的区别

关系型数据库

数据以二维表格的形式存储，可以通过外键关联，一致性强；
数据存储在磁盘中，每次读取数据都是一次I/O请求，数度慢，系统开销大，高并发的情况下很影响性能
使用方便，易于维护

非关系型数据库

数据以key-value形式存储，只适合存储以一些简单的数据；不适合持久存储海量数据；
数据存储在内存中，关联性弱，读取速度快；

常见关系型数据库管理系统

Oracle
Mysql
MariaDB
Microsoft SQL Server
db2
.......

常见非关系型数据库

Redis
MongoDB
Amazon
.......

MariaDB

MariaDB 数据库管理系统是Mysql的一个分支，是RDMS，主要由开源社区来维护；由于SUN被甲骨文公司收购，Mysql的所有权归oracle所有，不再开源。MySQL之父----Monty在2009年发起了MariaDB 开源项目。

MariaDB安装

MariaDB 10.3版本安装源

[mariadb]
name = MariaDB
baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

MariaDB数据类型

常用数据类型

整数型：int，bit
小数型：decimal #decimal(5,2)
字符串：varchar，char
时间：date，time，datetime
枚举类型：enum

约束

primary key：主键
not null：不为空
unique：字段不重复
default：默认
foreign key：外键，对关系字段进行约束，当关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常。

数值类型常用

类型	字节大小	有符号范围（signed）	无符号范围（unsigned）
tinyint	1	-128~127	0~255
smallint	2	-32768~32767	0~65535
mediumint	3	-8388608~8388607	0~16777215
int/integer	4	-2147483648~2147483647	0~4294967295
bigint	8	-9223372036854775808~ 9223372036854775807	0~18446744073709551615

字符串

类型	字节大小	示例
char	0~255	不能伸缩
varchar	0~255	可以伸缩
text	0~65535	大文本

日期时间类型

类型	字节大小	示例
date	4	'2019-01-01'
time	3	'12:30:30'
datetime	8	'2019-01-01 12:30:30'
timestamp	4	'1970-01-01 00:00:01'UTC~'2038-01-01 00:00:01'UTC

SQL语句（命令）

数据库管理

创建或修改密码

╭─root@localhost.localdomain ~  
╰─➤  mysql_secure_installation

进入RDBMS

╭─root@localhost.localdomain ~  
╰─➤  mysql -uroot -p123456     #SQLuser和passward

退出RDBMS

MariaDB [mysql]> quit
Bye
╭─root@localhost.localdomain ~  
╰─➤

查看所有数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

创建数据库

MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.001 sec)

#指定字符编码
MariaDB [(none)]> create database test2 character set utf8;
Query OK, 1 row affected (0.003 sec)

查看创建数据库的状态

MariaDB [test1]> show create database test1;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+

修改指定数据库的字符编码

MariaDB [test1]> alter database test1 default character set=utf8;
Query OK, 1 row affected (0.002 sec)

使用数据库

MariaDB [(none)]> use test1;
Database changed
MariaDB [test1]>

删除数据库

MariaDB [test1]> drop database test2;
Query OK, 0 rows affected (0.005 sec)

用户管理

查看当前用户

MariaDB [test1]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.002 sec)

查看用户信息

用户信息储存在mysql数据库的user表

MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+

在localhost主机上所有库所有表上给user250用户所有权限，认证密码'123456'

MariaDB [mysql]>-- grant 权限 on 库名.表 to 用户@主机 identified  by  '密码';

MariaDB [mysql]> grant all privileges on *.* to user250@localhost identified by '123456';
Query OK, 0 rows affected (0.003 sec)

给root用户在所有主机上的所有权限（用于第三方登录数据库）(端口号：3306)

MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select host,user,password from user;
+-----------+---------+-------------------------------------------+
| host      | user    | password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1       | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | user250 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+

给user250用户添加查询的权限

MariaDB [mysql]> grant select on *.* to user250@localhost ;
Query OK, 0 rows affected (0.000 sec)

select：查询权限
create：创建权限
update：更新权限
delete：删除权限

查看user250的权限

MariaDB [(none)]>-- show grants for 用户@主机；

MariaDB [(none)]> show grants for user250@localhost;

收回user250用户的所有权限

MariaDB [(none)]> revoke all on *.* from user250@localhost;
Query OK, 0 rows affected (0.001 sec)

表结构操作

显示当前时间

MariaDB [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-03 07:00:39 |
+---------------------+

创建葫芦娃表

MariaDB [test1]> create table huluwa (
    -> id int unsigned auto_increment primary key,
    -> name varchar(10),
    -> age tinyint unsigned,
    -> high decimal(5,2),
    -> gender enum('boy','girl','unknow')default 'unknow'
    -> );
Query OK, 0 rows affected (0.206 sec)

查看所有表

MariaDB [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| huluwa          |
+-----------------+

查看表结果

MariaDB [test1]> desc huluwa;
+--------+-----------------------------+------+-----+---------+----------------+
| Field  | Type                        | Null | Key | Default | Extra          |
+--------+-----------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)                 | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned         | YES  |     | NULL    |                |
| high   | decimal(5,2)                | YES  |     | NULL    |                |
| gender | enum('boy','girl','unknow') | YES  |     | unknow  |                |
+--------+-----------------------------+------+-----+---------+----------------+

给表添加字段

MariaDB [test1]>-- alter table 表名 add 列名 类型;

MariaDB [test1]> alter table huluwa add color varchar(10);
Query OK, 0 rows affected (0.406 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表字段；不重命名

MariaDB [test1]>-- alter table 表名 modify 列名 类型及约束；

MariaDB [test1]> alter table huluwa modify name varchar(20);
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表字段；重命名

MariaDB [test1]>-- alter table 表名 change 原名 新名 类型及约束;

MariaDB [test1]> alter table huluwa change age bir_day date;
Query OK, 0 rows affected (0.426 sec)              
Records: 0  Duplicates: 0  Warnings: 0

删除字段

MariaDB [test1]>-- alter table 表名 drop 列名；

MariaDB [test1]> alter table huluwa drop color;
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

MariaDB [test1]>-- drop table 表名;

MariaDB [test1]> drop table huluwa;
Query OK, 0 rows affected (0.010 sec)

表数据增，删，改

注意：bit 型数据有值只是终端显示不明显

表结构

MariaDB [test1]> desc huluwa;
+--------+-----------------------------+------+-----+---------+----------------+
| Field  | Type                        | Null | Key | Default | Extra          |
+--------+-----------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned            | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)                 | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned         | YES  |     | NULL    |                |
| high   | decimal(5,2)                | YES  |     | NULL    |                |
| gender | enum('boy','girl','unknow') | YES  |     | unknow  |                |
| lost   | bit(1)                      | YES  |     | NULL    |                |
+--------+-----------------------------+------+-----+---------+----------------+

查询表创建状态

MariaDB [test1]> show create table huluwa;

增加：全列插入(大娃出生了)

MariaDB [test1]>-- insert into 表名 values (...);

MariaDB [test1]> insert into huluwa values (0,'大娃',1,1.324,'boy',0);
Query OK, 1 row affected, 1 warning (0.003 sec)

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |      |
+----+--------+------+------+--------+------+

修改：（大娃丢了，lost列变为1）

MariaDB [test1]>-- update 表名 set 列=值... where 过滤条件；

MariaDB [test1]> update huluwa set lost=1 where name='大娃';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |     |
+----+--------+------+------+--------+------+

增加：部分插入（二娃出生了）

MariaDB [test1]>-- insert into 表名（字段） valuse（...）;

MariaDB [test1]> insert into huluwa(id, name,lost) values(0,'二娃',0);
Query OK, 1 row affected (0.003 sec)

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |     |
|  2 | 二娃   | NULL | NULL | unknow |      |
+----+--------+------+------+--------+------+

增加：插入多条（三娃和四娃一起出生了）

MariaDB [test1]>-- insert into 表名 valuse (...),(...),...;

MariaDB [test1]> insert into huluwa(id, name,lost) values(0,'三娃',0),(0,'四娃',0);
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  1 | 大娃   |    1 | 1.32 | boy    |     |
|  2 | 二娃   | NULL | NULL | unknow |      |
|  3 | 三娃   | NULL | NULL | unknow |      |
|  4 | 四娃   | NULL | NULL | unknow |      |
+----+--------+------+------+--------+------+

删除（delete / truncate）（删除丢了的葫芦娃）

MariaDB [test1]>-- delete from 表名 where 条件;

MariaDB [test1]> delete from huluwa where lost=1;

MariaDB [test1]> select * from huluwa;
+----+--------+------+------+--------+------+
| id | name   | age  | high | gender | lost |
+----+--------+------+------+--------+------+
|  2 | 二娃   | NULL | NULL | unknow |      |
|  3 | 三娃   | NULL | NULL | unknow |      |
|  4 | 四娃   | NULL | NULL | unknow |      |
+----+--------+------+------+--------+------+

MariaDB [test1]> truncate table huluwa;
Query OK, 0 rows affected (0.040 sec)

MariaDB [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| huluwa          |
+-----------------+
1 row in set (0.000 sec)

MariaDB [test1]> select * from huluwa;
Empty set (0.000 sec)