First, understand the MySQL database

1. What is a database

A Database is a warehouse that organizes, stores, and manages data according to a data structure

Professional database is specialized in data creation, access, management, search and other operations of the software, compared with our own file read and write method object data management more convenient, fast, safe

2, the role of

Data is persisted
Convenient data storage and query, fast, safe and convenient
Can handle concurrent access
More secure permission management access mechanism

3. Common databases

Database is divided into two categories, one is relational database. The other is called a non-relational database.

Relational databases: MySQL (acquired by Oracle), Oracle, PostgreSQL, SQLserver…

Non-relational database: Redis in-memory database, MongoDB document database…

4, MySQL overview

Database:

table

5, MySQL basic operation

Enter MySQL and switch to the database

See the table

View table structure

Win10 install MySQL and graphics tools

1. Install MySQL

Windows installation MySQL5.7.17

1) in MySQL website dev.mysql.com/downloads/m… Download the ZIP installation package above (second: Windows (x86, 64-bit), ZIP Archive).

2) After downloading, unpack it and put it in the directory you want to install. For example: D: \ MySQL5.7 \ mysql – 5.7.17 – winx64

3) Create a new my.ini configuration file. The original my-default.ini configuration file is just a template.

The contents of my.ini are as follows: The basedir and datadir values need to be set according to the MySQL directory.

[mysql] default-character-set=utf8 [mysqld] port = 3306 basedir=D:\MySQL5.7\mysql-5.7.17-winx64 Datadir = D: \ MySQL5.7 \ mysql - 5.7.17 - winx64 \ data max_connections = 200 character - set - server = utf8 default - storage - engine = INNODB  explicit_defaults_for_timestamp=trueCopy the code

4) Create an empty data folder in the installation path.

5) Run CMD as administrator, go to bin, and run mysqld –initialize-insecure –user=mysql. Without this step, the service cannot be started after the installation is complete.

6) In the bin directory of the administrator window, run mysqld install. After the installation is complete, a message is displayed indicating that the installation succeeded.

7) In the bin directory of the administrator window, run the net start mysql command to start the mysql service.

8) Modify the environment variable, add “D:\MySQL5.7\mysql-5.7.17-winx64\bin”. (So you can use MySQL in any directory)

9) Go to the bin directory and run the mysql -u root -p command. There is no password by default. Press Enter.

2. Install navicate

Install MySQL database on Mac system

Method 1: Download the installation package from the official website

Method 2: Use Homebrew on Mac to install mysql

1. Download and install mysql

The page is displayed after the installation is successful

2. Restart mysql

Change the source IP address of a domestic mirror

Uninstall MySQL

MySQL basic operation commands

1. Brief introduction

Usage:

Mode 1: Operate by typing commands on the command line (helps command mastery)
Method 2: Use graphical interface tools, such as Navicat, etc. (use it after you have mastered it)
Method 3: Through programming languages (Python, PHP, Java,go…) Run the mysql command

Structure Query Language (SQL

SQL language is divided into four parts: DDL (definition), DML (operation), DQL (query), DCL (control)

Shortcut keys in SQL statements

\G Formatted output (text, vertical display)
\s Displays server information
\c Terminates the command input operation
\q Exits the current SQL command line mode
\h View help

2. Perform database operations

1) Connect to MySQL through the command line

SQL statements can be newlines and must end with a semicolon

3) Commands are case-insensitive. Keywords and functions are recommended in uppercase

4) If the prompt is ‘> then type a ‘enter

5) The command cannot be modified after a wrong line break, but can be cancelled with \c

3. Database operation

Check the database show databases;

Create database default charset=utf8;

Drop database name; drop database name;

Open database use database name;

4. Data table operation

In a database management system, there can be many libraries, and each database can contain multiple tables

To view tables: show tables;

Engine =innodb default charset=utf8;

Create a table: if the table does not exist, create it; if it does, skip this command

Create table if not exists The name of the table (field 1 type, field 2 type);

