Basic operation of database

Cute rain2022-05-14 19:24:30

Catalog

One 、 Basic operation of database

1、 Login and exit of database

2、 View all databases

3、 Show database version

4、 Display time

5、 Create database

6、 Look at the statement to create the database

7、 View currently used databases

8、 View the current user

9、 Use a database

10、 Delete database

Two 、 Basic operation of data table

1、 View all tables in the current database

2、 Create table

3、 View table structure

4、 Look at the statement that created the table

5、 Insert... Into the table , to update , Delete data

6、 Look at the data in the table

7、 Modify table name

8、 Modify table field information

9、 Delete table

3、 ... and 、mysql Query operation

1、 The basic query

2、 Conditions

3、 Sort

4、 Aggregate functions

5、 grouping

6、 Link query

6.1、 Internal connection query

6.2、 External connection query

6.2.1、 Left connection query

6.2.2、 Right connection query

7、 Self correlation

8、 Subquery and union query


One 、 Basic operation of database

1、 Login and exit of database

  Exit database , Any of the following three ways :

exit

quit

ctrl+d

2、 View all databases

3、 Show database version

4、 Display time

5、 Create database

6、 Look at the statement to create the database

Be careful : When creating a database or viewing the create database statement ,database No, s. 

7、 View currently used databases

When a database is selected , It is shown as follows :

8、 View the current user

9、 Use a database

10、 Delete database

Two 、 Basic operation of data table

1、 View all tables in the current database

2、 Create table

Create table command :create table Database table name ( Field type constraint [ Field type   constraint ]), The in brackets can be omitted .

constraint :

  • Primary key primary key : The order of physical storage
  • Non empty not null : This field is not allowed to fill in null value
  • only unique: Duplicate values are not allowed for this field
  • Default default: When this value is not filled in , Default values will be used . If filled in , The value filled in shall prevail
  • Foreign keys foreign key : Constraints on relation fields , When filling in a value for a relationship field , It will query the associated table whether this value is saved
  • stay , If it exists, it will be written successfully , If it does not exist, the write fails . Although foreign key constraints can guarantee the validity of data , But it's going on
  • Data crud( increase , modify , Delete , Inquire about ) when , Will reduce the performance of the database .
  • auto_increment It means automatic growth

Create table

  Create another students surface

3、 View table structure

To view the table structure is to view the information of each field .

4、 Look at the statement that created the table

5、 Insert... Into the table , to update , Delete data

Insert :

to update :

Delete :

  Delete specified row :

Delete all rows :

 

6、 Look at the data in the table

View all data :

  You can also view the specified data ( hold * Just change to the specific field you want to find ):

7、 Modify table name

8、 Modify table field information

Add fields :alter table Table name add Name type ;

  Rename the field :alter table Table name change Original field name new field name Types and constraints ;

Modify fields :

  Delete field :alter Table name drop Name  

Modify the storage engine of the table :

Common engines :MyISAM,InnoDB

Look at the previous engine :

  After the modification :

9、 Delete table

3、 ... and 、mysql Query operation

1、 The basic query

Query all fields :

Query the specified field :

Use as Name the field :

Eliminate duplicate lines :

2、 Conditions

Comparison operations : Greater than , Less than , Greater than or equal to , Less than or equal to , equal , It's not equal ; Logical operations

Query age is greater than 18 Student information at age

  Query age is older than 21 Less than 23 Of

  Inquire about 18 Men over the age of

  Inquire about people over the age of 18 who are taller than 180 Classmate

  The query does not exceed 23 A year old man

  No women over the age of 18

Fuzzy query

Fuzzy query is to query the corresponding individual through a small part of information , Pictured :

Query the name with “ Small ” The name of the beginning

The query name contains “ Small ” All the names of

 

  Query a two word name

Query for names with at least two words

Range queries

Query age is 18,20,24 Student information

The age of inquiry is not 18,20,24

The age of inquiry is 18 To 24 Between all students, all information

Query age is not in 18 To 24 Names between the ages , Age information

Query students whose student ID is empty

3、 Sort

The age of inquiry is 18 Students over the age of , Age from small to large

The age of inquiry is 18 Students over the age of , From old to young

The age of inquiry is 18 To 24 Students aged between , According to age from small to large , Height from high to bottom

4、 Aggregate functions

count() total ,max() Maximum ,min() minimum value ,sum() Sum up ,avg() Average ,round() rounding

New table information :

Ask how many boys there are

Query maximum age

Query the minimum age

Calculate the sum of everyone's ages

Calculate the average age

Calculate the average age , Set the decimal places of the average age

5、 grouping

New table information :

group by grouping

Group by sex

Count the number of boys and girls

View gender groups , The oldest

View gender groups , The name of each group

6、 Link query

Internal connection query is a common connection query , You can query the information of two or more data tables

Suppose a student's name 、 Age 、 Gender information is in the first table , Home address 、 The student number information is in another table , Now I need all the information of this student , We need to connect and query

6.1、 Internal connection query

  • Definition : Take a record from a table , Go to another table to match , When a condition is the same in these two tables, it will be retained .
  • Query criteria : When there are fields with the same meaning in two tables , You can use this query method to connect queries , When the value of this field is the same, the record can be found .

Now there are two tables student1,student2, as follows :

 

Querying two tables can get all the information on the corresponding table

Query method 1 :

  A query 2:

6.2、 External connection query

For internal connections , We will only keep the information that meets the connection conditions , For those information that do not meet the connection conditions , We won't keep , Sometimes we may need this information , So the external connection is to solve this problem . The external connection will not only retain the information that meets the connection conditions , For those columns that do not meet the connection conditions , Will be filled in NULL value , Then return to the result . The tables participating in the external connection can be divided into master and slave , Each row of data in the master table matches the data columns in the slave table . External connection is divided into left external connection and right external connection .

6.2.1、 Left connection query

left join: Distinguish external connections by the direction of the main table , The main table is on the left , It's called the left outer connection ; Whether the left table can match the connection conditions or not , In the end, they will keep , Only the fields that cannot be matched in the right table are set to NULL;

6.2.2、 Right connection query

right join: Distinguish external connections by the direction of the main table , The main table is on the right , It is called the right external connection ; Whether the right table can match the condition or not , In the end, they will keep , Only the fields that cannot be matched in the left table are set to NULL( Here, all the results of the right table can match the upper left table , So there's no NULL value ).

7、 Self correlation

But when a table is connected to itself , Self join called table

Query ratio id Number is 1 Your classmate is tall ;

8、 Subquery and union query

The following article is very detailed

https://blog.csdn.net/weixin_39411321/article/details/90602030?spm=1001.2014.3001.5502


thank
Similar articles

2022-05-14