MySQL (5): Table crud

Source: Internet
Author: User

Prefix of table name:

Two student tables, in order to distinguish between different applications of the same logical table name, add a prefix to the logical table name

Student Management

CREATE TABLE info_Student (

Name varchar (20),

Stu_no varchar (20)

);

Online Exams

CREATE TABLE EXAM_Student (

Name varchar (20),

Stu_no varchar (20),

Score int

);

1. What are the tables?

Show tables;

2.

Show tables [like ' pattern '];

Where the like pattern section represents the table name that only gets that rule

Show tables like ' exam_% '; -----% represents the combination of any character----called a wildcard character

3. Creating information for a table

Show CREATE TABLE exam_student;

You can also use:

Show CREATE table exam_student\g (data very often, so the display is easy to read)

4. View the structure of the table (describes the structure of the table)

Describe Exam_student;

It can also be written as follows:

Desc info_student;

Database corresponding to the directory , it is obvious that the database content corresponds to the contents of the directory, files .

We traced the database file above the disk to the following :

Here's the frm file: The list structure is stored

5. Delete a table

drop table tbl_name;

If the table does not exist, performing the delete operation will be an error, and sometimes this can be done to avoid this error :

drop table if exists Democlass (also for drop database if exists db_name)

6. Modify the table

(1) Modify table name

Rename: Rename table old_tbl_name to new_tbl_name;

Rename to multiple tables at the same time:

Rename table info_student to Infos, exam_class to classes;

Above we can first show tables get all the tables, and then batch modify Rename table Info_student to Infos, exam_class to classes, ... ;

Support for cross-database renaming:

From the infos list in the original database Php_one to the Infos list in the demo database

Let's go in and see what's in the demo data:

The above supports cross-database renaming, which enables database renaming.

Re-create a new database, the old database table, all rename into the new database, delete the old database;

(2) Modifying a column definition

Modify the table structure, above the sub-command, the superior is, ALTER TABLE tbl_name [add| Drop| Change | Modify]

Add (Add a new column):

Modify (Modify the definition of a column): modify the type and properties of a column

Drop (delete a column):

Change (Rename a column): provide the renamed service

(3) Modify table options

ALTER TABLE Tbl_name the new table option ;

MySQL (5): Table crud

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.