create table if not exists users( id int not null primary key auto_increment, name varchar(4) not null, age tinyint, Sex enum(' male ',' female ') innodb default charset=utf8;Copy the code

Drop table name; drop table name;

Table name desc;

Show create table users;

5. Add, delete, check and modify data operations

insert

Insert into values(1, 2, 3); insert into values(1, 2, 3);
Insert into values(a = 1, A = 2, A = 3),(b = 1,b = 2,b = 3); insert into values(a = 1, A = 2, A = 3);

The query

Select * from table_name;
Select * from table_name where table_name = 1; select * from table_name where table_name = 1;
Select * from table_name where table_name = 1;

Modify the

Select * from table_name where table_name = 1;
Select * from table_name where table_name = 1 and table_name = 1;
Select * from table_name where table_name = 1; select * from table_name where table_name = 1;

delete

Delete from table_name where table_name = 1;

Data types in MySQL

Data types are the basic rules that define what type of data can be stored in a column and how that data is actually stored

Data types restrict data stored in data column columns. For example, a numeric data type column can only accept data of numeric type

When designing tables, special attention should be paid to the data types used. Using the wrong data type can seriously affect the functionality and performance of your application.

Changing a column that contains data is not a trivial matter (and doing so can result in data loss).

Data types: integer, floating point, string, date, etc

1. String data type

The most common data type is the string data type. They store strings, such as names, addresses, phone numbers, zip codes, and so on.

No matter what form of string data type is used, string values must be enclosed in quotes

There are two basic string types, fixed length and variable length

1.1 Fixed length string: char

Accepts a string of a fixed length that is specified when the table is created. Fixed-length columns are not allowed to store more than characters of specified length.

When the length is specified, a fixed amount of storage space is allocated for storing data

Char (7) takes up 7 character positions regardless of how many characters are actually inserted

1.2 Variable length string: VARCHAR

Store variable length string varchar(7). If 4 characters are actually inserted, it takes only 4 character positions, but of course the inserted data cannot be longer than 7 characters

If variable-length data types are so flexible, why use fixed-length data types?

Answer: MySQL processes fixed-length columns much faster than variable-length columns because of performance.

1.3 Text Variable length Text type storage

2. Numeric types

Numeric data types store numeric values. MySQL supports a variety of numeric data types, and each stored value has a different range of values. The larger the supported value range is, the more storage space is required

Unlike strings, numeric values should not be enclosed in quotes

Decimal (5, 2) represents a total of five digits, with two decimal digits

Tinyint 1 byte (8 bits) unsigned 0-255. A signed – 128-127

Int 4 bytes. Zero minus 4.2 billion, minus 2.1 billion, 2.1 billion.

There is no data type in MySQL for storing money; DECIMAL(8, 2) is generally used

Unsigned and signed numbers

All numeric data types (except BIT and BOOLEAN) can be signed or unsigned

Signed value columns can store positive or negative values

Unsigned numeric columns can only store positive numbers.

The default is signed, but if you know you don’t need to store negative values, use the UNSIGNED keyword

3. Date and time types

MySQL uses specialized data types to store date and time values

4. Binary data types (not commonly used)

Binary data types can store any data (even binary information), such as images, multimedia, word processing documents, and so on

Type constraints and operations in MySQL

1. Field constraints

1) Unsigned (used for a numeric type, represented as a positive number, either positive or negative if not written)

2) Limit the width by parentheses after the field type

Char (5).varchar (7) Specifies the length of the string followed by a constraint on the character type
Int (4) makes no sense, default unsigned int is int(11), signed int(10)
Setting the width of an int makes sense only if the int has a leading zero.

3) Not NULL Cannot be null. If the data entered in this field is NULL during database operation, an error will be reported

4) default Set the default value

5) The primary key cannot be empty or unique. Generally used in conjunction with auto increment.

6) Auto_increment defines an autoincrement attribute for a primary key

The unique index can increase query speed, but slow down insert and update speed

2, the primary key

1) Each row in the table should have a column that uniquely identifies itself. It is used to record that two records cannot be duplicated. No two rows have the same primary key value

2) You should always define a primary key Although you don’t always need a primary key, most database designers should ensure that each table they create has a primary key for future data manipulation and management.

requirements

Once the record is inserted into the table, it is best not to modify the primary key
Do not allow NULL
Do not use values that may change in primary key columns. (For example, if a name is used as a primary key to identify a vendor, this primary key must be changed when the vendor merges and changes its name.)
Auto-increment integer type: The database automatically assigns an auto-increment integer to each record when inserting data, so we don’t have to worry about primary key duplication or pre-generating primary keys ourselves
Multiple columns can be used as the union primary key, but the union primary key is not commonly used. When multiple columns are used as the primary key, the combination of all column values must be unique

MySQL operator

