MySQL > Insert into a pit

2017-04-06
Small conch
Code an egg
Code an egg

WeChat ID codeegg

Intro here, we learn from each other, grow together and share so that we are not alone. [email protected]

preface

MySQL is the most popular relational database management system

The author

Full blast engineer with a bit of a mess

video

Reply “20170405” in the “code egg” public account

directory

Common Knowledge of databases

Relational database
Non-relational databases

Build Mysql environment

windwos
linux
The working process

Library operation
The storage engine
Table operation
Basic data operation
Supported data types
The field properties
Advanced data operation
Relationship between

Common Knowledge of databases

Databases fall into two main camps: relational and non-relational.

Relational database SQL

Definition:

A database built on a relational model.

Relational model:

A model that represents the relationships between data through relationships.

Relational database:

Large (ORACLE, DB2), medium (mysql, SqlServer), small (Access)

Features:

The flexibility of the data database management system is not the simple accumulation of data, it has a lot of management functions on the basis of recording data information, such as input, output, query, edit and modify.
The data in the structured database of data is not disorganized and irrelevant, they have a certain organizational structure, and the data belonging to the same set has similar characteristics.

Non-relational database NOSQL: Not Only SQL

Definition:

All databases that are not relational databases are non-relational databases

Non-relational databases hold data in the following ways:

Key/value pair

Features:

Run in memory
Use key-value pairs to hold and represent data
After running, data synchronization (writing data from memory to disk) is performed

Both comparisons

Data stored in different media (relational in disk, non-relational in memory)
Non-relational databases are much more efficient than relational databases
Relational databases are more secure than non-relational databases

Build Mysql environment

windwos

Use the installation package.msi
Use the zip decompression form

Rename my-default.ini to my.ini
Modify related configurations ~~

Basedir = D: \ MySQL \ Server \ MySQL – 5.6.20 – win32

datadir = D:\MySQL\Server\data
mysqld -install
net start mysql

linux

sudo apt-get install mysql-server
apt isntall mysql-client
apt install libmysqlclient-dev
Test whether the installation is successful ` sudo netstat – tap | grep mysql `

The working process

1. The client sends a connection request to establish a connection: mysql -h -p u -p

H: host indicates the IP address or domain name. By default, it does not represent localhost
P: in uppercase, the default port number is 3306
U: username indicates the username
P: password indicates the user password

2. Prepare SQL statements: Execute them line by line, ending with a semicolon (;)

3. Send the SQL statement to the server

4. The server receives and executes the SQL

5. The server sends the execution result to the client

6. The client parses the result

7. Close the connection

exit
quit
\q

Library operation

Database naming conventions

1. Use letters, underscores, and numbers

2. The keyword cannot be a keyword. If the keyword is a keyword, enclose the name with inverted quotation marks

Backquotes: The wavy line key below ESC corresponds to the Output in English

Adding a Database

Create database Database name [library option];

Library options:

Character Set (CHARset) : Indicates the data to be stored in the current database. The default character set is UTF-8

Collate Set Settings: How do I compare sizes

Viewing a Database

Run the show databases command to view basic database information. | | fuzzy query: show the databases like ‘pattern’

Fuzzy matching: % matches anything, and _ matches a character

Run the show create database command to query the database creation statement

Modifying a Database

The database name cannot be changed. Only the library options of the database can be changed

Alter DATABASE Alter database name

Deleting a Database

Drop Database Specifies the Database name.

The storage engine

Storage engine: A way of processing and saving data. The main storage engines are InnoDB and Myisam

InnoDB: creates a structure file with data and indexes stored in ibDatA1 file

Myisam: Create three files, structure, index and data

Engines fall into two categories because of the cost

Free: InnoDB and Myisam are both free

Charge: BDB, memory, archive are charged

The difference between Myisam and InnoDB

1. Myisam is more efficient than InnoDB in inserting data and querying data

2. InnoDB updates and deletes more efficiently than Myisam

InnoDB supports transaction security, Myisam does not

How to choose a storage engine?

1. You can use the default database storage engine (InnoDB)

2. If you need transaction processing, you can only choose InnoDB

3. The data itself is mainly inserted and queried, so you can use Myisam

SQL is a strongly typed language. When defining data, you must specify data types.

Table operation

The new table

Create table name (field 1, field 2)[table option];

1. A field must have a field name and field type

2. Separate fields by commas (,)

3. The last field does not need a comma

Table options

Character set: What character set is used to store data in the current table

Storage engine: What storage engine is used to store the data of the current table

InnoDB: only one table structure file is created. Other indexes and data are stored in ibDATA1 file

Myisam: creates three files, one structure file, one data file, and one index file

Delete table

Drop table table name

See the table

1. Check the basic information table: show tables | | show tables like ‘pattern;

