Introduction to Mysql and sql statement

1. Overview of the database

Mysql: DBMS (DataBase Manager System)

Overview : A database is a warehouse that organizes, stores and manages data according to a data structure. It was created more than sixty years ago. There are many types of databases, from the simplest tables that store various data to Large-scale database systems with massive data storage have been widely used in all aspects. In an information society, the full and effective management and utilization of various information resources is a prerequisite for scientific research and decision-making management. Database technology is the core part of various information systems such as management information systems, office automation systems, decision support systems, and is an important technical means for scientific research and decision-making management.

Second, the classification of the database

Relational database: sql database
Non-relational database: nosql database, not only sql database

Relational database: Data has a fixed storage structure, stored in a library-table-row-column way, and there will be a structured relationship of the table when stored, the process is as follows: parse sql statement-connection layer-disk access-structured into table,;

Advantages :

1. Easy to understand , the structure of the two-dimensional table is very close to the real world, and the two-dimensional table is easy to understand;
2. Easy to use , general SQL statements make it very convenient to operate relational databases;
3. Easy to maintain , the ACID properties of the database, greatly Reduce the probability of data redundancy and data inconsistency;

bottleneck:

1. The read and write efficiency of massive data is low , and the concurrency of websites is high, often reaching tens of thousands of requests per second. For traditional relational databases, hard disk I/O is a big challenge.
2. High scalability and availability . In the web-based structure, the database is the most difficult to expand horizontally. When the number of users and visits of an application system is increasing day by day, the database cannot be as simple as web Server by adding more Hardware and service nodes to expand performance and load capacity.
Insert picture description here


Evolution from relational databases to non-relational databases : The biggest advantage of relational databases is the consistency of transactions. This feature makes the relational database applicable to all systems that require higher consistency. For example: the banking system. But in most web applications, the requirements for this consistency are not so strict, and a certain time interval is allowed, so the characteristic of relational databases is not so important. On the contrary, the huge price paid by relational databases in order to maintain consistency is poor read and write performance. And applications like Weibo and Facebook have extremely high requirements for concurrent reading and writing capabilities, and relational databases can no longer handle them. Therefore, a new data structure storage must be used to replace the relational database. So non-relational database applications were born.
Non-relational databases appeared to speed up the access speed of clients, because all non-relational databases put data in memory as much as possible; non-relational databases are stored in the form of key:value;
non Relational database: NoSQLMainly refers to those non-relational, distributed, mainly representing MongoDB, Redis, CouchDB. NoSQL proposes another concept, which is stored by key value, and the structure is unstable. Each tuple can have different fields. This is not limited to a fixed structure, which can reduce some time and space overhead . In this way, in order to obtain different user information, there is no need to perform multi-table query like in a relational database. Only need to extract the corresponding value value according to the key, so the complicated query relationship of relational database is avoided, and the query efficiency can be greatly increased;
Variable name = variable value
key (key) = value (value) name=dgf name=1


Three, an overview of Mysql:

Overview: MySQL is a relational database management system, developed by Sweden's MySQL AB, and currently belongs to an Oracle company. It is a true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular and standardized database language in the world. MySQL is an implementation of a client/server structure, which consists of a server daemon mysqld and many different client programs and libraries. SQL is a standardized language that makes it easier to store, update, and access information;
create database dgf character set utf8;

Four, Mysql storage principle

The overall structure of Mysql:

Insert picture description here
1. Connection pool: The top layer is responsible for connecting with clients, such as database connection APIs such as jdbc and odbj. At this layer, there is the concept of connection pool. Similar to thread pool, connection pool can handle many database requests at the same time. At the same time, this layer has the concept of SSL security, which can ensure that the connection is secure;
2.SQL interface: When SQL statements enter MySQL, they will first enter the SQL interface. This layer is the encapsulation layer, which separates the passed SQL statements. , Encapsulate the bottom-level results into the SQL data format;
3. Parser: This layer is responsible for splitting and verifying the SQL statement, if there is a problem with the statement, it will return an error, and if there is no problem, continue to execute it;
4. Optimizer: Optimize the results of SQL queries and generate multiple execution plans. In the end, the database will choose the most optimized plan to execute and return the results as soon as possible. For example, select a, b from c where d. Here, the data that meets d in the table c will be queried and their ab items will be projected, and the result will be returned, and the entire table will not be directly found out;
5. Cache: the SQL statement to be queried is hashed and cached, If the query statement is the same next time, the result will be returned directly after the SQL interface;
6. Storage engine: MySQL has many storage engines, each of which has different characteristics. They are responsible for organizing the storage format and location of files. Methods of accessing files and so on. The more commonly used ones are innoDB, MyISAM, MEMORY, etc.;
7. File system: the unit that actually stores physical files;

V. Case: Centos 7 builds Mysql 5.7