Arithmetic operators: +, -, *, /, %
Comparison operators: =, >, <, >=, <=,! =
In, not in, is NULL, is not NULL, like, between, and
Logical operators: and, OR, not
Like: supports special symbols % and _.

Where % represents any number of any characters and _ represents any character

MySQL > select * from ‘MySQL’

1. Database operation

1.1 Creating a Database

Mysql > select * from 'mysql'; Create database if not exists tlXY default charset=utf8; Create database if tlxy does not exist, create database if tlxy does exist. Create tlXY database and set character set to UTF8 # 3. No special case requires utF8 or UTF8MB4Copy the code

1.2 Viewing All Libraries

# 1. Check databases. Show databases;Copy the code

1.3 Open library/Enter Library/Select library

Use the library name use tlxyCopy the code

1.4 delete library

Deleting databases is risky, so be careful.

All data in the library will be deleted from disk. The drop database library nameCopy the code

2. Data table operation

2.1 create a table

Syntax format:

Create table name (column name, type,…) ;

Example:

Mysql > create table users; create table users; create table users; Id int unsigned not null primary key auto_increment, # int unsigned not null primary key auto_increment, Username varchar(5) not null, # create username varchar(5) not null Password char(32) not null, # create age field, cannot be null, # create age field, The default value is 20. Age tinyInt not null default 20)engine= Innodb default charset=utf8; Alter table desc users; Show create table users;Copy the code

View table structure

The same database can be recreated by displaying table building phrases, copying, and running

Basic principles for creating tables:

Table and field names should conform to the naming convention as much as possible, and should be “by name”
The data in the table must have a unique identifier, a primary key definition. In no special case, the primary key is numeric and increments
The types of fields in the table are set appropriately and the length is limited
Innodb is recommended for table engine, there is no special case for utF8 or UTF8MB4 character encoding

2.2 Modifying the table structure

Alter table table name action (change option)

Add fields

Alter table users add num int not null; Alter table users add email varchar(50) null string alter table users add email varchar(50)  after age; Alter table users add phone char(11) not null after age; alter table users add phone char(11) not null after age; Alter table users add AA int first;Copy the code

Delete the field

Alter table users drop AA;Copy the code

Modify the fields

Syntax format: the alter table table name change | the modify the modified field information

“Change” : the field name can be changed.
Modify: The field name cannot be changed.

To modify the num field type in a table, run the modify command

alter table users modify num tinyint not null default 12;

Alter table num field int and field name nn

alter table users change num nn int;

Example: Changing the field type

After the field type is modified

Note: In general, do not change the table structure without special requirements

2.3 Changing table names

Alter table original table name rename as new table name

2.4 Change the value incremented in the table

Alter table users AUTO_increment = 1000; alter table users auto_increment = 1000;Copy the code

2.5 Modifying the Table Engine

Innodb is recommended when defining tables. Mysql > show create table users\G; *************************** 1. row *************************** Table: users Create Table: CREATE TABLE 'users' (PRIMARY KEY (' id')) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET= UTf8 1 row in set (0.00) Mysql > show table status from tlxy where name = 'users'\G; *************************** 1. row *************************** Name: users Engine: Alter table users engine = 'myisam';Copy the code

2.6 delete table

Drop table table name

MySQL database table engine

1. The server handles client requests

No matter which way the client and server processes communicate, the result is:

The client process sends a piece of text (MySQL statement) to the server process, which then sends a piece of text (processing result) to the client process.

What does the server process do with the request sent by the client process to produce the final result?

The client can send all kinds of requests to the server to add, delete, modify and check. Here we take the complicated query request as an example to draw a picture to show the general process:

Although the query cache can sometimes improve the system performance, it also has to cause some overhead due to the maintenance of the cache. For example, the query cache has to be retrieved every time. After the query request is processed, the query cache needs to be updated and the memory region corresponding to the query cache is maintained.

As of MySQL 5.7.20, query caching is not recommended and was removed in MySQL 8.0.

Storage engine

The MySQL server encapsulates the data storage and extraction operations into a module called the storage engine.

We know that a table is made up of rows of records, but this is only a logical concept. The storage engine is responsible for how records are represented physically, how data is read from the table, and how data is written to specific physical storage.
To achieve different functions, MySQL provides a variety of storage engines. The specific storage structure of tables managed by different storage engines may be different, and the access algorithm adopted may also be different.
A storage engine, formerly known as a table processor, receives instructions from the top and then extracts or writes data from the table.

