Mysql database operation statement and mysql statement

Mysql database operation statement and mysql statement

Mysql format statement Specification

How to log on to your database?

Example!

If it is the cost of compilation and installation, you have to go to the directory after compilation and installation. I installed it under/usr/local/mysql.
Log on to the database:
Cd/usr/local/mysql
Bin/mysql-u root-p
Enter the password

Mysql-> show databases; view the database

Remember to include every sentence executed;




I. mysql common statements

Create, delete, and basic query:
Display the database mysql-> showdatabases;
Create a database mysql-> createdatabase database name;
Delete database mysql-> drop database name;
Select database mysql-> use Database Name
Create a table mysql-> create table name (name varchar (20), sex (char (1), birth date); (parameters in brackets, that is, the primary key)
Delete the table mysql-> drop table name;
Displays the table content mysql-> show tables;
Displays the table structure mysql-> describe database name. Table Name;

Update:
1. Column Operations:
Add the mysql-> alter table yourtableadd name varchar (20) notnull field to a table;
Delete a field mysql-> altertable yourtable drop name;
2. Operations on rows:
Insert a record mysql-> insert into mytablevalues ('summer ', 'M', '2017-08-24 ');
Delete a record mysql-> deletefrom mytable where name = 'summer ';
Modify a record mysql-> updatemytable set sex = 'vm 'where name = 'summer ';
Insert multiple records mysql-> insert into mytable select * fromyourtable ;(
In this form of INSERT statements, the data value of a new row is not explicitly specified in the statement body, but a database query specified in the statement. Logical restrictions of this query:
» The query cannot contain the order by clause.» the query result should contain columns with the same number of columns as in the INSERT statement, and the data type must be column-BY-column compatible .)

Simple query:
1. display the column name in the query results
A. Use the as Keyword: select name as 'name' from students order by age
B. Direct representation: select name 'name' fromstudents order by age
. Exact search:
A. Use in to specify the range: select * from students where native in ('hunan ', 'sichuan ')
B. between... and: select * from students where age between 20 and30
C. Comparison Tester: (including =, <>,<, <=, >,>=) select * from students where name = 'mountain Lee'
D. like: select * from students wherename like 'Li % '(note that the query condition contains "%", which indicates that it is partially matched, and there is information in it, that is, search for matching items starting with "Li. Therefore, to query all objects with "Li", run the following command: '% Li %'. If the second word is Li, it should be '_ Li %', '_ li', or '_ Li _'.)
E. [] match check character: select * from courses where cno like '[AC] %' (The Relationship Between or and "in (...) "similar, and" [] "can represent a range, such as: select * from courses where cnolike '[A-C] %') Note: when I use this character in mysql, mysql treats it as two common tokens.
[^] Stockname like '[^ F-M] %' --------- (^ exclude specified range)
A. count () calculates the total number, for example: selectcount (*) from students (total number of students)
B. avg (column) calculates the mean, for example, selectavg (mark) from grades wherecno = 'b2'
C. max (column) and min (column), max and min

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------
Ii. Some common mysql statements
PHP + MySQL + Linux has gradually become a classic combination of small web servers. Building and debugging MySQL Databases in windows is a preferred choice for many website developers.

The following are some common MYSQL statements:

1. Connect to MYSQL.
Format: mysql-h host address-u user name-p User Password
1. Example 1: connect to MYSQL on the local machine.
First, open the DOS window, enter the directory mysqlbin, then type the command mysql-uroot-p, and press enter to prompt you to enter the password. If you have just installed MYSQL, super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>
2. Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: you do not need to add spaces for u and root. The same applies to others)
3. exit MYSQL command: exit (Press ENTER)

2. Change the password.
Format: mysqladmin-u username-p old password New password
1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command
Mysqladmin-uroot-password ab12
Note: because the root account does not have a password at the beginning, the old-p password can be omitted.
2. Example 2: Change the root password to djg345.
Mysqladmin-uroot-pab12 password djg345

3. Add new users. (Note: Unlike the above, the following commands in the MYSQL environment are followed by a semicolon as the command Terminator)
Format: grant select on database. * to username @ login host identified by "password" Example 1. Add a user test1 whose password is abc so that he can log on to any host, all databases are permitted to query, insert, modify, and delete databases. First, use the root user to connect to MYSQL, and then type the following command:
Grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc ";
However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. For the solution, see Example 2.
Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host.
Grant select, insert, update, delete on mydb. * to test2 @ localhostidentified by "abc ";
If you do not want test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to test2 @ localhostidentified "";


4. The first command
Mysql> select version (), current_date ();
+ ---------------- + ----------------- +
| Version () | current_date () |
+ ---------------- + ----------------- +
| 3.23.25a-debug | 2001-05-17 |
+ ---------------- + ----------------- +
1 row in set (0.01 sec)
Mysql>

This command requires the mysql server to tell you its version number and current date. Run the preceding command in different cases to check the result.
The results show that the case sensitivity of the mysql Command is consistent.
Perform the following operations:
Mysql> Select (20 + 5) * 4;
Mysql> Select (20 + 5) * 4, sin (pi ()/3 );
Mysql> Select (20 + 5) * 4 AS Result, sin (pi ()/3); (AS: Specify the alias AS Result)

5. Multi-line statements
A command can be input in multiple lines until the Semicolon ";" is displayed:
Mysql> select
-> USER ()
->,
-> Now ()
->;
+ -------------------- + --------------------- +
| USER () | now () |
+ -------------------- + --------------------- +
| ODBC @ localhost | 2001-05-17 2215 |
+ -------------------- + --------------------- +
1 row in set (0.06 sec)
Mysql>
Note how to use the comma in the middle and the last semicolon.

6. Multiple commands in one line
Run the following command:
Mysql> Select USER (); Select NOW ();
+ ------------------ +
| USER () |
+ ------------------ +
| ODBC @ localhost |
+ ------------------ +
1 row in set (0.00 sec)

+ --------------------- +
| NOW () |
+ --------------------- +
| 23:06:15 |
+ --------------------- +
1 row in set (0.00 sec)
Mysql>
Note the semicolons in the middle. commands are separated by semicolons.

7. display the existing database
Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
+ ---------- +
2 row in set (0.06 sec)
Mysql>

8. Select a database and display the selected Database
Mysql> USE mysql
Database changed
Mysql>
(USE and QUIT commands do not need to end with a semicolon .)
Mysql> select database ();
+ --------------- +
| Database () |
+ --------------- +
| Mysql |
+ --------------- +
1 row in set (0.00 sec)

9. display tables in the current database
Mysql> show tables;

10. display the table (db) Content
Mysql> select * from db;

11. Command Cancellation
When the command input is wrong and cannot be changed (multi-line statement), you can use c to cancel the command before the semicolon appears.
Mysql> select
-> User ()
-> C
Mysql>

This is some of the most commonly used basic operation commands. you can stick to it after multiple exercises.

Learning

After learning some basic operation commands, let's learn how to create a database and a database table.

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

Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
+ ---------- +
3 rows in set (0.00 sec)

2. Create a database, abccs
Mysql> Create DATABASE abccs;
Note that different operating systems are case sensitive.

3. Select the database you created
Mysql> USE abccs
Database changed
Now you have entered the database abccs you just created.

4. Create a database table
First, check what tables exist in your database:
Mysql> show tables;
Empty set (0.00 sec)
It indicates that no database table exists in the Database just created. Create a database table mytable:

We want to create a birthday table for employees in your company. The table contains the employee name, gender, date of birth, and city of birth.
Mysql> Create TABLE mytable (name VARCHAR (20), sexCHAR (1 ),
-> Birth DATE, birthaddr VARCHAR (20 ));
Query OK, 0 rows affected (0.00 sec)

Because the column values of name and birthadd change, VARCHAR is selected and its length is not necessarily 20. You can select
Any length from 1 to 255. If you need to change its font length later, you can use the Alter TABLE statement .);
Gender can be expressed by only one character: "m" or "f". Therefore, CHAR (1) is used );
The birth column uses the DATE data type.