Case environment:

systemsystem
ip192.168.1.1
CPU namewww.zps.com
Required softwaremysql-5.7.12.tar.gz and boost_1_59_0.tar.gz
hardware
CPU core: 2Memory: 4G

Mysql download method:
https://downloads.mysql.com/archives/community/

Insert picture description here

Case steps:

Download and install the Mysql software program
need to use windows to download, can not use linux to download

wget http://dev.mysql.com/Downloads/MySQL-5.7/mysql-5.7.12.tar.gz
wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/download

Click to download mysql-5.7.12.tar.gz
extraction code: bvab

Click to download boost_1_59_0.tar.bz2
extraction code: qzdf

Insert picture description here


modify host name

hostname www.zps.com

vi /etc/hosts
192.168.1.1 www.zps.com
yum -y remove mysql-* boost-*
rpm -qa mysql
rpm -qa boost
yum -y install gcc gcc-c++ ncurses bison libgcrypt perl cmake ncurses-devel
tar -jxvf boost_1_59_0.tar.bz2
mv boost_1_59_0 /usr/local/boost/
groupadd mysql
useradd -r -g mysql mysql
tar zxvf mysql-5.7.12.tar.gz -C /usr/src/
cd /usr/src/mysql-5.7.12/
##编译多次时,需要删除CMAKE的缓存,rm -rf /usr/src/mysql-5.7.12/CMakeCache.txt
cmake  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost -DSYSCONFDIR=/etc

Notes:

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql                [MySQL安装的根目录]
-DMYSQL_DATADIR=/mydata/mysql/data                     [MySQL数据库文件存放目录]
-DDEFAULT_CHARSET=utf8                                 [设置默认字符集为utf8]
-DDEFAULT_COLLATION=utf8_general_ci                    [设置默认字符校对]
-DMYSQL_TCP_PORT=3306                                  [MySQL的监听端口]
-DMYSQL_USER=mysql                                     [MySQL用户名]    
-DWITH_MYISAM_STORAGE_ENGINE=1                         [安装MySQL的myisam数据库引擎]
-DWITH_INNOBASE_STORAGE_ENGINE=1                       [安装MySQL的innodb数据库引擎]
-DWITH_ARCHIVE_STORAGE_ENGINE=1                        [安装MySQL的archive数据库引擎]
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 					   [安装MySQL的blackhole数据库引擎]
-DWITH_MEMORY_STORAGE_ENGINE=1                         [安装MySQL的memory数据库引擎]
-DENABLE_DOWNLOADS=1                                   [编译时允许自主下载相关文件]
-DDOWNLOAD_BOOST=1 									   [允许下载BOOST]
-DWITH_BOOST=/usr/local/boost 						   [指定系统中存在的BOOST]

-DSYSCONFDIR=/etc                                      [MySQL配置文件所在目录]
-DWITH_READLINE=1                                      [MySQL的readline library]
-DMYSQL_UNIX_ADDR=/var/run/mysql/mysql.sock            [MySQL的通讯目录]
-DENABLED_LOCAL_INFILE=1                               [启用加载本地数据]
-DWITH_PARTITION_STORAGE_ENGINE=1  					   [启动mysql的分区存储结构]
-DEXTRA_CHARSETS=all                                   [使MySQL支持所有的扩展字符]
-DWITH_DEBUG=0                                         [禁用调试模式]
-DMYSQL_MAINTAINER_MODE=0 
-DWITH_SSL:STRING=bundled                              [通讯时支持ssl协议]
-DWITH_ZLIB:STRING=bundled                             [允许使用zlib library]
make -j `cat /proc/cpuinfo | grep processor| wc -l`
make install
ls /usr/local/mysql

Optimize and adjust Mysql program

chown -R mysql:mysql /usr/local/mysql
cp /usr/src/mysql-5.7.12/support-files/my-default.cnf /etc/my.cnf
cp /usr/src/mysql-5.7.12/support-files/mysql.server /etc/init.d/
chmod +x /etc/init.d/mysql.server

Write service control script

cat <<END >>/usr/lib/systemd/system/mysqld.service
[Unit]
Description=mysqldapi
After=network.target

[Service]
Type=forking
PIDFile=/usr/local/mysql/logs/mysqld.pid
ExecStart=/etc/init.d/mysql.server start
ExecReload=/etc/init.d/mysql.server restart
ExecStop=/etc/init.d/mysql.server stop
PrivateTmp=Flase

[Install]
WantedBy=multi-user.target
END

annotation:

PrivateTmp=Flase		##此配置必须关闭,不然mysql连接文件mysql.sock文件会默认生成在以下位置/tmp/systemd-private-83bba738e8ff4837b5ae657eff983821-mysqld.service-BPxWpJ/tmp/mysql.sock,导致数据库无法连接,将此配置项关闭后,则文件正常生成在/tmp/mysql.sock
echo "export PATH=$PATH:/usr/local/mysql/bin/" >>/etc/profile
source /etc/profile

