Software testing knowledge -- common MySQL commands

Premise: MySQL is installed (currently using Windows system)

Database connection

Connect to MySQL database through command line

C:\Users\yanpeipei>mysql -u root -p
Enter password:

Enter the above command and execute enter. If it can be connected normally, the input password will be displayed;
Enter the correct password and press enter. If the MySQL > command prompt appears, the login is successful.
Subsequently, you can enter the corresponding SQL statement for operation.

Exit MySQL

mysql> exit
Bye

Create database

mysql> create DATABASE test;

Create a database named test.

show database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| test_yan           |
| world              |
+--------------------+

View all databases of MySQL service.

Delete database

mysql> drop DATABASE test_yan;

Delete a database named test.

Note: once the drop command is deleted, all data in the database will be cleared.

Select database

Select database test.

mysql> use test;
Database changed

Create table

Syntax: create table <Table name> ( <Field name 1> <Type 1>，<Field name 2> <Type 2>， ...,<Field name n> <type n>);

create table student_test(
   number int(8) not null primary key auto_increment comment 'Student number',
   name varchar(20) NOT NULL comment 'full name',
   sex varchar(4) NOT NULL comment 'Gender',
   class int(8) NOT NULL comment 'class',
   age int(4) NOT NULL comment 'Age'
)DEFAULT CHARSET=utf8;

explain:

int Is integer;
varchar character;
The field attribute is not null，Indicates that the field value cannot be empty;
primary key Keywords are used to define columns as primary keys. The primary key is unique and non duplicate. Generally, the number is the primary key;
auto_increment Define the column attribute as self increment, which is generally used for primary key. When inserting data, the value will be automatically increased by 1;
comment It is equivalent to adding remarks to the column;
DEFAULT CHARSET Set code to utf8 .

Display data sheet

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
| student_test   |
+----------------+

View table structure

mysql> desc student_test;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| number | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | NO   |     | NULL    |                |
| sex    | varchar(4)  | NO   |     | NULL    |                |
| class  | int         | NO   |     | NULL    |                |
| age    | int         | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

Delete table

Syntax: drop table <Table name>;

Delete the table named student_test data sheet.

mysql> drop table student_test;

After drop executes the delete command, the table and its contained data will disappear.

Insert table data

Syntax: insert into <Table name> ( Field name 1,Field name 2,...Field name N)
                   values
                   ( Value 1, Value 2,...value N );

Insert a student message to student_test table.

mysql> insert into student_test (number,name,sex,class,age) values (2201,'October','female',202201,18);

Batch insert multiple student information.

mysql> insert into student_test (name,sex,class,age) 
								 values 
								('the first month of the lunar year','female',202201,19),('December','male',202201,21);

Modify table data

Syntax: update Table name set Field name 1= New value 1,Field name 2=New value 2, ... where Conditional expression 1 and(or)  Conditional expression 2;

Example:
Modify table student_ In the test, the gender of the student whose name is December is female.

mysql> update student_test set sex = 'female' where name = 'December';

Query sheet data

Syntax: select Field name 1,Field name 2,... from  <Table name>  where  Conditional expression 1 and(or) Conditional expression 2 ;

1. Query all field column information

Example:
Query table student_ In the test, the information of students whose gender is female and age is greater than 19.

mysql> select * from  student_test where  sex = 'female' and age > 19;
+--------+--------+-----+--------+-----+
| number | name   | sex | class  | age |
+--------+--------+-----+--------+-----+
|   2203 | December | female  | 202201 |  21 |
+--------+--------+-----+--------+-----+

2. Query the column information of the specified field

Query student_ In the test form, the gender is female or the name of the trainees over the age of 19.

mysql> select name from student_test where sex = 'female' or age > 19;
+--------+
| name   |
+--------+
| October   |
| the first month of the lunar year   |
| December |
+--------+

3. Query the first few lines of information

Query student_ In the test table, the information of the first two rows of students who are female and over 19 years old.

mysql> select name from student_test where sex = 'female' and age > 19 limit 0,2;

Query multi table associated data

Association query:

select Field name 1,... from Table name 1,Table name	2,...where  Association condition expression and Filter condition expression;

Equivalent connection:

select Field name 1,... from Table name 1 inner join Table name 2 on Association condition expression and Filter condition expression ;

Left connection:

select Field name 1,... from Table name 1 left join Table name 2 on Association condition expression and Filter condition expression ;

Right connection:

select Field name 1,... from Table name 1 right join Table name 2 on Association condition expression and Filter condition expression ;

mysql> select * from test1;
+----+--------+-----+
| id | t_name | age |
+----+--------+-----+
|  1 | Zhang San   |  20 |
|  2 | Zhang San   |  20 |
|  3 | Li Si   |  30 |
|  4 | Zhou Zhou   |  36 |
|  5 | elute    |  27 |
|  6 | Zhao er   |  18 |
+----+--------+-----+

mysql> select * from test2;
+----+------+--------+
| id | t_id | friend |
+----+------+--------+
|  1 |    1 | one by one   |
|  2 |    3 | two two   |
|  3 |    6 | three three   |
+----+------+--------+

Association query:

The above left table is test1 table, and the right table is test2 table of friend information in the left table. Query the information of friend relationship in the two tables.

mysql> SELECT * FROM test1, test2 WHERE test1.id = test2.t_id;
+----+--------+-----+----+------+--------+
| id | t_name | age | id | t_id | friend |
+----+--------+-----+----+------+--------+
|  1 | Zhang San   |  20 |  1 |    1 | one by one   |
|  3 | Li Si   |  30 |  2 |    3 | two two   |
|  6 | Zhao er   |  18 |  3 |    6 | three three   |
+----+--------+-----+----+------+--------+

mysql> SELECT * FROM test1 INNER JOIN test2 on test1.id = test2.t_id;
+----+--------+-----+----+------+--------+
| id | t_name | age | id | t_id | friend |
+----+--------+-----+----+------+--------+
|  1 | Zhang San   |  20 |  1 |    1 | one by one   |
|  3 | Li Si   |  30 |  2 |    3 | two two   |
|  6 | Zhao er   |  18 |  3 |    6 | three three   |
+----+--------+-----+----+------+--------+

Left connection

Read all the data in the left table test1, even if there is no associated data in the right table test2.

mysql> SELECT * FROM test1 LEFT JOIN  test2 on test1.id = test2.t_id;
+----+--------+-----+------+------+--------+
| id | t_name | age | id   | t_id | friend |
+----+--------+-----+------+------+--------+
|  1 | Zhang San   |  20 |    1 |    1 | one by one   |
|  2 | Zhang San   |  20 | NULL | NULL | NULL   |
|  3 | Li Si   |  30 |    2 |    3 | two two   |
|  4 | Zhou Zhou   |  36 | NULL | NULL | NULL   |
|  5 | elute    |  27 | NULL | NULL | NULL   |
|  6 | Zhao er   |  18 |    3 |    6 | three three   |
+----+--------+-----+------+------+--------+

Right connection

It is based on test2 in the right table, which is opposite to LEFT JOIN.

mysql> SELECT * FROM test1 RIGHT JOIN test2 on test1.id = test2.t_id;
+------+--------+------+----+------+--------+
| id   | t_name | age  | id | t_id | friend |
+------+--------+------+----+------+--------+
|    1 | Zhang San   |   20 |  1 |    1 | one by one   |
|    3 | Li Si   |   30 |  2 |    3 | two two   |
|    6 | Zhao er   |   18 |  3 |    6 | three three   |
+------+--------+------+----+------+--------+

Delete table data

delete from Table name where Condition 1 and(or) Condition 2;

Delete the user information younger than 18 years old in the student table

+--------+--------+-----+--------+-----+
| number | name   | sex | class  | age |
+--------+--------+-----+--------+-----+
|   2201 | October   | female  | 202201 |  18 |
|   2202 | the first month of the lunar year   | female  | 202201 |  19 |
|   2203 | December | female  | 202201 |  21 |
+--------+--------+-----+--------+-----+
mysql> delete from student_test where age < 19;

mysql> select * from student_test;
+--------+--------+-----+--------+-----+
| number | name   | sex | class  | age |
+--------+--------+-----+--------+-----+
|   2202 | the first month of the lunar year   | female  | 202201 |  19 |
|   2203 | December | female  | 202201 |  21 |
+--------+--------+-----+--------+-----+

Keywords: Database MySQL

Added by brailleschool on Mon, 07 Feb 2022 11:29:11 +0200

Programming VIP