Shulou(Shulou.com)06/01 Report--

Hello, Hi. What I bring to you today is "the use of common sentences in MariaDB (MySQL)".

Description

1. MySQL statements are not case-sensitive, and "parameters" are case-sensitive in Unix or Unix-like (Linux) systems.

2. Most MySQL statements end with ";"; if it is not written at the end, "MySQL will wait for the next statement to be entered until it ends with"; "

3. One line needs to be executed with multiple statements separated by ";"

The content of this article takes the Linux system as an example; in this article, [the content in the symbol is optional, either optional], {the content in the symbol is the content that must be given}, the symbol is or the meaning of the symbol; the symbol # before the statement is the command prompt of the Linux system (to be executed under the system), and the symbol > is the MySQL database prompt (to be executed in the MySQL database) The color of the font is red is the place to pay attention (there may be misoperation)

Warm Tip: it is better to read with a computer.

Classification of MariaDB (MySQL) statements

Server commands: send to the server for execution through a mysql connection and retrieve the results (SQL statement)

DDL (Data Defined Language): to be used to manage database components, such as tables, indexes, views, users, stored procedures

CREATE DATABASE, ALTER DATABASE, DROP DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, CREATE USER, DROP USER, SELECT

DML (Data Manipulation Language): a data manipulation language that mainly manages data in tables to add, delete, modify and query data.

INSERT, DELETE, UPDATE, SELECT

DCL: rights management

GRANT, REVOKE

Client commands: local execution

The following statement can be expressed in two ways (clear,\ c). There is no need to add it at the end of the statement, but it will not make an error.

Statement

Description

? (?)

Get command help

Clear (\ c)

Clear the current input statement

Connect (\ r)

Reconnect MySQL

Delimiter (\ d)

Sets the statement separator, which defaults to a semicolon

Ego (\ G)

Statement closing tag, and the result is displayed vertically

Exit (\ Q)

Exit MySQL

Go (\ g)

Statement end tag

Help (\ h)

Show this help

Prompt (\ R)

Change the MySQL prompt

Quit (\ Q)

Exit MySQL

Source (\.)

Load and run the SQL script. The location where the script is stored is "MySQL". Users need to have permission to read it.

Status (\ s)

Get MySQL status information

System (\!)

Execute the shell command

Use (\ u)

Set which library is the default database

Log in / out of MySQL

Log in

Log in to the local database

Anonymous login

# mysql [Database name]

# mysql-p [database name]

Specify user login

# mysql-u user name-p [database name]

# mysql-u user name-p user's password [database name]

Remote login to the database

# mysql-h host-u user name-p user's password [database name]

Note: the database "[DATABASE_NAME]" can be specified when logging in.

Quit

> exit

> quit

>\ Q

Ctrl+C

Second, select / switch databases: all operations performed in the database must first select the database

USE database name

III. Database management

Create

Create a new database

> CREATE {DATABASE | SCHEMA} database name

Create a new database, or if the database does not exist, create

CREATE DATABASE IF NOT EXISTS database name

Note: the database name is defined at creation time and cannot be changed later.

Modify

Modify the default character set of the specified database

ALTER DATABASE database name CHARACTER SET 'character set name'

Query

View existing databases on the current device

> SHOW DATABASES

View a database that begins with a specified character

> SHOW DATABASES LIKE 'name%'

View the database ending with the specified character

> SHOW DATABASES LIKE'% name'

View the engines supported by the database

> SHOW ENGINES

View supported character sets

> SHOW CHARACTER SET

Common character set

Big5: traditional Chinese

Gb2312: simplified Chinese

Utf8: a unified character coding standard

View how character sets are sorted

> SHOW COLLATION

View the version number and current date of the current database

> SELECT VERSION (), CURRENT_DATE

Delete

Delete database: be careful when deleting the database (MySQL does not have a recycle bin)

> DROP {DATABASE | SCHEMA} database name

> DROP {DATABASE | SCHEMA} [IF EXISTS] database name

It is recommended to move unused databases to another location instead of deleting them.

# mv / var/lib/mysql/DATABASE_NAME / PATH/TO/DIRECTORY_NAME

IV. Data sheet management

Create

Create a data table within the database

USE database name

> CREATE TABLE datasheet name (field 1 data type modifier, field 2 data type modifier, field 3 data type modifier.) [table_option]

MySQL data type reference: click me to check

You can also define the following when you create a datasheet:

Define key

Primary key: PRIMARY KEY (field 1, field 2,...)

Unique key: UNIQUE KEY (field 1, field 2.)

Foreign key: FOREIGN KEY (column)

Define index

KEY | INDEX [index name] (field 1, field 2.)

