MySQL Database related knowledge

What is a database?

A database is a repository of data that is organized, stored , and managed according to a structure that is built on a computer's storage device. (From: Baidu)

What is SQL?

The Structured Query Language (structured query Language) is called SQL (pronunciation:/?es kju?? el/"S-q-l"), is a special purpose programming language, is a database query and programming language , for accessing data and querying , update and manage the relational database system , and also the extension of the database script file . (From: Baidu)

The RDBMS is the feature of the relational database management system (relational Management Systems):

• 1. The data appears in tabular form
• 2. Various record names for each behavior
• 3. Data fields corresponding to record names for each column
• 4. A number of rows and columns form a single sheet
• 5. A number of forms form database

installation of MySQL

MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by the Oracle company. MySQL's most popular relational database management system, MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software in WEB applications.

To use MySQL to store and manipulate data, you need to follow the steps in step by step:
A. Install the MySQL server (download URL: https://www.mysql.com/downloads/)

B. Installing the MySQL Client

B. "Client" Connection "server Side"

C. "Client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate operation (increase and deletion of the search, etc.)

For specific installation, click:Windows system MySQL installation

mac system MySQL installation

linux system MySQL installation

For basic use of the database after installation, please refer to:

　Basic operation Diagram of database

MySQL data type :　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

The type of data field defined in MySQL is important for optimizing your database, and it can support multiple types, broadly divided into three categories: numeric, date/time, and string (character) types.

Numeric Type

MySQL supports all standard SQL numeric data types.

These types include strict numeric data types (INTEGER, SMALLINT, Decimal, and numeric), as well as approximate numeric data types (FLOAT, real, and double PRECISION).

The keyword int is a synonym for integer, and the keyword Dec is a synonym for decimal.

The bit data type holds the bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

As an extension of the SQL standard, MySQL also supports integer types tinyint, Mediumint, and bigint. The following table shows the storage and scope of each integer type that is required.

type	size	Range (signed)	Range (unsigned)	Use
TINYINT	1 bytes	(-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 233 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 value
DECIMAL	For decimal (m,d), if m>d, is m+2 otherwise d+2	Values that depend on M and D	Values that depend on M and D	Decimal value

date and Time type

The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.

Each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.

The timestamp type has a proprietary Automatic Update feature, which is described later.

type	size (bytes)	Range	format	Use
DATE	3	1000-01-01/9999-12-31	Yyyy-mm-dd	Date value
Time	3	' -838:59:59 '/' 838:59:59 '	HH:MM:SS	Time Value or duration
Year	1	1901/2155	YYYY	Year value
Datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	Blend date and time values
TIMESTAMP	4	1970-01-01 00:00:00/2038 End time is 2147483647 second, Beijing time 2038-1-19 11:14:07, GMT January 19, 2038 03:14:07	YYYYMMDD HHMMSS	Mixed date and time values, timestamp

String Type

The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.

type	size	purpose
CHAR	0-255 bytes	fixed length string
VARCHAR	0-65535 words Section	variable length string
tinyblob	0-255 bytes	No more than 255 characters of binary string
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	binary Form medium-length text data
mediumtext	0-16 777 215 bytes	medium-length text data
longblob	0-4 294 967 295 bytes	binary form of large text data
longtext	0-4 294 967 295 bytes	Extreme Text data

Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.

Binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string. This means that they do not have a character set, and sort and compare numeric values based on column-valued bytes.

A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

　　

　 Here is a brief introduction to some basic operations :

1.MySQL Database Basic Operation

　　　　Create a database

A. Creating a database using Mysqladmin

1, CREATE database name;

2. GRANT select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';

3, SET PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');

Execute 3 commands in turn to complete the database creation. Note: The Chinese "password" and "database" are the user's own needs to set.

　　　　Deleting a database

DROP database name;

　　　　Select Database

Use database name;

　　2.MySQL Data Sheet basic Operation

　　　1. CREATE table name (' column name ' type);　　　　　

In the following example we will create a data table Runoob_tbl in the Runoob database:

CREATE TABLE IF not EXISTS' Runoob_tbl '(  　　　　　　　　   ' runoob_id 'INT UNSIGNED auto_increment, 　　　　　　　　 ' Runoob_title 'VARCHAR (100)  not null,  ' runoob_author '  Varchar (40< Span class= "pun" >)  not null,  Submission_d Ate '  Date, PRIMARY KEY  (  ' runoob_id '  )   engine=innodb DEFAULT charset= utf8            

Instance parsing:

• • • If you do not want the fields to be null , you can set the field's property to not null, and you will get an error if the data entered in the field is null when you manipulate the database.
    • Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.
    • The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.
    • The engine sets the storage engines and CHARSET sets the encoding.

　　　2. Delete a table

　　　　　　　DROP TABLE table_name;

　　　　3, modify the table

　　　　　　DROP Table name rename new table name;

DROP Table name Modify/change/drop property name/Type

　　3.MySQL Delete and change (CRUD) data Operation

Add: INSERT into table name (' values for each column ');

Delete: delete from table name;

Modified: Update table name set column name = value;

Query: Select column name from table name;

To learn more about MySQL's basic knowledge, please click here: more Knowledge

MySQL Database related knowledge