MySQL Learning note _5_sql language design and writing (on)

 SQLthe design and writing of language (i)

I. Classification ofSQL statements

Data Definition language ( DDL ):

used to define and manage data objects, including databases, data tables, views, indexes, and so on. For example:CREATE,DROP,ALTER , and so on.

Data Manipulation Language ( DML ): "Language related to data records in the table"

used to manipulate the data contained in a database object. For example:INSERT,UPDATE,DELETE statement.

data Query Language ( DQL ):

It is used to query the data contained in the database objects, can make a single table query, connection query, nested query, and collection query and other different complexity of database query, and return the data to the client display. For example:SELECT statement (accounting for 60%).

Data Control Language ( DCL ):

is the language used to manage the database, including administrative permissions and data changes. Example:GRANT,REVOKE,COMMIT,ROLLBACK and other statements.

Second,theSQL Statement application case

1 , DDL "You can write a file in the following format and then paste it into the MySQL database in the "

CreateTable If not exists cats (

Idint NOT NULL auto_increment,

Pidint NOT NULL default ' 0 ',

Namevarchar (+) NOT null default ' ',

Desntext NOT NULL default ' ',

PrimaryKey (ID),

IndexName (Name,pid)

);

CreateTable If not EXISTS products (

Idint NOT NULL auto_increment,

Cidint NOT NULL default 0,

Namevarchar () NOT null default ' ',

Pricedouble (7,2) NOT NULL default 0.00,

Numint NOT NULL default 0,

Desntext,

Ptimeint NOT NULL default 0,

PrimaryKey (ID),

Keypname (Name,price)

);

2 , DML

a ) Insert , insert table Data

Insertinto Table name ([ field List ]) VALUES ( list of values) , (Value List 2), ( value list 3),..., ( Value list n);

Characteristics:

1. If the field list is not given after the table name, the value list must populate the values of all fields and must be inserted in the default order in the table

2. no single or double quotation marks are required for all fields that need to be written, but it is recommended that all values be used as characters

3. It is recommended that when inserting the data, it is best to give the field list, and the value should correspond to the field list one by one, not in the order of the fields in the table

b ) Update Table name Set Field = ' value ' [, field 2= ' value 2 ',..., field n= ' value N '] [ conditional ]# condition Specifies the record that needs to be changed

e.g. Updatecats set pid= ' 3 ' where id= ' 1 ';

Updatecats set pid= ' "Where ID >= ' 1 ' && ID <= ' 3 ';

C ) Deletefrom Table name [ condition ]

Deletefrom Cats; Clear Data Table

truncatecats; # You can also empty the data table for higher efficiency, truncate will be ... truncate

D ) where conditions

No matter update, delete, find, just write to the condition can find exactly one or more data to manage

"Can use a variety of operation symbols, you can use the field as a variable,"

3 , DQL " Select "

Select[all | DISTINCT]

{*|table.*| [table.] Field1[asalias1][,[table.] Field2[as alias2]][...]}

from Table name

[WHERE ...]

[GROUPBY ...]

[Having ...]

[By ...]

[Limitcount]

Use SELECT Query Language, the purpose is to be able to find the data according to the user's ideas, return the results!

MySQL Learning note _5_sql language design and writing (on)