CREATE TABLE server (IDC_ROOM VARCHAR (3) NOT NULL,RACK_SLOT VARCHAR (10) NOT NULL, SN VARCHAR (30) PRIMARY KEY,HOSTNAME VARCHAR (16) NOT NULL,MACHINE_TYPE VARCHAR (10) NOT NULL)

Table_option:

| | ENGINE [=] engine_name: [define storage engine] |

| | AUTO_INCREMENT [=] value: [sets the default auto-growth data to be calculated from the number, default is 1] |

| | [DEFAULT] CHARACTER SET [=] charset_name: [defines a character set and inherits it from the database if it is not defined] |

| | [DEFAULT] COLLATE [=] collation_name: [define sorting rules] |

| | MAX_ROWS [=] value [defines the maximum number of rows] |

| | MIN_ROWS [=] value [defines the minimum number of rows] |

| | PASSWORD [=] 'string' [encryption] |

| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} [define line format]

DEFAULT: default format

DYNAMIC: dynamic format

FIXED: fixed length

COMPRESSED: compressed Stora

REDUNDANT: redundant Stora

COMPACT: compact Stora

The key type can be indicated in the field

You can also specify separately: define multiple fields together (multi-field index)

> CREATE TABLE data table name (ID TINYINT NOT NULL AUTO_INCREMENT, NAME VARCHAR (20) NOT NULL,UNIQUE KEY (ID,NAME))

Creates a data table in the specified database

> CREATE TABLE database name. Datasheet name (field 1 data type modifier, field 2 data type modifier, field 3 data type modifier.) [table_option]

insert

Insert data into the data table

Insert one row at a time

> INSERT INTO [database name.] Datasheet name (field 1, field 2, field 3.)

Insert multiple rows at a time

