MySQL DDL operation--database

SQL is a very important programming language for manipulating relational databases

Structure Query Language since it is a language that can be programmed naturally, the classification of SQL can be divided into DDL,DML,DCL according to the different Operation objects.

We operate databases such as Create, add, update, delete operations, such as Operation tables, are all defined scopes.

So the DDL is the SQL definition language.

We want to use the database to store data, naturally there is a library, then there are tables, and then data, so we have to define the library, define the table.

1. Create a database

Create DATABASE db_name [DB Options] General options can not be filled in

As we create a student library:

Create database student;

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/85/E7/wKiom1etypXhHP7zAAAHT8x7yjQ116.png-wh_500x0-wm_3 -wmp_4-s_2488908867.png "title=" 1.png "alt=" Wkiom1etypxhhp7zaaaht8x7yjq116.png-wh_50 "/>

PS: When you create a database, the command rules for database names follow the following rules:

A, the command should follow the rules of the identifier, that is, letters, numbers, underline composition

B, the database name must not use the system built-in keywords or special symbols

C, if the use of special symbols or Chinese symbols to add the anti-quote ' '.

If you create a database in Chinese:

Create database ' student library ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/85/E7/wKioL1ety5-zKvA6AAAJQPBUkMg374.png "title=" 2.png " alt= "Wkiol1ety5-zkva6aaajqpbukmg374.png"/>

Then when we create the database, we automatically generate a directory in the data directory and a directory named after the database name, and there is also an. opt file, which is the database's options file, which stores the database option information. The default option is if we do not have the option to write the library when we create the database.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/85/E7/wKiom1etzGmgG3-TAACg6LbqQNE762.png-wh_500x0-wm_3 -wmp_4-s_77306886.png "title=" 3.png "alt=" Wkiom1etzgmgg3-taacg6lbqqne762.png-wh_50 "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/85/E7/wKiom1etzL2g-AYjAAEQgM-BMx8062.png "title=" 4.png " Width= "550" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:550px;height:440px; "alt=" Wkiom1etzl2g-ayjaaeqgm-bmx8062.png "/>

We noticed that if we were to create a database with a special symbol, then it was not garbled to use other encoded characters when we created the catalog, and we looked at the database results as follows:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/85/E7/wKioL1etzV-j5CkdAAASPRsswjI629.png "title=" 6.png " alt= "Wkiol1etzv-j5ckdaaasprsswji629.png"/>

So please do not mistakenly think this is garbled, this is a wrong understanding oh. Of course, it is recommended that you create a database to follow the rules of the identifiers to be named better.

We do not specify the corresponding library options when defining the database, in fact, the character encoding of the library and the collation of the characters, then we use how to define him:

Create database ' teacher ' character set UTF8;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/85/E7/wKiom1etzkKCXdyCAAAaERxwHs8772.png "title=" 7.png " alt= "Wkiom1etzkkcxdycaaaaerxwhs8772.png"/>

The options file for our open database is shown below:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/85/E7/wKioL1etzoyRbq_LAAB1Jdaq6NQ459.png "title=" 8.png " alt= "Wkiol1etzoyrbq_laab1jdaq6nq459.png"/>

Next we look at what databases are in the database server

Query command: show databases;

To view the creation information for the database: show-create DATABASE db_name;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/85/E7/wKiom1etzwqg020DAAAUlML-fM4420.png "title=" 9.png " alt= "Wkiom1etzwqg020daaaulml-fm4420.png"/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/85/E7/wKioL1etzz-zn56fAAAPyAGujcs835.png "title=" 10.png "alt=" Wkiol1etzz-zn56faaapyagujcs835.png "/>

These two query instructions are very simple, show databases is the query of which databases, and show create database db_name is to view the creation of databases information.

The database is created, and we sometimes don't want it. then delete the database

Delete command: Drop database db_name

Or: Drop database if exists db_name

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/85/E7/wKiom1et0DfB1raZAAAHkHoUkHA468.png "style=" float: none; "title=" 11.png "alt=" Wkiom1et0dfb1razaaahkhoukha468.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/85/E7/wKiom1et0DeQMaZlAAAGNBDXq6g884.png "style=" float: none; "title=" 12.png "alt=" Wkiom1et0deqmazlaaagnbdxq6g884.png "/>

The second instruction is deleted when the detection of the existence of the library, if there is deleted, there is no deletion will not error.

To modify a database:

You can modify library options such as character encoding for libraries

ALTER DATABASE db_name character set GBK;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/85/E7/wKioL1et0N_BE2xoAAAZDtsk2bQ784.png "title=" 13.png "alt=" Wkiol1et0n_be2xoaaazdtsk2bq784.png "/>

So how does the database name change? In the earlier version of the database is to support the Rename command, but the current version is not supported, if we want to update the database name, there are the following two solutions:

1, one is to update the database directory name directly under the data directory

2. Create a new database, then copy the table of the current library into the new library and delete it. The update operation of the data name was completed.

The above is the database creation, deletion, view, update operation.

Add a bit of our database server if you have many databases, you can do this again when you retrieve them:

Show databases like '%xx ' where% equals any character, string name ending with XX

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/85/E7/wKioL1et0n7AT9K-AAANz70HFig346.png "title=" 15.png "alt=" Wkiol1et0n7at9k-aaanz70hfig346.png "/>

The operation of the data table and the subsequent knowledge points are written according to the time situation. Welcome to the road friends advice.

This article is from "Le Learning" blog, please make sure to keep this source http://lestudy.blog.51cto.com/6378140/1837540

MySQL DDL operation--database