Shulou(Shulou.com)06/01 Report--

DDL statement:

DDL statements are mostly used by database administrators (DBA) to create, delete, modify and other operations on objects within the database. Developers rarely use SQL statements to perform various operations on the database after logging in to mysql.

Practical operation:

Before the basic operation, if you do not have mysql installed, you can refer to an article I wrote earlier (windows) windows command line to use mysql

Also, MYSQL is not case-sensitive, so here we use lowercase for the demonstration to see clearly.

Show databses; # check the semicolon behind the database list. Don't leave behind!

1) create a database

1 > use create database database name

2 > Select the database to operate: USE database; for the database to operate, we need to use use to select it!

3 > View all the data tables in the database show tables

2) Delete the database:

Drop database database name

We create a database of will_del and then delete it

3) create tables (in which database you need to use use to select the database you want to operate on)

1 > create a table

Create table table name (

Constraints of field 1 name field 1 type column

Constraints of field 2 name field 2 type column

.)

2 > you can view the definition of the table after you have created the table

Desc table name

3 > View the SQL statement that creates the table

Show create table table name\ G

The\ G option enables records to be arranged vertically according to fields to better display records with longer content, without the need for a semicolon after\ G.

Let's create a student list with fields with student number (ID) and first name (name), and simply demonstrate it.

First, we need to use a database (in which database to create tables)

Create an student data table

View the definition of the creation table

View the sql statement that creates the table

4) delete the table

Drop table table name

We can add any field to create a test table to demonstrate

5) modify the table

1 > modify the field type of the table

Alter table table name modify [column] field definition [first | after field name]

We changed the field type of student table ID from Int (11) to Int (12).

2 > add table field

Alter table table name add [column] field definition [first | after field name]

We add a sex gender field to the student table

3 > Delete a table field

Alter table table name drop [column] field name

Let's delete the sex that we just added.

4 > rename the field

Alter table table name change [column] Old field name field definition [first | after field name]

We changed the ID field to (number) and the field type to int (11)

Note: both change and modify can modify the definition of the table. The difference is that change needs to be followed by two column names, which is not convenient, but the advantage is that change can modify field names.

5 > modify field arrangement and sort

In the field addition and modification syntax (add/change/modify) described earlier, there is an optional first | after field name, which can be used to modify

Let's put the name field in the first

Alter table student modify Name tinyint first

Let's get it behind the number.

Alter table student modify Name tinyint after number

The position of the field in the table the new field is loaded in the last position in the table by default, while change/modify does not change the position of the field by default

Note: change/first | after field name these keywords belong to the extension of MySQL on standard SQL and may not be applicable to other databases

6) change the table name

Alter table table name rename [to] New table name

We changed student to md.

Alter table student rename to md

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.