MySQL common syntax

 

 

 

1. SelectTOP 50 PERCENT*
From Persons ~~~ In the "persons" table, select 50% of the records;

SelectTOP 2*
From Persons ~~~ Select the first two records in the "persons" table.

2. wildcard characters:

3. Select * from
Persons where lastname in ('adams', 'cart ');~~~ Specify multiple values in the WHERE clause;

4. Select
Persons. lastname, persons. firstname, orders. orderno

From persons, orders

Where persons. id_p =
Orders. id_p;

====== Equivalent:

Select
Persons. lastname, persons. firstname, orders. ordernofrom persons

Inner join orders

On persons. id_p =
Orders. id_p

Order
Persons. lastname;

Join type:

·
Join: If the table has at least one match, the row is returned.

·
Left join: returns all rows from the left table even if no match exists in the right table.

·
Right join: returns all rows from the right table even if no match exists in the left table.

·
Full join: if one of the tables matches, the row is returned.

URL: http://www.w3school.com.cn/ SQL/SQL _join_inner.asp;

5. Union
& Union all, for example:

Select e_name from
Employees_china

Union

Select e_name from
Employees_usa;

Union all lists all content, including duplicates;

6. Database Backup:

SELECT*

INTOPersonsIN'Backup. mdb'

From persons

Back up the table persons to the table persons in the database backup. mdb;

7. Create a database: Create Database database_name

8. database table:

Create Table orders

(

O_id int not null,

Orderno int not null,

Id_p int,

Primary Key (o_id ),

Foreign
Key (id_p) References persons (id_p)

)

9. Check constraints:

Create Table persons

(

Id_p int not null,

Lastname varchar (255) not null,

Firstname varchar (255 ),

Address varchar (255 ),

City varchar (255 ),

Check
(Id_p> 0)

)

Multiple check constraints:

Create Table persons

(

Id_p int not null,

Lastname varchar (255) not null,

Firstname varchar (255 ),

Address varchar (255 ),

City varchar (255 ),

Constraint
Chk_person check (id_p> 0 and city = 'sandes ')

)

10. Auto-increment: auto_increment

Create Table persons

(

P_id int not null auto_increment,

Lastname varchar (255) not null,

Firstname varchar (255 ),

Address varchar (255 ),

City varchar (255 ),

Primary
Key (p_id)

)

11. Group by: Select customer, sum (orderprice) from orders
Group by customer;

Locate the customer's name and total order amount by customer grouping.

12. The having clause is added to SQL because the where keyword cannot be used with the aggregate function:

Select customer, sum (orderprice) from orders

Group by customer

Having sum (orderprice) <2000