[Buddhist notes]-mysql command memo

Posted Jun 26, 20203 min read

MYSQL

Introduction

MySQL is the most popular relational database management system. In terms of WEB applications, MySQL is one of the best RDBMS(Relational Database Management System:relational database management system) application software.

database

Structure

The database has a simple hierarchical relationship, namely, database-data table-data(row)

type of data

Numeric type
Type Size Range(signed) Range(unsigned) Uses
TINYINT 1 byte (-128, 127) (0, 255) Small integer value
SMALLINT 2 bytes (-32 768, 32 767) (0, 65 535) Large integer value
MEDIUMINT 3 bytes (-8 388 608, 8 388 607) (0, 16 777 215) Large integer value
INT or INTEGER 4 bytes (-2 147 483 648, 2 147 483 647) (0, 4 294 967 295) Large integer value
BIGINT 8 bytes (-9,223,372,036,854,775,808, 9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) maximum integer value
FLOAT 4 bytes (-3.402 823 466 E+38, -1.175 494 351 E-38), 0,(1.175 494 351 E-38, 3.402 823 466 351 E+38) 0,(1.175 494 351 E -38, 3.402 823 466 E+38) Single-precision floating-point value
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308, -2.225 073 858 507 201 4 E-308), 0,(2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E +308) 0,(2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) double precision
Floating-point values
DECIMAL For DECIMAL(M,D), if M>D, M+2, otherwise D+2 depends on the value of M and D depends on the value of M and D Decimal value
Date and time type
Type Size Use
DATE 3 1000-01-01/9999-12-31
TIME 3 '-838:59:59'/'838:59:59'
YEAR 1 1901/2155
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59
TIMESTAMP 4 1970-01-01 00:00:00/2038 End time is 2147483647 seconds, Beijing time 2038-1-19 11:14:07, GMT January 19, 2038 03:14 am 07
String type
Type Size Use
CHAR 0-255 bytes Fixed-length character string
VARCHAR 0-65535 bytes Variable-length character string
TINYBLOB 0-255 bytes Binary string up to 255 characters
TINYTEXT 0-255 bytes Short text string
BLOB 0-65 535 bytes Long text data in binary form
TEXT 0-65 535 bytes Long text data
MEDIUMBLOB 0-16 777 215 bytes Medium-length text data in binary form
MEDIUMTEXT 0-16 777 215 bytes Medium-length text data
LONGBLOB 0-4 294 967 295 bytes Very large text data in binary form
LONGTEXT 0-4 294 967 295 bytes Very large text data

grammar

CREATE

CREATE is used to create a database or data table

CREATE DATABASE <database name>;
CREATE TABLE <data table name>(column1_name column1_type, column2_name, column2_type,...);

USE

USE is used to select the database

USE <database name>;

DROP

DROP is used to delete a database or data table

DROP database <database name>;
DROP TABLE <data table name>;

DELETE

DELETE is used to delete the data in the data table

DELETE FROM table_name [WHERE Clause];
Delete all without where

INSERT INTO

INSERT INTO is used to insert data into the data table

 INSERT INTO table_name(field1, field2,...fieldN)
                       VALUES
                      (value1, value2,...valueN);

SELECT

SELECT statement is used to query data from the database

SELECT column_name,column_name FROM table_name;

UPDATE

The UPDATE statement is used to modify or update the data in MySQL

UPDATE table_name SET field1=new-value1, field2=new-value2;

Clause

Clause is a part of grammar. It cannot be used as a complete grammar itself, and must be used together with others. Can be used as a condition.

WHERE

The WHERE clause is used to conditionally select data from the table

SELECT * from runoob_tbl WHERE runoob_author='Rookie tutorial';

The LINK clause is used to further describe the condition. You can substitute =, LIKE is usually used with%, similar to a metacharacter search.%Character to represent any character.

SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]]filed2 ='somevalue';

ORDER BY

The ORDER BY clause is used for sorting. You can use the ASC or DESC keywords to set whether the query results are sorted in ascending or descending order. By default, it is sorted in ascending order.

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][default ASC]], [field2...][ASC [DESC][default ASC]];