[CMB_Fintech_Trainee_2020] 07/31 Introduction to SQL and installation of MySQL8.0

[CMB_Fintech_Trainee_2020] 07/31 Introduction to SQL and installation of MySQL8.0

SQL

Overview

What is SQL?

  • SQL (Structured Query Language) is a concise and easy-to-use relational database language
  • Computer language conforming to ANSI standard
  • Oracle, SQL Server, MySQL, PostgreSQL, etc. have all implemented SQL language
  • Dbase, Access and other parts of the implementation of SQL language
Embedded SQL statements 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 can retrieve data from the database
  • SQL can insert, update, and delete data in the database
  • SQL can create a new database
  • SQL can create new tables, stored procedures and views in the database
  • SQL can set permissions for tables, stored procedures, and views

SQL DML and DDL

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

  • Query and update instructions form the DML part of SQL
  • SELECT-Get data from a database table
  • UPDATE-update the data in the database table
  • DELETE-delete data from a database table
  • INSERT INTO-insert 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)-drop database/table
  • create index-create index (search key)
  • drop index-delete index

SQL syntax is not case sensitive!

MySQL

Introduction

MySQL was developed by the Swedish company TcX and started in 1979.

  • MySQL is an implementation of a client/server structure
  • Need a server daemon mysqld and client program
  • MySQL features
  • Support multi-user, multi-thread, can be used on multiple platforms
  • Open source
  • High execution performance, fast running speed, easy to use
  • Provide users with a relatively simple solution

installation

Download resources

Enter the download page of the official website , select your own system, the required MySQL version and installation method (application installation or zip package):
![insert picture description here]](https://img-blog.csdnimg.cn/20210602170936496.png ?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzYyNDk0NQ==,size_16,color_70FFFF)
out of personal habit, t_zip) package.
Unzip the zip to the location you want.

Insert picture description here

Configure environment variables

Right click this computer ➡ properties ➡ advanced system settings ➡ environment variables:

Insert picture description here


edit the Path variable in the system variables, and create a new one:

MySQL bin folder path, such as mine:D:\Developer\MySQL\mysql-8.0.21-winx64\bin
Insert picture description here

installation

Run the cmd window as an administrator:

Insert picture description here


enter the MySQL bin directory:

Insert picture description here


install MySQL:

Enter mysqld -install
Insert picture description here


by the way, the command to remove the MySQL service is:mysqld -remove

initialization:

entermysqld --initialize
Insert picture description here
After initialization, a data folder will be generated in the MySQL directory, and there is a 计算机名.errfile in which you can view the initial password, which will be used later in this step.
Insert picture description here

Configure MySQL startup file

Create a new my.ini file in MySQL and edit:
Insert picture description here
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\Developer\\MySQL\\mysql-8.0.21-winx64
# 注意这里是自己的安装路径,用\\而非\
datadir=D:\\Developer\\MySQL\\mysql-8.0.21-winx64\\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

Start MySQL:

Run the cmd window as an administrator, enter net start mysql
Insert picture description here


by the way in the bin directory of the installation path (not in this directory after configuring the environment variables) , and the command to stop MySQL isnet stop mysql

Another method: Right-click "This Computer" ➡ Manage ➡ Services and Applications ➡ Services, find MySQL, and right-click to start/stop.
Insert picture description here

At this point, the basic installation of MySQL is complete, and you need to perform login operations later.

log in

mysql -u root -pStart MySQL by command

Insert picture description here
Expansion: Actually, the complete login command is:, mysql 【-h 主机名 -P 端口号】 -u 用户名 -pwhen connecting to this machine, the inside [] can of course be omitted.

As you can see, a password is required when logging in. Where does this password come from?

Mentioned in the initialization step, there is a 计算机名.errfile in the data folder under the MySQL directory , in which you can view and modify the initial password.
Insert picture description here

Modify the password
After logging in, use the MySQL command: ALTER USER 'root'@'localhost' IDENTIFIED BY '你的密码';

Insert picture description here


log in to MySQL again:

Insert picture description here
Ways to exit MySQL: exitorCtrl + c

enjoy