Initialize the Mysql database service

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

annotation:

--initialize-insecure 				##禁用mysql的密码策略(密码复杂性等),--initializeaize是开启密码策略,自动生成密码在mysqld.log文件中
--user=mysql 						##运行的账户
--basedir=/usr/local/mysql 			##mysql的安装位置
--datadir=/usr/local/mysql/data		##mysql数据库服务数据的物理存放路径
cat <<END >/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid
skip-name-resolve
END

annotation:

[mysqld]					                            ##声明区域
basedir = /usr/local/mysql			                    ##mysql的安装位置
datadir = /usr/local/mysql/data		                    ##mysql的物理文件存放位置
port = 3306						                        ##mysql服务监听的端口
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES		##mysql的模式
character_set_server=utf8			                    ##字符集
init_connect='SET NAMES utf8'		
log-error=/usr/local/mysql/logs/mysqld.log			    ##指定日志文件位置
pid-file=/usr/local/mysql/logs/mysqld.pid			    ##指定运行服务所产生的pid文件位置
skip-name-resolve					                    ##跳过mysql的域名反向解析
mkdir /usr/local/mysql/logs
chown mysql:mysql /usr/local/mysql/logs/
systemctl start mysqld
systemctl enable mysqld
netstat -utpln |grep mysqld

Test connection to access the database

mysqladmin  -u root -p password "123456"            ##登录mysql设置密码
mysql -uroot -p123456
mysql> exit

Six, the addition, deletion and modification of Sql statement operations

Sql statement classification:

DDL: Data Definition Language, used to create a database, data objects and define its columns, such as create, alter, drop;
DML: Data Manipulation Language, used to query, insert, delete, and modify data in the database, such as select, insert, update, delete;
DCL: data control language, used to control the access permissions, access permissions, etc. of database components, such as commit, rollback, grant, revoke;

Data Record Type:
Date:

Insert picture description here


Decimal Type:

Insert picture description here


String:

Insert picture description here


SQL
What is SQL?

Structured Query Language: The structured query language
Sql is executed on the client side (windows executes on the command line, linux executes on the terminal) or executes in JDBC through java code

Open the client and log in to mysql

Directly open the terminal in any position under linux, you can
use various clients under windows to open the login mysql command directly in the terminal, execute the following code and enter the password to
mysql -uroot -p
exit:
exit

SQL statement specification

1. End with; (semicolon)
2. There is usually one space between keywords, but there is no problem
if there are more than one. 3. Line
breaks can be added to the sql statement 4. SQL is case-insensitive

Database related SQL
1. View all database commands:

show databases;

2. When creating a database, a project usually corresponds to only one database format: create database database name;

create database db1;
指定字符集的创建格式 :create database 数据库名称 CHARACTER SET utf8;
  create database db2 character set gbk;

3. View database details-format: show create database database name;

  show create database db1;

4. Delete database-format: drop database database name;

  drop database db2;

5. Select the database

  use db2;

Table related SQL
what is a table

-The table is the unit of data in the database is similar to the attribute in the object corresponding to the field in the object table in Java

Create table

格式: create table 表名 (字段1名 字段类型,字段2名 字段类型);
create table person (name varchar(10),age int);

The principle of creating a table:

-After writing the sql statement to create the table in the client, the client will hand the sql statement to the DBMS (MySql). After the DBMS parses it, the table in the statement and the fields in the table will be created in the database

View all tables

show tables;

View table structure

-格式:show create table 表名;
show create table person;
-格式: desc 表名
desc person;

Table engine

InnoDB: supports advanced database processing, including foreign keys of things, etc. The default is innodb
Myisam: only supports basic storage of data

Specify the character set and engine of the table when creating the table

-格式
create table person (id int,name varchar(10)) engine=myisam charset=utf8;

Exercise: Create 5 databases, create 1 table in each database, and then delete the database. Practice
creating student tables

create table student (
    id int,
    name varchar(10),
    chinese int,
    math int,
    english int
);

Modify the created table
1. Modify the table name-format: rename table original name to new name;

rename table student to t_student;

2. Modify the attributes of the table-format: alter table table name engine=myisam charset=gbk;

alter table t_student engine=myisam charset=gbk;

3. Add the field of the table-add at the end-format: alter table table name add age int;

alter table student add age int;
   -在最前面添加 -格式:alter table 表名 add fatherAge int first;
alter table student add fatherAge int first;
   -在某个字段之后添加 -格式:alter table 表名 add fatherName varchar(10) after fatherAge;
alter table student add fatherName varchar(10) after fatherAge;

4. Delete table field-format: alter table table name drop field name;

