An absolutely useful SQL knowledge Note

Organized a number of database table information and other operations SQL, slowly Add.

--------------------------------------------------

1. know a field name, how to find it in the database in which table?

Use INFORMATION_SCHEMA;
SELECT table_name from COLUMNS WHERE column_name= ' field name ';

MySQL View library table field information is in Information_schemal, get data dictionary and other information to pass this view.

Such as:

Select table_name from columns where column_name= ' user_id ';

2. How do I find a constraint?

3. How do I view the build statement?

Show create table tablename;

To view the TB1 table Structure command:

Show create table tb1;

Note that there is no quotation marks.

4. How do I view the table structure?

DESC TableName;

To view the TB1 table Structure command:

Desc TB1;

5. How do I view table structure modification records for a table?

MySQL does not support direct viewing and can only be viewed through log files.

Oracle and SQL Server can.

6.Mysql changing column names and data types

Methods to modify only the data type of a column:
ALTER TABLE table name modify column column name the type of the new columns
For example: The type of column sname in the student table is char (20), which is now modified to varchar, the SQL statement is as follows
ALTER TABLE student Modify column sname varchar (20);

A method that modifies only the column name, or both the column name and the data type of the column:
ALTER TABLE name change column old column name new column name (original type) new columns type
For example: The type of column sname in the student table is char (20), which is now modified to stuname varchar, the SQL statement is as follows
ALTER TABLE student Change column sname stuname varchar (20);

An absolutely useful SQL knowledge Note