MySQL数据库安装及配置

一、安装及初始化

1.官网下载或者wget

[root@mysql ~]# ls
anaconda-ks.cfg
initserver.sh
mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.33-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm
mysql-community-common-8.0.33-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.33-1.el7.x86_64.rpm
mysql-community-devel-8.0.33-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.33-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm
mysql-community-libs-8.0.33-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm
mysql-community-server-8.0.33-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm
mysql-community-test-8.0.33-1.el7.x86_64.rpm

2. 确定 mysql-community-server 正常安装之后就可以开始配置

3. 初始化 mysqld 服务 mysqld initeialize

4. 启动服务 systemctl start mysqld

5. 添加开机启动列表 systecmctrl enable mysqld

6. 在 /var/log/mysql.log 中找到初始密码，前后复制空格 7. 登录管理 mysql -uroot -p

将复制的密码粘贴到输入密码的区

域，实现登录

8. 设置新的密码

9. 退出登录

二、数据库的管理

1. 数据库操作

删除库

mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

创建库

mysql> create datababse test charset utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)

查看库

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)

2. 表的操作

   1. 选择库

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

   2. 查看表

mysql>showtables;
+------------------------------------------------------+|Tables_in_mysql                   |+------------------------------------------------------+|columns_priv                    ||component                      ||db                         ||default_roles                    ||engine_cost                     ||func                        ||general_log                     ||global_grants                    ||gtid_executed                    ||help_category                    ||help_keyword                    ||help_relation                    ||help_topic                     ||innodb_index_stats                 ||innodb_table_stats                 ||ndb_binlog_index                  ||password_history                  ||plugin                       ||procs_priv 
|proxies_priv                    ||replication_asynchronous_connection_failover    ||replication_asynchronous_connection_failover_managed||replication_group_configuration_version       ||replication_group_member_actions          ||role_edges                     ||server_cost                     ||servers                       ||slave_master_info                  ||slave_relay_log_info                ||slave_worker_info                  ||slow_log                      ||tables_priv                     ||time_zone                      ||time_zone_leap_second                ||time_zone_name                   ||time_zone_transition                ||time_zone_transition_type              ||user                        |+------------------------------------------------------+38rowsinset(0.01sec)

3. 创建表

mysql> CREATE TABLE `test`.`user` (
-> `id` INT NOT NULL AUTO_INCREMENT,
-> `username` VARCHAR(45) NOT NULL,
-> `password` VARCHAR(45) NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE INDEX `username_UNIQUE` (`username` ASC)
VISIBLE)
-> COMMENT = 'us';
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
| user0 |
+----------------+

4. 删除表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
| user0 |
+----------------+
2 rows in set (0.00 sec)
mysql> drop table user0;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.01 sec)

新增列

ALTER TABLE `test`.`user`
ADD COLUMN `realname` VARCHAR(45) NULL AFTER `password`;
mysql> desc user;
+----------+-------------+------+-----+---------+---------
-------+
| Field | Type | Null | Key | Default | Extra
|
+----------+-------------+------+-----+---------+---------
-------+
| id | int | NO | PRI | NULL |
auto_increment |
| username | varchar(45) | NO | UNI | NULL |
|
| password | varchar(45) | NO | | NULL |
|
| realname | varchar(45) | YES | | NULL |
|
+----------+-------------+------+-----+---------+---------
-------+
4 rows in set (0.01 sec)

修改列名

mysql> ALTER TABLE `test`.`user`
-> CHANGE COLUMN `realname` `zsxm` VARCHAR(45) NULL
DEFAULT NULL ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+----------+-------------+------+-----+---------+---------
-------+
| Field | Type | Null | Key | Default | Extra
|
+----------+-------------+------+-----+---------+---------
-------+
| id | int | NO | PRI | NULL |
auto_increment |
| username | varchar(45) | NO | UNI | NULL |
|
| password | varchar(45) | NO | | NULL |
|
| zsxm | varchar(45) | YES | | NULL |
|
+----------+-------------+------+-----+---------+---------
-------+

删除列

mysql> ALTER TABLE `test`.`user`
-> DROP COLUMN `zsxm`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+----------+-------------+------+-----+---------+---------
-------+
| Field | Type | Null | Key | Default | Extra
|
+----------+-------------+------+-----+---------+---------
-------+
| id | int | NO | PRI | NULL |
auto_increment |
| username | varchar(45) | NO | UNI | NULL |
|
| password | varchar(45) | NO | | NULL |
|
+----------+-------------+------+-----+---------+---------
-------+
3 rows in set (0.00 sec)

5. 其他

3. 记录的操作

1. 新增

#insert into 表名 (字段名列表) values (字段值列表)
mysql> select * from user;
Empty set (0.00 sec)
mysql> insert into user (id,username,password) values
(3,'zhangsan','zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 3 | zhangsan | zhangsan |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 3 | zhangsan | zhangsan |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> insert into
user(username,password)values('lisi','lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 3 | zhangsan | zhangsan |
| 4 | lisi | lisi |
+----+----------+----------+
2 rows in set (0.00 sec)
mysql> desc user;
+----------+-------------+------+-----+---------+------
----------+
| Field | Type | Null | Key | Default | Extra
|
+----------+-------------+------+-----+---------+------
----------+
| id | int | NO | PRI | NULL |
auto_increment |
| username | varchar(45) | NO | UNI | NULL |
|
| password | varchar(45) | NO | | NULL |
|
+----------+-------------+------+-----+---------+------
----------+
3 rows in set (0.00 sec)

2. 删除

delte from user where 条件;
mysql> delete from user where username='zhangsan';
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 4 | lisi | lisi |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> delete from user;
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
Empty set (0.00 sec)

3. 修改

mysql>
mysql> insert into user values
(1,'zhangsan','zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (2,'lisi','lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (3,'wangwu','wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | zhangsan |
| 2 | lisi | lisi |
| 3 | wangwu | wangwu |
+----+----------+----------+
3 rows in set (0.00 sec)
mysql> update user set password=123 where
username='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123 |
| 2 | lisi | lisi |
| 3 | wangwu | wangwu |
+----+----------+----------+
3 rows in set (0.00 sec)

4、查询