alter table student drop fatherAge;

5. Modify the field name and type-format: alter table table name change original field name new field name new field type;

alter table student change age fatherAge varchar(10);

6. Modification type and order-format 1: alter table table name modify field name new field type after field name-format 2: alter table table name modify field name new field type first

alter table student modify fatherAge int first; 
alter table student modify fatherAge int after id; 
alter table dgf modify 姓名 varchar(20);

Delete table

-格式: drop table 表名
drop table student;

Seven, practice:

1. Create a hero table with id name type three fields

create table hero (id int,name varchar(10),type varchar(10));

2. Modify the attribute engine of the hero table to myisam and encode the attribute to utf8

alter table hero engine=myisam charset=utf8;

3. Add a money int field to the hero table after the name field

alter table hero add money int after name;

4. Modify the type field name to hero_type varchar(30)

alter table hero change type hero_type varchar(30);

5. Modify the name of the hero table as hero

rename table hero to heros;

6. Modify the name field to the end

alter table heros modify name varchar(10) after hero_type;

7. Delete the money field

alter table heros drop money;

8. Delete the hero table

drop table heros;

Data related SQL

create table student(
        id int,
        name varchar(20),
        chinese int,
        math int,
        english int
);

1. Insert statement-full table insert format: insert into table name values ​​(value 1, value 2, value 3...); The number and order of the values ​​after values ​​inserted into the whole table must be consistent with the number and order of the fields in the table

insert into student values(1,'lisi',18,26,88);
-指定字段插入格式:insert into 表名 (字段名1,字段名2)values(值1,值2);
insert into student (name,math)values('张三',90);
-批量插入
-全表批量 
insert into student values(10,'刘备',10,20,30),(11,'关羽',12,34,22),(12,'张飞',3,4,2); 
-指定字段批量
insert into student (id,name,english)values(20,'孙悟空',0),(21,'猪八戒',90),(22,'沙师弟',5);

2. Query statement-format: select field 1, field 2, field 3... from table name-if the field position is written *, it means to query all fields

select * from student;
select id,name,math from student;

3. Update (modification) statement-modify the format of the entire table: update table name set math=100;

update student set math=100; 
-添加条件修改格式: update 表名 set math=50 where id=10;
update student set math=50 where id=10;

4. Delete statement-delete the data format of the specified condition: delete from table name where id=10;

delete from student where id=10;
delete from student where chinese is null;
-删除表中所有数据 
delete from student;

After Mysql5.7 is installed, there are 4 databases by default

information_schema : information_schema is an information database that holds information about all other databases maintained by the MySQL server. (Such as database name, database tables, data types and access permissions of table columns, etc.)
Mysql : Mainly responsible for storing database users, permission settings, keywords and other control and management information that MySQL needs to use
performance_schema : mainly used for collection Database server performance parameters. And the storage engine of the table in the library is PERFORMANCE_SCHEMA, and the user cannot create the table
Sys with the storage engine of PERFORMANCE_SCHEMA : All data sources in the Sys library come from: performance_schema. The goal is to reduce the complexity of performance_schema so that DBAs can better read the contents of this library. Let the DBA understand the operation of the DB faster.

Eight, Mysql basic optimization operation

1. Forgot password:

vi /etc/my.cnf
[mysqld]
skip-grant-tables 					 ##添加该行,跳过密码验证
:wq
systemctl restart mysqld            ##重启mysql
[[email protected] ~]# mysql			        ##登录后操作
mysql> use mysql;
Database changed
mysql> update user set authentication_string=password('123456') where user="root";
mysql> select Host,user,authentication_string from user;
+-----------+-----------+-------------------------------------------+
| Host      | user      | authentication_string(原mysql 5.5的password字段)                     |
+-----------+-----------+-------------------------------------------+
| localhost | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
mysql> exit

[[email protected] ~]# vi /etc/my.cnf
[mysqld]
#skip-grant-tables					 ##注释该行
:wq
systemctl restart mysqld            ##重启mysql
mysql -uroot -p123456
mysql> exit

2. Solve the mysql garbled problem:

vi /etc/my.cnf
[client]
default-character-set=utf8
systemctl restart mysqld            ##重启mysql

Nine, the application of Mysql storage engine

1. View the type of storage engine that the database can configure:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
Insert picture description here


2. View the storage engine that the table is using:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show table status where name='user';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment                     |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| user | MyISAM |      10 | Dynamic    |    2 |            132 |         264 | 281474976710655 |         4096 |         0 |           NULL | 2021-06-09 10:45:57 | 2021-06-09 10:48:13 | NULL       | utf8_bin  |     NULL |                | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)
Insert picture description here


3. Create a table and specify the storage engine

mysql> create database haha;
Query OK, 1 row affected (0.00 sec)

