real! MySQL database: Hello, don't you run? Programmer: If you don't run away, y...

Today s sharing has started, please give me your suggestions~

MySQL: Don't run away, okay?

Programmer: If you don't run away, you raise me?

MySQL: Let me explain it to you.

Programmer: Take care of yourself first.

This is a very interesting conversation I have seen before, and this is also a true portrayal. Today I m sharing the MySQL database. It s very important to lay a good foundation. I won t say much, the text begins~

1. the database

1.1 DBMS

Database Management System: Database Management System

DBMA: database administrator

Common database management system:

• MySQL: Oracle's small and medium-sized database, charging from version 6
• Oracle: Oracle's large database, chargeable
• DB2: IBM's large database
• SQLServer: Mircosoft's large database
• SQLite: Embedded small database on the mobile terminal

MySQL and Oracle are mainly used in Java

1.2 The structure of the database

• Multiple databases can be maintained and managed in one DBMS
• A database consists of several tables
• A table contains several records
• A record contains several fields of information

1. The table in the database is equivalent to the entity class in the code

For example: To study the category of doctors, you should create a table of doctors

2. A column (field) in the database table is equivalent to the attribute in the entity class

For example: the car has brand attributes, then there should be a brand field in the car table

3. The object created according to the class is equivalent to a row (a record) in the database table

For example: when we get a pet object, we can get all the attributes it has from the object; similarly, when we query a pet record, we can clarify all the field information of the pet from the table.

1.3 SQL language

Structured Query Language: Structured Query Language

classification:

• DDL: Data Definition Language (Data Definition Language)

Create, modify, delete and other operations on databases and tables

Keywords: create, alter, drop, etc.

• DML: Data Manipulation Language

Add, delete, modify and other operations on records and fields in the table

Keywords: insert, delete, update, etc.

• DQL: Data Query Language

Query on databases, tables, records, and fields

Keywords: select, from, where, in, and, or, between, having, group by, order by, limit, etc.

• DCL: Data Control Language (Data Control Language)

Manage the security level and access authority of the database

Keywords: revoke, grant, commit, roll back, etc.

1.4 mysql software

Note:

1. The installation directory must be a pure English path
2. Delete step

• Uninstall the software in the control panel
• Delete the mysql folder under the mysql installation path
• Delete the mysql folder under ProgramData under C drive

Log in to mysql

method one:

• Open cmd
• Enter the mysql -uroot -p password

Method Two:

• Open cmd
• Type mysql -uroot -p
• Re-enter password

Way three:

• Find the Command Line Client after installing mysql
• enter password

2. SQL

C (create) R (read) U (update) D(delete)

2.1 Operation of the library

Build library: create database library name;

Determine whether the database exists, and create it if it does not exist: create database if not exists library name;

Create a database and specify the encoding format: create database library name character set encoding format;

Query database creation statement and coding format: show create database library name;

View existing databases: show databases;

Delete library: drop database library name;

Determine whether the database exists, and then delete it: drop database if exists library name;

Modify the encoding format of the database: alter database library name character set encoding format;

Specify the database used: use library name;

2.2 Table operation

Table creation: create table table name (field name field type [constraint], field name field type [constraint], field name field type [constraint], ...);

Determine whether the table exists, and then create it: create table if not exists table name (field name field type [constraint], field name field type [constraint], field name field type [constraint], ...);

Query all tables: show tables;

View table structure: desc table name;

Delete table: drop table table name;

Determine whether the table exists, and then delete it: drop table if exists table name;

Copy table structure: create table new table name like the name of the copied table;

Modify the table name: alter table old table name rename to new table name;

Modify the table structure:

Add a new field: alter table table name add new field field type [constraint];

Delete field: alter table table name drop field;

Modify field type: alter table table name modify field field type [constraint];

Modify the field name: alter table table name change old field name new field name field type [constraint];

2.3 Operation of records and fields

Query all records in the table:

• select * from table name;
• • Represents all fields (columns)
• Equivalent to select field 1, field 2, field 3,... from table name;

Add record:

Add data to all fields: insert into table name values (value 1, value 2, value 3,...);

Add data to the specified field: insert into table name (field 1, field 2, field 3,...) values (value 1, value 2, value 3,...);

Batch add (all fields): insert into table name values (value 1, value 2, value 3,...), (value 1, value 2, value 3,...),...;

Batch add (specify fields): insert into table name (field 1, field 2, field 3,...) values (value 1, value 2, value 3,...), (value 1, value 2, value 3 ,...),...;

Delete Record:

Delete all records: delete from table name;

Delete the specified record according to the condition: delete from table name where condition;

Delete all records: truncate table table name;

Modification record:

Modify all records: update table name set field name = value, field name = value, field name = value...;

Modify the specified record according to the conditions: update table name set field name = value, field name = value, field name = value,... where condition;

Note:

1. When adding records, the number of values must match the number of columns
2. When adding and modifying records, the value type must be consistent with the field type
3. Except for numeric types, other types must be enclosed in single/double quotes
4. delete from table name; delete all records in the table, its delete method is to delete row by row, how many records in the table, how many times the delete statement will be executed; truncate table table name; delete records in the table, its delete method is Delete the entire table, and then build an empty table with the same structure as the original table.

2.4 Data type

2.4.1 Numerical type

int: integer type

double: decimal type

Special: salary double(n, m)

n: Indicates that there are at most n digits in total and decimal places

m: indicates that the number of decimal places is reserved at most m

For example: the maximum value of salary double(6,2) is 9999.99

2.4.2 Date and time types

datetime: Timestamp, including year, month, day, hour, minute and second, format: yyyy-MM-dd HH:mm:ss

timestamp: timestamp, including year, month, day, hour, minute and second, format: yyyy-MM-dd HH:mm:ss

Note:

1. If the type of a field is timestamp, when we assign it to null or no value to it, the system will give it a default value. The default value is the time when this statement is added or when it is assigned a value of null time.
2. The timestamp type field value cannot be a null value

2.4.3 String type

varchar:

For example: name varchar(20): the maximum value of the name field is 20 characters

2.5 Query

2.5.1 Basic query

Query all: select * from table name;

Query the specified fields of all records: select field 1, field 2, field 3,... from table name;

Deduplication query: select distinct field name from table name;

2.5.2 Condition query

Add a condition after the where keyword, and the records will be filtered according to the condition when querying

1. Logical operators

• &&, and
• ||, or

2. Relational operators

• ">"
• "<"
• ">="
• "<="
• "!= "," <>"

3. Within the specified range

• between and

For example: query students whose grades are between 80 and 100

select * from student where score between 80 and 100;

Note: including head and tail

4. In the specified list (value 1, value 2, value 3,...)

Not in the specified list not in (value 1, value 2, value 3,...)

5. Empty and non-empty

• Determined to be empty: is null
• Judge non-empty: is not null

6. Fuzzy query

Keyword: like

Placeholder:

• _: any single character
• %: any number of arbitrary characters

2.5.3 Sort query

Keywords: order by

Sorting method:

• Ascending order: default, asc
• Descending order: desc

//Sort employees' sales in descending order, when the sales are the same, sort them in ascending order of salary

select * from emp order by sale desc, salary asc;

Note: If multiple fields are sorted, the second field will be sorted only when the value of the first field is the same

Format: order by field 1 sorting method, field 2 sorting method,...;

2.5.4 Aggregate functions

Concept: Take a column of data as a whole, and then perform vertical calculations

• avg(): calculate the average
• sum(): sum
• max(): find the maximum value
• min(): find the minimum value
• count(): find the number

Note:

• The parameters of count() generally use non-empty fields or *
• Aggregate functions do not calculate null values

2.5.5 Group query

Keywords: group by

Note:

• After grouping, the query fields can only be: grouping fields, aggregate functions
• The difference between where and having:

Where is used for conditional filtering before grouping. If the conditions of where is not met, it will not enter the grouping; having used for conditional filtering after grouping. If the conditions of having are not met, it will not be queried

Aggregate functions cannot be followed by where, and aggregate functions can be used for conditional filtering after grouping after having

2.5.6 Paging query

Keyword: limit

Syntax: limit start index, the number of records queried per page

Note: The index starts from 0

Formula: Start index = (current page number-1) * the number of records queried per page

2.5.7 Structure of the query statement

select: field list

from: table name

where: condition list

group by: group field

having: conditions after grouping

order by: sort field sorting method

limit: index, number of records

2.5.8 Alias

You can alias fields and tables

The role of an alias is to be able to distinguish fields with the same name in multiple tables and simplify writing

Format: as alias

Note: as can be omitted

2.6 Constraints

Concept: limit the data in the table

classification:

• Non-empty constraint: not null
• The only constraint: unique
• Primary key constraint: primary key
• Foreign key constraints: foreign key

2.6.1 Non-empty constraint: not null

The restricted field value cannot be empty

• When creating a table, add a non-empty constraint

create table table name (field name field type not null, field name field type [constraint], field name field class [constraint]...);

• After creating the table, add a non-empty constraint

alter table table name modify field name field type not null;

Note: A field with a null value cannot be set to non-empty

• Delete the non-null constraint (set the field to allow null values)

alter table table name modify field name field type;

2.6.2 Unique constraint: unique

Limit field values cannot be repeated

• When creating a table, add a unique constraint

create table table name (field name field type unique, field name field type [constraint], field name field class [constraint]...);

• After creating the table, add a unique constraint

alter table table name modify field name field type unique;

• Delete unique constraint

alter table table name drop index field name;

Note:

1. The value of the unique constraint can be a null value, and multiple null values are allowed
2. Fields that already have duplicate values cannot be set to be unique
3. A field can be set to be non-empty and unique at the same time, and the order of non-empty and unique keywords is arbitrary

