MySQL database Operations (2) Basic operations

To create a database:
CREATE database [IF not EXISTS] Library name
Example:
CREATE DATABASE ' mydb ';
CREATE DATABASE IF not EXISTS ' mydb ';
Attention:
If the library name already exists, not adding if not exists will error, plus only warning.

To delete a database:
DROP database [IF EXISTS] Library name;
Example:
DROP DATABASE ' mydb ';
DROP DATABASE IF EXISTS ' mydb ';
Attention:
If the library name does not exist, the error will be added without the if exists, plus only warnings.

Open the database:
Use database name
When I first entered MySQL, it was not in any database and could not directly manipulate the table.

To view the tables in the database:
SHOW TABLES [from library name];
Example:
SHOW TABLES; View the data tables in the current database.
Show TABLES from ' MySQL '; View the data table in MySQL database.

To create a data table:
The CREATE table [IF not EXISTS] Table name (
Column Name 1 data type,
Column Name 2 data type,
Column name N data type
)
Example:
CREATE TABLE ' tb1 ' (' id ' INT comment ' number ', ' Name ' VARCHAR () Comment ' name ');
Attention:
Examples of comment are used to illustrate, show people

To view the tables created:
SHOW CREATE table table name;
You can see the more complete statement that was used to create the table.
Example: SHOW CREATE TABLE ' tb1 ';

To view the data table structure:
DESCRIBE table name;
DESC table name;
SHOW COLUMNS from ' table name ';
Example:
DESCRIBE tb1;
DESC tb1;
SHOW COLUMNS from ' tb1 ';

To delete a data table:
DROP table ' table name ';
Example: DROP TABLE tb1;

Insert data:
INSERT into table name values (value 1, value 2,....);
Insert a data entry in the order in which you create the table column
Example: INSERT into Tb1 VALUES (1, ' abc ');
INSERT into table name (column 1, column 2,...) Values (value 1, value 2,....);
You can adjust the column order of the inserted data, or omit a column, and the order of the values will correspond to the order of the columns one by one
Example: INSERT into TB1 (name,id) VALUES (' BBC ', 2);
INSERT into table name set column name 1= value 1, column name 2= value 2, ....;
The corresponding relationship of each column and value can be visually seen
Example: INSERT into tb1 SET name= ' QQQ ', id=22;

Delete Deletes data:
DELETE from table name [WHERE clause];
Deletes all entries that conform to the WHERE clause, without a WHERE clause to delete all entries of the table, where the clause is optional, followed by a separate description
Example: DELETE from TB1;

Update data:
UPDATE table name set column name 1= value 1[, column name 2= value 2] ... [where sentence];
Update or modify an entry that conforms to the WHERE clause, without a WHERE clause to indicate that all entries of the table are modified, where the clause is optional, followed by a separate description
UPDATE tb1 SET name= ' test ';

SELECT Query data:
SELECT column name 1[, column Name 2 ...] from table name [WHERE clause];
The column name sequence can represent all columns with a *, where clause is optional, followed by a separate description
Example: SELECT name,id from TB1;

WHERE clause:
The WHERE clause is used to specify the criteria for selection.

WHERE clause:
The WHERE clause is used to specify the criteria for selection, in the format [WHERE column operator value]
The following operators can be used in the WHERE clause:

operator	Description
=	Equals
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
Between	Within a range
Like	Search for a pattern

Example:
SELECT name,id from tb1 WHERE name= ' abc ';
UPDATE tb1 SET name= ' test ' WHERE id=22;
DELETE from tb1 WHERE id<3;
Attention:
SQL uses single quotation marks to wrap text values (most database systems also accept double quotes). If it is a numeric value, do not use quotation marks.

MySQL database Operations (2) Basic operations