Today’s sharing started, please give us more advice ~

MySQL: Don’t run away yet, ok?

Programmer: Don’t run you support me?

MySQL: Let me explain.

Programmer: You mind your own business first.

This is a very interesting conversation I’ve seen before, and it’s true. Today’s share is MySQL database, lay a good foundation is very important, no more words, the text begins ~

I. Database

1.1 the DBMS

Database Management System: Database Management System

DBMA: database administrator

Common database management systems:

  • MySQL: Oracle’s small and medium sized database, starting with version 6
  • Oracle: Oracle corporation’s large database, for a fee
  • DB2: IBM’s large database
  • SQLServer: Mircosoft’s large database
  • SQLite: small embedded database on mobile

MySQL and Oracle are mainly used in Java

1.2 Database Structure

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

1. Tables in the database are equivalent to entity classes in the code

For example, to explore the doctor class, you would create a doctor table

2. A column (field) in a database table corresponds to an attribute in an entity class

For example, if a car has a brand attribute, there should be a brand field in the car table

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

For example, when we get a pet object, we can get all of its properties from that object; Similarly, when we query a pet record we can specify all the field information for that pet from the table.

1.3 SQL language

Structured Query Language: Structured Query Language

Classification:

  • DDL: Data Definition Language

Create, modify, and delete databases and tables

Keywords: CREATE, ALTER, drop, etc

  • DML: Data Manipulation Language

Add, delete, and modify records and fields in a table

Keywords: INSERT, DELETE, update, etc

  • DQL: Data Query Language

To query databases, tables, records and fields

Keywords: SELECT, FROM, WHERE, in, and, or, between, having, group by, order by, limit, etc

  • DCL: Data Control Language

Manages the security level and access rights of the database

Revoke, Grant, commit, roll back, etc

1.4 the mysql software

Note:

  1. The installation directory must be a pure English path
  2. Remove steps
  • Uninstall the software in the control panel
  • Delete the mysql folder in the mysql installation directory
  • Delete the mysql folder under ProgramData in drive C

Log on to the mysql

A:

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

Method 2:

  • Open the CMD
  • Enter mysql -uroot -p
  • Reenter your password

Three:

  • Locate the Command Line Client installed after mysql
  • Enter the password

Second, SQL

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

2.1 Library operations

Create database: create database name;

Create database if not exists Specifies the name of the database.

Create database name character set encoding format;

To query the database creation statement and encoding format, run the show create database database name command.

Run the show databases command to view existing databases.

Drop database drop database name;

Drop database if exists Indicates the database name.

Alter DATABASE database name character set;

Specify the database to use: use the library name;

2.2 Table operations

Create table name (field name Field type [constraint], field name field type [constraint], field name field type [constraint],…) ;

Create table if not exists Table name (field name Field type [constraint], field type [constraint], field name field type [constraint],…) ;

To query all tables: show tables;

Table name desc;

Drop table drop table name;

Drop table if exists Indicates the name of the table.

Create table new table name like the name of the table to be copied;

Alter table old table rename to new table;

Alter table structure:

Alter table table_name add table_name [constraint];

Alter table alter table drop;

Alter table table name modify field type [constraint];

Alter table name alter table name alter table name alter table name alter table name alter table name

2.3 Record and field operations

Query all records in the table:

  • Select * from table_name;
    • Represents all fields (columns)
  • Select field 1, field 2, field 3… From the name of the table;

Add a record:

Insert into values (1, 2, 3…) ;

Insert into table name (field 1, field 2, field 3…) Values (1, 2, 3…) ;

Insert into values (1, 2, 3,…) (Value 1, value 2, value 3…) . ;

Insert into table name (insert into table name, insert into table name, insert into table name) Values (1, 2, 3…) (Value 1, value 2, value 3…) . ;

Delete records:

Delete all records: delete from table name;

Delete from table name where condition;

Truncate TABLE name deletes all records.

Modification record:

Update table name set name = value, name = value, name = value… ;

Update table name set field name = value, field name = value, field name = value,… Where conditions;

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 the same as the field type
  3. All types except numeric types must be enclosed in single/double quotation marks
  4. Delete from table name; Delete all records in the table, it is deleted row by row, the number of records in the table, delete the statement will be executed many times; Truncate TABLE name; Delete a record from a table by deleting the entire table and building an empty table with the same structure as the original table.

2.4 Data Types

