MySQL Study Notes _ 9_MySQL Advanced Operations (on) and study notes _ 9_mysql

MySQL Study Notes _ 9_MySQL Advanced Operations (on) and study notes _ 9_mysql
 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_nameas 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.