V2AS问路
MariaDB数据库 ----数据库简介，用户管理，数据库创建，数据类型、数据增删改(实例演示)
阅读原文时间：2023年07月10日阅读：2

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

关系型数据库

非关系型数据库

  • Oracle
  • Mysql
  • MariaDB
  • Microsoft SQL Server
  • db2
  • …….
  • Redis
  • MongoDB
  • Amazon
  • …….

MariaDB

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

常用数据类型

  • 整数型：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

数据库管理

  • 忘记数据库的root密码怎么办？

    vim /etc/my.conf.d/server.conf

    [server]
    skip-grant-tables #添加

    #重启数据库

    #无密码登陆数据库

  • 创建或修改密码初始化数据库

    ╭─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)

  • 设置与更改用户密码

    SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword')

    #如果是当前登陆用户
    SET PASSWORD = PASSWORD("newpassword");

  • 查看创建数据库的状态

    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

  • 修改表字段；不重命名（modify）

    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 [test]> 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',
    -> lost bit(1));

  • 表结构

    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 | |
    +----+--------+------+------+--------+------+

  • 数字类型数据可以使用运算符

  • in（' ',' ',' '）语法的使用

    update huluwa set age=age+1 where name in('大娃','二娃');

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

    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)

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章