Mysql-Database operations

Before the introduction of the database additions and deletions, and found that the database has forgotten some basic operations, such as building a library, build tables, change tables and so on. Then here's a brief summary of the basic operations of the database SQL form.

First, the library operation

1. Building a library

Before building a library, you might want to look at what libraries exist in the database, so that you can deal with repetitive problems.

show databases;

NEWDB is here because I have executed the following script to create the library.

Then, you can create the library.

Create DATABASE newdb;

To execute this sentence, the database newdb will be created by default encoding. What is the default encoding, and look at:

Create database newdb;

If you want to specify the encoding format when you create the library, you just need to follow the above pattern to add.

Create Database default character Set UTF8

In fact, the code is not tube, using the default way is good.

2. Delete the library--use with caution

Drop database newdb;

3. Referencing libraries

If you are in library 1th and want to query the table of library 2nd, then you need to use this

 use test;

Second, table operation

1. Build a table

Similarly, before you build a table, you query the table to see if it already exists.

Show tables;

If not, you can start creating a new table, if the library already has a table template, and you want to import some of the data when you create it, you should use this method:

Create Table Select *  from Tch_contact;

In this case, TCH_CONTACT2 can get the structure and data of the tch_contact, but it can not get anything else, such as: Index, foreign key, trigger.

Another kind of building a table is a little bit more complicated

CREATE TABLE' contact ' (' Id ' )int( One) not NULLAuto_increment,--not empty, auto-grow' TId 'int( One)DEFAULT NULL, ' QQ 'varchar( the)DEFAULT NULL, ' Weixin 'varchar( -)DEFAULT NULL, ' Phone 'varchar( the)DEFAULT NULL,  PRIMARY KEY(' Id '),--Set Primary Key  KEY' Index_tid ' (' TId ') USING BTREE,--Index  CONSTRAINT' Key_tid 'FOREIGN KEY(' TId ')REFERENCES' Tch_teacher ' (' Id ') on DELETE CASCADE  on UPDATENO ACTION--set foreign keys) COMMENT='That 's the watch .' --Table Notes

Sometimes need to use the temporary table, the method is very simple, just need to add a sentence in the middle of CREATE table temporary, the other, is the same as the general table:

Create temporary table name ' ()

1.1 Setting the primary key

The statements that set the primary key can be written separately or together.

int (one notNULL  PRIMARY Key Auto_increment, -- or int (one notNULL  auto_increment,PRIMARYKEY (' Id '),

1.2 Set index--index there is a single index and a composite index, but the setting method is the same

Inside MySQL, there are three types of indexes, Normal, Unique, Fulltext. The index method consists of two kinds: Btree, Hash

-- Single Index KEY ' index_weixin ' (' Weixin ') USING BTREE, UNIQUE KEY  key  ' index_qq ' (' QQ '),--  composite index key ' Index_fuhe ' ('  QQ', ' Weixin ') USING BTREE,

I generally only use normal, btree two, single and consistent with the use.

1.3 Foreign Key

CONSTRAINT FOREIGN KEY REFERENCES  on DELETE CASCADE  on UPDATE NO ACTION

It may be a little confusing to see, but look at the interface in the software, it's very clear

No need to explain the other, but there is a deletion, update, still need to nag two sentences.

MySQL here, there are four values to choose from.

RESTRICT-Limit, this limit is before the update.

Delete fails when tch_teacher data is deleted, if tch_contact has a value corresponding to it

NO ACTION-Limit, this limit is after the update. The time that is triggered by the restrict is different, but the function is the same.

CASCADE-Cascade, do not restrict deletion, but will be modified or deleted synchronously.

If Tch_teacher deletes a piece of data, then Tch_contact will delete the data associated with it synchronously.

The same is true for modifications, which are synchronized.

Set null--sets to NULL, if Tch_teacher deletes a piece of data, then tch_contact sets the field associated with it to NULL, not the entire data, just that field.

Here, I think the more practical is cascade, sync delete function, save me a lot of work.

2. Table Operations

Table operations are divided into table structure modification and table name modification. Look at the table structure first.

After the table was built, I found that the columns were not exactly what I wanted, so what?

2.1 Query Columns

 from Tch_contact;

2.2 Plus columns

Alter Table Add int ' Create Person ';

2.3 Modifying columns

Alter Table MODIFY varchar (1);

2.4 Minus Columns

Alter Table Drop  CreateBy;

2.5 index

-- Add an index Create Index  on Tch_contact2 (TID) using BTREE; Alter Table Add Index index_weixin (Weixin); -- If you want to modify an index, MySQL inside, only first delete and then rebuild -- Delete an index Drop Index  on Tch_contact2;

2.6 Foreign Key

--add a foreign keyAlter TableTch_contact2Add CONSTRAINT' Key_tid 'FOREIGN KEY(' TId ')REFERENCES' Tch_teacher ' (' Id ') on DELETE CASCADE  on UPDATENO ACTION;--Delete a foreign keyAlter TableTch_contact2DROP FOREIGN KEYKey_tid;

2.7 Table Name Modification

Rename Table  to Tch_contact1; ALTER TABLE tch_contact1 RENAME tch_contact;

Both of these are the statements modified by the table name.

3. Table deletion--use with caution

Drop table TCH_CONTACTT;

The most common in the project, in fact, is the above.

Mysql-Database operations