(1) Introduction

When you log in your QQ account and blow water with others, when you hand in your homework at the end of the term and copy on Baidu, when you log in some websites at work, and when you check your bank card with almost no balance (covering your face), you may not pay attention to it (not necessary, We’ve all been working with databases in one way or another. Maybe you’re a developer, maybe you’re just a user. Databases are very much a part of our lives. Like Microsoft SQL Server used to cooperate with asp time is quite many, but now it is not so popular,

So let’s start with MySQL, while the article interspersed with some basic CONCEPTS of SQL, I think I can better understand these types of SQL database ha

I have updated several articles about MySQL in the past year or so, but they are not satisfactory, so RECENTLY I have worked overtime to re-organize MySQL related, such as syntax, constraints, transactions, etc. The first article will first look at some of the syntax and operation

Since you have opened this article, that you are not destined to be a villager, this round we jump Java programmers!

Development learning, want to meet some needs, dealing with data this can be too frequent, how to store these data is the problem you consider, of course, not considering performance, management, security and so on, you can choose more, save TXT, save XML, even can save excel and so on, A friend once asked me such a question “he is learning some IO technology, often will simply save some data to.txt file, why also need a database?” , such a small Demo, we should be done in the Java foundation of learning, so we will start from this problem ~

(2) Why use database?

(1) Query speed

Suppose we store all the data in a plain file (data.txt)_(separated by commas)

. "Eminem",male,1972,"USA" "Rihanna",female,1988,"Barbados" "Taylor Swift",1989,female,"USA" "Aavril Lavigne",1984,female,"Canada" ......Copy the code

If in the case of our data is very huge, we need to query some of the data, for example, the query Eminem’s nationality, we usually use traversal, but there is no doubt that the response time will be very slow, but after using the database, it provides some indexes of technology can solve this problem

(2) The data is complete and valid

"Rihanna",female,1988,"Barbados"
"Rihanna",female,1995,"USA"
Copy the code

If there is a duplicate name in the data, how can you tell if it is the same person?

What if the data in the data.txt file has been incorrectly modified, such as an invalid representation of a birth year being changed to another type of string?

Maybe you could write some logic statements in the program and then screening deal with these problems, but is still in the case of data is relatively large, can appear all sorts of problems, increase the development difficulty of developers, and database itself made some constraints, so as to ensure the integrity of the data, and effective, so that the developers only need to pay more attention to in the design of the program itself, You don’t have to spend too much time dealing with the details of the data

(3) Data sharing

TXT/Excel and other files commonly used for simple data storage belong to a single file, and cannot be shared, only the current user can use and modify

While the database allows users to share, different users can access the data in the database at the same time, users can also use the database through the interface in various ways, and provide data sharing

(4) Data security

In our former case, the modification of data is very arbitrary, but in real development, we sometimes have to face, multiple users to retrieve and modify the data in the same file, or in the case of concurrent, write the same file or record, and some of the database based lock technology can help us solve these problems

(5) Fault recovery

A logical or physical error leads to the wrong operation of the system, which leads to the destruction of the data. How to recover the data quickly? The single file system above us obviously cannot help us solve the problem, but the database has the relevant mechanism to remedy and deal with the related problem

Above we have only analyzed from a few common points why to use database, of course there are far more than these, so in general is the database its special storage and management way, not only improve efficiency, but also greatly reduce the burden of developers

To sum up: database EM… It’s a good thing!

(3) MySQL basic operation

As for the installation part, here will not mention, otherwise the length will be too long, whether it is to choose the installation version or free installation of the compression package, choose the right version, installed on the line, you can go to the online reference some tutorials, as for version 5.5 -> 5.6 -> 5.7 -> 8 are ok, After all, I’m just learning MySQL, so I don’t have to worry too much about the version or configuration of Java, etc. The version of my machine is 5.7 ha

It is also possible to use some graphical tools such as Navicat, SQLyog, etc. It is more comfortable to look at the data. However, even if you are getting started with graphical tools, I do not recommend clicking the button directly. Perform operations such as create insert, relying too much on the graphical interface, will make your inertia become more big, on the high-level language to write SQL is also harmful, must write, execute, in order to better understand and learn ha

(1) Start the service

First, you need to start the MySQL service before logging in. There are two ways

1. Set in the Service window

• CMD –> services. MSC –> find MySQL service –> Set start, disable etc

2. Run the command

Net start mysql: starts the mysql service

Net stop mysql: stops the mysql service

(2) Login and logout

A: login

MySQL 5.7 Command Line Client (MySQL 5.7 Command Line Client)

2. Through the command line

• If environment variables are not configured, run the CD command to go to the bin folder in the mysql installation directory
• If MySQL/bin is already in the environment variable, simply execute the command

3, command line example:

① mysql -u user name -p password

② mysql -u user name -p

③ mysql -h address -p port -u user name -p

For example, user name: root, password: root99

• mysql -uroot -proot99
  • In this case, a warning pops up that the password entered in clear text on the command line is not secure
  • I use Win PowerShell, using CMD is the same ha

• mysql -uroot -p
  • In this case, the password entry is masked by an asterisk and the warning disappears

• Mysql -h 192.168.3.144 -p 3306 -uroot -p

  • This method is used to connect to remote databases, as I demonstrated here using my local virtual machine
  

B: quit

1, the exit

2, the quit

(4) SIMPLE understanding of SQL

(1) What is SQL?

A: Basic concept

Structured Query Language: Structured Query Language

SQL is the computer standard language for accessing and processing relational databases. It defines the rules for operating on all relational databases

Although much of the SQL syntax standard can be used directly by other DBMSS, most databases extend the SQL standard. And each kind of database operation way exists is not the same place, called ** “dialect” **

Whenever relational databases are involved, SQL is indispensable, such as commodity information stored in e-commerce websites and equipment and props information stored in games

B: Common categories

Common relational databases: MySQL, Oracle, Microsoft SQL Server, Microsoft Access, DB2

Commercial: Oracle, DB2, Microsoft SQL Server

Open source: MySQL

Desktop: Microsoft Access

Common non-relational databases: Cloudant, MongoDb, Redis, HBase

C:

To sum up: SQL is a powerful language, we can achieve a variety of complex requirements through a certain concise syntax

D: Supplementary matters

1. SQL statements are written on one or more lines, ending with a semicolon (;). Spaces and indentation can be used to enhance the readability of statements

2. The SQL statements of the MySQL database are case-insensitive. You are advised to use uppercase keywords

3. Three kinds of annotations

• A: Single-line comment:

# comment content (this is unique to MySQL)Copy the code

• B: Multi-line comment:

/* Comment the content */
Copy the code

(2) the classification of SQL

classification	role
Data Definition Language (DDL)	Allows users to define (create) database objects: databases, tables, columns, etc
Data Manipulation Language (DML)	Allows users to add, delete, or modify data from tables in a database
Data Query Language (DQL)	Allows users to query records (data) of tables in the database
Data Control Language (DCL)	Defines database access permissions and security levels, and creates users

MySQL syntax

(1) Operate the database

In fact, this part of the grammar introduction, around the CURD

• CURE –> C: Create (Create) + U: modify (Update) + R: query/read (Retrieve) + D: Delete (Delete)

This part, such as creating, querying, using the database is more commonly used, but the operation of the database will not be very frequent, the general initial set up, rarely to modify the database, so this part, a quick understanding of the good, the title marked does symbol will be used

A: Create A database ※

1. Create a database

CREATE DATABASEDatabase name;Copy the code

For example:

mysql> CREATE DATABASE mysql_grammar_test;

Query OK, 1 row affected (0.12 sec)
Copy the code

2. Create a database and check whether it does not exist

CREATE DATABASE IF NOT EXISTSDatabase name;Copy the code

3. Create a database and specify a character set

CREATE DATABASEDatabase nameCHARACTER SETCharacter set name;Copy the code

B: Modify the database

1. Modify the character set of the database

ALTER DATABASEDatabase nameCHARACTER SETCharacter set name;Copy the code

C: Query database ※

Query all database names

SHOW DATABASES
Copy the code

Query the character set of a database

• We can see the character set of a database by querying its create statement. When we created the database earlier, we did not specify the character set, but we can see that the default database character set is UTF-8

SHOW CREATE DATABASEDatabase name;Copy the code

For example:

mysql> SHOW CREATE DATABASE mysql_grammar_test; +--------------------+-----------------------------------------------------------------------------+ | Database | Create  Database | +--------------------+-----------------------------------------------------------------------------+ | mysql_grammar_test | CREATE DATABASE `mysql_grammar_test` /*! 40100 DEFAULT CHARACTER SET utf8 */ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec)Copy the code

