Simple use of mariadb

SQL statement Classification:

Ddl:data defination Language Data Definition language

Create,drop,alter

Create a Delete modification

Dml:data maipulation Language Data Modification language

Insert,delete,update,select

Insert Delete Modify Query

GRANT: Permissions Added

REVOKE: Permission Delete

Data type:

Character type:

Fixed-length character type: CHAR (#) does not distinguish between character size, BINARY (#) Case-sensitive, #号为可选项, you can specify the length

Variable-length character type: VARCHAR (#) does not differentiate between character case, VARBINARY (#) character case, #号为可选项, can specify length

Object storage:

TEXT: Size Not distinguished

BLOB: Distinguishing character case

Built-in type:

Enum: Enum type

Set: Set

Numeric type :

Precise numerical type:

Integral type: int

Tinyint:1byte

Smallint:2bytes

Mediumint:3bytes

Int:4bytes

Bigint:8bytes

Decimals: Decimal

Myopia Numerical Type:

Single-precision floating-point type:

Float

Double-precision floating-point type:

Double

date and Time type :

Date: Date

Time:

Date Time: datetime

Time Stamp: TIMESTAMP

Vintage: Year (2), years (4)

modifier :

All types are used:

Not NULL, non-null constraint

Default value, setting defaults

PRIMARY Key, PRIMARY key

Unique key

Numeric use:

UNSIGNED, unsigned, or positive

Auto_increment, self-increment field

Database operation statements:

To Create a database :

CREATE database| SCHEMA [IF not EXISTS] ' db_name ';

Cases:

MariaDB [(None)]> CREATE database if not EXISTS mydb;//This command produces a new database named MyDB if the MyDB database does not exist

To Delete a database :

DROP database| SCHEMA [IF EXISTS] ' db_name ';

Cases:

MariaDB [(None)]> drop database if EXISTS mydb;//this command results in: If mydb exists, delete this

Set the default database :

Use db_name;

MariaDB [(None)]> use MyDB;

set the character set:

CHARACTER set ' CHAR set NAME ';

Cases:

MariaDB [(None)]> CHARACTER SET gb2312; Set the character set to gb2312

to view all supported character sets :

SHOW CHARACTER SET;

Cases:

MariaDB [(None)]> SHOW CHARACTER SET; View available Character sets

To set the collation :

COLLATE ' COLLATE NAME ';

Cases:

MariaDB [(None)]> COLLATE Big5_bin; Set Collation to Big5_bin

to view all supported collations :

SHOW COLLATION;

Cases:

MariaDB [(None)]> SHOW COLLATION; View all supported collations

get command Use Help :

Help KEYWORD;

Cases:

MariaDB [(None)]> help CREATE DATABASE; Get help about the CREATE DATABASE command

View database Information : show DATABASES;

View table Information : show TABLES;

Note: The delete operation is irreversible and unrecoverable.

Table Action Statement:

To Create a table :

CREATE TABLE [IF not EXISTS] [db_name.] Tb_name (col1 datatype modifier, col2 datatype modifier ...) [Engine= '];

Cases:

MariaDB [mydb]> Create TABLE IF not EXISTS mytb1 (ID int. not null,name varchar (+) not null,age int);//db_name bit database name, Gen  When you build a table, you need to specify which database the table belongs to, and if you set a default database, you do not need to use this command//col1 for Column name//datatype for data type//modifier null,not null,primary key,unique KEY, etc.// Engine for Data Engine mariadb [mydb]> CREATE TABLE students (ID int UNSIGNED not NULL primarykey,name VARCHAR (a) not null,age Tin Yint UNSIGNED);//The Definition ID field is an unsigned integer and is required to be non-null, and the primary key//Definition name field is a variable character with a length limit of 20, and requires a non-empty//defined age field to be unsigned tinyint

You can define a single field as the primary key, or you can define multiple fields as primary keys, as long as multiple fields are combined differently.

Cases:

MariaDB [mydb]> CREATE TABLE tbl2 (id int UNSIGNED not null,name VARCHAR (a) not null,age tinyint unsigned,primary KEY ( Id,name));//PRIMARY key (Id,name)//This defines the ID and name at the same time as the primary key, as long as the id+name is not repeated

Get help:

MariaDB [mydb]> help CREATE TABLE;

Engine is the database engine, also known as the table type, the View table Type List command is as follows:

MariaDB [mydb]> SHOW ENGINES;

Table storage Format:

Row_format [=] {default| dynamic| fixed| compressed| Redundant| COMPACT}

View Table :

SHOW TABLES [from db_name];

Cases:

MariaDB [mydb]> SHOW TABLES from MyDB;

To view the table structure :

MariaDB [mydb]> DESC [db_name.] Tb_name;

Cases:

MariaDB [mydb]> DESC mydb.mytb1;

To view the table creation command :

MariaDB [mydb]> SHOW CREATE tbale tbl_name;

Cases:

MariaDB [mydb]> SHOW CREATE TABLE mytb1;

View table Status:

MariaDB [mydb]> SHOW TABLE STATUS like ' tbl_name ' [\g]

Cases:

MariaDB [mydb]> show TABLE STATUS like myTB1 \g;//\g indicates vertical display

Delete Table : Note: The delete operation is irreversible and unrecoverable.

DROP TABLE [IF EXISTS] tb_name;

Cases:

MariaDB [mydb]> DROP TABLE IF EXISTS mytb1;

To Modify a table operation :

With the ALTER TABLE command, there are many subcommands under this command

To Add a field :

ALTER TABLE tbl_name ADD col1 data_type [first| After Col_name]

Cases:

MariaDB [mydb]> ALTER Table Students add Age ENUM (' m ', ' f ') first; Add a field to the students table age, whose character type is enumerated, only M or F, and is in field one

The ADD subcommand can also add constraints to a field separately

Cases:

Maria DB [mydb]> ALTER TABLE students ADD UNIQUE KEY (name);

To delete a field :

ALTER TABLE students DROP COLUMN col_name;

Cases:

MariaDB [mydb]> ALTER Table students drop COLUMN age; Delete the age field from the students table

Modify field Names and Field Properties : (Cannot modify field properties individually)

ALTER TABLE Students Change col_name col_name_new data_type options;

Cases:

MariaDB [mydb]> ALTER Table Students change age ages int unsiggned not NULL; The field that modifies the students table is ages, and an unsigned type of int is set and cannot be is empty

Note: Be aware that when the change command modifies a field property, the field name must be modified, the field property cannot be modified only, and the field name will not be modified, but the field property may not be modified when the field name is modified.

To Modify Field properties :

ALTER TABLE MODIFY col_name data_type options;

Cases:

MariaDB [mydb]> ALTER TABLE MODIFY age int; modify only Field properties

Index:

An index is a special data structure that defines a field that is used as a lookup condition when it is found; accelerates the query speed, with the disadvantage of occupying extra space

The index can have an additional name, and if the name is not set and is a single-key index, the index name defaults to the field name

To create an index :

CREATE INDEX index_name on Tbl_name (Index_col_name,...);

Cases:

MariaDB [mydb]> CREATE INDEX Myindex on students (ID);//Here is an index for the ID field of the students table and named MYINDEXMARIADB [mydb]> create I Ndex Myindex2 on students (age,name);//Here is a federated index created for students to express the age and Name fields and named Myindex2

To delete an index :

DROP INDEX index_name on Tbl_name;

Cases:

MariaDB [mydb]> Drop Index myindex on students;//here is the delete table students named Myindex index

ALTER TABLE tbl_name DROP INDEX (col_name);

Cases:

MariaDB [mydb]> ALTER Table Students Drop index (age);//This is the delete table on the age field in students.

To add an index :

ALTER TABLE tbl_name ADD INDEX (col_name);

MariaDB [mydb]> ALTER Table Students add index (age);//This is the Age field for table students.

View Index :

SHOW INDEXES from [Db_name.] Tbl_name;

MariaDB [mydb]> show INDEXES from mydb.students;//here is to show all indexes in MyDB table under Students database

Data manipulation:

Insert Data :

INSERT [into] tbl_name [(Col_name,...)] (VALUES | VALUE) (VAL1,...), (...),...

Cases:

MariaDB [mydb]> INSERT INTO students VALUES (1, ' Li ', ' m '), (2, ' WA ', ' f '); You can insert data for all fields at once MariaDB [mydb]> insert into Students (Sid,name) VALUES (3, ' ZZ '), (4, ' DD '), or you can insert data from a specified field

Query Data :

SELECT col1,col2,... from Tbl_name [WHERE clause] [ORDER by ' col_name ' [DESC]] [LIMIT [m,]n];

ORDER BY ' col_name ' in ascending order by col_name

ORDER BY ' col_name ' DESC sorted by col_name descending order

ORDER BY ' Col_name ' DESC LIMIT 4 is sorted in descending order of col_name, showing only 4

ORDER BY ' Col_name ' DESC LIMIT is sorted in descending order of col_name, and is offset by 1, showing 2

Col_name use ' * ' to refer to all fields

As: Field alias, col1 as Alias_name

Cases:

MariaDB [mydb]> Select Sid as Stuid,name as stuname from students, query all field data for SID and name in students table, and temporarily rename the SID to Stusid, Rename name to Stuname temporarily

Operator:

(1) <,>,>=,<=,==,!=

(2) between ... And ...

Cases:

MariaDB [mydb]> SELECT * from students WHERE sid>=2 and sid<=4; MariaDB [mydb]> SELECT * from students WHERE SID between 2 and 4;//the above two examples have the same effect, all data that is deleted by the SID is greater than or equal to 2 and less than or equal to 4.

(3) Like

Wildcard:% denotes any character of any length;

_ denotes any single character;

Cases:

MariaDB [mydb]> SELECT * from students where name is like ' z% ';//This represents the data in the students table that looks for any character of any length beginning with Z in the Name field

(4) Rlike

Character matching based on regular expression

Cases:

MariaDB [mydb]> SELECT * from students where name Rlike '. *u.* ';//This means finding data in the students table that contains the U letter in the Name field

(5) is null empty

is not NULL for non-null

Cases:

MariaDB [mydb]> SELECT * from students where name is null;//this represents the data in the students table that is empty for the name field MariaDB [mydb]> SELECT * FR OM students where name is not null;//this represents the data in the students table that finds the Name field as non-empty

Conditional logical Operation:

And and

OR OR

Not non-

Cases:

MariaDB [mydb]> SELECT * from students where SID >=2 and Sid <= 4;//here is a Lookup students table in which the SID is less than or equal to 4 and is greater than or equal to 2 of the data MariaDB [my  Db]> SELECT * from students where SID >=2 or name = = ' Zi ';//Here is a Lookup students table in which SID is greater than or equal to 2 or name is Zi data MariaDB [mydb]> SELECT * from students where not SID >=2;//this means finding data in the students table that is less than 2, that is, sid>=2 reverse

Delete Data :

DELETE from Tbl_name [WHERE clause] [ORDER by ' col_name ' [DESC]] [LIMIT [m],n];

Cases:

MariaDB [mydb]> Delete from students where Sid = = 3;//here to delete all row data for SID 3 in students table

Note: It should be noted that if the delete from Tbl_name is not followed by a conditional statement, the data for the entire table is deleted by default.

Update Data :

UPDATE tbl_name SET Col1=new_val1,col2=new_val2,... [WHERE clause] [ORDER by ' col_name ' [DESC]] [LIMIT [M],n];

Cases:

MariaDB [mydb]> UPDATE students SET name= ' Zi ', age=16 where id = = 4;//here indicates that the name field of the row with ID 4 in the Modify students table is zi,age to 16

User account and Rights management:

User account Format: [email protected]

USERNAME: User Name

Host: Which hosts are allowed to remotely create connections when this user accesses the MYSQLD service: which can be IP, hostname, wildcard (% and _);

To create a user :

CREATE USER [email protected] [identified by ' PASSWORD '];

Cases:

MariaDB [mydb]> Create user [e-mail protected] identified by ' redhat '; this means creating a person with a user name of User1 and a password of redhat, and this user can only log on by native

To delete a user:

DROP USER ' [email protected];

Cases:

MariaDB [mydb]> Drop User [email protected]; This means deleting users who can log on natively User1

User Authorization :

Permissions classification: Management, database, tables, fields, storage routines (stored procedures, stored functions, triggers), etc.;

GRANT Priv_type,... On [object_type] db_name.tb_name to ' [e-mail protected] ' [identified by ' PASSWORD '] [with GRANT OPTION];

Give specified permissions to the specified user at the specified login location in the specified table in the specified database;

Options:

Priv_type:all | [Privileges],all represents all permissions

Db_name.tb_name:

*. *: All tables for all databases;

Db_name.*: Make all the tables of the library;

Db_name.tb_name: Specifies the specified table for the library;

Db_name.routing_name: Develop a storage routine for the library;

With GRANT OPTION: means that the specified user can delegate all of his or her rights to other users;

Cases:

MariaDB [mydb]> GRANT Select,delete on mydb.students to [email protected];// This means that the query and delete permissions for the students table in the MyDB database are given to User1 users who can only log on natively

Note: It should be noted that if the user does not exist, this can be created by adding identified by password to the specified user and specifying the password at the same time

To View User Rights :

SHOW GRANTS for [email protected];

View permissions for a specified user

SHOW GRANTS for Current_User;

View permissions for the current user

Cases:

MariaDB [mydb]> show GRANTS for [email protected]//here to view permissions restricting user user1 for native logins

Reclaim user Rights :

REVOKE Priv_type,... On db_name.tb_name from [email protected];

Cases:

MariaDB [mydb]> REVOKE DELETE on mydb.students from [email protected];// This means that the delete permission for the students table in the MyDB database is reclaimed from the User1 permissions of the user who restricted the native login

Note: When the MARIADB service process starts, it reads all the authorization tables in the MySQL database into memory;

(1) grant or revoke EXECUTE permission operation will be saved in the authorization table, MARIADB service process will automatically reread the authorization table;

(2) for the failure or inability to reread the authorization form in time after the execution of the command, you can manually let the MARIADB service process reread authorization form;

Reread Authorization Form command: FLUSH privileges;

Write the more scribbled, if there are missing errors and controversies, welcome everyone's criticism and discussion, thank you.

Simple use of mariadb