Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the basic knowledge of MySQL, I hope you will gain something after reading this article, let's discuss it together!

MySQL Foundation

Start MySQL net start mysql

Close MySQL net stop mysql

Log in to mysql-uroot-p

Exit mysql > exit

Mysql > quit

Mysql >\ p

Show the current server version SELECT VERSION ()

Show current time SELECT NOW ()

Show current user SELECT USER ()

The default port number for MySQL is: 3306

The MySQL superuser is: root

Create a database: CREATE DATABASE

Modify the database: ALTER DATABASE

Delete database: DROP DATABASE

Data type

Integer: TINYINT SMALLINT MEDIUMINT INT BIGINT

Floating-point type: FLOAT [(M ~ D)] DOUBLE [(M ~ D)] M is the total number of digits, D is the number of digits after the decimal point

Character type: VERCHAR (M)

Data sheet

Check what database is available: SHOW DATABASES

Open database: USE database name

View the current database: SELECT DATABASES

Create a datasheet: CREATE TABLE [IF NOT EXISTS] table_name (

AgeTINYINT UNSGINED (unsigned bits)

.

)

View the list of data tables: SHOW TABLES [FORM db_name]; use form to view tables in other databases

View datasheet structure: SHOW COLUMNS FORM tbl_name

Insert record; INSERT [INTO] tbl_name [(coi_name,...)] VALUES (VAL,...)

Search record: SELECT expr,...FORM tbl_name (WHERE.)

Null value and non-null value

CREATE TABLE [IF NOT EXISTS] table_name (

Age1 TINYINT UNSGINED (unsigned bits) NOT NULL

Age2 TINYINT UNSGINED (unsigned bits) NULL,// can be empty by default

.

)

Automatic numbering AUTO_INCREMENT

1 is automatically numbered and must be used in combination with the primary key

2 by default, the starting value is 1 and the increment is 1

Primary key PRIMARY KEY

1 there can be only one primary key per table

The primary key ensures the uniqueness of the record

The primary key is automatically NOT NULL

CREATE TABLE [IF NOT EXISTS] table_name (

Age1 TINYINT UNSGINED (unsigned bits) PRIMARY KEY

.

)

Unique constraint UNIQUE KEY

Default constraint: DEFAULT

Update record UPDATE

Syntax: UPDATE tb_name SET age=age+10 WHERE name= chaihuo

Delete record FELETE

Syntax: DELETE FORM tb_name WHERE name= chaihuo

Query result grouping GROUP BY

Syntax: SELECT sex FORM users BY sex

HCAING grouping condition

Syntax: SELECT sex,age FORM users BY sex HAVING age > 35

Main: there are only two cases after HAVING at this time: 1) aggregate function 2) after select

Sort query results ORDER BY

Syntax: SELECT * FORM users ORDER BY id DESC

Operators and functions

Character operator

CONCAT () character connection

CONCAT_WS () uses the specified delimiter for character concatenation

FORMAT () digit format

LOWER () UPPER () converted to lowercase / uppercase letters

LEFT () RIGHT () gets the left / right character

LENGTH () gets the string length

SUBSTRING () string interception

[NOT] LIKE pattern matching

REPLACE () string substitution

Numeric operators and functions

CEIL () carry one round off

DIV integer division

FLOOR () rounding off

MOD remainder

POWER () power operation

ROUND () is rounded

Compare operators and functions

[NOT] BETWEEN...AND... [NOT] IN () is [not] NULL

Information function

CONNECTION_ID () connects to id

DATEBASE () current database

LAST_INSERT_ID () last inserts the Id number of the record

USER () current user

VERSON () version information

Aggregate function

Average of AVG ()

COUNT () count

MAX () MIN () SUM ()

Encryption function

MD5 () Information Summary algorithm

PASSWORD () cryptographic algorithm

Subqueries and joins

Write the query results to the data table

INSERT [INTO] tbl_name [(col_name,...)] SELECT...

Example: INSERT tdb_goods (cate_name) SELECT good_cake FORM table GROUP BY good_cake

Multi-table update

UPDATE table1 INNER JOIN table2 ON table1_name=table2_name SET table1_id=table2_di

CREATE...SELECT

Create a data table and write the query results to the data table

CREATE TABLE table_name [(create_definine)] select_statement

Example: CREATE TABLE table1 (

Id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT

Name VARCHAR (20) NOT NULL)

SELECT SELECT good_cake FORM table GROUP BY good_cake

)

Storage engine

By modifying the MySQL configuration file

1)-ddfault-storage-engine=engine

2) through the command to create a data table

CREATE TABLE [IF NOT EXISTS] table_name (

Age1 TINYINT UNSGINED (unsigned bits) NOT NULL

Age2 TINYINT UNSGINED (unsigned bits) NULL,// can be empty by default

.

) ENGINE=engine

Through the command to modify the data table.

-ALTER TABLE table_name ENGINE [=] engine_name

Storage engine

MyISAM: storage is now up to 256TB and supports indexing. Table level locking, data compression

InnoDB: storage is limited to 64TB, supporting transactions and indexes. The lock particles are row locks.

After reading this article, I believe you have a certain understanding of "what are the basics of MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.