1, create a database.

You can use the creat database statement to create a database. The format of this statement is as follows:

Create database database name;

For example, create a database named bookstore: create a database bookstore;

Note: Use the show databases statement command to view all the databases currently created; The mysql statement ends with a semicolon.

2. Select the target database to operate.

Use the use statement to select the database to use. The format of this statement is as follows:

Use database name;

For example, using a bookstore database: using a bookstore; After successful selection, the system prompts that the database has changed.

Note: the use statement cannot use semicolons.

3. Create a database table

You can use the create table statement to create a data table, which is also the most common form:

Create a table table name (column declaration);

Take creating a student table as an example. The table stores the ID, name, gender, age and telephone number of students, and defines the data type.

Create table students (ID int unsigned not null auto _ increment primary key, name char(8) not null, sex char(7)not null, age tinyint unsigned not null, tel char( 13) null by default "-";

Statement comments: "id" is the name of the column; Int specifies that the type of the column is an integer (the value range is -8388608-8388607), and the type is unsigned with the decoration of "unsigned", and the value range of the column is 0-16777215; "not null" means that the value of this column cannot be empty; "auto_increment" must be used in an integer column, and its function is that if the column is NULL when inserting data null, MySQL will automatically generate a unique identifier value that is greater than the existing value. There can only be one such value in each table, and the column must be an index column; Primary key is the primary key, and the value of this column must be unique, and mysql will automatically index this column; Char(8) indicates that the stored character length is 8, and the value range of tinyint is-127 to 128. When the column value is empty, the default property specifies the default value.

4, table insert data

The Insert statement can insert one or more rows of data into a database table, and the general form is as follows:

Insert into the data table name (column name 1, column name 2 2, ...) value (value 1, value 2, ...);

To insert a record into the student data table of the bookstore database, execute the following statement:

Insert into the student value ("20 1900 19", "Yang Fei", "female", 26, "19932714061");

Insert into the student value ("20 1900 18", "jerry", "nan", 25, "15100552378");

Sometimes we only need to insert part of the data, or insert it out of column order. We can insert it in this form:

Inserted into the student (name, gender, age) value ("Sun Lihua", "female", 21);

4. Query the data in the table

Select statement obtains data from the database according to certain rules, and its basic structure is as follows:

Select a column name from Table Name [Query Criteria];

For example, query the data before the age of 23 in the student table:

Select the age from the students, where the age is & gt23;

You can also use the wildcard * to query everything in the table. Statement: select * from students

Where is used for conditional search, and the usage form is: select the column name from the table name where condition;

For example, query the names of people over 23 years old and with gender nv:

Select names from students over age; 23 and sex = "nv

5. Update the data in the table

Update can be used to modify the data in the table. The basic forms of use are:

Update table name set column name = new value where update condition.

Example 1: Change the gender value in the table students as nv to girl:

Update student settings sex="girl "where sex =" nv.

Example 2: The name with id of 20 1900 19 was changed to Yang Fei, the age was changed to 18, and the sex was changed to female;

Update the student collection name = "Yang Fei", age = 18, gender = "female" where ID = "20190019";

6. Delete the data in the table

Delete is used to delete the data in the table. The basic usage forms are as follows:

Delete from the table name where the deletion condition exists;

Example 1: Delete the row with the id of 20 190020 in the student table:

Delete from students, where ID = "20190020";

Example 2: Delete all data in the table:

Delete from students;

7. Changes to the table after creation

Alter table is used to modify a table after it is created. The basic usage is as follows:

(1) Add column:

Alter table table name add column name data type [after insert position];

Example 1. Insert the column mail after age in the students table:

Alter table Add mail characters (20) after the age of students;

Example 2. Insert the column address at the end of the students table:

Alter table students add address characters (60);

(2) Modify the column

Alter table table name change column name new name new column data type;

Example 1: change the column name tel in the student table to telephone, and default to "-"when no value is filled in:

Change Table Students change the default "-"of telephone char(20);

(3) Delete columns

alter table table name drop column name；

Example 1: Delete the mail column in the student table:

Change the form for students to submit mail;

(4) Rename the table name

Rename the table "Students" to "Classmates":

Alter table changed its name to classmate;

(5) Delete the whole table.

Delete the table name;

(6) Delete the whole database

Delete the database database name;

8. Show all coding attributes:

Display a variable similar to "character_set_%";