What is a DATABASE
Efficient media for storing and processing data (disk and memory)
It is a warehouse built on computer storage equipment to organize, store and manage data according to data structure.
In short, it can be regarded as an electronic filing cabinet - the place where electronic files are stored. Users can add, intercept, update, delete and other operations on the data in the file.
Classification of database
By storage medium:
Relational database (sql)
It refers to a database that uses a relational model to organize data. It stores data in the form of rows and columns for user understanding. A series of rows and columns in a relational database are called tables, and a group of tables form a database. The user retrieves the data in the database through a query, which is an executive code used to limit some areas in the database. Relational model can be simply understood as two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships.
advantage:
- Easy to understand: the two-dimensional table structure is a concept very close to the logical world. The relational model is easier to understand than other models such as mesh and hierarchy.
- Easy to use: the general SQL language makes it very convenient to operate the relational database.
- Easy to maintain: rich integrity (entity integrity, reference integrity and user-defined integrity) greatly reduces the probability of data redundancy and data inconsistency.
Non relational database (nosql)
The most common explanation of NoSQL is "non relational" and "Not Only SQL" is also accepted by many people. NoSQL is just a concept, which generally refers to non relational databases. Different from relational databases, they do not guarantee the ACID characteristics of relational data. NoSQL is a new revolutionary movement of database. Its advocates advocate the use of non relational data storage. Compared with the overwhelming use of relational database, this concept is undoubtedly an injection of new thinking.
advantage:
- Easy to expand
- Large amount of data, high performance
- Flexible data model
- High availability
DML (data manipulation language)
There are mainly the following operations on the data of the database
select query
select Column name from Table name
Update update
update Table name set Updated data where condition
Insert insert
insert into table_name (column 1, column 2,...) values; ( Value 1, value 2,...)
Delete delete
delete from Table name where Column name = value
DDL (data definition language) database definition language
DDL is mainly used to define or change the table structure, data type, links and constraints between tables
For example:
Create create
Create table
create table Table name
(
Column name 1 Data type,
Column name 2 Data type,
Column name 3 Data type,
...
);
Create database
create database Database name
alter modification
alter table students change column birth birthday date;
alter table student rename students;
drop delete
drop table Table name;
drop database Database name;
DCL (Data Control Language):
It is a statement used to set or change database user or role permissions, including (grant,revoke, etc.) statements. This is rarely used.
mariadb
MariaDB database management system is a branch of MySQL, which is mainly maintained by the open source community. The purpose of adopting GPL license is to be fully compatible with MySQL, including API and command line, so that it can easily become a substitute for MySQL. In terms of storage engine, XtraDB (English: XtraDB) is used to replace MySQL InnoDB. MariaDB was developed under the leadership of Michael Widenius (English: Michael Widenius), the founder of MySQL. Earlier, he sold his company MySQL AB to SUN for $1 billion. Since then, with SUN being acquired by Oracle, the ownership of MySQL has also fallen into Oracle's hands. The name of MariaDB comes from the name of Maria, the daughter of Michael Widenius.
User management and access control
Create database login user
MariaDB [openlab]> create user xixi@localhost identified by 'xixi'; Query OK, 0 rows affected (0.001 sec)
View the user currently logged in to the database
MariaDB [openlab]> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.000 sec)
View the database of the current user
MariaDB [openlab]> select database(); +------------+ | database() | +------------+ | openlab | +------------+ 1 row in set (0.000 sec)
Log out of the database using xixi user
[root@redhat ~]# mysql -uxixi -pxixi
view the database
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.001 sec)
Log out, log in to the database with root user, and set permissions for xixi user
[root@redhat ~]# mysql -uroot -proot MariaDB [(none)]> grant select,update,insert,delete on openlab.student to xixi@localhost; Query OK, 0 rows affected (0.001 sec)
xixi user logs in to the database again
[root@redhat ~]# mysql -uxixi -pxixi
see
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | openlab | +--------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> use openlab; 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 [openlab]> select * from student; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 5 | mabo | 20 | nan | 1998-07-07 | +--------+---------+------+------+------------+ 4 rows in set (0.000 sec)
Test insert permissions
MariaDB [openlab]> insert into student(number,name,age,sex,birth) values (4,"zhangsan",100,"nan","100-01-01"); Query OK, 1 row affected (0.001 sec) MariaDB [openlab]> select * from student; +--------+----------+------+------+------------+ | number | name | age | sex | birth | +--------+----------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 5 | mabo | 20 | nan | 1998-07-07 | | 4 | zhangsan | 100 | nan | 0100-01-01 | +--------+----------+------+------+------------+ 5 rows in set (0.000 sec)
Test update permissions
MariaDB [openlab]> update student set age=19 where number=4; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [openlab]> select * from student; +--------+----------+------+------+------------+ | number | name | age | sex | birth | +--------+----------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 5 | mabo | 20 | nan | 1998-07-07 | | 4 | zhangsan | 19 | nan | 0100-01-01 | +--------+----------+------+------+------------+ 5 rows in set (0.000 sec)
Test delete permission
MariaDB [openlab]> delete from student where number=4; Query OK, 1 row affected (0.001 sec) MariaDB [openlab]> select * from student; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 5 | mabo | 20 | nan | 1998-07-07 | +--------+---------+------+------+------------+ 4 rows in set (0.000 sec)
Backup and restore
Backup data
[root@redhat ~]# mysqldump -u root -p openlab > /openlab_backup_20210904.dump Enter password:
The root user logs in to the database and deletes the table
[root@redhat ~]# mysql -uroot -proot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25 Server version: 10.3.28-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)]> use openlab; 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 [openlab]> drop table student; Query OK, 0 rows affected (0.112 sec) MariaDB [openlab]> select * from student; ERROR 1146 (42S02): Table 'openlab.student' doesn't exist
Exit for restore operation
[root@redhat ~]# mysql -u root -p openlab < /openlab_backup_20210904.dump Enter password:
Log in to the database again with root and check whether the table is restored
[root@redhat ~]# mysql -uroot -proot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 27 Server version: 10.3.28-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)]> use openlab; 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 [openlab]> select * from student; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 5 | mabo | 20 | nan | 1998-07-07 | +--------+---------+------+------+------------+ 4 rows in set (0.000 sec)
Set character set
There are generally two methods to set the character set: one is to set the character set when creating the table, and the other is to modify the character set after the table is completed.
1. Specify character set when creating
Specify the character set when creating the Library:
Syntax: create database database name default character set = character set;
create database db2 default character set=utf8
Specify the character set when creating the table:
Syntax: create table table name (attribute) default character set = character set;
mysql> create table test(id int(6),name char(10)) default character set = 'gbk'; Query OK, 0 rows affected (0.39 sec)
2. Modify character set
Modify global character set
/The code used to establish the connection/
set character_set_connection=utf8;
/Coding of database/
set character_set_database=utf8;
/Encoding of result set/
set character_set_results=utf8;
/Encoding of database server/
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
Modify the character set of the library
Syntax: alter database database name default character set character set;
alter database shiyan default character set gbk;
mysql> show create database shiyan\G
*************************** 1. row ***************************
Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql> alter database shiyan default character set gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database shiyan\G
*************************** 1. row ***************************
Database: shiyan
Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)
Modify the character set of the table
Syntax: alter table name convert to character set character set;
alter table test1 convert to character set utf8;
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(6) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk #Original character set
1 row in set (0.00 sec)
mysql> alter table test1 convert to character set utf8;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(6) DEFAULT NULL,
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #Modified character set
1 row in set (0.00 sec)
Modify the character set of the field
Syntax: alter table table name modify field name field attribute character set gbk;
alter table test1 modify name char(10) character set gbk;
mysql> show full columns from test1; +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(6) | NULL | YES | | NULL | | select,insert,update,references | | | name | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.01 sec) mysql> alter table test1 modify name char(10) character set gbk; Query OK, 0 rows affected (0.58 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show full columns from test1; +-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(6) | NULL | YES | | NULL | | select,insert,update,references | | | name | char(10) | gbk_chinese_ci | YES | | NULL | | select,insert,update,references | | +-------+----------+----------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.01 sec)
case
Create a table
Install the database (the system is installed by default. If it is not installed, the command is as follows)
[root@redhat ~]# yum install mariadb -y
Start database service
[root@redhat ~]# systemctl restart mariadb
Initialize the database and set the root password
[root@redhat ~]# mysql_secure_installation
Login database
[root@redhat ~]# mysql -uroot -proot
Create database
MariaDB [(none)]> create database openlab;
Enter openlab database
MariaDB [(none)]> use openlab;
Create student table
MariaDB [openlab]> create table student(number int,name varchar(20),age int,sex varchar(3),birth date);
View table
MariaDB [openlab]> show tables; +-------------------+ | Tables_in_openlab | +-------------------+ | student | +-------------------+ 1 row in set (0.001 sec) MariaDB [openlab]> desc student; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | number | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | sex | varchar(3) | YES | | NULL | | | birth | date | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.001 sec)
Insert data into a table
MariaDB [openlab]> insert into student(number,name,age,sex,birth) values (1,"wangkai",22,"nan","1996-02-02"); Query OK, 1 row affected (0.003 sec) MariaDB [openlab]> insert into student(number,name,age,sex,birth) values (2,"lili",21,"nv","1997-03-03"); Query OK, 1 row affected (0.001 sec) MariaDB [openlab]> insert into student(number,name,age,sex,birth) values (3,"kaili",21,"nv","1997-04-04"); Query OK, 1 row affected (0.001 sec) MariaDB [openlab]> insert into student(number,name,age,sex,birth) values (4,"wangkai",20,"nv","1998-05-05"); Query OK, 1 row affected (0.001 sec) MariaDB [openlab]> insert into student(number,name,age,sex,birth) values (5,"mabo",20,"nan","1998-02-02"); Query OK, 1 row affected (0.001 sec)
View the contents of the table
MariaDB [openlab]> select * from table; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table' at line 1 MariaDB [openlab]> select * from student; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 4 | wangkai | 20 | nv | 1998-05-05 | | 5 | mabo | 20 | nan | 1998-02-02 | +--------+---------+------+------+------------+ 5 rows in set (0.001 sec)
Query all students aged 20
MariaDB [openlab]> select * from student where age=20; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 4 | wangkai | 20 | nv | 1998-05-05 | | 5 | mabo | 20 | nan | 1998-02-02 | +--------+---------+------+------+------------+ 2 rows in set (0.001 sec)
Query the information about the boy named Wang Kai in the class
MariaDB [openlab]> select * from student where name="wangkai" ; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 4 | wangkai | 20 | nv | 1998-05-05 | +--------+---------+------+------+------------+ 2 rows in set (0.000 sec)
Change the birth of Mabo to 1998-7-7
MariaDB [openlab]> update student set birth="1998-07-07" where name="mabo"; Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [openlab]> select * from student where name="mabo"; +--------+------+------+------+------------+ | number | name | age | sex | birth | +--------+------+------+------+------------+ | 5 | mabo | 20 | nan | 1998-07-07 | +--------+------+------+------+------------+ 1 row in set (0.000 sec)
Delete student number 4
MariaDB [openlab]> delete from student where number=4; Query OK, 1 row affected (0.001 sec) MariaDB [openlab]> select * from student; +--------+---------+------+------+------------+ | number | name | age | sex | birth | +--------+---------+------+------+------------+ | 1 | wangkai | 22 | nan | 1996-02-02 | | 2 | lili | 21 | nv | 1997-03-03 | | 3 | kaili | 21 | nv | 1997-04-04 | | 5 | mabo | 20 | nan | 1998-07-07 | +--------+---------+------+------+------------+ 4 rows in set (0.000 sec)
List the names of all students in the table
MariaDB [openlab]> select name from student; +---------+ | name | +---------+ | wangkai | | lili | | kaili | | mabo | +---------+ 4 rows in set (0.001 sec)
List the name and age of student No. 3
MariaDB [openlab]> select number,name,age from student where number=3; +--------+-------+------+ | number | name | age | +--------+-------+------+ | 3 | kaili | 21 | +--------+-------+------+ 1 row in set (0.001 sec)