MySQL Usage Essentials Collection

1. Environment: Windows,mysql Server 5.5,navicat Formysql

2. mysql Common SQL statements

SQL classification:

ddl-Data Definition language (create,alter,drop,declare)

dml-Data Manipulation Language (Select,delete,update,insert)

dcl-Data Control Language (Grant,revoke,commit,rollback)

First, we introduce the underlying statement:

2.1 Creating a Database

CREATE DATABASE Database-name

2.2 Deleting a database

Drop Database dbname

2.3 Backing up SQL Server

---to create a device that backs up data

Use master EXEC sp_addumpdevice ' disk ', ' testback ', ' C:mssql7backupmynwind_1.dat '

---start Backup

BACKUP DATABASE pubs to Testback

2.4 Creating a new table

CREATE TABLE TabName (col1 type1[notnull][primary key],col2 type2[not null])

Create a new table based on an existing table

2.4a:create table Tab_new Liketab_old (Creating a new table with the old table)

2.4b:create table tab_new as Selectcol1,col2...from tab_old definition only

2.5 Deleting a table

Drop Table TabName

2.6 Adding a column to a table

Alter table tabname Add column Coltype Note: Columns cannot be deleted after they are added

2.7 Add primary key/delete primary key

Alter table tabname Add PrimaryKey (col)

Alter table TabName drop PrimaryKey (col)

2.8 Add index/DELETE index

Create [unique] index idxname ontabname (col ...)

Drop INDEX Idxname

2.9 Creating a view/deleting a view

CREATE VIEW viewname AS SELECT statement

Drop View ViewName

2.10 Common Basic SQL statements

Find: SELECT * Fromtable1 where range

Insert: Insert Intotable1 (FIELD1,FIELD2) VALUES (value1,value2)

Delete: Delete fromtable1 where range

Updated: Update Table1set field1=value1 where range

Fuzzy query: Select *from table1 where field1 like '%value1% '

Sort: SELECT * Fromtable1 ORDER by Field1,field2[desc]

Total: SelectCount (*) as TotalCount from table1

Summation: Selectsun (field1) as Sunvalue from table1

Average: Selectavg (field1) as Avgvalue from table1

Maximum: Selectmax (field1) as MaxValue from table1

Min: Selectmin (field1) as MinValue from table1

2.11 Several common advanced query operators

2.11a:union operator

The Union operator derives a result table by combining the other two result tables (for example, Table1 and table2) and eliminating any duplicate rows in the table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from table1 or table2.

2.11b:except operator

The except operator derives from a result table that includes all the rows in table1 but no longer table2 and eliminates all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.

2.11c:intersect operator

The Intersect operator derives a result table from rows that include both Table1 and table2 and eliminate all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.

2.12 using an external connection

2.12a:left outer JOIN

Left outer joins: The result set includes matching rows for the join table, and all rows that make the join table

Sql:select A.A,A.B,A.C,B.C,B.D,D.F from a left off JOIN B on A.A=B.C

2.12b:right outer JOIN

Right outer join: The result set includes both the matching row of the join table and all rows of the right join table

1.12c:full outer JOIN

Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the rows of the two join tables

3. mysql Sub-query

3.1mysql sub-query syntax and usage examples

a subquery is a SELECT The query result of the statement is used as an intermediate result for another SQL statement invocation. MySQL supports All the sub-query formats and operations required by the SQL standard, and also extends the unique features.

Example: select* from article where UID in (select UID from user where Status=1)

3.2mysql scalar Quantum Query

A scalar subquery is one that is returned by a subquery to a single value, such as a number or a string, and is the simplest form of return in a subquery.

Example: select* from article where uid= (select uid from user where Status=1 order by UID Desclimit 1)

3.3mysql Query

A sub-query is a result set returned by a subquery that is N row column, which is typically returned from a field query on a table.

Example:select *from article where uid in (select UID from user where Status=1)

3.4mysql Row Sub-query

A row subquery refers to a row of result sets returned by a subquery N column, the result of this subquery is usually the result set returned by querying a row of data for a table.

Example:select* from table1 where (in) = (select Col1,col2 from table)

3.5mysql Table Sub-query

A table subquery refers to a result set returned by a subquery that is N Line N a table data for the column.

Example:Select *from article where (title,content,uid) in (select title, Content, uid Fromblog)

3.6mysql from sub-query

the MySQL from subquery refers to the FROM clause as a subquery statement, and the main query to the subquery results to get the required data

Syntax:Select ... from (subquery) as name ...

3.7mysql exists and notexists Subqueries

Syntax:Select ... from table where exists (subquery)

This syntax can be interpreted as: putting the data of the main query into a subquery for conditional validation, based on the validation result (TRUE or FALSE ) to determine whether the data results of the main query are retained.

