Install MySQL for Ubuntu and query the average score

1: Download and install Mysql：

1.1 Download... On the official website ：

1.2 Unzip the file : tar -xvf mysql-server_5.7.13-1ubuntu16.04_i386.deb-bundle.tar

1.3: install ：

libmysqlclient20_5.7.13-1ubuntu16.04_i386.deb

libmysqlclient-dev_5.7.13-1ubuntu16.04_i386.deb

libmysqld-dev_5.7.13-1ubuntu16.04_i386.deb

mysql-common_5.7.13-1ubuntu16.04_i386.deb

mysql-community-source_5.7.13-1ubuntu16.04_i386.deb

mysql-community-client_5.7.13-1ubuntu16.04_i386.deb

mysql-community-server_5.7.13-1ubuntu16.04_i386.deb

mysql-server_5.7.13-1ubuntu16.04_i386.deb

1.4: Inquire about mysql Service status

service mysql start

1.5: Get into mysql

mysql -u root -p

2: Initialize configuration

sudo mysql_secure_installation

Configuration items ：

#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N ( choice N , No strong password verification )

#2
Please set the password for root here...
New password: ( Input password )
Re-enter new password: ( Repeat input )

#3
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N ( choice N, Don't delete anonymous users )

#4
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N ( choice N, allow root Remote connection )

#5
By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N ( choice N, Don't delete test database )

#6
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y ( choice Y, The modification will take effect immediately )

2.2: Check mysql Service status ：

systemctl status mysql.service

3: Configure remote access ： 

stay Ubuntu Next MySQL By default, only local access is allowed , Use workbench The connection tool cannot be connected ;
If you want other machines to be accessible as well , Configuration required ;

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf # find bind-address Modify value 0.0.0.0( If you need remote access )
sudo /etc/init.d/mysql restart # restart mysql

Enter the user password ：

# Switch database
mysql>use mysql;
# Query user table command ：
mysql>select User,authentication_string,Host from user;
# Check the status
select host,user,plugin from user;

# Set permissions and passwords

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ' password '; # Use mysql_native_password Modify encryption rules
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY ' password ' PASSWORD EXPIRE NEVER; # Update the user's password
mysql> UPDATE user SET host = '%' WHERE user = 'root'; # Allow remote access

# Refresh cache Middle configuration Refresh the permissions
mysql>flush privileges; 
mysql>quit;

4 Create a new database and user ：（ Introduction to the steps ）

Convert to logical data model , And check for logical errors , There is an error modifying the basic data model

 

 

Transform physical data model , And export Sql Execute statement , And run whether the table can be generated normally

 

 

5: The above operations and mysql After installation , start-up Mysql Server and enter mysql shell Interface ：

5.1: establish jw database

create database jw;

5.2: Create a form of student grades ：

use jw;

Design field list ： Student number , full name , Gender , subject , achievement , Examination time

create table xscj (id int(3) auto_increment not null primary key, xm varchar(10),xb varchar(2),km varchar(30),cj int(3),kssj date);

After the student achievement form is established, use describe Command view table ：

describe person;

 5.3: insert data

insert into xscj values(null,' Li Xiaolong ',' Woman ',' Cloud computing and big data processing ',76,'2021-07-10');

The verification results ：select

 select * from xscj;

5.4: Calculate average ： 

select AVG(cj) from xscj where km=' Cloud computing and big data processing ' and kssj='2021-3-25';

The result is consistent with the calculated average score