MySQL Study Notes

MySQL Study Notes
1. Relational Database Management System (RDBMS)

1.1 The Three paradigm paradigms of relational databases are simply the level of design standards that a table structure of a data table conforms. 1NF: Each attribute in the 1NF-compliant relationship cannot be further divided.

2NF: Based on 1NF, 2NF eliminates some function dependencies of non-primary attributes on codes.

3NF: Based on 2NF, 3NF eliminates the dependence of non-primary attributes on code passing functions.

Simple understanding: the first paradigm is that attributes are inseparable. (Attribute is the field in the table)

Second paradigm: to have a primary key, other fields must depend on the primary key. (Appendix: primary key judgment method. If the primary key is determined, all other keys are determined)

Third paradigm: to eliminate transmission dependencies and facilitate understanding, we can regard it as "eliminating redundancy ".

1.2. term database: Table set data table: data matrix row: column: primary key: The primary key is unique. A table can only contain one primary key. It is mainly used for query. Foreign key: Used to associate two tables. Index: The structure in which data in one or more columns is sorted.

2. Install the MySQL-MySQL server on the MySQL Linux/UNIX platform. You need this option unless you only want to connect to the MySQL server running on another machine. The MySQL-client-MySQL client program is used to connect to and operate the Mysql server. MySQL-devel-library and inclusion files. If you want to compile other MySQL clients, such as the Perl module, you need to install this RPM package. MySQL-shared-This package contains the shared Library (libmysqlclient. so *) that needs to be dynamically loaded by some languages and applications, using MySQL. MySQL-benchmark-MySQL database server benchmark and performance testing tool.

Download the installation package on Windows

3. Data Type

Type	Size	Range (Signed)	Range (unsigned)	Purpose	Remarks
TINYINT	1 byte	(-128,127)	(0,255)	Small integer
SMALLINT	2 bytes	(-32768,327 67)	(0, 65535)	Large integer
MEDIUMINT	3 bytes	(-8388608,838 8607)	(0, 16777215)	Large integer
INT or INTEGER	4 bytes	(-2147483648,214 7483647)	(0, 4294967295)	Large integer
BIGINT	8 bytes	(-9233372036854775808,922 3372042554775807)	(0, 18446744073709551615)	Maximum integer
FLOAT	4 bytes	(-3.402823466E + 38, 1.175494351E-38), 0, (1.175494351E-38, 3.402823466351E + 38)	0, (1.175494351E-38, 3.402823466E + 38)	Single-precision floating point value
DOUBLE	8 bytes	(1.7976931348623157E + 308, 2.225074255072014e-308), 0, (2.225074255072014e-308, 1.7976931348623157E + 308)	0, (2.225074255072014e-308, 1.7976931348623157E + 308)	Double-precision floating point value
DECIMAL	For DECIMAL (M, D), if M> D, it is M + 2; otherwise, it is D + 2.	Values dependent on M and D	Values dependent on M and D	Small value
DATE	3 bytes	April 1000-01-01/9999-12-31		Date Value	YYYY-MM-DD
TIME	3 bytes	'-838: 59: 59'/'2014: 59: 59'		Time Value or duration	HH: MM: SS
YEAR	1 byte	1901/2155		Year Value	YYYY
DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59		Mixed Date and Time values	YYYY-MM-DD HH: MM: SS
TIMESTAMP	8 bytes	2037 00:00:00/		Mixed Date and Time Value, timestamp	YYYYMMDD HHMMSS
CHAR	0-255 bytes			Fixed Length string
VARCHAR	0-255 bytes			Variable-length string
TINYBLOB	0-255 bytes			A binary string of no more than 255 characters
TINYTEXT	0-255 bytes			Short text string
BLOB	0-65535 bytes			Long text data in binary format
TEXT	0-65535 bytes			Long text data
MEDIUMBLOB	0-16777215 bytes			Moderate-length text data in binary format
MEDUIMTEXT	0-16777215 bytes			Moderate-length text data
LONGBLOB	0-4294967295 bytes			Extremely large text data in binary form
LONGTEXT	0-4294967295 bytes			Large text data

4. Common commands

Category	Description	Command	Remarks
Connection	Login Server	Mysql-h1.2.3.4-u root-p
Display	Library list	SHOW DATABASES
	Table list	SHOW TABLES
	Table Structure	Show create table table_name DESCRIBE table_name
	Table attribute information	Show columns from table_name
	Table index information	Show index from table_name
	Table status information	Show status like 'table _ name'
	Server Status Information	SHOW STATUS
	Server Configuration Information	SHOW VARIABLES
	Server version information	Select version ()
	Current Database Name	Select database ()
	Current User Name	Select user ()
Select	Library	USE database_name
New	Library	Create database database_name
	Table	Create table table_name (column_name column_type)
	Temporary table	Create temporary table table_name (column_name column_type)	The temporary table is only valid for the current connection. After the connection is disconnected, the temporary table is automatically destroyed.
Insert	Line	Insert into table_name (field1, field2,... fieldN) VALUES (Value1, value2,... valueN)
Delete	Library	Drop database database_name
	Line	Delete from table_name [WHERE clause_etc]
	Table	Drop table table_name
