Mysql _ syntax Summary

Environment: mysql 5.5

Create Database test:

   test;

We can execute the code to create the test database. If you want to check whether the database test has been created, you can use the code to view all the databases in mysql.

show databases;

What I found on my machine is:

mysql             information_schema  drp                 duona               expert              jeecgv3             mysql               performance_schema  sample              test                test_uuuu_test      rows  

We can see that we have created the test database in the database.

Now we have selected the test database. If we want to delete the test database, we only need to use the following SQL statement:

mysql> drop database test;Query OK, 0 rows affected

Now we can query all the databases in mysql again:

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || drp                || duona              || expert             || jeecgv3            || mysql              || performance_schema || sample             || test_uuuu_test     |+--------------------+9 rows in set

We can find that the test database has been deleted.

Now we need to operate the test database. We can use the following code and select the database test:

mysql  changed

We need to check the number of tables in the test database. You can use:

mysql

We can clearly see that there are no tables in test.

Now let's add a table to it!

mysql   student(id     ,name ()    rows affected

We added a student table to the test database, which contains the id and name fields.

Now let's take a look at the table in test:

mysql Tables_in_test  student         row  

You will find that the student table is now added.

We can also look at student's table structure:

mysql  Field  Type               Extra  id     ()      NO    PRI              name   ()  NO    UNI              rows  

So you can guess the SQL statement used to create the student table above:

The student table has two fields: id and name. For the id field, the type is int integer. The default mysql length is 11. The id field cannot be null, that is, it cannot be blank and the id field is the primary key, that is, primary key, no default value

For the name field, which is similar to the id field, the type is varchar, the length is 20, and cannot be blank. It is unique in the table (unique, the same value cannot appear in the same table), no default value

Now we want to add a field age in the student table, whose type is int:

mysql   student  age   Duplicates:   Warnings: 

Let's look at the table structure:

mysql  Field  Type               Extra  id     ()      NO    PRI              name   ()  NO    UNI              age    ()      YES                    rows  

It indicates that the age field is added.

Now we need to add the birthday field of the date type:

mysql   student   Duplicates:   Warnings: 

Let's check the structure of the student table:

mysql  Field     Type               Extra  id        ()      NO    PRI              name      ()  NO    UNI              age       ()      YES                    birthday  date         YES                    rows  

We have added the birthday field and the type is date.

Now we need to delete the birthday field:

mysql   student    Duplicates:   Warnings: 

View the table structure:

mysql  Field  Type               Extra  id     ()      NO    PRI              name   ()  NO    UNI              age    ()      YES                    rows  

The deletion is successful!

Insert data to the student table:

mysql   student (,, row affected

mysql   student(id,name,age) (,, row affected

Both methods insert data to the student table. The first method does not write the field name, and the value following it is the value of all fields.

The second method is to write all fields, followed by the value of the field.

Query the student table now:

mysql    id  name     age     hongten        hanyuan     rows  

We can see that the two groups of data we inserted have been saved to the database. Now we can query a field:

mysql  name  name     hanyuan  hongten  rows  

The above is only a query for the name field;

Now perform the conditional query:

mysql  name,age  student  id   name     age  hanyuan     row  

For name and age queries in the student table, the condition is the record with id = 2;

Query the total number of records in the student table:

mysql  ()  ()           row  

Query student with different names:

mysql  (name)  name     hanyuan  hongten  rows  

Modify the name field of id = 2:

mysql  student  name    id   Changed:   Warnings: 

Query the student table:

mysql    id  name     age     hongten        Tom         rows  

Now delete the record with id = 2:

mysql   student  id      id  name     age     hongten     row  

.................................. It's time to come here today !!!