D: Deletes the database

1. Delete the database

DROP DATABASEDatabase name;Copy the code

2. Check whether the database exists and delete it

DROP DATABASE IF EXISTSDatabase name;Copy the code

E: Use database ※

About the database commonly used to add, delete, change and check on the end, the following to begin to explain about the table and data, but we must specify the use of the database, can be aimed at this data for some operations table or data and so on

1. Query the name of the database being used

SELECT DATABASE(a);Copy the code

2. Use a database

USEDatabase name;Copy the code

(2) the operating table

Symbol rules: when showing some definitions below, for easy understanding, use Chinese symbols (there will be specific examples, do not worry about understanding).

• <>The content in is the actual semantics
• []Is optional (optional).
• {}Must be explicitly specified
• |Options for the operator
• [,...n]Indicates that the preceding item can be repeated multiple times

A: Create table ※

SQL defines relational schema by defining the base table corresponding to a relationship. The statement format is as follows:

CREATE TABLETable name (< column name1> < Data type1> [< column level integrity constraint >], [< column name2> < Data type2> [< column level integrity constraints >],... ] , [< table level integrity constraints >]);Copy the code

Of course, this definition might be a little confusing to you, but I’m going to explain it to you later with a practical example, but before I do that, there are two important things to add:

① Data type

