数据库简介
数据库--即电子文件柜，用户可以对文件中的数据进行增，删，改，查等操作。
数据库分类
- 关系型数据库
关系型数据库管理系统（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=1MariaDB数据类型
常用数据类型
- 整数型：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)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
