MySQL basic knowledge

MySQL basic knowledge

If we want to learn the database well to basically be able to mix and eat with any programming Languages and you can become a good programmer or Database developer or Database administrator.

This Story explains the basic knowledge of MYSQL, syntax of MYSQL and detailed usage of MySQL. The content includes: SQL syntax, SQL data types, common SQL statements, complex queries including, join queries on MySQL databases.

1.The community version of MySQL Community Server is open source and free, but does not have official technical support.
2. MySQL Enterprise Edition is an enterprise version that can be tried for 30 days and needs a fee.
3. Version of MySQL Database Cluster, open source and free. It’s possible to bundle multiple MySQL servers into one server.

4. Payment is needed for mySQL Cluster CGE Advanced Cluster Version.

5. MySQL Workbench (GUI TOOL) is a tool for ER/database modeling specifically designed for MySQL. It is the successor to DBDesigner4, the popular database design tool. MySQL Workbench consists of two versions, namely the MySQL Workbench OSS (community version) and the commercial version (MySQL Workbench SE).

MySQL Community Server, which is also the version of MySQL that we normally use, is open source and free. It is subdivided into different variants according to distinct operating system platforms.

What is MySQL Enterprise?

MySQL Enterprise Edition is an established and trusted platform that provides enterprise-level MySQL database software, monitoring and consulting services, and technical support to ensure the highest level of reliability, security and real-time is achieved by your company.

MySQL Enterprise Edition includes:

This is the most stable and secure version of the most common open source database in the world, the MySQL enterprise server.
It will provide monitoring and automated consulting services to help you reduce security threats, boost replication, maximize efficiency, etc. MySQL enterprise-level device monitoring tool
MySQL technical support will easily answer the most challenging technical questions.
Only customers who have paid MySQL enterprise silver or gold service will get this assistance from MySQL consulting support. In order to achieve better results, the MySQL technical support team will provide tailored feedback for your system and inform you how to properly build and adjust your MySQL server, plan, query and replication settings.

What is a Community Server for MySQL?

MySQL has concentrated on releasing to the open source community the world’s most common open source database-MySQL Community Server.

Under the open source GPL license, it can be used freely.

What is the difference between Group Edition and MySQL Enterprise Edition?

MySQL started releasing MySQL Enterprise at the end of 2006, which includes a range of more extensive services to enhance MySQL server reliability, protection and efficiency.

You can get the details in the table below to better understand the difference between MySQL Enterprise Edition and Community Edition:

It is recommended that you use the MySQL Enterprise Edition solution if your organization follows any of the following characteristics:

Other answers:

MySQL Community Version is a license under the open source GPL which can be accessed free of charge.

The online edition of MySQL is a license certified by MySQL and requires money to buy.

MySQL Network Edition provides network and business deployment capabilities, troubleshooting capabilities, technical and software support, enhancements and improvements, MySQL knowledge base enjoyment, and direct feedback from MySQL developers not included in the MySQL Community Edition.

No money for this, the first MySQL Community Server!
The second MySQL Enterprise needs capital, but you can call, that is, telephone technical support, to ask questions.
The third MySQL cluster should be used on the basis of 1 or 2; this alone is useless. It is used, of course, to balance various databases.
This is a positive thing for the fourth MySQL Workbench that was used to design the database. Does Erwin really know? It’s his job.

The community version of MySQL Community Server does not have official technical support.

Database of MySQL Business Server Server of MySQL Enterprise Version
The most stable, most secure and updated version of the MySQL enterprise server database is the program. It can cost-effectively deliver e-commerce, online transaction processing (OLTP), gigabit-scale data warehouse applications, etc. It facilitates the processing of ACID transactions and can provide complete functions for commit, rollback, crash recovery and row-level locking. Due to its ease of use, scalability and high performance, the MySQL database has become the most common open source database in the world.

MySQL Cluster 2 or more servers in the MySQL cluster