2.6.3 Primary key constraint: primary key

Generally, the primary key does not use fields with special meanings

Features:

1. Non-empty and unique
2. There can only be one primary key field in a table
3. The primary key is the unique identification of records in the table

• When creating a table, add a primary key constraint

create table table name (field name field type primary key, field name field type [constraint], field name field class [constraint]...);

create table table name (field name field type [constraint], field name field type [constraint],..., primary key (primary key field));

• After creating the table, add the primary key constraint

alter table table name modify field name field type primary key;

• Delete the primary key constraint

alter table table name drop primary key;

Note: After deleting the primary key constraint, the primary key still has a non-null constraint

Since the primary key grows:

Concept: If a field is a numeric primary key field, you can use auto_increment to achieve primary key self-growth

• When creating a table, add primary key self-growth

create table table name (field name field type primary key auto_increment, field name field type [constraint], field name field type [constraint]...);

• After the table is created, add the primary key self-growth

alter table table name modify primary key field field type auto_increment;

• Delete the primary key since the growth

alter table table name modify primary key field field type;

• After the table is created, add the primary key constraint while adding self-growth

alter table table name modify field field type primary key auto_increment;

Note:

1. Can add self-growth function must be the primary key field
2. Add the primary key from the growth of the primary key field must be of numeric type
3. The first self-increasing field value is 1
4. +1 from the maximum value that has existed
5. If the maximum value that has ever existed is a decimal, then the self-increasing value is the smallest integer greater than it

2.6.4 Foreign key constraints: foreign key

The foreign key creates a connection between the table and the table, ensuring the integrity of the data

Note: The type of the foreign key from the table must be consistent with the type of the primary key of the primary table

• When creating a table, add foreign key constraints

create table table name (field name field type [constraint], field name field type [constraint], ..., [constraint foreign key name] foreign key (foreign key field) references the main table (primary key field of the main table));

• Delete foreign key

alter table indicates drop foreign key foreign key name;

• After creating the table, add foreign key constraints

alter table table name add [constraint foreign key name] foreign key (foreign key field) references the main table (primary key field of the main table);

• After the table is created, while creating a new foreign key field, add a foreign key constraint

alter table table name add foreign key field field type, add [constraint foreign key name] foreign key (foreign key field) references the main table (primary key field of the main table);

Cascading operation: while updating and modifying the records in the master table, it can directly affect the records in the slave table

• When creating a slave table, add a cascade operation

create table table name (field name field type [constraint], field name field type [constraint], ..., [constraint foreign key name] foreign key (foreign key field) references the main table (primary key field of the main table) on delete cascade on update cascade);

• After creating the slave table, add a cascade operation

alter table table name add [constraint foreign key name] foreign key (foreign key field) references the main table (primary key field of the main table) on delete cascade on update cascade;

• After creating the slave table, create a new foreign key field, add foreign key constraints, and add foreign key cascade operations

alter table table name add foreign key field field type, add [constraint foreign key name] foreign key (foreign key field) references the main table (primary key field of the main table) on delete cascade on update cascade;

2.7 Multiple tables

2.7.1 The relationship between tables and tables

• One-to-one, such as: person and ID, company and registered address, car and license plate number
• One-to-many (many-to-one), for example: department and employee, product and category
• Many-to-many, for example: students and courses, products and orders

2.7.2 Multi-table connection establishment

• One-to-one, add a foreign key on either side to point to the primary key of the other side, and the foreign key must be unique
• One-to-many, add a foreign key to the primary key of the one side
• Many-to-many, a third intermediate table is needed. The intermediate table contains at least two fields. These two fields are used as the foreign keys of the intermediate table and point to the primary keys of the two main tables respectively. In order to ensure that there will be no duplicate values in the intermediate table, these two foreign key fields need to be formed into a joint primary key.

2.8 Multi-table query

2.8.1 Cross query

Syntax: select field list from table 1, table 2;

The result of the cross query is the Cartesian product, which is the composition of all records in multiple tables

We need to use conditions to remove invalid data

2.8.2 Inner join query

Use where conditions to remove invalid data on the basis of cross-query

syntax:

• Implicit inner join

select field list from table 1, table 2 where conditions;

• Explicit inner join

select field list from table 1 inner join table 2 on/where conditions;

Note: inner can be omitted

The result of the inner join query is the intersection of the two tables

2.8.3 Outer connection

syntax:

• Left outer join

select field list from table 1 left outer join table 2 on conditions;

• Right outer join

select field list from table 1 right outer join table 2 on condition;

Note: outer can be omitted

The result of the left outer join query is all of the left table and the intersection of the two tables

The result of the right outer join query is all of the right table and the intersection of the two tables

2.9 Subqueries

Concept: Use the result of a query statement as the table, record, field, and condition of another query statement

summary

A database is a warehouse for storing data, which can realize persistent storage of data. Its essence is a file system.

Today s sharing is over, please forgive me and give me your advice!