Complete collection of MYSQL classic statements - Basics

　1.Create database
　CREATE DATABASE database-name
　2.Delete database
　drop database dbname
　3.Back up sql server
　 --- Create device for backup data
　USE master
　EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
　 --- Start Backup
　BACKUP DATABASE pubs TO testBack
　4. 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. Delete the new table
drop table tabname
6. Add a column
Alter table tabname add column col type
Note: Once a column is added, it cannot be deleted. In DB2, the data type cannot be changed after the column is added. The only thing that can be changed is to increase the length of the varchar type.
　7. Add primary key: Alter table tabname add primary key(col)
　Delete primary key: Alter table tabname drop primary key(col)
　8. Create index: create [unique] index idxname on tabname(col….)
　 Delete index ：drop index idxname
Note: The index cannot be changed. If you want to change it, you must delete it and rebuild it.
　9. Create view: create view viewname as select statement
　Delete view: drop view viewname
　10. 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%' ---like's syntax is very subtle, check Data!
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
Minimum: select min(field1) as minvalue from table1
　11. Several advanced query operators
　A: UNION operator
　UNION operator combines the other two results tables (such as TABLE1 and TABLE2) and derive a result table by eliminating any duplicate rows in the table. When ALL is used with UNION (that is, UNION ALL), duplicate rows are not eliminated. In both cases, every row in the derived table comes from either TABLE1 or TABLE2.
　B: 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 is used with EXCEPT (EXCEPT ALL), duplicate rows are not eliminated.
　C: INTERSECT operator
　The INTERSECT operator derives a result table by including only rows that are present in both TABLE1 and TABLE2 and eliminating any duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate rows are not eliminated.
Note: Several query result rows using operator words must be consistent.
　12. Use outer joins
　A. left (outer) join:
　Left outer join (left join): The result set includes the matching rows of 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 OUT JOIN b ON a.a = b.c
　B: right (outer) join:
　Right outer join (right join): The result set includes both the join table Matches join rows, including all rows in the right join table.
　 C: full/cross (outer) join:
Full outer join: not only includes matching rows of the symbolic connection table, but also includes all records in the two joined tables.
　13. Group by:
　A table. Once the grouping is completed, only group-related information can be obtained after querying.
Group-related information: (statistical information) count, sum, max, min, avg Grouping criteria)
When grouping in SQL Server: Fields of text, ntext, and image types cannot be used as the basis for grouping.
The fields in the selecte statistical function cannot be put together with ordinary fields;
14. Operate the database:
Separate database: sp_detach_db ; Attach the database: sp_attach_db followed by the statement that attachment requires a complete path name
　15. How to modify the name of the database:
　 sp_renamedb 'old_name', 'new_name'

The above is the content of the MYSQL classic statement - the basics, and more For related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!