MySQL Learning note _9_mysql advanced operations (top)

 MySQL advanced Operation (top)

First, MySQL Table Replication

CREATE table t2 like T1; # Copy table structure,T2 can learn All the table structure of T1

INSERT INTO T2 select * from T1; # Copy the table data, but there is still a flaw, because the column does not take into account the corresponding, because the T1 and T2 table structure exactly the same, so this operation will not be wrong!

Suggestions:

insert into t3 (name) select name from T1; # Specify the copied columns

Second, MySQL Index

1 , create indexes directly

CREATE INDEX index_name on table_name (column_list); # Create a normal index

Create unique index index_name on table_name (colume_list); # Create a unique index, make sure the column does not have duplicate values before creating a unique index, otherwise, the creation is unsuccessful!

2 , delete the index directly

DROP INDEX index_name on table_name;

3 , modify - Create an index

ALTER TABLE table_name ADD index [index_name] (colum_list); # Create a normal 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 primary key index, if not add index_name, use column_list as default index name

4 , modify - Delete Index

ALTER TABLE table_name DROP INDEX index_name; # Delete normal / unique index

ALTER TABLE table_name drop PRIMARY key; # Delete primary key index

"Recommended ways to use 3 , 4 "

Attached

1. View index: Show index from T1 \g

2 , ALTER TABLE table_name MODIFY ID int not null;

Third, MySQL View

View: A condition in which a part of the data is extracted from a table, forming an intermediate table, which is the view

Note: The view changes as the main table changes

1 , create a view

Create VIEW view_name as select *from table_naem where ID > 4 and ID <= 10;

3 , see which views are created

showtables; # A view is an intermediate table

3 , view data in view

select* from view_name; # Same as view table data

4 , delete view

Drop View view_name;

Four, MySQL built-in function supplement

See functions and simple examples: ? function_name

e.g.? LCase

1 , String Functions

1 ) LCase ("string")/ucase ("String ") # Convert to lowercase / uppercase, with Lower (str)/upper (str) Same Effect

2 ) Length ("string") # returns the length of a string

3 ) repeat ("string", N) # duplicate a character from a N Times

4 ) space (n) # Build N a space

2 , Mathematical functions

1 ) bin (decimal_number) # Decimal Turn binary

2 ) ceiling (n) # Role and Ceil Same, rounding down

3 ) sqrt (n) # Open Square

4 ) Max (col)/min (Col) # Take maximum / minimum value, used when aggregating

5 ) rand () # Generate random numbers

SELECT * FROM table_name ORDER by RAND (); # Use Rand function as a sort datum

3 , date functions

1 ) DateDiff (EXPR1,EXPR2) # return Expr1 and the EXPR2 difference in number of days, if expr1> expr2 , a positive value is returned

MySQL Learning note _9_mysql advanced operations (top)