Database-mysql data additions and deletions and changes

One: the insertion syntax for MySQL data

The following is the INSERT into SQL syntax for inserting data into a MySQL data table:

INSERT into table_name (field1, Field2,... fieldn)                       VALUES                       (value1, value2,... Valuen);

If the data is a character type, you must use either single or double quotation marks, such as "value".

MariaDB [test2]>desc student;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| stu_id | Int (11) | NO | PRI | NULL | auto_increment | | name | char (10) |     NO | |                NULL | || Age | Int (11) |     NO | |                NULL | || sex | char (2) |     NO | |                F | |+--------+----------+------+-----+---------+----------------+4 rowsinchSet (0.00sec) MariaDB [Test2]> INSERT into Student (Name,age,sex) VALUES ('Shisanjun', 23,'F'); Query OK,1 Row Affected (0.00 sec)

Second: MySQL query dataGrammar

The following is a general SELECT syntax for querying data in a MySQL database:

SELECT column_name,column_name from table_name [WHERE Clause] [OFFSET M][limit N]

• In a query statement you can use one or more tables, separate the tables with commas (,), and use the where statement to set the query criteria.
• The SELECT command can read one or more records.
• You can use the asterisk (*) instead of the other fields, and the SELECT statement returns all the field data for the table
• You can use the WHERE statement to include any condition.
• You can specify the data offset for the SELECT statement start query by using offset. By default, the offset is 0.
• You can use the LIMIT property to set the number of records returned.

MariaDB [test2]> SELECT *  from student; +--------+------------+-----+-----+| stu_id | name | Age |      Sex |+--------+------------+-----+-----+| 1 |  Shisanjun | 23 |      F | | 2 |  shisanjun2 | 23 |      F | | 3 |  Shisanjun3 | 24 | F |+--------+------------+-----+-----+3 rowsinchSet (0.00sec) MariaDB [test2] > select *  from student limit 2 offset 1; Offset must be used with limit, and limit is in the front +--------+------------+-----+-----+| stu_id | name | Age |      Sex |+--------+------------+-----+-----+| 2 |  shisanjun2 | 23 |      F | | 3 |  Shisanjun3 | 24 | F |+--------+------------+-----+-----+2 rowsinchSet (0.00sec) MariaDB [test2] > select *  from Student offset 1 ; error. 1064 (42000): You had an errorinchyour SQL syntax; Check the manual
That corresponds to your MariaDB server version forThe right syntax'1'At line 1 MariaDB [test2]> select *  from student limit 2, 1; to start querying 1 from the back of the 2nd article +--------+------------+-----+-----+| stu_id | name | Age |      Sex |+--------+------------+-----+-----+| 3 |  Shisanjun3 | 24 | F |+--------+------------+-----+-----+1 RowinchSet (0.00sec) MariaDB [Test2]>

Three: The WHERE statement of MySQL

　　

Grammar

The following is a general syntax for SQL SELECT statements to read data from a data table using a WHERE clause:

SELECT field1, field2,... fieldn from Table_name1, table_name2... [[OR]] condition2      　　     ..... 

• You can use one or more tables in a query statement, use commas , split between tables, and use the where statement to set the query criteria.
• You can specify any condition in the WHERE clause.
• You can specify one or more conditions by using and OR OR.
• The WHERE clause can also be applied to the SQL DELETE or UPDATE command.
• The WHERE clause is similar to the IF condition in a program language, and reads the specified data according to the field values in the MySQL table.

The following is a list of operators that can be used in the WHERE clause

The example in the following table assumes a is ten and B is 20

operator	Description	Example
=	Equals, detects if two values are equal, returns true if equal	(A = B) returns FALSE.
<>! =	does not equal, detects whether two values are equal if not equal returns True	(A! = B) returns TRUE.
>	Greater than sign, detects if the left value is greater than the right value, and returns True if the left value is greater than the right value	(A > B) returns FALSE.
<	Less than sign, detects if the left value is less than the right value, and returns True if the left value is less than the right value	(A < B) returns TRUE.
>=	Greater than equals sign, detects if the left value is greater than or equal to the right value, if the left value is greater than or equal to the right value returns True	(A >= B) returns false.
<=	Less than equals sign, detects if the left value is less than or equal to the right value, if the left value is less than or equal to the right value returns True	(A <= B) returns True.

The WHERE clause is useful if we want to read the specified data in the MySQL data table again.

A conditional query that uses a primary key as a WHERE clause is very fast.

If a given condition does not have any matching records in the table, then the query does not return any data.

Database-mysql data additions and deletions