After creating a table, we can look at the results just now and use show tables to SHOW which TABLES are in the database:
Mysql> show tables;
+ --------------------- +
| Tables in menagerie |
+ --------------------- +
| Mytables |
+ --------------------- +

5. display table structure:
Mysql> DESCRIBE mytable;
+ ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ------ + ----- + --------- + ------- +
| Name | varchar (20) | YES | NULL |
| Sex | char (1) | YES | NULL |
| Birth | date | YES | NULL |
| Deathaddr | varchar (20) | YES | NULL |
+ ------------- + ------ + ----- + --------- + ------- +
4 rows in set (0.00 sec)

6. Add records to the table
We first use the Select command to view the data in the table:
Mysql> select * from mytable;
Empty set (0.00 sec)
This indicates that the table created just now has no records.

Add a new record:
Mysql> insert into mytable
-> Values ('abccs ', 'F', '2017-07-07', 'China ');
Query OK, 1 row affected (0.05 sec)
Use the Select command above to check what has changed.

We can add records of all employees to the table one by one using this method.

7. load data into a database table in text mode
It is troublesome to input data one by one. We can add all records to your database table using text files.
Create a file named "mymysql.txt". Each line contains a record. Use the "tab" to separate the values and
The column order listed in the Create TABLE statement is given, for example:

Abccs f 1977-07-07 china
Mary f 1978-12-12 usa
Tom m 1970-09-02 usa