mysql> create table haha.table1(id int) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> use haha;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show table status where name='table1';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2021-06-09 11:15:59 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.01 sec)

4. Modify the storage engine of an existing table:

mysql> alter table haha.table1 engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status where name='table1';
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 1970324836974591 |         1024 |         0 |           NULL | 2021-06-09 11:20:12 | 2021-06-09 11:20:12 | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

5. Modify the default storage engine of the Mysql service:

[[email protected] ~]# vi /etc/my.cnf					##新添配置项
[mysqld]
default-storage-engine=MyISAM

[[email protected] ~]# systemctl restart mysqld
[[email protected] ~]# mysql -uroot -p123456
mysql> create table haha.table2(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> use haha;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show table status where name='table2';
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table2 | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 1970324836974591 |         1024 |         0 |           NULL | 2021-06-09 11:24:18 | 2021-06-09 11:24:18 | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

X. Detailed explanation of Mysql configuration file

[[email protected] ~]# vi /etc/my.cnf
[mysqld]

########basic settings########
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
init_connect='SET NAMES utf8'
log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid
user = mysql
symbolic-links=0
server-id = 11
#bind_address = 192.168.100.101
autocommit = 1
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 100
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 128M
tmp_table_size = 128M
tmpdir = /dev/shm
max_allowed_packet = 16M
interactive_timeout = 60
wait_timeout = 60
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M

########log settings########
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 1
min_examined_row_limit = 100

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_bin = /usr/local/mysql/logs/mysql-bin
#sync_binlog = 4
gtid_mode = on
enforce_gtid_consistency = 1
#log_slave_updates
binlog_format = row
#relay_log = /usr/local/mysql/logs/mysql-relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

########innodb settings########
innodb_page_size = 16K
innodb_buffer_pool_size = 4G
#innodb_buffer_pool_instances = 8
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
#innodb_flush_method = O_DIRECT
#innodb_log_group_home_dir = /usr/local/mysql/logs/redolog/
#innodb_undo_directory = /usr/local/mysql/logs/undolog/
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 0
#innodb_flush_neighbors = 1
#innodb_log_file_size = 4G
#innodb_log_buffer_size = 16M
#innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
#innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1
innodb_sort_buffer_size = 64M

########semi sync replication settings########
#plugin_dir=/usr/local/mysql/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000

[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
#innodb_purge_rseg_truncate_frequency = 128
#binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
#show_compatibility_56=on

annotation:

[mysqld]
user = mysql
mysql以什么用户运行
port = 31306   
mysql运行在哪个端口
datadir = /usr/loca/mysql/data/
mysql的数据目录
socket=/tmp/mysql.sock
mysql以socket方式运行的sock文件位置

symbolic-links=0
是否支持符号链接,即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录,为0不开启

########basic settings########
server-id = 11
mysql的服务器分配id,在启用主从和集群的时候必须指定,每个节点必须不同
#bind_address = 10.166.224.32
mysql监听的ip地址,如果是127.0.0.1,表示仅本机访问
autocommit = 1
数据修改是否自动提交,为0不自动提交
character_set_server=utf8mb4
服务器使用的字符集
skip_name_resolve = 1
禁用DNS主机名查找,启用以后用内网地址向mysqlslap请求响应快了一半
max_connections = 800
mysql最大连接数

max_connect_errors = 1000
某台host连接错误次数等于max_connect_errors(默认10) ,主机'host_name'再次尝试时被屏蔽。可有效反的防止dos攻击
transaction_isolation = READ-COMMITTED
数据库事务隔离级别
1.READ-UNCOMMITTED(读取未提交内容)级别
2. READ-COMMITTED(读取提交内容)
3. REPEATABLE-READ(可重读)
4.SERIERLIZED(可串行化)
默认级别REPEATABLE-READ
explicit_defaults_for_timestamp = 1
mysql中TIMESTAMP类型和其他的类型有点不一样(在没有设置explicit_defaults_for_timestamp=1的情况下)
join_buffer_size = 128M
当我们的join是ALL,index,rang或者Index_merge的时候使用的buffer。 实际上这种join被称为FULL JOIN

tmp_table_size = 128M
规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下
tmpdir = /dev/shm/mysql-tmp/
保存临时文件的目录
max_allowed_packet = 16M
mysql最大接受的数据包大小

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
sql_mode 模式,定义了你MySQL应该支持的sql语法,对数据的校验等等,限制一些所谓的‘不合法’的操作
interactive_timeout = 60
服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端
wait_timeout = 60
服务器关闭非交互连接之前等待活动的秒数,在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)

read_buffer_size = 16M
读入缓冲区的大小,将对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区
read_rnd_buffer_size = 32M
随机读缓冲区大小,当按任意顺序读取行时(列如按照排序顺序)将分配一个随机读取缓冲区,进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度

sort_buffer_size = 32M
是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存

########log settings########

#log_error = /data/local/mysql-5.7.19/log/mysql-error.log
错误日志位置

slow_query_log = 1
是否开启慢查询日志收集
slow_query_log_file = /data/local/mysql-5.7.19/log/mysql-slow.log
慢查询日志位置
log_queries_not_using_indexes = 1
是否记录未使用索引的语句

log_slow_admin_statements = 1
慢查询也记录那些慢的optimize table,analyze table和alter table语句

log_slow_slave_statements = 1
记录由Slave所产生的慢查询

log_throttle_queries_not_using_indexes = 10
设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间

expire_logs_days = 90
日志自动过期清理天数

long_query_time = 1
设置记录慢查询超时时间

min_examined_row_limit = 100
查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

########replication settings########

#master_info_repository = TABLE
从机保存主节点信息方式,设成file时 会生成master.info 和 relay-log.info2个文件,设成table,信息就会存在mysql.master_slave_info表中。不管是设置的哪种值,都不要移动或者编辑相关的文件和表

#relay_log_info_repository = TABLE
用于保存slave读取relay log的位置信息,可选值为“FILE”、“TABLE”,以便crash重启后继续恢复

log_bin = /data/local/mysql-5.7.19/log/mysql-bin
binlog的保存位置,不能指定确定的文件名如mysql-bin.log,只能指定位置和前缀,会生成以前缀为开头的一系列文件

#sync_binlog = 4
这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下:
sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
gtid_mode = on
启用gtid类型,否则就是普通的复制架构

enforce_gtid_consistency = 1
强制GTID的一致性

#log_slave_updates
slave更新是否记入日志,在做双主架构时异常重要,影响到双主架构是否能互相同步
binlog_format = row
binlog日志格式,可选值“MIXED”、“ROW”、“STATEMENT”,在5.6版本之前默认为“STATEMENT”,5.6之后默认为“MIXED”;因为“STATEMENT”方式在处理一些“不确定”性的方法时会造成数据不一致问题,我们建议使用“MIXED”或者“ROW”

#relay_log = /data/local/mysql-5.7.19/log/mysql-relay.log
从机保存同步中继日志的位置

#relay_log_recovery = 1
当slave从库宕机后,假如relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从master上获取日志,这样就保证了relay-log的完整性

#binlog_gtid_simple_recovery = 1
这个参数控制了当mysql启动或重启时,mysql在搜寻GTIDs时是如何迭代使用binlog文件的。 这个选项设置为真,会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快

#slave_skip_errors = ddl_exist_errors
跳过指定error no类型的错误,设成all 跳过所有错误

########innodb settings########

innodb_page_size = 16K
innodb每个数据页大小,这个参数在一开始初始化时就要加入my.cnf里,如果已经创建了表,再修改,启动MySQL会报错

innodb_buffer_pool_size = 4G
缓存innodb表的索引,数据,插入数据时的缓冲,专用mysql服务器设置的大小: 操作系统内存的70%-80%最佳

#innodb_buffer_pool_instances = 8
可以开启多个内存缓冲池,把需要缓冲的数据hash到不同的缓冲池中,这样可以并行的内存读写

#innodb_buffer_pool_load_at_startup = 1
默认为关闭OFF。如果开启该参数,启动MySQL服务时,MySQL将本地热数据加载到InnoDB缓冲池中

#innodb_buffer_pool_dump_at_shutdown = 1
默认为关闭OFF。如果开启该参数,停止MySQL服务时,InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘

#innodb_lru_scan_depth = 2000
根据 官方文档 描述,它会影响page cleaner线程每次刷脏页的数量, 这是一个每1秒 loop一次的线程

innodb_lock_wait_timeout = 5
事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败;参数的时间单位是秒

#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
这两个设置会影响InnoDB每秒在后台执行多少操作. 大多数写IO(除了写InnoDB日志)是后台操作的. 如果你深度了解硬件性能(如每秒可以执行多少次IO操作),则使用这些功能是很可取的,而不是让它闲着

#innodb_flush_method = O_DIRECT
默认值为 fdatasync. 如果使用 硬件RAID磁盘控制器, 可能需要设置为 O_DIRECT. 这在读取InnoDB缓冲池时可防止“双缓冲(double buffering)”效应,否则会在文件系统缓存与InnoDB缓存间形成2个副本(copy). 如果不使用硬件RAID控制器,或者使用SAN存储时, O_DIRECT 可能会导致性能下降

#innodb_log_group_home_dir = /data/local/mysql-5.7.19/log/redolog/
innodb重做日志保存目录

#innodb_undo_directory = /data/local/mysql-5.7.19/log/undolog/
innodb回滚日志保存目录

#innodb_undo_logs = 128
undo回滚段的数量, 至少大于等于35,默认128

#innodb_undo_tablespaces = 0
用于设定创建的undo表空间的个数,在mysql_install_db时初始化后,就再也不能被改动了;默认值为0,表示不独立设置undo的tablespace,默认记录到ibdata中;否则,则在undo目录下创建这么多个undo文件,例如假定设置该值为4,那么就会创建命名为undo001~undo004的undo tablespace文件,每个文件的默认大小为10M。修改该值会导致Innodb无法完成初始化,数据库无法启动,但是另两个参数可以修改

#innodb_flush_neighbors = 1
InnoDB存储引擎在刷新一个脏页时,会检测该页所在区(extent)的所有页,如果是脏页,那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。对于传统机械硬盘建议使用,而对于固态硬盘可以关闭。

#innodb_log_file_size = 4G
这个值定义了日志文件的大小,innodb日志文件的作用是用来保存redo日志。一个事务对于数据或索引的修改往往对应到表空间中的随机的位置,因此当刷新这些修改到磁盘中就会引起随机的I/O,而随机的I/O往往比顺序的I/O更加昂贵的开销,因为随机的I/O需要更多的开销来定位到指定的位置。innodb使用日志来将随机的I/O转为顺序的I/O,只要日志文件是安全的,那么事务就是永久的,尽管这些改变还没有写到数据文件中,如果出现了当机或服务器断电的情况,那么innodb也可以通过日志文件来恢复以及提交的事务。但是日志文件是有一定的大小的,所以必须要把日志文件记录的改变写到数据文件中,innodb对于日志文件的操作是循环的,即当日志文件写满后,会将指针重新移动到文件开始的地方重新写,但是它不会覆盖那些还没有写到数据文件中的日志,因为这是唯一记录了事务持久化的记录
如果对 Innodb 数据表有大量的写入操作,那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了,就会增加恢复的时间,因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复

#innodb_log_buffer_size = 16M
事务在内存中的缓冲。 分配原 则:控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次

#innodb_purge_threads = 4
控制是否使用,使用几个独立purge线程(清除二进制日志)

innodb_large_prefix = 1
mysql在5.6之前一直都是单列索引限制767,起因是256×3-1。这个3是字符最大占用空间(utf8)。但是在5.6以后,开始支持4个字节的uutf8。255×4>767, 于是增加了这个参数。这个参数默认值是OFF。当改为ON时,允许列索引最大达到3072
innodb_thread_concurrency = 64
InnoDB kernel并发最大的线程数。 1) 最少设置为(num_disks+num_cpus)*2。 2) 可以通过设置成1000来禁止这个限制

