Mysql research-SQL language design and writing-MySQL

65279; 65279; 1. SQL statement Classification Data Definition Language (DDL): used to define and manage data objects, including databases, data tables, views, and indexes. For example, CREATE, DROP, ALTER, and other statements. Data operation language (DML): [language related to data records in the table 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 to improve efficiency... Truncation

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!

SELECT [ALL | DISTINCT] # distinct is DISTINCT, clear, and different.

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

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!

1. the fields to be queried must be listed.

E.g. selectname, price from products;

Selectprice, name from products;

Select * from products;

Selectproducts. * from products; # The table name is not required for a single table.

2. you can create an alias for each field [used later (keyword, multi-table query)] [table alias (multi-table query )]

E.g. selectname as bookname, price as bookprice from products; # use aliases; do not add as; note that single quotation marks are required when there are spaces in aliases;

3. use distinct to cancel duplicate data with the entire record. only one column is returned instead of the other.

E.g. selectdistinct price 'Book price' from products;

4. columns using expressions in SQL statements (arithmetic operators, conditional operators, logical operators, etc. can be used ...)

Game Programming Network www.cgzhw.com has a detailed description, here it will not be repeated.

5. WHERE can be found in SELECT/UPDATE/DELETE

A) available logical operator numbers (combining multiple conditions)

&/AND |/OR! /NOT

B) comparison operator numbers that can be used

= # Determine whether or not they are equal, which is the same as = in the program

<=> # Determine whether to be equal, consistent with =, but can be used for comparison with NULL

! =/<> # No.

<

<=

>

> =

C) operators not in the program

ISNULL # equal to '<=> Null'

ISNOT NULL

BETWEENAND

E.g. select * from products where id between 10 and 20;

Same as "select * from products where id >=10 & id <= 20 ;"

NOTBETWEEN AND

IN

E.g. select * from products where id in (5, 10, 15, 20 );

Updateproducts set num = 77 where id in (5, 10, 15, 20 );

Deletefrom products where id in (5, 10 );

D) Fuzzy search

LIKE _ (any one character) and % (0 or multiple arbitrary characters) two wildcard numbers

E.g. select * from products where name like '______'; # search for data with any name of 6 characters

Select * from products where name like '% java %'; # The query name contains java data.

NOTLIKE

E.g. select * from products where name not like '% java %'; # query data whose names do not contain the word "java.

REGEXP/RLIKE [regular expression] # RegExp regular expression

E.g. select * from products where name regexp '^ Java'; # search for all data starting with java

Select * from products where name regexp's $ '; # search for all data ending with s

6. multi-table queries (connection queries), which are commonly used # ambiguous

E.g. selectcats. name, products. name from cats, products;

Selectc. name cname, c. desn cdesn, p. name pname, p. price, p. desn pdesn, p. numfrom carts c, products as p; # match the records in Table A with those in Table B in sequence to obtain the result of A * B [Cartesian product], which is meaningless.

Selectc. name cname, c. desn cdesn, p. name pname, p. price, p. desn pdesn, p. numfrom carts c, products as p where c. id = p. cid;

Selectc. name cname, c. desn cdesn, p. name pname, p. price, p. desn pdesn, p. numfrom carts c, products as p where c. id = p. cid and c. id = 3;

Selecta. id aid, a. name aname, B. id bid, B. name bname from cats a, catsb; # divide a single table into multiple tables for query

Selecta. id aid, a. name aname, B. id bid, B. name bname from cats a, cats B wherea. pid = B. id;

7. nested query subquery

E.g. select * from products where cid in (select id from carts where name regexp '^ J ');

Select * from products where cid in (select id from carts where name like 'J % '); # Same role

8. orderby field [asc forward] desc reverse

E.g. select * from order by name;

Select * from order by price; # Sort by non-decreasing price

Select * from order by price desc; # Non-incremental sorting

Select * from where cid> 5 order by price desc; # used with where

9. limitcount [display limit]

E.g. select * from limit 7;

Select * from order by id desc limit 7;

Select * from where id <10 order by id desc limit 7;

Select * from where id> 14 order by id asc limit 0, 1; # limit0, 1 indicates to get from 0th, take 1

10. groupby field [group]

Common functions:

Count () # total number of fields

Sum ()

Avg () # average value

Max ()

Min ()

E.g. selectcount (*), sum (price), avg (price), max (price), min (price) from products;

Selectcid, count (price), sum (price), avg (price), max (price), min (price) fromproducts group by cid;

Selectcid, count (price), sum (price), avg (price), max (price), min (price) fromproducts group by cid having avg (price)> 50; # add the having condition, which is similar to where

# Having must be used in combination with gropby.