Use the following command to load the parent file named mytable.txt to the mytable table:
Mysql> load data local infile "mytable.txt" External table pet;

Run the following command to check whether the data has been input to the database table:
Mysql> select * from mytable;

In the previous article, we learned how to create a database and a database table, and how to add records to a database table.
How can we retrieve data from database tables?

1. Retrieve Information from a database table
In fact, we have used the Select statement to retrieve information from database tables.
The select statement format is generally:

Select search keyword FROM the retrieved table Where search condition (optional)

The previously used "*" indicates Selecting All columns.
Next we will continue to use the table mytable we created in the previous article:

2. query all data:
Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ---------- +
| Name | sex | birth | birthaddr |
+ ---------- + ------ + ------------ + -------- +
| Abccs | f | 1977-07-07 | china |
| Mary | f | 1978-12-12 | usa |
| Tom | m | 1970-09-02 | usa |
+ ---------- + ------ + ------------ + ---------- +
3 row in set (0.00 sec)

3. Corrected error records:
If the birth date of tom is incorrect, it should be, you can use the update statement to correct it:
Mysql> update mytable set birth = "1973-09-02" wherename = "tom ";
Use the statement in step 2 to check whether it has been corrected.

4. Select a Specific Row
We modified the birth date of tom above. We can select tom to see if there have been any changes:
Mysql> select * from mytable where name = "tom ";
+ -------- + ------ + ------------ +
| Name | sex | birth | birthaddr |
+ -------- + ------ + ------------ +
| Tom | m | 1973-09-02 | usa |
+ -------- + ------ + ------------ +
1 row in set (0.06 sec)

The above Where parameter specifies the search condition. We can also use combination conditions for queries:
Mysql> Select * FROM mytable Where sex = "f" ANDbirthaddr = "china ";
+ -------- + ------ + ------------ +
| Name | sex | birth | birthaddr |
+ -------- + ------ + ------------ +
| Abccs | f | 1977-07-07 | china |
+ -------- + ------ + ------------ +
1 row in set (0.06 sec)

5. Select a specific column
If you want to view the names of all users in the table, you can perform the following operations:
Mysql> Select name FROM mytable;
+ ---------- +
| Name |
+ ---------- +
| Abccs |
| Mary |
| Tom |
+ ---------- +
3 row in set (0.00 sec)
If you want to list the names and Gender columns, you can use commas to separate the keyword name and birth:
Myaql> select name, birth from mytable;

6. Sort rows
We can sort the records in the table by birthday size:
Mysql> Select name, birth FROM mytable orDER BYbirth;
+ ---------- + ------------ +
| Name | birth |
+ ---------- + ------------ +
| Tom | 1973-09-02 |
| Abccs | 1977-07-07 |
| Mary | 1978-12-12 |
+ ---------- + ------------ +
3 row in set (0.00 sec)

We can use DESC for reverse sorting:
Mysql> Select name, birth FROM mytable orDER BYbirth DESC;
+ ---------- + ------------ +
| Name | birth |
+ ---------- + ------------ +
| Mary | 1978-12-12 |
| Abccs | 1977-07-07 |
| Tom | 1973-09-02 |
+ ---------- + ------------ +
3 row in set (0.00 sec)

7. Row count
The database often needs to collect some data, such as the number of employees in the table, we need to use the row counting function COUNT ().
The COUNT () function is used to COUNT records with non-NULL results:
Mysql> Select COUNT (*) FROM mytable;
+ ---------- +
| COUNT (*) |
+ ---------- +
| 3 |
+ ---------- +
1 row in set (0.06 sec)

Number of male and female employees:
Mysql> Select sex, COUNT (*) FROM mytable GROUP BYsex;
+ ------ + ---------- +
| Sex | COUNT (*) |
+ ------ + ---------- +
| F | 2 |
| M | 1 |
+ ------ + ---------- +
2 row in set (0.00 sec)

Note that we use group by to group sex.

Multi-Table operations

We are familiar with the basic operations of databases and database tables. Now let's take a look at how to operate multiple tables.

Multiple tables may exist in a database, which are associated with each other. We will continue to use the previous example. The preceding table contains basic information about an employee, such as name, gender, date of birth, and place of birth. Create another table to describe the articles published by employees, including the author's name, article title, and publication date.

1. view the content of mytable in the first table:
Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- +
| Name | sex | birth | birthaddr |
+ ---------- + ------ + ------------ + ----------- +
| Abccs | f | 1977-07-07 | china |
| Mary | f | 1978-12-12 | usa |
| Tom | m | 1970-09-02 | usa |
+ ---------- + ------ + ------------ + ----------- +

2. Create the second table title (including the author, article title, and publication date ):
Mysql> create table title (writer varchar (20) notnull,
-> Title varchar (40) not null,
-> Senddate date );

