Mysql basics: create a database, add, delete, modify, and query _ MySQL

Mysql basics: create databases, add, delete, modify, and query bitsCN.com

Mysql basics: database creation, addition, deletion, modification, and query

================ View the database ================================= =

Use the SHOW statement to find out the current database on the server:

[SQL]

Mysql> show databases;

+ ---------- +

| Database |

+ ---------- +

| Mysql |

| Test |

| Tmp |

+ ---------- +

Mysql is required because it describes the user's access permissions. The test database is often used as a workspace for users to test their skills.

Note that if you do not have the show databases permission, you cannot view all DATABASES.

There are multiple databases here. you need to select one before operating the database and use the use command

Mysql> USE test

Database changed

Note that USE, similar to QUIT, does not require a semicolon.

================ Create a database, table ====================================== =

The create command is used to create a database.

[SQL]

Mysql> create database tmp;

Query OK, 1 row affected (0.03 sec)

Mysql> use tmp // use this database

Database changed

You need to use the use command again to select the database for each login. if you do not want to be so troublesome, you can specify

# Mysql-uroot-p password database name

#

After creating a database, you can create a table.

First, view the current table and use show tables;

[SQL]

Mysql> show tables;

Empty set (0.00 sec) // Empty table currently

Mysql> create table person (name VARCHAR (20), sex char (1), score int (3); // table name person, including name type: varchar; length: 20, sex is 1-bit char, score is 3 is an integer

View the table information and data type. use the describe table name.

====================================== Fill in the table content ================== ======================

After creating a table, enter the content. You can use the load data and INSERT statements to complete the task.

Because you started from an empty table, one simple way to fill it is to create a text file, each row of each person, and then load the file content into the table with a statement.

You can CREATE an external person.txt file. each line contains a record, which is separated by a tab and given in the order of the columns listed in the create table statement. You can use NULL for missing values (such as unknown gender, or dead dates of living animals. To represent the content in your text file, use/N (backslash, letter N ). For example, the record of the Whistler bird should be (the blank space between values here is a locator ):

Load

Add one or more insert statements

[SQL]

Mysql> insert into person values ('sin', 'M', '90 ');

Query OK, 1 row affected (0.00 sec)

There are two ways to modify the table content:

1. clear the table content and load it from the file again.

After modifying the file content, use the load data command again to load

[SQL]

Mysql> delete from person;

Query OK, 3 rows affected (0.00 sec)

Mysql> load data local infile '/root/person.txt' into table person;

Query OK, 3 rows affected (0.00 sec)

Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

2. use the update command

[SQL]

Mysql> update person set sex = "m" where name = "hexizhuo ";

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql> select * from person;

+ -------------- + ------ + ------- +

| Name | sex | score |

+ -------------- + ------ + ------- +

| Chen | m | 100 |

| Yang | m | 60 |

| Hex | m | 80 |

| Xin | m | 90 |

+ -------------- + ------ + ------- +

4 rows in set (0.00 sec)

Mysql>

====================================== Query table content ====================== ======================

Use the select command

The SELECT statement is used to retrieve information from a data table. The statement format is as follows:

Mysql> SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

What_to_select indicates the content you want to see. it can be a table of a column, or * indicates "all columns ".

Which_table indicates the table from which you want to retrieve data.

The WHERE clause is optional. if this option is selected,

Conditions_to_satisfy specifies the search condition that the row must meet.

[SQL]

Mysql> select * from person; // retrieve all information

+ -------------- + ------ + ------- +

| Name | sex | score |

+ -------------- + ------ + ------- +

| Chen | m | 100 |

| Yang | m | 60 |

| Hex | NULL | 80 |

| Xin | m | 90 |

+ -------------- + ------ + ------- +

4 rows in set (0.00 sec)

Mysql>

Mysql> select * from person where name = "chenjiankang ";

+ -------------- + ------ + ------- +

| Name | sex | score |

+ -------------- + ------ + ------- +

| Chen | m | 100 |

+ -------------- + ------ + ------- +

1 row in set (0.00 sec)

Mysql> // The logical operator and is used below. the other operator is or.

Mysql> select * from person where sex = "m" and score> 80;

+ -------------- + ------ + ------- +

| Name | sex | score |

+ -------------- + ------ + ------- +

| Chen | m | 100 |

| Xin | m | 90 |

+ -------------- + ------ + ------- +

2 rows in set (0.00 sec)

Mysql> select name, sex from person; // select several columns. you can also add where to filter data.

+ -------------- + ------ +

| Name | sex |

+ -------------- + ------ +

| Chen | m |

| Yang | m |

| Hex | m |

| Xin | m |

+ -------------- + ------ +

4 rows in set (0.00 sec)

Mysql> select name, sex from person where sex = "f ";

Empty set (0.00 sec)

If two of your data are the same, for example, two names are qq, as shown below:

Mysql> select name from person;

+ ---------- +

| Name |

+ ---------- +

| Siasjack |

| Qq |

| Hehe |

| Xin |

| Qq |

+ ---------- +

5 rows in set (0.00 sec)

You can add the distinct option if you don't want him to appear here for multiple times.

Mysql> select distinct name from person;

+ ---------- +

| Name |

+ ---------- +

| Siasjack |

| Qq |

| Hehe |

| Xin |

+ ---------- +

4 rows in set (0.00 sec)

Sort and print functions

Use the order option. to print the score in reverse order, add the desc parameter.

Mysql> select name, score from person order by score;

+ ---------- + ------- +

| Name | score |

+ ---------- + ------- +

| Qq | 60 |

| Hehe | 80 |

| Qq | 88 |

| Xin | 90 |

| Siasjack | 100 |

+ ---------- + ------- +

5 rows in set (0.00 sec)

Pattern matching

MySQL provides standard SQL mode matching and an extended regular expression mode matching format based on Unix utilities such as vi, grep, and sed.

SQL mode matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ).

In MySQL, the SQL Mode is case-insensitive by default. The following are some examples. Note that = OR! =; Instead, use the LIKE or not like comparison operator.

[SQL]

Mysql> select * from person where name like '% sias %'; // The name starts with sias or contains

+ ---------- + ------ + ------- +

| Name | sex | score |

+ ---------- + ------ + ------- +

| Siasjack | m | 100 |

+ ---------- + ------ + ------- +

1 row in set (0.00 sec)

Mysql> select * from person where name like '____'; // four characters in the name

+ ------ + ------- +

| Name | sex | score |

+ ------ + ------- +

| Hehe | f | 80 |

+ ------ + ------- +

1 row in set (0.00 sec)

BitsCN.com