Mysql _ syntax summary _ MySQL

Mysql _ syntax summary bitsCN.com

Environment: mysql 5.5

Create database test:

1 create database 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> show databases;+--------------------+| Database           |+--------------------+| information_schema || drp                || duona              || expert             || jeecgv3            || mysql              || performance_schema || sample             || test               || test_uuuu_test     |+--------------------+10 rows in set

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.

// Return to the state where test is not deleted

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

mysql> use test;Database changed

We need to check the number of tables in the test Database. you can use:

mysql> show tables;Empty set

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

Now let's add a table to it!

mysql> create table student(id int not null primary key,name varchar(20) not null unique);Query OK, 0 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> show tables;+----------------+| Tables_in_test |+----------------+| student        |+----------------+1 row in set

You will find that the student table is now added.

We can also look at student's table structure:

mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(20) | NO   | UNI | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set

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> alter table student add age int;Query OK, 0 rows affectedRecords: 0  Duplicates: 0  Warnings: 0

Let's look at the table structure:

mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(20) | NO   | UNI | NULL    |       || age   | int(11)     | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+3 rows in set

It indicates that the age field is added.

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

mysql> alter table student add birthday date;Query OK, 0 rows affectedRecords: 0  Duplicates: 0  Warnings: 0

Let's check the structure of the student table:

mysql> desc student;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | NO   | PRI | NULL    |       || name     | varchar(20) | NO   | UNI | NULL    |       || age      | int(11)     | YES  |     | NULL    |       || birthday | date        | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set

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

Now we need to delete the birthday field:

mysql> alter table student drop column birthday;Query OK, 0 rows affectedRecords: 0  Duplicates: 0  Warnings: 0

View the table structure:

mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(20) | NO   | UNI | NULL    |       || age   | int(11)     | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+3 rows in set

The deletion is successful!

Insert data to the student table:

mysql> insert into student values(1,'hongten',20);Query OK, 1 row affected

mysql> insert into student(id,name,age) values(2,'hanyuan',30);Query OK, 1 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> select * from student;+----+---------+-----+| id | name    | age |+----+---------+-----+|  1 | hongten |  20 ||  2 | hanyuan |  30 |+----+---------+-----+2 rows in set

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

mysql> select name from student;+---------+| name    |+---------+| hanyuan || hongten |+---------+2 rows in set

The above is only a query for the name field;

Now perform the conditional query:

mysql> select name,age from student where id = 2;+---------+-----+| name    | age |+---------+-----+| hanyuan |  30 |+---------+-----+1 row in set

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> select count(*) from student;+----------+| count(*) |+----------+|        2 |+----------+1 row in set

Query student with different names:

mysql> select distinct(name) from student;+---------+| name    |+---------+| hanyuan || hongten |+---------+2 rows in set

Modify the name field of id = 2:

mysql> update student set name = 'Tom' where id =2;Query OK, 1 row affectedRows matched: 1  Changed: 1  Warnings: 0

Query the student table:

mysql> select * from student;+----+---------+-----+| id | name    | age |+----+---------+-----+|  1 | hongten |  20 ||  2 | Tom     |  30 |+----+---------+-----+2 rows in set

Now delete the record with id = 2:

mysql> delete from student where id = 2;Query OK, 1 row affectedmysql> select * from student;+----+---------+-----+| id | name    | age |+----+---------+-----+|  1 | hongten |  20 |+----+---------+-----+1 row in set

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

BitsCN.com