Learning notes on common database operations

1, How to create a new table

Here is a table for creating user2:

CREATE TABLE if NOT EXISTS user2(
username VARCHAR(50) not NULL,
password VARCHAR(50) NOT NULL,
realname VARCHAR(50),
phone    VARCHAR(50),
userid int AUTO_INCREMENT,
PRIMARY key(userid)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Instance resolution:

• If you do not want the field to be NULL, you can set the property of the field to NOT NULL. When operating the database, if the data entered in the field is NULL, an error will be reported.
• AUTO_INCREMENT defines a column as a self incrementing attribute, which is generally used for primary keys, and the value will be automatically incremented by 1.
• PRIMARY KEY keyword is used to define a column as a PRIMARY KEY. You can define a PRIMARY KEY using multiple columns separated by commas.
• ENGINE sets the storage ENGINE and CHARSET sets the encoding.

2, Delete data table

//Delete table
DROP TABLE user2

3, Insert data

INSERT into user2 (
username,password,realname,phone
)VALUES
("Li Datou",123,"Li Si","18277884177")

4, Query data

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

select username ,phone FROM user2 WHERE realname="Li Si"

• In the query statement, you can use one or more tables separated by commas (,), and use the WHERE statement to set the query conditions.

• The SELECT command can read one or more records.

• You can use an asterisk (*) instead of other fields. The SELECT statement will return all field data of the table

• You can use the WHERE statement to include any condition.

• You can use the LIMIT property to set the number of records returned.

• You can use OFFSET to specify the data OFFSET at which the SELECT statement starts the query. By default, the OFFSET is 0.

• /*websites  Table name NAME alexa url country field*/
  SELECT * FROM websites;                      /* Query all data in the table */
  
  SELECT NAME FROM websites;                   /* Query table field data */
  
  SELECT * FROM websites where name = "Guangxi";   /* Query conditional data under table fields */
  
  SELECT * from websites where name like "_o%"; /* Fuzzy query table data */
  
  SELECT * FROM websites where id BETWEEN "1" AND "5";    /* Query the field range data under the table */
  
  SELECT * FROM websites WHERE name in ("Guangxi","Baidu");    /* Query fixed condition data under table fields */
  
  SELECT DISTINCT country FROM Websites;                  /* Query de duplication value */
  
  SELECT * FROM Websites WHERE country = "CN" AND alexa > 50;  /*Query the range condition data under the table*/
  
  SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* Different values of conditions in the query table */
  
  SELECT * FROM Websites ORDER BY alexa;                      /* Sorting results of values under query table */
  
  SELECT * FROM Websites ORDER BY alexa DESC;                 /* The sorting results in the query table are in descending order */
  
  SELECT * FROM Websites LIMIT 2;      /* Query the range data under the table */
  
  SELECT name as zzz from websites;    /*Alias query table data*
  
  

5, Update data

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• You can update one or more fields at the same time.
• You can specify any condition in the WHERE clause.
• You can update data simultaneously in a separate table.

The WHERE clause is very useful when you need to update the data of the specified row in the data table.

6, Delete data

DELETE FROM table_name [WHERE Clause]

• If the WHERE clause is not specified, all records in the MySQL table will be deleted.
• You can specify any condition in the WHERE clause
• You can delete records in a single table at once.

The WHERE clause is very useful when you want to delete the specified records in the data table.

7, Fuzzy query

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• You can specify any condition in the WHERE clause.
• You can use the LIKE clause in the WHERE clause.
• You can use the LIKE clause instead of the equal sign =.
• LIKE is usually used with%, similar to a metacharacter search.
• You can use AND OR to specify one OR more conditions.
• You can use the WHERE... LIKE clause in the DELETE or UPDATE command to specify conditions.

Use the LIKE clause instead of the equal sign =.

• LIKE is usually used with%, similar to a metacharacter search.
• You can use AND OR to specify one OR more conditions.
• You can use the WHERE... LIKE clause in the DELETE or UPDATE command to specify conditions.