[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

Time:2021-8-10

SQL

summary

What is SQL?

  • SQL (Structured Query Language) is a concise and easy-to-use relational database language
  • ANSI compliant computer language
  • Oracle, SQL server, MySQL and PostgreSQL all implement SQL language
  • DBASE, access and other parts implement SQL language

SQL statements embedded in HTML can access the database through www
SQL statements can be embedded in C, Java, etc

What can SQL do?

  • SQL executes queries against the database and retrieves data from the database
  • SQL can insert, update and delete data in the database
  • SQL creates a new database
  • SQL creates new tables, stored procedures, and views in the database
  • SQL can set permissions for tables, stored procedures, and views

DML and DDL of SQL

SQL is divided into two parts: Data Manipulation Language (DML) and data definition language (DDL).

  • Query and update instructionsForms the DML part of SQL

    • Select – get data from database tables
    • Update – updates the data in the database table
    • Delete – deletes data from a database table
    • Insert into – inserts data into a database table

Data definition language (DDL) can create or delete tables, define indexes (keys), specify links between tables, and impose constraints between tables.

  • The most important DDL statement in SQL

    • Create database (table) – create a new database / table
    • Alter database (table) – modify database / table
    • Drop database (table) – delete database / table
    • Create index – create index (search key)
    • Drop index – drop index

SQL syntax is not case sensitive!

MySQL

brief introduction

MySQL was developed by Swedish t.c.x company and began in 1979.

  • MySQL is an implementation of client / server architecture

    • You need a server daemon mysqld and a client
  • MySQL features

    • It supports multi-user and multi thread, and can be used on multiple platforms
    • Open source
    • High performance, fast running speed and easy to use
    • It provides a relatively simple solution for users

install

Download resources

Enter the official websiteDownload page, select your own system, required MySQL version and installation method (application installation or zip package):
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation
For personal habits, choose the zip package method.

Unzip the zip to the location you want.
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

Configure environment variables

Right click this computer ➡ Attributes ➡ Advanced system settings ➡ Environment variables:
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation
Edit the path variable in the system variable and create a new one:

MySQL bin folder path, such as mine:D:\Developer\MySQL\mysql-8.0.21-winx64\bin
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

install

Run CMD window as administrator:
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

Enter the MySQL bin directory:
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

To install MySQL:

inputmysqld -install
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation
By the way, the command to remove MySQL service is:mysqld -remove

initialization:

inputmysqld --initialize
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

After initialization, a data folder will be generated in the MySQL directory with aComputer name.errFile, where you can view the initial password, which will be used later in this step.
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

Configure MySQL startup file

Create a new my.ini file in MySQL and edit:
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

[mysql]
#Set the default character set of MySQL client
default-character-set=utf8
[mysqld]
#Set 3306 port
port=3306
#Set MySQL installation directory
basedir=D:\\Developer\\MySQL\\mysql-8.0.21-winx64
#Note that this is your own installation path. Use \ \ instead of\
datadir=D:\\Developer\\MySQL\\mysql-8.0.21-winx64\\data
#Maximum connections allowed
max_connections=200
#The character set used by the server defaults to the 8-bit encoded Latin1 character set
character-set-server=utf8
#The default storage engine that will be used when creating new tables
default-storage-engine=INNODB

Start MySQL:

Run the CMD window as an administrator and enter it in the bin directory of the installation path (you can also enter it in this directory after configuring the environment variable)net start mysql
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation
By the way, the command to stop MySQL isnet stop mysql

Another way: right click this computer ➡ Administration ➡ Services and Applications ➡ Service, find mysql, and right-click start / stop.
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

At this point, MySQL is basically installed. You need to log in later.

Sign in

By commandmysql -u root -pStart MySQL
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

Extension: in fact, the full login command is:MySQL [- H hostname - P port number] - U username - P, when connecting the local machine, of course, it can be omitted in [].

You can see that you are required to enter a password when logging in. Where does this password come from?

In the initialization step, it is mentioned that there is a data folder in the MySQL directoryComputer name.errFile, where you can view and modify the initial password.
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation


Change Password
After logging in, use the MySQL command:Alter user 'root' @'localhost 'identified by' your password ';
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

Log in to MySQL again:
[trainee] 07 / 31 SQL introduction and MySQL 8.0 installation

How to exit MySQL:exitorCtrl + c

enjoy