> INSERT INTO [database name.] Data table name (field 1, field 2, field 3.) {VALUES | VALUE} (DATE1: 'value of field 1', 'value of field 2', value of field 3 '...), (DATE2:' value of field 1', 'value of field 2', 'value of field 3'...).

Be careful

(1) using insert statement, you can insert one row at a time, or insert multiple rows at a time "(line1_data), (line2_data)..."

(2) if you want to insert a null value with an INSERT statement, you can directly use NULL

Replace the data already in the table: if there is no data, insert; if there is already data, replace (single row insert, multi-row insert, given field insert, or full field insert is supported)

> REPLACE [INTO] Datasheet name (field 1, field 2, field 3...) VALUES ('value of field 1', 'value of field 2', 'value of field 3', 'value of field 4', 'value of field 5', 'value of field 6') [, (line2_data)...]

Read data from a file and insert it into a table: an empty table, an easy way to fill it is to create a text file with one line for each piece of information, and then load the contents of the file into the table with a statement

> LOAD DATA LOCAL INFILE'/ PATH/TO/ FILE_NAME' INTO TABLE [database name.] Data Table name

Note: if you need to fill in a null value, use\ N (backslash + letter N) to indicate

I demonstrated importing from the file to the database. I have modified the sensitive data in the table.

Insert data from an EXCEL table: click me to view

Modify

Modify the name of the data table

> RENAME TABLE [database name.] Data table name TO database name. New table name

> ALTER TABLE [database name.] Data table name RENAME TO database name. New table name

Modify comment information

> ALTER TABLE [database name.] Data table name COMMENT = 'comment information'

Add a new field to the table

> ALTER TABLE data table name ADD new field name data type modifier [FIRST | AFTER field name]

Indicate the location of the field

FIRST: adding to first field

AFTER: which field is added after

If you do not specify the field location, put it at the end

Modify field name (CHANGE)

> ALTER TABLE datasheet name CHANGE original name new name original attribute information (data type, modifier.)

Modify / update specified fields

> UPDATE [LOW_PRIORITY] [IGNORE] [database name] Data table name SET field 1 = the value of field 1 [, field 2 = the value of field 2]. WHERE where_condition [ORDER BY...] [LIMIT row_count]

Note: when you modify a line with UPDATE, you must use the WHERE clause to specify the condition (primary key as far as possible), otherwise all will be modified.

Query

Query the data table in the current database

USE database name

> SHOW TABLES

Query the data table in the specified library

SHOW TABLES IN database name

SHOW TABLES FROM database name

View the details of the data table

> DESCRIBE [database name.] Data Table name

> DESC [database name.] Data Table name

View the status information of the data table

> SHOW TABLE STATUS [LIKE 'data Table name']

View the status information of the data table (displayed vertically)

> SHOW TABLE STATUS [LIKE 'data Table name']\ G

Description of the output content

View the create command

> SHOW CREATE TABLE [database name.] Data Table name

Displays the status of tables that begin with the specified character

> SHOW TABLE STATUS WHERE Name LIKE 'Datasheet name'\ G

Displays the table created at the specified time

> SHOW TABLE STATUS WHERE Create_time LIKE 'time%'\ G

SELECT statement: used to retrieve information from a data table

> SELECT SELECT_EXPR FROM [database name.] Datasheet name 1, [Datasheet name 2.] [WHERE where_condition]

The execution process of the clause of the SELECT statement

SELECT_EXPR

*: all fields (all rows, all columns)

ALL

Field 1, field 2, field 3: projection; only specified fields are displayed

DISTINCT: same data, displayed only once

Field AS field_alias: give field an individual name field_alias

Only view different data in the table

> SELECT DISTINCT FROM [database name.] Data Table 1, [data Table 2.]

View all fields in the specified table (use with caution)

> SELECT * FROM [database name.] Datasheet name 1, [Datasheet 2.]

> SELECT Field 1, Field 2, Field 3. FROM [database name.] Data Table name

Define the field aliases that are displayed

> SELECT field AS field alias FROM [database name.] Data Table name

FROM clause: specifies the relationship to query

Single table query (simple query): followed by a table name

Multi-table query: followed by multiple table names

Subquery: followed by the WHERE clause

WHERE clause: select and specify conditions; output only rows that meet the criteria (important)

> SELECT Field 1, Field 2, Field 3. FROM [database name.] Data Table name WHERE where_condition

Where_condition:

Operator

BETWEEN x AND y

Values between x and y

IS NULL

Null value

IS NOT NULL

Non-null value

IN (LIST)

To use the values given in a list; for discrete values.

Arithmetic operation

+

Add

-

Minus

*

Multiply

/

Except

%

Take the mold

Comparative expression

>

Greater than

=

Greater than or equal to

ORDER BY field name {ASC | DESC}

Ascending order: default (ASC)

> SELECT Field 1, Field 2, Field 3. FROM [database name.] Data Table name ORDER BY Field name ASC

Descending order (DESC)

> SELECT Field 1, Field 2, Field 3. FROM [database name.] Data Table name ORDER BY Field name DESC

Note: the default is case-sensitive. If you don't want to, execute "ORDER BY BINARY field name".

LIMIT clause

LIMIT [offset,] count

LIMIT #: only the first # lines are displayed

LIMIT xQuery y: ignore x lines and show the lines after y lines

X: offset

Y: take several values to display after offset

Note: strings must be in quotation marks in MySQL, but numeric values must not be in quotation marks.

Aggregate computing

> SELECT aggregate calculation function FROM [database name.] Data Table name

Aggregate calculation function

AVG (field name): averaging

MAX (field name): find the maximum

MIN (field name): minimizing

SUM (field name): fit

COUNT (field name): sum the number

Grouping (GROUP BY): the main purpose of grouping is to do aggregate computing

> SELECT * FROM [database name.] Data Table name GROUP BY 'Field name'

Filter packets

> HAVING conditions

Copy

Copy table structure

> CREATE TABLE data table name LIKE original database. Original data table

Copy several fields in the table and create a new table (its properties are not copied)

> CREATE TABLE data Table name SELECT Field 1, Field 2, Field 3. FROM original database. Original data table

Delete

Delete tables: be careful when deleting tables (MySQL does not have a Recycle Bin)

> DROP TABLE [IF EXISTS] [database name.] Data Table name

Delete rows from the table (DELETE)

> DELETE FROM [database name.] Datasheet name [WHERE where_condition]

> DELETE FROM data table name [ORDER BY...] [LIMIT row_count]

Note: you must use the WHERE clause when deleting rows with DELETE, otherwise the entire table (DELETE FROM table_name) will be emptied.

Sort before deleting

> DELETE FROM [database name.] Data table name [WHERE where_condition] [ORDER BY 'field name' [DESC]]

Delete field (column)

ALTER TABLE data table name DROP field name

Empty the whole data sheet

> TRUNCATE [database name.] Data Table name

V. Index management

Create

Create an index

> CREATE {INDEX | INDEXES} Index name ON [database name.] Datasheet name (field 1, field 2, field 3.)

Create a multi-column index

> CREATE UNIQUE {INDEX | INDEXES} Index name ON [database name.] Data Table name

Index the fields specified in the table

> ALTER TABLE [database name.] Data table name ADD INDEX ('field name')

Query

View indexes in a table

> SHOW {INDEX | INDEXES} FROM [database name] Data Table name

Delete: delete immediately when the index is not in use

Delete index: this may result in reduced query operations

> DROP {INDEX | INDEXES} Index name ON [database name.] Data Table name

Note: INDEX is one index and INDEXES is multiple indexes

VI. Key management

Create

(1) define the primary key when the data table is created

(2) add a primary key to the fields specified in the existing table

ALTER TABLE [database name.] Data table name ADD {PRIMARY KEY | UNIQUE KEY} ('field name')

Modify

Delete the key before adding the key

Query

Query primary key

> SHOW KEYS FROM [database name.] Data Table name WHERE key_name='PRIMARY'

Delete

Delete key: it can be deleted only if it is not self-growing. If it is self-growing (with auto_increment attribute), you must delete other attributes in the table first.

> ALTER TABLE [database name.] Data table name DROP {PRIMARY KEY | UNIQUE KEY}

VII. User management

MySQL users are different from users in the operating system; you cannot log in to the operating system with MySQL users, nor can you use operating system users to access MySQL

The user is public and everyone can see it, and the password can only be obtained by an authorized user.

MySQL uses its own unique encryption mechanism, the password () function, to encrypt user passwords

The default administrator of MySQL is root, which is consistent with the name of the operating system administrator

MySQL users are composed of 'user name' @ 'host'

MySQL user name: cannot exceed 16 characters

Mainframe

Hostname: zhangdaifu

IP address: 10.0.0.1

Network address: 10.0.0.1swap 255.0.0.0

It is supported to use wildcard characters to indicate: 10.% ..accountables255.0.0.0

If you want to express the meaning of the wildcard itself, you need to escape\

Create

Create a user account

> CREATE USER 'username' @ 'host'

Create a user account and set its password

> CREATE USER 'username' @ 'host' IDENTIFIED BY 'password'

Modify

Modify user name

> UPDATE mysql.user SET USER=' new user name 'WHERE User=' username'

Query: MySQL users save in mysql.user table

View all user information

> SELECT User,Host [,...] FROM mysql.user

View specified user information

> SELECT User,Host [,...] FROM mysql.user WHERE User=' user name'

Delete

Delete user account

> DROP USER 'username' [@ 'host' [, user name @ host].]

VIII. Password management

Create

Configure the user with a password

Configure a password for the currently logged in user

> SET PASSWORD = PASSWORD ("user's password")

Modify

Modify from the command line

# mysqladmin-u 'username'-p 'old password' password 'new password'

Edit the user table directly using UPDATE

> password of UPDATE mysql.user SET PASSWORD= user, WHERE User=' username'

Refresh permission: flush privileges

Modify through SET PASSWORD statement

> SET PASSWORD FOR user name = PASSWORD ('user's password')

Reference: https://blog.csdn.net/qq_33285112/article/details/78982766

Query

> SELECT User,Password FROM mysql.user WHERE User=' user name'

Delete

Clear the specified user password

> UPDATE mysql.user SET PASSWORD='' WHERE User=' user name'

IX. Authority management

MySQL uses the account for "authentication".

MySQL user information is stored in the following table

User

Stores the user's account, global permissions and other non-permission fields

Db

Database-level permissions, library-level permissions

Host

Has been discarded and integrated into the user table

Tables_priv

Permissions at the table level

Columns_priv

Permissions at the column (field) level

Procs_priv

Permissions related to stored procedures and stored functions

Proxies_priv

Proxy user rights

When the MySQL server starts up, it reads these six tables and generates an "authorization table" in memory.

Authorization

Permission levels: administrative permissions, databases, datasheets, fields, storage routines

> GRANT permissions_type,... ON [object_type] {[database name.] Data table name | function_name | procedure_name} TO 'user_name'@'host' [IDENTIFIED BY' password'] [WITH GRANT OPTION]

> flush privileges

Permission type

ALL: all permissions; not secure

Database_name.table_name

*. *: all tables of all libraries

Database. *: specify all tables of the library

Database. Datasheet: the specified table that specifies the library

Database .routine _ name: specify a stored procedure or stored function on the library

[object_type]

TABLE: authorizes datasheets; default

FUNCTION: authorizes storage functions

PROCEDURE: authorize stored procedures

[WITH GRANT OPTION]: transfer a copy of the permissions you have to other users

Query

View the permissions that the current user has

> SHOW GRANTS

View the permissions that the specified user has

> SHOW GRANTS FOR 'username' @ 'permission type'

Delete

Recycling authorization

> REVOKE permission type [,...] ON [database name.] Data table name FROM 'username' @ 'host'

Note: when the MariaDB service process starts, all authorization tables of the mysql library are read into memory

(1) permission operations performed by GRANT or REVOKE commands are saved in the table. MariaDB usually rereads the authorization table automatically, and the permission modification takes effect immediately.

(2) for permission modifications implemented in other ways, you must run the FLUSH PRIVILEGES command manually to take effect.

Check that the previous statement is executed correctly (similar to the Shell variable "$?")

> SHOW WARNINGS

Common mistakes

(1) ERROR 1046 (3D000): No database selected

No database selected

(2) ERROR 1050 (42S01): Table 'table1' already exists

Table 'table1' already exists

(3) ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Log in to the database without entering a password

(4) ERROR 1317 (70100): Query execution was interrupted

Query execution interrupted

More reference: http://www.bluestep.cc/demos/mysql/manual/index.htm

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.