Mysql and mysql download

Mysql and mysql download

I have introduced the addition, deletion, modification, and query of databases and found that some basic operations of the database have been forgotten, such as creating databases, creating tables, and modifying tables. here we will summarize the basic operations in the form of Database SQL.

I. Database Operations

1. Create a database

Before creating a database, you may need to check which databases already exist in the database to handle repeated problems.

show databases;

Here, newdb appears because I have executed the following script for creating a database.

Then, you can create a database.

create DATABASE newdb;

Run this statement to create the database newdb according to the default encoding method. What is the default encoding? Let's take a look:

show create database newdb;

If you want to specify the encoding format when creating a database, you only need to add it in the above mode.

create database newdb2 default character set utf8

In fact, you don't need to worry about encoding. Use the default method.

 

2. delete a database-use it with caution

drop database newdb;

 

3. Reference Library

If you want to query the table of database No. 2 in database No. 1, you need to use this

use test;

 

Ii. Table operations

1. Create a table

Before creating a table, you must check whether the expected table already exists in the table.

show tables;

If no, you can create a new table. If you already have a table template in the database and want to import some data when creating a table, use the following method:

create table tch_contact2 select * from tch_contact;

In this case, tch_contact2 can obtain the structure and data of tch_contact, but cannot obtain anything else, such as indexes, foreign keys, and triggers.

Creating another table is a little complicated.

Create table 'Contact '('id' int (11) not null AUTO_INCREMENT, -- NOT empty, automatically increasing 'tid' int (11) default null, 'qq' varchar (15) default null, 'weixin' varchar (50) default null, 'phone' varchar (15) default null, primary key ('id '), -- set the primary KEY 'index _ tid' ('tid') using btree, -- Index CONSTRAINT 'key _ tid' FOREIGN key ('tid ') REFERENCES 'tch _ teacher '('id') on delete cascade on update no action -- set foreign key) COMMENT = 'this table' -- table COMMENT

Sometimes you need to use a TEMPORARY table, and the method is very simple. You only need to add a TEMPORARY to the create table. Others are the same as the general table creation:

Create temporary table 'table name '()

1.1 set primary keys

The statement that sets the primary key can be written separately or together.

'Id' int (11) not null primary key AUTO_INCREMENT, -- or 'id' int (11) not null AUTO_INCREMENT, primary key ('id '),

1.2 set indexes -- the index contains a single index and a composite index, but the setting method is the same.

In mysql, there are three types of indexes: Normal, Unique, and FullText. There are two Indexing Methods: Btree and Hash.

-- Single Index key'index _ Weixin '('weixin') using btree, UNIQUE key'index _ tid' ('tid') using hash, fulltext key 'index _ qq' ('qq'), -- composite Index key' Index _ fuhe '('qq', 'weixin') using btree,

Generally, I only use Normal and Btree, and use a single sum.

1.3 foreign key

CONSTRAINT `key_tid` FOREIGN KEY (`TId`) REFERENCES `tch_teacher` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION

A sentence may be confusing, but it is very clear to look at the interface in the software.

You do not need to explain anything else, but there is a delete here. When updating, you still need to say a few things.

Mysql has four values to choose from.

RESTRICT -- restriction, which is before update.

When deleting tch_teacher data, if tch_contact has a corresponding value, the deletion fails.

No action -- limit. This limit is different from the time triggered by Restrict after the update, but the function is the same.

CASCADE-CASCADE, which does not limit deletion, but is modified or deleted synchronously.

If tch_teacher deletes a piece of data, tch_contact deletes the data associated with it synchronously.

The same is true for modifications.

Set null -- SET to null. If tch_teacher deletes a data entry, tch_contact sets the field associated with it to NULL instead of the entire data entry.

Here, I think cascade is more practical. The synchronous deletion function saves me a lot of work.

 

2. Table operations

Table operations are divided into Table Structure Modification and table name modification. first look at the table structure.

After the table is created, I find that the columns are not exactly what I want. What should I do?

2.1 query Columns

show COLUMNS from tch_contact;

2.2 Columns

Alter table tch_contact add createby int comment 'creator ';

2.3 Modify columns

alter table tch_contact MODIFY createby varchar(1);

2.4 column reduction

alter table tch_contact drop  createby;

2.5 Index

-- Create index index_tid on tch_contact2 (tid) using BTREE; alter table tch_contact2 add index index_weixin (weixin); -- if you want to modify an index in mysql, only Delete and then recreate -- delete an index drop index index_tid on tch_contact2;

2.6 foreign key

-- Adds a FOREIGN key alter table tch_contact2 add CONSTRAINT 'key _ tid' foreign key ('tid') REFERENCES 'tch _ teacher '('id ') on delete cascade on update no action; -- DELETE a foreign key alter table tch_contact2 drop foreign key key_tid;

2.7 table name Modification

rename table tch_contact to tch_contact1;ALTER TABLE tch_contact1 RENAME tch_contact;

These two statements are used to modify the table name.

3. Table deletion-use with caution

drop table tch_contactt;

This is the most commonly used project.