Add a record to the table. The contents of the last table are as follows:
Mysql> select * from title;
+ -------- + ------- + ------------ +
| Writer | title | senddate |
+ -------- + ------- + ------------ +
| Abccs | a1 | 2000-01-23 |
| Mary | b1 | 1998-03-21 |
| Abccs | a2 | 2000-12-04 |
| Tom | c1 | 1992-05-16 |
| Tom | c2 | maid |
+ -------- + ------- + ------------ +
5 rows in set (0.00sec)

3. Multi-Table query
Now we have two tables: mytable and title. Using these two tables, we can perform combined queries:
For example, we want to query the name, gender, and Article of the author abccs:
Mysql> Select name, sex, title FROMmytable, title
-> Where name = writer AND name = 'abccs ';
+ ------- + ------ + ------- +
| Name | sex | title |
+ ------- + ------ + ------- +
| Abccs | f | a1 |
| Abccs | f | a2 |
+ ------- + ------ + ------- +

In the above example, because the author's name, gender, and Article record are in two different tables, you must use a combination for query. You must specify how records in a table match those in other tables.
Note: If the writer column in the title of the second table is named name (the same as the name column in mytable) rather than writer, you must use mytable. name and title. name indicates the difference.

Another example is used to query the author, place of birth, and date of birth of article a2:
Mysql> select title, writer, birthaddr, birth frommytable, title
-> Where mytable. name = title. writer andtitle = 'a2 ';
+ ------- + -------- + ----------- + ------------ +
| Title | writer | birthaddr | birth |
+ ------- + -------- + ----------- + ------------ +
| A2 | abccs | china | 1977-07-07 |
+ ------- + -------- + ----------- + ------------ +

Modification and backup, batch processing

Sometimes we need to modify and delete database tables and databases, which can be implemented as follows:

1. Add a column:
For example, in the mytable table in the previous example, adding a column to indicate whether the table is single or not:
Mysql> alter table mytable add column singlechar (1 );

2. Modify records
Modify the single record of abccs to "y ":
Mysql> update mytable set single = 'y' wherename = 'abccs ';

Now let's see what happened:
Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- + -------- +
| Name | sex | birth | birthaddr | single |
+ ---------- + ------ + ------------ + ----------- + -------- +
| Abccs | f | 1977-07-07 | china | y |
| Mary | f | 1978-12-12 | usa | NULL |
| Tom | m | 1970-09-02 | usa | NULL |
+ ---------- + ------ + ------------ + ----------- + -------- +

3. Add records
We have already discussed how to add a record to repeat this record for ease of viewing:
Mysql> insert into mytable
-> Values ('abc', 'F', '2017-08-17 ', 'China', 'n ');
Query OK, 1 row affected (0.05 sec)
Check it out:
Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- + -------- +
| Name | sex | birth | birthaddr | single |
+ ---------- + ------ + ------------ + ----------- + -------- +
| Abccs | f | 1977-07-07 | china | y |
| Mary | f | 1978-12-12 | usa | NULL |
| Tom | m | 1970-09-02 | usa | NULL |
| Abc | f | 1966-08-17 | china | n |
+ ---------- + ------ + ------------ + ----------- + -------- +


3. delete records
Run the following command to delete a record in the table:
Mysql> delete from mytable where name = 'abc ';
Delete: Delete a record from the table that meets the conditions given by where.

The result is displayed as follows:
Mysql> select * from mytable;
+ ---------- + ------ + ------------ + ----------- + -------- +
| Name | sex | birth | birthaddr | single |
+ ---------- + ------ + ------------ + ----------- + -------- +
| Abccs | f | 1977-07-07 | china | y |
| Mary | f | 1978-12-12 | usa | NULL |
| Tom | m | 1970-09-02 | usa | NULL |
+ ---------- + ------ + ------------ + ----------- + -------- +

4. delete a table:
Mysql> drop table ***** (name of table 1), and *** name of table 2;
You can delete one or more tables with caution.

5. delete a database:
Mysql> drop database name;
Use it with caution.

6. Database Backup:
Return to DOS:
Mysql> quit
D: mysqlbin
Use the following command to back up the database abccs:
Mysqldump -- opt abccs> abccs. dbb
Abccs. dbb is the backup file of your database abccs.

7. Use MySQL in batches:

First, create a batch file mytest. SQL with the following content:
Use abccs;
Select * from mytable;
Select name, sex from mytable where name = 'abccs ';

Run the following command in DOS:
D: mysqlbin mysql <mytest. SQL
The execution result is displayed on the screen.

If you want to view the results, but there are many output results, you can use the following command:
Mysql <mytest. SQL | more

We can also output the result to a file:
Mysql <mytest. SQL> mytest. out