There are two models of MySQL Workbench:
MySQL Workbench Community Edition and MySQL Workbench Standard Edition (also called MySQL Workbench OSS, community edition) (also called MySQL Workbench SE, commercial edition). MySQL Workbench OSS is a version of the open source community that is published under a GPL certificate, while MySQL Workbench SE is an annually paid commercial version. There are variations in its functions (I have to admit that only the paid MySQL Workbench SE is available for the essential feature of database/model synchronization,

MySQL GUI Tools is a visual interface MySQL database management console that offers four very useful graphical applications to promote database management and data query. These graphical management tools can significantly enhance the efficiency of database management, backup, replication and query, and can be freely implemented even by users who do not have a rich SQL language basis.

They are: 
MySQL Migration Toolkit: migrating databases 
MySQL Administrator: Manager of MySQL 
MySQL Query Browser: a graphical Data Query Client 
MySQL Workbench: tool for DB Design

Basic Learning Mysql Contents:
1. Introduction to MySQL
Two, database operation
2.1, create a database
2.2, delete the database
2.3, use the database
2.4, view the database
Three, the type of database column
3.1, numeric type
3.2, string type
3.3, time and date type
Fourth, the field properties of the database
4.1, field attributes
Five, the operation of the database table
5.1, table creation
5.2. Table modification and deletion
Six, DML language data management
6.1, DML language
6.2, insert data
6.3, modify data
6.4, delete data
Seven, SQL statement operators
7.1. Operators and their meanings
8. DQL query data
8.1, Select statement
8.2, Where statement
8.3, fuzzy query
8.4, JoinOn table query
8.5, paging and sorting
8.6, subqueries
Nine, MySQL commonly used functions
Ten, MySQL transaction
10.1 Atomicity
10.2 Consistency
10.3, isolation
10.4. Persistence
11. Summary

1. MySQL introduction
MySQL is an open source relational database management framework that uses the Structured Query Language (SQL) for database management, which is the most widely used database management language.

MySQL is open source, so under the General Public License, anybody can download it and change it according to individual needs.

Due to its speed, reliability and adaptability, it has attracted a lot of interest. Most people think that MySQL is the best option without transactional processing for managing material.

Note: The material above is taken from the Encyclopedia

Notes on SQL statements:

1. In the SQL statement, the content of [] is optional and can be written or not written.

2. You may or may not write a semicolon at the end of each SQL statement.

3. SQL statements are not case sensitive, but upper and lower case statements are okay. — is the symbol for a statement.

4. All SQL statements have to be entered in English except for Chinese characters, otherwise an error would be identified.

5. The “ symbol (I call it an oblique quote mark) on the name of the database, the name of the table or the name of the sector. This symbol is not a single quote mark, but above the tab key is the key symbol. You may either write this symbol or not. If a special character or keyword is your database name, table name, or field name, you need to carry this symbol with you.

Two, database operations
2.1, create a database
Basic syntax:

CREATE DATABASE [IF NOT EXISTS] databasename;
For example, create a database named pankajtest:
CREATE DATABASE IF NOT EXISTS pankajtest;

When creating a database, we should use it IF NOT EXISTSto determine whether the created database already exists, so as to avoid errors when running SQL statements.

2.2, delete the database
Basic syntax:

DROP DATABASE [IF EXISTS] databasename;
For example, delete a database named pankajtest:
DROP DATABASE IF EXISTS pankajtest;

2.3, use the database
Basic syntax:

USE databasename;
For example, using a database named pankajtest:
USE pankajtest;

It is worth noting that if your database name, table name, or field name is a special character or keyword, you need to put ``, this symbol is not a single quote, but the symbol of the key above the tab key, that is, oblique quotation marks.

For example, use a database named pankajtest or table:
USE `pankajtest`;
USE `table`;

2.4, view the database
Basic syntax:

SHOW DATABASE;
Refers to viewing all databases.

3. The type of database column
3.1, numeric type

Fourth, the field properties of the database

4.1, field attributes

Unsigned: The Unsigned attribute refers to an unsigned integer. If this attribute is declared, the data of the column corresponding to the field cannot be a negative number.

zerofill: The zerofill attribute refers to 0 filling, that is, the insufficient digits are filled with 0.

Self-increment: Automatically add 1 to the previous record (default), generally used to set a unique primary key. The starting value and step length of self-increment can be customized.

Not empty: If set to not null, you must fill in the value, otherwise an error will be reported; if set to null, you can leave the value blank, and the default value is null.

Default: Set to the default value, if you do not specify the value of the column, there will be a default value.

Five, the operation of the database table

5.1, table creation

Basic syntax:

CREATE TABLE [IF NOT EXISTS] `table name` (
`Field name` column type [attribute] [index] [comment],
`Field name` column type [attribute] [index] [comment],
...
`Field name` column type [attribute] [index] [comment]
) [Type of table] [Character set setting] [Note]

For example, create a student table:

CREATE TABLE IF NOT EXISTS `student` (
`id` INT(10) NOT NULL auto_increment COMMENT'student number',
`name` VARCHAR(20) NOT NULL DEFAULT'Anonymous' COMMENT'Name',
`age` INT(3) NOT NULL COMMENT'age',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

- `id`: field name (column name), use diagonal quotation marks ``, above the tab key
- INT(10): column type and length
- NOT NULL: not empty
- auto_increment: self-increment
- COMMENT: comment, followed by a string, use single or double quotes
- PRIMARY KEY: Set the field `id` as the primary key, that is, the student ID as the primary key
- Note: Each field needs to be separated by a comma

5.2, table modification and deletion

The basic syntax to modify the table:

- Modify the table name
ALTER TABLE old table name RENAME AS new table name
- Increase table fields
ALTER TABLE table name ADD field name column type
- Modify the field constraints of the table
ALTER TABLE table name MODIFY field name column type [class attribute]
- Modify the field name of the table (rename)
ALTER TABLE table name CHANGE old field name new field name [column type] [column attribute]
- Delete table fields
ALTER TABLE table name DROP field name

For example, modify the table name, field name, field constraints, and delete fields:

- Modify the table name
ALTER TABLE student RENAME AS student1
- Increase table fields
ALTER TABLE student1 ADD sex INT(2)
- Modify the field constraints of the table
ALTER TABLE student1 MODIFY age VARCHAR(12)
- Modify the field name of the table (rename)
ALTER TABLE student1 CHANGE age age1 INT(2)
- Delete table fields
ALTER TABLE student1 DROP age1

The basic syntax of deleting a table:

DROP TABLE [IF EXISTS] tablename

For example, delete the student table:

DROP TABLE IF EXISTS student

Six, DML language data management

6.1, DML language

DML language, the database operation language, includes data insertion, modification and deletion operations, namely insert, update, and delete.

6.2, insert data

Basic syntax:

INSERT INTO table name ([field name 1], [field name 2], [field name 3],...) VALUES (value 1, value 2, value 3,...)

The field name 1 corresponds to the value 1, the field name 2 corresponds to the value 2, and the field name n corresponds to the value n, which must be one-to-one correspondence.

For example, insert a value in the student table:

INSERT INTO `student`(`id`,`name`,`age`) VALUES(202101,'pankaj',20)

Or you can write it like this:

INSERT INTO `student` 
VALUES(202101,'pankaj',20)

I usually omit the diagonal quotation marks, because student is not a special character.

6.3, modify data

Basic syntax:

UPDATE table name SET field name 1=value 1, field name 2=value 2,... WHERE conditional statement

For example, modify the name and age of the student whose middle school number is 202101 in the student table:

UPDATE `student` SET `name`='ankit',`age`=21 
WHERE id = 202101

You can also modify only one:

UPDATE `student` SET `name`='ankit' WHERE id = 202101

6.4, delete data

Basic syntax:

-Delete all data in the table 
DELETE FROM table name 
-Delete the specified data 
DELETE FROM table name WHERE conditional statement

For example, delete the data of the table:

-Delete all data in the table DELETE FROM `student` 
-Delete the specified data DELETE FROM `student` WHERE id = 202101

DELETEThe command can be TRUNCATEreplaced by a command, it is recommended to use TRUNCATE(TRUNCATE command will reset the auto-increment column and will not affect the transaction).

Seven, SQL statement operators

7.1. Operators and their meanings

8. DQL query data

8.1, Select statement

Basic syntax:

- Query all records
SELECT * FROM table name
- Query the specified field
SELECT field name 1, field name 2, field name 3,... FROM table name
- Query specified fields and set aliases, aliases do not need to be quoted
SELECT field name 1 AS alias 1, field name 2 AS alias 2, field name 
3 AS alias 3 FROM table name
- Remove duplicate queries, remove duplicate records
SELECT DISTINCT field name FROM table name

For example:

- Query all students
SELECT * FROM `student`
- Query the specified field
SELECT `id`,`name`,`age` FROM `student`
- Query specified fields and set aliases, aliases do not need to be quoted
SELECT `id` AS student number, `name` AS name, `age` AS age FROM `student`
- Remove duplicate queries, remove duplicate records
SELECT DISTINCT `name` FROM `student`

8.2, Where statement

Basic syntax:

SELECT ... FROM table name WHERE conditional statement

For example:

- Conditional query
SELECT * FROM `student` WHERE age>=16 AND age<= 21
SELECT * FROM `student` WHERE age>=16 && age<= 21
SELECT * FROM `student` WHERE age BETWEEN 16 AND 21

8.3, fuzzy query

For example:

- Query students whose surname is Zhang, such as Zhang San and Zhang Pankaj, with any word after the name
SELECT * FROM `student` WHERE `name` LIKE'panka%'
- Query students whose surname is Zhang, such as Ankit, with only one word after the name
SELECT * FROM `student` WHERE `name` LIKE '%Ankit%'
- Search for students whose surname is Kumar, such as Uncle Zhang, with two words after the name
SELECT * FROM `student` WHERE `name` LIKE'KUMAR__'
- Search for students with a'three' in the middle of their name, such as Zhang Sanfeng, with two characters after the name
SELECT * FROM `student` WHERE `name` LIKE'%三%'
- Query students whose student ID is 202101, 202102, 202103
SELECT * FROM `student` WHERE id IN (202101,202102,202103)
- Query the name of the student whose age is not empty
SELECT `name` FROM `student` WHERE age IS NOT NULL

8.4, JoinOn table query

Basic syntax:

- Query the same record, at least one match will be returned
SELECT alias 1. Field name 1 alias 2. Field name 2 FROM left table table name alias INNER JOIN right table table name alias ON alias 1. field name = alias 2. field name
- left query
- Even if there is no match in the right table, all values will be returned from the left table
SELECT alias 1. field name 1 alias 2. field name 2 FROM left table table name alias LEFT JOIN right table table name alias ON alias 1. field name = alias 2. field name
- right query
- Even if there is no match in the left table, all values will be returned from the right table
SELECT alias 1. field name 1 alias 2. field name 2 FROM left table table name alias RIGHT JOIN right table table name alias ON alias 1. field name = alias 2. field name

For example, join table query:

-Query the records with the same middle school number in the student table and the result table, and at least one match will be returned
SELECT s.id s.name FROM student s INNER JOIN result r ON s.id = r.id
- Query the records with the same middle school number in the student table and the result table, the left table shall prevail- Even if there is no match in the right table, all values will be returned from the left table
SELECT s.id s.name FROM student s LEFT JOIN result r ON s.id = r.id
- right query
- Query the records with the same middle school number in the student table and the result table, the right table shall prevail
- Even if there is no match in the left table, all values will be returned from the right table
SELECT s.id s.name FROM student s RIGHT JOIN result r ON s.id = r.id

8.5, paging and sorting

Basic syntax:

-- descending sort
ORDER BY field name DESC
-- Ascending
ORDER BY field name ASC
-- Paging query
LIMIT starting value, the size of the page

For example:

-- descending sort
SELECT * FROM student WHERE age = 20 ORDER BY id DESC
-- Ascending
SELECT * FROM student WHERE age = 20 ORDER BY id ASC
- Pagination, page 1, query data from 1-5
SELECT * FROM student WHERE age = 20 ORDER BY id ASC LIMIT 0,5
- Pagination, page 2, query 6-10 data
SELECT * FROM student WHERE age = 20 ORDER BY id ASC LIMIT 5,5
- Pagination, page 3, query data from 11-15
SELECT * FROM student WHERE age = 20 ORDER BY id ASC LIMIT1 10,5

8.6, subqueries

Basic syntax:

SELECT name,age
FROM `student` s
INNER JOIN `result` r
ON s.id = r.id
INNER JOIN `subject` sub
ON r.subNo = sub.subNo
WHERE subName ='MySQL runs from getting started to deleting the library'

Nine, MySQL commonly used functions

You can go to MySQL official website to view: MySQL official documentation

Ten, MySQL transaction

Note: MySQL transaction (the following content is from the rookie tutorial)

MySQL transactions are primarily used with a great number of operations and high complexity to process data. In the personnel management system, for example, if you delete a person, you need to delete both the person’s basic information and the person’s relevant information, such as mailboxes, posts, etc., so that these database process statements are a transaction!
In MySQL, transactions are supported by only databases or tables that use the Innodb database engine.
The handling of transactions can be used to preserve database integrity, ensuring that batches of SQL statements are either executed or not executed at all.
The transaction is used to handle statements for insert, update, and delete. In general, four conditions (ACID) must be met by a transaction: atomicity (atomicity or indivisibility), continuity, isolation (also referred to as independence), durability (durability).

10.1 Atomicity

In a contract, all operations are either completed or not completed at all and will not end in an intermediate connection. If an error happens during the execution of the transaction, the state before the transaction begins will be rolled back (Rollback) as if the transaction had never been executed.

10.2 Consistency

The credibility of the database was not lost prior to the start of the transaction and after the completion of the transaction. This ensures that written data must comply entirely with all the preset laws, including the consistency and continuity of the data, and that the planned work can be unexpectedly performed by the subsequent database.

10.3, isolation

The database allows several simultaneous transactions to simultaneously read, write and change their data. When several transactions are executed simultaneously, separation will avoid data inconsistencies caused by cross execution. The isolation of transactions is divided into different stages, including uncommitted read (read uncommitted), committed read (read committed), repeatable read and serialization (Serializable).

10.4. Persistence

After the transaction is over, the modification of the data is permanent and will not be lost even if the system fails.

11. Summary

This article is the basic knowledge I summarized in the process of learning MySQL. The purpose of the summary is to facilitate future reference. If you want to learn MySQL systematically, it is recommended to consult official documents.

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.