MySQL basic SQL statement/Grammar tutorial (1/5)

First, briefly introduce the underlying statement:

1, Description: Create a database tutorial
Create DATABASE Database-name

2, Note: Delete the database
Drop Database dbname

3, Description: Backup SQL Server
Device to create backup data---
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:mssql7backupmynwind_1.dat '
---start Backup
BACKUP DATABASE pubs to Testback

4, Description: Create 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 (Create a new table using the old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only

5, Description: Delete the new table
drop table TabName

6. Description: Add a column
Alter table tabname Add column col type
Note: The column will not be deleted after it has increased. DB2 the column plus the data type can not be changed, the only change is to increase the varchar type
The length.

7. Description: Add primary key: Alter table TabName Add primary key (COL)
Description: Delete primary key: Alter table tabname drop primary key (COL)

8. Description: Creating index: Create [unique] index idxname on tabname (col ...)
Deleting indexes: Drop INDEX Idxname
Note: The index is not to be changed and you want to change the rebuild must be deleted.

9. Description: Create VIEW: Created view viewname as SELECT statement
Delete view: Drop View ViewName

10, Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered 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
Max: Select Max (field1) as MaxValue from table1
Min: select min (field1) as MinValue from table1

Home 1 2 3 4 5 last