For the convenience of management, connection management, query caching, syntax parsing, query optimization and other functions that do not involve real data storage are divided into MySQL Server functions, and real data access functions are divided into storage engine functions.

The various storage engines provide a unified calling interface (also known as the storage engine API) to the upper MySQL Server layer, containing dozens of low-level functions such as “read index first”, “read index next”, “insert record” and so on.

Therefore, after MySQL Server completes query optimization, it only needs to call the API provided by the underlying storage engine according to the generated execution plan and return the data to the client.

MySQL supports a wide variety of storage engines:

The storage engine

describe

3, The difference between MyISAM and InnoDB table engine

MyISAM

InnoDB

Transaction support

Transactions are not supported;
Support;

Storage structure

Each MyISAM is stored on disk as three files (each table contains the following three files);
.FRM file storage table structure;
.myd files store data;
MYI File storage index;
It is mainly divided into two kinds of files for storage;
.FRM storage table structure;
Ibd stores data and indexes (possibly multiple. Ibd files, or separate tablespace files);

Table locks differences

Only table-level locks are supported.
Select, UPDATE, DELETE, insert from myISAM; insert into myISam; insert into myISam;
Innodb supports transaction and row-level locking.
Row locking dramatically improves the capability of multi-user concurrent operations. InnoDB’s row lock is only valid when the primary key of a PLACE is used. If the primary key is not used, the row lock will be upgraded to a table lock.

Table primary key

Allow tables without any indexes or primary keys, indexes are the addresses of rows;
If no primary key or non-empty unique index is set, a 6-byte primary key is automatically generated (invisible to the user). The data is part of the primary index, and the additional index holds the value of the primary index.
InnoDB has a larger primary key range, up to twice that of MyISAM;

The number of rows in the table

Select count() from table; It will just pull out the value;
Select count() from table; select count() from table; It will traverse the entire table, which is quite expensive, but with the wehre condition, MyISam and InnoDB handle it the same way;

CURD operation

If you perform a large number of SELECT, MyISAM is a better choice;
If your data performs a lot of INSERTS or updates, InnoDB tables should be used for performance reasons.
When InnoDB deletes FROM table, InnoDB does not re-create tables but deletes them row by row. If InnoDB wants to DELETE tables with a large amount of data, it is better to use truncate table command.

A foreign key

Does not support
support

The query efficiency

It is relatively simple, so it is better than InnoDB in efficiency. Small applications can consider using MyISAM.
I recommend using InnoDB instead of MyISAM because InnoDB has many nice features such as transaction support, stored procedures, visuals, row-level locking, etc.
In the case of a lot of concurrency, I believe InnoDB will perform much better than MyISAM;

Application scenarios

Manage non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities;
If your application needs to perform a large number of SELECT queries, MyISAM is a better choice.
For transaction processing applications, with numerous features, including ACID transaction support.
If your application needs to perform a lot of INSERT or UPDATE operations, use InnoDB to improve the performance of multi-user concurrent operations. InnoDB is now used by default;

MySQL character set UTf8

1. Introduction to character set

We know that only binary data can be stored on a computer, so how do you store strings? To create a mapping between characters and binary data, you need to know at least two things:

1. Which characters do you map to binary data?

That is, define the character range clearly.

2. How to map?

The process of mapping a character to a binary is also called encoding, and the process of mapping a binary to a character is called decoding.

People abstract the concept of a character set to describe the encoding rules for a range of characters

Let’s take a look at some common character sets:

The ASCII character set

Contains 128 characters, including Spaces, punctuation marks, digits, upper – and lower-case letters, and some invisible characters. Since there are only 128 characters in total, it is possible to encode 1 byte. Let’s look at some character encodings:

‘L’ -> 01001100 (hex: 0x4C, decimal: 76)

‘M’ -> 01001101 (hex: 0x4D, decimal: 77)

ISO 8859-1 character set

A total of 256 characters, is in the ASCII character set on the basis of the expansion of 128 common western European characters (including German and French letters), can also use 1 byte to encode. This character set also has an alias latin1

The GB2312 character set

It contains Chinese characters, Latin alphabet, Greek alphabet, Japanese hiragana and Katakana alphabet, Russian Cyrillic alphabet. Among them, 6,763 Chinese characters and 682 other characters are included. This character set is also compatible with the ASCII character set, so it is somewhat strange in terms of encoding:

