A database is a warehouse that organizes, stores, and manages data according to data structures. Relational database is a database built on the basis of relational model, with the help of set algebra and other mathematical concepts and methods to deal with the data in the database. The salient features are as follows:
- The data is presented as a table
- Each behavior has various record names
- The data field corresponding to the record name for each column
- Many rows and columns make up a data table
- A number of data tables constitute a database
This article introduces general SQL statements based on the MYSQL database version 8.0.16. By reading through this article and practicing it, you will be able to master common data operations with MySql data. Want to extend from the front end to the back end of the partner, MySql database is a loop around the past, the premise of skilled use of MySql is to master the basic SQL statements. Don’t be scared, basic SQL statement learning, the amount of knowledge is even simpler than CSS ha.
MySql database installation is unnecessary. You can download the configuration from the official website. Navicat Premium is the database visualization tool used in this article, as shown below. You can download the free version of Navicat Premium from the official website.
Basic commands
- Database connection
The premise of using the database is to connect the data first (and then the premise is to install the database…) , and then you can enter SQL statements in the terminal to run.
After installing and configuring the mysql database, the mysql command is used to start the database
-u specifies the root account for the database. -p specifies the password for the database
mysql -u root -p
Copy the codeAfter entering the password, press Enter, and the connection is successful, as shown below:
Here is the terminal running SQL, how to use it in the project? MySql is also used in the project through some libraries to connect to the database, and then call some methods of the library into our spliced SQL statement. Here we are learning the various operations of SQL statements.
Before learning about SQL statements, it is important to note that SQL statements are not case-sensitive! SQL statements are case insensitive!! SQL statements are case insensitive!! Important things say three times ha! There is also a semicolon at the end of the statement.
- Creating a database
CREATE DATABASE Specifies the DATABASE name.Copy the codeCreating a database is simple, as solved by the above one-line command. How to check whether the database was created successfully? You can view it in the Navicat Premium Visualization tool above or run the view data command below.
- Viewing all databases
# Notice the semicolon at the end
SHOW DATABASES;
Copy the code
To create a database, that is naturally able to delete the database, the command is very simple, we continue to learn in the future.
- Deleting a Database
DROP DATABASE Specifies the DATABASE name.Copy the codeAfter creating the database, it is time to add, delete, change and check the database operations. Add, delete, change and check the premise is that we choose a database first, otherwise so many database ghost know to operate which ah! Don’t say ghost, even your dog doesn’t know ha! Licking dogs don’t know! Let’s learn how to select a database.
- Select database
USE database name;Copy the code
We know that a database is composed of many tables, so after specifying the database, we want to see what tables are under the current database.
- View all tables in the specified database
# USE select database first
SHOW TABLES;
Copy the codeAs shown in the figure below, you can see that all the tables are empty if you haven’t created a table, and the various operations on the table are followed. Of course, you can also use visual tools to view it, so I won’t go into details below:
The database table
With the database selected above, how do you create a data table?
- Create a database table
Create table user if 'user' does not exist
CREATE TABLE IF NOT EXISTS `user`(
`uid` INT UNSIGNED AUTO_INCREMENT,
`username` VARCHAR(128) NOT NULL,
PRIMARY KEY ( `uid` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the codeNote that AUTO_INCREMENT denotes the increment of the column value; PRIMARY KEY specifies the PRIMARY KEY of a table. Each table can have only one PRIMARY KEY, and its column value is unique and not empty. NOT NULL indicates that the field is NOT allowed to be NULL during database operation. Otherwise, an error will be reported. ENGINE=InnoDB specifies the ENGINE type, CHARSET=utf8; Specifies the character set. INT, VARCHAR(128) are the data types of the defined column values. We’ll talk about what data types are later, but it’s good to get a sense of what they are.
- Drop a database table
DROP TABLE TABLE name;Copy the codeOnce the table is created, how do we see which data fields are created in our table? You can run the following command to quickly view the information.
- View the data table structure created
Desc table name;Copy the code
SQL data types
MySQL supports all standard SQL numeric data types, such as date and time, strings, values, and so on.
| type | instructions | The scope of |
|---|---|---|
| CHAR | Fixed length string | 0-255.byte |
| VARCHAR | Variable length string | 0-65535.byte |
| TINYTEXT | Short text string | 0-255.byte |
| TEXT | Long text string | 0-65535.byte |
| TINYINT | A small integer value | (128127) |
| INT | Large integer value | (-2,147,483,648, 2,147,483,647) |
| DATE | Date value | The 1000-01-01/1000-01-01 |
| DATETIME | Prior time value | The 1000-01-01 00:00:00/1000-01-01 23:59:59 |
Note that the n in the parentheses char(n) and vARCHar (n) represents the number of characters, not bytes.
Refer to the documentation for other data types
Insert data
With basic data types in mind, let’s look at how to insert a data record into a data table. INSERT data INTO the specified table using the INSERT INTO keyword, as shown below:
- Insert data into a table
INSERT INTO table name (field1, field2,... fieldN) VALUES (value1, value2,... valueN);Copy the codeNote that multiple data fields are separated by commas. If the value is a string, it is enclosed by single or double quotation marks. The increment value does not require assignment.
How do we query a piece of data once we have the data in the table? That’s where the SELECT keyword comes in, so let’s move on.
Query data
The logic of a query is to use the select keyword to query the specified data from the specified table. If no query condition is specified, the query is to query all data.
- Query all column data of the table
Select * from user where user = 1
SELECT * FROM user;
Copy the code- Queries all data for the specified column in the table
Select * from user where username = 'username'
SELECT username FROM user;
Select * from user
SELECT username, otherColumn1, otherColumn2 FROM user;
Copy the codeNote that * represents all column fields, but from an optimization point of view, instead of using * to query all column fields, you use commas to query the desired columns, much like the front end Yahoo catch-all.
Now that we’ve learned the basics of table query data manipulation, we’ll move on to more complex query logic to query data that matches our criteria. For example, common scenarios include querying user data with UID = N, querying all blog data with N articles type js, and so on.
WHEREThe keyword can be used to specify search criteria
Select * from user where username = make
SELECT * FROM user WHERE username='make';
Copy the code
WHERE keyword is used to query data with additional query conditions. If there are multiple query conditions, it is necessary to use AND AND OR to connect multiple expressions to express the relationship with AND OR. For example, when we query students with scores greater than 90 AND male students, there will be multiple query conditions. We continue to look at the use of multi-conditional expressions.
ANDandORCan be found inWHEREMultiple expressions are used to representwithandorThe logic of the
Select the set of records in the books table whose type is 666 and whose author is Schneider
SELECT * FROM books WHERE type=666 AND author='Nai am';
Select * from books where type = 666 and author = make
SELECT * FROM books WHERE type=666 OR author='make';
Copy the code- The expression operator can be
=,! =,>,<,> =,< =.
Select * from books where id > 2
SELECT * FROM books WHERE id > 2;
Copy the codeNow that we know WHERE to attach a query condition, what if we want to query a specified number of items from a specified location? Typical scenario is the common backend interface paging query ah!
LIMITSpecify query data,OFFSETSpecify query location
The data for the existing books table is as follows
Select * from books where subscript = 2
SELECT * FROM books LIMIT 2 OFFSET 2;
Copy the code
In addition to finding accurate data, sometimes we may want to fuzzy query, such as query article title contains JS data, so in MySql how to query?
LIKEKeywords Fuzzy search
# LIKE grammarSELECT field1, field2,... fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 ='somevalue'
Copy the codeIn the expression after LIKE, % represents any character, similar to * in Linux.
Select * from books where bookname = “westward Journey”;
SELECT * FROM books WHERE username LIKE 'westward journey %';
Copy the codeUNIONConcatenate multiple SELECT expressions
Select * from level where level > 2 and level > 5
SELECT * FROM level WHERE level < 2 UNION SELECT * FROM level WHERE level > 5;
Copy the code
By default, the intersection obtained by the UNION will remove duplicate data records, and can also be displayed to specify whether to remove:
The # DISTINCT field indicates the removal of duplicate records and is also the default
SELECT * FROM level WHERE level < 2 UNION DISTINCT SELECT * FROM level WHERE level > 5;
The # ALL field indicates that duplicate records are not removed
SELECT * FROM level WHERE level < 2 UNION ALL SELECT * FROM level WHERE level > 5;
Copy the codeAfter learning the basic data query, is it full of sense of achievement? Then it’s time to update the data!
Update the data
- Updates all data for the specified column
UPDATE table name SET field1=value1, field2=value2;Copy the codeWHEREUpdate according to filter criteria
SET field1=value1, field2=value2 WHERECopy the codeDelete the data
- Delete the records that match the conditions in the table
Select * from books where id = 6
DELETE FROM books WHERE id=6;
Copy the code- Delete all data from a table
Select * from user
DELETE FROM user;
Copy the codeSorting query results
- Ascending order.
ORDER BYThe default is ascending. The default value isASC
Select * from field1; select * from field1
SELECT * FROM level ORDER BY field1;
Copy the code- In descending order,
ORDER BY DESCSpecify descending sort
Select * from field1; select * from field1
SELECT * FROM level ORDER BY field1 DESC;
Copy the codegrouping
GROUP BY allows you to GROUP tables BY specified fields, and then further manipulate the grouped data. For example, group the table by name and count how many pieces of data there are per name:
SELECT name, COUNT(*) FROM level GROUP BY name;
Copy the code
Did it make you feel happy after studying so much? This is an important part because in a real world scenario, it is often necessary to query multiple tables together to get the data returned to the client.
Multi-table LianZha
JOINIt can be used to query data in multiple tables and obtain records of field matching relationships in two tables. It’s kind of an intersection.
Select * from user where level = level
The fields returned by the query are user.uid, user.author, user.level, level.name, level.description
select a.uid, a.author, a.level, b.name as levelName, b.description as levelDesc from user a join level b on a.level = b.level;
Copy the codeNote that the AS keyword can rename the returned field AS an alias, AS shown in the following figure:
LEFT JOINGets all eligible records from the left table, even if there is no matching record from the right table
Select * from table B where table B does not exist
select a.uid, a.author, a.level, b.name as levelName from user a left join level b on a.level = b.level;
Copy the code
RIGHT JOINGets all eligible records from the right table, even if there is no matching record from the left table
select a.uid, a.author, a.level, b.name as levelName from user a right join level b on a.level = b.level;
Copy the code
Conditional expressions use re
In addition to fuzzy matching using the LIKE keyword, conditional expressions for WHERE can also be queried using regular expressions, such as REGEXP:
Select bookname from books where bookname is from the beginning of westward Journey
select * from books where bookname regexp '^ westward journey';
Copy the codeREGEXP is not only used for fuzzy queries, but also provides a flexible way to customize queries. Next we’ll look at another important concept in SQL: —- transactions.
The transaction
The main purpose of a transaction is to ensure that a set of database operations are successfully executed, otherwise there will be incomplete results if one data operation goes wrong. For example, if you want to delete a user and require their article information, login data, and so on, make sure that all the other deletions that occurred when you deleted the user also succeed. Transactions have the following important characteristics:
- in
MySQLOnly useInnodbOnly the database or table of the database engine supports transactions. - Transaction processing can be used to maintain database integrity by ensuring that batches of SQL statements are either all executed or none executed.
- Transactions are used to manage
insert.update.deletestatements
There are two main steps in the usage: BEGIN; Start a transaction; The second is to use ROLLBACK. You can roll back things or use COMMIT; Conduct transaction validation. Let’s look at a specific example:
# start transaction
begin;
# Some database operations
None of the data operations are actually written to the database
insert into books (bookname, type, author) values ('Vue.js', 123, 'Bowen Liu');
insert into books (bookname, type, author) values ('new book', 123, 'make');
insert into books (bookname, type, author) values ('new book2', 123, 'make');
The transaction is confirmed, at which point all data is fully written
commit;
Copy the code
Now, let’s think about a question. What if, after we create a table, we suddenly find that some fields are missing, or some data types are not correct, and we need to modify the table?
ALTER updates the data table
ALTER alters table names or fields.
- Add columns to a data table
Select * from user where newField1 = INT
alter table user add newField1 int;
Copy the code- Modify the names and types of data table fields
MODIFY is used to MODIFY the field type. CHANGE is used to MODIFY the name and type of a field.
Select * from user where newField1 = varchar(64)
alter table user modify newField1 varchar(64);
Select * from user where newField1 = new_field1 and varchar = 128
alter table user change newField1 new_field1 varchar(128);
Copy the codeNote: ALTER type overwrite operation, not incremental overwrite, so add all of the original:
For example, the original uid field is as follows
`uid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'user ID'.Alter table alter table alter table
Int (NOT NULL); int (NOT NULL)
alter table user modify uid int NOT NULL AUTO_INCREMENT COMMENT 'user ID'
Copy the codeDROPRemove field
Select * from user where new_field1
alter table user drop new_field1;
Copy the code- Set/remove the default values for the fields
If no default value is specified during the ALTER operation, the default value is NULL. The following example can be used to remove/set the default value of a field:
Select * from user where new_field = 100
# Notice that alter is used twice
alter table user alter new_field set default 100;
# remove the default value of user's new_field field
alter table user alter new_field drop default;
Copy the code- Change the name of the data table
Let's create a table
create table new_table(uid int);
Alter table new_table2 alter table new_table2
alter table new_table rename to new_table2;
Copy the code
The index
Mysql > select * from user; mysql > select * from user; mysql > select * from user;
- The primary key index
A primary key index is a special unique index that does not allow NULL values. You can specify a primary key index when creating a table. A table can have only one primary key.
Create table demo_table;
Select PRIMARY KEY from PRIMARY KEY
# specify unique index by unique
# Index can be specified as a normal index
create table demo_table(
id int not null primary key,
username varchar(64) unique
) engine=innodb;
Copy the code
- The only index
The column value corresponding to a unique index must be unique, but can be NULL, or if it is a combined index, the combination of column values must be unique.
Alter table name add unique; Add a unique index. Alter table table_name add unique (1, 2); Create a unique composite index.
- Normal index
A normal index is a basic index with no restrictions. Alter table name add index index name (column name); Alter table add index Index name (column 1, column 2);
- The full text indexing
Add text_field1 to demo_table2
alter table demo_table2 add text_field1 text;
Add full-text index to text_field1 field of table demo_table2
alter table demo_table2 add fulltext(text_field1);
Copy the code- Remove the index
Once an index is created, it cannot be modified. If an index is to be modified, it must be deleted and rebuilt.
Drop index Index name on Table name;Copy the codeNote: An index is a data structure that can speed up retrieval, but takes up extra disk tools and slows down writes. Therefore, do not over-index.
MySQL index optimization Author: Liang Yueqi
export
- Export data for a table
Export all data from books table to./books.sql file
select * from books into outfile '/tmp/books.sql';
Copy the codeAfter the command is executed, you may encounter the following errors and cannot export the command:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Copy the code
This error indicates that the secure-file-priv setting of the database does not allow data to be imported or exported. Let’s look at the secure-file-priv configuration of our database:
# terminal running
show variables like '%secure%';
Copy the code
Secure-file-priv if the value is NULL, import and export are not allowed. If there is no specific value, import and export are not restricted. If there is a specific value, import and export are allowed only in the specified path.
You can refer to this article for solutions
SQL file is generated in/TMP directory. The file contains the following contents:
Wu Chengen 3 Romance of The Three Kingdoms 123 Luo Guanzhong 4 Outlaws of the Marsh 123 Shi Naian 5 DREAM of the Red Chamber 123 Cao Xueqin 7 Simple and Profound Node.js 123 Piao Ling 8 Simple and profound Vue. Js 123 Liu Bowen 9 Js2 123 Liu Bowen 10 Liu bowen 10 Liu Bowen 11 Liu Bowen. Js3 123 Liu BowenCopy the code- Export the entire database
Note that you are exporting tables and data for all tables in the entire database. Exported table structures can be imported elsewhere to create directly tables and data.
Export the format of all tables in the specified database to express-blog.sql
# direct terminal input, no need to log in mysql database firstMysqldump -u root -p SQL > express-blog.sqlCopy the codeThis is followed by a request for the database password, and when it is entered, you can see that an express-blog.sql file is exported with the following contents:
-- MySQL dump 10.13 Distrib 8.0.16, for macos10.14 (x86_64)
--
-- Host: localhost Database: express-blog
-- ------------------------------------------------------
- Server version 8.0.16
/ *! 40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/ *! 40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/ *! 40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4 ;
/ *! 40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/ *! 40103 SET TIME_ZONE='+00:00' */;
/ *! 40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/ *! 40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/ *! 40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/ *! 40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `books`
--
DROP TABLE IF EXISTS `books`;
/ *! 40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `books` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`bookname` varchar(100) NOT NULL,
`type` varchar(50) NOT NULL,
`author` varchar(50) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/ *! 40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `books`
--
LOCK TABLES `books` WRITE;
/ *! 40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (1.Das Kapital.'123'.'make'), (2.Journey to the West.'123'.'Wu Cheng 'en'), (3.Romance of The Three Kingdoms.'123'.'Luo Guanzhong'), (4.'Water Margin'.'123'.'Nai am'), (5.A Dream of Red Mansions.'123'.'Cao Xueqin'), (7.'Node.js'.'123'.'PiaoLing'), (8.'Vue.js'.'123'.'Bowen Liu'), (9.Vue.js2'.'123'.'Bowen Liu'), (10.'Simple things come out.'.'123'.'Bowen Liu'), (11.'The simplest part is the most obvious.'.'123'.'Bowen Liu');
/ *! 40000 ALTER TABLE `books` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `level`
--
DROP TABLE IF EXISTS `level`;
/ *! 40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `level` (
`name` varchar(255) DEFAULT NULL,
`level` int(64) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/ *! 40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `level`
--
LOCK TABLES `level` WRITE;
/ *! 40000 ALTER TABLE `level` DISABLE KEYS */;
INSERT INTO `level` VALUES ('bronze'.0.'Stubborn Bronze Grade'), ('silver'.1.'Silver grade'), ('gold'.2.'Shining Gold Grade'), ('platinum'.3.'Platinum Grade'), ('diamond'.4.'Brilliant Diamond Grade'), ('master'.5.'Extraordinary Master Level'), ('the king'.6.'Supreme King Rank'), ('the king'.7.'Sparkle level'), ('the king'.8.'Sparkle level');
/ *! 40000 ALTER TABLE `level` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/ *! 40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `user` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author` varchar(100) NOT NULL,
`level` int(10) unsigned DEFAULT NULL.PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/ *! 40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/ *! 40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1.'make'.1), (2.'berg'.3), (3.'emei'.11);
/ *! 40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/ *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/ *! 40101 SET SQL_MODE=@OLD_SQL_MODE */;
/ *! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/ *! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/ *! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/ *! 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/ *! 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/ *! 40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-04-08 23:00:40
Copy the codeAs you can see from the file contents, the database actually exports a series of commands for creating tables and inserting data.
- Exports the specified table structure
Mysqldump -u root -p data name Data table name >./xx.sqlCopy the codeThe import
- Import the exported data into the data table
# import data from '/ TMP /books.sql' into books table
LOAD DATA INFILE '/tmp/books.sql' INTO TABLE books;
Copy the code- Import the exported database (or table)
Create table and table data in express-blog database from export./books. SQL file
mysql -u root -p express-blog < ./books.sql
Copy the codeconclusion
Proficient in the use of SQL, it must be a front end partner cut into the back-end technology stack around the past a bend, I hope that through the study of this article can be the whole stack on the road to wish friends a helping hand. I am your old friend Leng Hammer, welcome to like the collection ❤️❤️