#innodb_print_all_deadlocks = 1
是否将死锁相关信息保存到MySQL 错误日志中

#innodb_strict_mode = 1
开启InnoDB严格检查模式,尤其采用了页数据压缩功能后,最好是开启该功能。开启此功能后,当创建表(CREATE TABLE)、更改表(ALTER TABLE)和创建索引(CREATE INDEX)语句时,如果写法有错误,不会有警告信息,而是直接抛出错误,这样就可直接将问题扼杀在摇篮里

innodb_sort_buffer_size = 64M
ORDER BY 或者GROUP BY 操作的buffer缓存大小

########semi sync replication settings########
#plugin_dir=/data/local/mysql-5.7.19/lib/plugin
指定mysql的插件目录

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
指定载入哪些插件

#loose_rpl_semi_sync_master_enabled = 1
控制主库上是否开启semisync

#loose_rpl_semi_sync_slave_enabled = 1
控制备库是否开启semisync

#loose_rpl_semi_sync_master_timeout = 5000
单位毫秒,防止半同步复制在没有收到确认的情况下,发送堵塞。master在超时之前没有收到确认,将恢复到异步复制,继续执行半同步没有进行的操作

[mysqld-5.7]

#innodb_buffer_pool_dump_pct = 40
表示转储每个bp instance LRU上最热的page的百分比。通过设置该参数可以减少转储的page数