Every property in a relationship comes from a field, and its value has to be the value of the field, and in SQL, the concept of the field is implemented as a data type, so for example, this column of data that we define can only be a string of 8 units or less

Several common data types are listed below

The data type	meaning
CHAR(n)	A string of constant length n
VARCHAR(n)	A side length string of maximum length n
INT or INTERGER	Long integer
SMALLINT	Short integer
DOUBLE (6, 3)	A double precision floating point number, representing a total of six digits and three digits after the decimal point.
FLOAT(n)	Floating-point number with accuracy of at least n digits (optionally used directly)
DATE	Date: yyyy- MM-DD
TIMESTAMP	The timestamp type includes year month day hour minute second YYYY-MM-DD HH: MM :ss

② Constraint conditions

A constraint is a set of constraint conditions for the value of an attribute. The constraint conditions for a single column are called column level constraint, and the constraint conditions for multiple columns are called table level constraint. For example, if a column is called student number, we specify the constraint that this line cannot be NULL and so on

Constraints to clarify, or need a bit of space, at the same time, such as foreign key constraints, will extend some of the problems between multiple tables, for the sake of simplicity, in this introductory article will not write about constraints and multiple tables, I will be related to this part of the content of the next two

3 Example for creating a table

Select stu_id as primary key, stu_id as primary key, stu_id as primary key, stu_id as primary key, stu_id as primary key, stu_id as primary key, stu_id as primary key, stu_id as primary key

CREATE TABLE student(
	stu_id INT,
	stu_name VARCHAR(32),
	stu_age INT,
	stu_birthday DATE,
	stu_score DOUBLE(4.1),
	stu_insert_time TIMESTAMP,
	PRIMARY KEY(stu_id)
);
Copy the code

B: Modify the table

1. Modify table name

ALTER TABLEThe name of the tableRENAME TOThe new name of the table;Copy the code

2. Change the column name and type