If the character is in the ASCII character set, the 1-byte encoding is used.

Otherwise, 2-byte encoding is used.

This encoding to indicate that the number of bytes required for a character may differ is called variable-length encoding. For example, the string ‘love U’, in which ‘love’ needs to be encoded with 2 bytes in hexadecimal representation 0xCED2, ‘u’ needs to be encoded with 1 byte in hexadecimal representation 0x75, so it is combined to 0xCED275.

Tip: How can we tell whether a byte represents a single character or part of a character? Remember that the ASCII character set contains only 128 characters, using 0 to 127 to represent all characters, so if a byte is within 0 to 127, it means that one byte represents a single character, otherwise two bytes represent a single character.

GBK character set

The GBK character set is only an extension of the GB2312 character set in the range of included characters, and compatible with GB2312 encoding mode.

The Unicode character set

Every character imaginable on Earth, and it keeps growing. This character set is compatible with the ASCII character set and is variable-length encoding, requiring 1 to 4 bytes to encode a character, for example

‘L’ -> 01001100 (hex: 0x4C)

‘Ah’ -> 11100101100101010110001010 (hexadecimal: 0xE5958A)

Tip: Utf8 uses 1 to 4 bytes to encode a character, utF16 uses 2 to 4 bytes to encode a character, and UTF16 uses 2 to 4 bytes to encode a character. Utf32 uses four bytes to encode a character. MySQL does not distinguish between character sets and encoding schemes. Utf8, UTF16, and UTF32 are all treated as character sets.

2, MySQL utf8 and UTf8mb4

We said that the UTF8 character set means that a character needs to use 1 to 4 bytes, but some of the characters we commonly use can use 1 to 3 bytes. In MySQL, the character set represents the maximum length of a character, which in some ways affects the storage and performance of the system. Therefore, the uncle who designed MySQL secretly defined two concepts:

Utf8mb3: Neutered UTF8 character set that uses only 1 to 3 bytes to represent characters.
Utf8mb4: The authentic UTF8 character set, which uses 1 to 4 bytes to represent characters.

Note that utF8 is an alias for UTF8MB3 in MySQL, so using UTF8 in MySQL means using 1 to 3 bytes to represent a character. If you use 4 bytes to encode a character, such as storing some emojis, Please use UTF8MB4.

3, character set view

MySQL supports many different character sets. To check the character set supported by MySQL, use the following statement:

show charset;
Copy the code

DML operations in MySQL – add, delete and modify data

1. Add data

Insert into table name [(insert into table name)] values ;

Mysql > insert into STu (id,name,age,sex, Classid) values(1,'zhangsan',20,'m','lamp138') Query OK, Mysql > insert into STu (name,classid) value('lisi','lamp138'); Query OK, 1 row affected (0.11 SEC) mysql> insert into stu value(null,'wangwu',21,'w','lamp138'); Mysql > insert into STu values -> (null,'zhaoliu',25,'w','lamp94') -> (null,'uu01',26,'m','lamp94'), -> (null,'uu02',28,'w','lamp92'), -> (null,'qq02',24,'m','lamp92'), -> (null,'uu03',32,'m','lamp138'), -> (null,'qq03',23,'w','lamp94'), -> (null,'aa',19,'m','lamp138'); Query OK, 7 Rows Affected (0.27 SEC) Records: 7 Duplicates: 0 Warnings: 0Copy the code

2. Modify data

Update table_name set table_name = table_name, table_name = table_name, table_name = table_name, table_name = table_name, table_name = table_name, table_name = table_name, table_name = table_name Where conditions

Mysql > update stu set age=35,sex='m' where id=11; Query OK, 1 row affected (0.16 SEC) Rows matched: 1 Changed: 1 Warnings: 0 -- change sex from id 12 to id 14 to m, Lamp92 mysql> update stu set sex='m',classid='lamp92' where ID =12 or ID =14 = 'm' sex, every 'lamp92' where id in (12, 14); Query OK, 2 rows affected (0.09 SEC) Rows matched: 2 Changed: 2 Warnings: 0Copy the code

3. Delete data

Delete from table name [where condition]

