MariaDB数据库 ----数据库简介,用户管理,数据库创建,数据类型、数据增删改(实例演示)
数据库简介
数据库--即电子文件柜,用户可以对文件中的数据进行增,删,改,查等操作。
数据库分类
- 关系型数据库
关系型数据库管理系统(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密码怎么办?
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)