ALTER TABLEThe name of the tableCHANGEColumn name New column name new data type;Copy the code

3. Change the type

ALTER TABLEThe name of the tableMODIFYColumn name new data type;Copy the code

Alter table character set

ALTER TABLEThe name of the tableCHARACTER SETCharacter set name;Copy the code

Add a column

ALTER TABLEThe name of the tableADDColumn name data type;Copy the code

C: Query table

1, query all table names in a database

SHOW TABLES;
Copy the code

2, query table structure

DESC table name;Copy the code

D: Delete the table

DROP TABLEThe name of the table.Drop TABLE IF EXISTSThe name of the table.Copy the code

(3) Add, delete and modify the data in the table ※

This piece of content, in fact, is our popular sense of the increase, deletion, change and check, this piece is also the most used, after all, the database and table, the initial design will be good, unless there is a large temporary demand for change, otherwise rarely to modify

A: Add data ※

1. Basic Grammar:

INSERT INTOTable name (column name1And the column name2. The column n)VALUES(value1And the value2. The value of n);Copy the code

2. Take an example

Use the student table created above

INSERT INTO student(stu_id, stu_name, stu_age, stu_birthday, stu_score, stu_insert_time) Values(1001.'Joe'.20.'2020-05-30'.66.6.'the 2020-05-30 19:00:35');
Copy the code

3, pay attention to

• Column names and values should correspond one to one
• All types except numeric types should be enclosed in single/double quotation marks (single quotation marks are common).
• If the column name is not defined after the table name, the value is automatically assigned if it matches the actual column name. Otherwise, an error is reportedColumn count doesn't match value count

B: Delete data ※

1. Basic grammar

DELETE FROMThe table name [WHEREConditions]Copy the code

The conditions for deleting data are flexible. In this section, I’ll give you a few examples, such as on the basis of inserting three such records

INSERT INTO student(stu_id, stu_name, stu_age, stu_birthday, stu_score, stu_insert_time) Values(1001.'Joe'.20.'2020-05-30'.66.6.'the 2020-05-30 19:00:35');

INSERT INTO student(stu_id, stu_name, stu_age, stu_birthday, stu_score, stu_insert_time) Values(1002.'bill'.25.'2020-05-30'.66.6.'the 2020-05-30 19:00:35');

INSERT INTO student(stu_id, stu_name, stu_age, stu_birthday, stu_score, stu_insert_time) Values(1003.'Cathy'.16.'2020-05-30'.66.6.'the 2020-05-30 19:00:35');
Copy the code

• Example 1: Delete the record whose STU_id (student ID) is 1001

DELETE FROM student WHERE stu_id = 1001;
Copy the code

• Example 2: Delete the record where stu_name (name) is zhang SAN

DELETE FROM student WHERE stu_name = 'Joe';
Copy the code

• Example 3: Delete the record whose STU_age is greater than 20

DELETE FROM student WHERE stu_age >= 20;
Copy the code

2. Delete all data

If you do not fill in the condition section of the delete statement, all data in the table will be deleted

 DELETE FROMThe name of the table.Copy the code

However, this method is not recommended because deletion operations are performed as many times as there are records in this method, and the efficiency is low when there is a large amount of data

It is recommended to use

TRUNCATE TABLEThe name of the table.Copy the code

It is more efficient to drop the entire table and then create an empty one, the same as the original one

C: Modify data ※

UPDATEThe name of the tableSETThe column name1= value1And the column name2= value2. [WHEREConditions];Copy the code

In terms of modifying data, in general, you do it by specifying an ID value in the condition, and then modifying it, because that ID value is usually the primary key, and it determines the unique data

• Example 1: For example, change the date of birth and grade of student number 1001

UPDATE student SET stu_birthday = '2020-02-22', stu_score = 90.8 WHERE stu_id = 1001;
Copy the code

When you test for yourself, the substitution seems to work without specifying WHERE stu_id = 1001

UPDATE student SET stu_id = 1001, stu_birthday = '2020-02-22', stu_score = 90.8;
Copy the code