Mysql > delete from stu where id=100; Query OK, 0 rows affected (0.00 SEC) mysql> delete from STu where ID >=20 and ID <=30; Mysql > delete from stu where id between 20 and 30; Query OK, 0 rows affected (0.00 SEC) mysql> delete from stu where ID between 20 and 30; Query OK, 0 rows affected (0.00 SEC) mysql> delete from stu where ID >200; Query OK, 0 rows affected (0.00 SEC)Copy the code

MySQL > select * from ‘MySQL’

Create a representation using a graphical interface

1. Select the database

2. Add fields, set primary keys, properties, and whether to automatically increment

3, modify the database engine, character set, collation rules

4. Insert data

5. View the SQL statements corresponding to the operation of creating a table and inserting data

6. View the corresponding SQL statement

Create a representation using database statements

/* Navicat Premium Data Transfer Source Server : localhost_3306 Source Server Type : MySQL Source Server Version : 80017 Source Host : localhost:3306 Source Schema : test Target Server Type : MySQL Target Server Version : 80017 File Encoding : 65001 Date: 14/01/2021 11:14:36 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for stu -- ---------------------------- DROP TABLE IF EXISTS `stu`; CREATE TABLE `stu` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `email` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `phone` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` tinyint(4) NOT NULL, `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `class_id` int(10) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci  ROW_FORMAT = Dynamic; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - Records of stu -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- INSERT INTO ` stu ` VALUES (1, 'zhang SAN, '[email protected]', '13781104321', 21, 'male ', 1); INSERT INTO 'stu' VALUES (null, '[email protected]', '13701104322', 22, '男', 1); INSERT INTO 'stu' VALUES (null, '[email protected]', '13701104323', 20, 'w ', 1); INSERT INTO 'stu' VALUES (null, 'stu ', '[email protected]', '13701104324', 19, '男', 1); INSERT INTO 'stu' VALUES (null, '[email protected]', '13701104325', 23, 'w ', 1); INSERT INTO 'stu' VALUES (null, '[email protected]', INSERT INTO 'stu' VALUES (null, '[email protected]', '13701104327', 25, '男', 2); INSERT INTO 'stu' VALUES (null, 'xiongereqq.com', 'xiongereqq.com', 2); INSERT INTO 'stu' VALUES (null, '-- ', 'yiyi@qq. Com ', '13701104329', 2); INSERT INTO 'stu' VALUES (null, 'proud ', '[email protected]', '13701104320', 2); SET FOREIGN_KEY_CHECKS = 1;Copy the code

Note: You can normally create tables and insert data by running SQL files.

When inserting a character from the command line, an error will be reported

ERROR 1366 (HY000): Incorrect String value: ‘\xD5\xC5\xD0\xA1\xC3\xF7’ for column ‘NAME’ at row 1解决】

1, retrieve data select

1.1 Retrieve a single column

select name from stu;
Copy the code

If you do not explicitly sort the query results (described in the next chapter), the order of the data returned has no special meaning.
The returned data may or may not be in the order in which it was added to the table. As long as the same number of rows are returned, this is normal

1.2 Retrieve multiple columns

select name,id,phone from stu;
Copy the code

When selecting multiple columns, always place commas between column names, but not after the last column name.
If you add a comma after the last column name, an error occurs.

1.3 Retrieve all columns

select * from stu;
Copy the code

In general, it is best not to use * wildcards unless you really need every column in a table.
While using wildcards may save you the trouble of explicitly listing the required columns, retrieving unwanted columns often degrades retrieval and application performance.
There is one big advantage to using wildcards. Because column names are not explicitly specified (because an asterisk retrieves every column), columns with unknown names can be retrieved.

1.4 Retrieve how many DISTINCT rows there are

select distinct class_id from stu;
Copy the code

DISTINCT keyword. As the name implies, this keyword indicates that MySQL only returns different values (which can be used to de-duplicate)
The DISTINCT keyword applies to all columns, not just the column that precedes it.
If SELECT DISTINCT vend_id, prod_price is given, all rows will be retrieved unless both columns specified are different

1.5 LIMIT results LIMIT

Select * from stu limit 3,4;Copy the code

If the amount of data reaches tens of millions, a select * will probably bring down the server, so you need to use limit to control the amount of data retrieved at one time.
LIMIT 3, 4 means 4 rows from line 3 (skip the first 3 rows and take 4 rows)
The alternative syntax LIMIT 4 OFFSET 3 means to take 4 rows from line 3, just like LIMIT 3,4.

1.6 Use fully qualified table names

Mainly used for multi-table query, limit the field from which table to take out the data;

2, Sort the retrieved data ORDER BY

2.1 Sorting data ORDER BY

By default, the queried data is not sorted randomly. If sorting is not specified, the data will be displayed in the order in which it appears in the underlying table

Relational database design theory states that if the sorting order is not clearly specified, the order of retrieved data should not be assumed to be meaningful

In general, the columns used in the ORDER BY clause will be the columns selected for display. (Meaning that in most cases, you only need to sort the columns that will eventually be displayed)

However, this does not have to be the case; it is perfectly legal to sort data with non-retrieved columns.

2.2 Sort by multiple columns

When the selected field is repeated, you can choose to refer to multiple fields for sorting again.

select * from stu order by classid, age;
Copy the code

When multiple columns need to be sorted, the column names are separated by commas and sorted in order
The order by default is ascending. You can use DESC to set descending order

select * from stu order by classid, age DESC;

Sort lassid in ascending order and age in descending order

2.3 pay attention to

The position of the ORDER BY clause, using the clauses in the wrong ORDER will generate an error message;

The FROM clause precedes the ORDER BY clause.
LIMIT must be placed after ORDER BY.

3. Data retrieval criteria filter Where

3.1 the WHERE usage

Database tables typically contain a large amount of data, and it is rarely necessary to retrieve all rows in the table. Typically, only a subset of the table data is extracted based on the needs of a particular operation or report.

Search criteria, also known as filter conditions, must be specified to retrieve only the required data.

In the SELECT statement, the data is filtered based on the search criteria specified in the WHERE clause.

select name from stu where age = 22;
Copy the code

The ORDER BY statement should be placed after the WHERE statement

3.2 WHERE clause operator

Delimiting boundaries (between includes boundary values)

Use is null

3.3 AND AND OR operators

For example: I need to look up 1st or 2nd class students in the database and they should be female

select name from stu where class_id=18 or class_id =19 and sex='m'
Copy the code

The above statement does not retrieve the correct data as expected. What is the problem?

The reason lies in the order of the calculations. SQL processes the AND operator before processing the OR operator.

When SQL sees the above WHERE clause, it understands that it is all the girls in the class of Phase 19, or all the students in the class of phase 18, regardless of gender. In other words, because AND has higher precedence in the computation order, the operators are incorrectly combined

So the solution is to raise the priority, using parentheses to explicitly group the corresponding operators

select name from stu where (class_id=18 or class_id =19) and sex= 'm'
Copy the code

3.4 with the NOT IN

The IN operator is used to specify a range of conditions IN which each condition can be matched.

Select name from stu where class_id in (1,2)Copy the code

The keyword used IN the IN WHERE clause to specify a list of values to match, which is equivalent to OR

Why use the IN operator? Its advantages are as follows.

The syntax of the IN operator is clearer and more intuitive when using a long list of legal options.
When IN is used, the order of calculations is easier to manage (because fewer operators are used).
The IN operator is generally faster than the OR operator list.
The biggest advantage of IN is that it can include other SELECT statements, making it possible to build WHERE clauses more dynamically.

The keyword used to negate the followed condition in the NOT WHERE clause

Select name from stu where class_id not in (18,19)Copy the code

Why NOT? For simple WHERE clauses, there is really no advantage to using NOT.
But in more complex clauses, NOT is very useful.
For example, when used IN conjunction with the IN operator, NOT makes it easy to find lines that do NOT match a list of conditions.

4. Like and wildcard characters

All of the operators described above filter against known values. However, this filtering method does not work all the time.

For example, how do you search for all products that have the text anvil in their name? Using a simple comparison operator will not work; you must use wildcards.

To use wildcards in search clauses, you must use the LIKE operator.

LIKE instructs MySQL, followed by the search pattern, to use wildcard matching instead of direct equality matching for comparison.

4.1 Percent sign (%)

Wildcard In the search string, % represents any number of occurrences of any character

select name from stu where name like ‘a%’
select name from stu where name like ‘%a’
select nane from stu where name like ‘%a%’

4.2 Underscore (_)

The wildcard underscore serves the same purpose as %, but underscores only match single characters, not multiple characters

The technique of using wildcards

MySQL wildcards are useful.

But this functionality comes at a cost: Wildcard searches generally take longer to process than the other searches discussed earlier.

Here are some tips to keep in mind when using wildcards

Don’t overuse wildcards. Other operators should be used if they serve the same purpose.
When you do need to use wildcards, don’t use them at the beginning of a search pattern unless absolutely necessary. Placing wildcards at the beginning of a search pattern is the slowest.
Pay close attention to the wildcard positions. If misplaced, the desired data may not be returned

4.3 Learning about MySql REGEXP

Regular expressions are supported by all kinds of programming languages, text editors, operating systems, and so on

select name from stu where name regexp '[0-5]abc'
Copy the code

5. Field calculation

5.1 introduction

Data stored in database tables is generally not in the format required by the application. Here are a few examples

If you want to display both the user name and the class number in one field, the two information is typically contained in separate table columns.
While the name, phone number, and address are stored in separate columns (as they should be), the Courier printer needs to retrieve them as a properly formatted field.
Column data is case – mixed, but the reporting program needs to represent all data in uppercase.
The item order table stores the price and quantity of items, but does not need to store the total price of each item (price multiplied by quantity). The total price of the item is required to print the invoice.
You need to make totals, averages, or other calculations based on table data

Computed fields do not actually exist in database tables. Computed fields are created at run time within the SELECT statement

5.2 Field Concat

The STU table contains user names and mobile phone numbers. If you want to generate a student report, you need to list the student’s name in a format like name(phone).

The solution is to concatenate the two columns. In MySQL SELECT statements, you can use the Concat() function to concatenate two columns

select concat(name, '(', phone')') from stu
Copy the code

5.3 Using the Alias AS

SELECT statement concatenation fields can be done. But what is the name of this new computed column? It doesn’t actually have a name, it’s just a value.

This is fine if you just view the results in the SQL query tool.

However, an unnamed column cannot be used in a client application because there is no way for the client to reference it.

To solve this problem, SQL supports column aliases. An alias is an alternate name for a field or value. The alias is given with the AS keyword

select concat(name, '(', phone')') from stu as name_phone from stu
Copy the code

Database _ Database Basics

First, understand the MySQL database

1. What is a database

2, the role of

3. Common databases

4, MySQL overview

5, MySQL basic operation

Win10 install MySQL and graphics tools

1. Install MySQL

2. Install navicate

Install MySQL database on Mac system

Method 1: Download the installation package from the official website

Method 2: Use Homebrew on Mac to install mysql

Change the source IP address of a domestic mirror

Uninstall MySQL

MySQL basic operation commands

1. Brief introduction

2. Perform database operations

3. Database operation

4. Data table operation

5. Add, delete, check and modify data operations

Data types in MySQL

1. String data type

1.1 Fixed length string: char

1.2 Variable length string: VARCHAR

1.3 Text Variable length Text type storage

2. Numeric types

Unsigned and signed numbers

3. Date and time types

4. Binary data types (not commonly used)

Type constraints and operations in MySQL

1. Field constraints

2, the primary key

MySQL operator

MySQL > select * from ‘MySQL’

1. Database operation

1.1 Creating a Database

1.2 Viewing All Libraries

1.3 Open library/Enter Library/Select library

1.4 delete library

2. Data table operation

2.1 create a table

2.2 Modifying the table structure

To modify the num field type in a table, run the modify command

Alter table num field int and field name nn

2.3 Changing table names

2.4 Change the value incremented in the table

2.5 Modifying the Table Engine

2.6 delete table

MySQL database table engine

1. The server handles client requests

Storage engine

3, The difference between MyISAM and InnoDB table engine

MySQL character set UTf8

1. Introduction to character set

2, MySQL utf8 and UTf8mb4

3, character set view

DML operations in MySQL – add, delete and modify data

1. Add data

2. Modify data

3. Delete data

MySQL > select * from ‘MySQL’

Create a representation using a graphical interface

Create a representation using database statements

1, retrieve data select

1.1 Retrieve a single column

1.2 Retrieve multiple columns

1.3 Retrieve all columns

1.4 Retrieve how many DISTINCT rows there are

1.5 LIMIT results LIMIT

1.6 Use fully qualified table names

2, Sort the retrieved data ORDER BY

2.1 Sorting data ORDER BY

2.2 Sort by multiple columns

2.3 pay attention to

3. Data retrieval criteria filter Where

3.1 the WHERE usage

3.2 WHERE clause operator

3.3 AND AND OR operators

3.4 with the NOT IN