- MySQL download, install, configure:
1.1 Download Address: https://dev.mysql.com/downloads/mysql/
1.2 Unzip, Configuration File: Unzip to the C disk'web'folder.
Add the configuration file "my.ini" under the mysql-8.0.17 folder:
[client] # Setting the default character set for mysql client default-character-set=utf8 [mysqld] # Setting up port 3306 port = 3306 # Set up the installation directory of mysql basedir=C:\\web\\mysql-8.0.17 # Setting up the storage directory of mysql database data, MySQL 8 + does not need the following configuration, the system can generate itself, otherwise it may report errors. # datadir=C:\\web\\sqldata # Permissible maximum number of connections max_connections=20 # The character set used by the server defaults to the latin1 character set of 8 bits encoding character-set-server=utf8 # Default storage engine to be used when creating new tables default-storage-engine=INNODB
1.3 Database Initialization, Installation, Startup
#Switching directories cd C:\web\mysql-8.0.17\bin #Initialization of database. User name and password will be output after initialization. mysqld --initialize --console # Database Installation mysqld install #Database startup net start mysql #To solve the problem: If the root password is forgotten, reinitialization can overwrite the previous user name and password #Initialization again, error message C:\web\mysql-8.0.17\bin>mysql --initialize --console mysql: [ERROR] unknown option '--initialize'. C:\web\mysql-8.0.17\bin>mysqld --initialize --console 2019-08-06T12:38:51.917931Z 0 [System] [MY-013169] [Server] C:\web\mysql-8.0.17\bin\mysqld.exe (mysqld 8.0.17) initializing of server in progress as process 16740 2019-08-06T12:38:51.923761Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2019-08-06T12:38:51.925938Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting. 2019-08-06T12:38:51.925976Z 0 [ERROR] [MY-013236] [Server] The designated data directory C:\web\mysql-8.0.17\data\ is unusable. You can remove all files that the server added to it. 2019-08-06T12:38:51.939936Z 0 [ERROR] [MY-010119] [Server] Aborting 2019-08-06T12:38:51.940969Z 0 [System] [MY-010910] [Server] C:\web\mysql-8.0.17\bin\mysqld.exe: Shutdown complete (mysqld 8.0.17) MySQL Community Server - GPL.
1.4 Login database:
#Enter the command line in the following format mysql -h host name -u User name -p # Mysql-u root-p connects to local database #Enter password according to prompt to login # e.g. C:\web\mysql-8.0.17\bin>mysql -u root -p Enter password: *********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> #Root password reset, see blogger: https://blog.csdn.net/qq_43342301/article/details/91288891 #This practice part code command line is lost, information is incomplete, can refer to the practice of the blogger. #First, you need the administrator's command line window to close the service C:\web\mysql-8.0.17\bin>net stop mysql MySQL Services are stopping. MySQL The service has been successfully discontinued. #Do not close the first command line window, open the second administrator command line window C:\WINDOWS\System32>cd C:\web\mysql-8.0.17\bin C:\web\mysql-8.0.17\bin>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. #Refresh changes mysql> FLUSH PRIVILEGES； Query OK, 0 rows affected (0.03 sec) #Change the root user login password mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '**************8'； Query OK, 0 rows affected (0.02 sec) #root Root User Re-login to MySQL System #Start up service C:\web\mysql-8.0.17\bin>net start mysql MySQL The service is starting .. MySQL The service has been launched successfully. #Land C:\web\mysql-8.0.17\bin>mysql -u root -p Enter password: *********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
NaviCat 12.1.12 Download, Install, Crack
The version between Navicat and MySQL Server has some adaptability, and there may be an error of "1251", which may be caused by the difference of "encryption mode" between the two software. Solution Reference Forum Related Posts "Change Encryption Mode"
2.1 Download: Official website is the latest version 12.1.20, the forum can be searched to 12.1.12 download links
2.2 Installation: According to the prompt, step by step next can be done, after installation, do not open the software
2.3 Cracking: Most of the cracking tools provided in the forum are invalid. See the Double Sine blogger github link for cracking methods and tools:
https://github.com/DoubleLabyrinth/navicat-keygen
Step by step according to the blogger's example operation can be successful in cracking. Blogger Niu X -
NaviCat 12.1.12 Connect MySQL Server 8.0.17
Follow the instructions and fill in the relevant information to connect successfully.
-
Basic knowledge of database
4.1 Definition: data collection; analogy library book number, bookshelf, book partition, library branch
4.2 Relational databases:
For a comparison of relational and non-relational databases, see Blog: https://blog.csdn.net/aaronthon/article/details/81714528
The most typical data structure is tables.
Non-relational databases:
A collection of structured data storage methods, which can be documents or key-value equivalents
4.3 Table, row, column, primary key, foreign key:
table: A structured list of certain types of data.
Patterns: Information about the layout and characteristics of databases and tables.
column: A field in a table. All tables consist of one or more columns.
row: a record in a table.
primary key: A column (or group of columns) whose value uniquely identifies each row in the table.
FOREIGN KEY: FOREIGN KEY in one table points to PRIMARY KEY in another table. -
MySQL Foundation (1) - Query Statement
5.1 Import sample database:
Reference material: https://www.yiibai.com/mysql/how-to-load-sample-database-into-mysql-database-server.html
Microsoft Windows [Version 10.0.17134.915] (c) 2018 Microsoft Corporation. All rights reserved. C:\Users\arthur>cd C:\web\mysql-8.0.17\bin C:\web\mysql-8.0.17\bin>mysql -h localhost -u root -p Enter password: *********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. #Create yiibaidb database mysql> create database if not exists yiibaidb default charset utf8 collate utf8_general_ci; Query OK, 1 row affected, 2 warnings (0.03 sec) #Call the database mysql> use yiibaidb; Database changed #Import table mysql> source D:/worksp/yiibaidb.sql; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected, 1 warning (0.01 sec) Database changed Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected, 3 warnings (0.10 sec) Query OK, 1 row affected (0.03 sec) Query OK, 0 rows affected, 1 warning (0.09 sec) Query OK, 1 row affected (0.01 sec) #Note: Both sides of office are not single or double quotation marks. #It's the sign on the left side of the number 1, the query table, in the input state of English. mysql> select city,phone,country from 'offices'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''offices'' at line 1 mysql> select city,phone,country from `offices`; +---------------+------------------+-----------+ | city | phone | country | +---------------+------------------+-----------+ | San Francisco | +1 650 219 4782 | USA | | Boston | +1 215 837 0825 | USA | | NYC | +1 212 555 3000 | USA | | Paris | +33 14 723 4404 | France | | Beijing | +86 33 224 5000 | China | | Sydney | +61 2 9264 2451 | Australia | | London | +44 20 7877 2041 | UK | +---------------+------------------+-----------+ 7 rows in set (0.00 sec) mysql>
5.2 SQL and MySQL:
SQL (pronounced S-Q-L or sequel) is Structured Query Language (structure)
Abbreviation for Query Language. SQL is a special language for communicating with databases.
The fundamental difference is that they follow the basic principles.
The basic principle they follow is their main difference: open vs is conservative. The narrow, conservative storage engine of the SQL server is totally different from the extensible and open storage engine of the MySQL server. Although you can use the Sybase engine of the SQL server, MySQL can provide more options, such as MyISAM, Heap, InnoDB, and Berkeley DB. MySQL does not fully support unfamiliar keywords, so it has fewer related databases than the SQL server. At the same time, MySQL also lacks some storage functions, such as MyISAM engine support exchange function. (References: https://www.cnblogs.com/qingqing-919/p/8417773.html)
5.3 Query Statement: select and from
select :
SQL statements are made up of simple English words (keywords). Purpose: Retrieving information from one or more tables.
When retrieving multiple columns, column names are separated by commas, and the last column name is not followed by commas.
When retrieving all columns, the column name position is replaced by a wildcard asterisk (*).
Retrieval results show only unique values, distinct keywords; DISTINCT keywords act on all columns
Search result line number limit, keyword (top 5, first 5 lines) (limit 5, no more than 5 lines of data)
LIMIT 5 OFFSET 5 instructs DBMS such as MySQL to return five rows of data from line 5.
The first number is the number of rows retrieved, and the second number is where to start.
Tip: To end the SQL statement, multiple SQL statements must be separated by semicolons (;). SQL statements are case-insensitive. When dealing with SQL statements, all spaces are ignored.
5.4 filter statement where
Extract a subset of table data according to the needs of a particular operation or report. Retrieving only the required data needs to refer to
The search criteria are also called filter condition s. In the SELECT statement, the data is filtered according to the search conditions specified in the WHERE clause.
5.5 Advanced Data Filtering:
Operator 5.5.1: Combine where clauses: SQL allows multiple WHERE clauses to be given. Usage: The way of using an AND clause or an OR clause.
The IN operator is used to specify the range of conditions in which each condition can be matched. IN takes a set of valid values separated by commas and enclosed in parentheses.
The NOT operator in the WHERE clause has and has only one function, which is to negate any condition that follows.
5.5.2 wildcards
% Represents any number of occurrences of any character
Underlines serve the same purpose as% but match only a single character, not multiple characters. Underline ()
The square bracket ([]) wildcard is used to specify a character set, and it must match a character at the specified position (the position of the wildcard).
5.6 Grouping Statements
Grouping is established using the GROUP BY clause of SELECT statements
Aggregation function
Sentence Interpretation
HAVING clause: WHERE filters rows while HAVING filters groupings.
5.7 Sort statement ORDER BY
Sentence Interpretation
Positive and Inverse Order
5.8 function
Unlike almost all DBMS that equally support SQL statements (such as SELECT), each DBMS has a specific function. In fact, only a few functions are equally supported by all major DBMS. Although all types of functions can generally be used in every DBMS, the names and syntax of each function may be extremely different.
Common Text Processing Functions
Common numerical processing functions
5.9 SQL Annotations
Use ""
"-- "
/* */
5.10 SQL Code Specification
[Suggestions for optimization of SQL programming format] https://zhuanlan.zhihu.com/p/27466166
[SQL Style Guide] https://www.sqlstyle.guide/
Operational section
task1 Finds duplicate mailboxes
Create an email table and insert the following three rows of data
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
Write an SQL query to find all duplicate e-mails in the Email table.
Based on the above input, your query should return the following results:
±--------+
| Email |
±--------+
| a@b.com |
±--------+
Note: All e-mailboxes are lowercase letters.
# select email from person group by email having count(*) >= 2
task2 Find Big Countries
Create the following World table
±----------------±-----------±-----------±-------------±--------------+
| name | continent | area | population | gdp |
±----------------±-----------±-----------±-------------±--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
±----------------±-----------±-----------±-------------±--------------+
If a country has an area of more than 3 million square kilometers, or (with a population of more than 25 million and a gdp of more than 20 million), it is a big country.
Write an SQL query to output the names, population and area of all the countries in the table.
For example, according to the table above, we should output:
±-------------±------------±-------------+
| name | population | area |
±-------------±------------±-------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
±-------------±------------±-------------+
SELECT name,population,area FROM World WHERE area > 3000000 OR population > 25000000