MySQL Basic operation

MySQL Basic operation

To display the currently existing database:

show databases;

Database

Information_schema

Mysql

Performance_schema

Create a database

Create database user;

Note: Under UNIX, the database name is case-sensitive (unlike the SQL keyword), so you must always access the database with test, not test, test, or some other variable. The same is true for table names. (under Windows, this restriction does not apply, although you must use the same case in a given query to reference the database and the table.) However, for a variety of reasons, as a best practice, be sure to use the same casing as when the database was created. ）

Specify the database that is currently in use

Use Ouyangjun;

Querying the currently used database

Select Database ();

Create a table

CREATE TABLE Pet (

Name varchar (20),

Owner varchar (20),

Species varchar (20),

Sex char (1),

Birth DATE,

Death DATE

)

View the table for this database

Show tables;

View the structure of a table

Desc Pet;

View detailed SQL statements to create a table

Show create TABLE pet;

Loading data into a table

(1) via Load data

Load data local infile '/home/shiyanlou/data.txt ' into table pet;

(2) by insert

INSERT into Pet value (' Peffball ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);

The simplest way to retrieve all records from a table is to use the SELECT statement:

Mysql> SELECT * from pet;

Chances are you think Bowser's birthday doesn't look right. After querying, you find that the correct year of birth is 1989, not 1979.

Now there are at least two ways to fix this:

Edit the file "Pet.txt" to correct the error, then use delete and load data to empty and reload the table:

Mysql> DELETE from Pet;

mysql> LOAD DATA LOCAL INFILE ' pet.txt ' into TABLE pet;

However, before you do so, you need to reenter the puffball record.

Error logging can be corrected with an UPDATE statement:

Mysql> UPDATE Pet SET birth = ' 1989-08-31 ' WHERE name = ' Bowser ';

Update changes only the problematic records and does not require that the database tables be reloaded.

Select a special row

In general, you do not want to retrieve the contents of the entire table, especially when the table becomes very large. Instead, you are usually more interested in solving a specific problem, in which case you need to add some restrictions:

You can select only specific rows from a table.

For example, if you want to verify that your changes to Bowser's birthday are correct or in effect, select Bowser records as follows:

Mysql> SELECT * from pet WHERE name = ' Bowser ';

The output confirms that the correct year record is 1989, not 1979.

String comparisons are usually case insensitive, so you can specify the names as "Bowser", "Bowser", and so on, with the same query results.

You can specify a condition on any column, not just name. For example, if you want to know which animal was born after 1998, try filtering the birth column:

Mysql> SELECT * from pet WHERE birth > ' 1998-1-1 ';

You can also use the and statement to combine filter criteria, for example, to find a female dog:

Mysql> SELECT * from pet WHERE species = ' dog ' and sex = ' f ';

Has an and logical operator, then there is an OR operator:

Mysql> SELECT * from pet WHERE species = ' snake ' OR species = ' bird ';

And and or can be mixed, but and has a higher priority than or. If you use two operators, it is best to use parentheses to indicate how to group by criteria:

Mysql> SELECT * from pet WHERE (species = ' cat ' and sex = ' m ')

or (species = ' dog ' and sex = ' f ');

Select Special Columns

If you do not want to see all the rows in the table, you need to indicate the name of the column you are interested in and separate the column names with commas.

For example, if you want to know when your animal was born, select the name and birth column:

mysql> SELECT name, birth from pet;

To find out who owns a pet, you can use the following query:

Mysql> SELECT owner from Pet;

Note that the query simply retrieves the owner column for each record, but some names appear several times. To minimize the output (avoid duplication), increase the keyword distinct to retrieve each unique output record:

Mysql> SELECT DISTINCT owner from Pet;

You can use a WHERE clause to select rows and columns at the same time. For example, to query the date of birth of a dog and cat, use the following query:

mysql> SELECT name, species, birth from pet

WHERE species = ' dog ' OR species = ' cat ';

Row classification

You may have noticed that the rows in the previous example are not displayed in a particular order. However, when rows are sorted in some way, it is usually relatively simple to check the output of the query. To sort the results, use the ORDER BY clause. Here is the date of the animal birthday sorted by:

mysql> SELECT name, birth from pet ORDER by birth;

Because the string type of data is not sensitive to case. This means that the sorted results will not change for columns that are the same for different case. In this case, you can use binary to enforce a case-sensitive sort function.

such as: ORDER by BINARY Col_name.

The default sort is ascending, that is, the smallest value is ranked first. To sort in descending order, add the desc (descending) keyword next to the column name you are sorting:

mysql> SELECT name, birth from pet ORDER by birth DESC;

You can sort multiple columns, and you can sort different columns in different directions. For example, sort the animals in ascending order and then sort the animals by their birthdays in descending order (youngest animal at the top), using the following query:

mysql> SELECT name, species, birth from pet

ORDER by species, birth DESC;

Note The DESC keyword applies only to the column name (birth) that precedes it, and does not affect the sort order of the pecies column.

This article is from the "Ouyangjun" blog, make sure to keep this source http://ouyangjun.blog.51cto.com/10284323/1700373

MySQL Basic operation