Add, delete, modify, and query mysql instances

Add, delete, modify, and query mysql instances

Command for adding data to a table:

mysql>insert into table_name (field1, filed2, ... fieldN)     -> values(value1, value2, ...valueN);

If the string type is inserted, it must be enclosed by quotation marks. Both single quotation marks and double quotation marks are acceptable. For example:

mysql>insert into test (field) values("xyb");

If you want to insert multiple data records at the same time, for example, insert three:

mysql>insert into test (field) values("xyb"), ("love"), ("Charlotte");

Command to query in the table:

mysql>select field1, field2, ... fieldN from table_name1, table_name2 ...    -> [where condition1 [and [or]] condition2 ...]    -> [offset m] [limit n] [order by field1, [...fieldn] [desc]];

Fields are separated by commas, and tables are separated by commas. Offset specifies the offset, which not only skips the initial m records; limit limits the maximum number of output records to n; where is followed by the query condition, and multiple query conditions are connected by and or; order by specifies to sort by a field and then return.
Common operators of where are as follows:

=	! =	<	>	<=	> =

If you use the where and above operators to query strings, The Case sensitivity is ignored. To be case sensitive, you can add the binary keyword. For example:

mysql>select field from table_name where binary field='xyb';

The where condition uses the like operator for pattern matching. The like operator uses the SQL pattern matching. In this case, "_" matches any single character, and "%" matches any number of characters (including 0 ). By default, SQL mode matching in mysql is case sensitive.
Search for fields starting with "B:

select * from table_name where field like 'b%';

Search for the field ending with "fy:

select * from table_name where field like '%fy';

Find the field that contains "w ":

select * from table_name where field like '%w%';

The field query contains exactly five characters:

select * from table_name where field like '_____';

For more information about rlike and extended mode matching, see the first link.

The return values of mysql queries are not in a fixed order (generally in the insert order ). If you want to return results by sorting a query field, add the order by query field at the end of the select statement. You can sort multiple fields in ascending order by default. The desc keyword indicates descending order. When multiple fields are sorted, the first field is sorted in order, and the first field in each group is the same record set, and then the second field is sorted. For example:

FirstName	LastName	YearOfBirth
Thomas	Alva Edison	1847
Benjamin	Franklin	1706
Thomas	More	1478
Thomas	Jefferson	1826

Use the following statement:

SELECT * FROM People ORDER BY FirstName DESC, YearOfBirth ASC

The result is as follows:

FirstName	LastName	YearOfBirth
Thomas	More	1478
Thomas	Jefferson	1826
Thomas	Alva Edison	1847
Benjamin	Franklin	1706

For more information, see the second answer to sotack overflow.

Command for updating table data:

mysql>update table_name set field1=value1, field2=value2    -> [where condition, ...];

Command for deleting table data:

mysq>delete from table_name    -> [where condition, ...];

If no conditions are specified, the entire table is deleted.

Reference link:
Https://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html
Http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering