The MySql command

The login

mysql -u root -p password
Copy the code

use

USEDatabase name;Copy the code

To view

// View all databasesSHOW DATABASES; // View database creation informationSHOW CREATE DATABASEDatabase name; // View the database currently in useSELECT DATABASE(a);Copy the code

create

// Create databaseCREATE DATABASEDatabase name; // Create database and set encoding;CREATEDATEBASE Database name1 CHARACTER SETgbk; // Enter the database. The database is created if it does not exist.CREATE DATABASE IF NOT EXISTSDatabase name2;
Copy the code

Modify the

ALTER DATABASEDatabase nameCHARACTER SET utf8;
Copy the code

delete

DROP DATABASEDatabase name;Copy the code

Database query

Basic query syntax

# Basic syntax
SELECTThe column nameFROMThe name of the tablePart # column
SELECTThe column name1And the column name2And the column name3 FROMThe name of the table# all columns
SELECT * FROMThe name of the tableCopy the code

The column operation

The column alias

SELECT 列 AS 'name' FROMThe name of the tableCopy the code

The query result is deduplicated

SELECT DISTINCTThe column nameFROMThe name of the tableCopy the code

Operation table query

Sorting query

SELECTThe column nameFROMThe name of the tableORDER BYRank [sort]Copy the code

collation	describe
ASC	Ascending order
DESC	Descending order

Conditions of the query

SELECTThe column nameFROMThe name of the tableWHEREconditionsCopy the code

conditions	describe
Value judgment	=
logic	And, or, not
Unequal judgment	>, >, >=, <=! =, < >
Interval judgment	Column name BETWEEN values 1 AND 2
A NULL value judgment	The column IS NULLorThe column name IS NOT NULL
Enumeration query	IN(value 1, value 3)
Fuzzy query	Column name LIKE ‘value 1_’ or column name LIKE’ tang %’

Branch structure query

CASE WHEN condition 1 THEN result 1 WHEN condition 2 THEN result 2 WHEN condition 3 THEN result 3 ELSE resultEND
Copy the code

Grouping query

SELECTThe column nameFROMThe name of the tableWHEREconditionsGROUP BYGrouping basis (column);Copy the code

In group query, SELECT columns that are grouped by or aggregate function columns

Group Filtering query

SELECTThe column nameFROMThe name of the tableWHEREConditions for GROUNPBYGrouping columnsHAVINGFiltering rulesCopy the code

The keyword	instructions
HAVING Filtering rule	Filtering rules filter the grouped data

Paging query

SELECT column name FROM table name LIMIT Number of rowsCopy the code

The subquery

conditional

SELECTThe column nameFROMThe name of the tableWHEREConditions (subquery results)Copy the code

• Note: Make the second query conditional on the result of the subquery “row, column” as a condition of the external query.
• Only row – column results obtained by sub-queries can be used as equal or unequal judgment conditions for external queries

Enumeration query criteria

SELECTThe column nameFROMThe name of the tableWHEREThe column nameIN(sub-query results);Copy the code

Use the result of the subquery “many rows, one column” as the enumeration query condition of the external query to do the second query

Note: The ANY or ALL keyword can be used when the subquery result set is multi-row and single-column

A table

SELECTThe column nameFROM(Result set of subquery)WHEREConditions;Copy the code

• Do a second query using the result of the subquery “many rows, many columns” as a table from the external query.
• Note: Subqueries are temporary tables and need to be given a temporary table name

Function queries

The query time

SELECTTime function ([parameter list])fromThe name of the tableCopy the code

Time function	describe
SYSDATE()	Current system time (day, month, year, hour, minute, second)
CURDATE()	Get the current date
CURTIME()	Get the current time
WEEK(DATE)	Gets the week of the year for the specified date
YEAR(DATE)	Gets the year of the specified time
HOUR(TIME)	Gets the hour value for the specified time
MINUTE(TIME)	Gets the minute value for the specified time
DATEDIFF(DATE1,DATE2)	Gets the number of days between DATE1 and DATE2
ADDDATE(DATE,N)	Calculate DATE N days after lock

Experience: Execute time function query, automatically generate a virtual table (row by column)

String query

SELECTString function ([argument list])fromThe name of the tableCopy the code

String function	instructions
CONCAT (str1 str2,…).	Concatenate multiple strings
INSERT(str,post,len,newStr)	Replace the len length from the specified post position in STR with newStr
LOWER(str)	Converts the specified string to lowercase
UPPER(str)	Converts the specified string to uppercase
SUBSTRING(str,num.len)	The STR string starts with len intercepts at the num position

