Understand the basics of Navicat for MySQL in one article

1. Database Operation

1. Create a new database

2. Open the database

Just right click or double click.

3. Delete the database

Right click -> Delete database

4. Modify the database

Right click -> Database Properties

2. Data Types

1. Commonly used data types

Integer: int
Decimal
String: varchar
Date and time: datatime

2. Constraints

Primary key: the order in which data is physically stored (the primary key is unique and cannot be empty, so uncheck the option to allow null values, otherwise you will not be able to create or save a new key. You can also select the option to automatically increment the value in the comment below to save work)

Not empty: This field cannot be left blank Unique: This field cannot be repeated

Default value: If left blank, the default value will be used. If filled in, the filled value will be used.
Foreign key: maintains the relationship between two tables

3. Backup and Recovery

Backup: Right click -> Dump to SQL file Restore: Right click to run SQL file

3. Operation

1. Simple query

grammar:

select field form table name where condition;

Use as to alias tables and fields: select name as n from students as s;

Eliminate duplicate rows : select distinct * from students;

Comparison operators:

Equal to: =
Greater than: >
Greater than or equal to: >=
Less than: <
Less than or equal to: <=
Not equal to: != or <>

Logical operators:

and
or
Not: not

Fuzzy query:

like
% represents any number of characters
Indicates an arbitrary character Example 1: Query students with the surname Sun
select * from students where name like '孙%';
Example 2: Search for students whose surname is Sun and whose given name is one character
select * from students where name like '孙'

Range query:

in means in a non-connected range;
Example 1: Query students whose hometown is Beijing, Shanghai or Guangdong
select * from students where hometown in('Beijing','Shanghai','Guangdong');
between...and... represents a continuous range. Example 2: Query students aged 18 to 28
select * from students where age between 18 and 28;
Empty judgment:
Note: null and '' are different judgments.
Example 1: Query students who have not filled in their ID cards
select * from students where card is null;
Is not null
select * from students where card is not null;

Sorting:

Syntax: select * from table name order by column 1 asc|desc, column 1 asc|desc…;
By default, the columns are sorted from small to large.
asc Sort from small to large, that is, ascending order.
desc Sort from largest to smallest, that is, descending order.

Aggregate functions:

1. count( * ) means calculating the total number of rows. The asterisk in the brackets is the same as the column name.
Aggregate functions cannot be used in where clauses Example 1: Query the total number of students
select count( * ) from students;
2. max(column) means to find the maximum value of this column Example 2: Query the minimum age of girls
select max(age) from students where sex='女';
3. min(column) indicates the minimum value of this column. Example: Query the oldest age in class 1
select min(age) from students where class='1'
4. sum(column) means to find the sum of this column
select sum(age) from students
5. avg(column) means to find the average value of this column
select avg(age) from students

Grouping:

1. Group by field, which means that data with the same field will be put into one group
2. After grouping, the column based on which the grouping is performed will be displayed in the result set, and other columns will not be displayed in the result set
3. You can perform statistics on the grouped data and perform aggregation operations:
select column 1, column 2, count(*) from table name group by/having column 1, column 2
The conditional operator after having is the same as that of where
Contrast where with having :
1. Where is to filter the data in the table specified after from, which is the filter of the original data
2. Having is to filter the results of group by

Paginated query: Get partial rows

1. When the data is too large, it is very troublesome to view the data in one page
2. Grammar:
select * from limit start,count;
2.1 Starting from start, get count pieces of data.
2.2 start index starts from 0 Example 1: Query the first 3 rows of student information
select * from students limit 0,3;
Pagination:
1. Given: m pieces of data are displayed per page, find: display the data on page n
select * from students limit (n-1)*m,m;
2. Find the total number of pages and query the total number of entries p1
Divide p1 by m to get p2
If it is an integer, p2 is the total number of pages. If it is not an integer, p2+1 is the total number of pages.

Connection query:

1. When the query result columns come from multiple tables, you need to connect the multiple tables into a large data set and then select the appropriate columns to return.
2. Equi-join query: The query result is the data matched by the two tables
3. Left join query: The query result is the data matched by the two tables plus the data unique to the left table. For data that does not exist in the right table, use null.
4. Right join query: The query result is the data matched by the two tables plus the data unique to the right table. For data that does not exist in the left table, use null.

Equi-join:

Method 1: select * from table1, table2 where table1.column = table2.column;
Method 2 (also known as inner join): select * from table1 inner join table2 on table1.column=table2.column;
Method 3 (inner table join): select * from table1, table2, table3 where table1.column = table2.column and table2 = table3;
Method 4 (also known as left join): select * from table1 left join table2 on table1.column=table2.column;
Method 5 (also known as right join): select * from table1 right join table2 on table1.column=table2.column;

Main query:

The main query object, the first select statement

Subquery:

If another select statement is embedded in the first select statement, the nested select statement is called a subquery.

The relationship between the main query and the subquery:

1. Subquery is embedded in the main query
2. Subqueries assist the main query, either as conditions or as data sources
3. A subquery is an independent statement and a complete select statement.

Subquery categories:

1. Scalar subquery: The result returned by the subquery is one data (one row and one column)
2. Column subquery: the result returned is one column (one column with multiple rows)
3. Row subquery: The returned result is a row (one row with multiple columns)
4. Table-level subquery: The returned result is a multi-row, multi-column scalar subquery:
Example 1: Query the average age of students in a class
select avg(age) from students;
Example subquery:
Example 1: Query the student ID of 18-year-old students
select studentno from students where age=18;
Row-level query:
Example 1: Query the oldest student information among boys
select max(age) from students where sex='男';

2. Insert data

grammar:
insert into table name values(...)
Insert multiple records: insert into table name values(...),(...)

3. Delete data

grammar:
delete from table name where condition

4. Modify/update data

grammar:
update table name set column 1 = value 1, column 2 = value 2... where condition

4. Advanced

view:

1. For complex queries, they are used in many places. If the requirements change and the SQL statement needs to be changed, it needs to be modified in multiple places, which is very troublesome to maintain.
2. Solution: Define the view.
3. The essence of a view is the encapsulation of a query
4. Define a view. It is recommended to start with V_
select view view name as select tone;
View views: Viewing a table will also list all views

show tables;

To delete a view:

drop view view name;

Use: The purpose of the view is to query
select * from view name;

index:

grammar:
View the index: show index from table name;

Foreign Key:

The value of a foreign key must be the primary key of another table. Before setting the primary key, you must first change the engine in both table options to InnoDB:

Then set the foreign key:

Note: The table where the foreign key is located cannot have a primary key.

only:

After setting uniqueness, duplicate data can appear.

Entering duplicate data will result in an error:

5. Knowledge Supplement

ctr+/ Comment function Cartesian product = multiplication of the number of data in two tables

The above is the detailed content of understanding the basic knowledge of navicat for mysql in one article. For more information about navicat mysql basics, please pay attention to other related articles on 123WORDPRESS.COM!