This is because we passed the primary key stu_id = 1001

• Supplement: generally, we use MySQL in a high-level language, do not consider using plug-in, write their own most of the time is always the SQL to write all of the fields directly, does not like above, only write some, because we are not sure every time a user which is need to modify the data items, the effectiveness of the final can simulate as follows:

UPDATE student SET stu_id = 1001, stu_name = 'Joe', stu_age = 20, stu_birthday = '2020-01-01', stu_score = 90.8, stu_insert_time = '2020-02-22' WHERE stu_id = 1001;
Copy the code

That is, the values that do not need to be updated are also listed, but the updated values are not changed, so that the user can make various changes. This part of the content, which is the focus of JDBC related articles, is not mentioned here

(4) Query the data in the table

This section is quite a bit of work, but it only deals with single-table queries. As for multi-table complex queries, we will cover constraints in the next article

A: Query all

This is very common, * for all, and then FROM specifies the table name, which lists the complete information for all records

SELECT * FROMThe name of the table.Copy the code

In MyBatis, the Base_Column_List will be used as the value of the query. The Base_Column_List will represent all the fields (columns), and the Base_Column_List will be used as the value of the query. * if there is a problem with late field growth, to some extent this will improve efficiency, but for ordinary projects, in fact, it does not matter much

B: Query the specified field (column)

If you need to query only a few specified fields, you can use the following method to list the specified fields, separated by commas

SELECTThe field name1The field name2..FROMThe name of the table.Copy the code

C: Conditional query

1. All conditional query statements are based on WHERE

2, WHERE with a variety of operators, you can flexibly achieve a lot of query requirements

• > 、< 、<= 、>= 、= 、<>
• IS NULL or IS NOT NULL
• AND or &&
• BETWEEN... AND
• The OR OR | |
• Or NOT!
• IN (set)
• LIKE: fuzzy query
  • _ ：A singleAny character placeholder
  • % ：multipleAny character placeholder

3. Give some examples

• Example 1: Query student whose grade is not 66.6

SELECT * FROM student WHEREstu_score ! =66.6;

SELECT * FROM student WHERE stu_score <> 66.6;
Copy the code

• Example 2: Query whether the result is NULL (for example, not entered)

Select * from student where grade fields are NULL
SELECT * FROM student WHERE stu_score IS NULL;	

Select * from student whose grade field is not NULL
SELECT * FROM student WHERE stu_score IS NOT NULL;	

