Several simple query statements for MySQL tables

1. Create DATABASE Database-name

2. Deleting a database Drop DB dbname

3. Creating a new Table CREATE table tabname (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:

A:CREATE table tab_new like Tab_old ( creating a new table with the old table )

B:CREATE table tab_new as Select Col1,col2 ... from tab_old definition only

Description: Delete a new table drop table TabName

4. add a column ALTER TABLE tabname add column col type

Note: Columns cannot be deleted after they are added. DB2 In addition to the data type can not be changed, the only change is to increase varchar the length of the type.

5. Add primary key: Alter table TabName Add primary key (COL)

Delete primary key: Alter table tabname drop primary key (COL)

6. Creating an index: Create [unique] index idxname on tabname (col ...)

Drop INDEX: Idxname

Note: The index is immutable and you must remove the rebuild if you want to change it.

7. Creating a View: Create VIEW viewname AS SELECT statement

Delete view: Drop View ViewName

8. a few simple basic MySQL statements

Select: SELECT * FROM table1 where range

Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)

Delete: Delete from table1 where range

Updated: Update table1 set field1=value1 where range

Find: SELECT * FROM table1 where field1 like '%value1% '--- the syntax of like is very subtle, check the information !

Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [DESC]

Total: Select count as TotalCount from table1

Sum: Select SUM (field1) as Sumvalue from table1

Average: Select AVG (field1) as Avgvalue from table1

Maximum: Select Max (field1) as MaxValue from table1

Min: select min (field1) as MinValue from table1

9. Several advanced query arithmetic words

A: the Union operator 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 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 from TABLE2.

B: The EXCEPT operator The EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows.

when All when used with EXCEPT (EXCEPT All), does not eliminate duplicate rows.

C: the INTERSECT operator INTERSECT operator by including only TABLE1 and TABLE2 A result table is derived from all rows in the row and all duplicate rows are eliminated.

when All when used with INTERSECT (INTERSECT All), does not eliminate duplicate rows.

Note: Several query result rows that use an operation word must be consistent.

using outer joins

A,Left (outer) join:

Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.

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

B:Right (outer) join:

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

C:full/cross (outer) Join:

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

cluster :GROUP By:

A table, once the grouping is complete, you can only get group-related information after the query.

Group-related information: (statistical information) Count,sum,max,min,avg grouping criteria )

The fields in the selecte statistic function cannot be combined with ordinary fields;

Several simple query statements for MySQL tables