innodb_page_cleaners = 4
为了提升扩展性和刷脏效率,在5.7.4版本里引入了多个page cleaner线程。从而达到并行刷脏的效果
在该版本中,Page cleaner并未和buffer pool绑定,其模型为一个协调线程 + 多个工作线程,协调线程本身也是工作线程。因此如果innodb_page_cleaners设置为8,那么就是一个协调线程,加7个工作线程

#innodb_undo_log_truncate = 1
是否开启在线回收(收缩)undo log日志文件,支持动态设置

#innodb_max_undo_log_size = 2G
当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M

#innodb_purge_rseg_truncate_frequency = 128
控制回收(收缩)undo log的频率。undo log空间在它的回滚段没有得到释放之前不会收缩, 想要增加释放回滚区间的频率,就得降低设定值

#binlog_gtid_simple_recovery=1
这个参数控制了当mysql启动或重启时,mysql在搜寻GTIDs时是如何迭代使用binlog文件的。 这个选项设置为真,会提升mysql执行恢复的性能。因为这样mysql-server启动和binlog日志清理更快。该参数为真时,mysql-server只需打开最老的和最新的这2个binlog文件

log_timestamps=system
在MySQL 5.7.2 新增了 log_timestamps 这个参数,该参数主要是控制 error log、genera log,等等记录日志的显示时间参数。 在 5.7.2 之后改参数为默认 UTC 这样会导致日志中记录的时间比中国这边的慢,导致查看日志不方便。修改为 SYSTEM 就能解决问题

