MySQL Advanced Operations (I)
I. MySQL table replication
Create table t2 like t1; # copy the table structure. t2 can learn all the table structures of t1.
Insert into t2 select * from t1; # copy table data, but this still has a defect, because the corresponding column is not taken into account, because t1 and t2 table structures are completely consistent, so this operation will not go wrong!
Suggestion:
Insert into t3 (name) select name from t1; # specify the copied Column
Ii. MySQL Index
1. directly create an index
Create index index_name on table_name (column_list); # create a common index
Create unique index index_name on table_name (colume_list); # create a unique index. Before creating a unique index, make sure that the column has no duplicate values. Otherwise, the creation is unsuccessful!
2. Directly Delete the index
Drop index index_name on table_name;
3. Modify-create an index
Alter table table_name add index [index_name] (colum_list); # create a common index
Alter table table_name add unique [index_name] (column_list); # create a unique index
Alter table table_name add primary key [index_name] (column_list); # create a primary key index. If no index_name is added, use column_list as the default index name.
4. Modify-delete an index
Alter table table_name drop index index_name; # Delete normal/unique Indexes
Alter table table_name drop primary key; # delete a primary key index
[Recommended Methods: 3 and 4]
[Appendix]
1. View indexes: show index from t1 \ G
2. alter table table_name modify id int not null;
Iii. MySQL View
View: extract part of the data from a table using a condition to form an intermediate table. This table is a view.
Note: The view changes with the change of the master table.
1. Create a view
Create view view_name as select * from table_naem where id> 4 and id <= 10;
3. View created views
Showtables; # The view is an intermediate table.
3. View data in the view
Select * from view_name; # The same as viewing table data
4. delete a view
Drop view view_name;
Iv. MySQL built-in function supplement
View functions and simple examples :? Function_name
E.g .? Lcase;
1. String Functions
1) lcase ("string")/ucase ("string") # converts it to lowercase or upper case, which works the same as lower (str)/upper (str ).
2) length ("string") # returns the length of the string.
3) repeat ("string", n) # repeat characters from n times
4) space (n) # generate n Spaces
2. mathematical functions
1) bin (decimal_number) # convert decimal to binary
2) ceiling (n) # act the same as ceil, rounded down
3) sqrt (n) # Square
4) max (col)/min (col) # obtain the Maximum/minimum value, which is used for aggregation.
5) rand () # generate a random number
Select * from table_name order by rand (); # Use the rand function as the sorting benchmark.
3. Date Functions
1) datediff (expr1, expr2) # returns the number of days between expr1 and expr2. If expr1> expr2, a positive value is returned.