MySQL Study Notes _ 5_ SQL language design and writing (on), study notes _ 5_ SQL

MySQL Study Notes _ 5_ SQL language design and writing (on), study notes _ 5_ SQL
 SQL language design and writing (I)

I. SQL statement Classification

Data Definition Language (DDL ):

Defines and manages data objects, including databases, data tables, views, and indexes. For example, CREATE, DROP, ALTER, and other statements.

Data Operation Language (DML): [language related to the data records in the table]

Used to manipulate data contained in database objects. For example, INSERT, UPDATE, and DELETE statements.

Data Query Language (DQL ):

This interface is used to query data contained in database objects. It can be used for single-table queries, connection queries, nested queries, set queries, and other database queries with different levels of complexity, and return the data to the client for display. For example, SELECT statement (60% ).

Data Control Language (DCL ):

Is the language used to manage databases, including management permissions and data changes. For example, GRANT, REVOKE, COMMIT, ROLLBACK, and other statements.

Ii. SQL statement application cases

1. DDL [You can write a file in the following format and paste it into the MySQL database]

Createtable if not exists cats (

Idint not null auto_increment,

Pidint not null default '0 ',

Namevarchar (30) 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 (60) not null default '',

Pricedouble (0.00) not null default,

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 (Value List), (Value List 2), (Value List 3),..., (Value List n );

Features:

1. If the Field List is not provided after the table name, the value list must be filled with the values of all fields and must be inserted in the default order of the table.

2. No single or double quotation marks are added for all fields to be written. However, we recommend that all values be in character format.

3. It is recommended that you provide a field list when inserting data, so that the values can correspond to the field list one by one, instead of in the field order in the table.

B) update table name set field = 'value' [, Field 2 = 'value ',..., field n = 'value n'] [condition] # condition specifies the record to be changed

E.g. updatecats set pid = '3' where id = '1 ';

Updatecats set pid = '99' where id> = '1' & id <= '3 ';

C) deletefrom table name [condition]

Deletefrom cats; # Clear a data table

Truncatecats; # You can also clear data tables, which is more efficient. truncate...

D) where Condition

No matter whether it is updated, deleted, or searched, you only need to write the conditions to find one or more data records to manage.

[All operators can be used, and fields can be used as a variable]

3. DQL [select]

SELECT [ALL | DISTINCT]

{* | Table. * | [table.] field1 [asalias1] [, [table.] field2 [as alias2] [...]}

FROM table name

[WHERE...]

[GROUPBY...]

[HAVING...]

[ORDERBY...]

[LIMITcount]

The SELECT query language is used to check the data and return the results according to the user's ideas!