Basic knowledge in MySQL

Clear screen: cls

MySQL basics

Start MySQL net start mysql

Close MySQL net stop mysql

Log in mysql -uroot -p

Exit mysql>exit;

mysql>quit;

mysql>\p;

Display the current server version SELECT VERSION();

Display the current time SELECT NOW();

Display the current user SELECT USER();

The default port number of MySQL is: 3306

MySQL super user Yes: root

Create database: CREATE DATABASE

Modify database: ALTER DATABASE

Delete database: DROP DATABASE

Data type

Integer type: 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 table

Check what databases there are: SHOW DATABASES;

Open the database: USE database name

View the current database: SELECT DATABASES;

Create data table: CREATE TABLE[IF NOT EXISTS]table_name(

ageTINYINT UNSGINED (unsigned bit),

... ...

)

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

View the data table structure: SHOW COLUMNS FORM tbl_name;

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

Find record: SELECT expr,.. .FORM tbl_name（WHERE .....）;

Null value and non-null

CREATE TABLE[IF NOT EXISTS]table_name(

age1 TINYINT UNSGINED(unsigned Bit) NOT NULL,

Automatic numberAUTO_INCREMENT

1Automatic numbering and must be used in combination with the primary key

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

Primary key PRIMARY KEY

1Each table can only have one primary key

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 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 Conditions

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

Mainly: At this time, there are only two situations behind HAVING 1) Aggregation function 2) Behind select

Sort query results ORDER BY

Syntax: SELECT * FORM users ORDER BY id DESC;

Operators and functions

Character operators

CONCAT()Character connection

CONCAT_WS()Use the specified delimiter for character connection

FORMAT()Number formatting

LOWER()UPPER( ) Convert to small/uppercase letters

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

LENGTH() Get the string length

SUBSTRING() String interception

[NOT] LIKE pattern matching

REPLACE() string replacement

Numeric operators and functions

CEIL() rounding

DIV Integer division

FLOOR()Rounding

MOD Remainder

POWER() Power operation

ROUND()Rounding

Comparison operators and functions

[NOT] BETWEEN...AND...                                                                                                                                                                                                                                          

#CONNECTION_ID() Connection id

DATEBASE()Current database

LAST_INSERT_ID()The Id number of the last inserted record

USER()Current user

VERSON()Version information

Aggregation function

AVG()Average

COUNT()Count

MAX() MIN() SUM()

Encryption function

MD5() information digest algorithm

PASSWORD() password algorithm

Subquery and connection

Convert the query results Write 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;

Multiple table update

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

CREATE...SELECT

Create the data table while The query results are written 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;

)

                                                                                                                                                        Achieved by modifying the MySQL configuration file

1)-ddfault-storage-engine=engine

2)  Achieved by creating a data table command

CREATE TABLE[IF NOT EXISTS]table_name(

age1 TINYINT UNSGINED (unsigned bit) NOT NULL,

age2 TINYINT UNSGINED (unsigned bit) NULL,//The default is OK Empty

            …                                                                                  

                                                                                                              

                                                                       ]engine_name;

Storage Engine

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

InnoDB: Storage limit is 64TB, supports transactions and indexes. The lock granule is row lock

The above is the detailed content of Basic knowledge in MySQL. For more information, please follow other related articles on the PHP Chinese website!