Query	Line	SELECT field1, field2,... fieldN FROM table_name1, table_name2,... [WHERE clause_etc] [offset m] [limit n]
Query (join table)	Row (inner join)	Implicit: SELECT table1.column table2.column FROM table1, table2 WHERE table1.column2 = table2.column2 explicit: SELECT table1.column FROM table1 inner join table2 ON table1.column2 = table2.column2	Only select update delete is supported.
	Row (outer left join)	SELECT table1.column FROM table1 left join table2 ON table1.column2 = table2.column2	All records in Table 1 are retained. If no corresponding record exists in Table 2, it is left blank by default.
	Row (outer right join)	SELECT table1.column FROM table1 right join table2 ON table1.column2 = table2.column2	All records in Table 2 are retained. If no corresponding record exists in table 1, it is left blank by default.
Update	Line	UPDATE table_name SET field1 = value1, field2 = value2 [WHERE clause_etc]
Modify	Add Column	Alter table table_name ADD column_name typeALTERTABLE table_name ADD column_name type FIRST ALTERTABLETable_name ADD column_name type AFTER column2	Add to the last column (default) after adding to the first column to the specified column column2
	Delete column	ALTERTABLE table_name DROP column_name
	Modify column (type)	ALTERTABLE table_name MODIFY column_name type	Only modify the column Field Type
	Modify column (name)	ALTERTABLE table_name CHANGE old_column column_name type	Modify the column field name and type at the same time
	Table	Alter table table_name rename to table_name2	Modify Table Name
Conditions	Offset	OFFSET offset_number
	Number of rows	LIMIT limit_number
	Any condition	WHERE clause_etc
	Regular Expression	WHERE column REGEXP 'exp'
Sort	Ascending	ASC
	Descending Order	DESC
Index	Column	CREATE [UNIQUE] INDEX index_name ON table_name (column (length) ALTER table_name ADD [UNIQUE] INDEX [index_name] ON (column (length) drop index [index_name] ON table_name	If the column is CHAR or VCHAR, the length can be smaller than the field length. If the column is BLOB or TEXT, the length must be specified.
	Column (combination)

5. In the operator table, assume A = 10 B = 20.

Operator	Description	Instance
=	Equal sign: checks whether two values are equal. If yes, returns true.	(A = B) returns false.
<> ,! =	If not, check whether two values are not equal. If yes, return true.	(! = B) returns true.
>	Greater than: checks whether the left value is greater than the right value. If yes, returns true.	(A> B) returns false.
<	If the value is less than, check whether the value on the left is less than the value on the right. If yes, return true.	(A <B) returns true.
> =	If the value is greater than or equal to, check whether the value on the left is greater than or equal to the value on the right. If yes, return true.	(A> = B) returns false.
<=	If the value is less than or equal to, check whether the value on the left is less than or equal to the value on the right. If yes, return true.	(A <= B) returns true.
IS NULL	Check whether the value is NULL
IS NOT NULL	Check whether the value is not NULL

6. Connector

Operator	Description	Remarks
AND	Two conditions are connected, indicating that the conditions on the left and right must be both true and the result is true.
OR	Concatenates two conditions, indicating that either of the conditions on the left and right is true, and the result is true.
LIKE	Condition matching, usually used with % to achieve fuzzy query	WHERE feild LIKE '% abc'

7. Regular Expression

Mode	Description	Remarks
^	Match the start position of the input character. If the Multiline attribute of the RegExp object is set, ^ matches the position after '\ n' or' \ R.
$	Matches the end position of the input character. If the Multiline attribute of the RegExp object is set, ^ matches the position before '\ n' or' \ R.
[...]	Character Set combination. Match any character in it.	For example, '[abc]' can match 'A' in 'plain '.
.	Match any single character except '\ n.	To match any character including '\ n', use a pattern like' [. \ n.
[^...]	Negative value character set combination. Match any character not included.	For example, '[^ abc]' can match 'P' 'l''' in 'plain'
{N}	N is a non-negative integer. Match the n times specified by the previous expression.	For example, 'O {2} 'can match 'food'
{N, m}	M n is a non-negative integer, where n <= m. Match the expression at least n times and at most m times.
P1 | p2	Match p1 or p2.	For example, 'z | food' can match 'Z' or 'food'
*	Matches the previous subexpression zero or multiple times. Equivalent to {0 ,}	For example, 'Zo * 'can match 'Z' 'osz '.
+	Match the previous subexpression once or multiple times. Equivalent to {1 ,}

8. A transaction is used to ensure that all SQL commands are executed. If a transaction fails, it rolls back to the status before execution. Transactions must meet four conditions (ACID): Atomicity (Atomicity), Consistency (stability), Isolation (Isolation), durability (reliability) in MySQL, transactions are supported only for databases or tables that use the Innodb database engine.

9. The sequence uses the keyword AUTO_INCREMENT to define an integer column auto-increment.

10. To prevent SQL injection, pay attention to the following points:

Never trust users' input. User input can be verified by regular expressions or limited lengths. Single quotes and double "-" can be converted.

Never use dynamic assembled SQL statements. You can use parameterized SQL statements or directly use stored procedures for data query and access.

Never use a database connection with administrator permissions, and use a database connection with limited permissions for each application.

Do not store confidential information directly, encrypt or hash the password and third information.

The application exception information should be provided with minimal prompts. It is best to use custom error information to wrap the original error information.

The SQL injection detection method is generally used by the auxiliary software or website platform, and the software generally uses the SQL Injection detection tool.