2.4.1 Value Types

Int: indicates an integer

Double: indicates the decimal type

Special: salary double(n, m)

N: Indicates that the integer and decimal digits contain a maximum of N digits

M: indicates that a maximum of m digits can be reserved

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

2.4.2 Date and time type

Datetime: indicates the time stamp. The value contains year, month, day, hour, minute, second in the format yyyY-MM-DD HH: MM :ss

Timestamp: indicates the timestamp, including year, month, day, hour, minute, second in the format yyyy-mm-dd HH: MM :ss

Note:

  1. If a field is of type TIMESTAMP, if we assign it a value of null or no value is assigned to it, the system will give it a default value, which is either the time at which the statement was added or the time at which it was assigned a value of NULL.
  2. A timestamp field value cannot be null

2.4.3 String type

Varchar:

For example, name vARCHAR (20) : indicates that the name field contains a maximum of 20 characters

2.5 the query

2.5.1 Basic Query

Select * from table_name;

Select field 1, field 2, field 3,… From the name of the table;

SQL > select * from ‘select * from’ select * from ‘select * from’;

2.5.2 Querying Conditions

Conditions are added after the WHERE keyword, and records will be filtered based on the conditions during queries

1. Logical operators

  • &&, the and
  • | |, or
  • !

2. Relational operators

  • “>”
  • “<“
  • “> =”
  • “< =”
  • “=”
  • ! “” = “, “<>”

3. Within the specified scope

  • Between… The and…

For example, query students whose scores are between 80 and 100

select * from student where score between 80 and 100;

Note: including head and tail

4. In (value 1, value 2, value 3…)

Not in(value 1, value 2, value 3…)

5. Empty and non-empty

  • Null: is null
  • Check whether the value is not null

6. Fuzzy query

Keyword: like

Placeholder:

  • _ : a single arbitrary character
  • % : any character

2.5.3 Sorting Queries

Order by

Sorting method:

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

// Rank the employees’ sales in descending order. If the sales amount is the same, rank them in ascending order according to their salary

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

Note: If multiple fields are sorted, the second field will only be sorted if the first field has the same value

Order by… order by… order by… ;

2.5.4 Aggregate function

Concept: Take a column of data as a whole and compute it vertically

  • Avg () : Calculates the average value
  • The sum () : the sum
  • Max () : Finds the maximum value
  • Min () : find the minimum value
  • Count () : Calculates the number

Note:

  • Arguments to count() generally use non-empty fields or *
  • Aggregate functions do not evaluate null values

2.5.5 Group Query

Key words: group by

Note:

  • The fields to be queried after grouping can only be group fields and aggregate functions
  • Where and having

Where is used to filter conditions before grouping. If the conditions of WHERE are not met, the group is not entered. Having is used to filter groups. If the conditions of HAVING are not met, the group will not be queried

Where cannot be grouped with an aggregate function. Having can be grouped with an aggregate function

2.5.6 Paging Query

Key word: limit

Syntax: limit Indicates the number of records queried per page

Note: The index starts at 0

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

2.5.7 Query Statement Structure

Select: indicates the list of fields

From: the name of the table

Where: List of conditions

Group by: indicates a group field

Having: Condition after grouping

Order by: Sort the sorting method of a field

Limit: indicates the number of indexes

2.5.8 names

You can alias fields and tables

Aliasing is used to distinguish fields of the same name from multiple tables and simplifies writing

Format: AS alias

Note: As can be omitted

2.6 the constraint

Concept: Qualify data in a table

Classification:

  • Non-null constraint: not NULL
  • The only constraint is unique
  • Primary key constraint: Primary key
  • Foreign key constraints: Foreign key

2.6.1 Non-null Constraint: Not NULL

The qualified field value cannot be empty

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

Create table table name (column name field type not NULL, column name field type [constraint], column name field class [constraint]…) ;

  • After the table is created, add non-null constraints

Alter table table name modify field name Field type not null;

Note: fields that already have null values cannot be set to non-null

  • Remove non-null constraints (set fields to allow null values)

Alter table table name modify field name;

2.6.2 Unique constraint: unique

Restrict field values to have no duplicates

  • When creating a table, add a unique constraint