#transaction_write_set_extraction=MURMUR32
这个神奇的参数5.7.6版本引入,用于定义一个记录事务的算法,这个算法使用hash标识来记录事务。如果使用MGR,那么这个hash值需要用于分布式冲突检测何处理,在64位的系统,官网建议设置该参数使用 XXHASH64 算法。如果线上并没有使用该功能,应该设为off

#show_compatibility_56=on
从mysql5.7.6开始information_schema.global_status已经开始被舍弃,为了兼容性,此时需要打开 show_compatibility_56

Sql statement exercise:
database related
1, the command to connect to the database

mysql -uroot -p 

2. Create a database

create database db2;

3. Query all databases

show databases;

4. Query the information of a single database

show create database db2;

5. Delete the database

drop database db2;

6. Specify the character set when creating the database

create database db2 character set utf8;

7. Use the database

use db2;

Table related SQL

创建表student
create table student (id int, name varchar(20));
查看所有的表
show tables;
查看表属性 数据库表的引擎 和 编码
show create table student;
查看表结构
desc student;
创建表 指定引擎和编码
create table teacher (id int, name varchar(10)) engine=innodb charset=utf8;
修改表的名字
rename table student to stu;
给表添加字段
-在最后
alter table stu add age int;
-在最前端添加
alter table stu add age int first;
-在某个字段的后面
alter table stu add age int after id;
删除表字段
alter table stu drop age;
修改表的属性
alter table stu engine=myisam charset=utf8;
修改字段的名称和类型
alter table stu change age fatherAge int;
修改字段的类型和位置
alter table stu modify fatherAge double after name;
删除表
drop table stu;

Data related

-创建商品表:
create table t_item(
        id int,
        title varchar(40),
        num int,
        price double,
        category varchar(20),
        createDate date
);

1. Insert data-insert into t_item values ​​(1,'towel',89,4.5,'daily necessities','1985-06-23'); -specify field insert

insert into t_item (title,price,category)values
('香皂',1.5,'日用品');
-批量插入数据

insert into t_item values
(3,'鼠标',200,20,'电脑配件','2008-12-21'),
(4,'键盘',260,35,'电脑配件','2018-11-25'),
(3,'写字板',10,120,'电脑配件','2006-10-28');

2. Modify the data

update t_item set title='双飞燕鼠标' where title='鼠标';

-修改多条数据
update t_item set title='鼠标',price=38 where title='双飞燕鼠标';

3. Delete data

delete from t_item where id is null;
delete from t_item where title='键盘';

Query data

-查询sql 后面也可以添加where 条件
select title from t_item where category='日用品';

Exercise:
1. Create a newdb database to create a table emp (employee table) fields: id name, salary (salary), dept (department name), joinDate (entry date)

create table emp( id int, name varchar(10), salary double, dept varchar(10), joinDate date );

2. Insert 7 people from Liu Guanzhang and Tang Seng's four-member group. Liu Guanzhang's department is: Tang Seng of the Three Kingdoms Department. Their department is the Scripture Study Department.

insert into emp values(1,'刘备',1800,'三国部','2001-12-10'),(2,'关羽',800,'三国部','2002-12-10'),(3,'张飞',5800,'三国部','2003-12-10'),(4,'唐僧',11800,'取经部','2004-12-10'),(5,'悟空',2800,'取经部','2005-12-10'),(6,'八戒',1800,'取经部','2006-12-10'),(7,'悟净',800,'取经部','2007-12-10')

3. Modify Liu Bei's salary to 2000

update emp set salary=2000 where id=1;

4. Modify Tang Monk's name as Tang Elder

update emp set name='唐长老' where id=4;

5. Add an age field to the table after the name field

alter table emp add age int after name;

6. Modify the age of the Ministry of Three Kingdoms to 45

update emp set age=45 where dept='三国部';

7. Revise the date of the study department to today's date

update emp set joinDate='2018-2-26' where dept='取经部';

8. Modify the age for wages less than 5000 to 18

update emp set age=18 where salary<5000; 

9. Delete all employees in the three countries department

delete from emp where dept='三国部'; 

10. Delete the entire table

delete from emp;
drop table emp;