Understanding Database Statements

To create a table:

CREATE TABLE Ceshi

(

Uid varchar (primary) key,

PWD varchar (50),

Name varchar (50),

Nation varchar (50),

Foreign key (Nation) reference Nation (Code)

)

Writing query statements requires attention:

1. When creating a table, do not write a comma after the last column

2. If there are extra statements executed together, be careful to separate the statements with semicolons

3. Write code all symbols are half-width

Relational databases: There is a relationship between tables and tables

Create several keywords for the table:

1. Primary key: Primary key

2. Non-empty: NOT NULL

3. Self-growth column: auto_increment

4. Foreign key Relationship: FOREIGN key (column name) reference table name (column name)

CRUD Operations:

1. Add Data:

The INSERT into table name values (' ', ' ', ' ', ') require that the value in the parentheses be the same as the number of columns in the table

Insert into table name (column name, column name) adds the value of the specified column

2. Modify the data:

Update info set name= ' Zhang San ' where code= ' p001 '

3. Delete data:

Delete from info where code= ' p001 '

Query data:

1. General Enquiry, check all

Select*from Info Check All data

Select Code,name from info to specify columns

2. Conditional query

Select*from info where code= ' a condition

Select*from info where name= ' and nation= ' two conditions and relationships

Select*from info where name= ' or nation= ' two conditions or relationships

3. Sort queries

Select*from Info ORDER BY birthday default ascending ASC If you want to sort desc in descending order

Select*from Info ORDER BY brand,oil DESC Multi-column sort

4. Aggregation functions

Select COUNT (*) from info fetch number

Select SUM (price) from car queries the sum of the price column

Select AVG (price) from car queries the average value of the price column

Select min (Price)/max (price) the minimum or maximum value from car query Price column

5. Paging Query

Select*from car Limit n,m skipping n data fetching m-bar data

6. Grouping data

Select brand from Car GROUP by brand simple Group query

Select brand from car GROUP by Brand have count (*) >2 query series inside the number of cars more than 2 series

7. Go to re-query

Select distinct brand from car

8. Modify column names

Select Brand as ' series ' from car

9. Fuzzy Query

Select*from car where name like ' ao% ' means any number of characters _ represents one character

10. Discrete query

Select*from car where code in (",", ",")

Select*from car where code not in (' ', ' ', ', ')

Understanding Database Statements