Highlights of mysql usage and mysql usage

Highlights of mysql usage and mysql usage

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 will introduce the basic statements:

2.1 create a database

Create database database-name

2.2 Delete A Database

Drop database dbname

2.3 back up SQL server

--- Create a device for the backup data

USE master EXEC sp_addumpdevice 'disk', 'testback', 'c: mssql7backupMyNwind_1.dat'

--- Start backup

Backup database pubs TO testBack

2.4 create 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 (use the old table to create a new table)

2.4B: create table tab_new as selectcol1, col2... From tab_old definition only

2.5 Delete a table

Drop table tabname

2.6 Add a column to the table.

Alter table tabname add column coltype Note: After a column is added, it cannot be deleted.

2.7 add or delete a primary key

Alter table tabname add primarykey (col)

Alter table tabname drop primarykey (col)

2.8 Add/delete an index

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

Drop index idxname

2.9 create or delete a view

Create view viewname as select statement

Drop view viewname

2.10 common basic SQL statements

Search: select * fromtable1 where range

Insert: insert into Table1 (field1, field2) values (value1, value2)

Delete: delete fromtable1 where range

Update: 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

Sum: 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 common advanced query Operators

2.11A: UNION operator

The UNION operator combines two other result tables (such as table1 and table2) and removes any duplicate rows from the table to generate a result table. When ALL and UNION are used together (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is neither table1 nor table2.

2.11B: Random t operator

The distinct t operator is a result table derived from table 1 by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.

2.11C: INTERSECT Operator

The INTERSECT operator derives a result table by going to the rows including table1 and table2 and removing all duplicate rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.

2.12 use external connections

2.12A: left outer join

Left Outer Join: The result set includes the matched rows of the connected table and all rows of the connected table.

SQL: select a. a, a. B, a. c, B. c, B. d, d. f from a left out join B ON a. a = B. c

2.12B: right outer join

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

1.12C: full outer join

Full outer join: includes not only matching rows of the symbolic join table, but also all rows of the two connected tables.

3. mysql subquery

3.1mysql subquery syntax and usage example

A subquery uses the query result of a SELECT statement as the intermediate result for calling by another SQL statement. MySQL supports all the subquery 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

Scalar query refers to a scalar returned by a subquery as a single value, such as a number or a string. It is also the simplest return form in subquery.

Example: select * from article where uid = (select uid from user where status = 1 order by uid desclimit 1)

3.3mysql column subquery

A column subquery is a result set returned by a subquery that contains N rows and one column. This result is usually returned by a table field query.

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

3.4mysql row subquery

Row-based subquery refers to a row of N columns returned by a subquery. The result of this subquery is usually the result set returned when a row of data in the table is queried.

Example: select * from table1 where (1, 2) = (select col1, col2 from table)

3.5mysql table subquery

Table subquery refers to a table with N rows and N columns returned by a subquery.

Example: select * from article where (title, content, uid) in (select title, content, uid fromblog)

3.6 mysql from subquery

MySQL FROM subquery refers to the FROM clause as a subquery statement. The primary query then obtains the required data FROM the subquery results.

Syntax: select... From (subquery) as name...

3.7 mysql exists and notexists subqueries

Syntax: select... From table where exists (subquery)

This syntax can be understood as: Put the data of the primary query into the subquery for conditional verification, and determine whether the data results of the primary query are retained based on the verification result (TRUE or FALSE.

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

3.8mysql associated subquery

An associated subquery is a subquery that contains reference to a table. The table is also displayed in an external query. Generally speaking, the subquery references the data of the primary query.

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

4. mysql Loop

4.1 while... 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.2 repeat... End repeat loop

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: until can be followed by a semicolon

4.3 loop... End 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 query results.

5.2 use of views

If a query result appears frequently, that is, the query result must be used frequently as a subquery.

5.3 syntax

Create view name as select statement

5.4 view advantages

L simplified query statements

Example: There is a commodity table. We often need to check the average prices of commodities in each topic.

Select cat_id, avg (shop_price) from goods group by cat_id;

Create a view

Create view avgpice as select cat_id, avg (shop_price) fromgoods group by cat_id;

When we query the average price of each topic again, we only need to write

Select * from avuplice;

L permission Control

The table permission is closed, but the corresponding view permission is open. Only some data columns are open in the view, for example, our goods product table. We don't want others to see our sales prices, in this case, we can disable the commodity table permission to create a view.

Create view showGoods as select goods_id, goods_name fromgoods;

5.5 view Modification

Alter view name as select statement;

5.6 relationship between views and tables

A view is the query result of a table. Changing the data of a natural table will affect the view result.

6. mysql association query

6.1 connection query Overview

The conditions used to connect two tables in the join query are called join conditions or join predicates. The format is:

[<Table 1>]. <column Name> <join operator> [<Table 2>]. <Column 2>

Common connection operators include

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

L logical operators: not, and, or

6.2 connections are classified by result set

L inner join: the rows in the table are connected to each other. The number of rows in the result set is equal to the number of rows that meet the conditions in each table.

L outer join: The tables involved in the join operation have the primary and secondary points. each row of data in the master table matches the data column in the slave table, and data that meets the connection conditions is directly returned to the result set, data columns that do not meet the connection conditions are filled with null and returned to the result set. The outer join is divided into left Outer Join, right outer join, and full join.

Connection Query within 6.3

Internal Connection syntax structure:

Select <attribute or expression list> from <Table Name> [inner] join <Table Name> on <connection condition> [where <restriction condition>]

Inner can be omitted. When only join is seen, inner is omitted. Internal join is a traditional join operation. Here, the on clause is used to specify the join condition, and the where clause is used to specify other conditions,

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 <connection condition> [where <condition>]

The left Outer Join table contains all records that meet the conditions in the first table. If the connection condition matches, the second table returns the corresponding value; otherwise, null is returned. That is to say, the fields in the first table are returned no matter whether there is any record in the second table.

Example: select p. name, c. countryname from country as c right join person p onp. countryid = c. countryid

6.5 right outer join query

Syntax structure of right outer join query:

Select <attribute or expression list> from <Table Name> rightouter join <Table Name> on <connection condition> [where <condition>]

The result table of the outer right join contains all records that meet the conditions in the second table. 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 query all external connections

Syntax structure of the full outer join query:

Select <attribute or expression list> from <Table Name> fullouter join <Table Name> on <connection condition> [where <condition>]

The results set table of the all outer join query contains two tables that meet all records of the record. If the matching tuples are matched on the connection condition, the other table returns the corresponding value; otherwise, null is returned.