2. Run the show create table command to query the table creation statement.

3. Check the table structure: desc | the describe table name | | show the columns from the table name

Modify the table

You can modify the name of the table, add, delete, change the table of the field, field property modification, field position modification

Alter table alter table [add/modify/drop] [column]

Add field [add]

The alter table table name

Add column name [position]

Fields are added at the end of the table by default

Position: first means first, after means after a field (the default is after the last field)

[modify]

Alter table name alter table name alter table name

Alter field name [change]

Alter table alter table alter table alter table alter table alter table

Drop field [drop]

Alter TABLE Table name Drop field name

Note:

During the creation, whether the current table name exists is not known. If not exists, the table is created only when the table name does not exist. Otherwise, the execution is abandoned.

Create table [if not exists] Create table name (field 1 type, field 2 type)
A table is deleted without knowing whether the table exists: if exists

Drop TABLE [if exists] Specifies the name of the table
Databases and tables cannot be deleted at will. Deletion is irreversible. If you decide to drop a database or table, you must make a backup.

Basic data operation

The new data

Insert into table name (table name) values(table name)

The field list can have no fields, which means that the number of fields in the value list must be exactly the same as the number of fields in the table

Care must be taken to insert data

1. The inserted value type must be the same as the data type defined in the data field. All values except integers must be quoted

2. Integers can also be quoted

3. Multiple records can be inserted at a time. Separate values by commas (,)

View the data

Select * from ‘select’ where ‘select’ from ‘select’

Modify/update data

Select * from table_name where table_name = 1 where table_name = 1

Delete the data

Delete from table name [where condition]

Row and record: Row and record represent exactly the same meaning, row is defined in terms of table structure, and record is defined in terms of data

Columns and fields: The differences are the same as rows and records.

The data type

The field properties

null/not null

Not NULL indicates that the value of a field cannot be null and must be given a value (not NULL).

default

primary key

Primary key, primary key, a field decorated with a primary key that is unique (cannot be repeated)

A table can have at most one primary key
The primary key field cannot be empty

auto_increment

Automatic growth

Fields must be integers
One of the first conditions that must be met to use an auto-growing field is that it is an index field
A table can only have one automatic growth
Int primary key auto_increment (int primary key auto_increment)

unique key

Unique key. The value of the field cannot be repeated

A table can have multiple unique keys
Unique keys do not handle NULL

comment

annotation

Advanced data operation

increase

Primary key conflict :insert into table name values(values list) on duplicate key update field = value, field = value

Insert: replace into table name (field list) values(value list);

delete

Delete from table name [where condition] [limit]

Deleting data does not change the index and self-growth of a table. If you want to delete the autogrowth of the table and start again

Drop table drop; create table drop;

Mysql provides a simple way to implement two steps: truncate table name;

check

Alias: as

Where: Filters data based on criteria

Comparison operators: >, >=, <, <=, = (comparison),! =, <>, in, between and, is [not]null

In: Represents the value of a field in a collection (containing multiple data)

Select * from table where id = 1, id = 2 ;

Between and: indicates that within a certain interval (closed interval)

Select * from table where (A must be less than or equal to B);

Is NULL: Checks whether the data is null

Select * from table where id is [not] null;

Logical operators: && and | |, or not

Group by:

After group by, the data is merged so that only the first record of each group is symbolically retained.

Generally, group by is not to get all the data of each group after grouping, but to count the information of each group.

Statistical functions: all mathematical statistics

Count: Count the number of all records, or all fields (not null)

Max: Indicates the maximum value of a group, usually a field

Min: indicates the minimum statistics

Avg: statistical average

The sum, sum

Having:

The function is the same as where, used to make conditional judgment

Difference: Having can use field aliases, but WHERE cannot

Difference: Having can use statistical functions

The order by:

Collate desc collate desc collate desc collate desc collate desc collate desc collate desc

limit:

Limit the starting position of the data query and the number of pieces of data returned (starting with the first piece by default)

change

Update table_name set table_name = table_name [where table_name] [limit]

Relationship between

Many to one/one to many

Requirements: There is a student list and a class list

A student belongs to a classroom, but a classroom contains many students. This relationship is called a many-to-one or one-to-many relationship

Many to many

Requirements: There is a teacher table and a student table

If one student is taught by more than one teacher, and one teacher teaches more than one student, the relationship is called many-to-many

One to one

Requirements: One student information, there are 10 kinds of information, only 5 information is very commonly used, the other 5 information is basically not used (marital status, native place, nationality).

Frequently used and infrequently used data in a table are separated and stored separately, but managed using the same ID.

MySQL > Insert into a pit

MySQL > Insert into a pit

Related Posts

Where did the “time” go? Performance tuning analysis method and case study

MySQL garbled characters

Dubbo performance tuning parameters and principles