Aggregation function

SELECTAggregate function (column name)FROMThe name of the table.Copy the code

Aggregation function	instructions
SUM()	Find the sum of the single column results in all rows
AVG()	The average
MAX()	The maximum
MIN()	The minimum value
COUNT()	Strives for the total number of rows

Experience: Perform statistics on a single column of multiple data and return the result of a row after statistics

Multi-table query

Merge query

SELECT * FROMThe name of the table1 UNION SELECT * FROMThe name of the table2
SELECT * FROMThe name of the table1 UNION ALL SELECT * FROMThe name of the table2
Copy the code

• Note: The resulting tables must have the same number of columns and may have different data types.

Table join query

SELECTThe column nameFROM 表1List of connection modes2 ONJoin conditionCopy the code

Multi-table join query

SELECTThe column nameFROM 表1The connection method1 表2 ONConnection condition Connection mode2 表3 ONJoin conditionCopy the code

Inner join query

SELECTThe column nameFROM 表1 INNER JOIN 表2 ONJoin conditionCopy the code

The left outer join

SELECTThe column nameFROM 表1 LEFT JOIN 表2 ONJoin conditionCopy the code

• Note: the left outer join takes the left table as the main table and matches it to the right in turn. The result will be returned if it is matched.
• If no match is found, NULL is returned

Right connection

SELECTThe column nameFROM 表1 RIGHT JOIN 表2 ONJoin conditionCopy the code

• Note: the right outer join, the right table is the main table, the left match, match to return the result.
• If no match is found, NULL is returned

Query summary

Order in which SQL statements are written

SELECTThe column nameFROMThe name of the tableWHEREconditionsGROUP BYgroupingHAVINGFilter conditionsORDER BYRow sequence (ASC|DESC) 
LIMITStart row, query the number of rowsCopy the code

SQL statement execution sequence

FROM: specify the data source table 2.WHERE: Filter the query data for the first time 3.GROUP BY 4Copy the code

DML operations

new

INSERT INTOThe name of the table (column1Column,2Column,3....).VALUES(value1And the value2And the value3......);
Copy the code

Note: The column name after the table name must correspond to the VALUE in the table name (number, order, type).

Modify the

UPDATEThe name of the tableSET 列1= value1Column,2= value2Column,3= value3 WHEREConditions;Copy the code

• Note: After SET, multiple column names = values. In most cases, add a WHERE condition to specify the change. Otherwise, the whole table will be updated.

delete

DELETE FROMThe name of the tableWHEREConditions;Copy the code

• Note: If no condition is added, the entire table is deleted by default.

Clear the entire table

TRUNCATE TABLEThe name of the table.Copy the code

• Note: Unlike DELETE, which deletes the entire table without adding WHERE, TRUNCATE destroys the original table and creates a new table in the format of the original table.

Data table operation

The data type

Numeric types

type	The size of the	Range (signed)	Scope (unsigned)	use
INT	4 bytes	(647-2147483648214483)	(0429967 295).	Large integer value
DOUBLE	8 bytes	E+308 (1.797, 2.22 e-308)	E-308 0, (2.22, 1.797 e+308)	Double – precision floating – point value
DOUBLE(M,D)	8 bytes, M for length, D for decimal number	Same as above, constrained by M and D; Such as DOUBLE (5, 2) to 999.99 ~ 999.99	Same as above, constrained by M and D	Double – precision floating – point value
DECIMAL(M,D)	DECIMAL(M,D)	Depending on the values of M and D, M is at most 65	Depending on the values of M and D, M is at most 65	Small numerical

The date type

type	The size of the	The scope of	format	use
DATE	3	The 1000-01-01/1000-01-01	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
DETETIME	8	The 1000-01-01 00:00:00/1000-01-01 23:59:59	YYYY-MM-DD HH:MM:SS	Mixes date and time values
TIMESTAMP	4	1970-01-01 00:00:00/2038 end time is no2147483647Second, Beijing timeThe 2038-1-19 11:14:07At 03:14:07 GMT on January 19, 2038	YYYYMMDD HHMMSS	Mix date and time values, time stamps

String type

type	The size of the	use
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-65535 bytes	Variable length string
BLOB (Binary Large Object)	0-65 535 bytes	Long text data in binary form
TEXT	0-65 535 bytes	Long text data

Table creation