Example:Select *from article where exists (select * from user where article.uid=user.uid)

3.8mysql correlated subqueries

An associated subquery is a subquery that contains a reference to a table, which is also displayed in an external query. In layman's terms, the subquery refers to the data data of the main query .

Example:selectarticle.* from article inner join user on Article.uid=user.uid

4. mysql loop

4.1while...end while loop

Create procedure P1 ()

Begin

declare v int;

Set v=0;

While v<5 do

Insertinto T Values (v)

setv=v+1;

End while;

End

4.2repeat...end Repeat Cycle

CREATE PROCEDURE P2 ()

Begin

declare v int;

Set v=0;

Repeat

INSERT into T values (v);

Set v=v+1;

Until v>=5;

End repeat;

End

Note: There can be no semicolon behind until

4.3loop...end Loop Loop

CREATE PROCEDURE P3 ()

Begin

declare v int;

Set v=0;

Loop_label:loop

Insert into Tvalues (v);

Set v=v+1;

If V>=5 Then

Leaveloop_label;

End If;

End Loop;

End

5. mysql View query

5.1 View definition

A view is a virtual table formed by the results of a query.

5.2 Use of views

If a query results in a very frequent, that is, to often take this query results to do subqueries

5.3 Syntax

CREATE VIEW name as SELECT statement

5.4 View Benefits

L Simplify Query statements

Example: There is a commodity table, we have to check the average price of each item under each column

Select cat_id, AVG (shop_price) from goods group by CAT_ID;

Create a View

CREATE View Avgprice as select cat_id, avg (shop_price) fromgoods GROUP by cat_id;

When we check the average price of each column again, we only need to write this

SELECT * from Avgprice;

L Perform permission control

Closed the permissions of the table, but open the corresponding view permissions, the view only open part of the data column, such as our goods commodity table, we do not want to let others see our sales price, this time we can close the list of permissions, create a view

CREATE View showgoods as select Goods_id,goods_name fromgoods;

5.5 View Modifications

Alter View View name as SELECT statement;

5.6 View-to-table relationships

A view is a query result of a table, and the data of the natural table changes, affecting the view's results

6. mysql Correlation query

6.1 Introduction to Connection queries

The conditions used to connect two tables in a connection query are called join conditions or connection predicates. The form is:

[< table 1>].< column name >< join operator >[< table 2>].< column 2>

Common join operators include

L comparison operators: =, >, <, >=, <=,! =, between, and and

L logical operators: not, and, or

6.2 Connections sorted by result set

L Inner joins: The rows in the table are connected to each other, the number of rows in the result set equals the number of rows that each table satisfies the criteria, and the representation of the join is equal.

L OUTER join: Join table has primary and secondary points, each row of the main table data to match from the table data column, the data matching the connection condition directly return to the result set, the data column that does not conform to the join condition will be returned to the result set with null padding, the outer join is divided into left outer connection, right outer connection and full connection.

6.3 Internal Connection Query

Internal connection Syntax Structure:

Select < attribute or expression list > from < table name > [Inner]join < table name > on < join condition > [where < qualification]

Inner can be omitted, when only the join is omitted to omit the inner. An inner join is a traditional connection operation, where a join condition is specified with an ON clause, and where the other qualification is specified,

Example: Select P.name,c.countryname from country as C inner join person p Onp.countryid=c.countryid

6.4 Left OUTER JOIN query

LEFT OUTER JOIN syntax structure:

Select < attribute or expression list > from < table name > Leftouter join < table name > on < join condition > [where < qualification]

The left OUTER join contains all records in the first table that meet the criteria in the result table, or null if the join condition matches, and the second table returns the corresponding value. In other words, the fields of the first table are returned, regardless of whether the second table has any records.

Example: Select P.name,c.countryname from country as C right join person P Onp.countryid=c.countryid

6.5 Right OUTER join query

Right outer JOIN query syntax structure:

Select < attribute or expression list > from < table name > Rightouter join < table name > on < join condition > [where < qualification]

The result table of the right outer join contains all the records in the second table that meet the criteria, and if the join condition matches, the first table returns the corresponding value, otherwise null is returned.

Example: Select P.name, c.countryname from country as C right join person Pon P.countryid=c.countryid

6.6 Full OUTER JOIN query

Full OUTER JOIN query syntax structure:

Select < attribute or expression list > from < table name > Fullouter join < table name > on < join condition > [where < qualification]

The result set table for the full outer join query contains two tables that satisfy all records in the record. If a tuple is matched on a condition on the connection, the other table returns the corresponding value, otherwise null is returned

MySQL Usage Essentials Collection