This is the wrong way to write it. NULL checks are not equal
SELECT * FROM student WHERE stu_score = NULL; (error!!!Copy the code

• Select * from student where score = 60 and score = 80

SELECT * FROM student WHERE stu_score >=60.0 AND stu_score <= 80.0;	

SELECT * FROM student WHERE stu_score >=60.0 && stu_score <= 80.0;	

SELECT * FROM student WHERE stu_score BETWEEN 60.0 AND 80.0;	
Copy the code

• Example 4: query the score of the student is 40.0, 90.8, the following three kinds of check result is the same

SELECT * FROM student WHERE stu_score = 40.0 OR stu_score = 90.8;	

SELECT * FROM student WHERE stu_score = 40.0 || stu_score = 90.8;	

SELECT * FROM student WHERE stu_score IN (40.0.90.8);
Copy the code

Note here that I used the = test IN OR above to introduce this use of IN, but it doesn’t quite reflect the difference between OR AND AND

• AND literally means “AND”, while OR is “OR”, that is to say, “OR” only needs to meet the requirements of one side to find out
• The problem arises when, for example, we also use the greater than or less operator

SELECT * FROM student WHERE stu_score >= 60.0 OR stu_score <= 80.0;	
Copy the code

Select * from (select * from (select * from (select * from (select * from (select * from (select * from (select * from))))

• Example 5: Fuzzy query: query the students whose surname is Zhang

SELECT * FROM student WHERE stu_name LIKE 'a %';
Copy the code

• Example 6: Fuzzy query: query the student whose name the second word is 4

SELECT * FROM student WHERE stu_name LIKE Forty percent '_';
Copy the code

• Example 7: Fuzzy query: query students whose name length is 4 characters_ ）

SELECT * FROM student WHERE stu_name LIKE '____;
Copy the code

• Example 8: Fuzzy query: query students whose names contain the word “5”

SELECT * FROM student WHERE stu_name LIKE '% fifty percent';
Copy the code

D: Sort query

Most of the time, the query will be ordered BY the primary key, such as id, but if we want to ORDER the query in a certain way, we will use the ORDER BY statement

ORDER BY... ORDER BY... ORDER BY...-- Sort by:ASC: Ascending, default. DESC: descendingCopy the code

Note:

Select * from primary key; select * from primary key; select * from primary key; select * from primary key

2. The default sort is ascending, that is, from lowest to highest

• Example 1: Sort queries from highest to lowest grade (descending order)

SELECT * FROM student ORDER BY stu_score DESC; 
Copy the code

• Example 2: Sort queries by grade and from highest to lowest (descending), followed by lowest to highest by age (ascending)

SELECT * FROM student ORDER BY stu_score DESC, stu_age ASC; 
Copy the code

E: Group query

1. Basic grammar

GROUP BY Indicates the GROUP fieldCopy the code

• Select * from students aged 18 and 20; select * from students aged 20 and 18; select * from students aged 18 and 20; select * from students aged 20 and 18; select * from students aged 20 and 18

SELECT stu_age, AVG(stu_score), COUNT(stu_id) FROM student WHERE stu_score > 60 GROUP BY stu_age;
Copy the code

AVG method is the average value, COUNT method is to calculate the number

This may not be easy to understand, but here’s another picture, let’s take a look at all the data, there are 7 students, the age is only 18 and 20, of course, it’s more realistic to use gender, class, department, etc., but I’m just too lazy to create another table, let’s just use age to illustrate

SELECT stu_age, AVG(stu_score), COUNT(stu_id) FROM student WHERE stu_score > 60 GROUP BY stu_age HAVING COUNT(stu_id) > 2;
Copy the code

This place is about to introduce a new concept, HAVING

What’s the difference between WHERE and HAVING?

• WHERE is qualified before grouping. If not, no grouping is performed
• HAVING is qualified after grouping, not available
• WHERE can not be followed by aggregation functions. HAVING can be used to judge aggregation functions, such as AVG method and COUNT method

Execution Result:

Aggregate function supplement:

Aggregate function: vertically calculates a column of data as a whole. Try to select columns that are not empty for calculation; otherwise, IFNULL function is required for judgment

COUNT: Indicates the number to be calculatedSelect a non-empty column: primary keyMAX: calculate the maximum value MIN: calculate the minimum value SUM: calculate and AVG: calculate the average valueCopy the code

F: Paging query

If too much data is queried and displayed on the same page, it can be troublesome, and paging queries can solve this problem

1. Basic grammar

Limit Specifies the number of queries per page.Copy the code

2. Basic writing formula sets

Start index = (current page number -1) * number of pages to display per pageCopy the code

• Example 1: Display two pieces of data per page

SELECT * FROM student LIMIT 0.2; - page 1

SELECT * FROM student LIMIT 2.2; - page 2

SELECT * FROM student LIMIT 4.2; - page 3

SELECT * FROM student LIMIT 6.2; Page 4 -
Copy the code

I temporarily have 7 data ids from 1001-1007 in the table, so if I execute the statement to display the second page, I will display data with ids 1003 and 1004

In addition, LIMIT is a MySQL” dialect “that is not supported in other databases, but there are some alternatives for different databases

(6) Conclusion

If there is any deficiency in the article, you are welcome to leave a message to exchange, thank friends for their support!

If it helps you, follow me! If you prefer the way of reading articles on wechat, you can follow my official account

We don’t know each other here, but we are working hard for our dreams

A adhere to push original development of technical articles of the public number: ideal more than two days