Create table name (select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name, select table name. ;

  • After the table is created, add unique constraints

Alter table table_name alter table table_name alter table table_name

  • Delete unique constraints

Alter table drop index drop index;

Note:

  1. The value of a unique constraint can be null 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 non-empty and unique at the same time, and the order of non-empty and unique keywords is arbitrary

2.6.3 Constraints on the Primary Key: Primary key

Generally, primary keys do 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 the records in the table
  • Add primary key constraints when creating a table

Create table table name (select primary key, select primary key, select constraint…) ;

Create table table name (field name field type [constraint], field name field type [constraint],… , primary key(primary key));

  • After the table is created, add primary key constraints

Alter table table name modify field name primary key;

  • Delete the primary key constraint

Alter table drop primary key;

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

Primary key auto-growth:

Concept: If a field is a numeric primary key field, you can use auto_INCREMENT to implement auto-increment of the primary key

  • Add primary key auto-growth when creating a table

Create TABLE auto_increment primary KEY AUTO_increment Primary KEY AUTO_increment ;

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

Alter table alter table name modify primary key auto_increment;

  • Delete primary key autogrowth

Alter table table name modify primary key field;

  • After creating the table, add the primary key constraint along with self-growth

Alter table alter table name modify primary key AUTO_increment;

Note:

  1. The primary key field that can add self-growth must be the primary key field
  2. The primary key field to which primary key growth is added must be numeric
  3. The first self-growing field has a value of 1
  4. Plus 1 from the maximum that ever existed
  5. If the largest ever exists is a decimal, then the self-increasing value is the smallest integer greater than it

2.6.4 Constraints on Foreign Keys: Foreign keys

Foreign keys allow tables to relate to each other, ensuring data integrity

Note: The type of the secondary foreign key must be the same as that of the primary key of the primary table

  • When creating a table, add a foreign key constraint

Create table table name (field name field type [constraint], field name field type [constraint],… [Constraint foreign key name] References [constraint foreign key name] References [Constraint foreign key name]

  • Remove the foreign key

Alter table drop foreign key name;

  • After the table is created, add foreign key constraints

Alter table name add [constraint foreign key name] Foreign key references [constraint foreign key name]

  • After creating a table, add a foreign key constraint along with a new foreign key field

Alter table table_name add [constraint foreign key name] Foreign key references (constraint foreign key name);

Cascading operation: Updates and modifies the records in the primary table while directly affecting the records in the secondary table

  • Add cascading operations when creating slave tables

Create table table name (field name field type [constraint], field name field type [constraint],… , [Constraint foreign key name] Foreign key references On delete CASCADE on update cascade;

  • After creating slave tables, add cascading operations

Alter table name add [constraint foreign key name] Foreign key references On delete CASCADE on update cascade;

  • After the slave table is created, create new foreign key fields, add foreign key constraints, and add cascading operations for foreign keys

Alter table table_name add foreign key; Add [constraint foreign key name] Foreign key references On delete CASCADE on update cascade;

More than 2.7 table

2.7.1 Relationships between Tables

  • One-to-one, such as person and ID card, company and registered address, car and license plate number
  • One-to-many (many-to-one), e.g., departments and employees, goods and categories
  • Many-to-many, for example: students and courses, goods and orders

2.7.2 Establishing Connections for Multiple Tables

  • One-to-one: Add a foreign key to either party that points to the primary key of the other party. Ensure that the foreign key is unique
  • One-to-many, add a foreign key to the many party to point to the primary key of the one party
  • Many-to-many, we need a third intermediate table. The intermediate table contains at least two fields that serve as the foreign key of the intermediate table and point to the primary key of the two primary tables. To ensure that the intermediate table does not have duplicate values, you need to form the two foreign key fields as joint primary keys.

2.8 Multi-table Query

2.8.1 Cross Query

Select * from table 1, table 2;

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

We need to use conditions to remove invalid data

2.8.2 Internal Connection Query

Use where conditions to remove invalid data based on cross-queries

Grammar:

  • Implicit inner join

Select * from table1 where table2;

  • Explicit inner join

Select * from 表1 inner join 表2 on/ WHERE;

Note: inner can be omitted

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

2.8.3 outer join

Grammar:

  • The left outer join

Select * from 表1 left OUTER join 表2 on;

  • Right connection

Select * from 表1 right outer join 表2 on;

Note: outer can be omitted

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

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

2.9 the subquery

Concept: Use the results of one query statement as tables, records, fields, and conditions of another query statement

summary

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

Today’s share has ended, please forgive and give advice!