MySQL Query table basic operations

Creation of database tables
CREATE table < table name >
(
< column name > < data type and length > [NOT NULL],
< column name > < data type and length;
...
< column name > < data type and length >
)
Delete a table
DROP table < table name >

Import and Export data
Turn the table into SQL code
Backup and restore

increase, delete, change, check CRUD

Add to:
INSERT into < table name >[(column 1, column 2 ...)] VALUES (< ' value 1 ' >,[' Value 2 '))
Attention:
1. Columns and values to match (quantity, type, order)
2. The column can be omitted, but the value must correspond exactly to the total number of columns in the table and the order of the columns.
3. The self-increment column cannot be omitted from the Increment column, and the self-increment column is assigned a "

Delete from car where code= ' c001 '
Delete from car where brand= ' b001 ' or brand= ' b004 '
Delete from car where brand= ' b001 ' | | Brand= ' b004 '
Delete from car where brand= ' b007 ' && price>50
Delete from car where brand= ' b007 ' and price>50

<>! =

Update
Update < table name > Set < column = value >[, column = value ...] where .....
Update info set sex= ' 1 ' where code= ' p003 '
Update info set sex= ' 0 ', nation= ' n004 ', birthday= ' 1999-9-9 ' where code= ' p001 '
Update car Set Price=price * 0.9 where price > 30
Update car set price =price * 0.95 where (brand= ' b006 ' | | brand= ' b005 ') &&price>30

Inquire
SELECT * FROM table name
Select Column Name 1, column Name 2 ... from table name--Projection
SELECT * FROM table name where condition--filter

1. Equivalence and non-equivalence
SELECT * from car where code= ' c001 ';
SELECT * FROM car where code! = ' c001 ';
SELECT * from car where price > 30;
--The following are the ranges
SELECT * from car where price >=30 && price <=50;
SELECT * from car where price between and 50
SELECT * from car where brand= ' b002 ' | | brand= ' b004 ' | | Brand= ' b006 '
SELECT * FROM car where brand in (' b002 ', ' b004 ', ' b006 ')

2. Fuzzy Search
SELECT * from car where name like ' BMW% '%--any number of arbitrary characters
SELECT * from car where name like '%5% '
SELECT * from car where name like '% type '
SELECT * from car where name like ' __5% ' _--one arbitrary character

3. Sorting
SELECT * FROM table name where .... order BY column name [ASC/DESC], column name [Asc/desc] ....

SELECT * FROM car ORDER BY price DESC
SELECT * FROM Car ORDER BY brand Desc,price ASC

Protect data:
Get database Add permission
Grant Insert
On Constomers
To Mary

Get Database Retrieval Permissions
Grant Update,select
On Constomers
To Mary
Release permissions
Revoke Inse RT
On Constomers
From Mary

MySQL Query table basic operations