Mysql basic SQL statements/syntax tutorial (1/5)

First, we will briefly introduce the basic statements:

1. Description: database creation tutorial
Create DATABASE database-name

2. Description: delete a database.
Drop database dbname

3. Description: 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

4. Description: Create a new table.
Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only

5. Description: delete a new table.
Drop table tabname

6. Description: Add a column.
Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the varchar type.
.

7. Description: add a primary key: Alter table tabname add primary key (col)
Delete a primary key: Alter table tabname drop primary key (col)

8. Description: create an index: create [unique] index idxname on tabname (col ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.

9. Description: create view viewname as select statement
Delete view: drop view viewname

10. Description: several simple basic SQL statements
Select: select * from table1 where range
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where range
Update: update table1 set field1 = value1 where range
Search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query 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
Max: select max (field1) as maxvalue from table1
Min: select min (field1) as minvalue from table1

1 2 3 4 5