MariaDB Database Management System Installation and Use introduction,

MariaDB Database Management System Installation and Use introduction,

1. What is mariadb?

The MariaDB database management system is a branch of MySQL and is mainly maintained by the open-source community. The purpose of using GPL to authorize MariaDB is to be fully compatible with MySQL, including APIs and command lines, it can easily become a substitute for MySQL. In terms of storage engine, XtraDB is used to replace InnoDB of MySQL. MariaDB was developed by Michael Widenius, MySQL founder. He sold his own company MySQL AB to SUN for $1 billion, as SUN was acquired by Oracle, the ownership of MySQL also fell into the hands of Oracle. MariaDB is named by Michael Widenius's daughter Maria.

2 What are the installation methods?

It can be installed either in binary format or in yum format.

3. What are common components of a relational database?

Database: database

Table: table

Row: row

Column: column

Index: index

View: view

User: user

Permission: privilege

Stored procedure: procedure. The procedure has no return value.

Storage function: function, which has returned values.

Trigger: trigger

Event scheduler: event scheduler, Task scheduler

4 logon and command use?

Logon system: mysql> mysql-uroot-p

System help: mysql> help or \ h

View status: mysql> status or \ s

SQL statement:

DDL: Data Defination Language # Data definition command

CREATE, DROP, ALTER

DML: Data Manipulation Language # Data Processing Command

INSERT, DELETE, UPDATE

DCL: Data Control Language # Data Control statement

GRANT, REVOKE

DQL: Data Query Language # Data Query statement

SELECT

SQL syntax specification:

In the database system, SQL statements are case-insensitive (uppercase is recommended)

However, string constants are case sensitive.

An SQL statement can be written in one or more rows, ending ";".

Keywords cannot be cross-line or abbreviated

Improve statement readability with spaces and indentation

Clauses are usually located in independent rows for easy editing and readability.

Note:

SQL standard:

/Comment/multi-line comment

-Comment on a single line of content with spaces

MySQL notes :#

Must start with a letter

It can contain numbers and three special characters (#_$)

Do not use reserved MySQL words

Objects in the same Schema cannot have the same name.

5. create databases and tables, and add, delete, modify, and delete tables respectively:

Create a database:

Create database | SCHEMA [if not exists] 'db _ name ';

Delete database:

Drop database | SCHEMA 'db _ name ';

View All supported CHARACTER sets: show character set;

View all the supported sorting rules: show collation;

View the Database List: show databases;

Create a table:

Create table students (id int unsigned not null primary key, name VARCHAR (20) not null, age tinyint UNSIGNED); # CREATE a students TABLE with id, name, age columns and define their respective attributes.

Create table students (id int unsigned not null, name varchar (20) not null, age tinyint unsigned, primary key (id, name); # CREATE a students TABLE, the table contains three columns: id, name, and age. The primary key is defined as id and name at the end.

Table operations:

View All ENGINES: show engines;

View table: show tables [FROM DB_NAME];

View the TABLE structure: DESC [DB_NAME] TABLE;

Drop table 'tb _ name ';

Run the show create table 'tb _ name' command to CREATE a TABLE ';

Create table ddd (id int unsigned primary key, name varchar (20) not null, age tinyint unsigned not null );

Example:

1 alter table students RENAME s1;

2 alter table s1 ADD phone varchar (11) AFTER name;

3 alter table s1 MODIFY phone int;

4 alter table s1 change column phone mobile char (11 );

5 alter table s1 drop column mobile;

Index Syntax:

1. Create an index:

2 create index index_name ON tbl_name;

3. delete an index:

4 drop index index_name ON tbl_name;

5. view the index:

6 show indexes from [db_name.] tbl_name;

1 INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f'); 2 INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen'); 3 SELECT * FROM students WHERE id < 3; 4 SELECT * FROM students WHERE gender='m'; 5 SELECT * FROM students WHERE gender IS NULL; 6 SELECT * FROM students WHERE gender IS NOT NULL; 7 SELECT * FROM students ORDER BY name DESC LIMIT 2; 8 SELECT * FROM students ORDER BY name DESC LIMIT 1,2; 9 SELECT * FROM students WHERE id >=2 and id <=410 SELECT * FROM students WHERE BETWEEN 2 AND 411 SELECT * FROM students WHERE name LIKE ‘t%’12 SELECT * FROM students WHERE name RLIKE '.*[lo].*';13 SELECT id stuid,name as stuname FROM students 

Search for and sort data between 18-20 years old.

select * from table1 where age between 18 and 20 order by age ;

Search for data whose names start with c: % is all wildcards

select * from table1 where name like "c%"

In the student and emp tables, find the same ID and display its ID and corresponding name items.

select s.id e.name from student as s,emp as e where s.id=e.id;