CREATE TABLETable name (column name data type [constraint], column name data type [constraint],...... Column name data type [constraint]No comma at the end of the last line) [CHARSET=UTF8]The character encoding set of the table can be specified as required
Copy the code

Data table modification

ALTER TABLETable name operation;Copy the code

Add columns

ALTER TABLEThe name of the tableADDThe option parameter of the column name column;Copy the code

Modify the column

ALTER TABLEThe name of the tableMODIFYThe option parameter of the column name column;Copy the code

• Note: When modifying a column in a table, also write the name, data type, and constraint of the entire column.

Delete the column

ALTER TABLEThe name of the tableDROPThe column name.Copy the code

Note: When deleting columns, you can delete only one column at a time

Modify the column name

ALTER TABLEThe name of the tableCHANGEColumn name New column name option parameter for new column;Copy the code

• Note: When changing a column name, you specify the type and constraints of the column when given a new column name.

Modify the name of the table

ALTER TABLEThe name of the tableRENAMEThe new name of the table;Copy the code

Delete data table

DROP TABLEThe name of the tableCopy the code

The constraint

Entity integrity constraint

Primary key constraint

Identifies a row of data in a table. The value of this column is non-repeatable and cannot be NULL

PRIMARY KEY
Copy the code

The only constraints

Identifies a row of data in a table. It cannot be repeated and can be NULL

UNIQUE
Copy the code

Automatic growing column

Primary key numeric column adds automatic growth. You start at 1 and you increase by 1

AUTO_INCREAMENT
Copy the code

Domain integrity constraint

Not null constraint

NOT NULL
Copy the code

Default constraints

DEFAULT 
Copy the code

Referential integrity constraint

CONSTRAINT FOREIGN KEY REFERENCES Table nameCopy the code

• The FOREIGN KEY references the value of a column in an external table. When adding data, the value of this column must be a value that references the value in the table
• ** delete from primary table ** delete from secondary table ** delete from primary table

The transaction

# start transaction
START TRANSACTION;
Connect to localhost, commit transaction
COMMIT;
#localhost connection, transaction rollback
ROLLBACK;
Copy the code

Properties of transactions (ACID)

• Atomicity

  Indicates that all operations within a transaction are integrated and either all succeed or all fail.

• Consistency

  Indicates that when an operation fails within a transaction, all changed data must be rolled back to the modified state.

• Isolation

  A transaction looks at the state in which the data is at the time of a database operation, either before it was modified by another concurrent transaction or after it was modified by another transaction. A transaction does not look at the data in the intermediate state.

• They offer “Durability”

  After a persistent transaction completes, its impact on the system is permanent.

Rights management

Create a user

CREATE USERThe user nameIDENTIFIED BYpasswordCopy the code

authorization

GRANT ALL ONDatabases - TablesTOThe user name;Copy the code

Revoke permissions

REVOKE ALL ONDatabase. Table nameFROMThe user nameCopy the code

Note: Revoking permissions will only take effect if the account is reconnected to the client

Delete user

DROP USERThe user nameCopy the code

view

View creation

CREATE VIEWView nameASQuery data source table structure statement;Copy the code

View modification

A:CREATE OR REPLACE VIEWView nameASQuery statement Mode 2:ALTER VIEWView nameASThe queryCopy the code

View deletion

DROP VIEWView nameCopy the code

• Note: Deleting a view does not affect the original table.

View note

• The view does not store data independently. When the original table changes, the view changes. No query performance was optimized.
• A view cannot be updated if it contains one of the following structures:
  • Result of aggregate function
  • DISTINCT Result after deduplication
  • GROUP BY Indicates the GROUP result
  • HAVING The result of filtering
  • UNION, UNION ALL result after UNION

The trigger

• Monitoring location (Table)
• Monitoring events (insert | update | delete)
• Triggering time (before | after)
• The triggering event (insert | update | delete)

Create trigger

Create trigger
CREATE TRIGGERThe trigger nameBEFORE 或 AFTER # trigger time
  INSERT 或UPDATE 或 DELETE # monitor events
  ONThe name of the table# Surveillance site
  FOR EACH ROW Mysql does not write row-level triggers. Mysql does not write row-level triggers. Mysql does not write row-level triggers
  BEGIN # start trigger
    sqlstatements1 sqlstatements2.END # end trigger
Copy the code

View trigger

SHOW TRIGGERS;
Copy the code

Delete trigger

DROP TRIGGERTrigger name;Copy the code

reference

Learn MySQL database one day

【 dark Horse 】MySQL learning notes