1. Create a database
- CREATE DATABASE database_name;
2. Delete Database
- DROP DATABASE database_name;
3. Create a table
- A: Create A new table with the old table
- CREATE table new_table_name like old_table_name;
- B: Create a table with the data from the old table
- CREATE TABLE New_table_name as select Col1,col2...coln from Old_table_name only;
4. Delete new Table
5. Add a column
- ALTER TABLE table_name ADD column col_name type;
6. Add PRIMARY Key
- ALTER TABLE TABLE_NAME ADD PRIMARY key (Col_name);
7. Edit Index
- A: Create an index:
- CREATE INDEX index_name on table_name (COL_NAME_01,COL_NAME_02);
- B: Delete index:
- DROP INDEX index_name on table_name;
(Note: The index cannot be changed, you want to change only delete and then add again)
8, a few basic SQL statements
- Selection: Select Col_name from table where condition;
- Insert: INSERT INTO table (COL_NAME,COL_NAME01,COL_NAME02 ...) VALUES (value01,value02,value03 ...);
- Delete: Delete from table where condition;
- Update: The Update table set col_name=value_new where condition;
- Find: SELECT * FROM table_name where col_name like '%value% ';
- Sort: SELECT * FROM table_name order BY Col_name;
- Total: Select count as TotalCount from table_name;
- Sum: Select SUM (col_name) as sumvalue from table_name;
- Average: Select AVG (col_name) as avgvalue from table_name;
- Max, min: Max (col_name) min (col_name);
9. Use external connection
- A:left join left joins: The result set includes matching rows for the join table, and also includes all rows of the LEFT join table
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 Join right: The result set includes matching rows for the join table, and all rows with a joined table
- C:\full/cross Join full join: The result set includes matching rows for the join table, and also includes all records for two join tables
10. Database Grouping
GROUP BY
11. Modify the database name
Sp_renamedb ' old_db_name ' new_db_name ';
SQL Statement Basics
